#!/usr/bin/env python

__author__ = 'holger'

import os
import fnmatch
import logging
import shutil
import time
import xlsxwriter
from openpyxl import load_workbook

ITEM_COL_EAN_UPC_13CHAR = 'D'
PRICE_COL_ITEM_NO = 'B'

def write_excel_file(out_file, header_list, item_lists):
    logging.info('Entering write_excel_file...')
    workbook = xlsxwriter.Workbook(out_file)

    try:
        worksheet = workbook.add_worksheet("Combined")

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

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

        current_row = 0

        current_col = 0
        for item in header_list:
            worksheet.write(current_row, current_col, item, format_bold)
            current_col += 1

        for item_list in item_lists:
            current_row += 1

            current_col = 0
            for item in item_list:
                worksheet.write(current_row, current_col, item)
                current_col += 1

    except Exception as e:
        logging.error(e)
    finally:
        workbook.close()

def read_items(in_file):
    logging.info('Entering read_items...')

    wb = load_workbook(filename = in_file, use_iterators = True)
    ws = wb.get_active_sheet()

    skip_rows = 2
    header_read = False
    header_list = []
    data_list = []

    for row in ws.iter_rows():
        if skip_rows > 0:
            skip_rows -= 1
            continue

        if header_read:
            data_row = []
            for cell in row:

                if cell.data_type == 'n':
                    value = str(int(cell.value))
                else:
                    value = cell.value

                data_row.append(value)
            if cell.column == ITEM_COL_EAN_UPC_13CHAR:
                if len(value) < 13:
                    # pad with zeros if necessary
                    value = value.zfill(13-len(value))

            data_list.append(data_row)
        else:
            for cell in row:
                header_list.append(cell.value)
            header_read = True
            skip_rows = 1

    return header_list, data_list

def read_prices(in_file):
    logging.info('Entering read_prices...')

    wb = load_workbook(filename = in_file, use_iterators = True)
    ws = wb.get_active_sheet()

    skip_rows = 1
    header_read = False
    header_list = []
    data_dict = dict()

    for row in ws.iter_rows():
        if skip_rows > 0:
            skip_rows -= 1
            continue

        if header_read:
            data_row = []
            key = ''
            for cell in row:
                if  cell.column == PRICE_COL_ITEM_NO:
                    key = cell.value
                else:
                    data_row.append(cell.value)

            data_dict[key]= data_row
        else:
            for cell in row:
                if  cell.column != PRICE_COL_ITEM_NO:
                    header_list.append(cell.value)
            header_read = True

    return header_list, data_dict

def merge_files(items_in, prices_in, out):
    logging.info('Entering merge_files...')
    item_header_list, item_data_lists = read_items(items_in)
    price_header_list, price_data_dict = read_prices(prices_in)

    combined = []
    for item_data_list in item_data_lists:
        item_num = item_data_list[0]

        if item_num in price_data_dict:
            price_data_list = price_data_dict[item_num]
        else:
            price_data_list = []
        combined.append(item_data_list + price_data_list)
    header_combined = item_header_list + price_header_list
    write_excel_file(out, header_combined, combined)


def find_matching_file(files, expr):
    for file in files:
        file_no_ext = os.path.splitext(file)[0]
        if fnmatch.fnmatch(file_no_ext, expr):
            return file

    return None

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")

    # Set up logging
    time_file_name = time.strftime("%Y%m%d-%H%M%S")
    log_file_name = time_file_name + '.log'
    log_file = os.path.join(path_log, log_file_name)
    logging.basicConfig(filename=log_file, level=logging.DEBUG)

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

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

    if num_files == 0:
        return

    item_in_filename = find_matching_file(files, '*_1')
    item_in_file = os.path.join(path_in, item_in_filename)

    if item_in_file and os.path.isfile(item_in_file):
        price_in_filename = find_matching_file(files, '*_2')
        price_in_file = os.path.join(path_in, price_in_filename)

        if price_in_file and os.path.isfile(price_in_file):
            if len(os.path.split(item_in_file)) == 2:
                item_filename = os.path.split(item_in_file)[1]
            else:
                item_filename = os.path.split(item_in_file)[0]

            if len(os.path.split(price_in_file)) == 2:
                price_filename = os.path.split(price_in_file)[1]
            else:
                price_filename = os.path.split(price_in_file)[0]

            out_file_name = os.path.splitext(item_filename)[0] + '_combined.xlsx'
            out_file = os.path.join(path_out, out_file_name)
            proc_item_file = os.path.join(path_prc, item_filename)
            proc_price_file = os.path.join(path_prc, price_filename)
            err_item_file = os.path.join(path_err, item_filename)
            err_price_file = os.path.join(path_err, price_filename)

            try:
                logging.debug("Processing {0}...".format(item_in_file))
                merge_files(item_in_file, price_in_file, out_file)
                shutil.move(item_in_file, proc_item_file)
                shutil.move(price_in_file, proc_price_file)
            except Exception as e:
                logging.error(e)
                shutil.move(item_in_file, err_item_file)
                shutil.move(price_in_file, err_price_file)

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

    data_root = os.path.join(data_root, "Matching_Nike_Anlage")
    process_files(data_root)

if __name__ == "__main__":
    main()