# -*- coding: utf-8 -*-
"""
Simple Pandas program to combine Excel files and summarize data.
See http://pbpython.com/pandas-gui.html for details on this script
This demonstrates the use of Gooey to add a simple UI on top of the script
"""
from __future__ import print_function
import pandas as pd
import numpy as np
import glob
import os
import json
from gooey import Gooey, GooeyParser


@Gooey(program_name="Create Quarterly Marketing Report")
def parse_args():
    """ Use GooeyParser to build up the arguments we will use in our script
    Save the arguments in a default json file so that we can retrieve them
    every time we run the script.
    """
    stored_args = {}
    # get the script name without the extension & use it to build up
    # the json filename
    script_name = os.path.splitext(os.path.basename(__file__))[0]
    args_file = "{}-args.json".format(script_name)
    # Read in the prior arguments as a dictionary
    if os.path.isfile(args_file):
        with open(args_file) as data_file:
            stored_args = json.load(data_file)
    parser = GooeyParser(description='Create Quarterly Marketing Report')
    parser.add_argument('data_directory',
                        action='store',
                        default=stored_args.get('data_directory'),
                        widget='DirChooser',
                        help="Source directory that contains Excel files")
    parser.add_argument('output_directory',
                        action='store',
                        widget='DirChooser',
                        default=stored_args.get('output_directory'),
                        help="Output directory to save summary report")
    parser.add_argument('cust_file',
                        action='store',
                        default=stored_args.get('cust_file'),
                        widget='FileChooser',
                        help='Customer Account Status File')
    parser.add_argument('-d', help='Start date to include',
                        default=stored_args.get('d'),
                        widget='DateChooser')
    args = parser.parse_args()
    # Store the values of the arguments so we have them next time we run
    with open(args_file, 'w') as data_file:
        # Using vars(args) returns the data as a dictionary
        json.dump(vars(args), data_file)
    return args


def combine_files(src_directory):
    """ Read in all of the sales xlsx files and combine into 1
    combined DataFrame
    """
    all_data = pd.DataFrame()
    for f in glob.glob(os.path.join(src_directory, "sales-*.xlsx")):
        df = pd.read_excel(f)
        all_data = all_data.append(df, ignore_index=True)
    all_data['date'] = pd.to_datetime(all_data['date'])
    return all_data


def add_customer_status(sales_data, customer_file):
    """ Read in the customer file and combine with the sales data
    Return the customer with their status as an ordered category
    """
    df = pd.read_excel(customer_file)
    all_data = pd.merge(sales_data, df, how='left')
    # Default everyone to bronze if no data included
    all_data['status'].fillna('bronze', inplace=True)
    # Convert the status to a category and order it
    all_data["status"] = all_data["status"].astype("category")
    all_data["status"].cat.set_categories(["gold", "silver", "bronze"], inplace=True)
    return all_data


def save_results(sales_data, output):
    """ Perform a summary of the data and save the data as an excel file
    """
    summarized_sales = sales_data.groupby(["status"])["unit price"].agg([np.mean])
    output_file = os.path.join(output, "sales-report.xlsx")
    writer = pd.ExcelWriter(output_file, engine='xlsxwriter')
    summarized_sales = summarized_sales.reset_index()
    summarized_sales.to_excel(writer)


if __name__ == '__main__':
    conf = parse_args()
    print("Reading sales files")
    sales_df = combine_files(conf.data_directory)
    print("Reading customer data and combining with sales")
    customer_status_sales = add_customer_status(sales_df, conf.cust_file)
    print("Saving sales and customer summary data")
    save_results(customer_status_sales, conf.output_directory)
    print("Done")