#!/usr/bin/env python
# -*- coding: utf-8 -*-

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

from common import get_column_index_csv
from openpyxl import Workbook, load_workbook
from openpyxl.utils.cell import get_column_letter

WORKSHEET_NAME = "Suchergebnisse"

logger = logging.getLogger(__name__)
logger.setLevel(logging.NOTSET)

HEADER_REF_EAN_TEXT = "Universal Product Code"
HEADER_REF_PROD_CODE_TEXT = "Product Code"
HEADER_REF_DEF_SIZE_TEXT = "Default Size Description"

HEADER_ROW_AVAIL_DATE_TEXT = "Verfügbarkeitsdatum"
HEADER_ROW_AVAILABLE_SIZES_TEXT = "Verfügbare Größen"
HEADER_ROW_COLOR_TEXT = "Artikelfarbe"
HEADER_ROW_WHOLESALE_PRICE_TEXT = "Großhandelspreis"
HEADER_ROW_ITEM_NAME_TEXT = "Artikelname"
HEADER_ROW_RETAIL_PRICE_TEXT = "Einzelhandelspreis"
HEADER_ROW_CATEGORY_TEXT = "Kategorie"
HEADER_ROW_GENDER_TEXT = "Geschlecht"
HEADER_ROW_DIVISION_TEXT = "Division"
HEADER_ROW_SILHOUETTE_TEXT = "Silhouette"
HEADER_ROW_START_DATE_TEXT = "Beginn Angebotsdatum"
HEADER_ROW_END_DATE_TEXT = "Ende Angebotsdatum"
HEADER_ROW_SEASON_TEXT = "Saisons"
HEADER_ROW_AGE_TEXT = "Age"
HEADER_ROW_TEAM_CATEGORY_TEXT = "Team Category"
HEADER_ROW_TEAM_NAME_TEXT = "Team Name"

AVAILABLE_NOW = "Jetzt verfügbar"
MISSING_VALUE = "-2"
MISSING_WEIGHT = "150"

def setup_logging(log_path):
    # Set up logging
    log_file = os.path.join(log_path, '{}.log'.format(time.strftime("%Y%m%d-%H%M%S")))

    logging.config.dictConfig({
        'version': 1,
        'disable_existing_loggers': False,
        'formatters': {
            'standard': {
                'format': '%(asctime)s [%(levelname)s] %(name)s: %(message)s'
            },
        },
        'handlers': {
            'default': {
                'level': 'NOTSET',
                'class': 'logging.StreamHandler',
                'formatter': 'standard'
            },
            'logfile': {
                'filename': log_file,
                'class': 'logging.FileHandler',
                'formatter': 'standard'
            }
        },
        'loggers': {
            '': {
                'handlers': ['default', 'logfile'],
                'level': 'NOTSET',
                'propagate': True
            }
        }
    })

def create_key(code, size):
    return code + "_" + size

def read_ref(ref_path):
    ref_dict = dict()

    files = os.listdir(ref_path)

    for file in files:
        header_read = False
        ean_index = -1
        prod_code_index = -1
        def_size_index = -1

        ref_file = os.path.join(ref_path, file)
        logger.debug("Reading ref file {}...".format(file))
        with codecs.open(ref_file, "rb", encoding='utf-8') as f:
            reader = csv.reader(f, delimiter=';')

            for row in reader:
                if header_read:
                    prod_code = row[prod_code_index].strip()
                    def_size = row[def_size_index].strip()

                    if prod_code and def_size:
                        key = create_key(prod_code, def_size)
                        if key not in ref_dict:
                            ean = row[ean_index].strip()
                            if len(ean) == 14:
                                ean = ean[1:]

                            ref_dict[key] = ean
                else:
                    ean_index = get_column_index_csv(row, HEADER_REF_EAN_TEXT)
                    prod_code_index = get_column_index_csv(row, HEADER_REF_PROD_CODE_TEXT)
                    def_size_index = get_column_index_csv(row, HEADER_REF_DEF_SIZE_TEXT)

                    if ean_index < 0:
                        raise Exception("Cannot find column '{}' in ref".format(HEADER_REF_EAN_TEXT))

                    if prod_code_index < 0:
                        raise Exception("Cannot find column '{}' in ref".format(HEADER_REF_PROD_CODE_TEXT))

                    if def_size_index < 0:
                        raise Exception("Cannot find column '{}' in ref".format(HEADER_REF_DEF_SIZE_TEXT))

                    header_read = True

    logger.debug("Read a total of {} ref keys.".format(len(ref_dict)))

    return ref_dict

