[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_105":106,"_107":108},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","tambovtsev007","name","ufin","path","index.ts","project",{"_22":45,"_11":12,"_33":46,"_47":-5,"_48":42,"_49":50,"_51":50,"_52":53,"_54":-5,"_55":56,"_57":25,"_58":32,"_59":-5,"_60":-5,"_61":62,"_63":64,"_65":66,"_67":68,"_69":70,"_71":-5,"_72":73,"_74":73,"_75":76,"_77":76,"_78":76,"_79":73,"_80":73,"_81":82,"_83":84,"_85":86,"_87":42},"file",{"_22":23,"_11":14,"_24":25,"_26":-5,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":-5,"_13":44},"permalink","https://www.val.town/x/tambovtsev007/ufin/code/index.ts","extension","id","9f9a92b2-d1b5-4429-99fa-58d53f75c93b","fileId","8193b528-9caa-11f1-a2f1-1607ee4eb77e","parentId","branchId","4e7c594c-7131-44af-85f7-c363fcbc85c7","branchVersion",61,"latest",true,"createdAt",["D",1787314914799],"version",8,"fileType","http","content","// U.fin v2 — финансовая система агентства недвижимости по скелету ПланФакта\n// Backend: Hono + SQLite (val-scoped). Вход по PIN.\nimport { Hono } from \"npm:hono\";\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { parseVal, readFile, serveFile } from \"https://esm.town/v/std/utils/index.ts\";\n\nconst app = new Hono();\napp.onError((err) => Promise.reject(err));\n\nconst today = () => new Date().toISOString().slice(0, 10);\nconst num = (v: unknown) => (v === null || v === undefined || v === \"\" ? 0 : Number(v));\n\n// ---------------- Схема БД ----------------\nawait sqlite.batch([\n `CREATE TABLE IF NOT EXISTS pf_accounts (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n acc_type TEXT DEFAULT 'Безналичный', -- Наличный | Безналичный | Карта физлица | Электронный\n bank TEXT DEFAULT '',\n number TEXT DEFAULT '',\n grp TEXT DEFAULT '', -- группа: Основной счет, Депозиты, Налоговый...\n start_balance REAL DEFAULT 0,\n start_date TEXT,\n active INTEGER DEFAULT 1\n )`,\n `CREATE TABLE IF NOT EXISTS pf_categories (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n kind TEXT NOT NULL, -- income | expense\n parent_id INTEGER,\n sort INTEGER DEFAULT 0\n )`,\n `CREATE TABLE IF NOT EXISTS pf_contractors (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n grp TEXT DEFAULT '', -- Застройщик | Брокер (ИП) | Банк | Сотрудник | Прочее\n inn TEXT DEFAULT '',\n comment TEXT DEFAULT ''\n )`,\n `CREATE TABLE IF NOT EXISTS pf_deals (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL, -- «Брокер №J1207»\n client TEXT DEFAULT '',\n status TEXT DEFAULT 'work', -- work | nuance | done | fail\n amount REAL DEFAULT 0, -- сумма сделки (комиссия)\n created_at TEXT,\n comment TEXT DEFAULT ''\n )`,\n `CREATE TABLE IF NOT EXISTS pf_ops (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n date TEXT NOT NULL,\n op_type TEXT NOT NULL, -- income | expense | transfer\n confirmed INTEGER DEFAULT 1, -- 1 = оплата подтверждена (факт), 0 = план\n amount REAL NOT NULL,\n account_id INTEGER, -- счёт (для transfer — со счёта)\n account2_id INTEGER, -- для transfer — на счёт\n category_id INTEGER,\n contractor_id INTEGER,\n deal_id INTEGER,\n purpose TEXT DEFAULT '' -- назначение платежа\n )`,\n `CREATE TABLE IF NOT EXISTS pf_settings (key TEXT PRIMARY KEY, value TEXT)`,\n `CREATE TABLE IF NOT EXISTS pf_projects (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n comment TEXT DEFAULT '',\n active INTEGER DEFAULT 1\n )`,\n `CREATE TABLE IF NOT EXISTS pf_budget (\n period TEXT NOT NULL, -- 'YYYY-MM'\n category_id INTEGER NOT NULL,\n amount REAL DEFAULT 0,\n PRIMARY KEY (period, category_id)\n )`,\n `CREATE TABLE IF NOT EXISTS pf_legal_entities (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n inn TEXT DEFAULT '',\n comment TEXT DEFAULT ''\n )`,\n `CREATE TABLE IF NOT EXISTS pf_products (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n ptype TEXT DEFAULT 'service', -- product | service\n unit TEXT DEFAULT 'шт',\n price REAL DEFAULT 0\n )`,\n `CREATE TABLE IF NOT EXISTS pf_shipments (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n deal_id INTEGER NOT NULL,\n date TEXT NOT NULL,\n amount REAL NOT NULL,\n comment TEXT DEFAULT ''\n )`,\n `CREATE TABLE IF NOT EXISTS pf_deal_items (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n deal_id INTEGER NOT NULL,\n name TEXT NOT NULL,\n qty REAL DEFAULT 1,\n unit TEXT DEFAULT 'шт',\n price REAL DEFAULT 0\n )`,\n `CREATE TABLE IF NOT EXISTS pf_deal_comments (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n deal_id INTEGER NOT NULL,\n created_at TEXT,\n text TEXT NOT NULL\n )`,\n `CREATE TABLE IF NOT EXISTS pf_budgets (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n btype TEXT DEFAULT 'bdr', -- bdr | bdds\n project_id INTEGER,\n year TEXT,\n updated_at TEXT\n )`,\n `CREATE TABLE IF NOT EXISTS pf_budget_values (\n budget_id INTEGER NOT NULL,\n row_key TEXT NOT NULL, -- inc_ | out_ | var|fix|oi|oe|dep|int|tax\n period TEXT NOT NULL, -- YYYY-MM\n amount REAL DEFAULT 0,\n PRIMARY KEY (budget_id, row_key, period)\n )`,\n `CREATE TABLE IF NOT EXISTS pf_invoices (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n number TEXT DEFAULT '',\n date TEXT NOT NULL,\n contractor_id INTEGER,\n client TEXT DEFAULT '',\n deal_id INTEGER,\n amount REAL DEFAULT 0,\n status TEXT DEFAULT 'unpaid', -- unpaid | paid\n comment TEXT DEFAULT ''\n )`,\n]);\ntry { await sqlite.execute(`ALTER TABLE pf_ops ADD COLUMN project_id INTEGER`); } catch (_e) { /* колонка уже есть */ }\ntry { await sqlite.execute(`ALTER TABLE pf_deals ADD COLUMN deal_type TEXT DEFAULT 'sale'`); } catch (_e) { /* есть */ }\ntry { await sqlite.execute(`ALTER TABLE pf_accounts ADD COLUMN legal_entity_id INTEGER`); } catch (_e) { /* есть */ }\ntry { await sqlite.execute(`ALTER TABLE pf_projects ADD COLUMN grp TEXT DEFAULT ''`); } catch (_e) { /* есть */ }\ntry { await sqlite.execute(`ALTER TABLE pf_projects ADD COLUMN status TEXT DEFAULT 'work'`); } catch (_e) { /* есть */ }\ntry { await sqlite.execute(`ALTER TABLE pf_projects ADD COLUMN date_start TEXT`); } catch (_e) { /* есть */ }\ntry { await sqlite.execute(`ALTER TABLE pf_projects ADD COLUMN date_end TEXT`); } catch (_e) { /* есть */ }\ntry { await sqlite.execute(`ALTER TABLE pf_ops ADD COLUMN ext_id TEXT`); } catch (_e) { /* есть */ }\ntry { await sqlite.execute(`ALTER TABLE pf_categories ADD COLUMN cat_class TEXT DEFAULT ''`); } catch (_e) { /* есть */ }\ntry { await sqlite.execute(`ALTER TABLE pf_deals ADD COLUMN ext_id TEXT`); } catch (_e) { /* есть */ }\ntry { await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_ops_ext ON pf_ops(ext_id)`); } catch (_e) { /* есть */ }\nawait sqlite.execute(\n `INSERT INTO pf_settings(key,value) SELECT 'company_name','Unlock'\n WHERE NOT EXISTS (SELECT 1 FROM pf_settings WHERE key='company_name')`,\n);\n// сид реальных проектов клиента (как в его ПланФакте)\n{\n const pc = (await sqlite.execute(`SELECT COUNT(*) n FROM pf_projects`)).rows[0] as any;\n if (Number(pc.n) <= 1) {\n await sqlite.execute(`DELETE FROM pf_projects`);\n const P: [string, string, string, string | null, string | null][] = [\n [\"Абсолют\", \"ДОП % ОТ КА\", \"work\", \"2025-06-17\", \"2025-06-17\"],\n [\"Аренда вторичной недвижимости\", \"Вторичная недвижимость\", \"work\", \"2025-06-11\", \"2026-06-22\"],\n [\"Аренда загородного объекта недвижимости\", \"Загородная недвижимость\", \"work\", \"2025-06-18\", \"2025-06-19\"],\n [\"Аренда коммерческого объекта\", \"Коммерческая недвижимость\", \"work\", \"2025-04-04\", \"2025-07-14\"],\n [\"Входящий контрагентский\", \"\", \"plan\", null, null],\n [\"Договор подряда (загородная)\", \"Первичная недвижимость\", \"work\", \"2025-08-30\", \"2026-07-13\"],\n [\"КВ ОТ АЛЬФА БАНКА\", \"ДОП % ОТ КА\", \"work\", \"2025-02-10\", \"2026-07-16\"],\n [\"Одобрение Ипотеки\", \"Ипотека\", \"work\", \"2026-07-09\", \"2026-07-09\"],\n [\"Покупка вторичной недвижимости\", \"Вторичная недвижимость\", \"work\", \"2025-01-23\", \"2026-07-22\"],\n [\"Покупка загородного объекта недвижимости\", \"Загородная недвижимость\", \"plan\", null, null],\n [\"Покупка коммерческого объекта\", \"Коммерческая недвижимость\", \"work\", \"2025-05-14\", \"2025-05-16\"],\n [\"Покупка новостройки\", \"Первичная недвижимость\", \"work\", \"2025-01-09\", \"2026-08-12\"],\n [\"Покупка строящегося коммерческого объекта\", \"Первичная недвижимость\", \"work\", \"2025-10-10\", \"2025-12-29\"],\n [\"Продажа вторичной недвижимости\", \"Вторичная недвижимость\", \"work\", \"2025-01-03\", \"2026-08-12\"],\n [\"Продажа загородного объекта недвижимости\", \"Загородная недвижимость\", \"work\", \"2026-07-01\", \"2026-07-21\"],\n [\"Продажа коммерческого объекта\", \"Коммерческая недвижимость\", \"work\", \"2025-04-30\", \"2025-10-06\"],\n [\"Рекламный бюджет\", \"Рекламный бюджет\", \"work\", \"2026-08-12\", \"2026-08-12\"],\n ];\n for (const [name, grp, status, ds, de] of P) {\n await sqlite.execute({\n sql: `INSERT INTO pf_projects (name, grp, status, date_start, date_end) VALUES (?,?,?,?,?)`,\n args: [name, grp, status, ds, de],\n });\n }\n }\n}\n// первичное юрлицо и услуга\n{\n const le = (await sqlite.execute(`SELECT COUNT(*) n FROM pf_legal_entities`)).rows[0] as any;\n if (!Number(le.n)) {\n await sqlite.execute(`INSERT INTO pf_legal_entities (name, inn) VALUES ('Основное юрлицо', '231299600512')`);\n await sqlite.execute(`UPDATE pf_accounts SET legal_entity_id=1 WHERE legal_entity_id IS NULL`);\n }\n const pr = (await sqlite.execute(`SELECT COUNT(*) n FROM pf_products`)).rows[0] as any;\n if (!Number(pr.n)) {\n await sqlite.execute(`INSERT INTO pf_products (name, ptype, unit, price) VALUES ('Привлечение клиента на покупку объекта недвижимости', 'service', 'усл', 0)`);\n }\n}\nawait sqlite.execute(\n `INSERT INTO pf_settings(key,value) SELECT 'pin','1111'\n WHERE NOT EXISTS (SELECT 1 FROM pf_settings WHERE key='pin')`,\n);\n\n// ---------------- Первичное наполнение справочников (как в ПланФакте клиента) ----------------\nasync function seedRefs() {\n const has = (await sqlite.execute(`SELECT COUNT(*) n FROM pf_accounts`)).rows[0] as any;\n if (num(has.n) > 0) return;\n const t = today();\n // Счета (остатки из ПланФакта на 20.08.2026)\n const accounts: [string, string, string, string, string, number][] = [\n [\"Основной расчетный счет\", \"Безналичный\", \"АО «Т-Банк»\", \"40802810300001442713\", \"Основной счет\", 412987.78],\n [\"Касса (наличка)\", \"Наличный\", \"\", \"\", \"Основной счет\", 2288160],\n [\"АЛЬФА-БАНК 2996\", \"Безналичный\", \"АО АЛЬФА-БАНК\", \"40802810502120002996\", \"\", 3145000],\n [\"Т-Банк 5241\", \"Безналичный\", \"АО «Т-Банк»\", \"40802810300009275241\", \"\", 169822.38],\n [\"Налог\", \"Безналичный\", \"АО «Т-Банк»\", \"40802810600003834236\", \"Налоговый\", 241751.34],\n [\"Благотворительность\", \"Безналичный\", \"АО «Т-Банк»\", \"40802810400003834245\", \"Благотворительный счет\", 3596.44],\n [\"ЗП АУП\", \"Безналичный\", \"АО «Т-Банк»\", \"40802810700003834233\", \"Зарплатный\", 0],\n [\"Предоплата от застройщика\", \"Безналичный\", \"АО «Т-Банк»\", \"40802810200003834238\", \"Предоплаты\", 0],\n [\"ХОЗ НУЖДЫ\", \"Безналичный\", \"АО ТБанк\", \"40802810600009124445\", \"\", 0],\n [\"Депозит\", \"Безналичный\", \"АО ТБанк\", \"42110810000000213137\", \"Депозиты\", 0],\n ];\n for (const [name, tp, bank, numb, grp, bal] of accounts) {\n await sqlite.execute({\n sql: `INSERT INTO pf_accounts (name,acc_type,bank,number,grp,start_balance,start_date) VALUES (?,?,?,?,?,?,?)`,\n args: [name, tp, bank, numb, grp, bal, t],\n });\n }\n // Учетные статьи: kind, name, children\n const cat = async (name: string, kind: string, parent: number | null): Promise => {\n const r = await sqlite.execute({\n sql: `INSERT INTO pf_categories (name,kind,parent_id) VALUES (?,?,?)`,\n args: [name, kind, parent],\n });\n return Number(r.lastInsertRowid);\n };\n // Доходы\n await cat(\"Контрагентские\", \"income\", null);\n await cat(\"Страхование\", \"income\", null);\n await cat(\"Оказание услуг\", \"income\", null);\n await cat(\"Регистрация сделки\", \"income\", null);\n const kb = await cat(\"Комиссия от банка\", \"income\", null);\n await cat(\"Абсолют Банк\", \"income\", kb);\n await cat(\"Альфа Банк\", \"income\", kb);\n const pd = await cat(\"Прочие доходы\", \"income\", null);\n await cat(\"Проценты по вкладам\", \"income\", pd);\n await cat(\"Курсовая разница (+)\", \"income\", pd);\n // Расходы\n const perem = await cat(\"Переменные\", \"expense\", null);\n await cat(\"UNLOCK.CRM\", \"expense\", perem);\n await cat(\"Благотворительность\", \"expense\", perem);\n const proizv = await cat(\"Производители\", \"expense\", perem);\n await cat(\"Брокеры\", \"expense\", proizv);\n await cat(\"Контрагенты\", \"expense\", proizv);\n await cat(\"% руководителей\", \"expense\", proizv);\n await cat(\"Ипотека % от кв банка\", \"expense\", proizv);\n await cat(\"Реферальная программа\", \"expense\", proizv);\n const mark = await cat(\"Маркетинг\", \"expense\", perem);\n await cat(\"Рекламная компания\", \"expense\", mark);\n await cat(\"Авито\", \"expense\", mark);\n await cat(\"Циан\", \"expense\", mark);\n await cat(\"Баннер (Растяжка)\", \"expense\", mark);\n await cat(\"Фин.гарантия\", \"expense\", perem);\n await cat(\"Возврат покупателям\", \"expense\", perem);\n const post = await cat(\"Постоянные\", \"expense\", null);\n await cat(\"Супервайзер (коммерция)\", \"expense\", post);\n const fot = await cat(\"ФОТ\", \"expense\", post);\n const dir = await cat(\"Дирекция\", \"expense\", fot);\n await cat(\"CEO\", \"expense\", dir);\n await cat(\"COO\", \"expense\", dir);\n await cat(\"CMO\", \"expense\", dir);\n await cat(\"РОП\", \"expense\", dir);\n const adm = await cat(\"Административный\", \"expense\", fot);\n await cat(\"РОСФИН Юрист\", \"expense\", adm);\n await cat(\"Офис менеджер\", \"expense\", adm);\n await cat(\"Ипотечный специалист\", \"expense\", adm);\n await cat(\"Менеджер отдела Заботы\", \"expense\", adm);\n await cat(\"Куратор по новостройкам\", \"expense\", adm);\n const mrk2 = await cat(\"Маркетинговый\", \"expense\", fot);\n await cat(\"Оператор/монтажер\", \"expense\", mrk2);\n await cat(\"СММ\", \"expense\", mrk2);\n const bonus = await cat(\"Бонусная система\", \"expense\", fot);\n await cat(\"Ежемесячный бонус\", \"expense\", bonus);\n await cat(\"Ежеквартальный бонус\", \"expense\", bonus);\n await cat(\"Годовой бонус\", \"expense\", bonus);\n const ofs = await cat(\"Офис\", \"expense\", post);\n await cat(\"Аренда офиса\", \"expense\", ofs);\n await cat(\"Интерьер\", \"expense\", ofs);\n const po = await cat(\"Программное обеспечение\", \"expense\", post);\n for (const n of [\"U.team\", \"F-doc\", \"CRM\", \"Финучет\", \"Контур\", \"Услуги связи\", \"Яндекс.почта\"]) {\n await cat(n, \"expense\", po);\n }\n const sait = await cat(\"Сайт\", \"expense\", post);\n await cat(\"ФИД новостроек\", \"expense\", sait);\n await cat(\"Налоги\", \"expense\", null);\n await cat(\"Кредиты и овердрафт\", \"expense\", null);\n await cat(\"Нераспределенный расход\", \"expense\", null);\n await cat(\"Нераспределенный доход\", \"income\", null);\n // Контрагенты\n const contractors: [string, string][] = [\n [\"ООО СЗ БЦ АПСАЙД ОСТАНКИНО\", \"Застройщик\"],\n [\"ООО СЗ ПАРК РАЗВЛЕЧЕНИЙ\", \"Застройщик\"],\n [\"ГК Самолёт\", \"Застройщик\"],\n [\"ИП Мателевич Сергей Валерьевич\", \"Брокер (ИП)\"],\n [\"ИП Дедерер Евгения Владимировна\", \"Брокер (ИП)\"],\n [\"АО ТБанк\", \"Банк\"],\n [\"АО АЛЬФА-БАНК\", \"Банк\"],\n ];\n for (const [name, grp] of contractors) {\n await sqlite.execute({ sql: `INSERT INTO pf_contractors (name,grp) VALUES (?,?)`, args: [name, grp] });\n }\n}\nawait seedRefs();\n\n// статья «Дивиденды» нужна для показателей — создаём, если нет\nawait sqlite.execute(\n `INSERT INTO pf_categories (name, kind) SELECT 'Дивиденды', 'expense'\n WHERE NOT EXISTS (SELECT 1 FROM pf_categories WHERE name='Дивиденды')`,\n);\n\nasync function getPin(): Promise {\n const r = await sqlite.execute(`SELECT value FROM pf_settings WHERE key='pin'`);\n return String(r.rows[0]?.value ?? \"1111\");\n}\n\n// ---------------- Авторизация ----------------\napp.use(\"/api/*\", async (c, next) => {\n if (c.req.path === \"/api/login\") return next();\n if ((c.req.header(\"x-pin\") || \"\") !== (await getPin())) {\n return c.json({ error: \"unauthorized\" }, 401);\n }\n await next();\n});\n\napp.post(\"/api/login\", async (c) => {\n const { pin } = await c.req.json();\n if (String(pin) === (await getPin())) return c.json({ ok: true });\n return c.json({ ok: false }, 401);\n});\n\napp.post(\"/api/settings/company\", async (c) => {\n const { name } = await c.req.json();\n await sqlite.execute({\n sql: `INSERT INTO pf_settings(key,value) VALUES ('company_name', ?) ON CONFLICT(key) DO UPDATE SET value=excluded.value`,\n args: [String(name || \"Компания\")],\n });\n return c.json({ ok: true });\n});\n\napp.post(\"/api/settings/pin\", async (c) => {\n const { oldPin, newPin } = await c.req.json();\n if (String(oldPin) !== (await getPin())) return c.json({ error: \"Неверный текущий PIN\" }, 400);\n if (!/^\\d{4,8}$/.test(String(newPin))) return c.json({ error: \"PIN: 4–8 цифр\" }, 400);\n await sqlite.execute({ sql: `UPDATE pf_settings SET value=? WHERE key='pin'`, args: [String(newPin)] });\n return c.json({ ok: true });\n});\n\n// ---------------- Остатки ----------------\n// эффект подтверждённых операций на каждый счёт\nconst BALANCE_SQL = `\n SELECT a.id,\n a.start_balance\n + COALESCE((SELECT SUM(CASE\n WHEN o.op_type='income' AND o.account_id=a.id THEN o.amount\n WHEN o.op_type='expense' AND o.account_id=a.id THEN -o.amount\n WHEN o.op_type='transfer' AND o.account_id=a.id THEN -o.amount\n WHEN o.op_type='transfer' AND o.account2_id=a.id THEN o.amount\n ELSE 0 END)\n FROM pf_ops o\n WHERE o.confirmed=1 AND (o.account_id=a.id OR o.account2_id=a.id)), 0) AS balance\n FROM pf_accounts a`;\n\nasync function accountBalances() {\n const r = await sqlite.execute(BALANCE_SQL);\n const map: Record = {};\n for (const row of r.rows as any[]) map[row.id] = num(row.balance);\n return map;\n}\n\n// ---------------- Справочники ----------------\napp.get(\"/api/meta\", async (c) => {\n const [acc, cats, contr, proj, les, prods] = await Promise.all([\n sqlite.execute(`SELECT * FROM pf_accounts ORDER BY active DESC, name`),\n sqlite.execute(`SELECT * FROM pf_categories ORDER BY kind, COALESCE(parent_id,0), sort, id`),\n sqlite.execute(`SELECT * FROM pf_contractors ORDER BY name`),\n sqlite.execute(`SELECT * FROM pf_projects ORDER BY active DESC, name`),\n sqlite.execute(`SELECT * FROM pf_legal_entities ORDER BY id`),\n sqlite.execute(`SELECT * FROM pf_products ORDER BY ptype, name`),\n ]);\n const bal = await accountBalances();\n const accounts = (acc.rows as any[]).map((a) => ({ ...a, balance: bal[a.id] ?? num(a.start_balance) }));\n const total = accounts.filter((a) => a.active).reduce((s, a) => s + a.balance, 0);\n // спарклайн остатка: дневная динамика за последние 90 дней (только подтверждённые)\n const t = today();\n const from90 = new Date(Date.now() - 90 * 864e5).toISOString().slice(0, 10);\n const daily = await sqlite.execute({\n sql: `SELECT date, SUM(CASE WHEN op_type='income' THEN amount WHEN op_type='expense' THEN -amount ELSE 0 END) net\n FROM pf_ops WHERE confirmed=1 AND date>=? AND date<=? GROUP BY date ORDER BY date`,\n args: [from90, t],\n });\n const spark: { d: string; b: number }[] = [];\n {\n let b = total;\n const rows = (daily.rows as any[]).slice().reverse(); // от сегодня назад\n spark.push({ d: t, b });\n for (const r of rows) { b -= num(r.net); spark.push({ d: r.date, b }); }\n spark.reverse();\n }\n // кассовый разрыв: прогноз по будущим операциям (включая неподтверждённые)\n const future = await sqlite.execute({\n sql: `SELECT date, SUM(CASE WHEN op_type='income' THEN amount WHEN op_type='expense' THEN -amount ELSE 0 END) net\n FROM pf_ops WHERE date>? AND op_type!='transfer' AND (confirmed=0 OR date>?)\n GROUP BY date ORDER BY date`,\n args: [t, t],\n });\n let gap: { from: string; to: string; min: number } | null = null;\n {\n let b = total;\n let inGap = false;\n for (const r of future.rows as any[]) {\n b += num(r.net);\n if (b < 0 && !gap) { gap = { from: r.date, to: r.date, min: b }; inGap = true; }\n else if (b < 0 && gap && inGap) { gap.to = r.date; gap.min = Math.min(gap.min, b); }\n else if (b >= 0 && inGap) inGap = false;\n }\n }\n const cname = (await sqlite.execute(`SELECT value FROM pf_settings WHERE key='company_name'`)).rows[0] as any;\n return c.json({\n accounts, categories: cats.rows, contractors: contr.rows, projects: proj.rows,\n legal_entities: les.rows, products: prods.rows,\n total_balance: total, spark, gap,\n company_name: String(cname?.value || \"Компания\"),\n });\n});\n\n// ---------------- Комментарии к сделке ----------------\napp.post(\"/api/deals/:id/comments\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `INSERT INTO pf_deal_comments (deal_id, created_at, text) VALUES (?,?,?)`,\n args: [Number(c.req.param(\"id\")), new Date().toISOString(), String(b.text || \"\").slice(0, 2000)],\n });\n return c.json({ ok: true });\n});\napp.delete(\"/api/comments/:id\", async (c) => {\n await sqlite.execute({ sql: `DELETE FROM pf_deal_comments WHERE id=?`, args: [Number(c.req.param(\"id\"))] });\n return c.json({ ok: true });\n});\n\n// ---------------- Бюджеты (БДР / БДДС) ----------------\napp.get(\"/api/budgets\", async (c) => {\n const bt = c.req.query(\"type\") || \"bdr\";\n const r = await sqlite.execute({\n sql: `SELECT b.*, p.name project_name FROM pf_budgets b\n LEFT JOIN pf_projects p ON p.id=b.project_id\n WHERE b.btype=? ORDER BY b.id DESC`,\n args: [bt],\n });\n return c.json(r.rows);\n});\napp.post(\"/api/budgets\", async (c) => {\n const b = await c.req.json();\n const r = await sqlite.execute({\n sql: `INSERT INTO pf_budgets (name, btype, project_id, year, updated_at) VALUES (?,?,?,?,?)`,\n args: [b.name || \"Бюджет\", b.btype === \"bdds\" ? \"bdds\" : \"bdr\", b.project_id || null, b.year || today().slice(0, 4), today()],\n });\n return c.json({ ok: true, id: Number(r.lastInsertRowid) });\n});\napp.delete(\"/api/budgets/:id\", async (c) => {\n const id = Number(c.req.param(\"id\"));\n await sqlite.batch([\n { sql: `DELETE FROM pf_budget_values WHERE budget_id=?`, args: [id] },\n { sql: `DELETE FROM pf_budgets WHERE id=?`, args: [id] },\n ]);\n return c.json({ ok: true });\n});\napp.get(\"/api/budgets/:id\", async (c) => {\n const id = Number(c.req.param(\"id\"));\n const b = (await sqlite.execute({\n sql: `SELECT b.*, p.name project_name FROM pf_budgets b LEFT JOIN pf_projects p ON p.id=b.project_id WHERE b.id=?`,\n args: [id],\n })).rows[0] as any;\n if (!b) return c.json({ error: \"not found\" }, 404);\n const values = await sqlite.execute({ sql: `SELECT row_key, period, amount FROM pf_budget_values WHERE budget_id=?`, args: [id] });\n // факт: по подтверждённым операциям за год бюджета (при проекте — только его операции)\n const args: unknown[] = [String(b.year)];\n let pw = \"\";\n if (b.project_id) { pw = \" AND o.project_id=?\"; args.push(b.project_id); }\n const facts = await sqlite.execute({\n sql: `SELECT substr(o.date,1,7) m, o.op_type, o.category_id, SUM(o.amount) s\n FROM pf_ops o WHERE o.confirmed=1 AND o.op_type!='transfer' AND substr(o.date,1,4)=?${pw}\n GROUP BY substr(o.date,1,7), o.op_type, o.category_id`,\n args,\n });\n return c.json({ budget: b, values: values.rows, facts: facts.rows });\n});\napp.put(\"/api/budgets/:id/value\", async (c) => {\n const id = Number(c.req.param(\"id\"));\n const b = await c.req.json();\n await sqlite.batch([\n { sql: `INSERT INTO pf_budget_values (budget_id,row_key,period,amount) VALUES (?,?,?,?)\n ON CONFLICT(budget_id,row_key,period) DO UPDATE SET amount=excluded.amount`,\n args: [id, String(b.row_key), String(b.period), num(b.amount)] },\n { sql: `UPDATE pf_budgets SET updated_at=? WHERE id=?`, args: [today(), id] },\n ]);\n return c.json({ ok: true });\n});\n\n// ---------------- Данные дашборда ----------------\napp.get(\"/api/dashboard2\", async (c) => {\n const from = c.req.query(\"from\") || `${today().slice(0, 4)}-01-01`;\n const to = c.req.query(\"to\") || today();\n const basis = c.req.query(\"basis\") === \"accrual\" ? \"accrual\" : \"cash\";\n // необязательные фильтры (как в референсе: счёт / проект / сделка)\n let fw = \"\";\n const fa: unknown[] = [];\n if (c.req.query(\"account\")) { fw += \" AND account_id=?\"; fa.push(Number(c.req.query(\"account\"))); }\n if (c.req.query(\"project\")) { fw += \" AND project_id=?\"; fa.push(Number(c.req.query(\"project\"))); }\n if (c.req.query(\"deal\")) { fw += \" AND deal_id=?\"; fa.push(Number(c.req.query(\"deal\"))); }\n // доходы/расходы по месяцам (только статьи классов Доходы/Расходы; Активы/Обязательства/Капитал — не П/У)\n const fwO = fw.replaceAll(\"account_id\", \"o.account_id\").replaceAll(\"project_id\", \"o.project_id\").replaceAll(\"deal_id\", \"o.deal_id\");\n const PU = `COALESCE(c.cat_class,'') NOT IN ('assets','liabilities','capital')`;\n const months = basis === \"cash\"\n ? await sqlite.execute({\n sql: `SELECT substr(o.date,1,7) m,\n SUM(CASE WHEN o.op_type='income' AND o.confirmed=1 AND ${PU} THEN o.amount ELSE 0 END) income,\n SUM(CASE WHEN o.op_type='expense' AND o.confirmed=1 AND ${PU} THEN o.amount ELSE 0 END) expense,\n SUM(CASE WHEN o.op_type='income' AND ${PU} THEN o.amount ELSE 0 END) plan_income,\n SUM(CASE WHEN o.op_type='expense' AND ${PU} THEN o.amount ELSE 0 END) plan_expense\n FROM pf_ops o LEFT JOIN pf_categories c ON c.id=o.category_id\n WHERE o.op_type!='transfer' AND o.date>=? AND o.date<=?${fwO}\n GROUP BY substr(o.date,1,7) ORDER BY m`,\n args: [from, to, ...fa],\n })\n : await sqlite.execute({\n sql: `SELECT m, SUM(income) income, SUM(expense) expense, SUM(plan_income) plan_income, SUM(plan_expense) plan_expense FROM (\n SELECT substr(date,1,7) m, SUM(amount) income, 0 expense, SUM(amount) plan_income, 0 plan_expense FROM pf_shipments WHERE date>=? AND date<=? GROUP BY substr(date,1,7)\n UNION ALL\n SELECT substr(o.date,1,7) m, 0 income,\n SUM(CASE WHEN o.confirmed=1 AND ${PU} THEN o.amount ELSE 0 END) expense,\n 0 plan_income,\n SUM(CASE WHEN ${PU} THEN o.amount ELSE 0 END) plan_expense\n FROM pf_ops o LEFT JOIN pf_categories c ON c.id=o.category_id\n WHERE o.op_type='expense' AND o.date>=? AND o.date<=?\n GROUP BY substr(o.date,1,7)\n ) GROUP BY m ORDER BY m`,\n args: [from, to, from, to],\n });\n // план из бюджетов БДР\n const plans = await sqlite.execute(\n `SELECT v.row_key, v.period, SUM(v.amount) s FROM pf_budget_values v\n JOIN pf_budgets b ON b.id=v.budget_id AND b.btype='bdr'\n GROUP BY v.row_key, v.period`,\n );\n // дневные остатки за период\n const startTotal = (await sqlite.execute(`SELECT COALESCE(SUM(start_balance),0) s FROM pf_accounts WHERE active=1`)).rows[0] as any;\n const netAll = await sqlite.execute(\n `SELECT date, SUM(CASE WHEN op_type='income' THEN amount WHEN op_type='expense' THEN -amount ELSE 0 END) net\n FROM pf_ops WHERE confirmed=1 GROUP BY date ORDER BY date`,\n );\n const daily: { d: string; b: number }[] = [];\n {\n let bal = num(startTotal.s);\n for (const r of netAll.rows as any[]) {\n bal += num(r.net);\n if (r.date >= from && r.date <= to) daily.push({ d: r.date, b: bal });\n }\n if (!daily.length) daily.push({ d: to, b: bal });\n }\n // структура платежей по корневым статьям\n const struct = await sqlite.execute({\n sql: `SELECT substr(o.date,1,7) m, o.op_type, o.category_id, SUM(o.amount) s\n FROM pf_ops o WHERE o.confirmed=1 AND o.op_type!='transfer' AND o.date>=? AND o.date<=?${fw.replaceAll(\"account_id\", \"o.account_id\").replaceAll(\"project_id\", \"o.project_id\").replaceAll(\"deal_id\", \"o.deal_id\")}\n GROUP BY substr(o.date,1,7), o.op_type, o.category_id`,\n args: [from, to, ...fa],\n });\n // ABC по контрагентам (доходы)\n const abc = await sqlite.execute({\n sql: `SELECT COALESCE(ct.name,'Без контрагента') name, SUM(o.amount) s\n FROM pf_ops o LEFT JOIN pf_contractors ct ON ct.id=o.contractor_id\n WHERE o.confirmed=1 AND o.op_type='income' AND o.date>=? AND o.date<=?\n GROUP BY o.contractor_id ORDER BY s DESC LIMIT 25`,\n args: [from, to],\n });\n // прибыльность проектов\n const projProfit = await sqlite.execute({\n sql: `SELECT p.name,\n COALESCE(SUM(CASE WHEN o.op_type='income' AND o.confirmed=1 THEN o.amount\n WHEN o.op_type='expense' AND o.confirmed=1 THEN -o.amount ELSE 0 END),0) profit\n FROM pf_projects p\n LEFT JOIN pf_ops o ON o.project_id=p.id AND o.date>=? AND o.date<=?\n WHERE p.active=1 GROUP BY p.id HAVING profit != 0 ORDER BY profit DESC LIMIT 20`,\n args: [from, to],\n });\n return c.json({\n from, to, basis,\n months: months.rows,\n plans: plans.rows,\n daily,\n struct: struct.rows,\n abc: abc.rows,\n proj_profit: projProfit.rows,\n });\n});\n\n// ---------------- Статистика контрагентов ----------------\napp.get(\"/api/contractors/stats\", async (c) => {\n const r = await sqlite.execute(\n `SELECT ct.id, ct.name, ct.grp, ct.inn,\n COUNT(o.id) n,\n COALESCE(SUM(CASE WHEN o.op_type='income' THEN o.amount ELSE 0 END),0) income,\n COALESCE(SUM(CASE WHEN o.op_type='expense' THEN o.amount ELSE 0 END),0) expense\n FROM pf_contractors ct\n LEFT JOIN pf_ops o ON o.contractor_id=ct.id AND o.confirmed=1\n GROUP BY ct.id ORDER BY ct.name`,\n );\n return c.json(r.rows);\n});\n\n// ---------------- Проекты ----------------\napp.get(\"/api/projects\", async (c) => {\n const r = await sqlite.execute(\n `SELECT p.*,\n COALESCE((SELECT SUM(amount) FROM pf_ops WHERE project_id=p.id AND op_type='income' AND confirmed=1),0) income,\n COALESCE((SELECT SUM(amount) FROM pf_ops WHERE project_id=p.id AND op_type='expense' AND confirmed=1),0) expense,\n COALESCE((SELECT SUM(amount) FROM pf_ops WHERE project_id=p.id AND op_type='income' AND confirmed=0),0) income_plan,\n COALESCE((SELECT SUM(amount) FROM pf_ops WHERE project_id=p.id AND op_type='expense' AND confirmed=0),0) expense_plan\n FROM pf_projects p ORDER BY p.active DESC, p.name`,\n );\n return c.json(r.rows);\n});\napp.post(\"/api/projects\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `INSERT INTO pf_projects (name,comment,grp,status,date_start,date_end) VALUES (?,?,?,?,?,?)`,\n args: [b.name || \"Проект\", b.comment || \"\", b.grp || \"\", b.status || \"work\", b.date_start || null, b.date_end || null],\n });\n return c.json({ ok: true });\n});\napp.put(\"/api/projects/:id\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `UPDATE pf_projects SET name=?, comment=?, grp=?, status=?, date_start=?, date_end=?, active=? WHERE id=?`,\n args: [b.name || \"Проект\", b.comment || \"\", b.grp || \"\", b.status || \"work\", b.date_start || null, b.date_end || null, b.active === false ? 0 : 1, Number(c.req.param(\"id\"))],\n });\n return c.json({ ok: true });\n});\napp.delete(\"/api/projects/:id\", async (c) => {\n const id = Number(c.req.param(\"id\"));\n await sqlite.batch([\n { sql: `UPDATE pf_ops SET project_id=NULL WHERE project_id=?`, args: [id] },\n { sql: `DELETE FROM pf_projects WHERE id=?`, args: [id] },\n ]);\n return c.json({ ok: true });\n});\n\n// ---------------- Бюджет (БДР) ----------------\napp.get(\"/api/budget\", async (c) => {\n const year = c.req.query(\"year\") || today().slice(0, 4);\n const plans = await sqlite.execute({\n sql: `SELECT period, category_id, amount FROM pf_budget WHERE substr(period,1,4)=?`, args: [year],\n });\n const facts = await sqlite.execute({\n sql: `SELECT substr(date,1,7) m, category_id, op_type, SUM(amount) s\n FROM pf_ops WHERE confirmed=1 AND op_type!='transfer' AND substr(date,1,4)=?\n GROUP BY substr(date,1,7), category_id, op_type`,\n args: [year],\n });\n return c.json({ year, plans: plans.rows, facts: facts.rows });\n});\napp.put(\"/api/budget\", async (c) => {\n const b = await c.req.json(); // {period, category_id, amount}\n await sqlite.execute({\n sql: `INSERT INTO pf_budget (period, category_id, amount) VALUES (?,?,?)\n ON CONFLICT(period, category_id) DO UPDATE SET amount=excluded.amount`,\n args: [b.period, Number(b.category_id), num(b.amount)],\n });\n return c.json({ ok: true });\n});\n\napp.post(\"/api/accounts\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `INSERT INTO pf_accounts (name,acc_type,bank,number,grp,start_balance,start_date) VALUES (?,?,?,?,?,?,?)`,\n args: [b.name || \"Счет\", b.acc_type || \"Безналичный\", b.bank || \"\", b.number || \"\", b.grp || \"\", num(b.start_balance), b.start_date || today()],\n });\n return c.json({ ok: true });\n});\napp.put(\"/api/accounts/:id\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `UPDATE pf_accounts SET name=?, acc_type=?, bank=?, number=?, grp=?, start_balance=?, start_date=?, active=? WHERE id=?`,\n args: [b.name || \"Счет\", b.acc_type || \"Безналичный\", b.bank || \"\", b.number || \"\", b.grp || \"\", num(b.start_balance), b.start_date || today(), b.active === false ? 0 : 1, Number(c.req.param(\"id\"))],\n });\n return c.json({ ok: true });\n});\napp.delete(\"/api/accounts/:id\", async (c) => {\n const id = Number(c.req.param(\"id\"));\n const used = (await sqlite.execute({ sql: `SELECT COUNT(*) n FROM pf_ops WHERE account_id=? OR account2_id=?`, args: [id, id] })).rows[0] as any;\n if (num(used.n) > 0) return c.json({ error: \"По счёту есть операции — сначала удалите их или переведите\" }, 400);\n await sqlite.execute({ sql: `DELETE FROM pf_accounts WHERE id=?`, args: [id] });\n return c.json({ ok: true });\n});\n\napp.post(\"/api/categories\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `INSERT INTO pf_categories (name,kind,parent_id) VALUES (?,?,?)`,\n args: [b.name || \"Статья\", b.kind === \"expense\" ? \"expense\" : \"income\", b.parent_id || null],\n });\n return c.json({ ok: true });\n});\napp.put(\"/api/categories/:id\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `UPDATE pf_categories SET name=?, parent_id=? WHERE id=?`,\n args: [b.name || \"Статья\", b.parent_id || null, Number(c.req.param(\"id\"))],\n });\n return c.json({ ok: true });\n});\napp.delete(\"/api/categories/:id\", async (c) => {\n const id = Number(c.req.param(\"id\"));\n const kids = (await sqlite.execute({ sql: `SELECT COUNT(*) n FROM pf_categories WHERE parent_id=?`, args: [id] })).rows[0] as any;\n if (num(kids.n) > 0) return c.json({ error: \"У статьи есть вложенные — сначала удалите их\" }, 400);\n await sqlite.batch([\n { sql: `UPDATE pf_ops SET category_id=NULL WHERE category_id=?`, args: [id] },\n { sql: `DELETE FROM pf_categories WHERE id=?`, args: [id] },\n ]);\n return c.json({ ok: true });\n});\n\napp.post(\"/api/contractors\", async (c) => {\n const b = await c.req.json();\n const r = await sqlite.execute({\n sql: `INSERT INTO pf_contractors (name,grp,inn,comment) VALUES (?,?,?,?)`,\n args: [b.name || \"Контрагент\", b.grp || \"\", b.inn || \"\", b.comment || \"\"],\n });\n return c.json({ ok: true, id: Number(r.lastInsertRowid) });\n});\napp.put(\"/api/contractors/:id\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `UPDATE pf_contractors SET name=?, grp=?, inn=?, comment=? WHERE id=?`,\n args: [b.name || \"Контрагент\", b.grp || \"\", b.inn || \"\", b.comment || \"\", Number(c.req.param(\"id\"))],\n });\n return c.json({ ok: true });\n});\napp.delete(\"/api/contractors/:id\", async (c) => {\n const id = Number(c.req.param(\"id\"));\n await sqlite.batch([\n { sql: `UPDATE pf_ops SET contractor_id=NULL WHERE contractor_id=?`, args: [id] },\n { sql: `DELETE FROM pf_contractors WHERE id=?`, args: [id] },\n ]);\n return c.json({ ok: true });\n});\n\n// ---------------- Операции ----------------\napp.get(\"/api/ops\", async (c) => {\n const q = c.req.query();\n const where: string[] = [];\n const args: unknown[] = [];\n if (q.from) { where.push(\"o.date >= ?\"); args.push(q.from); }\n if (q.to) { where.push(\"o.date <= ?\"); args.push(q.to); }\n if (q.type) { where.push(\"o.op_type = ?\"); args.push(q.type); }\n if (q.confirmed === \"1\" || q.confirmed === \"0\") { where.push(\"o.confirmed = ?\"); args.push(Number(q.confirmed)); }\n if (q.account) { where.push(\"(o.account_id = ? OR o.account2_id = ?)\"); args.push(Number(q.account), Number(q.account)); }\n if (q.category) { where.push(\"o.category_id = ?\"); args.push(Number(q.category)); }\n if (q.contractor) { where.push(\"o.contractor_id = ?\"); args.push(Number(q.contractor)); }\n if (q.deal) { where.push(\"o.deal_id = ?\"); args.push(Number(q.deal)); }\n if (q.project) { where.push(\"o.project_id = ?\"); args.push(Number(q.project)); }\n if (q.uncat === \"1\") { where.push(\"o.category_id IS NULL AND o.op_type != 'transfer'\"); }\n if (q.q) { where.push(\"(o.purpose LIKE ? OR ct.name LIKE ?)\"); args.push(`%${q.q}%`, `%${q.q}%`); }\n const w = where.length ? \"WHERE \" + where.join(\" AND \") : \"\";\n const r = await sqlite.execute({\n sql: `SELECT o.*, a.name account_name, a2.name account2_name, cat.name category_name,\n ct.name contractor_name, d.name deal_name, p.name project_name\n FROM pf_ops o\n LEFT JOIN pf_accounts a ON a.id=o.account_id\n LEFT JOIN pf_accounts a2 ON a2.id=o.account2_id\n LEFT JOIN pf_categories cat ON cat.id=o.category_id\n LEFT JOIN pf_contractors ct ON ct.id=o.contractor_id\n LEFT JOIN pf_deals d ON d.id=o.deal_id\n LEFT JOIN pf_projects p ON p.id=o.project_id\n ${w}\n ORDER BY o.date DESC, o.id DESC\n LIMIT 500`,\n args,\n });\n const rows = r.rows as any[];\n const totals = {\n n: rows.length,\n income: rows.filter((x) => x.op_type === \"income\").reduce((s, x) => s + num(x.amount), 0),\n expense: rows.filter((x) => x.op_type === \"expense\").reduce((s, x) => s + num(x.amount), 0),\n transfer: rows.filter((x) => x.op_type === \"transfer\").reduce((s, x) => s + num(x.amount), 0),\n };\n return c.json({ rows, totals });\n});\n\nfunction opArgs(b: any) {\n return [\n b.date || today(),\n [\"income\", \"expense\", \"transfer\"].includes(b.op_type) ? b.op_type : \"income\",\n b.confirmed ? 1 : 0,\n num(b.amount),\n b.account_id || null,\n b.op_type === \"transfer\" ? (b.account2_id || null) : null,\n b.op_type === \"transfer\" ? null : (b.category_id || null),\n b.op_type === \"transfer\" ? null : (b.contractor_id || null),\n b.deal_id || null,\n b.purpose || \"\",\n b.project_id || null,\n ];\n}\napp.post(\"/api/ops\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `INSERT INTO pf_ops (date,op_type,confirmed,amount,account_id,account2_id,category_id,contractor_id,deal_id,purpose,project_id)\n VALUES (?,?,?,?,?,?,?,?,?,?,?)`,\n args: opArgs(b),\n });\n return c.json({ ok: true });\n});\napp.put(\"/api/ops/:id\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `UPDATE pf_ops SET date=?,op_type=?,confirmed=?,amount=?,account_id=?,account2_id=?,category_id=?,contractor_id=?,deal_id=?,purpose=?,project_id=? WHERE id=?`,\n args: [...opArgs(b), Number(c.req.param(\"id\"))],\n });\n return c.json({ ok: true });\n});\n// массовое распределение (статья/проект) по выбранным операциям\napp.post(\"/api/ops/bulk\", async (c) => {\n const b = await c.req.json(); // {ids:[], category_id?, project_id?}\n const ids = (b.ids || []).map(Number).filter(Boolean);\n if (!ids.length) return c.json({ ok: true, n: 0 });\n const sets: string[] = [];\n const args: unknown[] = [];\n if (\"category_id\" in b) { sets.push(\"category_id=?\"); args.push(b.category_id || null); }\n if (\"project_id\" in b) { sets.push(\"project_id=?\"); args.push(b.project_id || null); }\n if (!sets.length) return c.json({ ok: true, n: 0 });\n await sqlite.execute({\n sql: `UPDATE pf_ops SET ${sets.join(\",\")} WHERE id IN (${ids.map(() => \"?\").join(\",\")})`,\n args: [...args, ...ids],\n });\n return c.json({ ok: true, n: ids.length });\n});\n// импорт выписки: массив строк, контрагенты создаются автоматически\napp.post(\"/api/ops/import\", async (c) => {\n const b = await c.req.json(); // {rows:[{date,op_type,amount,account_id,contractor_name,purpose,confirmed}]}\n const rows = b.rows || [];\n let n = 0;\n const ctrCache: Record = {};\n for (const r of rows) {\n if (!r.date || !r.amount || !r.account_id) continue;\n let ctrId: number | null = null;\n const cname = (r.contractor_name || \"\").trim();\n if (cname) {\n if (ctrCache[cname]) ctrId = ctrCache[cname];\n else {\n const ex = await sqlite.execute({ sql: `SELECT id FROM pf_contractors WHERE name=?`, args: [cname] });\n if (ex.rows.length) ctrId = Number((ex.rows[0] as any).id);\n else {\n const ins = await sqlite.execute({ sql: `INSERT INTO pf_contractors (name,grp) VALUES (?, '')`, args: [cname] });\n ctrId = Number(ins.lastInsertRowid);\n }\n ctrCache[cname] = ctrId;\n }\n }\n await sqlite.execute({\n sql: `INSERT INTO pf_ops (date,op_type,confirmed,amount,account_id,category_id,contractor_id,purpose)\n VALUES (?,?,?,?,?,?,?,?)`,\n args: [\n String(r.date).slice(0, 10), r.op_type === \"expense\" ? \"expense\" : \"income\",\n r.confirmed === false ? 0 : 1, Math.abs(num(r.amount)),\n Number(r.account_id), r.category_id || null, ctrId, r.purpose || \"\",\n ],\n });\n n++;\n }\n return c.json({ ok: true, n });\n});\napp.post(\"/api/ops/:id/confirm\", async (c) => {\n const b = await c.req.json().catch(() => ({}));\n await sqlite.execute({\n sql: `UPDATE pf_ops SET confirmed=1, date=? WHERE id=?`,\n args: [b.date || today(), Number(c.req.param(\"id\"))],\n });\n return c.json({ ok: true });\n});\napp.delete(\"/api/ops/:id\", async (c) => {\n await sqlite.execute({ sql: `DELETE FROM pf_ops WHERE id=?`, args: [Number(c.req.param(\"id\"))] });\n return c.json({ ok: true });\n});\n\n// ---------------- Сделки ----------------\nconst DEAL_AGG = `\n SELECT d.*,\n COALESCE((SELECT SUM(amount) FROM pf_ops WHERE deal_id=d.id AND op_type='income' AND confirmed=1),0) AS received,\n COALESCE((SELECT SUM(amount) FROM pf_ops WHERE deal_id=d.id AND op_type='income'),0) AS income_all,\n COALESCE((SELECT SUM(amount) FROM pf_ops WHERE deal_id=d.id AND op_type='expense'),0) AS expense_all,\n COALESCE((SELECT SUM(amount) FROM pf_ops WHERE deal_id=d.id AND op_type='expense' AND confirmed=1),0) AS expense_paid,\n COALESCE((SELECT SUM(amount) FROM pf_shipments WHERE deal_id=d.id),0) AS shipped,\n COALESCE((SELECT SUM(qty*price) FROM pf_deal_items WHERE deal_id=d.id),0) AS items_sum\n FROM pf_deals d`;\n\napp.get(\"/api/deals\", async (c) => {\n const dt = c.req.query(\"type\");\n const r = dt\n ? await sqlite.execute({ sql: `${DEAL_AGG} WHERE COALESCE(d.deal_type,'sale')=? ORDER BY d.created_at DESC, d.id DESC`, args: [dt] })\n : await sqlite.execute(`${DEAL_AGG} ORDER BY d.created_at DESC, d.id DESC`);\n return c.json(r.rows);\n});\napp.get(\"/api/deals/:id\", async (c) => {\n const id = Number(c.req.param(\"id\"));\n const d = (await sqlite.execute({ sql: `${DEAL_AGG} WHERE d.id=?`, args: [id] })).rows[0];\n if (!d) return c.json({ error: \"not found\" }, 404);\n const [ops, shipments, items, invoices] = await Promise.all([\n sqlite.execute({\n sql: `SELECT o.*, a.name account_name, cat.name category_name, ct.name contractor_name\n FROM pf_ops o\n LEFT JOIN pf_accounts a ON a.id=o.account_id\n LEFT JOIN pf_categories cat ON cat.id=o.category_id\n LEFT JOIN pf_contractors ct ON ct.id=o.contractor_id\n WHERE o.deal_id=? ORDER BY o.date DESC, o.id DESC`,\n args: [id],\n }),\n sqlite.execute({ sql: `SELECT * FROM pf_shipments WHERE deal_id=? ORDER BY date DESC, id DESC`, args: [id] }),\n sqlite.execute({ sql: `SELECT * FROM pf_deal_items WHERE deal_id=? ORDER BY id`, args: [id] }),\n sqlite.execute({ sql: `SELECT * FROM pf_invoices WHERE deal_id=? ORDER BY date DESC, id DESC`, args: [id] }),\n ]);\n const comments = await sqlite.execute({ sql: `SELECT * FROM pf_deal_comments WHERE deal_id=? ORDER BY id DESC`, args: [id] });\n return c.json({ deal: d, ops: ops.rows, shipments: shipments.rows, items: items.rows, invoices: invoices.rows, comments: comments.rows });\n});\napp.post(\"/api/deals\", async (c) => {\n const b = await c.req.json();\n const r = await sqlite.execute({\n sql: `INSERT INTO pf_deals (name,client,status,amount,created_at,comment,deal_type) VALUES (?,?,?,?,?,?,?)`,\n args: [b.name || \"Сделка\", b.client || \"\", b.status || \"work\", num(b.amount), b.created_at || today(), b.comment || \"\", b.deal_type === \"purchase\" ? \"purchase\" : \"sale\"],\n });\n return c.json({ ok: true, id: Number(r.lastInsertRowid) });\n});\napp.put(\"/api/deals/:id\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `UPDATE pf_deals SET name=?, client=?, status=?, amount=?, created_at=?, comment=? WHERE id=?`,\n args: [b.name || \"Сделка\", b.client || \"\", b.status || \"work\", num(b.amount), b.created_at || today(), b.comment || \"\", Number(c.req.param(\"id\"))],\n });\n return c.json({ ok: true });\n});\n// отгрузки (акты) по сделке — метод начисления\napp.post(\"/api/deals/:id/shipments\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `INSERT INTO pf_shipments (deal_id,date,amount,comment) VALUES (?,?,?,?)`,\n args: [Number(c.req.param(\"id\")), b.date || today(), num(b.amount), b.comment || \"\"],\n });\n return c.json({ ok: true });\n});\napp.delete(\"/api/shipments/:id\", async (c) => {\n await sqlite.execute({ sql: `DELETE FROM pf_shipments WHERE id=?`, args: [Number(c.req.param(\"id\"))] });\n return c.json({ ok: true });\n});\n// позиции (товары и услуги) сделки\napp.post(\"/api/deals/:id/items\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `INSERT INTO pf_deal_items (deal_id,name,qty,unit,price) VALUES (?,?,?,?,?)`,\n args: [Number(c.req.param(\"id\")), b.name || \"Услуга\", num(b.qty) || 1, b.unit || \"шт\", num(b.price)],\n });\n return c.json({ ok: true });\n});\napp.delete(\"/api/items/:id\", async (c) => {\n await sqlite.execute({ sql: `DELETE FROM pf_deal_items WHERE id=?`, args: [Number(c.req.param(\"id\"))] });\n return c.json({ ok: true });\n});\napp.delete(\"/api/deals/:id\", async (c) => {\n const id = Number(c.req.param(\"id\"));\n await sqlite.batch([\n { sql: `UPDATE pf_ops SET deal_id=NULL WHERE deal_id=?`, args: [id] },\n { sql: `DELETE FROM pf_deals WHERE id=?`, args: [id] },\n ]);\n return c.json({ ok: true });\n});\n\n// ---------------- Полный импорт из внешней системы ----------------\n// кэшируем справочники по имени, создаём недостающие\nasync function resolveByName(table: string, name: string, extra: Record = {}): Promise {\n if (!name) return null;\n const ex = await sqlite.execute({ sql: `SELECT id FROM ${table} WHERE name=?`, args: [name] });\n if (ex.rows.length) return Number((ex.rows[0] as any).id);\n const cols = [\"name\", ...Object.keys(extra)];\n const r = await sqlite.execute({\n sql: `INSERT INTO ${table} (${cols.join(\",\")}) VALUES (${cols.map(() => \"?\").join(\",\")})`,\n args: [name, ...Object.values(extra)] as any,\n });\n return Number(r.lastInsertRowid);\n}\napp.post(\"/api/import/deals\", async (c) => {\n const { rows } = await c.req.json();\n let n = 0, skipped = 0;\n for (const r of rows || []) {\n const ext = String(r.ext_id || \"\");\n if (ext) {\n const ex = await sqlite.execute({ sql: `SELECT id FROM pf_deals WHERE ext_id=?`, args: [ext] });\n if (ex.rows.length) { skipped++; continue; }\n }\n const res = await sqlite.execute({\n sql: `INSERT INTO pf_deals (name,client,status,amount,created_at,comment,deal_type,ext_id) VALUES (?,?,?,?,?,?,?,?)`,\n args: [r.name || \"Сделка\", r.client || \"\", r.status || \"work\", num(r.amount), r.created_at || today(), r.comment || \"\", r.deal_type === \"purchase\" ? \"purchase\" : \"sale\", ext || null],\n });\n if (num(r.shipped) > 0) {\n await sqlite.execute({\n sql: `INSERT INTO pf_shipments (deal_id,date,amount,comment) VALUES (?,?,?,?)`,\n args: [Number(res.lastInsertRowid), r.created_at || today(), num(r.shipped), \"Отгружено (импорт)\"],\n });\n }\n n++;\n }\n return c.json({ ok: true, n, skipped });\n});\napp.post(\"/api/import/contractors\", async (c) => {\n const { rows } = await c.req.json();\n let n = 0;\n for (const r of rows || []) {\n if (!r.name) continue;\n const ex = await sqlite.execute({ sql: `SELECT id FROM pf_contractors WHERE name=?`, args: [r.name] });\n if (ex.rows.length) {\n if (r.inn) await sqlite.execute({ sql: `UPDATE pf_contractors SET inn=? WHERE id=? AND (inn IS NULL OR inn='')`, args: [String(r.inn), Number((ex.rows[0] as any).id)] });\n continue;\n }\n await sqlite.execute({\n sql: `INSERT INTO pf_contractors (name,grp,inn) VALUES (?,?,?)`,\n args: [r.name, r.grp || \"\", r.inn ? String(r.inn) : \"\"],\n });\n n++;\n }\n return c.json({ ok: true, n });\n});\napp.post(\"/api/import/accounts\", async (c) => {\n const { rows } = await c.req.json(); // {name, start_balance, start_date, bank, number, acc_type}\n let n = 0;\n for (const r of rows || []) {\n if (!r.name) continue;\n const ex = await sqlite.execute({ sql: `SELECT id FROM pf_accounts WHERE name=?`, args: [r.name] });\n if (ex.rows.length) {\n await sqlite.execute({\n sql: `UPDATE pf_accounts SET start_balance=?, start_date=? WHERE id=?`,\n args: [num(r.start_balance), r.start_date || null, Number((ex.rows[0] as any).id)],\n });\n } else {\n await sqlite.execute({\n sql: `INSERT INTO pf_accounts (name,acc_type,bank,number,grp,start_balance,start_date,legal_entity_id) VALUES (?,?,?,?,?,?,?,1)`,\n args: [r.name, r.acc_type || \"Безналичный\", r.bank || \"\", r.number || \"\", r.grp || \"\", num(r.start_balance), r.start_date || null],\n });\n }\n n++;\n }\n return c.json({ ok: true, n });\n});\napp.post(\"/api/import/ops\", async (c) => {\n const { rows } = await c.req.json();\n // предзагрузка справочников в память — минимум SQL на строку\n const [accR, ctrR, catR, projR, dealR, extR] = await Promise.all([\n sqlite.execute(`SELECT id,name FROM pf_accounts`),\n sqlite.execute(`SELECT id,name FROM pf_contractors`),\n sqlite.execute(`SELECT id,name,kind FROM pf_categories`),\n sqlite.execute(`SELECT id,name FROM pf_projects`),\n sqlite.execute(`SELECT id,ext_id FROM pf_deals WHERE ext_id IS NOT NULL`),\n sqlite.execute(`SELECT ext_id FROM pf_ops WHERE ext_id IS NOT NULL`),\n ]);\n const accs = new Map((accR.rows as any[]).map((r) => [r.name, Number(r.id)]));\n const ctrs = new Map((ctrR.rows as any[]).map((r) => [r.name, Number(r.id)]));\n const cats = new Map((catR.rows as any[]).map((r) => [r.name + \"|\" + r.kind, Number(r.id)]));\n const projs = new Map((projR.rows as any[]).map((r) => [r.name, Number(r.id)]));\n const deals = new Map((dealR.rows as any[]).map((r) => [String(r.ext_id), Number(r.id)]));\n const exts = new Set((extR.rows as any[]).map((r) => String(r.ext_id)));\n const mk = async (table: string, name: string, map: Map, extraCols = \"\", extraVals: unknown[] = []) => {\n if (map.has(name)) return map.get(name)!;\n const r = await sqlite.execute({\n sql: `INSERT INTO ${table} (name${extraCols}) VALUES (?${extraVals.map(() => \",?\").join(\"\")})`,\n args: [name, ...extraVals] as any,\n });\n const id = Number(r.lastInsertRowid);\n map.set(name, id);\n return id;\n };\n const inserts: { sql: string; args: unknown[] }[] = [];\n let skipped = 0;\n for (const r of rows || []) {\n const ext = String(r.ext_id || \"\");\n if (ext && exts.has(ext)) { skipped++; continue; }\n const accId = r.account ? await mk(\"pf_accounts\", r.account, accs) : null;\n const acc2Id = r.type === \"transfer\" && r.account2 ? await mk(\"pf_accounts\", r.account2, accs) : null;\n const ctrId = r.contractor ? await mk(\"pf_contractors\", r.contractor, ctrs) : null;\n let catId: number | null = null;\n if (r.category && r.type !== \"transfer\") {\n const kind = r.type === \"income\" ? \"income\" : \"expense\";\n const key = r.category + \"|\" + kind;\n if (cats.has(key)) catId = cats.get(key)!;\n else {\n const cr = await sqlite.execute({ sql: `INSERT INTO pf_categories (name,kind) VALUES (?,?)`, args: [r.category, kind] });\n catId = Number(cr.lastInsertRowid);\n cats.set(key, catId);\n }\n }\n const projId = r.project ? await mk(\"pf_projects\", r.project, projs, \",status\", [\"work\"]) : null;\n const dealId = r.deal_ext ? (deals.get(String(r.deal_ext)) ?? null) : null;\n inserts.push({\n sql: `INSERT INTO pf_ops (date,op_type,confirmed,amount,account_id,account2_id,category_id,contractor_id,deal_id,purpose,project_id,ext_id)\n VALUES (?,?,?,?,?,?,?,?,?,?,?,?)`,\n args: [\n String(r.date).slice(0, 10),\n r.type === \"transfer\" ? \"transfer\" : (r.type === \"income\" ? \"income\" : \"expense\"),\n r.confirmed === false ? 0 : 1,\n Math.abs(num(r.amount)),\n accId, acc2Id, catId, ctrId, dealId, (r.purpose || \"\").slice(0, 500), projId, ext || null,\n ],\n });\n if (ext) exts.add(ext);\n }\n if (inserts.length) await sqlite.batch(inserts);\n return c.json({ ok: true, n: inserts.length, skipped });\n});\n\n// классы статей (Доходы/Расходы/Активы/Обязательства/Капитал) по именам\napp.post(\"/api/import/categories\", async (c) => {\n const { rows } = await c.req.json(); // {name, cat_class}\n let n = 0;\n for (const r of rows || []) {\n if (!r.name) continue;\n const res = await sqlite.execute({\n sql: `UPDATE pf_categories SET cat_class=? WHERE name=?`,\n args: [String(r.cat_class || \"\"), String(r.name).trim()],\n });\n n += Number(res.rowsAffected || 0);\n }\n return c.json({ ok: true, n });\n});\n\n// ---------------- Выставленные счета ----------------\napp.get(\"/api/invoices\", async (c) => {\n const r = await sqlite.execute(\n `SELECT i.*, ct.name contractor_name, d.name deal_name\n FROM pf_invoices i\n LEFT JOIN pf_contractors ct ON ct.id=i.contractor_id\n LEFT JOIN pf_deals d ON d.id=i.deal_id\n ORDER BY i.date DESC, i.id DESC`,\n );\n return c.json(r.rows);\n});\napp.post(\"/api/invoices\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `INSERT INTO pf_invoices (number,date,contractor_id,client,deal_id,amount,status,comment) VALUES (?,?,?,?,?,?,?,?)`,\n args: [b.number || \"\", b.date || today(), b.contractor_id || null, b.client || \"\", b.deal_id || null, num(b.amount), b.status === \"paid\" ? \"paid\" : \"unpaid\", b.comment || \"\"],\n });\n return c.json({ ok: true });\n});\napp.put(\"/api/invoices/:id\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `UPDATE pf_invoices SET number=?, date=?, contractor_id=?, client=?, deal_id=?, amount=?, status=?, comment=? WHERE id=?`,\n args: [b.number || \"\", b.date || today(), b.contractor_id || null, b.client || \"\", b.deal_id || null, num(b.amount), b.status === \"paid\" ? \"paid\" : \"unpaid\", b.comment || \"\", Number(c.req.param(\"id\"))],\n });\n return c.json({ ok: true });\n});\napp.post(\"/api/invoices/:id/paid\", async (c) => {\n await sqlite.execute({ sql: `UPDATE pf_invoices SET status='paid' WHERE id=?`, args: [Number(c.req.param(\"id\"))] });\n return c.json({ ok: true });\n});\napp.delete(\"/api/invoices/:id\", async (c) => {\n await sqlite.execute({ sql: `DELETE FROM pf_invoices WHERE id=?`, args: [Number(c.req.param(\"id\"))] });\n return c.json({ ok: true });\n});\n\n// ---------------- Товары и услуги ----------------\napp.post(\"/api/products\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `INSERT INTO pf_products (name,ptype,unit,price) VALUES (?,?,?,?)`,\n args: [b.name || \"Услуга\", b.ptype === \"product\" ? \"product\" : \"service\", b.unit || \"шт\", num(b.price)],\n });\n return c.json({ ok: true });\n});\napp.put(\"/api/products/:id\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `UPDATE pf_products SET name=?, ptype=?, unit=?, price=? WHERE id=?`,\n args: [b.name || \"Услуга\", b.ptype === \"product\" ? \"product\" : \"service\", b.unit || \"шт\", num(b.price), Number(c.req.param(\"id\"))],\n });\n return c.json({ ok: true });\n});\napp.delete(\"/api/products/:id\", async (c) => {\n await sqlite.execute({ sql: `DELETE FROM pf_products WHERE id=?`, args: [Number(c.req.param(\"id\"))] });\n return c.json({ ok: true });\n});\n\n// ---------------- Мои юрлица ----------------\napp.post(\"/api/legal-entities\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({ sql: `INSERT INTO pf_legal_entities (name,inn,comment) VALUES (?,?,?)`, args: [b.name || \"Юрлицо\", b.inn || \"\", b.comment || \"\"] });\n return c.json({ ok: true });\n});\napp.put(\"/api/legal-entities/:id\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({ sql: `UPDATE pf_legal_entities SET name=?, inn=?, comment=? WHERE id=?`, args: [b.name || \"Юрлицо\", b.inn || \"\", b.comment || \"\", Number(c.req.param(\"id\"))] });\n return c.json({ ok: true });\n});\napp.delete(\"/api/legal-entities/:id\", async (c) => {\n const id = Number(c.req.param(\"id\"));\n await sqlite.batch([\n { sql: `UPDATE pf_accounts SET legal_entity_id=NULL WHERE legal_entity_id=?`, args: [id] },\n { sql: `DELETE FROM pf_legal_entities WHERE id=?`, args: [id] },\n ]);\n return c.json({ ok: true });\n});\n\n// ---------------- Показатели PRO ----------------\napp.get(\"/api/pro\", async (c) => {\n const from = c.req.query(\"from\") || `${today().slice(0, 4)}-01-01`;\n const to = c.req.query(\"to\") || today();\n const byAccount = await sqlite.execute({\n sql: `SELECT a.id, a.name,\n SUM(CASE WHEN o.op_type='income' AND o.account_id=a.id THEN o.amount ELSE 0 END) inc,\n SUM(CASE WHEN o.op_type='expense' AND o.account_id=a.id THEN o.amount ELSE 0 END) exp\n FROM pf_accounts a\n LEFT JOIN pf_ops o ON (o.account_id=a.id) AND o.confirmed=1 AND o.date>=? AND o.date<=?\n WHERE a.active=1 GROUP BY a.id ORDER BY a.name`,\n args: [from, to],\n });\n const topCats = await sqlite.execute({\n sql: `SELECT cat.name, o.op_type, SUM(o.amount) s\n FROM pf_ops o LEFT JOIN pf_categories cat ON cat.id=o.category_id\n WHERE o.confirmed=1 AND o.op_type!='transfer' AND o.date>=? AND o.date<=?\n GROUP BY o.category_id, o.op_type ORDER BY s DESC LIMIT 30`,\n args: [from, to],\n });\n const topContr = await sqlite.execute({\n sql: `SELECT ct.name, o.op_type, SUM(o.amount) s\n FROM pf_ops o LEFT JOIN pf_contractors ct ON ct.id=o.contractor_id\n WHERE o.confirmed=1 AND o.op_type!='transfer' AND o.date>=? AND o.date<=?\n GROUP BY o.contractor_id, o.op_type ORDER BY s DESC LIMIT 30`,\n args: [from, to],\n });\n return c.json({ from, to, by_account: byAccount.rows, top_categories: topCats.rows, top_contractors: topContr.rows });\n});\n\n// ---------------- Показатели ----------------\napp.get(\"/api/indicators\", async (c) => {\n const from = c.req.query(\"from\") || `${today().slice(0, 4)}-01-01`;\n const to = c.req.query(\"to\") || today();\n const basis = c.req.query(\"basis\") === \"accrual\" ? \"accrual\" : \"cash\";\n const months = basis === \"cash\"\n ? await sqlite.execute({\n sql: `SELECT substr(date,1,7) m,\n SUM(CASE WHEN op_type='income' THEN amount ELSE 0 END) income,\n SUM(CASE WHEN op_type='expense' THEN amount ELSE 0 END) expense\n FROM pf_ops WHERE confirmed=1 AND op_type!='transfer' AND date>=? AND date<=?\n GROUP BY substr(date,1,7) ORDER BY m`,\n args: [from, to],\n })\n : await sqlite.execute({ // метод начисления: доход по отгрузкам, расход по операциям\n sql: `SELECT m, SUM(income) income, SUM(expense) expense FROM (\n SELECT substr(date,1,7) m, SUM(amount) income, 0 expense FROM pf_shipments WHERE date>=? AND date<=? GROUP BY substr(date,1,7)\n UNION ALL\n SELECT substr(date,1,7) m, 0 income, SUM(amount) expense FROM pf_ops WHERE confirmed=1 AND op_type='expense' AND date>=? AND date<=? GROUP BY substr(date,1,7)\n ) GROUP BY m ORDER BY m`,\n args: [from, to, from, to],\n });\n const bal = await accountBalances();\n const acc = await sqlite.execute(`SELECT id,name,active FROM pf_accounts WHERE active=1 ORDER BY name`);\n const accounts = (acc.rows as any[]).map((a) => ({ ...a, balance: bal[a.id] ?? 0 }));\n const planned = await sqlite.execute(\n `SELECT\n SUM(CASE WHEN op_type='income' THEN amount ELSE 0 END) inc,\n SUM(CASE WHEN op_type='expense' THEN amount ELSE 0 END) exp,\n COUNT(*) n\n FROM pf_ops WHERE confirmed=0`,\n );\n return c.json({\n from, to,\n months: months.rows,\n accounts,\n total_balance: accounts.reduce((s, a) => s + a.balance, 0),\n planned: planned.rows[0],\n });\n});\n\n// ---------------- Платёжный календарь ----------------\napp.get(\"/api/calendar\", async (c) => {\n const year = c.req.query(\"year\") || today().slice(0, 4);\n const r = await sqlite.execute({\n sql: `SELECT substr(date,1,7) m,\n SUM(CASE WHEN op_type='income' AND confirmed=1 THEN amount ELSE 0 END) income_fact,\n SUM(CASE WHEN op_type='expense' AND confirmed=1 THEN amount ELSE 0 END) expense_fact,\n SUM(CASE WHEN op_type='income' THEN amount ELSE 0 END) income_plan,\n SUM(CASE WHEN op_type='expense' THEN amount ELSE 0 END) expense_plan\n FROM pf_ops WHERE op_type!='transfer' AND substr(date,1,4)=?\n GROUP BY substr(date,1,7) ORDER BY m`,\n args: [year],\n });\n return c.json({ year, months: r.rows });\n});\n\n// ---------------- Отчёт ДДС ----------------\napp.get(\"/api/report/cashflow\", async (c) => {\n const from = c.req.query(\"from\") || `${today().slice(0, 4)}-01-01`;\n const to = c.req.query(\"to\") || today();\n const byCat = await sqlite.execute({\n sql: `SELECT substr(o.date,1,7) m, o.op_type, o.category_id, SUM(o.amount) s\n FROM pf_ops o\n WHERE o.confirmed=1 AND o.date>=? AND o.date<=? AND o.op_type!='transfer'\n GROUP BY substr(o.date,1,7), o.op_type, o.category_id`,\n args: [from, to],\n });\n const transfers = await sqlite.execute({\n sql: `SELECT substr(date,1,7) m, SUM(amount) s FROM pf_ops\n WHERE confirmed=1 AND op_type='transfer' AND date>=? AND date<=?\n GROUP BY substr(date,1,7)`,\n args: [from, to],\n });\n // остаток на конец каждого месяца: старт + все подтверждённые движения до конца месяца\n const startTotal = (await sqlite.execute(`SELECT COALESCE(SUM(start_balance),0) s FROM pf_accounts WHERE active=1`)).rows[0] as any;\n const flows = await sqlite.execute(\n `SELECT substr(date,1,7) m,\n SUM(CASE WHEN op_type='income' THEN amount WHEN op_type='expense' THEN -amount ELSE 0 END) net\n FROM pf_ops WHERE confirmed=1 GROUP BY substr(date,1,7) ORDER BY m`,\n );\n const shipM = await sqlite.execute(\n `SELECT substr(date,1,7) m, SUM(amount) s FROM pf_shipments GROUP BY substr(date,1,7) ORDER BY m`,\n );\n const recvM = await sqlite.execute(\n `SELECT substr(date,1,7) m, SUM(amount) s FROM pf_ops WHERE confirmed=1 AND op_type='income' AND deal_id IS NOT NULL GROUP BY substr(date,1,7) ORDER BY m`,\n );\n return c.json({\n from, to,\n by_category: byCat.rows,\n transfers: transfers.rows,\n start_total: num(startTotal.s),\n net_by_month: flows.rows,\n shipments_by_month: shipM.rows,\n deal_income_by_month: recvM.rows,\n });\n});\n\n// ---------------- Демо-данные ----------------\napp.post(\"/api/demo\", async (c) => {\n const { action } = await c.req.json();\n if (action === \"clear\") {\n await sqlite.batch([\n `DELETE FROM pf_ops`, `DELETE FROM pf_deals`, `DELETE FROM pf_shipments`,\n `DELETE FROM pf_deal_items`, `DELETE FROM pf_invoices`, `DELETE FROM pf_deal_comments`,\n ]);\n return c.json({ ok: true });\n }\n const m = today().slice(0, 7);\n const d = (day: number) => `${m}-${String(day).padStart(2, \"0\")}`;\n const acc = async (name: string) => {\n const r = await sqlite.execute({ sql: `SELECT id FROM pf_accounts WHERE name=?`, args: [name] });\n return (r.rows[0] as any)?.id ?? null;\n };\n const cat = async (name: string) => {\n const r = await sqlite.execute({ sql: `SELECT id FROM pf_categories WHERE name=?`, args: [name] });\n return (r.rows[0] as any)?.id ?? null;\n };\n const ctr = async (name: string) => {\n const r = await sqlite.execute({ sql: `SELECT id FROM pf_contractors WHERE name LIKE ?`, args: [`%${name}%`] });\n return (r.rows[0] as any)?.id ?? null;\n };\n const main = await acc(\"Основной расчетный счет\"), tbank = await acc(\"Т-Банк 5241\"), nalog = await acc(\"Налог\");\n const kAgent = await cat(\"Контрагентские\"), kRuk = await cat(\"% руководителей\"), kBrok = await cat(\"Брокеры\"),\n kAvito = await cat(\"Авито\"), kCeo = await cat(\"CEO\"), kNal = await cat(\"Налоги\"), kArenda = await cat(\"Аренда офиса\");\n const upside = await ctr(\"АПСАЙД\"), park = await ctr(\"ПАРК\"), matel = await ctr(\"Мателевич\"), ded = await ctr(\"Дедерер\");\n const deal = async (name: string, client: string, status: string, amount: number, day: number) => {\n const r = await sqlite.execute({\n sql: `INSERT INTO pf_deals (name,client,status,amount,created_at) VALUES (?,?,?,?,?)`,\n args: [name, client, status, amount, d(day)],\n });\n return Number(r.lastInsertRowid);\n };\n const d1 = await deal(\"Мателевич №J1401\", \"Аужбикович Алла Михайловна\", \"done\", 662156, 2);\n const d2 = await deal(\"Дедерер №J1402\", \"Симонов Михаил Юрьевич\", \"work\", 549000, 8);\n const d3 = await deal(\"Тамбовцев (Екатерина)\", \"Зверева Екатерина Викторовна\", \"nuance\", 390000, 12);\n const op = (o: any) =>\n ({ sql: `INSERT INTO pf_ops (date,op_type,confirmed,amount,account_id,account2_id,category_id,contractor_id,deal_id,purpose) VALUES (?,?,?,?,?,?,?,?,?,?)`,\n args: [o.date, o.t, o.conf ? 1 : 0, o.sum, o.a ?? null, o.a2 ?? null, o.cat ?? null, o.ctr ?? null, o.deal ?? null, o.p ?? \"\"] });\n await sqlite.batch([\n op({ date: d(5), t: \"income\", conf: 1, sum: 662156, a: main, cat: kAgent, ctr: upside, deal: d1, p: \"Оплата по УПД, вознаграждение за привлечение покупателей\" }),\n op({ date: d(7), t: \"expense\", conf: 1, sum: 300000, a: main, cat: kRuk, ctr: matel, deal: d1, p: \"Агентское вознаграждение по договору\" }),\n op({ date: d(25), t: \"income\", conf: 0, sum: 549000, a: main, cat: kAgent, ctr: park, deal: d2, p: \"Ожидаем оплату комиссии от застройщика\" }),\n op({ date: d(28), t: \"expense\", conf: 0, sum: 164700, a: main, cat: kBrok, ctr: ded, deal: d2, p: \"Выплата брокеру после поступления\" }),\n op({ date: d(3), t: \"expense\", conf: 1, sum: 65000, a: main, cat: kArenda, p: \"Аренда офиса\" }),\n op({ date: d(4), t: \"expense\", conf: 1, sum: 45000, a: main, cat: kAvito, p: \"Реклама Авито\" }),\n op({ date: d(5), t: \"expense\", conf: 1, sum: 150000, a: main, cat: kCeo, p: \"ФОТ дирекция\" }),\n op({ date: d(10), t: \"transfer\", conf: 1, sum: 42000, a: main, a2: nalog, p: \"Перевод собственных средств (налоговая копилка)\" }),\n op({ date: d(15), t: \"expense\", conf: 0, sum: 101601, a: nalog, cat: kNal, p: \"УСН авансовый платёж\" }),\n op({ date: d(12), t: \"transfer\", conf: 1, sum: 30000, a: main, a2: tbank, p: \"Перевод собственных средств\" }),\n ]);\n return c.json({ ok: true });\n});\n\n// ---------------- Frontend ----------------\n// экспорт данных для переноса в U.fin v2 (защищён PIN-middleware выше)\nconst EXPORT_TABLES = [\"pf_accounts\", \"pf_categories\", \"pf_contractors\", \"pf_projects\", \"pf_deals\", \"pf_ops\", \"pf_budget_values\", \"pf_budgets\", \"pf_settings\"];\napp.get(\"/api/export\", async (c) => {\n const table = c.req.query(\"table\") || \"\";\n if (!EXPORT_TABLES.includes(table)) return c.json({ error: \"bad table\" }, 400);\n const limit = Math.min(Number(c.req.query(\"limit\") || 500), 1000);\n const offset = Number(c.req.query(\"offset\") || 0);\n const cols = (await sqlite.execute(`PRAGMA table_info(${table})`)).rows.map((r: any) => String(r[1]));\n const r = await sqlite.execute(`SELECT * FROM ${table} LIMIT ${limit} OFFSET ${offset}`);\n const total = (await sqlite.execute(`SELECT COUNT(*) FROM ${table}`)).rows[0][0];\n return c.json({ columns: cols, rows: r.rows, total: Number(total) });\n});\n\napp.get(\"/source\", (c) => c.redirect(parseVal().links.self.val));\n// собранный бандл хранится частями (лимит размера файла в API) и склеивается здесь\napp.get(\"/frontend/app.js\", async () => {\n let out = \"\";\n for (let i = 1; i <= 9; i++) {\n try { out += await readFile(`/frontend/app.part${i}.txt`, import.meta.url); } catch (_e) { break; }\n }\n return new Response(out, { headers: { \"content-type\": \"text/javascript;charset=UTF-8\" } });\n});\napp.get(\"/frontend/*\", (c) => serveFile(c.req.path));\napp.get(\"/\", (c) => serveFile(\"/frontend/index.html\"));\n\nexport default app.fetch;\n","deleted",false,"label",[14],"6e904ebf-a254-4dd3-b3e8-cca1eea5f4a2",["D",1787239139778],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","U.fin — финансовая система агентства недвижимости","imageUrl","userId","caa5b6a0-dd4e-4cd6-a997-9389a6a43948","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019eff71-5749-76b5-abdd-e25a47b3deca","tursoName","6e904ebfa2544dd3b3e8cca1eea5f4a2","tursoHostname","6e904ebfa2544dd3b3e8cca1eea5f4a2-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789499469860],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_104":100},"org",{"_9":10,"_97":98,"_99":100,"_55":56,"_101":102,"_103":42},"branch",{"_35":30,"_11":88,"_22":28,"_89":45,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":12,"_96":10},"isOwned","main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","Кирилл Тамбовцев","avatar","https://lh3.googleusercontent.com/a/ACg8ocKCNneTjcn307kcxhB_NS1R7_0SBhSKM-2hy73z0JvhXFipwkhs=s96-c","tier","free","canRestrictVals","profileImageUrl","ctx",{"_81":-5,"_138":139,"_140":141,"_142":143,"_144":42,"_145":42,"_146":42,"_147":32,"_148":-5,"_149":-7,"_150":-7,"_151":62,"_152":42,"_153":42,"_154":-5,"_155":42,"_156":42,"_157":42,"_158":-5},"ENV",{"_109":110,"_111":112,"_113":88,"_114":115,"_116":117,"_118":119,"_120":121,"_122":123,"_124":125,"_126":127,"_128":129,"_130":131,"_132":133,"_134":135,"_136":137},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town","orgs",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]