"""Validate invoice data and create a formatted follow-up workbook."""

from datetime import date
from pathlib import Path
import tkinter as tk
from tkinter import filedialog, messagebox

import pandas as pd
from openpyxl import load_workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side


# Change this value when the report needs to be prepared for another date.
REPORT_DATE = date(2026, 8, 4)

DATE_FORMAT = "%Y-%m-%d"
MONEY_FORMAT = '#,##0.00'

SOURCE_COLUMNS = [
    "Invoice Number",
    "Customer Name",
    "Customer Email",
    "Invoice Date",
    "Due Date",
    "Invoice Amount",
    "Amount Paid",
]


def clean_text(value):
    """Return trimmed text, or an empty string for a missing value."""
    if pd.isna(value):
        return ""
    return str(value).strip()


def validate_and_calculate(source):
    """Split source rows into rejected rows and calculated valid invoices."""
    valid_records = []
    rejected_records = []

    for index, row in source.iterrows():
        source_row = int(row["__source_row__"])
        source_file = row["__source_file__"]
        cleaned = {column: clean_text(row[column]) for column in SOURCE_COLUMNS}
        reasons = []

        if not cleaned["Invoice Number"]:
            reasons.append("Invoice Number is required")
        if not cleaned["Customer Name"]:
            reasons.append("Customer Name is required")
        if not cleaned["Customer Email"]:
            reasons.append("Customer Email is required")

        invoice_date = pd.to_datetime(
            cleaned["Invoice Date"], format=DATE_FORMAT, errors="coerce"
        )
        due_date = pd.to_datetime(
            cleaned["Due Date"], format=DATE_FORMAT, errors="coerce"
        )
        if pd.isna(invoice_date):
            reasons.append("Invoice Date must be a valid date in YYYY-MM-DD format")
        if pd.isna(due_date):
            reasons.append("Due Date must be a valid date in YYYY-MM-DD format")

        invoice_amount = pd.to_numeric(cleaned["Invoice Amount"], errors="coerce")
        amount_paid = pd.to_numeric(cleaned["Amount Paid"], errors="coerce")
        if pd.isna(invoice_amount):
            reasons.append("Invoice Amount must be numeric")
        elif invoice_amount <= 0:
            reasons.append("Invoice Amount must be greater than zero")
        if pd.isna(amount_paid):
            reasons.append("Amount Paid must be numeric")
        elif amount_paid < 0:
            reasons.append("Amount Paid cannot be negative")

        if not pd.isna(invoice_date) and not pd.isna(due_date) and due_date < invoice_date:
            reasons.append("Due Date cannot be earlier than Invoice Date")

        if reasons:
            rejected_records.append(
                {
                    "Source File": source_file,
                    "Source Row": source_row,
                    **cleaned,
                    "Reason": "; ".join(reasons),
                }
            )
            continue

        invoice_amount = float(invoice_amount)
        amount_paid = float(amount_paid)
        invoice_date_value = invoice_date.date()
        due_date_value = due_date.date()

        # Overpayments remain valid; their outstanding balance is never negative.
        if amount_paid > invoice_amount:
            payment_status = "Overpaid"
        elif amount_paid == invoice_amount:
            payment_status = "Paid"
        elif amount_paid == 0:
            payment_status = "Unpaid"
        else:
            payment_status = "Partially Paid"

        outstanding_balance = max(invoice_amount - amount_paid, 0.0)
        days_until_due = max((due_date_value - REPORT_DATE).days, 0)
        days_overdue = max((REPORT_DATE - due_date_value).days, 0)

        if payment_status in {"Paid", "Overpaid"}:
            priority = "No Follow-Up"
        elif due_date_value > REPORT_DATE:
            priority = "Not Due"
        elif due_date_value == REPORT_DATE:
            priority = "Due Today"
        elif days_overdue <= 7:
            priority = "Low Priority"
        elif days_overdue <= 30:
            priority = "Medium Priority"
        else:
            priority = "High Priority"

        valid_records.append(
            {
                "Source File": source_file,
                "Source Row": source_row,
                "Invoice Number": cleaned["Invoice Number"],
                "Customer Name": cleaned["Customer Name"],
                "Customer Email": cleaned["Customer Email"],
                "Invoice Date": invoice_date_value,
                "Due Date": due_date_value,
                "Invoice Amount": invoice_amount,
                "Amount Paid": amount_paid,
                "Outstanding Balance": outstanding_balance,
                "Payment Status": payment_status,
                "Days Until Due": days_until_due,
                "Days Overdue": days_overdue,
                "Follow-Up Priority": priority,
            }
        )

    return pd.DataFrame(valid_records), pd.DataFrame(rejected_records)


