import sqlite3
from flask import Flask, render_template, request
import plotly, plotly.graph_objs as go
import plotly.offline as py
from plotly.subplots import make_subplots
DB = 'check.sqlite'


def create_scents_list():
    '''
    Create a list with all scents in database for users to make choices on Flask page

    Parameters
    ----------
    None
    
    Returns
    -------
    scent_list: list
        A list with all scents in database
    '''
    scent_list = []
    query = 'SELECT Name FROM Scent'
    connection = sqlite3.connect(DB)
    cursor = connection.cursor()
    result = cursor.execute(query).fetchall()
    connection.close()
    for scent in result:
        scent_list.append(scent[0])
    return scent_list

def bar_plot(plot_list):
    '''
    Four barplots would be given to visualizes the searching results:

    A bar chart of the top-ten rating perfumes with this scent in descending order (based on ratings, generated by the 
    attribute Perfume.Rating)

    A bar chart of top-ten frequently-tested perfumes with this scent (based on how many
    people gave the rate, generated by the attribute Perfume.RatingCount)

    A bar chart of average rating of all perfumes with this scent, comparing with that of
    top-five rating scents

    A bar chart of rating count of all perfumes with this scent, comparing with that of top-five frequently-tested scents


    Parameters
    ----------
    plot_list: list
        A list containing all search results
    
    Returns
    -------
    None
    '''
    
    x1 = [plot_list[0][i][0] for i in range(len(plot_list[0]))]
    y1 = [plot_list[0][i][3] for i in range(len(plot_list[0]))]
    text_1 = []
    for i in range(len(plot_list[0])):
        text_1.append(f'Designer: {plot_list[0][i][2]}\nBrand: {plot_list[0][i][1]}\n RatingCount:{plot_list[0][i][4]}')
    bar_data_1 = go.Bar(x=x1, y=y1, text=text_1)

    x2 = [plot_list[1][i][0] for i in range(len(plot_list[1]))]
    y2 = [plot_list[1][i][4] for i in range(len(plot_list[1]))]
    text_2 = []
    for i in range(len(plot_list[0])):
        text_2.append(f'Designer: {plot_list[0][i][2]}\nBrand: {plot_list[0][i][1]}\n Rating:{plot_list[0][i][3]}')
    bar_data_2 = go.Bar(x=x2, y=y2, text=text_2)
    
    x3 = [plot_list[2][i][0] for i in range(len(plot_list[2]))]
    y3 = [plot_list[2][i][1] for i in range(len(plot_list[2]))]
    bar_data_3 = go.Bar(x=x3, y=y3)

    x4 = [plot_list[3][i][0] for i in range(len(plot_list[3]))]
    y4 = [plot_list[3][i][1] for i in range(len(plot_list[3]))]
    bar_data_4 = go.Bar(x=x4, y=y4)

    fig = make_subplots(rows=4,
                        cols=1,
                        subplot_titles=["Top-ten Rating Perfumes with this Scent", 
                                        "Top-ten Frequently-tested Perfumes with this Scent", 
                                        "AVG Rating of this Scent Comparing with that of Top-five Scents", 
                                        "SUM of Rating Count of this Scent Comparing with that of Top-five Scents"], 
                        )
    # fig = go.Figure(data=bar_data, layout=basic_layout)
    fig.append_trace(bar_data_1, 1, 1)
    fig.append_trace(bar_data_2, 2, 1)
    fig.append_trace(bar_data_3, 3, 1)
    fig.append_trace(bar_data_4, 4, 1)
    fig.update_layout(height=1900, width=900, title_text=f"About {plot_list[3][0][0].upper()}")
    py.plot(fig, filename='result_file.html')


