#!/usr/bin/env python3
"""
AA-043-P2R: migration dry-run against a COPY of the real knowledge.db.
Drops the legacy table-level UNIQUE(obsidian_path, content_hash) by rebuilding
the table without it; keeps all three AA-043 unique indexes intact.
Verifies: row counts identical before/after, legacy data unchanged (checksum
per row), AA-043 indexes present, rollback possible from backup file.
"""
import hashlib
import sqlite3

SRC = "/opt/struktur/youtube-research/knowledge.db"
TEST = "/tmp/aa043-p2r-migration-test.db"

c = sqlite3.connect(TEST)
c.row_factory = sqlite3.Row

# full schema of the existing table
cols = [r["name"] for r in c.execute("PRAGMA table_info(graphiti_import_queue)")]
print("columns:", len(cols))

# row checksum before
def checksum():
    rows = c.execute("SELECT * FROM graphiti_import_queue ORDER BY id").fetchall()
    h = hashlib.sha256()
    for r in rows:
        h.update(repr(tuple(r)).encode())
    return h.hexdigest(), len(rows)

before_hash, before_n = checksum()
print("before:", before_n, "rows", before_hash[:16])

# rebuild WITHOUT the UNIQUE(obsidian_path,content_hash) constraint
c.executescript("""
BEGIN;
CREATE TABLE graphiti_import_queue_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source_type TEXT NOT NULL,
    video_id TEXT,
    obsidian_path TEXT NOT NULL,
    content_hash TEXT NOT NULL,
    file_modified_at TEXT,
    graphiti_status TEXT NOT NULL DEFAULT 'pending',
    graphiti_attempts INTEGER NOT NULL DEFAULT 0,
    graphiti_last_attempt_at TEXT,
    graphiti_processed_at TEXT,
    graphiti_episode_id TEXT,
    graphiti_error TEXT,
    next_attempt_at TEXT,
    locked_at TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    attempt_count INTEGER NOT NULL DEFAULT 0,
    last_attempt_at TEXT,
    last_error_class TEXT,
    last_error_message TEXT,
    last_http_status INTEGER,
    completed_at TEXT,
    reconciliation_record_id TEXT,
    source_version_id INTEGER,
    import_identity TEXT,
    knowledge_unit_identity TEXT NOT NULL DEFAULT '__source__',
    action TEXT NOT NULL DEFAULT 'CREATE',
    payload_hash TEXT,
    payload_json TEXT,
    lock_owner TEXT,
    lock_token TEXT,
    lease_expires_at TEXT,
    heartbeat_at TEXT,
    remote_outcome TEXT,
    unknown_since TEXT,
    last_lookup_at TEXT,
    lookup_attempt_count INTEGER NOT NULL DEFAULT 0,
    post_request_id TEXT,
    file_state TEXT
);
INSERT INTO graphiti_import_queue_new SELECT * FROM graphiti_import_queue;
DROP TABLE graphiti_import_queue;
ALTER TABLE graphiti_import_queue_new RENAME TO graphiti_import_queue;
COMMIT;
""")

# recreate the three AA-043 indexes (dropped with the old table)
c.executescript("""
CREATE UNIQUE INDEX IF NOT EXISTS uq_queue_decision ON graphiti_import_queue(reconciliation_record_id, action);
CREATE UNIQUE INDEX IF NOT EXISTS uq_queue_identity_action_version_unit ON graphiti_import_queue(import_identity, action, source_version_id, knowledge_unit_identity);
CREATE UNIQUE INDEX IF NOT EXISTS uq_queue_version_action_unit ON graphiti_import_queue(source_version_id, action, knowledge_unit_identity);
CREATE INDEX IF NOT EXISTS idx_graphiti_queue_due ON graphiti_import_queue(graphiti_status, next_attempt_at);
""")
c.commit()

after_hash, after_n = checksum()
print("after :", after_n, "rows", after_hash[:16])
assert before_n == after_n and before_hash == after_hash, "DATA CHANGED!"

schema = c.execute("SELECT sql FROM sqlite_master WHERE name='graphiti_import_queue'").fetchone()[0]
print("legacy UNIQUE removed:", "UNIQUE(obsidian_path,content_hash)" not in schema)
idx = [r[0] for r in c.execute("SELECT name FROM sqlite_master WHERE tbl_name='graphiti_import_queue' AND type='index' AND name LIKE 'uq_%'")]
print("AA-043 indexes:", sorted(idx))
assert sorted(idx) == ["uq_queue_decision", "uq_queue_identity_action_version_unit", "uq_queue_version_action_unit"]

# behavioral test: new aa043 row for same obsidian_path+content_hash must now insert cleanly
try:
    c.execute("""INSERT INTO graphiti_import_queue(source_type,obsidian_path,content_hash,
        graphiti_status,created_at,updated_at,reconciliation_record_id,
        source_version_id,import_identity,knowledge_unit_identity,action)
        VALUES('obsidian','/opt/obsidian-vault/Solutions/SOL-005-content-briefing-modul.md',
        'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','queued',
        '2026-01-01','2026-01-01','aa043:obsidian-general:deadbeef',NULL,
        'testiid','__source__','CREATE')""")
    c.commit()
    print("NEW-SOURCE INSERT OK (no collision)")
except sqlite3.IntegrityError as e:
    print("STILL COLLIDES:", e)
    raise

# duplicate protection via uq_queue_decision: same rid+action twice -> conflict
try:
    c.execute("""INSERT INTO graphiti_import_queue(source_type,obsidian_path,content_hash,
        graphiti_status,created_at,updated_at,reconciliation_record_id,
        source_version_id,import_identity,knowledge_unit_identity,action)
        VALUES('obsidian','/other/path.md','bbbb', 'queued','2026-01-01','2026-01-01',
        'aa043:obsidian-general:deadbeef',NULL,'testiid2','__source__','CREATE')""")
    print("UNEXPECTED: duplicate rid+action accepted")
except sqlite3.IntegrityError as e:
    print("uq_queue_decision still protects:", e)
c.rollback()

n2 = c.execute("select count(*) from graphiti_import_queue").fetchone()[0]
print("rows after rollback of test inserts:", n2)
c.close()
print("MIGRATION DRY-RUN: SUCCESS")