import sqlite3
import re
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
from email_validator import validate_email, EmailNotValidError

pd.options.display.float_format = '${:,.2f}'.format

SalesDataFull = pd.ExcelFile("SalesDataFull.xlsx")


def EMCheck(self):
    Email = input("Email")
    if Email:
        try:
            v = validate_email(Email)
            self.dbConn()
        except EmailNotValidError as er:
            self.showMessage("Error", str(er))

def register():
    with conn:
        cur = conn.cursor()
        try:
            EmployeeID = int(input("Enter New Employee ID: "))
            while EmployeeID == "":
                EmployeeID = input("What's your Employee ID:")

            FirstName = input("What's your first name:")
            while FirstName == "":
                FirstName = input("What's your first name:")

            LastName = input("What's your last name: ")
            while LastName == "":
                LastName = input("What's your last name:")

            Email = input("Email: ")
            EMCheck(Email)

            Password = input("Enter a password: ")
            while Password == "":
                Password = input("Enter a password: ")
            Password = Password.lower()

            cur.execute(
                'insert into employee values(?,?,?,?,?)',
                (EmployeeID, FirstName, LastName, Email, Password))
            cur.execute(
                'select * from employee where EmployeeId=?',
                (EmployeeID,))

            results = cur.fetchall()
            print(results)
        except sqlite3.Error as e:
            print(e)
            print("User already exist")


def login():
    with conn:
        cur = conn.cursor()
        try:
            loginTest = False  # main condition to loop if email and password not met
            while not loginTest:  # wrong email loopy
                userEmail = input("Email please: ")
                userEmail = userEmail.lower().replace(" ", "")
                userPassword = input("Password: ").strip()
                cur.execute(
                    "SELECT COUNT (*) FROM Employee WHERE(Email= '" + userEmail.lower() + "' AND Password= '" + userPassword + "')")
                results = cur.fetchone()  # return very first thing it finds that matches
                print(results[0])  # print first thing
                if results[0] == 1:
                    print("Login successful")
                    loginTest = True
                else:
                    print("Login Unsuccessful")
                    existingUser = input("Existing user?[yes/no]")
                    if existingUser == "no":
                        register()
        except:
            print("connection failed")

def most():
    OrdersOnlyData = SalesDataFull.parse("Orders")

    df_year = OrdersOnlyData["Order Date"].dt.year
    OrdersOnlyData["Year"] = df_year

    # month_profit_headers = OrdersOnlyData[['Year', 'Month', 'Quarter', 'Product Name', 'Profit']]
    # month_profit_sum = month_profit_headers.groupby(by=['Product Name', 'Month', 'Quarter' ]).sum().sort_values(
    #   by='Profit', ascending=False).reset_index()

    year = int(input("Choose year (2014-2017): "))
    while not (2014 <= year <= 2017):
        print("Invalid input. Enter between 2014 to 2017")
        year = int(input("Enter year (2014-2017): "))

    choice = input("\nTo view product per month enter 1" + "To view product by quarter 2:")

    if choice == "1":
        df_month = OrdersOnlyData["Order Date"].dt.month
        OrdersOnlyData["Month"] = df_month
        month_profit_headers = OrdersOnlyData[["Product Name", "Profit"]]
        month_profit_sum = month_profit_headers.groupby(["Product Name"]).sum().sort_values(
            by="Profit", ascending=False)
        month_profit_sum = month_profit_sum.reset_index()
        month = int(input("Enter month (1-12): "))
        while not (1 <= month <= 12):
            print("Invalid entry, please try again")
            month = int(input("Enter month (1-12): "))
        # year = month_profit_sum.loc[month_profit_sum["Year"] == year]
        print("The 10 most profitable products ")
        data1 = month_profit_sum.head(10).round(2)
        print(data1)
        barchart2 = sns.barplot(x="Month", y="Profit", data=month_profit_sum)
        barchart2.set_title("Sales by Profit")
        plt.show()

        # sns.set_style("whitegrid")
        # ax1 = sns.barplot(x="Product Name", y="Profit", data=data1)
        # ax1.set_xticklabels(labels=month_profit_headers["Product Name"], rotation=90, size=10)
        # ax1.set_title("Sales by Month")
        # plt.show()
        # plt.figure(figsize=(6,6))
        # plt.pie(month_profit_sum.Month, labels=month_profit_sum.Profit, shadow=True, autopct='%1.1f%%')
        # plt.title("Profit per month")
        # plt.axis('equal')
        # plt.show()

        main_menu = input("Exit, enter 1. Main menu, enter 2: ")
        if main_menu == "1":
            print("Bye!")
            exit()
        if main_menu == "2":
            main()

    if choice == "2":
        df_quarter = OrdersOnlyData["Order Date"].dt.quarter
        OrdersOnlyData["Quarter"] = df_quarter
        month_profit_headers = OrdersOnlyData[['Year', 'Quarter', 'Product Name', 'Profit']]
        month_profit_sum = month_profit_headers.groupby(by=['Product Name', 'Quarter']).sum().sort_values(
            by='Profit', ascending=False).reset_index()
        quarter = int(input("Enter quarter (1, 2, 3, 4): "))

        while not (1 <= quarter <= 4):
            print("Invalid entry, please try again")
            quarter = int(input("Enter quarter: (1, 2, 3, 4"))
        year = month_profit_sum.loc[month_profit_sum['Year'] == year]
        print(year.loc[month_profit_sum['Quarter'] == quarter].head(10).round(2))

        if quarter == 1:
            print("The 10 most profitable products ")
            print(df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].head(10).round(2))

        elif quarter == 2:
            print("The 10 most profitable products ")
            print(df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].head(10).round(2))

        elif quarter == 3:
            print("The 10 most profitable products ")
            print(df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].head(10).round(2))

        elif quarter == 4:
            print("The 10 most profitable products ")
            print(df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].head(10).round(2))

        main_menu = input("Exit, enter 1. Main menu, enter 2: ")
        if main_menu == "1":
            print("Bye!")
            exit()
        if main_menu == "2":
            main()
    else:
        print("Invalid input only use integers 1 and 2")


