#!/usr/bin/env python

import os
import codecs
import csv
import locale
import logging
import operator
import shutil
import xlsxwriter

__author__ = 'Holger Knust'


def read_ean_lookup(ref_path):
    ref_dict = dict()
    exceptions_set = set()

    header_read = False

    ean_index = -1
    sku_index = -1
    item_prod_index = -1
    purchase_price_index = -1

    full_ref_path = os.path.join(ref_path, "Referenz_Matching_Abrechnung.csv")

    with codecs.open(full_ref_path, "rb", encoding='utf-8') as f:
        reader = csv.reader(f, delimiter=';')
        for row in reader:
            if header_read:
                ean = row[ean_index]
                sku = row[sku_index]
                item_prod = row[item_prod_index]
                purchase_price = row[purchase_price_index]

                if len(ean) > 0 and not ean in ref_dict:
                    ref_dict[ean] = [item_prod, purchase_price]

                if len(sku) > 0 and not sku in ref_dict:
                    ref_dict[sku] = [item_prod, purchase_price]

                if len(item_prod) == 0:
                    exceptions_set.add(ean)
            else:
                ean_index = row.index("EAN")
                sku_index = row.index("VariationNo")
                item_prod_index = row.index("ItemProducer")
                purchase_price_index = row.index("PurchasePrice")

                header_read = True

    return ref_dict, exceptions_set


