"""Lesen und formatgetreues Schreiben von Excel-Dateien."""

from __future__ import annotations

from copy import copy
from dataclasses import dataclass
from datetime import date, datetime
from pathlib import Path
from typing import Any

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet

from app.mapping import (
    CORE_TARGET_FIELDS,
    FIELDS_BY_KEY,
    FieldKey,
    MIN_SOURCE_MAPPED_FIELDS,
    MIN_TARGET_MAPPED_FIELDS,
    match_columns,
)
from app.models import CustomerRecord
from app.normalize import (
    clean_text,
    combine_street,
    format_zip,
    is_empty,
    parse_date,
)


class ExcelReadError(Exception):
    def __init__(self, message: str) -> None:
        self.message = message
        super().__init__(message)


@dataclass
class LoadedTable:
    headers: list[str]
    mapping: dict[str, int]
    records: list[CustomerRecord]
    header_row: int
    max_column: int
    sheet_name: str = ""


def _cell_raw(value: Any) -> Any:
    if value is None:
        return ""
    if isinstance(value, str) and value.strip() == "":
        return ""
    return value


def _header_list(row: list[Any]) -> list[str]:
    return [clean_text(c) if not isinstance(c, (datetime, date)) else clean_text(c) for c in row]


def _is_row_empty(row: list[Any]) -> bool:
    return all(is_empty(c) for c in row)


def _coerce(field_type: str, raw: Any) -> Any:
    if is_empty(raw):
        return ""
    if field_type == "zip":
        return format_zip(raw)
    if field_type == "date":
        parsed = parse_date(raw)
        return parsed if parsed is not None else clean_text(raw)
    if field_type == "street":
        return clean_text(raw)
    return clean_text(raw)


def _finalize_record_values(values: dict[str, Any]) -> dict[str, Any]:
    street = combine_street(values.get(FieldKey.STREET), values.get(FieldKey.HOUSE_NO))
    if street:
        values[FieldKey.STREET] = street
    generic = values.get(FieldKey.EMAIL)
    if is_empty(values.get(FieldKey.EMAIL_PRIVATE)) and not is_empty(generic):
        values[FieldKey.EMAIL_PRIVATE] = generic
    if is_empty(values.get(FieldKey.EMAIL_BUSINESS)) and not is_empty(generic):
        values[FieldKey.EMAIL_BUSINESS] = generic
    if is_empty(values.get(FieldKey.LAST_NAME)):
        company = values.get(FieldKey.COMPANY) or values.get(FieldKey.CUSTOMER_NAME)
        if not is_empty(company):
            values[FieldKey.LAST_NAME] = company
    return values


def _records_from_matrix(
    matrix: list[list[Any]],
    header_row_idx: int,
    mapping: dict[str, int],
    source: str,
) -> list[CustomerRecord]:
    records: list[CustomerRecord] = []
    for offset, row in enumerate(matrix[header_row_idx + 1 :], start=1):
        if _is_row_empty(row):
            continue
        values: dict[str, Any] = {}
        for key, col in mapping.items():
            field_type = FIELDS_BY_KEY[key].field_type
            raw = row[col] if col < len(row) else ""
            values[key] = _coerce(field_type, raw)
        values = _finalize_record_values(values)
        if all(is_empty(v) for v in values.values()):
            continue
        records.append(
            CustomerRecord(
                values=values,
                source=source,
                row_index=header_row_idx + 1 + offset,
            )
        )
    return records


def _best_header(matrix: list[list[Any]], side: str, scan_rows: int = 12) -> tuple[int, dict[str, int], list[str]]:
    best_idx = 0
    best_mapping: dict[str, int] = {}
    best_headers: list[str] = []
    limit = min(len(matrix), scan_rows)
    for idx in range(limit):
        headers = _header_list(matrix[idx])
        mapping = match_columns(headers, side)
        if len(mapping) > len(best_mapping):
            best_idx = idx
            best_mapping = mapping
            best_headers = headers
    return best_idx, best_mapping, best_headers


def _trim_matrix(matrix: list[list[Any]]) -> list[list[Any]]:
    while matrix and _is_row_empty(matrix[-1]):
        matrix.pop()
    return matrix


