"""
V74 적용 후 갭 재검증 스크립트 (DB 복귀 시 실행)
작성: 2026-05-17 (infra)

사용법:
    python verify_v74.py
    # DB UP 시 자동으로 V74 적용 결과 검증. 갭 0건이면 PASS.
    # ga-api 재기동 후 Flyway V74 자동 적용(멱등) → 그 후 이 스크립트 실행.
"""
import psycopg2

conn = psycopg2.connect(
    host="192.168.0.60",
    port=55432,
    dbname="trading_ai",
    user="kyu",
    password="7895123",
    options="-c search_path=ga",
    connect_timeout=8
)
cur = conn.cursor()

# 1. Flyway 버전 확인
cur.execute("SELECT version FROM flyway_schema_history ORDER BY installed_rank DESC LIMIT 1")
ver = cur.fetchone()[0]
print("[FLYWAY] Latest version: %s" % ver)
if ver != "74":
    print("  WARNING: V74 not yet applied (current=%s). Run ga-api to apply." % ver)

# 2. 행 수 확인
for tbl in ["menu", "menu_permission", "role_menu_permission", "role"]:
    cur.execute("SELECT COUNT(*) FROM %s" % tbl)
    print("[COUNT] %s: %s" % (tbl, cur.fetchone()[0]))

# 3. V74 갭 보강 항목 검증
print("\n--- V74 보강 검증 ---")
checks = []

# (b) menu_permission 3건
for mc, pc in [("NOTICE", "DELETE"), ("SYSTEM_DATA_DICT", "DELETE"), ("TERM_SETTLE", "APPROVE")]:
    cur.execute("""
        SELECT COUNT(*) FROM menu_permission mp
        JOIN menu m ON m.menu_id = mp.menu_id
        WHERE m.menu_code = %s AND mp.perm_code = %s
    """, (mc, pc))
    cnt = cur.fetchone()[0]
    status = "PASS" if cnt > 0 else "FAIL"
    checks.append(status)
    print("  [%s] menu_permission %s/%s: %s" % (status, mc, pc, cnt))

# (c) role_menu_permission 6건 (3 perm x 2 roles)
for role in ["SUPER_ADMIN", "ADMIN"]:
    for mc, pc in [("NOTICE", "DELETE"), ("SYSTEM_DATA_DICT", "DELETE"), ("TERM_SETTLE", "APPROVE")]:
        cur.execute("""
            SELECT COUNT(*) FROM role_menu_permission rmp
            JOIN role r ON r.role_id = rmp.role_id
            JOIN menu m ON m.menu_id = rmp.menu_id
            WHERE r.role_code = %s AND m.menu_code = %s AND rmp.perm_code = %s AND rmp.is_granted = 'Y'
        """, (role, mc, pc))
        cnt = cur.fetchone()[0]
        status = "PASS" if cnt > 0 else "FAIL"
        checks.append(status)
        print("  [%s] grant %s/%s/%s: %s" % (status, role, mc, pc, cnt))

# (route) menu_path 11건
route_checks = [
    ("REINSTATE_COMM",  "/commission/reinstatement"),
    ("PERSIST_BONUS",   "/commission/persistency"),
    ("OVERRIDE_LEDGER", "/commission/override-ledger"),
    ("AGENT_CONTRACT",  "/agent/contracts"),
    ("AGENT_LICENSE",   "/agent/licenses"),
    ("AGENT_TRAINING",  "/agent/trainings"),
    ("ENDORSE",         "/contracts/endorsements"),
    ("COMPLAINT",       "/contracts/complaints"),
    ("APPROVAL",        "/system/approvals"),
    ("NOTICE",          "/system/notices"),
    ("WITHDRAW",        "/payments/withdraw"),
]
for mc, expected_path in route_checks:
    cur.execute("SELECT menu_path FROM menu WHERE menu_code = %s", (mc,))
    row = cur.fetchone()
    actual = row[0] if row else "NOT_FOUND"
    status = "PASS" if actual == expected_path else "FAIL"
    checks.append(status)
    print("  [%s] menu_path %s: %s (expected %s)" % (status, mc, actual, expected_path))

# Summary
total = len(checks)
passed = checks.count("PASS")
failed = checks.count("FAIL")
print("\n[RESULT] %d/%d PASS, %d FAIL" % (passed, total, failed))
if failed == 0:
    print("[OVERALL] V74 검증 완료 - 모든 갭 해소 확인")
else:
    print("[OVERALL] FAIL 항목 있음 - V74 재적용 또는 SQL 확인 필요")

conn.close()