import sqlite3
import os
import asyncio
import logging

async def run_all_migrations(db_path: str):
    """
    Ensures that all SQLite tables exist before any subsystem tries to query them.
    If the db_path directory doesn't exist, it creates it.
    """
    logging.info(f"Running database migrations on {db_path}...")
    
    os.makedirs(os.path.dirname(os.path.abspath(db_path)), exist_ok=True)
    
    def _migrate():
        with sqlite3.connect(db_path) as conn:
            conn.execute('PRAGMA journal_mode=WAL')
            cursor = conn.cursor()
            
            # Easter Eggs
            cursor.execute("CREATE TABLE IF NOT EXISTS easter_eggs_triggered (egg_id TEXT PRIMARY KEY, first_triggered_at INTEGER, trigger_count INTEGER DEFAULT 0)")
            
            # Omega Events
            cursor.execute('''CREATE TABLE IF NOT EXISTS omega_events (
                id TEXT PRIMARY KEY,
                domain TEXT,
                event_type TEXT NOT NULL,
                description TEXT,
                persona TEXT,
                timestamp TEXT NOT NULL
            )''')
            # Add columns if they don't exist
            try:
                cursor.execute("ALTER TABLE omega_events ADD COLUMN domain TEXT")
            except sqlite3.OperationalError:
                pass
            try:
                cursor.execute("ALTER TABLE omega_events ADD COLUMN description TEXT")
            except sqlite3.OperationalError:
                pass
            try:
                cursor.execute("ALTER TABLE omega_events ADD COLUMN persona TEXT")
            except sqlite3.OperationalError:
                pass
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_omega_events_type ON omega_events(event_type)")

            # Procedural Memory (Skills)
            cursor.execute('''CREATE TABLE IF NOT EXISTS skills (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                description TEXT,
                code_blob TEXT NOT NULL,
                created_at INTEGER
            )''')
            
            # Semantic Memory (Nodes and Edges)
            cursor.execute('''CREATE TABLE IF NOT EXISTS nodes (
                id TEXT PRIMARY KEY,
                label TEXT NOT NULL,
                attributes TEXT,
                embedding BLOB
            )''')
            
            cursor.execute('''CREATE TABLE IF NOT EXISTS edges (
                source_id TEXT,
                target_id TEXT,
                relationship TEXT,
                weight REAL DEFAULT 1.0,
                PRIMARY KEY (source_id, target_id, relationship)
            )''')
            
            # Credential Vault
            cursor.execute('''CREATE TABLE IF NOT EXISTS credentials (
                service_id TEXT PRIMARY KEY,
                encrypted_key BLOB NOT NULL,
                nonce BLOB NOT NULL,
                updated_at INTEGER
            )''')
            
            # Tasks (Agent Executor)
            cursor.execute('''CREATE TABLE IF NOT EXISTS tasks (
                id TEXT PRIMARY KEY,
                name TEXT,
                status TEXT,
                steps TEXT,
                current_step INTEGER,
                result TEXT,
                error TEXT,
                permission_tier TEXT
            )''')
            
            # Conversation History
            cursor.execute('''CREATE TABLE IF NOT EXISTS personas (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL
            )''')
            
            cursor.execute('''CREATE TABLE IF NOT EXISTS conversations (
                id TEXT PRIMARY KEY,
                conversation_id TEXT NOT NULL DEFAULT 'default_conv',
                persona_id TEXT NOT NULL DEFAULT 'jarvis',
                role TEXT NOT NULL,
                content TEXT NOT NULL,
                timestamp INTEGER NOT NULL,
                FOREIGN KEY(persona_id) REFERENCES personas(id) ON DELETE CASCADE
            )''')
            
            try:
                cursor.execute('''CREATE INDEX IF NOT EXISTS idx_conversations_lookup ON conversations(conversation_id, persona_id, timestamp)''')
            except sqlite3.OperationalError:
                pass # The column doesn't exist yet, it will be added by a later alter table migration
            
            # Auto Upgrades
            cursor.execute('''CREATE TABLE IF NOT EXISTS upgrades (
                id TEXT PRIMARY KEY,
                timestamp INTEGER,
                feature_request TEXT,
                files_modified TEXT,
                persona TEXT,
                status TEXT
            )''')
            
            # §2.3+2.4 — Research & Enhancement Framework
            # Continuous Research Notes
            cursor.execute('''CREATE TABLE IF NOT EXISTS research_notes (
                id TEXT PRIMARY KEY,
                timestamp INTEGER,
                category TEXT,
                title TEXT,
                summary TEXT,
                source TEXT,
                importance TEXT,
                recommended_action TEXT
            )''')
            
            # Pending Review Queue (Approval Gate)
            cursor.execute('''CREATE TABLE IF NOT EXISTS pending_review_queue (
                id TEXT PRIMARY KEY,
                discovered_at INTEGER,
                feature_name TEXT,
                purpose TEXT,
                benefits TEXT,
                risks TEXT,
                dependencies TEXT,
                complexity TEXT,
                implementation_plan TEXT,
                discovery_source TEXT,
                status TEXT DEFAULT 'pending',
                persona TEXT
            )''')
            
            # Knowledge Reports (Tech News)
            cursor.execute('''CREATE TABLE IF NOT EXISTS research_reports (
                id TEXT PRIMARY KEY,
                generated_at INTEGER,
                report_type TEXT,
                content TEXT,
                presented_to_user INTEGER DEFAULT 0
            )''')

            # §Token Safety — Gemini 3.5 Flash Token Checkpoint & Resume
            cursor.execute('''CREATE TABLE IF NOT EXISTS token_checkpoints (
                id TEXT PRIMARY KEY,
                task_type TEXT NOT NULL,
                original_prompt TEXT NOT NULL,
                partial_result TEXT DEFAULT '',
                last_word TEXT DEFAULT '',
                status TEXT DEFAULT 'pending_resume',
                created_at INTEGER,
                resumed_at INTEGER,
                completed_at INTEGER,
                retry_count INTEGER DEFAULT 0,
                persona TEXT DEFAULT 'jarvis'
            )''')
            
            # Gaming Coach
            cursor.execute('''CREATE TABLE IF NOT EXISTS gaming_sessions (
                id TEXT PRIMARY KEY,
                game_id TEXT,
                started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                ended_at TIMESTAMP,
                avg_dqi_score REAL,
                decision_count INTEGER,
                persona_coaching TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )''')
            cursor.execute('''CREATE INDEX IF NOT EXISTS idx_gaming_session_game ON gaming_sessions(game_id)''')
            
            conn.commit()
            
    await asyncio.to_thread(_migrate)
    logging.info("Database migrations complete. All tables verified.")