991 lines
43 KiB
Python
991 lines
43 KiB
Python
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=?)
|
||
)"""
|