#!/usr/bin/env /usr/local/bin/python3.6
from datetime import datetime
import logging
import os
import shutil
import uuid

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.styles.colors import Color

WORKSHEET_NAME = "Zahlungen"

HEADING_PAYMENTS = "Zahlungen"
HEADING_INVOICES = "Rechnungen"
HEADING_COOP = "COOP"


def coord(r, c):
    return "{}{}".format(chr(ord("A") - 1 + c), r)


def get_country_total_label(country_code):
    if country_code == "DE":
        return "DE Brutto"
    return "{} Brutto/Netto".format(country_code)

def find_section_headings(ws):
    headings_dict = {}

    for row_num in range(1, ws.max_row):
        cell = ws[coord(row_num, 1)]
        if cell.value == HEADING_PAYMENTS:
            headings_dict[HEADING_PAYMENTS] = row_num
        elif cell.value == HEADING_INVOICES:
            headings_dict[HEADING_INVOICES] = row_num

    return headings_dict

def summarize_payments(src, dst, sect_start_row, sect_end_row):
    payment_headers = ["Zahlungsnummer", "Zahlungsdatum", "Rechnungswährung",
                       "Betrag in der Rechnungswährung", "Zahlungswährung",
                       "Betrag in der Zahlungswährung", "Wechselkurs", "Zahlungsart",
                       "Zahlungsstatus", "Grund für Zahlungsstornierung"]

    header_read = False
    hdr_font = Font(name="Arial", size=12, bold=True)
    hdr_fill = PatternFill(patternType="solid", fgColor=Color(rgb="c0c0c0"))

    payment_number_column_num = payment_headers.index("Zahlungsnummer") + 1
    payment_amount_column_num = payment_headers.index("Betrag in der Rechnungswährung") + 1
    payment_date_column_num = payment_headers.index("Zahlungsdatum") + 1

    payments = {}
    payment_total = 0.0

    curr_src_row = sect_start_row + 1
    curr_dst_row = curr_src_row

    for idx in range(1, sect_end_row - sect_start_row):
        if header_read:
            if not src[coord(curr_src_row, 1)].value:
                break

            payment_number = src[coord(curr_src_row, payment_number_column_num)].value
            payment_date = datetime.strptime(
                src[coord(curr_src_row, payment_date_column_num)].value, "%d/%m/%Y")
            payment_amount = float(src[coord(curr_src_row, payment_amount_column_num)].value)

            payment_total += payment_amount

            if payment_number in payments:
                payment_number_total = payments[payment_number]["amount"] + payment_amount
                payments[payment_number] = {"amount": payment_number_total, "date": payment_date}
            else:
                payments[payment_number] = {"amount": payment_amount, "date": payment_date}

            for col_idx, _ in enumerate(payment_headers):
                src_cell = src[coord(curr_src_row, col_idx + 1)]
                dst_cell = dst[coord(curr_dst_row, col_idx + 1)]
                if col_idx + 1 == payment_date_column_num:
                    dst_cell.value = payment_date
                    dst_cell.number_format = "DD.MM.YYYY"
                elif col_idx + 1 == payment_amount_column_num:
                    dst_cell.value = payment_amount
                    dst_cell.number_format = "0.00"
                else:
                    dst_cell.value = src_cell.value
        else:
            if src[coord(sect_start_row + idx, 1)].value == payment_headers[0]:
                header_read = True

                # Verify all columns are as expected
                for col_idx, col_val in enumerate(payment_headers):
                    src_cell = src[coord(curr_src_row, col_idx + 1)]
                    dst_cell = dst[coord(curr_dst_row, col_idx + 1)]

                    if src_cell.value != col_val:
                        raise Exception("Missing column %s", col_val)

                    dst_cell.value = src_cell.value
                    dst_cell.font = hdr_font
                    dst_cell.fill = hdr_fill

        curr_src_row += 1
        curr_dst_row += 1

    payment_amt_cell = dst[coord(curr_dst_row, payment_amount_column_num)]
    payment_amt_cell.value = round(payment_total, 2)
    payment_amt_cell.number_format = "0.00"
    payment_amt_cell.fill = PatternFill(patternType="solid", fgColor=Color(rgb="ffeb9c"))

    return curr_dst_row, payments

