#!/usr/bin/env python3
"""Verify the cutover DDL tables exist and match the expected schema on
prod (easyperl_inventory / vdb1b). Read-only — no INSERT/UPDATE/DELETE,
just SELECT against information_schema and SHOW CREATE TABLE.

Run from the site-ng/ root:
    .venv/bin/python scripts/verify-cutover-ddl.py

Reads prod connection creds from scanner-ng/config.ini (single source of
truth on this box). Same pattern the deploy script uses.

Exit code:
    0  — all checks pass
    1  — one or more checks failed (details printed)
    2  — couldn't connect / config problem

Re-runnable. Safe to run at any time, including with site-ng live.
"""

from __future__ import annotations

import configparser
import sys
from pathlib import Path

# ---------- expected schema ----------
# What each table SHOULD have. Kept compact: column list, index list,
# FK list. Compared via set membership so order of indexes / columns
# doesn't matter, only presence + key properties.
EXPECTED = {
    "app_user": {
        "columns": {"id", "email", "created", "last_login"},
        "unique_keys": {"uq_email"},          # excluding PRIMARY
        "indexes": set(),                     # non-unique secondary keys
        "fks": {},                            # {fk_name: (col, ref_table, ref_col, on_delete)}
    },
    "magic_link": {
        "columns": {"id", "user_id", "email", "token", "code",
                    "failed_attempts", "created", "expires_at",
                    "consumed_at"},
        "unique_keys": {"uq_token"},
        "indexes": {"idx_user", "idx_email", "idx_code"},
        "fks": {
            "fk_link_user": ("user_id", "app_user", "id", "CASCADE"),
        },
    },
    "build": {
        "columns": {"id", "code", "owner_user_id", "parent_build_id",
                    "root_build_id", "is_latest", "title", "notes",
                    "votes", "status", "created", "updated"},
        "unique_keys": {"uq_code"},
        "indexes": {"idx_owner", "idx_parent", "idx_root",
                    "idx_owner_latest"},
        "fks": {
            "fk_build_owner": ("owner_user_id", "app_user", "id",
                               "SET NULL"),
        },
    },
    "build_item": {
        "columns": {"id", "build_id", "parts_id", "qty", "type",
                    "company", "description", "url", "img",
                    "price_at_add", "position", "added"},
        "unique_keys": set(),
        "indexes": {"idx_build", "idx_parts"},
        "fks": {
            "fk_item_build": ("build_id", "build", "id", "CASCADE"),
        },
    },
    # Materialized per-type nav counts + stockometer denominator, refreshed
    # from parts.type by the scanner (scanner-ng/ngscan/category_counts.py).
    # PK(type) only — no secondary keys or FKs. Already created on prod.
    "category_count": {
        "columns": {"type", "in_stock", "total", "updated_at"},
        "unique_keys": set(),     # PRIMARY KEY (type) excluded from this check
        "indexes": set(),
        "fks": {},
    },
}


def load_prod_creds():
    """Pull connection params from scanner-ng via its own settings loader.
    scanner-ng/config.ini isn't a plain INI — we shell out to ngscan's
    venv to use the loader that knows how to parse it (same pattern
    deploy.sh uses)."""
    import subprocess
    # .../sites/arpartsfinder/web/scripts/verify-cutover-ddl.py
    #   parents[0]=scripts [1]=web [2]=arpartsfinder [3]=sites [4]=repo root
    repo_root = Path(__file__).resolve().parents[4]
    scanner_root = repo_root / "scanner-ng"
    if not (scanner_root / "config.ini").exists():
        sys.exit(f"ERROR: scanner-ng config not found at "
                 f"{scanner_root}/config.ini")
    out = subprocess.run(
        [str(scanner_root / ".venv" / "bin" / "python"),
         "-c",
         "from ngscan import settings; c = settings.load_db_config(); "
         "print(c.host); print(c.port); print(c.name); "
         "print(c.user); print(c.password)"],
        cwd=str(scanner_root),
        env={**__import__("os").environ, "NGSCAN_USE_LEGACY_CONFIG": "1"},
        capture_output=True, text=True, check=True)
    lines = out.stdout.strip().split("\n")
    return {
        "host": lines[0], "port": int(lines[1]), "db": lines[2],
        "user": lines[3], "password": lines[4],
    }


