# db.py
import aiosqlite
import uuid
import os
import colorlog
from typing import List, Optional, Dict, Any

# Se usarán placehoders para los imports de app.py para evitar errores
logger = colorlog.getLogger("OMEGA_DB")
logger.setLevel(colorlog.logging.INFO) 
DB_PATH = "data/omega_modular_argentina.db"

class AsyncDatabase:
    """Clase central para interactuar con SQLite."""
    def __init__(self, db_path: str = DB_PATH):
        self.db_path = db_path
        os.makedirs(os.path.dirname(db_path), exist_ok=True)

    async def init(self):
        logger.info("🛠️ Base de Datos: Verificando tablas y consistencia.")
        async with aiosqlite.connect(self.db_path) as db:
            await db.execute("PRAGMA journal_mode=WAL;") 
            await db.execute("""
                CREATE TABLE IF NOT EXISTS souls (
                    uuid TEXT PRIMARY KEY, username TEXT UNIQUE, password_hash TEXT,
                    role TEXT DEFAULT 'INICIADO', karma INTEGER DEFAULT 100, 
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, is_banned BOOLEAN DEFAULT 0
                )
            """)
            await db.execute("""
                CREATE TABLE IF NOT EXISTS codex (
                    id INTEGER PRIMARY KEY AUTOINCREMENT, uuid TEXT UNIQUE,
                    title TEXT, content TEXT, principle TEXT, author_id TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)
            await db.execute("""
                CREATE TABLE IF NOT EXISTS audit_logs (
                    id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT,
                    action TEXT, details TEXT, timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)
            await db.commit()
        await self.log_event(None, "SYSTEM_DB_INIT", "Base de Datos en línea. ¡Fierro!")

    async def create_soul_transactional(self, username: str, pass_hash: str, role: str) -> str:
        soul_uuid = str(uuid.uuid4())
        try:
            async with aiosqlite.connect(self.db_path) as db:
                await db.execute(
                    "INSERT INTO souls (uuid, username, password_hash, role) VALUES (?, ?, ?, ?)",
                    (soul_uuid, username, pass_hash, role)
                )
                await db.commit()
            return soul_uuid
        except aiosqlite.IntegrityError:
            class DatabaseIntegrityError(Exception): pass # Placeholder
            raise DatabaseIntegrityError("Ese usuario ya existe.")
        except Exception as e:
            logger.error(f"Quilombo al crear alma: {e}")
            raise

    async def get_soul_by_username(self, username: str) -> Optional[Dict[str, Any]]:
        async with aiosqlite.connect(self.db_path) as db:
            db.row_factory = aiosqlite.Row
            async with db.execute("SELECT * FROM souls WHERE username = ?", (username,)) as cursor:
                row = await cursor.fetchone()
                return dict(row) if row else None

    async def get_all_souls_data(self) -> List[Dict[str, Any]]:
        async with aiosqlite.connect(self.db_path) as db:
            db.row_factory = aiosqlite.Row
            async with db.execute("SELECT * FROM souls ORDER BY karma DESC") as cursor:
                rows = await cursor.fetchall()
                return [dict(r) for r in rows]
    
    async def update_ban_status_db(self, username: str, is_banned: bool):
        async with aiosqlite.connect(self.db_path) as db:
            await db.execute("UPDATE souls SET is_banned = ? WHERE username = ?", (1 if is_banned else 0, username))
            await db.commit()

    async def write_law_and_reward(self, title: str, content: str, principle: str, author_uuid: str):
        law_uuid = str(uuid.uuid4())
        async with aiosqlite.connect(self.db_path) as db:
            await db.execute(
                "INSERT INTO codex (uuid, title, content, principle, author_id) VALUES (?, ?, ?, ?, ?)",
                (law_uuid, title, content, principle, author_uuid)
            )
            await db.execute("UPDATE souls SET karma = karma + 20 WHERE uuid = ?", (author_uuid,))
            await db.commit()

    async def get_codex_feed_data(self, limit: int = 25) -> List[dict]:
        async with aiosqlite.connect(self.db_path) as db:
            db.row_factory = aiosqlite.Row
            query = """
                SELECT c.*, s.username as author_name, s.role 
                FROM codex c 
                LEFT JOIN souls s ON c.author_id = s.uuid 
                ORDER BY c.created_at DESC LIMIT ?
            """
            async with db.execute(query, (limit,)) as cursor:
                rows = await cursor.fetchall()
                return [dict(r) for r in rows]

    async def search_context_rag(self, query: str) -> str:
        async with aiosqlite.connect(self.db_path) as db:
            db.row_factory = aiosqlite.Row
            async with db.execute("SELECT title, content FROM codex ORDER BY RANDOM() LIMIT 2") as cursor:
                rows = await cursor.fetchall()
                context = "\n".join([f"PRECEDENTE [{r['title']}]: {r['content']}" for r in rows])
                return context if context else "No hay precedentes cargados."

    async def log_event(self, user_id: Optional[str], action: str, details: str):
        async with aiosqlite.connect(self.db_path) as db:
            await db.execute(
                "INSERT INTO audit_logs (user_id, action, details) VALUES (?, ?, ?)",
                (user_id, action, details)
            )
            await db.commit()
        
    async def get_recent_audit_logs(self, limit: int = 20) -> List[Dict[str, Any]]:
        async with aiosqlite.connect(self.db_path) as db:
            db.row_factory = aiosqlite.Row
            async with db.execute("SELECT * FROM audit_logs ORDER BY timestamp DESC LIMIT ?", (limit,)) as cursor:
                rows = await cursor.fetchall()
                return [dict(r) for r in rows]

DB = AsyncDatabase()