#!/usr/bin/env python3
""" Conver files in Intrastat """

#pylint: disable=missing-function-docstring,invalid-name,consider-using-dict-items,broad-except,line-too-long

import codecs
import csv
import locale
import logging
import os
import shutil
import time
from dateutil import parser

COL_NAME_BARCODE_EAN = 'code'
COL_NAME_BARCODE_VAR_ID = 'variationid'

COL_NAME_VARIATION_VAR_ID = 'variationid'
COL_NAME_VARIATION_ITEM_ID = 'itemid'

COL_NAME_TARIFF_ITEM_ID = 'ItemID'
COL_NAME_TARIFF_NUM = 'ItemCustomsTariffNumber'

THRESHOLD = 3

def read_tax_ref(ref_path):
    ref_dict = dict()

    with codecs.open(os.path.join(ref_path, "Ref_Ust_Id.csv"), "rb", encoding='utf-8') as f:
        reader = csv.reader(f, delimiter=';')
        for row in reader:
            if len(row) < 2:
                msg = "Expected two columns in Ref_Ust_Id.csv"
                logging.warning(msg)

            country_code = row[0]
            tax_num = row[1]

            if len(country_code) == 0:
                continue

            if country_code in ref_dict:
                logmsg = f"Found duplicate country {country_code}"
                logging.warning(logmsg)

            ref_dict[country_code] = tax_num

    return ref_dict


def read_ceiling_ref(ref_path):
    ref_dict = dict()
    header_read = False
    country_index = -1
    period_index = -1
    ceiling_amt_index = -1


    with codecs.open(os.path.join(ref_path, "Referenz_Matching_Betraege.csv"), "rb", encoding='utf-8') as f:
        reader = csv.reader(f, delimiter=';')
        for row in reader:
            if header_read:
                country = row[country_index]
                period = row[period_index]
                amount = int(row[ceiling_amt_index])
                if country in ref_dict:
                    if period in ref_dict[country]:
                        msg = f"Found duplicate period {period} for country {country}"
                        logging.info(msg)
                    else:
                        ref_dict[country][period] = amount
                else:
                    ref_dict[country] = {period: amount}
            else:
                # Land;Betrag;Periode
                lc_row = [x.lower() for x in row]
                if "land" in lc_row:
                    country_index = lc_row.index("land")
                if "betrag" in lc_row:
                    ceiling_amt_index = lc_row.index("betrag")
                if "periode" in lc_row:
                    period_index = lc_row.index("periode")

                header_read = True

    return ref_dict


def read_country_ref(ref_path):
    ref_dict = dict()


    code_number_index = -1
    origin_country_index = -1
    int_code_index = -1

    header_read = False


    with codecs.open(os.path.join(ref_path, "Ref_Lieferländer.csv"), "rb", encoding='utf-8') as f:
        reader = csv.reader(f, delimiter=';')
        for row in reader:
            # print(row)
            if header_read:
                # code_number = row[code_number_index]
                origin_country = row[origin_country_index]
                int_code = row[int_code_index]

                ref_dict[origin_country] = int_code
            else:
                if 'Code' in row:
                    code_number_index = row.index('Code')
                if 'Lieferland' in row:
                    origin_country_index = row.index('Lieferland')
                if 'INT - Code' in row:
                    int_code_index = row.index('INT - Code')

                if code_number_index < 0:
                    raise Exception('Missing Code column in Ref_Lieferländer.csv')

                header_read = True

    return ref_dict


def read_gender_ref(ref_path):
    ref_dict = dict()


    gender_index = -1
    # age_index = -1
    free_text_index = -1

    header_read = False


    with codecs.open(os.path.join(ref_path, "Ref_Gender_Age.csv"), "rb", encoding='utf-8') as f:
        reader = csv.reader(f, delimiter=';')
        for row in reader:
            # print(row)
            if header_read:
                gender = row[gender_index]
                # age = row[age_index]
                # free_text = row[free_text_index]

                ref_dict[gender] = free_text_index
            else:
                if 'Gender' in row:
                    gender_index = row.index('Gender')
                # if 'Age' in row:
                #     age_index = row.index('Age')
                if 'ItemFreeTextField6' in row:
                    free_text_index = row.index('ItemFreeTextField6')

                if gender_index < 0:
                    raise Exception('Missing Gender column in Ref_Gender_Age.csv')

                header_read = True

    return ref_dict


