#!/usr/bin/env python3
"""t2_vip_registry_patch.py — VIP 票 registry 落地 D1：core DDL ＋ 現有 DB 遷移（唔含指令）。

依據 VIP_TICKET_SPEC_v1（owner seq2964/2965/2966 批覆 seq2970）。
本支**只做結構**：加一張 vip_tickets 表 ＋ 「全庫最多一張 BOUND 票」嘅 partial unique index，
（2026-10-02 owner 批 D2.1 之後加咗三欄：bot_nonce／bot_nonce_issued_at／bot_verified_at，
 用嚟做「bot 擁有權證明」——token 永不交出，改為用 bot 發一條帶 nonce 嘅訊息嚟證明控制權。）
並且保證「遷移出嚟嘅 DB」同「patched core 全新建嘅 DB」schema sha **完全一致**（同 seats100 嗰次一樣嘅做法）。
vip-issue／vip-revoke／vip-handover／vip-bind 指令係 D2，另一支工具，唔喺呢度。

為乜要分開：加表會改 sqlite_schema_sha256，係一個需要停機＋改 policy pin 嘅變更窗口；
指令層純 Python，唔改 schema，可以之後熱交付。

流程（--apply）：
  1. 唯讀 preflight：core 錨點、DB application_id/user_version/integrity、vip_tickets 係咪已存在
  2. 由 patched core 嘅 DDL 喺記憶體建一個 reference DB → 算出目標 SCHEMA_SHA
  3. VACUUM INTO 副本試跑遷移＋負樣（兩張 BOUND 要被 unique index 拒）→ 全綠先做正本
  4. VACUUM INTO 正式備份 → 改 core → 改正本 DB → 再算 sha，三邊（reference／migrated）必須一致
  5. 任何一步失敗：core 由 .bak 還原、DB 由備份 copy2 還原
rc 0 OK／1 前置唔符／2 已經做咗（冪等）／3 FATAL（已還原）
"""
from __future__ import annotations

import argparse
import hashlib
import json
import py_compile
import re
import shutil
import sqlite3
import sys
import tempfile
import time
from pathlib import Path

DEFAULT_DB = "/var/lib/ai-research-council/council/state.sqlite3"
ANCHOR_AUDIT = """            CREATE TABLE IF NOT EXISTS audit_events ("""
ANCHOR_TABLES = "\"enrollment_flags\", \"session_flags\","

VIP_DDL = """
            CREATE TABLE IF NOT EXISTS vip_tickets (
              ticket_id TEXT PRIMARY KEY,
              epoch INTEGER NOT NULL,
              status TEXT NOT NULL CHECK(status IN ('ISSUED','BOUND','REVOKED')),
              intended_slot TEXT,
              holder_session_id TEXT,
              holder_agent_id TEXT,
              holder_slot TEXT,
              bot_sender_id TEXT,
              bot_nonce TEXT,
              bot_nonce_issued_at INTEGER,
              bot_verified_at INTEGER,
              created_at INTEGER NOT NULL,
              bound_at INTEGER,
              revoked_at INTEGER,
              reason TEXT
            );
            CREATE UNIQUE INDEX IF NOT EXISTS vip_one_bound
              ON vip_tickets(status) WHERE status='BOUND';
"""


def canonical_json(value) -> bytes:
    return json.dumps(value, sort_keys=True, separators=(",", ":"),
                      ensure_ascii=False).encode("utf-8")


def schema_sha(connection: sqlite3.Connection) -> str:
    rows = [dict(row) for row in connection.execute(
        "SELECT type,name,tbl_name,sql FROM sqlite_master "
        "WHERE sql IS NOT NULL AND name NOT LIKE 'sqlite_%' ORDER BY type,name")]
    return hashlib.sha256(canonical_json(rows)).hexdigest()


def open_db(path: Path) -> sqlite3.Connection:
    connection = sqlite3.connect(str(path))
    connection.row_factory = sqlite3.Row
    return connection


def extract_ddl(core_text: str) -> str:
    """抽出 executescript 入面成段 DDL（用嚟建 reference DB）。"""
    match = re.search(r'self\.db\.executescript\(\s*"""(.*?)"""\s*\)', core_text, re.S)
    if not match:
        raise RuntimeError("搵唔到 core 嘅 executescript DDL 區塊")
    return match.group(1)


