#!/usr/bin/env python3
"""
AA-043-P3: Schema-Migration für Promotion Policy
Erweitert die bestehenden Tabellen um Promotion-Gate-Felder
OHNE bestehende Schutzmechanismen zu verändern.
"""

import sqlite3
import sys

def migrate(db_path):
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()
    
    print(f"Migriere {db_path}...")
    
    # 1. graphiti_import_queue: Promotion-Felder hinzufügen
    # Nur falls nicht vorhanden
    cols = [row[1] for row in cur.execute("PRAGMA table_info(graphiti_import_queue)")]
    
    if 'promotion_policy' not in cols:
        cur.execute("""
            ALTER TABLE graphiti_import_queue 
            ADD COLUMN promotion_policy TEXT DEFAULT 'REVIEW_REQUIRED'
        """)
        print("  + graphiti_import_queue.promotion_policy")
    
    if 'promotion_reason' not in cols:
        cur.execute("""
            ALTER TABLE graphiti_import_queue 
            ADD COLUMN promotion_reason TEXT
        """)
        print("  + graphiti_import_queue.promotion_reason")
    
    if 'promotion_decided_at' not in cols:
        cur.execute("""
            ALTER TABLE graphiti_import_queue 
            ADD COLUMN promotion_decided_at TEXT
        """)
        print("  + graphiti_import_queue.promotion_decided_at")
    
    if 'knowledge_unit_identities' not in cols:
        cur.execute("""
            ALTER TABLE graphiti_import_queue 
            ADD COLUMN knowledge_unit_identities TEXT
        """)
        print("  + graphiti_import_queue.knowledge_unit_identities")
    
    # 2. source_versions: Promotion-Status
    cols = [row[1] for row in cur.execute("PRAGMA table_info(source_versions)")]
    
    if 'promotion_policy' not in cols:
        cur.execute("""
            ALTER TABLE source_versions 
            ADD COLUMN promotion_policy TEXT DEFAULT 'REVIEW_REQUIRED'
        """)
        print("  + source_versions.promotion_policy")
    
    if 'promotion_reason' not in cols:
        cur.execute("""
            ALTER TABLE source_versions 
            ADD COLUMN promotion_reason TEXT
        """)
        print("  + source_versions.promotion_reason")
    
    if 'promotion_decided_at' not in cols:
        cur.execute("""
            ALTER TABLE source_versions 
            ADD COLUMN promotion_decided_at TEXT
        """)
        print("  + source_versions.promotion_decided_at")
    
    # 3. reconciliation_decisions: Promotion-Referenz
    cols = [row[1] for row in cur.execute("PRAGMA table_info(reconciliation_decisions)")]
    
    if 'promotion_policy' not in cols:
        cur.execute("""
            ALTER TABLE reconciliation_decisions 
            ADD COLUMN promotion_policy TEXT
        """)
        print("  + reconciliation_decisions.promotion_policy")
    
    # 4. files (Importer-State): Promotion-Felder
    cols = [row[1] for row in cur.execute("PRAGMA table_info(files)")]
    
    if 'promotion_policy' not in cols:
        cur.execute("""
            ALTER TABLE files 
            ADD COLUMN promotion_policy TEXT DEFAULT 'REVIEW_REQUIRED'
        """)
        print("  + files.promotion_policy")
    
    if 'promotion_reason' not in cols:
        cur.execute("""
            ALTER TABLE files 
            ADD COLUMN promotion_reason TEXT
        """)
        print("  + files.promotion_reason")
    
    if 'promotion_decided_at' not in cols:
        cur.execute("""
            ALTER TABLE files 
            ADD COLUMN promotion_decided_at TEXT
        """)
        print("  + files.promotion_decided_at")
    
    # 5. Index für Promotion-Queue
    cur.execute("""
        CREATE INDEX IF NOT EXISTS idx_graphiti_queue_promotion 
        ON graphiti_import_queue(promotion_policy, graphiti_status, next_attempt_at)
    """)
    print("  + idx_graphiti_queue_promotion")
    
    conn.commit()
    conn.close()
    print("Migration abgeschlossen.")

if __name__ == '__main__':
    if len(sys.argv) != 2:
        print("Usage: python3 aa043_p3_schema_migration.py <db_path>")
        sys.exit(1)
    migrate(sys.argv[1])