def iter_rows(ws):
    for row in ws.iter_rows():
        yield [cell.value for cell in row]

def extract_inventory(in_file, out_file, miss_file, future_file, ref_dict):
    header_read = False
    avail_date_index = -1
    color_index = -1
    wholesale_price_index = -1
    size_index = -1
    retail_price_index = -1
    name_index = -1
    category_index = -1
    gender_index = -1
    division_index = -1
    silhouette_index = -1
    start_date_index = -1
    end_date_index = -1
    season_index = -1
    age_index = -1
    team_category_index = -1
    team_name_index = -1
    miss_written_header = False

    wb = load_workbook(in_file, read_only=True)

    if WORKSHEET_NAME not in wb:
        raise Exception("Worksheet '{}' not found".format(WORKSHEET_NAME))

    ws = wb[WORKSHEET_NAME]

    rows = list(iter_rows(ws))

    try:
        with codecs.open(out_file, "w", encoding='utf-8') as of, \
                codecs.open(miss_file, "w", encoding='utf-8') as mf, \
                codecs.open(future_file, "w", encoding='utf-8') as ff:

            writer = csv.writer(of, delimiter=';', quoting=csv.QUOTE_NONNUMERIC)
            miss_writer = csv.writer(mf, delimiter=';', quoting=csv.QUOTE_NONNUMERIC)
            future_writer = csv.writer(ff, delimiter=';', quoting=csv.QUOTE_NONNUMERIC)

            headers = [
                "Artikelnummer", "Size", "Bestand", "Price", "EAN", "Retail", "Name",
                "Kategorie", "Geschlecht", "Division", "Silhouette",
                "Beginn Angebotsdatum", "Ende Angebotsdatum", "Saisons", "Age", "Team Category", "Team Name", "Plenty"
            ]
            writer.writerow(headers)
            future_writer.writerow(headers + ["Verfügbarkeit"])

            for row in rows:
                if header_read:
                    if not row[0]:
                        sizes = []
                        curr_index = size_index
                        curr_size = str(row[curr_index]).strip()
                        while curr_size:
                            sizes.append(curr_size)
                            curr_index += 1
                            curr_size = str(row[curr_index]).strip() if row[curr_index] else None
                    else:
                        color_code = row[color_index]
                        wholesale_price_text = row[wholesale_price_index]
                        item_name = row[name_index]

                        category = row[category_index]
                        gender = row[gender_index]
                        division = row[division_index]
                        silhouette = row[silhouette_index]
                        start_date = row[start_date_index]
                        end_date = row[end_date_index]
                        season = row[season_index]
                        age = row[age_index]
                        team_category = row[team_category_index]
                        team_name = row[team_name_index]

                        plenty = "NI" + color_code.replace("-", "_")

                        # Clean wholesale price
                        if wholesale_price_text:
                            wholesale_price_text = wholesale_price_text.split(" ")[0]
                            wholesale_price_text = wholesale_price_text.replace(",", ".").replace("€", "").strip()
                        try:
                            price = float(wholesale_price_text)
                        except ValueError:
                            price = 0

                        # Clean retail price
                        retail_price_text = row[retail_price_index]
                        if retail_price_text:
                            retail_price_text = retail_price_text.split(" ")[0]
                            retail_price_text = retail_price_text.replace(",", ".").replace("€", "").strip()
                        try:
                            retail_price = float(retail_price_text)
                        except ValueError:
                            retail_price = 0

                        for i in range(len(sizes)):
                            size = sizes[i]
                            key = create_key(color_code, size)
                            if key in ref_dict:
                                ean = ref_dict[key]
                                stock_text = str(row[size_index + i]).replace("+", "")
                                try:
                                    stock_amt = int(stock_text)
                                    if stock_amt > 0:
                                        row_data = [
                                            color_code, size, stock_amt, price, ean, retail_price, item_name,
                                            category, gender, division, silhouette,
                                            start_date, end_date, season, age, team_category, team_name, plenty
                                        ]
                                        writer.writerow(row_data)

                                        # Process futures availability
                                        avail_date = row[avail_date_index]
                                        if avail_date:
                                            future_writer.writerow(row_data + [avail_date])
                                except ValueError:
                                    continue
                            else:
                                if not miss_written_header:
                                    miss_writer.writerow(["unmatched_ref_key"])
                                    miss_written_header = True
                                miss_writer.writerow([key])
                else:
                    avail_date_index = get_column_index_csv(row, HEADER_ROW_AVAIL_DATE_TEXT)
                    color_index = get_column_index_csv(row, HEADER_ROW_COLOR_TEXT)
                    wholesale_price_index = get_column_index_csv(row, HEADER_ROW_WHOLESALE_PRICE_TEXT)
                    size_index = get_column_index_csv(row, HEADER_ROW_AVAILABLE_SIZES_TEXT)
                    retail_price_index = get_column_index_csv(row, HEADER_ROW_RETAIL_PRICE_TEXT)
                    name_index = get_column_index_csv(row, HEADER_ROW_ITEM_NAME_TEXT)
                    category_index = get_column_index_csv(row, HEADER_ROW_CATEGORY_TEXT)
                    gender_index = get_column_index_csv(row, HEADER_ROW_GENDER_TEXT)
                    division_index = get_column_index_csv(row, HEADER_ROW_DIVISION_TEXT)
                    silhouette_index = get_column_index_csv(row, HEADER_ROW_SILHOUETTE_TEXT)
                    start_date_index = get_column_index_csv(row, HEADER_ROW_START_DATE_TEXT)
                    end_date_index = get_column_index_csv(row, HEADER_ROW_END_DATE_TEXT)
                    season_index = get_column_index_csv(row, HEADER_ROW_SEASON_TEXT)
                    age_index = get_column_index_csv(row, HEADER_ROW_AGE_TEXT)
                    team_category_index = get_column_index_csv(row, HEADER_ROW_TEAM_CATEGORY_TEXT)
                    team_name_index = get_column_index_csv(row, HEADER_ROW_TEAM_NAME_TEXT)

                    if avail_date_index < 0 or color_index < 0 or wholesale_price_index < 0 or size_index < 0 or \
                            retail_price_index < 0 or name_index < 0:
                        raise Exception("Not all columns found in input file.")

                    header_read = True
    finally:
        wb.close()