def _matrix_from_worksheet(ws: Worksheet) -> list[list[Any]]:
    matrix: list[list[Any]] = []
    for row in ws.iter_rows(values_only=True):
        matrix.append([_cell_raw(c) for c in row])
    return _trim_matrix(matrix)


def _pick_worksheet(wb: Workbook, side: str) -> tuple[Worksheet, int, dict[str, int], list[str], list[list[Any]]]:
    best: tuple[Worksheet, int, dict[str, int], list[str], list[list[Any]]] | None = None
    best_score = -1
    for ws in wb.worksheets:
        matrix = _matrix_from_worksheet(ws)
        if not matrix:
            continue
        header_idx, mapping, headers = _best_header(matrix, side)
        score = len(mapping)
        if score > best_score:
            best_score = score
            best = (ws, header_idx, mapping, headers, matrix)
    if best is None:
        raise ExcelReadError("Die Excel-Datei konnte nicht gelesen werden.")
    return best


def _dataframe_to_matrix(df: pd.DataFrame) -> list[list[Any]]:
    matrix: list[list[Any]] = []
    matrix.append([_cell_raw(c) for c in list(df.columns)])
    for _, series in df.iterrows():
        matrix.append([_cell_raw(v) for v in series.tolist()])
    return _trim_matrix(matrix)


def load_source(path: Path) -> LoadedTable:
    suffix = path.suffix.lower()
    try:
        if suffix == ".csv":
            matrix = _read_csv_matrix(path)
            sheet_name = path.stem
        elif suffix == ".xls":
            df = pd.read_excel(path, header=None, dtype=object, engine="xlrd")
            matrix = [[_cell_raw(v) for v in row] for row in df.values.tolist()]
            matrix = _trim_matrix(matrix)
            sheet_name = path.stem
        elif suffix in {".xlsx", ".xlsm"}:
            wb = load_workbook(path, data_only=True, read_only=True)
            try:
                ws, header_idx, mapping, headers, matrix = _pick_worksheet(wb, "source")
                sheet_name = ws.title
            finally:
                wb.close()
            return _loaded_from_parts(headers, mapping, matrix, header_idx, sheet_name, "A", MIN_SOURCE_MAPPED_FIELDS)
        else:
            raise ExcelReadError("Tabelle A konnte nicht erkannt werden.")
    except ExcelReadError:
        raise
    except Exception as exc:
        raise ExcelReadError("Die Excel-Datei konnte nicht gelesen werden.") from exc

    header_idx, mapping, headers = _best_header(matrix, "source")
    return _loaded_from_parts(headers, mapping, matrix, header_idx, sheet_name, "A", MIN_SOURCE_MAPPED_FIELDS)


def _read_csv_matrix(path: Path) -> list[list[Any]]:
    encodings = ("utf-8-sig", "utf-8", "cp1252", "latin-1")
    separators = (";", ",", "\t")
    for encoding in encodings:
        for sep in separators:
            try:
                df = pd.read_csv(
                    path,
                    encoding=encoding,
                    sep=sep,
                    dtype=object,
                    keep_default_na=False,
                )
            except Exception:
                continue
            if df.shape[1] >= 3:
                return _dataframe_to_matrix(df)
    raise ExcelReadError("Die Excel-Datei konnte nicht gelesen werden.")


def _loaded_from_parts(
    headers: list[str],
    mapping: dict[str, int],
    matrix: list[list[Any]],
    header_idx: int,
    sheet_name: str,
    source: str,
    min_mapped: int,
) -> LoadedTable:
    if len(mapping) < min_mapped:
        if source == "A":
            raise ExcelReadError("Tabelle A konnte nicht erkannt werden.")
        raise ExcelReadError("Vorlage B ist nicht kompatibel.")
    records = _records_from_matrix(matrix, header_idx, mapping, source)
    max_column = max((len(row) for row in matrix), default=len(headers))
    return LoadedTable(
        headers=headers,
        mapping=mapping,
        records=records,
        header_row=header_idx + 1,
        max_column=max(max_column, len(headers)),
        sheet_name=sheet_name,
    )