def write_excel_summary(out_file, summary_dict, exchange_rate, exceptions_set):
    workbook = xlsxwriter.Workbook(out_file)
    worksheet = workbook.add_worksheet("Summary")

    worksheet.set_column('A:A', 25)
    worksheet.set_column('B:B', 20)
    worksheet.set_column('C:C', 25)
    worksheet.set_column('D:D', 10)
    worksheet.set_column('E:E', 10)

    format_currency = workbook.add_format()
    format_currency.set_num_format('#,##0.00')

    format_exchange_rate = workbook.add_format()
    format_exchange_rate.set_num_format('#,##0.00000')

    format_bold = workbook.add_format({'bold': 1})

    format_italic = workbook.add_format()
    format_italic.set_italic()

    format_currency_bold = workbook.add_format()
    format_currency_bold.set_num_format('#,##0.00')
    format_currency_bold.set_bold()

    current_row = 0

    worksheet.write(current_row, 0, 'Externe', format_bold)

    current_row += 1

    worksheet.write(current_row, 0, 'transaction-type', format_italic)
    worksheet.write(current_row, 1, 'amount-type', format_italic)
    worksheet.write(current_row, 2, 'amount-description', format_italic)
    worksheet.write(current_row, 3, 'total', format_italic)

    current_row += 1

    last_tx_type = ''
    last_amt_type = ''
    sub_total = 0.0
    unmatched_total = 0.0
    matched_total = 0.0

    unmatched_total_cell = None
    matched_total_cell = None
    refund_total_cell = None
    order_fees_total_cell = None
    refund_fees_total_cell = None

    if 'unmatched_summary' in summary_dict:

        summary_groups = summary_dict['unmatched_summary']
        for grp in summary_groups:
            group_keys = grp[0].split('~')

            if len(group_keys) == 3:
                if group_keys[0] != last_tx_type or group_keys[1] != last_amt_type:
                    if last_tx_type == 'Order' and last_amt_type == 'ItemPrice':
                        unmatched_total_cell = xlsxwriter.utility.xl_rowcol_to_cell(current_row - 1, 4)

                    last_tx_type = group_keys[0]
                    last_amt_type = group_keys[1]

                    if abs(sub_total) > 0.001:
                        # Write sub group total
                        worksheet.write_number(current_row - 1, 4, sub_total, format_currency)
                        sub_total = 0.0
                        current_row += 1

                worksheet.write_string(current_row, 0, group_keys[0])
                worksheet.write_string(current_row, 1, group_keys[1])
                worksheet.write_string(current_row, 2, group_keys[2])

                # Write group amount
                amount = round(grp[1], 2) * exchange_rate
                worksheet.write_number(current_row, 3, amount, format_currency)
                sub_total += amount
                unmatched_total += amount

                current_row += 1

        if abs(sub_total) > 0.001:
            # Write sub group total
            worksheet.write_number(current_row - 1, 4, sub_total, format_currency)

    worksheet.write_string(current_row, 2, 'Externe Total', format_bold)
    worksheet.write_number(current_row, 3, unmatched_total, format_currency_bold)

    current_row += 2

    worksheet.write(current_row, 0, 'SV24', format_bold)

    current_row += 1

    worksheet.write(current_row, 0, 'transaction-type', format_italic)
    worksheet.write(current_row, 1, 'amount-type', format_italic)
    worksheet.write(current_row, 2, 'amount-description', format_italic)
    worksheet.write(current_row, 3, 'total', format_italic)

    current_row += 1

    last_tx_type = ''
    last_amt_type = ''
    sub_total = 0.0

    if 'matched_summary' in summary_dict:
        summary_groups = summary_dict['matched_summary']
        for grp in summary_groups:
            group_keys = grp[0].split('~')

            if len(group_keys) == 3:
                if group_keys[0] != last_tx_type or group_keys[1] != last_amt_type:
                    if last_tx_type == 'Order' and last_amt_type == 'ItemPrice':
                        matched_total_cell = xlsxwriter.utility.xl_rowcol_to_cell(current_row - 1, 4)

                    if last_tx_type == 'Refund' and last_amt_type == 'ItemPrice':
                        refund_total_cell = xlsxwriter.utility.xl_rowcol_to_cell(current_row - 1, 4)

                    if last_tx_type == 'Order' and last_amt_type == 'ItemFees':
                        #order_fees_total_cell = xlsxwriter.utility.xl_rowcol_to_cell(current_row - 1, 4)
                        order_fees_total = sub_total

                    if last_tx_type == 'Refund' and last_amt_type == 'ItemFees':
                        refund_fees_total_cell = xlsxwriter.utility.xl_rowcol_to_cell(current_row - 1, 5)
                        refund_fees_total = sub_total
                        worksheet.write_number(current_row - 1, 5, order_fees_total + refund_fees_total, format_currency)


                    last_tx_type = group_keys[0]
                    last_amt_type = group_keys[1]

                    if abs(sub_total) > 0.001:
                        # Write sub group total
                        worksheet.write_number(current_row - 1, 4, sub_total, format_currency)
                        sub_total = 0.0
                        current_row += 1

                worksheet.write_string(current_row, 0, group_keys[0])
                worksheet.write_string(current_row, 1, group_keys[1])
                worksheet.write_string(current_row, 2, group_keys[2])

                # Write group amount
                amount = round(grp[1], 2) * exchange_rate
                worksheet.write_number(current_row, 3, amount, format_currency)
                sub_total += amount
                matched_total += amount

                current_row += 1

        if abs(sub_total) > 0.001:
            if last_tx_type == 'Refund' and last_amt_type == 'ItemPrice':
                refund_total_cell = xlsxwriter.utility.xl_rowcol_to_cell(current_row - 1, 4)

            # Write sub group total
            worksheet.write_number(current_row - 1, 4, sub_total, format_currency)

        worksheet.write_string(current_row, 2, 'SV24 Total', format_bold)
        worksheet.write_number(current_row, 3, matched_total, format_currency_bold)

        current_row += 2
        worksheet.write_string(current_row, 0, 'total-amount', format_bold)
        worksheet.write_number(current_row, 3, summary_dict['total'] * exchange_rate, format_currency)
        current_row += 1
        worksheet.write_string(current_row, 0, 'Kontrolle', format_bold)
        worksheet.write_number(current_row, 3, matched_total + unmatched_total, format_currency)

        if matched_total_cell and refund_total_cell:
            current_row += 2
            worksheet.write_string(current_row, 0, 'Provision %')
            worksheet.write_number(current_row, 3, 0.029)
            percent_cell = xlsxwriter.utility.xl_rowcol_to_cell(current_row, 3)

            current_row += 1
            worksheet.write_string(current_row, 0, 'Provision', format_bold)

            formula_expr = "=({0}+{1})*{2}".format(matched_total_cell, refund_total_cell, percent_cell)
            worksheet.write_formula(current_row, 3, formula_expr, format_currency)

            current_row += 2
            worksheet.write_string(current_row, 0, 'Base Currency')
            worksheet.write_string(current_row, 3, summary_dict['currency'])

            current_row += 1
            worksheet.write_string(current_row, 0, 'Exchange Rate')
            worksheet.write_number(current_row, 3, exchange_rate, format_exchange_rate)

    # Show Details
    header = summary_dict['header']
    details = summary_dict['matched_details']

    if len(details) > 0:
        worksheet = workbook.add_worksheet("SV24 Details")
        current_row = 0
        current_col = 0

        for col in header:
            worksheet.write_string(current_row, current_col, col)
            current_col += 1

        current_row += 1

        for row in details:
            current_col = 0

            for col in row:
                worksheet.write_string(current_row, current_col, col)
                current_col += 1

            current_row += 1

    details = summary_dict['unmatched_details']

    if len(details) > 0:
        worksheet = workbook.add_worksheet("Externe Details")
        current_row = 0
        current_col = 0

        for col in header:
            worksheet.write_string(current_row, current_col, col)
            current_col += 1

        current_row += 1

        for row in details:
            current_col = 0

            for col in row:
                worksheet.write_string(current_row, current_col, col)
                current_col += 1

            current_row += 1

    if len(exceptions_set) > 0:
        worksheet = workbook.add_worksheet("Fehler")

        current_row = 0
        current_col = 0

        worksheet.write(current_row, current_col, "EAN")
        current_row += 1

        for exception in exceptions_set:
            worksheet.write_string(current_row, current_col, exception)
            current_col += 1

    workbook.close()