def summarize_invoices(src, dst, dst_start_row, sect_start_row, sect_end_row, payments):
    invoice_headers = ["Zahlungsnummer", "Rechnungsnummer", "Rechnungsdatum",
                       "Bezeichnung", "Rechnungsbetrag", "Rechnungswährung",
                       "Steuerabzugsbetrag", "Realisierter Rabatt", "gezahlten Betrag",
                       "verbleibende Betrag"]

    header_read = False
    hdr_font = Font(name="Arial", size=12, bold=True)
    hdr_fill = PatternFill(patternType="solid", fgColor=Color(rgb="c0c0c0"))
    hdr_fill_extra = PatternFill(patternType="solid", fgColor=Color(rgb="ffc7ce"))

    payment_number_col_num = invoice_headers.index("Zahlungsnummer") + 1
    invoice_number_col_num = invoice_headers.index("Rechnungsnummer") + 1
    description_col_num = invoice_headers.index("Bezeichnung") + 1
    invoice_date_col_num = invoice_headers.index("Rechnungsdatum") + 1
    invoice_amount_col_num = invoice_headers.index("Rechnungsbetrag") + 1
    realized_rebate_col_num = invoice_headers.index("Realisierter Rabatt") + 1
    paid_amount_col_num = invoice_headers.index("gezahlten Betrag") + 1
    remaing_amount_col_num = invoice_headers.index("verbleibende Betrag") + 1

    tax_column_col_num = len(invoice_headers) + 1
    net_amount_col_num = len(invoice_headers) + 2

    curr_src_row = sect_start_row + 1
    curr_dst_row = dst_start_row + 1

    curr_country_code = "CZ"
    country_invoices = {"CZ": 0.0, "DE": 0.0, "PL": 0.0}
    invoice_total = 0.0
    realized_rebate_total = 0.0
    paid_total = 0.0
    tax_total = 0.0
    net_amount_total = 0.0
    coop_total = 0.0
    coop_tax_total = 0.0
    coop_net_total = 0.0
    coop_lines = []

    for idx in range(1, sect_end_row - sect_start_row + 1):
        if header_read:
            if not src[coord(curr_src_row, 1)].value:
                break

            # Damage Allowance
            description = src[coord(curr_src_row, description_col_num)].value
            payment_number = src[coord(curr_src_row, payment_number_col_num)].value
            invoice_number = src[coord(curr_src_row, invoice_number_col_num)].value
            invoice_date = datetime.strptime(
                src[coord(curr_src_row, invoice_date_col_num)].value, "%d/%m/%Y")
            invoice_amount = float(src[coord(curr_src_row, invoice_amount_col_num)].value)
            realized_rebate_amount = float(src[coord(curr_src_row,
                                                     realized_rebate_col_num)].value)
            paid_amount = float(src[coord(curr_src_row, paid_amount_col_num)].value)
            remaining_amount = float(src[coord(curr_src_row, remaing_amount_col_num)].value)

            if curr_country_code == "CZ" and (invoice_number[:5] == "OUT-D" \
                or invoice_number[:5] == "OUT-C"):
                curr_country_code = "DE"
            elif curr_country_code == "DE" and (invoice_number[:5] == "OUT-L" \
                or invoice_number[:5] == "OUT-P"):
                curr_country_code = "PL"

            if curr_country_code == "DE":
                net_amount = round(invoice_amount / 1.19, 2)
                tax = round(invoice_amount - net_amount, 2)
            else:
                tax = 0.0
                net_amount = invoice_amount

            if description[:16] != "Damage Allowance":
                country_invoices[curr_country_code] += invoice_amount
                invoice_total += invoice_amount
                realized_rebate_total += realized_rebate_amount
                paid_total += paid_amount
                net_amount_total += net_amount
                tax_total += tax

                for col_idx, _ in enumerate(invoice_headers):
                    src_cell = src[coord(curr_src_row, col_idx + 1)]
                    dst_cell = dst[coord(curr_dst_row, col_idx + 1)]
                    if col_idx + 1 == invoice_date_col_num:
                        dst_cell.value = invoice_date
                        dst_cell.number_format = "DD.MM.YYYY"
                    elif col_idx + 1 == invoice_amount_col_num:
                        dst_cell.value = invoice_amount
                        dst_cell.number_format = "0.00"
                    elif col_idx + 1 == paid_amount_col_num:
                        dst_cell.value = paid_amount
                        dst_cell.number_format = "0.00"
                    elif col_idx + 1 == remaing_amount_col_num:
                        dst_cell.value = remaining_amount
                        dst_cell.number_format = "0.00"
                    else:
                        dst_cell.value = src_cell.value

                dst_cell = dst[coord(curr_dst_row, tax_column_col_num)]
                dst_cell.value = tax
                dst_cell.number_format = "0.00"

                dst_cell = dst[coord(curr_dst_row, net_amount_col_num)]
                dst_cell.value = net_amount
                dst_cell.number_format = "0.00"

                if payment_number in payments:
                    dst_cell = dst[coord(curr_dst_row, len(invoice_headers)+3)]
                    dst_cell.value = payments[payment_number]["date"]
                    dst_cell.number_format = "DD.MM.YYYY"

                    if curr_src_row < src.max_row:
                        next_payment_number = src[coord(curr_src_row + 1,
                                                        payment_number_col_num)].value
                    else:
                        next_payment_number = ""

                    # Add amount only to the last row beloning to the same payment
                    if payment_number != next_payment_number:
                        dst_cell = dst[coord(curr_dst_row, len(invoice_headers)+4)]
                        dst_cell.value = payments[payment_number]["amount"]
                        dst_cell.number_format = "0.00"
            else:
                coop_total += paid_amount
                coop_tax_total += tax
                coop_net_total += net_amount
                coop_line = []
                for col_idx in range(1, len(invoice_headers)+1):
                    cell = src[coord(curr_src_row, col_idx)]
                    if col_idx == invoice_date_col_num:
                        coop_line.append(invoice_date)
                    elif col_idx == invoice_amount_col_num:
                        coop_line.append(invoice_amount)
                    elif col_idx == paid_amount_col_num:
                        coop_line.append(paid_amount)
                    elif col_idx == remaing_amount_col_num:
                        coop_line.append(remaining_amount)
                    else:
                        coop_line.append(cell.value)

                coop_line.append(tax)
                coop_line.append(net_amount)
                coop_lines.append(coop_line)
                # No line was added so don't increase
                curr_dst_row -= 1
        else:
            if src[coord(sect_start_row + idx, 1)].value == invoice_headers[0]:
                header_read = True

                # Verify all columns are as expected
                for col_idx, col_val in enumerate(invoice_headers):
                    src_cell = src[coord(curr_src_row, col_idx + 1)]
                    dst_cell = dst[coord(curr_dst_row, col_idx + 1)]

                    if src_cell.value != col_val:
                        raise Exception("Missing column %s", col_val)

                    dst_cell.value = src_cell.value
                    dst_cell.font = hdr_font
                    dst_cell.fill = hdr_fill

                dst_cell = dst[coord(curr_dst_row, len(invoice_headers)+1)]
                dst_cell.value = "Steuer"
                dst_cell.font = hdr_font
                dst_cell.fill = hdr_fill_extra

                dst_cell = dst[coord(curr_dst_row, len(invoice_headers)+2)]
                dst_cell.value = "Nettobetrag"
                dst_cell.font = hdr_font
                dst_cell.fill = hdr_fill_extra

                dst_cell = dst[coord(curr_dst_row, len(invoice_headers)+3)]
                dst_cell.value = "Zahlungsdatum"
                dst_cell.font = hdr_font
                dst_cell.fill = hdr_fill

                dst_cell = dst[coord(curr_dst_row, len(invoice_headers)+4)]
                dst_cell.value = "Betrag in der Rechnungswährung"
                dst_cell.font = hdr_font
                dst_cell.fill = hdr_fill

        curr_src_row += 1
        curr_dst_row += 1


    dst_cell = dst[coord(curr_dst_row, invoice_amount_col_num)]
    dst_cell.value = round(invoice_total, 2)
    dst_cell.number_format = "0.00"
    dst_cell.font = hdr_font
    dst_cell.fill = PatternFill(patternType="solid", fgColor=Color(rgb="ffeb9c"))

    dst_cell = dst[coord(curr_dst_row, realized_rebate_col_num)]
    dst_cell.value = round(realized_rebate_total, 2)
    dst_cell.number_format = "0.00"
    dst_cell.font = hdr_font
    dst_cell.fill = PatternFill(patternType="solid", fgColor=Color(rgb="ffeb9c"))

    dst_cell = dst[coord(curr_dst_row, paid_amount_col_num)]
    dst_cell.value = round(paid_total, 2)
    dst_cell.number_format = "0.00"
    dst_cell.font = hdr_font
    dst_cell.fill = PatternFill(patternType="solid", fgColor=Color(rgb="ffeb9c"))

    dst_cell = dst[coord(curr_dst_row, tax_column_col_num)]
    dst_cell.value = round(tax_total, 2)
    dst_cell.number_format = "0.00"
    dst_cell.font = hdr_font
    dst_cell.fill = PatternFill(patternType="solid", fgColor=Color(rgb="ffeb9c"))

    dst_cell = dst[coord(curr_dst_row, net_amount_col_num)]
    dst_cell.value = round(net_amount_total, 2)
    dst_cell.number_format = "0.00"
    dst_cell.font = hdr_font
    dst_cell.fill = PatternFill(patternType="solid", fgColor=Color(rgb="c6efce"))

    curr_dst_row += 1

    coop = {"lines": coop_lines, "total": coop_total, "tax": coop_tax_total,
            "net": coop_net_total}

    return curr_dst_row, country_invoices, coop, net_amount_total

