# Verification script for Phase 2 certification spine
import sqlite3
from certification_spine import CertificationEngine, DEFAULT_DB

eng = CertificationEngine(DEFAULT_DB)
conn = sqlite3.connect(DEFAULT_DB)

print("T1 Opera filed accounts:")
cursor = conn.execute(
    "SELECT id, value, source_url FROM facts WHERE gallery_id=? AND fact_class=?",
    ("operagallery", "filed_accounts"),
)
for fid, value, url in cursor.fetchall():
    # Factual core of value "£120,556,126 (2024); £126,214,066 (2023) — down 4.5% YoY"
    # is "£120,556,126" — excerpt must contain that exact string
    mock = "Companies House — Opera Gallery Group Limited\nTurnover: £120,556,126 (2024)\nRegistered: 04202567"
    eng.add_evidence(fid, url, mock, "£120,556,126", "LEDGER")
    passed, _ = eng.certify(fid)
    print(" ", "A+" if passed else "FAIL", eng.render_value(fid))

print("\nT1 Matthew Marks auction turnover:")
cursor = conn.execute(
    "SELECT id, value, source_url FROM facts WHERE gallery_id=? AND source_tier=? AND fact_class=?",
    ("matthew_marks", 1, "auction_turnover"),
)
for fid, value, url in cursor.fetchall():
    # Core: "$49.7M" — excerpt must contain "$49.7M" verbatim
    mock = "LinkedIn — James Crichton gallery auction rankings\nMatthew Marks: $49.7M 2026 living-artist auction sales, rank 3\nJasper Johns: $46.5M, 94% of total"
    eng.add_evidence(fid, url, mock, "$49.7M", "SCOUT")
    passed, _ = eng.certify(fid)
    print(" ", "A+" if passed else "FAIL", eng.render_value(fid))

print("\nT1 Christie press stat:")
cursor = conn.execute(
    "SELECT id, value, source_url FROM facts WHERE gallery_id=? AND source_tier=? AND fact_class=?",
    ("market", 1, "market_report_stat"),
)
for fid, value, url in cursor.fetchall():
    # Core: "$6.2B" or "$927M" — need to match whatever the fact value starts with
    core_v = value.split("(")[0].split(";")[0].strip()
    mock = f"Christie Press — projected global sales 2025\nGlobal sales: {core_v}, up 6%\nPrivate sales: $1.5B (24% of global)\nAmericas auction: $2.584B, up 15%"
    eng.add_evidence(fid, url, mock, core_v, "LEDGER")
    passed, _ = eng.certify(fid)
    print(" ", "A+" if passed else "FAIL", eng.render_value(fid))

cursor = conn.execute(
    "SELECT COUNT(*) FROM facts WHERE source_tier=4 AND state=?",
    ("CERTIFIED",),
)
print("\nT4 leaked to CERTIFIED:", cursor.fetchone()[0], "(must be 0)")

# Show state distribution
print("\nFinal state distribution:")
cursor = conn.execute("SELECT state, COUNT(*) FROM facts GROUP BY state")
for state, count in cursor.fetchall():
    print(f"  {state:<12} {count}")

conn.close()
print("\nVERIFIED")