def least():
    OrdersOnlyData = SalesDataFull.parse("Orders")

    df_month = OrdersOnlyData["Order Date"].dt.month
    OrdersOnlyData["Month"] = df_month

    df_year = OrdersOnlyData["Order Date"].dt.year
    OrdersOnlyData["Year"] = df_year

    df_quarter = OrdersOnlyData["Order Date"].dt.quarter
    OrdersOnlyData["Quarter"] = df_quarter

    month_profit_headers = OrdersOnlyData[['Year', 'Month', 'Quarter', 'Product Name', 'Profit']]
    month_profit_sum = month_profit_headers.groupby(['Product Name', 'Year', 'Month', 'Quarter']).sum().sort_values(
        by='Profit', ascending=False).reset_index()

    year = int(input("Enter year (2014-2017): "))

    while not (2014 <= year <= 2017):
        print("Invalid year. Please enter between 2014 to 2017")
        year = int(input("Enter year(2014-2017): "))

    choice = input("\nTo view product per month enter 1. To view product by quarter 2: ")

    if choice == "1":
        month = int(input("Enter month (1-12): "))
        while not (1 <= month <= 12):
            print("Invalid Month")
            month = int(input("Enter month (1-12): "))
        select_year = month_profit_sum.loc[month_profit_sum['Year'] == year]
        print("The 10 least profitable products ")
        print(select_year.loc[month_profit_sum['Month'] == month].tail(10).round(2))
        main_menu = input("Exit, enter 1. Main menu, enter 2: ")
        if main_menu == "1":
            print("Bye!")
            exit()
        if main_menu == "2":
            main()

    if choice == "2":
        quarter = int(input("Enter quarter (1, 2, 3, 4): "))

        while not (1 <= quarter <= 4):
            print("Invalid entry, please try again")
            quarter = int(input("Enter quarter: (1, 2, 3, 4"))
        year = month_profit_sum.loc[month_profit_sum['Year'] == year]
        print(year.loc[month_profit_sum['Quarter'] == quarter].tail(10).round(2))

        if quarter == 1:
            print("The 10 most profitable products ")
            print(df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].tail(10).round(2))

        elif quarter == "2":
            print("The 10 least profitable products ")
            print(df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].tail(10).round(2))

        elif quarter == "3":
            print("The 10 least profitable products ")
            print(df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].tail(10).round(2))

        elif quarter == "4":
            print("The 10 least profitable products ")
            print(df_quarter.loc[month_profit_sum['Quarter'] == df_quarter].tail(10).round(2))

        main_menu = input("Exit, enter 1. Main menu, enter 2: ")
        if main_menu == "1":
            print("Bye!")
            exit()
        if main_menu == "2":
            main()
    else:
        print("Invalid input only use integers 1 and 2")

def main():
    print("\nWelcome!\nTo view 10 most profitable products, enter 1. To view 10 least profitable products, enter 2: ")

    while True:
        try:
            userdecision = input("Enter a choice 1 or 2: ")
            if userdecision == "1":
                most()

            elif userdecision == "2":
                least()

            else:
                print("Invalid entry, enter 1 or 2")
                main()
        except ValueError:
            print("Please enter 1 or 2")
    exit