def add_coop(dst, coop_dict, curr_dst_row):
    invoice_headers = ["Zahlungsnummer", "Rechnungsnummer", "Rechnungsdatum",
                       "Bezeichnung", "Rechnungsbetrag", "Rechnungswährung",
                       "Steuerabzugsbetrag", "Realisierter Rabatt", "gezahlten Betrag",
                       "verbleibende Betrag", "Steuer", "Nettobetrag"]

    hdr_font = Font(name="Arial", size=12, bold=True)
    hdr_fill = PatternFill(patternType="solid", fgColor=Color(rgb="c0c0c0"))
    hdr_fill_extra = PatternFill(patternType="solid", fgColor=Color(rgb="ffc7ce"))

    invoice_amount_col_num = invoice_headers.index("Rechnungsbetrag") + 1
    invoice_date_col_num = invoice_headers.index("Rechnungsdatum") + 1
    tax_column_col_num = invoice_headers.index("Steuer") + 1
    net_amount_col_num = invoice_headers.index("Nettobetrag") + 1
    paid_amount_col_num = invoice_headers.index("gezahlten Betrag") + 1
    remaining_amount_col_num = invoice_headers.index("verbleibende Betrag") + 1

    paid_total = coop_dict["total"]
    tax_total = coop_dict["tax"]
    net_amount_total = coop_dict["net"]

    # Write header for COOP
    for col_idx, col_val in enumerate(invoice_headers):
        dst_cell = dst[coord(curr_dst_row, col_idx + 1)]

        dst_cell.value = col_val
        dst_cell.font = hdr_font
        if col_val != "Steuer" and col_val != "Nettobetrag":
            dst_cell.fill = hdr_fill
        else:
            dst_cell.fill = hdr_fill_extra

    curr_dst_row += 1

    for coop in coop_dict["lines"]:
        for col_idx, val in enumerate(coop):
            dst_cell = dst[coord(curr_dst_row, col_idx+1)]
            dst_cell.value = val
            if col_idx + 1 == invoice_date_col_num:
                dst_cell.number_format = "DD.MM.YYYY"
            elif col_idx + 1 == invoice_amount_col_num:
                dst_cell.number_format = "0.00"
            elif col_idx + 1 == paid_amount_col_num:
                dst_cell.number_format = "0.00"
            elif col_idx + 1 == remaining_amount_col_num:
                dst_cell.number_format = "0.00"

        curr_dst_row += 1


    dst_cell = dst[coord(curr_dst_row, paid_amount_col_num)]
    dst_cell.value = round(paid_total, 2)
    dst_cell.font = hdr_font
    dst_cell.fill = PatternFill(patternType="solid", fgColor=Color(rgb="ffeb9c"))

    dst_cell = dst[coord(curr_dst_row, tax_column_col_num)]
    dst_cell.value = round(tax_total, 2)
    dst_cell.font = hdr_font
    dst_cell.fill = PatternFill(patternType="solid", fgColor=Color(rgb="ffeb9c"))

    dst_cell = dst[coord(curr_dst_row, net_amount_col_num)]
    dst_cell.value = round(net_amount_total, 2)
    dst_cell.number_format = "0.00"
    dst_cell.font = hdr_font
    dst_cell.fill = PatternFill(patternType="solid", fgColor=Color(rgb="c6efce"))

    curr_dst_row += 1

    return curr_dst_row