def inspect_table(cur, db_name: str, table: str):
    """Return dicts of what's actually in the DB for this table."""
    # 1. existence
    cur.execute(
        "SELECT 1 FROM information_schema.tables "
        "WHERE table_schema=%s AND table_name=%s", (db_name, table))
    if not cur.fetchone():
        return None

    # 2. columns
    cur.execute(
        "SELECT column_name FROM information_schema.columns "
        "WHERE table_schema=%s AND table_name=%s", (db_name, table))
    columns = {r[0] for r in cur.fetchall()}

    # 3. indexes (separated by uniqueness; PRIMARY excluded)
    cur.execute(
        "SELECT index_name, non_unique FROM information_schema.statistics "
        "WHERE table_schema=%s AND table_name=%s "
        "AND index_name != 'PRIMARY' GROUP BY index_name, non_unique",
        (db_name, table))
    unique_keys, indexes = set(), set()
    for name, non_unique in cur.fetchall():
        (indexes if non_unique else unique_keys).add(name)

    # 4. foreign keys + their ON DELETE rule
    cur.execute(
        "SELECT k.constraint_name, k.column_name, k.referenced_table_name, "
        "       k.referenced_column_name, r.delete_rule "
        "FROM information_schema.key_column_usage k "
        "JOIN information_schema.referential_constraints r "
        "  ON k.constraint_schema=r.constraint_schema "
        "  AND k.constraint_name=r.constraint_name "
        "WHERE k.table_schema=%s AND k.table_name=%s "
        "  AND k.referenced_table_name IS NOT NULL",
        (db_name, table))
    fks = {row[0]: (row[1], row[2], row[3], row[4])
           for row in cur.fetchall()}

    return {"columns": columns, "unique_keys": unique_keys,
            "indexes": indexes, "fks": fks}


def diff_set(label: str, expected: set, actual: set) -> list[str]:
    issues = []
    missing = expected - actual
    extra = actual - expected
    if missing:
        issues.append(f"  ✗ missing {label}: {', '.join(sorted(missing))}")
    if extra:
        issues.append(f"  ℹ extra {label} (probably fine): "
                      f"{', '.join(sorted(extra))}")
    return issues


def check_table(cur, db_name: str, table: str, expected: dict) -> tuple[bool, int]:
    """Return (ok, row_count). Print findings."""
    actual = inspect_table(cur, db_name, table)
    print(f"=== {table} ===")
    if actual is None:
        print("  ✗ TABLE DOES NOT EXIST")
        return False, 0

    print("  ✓ exists")
    issues = []
    issues += diff_set("columns",      expected["columns"],     actual["columns"])
    issues += diff_set("unique keys",  expected["unique_keys"], actual["unique_keys"])
    issues += diff_set("indexes",      expected["indexes"],     actual["indexes"])

    # FK comparison — check each expected FK has the right ref + rule
    for fk_name, (col, ref_t, ref_c, on_del) in expected["fks"].items():
        if fk_name not in actual["fks"]:
            issues.append(f"  ✗ missing FK: {fk_name}")
            continue
        a_col, a_ref_t, a_ref_c, a_on_del = actual["fks"][fk_name]
        if (a_col, a_ref_t, a_ref_c) != (col, ref_t, ref_c):
            issues.append(
                f"  ✗ {fk_name}: column/ref mismatch — "
                f"expected {col}→{ref_t}.{ref_c}, "
                f"got {a_col}→{a_ref_t}.{a_ref_c}")
        if a_on_del.upper().replace(" ", "") != on_del.upper().replace(" ", ""):
            issues.append(
                f"  ✗ {fk_name}: ON DELETE mismatch — "
                f"expected {on_del}, got {a_on_del}")
    extra_fks = set(actual["fks"]) - set(expected["fks"])
    if extra_fks:
        issues.append(f"  ℹ extra FKs (probably fine): {', '.join(sorted(extra_fks))}")

    if issues:
        for line in issues:
            print(line)
    else:
        print(f"  ✓ {len(expected['columns'])} columns, "
              f"{len(expected['unique_keys'])} unique key(s), "
              f"{len(expected['indexes'])} index(es), "
              f"{len(expected['fks'])} FK(s) — all match")

    # Row count (informational only)
    cur.execute(f"SELECT COUNT(*) FROM `{table}`")
    n = cur.fetchone()[0]
    print(f"  ℹ {n} row(s)")
    print()

    has_real_issue = any(i.lstrip().startswith("✗") for i in issues)
    return (not has_real_issue), n


def main():
    try:
        import pymysql
    except ImportError:
        sys.exit("ERROR: pymysql not installed. Run from .venv: "
                 ".venv/bin/python scripts/verify-cutover-ddl.py")

    creds = load_prod_creds()
    print(f"Connecting to PROD: {creds['user']}@{creds['host']}:"
          f"{creds['port']}/{creds['db']}")
    print()

    try:
        conn = pymysql.connect(
            host=creds["host"], port=creds["port"],
            user=creds["user"], password=creds["password"],
            db=creds["db"], read_timeout=10, connect_timeout=10)
    except pymysql.MySQLError as e:
        sys.exit(f"ERROR: connect failed: {e}")

    cur = conn.cursor()
    results = []
    for table, expected in EXPECTED.items():
        ok, n = check_table(cur, creds["db"], table, expected)
        results.append((table, ok, n))

    cur.close(); conn.close()

    ok_count = sum(1 for _, ok, _ in results if ok)
    print("=" * 40)
    print(f"SUMMARY: {ok_count}/{len(results)} tables match expected schema")
    if ok_count == len(results):
        print("All cutover DDL is in place. Auth + saved-build features "
              "will work on staging.")
        return 0
    return 1


if __name__ == "__main__":
    sys.exit(main())