def reference_sha(ddl: str) -> str:
    with tempfile.TemporaryDirectory() as workdir:
        path = Path(workdir) / "ref.sqlite3"
        connection = open_db(path)
        connection.executescript(ddl)
        connection.commit()
        value = schema_sha(connection)
        connection.close()
        return value


def migrate(connection: sqlite3.Connection) -> None:
    connection.executescript(VIP_DDL)   # 逐字同 core DDL 一樣，sqlite_master 文字先會一致
    connection.commit()


def negative_test(path: Path) -> list[str]:
    """喺副本上試負樣：兩張 BOUND 票要被 unique index 拒。"""
    notes = []
    connection = open_db(path)
    now = int(time.time())
    connection.execute(
        "INSERT INTO vip_tickets(ticket_id,epoch,status,created_at) VALUES('t1',1,'BOUND',?)", (now,))
    try:
        connection.execute(
            "INSERT INTO vip_tickets(ticket_id,epoch,status,created_at) VALUES('t2',1,'BOUND',?)", (now,))
        notes.append("BAD：第二張 BOUND 票竟然插得入")
    except sqlite3.IntegrityError as error:
        notes.append("OK：第二張 BOUND 被拒（%s）" % error)
    connection.execute(
        "INSERT INTO vip_tickets(ticket_id,epoch,status,created_at) VALUES('t3',1,'ISSUED',?)", (now,))
    connection.execute(
        "INSERT INTO vip_tickets(ticket_id,epoch,status,created_at) VALUES('t4',1,'REVOKED',?)", (now,))
    notes.append("OK：ISSUED／REVOKED 可以多張並存")
    try:
        connection.execute(
            "INSERT INTO vip_tickets(ticket_id,epoch,status,created_at) VALUES('t5',1,'HOST',?)", (now,))
        notes.append("BAD：非法 status 竟然收")
    except sqlite3.IntegrityError:
        notes.append("OK：非法 status 被 CHECK 拒")
    connection.rollback()
    connection.close()
    return notes


def fail(message: str, code: int = 1) -> int:
    print("FATAL：%s" % message, file=sys.stderr)
    return code