def load_template(path: Path) -> LoadedTable:
    suffix = path.suffix.lower()
    if suffix not in {".xlsx", ".xlsm", ".xls"}:
        raise ExcelReadError("Vorlage B ist nicht kompatibel.")
    try:
        if suffix == ".xls":
            df = pd.read_excel(path, header=None, dtype=object, engine="xlrd")
            matrix = [[_cell_raw(v) for v in row] for row in df.values.tolist()]
            matrix = _trim_matrix(matrix)
            header_idx, mapping, headers = _best_header(matrix, "target")
            loaded = _loaded_from_parts(
                headers, mapping, matrix, header_idx, path.stem, "B", MIN_TARGET_MAPPED_FIELDS
            )
        else:
            wb = load_workbook(path, data_only=False)
            try:
                ws, header_idx, mapping, headers, matrix = _pick_worksheet(wb, "target")
                loaded = _loaded_from_parts(
                    headers, mapping, matrix, header_idx, ws.title, "B", MIN_TARGET_MAPPED_FIELDS
                )
            finally:
                wb.close()
    except ExcelReadError:
        raise
    except Exception as exc:
        raise ExcelReadError("Die Excel-Datei konnte nicht gelesen werden.") from exc

    missing_core = [key for key in CORE_TARGET_FIELDS if key not in loaded.mapping]
    if missing_core:
        raise ExcelReadError("Vorlage B ist nicht kompatibel.")
    return loaded


@dataclass
class _StyleSnap:
    font: Any
    border: Any
    fill: Any
    number_format: str
    protection: Any
    alignment: Any
    height: float | None = None


def _snapshot_row(ws: Worksheet, row: int, max_column: int) -> dict[int, _StyleSnap]:
    snaps: dict[int, _StyleSnap] = {}
    height = ws.row_dimensions[row].height
    for col in range(1, max_column + 1):
        cell = ws.cell(row, col)
        snaps[col] = _StyleSnap(
            font=copy(cell.font),
            border=copy(cell.border),
            fill=copy(cell.fill),
            number_format=cell.number_format,
            protection=copy(cell.protection),
            alignment=copy(cell.alignment),
            height=height,
        )
    return snaps


def _apply_style(snap: _StyleSnap | None, target) -> None:
    if snap is None:
        return
    target.font = copy(snap.font)
    target.border = copy(snap.border)
    target.fill = copy(snap.fill)
    target.number_format = snap.number_format
    target.protection = copy(snap.protection)
    target.alignment = copy(snap.alignment)


def _cell_value_for_excel(value: Any, number_format: str) -> Any:
    if is_empty(value):
        return None
    if isinstance(value, datetime):
        return value
    if isinstance(value, date):
        return datetime(value.year, value.month, value.day)
    if isinstance(value, str) and any(token in number_format.lower() for token in ("d", "y", "m")) and "€" not in number_format:
        parsed = parse_date(value)
        if parsed is not None:
            return datetime(parsed.year, parsed.month, parsed.day)
    return value


def _unmerge_below(ws: Worksheet, header_row: int) -> None:
    ranges = [str(rng) for rng in ws.merged_cells.ranges if rng.min_row > header_row]
    for rng in ranges:
        ws.unmerge_cells(rng)


