import aiosqlite

from bot.config import settings

_db: aiosqlite.Connection | None = None

SCHEMA = """\
CREATE TABLE IF NOT EXISTS users (
    telegram_id  INTEGER PRIMARY KEY,
    username     TEXT,
    first_name   TEXT,
    lang         TEXT NOT NULL DEFAULT 'en',
    free_used    INTEGER NOT NULL DEFAULT 0,
    created_at   TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS analyses (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    telegram_id  INTEGER NOT NULL REFERENCES users(telegram_id),
    channel      TEXT NOT NULL,
    depth        TEXT NOT NULL,
    focus_areas  TEXT NOT NULL,
    model_id     TEXT NOT NULL,
    stars_paid   INTEGER NOT NULL DEFAULT 0,
    report_path  TEXT,
    created_at   TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS payments (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    telegram_id         INTEGER NOT NULL REFERENCES users(telegram_id),
    telegram_payment_id TEXT NOT NULL UNIQUE,
    stars_amount        INTEGER NOT NULL,
    analysis_id         INTEGER REFERENCES analyses(id),
    created_at          TEXT NOT NULL DEFAULT (datetime('now'))
);
"""


async def get_db() -> aiosqlite.Connection:
    global _db
    if _db is None:
        _db = await aiosqlite.connect(settings.db_path)
        _db.row_factory = aiosqlite.Row
        await _db.executescript(SCHEMA)
        await _db.commit()
    return _db


async def close_db() -> None:
    global _db
    if _db is not None:
        await _db.close()
        _db = None