def search_helper(scent):
    '''
    When the user chooses an option on Flask page and submits it, the backend searchs for perfume info
    in database and generate a list containing all search results.
    
    Parameters
    ----------
    scent: str
        The scent used for searching in the database
    
    Returns
    -------
    plot_list: list
        A list containing all search results
    '''
    query_1 = f'SELECT DISTINCT Perfume.Name, Brand.Name, Designer.Name, Perfume.Rating, Perfume.RatingCount \
        FROM Scent JOIN Perfume_Scent ON Scent.ScentId = Perfume_Scent.ScentId \
        JOIN Perfume ON Perfume_Scent.PerfumeId = Perfume.PerfumeId \
        JOIN Brand ON Brand.BrandId = Perfume.BrandId \
        JOIN Designer ON Designer.DesignerId = Perfume.DesignerId\
        WHERE Scent.Name="{scent}" ORDER BY Rating DESC LIMIT 10'
    query_2 = f'SELECT DISTINCT Perfume.Name, Brand.Name, Designer.Name, Perfume.Rating, Perfume.RatingCount \
        FROM Scent JOIN Perfume_Scent ON Scent.ScentId = Perfume_Scent.ScentId \
        JOIN Perfume ON Perfume_Scent.PerfumeId = Perfume.PerfumeId \
        JOIN Brand ON Brand.BrandId = Perfume.BrandId \
        JOIN Designer ON Designer.DesignerId = Perfume.DesignerId\
        WHERE Scent.Name="{scent}" ORDER BY RatingCount DESC LIMIT 10'
    query_3_1 = f'SELECT DISTINCT Scent.Name, AVG(Perfume.Rating) \
        FROM Scent JOIN Perfume_Scent ON Scent.ScentId = Perfume_Scent.ScentId \
        JOIN Perfume ON Perfume_Scent.PerfumeId = Perfume.PerfumeId \
        WHERE Scent.Name="{scent}"'
    # Perfume.Name, Perfume.Rating, Perfume.RatingCount, Scent.Name
    query_3_2 = f'SELECT DISTINCT Scent.Name, AVG(Perfume.Rating) \
        FROM Scent JOIN Perfume_Scent ON Scent.ScentId = Perfume_Scent.ScentId \
        JOIN Perfume ON Perfume_Scent.PerfumeId = Perfume.PerfumeId \
        GROUP BY Scent.Name ORDER BY AVG(Perfume.Rating) DESC LIMIT 5'
    query_4_1 = f'SELECT DISTINCT Scent.Name, SUM(Perfume.RatingCount) \
        FROM Scent JOIN Perfume_Scent ON Scent.ScentId = Perfume_Scent.ScentId \
        JOIN Perfume ON Perfume_Scent.PerfumeId = Perfume.PerfumeId \
        WHERE Scent.Name="{scent}"'
    query_4_2 = f'SELECT DISTINCT Scent.Name, SUM(Perfume.RatingCount) \
        FROM Scent JOIN Perfume_Scent ON Scent.ScentId = Perfume_Scent.ScentId \
        JOIN Perfume ON Perfume_Scent.PerfumeId = Perfume.PerfumeId \
        GROUP BY Scent.Name ORDER BY SUM(RatingCount) DESC LIMIT 5'
    connection = sqlite3.connect(DB)
    cursor = connection.cursor()
    result_1 = cursor.execute(query_1).fetchall()
    result_2 = cursor.execute(query_2).fetchall()
    result_3_1 = cursor.execute(query_3_1).fetchall()
    result_3_2 = cursor.execute(query_3_2).fetchall()
    result_3_3 = [result_3_2[i][0] for i in range(len(result_3_2))]
    if result_3_1[0][0] in result_3_3:
        result_3 = result_3_2
    else:
        result_3 = result_3_1 + result_3_2
    result_4_1 = cursor.execute(query_4_1).fetchall()
    result_4_2 = cursor.execute(query_4_2).fetchall()
    result_4_3 = [result_4_2[i][0] for i in range(len(result_4_2))]
    if result_4_1[0][0] in result_4_3:
        result_4 = result_4_2
    else:
        result_4 = result_4_1 + result_4_2
    connection.close()
    bar_plot([result_1, result_2, result_3, result_4])


app = Flask(__name__)
@app.route('/')
def index(): 
    scents_list = create_scents_list()
    html_1 = f'''
    <form action="/handle_form" method="POST">        
    <p>
        Select a scent: <br>'''
    html_2 = ''
    for scent in scents_list:
        line = f'<input type="radio" name="scent" value="{scent}">{scent}<br/>'
        html_2 = html_2 + line
    html_3 = '''
    <input type="submit" value="Submit Form"/>
    </form> 
    </p>
    '''    
    return html_1 + html_2 + html_3

@app.route('/handle_form', methods=['POST'])
def handle_the_form():
    scent = request.form["scent"]
    search_helper(scent)

    html = f'''
    <h1>Thank You!</h1>
    <p>  
        The result of {scent} is shown in result_file.html
    </p>  
    <p>        
        Return <a href='/'>home</a>.        
    </p>  
    '''
    return html
    

if __name__ == '__main__':      
    print('starting Flask app', app.name)      
    app.run(debug=True)
    
    
