import os
import pandas as pd
import numpy as np
import sqlite3

from flask import Flask, jsonify, render_template

app = Flask(__name__)

@app.route("/")
def index():
    """Return the homepage."""
    return render_template("index.html")

@app.route("/sentiment")
def sentiment():
    conn = sqlite3.connect('./db/yelp_stars_2018.db')
    c = conn.cursor()
    one_star_2018 = c.execute(
        "SELECT polarity, subjectivity FROM one_star_2018 limit 50000").fetchall()
    one_star_polarity = [row[0] for row in one_star_2018]
    one_star_subjectivity = [row[1] for row in one_star_2018]

    one_star = {
        "x": one_star_polarity,
        "y": one_star_subjectivity,
        "mode": 'markers',
        "type": 'scatter',
        "name": 'One Star',
        "marker": {
            "size":  2,
            "color": one_star_subjectivity,
            "colorscale": 'RdYlGn'
        }
    }

    two_star_2018 = c.execute(
        "SELECT polarity, subjectivity FROM two_star_2018 limit 20000").fetchall()
    two_star_polarity = [row[0] for row in two_star_2018]
    two_star_subjectivity = [row[1] for row in two_star_2018]

    two_star = {
        "x": two_star_polarity,
        "y": two_star_subjectivity,
        "name": 'Two Star'
    }

    three_star_2018 = c.execute(
        "SELECT polarity, subjectivity FROM three_star_2018 limit 25000").fetchall()
    three_star_polarity = [row[0] for row in three_star_2018]
    three_star_subjectivity = [row[1] for row in three_star_2018]

    three_star = {
        "x": three_star_polarity,
        "y": three_star_subjectivity,
        "name": 'Three Star'
    }

    four_star_2018 = c.execute(
        "SELECT polarity, subjectivity FROM four_star_2018 limit 50000").fetchall()
    four_star_polarity = [row[0] for row in four_star_2018]
    four_star_subjectivity = [row[1] for row in four_star_2018]

    four_star = {
        "x": four_star_polarity,
        "y": four_star_subjectivity,
        "name": 'Four Star'
    }

    five_star_2018 = c.execute(
        "SELECT polarity, subjectivity FROM five_star_2018 limit 50000").fetchall()
    five_star_polarity = [row[0] for row in five_star_2018]
    five_star_subjectivity = [row[1] for row in five_star_2018]

    five_star = {
        "x": five_star_polarity,
        "y": five_star_subjectivity,
        "name": 'Five Star'
    }

    
    return render_template("sentiment.html", one_star=one_star, two_star=two_star, three_star=three_star, four_star=four_star, five_star=five_star)

@app.route("/wordclouds")
def wordcloud():
    return render_template("wordclouds.html")




# @app.route("/api/names")
# def names():
#     """Return a list of sample names."""

#     # Use Pandas to perform the sql query
#     stmt = db.session.query(Samples).statement
#     df = pd.read_sql_query(stmt, db.session.bind)

#     # Return a list of the column names (sample names)
#     return jsonify(list(df.columns)[2:])


# @app.route("/metadata/<sample>")
# def sample_metadata(sample):
#     """Return the MetaData for a given sample."""
#     sel = [
#         Samples_Metadata.sample,
#         Samples_Metadata.ETHNICITY,
#         Samples_Metadata.GENDER,
#         Samples_Metadata.AGE,
#         Samples_Metadata.LOCATION,
#         Samples_Metadata.BBTYPE,
#         Samples_Metadata.WFREQ,
#     ]

#     results = db.session.query(*sel).filter(Samples_Metadata.sample == sample).all()

#     # Create a dictionary entry for each row of metadata information
#     sample_metadata = {}
#     for result in results:
#         sample_metadata["sample"] = result[0]
#         sample_metadata["ETHNICITY"] = result[1]
#         sample_metadata["GENDER"] = result[2]
#         sample_metadata["AGE"] = result[3]
#         sample_metadata["LOCATION"] = result[4]
#         sample_metadata["BBTYPE"] = result[5]
#         sample_metadata["WFREQ"] = result[6]

#     print(sample_metadata)
#     return jsonify(sample_metadata)


# @app.route("/samples/<sample>")
# def samples(sample):
#     """Return `otu_ids`, `otu_labels`,and `sample_values`."""
#     stmt = db.session.query(Samples).statement
#     df = pd.read_sql_query(stmt, db.session.bind)

#     # Filter the data based on the sample number and
#     # only keep rows with values above 1
#     sample_data = df.loc[df[sample] > 1, ["otu_id", "otu_label", sample]]
#     # Format the data to send as json
#     data = {
#         "otu_ids": sample_data.otu_id.values.tolist(),
#         "sample_values": sample_data[sample].values.tolist(),
#         "otu_labels": sample_data.otu_label.tolist(),
#     }
#     return jsonify(data)


if __name__ == "__main__":
    app.run(debug=True)
