#!/usr/bin/env python3.7
import os
import shutil
import codecs
import csv
import logging
import logging.config

import xlrd
import openpyxl

import common

logger = None


def read_asn_files(xls_path):
    asn_dict = dict()
    content_dict = dict()
    xls_files = os.listdir(xls_path)
    for f in xls_files:
        _, extension = os.path.splitext(f)

        # we are only interested in XLSX files
        if extension.lower() != '.xlsx':
            continue

        full_name = os.path.join(xls_path, f)
        content_dict[full_name] = dict()
        try:
            wb = xlrd.open_workbook(full_name)
            sht = wb.sheet_by_index(0)
            for row_num in range(5, sht.nrows):
                row = sht.row_values(row_num)
                po_number = row[1]
                ean = row[2]
                if po_number not in asn_dict:
                    asn_dict[po_number] = full_name

                row_key = f"{po_number}-{ean}"
                if row_key in content_dict[full_name]:
                    raise Exception("Should now be in 2 excel files")
                content_dict[full_name][row_key] = row_num
        finally:
            wb.release_resources()
    return asn_dict, content_dict


def process_export_file(in_path, prc_path, asn_dict, content_dict):
    current_file = None
    dirty_file_list = list()
    header_read = False

    changes_dict = dict()

    in_files = os.listdir(in_path)
    if len(in_files) == 0:
        return []

    for f in in_files:
        _, extension = os.path.splitext(f)

        # we are only interested in CSV files
        if extension.lower() != '.csv':
            continue

        in_file = os.path.join(in_path, f)
        prc_file = os.path.join(prc_path, f)
        with codecs.open(in_file, "r", encoding='utf-8') as f:
            rdr = csv.reader(f, delimiter=';')
            for row in rdr:
                if header_read:
                    po_number = row[0]
                    ean =  '0' + row[1] if len(row[1]) == 12 else row[1]
                    qty = row[2]

                    if po_number in asn_dict:
                        current_file = asn_dict[po_number]
                        content = content_dict[current_file]
                        row_key = f"{po_number}-{ean}"
                        if row_key in content:
                            row = content[row_key]
                            if current_file in changes_dict:
                                changes_dict[current_file].append((row, qty))
                            else:
                                changes_dict[current_file] = [(row, qty)]
                        else:
                            raise Exception(
                                f"'{row_key}' should be in same Excel file")
                else:
                    if row[0] != "po_number":
                        raise Exception(
                            "Missing po_number in input file {}".format(in_file))
                    elif row[1] != "ean":
                        raise Exception(
                            "Missing ean in input file {}".format(in_file))
                    elif row[2] != "qty":
                        raise Exception(
                            "Missing qty in input file {}".format(in_file))
                    header_read = True
        shutil.move(in_file, prc_file)

    for filename, change_rows in changes_dict.items():
        if not change_rows:
            continue

        
        try:
            wb = openpyxl.load_workbook(filename = filename)
            ws = wb.active
            for chgs in change_rows:
                row, qty = chgs
                cell = f"I{row + 1}"
                ws[cell] = int(qty)
            wb.save(filename)
            dirty_file_list.append(filename)
        except Exception as ex:
            logger.error(ex)
    
    return dirty_file_list

def process_files(data_root):
    global logger

    path_in = os.path.join(data_root, "in")
    path_xls = os.path.join(data_root, "asn_excel")
    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")

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

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

    if num_files == 0:
        return

    log_file = os.path.join(path_log, __file__ + ".log")
    logging.config.dictConfig(common.get_logging_config(log_file))

    logger = logging.getLogger(__file__)

    asn_ref, content_ref = read_asn_files(path_xls)

    try:
        xls_files = process_export_file(path_in, path_prc, asn_ref, content_ref)
        # Move the updated XLS files to out for upload
        for x in xls_files:
            f = os.path.basename(x)
            t = os.path.join(path_out, f)
            shutil.move(x, t)
    except Exception as e:
        logging.error(e)


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

    project_root = os.path.join(data_root, "Matching_Vendor_ASN")
    process_files(project_root)
    print("Done.")


if __name__ == "__main__":
    main()