import aiosqlite
from app.core.config import settings
from custom_logger import logger_config as logger

async def init_db():
    logger.info(f"Initializing database at {settings.DATABASE_FILE}")
    async with aiosqlite.connect(settings.DATABASE_FILE) as db:
        await db.execute('''CREATE TABLE IF NOT EXISTS tasks
                     (id TEXT PRIMARY KEY,
                      filename TEXT NOT NULL,
                      filepath TEXT NOT NULL,
                      status TEXT NOT NULL,
                      result TEXT,
                      created_at TEXT NOT NULL,
                      processed_at TEXT,
                      progress INTEGER DEFAULT 0,
                      progress_text TEXT,
                      hide_from_ui INTEGER DEFAULT 0,
                      language TEXT,
                      task TEXT DEFAULT 'transcribe',
                      engine TEXT)'''
        )

        # Migrate databases created before language/task existed.
        async with db.execute("PRAGMA table_info(tasks)") as cursor:
            existing = {row[1] for row in await cursor.fetchall()}
        if 'language' not in existing:
            await db.execute("ALTER TABLE tasks ADD COLUMN language TEXT")
        if 'task' not in existing:
            await db.execute("ALTER TABLE tasks ADD COLUMN task TEXT DEFAULT 'transcribe'")
        if 'engine' not in existing:
            await db.execute("ALTER TABLE tasks ADD COLUMN engine TEXT")

        await db.commit()
    logger.info("Database initialized successfully.")