"""Parse the ``projects`` worksheet into planned lines (CONTRACT §5).

Pure ``openpyxl`` + stdlib. The month column is located by **header date match**
(not a fixed index), so the sheet can grow columns without breaking parsing.
"""

from __future__ import annotations

from datetime import date, datetime
from pathlib import Path
from typing import Optional, Union

from openpyxl import load_workbook

# Scope: only worksheet rows 1-138 are project lines; the EDI tail at 139+ is out.
FIRST_DATA_ROW = 1
LAST_DATA_ROW = 138
NAME_COL = 2  # column B
VALUE_COL = 6  # column F
# Header dates live in the first few rows; scan generously for robustness.
HEADER_SCAN_ROWS = 8
SHEET_NAME = "projects"

# Non-project label rows to skip (case-insensitive, trimmed). Any name starting
# with "total" is also skipped (handled separately).
_KNOWN_LABELS = {
    "sla total for each month",
    "sla + ad hoc + project + edi + finished projects",
    "target",
    "total",
    "grand total",
}

# Text header formats accepted in addition to real datetime cells.
_TEXT_FORMATS = (
    "%b-%y",   # Oct-26
    "%b-%Y",   # Oct-2026
    "%b %y",   # Oct 26
    "%b %Y",   # Oct 2026
    "%B %Y",   # October 2026
    "%B-%Y",   # October-2026
    "%Y-%m",   # 2026-10
    "%m/%Y",   # 10/2026
    "%m-%Y",   # 10-2026
)


def _header_to_date(value: object) -> Optional[date]:
    """Coerce a header cell to a ``date``, or ``None`` if it is not month-like."""
    if isinstance(value, datetime):
        return value.date()
    if isinstance(value, date):
        return value
    if isinstance(value, str):
        text = value.strip()
        if not text:
            return None
        for fmt in _TEXT_FORMATS:
            try:
                return datetime.strptime(text, fmt).date()
            except ValueError:
                continue
    return None


def _is_skippable_name(name: str) -> bool:
    lowered = name.lower()
    return lowered in _KNOWN_LABELS or lowered.startswith("total")


def _to_amount(value: object) -> Optional[float]:
    """Return a non-zero numeric amount, or ``None`` for blank/zero/non-numeric."""
    if value is None or isinstance(value, bool):
        return None
    if isinstance(value, (int, float)):
        amount = float(value)
    elif isinstance(value, str):
        text = value.strip().replace(",", "")
        if not text:
            return None
        try:
            amount = float(text)
        except ValueError:
            return None
    else:
        return None
    return None if amount == 0 else amount


def _find_month_column(ws, year: int, month: int) -> Optional[int]:
    """Scan the header rows for the column whose date matches year/month."""
    max_col = ws.max_column or 0
    scan_rows = min(HEADER_SCAN_ROWS, ws.max_row or HEADER_SCAN_ROWS)
    for row in range(1, scan_rows + 1):
        for col in range(1, max_col + 1):
            parsed = _header_to_date(ws.cell(row=row, column=col).value)
            if parsed and parsed.year == year and parsed.month == month:
                return col
    return None


def parse_planned_lines(
    path: Union[str, Path], year: int, month: int
) -> list["PlannedLine"]:
    """Parse the ``projects`` sheet for ``year``/``month`` into planned lines.

    Args:
        path: Path to the ``.xlsx`` workbook.
        year: Reconciled year (e.g. 2026).
        month: Reconciled month 1-12 (e.g. 10 for October).

    Returns:
        Kept :class:`PlannedLine` objects — blank/label/subtotal rows and
        zero/blank/non-numeric month amounts are dropped.

    Raises:
        ValueError: if the ``projects`` sheet or the month column is missing.
    """
    # Local import keeps the module import graph obvious; no heavy deps involved.
    from .models import PlannedLine

    wb = load_workbook(filename=str(path), data_only=True, read_only=True)
    try:
        if SHEET_NAME in wb.sheetnames:
            ws = wb[SHEET_NAME]
        else:  # be forgiving if the tab was renamed/cased differently
            match = next(
                (n for n in wb.sheetnames if n.strip().lower() == SHEET_NAME),
                None,
            )
            if match is None:
                raise ValueError(
                    f"Workbook has no '{SHEET_NAME}' sheet "
                    f"(found: {wb.sheetnames})"
                )
            ws = wb[match]

        month_col = _find_month_column(ws, year, month)
        if month_col is None:
            raise ValueError(
                f"No header column matches {year}-{month:02d} in the "
                f"'{SHEET_NAME}' sheet"
            )

        last_row = min(LAST_DATA_ROW, ws.max_row or LAST_DATA_ROW)
        lines: list[PlannedLine] = []
        for row in range(FIRST_DATA_ROW, last_row + 1):
            raw_name = ws.cell(row=row, column=NAME_COL).value
            if raw_name is None:
                continue
            name = " ".join(str(raw_name).split())  # trim + collapse newlines
            if not name or _is_skippable_name(name):
                continue

            amount = _to_amount(ws.cell(row=row, column=month_col).value)
            if amount is None:
                continue

            project_value = _to_amount(ws.cell(row=row, column=VALUE_COL).value)
            lines.append(
                PlannedLine(
                    source_row=row,
                    client_name=name,
                    planned_amount=amount,
                    project_value=project_value,
                )
            )
        return lines
    finally:
        wb.close()


# Re-export for callers importing the dataclass alongside the parser.
from .models import PlannedLine  # noqa: E402  (placed last to avoid cycles)