""" Script to process Matching_Vendor_Katalog files. """
#!/usr/bin/env python3.7

import logging.config
import os
import shutil
from typing import Optional, List

import pandas as pd

from common import get_data_root, setup_logging

DATA_FOLDER_NAME = "Matching_Vendor_Katalog"


def convert_xlsm_to_csv(in_file: str, sheets: Optional[List]):
    """ Read data from Excel sheets and add to list. """
    xls = pd.ExcelFile(in_file)
    sheet_names = xls.sheet_names
    if len(sheet_names) <= 6:
        raise Exception(
            "Something is wrong. The Excel workbook contains " \
            + "less than 6 sheets.")

    for sheet_name in sheet_names[6:]:
        logging.info(f"Sheetname: {sheet_name}\n")

        sheet = pd.read_excel(xls, sheet_name)
        col_names = sheet.iloc[1, 0:]
        col_names = [
            str(col_name).replace(" ", "_") \
               .replace("\xa0", "_") \
               .replace("_–_", "_") \
               .replace("-", "_") \
               .replace("(", "") \
               .replace(")", "") \
               .replace(".", "") \
            for col_name in col_names
        ]

        # Change data frame column names to row 3 contents
        sheet.columns = col_names
        # Get rid of the first 4 rows
        sheet = sheet.drop(sheet.index[0:4])
        # Get rid of Händlercode and Händler-SKU columns
        sheet.drop(["Händlercode", "Händler_SKU"], axis=1, inplace=True)
        col_names = list(sheet.columns)
        num_columns = len(col_names)

        unique_columns = []
        columns_to_drop = []
        for idx in range(num_columns):
            col_name = sheet.columns[idx]

            if not col_name in unique_columns:
                unique_columns.append(col_name)
            else:
                # Rename the duplicate column for deletion
                col_names[idx] = col_name + str(idx)
                columns_to_drop.append(idx)
        sheet.columns = col_names
        sheet.drop(sheet.columns[columns_to_drop], axis=1, inplace=True)

        sheets.append(sheet)


def main() -> None:  # pylint: disable=too-many-locals
    """ Main app. """
    data_folder = os.path.join(get_data_root(), DATA_FOLDER_NAME)

    path_in = os.path.join(data_folder, "in")
    path_out = os.path.join(get_data_root(), "Matching_Vendor_Artikelanlage",
                            "ref")
    path_prc = os.path.join(data_folder, "processed")
    path_err = os.path.join(data_folder, "error")
    path_log = os.path.join(data_folder, "log")

    setup_logging(path_log)

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

    logging.debug(f"Found {num_files} files...")

    if num_files == 0:
        return

    out_file = os.path.join(path_out, "combined.csv")
    logging.info(f"Writing to '{out_file}'")

    if os.path.exists(out_file):
        # Read existing CSV to be combined with the new data
        combined_sheet = pd.read_csv(out_file)
        logging.info(f"Combined rows,cols before: {combined_sheet.shape}")
    else:
        combined_sheet = None

    sheets = []
    for src_file in files:
        try:
            in_file = os.path.join(path_in, src_file)
            prc_file = os.path.join(path_prc, src_file)
            err_file = os.path.join(path_err, src_file)

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

            full_filename = os.path.split(in_file)[-1]
            out_filename = os.path.splitext(full_filename)[0]

            logging.info(f"Converting '{in_file}' to '{out_filename}'...")
            convert_xlsm_to_csv(in_file, sheets)

            if os.path.isfile(prc_file):
                os.remove(prc_file)

            logging.info(f"Moving '{in_file}' to '{prc_file}'...")
            shutil.move(in_file, prc_file)
        except Exception as e:  # pylint: disable=broad-except, invalid-name
            logging.exception(e)
            try:
                shutil.move(in_file, err_file)
            except Exception as e:  # pylint: disable=broad-except , invalid-name
                logging.exception(e)

    if combined_sheet is not None:
        # Merge existing data with new data
        sheets.insert(0, combined_sheet)

    new_combined_sheet = pd.concat(sheets, axis=0, ignore_index=True, sort=True)

    if new_combined_sheet is not None:
        # Write combined file back to disk
        new_combined_sheet.to_csv(out_file, index=False, header=True)
    else:
        logging.warning("No combined sheet")

    logging.info("Done.")


if __name__ == "__main__":
    main()