def state():
    OrdersOnlyData = SalesDataFull.parse("Orders")
    # line below is a variable, separates states by creating a line between each state's information
    lines = "=" * 25

    df_year = OrdersOnlyData["Order Date"].dt.year
    OrdersOnlyData["Year"] = df_year

    states = OrdersOnlyData.State.unique()
    # block below will present positive profit sorted by state, will display from 1-10 products, depending on how many
    # products are available
    state_decision = input("To view best products, enter 1. To view worse products, enter 2.\n")
    if state_decision == "1":
        for pos_state in states:
            state_select = OrdersOnlyData.loc[OrdersOnlyData["State"] == pos_state]
            state_prof = state_select[["Year", "State", "Product Name", "Profit"]]
            best_state = state_prof.groupby("Product Name").sum().sort_values(by="Profit",
                                                                              ascending=False).reset_index()
            print("\nBest Product(s) in {} ".format(pos_state))
            print(lines)
            print(best_state.head(5).round(2))
    # block below will present negative profit sorted by state, will display 1-10 products, depending on how many
    # products are available
    elif state_decision == "2":
        for neg_state in states:
            state_select = OrdersOnlyData.loc[OrdersOnlyData["State"] == neg_state]
            state_prof = state_select[["Year", "State", "Product Name", "Profit"]]
            worse_state = state_prof.groupby("Product Name").sum().sort_values(by="Profit").reset_index()
            print("\nBest Product(s) in {} ".format(neg_state))
            print(lines)
            print(worse_state.tail(5).round(2))

    # block below gives user the option to either exit program or return to main menu
    else:
        print("Invalid input, enter 1 or 2: ")
    menu = input("\nMain menu, enter 1. Exit, enter 2.\n ")
    if menu == "1":
        main()
    if menu == "2":
        print("\nBye!")
        exit()


# block will display profitable/negative products sorted by city
def city_prod():
    OrdersOnlyData = SalesDataFull.parse("Orders")
    # line below is a variable, separates states by creating a line between each state's information
    lines = "=" * 25
    df_year = OrdersOnlyData["Order Date"].dt.year
    OrdersOnlyData["Year"] = df_year

    cities = OrdersOnlyData.City.unique()

    # block below will present positive profit sorted by city, will display from 1-10 products, depending on how many
    # products are available
    city_decision = input("To view best products, enter 1. To view worse products, enter 2.\n")
    if city_decision == "1":
        for city in cities:
            cit_select = OrdersOnlyData.loc[OrdersOnlyData["City"] == city]
            cit_prof = cit_select[["Year", "City", "Product Name", "Profit"]]
            best_city = cit_prof.groupby("Product Name").sum().sort_values(by="Profit", ascending=False).reset_index()
            print("\nBest Product(s) in {} ".format(city))
            print(lines)
            print(best_city.head(5).round(2))

    if city_decision == "2":
        for city in cities:
            cit_select = OrdersOnlyData.loc[OrdersOnlyData["City"] == city]
            cit_neg = cit_select[["Year", "City", "Product Name", "Profit"]]
            nega_city = cit_neg.groupby("Product Name").sum().sort_values(by="Profit").reset_index()
            print("\nBest Product(s) in {} ".format(city))
            print(lines)
            print(nega_city.tail(5).round(2))

    # block below gives user the option to either exit program or return to main menu
    else:
        print("Invalid input, enter 1 or 2: ")
    menu = input("\nMain menu, enter 1. Exit, enter 2.\n ")
    if menu == "1":
        main()
    if menu == "2":
        print("\nBye!")
        exit()


# main menu, prompts user to choose between sorting products by state or city
def main2():
    print("\nWelcome!\nSort product by state, enter 1. Sort product by city, enter 2.")

    while True:
        try:
            option = input("Enter option (1 or 2): ")

            if option == "1":
                state()
            elif option == "2":
                city_prod()
            else:
                print("Invalid input, enter 1 or 2: ")

        # excepts value error if its an invalid input (e.g. a letter, or number that is not 1 or 2
        except ValueError:
            print("Please enter 1 or 2")
    exit


conn = sqlite3.connect('OS_employee.db')
with conn:
    cur = conn.cursor()
    print("successfully connected")

    existingUser = input("Existing user?[yes/no]")
    existingUser = existingUser.lower()
    while existingUser != "yes" or existingUser != "no":
        if existingUser == "no":
            register()
            proceed = input("proceed to log in?")
            if proceed == "yes":
                login()
                print("successfully log in")
                main()
                main2()
            break
        elif existingUser == "yes":
            login()
            print("successfully log in")
            main()
            main2()
            break
        else:
            input("Invalid input. Please answer [yes/no]")
            existingUser = input("Existing user?[yes/no]")