def main() -> int:
    parser = argparse.ArgumentParser(description=__doc__,
                                     formatter_class=argparse.RawDescriptionHelpFormatter)
    parser.add_argument("--root", default="/opt/ai-research-council")
    parser.add_argument("--db", default=DEFAULT_DB)
    parser.add_argument("--backup-dir", default=None)
    parser.add_argument("--apply", action="store_true")
    args = parser.parse_args()

    root = Path(args.root)
    core = root / "council_core.py"
    db_path = Path(args.db)
    if not core.is_file():
        return fail("搵唔到 %s" % core)
    if not db_path.is_file():
        return fail("搵唔到 DB %s" % db_path)

    core_text = core.read_text(encoding="utf-8")
    patched_core = "vip_tickets" in core_text

    connection = open_db(db_path)
    try:
        app_id = connection.execute("PRAGMA application_id").fetchone()[0]
        user_version = connection.execute("PRAGMA user_version").fetchone()[0]
        integrity = connection.execute("PRAGMA integrity_check").fetchone()[0]
        has_table = connection.execute(
            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='vip_tickets'"
        ).fetchone()[0] == 1
        before_sha = schema_sha(connection)
    finally:
        connection.close()

    print("== preflight ==")
    print("core            : %s（已含 vip_tickets：%s）" % (core, patched_core))
    print("db              : %s" % db_path)
    print("application_id  : %s（要 1095910211）" % app_id)
    print("user_version    : %s（要 1）" % user_version)
    print("integrity_check : %s" % integrity)
    print("vip_tickets 已存在：%s" % has_table)
    print("現時 SCHEMA_SHA : %s" % before_sha)

    if app_id != 1095910211 or user_version != 1 or integrity != "ok":
        return fail("DB 前置唔符", 1)
    if patched_core and has_table:
        print("\nALREADY：core 同 DB 都已經有 vip_tickets。")
        return 2

    anchors = [("core audit_events DDL 錨", core_text.count(ANCHOR_AUDIT), 1),
               ("core TABLES frozenset 錨", core_text.count(ANCHOR_TABLES), 1)]
    print("\n== core 錨點 ==")
    ok = True
    for label, hits, want in anchors:
        good = hits == want
        ok = ok and good
        print("  [%s] %-28s 命中 %d（要求 %d）" % ("OK " if good else "BAD", label, hits, want))
    if not ok:
        return fail("core 錨點唔符（live core 可能同鏡像唔同），冇郁嘢", 1)

    # 目標 schema：喺 patched DDL 上建 reference DB
    ddl = extract_ddl(core_text)
    if "vip_tickets" not in ddl:
        ddl = ddl.rstrip() + "\n" + VIP_DDL
    target_sha = reference_sha(ddl)
    print("\n目標 SCHEMA_SHA（patched core 全新建）：%s" % target_sha)

    # 副本試跑
    with tempfile.TemporaryDirectory() as workdir:
        probe = Path(workdir) / "probe.sqlite3"
        connection = open_db(db_path)
        connection.execute("VACUUM INTO ?", (str(probe),))
        connection.close()
        probe_connection = open_db(probe)
        migrate(probe_connection)
        probe_sha = schema_sha(probe_connection)
        probe_connection.close()
        print("副本遷移後 SCHEMA_SHA：%s" % probe_sha)
        for note in negative_test(probe):
            print("  %s" % note)
        if probe_sha != target_sha:
            return fail("副本遷移後同 reference 唔一致（%s vs %s）——live schema 可能有額外物件，停低問"
                        % (probe_sha, target_sha), 1)
        if any(note.startswith("BAD") for note in negative_test(probe)):
            return fail("負樣測試唔過", 1)

    print("\nCHECK_OK（副本試跑全綠，schema 等價已證）")
    if not args.apply:
        print("（唯讀；要落地請加 --apply）")
        return 0

    stamp = time.strftime("%Y%m%d-%H%M%S")
    backup_dir = Path(args.backup_dir) if args.backup_dir else db_path.parent
    backup = backup_dir / (db_path.name + ".pre-vip-%s.bak" % stamp)
    core_backup = core.with_suffix(".py.bak-vip-%s" % stamp)

    new_core = core_text
    if not patched_core:
        new_core = new_core.replace(ANCHOR_AUDIT, VIP_DDL.rstrip("\n") + "\n" + ANCHOR_AUDIT, 1)
        new_core = new_core.replace(ANCHOR_TABLES, ANCHOR_TABLES + ' "vip_tickets",', 1)
        with tempfile.TemporaryDirectory() as workdir:
            probe = Path(workdir) / "core.py"
            probe.write_text(new_core, encoding="utf-8")
            try:
                py_compile.compile(str(probe), doraise=True)
            except py_compile.PyCompileError as error:
                return fail("預演 compile 失敗：%s" % error, 3)
        print("預演 compile PASS")

    connection = open_db(db_path)
    connection.execute("VACUUM INTO ?", (str(backup),))
    connection.close()
    backup.chmod(0o600)
    print("DB 備份：%s" % backup)

    try:
        if not patched_core:
            shutil.copy2(core, core_backup)
            core.write_text(new_core, encoding="utf-8")
            print("core 已改（備份 %s）" % core_backup.name)
        connection = open_db(db_path)
        migrate(connection)
        after_sha = schema_sha(connection)
        integrity = connection.execute("PRAGMA integrity_check").fetchone()[0]
        tables = connection.execute(
            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='vip_tickets'"
        ).fetchone()[0]
        connection.close()
        if integrity != "ok" or tables != 1 or after_sha != target_sha:
            raise RuntimeError("遷移後覆檢唔過（integrity=%s table=%s sha=%s）"
                               % (integrity, tables, after_sha))
    except Exception as error:   # noqa: BLE001
        shutil.copy2(backup, db_path)
        if core_backup.is_file():
            shutil.copy2(core_backup, core)
        return fail("落地失敗，core＋DB 已還原：%s" % error, 3)

    print("\nAPPLIED")
    print("SCHEMA_SHA = %s" % after_sha)
    print("\n跟手一定要做：")
    print("  ① policies/r03_core_policy_v1.json 嘅 sqlite_schema_sha256 ← 上面個值")
    print("  ② 如果 policy chain 有 a2 pin／council.json source pin，core 改咗要一齊重釘")
    print("  ③ 之後先起機；D2（vip-issue／revoke／handover／bind 指令）另外交付")
    return 0


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