def add_country_totals(dst, country_invoices, start_row):
    curr_dst_row = start_row + 1

    hdr_font = Font(name="Arial", size=12, bold=True)
    hdr_fill_extra = PatternFill(patternType="solid", fgColor=Color(rgb="ffc7ce"))

    country_grand_total = 0.0
    for k, v in sorted(country_invoices.items()):
        country_total_cell = dst[coord(curr_dst_row, 11)]
        country_total_cell.value = get_country_total_label(k)
        country_total_cell.font = hdr_font
        country_total_cell.fill = hdr_fill_extra

        country_total_cell = dst[coord(curr_dst_row, 12)]
        country_total_cell.value = v
        country_total_cell.number_format = "0.00"
        country_total_cell.font = hdr_font
        country_total_cell.fill = hdr_fill_extra

        country_grand_total += v
        curr_dst_row += 1

    country_total_cell = dst[coord(curr_dst_row, 11)]
    country_total_cell.value = "Summe"
    country_total_cell.font = hdr_font
    country_total_cell.fill = hdr_fill_extra

    country_total_cell = dst[coord(curr_dst_row, 12)]
    country_total_cell.value = round(country_grand_total, 2)
    country_total_cell.number_format = "0.00"
    country_total_cell.font = hdr_font
    country_total_cell.fill = hdr_fill_extra

    return curr_dst_row