def get_ecb_exchange_rate(symbol):
    import requests
    from xml.etree import ElementTree
    import locale
    '''
    Retrieve exchange rates of foreign currency against the Euro from the ECB

    Based on code from
    http://stackoverflow.com/questions/17250660/how-to-parse-xml-file-from-european-central-bank-with-python

    :param symbol: Currency symbol, e.g. GBP
    :return: Exchange rate
    '''

    rate = 1.0

    loc = locale.getlocale()
    # getlocale() may return (None,None) if not set before.
    # Workaround by setting locale to default before changing it.
    if not loc[0]:
        # Set default locale
        locale.setlocale(locale.LC_ALL, '')
        loc = locale.getlocale()

    locale.setlocale(locale.LC_ALL, 'en_US.utf8')

    try:
        r = requests.get('http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml', stream=True)
        tree = ElementTree.parse(r.raw)
        root = tree.getroot()
        namespaces = {'ex': 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref'}
        for cube in root.findall('.//ex:Cube[@currency]', namespaces=namespaces):
            if cube.attrib['currency'] == symbol:
                # Exchange rate format is in x.xxxxx so use US number format.
                rate = locale.atof(cube.attrib['rate'])
                break
    except Exception as e:
        logging.error(e)
    finally:
        if loc[0]:
            locale.setlocale(locale.LC_ALL, loc)

    return 1.0 / rate

def summarize_invoice(in_file, ean_ref):
    header_read = False
    header_row = None

    total_amount_index = -1
    amount_type_index = -1
    amount_desc_index = -1
    amount_index = -1
    tx_type_index = -1
    sku_index = -1
    currency_index = -1

    # Currency and locale defaults
    currency = 'EUR'
    locale_string = 'de_DE.utf8'
    locale.setlocale(locale.LC_ALL, locale_string)

    total_amount = 0.0

    matched_items = dict()
    unmatched_items = dict()

    matched_details = list()
    unmatched_details = list()

    # extra_cols = ['', '']

    with codecs.open(in_file, "r", encoding='utf-8') as f:
        reader = csv.reader(f, delimiter='\t')

        for row in reader:
            if any(row):
                if header_read:
                    if len(row[currency_index]) > 0:
                        currency = row[currency_index].upper()

                        if currency == 'GBP':
                            locale_string = 'en_US.utf8'
                        else:
                            locale_string = 'de_DE.utf8'

                        locale.setlocale(locale.LC_ALL, locale_string)

                    if len(row[total_amount_index]) > 0:
                        total_amount += locale.atof(row[total_amount_index])

                    amount_description = row[amount_desc_index]
                    amount_type = row[amount_type_index]
                    tx_type = row[tx_type_index]
                    sku = row[sku_index]

                    # Remove appendixes
                    if sku[-4:].lower() == "_fba":
                        sku = sku[:-4]

                    if sku in ean_ref:
                        is_match = True
                        extra_cols = ean_ref[sku]
                    else:
                        is_match = False
                        extra_cols = ['', '']

                    if len(row[amount_index]) > 0:
                        amount = locale.atof(row[amount_index])
                    else:
                        amount = 0.0

                    if len(amount_description) > 0:
                        combo_key = "{0}~{1}~{2}".format(tx_type, amount_type, amount_description)

                        if is_match:
                            if combo_key in matched_items:
                                matched_items[combo_key] += amount
                            else:
                                matched_items[combo_key] = amount

                            row = row + extra_cols
                            matched_details.append(row)
                        else:
                            if combo_key in unmatched_items:
                                unmatched_items[combo_key] += amount
                            else:
                                unmatched_items[combo_key] = amount
                            row = row + extra_cols
                            unmatched_details.append(row)
                else:
                    header_read = True

                    row.append("ItemProducer")
                    row.append("PurchasePrice")
                    header_row = row

                    total_amount_index = row.index('total-amount')
                    amount_type_index = row.index('amount-type')
                    amount_desc_index = row.index('amount-description')
                    amount_index = row.index('amount')
                    tx_type_index = row.index('transaction-type')
                    sku_index = row.index('sku')
                    currency_index = row.index('currency')

    summary_dict = dict()

    summary_dict['header'] = header_row
    summary_dict['total'] = round(total_amount, 2)
    summary_dict['matched_summary'] = sorted(matched_items.items(), key=operator.itemgetter(0))
    summary_dict['unmatched_summary'] = sorted(unmatched_items.items(), key=operator.itemgetter(0))
    summary_dict['matched_details'] = matched_details
    summary_dict['unmatched_details'] = unmatched_details
    summary_dict['currency'] = currency

    return summary_dict

def process_files(data_root):
    path_in = os.path.join(data_root, "in")
    path_out = os.path.join(data_root, "out")
    path_prc = os.path.join(data_root, "processed")
    path_err = os.path.join(data_root, "error")
    path_log = os.path.join(data_root, "log")
    path_ref = os.path.join(data_root, "ref")

    files = os.listdir(path_in)
    num_files = len(files)

    print("Found {0} files...".format(num_files))

    if num_files == 0:
        return

    ean_ref, exceptions_ref = read_ean_lookup(path_ref)

    for f in files:
        log_file_name = os.path.splitext(f)[0] + '.log'
        summary_file_name = os.path.splitext(f)[0] + '.xlsx'

        in_file = os.path.join(path_in, f)
        out_file = os.path.join(path_out, summary_file_name)
        prc_file = os.path.join(path_prc, f)
        err_file = os.path.join(path_err, f)
        log_file = os.path.join(path_log, log_file_name)

        if os.path.isfile(in_file):
            logging.basicConfig(filename=log_file, level=logging.DEBUG)

            logging.debug("Summarizing Amazon Invoice {0}...".format(in_file))
            try:
                summary_dict = summarize_invoice(in_file, ean_ref)

                if summary_dict['currency'] == 'EUR':
                    exchange_rate = 1.0
                else:
                    exchange_rate = get_ecb_exchange_rate(summary_dict['currency'])

                write_excel_summary(out_file, summary_dict, exchange_rate, exceptions_ref)
                shutil.move(in_file, prc_file)
            except Exception as e:
                logging.error(e)
                shutil.move(in_file, err_file)


def main():
    data_root = r"/ftproot/"
    #data_root = r"/data/projects/pycharm/thelen/conversion/data/"
    #data_root = r"c:/temp/thelen/"

    invoice_data_root = os.path.join(data_root, "Matching_Abrechnung")
    process_files(invoice_data_root)

if __name__ == "__main__":
    main()