def build_reminder(invoice):
    """Create a professional reminder draft without sending any email."""
    timing = ""
    if invoice["Days Overdue"] > 0:
        timing = f" The invoice is now {invoice['Days Overdue']} day(s) overdue."

    return (
        f"Subject: Payment reminder for invoice {invoice['Invoice Number']}\n\n"
        f"Dear {invoice['Customer Name']},\n\n"
        f"This is a polite reminder regarding invoice {invoice['Invoice Number']} for "
        f"{invoice['Invoice Amount']:,.2f}. We have recorded payments totaling "
        f"{invoice['Amount Paid']:,.2f}, leaving an outstanding balance of "
        f"{invoice['Outstanding Balance']:,.2f}. The invoice due date is "
        f"{invoice['Due Date'].strftime(DATE_FORMAT)}.{timing}\n\n"
        "Please arrange payment or confirm the payment status at your earliest convenience. "
        "If you need clarification or believe our records should be updated, please contact "
        "the accounts department.\n\nKind regards,\nAccounts Department"
    )


def create_summaries(valid, rejected):
    """Build customer-level and management-level summary tables."""
    summary_source = valid.copy()
    summary_source["Is Overdue"] = (
        (summary_source["Outstanding Balance"] > 0)
        & (summary_source["Due Date"] < REPORT_DATE)
    ).astype(int)
    customer_summary = (
        summary_source.groupby("Customer Name", as_index=False)
        .agg(
            Invoice_Count=("Invoice Number", "count"),
            Total_Invoiced=("Invoice Amount", "sum"),
            Total_Paid=("Amount Paid", "sum"),
            Outstanding_Balance=("Outstanding Balance", "sum"),
            Overdue_Invoice_Count=("Is Overdue", "sum"),
        )
        .rename(
            columns={
                "Invoice_Count": "Invoice Count",
                "Total_Invoiced": "Total Invoiced",
                "Total_Paid": "Total Paid",
                "Outstanding_Balance": "Outstanding Balance",
                "Overdue_Invoice_Count": "Overdue Invoice Count",
            }
        )
        .sort_values(["Outstanding Balance", "Customer Name"], ascending=[False, True])
    )

    outstanding = valid[valid["Outstanding Balance"] > 0]
    overdue = outstanding[valid.loc[outstanding.index, "Due Date"] < REPORT_DATE]
    high_priority = valid[valid["Follow-Up Priority"] == "High Priority"]

    management_summary = pd.DataFrame(
        [
            ("Report Date", REPORT_DATE),
            ("Source Rows", len(valid) + len(rejected)),
            ("Valid Invoices", len(valid)),
            ("Rejected Rows", len(rejected)),
            ("Total Invoice Amount", valid["Invoice Amount"].sum()),
            ("Total Amount Paid", valid["Amount Paid"].sum()),
            ("Total Outstanding Balance", valid["Outstanding Balance"].sum()),
            ("Invoices With Outstanding Balance", len(outstanding)),
            ("Overdue Invoices", len(overdue)),
            ("High Priority Invoices", len(high_priority)),
            ("Overpaid Invoices", int((valid["Payment Status"] == "Overpaid").sum())),
        ],
        columns=["Metric", "Value"],
    )
    return customer_summary, management_summary


def format_workbook(path):
    """Apply consistent professional formatting to every output sheet."""
    workbook = load_workbook(path)
    header_fill = PatternFill("solid", fgColor="1F4E78")
    header_font = Font(color="FFFFFF", bold=True)
    accent_fill = PatternFill("solid", fgColor="D9EAF7")
    thin_gray = Side(style="thin", color="D9E2F3")

    money_headers = {
        "Invoice Amount",
        "Amount Paid",
        "Outstanding Balance",
        "Total Invoiced",
        "Total Paid",
    }
    date_headers = {"Invoice Date", "Due Date"}

    for sheet in workbook.worksheets:
        sheet.freeze_panes = "A2"
        sheet.sheet_view.showGridLines = False
        sheet.auto_filter.ref = sheet.dimensions
        sheet.row_dimensions[1].height = 24

        headers = {cell.value: cell.column for cell in sheet[1] if cell.value is not None}
        for cell in sheet[1]:
            cell.fill = header_fill
            cell.font = header_font
            cell.alignment = Alignment(horizontal="center", vertical="center")
            cell.border = Border(bottom=Side(style="medium", color="17365D"))

        for header in money_headers:
            if header in headers:
                for cell in sheet.iter_cols(
                    min_col=headers[header], max_col=headers[header], min_row=2
                ):
                    for item in cell:
                        item.number_format = MONEY_FORMAT

        for header in date_headers:
            if header in headers:
                for cell in sheet.iter_cols(
                    min_col=headers[header], max_col=headers[header], min_row=2
                ):
                    for item in cell:
                        item.number_format = "yyyy-mm-dd"

        for row in sheet.iter_rows(min_row=2):
            for cell in row:
                cell.border = Border(bottom=thin_gray)
                cell.alignment = Alignment(vertical="top")

        # Wrap long explanatory fields and reminder drafts.
        for header in ("Reason", "Reminder Draft"):
            if header in headers:
                col_index = headers[header]
                for row in range(2, sheet.max_row + 1):
                    sheet.cell(row=row, column=col_index).alignment = Alignment(
                        wrap_text=True, vertical="top"
                    )
                    sheet.row_dimensions[row].height = 90 if header == "Reminder Draft" else 45

        # Use restrained highlighting for monetary totals on the management sheet.
        if sheet.title == "Management Summary":
            for row in range(2, sheet.max_row + 1):
                metric = sheet.cell(row=row, column=1).value
                if metric in {
                    "Total Invoice Amount",
                    "Total Amount Paid",
                    "Total Outstanding Balance",
                }:
                    sheet.cell(row=row, column=2).number_format = MONEY_FORMAT
                    for cell in sheet[row]:
                        cell.fill = accent_fill
                        cell.font = Font(bold=True)
                elif metric == "Report Date":
                    sheet.cell(row=row, column=2).number_format = "yyyy-mm-dd"

        # Adjust widths based on content, with practical caps for long text.
        for column_cells in sheet.columns:
            letter = column_cells[0].column_letter
            header = column_cells[0].value
            max_length = max(
                len(str(cell.value)) if cell.value is not None else 0 for cell in column_cells
            )
            cap = 90 if header == "Reminder Draft" else 55 if header == "Reason" else 30
            sheet.column_dimensions[letter].width = min(max(max_length + 2, 12), cap)

        if "Outstanding Balance" in headers and sheet.max_row >= 2:
            balance_letter = sheet.cell(row=1, column=headers["Outstanding Balance"]).column_letter
            sheet.conditional_formatting.add(
                f"{balance_letter}2:{balance_letter}{sheet.max_row}",
                CellIsRule(
                    operator="greaterThan",
                    formula=["0"],
                    fill=PatternFill("solid", fgColor="FFF2CC"),
                ),
            )

    workbook.save(path)