def write_workbook(
    template_path: Path,
    output_path: Path,
    records: list[CustomerRecord],
    template_info: LoadedTable,
    summary_rows: list[tuple[str, Any]],
    detail_rows: list[dict[str, Any]],
) -> None:
    suffix = template_path.suffix.lower()
    if suffix == ".xls":
        wb = Workbook()
        ws = wb.active
        ws.title = template_info.sheet_name or "Tabelle B"
        for col, header in enumerate(template_info.headers, start=1):
            ws.cell(1, col, header)
        header_row = 1
        max_column = max(template_info.max_column, len(template_info.headers))
        proto_snaps: dict[int, _StyleSnap] = {}
        mapping = template_info.mapping
    else:
        wb = load_workbook(template_path)
        ws = wb[template_info.sheet_name] if template_info.sheet_name in wb.sheetnames else wb.worksheets[0]
        header_row = template_info.header_row
        max_column = max(ws.max_column, template_info.max_column)
        proto_row = header_row + 1 if ws.max_row > header_row else header_row
        proto_snaps = _snapshot_row(ws, proto_row, max_column)
        mapping = template_info.mapping
        _unmerge_below(ws, header_row)
        if ws.max_row > header_row:
            ws.delete_rows(header_row + 1, ws.max_row - header_row)

    mapping_excel = {key: col + 1 for key, col in mapping.items() if FIELDS_BY_KEY[key].output}

    for index, record in enumerate(records):
        row_number = header_row + 1 + index
        sample = next(iter(proto_snaps.values()), None)
        if sample and sample.height:
            ws.row_dimensions[row_number].height = sample.height

        for col in range(1, max_column + 1):
            target = ws.cell(row_number, col)
            _apply_style(proto_snaps.get(col), target)

        for key, col in mapping_excel.items():
            snap = proto_snaps.get(col)
            number_format = snap.number_format if snap is not None else "General"
            value = _cell_value_for_excel(record.get(key), number_format)
            cell = ws.cell(row_number, col, value)
            _apply_style(snap, cell)
            if key == FieldKey.ZIP:
                cell.number_format = "@"
                if value is not None:
                    cell.value = str(value)

    last_data_row = header_row + max(len(records), 1)
    try:
        ws.auto_filter.ref = f"A{header_row}:{get_column_letter(max_column)}{last_data_row}"
    except Exception:
        pass

    _write_audit_sheet(wb, summary_rows, detail_rows)
    output_path.parent.mkdir(parents=True, exist_ok=True)
    wb.save(output_path)
    wb.close()


def _write_audit_sheet(
    wb: Workbook,
    summary_rows: list[tuple[str, Any]],
    detail_rows: list[dict[str, Any]],
) -> None:
    if "Importprüfung" in wb.sheetnames:
        del wb["Importprüfung"]
    ws = wb.create_sheet("Importprüfung")

    header_font = Font(name="Calibri", bold=True, size=12, color="FFFFFF")
    header_fill = PatternFill("solid", fgColor="1F4E79")
    thin = Border(
        left=Side(style="thin", color="D0D0D0"),
        right=Side(style="thin", color="D0D0D0"),
        top=Side(style="thin", color="D0D0D0"),
        bottom=Side(style="thin", color="D0D0D0"),
    )
    label_font = Font(name="Calibri", size=11)
    wrap = Alignment(vertical="center", wrap_text=True)

    ws["A1"] = "Prüfung"
    ws["B1"] = "Ergebnis"
    for col in (1, 2):
        cell = ws.cell(1, col)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(vertical="center")

    for idx, (label, value) in enumerate(summary_rows, start=2):
        ws.cell(idx, 1, label).font = label_font
        ws.cell(idx, 2, value).font = label_font
        ws.cell(idx, 1).border = thin
        ws.cell(idx, 2).border = thin

    start = len(summary_rows) + 4
    ws.cell(start, 1, "Datensatzdetails")
    ws.cell(start, 1).font = Font(name="Calibri", bold=True, size=12)

    detail_headers = [
        "Zeile",
        "Name",
        "Adressnr.",
        "Kennzeichen",
        "Fahrgestellnr.",
        "MATCH_STATUS",
        "Straße",
        "PLZ",
        "Ort",
        "Hinweise",
    ]
    header_row = start + 1
    for col, title in enumerate(detail_headers, start=1):
        cell = ws.cell(header_row, col, title)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin

    for ridx, detail in enumerate(detail_rows, start=header_row + 1):
        values = [
            detail.get("row"),
            detail.get("name"),
            detail.get("address_no"),
            detail.get("plate"),
            detail.get("vin"),
            detail.get("match_status"),
            detail.get("street"),
            detail.get("zip"),
            detail.get("city"),
            "; ".join(detail.get("reasons") or []),
        ]
        for col, value in enumerate(values, start=1):
            cell = ws.cell(ridx, col, value)
            cell.font = label_font
            cell.border = thin
            cell.alignment = wrap

    widths = [14, 36, 16, 18, 22, 24, 28, 12, 18, 50]
    for idx, width in enumerate(widths, start=1):
        ws.column_dimensions[get_column_letter(idx)].width = width
    ws.freeze_panes = "A2"
    ws.auto_filter.ref = f"A{header_row}:{get_column_letter(len(detail_headers))}{header_row + max(len(detail_rows), 1)}"


# Re-export for tests
__all__ = [
    "ExcelReadError",
    "LoadedTable",
    "load_source",
    "load_template",
    "write_workbook",
]