#!/usr/bin/env python3
"""Validate WEHAGO voucher projection display and source-row invariants."""

from __future__ import annotations

import argparse
import sqlite3
from collections import Counter, defaultdict
from pathlib import Path


DEFAULT_DB = Path("/home/b17301/intranet-runtime/db/data.db")

FIXED_CASES = (
    ("2025-01-01 50004", "01-01", "50004"),
    ("2025-01-05 50003", "01-05", "50003"),
    ("2025-01-05 50004", "01-05", "50004"),
    ("2025-01-07 50013", "01-07", "50013"),
    ("2025-01-10 00036", "01-10", "00036"),
    ("2025-01-10 00048", "01-10", "00048"),
    ("2025-01-10 00056", "01-10", "00056"),
    ("2025-01-10 00058", "01-10", "00058"),
    ("2025-01-10 00059", "01-10", "00059"),
    ("2025-01-10 00061", "01-10", "00061"),
    ("2025-12-23 50031", "12-23", "50031"),
    ("2025-12-23 50032", "12-23", "50032"),
)

SOURCE_CASES = {"", "DIRECT_MATCH_CANDIDATE", "SETTLEMENT_BRIDGE_CANDIDATE"}
CONTEXT_CASES = {
    "ERP_CONTEXT_ROW",
    "UNASSIGNED_ERP_ROW",
    "SHARED_ALLOCATION_CANDIDATE",
    "SHARED_BUNDLE_PAYMENT_CANDIDATE",
}


def latest_signature(conn: sqlite3.Connection, year: int) -> str:
    row = conn.execute(
        """
        SELECT signature
        FROM wehago_compare_query_rows
        WHERE start_year = ? AND end_year = ?
        ORDER BY updated_at DESC
        LIMIT 1
        """,
        (year, year),
    ).fetchone()
    if row is None:
        raise SystemExit(f"No projection rows found for {year}.")
    return str(row["signature"])


def is_source_row(row: sqlite3.Row) -> bool:
    matched_case = (row["matched_case"] or "").strip()
    return (
        bool((row["ledger_account_name"] or "").strip())
        and bool((row["voucher_account_name"] or "").strip())
        and matched_case in SOURCE_CASES
        and matched_case not in CONTEXT_CASES
    )


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("--db", default=str(DEFAULT_DB))
    parser.add_argument("--year", type=int, default=2025)
    args = parser.parse_args()

    conn = sqlite3.connect(args.db)
    conn.row_factory = sqlite3.Row
    signature = latest_signature(conn, args.year)
    print("signature", signature)

    rows = list(
        conn.execute(
            """
            SELECT *
            FROM wehago_compare_query_rows
            WHERE start_year = ? AND end_year = ? AND signature = ?
            """,
            (args.year, args.year, signature),
        )
    )
    by_status_group: dict[tuple[str, int], list[sqlite3.Row]] = defaultdict(list)
    for row in rows:
        by_status_group[(row["status_key"], int(row["group_index"]))].append(row)

    status_counts = Counter(status for status, _ in by_status_group)
    print("status_groups", dict(sorted(status_counts.items())))

    for status in ("voucher_matched", "erp_voucher_matched"):
        source_rows = [row for row in rows if row["status_key"] == status and is_source_row(row)]
        ledger_keys = Counter(row["ledger_row_key"] for row in source_rows if row["ledger_row_key"])
        voucher_keys = Counter(row["voucher_row_key"] for row in source_rows if row["voucher_row_key"])
        one_side_rows = [
            row
            for row in rows
            if row["status_key"] == status
            and (bool((row["ledger_account_name"] or "").strip()) != bool((row["voucher_account_name"] or "").strip()))
        ]
        print(
            status,
            {
                "source_rows": len(source_rows),
                "missing_source_keys": sum(1 for row in source_rows if not row["ledger_row_key"] or not row["voucher_row_key"]),
                "duplicate_ledger_extra_rows": sum(count - 1 for count in ledger_keys.values() if count > 1),
                "duplicate_voucher_extra_rows": sum(count - 1 for count in voucher_keys.values() if count > 1),
                "one_side_display_rows": len(one_side_rows),
            },
        )

    final_wehago_keys = {
        row["ledger_row_key"]
        for row in rows
        if row["status_key"] in {"voucher_matched", "voucher_excepted"}
        and row["ledger_row_key"]
    }
    shadow_rows = [
        row
        for row in rows
        if row["status_key"] in {"ledger_only", "amount_mismatch"}
        and row["ledger_row_key"]
        and row["ledger_row_key"] in final_wehago_keys
    ]
    print("standard_shadow_rows_for_final_wehago", len(shadow_rows))

    final_statuses = ("voucher_matched", "voucher_recheck", "voucher_unmatched", "voucher_excepted")
    identity_statuses: dict[tuple[int, str, str], set[str]] = defaultdict(set)
    group_summaries = {
        (row["status_key"], int(row["group_index"])): row
        for row in conn.execute(
            """
            SELECT status_key, group_index, fiscal_year, ledger_date, voucher_no
            FROM wehago_compare_query_groups
            WHERE start_year = ? AND end_year = ? AND signature = ?
            """,
            (args.year, args.year, signature),
        )
    }
    for (status, group_index), group_rows in by_status_group.items():
        if status not in final_statuses or not group_rows:
            continue
        summary = group_summaries.get((status, group_index)) or group_rows[0]
        identity_statuses[
            (
                int(summary["fiscal_year"] or 0),
                str(summary["ledger_date"] or "").strip(),
                str(summary["voucher_no"] or "").strip(),
            )
        ].add(status)
    cross_status_identities = {
        identity: statuses
        for identity, statuses in identity_statuses.items()
        if len(statuses) > 1
    }
    print("cross_status_voucher_identities", len(cross_status_identities))

    print("fixed_cases")
    for label, ledger_date, voucher_no in FIXED_CASES:
        matches = [
            group_rows
            for (_status, _group_index), group_rows in by_status_group.items()
            if any(row["ledger_date"] == ledger_date and row["voucher_no"] == voucher_no for row in group_rows)
        ]
        summary = []
        for group_rows in matches:
            first = group_rows[0]
            source_count = sum(1 for row in group_rows if is_source_row(row))
            one_side = sum(
                1
                for row in group_rows
                if bool((row["ledger_account_name"] or "").strip()) != bool((row["voucher_account_name"] or "").strip())
            )
            reasons = sorted({row["review_reason"] for row in group_rows if row["review_reason"]})
            summary.append(
                f"{first['status_key']}#{first['group_index']}:rows={len(group_rows)} source={source_count} one_side={one_side} reason={' | '.join(reasons[:2])}"
            )
        print(label, " ; ".join(summary) if summary else "MISSING")


if __name__ == "__main__":
    main()