def select_files(root):
    """Ask the user which CSV files to process and where to save the report."""
    selected = filedialog.askopenfilenames(
        parent=root,
        title="Select invoice CSV files",
        filetypes=[("CSV files", "*.csv"), ("All files", "*.*")],
    )
    if not selected:
        return [], None

    output = filedialog.asksaveasfilename(
        parent=root,
        title="Save invoice follow-up report",
        initialdir=str(Path(selected[0]).parent),
        initialfile="invoice_followup_report.xlsx",
        defaultextension=".xlsx",
        filetypes=[("Excel workbook", "*.xlsx")],
    )
    return [Path(path) for path in selected], Path(output) if output else None


def read_source_files(input_files):
    """Read and combine all selected CSV files while retaining row provenance."""
    frames = []
    for input_file in input_files:
        frame = pd.read_csv(input_file, dtype=str, keep_default_na=False)
        missing_columns = [column for column in SOURCE_COLUMNS if column not in frame.columns]
        if missing_columns:
            raise ValueError(
                f"{input_file.name} is missing required columns: {', '.join(missing_columns)}"
            )
        frame = frame[SOURCE_COLUMNS].copy()
        frame["__source_file__"] = input_file.name
        frame["__source_row__"] = range(2, len(frame) + 2)
        frames.append(frame)
    return pd.concat(frames, ignore_index=True)


def create_report(input_files, output_file):
    """Read selected CSV files, validate them, and export one combined report."""
    source = read_source_files(input_files)

    valid, rejected = validate_and_calculate(source)
    overdue = valid[
        (valid["Outstanding Balance"] > 0) & (valid["Due Date"] < REPORT_DATE)
    ].copy()
    high_priority = valid[valid["Follow-Up Priority"] == "High Priority"].copy()

    reminder_columns = [
        "Invoice Number",
        "Customer Name",
        "Customer Email",
        "Follow-Up Priority",
        "Reminder Draft",
    ]
    reminders = valid[valid["Outstanding Balance"] > 0].copy()
    reminders["Reminder Draft"] = reminders.apply(build_reminder, axis=1)
    reminders = reminders[reminder_columns]

    customer_summary, management_summary = create_summaries(valid, rejected)

    # ExcelWriter creates only the requested workbook and never changes the CSV.
    with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
        valid.to_excel(writer, sheet_name="Valid Invoices", index=False)
        rejected.to_excel(writer, sheet_name="Rejected Rows", index=False)
        overdue.to_excel(writer, sheet_name="Overdue Invoices", index=False)
        high_priority.to_excel(writer, sheet_name="High Priority", index=False)
        customer_summary.to_excel(writer, sheet_name="Customer Summary", index=False)
        reminders.to_excel(writer, sheet_name="Reminder Drafts", index=False)
        management_summary.to_excel(writer, sheet_name="Management Summary", index=False)

    format_workbook(output_file)


def main():
    """Display file dialogs, create the report, and show the outcome to the user."""
    root = tk.Tk()
    root.withdraw()
    try:
        root.attributes("-topmost", True)
        input_files, output_file = select_files(root)
        if not input_files or output_file is None:
            return
        create_report(input_files, output_file)
        messagebox.showinfo(
            "Invoice Follow-Up",
            f"Report created successfully:\n{output_file}\n\n"
            f"Files processed: {len(input_files)}\nReport date: {REPORT_DATE:%Y-%m-%d}",
            parent=root,
        )
    except Exception as exc:
        messagebox.showerror("Invoice Follow-Up", str(exc), parent=root)
        raise
    finally:
        root.destroy()


if __name__ == "__main__":
    main()
