#!/usr/bin/env python

import codecs
import csv
import logging
import os
import shutil
import time

def read_merge_column_ref(ref_path):
    # ref_dict = {}

    header_read = False
    column_list = []

    full_ref_path = os.path.join(ref_path, "Ref_Spaltenzusammenlegung.csv")
    with codecs.open(full_ref_path, "rb", encoding='utf-8') as f:

        reader = csv.reader(f, delimiter=';')
        for row in reader:
            if header_read:
                merge_column_list = []
                for i in range(8):
                    if row[i]:
                        merge_column_list.append(row[i])
                # Create tuple with (names list, merged name, delimiter)
                delimiter = row[9]
                if delimiter.lower() == "leer":
                    delimiter = " "
                column_list.append((merge_column_list, row[8], delimiter))
            else:
                if len(row) != 10:
                    raise Exception("Ref_Spaltenzusammenlegung.csv must have exactly 10 columns")

                header_read = True

    return column_list


def read_terms(ref_path):
    ref_dict = {}

    header_read = False

    col_name_index = -1
    old_term_index = -1
    new_term_index = -1

    full_ref_path = os.path.join(ref_path, "Ref_Umbennen.csv")
    with codecs.open(full_ref_path, "rb", encoding='utf-8') as f:

        reader = csv.reader(f, delimiter=';')
        for row in reader:
            if header_read:
                col_name = row[col_name_index]

                if col_name in ref_dict:
                    term_list_tuple = ref_dict[col_name]
                else:
                    term_list_tuple = ([], [])

                term_list_tuple[0].append(row[old_term_index])
                term_list_tuple[1].append(row[new_term_index])

                ref_dict[col_name] = term_list_tuple
            else:
                if 'Spaltenbezeichnung' in row:
                    col_name_index = row.index('Spaltenbezeichnung')

                if 'alt' in row:
                    old_term_index = row.index('alt')

                if 'neu' in row:
                    new_term_index = row.index('neu')

                if col_name_index < 0 or old_term_index < 0 or new_term_index < 0:
                    raise Exception("Missing column in ref")

                header_read = True

    return ref_dict


def convert_tracking(in_file, out_file, ref_dict):
    header_read = False
    exist_col_index_dict = dict()
    column_names = sorted(list(ref_dict.keys()))

    with codecs.open(in_file, "r", encoding='utf-8') as f, \
        codecs.open(out_file, "w", encoding='utf-8') as of:
        reader = csv.reader(f, delimiter=';')
        writer = csv.writer(of, delimiter=';')

        for row in reader:
            if any(row):
                if header_read:
                    for column_name, col_index in exist_col_index_dict.items():
                        col_content = row[col_index]
                        was_term_substituted = False

                        if column_name == "LongDescription":
                            # Find text that starts with *** and remove it
                            try:
                                junk_idx = col_content.index(" ***")
                                col_content = col_content[:junk_idx]
                                was_term_substituted = True
                            except ValueError:
                                pass

                            if "  " in col_content:
                                # Remove multiple spaces with a single space
                                col_content = " ".join([x.strip() for x in col_content.split(" ")
                                                        if x.strip()])
                                was_term_substituted = True

                            if "--" in col_content:
                                while "--" in col_content:
                                    col_content = col_content.replace("--", "-")
                                was_term_substituted = True

                        subst_tuples = ref_dict[column_name]

                        for term_index in range(len(subst_tuples[0])):
                            if subst_tuples[0][term_index] in col_content:
                                col_content = col_content.replace(subst_tuples[0][term_index],
                                                                  subst_tuples[1][term_index])
                                was_term_substituted = True
                        if was_term_substituted:
                            row[col_index] = col_content

                    writer.writerow(row)
                else:
                    # Add only existing columns to dict an record the index number.
                    for colunn_name in column_names:
                        if colunn_name in row:
                            print("Found column name {}".format(colunn_name))
                            exist_col_index_dict[colunn_name] = row.index(colunn_name)

                    print("Number of existing columns found : {}".format(len(exist_col_index_dict)))
                    header_read = True
                    writer.writerow(row)


def merge_columns(in_file, out_file, ref_list):
    header_read = False
    subst_list = []
    with codecs.open(in_file, "r", encoding='utf-8') as f, \
        codecs.open(out_file, "w", encoding='utf-8') as of:

        reader = csv.reader(f, delimiter=';')
        writer = csv.writer(of, delimiter=';')

        for row in reader:
            if header_read:
                if len(subst_list) > 0:
                    for x in subst_list:
                        column_ids = x[0]
                        delimiter = x[1]
                        new_column = ""
                        for column_id in column_ids:
                            column_content = str(row[column_id]).strip()
                            if column_content:
                                if new_column:
                                    new_column += delimiter + column_content
                                else:
                                    new_column = column_content
                        row.append(new_column)

                writer.writerow(row)
            else:
                for merge_col_tuple in ref_list:
                    col_list = merge_col_tuple[0]
                    merge_col_name = merge_col_tuple[1]
                    delimiter = merge_col_tuple[2]

                    col_index_list = []
                    for col in col_list:
                        if col in row:
                            col_index_list.append(row.index(col))
                        else:
                            if len(col_index_list) > 0:
                                # If not all columns can be found in the file
                                # don't merge anything
                                col_index_list = []
                            break

                    if len(col_index_list) > 0:
                        subst_list.append((col_index_list, delimiter))
                        row.append(merge_col_name)

                header_read = True
                writer.writerow(row)


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

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

    if num_files == 0:
        return

    # Set up logging
    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)

    merge_column_list = read_merge_column_ref(path_ref)

    substitute_term_dict = read_terms(path_ref)

    orig_file_dict = {}
    for f in files:
        out_file_name = os.path.splitext(f)[0] + '.csv'
        proc_file_name = os.path.splitext(f)[0] + '.csv'

        in_file = os.path.join(path_in, f)
        out_file = os.path.join(path_intermediate, out_file_name)
        proc_file = os.path.join(path_proc, proc_file_name)
        err_file = os.path.join(path_err, f)

        logging.debug("Merging columns in %s...", in_file)

        if os.path.isfile(in_file):
            print("Merging from {0} to {1}...".format(in_file, out_file))
            try:
                merge_columns(in_file, out_file, merge_column_list)
                orig_file_dict[out_file] = (in_file, proc_file)
                # shutil.move(in_file, proc_file)
            except Exception as e:
                logging.error(e)
                print(e)
                shutil.move(in_file, err_file)

    interm_files = os.listdir(path_intermediate)

    for f in interm_files:
        out_file_name = os.path.splitext(f)[0] + '.csv'

        in_file = os.path.join(path_intermediate, f)
        out_file = os.path.join(path_out, out_file_name)
        err_file = os.path.join(path_err, f)

        logging.debug("Substituting terms in %s...", in_file)

        if os.path.isfile(in_file):
            print("Converting {0} to {1}...".format(in_file, out_file))
            try:
                convert_tracking(in_file, out_file, substitute_term_dict)
                shutil.move(orig_file_dict[in_file][0], orig_file_dict[in_file][1])
                os.remove(in_file)
            except Exception as e:
                logging.error(e)
                print(e)
                shutil.move(in_file, err_file)


def main():
    # data_root = r"/home/holger/git/bitbucket/thelen/data/"
    data_root = r"/ftproot/"

    file_root = os.path.join(data_root, "Matching_Umbenenner")

    process(file_root)

    print("Done.")


if __name__ == "__main__":
    main()