import numpy as np
from time import time
import matplotlib.pyplot as plt

from sklearn.datasets import fetch_20newsgroups
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.feature_extraction.text import HashingVectorizer
from sklearn.feature_selection import SelectKBest, chi2
from sklearn.linear_model import RidgeClassifier
from sklearn.svm import LinearSVC
from sklearn.linear_model import SGDClassifier
from sklearn.linear_model import Perceptron
from sklearn.linear_model import PassiveAggressiveClassifier
from sklearn.naive_bayes import BernoulliNB, MultinomialNB
from sklearn.neighbors import KNeighborsClassifier
from sklearn.neighbors import NearestCentroid
from sklearn.utils.extmath import density
from sklearn import metrics

import pandas as pd
import connect_aws_db as cadb


%matplotlib inline

"""
Explanation: classify reviews
This notebook describes the binary classification of Yelp hotel reviews on whether or not they are dog related.
End of explanation
"""


engine = cadb.connect_aws_db(write_unicode=True)

"""
Explanation: Connect to DB
End of explanation
"""


cmd = "SELECT review_id, review_rating, review_text FROM bf_reviews"

bfdf = pd.read_sql_query(cmd, engine)

print(len(bfdf))
bfdf.head(5)

len(bfdf[bfdf['review_text'].str.len() > 500])

num_cities = 'all'
if num_cities is 'all':
    print('hello')

"""
Explanation: Restore BF Reviews
End of explanation
"""


cmd = "SELECT * FROM yelp_reviews"

yelpdf = pd.read_sql_query(cmd, engine)

print(len(yelpdf))
yelpdf.head(5)

yelp_review_data = yelpdf['review_text'].values

train_data = np.hstack((bfdf['review_text'].values[:1500],
                        yelpdf['review_text'].values[:1500]))

len(train_data)

labels = ['dog'] * 1500
labels.extend(['general'] * 1500)
y_train = labels

t0 = time()
vectorizer = TfidfVectorizer(sublinear_tf=True, max_df=0.5,
                             stop_words='english')
X_train = vectorizer.fit_transform(train_data)
duration = time() - t0
print('vectorized in {:.2f} seconds.'.format(duration))
print(X_train.shape)

feature_names = np.asarray(vectorizer.get_feature_names())

len(feature_names)

penalty = 'l2'
clf = LinearSVC(loss='l2', penalty=penalty, dual=False, tol=1e-3)

print(clf)

clf.fit(X_train, y_train)

#yelp_review_data[:10]

X_yrevs = vectorizer.transform(yelp_review_data)

pred = clf.predict(X_yrevs)

pred.shape

# print the number of yelp hotel reviews that are identified as dog reviews:
len(np.where(pred == 'dog')[0])

ydogrevs = np.where(pred == 'dog')[0]

yelp_review_data[ydogrevs[4]]

yelp_review_data[ydogrevs[5]]

ygenrevs = np.where(pred == "general")[0]
ygenrevs

yelp_review_data[ygenrevs[4]]

print(len(pred))
print(len(yelpdf))

pred[:10]

"""
Explanation: Restore Yelp Reviews
End of explanation
"""


yelpdf['review_category'] = pred

"""
Explanation: Add a New Column Stating the Review Type
End of explanation
"""


# conn = engine.connect()

# cmd = "ALTER TABLE yelp_reviews "
# cmd += "ADD review_category VARCHAR(56)"

# print(cmd)
# result = conn.execute(cmd)

# cmd = "UPDATE TABLE yelp_reviews "
# cmd += "SET review_category = ('"
# cmd += "','".join(pred)+"') "
# cmd += "WHERE yelp_review_id = ('"
# cmd += "','".join(yelpdf['yelp_review_id'].values)+"')"
# print(cmd[:500])
# print(cmd[-50:])

#result = conn.execute(cmd)

cmd = "DROP TABLE yelp_reviews"

result = conn.execute(cmd)

cmd = """
        CREATE TABLE yelp_reviews
        (
        rev_id MEDIUMINT AUTO_INCREMENT,
        business_id VARCHAR(256),
        yelp_review_date DATE,
        yelp_review_id VARCHAR(256),
        review_rating INT,
        review_text VARCHAR(5000),
        user_id VARCHAR(256),
        review_category VARCHAR(56),
        PRIMARY KEY (rev_id)
        )
        """
        

result = conn.execute(cmd)

yelpdf.to_sql('yelp_reviews', engine, if_exists='append', index=False)

"""
Explanation: Update the yelp_reviews SQL Table with the Dog Friendly Data
End of explanation
"""


conn = engine.connect()

cmd = "SELECT biz_review_id, review_text FROM ta_reviews limit 3"

res = conn.execute(cmd)

dat = res.fetchall()

dat

for row in result:
    print(row)

bizids = [str(el[0]) for el in dat]

len(bizids)

cats = ['doggies', 'giraffes', 'random']

cmd = 'UPDATE ta_reviews SET review_category = NULL '
cmd += 'WHERE biz_review_id in ('+(',').join(bizids)+')'
cmd

res = conn.execute(cmd)

len(bfdf)

dids = bfdf[bfdf['review_rating'] == 3]['review_id'].values

dids[:5]

"""
Explanation: test updating the ta review category
This section tests updating the review_category column without deleting the entire table.
End of explanation
"""


