#!/usr/bin/env python3.6

import codecs
import csv
import logging
import logging.config
import os
import shutil

import pandas as pd

import common

logger = None


def read_refs(ref_path):
    ref_dict = {}

    ref_files = os.listdir(ref_path)

    if len(ref_files) == 0:
        logger.warning("No ref files found. Aborting...")
        return

    row_count = 0
    ean_count = 0
    for rf in sorted(ref_files):
        ref_file = os.path.join(ref_path, rf)

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

            ean_idx = -1
            for row in reader:
                if header_read:
                    ean = row.pop(ean_idx)
                    if not ean in ref_dict:
                        ref_dict[ean] = row
                        ean_count += 1
                    else:
                        logger.warning(
                            "EAN '{}' appeared more than once. Skipped.".
                            format(ean))
                    row_count += 1
                else:
                    if not "Code" in row:
                        raise Exception(
                            "'Code' column missing from ref '{}'".format(rf))
                    ean_idx = row.index("Code")
                    header_read = True
    logger.info("EANs read: {} ".format(ean_count))
    if ean_count != row_count:
        logger.info("Dupes found: {}.".format(row_count - ean_count))

    return ref_dict


def write_csv(out_file, matched_data):
    if not matched_data or len(matched_data) == 0:
        logger.warning("No matched data. Nothing to write")
        return

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

        for row in matched_data:
            writer.writerow(row)


def read_excel_file(in_file):
    df = pd.read_excel(in_file, header=1, skiprows=3)
    if not df.empty and df.columns.empty and df.columns[
            0] != "Long Description":
        logger.warning(
            "Excel file does not have the header in the expected row. Aborting..."
        )
        return None
    # Replace NaN as None
    return df.where((pd.notnull(df)), None)


def match_data(df, ref_dict):
    matched_data = []
    headers = list(df.columns.values)
    if not "SizeEAN" in headers:
        logger.error(
            "Could not find column 'SizeEAN' in Excel file. Aborting...")
        return []

    matched_data.append(headers + ["VariationID", "VariationNumber"])
    ean_idx = headers.index("SizeEAN")
    not_found = 0
    for _, row in df.iterrows():
        size_eans = str(row[ean_idx])
        for pic_col in ["Bild1", "Bild2", "Bild3", "Bild4", "Bild5"]:
            if row[pic_col]:
                # row[pic_col] = r"https://sv24erp.de/Media_Export/" + row[
                row[pic_col] = r"https://www.sport-versand24.de/layout/sportversand24/bilder/Media_Export/" + row[
                    pic_col]

        ean_list = size_eans.strip().split(' ')
        for ean in ean_list:
            if ean in ref_dict:
                new_row = row.tolist() + ref_dict[ean]
                new_row[ean_idx] = ean
                matched_data.append(new_row)
            else:
                logger.info("No match found for %s", ean)
                not_found += 1

    if not_found:
        logger.warning("Unmatched EANs: %d", not_found)

    return matched_data


def process(data_root):
    global logger

    path_log = os.path.join(data_root, "log")

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

    logger = logging.getLogger(__file__)

    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_ref = os.path.join(data_root, "ref")

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

    logger.info("Found {0} file(s) in IN...".format(num_files))

    if num_files == 0:
        return

    try:
        ref_dict = read_refs(path_ref)

        for f in in_files:
            in_file = os.path.join(path_in, f)

            if not os.path.isfile(in_file):
                continue

            proc_file = os.path.join(path_proc, f)
            filename = os.path.splitext(os.path.basename(f))[0]

            out_file = os.path.join(path_out, filename + ".csv")

            logger.info("Reading in file '%s'", f)
            logger.info("Writing to file '%s", filename + ".csv")

            try:
                df = read_excel_file(in_file)
                if not df.empty:
                    matched_data = match_data(df, ref_dict)
                    write_csv(out_file, matched_data)

                shutil.move(in_file, proc_file)
            except Exception as ex:
                logger.error(ex)
                err_file = os.path.join(path_err, f)
                shutil.move(in_file, err_file)
    except Exception as e:
        logger.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

    var_data_root = os.path.join(data_root, "Matching_Intersport_Bilder")

    process(var_data_root)

    print("Done.")


if __name__ == "__main__":
    main()