def process_files(data_root):
    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")
    path_ref = os.path.join(data_root, "ref")

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

    setup_logging(path_log)

    logger.info("Found {0} files...".format(num_files))

    if num_files == 0:
        return

    ref_dict = None

    try:
        ref_dict = read_ref(path_ref)
    except Exception as e:
        logger.error(e)

    if ref_dict:
        for f in files:
            in_file = os.path.join(path_in, f)
            out_file_name = "Nike_Bestand_" + os.path.basename(f).split('.')[0] + ".csv"
            out_file = os.path.join(path_out, out_file_name)
            miss_file_name = 'Nike_Bestand_nomatch.csv'
            miss_file = os.path.join(path_out, miss_file_name)
            future_file_name = 'Nike_Futures_' + os.path.basename(f).split('.')[0] + '.csv'
            future_file = os.path.join(path_out, future_file_name)
            proc_file = os.path.join(path_proc, f)
            err_file = os.path.join(path_err, f)

            logger.debug("Processing {0}...".format(in_file))

            if os.path.isfile(in_file):
                try:
                    extract_inventory(in_file, out_file, miss_file, future_file, ref_dict)
                    shutil.move(in_file, proc_file)
                except Exception as e:
                    logger.error(e)
                    shutil.move(in_file, err_file)
                    os.remove(out_file)

    logging.shutdown()

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

if __name__ == "__main__":
    main()