"""Clean sales data and create a formatted Excel management report."""

from pathlib import Path

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Font, PatternFill


INPUT_FILE = "sales_data.csv"
OUTPUT_FILE = "sales_summary.xlsx"
REQUIRED_COLUMNS = ["Date", "Customer", "Product", "Quantity", "Unit Price", "Region"]
MONEY_FORMAT = "#,##0.00"
COUNT_FORMAT = "#,##0"
HEADER_FILL = PatternFill("solid", fgColor="1F4E78")
HEADER_FONT = Font(color="FFFFFF", bold=True)


def clean_text(value):
    """Trim text fields and normalize missing values to an empty string."""
    if pd.isna(value):
        return ""
    return str(value).strip()


def clean_sales_data(source_path):
    """Read the CSV and separate valid transactions from rejected rows."""
    raw = pd.read_csv(source_path, dtype=str, keep_default_na=False)
    raw.columns = [str(column).strip() for column in raw.columns]

    missing_columns = [column for column in REQUIRED_COLUMNS if column not in raw.columns]
    if missing_columns:
        raise ValueError(f"Missing required columns: {', '.join(missing_columns)}")

    raw = raw[REQUIRED_COLUMNS].copy()
    for column in REQUIRED_COLUMNS:
        raw[column] = raw[column].map(clean_text)

    # Completely blank rows are removed rather than reported as rejected records.
    blank_rows = raw.eq("").all(axis=1)
    raw = raw.loc[~blank_rows].copy()

    quantity_numeric = pd.to_numeric(raw["Quantity"], errors="coerce")
    price_numeric = pd.to_numeric(raw["Unit Price"], errors="coerce")

    # Build a complete, readable list of rejection reasons for every invalid row.
    reasons = []
    for index, row in raw.iterrows():
        row_reasons = []
        if not row["Product"]:
            row_reasons.append("Missing product name")
        if pd.isna(quantity_numeric.loc[index]):
            row_reasons.append("Quantity is not numeric")
        elif quantity_numeric.loc[index] <= 0:
            row_reasons.append("Quantity is zero or negative")
        if pd.isna(price_numeric.loc[index]):
            row_reasons.append("Unit Price is not numeric")
        elif price_numeric.loc[index] <= 0:
            row_reasons.append("Unit Price is zero or negative")
        reasons.append("; ".join(row_reasons))

    reason_series = pd.Series(reasons, index=raw.index, dtype="object")
    rejected = raw.loc[reason_series.ne("")].copy()
    rejected["Reason"] = reason_series.loc[rejected.index]

    clean = raw.loc[reason_series.eq("")].copy()
    clean["Customer"] = clean["Customer"].replace("", "Unknown Customer")
    clean["Quantity"] = quantity_numeric.loc[clean.index]
    clean["Unit Price"] = price_numeric.loc[clean.index]
    clean["Total Sales"] = clean["Quantity"] * clean["Unit Price"]

    # Use integer quantities when every valid quantity is a whole number.
    if not clean.empty and clean["Quantity"].mod(1).eq(0).all():
        clean["Quantity"] = clean["Quantity"].astype("int64")

    return clean.reset_index(drop=True), rejected.reset_index(drop=True)


def create_summaries(clean):
    """Create product, region, and management summaries from valid rows."""
    product_summary = (
        clean.groupby("Product", as_index=False, dropna=False)
        .agg(
            **{
                "Revenue": ("Total Sales", "sum"),
                "Quantity Sold": ("Quantity", "sum"),
                "Transactions": ("Product", "size"),
            }
        )
        .sort_values(["Revenue", "Product"], ascending=[False, True])
        .reset_index(drop=True)
    )

    region_summary = (
        clean.groupby("Region", as_index=False, dropna=False)
        .agg(
            **{
                "Revenue": ("Total Sales", "sum"),
                "Transactions": ("Region", "size"),
                "Quantity Sold": ("Quantity", "sum"),
            }
        )
        .sort_values(["Revenue", "Region"], ascending=[False, True])
        .reset_index(drop=True)
    )

    if clean.empty:
        best_quantity = highest_product = highest_region = "N/A"
    else:
        best_quantity = product_summary.sort_values(
            ["Quantity Sold", "Product"], ascending=[False, True]
        ).iloc[0]["Product"]
        highest_product = product_summary.iloc[0]["Product"]
        highest_region = region_summary.iloc[0]["Region"]

    management_summary = pd.DataFrame(
        [
            ["Total Revenue", clean["Total Sales"].sum()],
            ["Total Quantity Sold", clean["Quantity"].sum()],
            ["Number of Valid Transactions", len(clean)],
            ["Best-Selling Product by Quantity", best_quantity],
            ["Highest-Revenue Product", highest_product],
            ["Highest-Revenue Region", highest_region],
        ],
        columns=["Metric", "Value"],
    )
    return product_summary, region_summary, management_summary


def format_workbook(output_path):
    """Apply consistent headings, number formats, widths, and frozen rows."""
    workbook = load_workbook(output_path)

    for worksheet in workbook.worksheets:
        worksheet.freeze_panes = "A2"
        worksheet.auto_filter.ref = worksheet.dimensions
        worksheet.row_dimensions[1].height = 22

        for cell in worksheet[1]:
            cell.fill = HEADER_FILL
            cell.font = HEADER_FONT
            cell.alignment = Alignment(horizontal="center", vertical="center")

        for column_cells in worksheet.columns:
            values = ["" if cell.value is None else str(cell.value) for cell in column_cells]
            width = min(max(max(map(len, values), default=0) + 2, 12), 45)
            worksheet.column_dimensions[column_cells[0].column_letter].width = width

    # Apply monetary and count formats by column heading.
    for worksheet in workbook.worksheets:
        headings = {cell.value: cell.column for cell in worksheet[1]}
        for heading in ("Unit Price", "Total Sales", "Revenue"):
            if heading in headings:
                for row in range(2, worksheet.max_row + 1):
                    worksheet.cell(row, headings[heading]).number_format = MONEY_FORMAT
        for heading in ("Quantity", "Quantity Sold", "Transactions"):
            if heading in headings:
                for row in range(2, worksheet.max_row + 1):
                    worksheet.cell(row, headings[heading]).number_format = COUNT_FORMAT

    management = workbook["Management Summary"]
    management.column_dimensions["A"].width = 38
    management.column_dimensions["B"].width = 24
    management["B2"].number_format = MONEY_FORMAT
    management["B3"].number_format = COUNT_FORMAT
    management["B4"].number_format = COUNT_FORMAT

    workbook.save(output_path)


def main():
    """Run the complete cleaning, summarizing, and export workflow."""
    base_directory = Path(__file__).resolve().parent
    source_path = base_directory / INPUT_FILE
    output_path = base_directory / OUTPUT_FILE

    clean, rejected = clean_sales_data(source_path)
    product_summary, region_summary, management_summary = create_summaries(clean)

    # ExcelWriter creates only the five requested worksheets.
    with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
        clean.to_excel(writer, sheet_name="Clean Data", index=False)
        rejected.to_excel(writer, sheet_name="Rejected Rows", index=False)
        product_summary.to_excel(writer, sheet_name="Product Summary", index=False)
        region_summary.to_excel(writer, sheet_name="Region Summary", index=False)
        management_summary.to_excel(writer, sheet_name="Management Summary", index=False)

    format_workbook(output_path)
    print(f"Created {output_path.name} with {len(clean)} valid and {len(rejected)} rejected rows.")


if __name__ == "__main__":
    main()
