database.py

from __future__ import annotations

from collections.abc import Iterable
from dataclasses import dataclass
from datetime import datetime

import aiosqlite


def utc_now() -> str:
    return datetime.utcnow().isoformat(timespec="seconds")


@dataclass(frozen=True)
class Database:
    path: str

    async def init(self) -> None:
        async with aiosqlite.connect(self.path) as db:
            await db.execute("PRAGMA foreign_keys = ON")
            await db.executescript(
                """
                CREATE TABLE IF NOT EXISTS users (
                    telegram_id INTEGER PRIMARY KEY,
                    username TEXT,
                    phone TEXT UNIQUE NOT NULL,
                    created_at TEXT NOT NULL
                );

                CREATE TABLE IF NOT EXISTS bonus_accounts (
                    phone TEXT PRIMARY KEY,
                    points INTEGER NOT NULL DEFAULT 0,
                    updated_at TEXT NOT NULL
                );

                CREATE TABLE IF NOT EXISTS bonus_transactions (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    phone TEXT NOT NULL,
                    type TEXT NOT NULL CHECK(type IN ('earn', 'redeem')),
                    points INTEGER NOT NULL,
                    order_amount INTEGER,
                    seller_id INTEGER,
                    comment TEXT,
                    created_at TEXT NOT NULL
                );

                CREATE TABLE IF NOT EXISTS sellers (
                    telegram_id INTEGER PRIMARY KEY,
                    name TEXT,
                    is_active INTEGER NOT NULL DEFAULT 1
                );
                """
            )
            await db.commit()

    async def seed_sellers(self, seller_ids: Iterable[int]) -> None:
        async with aiosqlite.connect(self.path) as db:
            for seller_id in seller_ids:
                await db.execute(
                    """
                    INSERT INTO sellers (telegram_id, name, is_active)
                    VALUES (?, ?, 1)
                    ON CONFLICT(telegram_id) DO UPDATE SET is_active = 1
                    """,
                    (seller_id, f"seller_{seller_id}"),
                )
            await db.commit()

    async def execute(self, query: str, params: tuple = ()) -> None:
        async with aiosqlite.connect(self.path) as db:
            await db.execute("PRAGMA foreign_keys = ON")
            await db.execute(query, params)
            await db.commit()

    async def fetchone(self, query: str, params: tuple = ()) -> aiosqlite.Row | None:
        async with aiosqlite.connect(self.path) as db:
            db.row_factory = aiosqlite.Row
            cursor = await db.execute(query, params)
            return await cursor.fetchone()

    async def fetchall(self, query: str, params: tuple = ()) -> list[aiosqlite.Row]:
        async with aiosqlite.connect(self.path) as db:
            db.row_factory = aiosqlite.Row
            cursor = await db.execute(query, params)
            return await cursor.fetchall()

    async def transaction(self) -> aiosqlite.Connection:
        db = await aiosqlite.connect(self.path)
        db.row_factory = aiosqlite.Row
        await db.execute("PRAGMA foreign_keys = ON")
        return db