#!/usr/bin/env python3
"""Validate portfolio coverage, aggregate reconciliation, privacy, and delivery files."""

from __future__ import annotations

import csv
import json
from pathlib import Path


ROOT = Path(__file__).resolve().parent


def rows(name: str) -> list[dict[str, str]]:
    with (ROOT / name).open(encoding="utf-8-sig", newline="") as handle:
        return list(csv.DictReader(handle))


def integer(value: str) -> int:
    return int(float(value or 0))


def main() -> None:
    master = rows("project-master.csv")
    cards = rows("project-usage-cards.csv")
    central = rows("central-project-summary.csv")
    core = rows("db-project-status.csv")
    periods = rows("db-order-periods.csv")
    channels = rows("db-channel-mix.csv")
    meals = rows("db-meal-mix.csv")
    features = rows("db-feature-activity.csv")
    reconciliation = rows("source-reconciliation.csv")

    assert len(master) == 28 == len(cards)
    assert len({row["project_id"] for row in master}) == 28
    assert len({row["project_name"] for row in master}) == 28
    assert sum(row["central_registered"] == "yes" for row in master) == 20
    assert sum(row["db_registry_registered"] == "yes" for row in master) == 17
    assert len(central) == 20 and len(core) == 17
    assert sum(row["data_status"] == "live" for row in core) == 12
    assert sum(row["data_status"] == "unavailable" for row in core) == 5

    core_live = {row["project_key"]: row for row in core if row["data_status"] == "live"}
    current = {row["project_key"]: row for row in periods if row["period"] == "近30天"}
    previous = {row["project_key"]: row for row in periods if row["period"] == "前30天"}
    assert set(core_live) == set(current) == set(previous)
    for key, row in core_live.items():
        assert integer(row["orders30"]) == integer(current[key]["orders"])

    active = {key for key, row in core_live.items() if integer(row["orders30"]) > 0}
    assert len(active) == 7
    assert sum(integer(row["orders30"]) for row in core_live.values()) == 445_164
    assert sum(integer(row["current30_orders"]) for row in central) == 203_990

    for key in active:
        expected = integer(current[key]["orders"])
        assert sum(integer(row["orders"]) for row in channels if row["project_key"] == key and row["period"] == "近30天") == expected
        assert sum(integer(row["orders"]) for row in meals if row["project_key"] == key and row["period"] == "近30天") == expected
    assert len(features) == 12 * 20
    assert all(row["table_state"] in {"queried", "table_missing", "query_failed"} for row in features)

    assert next(row for row in reconciliation if row["scope"] == "历史线程里的28")["meaning"].endswith("不是客户项目数")
    artifact = json.loads((ROOT / "artifact.json").read_text(encoding="utf-8"))
    assert artifact["manifest"]["title"] == "28个智慧食堂项目：哪些功能在真正使用"
    html_text = (ROOT / "smart-canteen-28-project-usage.html").read_text(encoding="utf-8")
    assert artifact["manifest"]["title"] in html_text
    assert "Executive Summary" in html_text
    assert "28个项目逐项卡片" in html_text
    assert html_text.count("<tr>") >= 28
    assert "password" not in html_text.lower()
    assert "accesskey" not in html_text.lower()
    assert "手机号" not in html_text
    assert (ROOT / "smart-canteen-28-project-usage-analysis.ipynb").exists()

    print("PASS: 28-candidate lineage, 20/17/12/7 coverage, order reconciliation, privacy, and report delivery validated")


if __name__ == "__main__":
    main()