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


SalesDataFull = pd.ExcelFile("SalesDataFull.xlsx")
conn = sqlite3.connect('OS_employee.db')
OrdersOnlyData = SalesDataFull.parse("Orders")
df_quarter = OrdersOnlyData["Order Date"].dt.quarter
OrdersOnlyData["Quarter"] = df_quarter

def checkChoice():
    print("Welcome to Office Solutions")
    choice = input("Existing user?")
    while choice != 'no' and choice != 'yes' and choice != "x":
        choice = input("Error. Please enter yes/no or x to Exit: ")
    if choice == "yes":
        login()
    elif choice == "no":
        register()
    elif choice == "x":
        print("Thank you for visiting Office Solutions.")
        exit()

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 emailCheck():
    Email = input("Email: ")
    while Email:
            try:
                v = validate_email(Email)
                break
            except EmailNotValidError:
                print("Error")
                Email = input("Email: ")
    #check email duplication
    with conn:
        cur = conn.cursor()
        try:
            cur.execute("SELECT COUNT (*) FROM Employee WHERE(Email = '" + Email + "')")
            results = cur.fetchone()
            while results[0] == 1:
                Email = input("Email existed. Please enter again: ")
                cur.execute("SELECT COUNT (*) FROM Employee WHERE(Email = '" + Email + "')")
                results = cur.fetchone()
            return Email
        except:
            print("Connected Failed")

def EmployeeID_DuplicationCheck():
    with conn:
        cur = conn.cursor()
        try:
            EmployeeID = input("Enter New Employee ID: ")
            while EmployeeID == "":
                EmployeeID = input("What's your Employee ID:")
            # check EmployeeID duplication
            cur.execute("SELECT COUNT (*) FROM Employee WHERE(EmployeeID = '" + EmployeeID + "')")
            results = cur.fetchone()
            while results[0] == 1:
                EmployeeID = input("EmployeeID existed. Please enter again: ")
                cur.execute("SELECT COUNT (*) FROM Employee WHERE(EmployeeID = '" + EmployeeID + "')")
                results = cur.fetchone()
            return EmployeeID
        except sqlite3.Error as e:
            print(e)

def register():
    with conn:
        cur = conn.cursor()
        try:
            EmployeeID = EmployeeID_DuplicationCheck()

            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 = emailCheck()

            password = input("Enter a password: ")
            while password == "":
                password = input("Enter a password: ")
            cur.execute("""
                        INSERT INTO Employee (EmployeeID, FirstName, LastName,Email, Password) VALUES (?, ?, ?, ?, ?)""",
                        (EmployeeID, FirstName, LastName, email, password))
            cur.execute('select * from employee where EmployeeId=?', (EmployeeID,))
            results = cur.fetchall()
            print(results)
            # After registering, user is sent back to the main page.
            print("You have been successfully registered!\n")
        except:
            print("Connected Failed")


def totalProfit_month():
    #total profit by month
    ProfitDataMonth = OrdersOnlyData
    ProfitDataMonth["Profit"] = ProfitDataMonth["Profit"].sum()
    ProfitDataMonth["Month"] = ProfitDataMonth["Order Date"].dt.month
    MonthlyProfit = ProfitDataMonth[["Month", "Profit"]]
    MonthlyProfitSum = MonthlyProfit.groupby(by="Month").sum()
    MonthlyProfitSum['Profit'] = MonthlyProfitSum['Profit'].map("{:.2f}".format)
    MonthlyProfitSum['Profit'] = pd.np.where(MonthlyProfitSum['Profit'].astype(float)
                                             < 0, '-$' + MonthlyProfitSum['Profit'].astype(str).str[1:],
                                             '$' + MonthlyProfitSum['Profit'].astype(str))
    print(MonthlyProfitSum)
    MonthlyProfitSum = MonthlyProfitSum.reset_index()
    #barchart
    barchart_mostProfitable_month = sns.barplot(x="Month", y="Profit", data=MonthlyProfit[["Month", "Profit"]])
    barchart_mostProfitable_month.set_title("Sales by Profit")

    #piechart
    # labels = ProfitDataMonth
    # sizes = [ProfitDataMonth[["Month", "Profit"]]]
    #
    # fig1, ax1 = plt.subplots()
    # ax1.pie(sizes, labels=labels,
    #         shadow=True, startangle=90)
    # ax1.axis('equal')
    plt.show()

def Overallprofit_region():
    OrdersOnlyData = SalesDataFull.parse("Orders")
    profit_region = OrdersOnlyData[["Region", "Profit"]]
    overall_prof = profit_region.groupby(by="Region").sum().sort_values(by="Profit", ascending=False)
    print("\nThese are the total profits for each region: ")
    print(overall_prof.head(4))

    prof_cols_sub = OrdersOnlyData[["Region", "Profit"]]
    prof_cols_sub.groupby("Region").sum().plot(kind="bar")
    plt.show()

