from __future__ import annotations

from datetime import datetime
from pathlib import Path
import secrets

import aiosqlite

from app.categories import CATEGORY_META, DEFAULT_SUBCATEGORIES, category_label
from app.domain import (
    BudgetCategory,
    Category,
    FamilyInfo,
    FamilyJoinRequest,
    Totals,
    Transaction,
    TransactionKind,
)


class FamilyJoinError(ValueError):
    pass


class Database:
    def __init__(self, path: Path) -> None:
        self.path = path

    async def initialize(self) -> None:
        self.path.parent.mkdir(parents=True, exist_ok=True)
        async with aiosqlite.connect(self.path) as db:
            await db.execute("PRAGMA journal_mode=WAL")
            await db.execute("PRAGMA foreign_keys=ON")
            await db.executescript(
                """
                CREATE TABLE IF NOT EXISTS users (
                    user_id INTEGER PRIMARY KEY,
                    chat_id INTEGER NOT NULL,
                    username TEXT,
                    full_name TEXT NOT NULL,
                    timezone TEXT NOT NULL,
                    report_time TEXT NOT NULL,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                );

                CREATE TABLE IF NOT EXISTS households (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    invite_code TEXT NOT NULL UNIQUE,
                    created_by INTEGER NOT NULL REFERENCES users(user_id),
                    notification_chat_id INTEGER,
                    notification_chat_title TEXT,
                    debt_base_kopecks INTEGER,
                    debt_started_at TEXT,
                    created_at TEXT NOT NULL
                );

                CREATE TABLE IF NOT EXISTS household_members (
                    household_id INTEGER NOT NULL REFERENCES households(id) ON DELETE CASCADE,
                    user_id INTEGER NOT NULL UNIQUE REFERENCES users(user_id) ON DELETE CASCADE,
                    joined_at TEXT NOT NULL,
                    PRIMARY KEY (household_id, user_id)
                );

                CREATE TABLE IF NOT EXISTS budget_categories (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    household_id INTEGER NOT NULL REFERENCES households(id) ON DELETE CASCADE,
                    name TEXT NOT NULL,
                    icon TEXT NOT NULL DEFAULT '📁',
                    parent_id INTEGER REFERENCES budget_categories(id) ON DELETE RESTRICT,
                    system_key TEXT,
                    kind TEXT NOT NULL DEFAULT 'expense' CHECK (kind IN ('expense', 'income')),
                    created_by INTEGER REFERENCES users(user_id),
                    created_at TEXT NOT NULL,
                    UNIQUE (household_id, system_key)
                );

                CREATE UNIQUE INDEX IF NOT EXISTS idx_budget_category_name
                    ON budget_categories(household_id, COALESCE(parent_id, 0), lower(name));

                CREATE TABLE IF NOT EXISTS pending_family_joins (
                    household_id INTEGER NOT NULL REFERENCES households(id) ON DELETE CASCADE,
                    user_id INTEGER NOT NULL UNIQUE REFERENCES users(user_id) ON DELETE CASCADE,
                    requested_at TEXT NOT NULL,
                    PRIMARY KEY (household_id, user_id)
                );

                CREATE TABLE IF NOT EXISTS transactions (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
                    amount_kopecks INTEGER NOT NULL CHECK (amount_kopecks > 0),
                    description TEXT NOT NULL,
                    kind TEXT NOT NULL CHECK (kind IN ('expense', 'income')),
                    debt_effect INTEGER NOT NULL DEFAULT 1 CHECK (debt_effect IN (-1, 1)),
                    debt_effect_override INTEGER CHECK (debt_effect_override IN (-1, 1)),
                    category TEXT NOT NULL,
                    household_id INTEGER REFERENCES households(id),
                    budget_category_id INTEGER REFERENCES budget_categories(id),
                    occurred_at TEXT NOT NULL,
                    created_at TEXT NOT NULL
                );

                CREATE INDEX IF NOT EXISTS idx_transactions_user_date
                    ON transactions(user_id, occurred_at);

                CREATE TABLE IF NOT EXISTS daily_reports (
                    user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
                    local_date TEXT NOT NULL,
                    sent_at TEXT NOT NULL,
                    PRIMARY KEY (user_id, local_date)
                );

                CREATE TABLE IF NOT EXISTS family_daily_reports (
                    household_id INTEGER NOT NULL REFERENCES households(id) ON DELETE CASCADE,
                    local_date TEXT NOT NULL,
                    sent_at TEXT NOT NULL,
                    PRIMARY KEY (household_id, local_date)
                );
                """
            )
            columns_cursor = await db.execute("PRAGMA table_info(transactions)")
            columns = {row[1] for row in await columns_cursor.fetchall()}
            if "household_id" not in columns:
                await db.execute(
                    "ALTER TABLE transactions ADD COLUMN household_id INTEGER REFERENCES households(id)"
                )
            if "budget_category_id" not in columns:
                await db.execute(
                    "ALTER TABLE transactions ADD COLUMN budget_category_id INTEGER "
                    "REFERENCES budget_categories(id)"
                )
            if "debt_effect" not in columns:
                await db.execute(
                    "ALTER TABLE transactions ADD COLUMN debt_effect INTEGER NOT NULL DEFAULT 1"
                )
                await db.execute(
                    "UPDATE transactions SET debt_effect=CASE WHEN kind='income' THEN -1 ELSE 1 END"
                )
            if "debt_effect_override" not in columns:
                await db.execute(
                    "ALTER TABLE transactions ADD COLUMN debt_effect_override INTEGER"
                )
            await db.execute(
                "CREATE INDEX IF NOT EXISTS idx_transactions_household_date "
                "ON transactions(household_id, occurred_at)"
            )
            household_columns_cursor = await db.execute("PRAGMA table_info(households)")
            household_columns = {row[1] for row in await household_columns_cursor.fetchall()}
            if "notification_chat_id" not in household_columns:
                await db.execute("ALTER TABLE households ADD COLUMN notification_chat_id INTEGER")
            if "notification_chat_title" not in household_columns:
                await db.execute("ALTER TABLE households ADD COLUMN notification_chat_title TEXT")
            if "debt_base_kopecks" not in household_columns:
                await db.execute("ALTER TABLE households ADD COLUMN debt_base_kopecks INTEGER")
            if "debt_started_at" not in household_columns:
                await db.execute("ALTER TABLE households ADD COLUMN debt_started_at TEXT")
            category_columns_cursor = await db.execute("PRAGMA table_info(budget_categories)")
            category_columns = {row[1] for row in await category_columns_cursor.fetchall()}
            if "kind" not in category_columns:
                await db.execute(
                    "ALTER TABLE budget_categories ADD COLUMN kind TEXT NOT NULL DEFAULT 'expense'"
                )
                await db.execute(
                    """
                    UPDATE budget_categories
                    SET kind=CASE
                        WHEN system_key='income' OR system_key='salary'
                             OR system_key LIKE 'salary_%' THEN 'income'
                        ELSE 'expense'
                    END
                    """
                )
            household_cursor = await db.execute("SELECT id FROM households")
            for household_row in await household_cursor.fetchall():
                await self._seed_default_categories(db, int(household_row[0]))
            await db.execute(
                """
                UPDATE transactions
                SET budget_category_id=(
                    SELECT bc.id FROM budget_categories bc
                    WHERE bc.household_id=transactions.household_id
                      AND bc.system_key=transactions.category
                )
                WHERE household_id IS NOT NULL AND budget_category_id IS NULL
                """
            )
            await db.commit()

    async def upsert_user(
        self,
        *,
        user_id: int,
        chat_id: int,
        username: str | None,
        full_name: str,
        timezone: str,
        report_time: str,
        now: datetime,
    ) -> None:
        timestamp = now.isoformat()
        async with aiosqlite.connect(self.path) as db:
            await db.execute(
                """
                INSERT INTO users (
                    user_id, chat_id, username, full_name, timezone,
                    report_time, created_at, updated_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(user_id) DO UPDATE SET
                    chat_id=excluded.chat_id,
                    username=excluded.username,
                    full_name=excluded.full_name,
                    timezone=excluded.timezone,
                    report_time=excluded.report_time,
                    updated_at=excluded.updated_at
                """,
                (user_id, chat_id, username, full_name, timezone, report_time, timestamp, timestamp),
            )
            await db.commit()

    async def add_transaction(
        self,
        *,
        user_id: int,
        amount_kopecks: int,
        description: str,
        kind: TransactionKind,
        category: Category,
        debt_effect: int | None = None,
        debt_effect_override: int | None = None,
        budget_category_id: int | None = None,
        occurred_at: datetime,
    ) -> Transaction:
        if debt_effect is None:
            debt_effect = -1 if kind is TransactionKind.INCOME else 1
        if debt_effect not in (-1, 1):
            raise ValueError("Влияние операции на долг должно быть -1 или 1")
        if debt_effect_override not in (None, -1, 1):
            raise ValueError("Переопределение влияния на долг должно быть -1, 1 или None")
        now = datetime.now(tz=occurred_at.tzinfo)
        async with aiosqlite.connect(self.path) as db:
            user_cursor = await db.execute(
                """
                SELECT hm.household_id, u.full_name
                FROM users u
                LEFT JOIN household_members hm ON hm.user_id=u.user_id
                WHERE u.user_id=?
                """,
                (user_id,),
            )
            user_row = await user_cursor.fetchone()
            household_id = user_row[0] if user_row else None
            author_name = str(user_row[1]) if user_row else None
            cursor = await db.execute(
                """
                INSERT INTO transactions (
                    user_id, amount_kopecks, description, kind, debt_effect,
                    debt_effect_override, category, household_id,
                    budget_category_id, occurred_at, created_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    user_id,
                    amount_kopecks,
                    description,
                    kind.value,
                    debt_effect,
                    debt_effect_override,
                    category.value,
                    household_id,
                    budget_category_id,
                    occurred_at.isoformat(),
                    now.isoformat(),
                ),
            )
            await db.commit()
            transaction_id = int(cursor.lastrowid)
        transaction = await self.get_transaction(transaction_id, user_id)
        if transaction is None:
            raise RuntimeError("Операция не сохранилась")
        return transaction

    async def get_transaction(self, transaction_id: int, user_id: int) -> Transaction | None:
        async with aiosqlite.connect(self.path) as db:
            db.row_factory = aiosqlite.Row
            cursor = await db.execute(
                """
                SELECT t.*, u.full_name AS author_name,
                       c.name AS budget_category_name, c.icon AS budget_category_icon,
                       p.name AS parent_category_name, p.icon AS parent_category_icon
                FROM transactions t JOIN users u ON u.user_id=t.user_id
                LEFT JOIN budget_categories c ON c.id=t.budget_category_id
                LEFT JOIN budget_categories p ON p.id=c.parent_id
                WHERE t.id=? AND (
                    t.user_id=? OR EXISTS (
                        SELECT 1 FROM household_members hm
                        WHERE hm.user_id=? AND hm.household_id=t.household_id
                    )
                )
                """,
                (transaction_id, user_id, user_id),
            )
            row = await cursor.fetchone()
        return self._row_to_transaction(row) if row else None

    async def update_category(
        self, transaction_id: int, user_id: int, category: Category
    ) -> bool:
        async with aiosqlite.connect(self.path) as db:
            cursor = await db.execute(
                "UPDATE transactions SET category=? WHERE id=? AND user_id=? AND kind='expense'",
                (category.value, transaction_id, user_id),
            )
            await db.commit()
            return cursor.rowcount > 0

    async def delete_transaction(self, transaction_id: int, user_id: int) -> bool:
        async with aiosqlite.connect(self.path) as db:
            cursor = await db.execute(
                "DELETE FROM transactions WHERE id=? AND user_id=?",
                (transaction_id, user_id),
            )
            await db.commit()
            return cursor.rowcount > 0

    async def totals(
        self, user_id: int, start: datetime | None = None, end: datetime | None = None
    ) -> Totals:
        where = [self._scope_clause("t")]
        params: list[object] = [user_id, user_id]
        if start is not None:
            where.append("occurred_at >= ?")
            params.append(start.isoformat())
        if end is not None:
            where.append("occurred_at < ?")
            params.append(end.isoformat())
        query = f"""
            SELECT
                COALESCE(SUM(CASE WHEN kind='income' THEN amount_kopecks ELSE 0 END), 0),
                COALESCE(SUM(CASE WHEN kind='expense' THEN amount_kopecks ELSE 0 END), 0)
            FROM transactions t WHERE {' AND '.join(where)}
        """
        async with aiosqlite.connect(self.path) as db:
            cursor = await db.execute(query, params)
            row = await cursor.fetchone()
        return Totals(income=int(row[0]), expense=int(row[1]))

    async def category_totals(
        self, user_id: int, start: datetime | None = None, end: datetime | None = None
    ) -> dict[str, int]:
        where = [self._scope_clause("t"), "t.kind='expense'"]
        params: list[object] = [user_id, user_id]
        if start is not None:
            where.append("occurred_at>=?")
            params.append(start.isoformat())
        if end is not None:
            where.append("occurred_at<?")
            params.append(end.isoformat())
        async with aiosqlite.connect(self.path) as db:
            cursor = await db.execute(
                f"""
                SELECT t.category, c.name, c.icon, p.name, p.icon,
                       SUM(t.amount_kopecks) AS total
                FROM transactions t
                LEFT JOIN budget_categories c ON c.id=t.budget_category_id
                LEFT JOIN budget_categories p ON p.id=c.parent_id
                WHERE {' AND '.join(where)}
                GROUP BY t.category, c.id, p.id ORDER BY total DESC
                """,
                params,
            )
            rows = await cursor.fetchall()
        result: dict[str, int] = {}
        for legacy_key, name, icon, parent_name, parent_icon, total in rows:
            if name and parent_name:
                label = f"{parent_icon or '📁'} {parent_name} → {icon or '📁'} {name}"
            elif name:
                label = f"{icon or '📁'} {name}"
            else:
                label = category_label(Category(legacy_key))
            result[label] = result.get(label, 0) + int(total)
        return result

    async def recent_transactions(self, user_id: int, limit: int = 10) -> list[Transaction]:
        async with aiosqlite.connect(self.path) as db:
            db.row_factory = aiosqlite.Row
            cursor = await db.execute(
                f"""
                SELECT t.*, u.full_name AS author_name,
                       c.name AS budget_category_name, c.icon AS budget_category_icon,
                       p.name AS parent_category_name, p.icon AS parent_category_icon
                FROM transactions t JOIN users u ON u.user_id=t.user_id
                LEFT JOIN budget_categories c ON c.id=t.budget_category_id
                LEFT JOIN budget_categories p ON p.id=c.parent_id
                WHERE {self._scope_clause("t")}
                ORDER BY t.occurred_at DESC, t.id DESC LIMIT ?
                """,
                (user_id, user_id, limit),
            )
            rows = await cursor.fetchall()
        return [self._row_to_transaction(row) for row in rows]

    async def create_family(self, user_id: int, now: datetime) -> FamilyInfo:
        async with aiosqlite.connect(self.path) as db:
            await db.execute("PRAGMA foreign_keys=ON")
            await db.execute("BEGIN IMMEDIATE")
            existing = await self._family_info_in_connection(db, user_id)
            if existing is not None:
                await db.rollback()
                return existing

            for _ in range(10):
                code = "FIN-" + "".join(
                    secrets.choice("ABCDEFGHJKLMNPQRSTUVWXYZ23456789") for _ in range(6)
                )
                try:
                    cursor = await db.execute(
                        "INSERT INTO households(invite_code, created_by, created_at) VALUES (?, ?, ?)",
                        (code, user_id, now.isoformat()),
                    )
                    break
                except aiosqlite.IntegrityError:
                    continue
            else:
                await db.rollback()
                raise RuntimeError("Не удалось создать уникальный код семьи")

            household_id = int(cursor.lastrowid)
            await self._seed_default_categories(db, household_id)
            await db.execute(
                "INSERT INTO household_members(household_id, user_id, joined_at) VALUES (?, ?, ?)",
                (household_id, user_id, now.isoformat()),
            )
            await db.execute(
                "UPDATE transactions SET household_id=? WHERE user_id=? AND household_id IS NULL",
                (household_id, user_id),
            )
            await db.execute(
                """
                UPDATE transactions
                SET budget_category_id=(
                    SELECT id FROM budget_categories
                    WHERE household_id=? AND system_key=transactions.category
                )
                WHERE user_id=? AND household_id=? AND budget_category_id IS NULL
                """,
                (household_id, user_id, household_id),
            )
            await db.commit()
        info = await self.family_info(user_id)
        if info is None:
            raise RuntimeError("Семья не сохранилась")
        return info

    async def request_family_join(
        self, user_id: int, invite_code: str, now: datetime
    ) -> FamilyJoinRequest:
        code = invite_code.strip().upper()
        async with aiosqlite.connect(self.path) as db:
            await db.execute("PRAGMA foreign_keys=ON")
            await db.execute("BEGIN IMMEDIATE")
            if await self._family_info_in_connection(db, user_id) is not None:
                await db.rollback()
                raise FamilyJoinError("Вы уже состоите в семейном бюджете")
            cursor = await db.execute(
                """
                SELECT h.id, owner.chat_id, candidate.full_name,
                       (SELECT COUNT(*) FROM household_members WHERE household_id=h.id)
                FROM households h
                JOIN users owner ON owner.user_id=h.created_by
                JOIN users candidate ON candidate.user_id=?
                WHERE h.invite_code=?
                """,
                (user_id, code),
            )
            row = await cursor.fetchone()
            if row is None:
                await db.rollback()
                raise FamilyJoinError("Код приглашения не найден")
            if int(row[3]) >= 2:
                await db.rollback()
                raise FamilyJoinError("В этом семейном бюджете уже два участника")
            await db.execute(
                """
                INSERT INTO pending_family_joins(household_id, user_id, requested_at)
                VALUES (?, ?, ?)
                ON CONFLICT(user_id) DO UPDATE SET
                    household_id=excluded.household_id,
                    requested_at=excluded.requested_at
                """,
                (row[0], user_id, now.isoformat()),
            )
            await db.commit()
            return FamilyJoinRequest(
                candidate_user_id=user_id,
                candidate_name=str(row[2]),
                owner_chat_id=int(row[1]),
            )

    async def resolve_family_join(
        self,
        *,
        owner_user_id: int,
        candidate_user_id: int,
        approve: bool,
        now: datetime,
    ) -> tuple[bool, int, FamilyInfo | None]:
        async with aiosqlite.connect(self.path) as db:
            await db.execute("PRAGMA foreign_keys=ON")
            await db.execute("BEGIN IMMEDIATE")
            cursor = await db.execute(
                """
                SELECT p.household_id, u.chat_id
                FROM pending_family_joins p
                JOIN households h ON h.id=p.household_id
                JOIN users u ON u.user_id=p.user_id
                WHERE h.created_by=? AND p.user_id=?
                """,
                (owner_user_id, candidate_user_id),
            )
            row = await cursor.fetchone()
            if row is None:
                await db.rollback()
                raise FamilyJoinError("Запрос уже обработан или не найден")
            household_id, candidate_chat_id = int(row[0]), int(row[1])
            await db.execute(
                "DELETE FROM pending_family_joins WHERE household_id=? AND user_id=?",
                (household_id, candidate_user_id),
            )
            if not approve:
                await db.commit()
                return False, candidate_chat_id, None
            count_cursor = await db.execute(
                "SELECT COUNT(*) FROM household_members WHERE household_id=?",
                (household_id,),
            )
            if int((await count_cursor.fetchone())[0]) >= 2:
                await db.rollback()
                raise FamilyJoinError("В семейном бюджете уже два участника")
            await db.execute(
                "INSERT INTO household_members(household_id, user_id, joined_at) VALUES (?, ?, ?)",
                (household_id, candidate_user_id, now.isoformat()),
            )
            await db.execute(
                "UPDATE transactions SET household_id=? WHERE user_id=? AND household_id IS NULL",
                (household_id, candidate_user_id),
            )
            await db.execute(
                """
                UPDATE transactions
                SET budget_category_id=(
                    SELECT id FROM budget_categories
                    WHERE household_id=? AND system_key=transactions.category
                )
                WHERE user_id=? AND household_id=? AND budget_category_id IS NULL
                """,
                (household_id, candidate_user_id, household_id),
            )
            await db.commit()
        return True, candidate_chat_id, await self.family_info(candidate_user_id)

    async def bind_family_chat(
        self, owner_user_id: int, chat_id: int, chat_title: str
    ) -> FamilyInfo:
        async with aiosqlite.connect(self.path) as db:
            cursor = await db.execute(
                """
                UPDATE households
                SET notification_chat_id=?, notification_chat_title=?
                WHERE created_by=?
                """,
                (chat_id, chat_title, owner_user_id),
            )
            await db.commit()
            if cursor.rowcount == 0:
                raise FamilyJoinError("Привязать чат может только создатель семейного бюджета")
        info = await self.family_info(owner_user_id)
        if info is None:
            raise FamilyJoinError("Семейный бюджет не найден")
        return info

    async def set_family_debt(
        self, owner_user_id: int, amount_kopecks: int, now: datetime
    ) -> int:
        if amount_kopecks < 0:
            raise ValueError("Долг не может быть отрицательным")
        async with aiosqlite.connect(self.path) as db:
            cursor = await db.execute(
                """
                UPDATE households
                SET debt_base_kopecks=?, debt_started_at=?
                WHERE created_by=?
                """,
                (amount_kopecks, now.isoformat(), owner_user_id),
            )
            await db.commit()
            if cursor.rowcount == 0:
                raise FamilyJoinError("Изменить общий долг может только создатель бюджета")
        return amount_kopecks

    async def family_debt(self, user_id: int) -> int | None:
        async with aiosqlite.connect(self.path) as db:
            cursor = await db.execute(
                """
                SELECT h.debt_base_kopecks, h.debt_started_at,
                       COALESCE(SUM(t.amount_kopecks * t.debt_effect), 0)
                FROM households h
                JOIN household_members hm ON hm.household_id=h.id
                LEFT JOIN transactions t
                    ON t.household_id=h.id AND t.occurred_at>h.debt_started_at
                WHERE hm.user_id=?
                GROUP BY h.id
                """,
                (user_id,),
            )
            row = await cursor.fetchone()
        if row is None or row[0] is None or row[1] is None:
            return None
        return int(row[0]) + int(row[2])

    async def budget_categories(self, user_id: int) -> list[BudgetCategory]:
        async with aiosqlite.connect(self.path) as db:
            cursor = await db.execute(
                """
                SELECT c.id, c.name, c.icon, c.parent_id, p.name, p.icon,
                       c.system_key, c.kind
                FROM household_members hm
                JOIN budget_categories c ON c.household_id=hm.household_id
                LEFT JOIN budget_categories p ON p.id=c.parent_id
                WHERE hm.user_id=? AND (c.system_key IS NULL OR c.system_key<>'income')
                ORDER BY COALESCE(p.id, c.id), c.parent_id IS NOT NULL, c.name
                """,
                (user_id,),
            )
            rows = await cursor.fetchall()
        return [
            BudgetCategory(
                id=int(row[0]),
                name=str(row[1]),
                icon=str(row[2]),
                parent_id=int(row[3]) if row[3] is not None else None,
                parent_name=str(row[4]) if row[4] is not None else None,
                parent_icon=str(row[5]) if row[5] is not None else None,
                system_key=str(row[6]) if row[6] is not None else None,
                kind=str(row[7]),
            )
            for row in rows
        ]

    async def resolve_budget_category(
        self, user_id: int, main_category: Category, subcategory_key: str | None
    ) -> int | None:
        key = subcategory_key or main_category.value
        async with aiosqlite.connect(self.path) as db:
            cursor = await db.execute(
                """
                SELECT c.id
                FROM household_members hm
                JOIN budget_categories c ON c.household_id=hm.household_id
                WHERE hm.user_id=? AND c.system_key=?
                """,
                (user_id, key),
            )
            row = await cursor.fetchone()
        return int(row[0]) if row else None

    async def resolve_salary_category(self, user_id: int) -> int | None:
        async with aiosqlite.connect(self.path) as db:
            cursor = await db.execute(
                """
                SELECT c.id
                FROM household_members hm
                JOIN households h ON h.id=hm.household_id
                JOIN budget_categories c ON c.household_id=h.id
                WHERE hm.user_id=?
                  AND c.system_key=CASE
                      WHEN h.created_by=? THEN 'salary_vitya'
                      ELSE 'salary_yana'
                  END
                """,
                (user_id, user_id),
            )
            row = await cursor.fetchone()
        return int(row[0]) if row else None

    async def create_budget_category(
        self,
        *,
        user_id: int,
        name: str,
        parent_id: int | None,
        kind: str = "expense",
        now: datetime,
    ) -> BudgetCategory:
        clean_name = " ".join(name.split()).strip()
        if not 2 <= len(clean_name) <= 40:
            raise ValueError("Название должно содержать от 2 до 40 символов")
        async with aiosqlite.connect(self.path) as db:
            await db.execute("PRAGMA foreign_keys=ON")
            household_cursor = await db.execute(
                "SELECT household_id FROM household_members WHERE user_id=?", (user_id,)
            )
            household_row = await household_cursor.fetchone()
            if household_row is None:
                raise FamilyJoinError("Категории настраиваются внутри семейного бюджета")
            household_id = int(household_row[0])
            count_cursor = await db.execute(
                "SELECT COUNT(*) FROM budget_categories WHERE household_id=?", (household_id,)
            )
            if int((await count_cursor.fetchone())[0]) >= 60:
                raise ValueError("Достигнут лимит в 60 категорий и подкатегорий")
            if parent_id is not None:
                parent_cursor = await db.execute(
                    """
                    SELECT kind FROM budget_categories
                    WHERE id=? AND household_id=? AND parent_id IS NULL
                      AND (system_key IS NULL OR system_key<>'income')
                    """,
                    (parent_id, household_id),
                )
                parent_row = await parent_cursor.fetchone()
                if parent_row is None:
                    raise ValueError("Основная категория не найдена")
                kind = str(parent_row[0])
            elif kind not in {"expense", "income"}:
                raise ValueError("Тип категории должен быть expense или income")
            try:
                cursor = await db.execute(
                    """
                    INSERT INTO budget_categories(
                        household_id, name, icon, parent_id, kind, created_by, created_at
                    ) VALUES (?, ?, '📁', ?, ?, ?, ?)
                    """,
                    (household_id, clean_name, parent_id, kind, user_id, now.isoformat()),
                )
                await db.commit()
            except aiosqlite.IntegrityError as exc:
                raise ValueError("Такая категория уже существует") from exc
            category_id = int(cursor.lastrowid)
        return next(
            category for category in await self.budget_categories(user_id)
            if category.id == category_id
        )

    async def update_transaction_budget_category(
        self, transaction_id: int, user_id: int, category_id: int
    ) -> bool:
        async with aiosqlite.connect(self.path) as db:
            cursor = await db.execute(
                """
                UPDATE transactions
                SET budget_category_id=?,
                    kind=(SELECT kind FROM budget_categories WHERE id=?),
                    debt_effect=CASE
                        WHEN debt_effect_override IS NOT NULL THEN debt_effect_override
                        WHEN (SELECT kind FROM budget_categories WHERE id=?)='income' THEN -1
                        ELSE 1
                    END
                WHERE id=?
                  AND (user_id=? OR EXISTS (
                      SELECT 1 FROM household_members hm
                      WHERE hm.user_id=? AND hm.household_id=transactions.household_id
                  ))
                  AND household_id=(
                      SELECT household_id FROM budget_categories WHERE id=?
                  )
                """,
                (
                    category_id, category_id, category_id,
                    transaction_id, user_id, user_id, category_id,
                ),
            )
            await db.commit()
            return cursor.rowcount > 0

    async def family_info(self, user_id: int) -> FamilyInfo | None:
        async with aiosqlite.connect(self.path) as db:
            return await self._family_info_in_connection(db, user_id)

    async def family_recipients(self, user_id: int) -> list[aiosqlite.Row]:
        async with aiosqlite.connect(self.path) as db:
            db.row_factory = aiosqlite.Row
            cursor = await db.execute(
                """
                SELECT u.user_id, u.chat_id, u.full_name
                FROM household_members mine
                JOIN household_members other ON other.household_id=mine.household_id
                JOIN users u ON u.user_id=other.user_id
                WHERE mine.user_id=? AND other.user_id<>?
                """,
                (user_id, user_id),
            )
            return await cursor.fetchall()

    async def family_notification_chats(self, user_id: int) -> list[int]:
        recipients = [int(row["chat_id"]) for row in await self.family_recipients(user_id)]
        info = await self.family_info(user_id)
        if info and info.notification_chat_id is not None:
            recipients.append(info.notification_chat_id)
        return list(dict.fromkeys(recipients))

    async def _family_info_in_connection(
        self, db: aiosqlite.Connection, user_id: int
    ) -> FamilyInfo | None:
        cursor = await db.execute(
            """
            SELECT h.id, h.invite_code, h.created_by,
                   h.notification_chat_id, h.notification_chat_title
            FROM households h
            JOIN household_members hm ON hm.household_id=h.id
            WHERE hm.user_id=?
            """,
            (user_id,),
        )
        row = await cursor.fetchone()
        if row is None:
            return None
        members_cursor = await db.execute(
            """
            SELECT u.full_name
            FROM household_members hm JOIN users u ON u.user_id=hm.user_id
            WHERE hm.household_id=? ORDER BY hm.joined_at
            """,
            (row[0],),
        )
        members = tuple(str(member[0]) for member in await members_cursor.fetchall())
        return FamilyInfo(
            household_id=int(row[0]),
            invite_code=str(row[1]),
            members=members,
            owner_user_id=int(row[2]),
            notification_chat_id=int(row[3]) if row[3] is not None else None,
            notification_chat_title=str(row[4]) if row[4] is not None else None,
        )

    async def family_report_target(self, user_id: int) -> tuple[int, int] | None:
        info = await self.family_info(user_id)
        if info is None or info.notification_chat_id is None:
            return None
        return info.household_id, info.notification_chat_id

    async def family_report_was_sent(self, household_id: int, local_date: str) -> bool:
        async with aiosqlite.connect(self.path) as db:
            cursor = await db.execute(
                "SELECT 1 FROM family_daily_reports WHERE household_id=? AND local_date=?",
                (household_id, local_date),
            )
            return await cursor.fetchone() is not None

    async def mark_family_report_sent(
        self, household_id: int, local_date: str, sent_at: datetime
    ) -> None:
        async with aiosqlite.connect(self.path) as db:
            await db.execute(
                """
                INSERT OR IGNORE INTO family_daily_reports(household_id, local_date, sent_at)
                VALUES (?, ?, ?)
                """,
                (household_id, local_date, sent_at.isoformat()),
            )
            await db.commit()

    async def users_for_reports(self) -> list[aiosqlite.Row]:
        async with aiosqlite.connect(self.path) as db:
            db.row_factory = aiosqlite.Row
            cursor = await db.execute(
                "SELECT user_id, chat_id, timezone, report_time FROM users"
            )
            return await cursor.fetchall()

    async def report_was_sent(self, user_id: int, local_date: str) -> bool:
        async with aiosqlite.connect(self.path) as db:
            cursor = await db.execute(
                "SELECT 1 FROM daily_reports WHERE user_id=? AND local_date=?",
                (user_id, local_date),
            )
            return await cursor.fetchone() is not None

    async def mark_report_sent(self, user_id: int, local_date: str, sent_at: datetime) -> None:
        async with aiosqlite.connect(self.path) as db:
            await db.execute(
                "INSERT OR IGNORE INTO daily_reports(user_id, local_date, sent_at) VALUES (?, ?, ?)",
                (user_id, local_date, sent_at.isoformat()),
            )
            await db.commit()

    @staticmethod
    def _row_to_transaction(row: aiosqlite.Row) -> Transaction:
        return Transaction(
            id=int(row["id"]),
            user_id=int(row["user_id"]),
            amount_kopecks=int(row["amount_kopecks"]),
            description=str(row["description"]),
            kind=TransactionKind(row["kind"]),
            category=Category(row["category"]),
            debt_effect=int(row["debt_effect"]),
            occurred_at=datetime.fromisoformat(row["occurred_at"]),
            author_name=str(row["author_name"]) if "author_name" in row.keys() else None,
            budget_category_id=(
                int(row["budget_category_id"])
                if "budget_category_id" in row.keys() and row["budget_category_id"] is not None
                else None
            ),
            budget_category_name=(
                str(row["budget_category_name"])
                if "budget_category_name" in row.keys() and row["budget_category_name"] is not None
                else None
            ),
            budget_category_icon=(
                str(row["budget_category_icon"])
                if "budget_category_icon" in row.keys() and row["budget_category_icon"] is not None
                else None
            ),
            parent_category_name=(
                str(row["parent_category_name"])
                if "parent_category_name" in row.keys() and row["parent_category_name"] is not None
                else None
            ),
            parent_category_icon=(
                str(row["parent_category_icon"])
                if "parent_category_icon" in row.keys() and row["parent_category_icon"] is not None
                else None
            ),
        )

    @staticmethod
    async def _seed_default_categories(
        db: aiosqlite.Connection, household_id: int
    ) -> None:
        created_at = datetime.now().astimezone().isoformat()
        for category in Category:
            icon, name = CATEGORY_META[category]
            kind = "income" if category is Category.INCOME else "expense"
            await db.execute(
                """
                INSERT OR IGNORE INTO budget_categories(
                    household_id, name, icon, parent_id, system_key, kind, created_at
                ) VALUES (?, ?, ?, NULL, ?, ?, ?)
                """,
                (household_id, name, icon, category.value, kind, created_at),
            )
            parent_cursor = await db.execute(
                "SELECT id FROM budget_categories WHERE household_id=? AND system_key=?",
                (household_id, category.value),
            )
            parent_id = int((await parent_cursor.fetchone())[0])
            for system_key, sub_icon, sub_name, _keywords in DEFAULT_SUBCATEGORIES.get(
                category, ()
            ):
                await db.execute(
                    """
                    INSERT OR IGNORE INTO budget_categories(
                        household_id, name, icon, parent_id, system_key, kind, created_at
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)
                    """,
                    (household_id, sub_name, sub_icon, parent_id, system_key, kind, created_at),
                )

        await db.execute(
            """
            INSERT OR IGNORE INTO budget_categories(
                household_id, name, icon, parent_id, system_key, kind, created_at
            ) VALUES (?, 'Зарплата', '💰', NULL, 'salary', 'income', ?)
            """,
            (household_id, created_at),
        )
        salary_cursor = await db.execute(
            "SELECT id FROM budget_categories WHERE household_id=? AND system_key='salary'",
            (household_id,),
        )
        salary_parent_id = int((await salary_cursor.fetchone())[0])
        for system_key, name in (
            ("salary_yana", "Зарплата Яна"),
            ("salary_vitya", "Зарплата Витя"),
        ):
            await db.execute(
                """
                INSERT OR IGNORE INTO budget_categories(
                    household_id, name, icon, parent_id, system_key, kind, created_at
                ) VALUES (?, ?, '💵', ?, ?, 'income', ?)
                """,
                (household_id, name, salary_parent_id, system_key, created_at),
            )

    @staticmethod
    def _scope_clause(alias: str) -> str:
        return f"""(
            ({alias}.household_id IS NOT NULL AND {alias}.household_id=(
                SELECT household_id FROM household_members WHERE user_id=?
            ))
            OR ({alias}.household_id IS NULL AND {alias}.user_id=?)
        )"""