def read_customs_ref(ref_path):
    ref_dict = dict()

    header_read = False

    ref_filename = "Ref_Intrastat.csv"

    ean_index = -1
    tariff_nunber_index = -1

    ean_colname = "EAN"
    tariff_colname = "customsTariffNumber"

    with codecs.open(os.path.join(ref_path, ref_filename), "rb", encoding='utf-8') as f:
        reader = csv.reader(f, delimiter=';')

        for row in reader:
            if header_read:
                ean = row[ean_index]
                tariff_number = row[tariff_nunber_index]
                ref_dict[ean] = tariff_number
            else:
                if ean_colname in row:
                    ean_index = row.index(ean_colname)
                if tariff_colname in row:
                    tariff_nunber_index = row.index(tariff_colname)

                if ean_index < 0:
                    raise Exception(f"Missing column {ean_colname} in {ref_filename}")

                if tariff_nunber_index < 0:
                    raise Exception(f"Missing column {tariff_colname} in {ref_filename}")

                header_read = True

    return ref_dict


def read_transactions(in_file, customs_ref_dict):
    tx_dict = dict()
    header_read = False
    ean_index = -1
    receive_date_index = -1
    return_date_index = -1
    item_desc_index = -1
    inv_amt_index = -1
    qty_index = -1

    with codecs.open(in_file, "rb", encoding='utf-8-sig') as f:
        reader = csv.reader(f, delimiter=';')

        for row in reader:
            if header_read:
                ean = row[ean_index]

                if not ean:
                    continue

                if not ean in customs_ref_dict.keys():
                    logging.warning("EAN '%s' has no match Ref_Intrastat! Skipped.",ean)
                    continue

                if row[receive_date_index]:
                    report_date = parser.parse(row[receive_date_index])
                else:
                    report_date = parser.parse(row[return_date_index])

                key = "{}-{}".format(report_date.year, str(report_date.month).zfill(2))

                invoice_amt_text = row[inv_amt_index].replace(".", "").replace(",", ".")
                item_desc = row[item_desc_index]
                qty = row[qty_index]

                try:
                    invoice_amount_num = int(locale.atof(invoice_amt_text))
                except ValueError:
                    msg = f"Failed to convert '{invoice_amt_text}' to a numeric value. Skipping."
                    logging.error(msg)
                    continue

                customs = customs_ref_dict[ean]

                if key not in tx_dict:
                    logging.info("Creating list for period '%s'", key)
                    tx_dict[key] = []

                period_list = tx_dict[key]
                if isinstance(period_list, list):
                    period_list.append([item_desc, customs, invoice_amount_num, qty, False])
                    tx_dict[key] = period_list
                else:
                    logging.info("Bad list for period %s : %s",key, period_list)
            else:
                header_read = True

                # Receive Date;Return Date;Invoice Day;Transaction Type;Quantity;Net Receipts;
                # Net Receipts Currency;List Price;List Price Currency;Rebate;Rebate Currency;
                # Purchase Order;Asin;UPC;EAN;Manufacturer;Distributor;Product Group;Category;
                # Subcategory;Title;Product Description;Binding

                if "EAN" in row:
                    ean_index = row.index("EAN")

                if "Receive Date" in row:
                    receive_date_index = row.index("Receive Date")

                if "Return Date" in row:
                    return_date_index = row.index("Return Date")

                if "Title" in row:
                    item_desc_index = row.index("Title")

#                if "Net Receipts" in row:
#                    inv_amt_index = row.index("Net Receipts")

                if "Cost" in row:
                    inv_amt_index = row.index("Cost")

                if "Quantity" in row:
                    qty_index = row.index("Quantity")

                if ean_index < 0:
                    raise Exception("Could not find EAN")
                if receive_date_index < 0:
                    raise Exception("Could not find receive date")
                if return_date_index < 0:
                    raise Exception("Could not find return date")
                if item_desc_index < 0:
                    raise Exception("Could not find item description")
                if inv_amt_index < 0:
                    raise Exception("Could not find invoice amount")
                if qty_index < 0:
                    raise Exception("Could not find quantity")

    return tx_dict


