"""Async SQLite helper with a tiny query API and idempotent migrations."""
import aiosqlite, pathlib
from config import DB_PATH

SCHEMA_PATH = pathlib.Path(__file__).with_name("schema.sql")

# Idempotent ALTERs for upgrades from older deployments.
MIGRATIONS = [
    "ALTER TABLE users ADD COLUMN channel_verified INTEGER NOT NULL DEFAULT 0",
    "ALTER TABLE orders ADD COLUMN quantity INTEGER NOT NULL DEFAULT 1",
    "ALTER TABLE deposits ADD COLUMN unique_amount REAL",
    "ALTER TABLE deposits ADD COLUMN address TEXT",
    "ALTER TABLE deposits ADD COLUMN expires_at TEXT",
    "ALTER TABLE deposits ADD COLUMN detected_txid TEXT",
    "ALTER TABLE deposits ADD COLUMN confirmations INTEGER NOT NULL DEFAULT 0",
    "ALTER TABLE deposits ADD COLUMN chat_id INTEGER",
    "ALTER TABLE deposits ADD COLUMN message_id INTEGER",
]

class DB:
    _conn: aiosqlite.Connection | None = None

    @classmethod
    async def connect(cls):
        if cls._conn is None:
            cls._conn = await aiosqlite.connect(DB_PATH)
            cls._conn.row_factory = aiosqlite.Row
            await cls._conn.execute("PRAGMA foreign_keys = ON;")
            await cls._conn.executescript(SCHEMA_PATH.read_text())
            for stmt in MIGRATIONS:
                try:
                    await cls._conn.execute(stmt)
                except Exception:
                    pass  # column already exists
            await cls._conn.commit()
        return cls._conn

    @classmethod
    async def close(cls):
        if cls._conn:
            await cls._conn.close()
            cls._conn = None

    @classmethod
    async def execute(cls, sql, params=()):
        conn = await cls.connect()
        cur = await conn.execute(sql, params)
        await conn.commit()
        return cur

    @classmethod
    async def fetchone(cls, sql, params=()):
        conn = await cls.connect()
        async with conn.execute(sql, params) as cur:
            return await cur.fetchone()

    @classmethod
    async def fetchall(cls, sql, params=()):
        conn = await cls.connect()
        async with conn.execute(sql, params) as cur:
            return await cur.fetchall()