#Data display part
#montly most profitable product
def mostProfitable_month():
    OrdersOnlyData = SalesDataFull.parse("Orders")
    df_month = OrdersOnlyData["Order Date"].dt.month
    OrdersOnlyData["Month"] = df_month
    month = int(input("Enter month (1-12): "))
    while not (1 <= month <= 12):
        print("Invalid Month")
        month = int(input("Enter month (1-12): "))
    month_profit_headers = OrdersOnlyData[['Month', 'Product Name', 'Profit']]
    month_profit_sum = month_profit_headers.groupby(['Product Name']).sum().sort_values(by='Profit', ascending=True).reset_index()
    print("The 10 least profitable products ")
    month_profit_sum['Profit'] = month_profit_sum['Profit'].map("{:.2f}".format)
    month_profit_sum['Profit'] = pd.np.where(month_profit_sum['Profit'].astype(float)
                                             < 0, '-$' + month_profit_sum['Profit'].astype(str).str[1:],
                                             '$' + month_profit_sum['Profit'].astype(str))
    print("10 least profitable product by month")
    print(month_profit_sum.tail(10).round(2))
    print(lines)


    # OrdersOnlyData = ProfitableData
    # OrdersOnlyData["Month"] = OrdersOnlyData["Order Date"].dt.month
    # month_profit_headers = OrdersOnlyData[["Product Name", "Profit"]]
    # month_profit_sum = month_profit_headers.groupby(by="Profit").sum().sort_values(by="Profit", ascending=False).head(10).round(2)
    # month_profit_sum = month_profit_sum.reset_index()
    # print("10 most profitable product by month")
    # print(month_profit_sum)
    # print("--------------")

#10 least profitable product
def leastProfitable_month():
    OrdersOnlyData = SalesDataFull.parse("Orders")
    df_month = OrdersOnlyData["Order Date"].dt.month
    OrdersOnlyData["Month"] = df_month
    month = int(input("Enter month (1-12): "))
    while not (1 <= month <= 12):
        print("Invalid Month")
        month = int(input("Enter month (1-12): "))
    month_profit_headers = OrdersOnlyData[['Month', 'Product Name', 'Profit']]
    month_profit_sum = month_profit_headers.groupby(['Product Name']).sum().sort_values(by='Profit', ascending=False).reset_index()
    print("The 10 least profitable products ")
    month_profit_sum['Profit'] = month_profit_sum['Profit'].map("{:.2f}".format)
    month_profit_sum['Profit'] = pd.np.where(month_profit_sum['Profit'].astype(float)
                                             < 0, '-$' + month_profit_sum['Profit'].astype(str).str[1:], '$' + month_profit_sum['Profit'].astype(str))
    print("10 least profitable product by month")
    print(month_profit_sum.tail(10).round(2))
    print(lines)


#def state():
def stateTopProfit():
    states = OrdersOnlyData.State.unique()
    for pos_state in states:
        state_select = OrdersOnlyData.loc[OrdersOnlyData["State"] == pos_state]
        state_prof = state_select[["State", "Product Name", "Profit"]]
        best_state = state_prof.groupby("Product Name").sum().sort_values(by="Profit", ascending=True).reset_index()
        print("\nBest Product(s) in {} ".format(pos_state))
        print("--------------------------------------------")
        print(best_state.head(5).round(2))

def stateLeastProfit():
    states = OrdersOnlyData.State.unique()
    for neg_state in states:
        state_select = OrdersOnlyData.loc[OrdersOnlyData["State"] == neg_state]
        state_prof = state_select[["State", "Product Name", "Profit"]]
        worse_state = state_prof.groupby("Product Name").sum().sort_values(by="Profit").reset_index()
        print("\nWorst Product(s) in {} ".format(neg_state))
        print("--------------------------------------------")
        print(worse_state.tail(5).round(2))


def MostProfitable_quarter():
    month_profit_headers = OrdersOnlyData[['Year', 'Month', 'Quarter', 'Product Name', 'Profit']]
    month_profit_sum = month_profit_headers.groupby('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): "))
        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]
        year['Profit'] = year['Profit'].map("{:.2f}".format)
        year['Profit'] = pd.np.where(year['Profit'].astype(float)
                                     < 0, '-$' + year['Profit'].astype(str).str[1:],
                                              '$' + year['Profit'].astype(str))
        # 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].head(10).round(2))

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

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

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


def leastProfitable_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): "))
        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]
        year['Profit'] = year['Profit'].map("{:.2f}".format)
        year['Profit'] = pd.np.where(year['Profit'].astype(float)
                                     < 0, '-$' + year['Profit'].astype(str).str[1:],
                                              '$' + year['Profit'].astype(str))
        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))


#def city():
MostProfitable_quarter()
leastProfitable_quarter()
# totalProfit_month()