def clear_transactions(transactions):
    # Recycle the transactions
    for i, _ in enumerate(transactions):
        transactions[i][4] = False


def write_period_transactions(out_file, country_code, period, transactions, ceiling_amount, running_total, tax_num):
    deadweight_num = 0

    with codecs.open(out_file, "a", encoding='utf-8') as of:
        writer = csv.writer(of, delimiter=';')

        for i, tx in enumerate(transactions):
            item_desc, customs, inv_amt, qty, used = tx

            if used:
                continue

            if running_total + inv_amt < ceiling_amount:
                running_total += inv_amt

                # Mark as used
                transactions[i][4] = True

                if inv_amt > 0:
                    writer.writerow([period[:4], period[5:], country_code, customs,
                                    item_desc[:80], deadweight_num, qty, inv_amt, tax_num])

                if (ceiling_amount - running_total) <= THRESHOLD:
                    return running_total
    return running_total


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

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

    log_file_name = time.strftime("%Y%m%d-%H%M%S") + '.log'
    log_file = os.path.join(path_log, log_file_name)

    logging.basicConfig(filename=log_file, level=logging.DEBUG)

    msg = f"Found {num_files} files..."
    logging.info(msg)

    if num_files == 0:
        return

    try:
        logging.info("Reading Tax ref...")
        tax_ref_dict = read_tax_ref(path_ref)
        logging.info("Reading Customs ref...")
        customs_ref_dict = read_customs_ref(path_ref)
        logging.info("Reading Amount ref...")
        amt_ref_dict = read_ceiling_ref(path_ref)
    except Exception as ex:
        logging.info("Failed to read ref files: %s", ex)
        return

    for f in files:
        in_file = os.path.join(path_in, f)
        out_file_name = os.path.splitext(f)[0] + "-" + time.strftime("%Y%m%d%H%M%S") + ".csv"

        out_file = os.path. join(path_out, out_file_name)
        proc_file = os.path.join(path_proc, f)
        err_file = os.path.join(path_err, f)

        logging.debug("Matching item ids for %s...", format(in_file))

        if os.path.isfile(in_file):
            try:
                logging.info("Reading transactions from %s...",f)
                txs_dict = read_transactions(in_file, customs_ref_dict)
                for country in amt_ref_dict.keys():
                    logging.info("Processing '%s'",country)
                    running_total = 0.0
                    for period in txs_dict:
                        logging.info("Processing period %s",period)
                        if period not in amt_ref_dict[country]:
                            msg = f"No total available for period {period}"
                            logging.error(msg)
                            continue

                        logging.info("Writing txs for period '%s'",period)
                        amount = amt_ref_dict[country][period]

                        iteration = 0
                        while (amount - running_total) > THRESHOLD and iteration < 4:

                            running_total = write_period_transactions(out_file, country, period, txs_dict[period],
                                                                      amount, running_total, tax_ref_dict[country])
                            logging.info("After the %s iteration a total of %s",iteration, running_total)

                            iteration += 1

                            if iteration == 2:
                                logging.info("Clearing transactions flags...")
                                clear_transactions(txs_dict[period])
                        msg = f"{country} : Accounted for {running_total} of {amount} after {iteration} iterations"
                        logging.info(msg)

                shutil.move(in_file, proc_file)
            except Exception as e:
                logging.error(e)
                logging.info(e)
                shutil.move(in_file, err_file)
                if os.path.exists(out_file):
                    os.remove(out_file)


def main():
    if os.path.exists(r"/ftproot/"):
        data_root = r"/ftproot/"
    elif os.path.exists(r"/home/holger/git/bitbucket/thelen/data/"):
        data_root = r"/home/holger/git/bitbucket/thelen/data/"
    else:
        print("Unknown environment. Please set data_root to data folder location. Aborting.")
        return

    # data_root = r"/home/holger/git/bitbucket/thelen/data/"
    # data_root = r"/ftproot/"

    project_data_root = os.path.join(data_root, "Matching_Intrastat")

    process(project_data_root)

    logging.info("Done.")


if __name__ == "__main__":
    main()