from __future__ import annotations

import argparse
from pathlib import Path

import yaml

ROOT = Path(__file__).resolve().parents[1]
CONTRACT = ROOT / "spec" / "postgresql_history_contract.yaml"
DEFAULT_SQL = ROOT / "Temp" / "postgresql_history_schema_v1.sql"
DEFAULT_JSON = ROOT / "Temp" / "postgresql_history_schema_v1.json"


def _columns(domain: dict) -> list[str]:
    cols = domain.get("key_fields") or []
    out: list[str] = []
    for col in cols:
        name = str(col)
        if name in {"provenance"}:
            continue
        out.append(name)
    return out


def _table_name(domain_name: str) -> str:
    return domain_name


def main() -> int:
    ap = argparse.ArgumentParser()
    ap.add_argument("--contract", default=str(CONTRACT))
    ap.add_argument("--sql-out", default=str(DEFAULT_SQL))
    ap.add_argument("--json-out", default=str(DEFAULT_JSON))
    args = ap.parse_args()

    contract_path = Path(args.contract)
    data = yaml.safe_load(contract_path.read_text(encoding="utf-8"))
    domains = data.get("domains") or {}

    sql_lines = [
        "-- PostgreSQL history-first schema",
        "-- generated from spec/postgresql_history_contract.yaml",
        "",
        "CREATE SCHEMA IF NOT EXISTS engine_history;",
        ""
    ]
    table_defs: dict[str, dict[str, object]] = {}

    for domain_name, domain in domains.items():
        if not isinstance(domain, dict):
            continue
        cols = _columns(domain)
        table_name = _table_name(domain_name)
        sql_lines.append(f"CREATE TABLE IF NOT EXISTS engine_history.{table_name} (")
        sql_lines.append("    id BIGSERIAL PRIMARY KEY,")
        for col in cols:
            sql_lines.append(f"    {col} TEXT NOT NULL,")
        sql_lines.append("    provenance JSONB NOT NULL DEFAULT '{}'::jsonb,")
        sql_lines.append("    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()")
        sql_lines.append(");")
        sql_lines.append("")
        sql_lines.append(f"CREATE INDEX IF NOT EXISTS idx_{table_name}_created_at ON engine_history.{table_name} (created_at DESC);")
        sql_lines.append("")
        table_defs[table_name] = {"columns": cols, "description": domain.get("description", "")}

    sql_text = "\n".join(sql_lines).rstrip() + "\n"
    sql_out = Path(args.sql_out)
    json_out = Path(args.json_out)
    sql_out.parent.mkdir(parents=True, exist_ok=True)
    sql_out.write_text(sql_text, encoding="utf-8")
    json_out.write_text(
        yaml.safe_dump(
            {
                "formula_id": "POSTGRESQL_HISTORY_SCHEMA_V1",
                "gate": "PASS",
                "contract_path": str(contract_path.relative_to(ROOT)),
                "tables": table_defs,
                "sql_out": str(sql_out.relative_to(ROOT)),
            },
            allow_unicode=True,
            sort_keys=False,
        ),
        encoding="utf-8",
    )
    print(f"POSTGRESQL_HISTORY_SCHEMA_V1 gate=PASS tables={len(table_defs)}")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())