def add_percentages(dst, grand_total, start_row):
    curr_dst_row = start_row + 2
    percentages = [0.0125, 0.029, 0.06, 0.07, 0.08, 0.09]
    label_font = Font(name="Arial", size=12, bold=True)
    label_alignment = Alignment(horizontal="right")

    for percentage in percentages:
        label_cell = dst[coord(curr_dst_row, 11)]
        label_cell.value = "{} %".format(round(percentage * 100, 2))
        label_cell.font = label_font

        perc_cell = dst[coord(curr_dst_row, 12)]
        perc_cell.value = round(percentage * grand_total, 2)

        curr_dst_row += 1

    perc_cell = dst[coord(curr_dst_row, 12)]
    perc_cell.value = "{} %".format(round(100000.00 / grand_total, 2))
    perc_cell.alignment = label_alignment


def process_payments_file(in_file, temp_file):
    wb = load_workbook(in_file)
    new_wb = Workbook()

    section_heading_font = Font(name="Arial", size=16, bold=True)

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

    src = wb[WORKSHEET_NAME]
    dst = new_wb.active

    section_heading_rows = find_section_headings(src)

    paym_section_hdr_cell = dst[coord(section_heading_rows[HEADING_PAYMENTS], 1)]
    paym_section_hdr_cell.value = HEADING_PAYMENTS
    paym_section_hdr_cell.font = section_heading_font

    print("Summarizing payments...")

    # Read payments from the first section
    curr_dst_row, payments = summarize_payments(src, dst,
                                                section_heading_rows[HEADING_PAYMENTS],
                                                section_heading_rows[HEADING_INVOICES])

    curr_dst_row += 1
    inv_section_hdr_cell = dst[coord(section_heading_rows[HEADING_INVOICES], 1)]
    inv_section_hdr_cell.value = HEADING_INVOICES
    inv_section_hdr_cell.font = section_heading_font

    curr_dst_row += 2

    sect_end_row = section_heading_rows["COOP"] if "COOP" in section_heading_rows else src.max_row

    print("Summarizing invoices...")
    curr_dst_row, country_invoices, coop, grand_total = \
        summarize_invoices(src, dst, curr_dst_row,
                           section_heading_rows[HEADING_INVOICES],
                           sect_end_row, payments)

    if len(coop["lines"]) > 0:
        print("Summarizing COOP...")
        curr_dst_row += 1
        coop_section_hdr_cell = dst[coord(curr_dst_row, 1)]
        coop_section_hdr_cell.value = HEADING_COOP
        coop_section_hdr_cell.font = section_heading_font
        curr_dst_row += 1
        curr_dst_row = add_coop(dst, coop, curr_dst_row)

    print("Adding country totals")
    curr_dst_row = add_country_totals(dst, country_invoices, curr_dst_row)

    print("Adding percentages")
    add_percentages(dst, grand_total, curr_dst_row)

    new_wb.save(temp_file)


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

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

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

    if num_files == 0:
        return

    for f in files:
        log_filename = os.path.splitext(f)[0] + '.log'
        temp_filename = str(uuid.uuid4()) + ".xlsx"
        summary_file_name = os.path.splitext(f)[0] + '.xlsx'

        in_file = os.path.join(path_in, f)
        temp_file = os.path.join(path_in, temp_filename)
        out_file = os.path.join(path_out, summary_file_name)
        prc_file = os.path.join(path_prc, f)
        err_file = os.path.join(path_err, f)
        log_file = os.path.join(path_log, log_filename)

        if os.path.isfile(in_file):
            logging.basicConfig(filename=log_file, level=logging.DEBUG)

            logging.debug("Summarizing Payments %s...", in_file)
            try:
                process_payments_file(in_file, temp_file)
                shutil.move(in_file, prc_file)
                shutil.move(temp_file, out_file)
            except Exception as e:
                logging.error(e)
                print("Error : {}".format(e))
                shutil.move(in_file, err_file)


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

    vendor_invoice_data_root = os.path.join(data_root, "Matching_Abrechnung_Vendor_OUT24")
    process_files(vendor_invoice_data_root)

if __name__ == "__main__":
    main()