[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_159":160,"_161":162},"routes/_app.x.$handle.$name",{"_99":98,"_32":45,"_108":89,"_27":53,"_13":14,"_109":-5,"_11":110,"_111":112,"_113":114,"_115":116,"_117":118,"_20":-5,"_119":-5,"_120":-5,"_121":25,"_122":123,"_124":58,"_125":-5,"_126":-5,"_127":128},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_43":44,"_32":45,"_38":46,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":31,"_57":58,"_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":25},"version",25,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":37,"_38":39,"_40":14,"_41":42,"_24":25},"previous",{"_30":31,"_32":28,"_33":34,"_35":36},"deleted",false,"deletedHere","path","main.ts","latestPath","fileId","7a6545e2-a22b-11f1-a7fb-1607ee4eb77e","name","fileType","http","content","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\nconst PLUGGY_BASE_URL = \"https://api.pluggy.ai\";\nconst corsHeaders = {\n \"Access-Control-Allow-Origin\": \"*\",\n \"Access-Control-Allow-Headers\": \"Authorization, Content-Type\",\n \"Access-Control-Allow-Methods\": \"GET, POST, PUT, DELETE, OPTIONS\",\n \"Content-Type\": \"application/json; charset=utf-8\",\n};\n\nfunction json(data: unknown, status = 200) {\n return new Response(JSON.stringify(data), { status, headers: corsHeaders });\n}\n\nfunction requiredEnv(name: string): string {\n const value = Deno.env.get(name)?.trim();\n if (!value) throw new Error(`Variável ${name} não configurada no Val Town.`);\n return value;\n}\n\nfunction validateItemIds(values: string[]): string[] {\n const ids = values.map((id) => id.trim().replace(/^[\"']|[\"']$/g, \"\")).filter(\n Boolean,\n );\n const uuid =\n /^[0-9a-f]{8}-[0-9a-f]{4}-[1-8][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i;\n if (ids.some((id) => !uuid.test(id))) {\n throw new Error(\n \"PLUGGY_ITEM_IDS contém valor inválido. Use somente UUIDs completos.\",\n );\n }\n return [...new Set(ids)];\n}\n\nfunction parseItemIds(raw: string): string[] {\n if (raw.startsWith(\"[\")) {\n const parsed = JSON.parse(raw);\n if (!Array.isArray(parsed)) {\n throw new Error(\n \"PLUGGY_ITEM_IDS deve ser uma lista ou IDs separados por vírgula.\",\n );\n }\n return validateItemIds(parsed.map(String));\n }\n return validateItemIds(raw.split(/[\\s,;]+/));\n}\n\nasync function ensureSchema() {\n await sqlite.batch([\n `CREATE TABLE IF NOT EXISTS items (id TEXT PRIMARY KEY,connector_id TEXT,bank_name TEXT NOT NULL,status TEXT,updated_at TEXT NOT NULL)`,\n `CREATE TABLE IF NOT EXISTS accounts (id TEXT PRIMARY KEY,item_id TEXT NOT NULL,name TEXT NOT NULL,type TEXT,subtype TEXT,currency_code TEXT,updated_at TEXT NOT NULL)`,\n `CREATE TABLE IF NOT EXISTS wallet_groups (id TEXT PRIMARY KEY,name TEXT NOT NULL UNIQUE,sort_order INTEGER NOT NULL DEFAULT 0,created_at TEXT NOT NULL)`,\n `CREATE TABLE IF NOT EXISTS wallets (id TEXT PRIMARY KEY,name TEXT NOT NULL UNIQUE,created_at TEXT NOT NULL)`,\n `CREATE TABLE IF NOT EXISTS account_wallet_mapping (account_id TEXT PRIMARY KEY,wallet_id TEXT NOT NULL,updated_at TEXT NOT NULL,FOREIGN KEY(account_id) REFERENCES accounts(id),FOREIGN KEY(wallet_id) REFERENCES wallets(id))`,\n `CREATE TABLE IF NOT EXISTS investments (id TEXT PRIMARY KEY,item_id TEXT NOT NULL,name TEXT NOT NULL,number TEXT,type TEXT,subtype TEXT,balance REAL,amount REAL,currency_code TEXT,owner TEXT,status TEXT,institution_name TEXT,date TEXT,raw_details TEXT,updated_at TEXT NOT NULL,is_deleted INTEGER NOT NULL DEFAULT 0)`,\n `CREATE TABLE IF NOT EXISTS investment_wallet_mapping (investment_id TEXT PRIMARY KEY,wallet_id TEXT NOT NULL,updated_at TEXT NOT NULL,FOREIGN KEY(investment_id) REFERENCES investments(id),FOREIGN KEY(wallet_id) REFERENCES wallets(id))`,\n `CREATE TABLE IF NOT EXISTS transactions (id TEXT PRIMARY KEY,account_id TEXT NOT NULL,item_id TEXT,date TEXT NOT NULL,description TEXT,description_raw TEXT,category TEXT,category_id TEXT,amount REAL NOT NULL,currency_code TEXT,type TEXT,status TEXT,content_hash TEXT NOT NULL,is_deleted INTEGER NOT NULL DEFAULT 0,first_seen_at TEXT NOT NULL,updated_at TEXT NOT NULL,last_seen_sync TEXT NOT NULL)`,\n `CREATE INDEX IF NOT EXISTS idx_transactions_date ON transactions(date)`,\n `CREATE INDEX IF NOT EXISTS idx_transactions_account ON transactions(account_id)`,\n `CREATE TABLE IF NOT EXISTS sync_runs (id TEXT PRIMARY KEY,date_from TEXT NOT NULL,started_at TEXT NOT NULL,finished_at TEXT,new_count INTEGER DEFAULT 0,updated_count INTEGER DEFAULT 0,unchanged_count INTEGER DEFAULT 0,removed_count INTEGER DEFAULT 0,status TEXT NOT NULL,error TEXT)`,\n `CREATE TABLE IF NOT EXISTS transaction_change_log (id TEXT PRIMARY KEY,sync_id TEXT NOT NULL,transaction_id TEXT NOT NULL,changed_at TEXT NOT NULL,changed_fields TEXT NOT NULL,date_before TEXT,date_after TEXT,description_before TEXT,description_after TEXT,amount_before REAL,amount_after REAL,category_before TEXT,category_after TEXT,status_before TEXT,status_after TEXT,FOREIGN KEY(transaction_id) REFERENCES transactions(id))`,\n `CREATE INDEX IF NOT EXISTS idx_transaction_change_sync ON transaction_change_log(sync_id)`,\n `CREATE INDEX IF NOT EXISTS idx_transaction_change_transaction ON transaction_change_log(transaction_id,changed_at)`,\n `CREATE TABLE IF NOT EXISTS category2_groups (id TEXT PRIMARY KEY,nature TEXT NOT NULL,code TEXT NOT NULL,name TEXT NOT NULL,sort_order INTEGER NOT NULL DEFAULT 0,created_at TEXT NOT NULL,UNIQUE(nature,code))`,\n `CREATE TABLE IF NOT EXISTS category2_items (id TEXT PRIMARY KEY,group_id TEXT NOT NULL,nature TEXT NOT NULL,code TEXT NOT NULL,name TEXT NOT NULL,is_reducer INTEGER NOT NULL DEFAULT 0,sort_order INTEGER NOT NULL DEFAULT 0,created_at TEXT NOT NULL,FOREIGN KEY(group_id) REFERENCES category2_groups(id),UNIQUE(nature,code))`,\n `CREATE TABLE IF NOT EXISTS transaction_category2 (transaction_id TEXT PRIMARY KEY,category_id TEXT NOT NULL,updated_at TEXT NOT NULL,FOREIGN KEY(transaction_id) REFERENCES transactions(id),FOREIGN KEY(category_id) REFERENCES category2_items(id))`,\n `CREATE INDEX IF NOT EXISTS idx_category2_items_group ON category2_items(group_id,sort_order)`,\n `CREATE INDEX IF NOT EXISTS idx_transaction_category2_category ON transaction_category2(category_id)`,\n `CREATE TABLE IF NOT EXISTS category2_meta (key TEXT PRIMARY KEY,value TEXT NOT NULL)`,\n `CREATE TABLE IF NOT EXISTS pluggy_category_map (pluggy_category TEXT PRIMARY KEY,category_id TEXT NOT NULL,updated_at TEXT NOT NULL,FOREIGN KEY(category_id) REFERENCES category2_items(id))`,\n ]);\n await ensureColumn(\"accounts\", \"number\", \"TEXT\");\n await ensureColumn(\"accounts\", \"balance\", \"REAL\");\n await ensureColumn(\"accounts\", \"owner\", \"TEXT\");\n await ensureColumn(\"accounts\", \"tax_number\", \"TEXT\");\n await ensureColumn(\"accounts\", \"bank_data\", \"TEXT\");\n await ensureColumn(\"accounts\", \"credit_data\", \"TEXT\");\n await ensureColumn(\"items\", \"products\", \"TEXT\");\n await ensureColumn(\"items\", \"bank_cnpj\", \"TEXT\");\n await ensureColumn(\"transactions\", \"source_type\", \"TEXT\");\n await ensureColumn(\"transactions\", \"investment_id\", \"TEXT\");\n await ensureColumn(\"transactions\", \"reviewed\", \"INTEGER NOT NULL DEFAULT 0\");\n await ensureColumn(\"transactions\", \"reviewed_at\", \"TEXT\");\n await ensureColumn(\"transactions\", \"manual_note\", \"TEXT\");\n await ensureColumn(\"transactions\", \"manual_note_updated_at\", \"TEXT\");\n await ensureColumn(\"transactions\", \"raw_details\", \"TEXT\");\n await ensureColumn(\"wallets\", \"sort_order\", \"INTEGER NOT NULL DEFAULT 0\");\n await ensureColumn(\"wallets\", \"group_id\", \"TEXT\");\n await ensureColumn(\"wallets\", \"opening_balance\", \"REAL NOT NULL DEFAULT 0\");\n await ensureColumn(\"wallets\", \"opening_date\", \"TEXT\");\n await ensureCategory2Seeds();\n // O histórico é acumulativo: registros já armazenados nunca são ocultados\n // apenas porque a Pluggy deixou de devolvê-los em uma sincronização posterior.\n await sqlite.batch([\n `UPDATE transactions SET is_deleted=0 WHERE is_deleted<>0`,\n `UPDATE investments SET is_deleted=0 WHERE is_deleted<>0`,\n ]);\n}\n\nconst CATEGORY2_GROUPS = [\n [\"DESPESA\", \"1.0\", \"CASA\"],\n [\"DESPESA\", \"2.0\", \"ALIMENTAÇÃO\"],\n [\"DESPESA\", \"3.0\", \"SAÚDE\"],\n [\"DESPESA\", \"4.0\", \"COMPRAS\"],\n [\"DESPESA\", \"5.0\", \"TRANSPORTE\"],\n [\"DESPESA\", \"6.0\", \"DEPENDENTES\"],\n [\"DESPESA\", \"7.0\", \"VIAGEM\"],\n [\"DESPESA\", \"8.0\", \"EDUCAÇÃO\"],\n [\"DESPESA\", \"9.0\", \"FINANCEIRO\"],\n [\"DESPESA\", \"9.1\", \"EMPRESA\"],\n [\"DESPESA\", \"10.0\", \"REEMBOLSO\"],\n [\"RECEITA\", \"0.0\", \"CORRENTE\"],\n [\"RECEITA\", \"0.1\", \"RECEITA INVESTIMENTO (FÍSICO)\"],\n [\"RECEITA\", \"0.2\", \"RECEITA FINANCEIRA\"],\n [\"RECEITA\", \"0.4\", \"TRANSACIONAL\"],\n [\"RECEITA\", \"0.5\", \"OUTROS RECEITA\"],\n [\"MOVIMENTACAO\", \"11.0\", \"MOVIMENTAÇÃO\"],\n] as const;\n\nconst CATEGORY2_ITEMS = [\n [\"1.0\", \"1.01\", \"FINANCIAMENTO\", 0],\n [\"1.0\", \"1.02\", \"CONDOMÍNIO 1\", 0],\n [\"1.0\", \"1.03\", \"CONDOMÍNIO 2\", 0],\n [\"1.0\", \"1.04\", \"TELEFONE\", 0],\n [\"1.0\", \"1.05\", \"GÁS\", 0],\n [\"1.0\", \"1.06\", \"INTERNET\", 0],\n [\"1.0\", \"1.07\", \"LUZ\", 0],\n [\"1.0\", \"1.08\", \"MOTORISTA\", 0],\n [\"1.0\", \"1.09\", \"EMPREGADA\", 0],\n [\"1.0\", \"1.10\", \"STREAMING\", 0],\n [\"1.0\", \"1.11\", \"IPTU\", 0],\n [\"1.0\", \"1.12\", \"CASA OUTROS\", 0],\n [\"1.0\", \"1.13\", \"ALUGUEL\", 0],\n [\"2.0\", \"2.01\", \"MERCADO\", 0],\n [\"2.0\", \"2.02\", \"IFOOD\", 0],\n [\"2.0\", \"2.03\", \"RESTAURANTE\", 0],\n [\"2.0\", \"2.04\", \"ALIMENTAÇÃO POSTO\", 0],\n [\"2.0\", \"2.05\", \"ALIMENTAÇÃO OUTROS\", 0],\n [\"3.0\", \"3.01\", \"PLANO SAÚDE\", 0],\n [\"3.0\", \"3.02\", \"FARMÁCIA\", 0],\n [\"3.0\", \"3.03\", \"ACADEMIA\", 0],\n [\"3.0\", \"3.04\", \"PERSONAL\", 0],\n [\"3.0\", \"3.05\", \"DENTISTA\", 0],\n [\"3.0\", \"3.06\", \"CABELO\", 0],\n [\"3.0\", \"3.07\", \"SAÚDE OUTROS\", 0],\n [\"4.0\", \"4.01\", \"COMPRAS CASA\", 0],\n [\"4.0\", \"4.02\", \"COMPRAS PESSOAL\", 0],\n [\"4.0\", \"4.03\", \"COMPRAS PRESENTES\", 0],\n [\"4.0\", \"4.04\", \"COMPRAS ROUPAS\", 0],\n [\"4.0\", \"4.05\", \"COMPRAS LAZER\", 0],\n [\"4.0\", \"4.06\", \"COMPRAS OUTROS\", 0],\n [\"5.0\", \"5.01\", \"COMBUSTÍVEL\", 0],\n [\"5.0\", \"5.02\", \"SEGURO\", 0],\n [\"5.0\", \"5.03\", \"UBER\", 0],\n [\"5.0\", \"5.04\", \"IPVA\", 0],\n [\"5.0\", \"5.05\", \"REVISÃO\", 0],\n [\"5.0\", \"5.06\", \"ESTACIONAMENTO\", 0],\n [\"5.0\", \"5.07\", \"TRANSPORTE OUTROS\", 0],\n [\"6.0\", \"6.01\", \"KUMON CECÍLIA\", 0],\n [\"6.0\", \"6.02\", \"NATAÇÃO CECÍLIA\", 0],\n [\"6.0\", \"6.03\", \"PLANO SAÚDE VERE\", 0],\n [\"6.0\", \"6.04\", \"DEPENDENTES OUTROS\", 0],\n [\"7.0\", \"7.01\", \"PASSAGEM\", 0],\n [\"7.0\", \"7.02\", \"VIAGEM OUTROS\", 0],\n [\"8.0\", \"8.01\", \"EDUCAÇÃO OUTROS\", 0],\n [\"9.0\", \"9.01\", \"PMT EMPRÉSTIMO PESSOAL\", 0],\n [\"9.0\", \"9.02\", \"PMT EMPRÉSTIMO PJ\", 0],\n [\"9.0\", \"9.05\", \"JUROS\", 0],\n [\"9.0\", \"9.06\", \"IOF\", 0],\n [\"9.0\", \"9.07\", \"TARIFA\", 0],\n [\"9.0\", \"9.08\", \"FINANCEIRO OUTROS\", 0],\n [\"9.1\", \"9.1.1\", \"CONTADOR PRACO\", 0],\n [\"9.1\", \"9.1.2\", \"CONTADOR CAVENAGUE\", 0],\n [\"9.1\", \"9.1.3\", \"IMPOSTOS CORRENTE\", 0],\n [\"9.1\", \"9.1.4\", \"IMPOSTOS PARCELADOS FEDERAL\", 0],\n [\"9.1\", \"9.1.5\", \"IMPOSTOS PARCELADOS MUNICIPAL\", 0],\n [\"10.0\", \"10.01\", \"REEMBOLSO DRYVE\", 1],\n [\"10.0\", \"10.02\", \"REEMBOLSO OUTROS\", 1],\n [\"0.0\", \"0.0.1\", \"SALÁRIO\", 0],\n [\"0.0\", \"0.0.2\", \"13º\", 0],\n [\"0.0\", \"0.0.3\", \"FÉRIAS\", 0],\n [\"0.0\", \"0.0.4\", \"BÔNUS EMPRESA\", 0],\n [\"0.1\", \"0.1.1\", \"ALUGUEL 3º\", 0],\n [\"0.2\", \"0.2.1\", \"RENDIMENTO\", 0],\n [\"0.4\", \"0.4.1\", \"PIX TRANSACIONAL\", 0],\n [\"0.4\", \"0.4.2\", \"TRANSFERÊNCIA TRANSACIONAL\", 0],\n [\"0.5\", \"0.5.1\", \"ENTRADA OUTROS\", 0],\n [\"11.0\", \"11.01\", \"PAGAMENTO CARTÃO (BUSINESS/AMEX)\", 0],\n [\"11.0\", \"11.02\", \"APORTE INVESTIMENTO\", 0],\n [\"11.0\", \"11.03\", \"RESGATE INVESTIMENTO\", 0],\n [\"11.0\", \"11.04\", \"INVESTIMENTO INICIAL\", 0],\n [\"11.0\", \"11.05\", \"EMPRÉSTIMO (ENTRADA)\", 0],\n [\"11.0\", \"11.06\", \"AJUSTE SALDO\", 0],\n [\"11.0\", \"11.07\", \"TRANSFERÊNCIA ENTRE CONTAS\", 0],\n] as const;\n\nfunction category2Id(prefix: string, code: string) {\n return `${prefix}-${code.replace(/\\./g, \"-\")}`;\n}\n\nasync function ensureCategory2Seeds() {\n const marker = await sqlite.execute({\n sql: `SELECT value FROM category2_meta WHERE key='base-seeded'`,\n args: [],\n });\n if (marker.rows.length) return;\n const now = new Date().toISOString();\n const groups = CATEGORY2_GROUPS.map((group, index) => ({\n sql:\n `INSERT OR IGNORE INTO category2_groups(id,nature,code,name,sort_order,created_at) VALUES(?,?,?,?,?,?)`,\n args: [\n category2Id(\"base-group\", group[1]),\n group[0],\n group[1],\n group[2],\n index,\n now,\n ],\n }));\n const groupMap = new Map(\n CATEGORY2_GROUPS.map((\n group,\n ) => [group[1], {\n id: category2Id(\"base-group\", group[1]),\n nature: group[0],\n }]),\n );\n const counters = new Map();\n const items = CATEGORY2_ITEMS.map((item) => {\n const group = groupMap.get(item[0])!;\n const order = counters.get(item[0]) || 0;\n counters.set(item[0], order + 1);\n return {\n sql:\n `INSERT OR IGNORE INTO category2_items(id,group_id,nature,code,name,is_reducer,sort_order,created_at) VALUES(?,?,?,?,?,?,?,?)`,\n args: [\n category2Id(\"base-item\", item[1]),\n group.id,\n group.nature,\n item[1],\n item[2],\n item[3],\n order,\n now,\n ],\n };\n });\n await executeBatches([...groups, ...items]);\n await sqlite.execute({\n sql:\n `INSERT OR REPLACE INTO category2_meta(key,value) VALUES('base-seeded','1')`,\n args: [],\n });\n}\n\nasync function ensureColumn(table: string, column: string, type: string) {\n const info = await sqlite.execute(`PRAGMA table_info(${table})`);\n if (!(info.rows as any[]).some((row) => row.name === column)) {\n await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN ${column} ${type}`);\n }\n}\n\nasync function pluggyFetch(pathOrUrl: string, apiKey: string) {\n const url = pathOrUrl.startsWith(\"http\")\n ? pathOrUrl\n : `${PLUGGY_BASE_URL}${pathOrUrl}`;\n const response = await fetch(url, {\n headers: { \"X-API-KEY\": apiKey, Accept: \"application/json\" },\n });\n const body = await response.json().catch(() => null);\n if (!response.ok) {\n throw new Error(\n `Pluggy: ${\n body?.message || body?.error?.message || `HTTP ${response.status}`\n }`,\n );\n }\n return body;\n}\n\nasync function createApiKey() {\n const response = await fetch(`${PLUGGY_BASE_URL}/auth`, {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/json\", Accept: \"application/json\" },\n body: JSON.stringify({\n clientId: requiredEnv(\"PLUGGY_CLIENT_ID\"),\n clientSecret: requiredEnv(\"PLUGGY_CLIENT_SECRET\"),\n }),\n });\n const body = await response.json().catch(() => null);\n if (!response.ok || !body?.apiKey) {\n throw new Error(\"Não foi possível autenticar na Pluggy.\");\n }\n return body.apiKey as string;\n}\n\nasync function getAccounts(itemIds: string[], apiKey: string) {\n const groups = await Promise.all(itemIds.map(async (itemId) => {\n const body = await pluggyFetch(\n `/accounts?itemId=${encodeURIComponent(itemId)}`,\n apiKey,\n );\n return (body?.results || []).map((account: any) => ({\n ...account,\n itemId,\n }));\n }));\n return groups.flat();\n}\n\nasync function getItemDetails(itemIds: string[], apiKey: string) {\n return await Promise.all(itemIds.map(async (itemId) => {\n const item = await pluggyFetch(\n `/items/${encodeURIComponent(itemId)}`,\n apiKey,\n );\n let connector: any = item?.connector || null;\n const connectorId = item?.connectorId || connector?.id || null;\n if (!connector?.name && connectorId) {\n connector = await pluggyFetch(\n `/connectors/${encodeURIComponent(connectorId)}`,\n apiKey,\n ).catch(() => null);\n }\n const bankCnpj = firstDocument([\n connector?.cnpj,\n connector?.taxNumber,\n connector?.documentNumber,\n connector?.institution?.cnpj,\n connector?.institution?.taxNumber,\n item?.connector?.cnpj,\n item?.connector?.taxNumber,\n item?.institution?.cnpj,\n item?.institution?.taxNumber,\n item?.bankCnpj,\n ]);\n return {\n id: itemId,\n connectorId: connectorId ? String(connectorId) : null,\n bankName: connector?.name ||\n (typeof item?.connector === \"string\" ? item.connector : \"\") ||\n item?.connectorName || item?.name || `Conexão ${itemId.slice(0, 8)}`,\n bankCnpj,\n status: item?.status || item?.executionStatus || null,\n products: Array.isArray(item?.products) ? item.products : [],\n };\n }));\n}\n\nfunction firstDocument(values: unknown[]): string | null {\n for (const value of values) {\n const text = typeof value === \"object\" && value !== null\n ? String((value as any).value || \"\")\n : String(value || \"\");\n const digits = text.replace(/\\D/g, \"\");\n if (digits.length === 14) return digits;\n }\n return null;\n}\n\nasync function getInvestments(itemIds: string[], apiKey: string) {\n const groups = await Promise.all(itemIds.map(async (itemId) => {\n const all: any[] = [];\n for (let page = 1; page <= 100; page++) {\n const body = await pluggyFetch(\n `/investments?itemId=${\n encodeURIComponent(itemId)\n }&pageSize=500&page=${page}`,\n apiKey,\n );\n const results = Array.isArray(body?.results) ? body.results : [];\n all.push(\n ...results.map((investment: any) => ({ ...investment, itemId })),\n );\n const totalPages = Number(body?.totalPages);\n if (\n Number.isFinite(totalPages) && totalPages > 0\n ? page >= totalPages\n : results.length < 500\n ) break;\n }\n return all;\n }));\n return groups.flat();\n}\n\nasync function getEveryTransaction(\n account: any,\n apiKey: string,\n dateFrom: string,\n) {\n const params = new URLSearchParams({ accountId: account.id, dateFrom });\n params.set(\"pageSize\", \"500\");\n let path = `/v2/transactions?${params.toString()}`;\n const transactions: any[] = [];\n const visited = new Set();\n while (path) {\n if (visited.has(path)) {\n throw new Error(`Cursor repetido na conta ${account.id}.`);\n }\n visited.add(path);\n const page = await pluggyFetch(path, apiKey);\n if (!Array.isArray(page?.results)) {\n throw new Error(`Resposta inválida para a conta ${account.id}.`);\n }\n transactions.push(\n ...page.results.map((transaction: any) => ({\n ...transaction,\n itemId: account.itemId,\n sourceType: \"account\",\n investmentId: null,\n rawDetails: transaction,\n })),\n );\n path = nextTransactionsPath(path, page.next);\n }\n return transactions;\n}\n\nfunction nextTransactionsPath(currentPath: string, next: unknown): string {\n if (!next) return \"\";\n const value = String(next).trim();\n if (!value) return \"\";\n const current = new URL(currentPath, PLUGGY_BASE_URL);\n let candidate: URL;\n if (/^https?:\\/\\//i.test(value)) {\n candidate = new URL(value);\n if (candidate.origin !== PLUGGY_BASE_URL) {\n throw new Error(\"A Pluggy devolveu paginação de outra origem.\");\n }\n } else if (value.startsWith(\"/\")) candidate = new URL(value, PLUGGY_BASE_URL);\n else if (value.startsWith(\"?\")) {\n candidate = new URL(current.pathname + value, PLUGGY_BASE_URL);\n } else candidate = new URL(current.pathname + \"?\" + value, PLUGGY_BASE_URL);\n for (const [key, paramValue] of current.searchParams.entries()) {\n if (!candidate.searchParams.has(key)) {\n candidate.searchParams.set(key, paramValue);\n }\n }\n const resolved = candidate.pathname +\n (candidate.searchParams.toString()\n ? `?${candidate.searchParams.toString()}`\n : \"\");\n return resolved === current.pathname + current.search ? \"\" : resolved;\n}\n\nfunction movementFingerprint(investmentId: string, movement: any) {\n return [\n investmentId,\n movement.date,\n movement.tradeDate,\n movement.type,\n movement.amount,\n movement.netAmount,\n movement.quantity,\n movement.value,\n ].map((value) => String(value ?? \"\")).join(\"|\");\n}\n\nasync function collectInvestmentMovements(\n investment: any,\n apiKey: string,\n dateFrom: string,\n) {\n const movements: any[] = [];\n const signatures = new Set();\n for (let page = 1; page <= 100; page++) {\n const body = await pluggyFetch(\n `/investments/${\n encodeURIComponent(investment.id)\n }/transactions?pageSize=500&page=${page}`,\n apiKey,\n );\n const results = Array.isArray(body?.results) ? body.results : [];\n const signature = results.length\n ? `${results.length}:${results[0]?.id || JSON.stringify(results[0])}:${\n results.at(-1)?.id || JSON.stringify(results.at(-1))\n }`\n : \"empty\";\n if (signatures.has(signature)) {\n throw new Error(\n `A Pluggy repetiu a página ${page} do investimento ${investment.id}.`,\n );\n }\n signatures.add(signature);\n movements.push(...results);\n const totalPages = Number(body?.totalPages);\n if (\n Number.isFinite(totalPages) && totalPages > 0\n ? page >= totalPages\n : results.length < 500\n ) break;\n }\n const decorated = movements.map((movement, index) => ({\n movement,\n index,\n fingerprint: movementFingerprint(investment.id, movement),\n }));\n const ordered = decorated.slice().sort((a, b) =>\n `${\n a.movement.date || a.movement.tradeDate || \"\"\n }|${a.fingerprint}|${a.index}`.localeCompare(\n `${\n b.movement.date || b.movement.tradeDate || \"\"\n }|${b.fingerprint}|${b.index}`,\n )\n );\n const counters = new Map();\n const occurrences = new Map();\n for (const entry of ordered) {\n const count = (counters.get(entry.fingerprint) || 0) + 1;\n counters.set(entry.fingerprint, count);\n occurrences.set(entry.index, count);\n }\n const output: any[] = [];\n for (const entry of decorated) {\n const movement = entry.movement;\n const date = String(movement.date || movement.tradeDate || \"\").slice(0, 10);\n if (!/^\\d{4}-\\d{2}-\\d{2}$/.test(date) || date < dateFrom) continue;\n const movementType = String(movement.type || \"\").toUpperCase();\n const rawAmount = movement.netAmount ?? movement.amount ??\n ((Number(movement.quantity) || 0) * (Number(movement.value) || 0));\n const providerId = movement.id || movement.providerId;\n const synthetic = await hashText(entry.fingerprint);\n output.push({\n id: providerId\n ? `inv:${investment.id}:${providerId}`\n : `inv:${investment.id}:synthetic-${synthetic}-${\n occurrences.get(entry.index)\n }`,\n accountId: investment.id,\n investmentId: investment.id,\n itemId: investment.itemId,\n sourceType: \"investment\",\n date,\n description: [\n movementType || \"Movimentação\",\n movement.description,\n investment.name,\n ].filter(Boolean).join(\" · \"),\n descriptionRaw: movement.description || null,\n category: \"Investimentos\",\n categoryId: null,\n amount: Math.abs(Number(rawAmount) || 0),\n currencyCode: investment.currencyCode || \"BRL\",\n type: movementType === \"INTEREST\"\n ? \"CREDIT\"\n : movementType === \"TAX\"\n ? \"DEBIT\"\n : \"MOVEMENT\",\n status: \"POSTED\",\n rawDetails: movement,\n });\n }\n return output;\n}\n\nasync function hashText(value: string) {\n const digest = await crypto.subtle.digest(\n \"SHA-256\",\n new TextEncoder().encode(value),\n );\n return [...new Uint8Array(digest)].map((byte) =>\n byte.toString(16).padStart(2, \"0\")\n ).join(\"\");\n}\n\nasync function hashTransaction(transaction: any) {\n const comparable = JSON.stringify({\n accountId: transaction.accountId,\n investmentId: transaction.investmentId,\n sourceType: transaction.sourceType,\n date: transaction.date,\n description: transaction.description,\n descriptionRaw: transaction.descriptionRaw,\n category: transaction.category,\n categoryId: transaction.categoryId,\n amount: transaction.amount,\n currencyCode: transaction.currencyCode,\n type: transaction.type,\n status: transaction.status,\n });\n const digest = await crypto.subtle.digest(\n \"SHA-256\",\n new TextEncoder().encode(comparable),\n );\n return [...new Uint8Array(digest)].map((byte) =>\n byte.toString(16).padStart(2, \"0\")\n ).join(\"\");\n}\n\nasync function executeBatches(statements: any[], size = 100) {\n for (let index = 0; index < statements.length; index += size) {\n await sqlite.batch(statements.slice(index, index + size));\n }\n}\n\nasync function syncTransactions(dateFrom: string) {\n const syncId = crypto.randomUUID();\n const startedAt = new Date().toISOString();\n await sqlite.execute({\n sql:\n `INSERT INTO sync_runs(id,date_from,started_at,status) VALUES(?,?,?,?)`,\n args: [syncId, dateFrom, startedAt, \"RUNNING\"],\n });\n try {\n const apiKey = await createApiKey();\n const itemIds = parseItemIds(requiredEnv(\"PLUGGY_ITEM_IDS\"));\n const [itemDetails, accounts, investments] = await Promise.all([\n getItemDetails(itemIds, apiKey),\n getAccounts(itemIds, apiKey),\n getInvestments(itemIds, apiKey),\n ]);\n for (const item of itemDetails) {\n const relatedAccount = accounts.find((account: any) =>\n account.itemId === item.id\n );\n const relatedInvestment = investments.find((investment: any) =>\n investment.itemId === item.id\n );\n if (!item.bankName || /^meupluggy$/i.test(item.bankName)) {\n item.bankName = relatedAccount?.marketingName || relatedAccount?.name ||\n relatedInvestment?.institution?.name || relatedInvestment?.issuer ||\n item.bankName || `Conexão ${item.id.slice(0, 8)}`;\n }\n }\n const now = new Date().toISOString();\n await executeBatches(itemDetails.map((item: any) => ({\n sql:\n `INSERT INTO items(id,connector_id,bank_name,status,updated_at,products,bank_cnpj) VALUES(?,?,?,?,?,?,?) ON CONFLICT(id) DO UPDATE SET connector_id=excluded.connector_id,bank_name=excluded.bank_name,status=excluded.status,updated_at=excluded.updated_at,products=excluded.products,bank_cnpj=excluded.bank_cnpj`,\n args: [\n item.id,\n item.connectorId,\n item.bankName,\n item.status,\n now,\n JSON.stringify(item.products),\n item.bankCnpj,\n ],\n })));\n await executeBatches(accounts.map((account: any) => ({\n sql:\n `INSERT INTO accounts(id,item_id,name,type,subtype,currency_code,updated_at,number,balance,owner,tax_number,bank_data,credit_data) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?) ON CONFLICT(id) DO UPDATE SET item_id=excluded.item_id,name=excluded.name,type=excluded.type,subtype=excluded.subtype,currency_code=excluded.currency_code,updated_at=excluded.updated_at,number=excluded.number,balance=excluded.balance,owner=excluded.owner,tax_number=excluded.tax_number,bank_data=excluded.bank_data,credit_data=excluded.credit_data`,\n args: [\n account.id,\n account.itemId,\n account.marketingName || account.name || account.number || \"Conta\",\n account.type,\n account.subtype,\n account.currencyCode,\n now,\n account.number || account.bankData?.transferNumber || null,\n Number(account.balance) || 0,\n account.owner || null,\n account.taxNumber || null,\n JSON.stringify(account.bankData || null),\n JSON.stringify(account.creditData || null),\n ],\n })));\n await executeBatches(investments.map((investment: any) => ({\n sql:\n `INSERT INTO investments(id,item_id,name,number,type,subtype,balance,amount,currency_code,owner,status,institution_name,date,raw_details,updated_at,is_deleted) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0) ON CONFLICT(id) DO UPDATE SET item_id=excluded.item_id,name=excluded.name,number=excluded.number,type=excluded.type,subtype=excluded.subtype,balance=excluded.balance,amount=excluded.amount,currency_code=excluded.currency_code,owner=excluded.owner,status=excluded.status,institution_name=excluded.institution_name,date=excluded.date,raw_details=excluded.raw_details,updated_at=excluded.updated_at,is_deleted=0`,\n args: [\n investment.id,\n investment.itemId,\n investment.name || investment.code || \"Investimento\",\n investment.number || investment.code || null,\n investment.type,\n investment.subtype,\n Number(investment.balance) || 0,\n Number(investment.amount) || 0,\n investment.currencyCode || \"BRL\",\n investment.owner || null,\n investment.status || null,\n investment.institution?.name || investment.issuer || null,\n investment.date || null,\n JSON.stringify({\n code: investment.code,\n issuer: investment.issuer,\n quantity: investment.quantity,\n value: investment.value,\n amountProfit: investment.amountProfit,\n amountWithdrawal: investment.amountWithdrawal,\n amountOriginal: investment.amountOriginal,\n dueDate: investment.dueDate,\n rate: investment.rate,\n rateType: investment.rateType,\n fixedAnnualRate: investment.fixedAnnualRate,\n }),\n now,\n ],\n })));\n const warnings: string[] = [];\n if (!accounts.length) {\n warnings.push(\n \"Nenhuma conta ou subconta foi devolvida pelos Items configurados.\",\n );\n }\n if (!investments.length) {\n warnings.push(\n \"Nenhuma posição de investimento foi devolvida pelos Items configurados.\",\n );\n }\n const completeAccountIds = new Set();\n const accountGroups = await Promise.all(\n accounts.map(async (account: any) => {\n try {\n const rows = await getEveryTransaction(account, apiKey, dateFrom);\n completeAccountIds.add(account.id);\n return rows;\n } catch (error) {\n warnings.push(\n `Conta ${account.name || account.id}: ${\n error instanceof Error ? error.message : \"falha na coleta\"\n }`,\n );\n return [];\n }\n }),\n );\n const completeInvestmentIds = new Set();\n const investmentGroups = await Promise.all(\n investments.map(async (investment: any) => {\n try {\n const rows = await collectInvestmentMovements(\n investment,\n apiKey,\n dateFrom,\n );\n completeInvestmentIds.add(investment.id);\n return rows;\n } catch (error) {\n warnings.push(\n `Investimento ${investment.name || investment.id}: ${\n error instanceof Error ? error.message : \"falha na coleta\"\n }`,\n );\n return [];\n }\n }),\n );\n const incoming = [...accountGroups.flat(), ...investmentGroups.flat()];\n const existingResult = await sqlite.execute({\n sql:\n `SELECT id,content_hash,is_deleted,account_id AS accountId,investment_id AS investmentId,source_type AS sourceType,date,description,amount,category,status,type FROM transactions WHERE date >= ?`,\n args: [dateFrom],\n });\n const existing = new Map(\n (existingResult.rows as any[]).map((row) => [String(row.id), row]),\n );\n const seen = new Set();\n const statements: any[] = [];\n const changeStatements: any[] = [];\n const pastChanges: any[] = [];\n let newCount = 0, updatedCount = 0, unchangedCount = 0;\n for (const transaction of incoming) {\n const hash = await hashTransaction(transaction);\n const previous: any = existing.get(transaction.id);\n seen.add(transaction.id);\n if (!previous) newCount++;\n else if (\n previous.content_hash !== hash || Number(previous.is_deleted) === 1\n ) {\n updatedCount++;\n const changedFields: string[] = [];\n if (String(previous.date || \"\") !== String(transaction.date || \"\")) {\n changedFields.push(\"DATA\");\n }\n if (\n String(previous.description || \"\") !==\n String(transaction.description || \"\")\n ) changedFields.push(\"DESCRIÇÃO\");\n if (Number(previous.amount || 0) !== Number(transaction.amount || 0)) {\n changedFields.push(\"VALOR\");\n }\n if (\n String(previous.category || \"\") !== String(transaction.category || \"\")\n ) changedFields.push(\"CATEGORIA PLUGGY\");\n if (\n String(previous.status || \"\") !== String(transaction.status || \"\")\n ) changedFields.push(\"STATUS\");\n if (String(previous.type || \"\") !== String(transaction.type || \"\")) {\n changedFields.push(\"TIPO\");\n }\n if (!changedFields.length) changedFields.push(\"OUTROS DADOS DA PLUGGY\");\n const change = {\n transactionId: transaction.id,\n date: transaction.date,\n description: transaction.description || \"Sem descrição\",\n changedFields,\n before: {\n date: previous.date,\n description: previous.description,\n amount: Number(previous.amount || 0),\n category: previous.category,\n status: previous.status,\n type: previous.type,\n },\n after: {\n date: transaction.date,\n description: transaction.description,\n amount: Number(transaction.amount || 0),\n category: transaction.category,\n status: transaction.status,\n type: transaction.type,\n },\n };\n pastChanges.push(change);\n changeStatements.push({\n sql:\n `INSERT INTO transaction_change_log(id,sync_id,transaction_id,changed_at,changed_fields,date_before,date_after,description_before,description_after,amount_before,amount_after,category_before,category_after,status_before,status_after) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,\n args: [\n crypto.randomUUID(),\n syncId,\n transaction.id,\n now,\n JSON.stringify(changedFields),\n previous.date,\n transaction.date,\n previous.description,\n transaction.description,\n Number(previous.amount || 0),\n Number(transaction.amount || 0),\n previous.category,\n transaction.category,\n previous.status,\n transaction.status,\n ],\n });\n } else unchangedCount++;\n statements.push({\n sql:\n `INSERT INTO transactions(id,account_id,item_id,date,description,description_raw,category,category_id,amount,currency_code,type,status,content_hash,source_type,investment_id,raw_details,is_deleted,first_seen_at,updated_at,last_seen_sync) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0,?,?,?) ON CONFLICT(id) DO UPDATE SET account_id=excluded.account_id,item_id=excluded.item_id,date=excluded.date,description=excluded.description,description_raw=excluded.description_raw,category=excluded.category,category_id=excluded.category_id,amount=excluded.amount,currency_code=excluded.currency_code,type=excluded.type,status=excluded.status,content_hash=excluded.content_hash,source_type=excluded.source_type,investment_id=excluded.investment_id,raw_details=excluded.raw_details,is_deleted=0,updated_at=CASE WHEN transactions.content_hash<>excluded.content_hash OR transactions.is_deleted=1 THEN excluded.updated_at ELSE transactions.updated_at END,last_seen_sync=excluded.last_seen_sync`,\n args: [\n transaction.id,\n transaction.accountId,\n transaction.itemId,\n transaction.date,\n transaction.description || null,\n transaction.descriptionRaw || null,\n transaction.category || null,\n transaction.categoryId || null,\n Number(transaction.amount) || 0,\n transaction.currencyCode || \"BRL\",\n transaction.type || null,\n transaction.status || null,\n hash,\n transaction.sourceType || \"account\",\n transaction.investmentId || null,\n JSON.stringify(transaction.rawDetails || null),\n now,\n now,\n syncId,\n ],\n });\n }\n await executeBatches(statements);\n await executeBatches(changeStatements);\n // Sincronização acumulativa: novos registros são inseridos e registros\n // existentes são atualizados, mas ausências na resposta nunca apagam dados.\n const removedCount = 0;\n await sqlite.execute({\n sql:\n `UPDATE sync_runs SET finished_at=?,new_count=?,updated_count=?,unchanged_count=?,removed_count=?,status='SUCCESS' WHERE id=?`,\n args: [now, newCount, updatedCount, unchangedCount, removedCount, syncId],\n });\n return {\n syncId,\n dateFrom,\n newCount,\n updatedCount,\n unchangedCount,\n removedCount,\n pastChangeCount: pastChanges.length,\n pastChanges: pastChanges.slice(0, 50),\n totalReceived: incoming.length,\n accountCount: accounts.length,\n investmentCount: investments.length,\n investmentTransactionCount: investmentGroups.flat().length,\n warnings,\n complete: warnings.length === 0,\n };\n } catch (error) {\n await sqlite.execute({\n sql:\n `UPDATE sync_runs SET finished_at=?,status='ERROR',error=? WHERE id=?`,\n args: [\n new Date().toISOString(),\n error instanceof Error ? error.message : \"Erro inesperado\",\n syncId,\n ],\n });\n throw error;\n }\n}\n\nasync function listTransactions(url: URL) {\n const dateFrom = url.searchParams.get(\"dateFrom\") || null;\n const result = await sqlite.execute({\n sql:\n `SELECT t.id,t.account_id AS accountId,t.investment_id AS investmentId,t.item_id AS itemId,COALESCE(t.source_type,'account') AS sourceType,t.date,t.description,t.description_raw AS descriptionRaw,COALESCE(t.category,'Não classificado') AS category,c2.id AS category2Id,c2.name AS category2,c2.code AS category2Code,g2.id AS category2GroupId,g2.name AS category2Group,g2.nature AS category2Nature,c2.is_reducer AS category2Reducer,COALESCE(t.reviewed,0) AS reviewed,t.reviewed_at AS reviewedAt,COALESCE(t.manual_note,'') AS manualNote,t.manual_note_updated_at AS manualNoteUpdatedAt,t.raw_details AS rawDetails,t.amount,t.currency_code AS currencyCode,t.type,t.status,COALESCE(a.name,i.name,'Origem não identificada') AS accountName,COALESCE(wa.name,wi.name,'Não classificada') AS walletName,COALESCE(wa.id,wi.id) AS walletId,t.updated_at AS updatedAt FROM transactions t LEFT JOIN accounts a ON a.id=t.account_id AND COALESCE(t.source_type,'account')='account' LEFT JOIN investments i ON i.id=COALESCE(t.investment_id,CASE WHEN t.source_type='investment' THEN t.account_id END) LEFT JOIN account_wallet_mapping ma ON ma.account_id=a.id LEFT JOIN wallets wa ON wa.id=ma.wallet_id LEFT JOIN investment_wallet_mapping mi ON mi.investment_id=i.id LEFT JOIN wallets wi ON wi.id=mi.wallet_id LEFT JOIN transaction_category2 tc2 ON tc2.transaction_id=t.id LEFT JOIN category2_items c2 ON c2.id=tc2.category_id LEFT JOIN category2_groups g2 ON g2.id=c2.group_id WHERE (? IS NULL OR t.date>=?) ORDER BY t.date DESC`,\n args: [dateFrom, dateFrom],\n });\n return (result.rows as any[]).map((row) => {\n try {\n row.rawDetails = JSON.parse(row.rawDetails || \"null\");\n } catch {\n row.rawDetails = null;\n }\n return row;\n });\n}\n\nasync function listWalletData() {\n const groups =\n (await sqlite.execute(\n `SELECT id,name,sort_order AS sortOrder,created_at AS createdAt FROM wallet_groups ORDER BY sort_order,name`,\n )).rows;\n const wallets =\n (await sqlite.execute(\n `SELECT w.id,w.name,w.created_at AS createdAt,w.sort_order AS sortOrder,w.group_id AS groupId,g.name AS groupName,COALESCE(w.opening_balance,0) AS openingBalance,w.opening_date AS openingDate FROM wallets w LEFT JOIN wallet_groups g ON g.id=w.group_id ORDER BY COALESCE(g.sort_order,999999),w.sort_order,w.name`,\n )).rows;\n const items =\n (await sqlite.execute(\n `SELECT id,connector_id AS connectorId,bank_name AS bankName,bank_cnpj AS bankCnpj,status,products FROM items ORDER BY bank_name,id`,\n )).rows as any[];\n const accounts =\n (await sqlite.execute(\n `SELECT a.id,a.item_id AS itemId,a.name,a.number,a.type,a.subtype,a.balance,a.currency_code AS currencyCode,a.owner,a.tax_number AS taxNumber,a.bank_data AS bankData,a.credit_data AS creditData,m.wallet_id AS walletId,COALESCE(w.name,'Não classificada') AS walletName FROM accounts a LEFT JOIN account_wallet_mapping m ON m.account_id=a.id LEFT JOIN wallets w ON w.id=m.wallet_id ORDER BY a.name`,\n )).rows as any[];\n const investments =\n (await sqlite.execute(\n `SELECT i.id,i.item_id AS itemId,i.name,i.number,i.type,i.subtype,i.balance,i.amount,i.currency_code AS currencyCode,i.owner,i.status,i.institution_name AS institutionName,i.date,i.raw_details AS rawDetails,m.wallet_id AS walletId,COALESCE(w.name,'Não classificada') AS walletName FROM investments i LEFT JOIN investment_wallet_mapping m ON m.investment_id=i.id LEFT JOIN wallets w ON w.id=m.wallet_id ORDER BY i.name`,\n )).rows;\n for (const account of accounts) {\n try {\n account.bankData = JSON.parse(account.bankData || \"null\");\n } catch {\n account.bankData = null;\n }\n try {\n account.creditData = JSON.parse(account.creditData || \"null\");\n } catch {\n account.creditData = null;\n }\n }\n for (const item of items) {\n try {\n item.products = JSON.parse(item.products || \"[]\");\n } catch {\n item.products = [];\n }\n }\n for (const investment of investments as any[]) {\n try {\n investment.rawDetails = JSON.parse(investment.rawDetails || \"null\");\n } catch {\n investment.rawDetails = null;\n }\n }\n return { groups, wallets, items, accounts, investments };\n}\n\nasync function listCategory2() {\n const groups =\n (await sqlite.execute(\n `SELECT g.id,g.nature,g.code,g.name,g.sort_order AS sortOrder,COUNT(i.id) AS categoryCount FROM category2_groups g LEFT JOIN category2_items i ON i.group_id=g.id GROUP BY g.id ORDER BY CASE g.nature WHEN 'RECEITA' THEN 1 WHEN 'DESPESA' THEN 2 ELSE 3 END,g.sort_order,g.code`,\n )).rows;\n const categories =\n (await sqlite.execute(\n `SELECT i.id,i.group_id AS groupId,i.nature,i.code,i.name,i.is_reducer AS isReducer,i.sort_order AS sortOrder,COUNT(tc.transaction_id) AS transactionCount FROM category2_items i LEFT JOIN transaction_category2 tc ON tc.category_id=i.id GROUP BY i.id ORDER BY i.group_id,i.sort_order,i.code`,\n )).rows;\n return { groups, categories };\n}\n\n// Categorias que a Pluggy já devolveu, com quantos lançamentos cada uma tem, quantos ainda estão\n// sem Categoria 2, e para onde a categoria está mapeada. É a base da classificação automática.\nasync function listCategoryMap() {\n const rows =\n (await sqlite.execute(\n `SELECT COALESCE(t.category,'Não classificado') AS pluggyCategory,COUNT(*) AS total,SUM(CASE WHEN tc.transaction_id IS NULL THEN 1 ELSE 0 END) AS pending,m.category_id AS categoryId,c.code AS categoryCode,c.name AS categoryName,g.name AS categoryGroup,g.nature AS categoryNature FROM transactions t LEFT JOIN transaction_category2 tc ON tc.transaction_id=t.id LEFT JOIN pluggy_category_map m ON m.pluggy_category=COALESCE(t.category,'Não classificado') LEFT JOIN category2_items c ON c.id=m.category_id LEFT JOIN category2_groups g ON g.id=c.group_id GROUP BY pluggyCategory,m.category_id,c.code,c.name,g.name,g.nature ORDER BY pending DESC,total DESC`,\n )).rows;\n return { categories: rows };\n}\n\n// Aplica os mapeamentos SOMENTE onde ainda não existe Categoria 2. Nunca sobrescreve classificação\n// feita à mão e NUNCA toca em reviewed: preencher categoria não é o mesmo que conferir o lançamento.\nasync function applyCategoryMap() {\n const result = await sqlite.execute({\n sql:\n `INSERT INTO transaction_category2(transaction_id,category_id,updated_at) SELECT t.id,m.category_id,? FROM transactions t JOIN pluggy_category_map m ON m.pluggy_category=COALESCE(t.category,'Não classificado') LEFT JOIN transaction_category2 tc ON tc.transaction_id=t.id WHERE tc.transaction_id IS NULL`,\n args: [new Date().toISOString()],\n });\n return { classified: Number(result.rowsAffected) || 0 };\n}\n\nconst CATEGORY2_NATURES = new Set([\"RECEITA\", \"DESPESA\", \"MOVIMENTACAO\"]);\nfunction upperText(value: unknown) {\n return String(value || \"\").trim().toLocaleUpperCase(\"pt-BR\");\n}\nfunction validCategory2Nature(value: unknown) {\n const nature = upperText(value).replace(\"Ç\", \"C\").replace(\"Ã\", \"A\");\n return CATEGORY2_NATURES.has(nature) ? nature : \"\";\n}\n\nasync function requireCategory2Group(groupId: string) {\n const result = await sqlite.execute({\n sql: `SELECT id,nature FROM category2_groups WHERE id=?`,\n args: [groupId],\n });\n return result.rows[0] as any;\n}\n\nasync function readBody(request: Request) {\n return await request.json().catch(() => ({}));\n}\n\nexport default async function handler(request: Request): Promise {\n if (request.method === \"OPTIONS\") {\n return new Response(null, { status: 204, headers: corsHeaders });\n }\n const url = new URL(request.url);\n if (\n request.method === \"GET\" &&\n (url.pathname === \"/\" || url.pathname === \"/health\")\n ) return json({ ok: true, service: \"financas-pluggy-db\" });\n try {\n const receivedToken = request.headers.get(\"Authorization\")?.replace(\n /^Bearer\\s+/i,\n \"\",\n );\n if (!receivedToken || receivedToken !== requiredEnv(\"APP_TOKEN\")) {\n return json({ error: \"APP_TOKEN inválido ou ausente.\" }, 401);\n }\n await ensureSchema();\n if (request.method === \"GET\" && url.pathname === \"/transactions\") {\n const transactions = await listTransactions(url);\n return json({\n transactions,\n meta: { transactionCount: transactions.length, source: \"database\" },\n });\n }\n if (request.method === \"POST\" && url.pathname === \"/sync\") {\n const body = await readBody(request);\n if (!/^\\d{4}-\\d{2}$/.test(body.startMonth || \"\")) {\n return json({ error: \"Escolha o mês e o ano iniciais.\" }, 400);\n }\n const dateFrom = `${body.startMonth}-01`;\n if (dateFrom > new Date().toISOString().slice(0, 10)) {\n return json({ error: \"A data inicial não pode estar no futuro.\" }, 400);\n }\n return json(await syncTransactions(dateFrom));\n }\n if (request.method === \"GET\" && url.pathname === \"/wallets\") {\n return json(await listWalletData());\n }\n if (request.method === \"POST\" && url.pathname === \"/wallet-groups\") {\n const body = await readBody(request);\n const name = String(body.name || \"\").trim();\n if (!name) return json({ error: \"Informe o nome do grupo.\" }, 400);\n const id = crypto.randomUUID();\n await sqlite.execute({\n sql:\n `INSERT INTO wallet_groups(id,name,sort_order,created_at) VALUES(?,?,(SELECT COALESCE(MAX(sort_order),-1)+1 FROM wallet_groups),?)`,\n args: [id, name, new Date().toISOString()],\n });\n return json({ id, name }, 201);\n }\n const walletGroupPath = url.pathname.match(\n /^\\/wallet-groups\\/([0-9a-f-]+)$/i,\n );\n if (request.method === \"PUT\" && walletGroupPath) {\n const body = await readBody(request);\n const name = String(body.name || \"\").trim();\n if (!name) return json({ error: \"Informe o novo nome do grupo.\" }, 400);\n const result = await sqlite.execute({\n sql: `UPDATE wallet_groups SET name=? WHERE id=?`,\n args: [name, walletGroupPath[1]],\n });\n if (!result.rowsAffected) {\n return json({ error: \"Grupo não encontrado.\" }, 404);\n }\n return json({ ok: true });\n }\n if (request.method === \"DELETE\" && walletGroupPath) {\n await sqlite.batch([\n {\n sql: `UPDATE wallets SET group_id=NULL WHERE group_id=?`,\n args: [walletGroupPath[1]],\n },\n {\n sql: `DELETE FROM wallet_groups WHERE id=?`,\n args: [walletGroupPath[1]],\n },\n ]);\n return json({ ok: true });\n }\n if (request.method === \"PUT\" && url.pathname === \"/wallet-group-mapping\") {\n const body = await readBody(request);\n const walletId = String(body.walletId || \"\"),\n groupId = String(body.groupId || \"\");\n const wallet = await sqlite.execute({\n sql: `SELECT id FROM wallets WHERE id=?`,\n args: [walletId],\n });\n if (!wallet.rows.length) {\n return json({ error: \"Carteira não encontrada.\" }, 404);\n }\n if (groupId) {\n const group = await sqlite.execute({\n sql: `SELECT id FROM wallet_groups WHERE id=?`,\n args: [groupId],\n });\n if (!group.rows.length) {\n return json({ error: \"Grupo não encontrado.\" }, 404);\n }\n }\n await sqlite.execute({\n sql: `UPDATE wallets SET group_id=? WHERE id=?`,\n args: [groupId || null, walletId],\n });\n return json({ ok: true });\n }\n if (request.method === \"PUT\" && url.pathname === \"/wallet-opening\") {\n const body = await readBody(request);\n const walletId = String(body.walletId || \"\");\n const openingBalance = Number(body.openingBalance);\n const openingDate = String(body.openingDate || \"\").trim();\n if (!Number.isFinite(openingBalance)) {\n return json({ error: \"Saldo inicial inválido.\" }, 400);\n }\n if (openingDate && !/^\\d{4}-\\d{2}-\\d{2}$/.test(openingDate)) {\n return json({\n error: \"A data do saldo inicial deve estar no formato AAAA-MM-DD.\",\n }, 400);\n }\n const wallet = await sqlite.execute({\n sql: `SELECT id FROM wallets WHERE id=?`,\n args: [walletId],\n });\n if (!wallet.rows.length) {\n return json({ error: \"Carteira não encontrada.\" }, 404);\n }\n await sqlite.execute({\n sql: `UPDATE wallets SET opening_balance=?,opening_date=? WHERE id=?`,\n args: [openingBalance, openingDate || null, walletId],\n });\n return json({ ok: true });\n }\n if (request.method === \"GET\" && url.pathname === \"/category2\") {\n return json(await listCategory2());\n }\n if (request.method === \"POST\" && url.pathname === \"/category2/groups\") {\n const body = await readBody(request);\n const nature = validCategory2Nature(body.nature),\n code = upperText(body.code),\n name = upperText(body.name);\n if (!nature || !code || !name) {\n return json({\n error: \"NATUREZA, CÓDIGO E NOME DO GRUPO SÃO OBRIGATÓRIOS.\",\n }, 400);\n }\n const id = crypto.randomUUID();\n await sqlite.execute({\n sql:\n `INSERT INTO category2_groups(id,nature,code,name,sort_order,created_at) VALUES(?,?,?,?,(SELECT COALESCE(MAX(sort_order),-1)+1 FROM category2_groups WHERE nature=?),?)`,\n args: [id, nature, code, name, nature, new Date().toISOString()],\n });\n return json({ id }, 201);\n }\n const category2GroupPath = url.pathname.match(\n /^\\/category2\\/groups\\/([0-9a-z.-]+)$/i,\n );\n if (request.method === \"PUT\" && category2GroupPath) {\n const body = await readBody(request);\n const nature = validCategory2Nature(body.nature),\n code = upperText(body.code),\n name = upperText(body.name);\n if (!nature || !code || !name) {\n return json({\n error: \"NATUREZA, CÓDIGO E NOME DO GRUPO SÃO OBRIGATÓRIOS.\",\n }, 400);\n }\n const id = category2GroupPath[1];\n const result = await sqlite.execute({\n sql: `UPDATE category2_groups SET nature=?,code=?,name=? WHERE id=?`,\n args: [nature, code, name, id],\n });\n if (!result.rowsAffected) {\n return json({ error: \"GRUPO NÃO ENCONTRADO.\" }, 404);\n }\n await sqlite.execute({\n sql: `UPDATE category2_items SET nature=? WHERE group_id=?`,\n args: [nature, id],\n });\n return json({ ok: true });\n }\n if (request.method === \"DELETE\" && category2GroupPath) {\n const id = category2GroupPath[1];\n const children = await sqlite.execute({\n sql: `SELECT COUNT(*) AS total FROM category2_items WHERE group_id=?`,\n args: [id],\n });\n if (Number((children.rows[0] as any)?.total) > 0) {\n return json({\n error: \"MOVA OU EXCLUA AS CATEGORIAS DESTE GRUPO PRIMEIRO.\",\n }, 409);\n }\n const result = await sqlite.execute({\n sql: `DELETE FROM category2_groups WHERE id=?`,\n args: [id],\n });\n if (!result.rowsAffected) {\n return json({ error: \"GRUPO NÃO ENCONTRADO.\" }, 404);\n }\n return json({ ok: true });\n }\n if (request.method === \"POST\" && url.pathname === \"/category2/items\") {\n const body = await readBody(request);\n const groupId = String(body.groupId || \"\"),\n group = await requireCategory2Group(groupId);\n const code = upperText(body.code), name = upperText(body.name);\n if (!group || !code || !name) {\n return json({\n error: \"GRUPO, CÓDIGO E NOME DA CATEGORIA SÃO OBRIGATÓRIOS.\",\n }, 400);\n }\n const id = crypto.randomUUID();\n await sqlite.execute({\n sql:\n `INSERT INTO category2_items(id,group_id,nature,code,name,is_reducer,sort_order,created_at) VALUES(?,?,?,?,?,?,(SELECT COALESCE(MAX(sort_order),-1)+1 FROM category2_items WHERE group_id=?),?)`,\n args: [\n id,\n groupId,\n group.nature,\n code,\n name,\n body.isReducer ? 1 : 0,\n groupId,\n new Date().toISOString(),\n ],\n });\n return json({ id }, 201);\n }\n const category2ItemPath = url.pathname.match(\n /^\\/category2\\/items\\/([0-9a-z.-]+)$/i,\n );\n if (request.method === \"PUT\" && category2ItemPath) {\n const body = await readBody(request);\n const groupId = String(body.groupId || \"\"),\n group = await requireCategory2Group(groupId);\n const code = upperText(body.code), name = upperText(body.name);\n if (!group || !code || !name) {\n return json({\n error: \"GRUPO, CÓDIGO E NOME DA CATEGORIA SÃO OBRIGATÓRIOS.\",\n }, 400);\n }\n const result = await sqlite.execute({\n sql:\n `UPDATE category2_items SET group_id=?,nature=?,code=?,name=?,is_reducer=? WHERE id=?`,\n args: [\n groupId,\n group.nature,\n code,\n name,\n body.isReducer ? 1 : 0,\n category2ItemPath[1],\n ],\n });\n if (!result.rowsAffected) {\n return json({ error: \"CATEGORIA NÃO ENCONTRADA.\" }, 404);\n }\n return json({ ok: true });\n }\n if (request.method === \"DELETE\" && category2ItemPath) {\n const id = category2ItemPath[1];\n const linked = await sqlite.execute({\n sql:\n `SELECT COUNT(*) AS total FROM transaction_category2 WHERE category_id=?`,\n args: [id],\n });\n if (Number((linked.rows[0] as any)?.total) > 0) {\n return json({\n error:\n \"ESTA CATEGORIA POSSUI LANÇAMENTOS VINCULADOS E NÃO PODE SER EXCLUÍDA.\",\n }, 409);\n }\n const result = await sqlite.execute({\n sql: `DELETE FROM category2_items WHERE id=?`,\n args: [id],\n });\n if (!result.rowsAffected) {\n return json({ error: \"CATEGORIA NÃO ENCONTRADA.\" }, 404);\n }\n return json({ ok: true });\n }\n if (request.method === \"PUT\" && url.pathname === \"/category2/reorder\") {\n const body = await readBody(request);\n const groups = Array.isArray(body.groups) ? body.groups : [],\n categories = Array.isArray(body.categories) ? body.categories : [];\n const storedGroups =\n (await sqlite.execute(`SELECT id,nature FROM category2_groups`))\n .rows as any[];\n const storedCategories =\n (await sqlite.execute(`SELECT id FROM category2_items`)).rows as any[];\n const groupMap = new Map(\n storedGroups.map((row) => [String(row.id), String(row.nature)]),\n );\n const groupIds = groups.map((row: any) => String(row.id)),\n categoryIds = categories.map((row: any) => String(row.id));\n if (\n new Set(groupIds).size !== storedGroups.length ||\n groupIds.some((id: string) => !groupMap.has(id))\n ) return json({ error: \"ORDEM DE GRUPOS INCOMPLETA OU INVÁLIDA.\" }, 400);\n const storedCategoryIds = new Set(\n storedCategories.map((row) => String(row.id)),\n );\n if (\n new Set(categoryIds).size !== storedCategories.length ||\n categoryIds.some((id: string) => !storedCategoryIds.has(id))\n ) {\n return json(\n { error: \"ORDEM DE CATEGORIAS INCOMPLETA OU INVÁLIDA.\" },\n 400,\n );\n }\n if (categories.some((row: any) => !groupMap.has(String(row.groupId)))) {\n return json({\n error: \"UMA CATEGORIA FOI MOVIDA PARA UM GRUPO INVÁLIDO.\",\n }, 400);\n }\n const statements = [\n ...groups.map((row: any, index: number) => ({\n sql: `UPDATE category2_groups SET sort_order=? WHERE id=?`,\n args: [index, String(row.id)],\n })),\n ...categories.map((row: any, index: number) => ({\n sql:\n `UPDATE category2_items SET group_id=?,nature=?,code=?,sort_order=? WHERE id=?`,\n args: [\n String(row.groupId),\n groupMap.get(String(row.groupId)),\n upperText(row.code),\n Number(row.sortOrder ?? index),\n String(row.id),\n ],\n })),\n ];\n await executeBatches(statements);\n return json({ ok: true });\n }\n if (request.method === \"PUT\" && url.pathname === \"/transaction-category2\") {\n const body = await readBody(request);\n const transactionId = String(body.transactionId || \"\"),\n categoryId = String(body.categoryId || \"\");\n const transaction = await sqlite.execute({\n sql: `SELECT id FROM transactions WHERE id=?`,\n args: [transactionId],\n });\n if (!transaction.rows.length) {\n return json({ error: \"LANÇAMENTO NÃO ENCONTRADO.\" }, 404);\n }\n if (!categoryId) {\n await sqlite.execute({\n sql: `DELETE FROM transaction_category2 WHERE transaction_id=?`,\n args: [transactionId],\n });\n } else {\n const category = await sqlite.execute({\n sql: `SELECT id FROM category2_items WHERE id=?`,\n args: [categoryId],\n });\n if (!category.rows.length) {\n return json({ error: \"CATEGORIA 2 NÃO ENCONTRADA.\" }, 404);\n }\n await sqlite.execute({\n sql:\n `INSERT INTO transaction_category2(transaction_id,category_id,updated_at) VALUES(?,?,?) ON CONFLICT(transaction_id) DO UPDATE SET category_id=excluded.category_id,updated_at=excluded.updated_at`,\n args: [transactionId, categoryId, new Date().toISOString()],\n });\n }\n return json({ ok: true });\n }\n if (\n request.method === \"PUT\" && url.pathname === \"/transaction-category2/bulk\"\n ) {\n const body = await readBody(request);\n const ids = Array.isArray(body.transactionIds)\n ? [...new Set(body.transactionIds.map(String).filter(Boolean))]\n : [];\n const categoryId = String(body.categoryId || \"\");\n if (!ids.length) {\n return json({ error: \"NENHUM LANÇAMENTO INFORMADO.\" }, 400);\n }\n if (ids.length > 1000) {\n return json({ error: \"NO MÁXIMO 1.000 LANÇAMENTOS POR VEZ.\" }, 400);\n }\n if (categoryId) {\n const category = await sqlite.execute({\n sql: `SELECT id FROM category2_items WHERE id=?`,\n args: [categoryId],\n });\n if (!category.rows.length) {\n return json({ error: \"CATEGORIA 2 NÃO ENCONTRADA.\" }, 404);\n }\n }\n const now = new Date().toISOString();\n // O INSERT...SELECT ignora id inexistente. reviewed fica intocado de propósito:\n // classificar em lote não é conferir em lote.\n await executeBatches(ids.map((id) =>\n categoryId\n ? {\n sql:\n `INSERT INTO transaction_category2(transaction_id,category_id,updated_at) SELECT id,?,? FROM transactions WHERE id=? ON CONFLICT(transaction_id) DO UPDATE SET category_id=excluded.category_id,updated_at=excluded.updated_at`,\n args: [categoryId, now, id],\n }\n : {\n sql: `DELETE FROM transaction_category2 WHERE transaction_id=?`,\n args: [id],\n }\n ));\n return json({ ok: true, count: ids.length });\n }\n if (request.method === \"GET\" && url.pathname === \"/category-map\") {\n return json(await listCategoryMap());\n }\n if (request.method === \"POST\" && url.pathname === \"/category-map/apply\") {\n return json(await applyCategoryMap());\n }\n if (request.method === \"PUT\" && url.pathname === \"/category-map\") {\n const body = await readBody(request);\n const pluggyCategory = String(body.pluggyCategory || \"\").trim();\n const categoryId = String(body.categoryId || \"\");\n if (!pluggyCategory) {\n return json({ error: \"INFORME A CATEGORIA DA PLUGGY.\" }, 400);\n }\n if (!categoryId) {\n await sqlite.execute({\n sql: `DELETE FROM pluggy_category_map WHERE pluggy_category=?`,\n args: [pluggyCategory],\n });\n return json({ ok: true, removed: true });\n }\n const category = await sqlite.execute({\n sql: `SELECT id FROM category2_items WHERE id=?`,\n args: [categoryId],\n });\n if (!category.rows.length) {\n return json({ error: \"CATEGORIA 2 NÃO ENCONTRADA.\" }, 404);\n }\n await sqlite.execute({\n sql:\n `INSERT INTO pluggy_category_map(pluggy_category,category_id,updated_at) VALUES(?,?,?) ON CONFLICT(pluggy_category) DO UPDATE SET category_id=excluded.category_id,updated_at=excluded.updated_at`,\n args: [pluggyCategory, categoryId, new Date().toISOString()],\n });\n return json({ ok: true });\n }\n if (request.method === \"PUT\" && url.pathname === \"/transaction-reviewed\") {\n const body = await readBody(request);\n const transactionId = String(body.transactionId || \"\");\n const reviewed = body.reviewed === true || body.reviewed === 1;\n const transaction = await sqlite.execute({\n sql: `SELECT id FROM transactions WHERE id=?`,\n args: [transactionId],\n });\n if (!transaction.rows.length) {\n return json({ error: \"LANÇAMENTO NÃO ENCONTRADO.\" }, 404);\n }\n await sqlite.execute({\n sql: `UPDATE transactions SET reviewed=?,reviewed_at=? WHERE id=?`,\n args: [\n reviewed ? 1 : 0,\n reviewed ? new Date().toISOString() : null,\n transactionId,\n ],\n });\n return json({ ok: true, reviewed });\n }\n if (request.method === \"PUT\" && url.pathname === \"/transaction-note\") {\n const body = await readBody(request);\n const transactionId = String(body.transactionId || \"\");\n const manualNote = String(body.manualNote || \"\").trim();\n if (manualNote.length > 1000) {\n return json({\n error: \"A observação pode ter no máximo 1.000 caracteres.\",\n }, 400);\n }\n const transaction = await sqlite.execute({\n sql: `SELECT id FROM transactions WHERE id=?`,\n args: [transactionId],\n });\n if (!transaction.rows.length) {\n return json({ error: \"LANÇAMENTO NÃO ENCONTRADO.\" }, 404);\n }\n await sqlite.execute({\n sql:\n `UPDATE transactions SET manual_note=?,manual_note_updated_at=? WHERE id=?`,\n args: [manualNote || null, new Date().toISOString(), transactionId],\n });\n return json({ ok: true, manualNote });\n }\n if (request.method === \"POST\" && url.pathname === \"/wallets\") {\n const body = await readBody(request);\n const name = String(body.name || \"\").trim();\n if (!name) return json({ error: \"Informe o nome da carteira.\" }, 400);\n const id = crypto.randomUUID();\n await sqlite.execute({\n sql:\n `INSERT INTO wallets(id,name,created_at,sort_order) VALUES(?,?,?,(SELECT COALESCE(MAX(sort_order),0)+1 FROM wallets))`,\n args: [id, name, new Date().toISOString()],\n });\n return json({ id, name }, 201);\n }\n if (request.method === \"PUT\" && url.pathname === \"/wallets/reorder\") {\n const body = await readBody(request);\n const walletIds = Array.isArray(body.walletIds)\n ? body.walletIds.map(String)\n : [];\n if (!walletIds.length) {\n return json({ error: \"Nenhuma carteira informada para ordenar.\" }, 400);\n }\n if (new Set(walletIds).size !== walletIds.length) {\n return json(\n { error: \"A lista de carteiras contém IDs repetidos.\" },\n 400,\n );\n }\n const storedWallets = await sqlite.execute(`SELECT id FROM wallets`);\n const storedIds = new Set(\n (storedWallets.rows as any[]).map((row) => String(row.id)),\n );\n if (\n storedIds.size !== walletIds.length ||\n walletIds.some((id) => !storedIds.has(id))\n ) {\n return json({\n error: \"A lista de carteiras está incompleta ou inválida.\",\n }, 400);\n }\n await sqlite.batch(\n walletIds.map((id, index) => ({\n sql: `UPDATE wallets SET sort_order=? WHERE id=?`,\n args: [index, id],\n })),\n );\n return json({ ok: true });\n }\n const walletPath = url.pathname.match(/^\\/wallets\\/([0-9a-f-]+)$/i);\n if (request.method === \"PUT\" && walletPath) {\n const body = await readBody(request);\n const name = String(body.name || \"\").trim();\n if (!name) {\n return json({ error: \"Informe o novo nome da carteira.\" }, 400);\n }\n const result = await sqlite.execute({\n sql: `UPDATE wallets SET name=? WHERE id=?`,\n args: [name, walletPath[1]],\n });\n if (!result.rowsAffected) {\n return json({ error: \"Carteira não encontrada.\" }, 404);\n }\n return json({ ok: true });\n }\n if (request.method === \"DELETE\" && walletPath) {\n await sqlite.batch([\n {\n sql: `DELETE FROM account_wallet_mapping WHERE wallet_id=?`,\n args: [walletPath[1]],\n },\n {\n sql: `DELETE FROM investment_wallet_mapping WHERE wallet_id=?`,\n args: [walletPath[1]],\n },\n { sql: `DELETE FROM wallets WHERE id=?`, args: [walletPath[1]] },\n ]);\n return json({ ok: true });\n }\n if (request.method === \"PUT\" && url.pathname === \"/wallet-mapping\") {\n const body = await readBody(request);\n const accountId = String(body.accountId || \"\");\n const walletId = String(body.walletId || \"\");\n if (!accountId) return json({ error: \"Conta inválida.\" }, 400);\n const entityType = body.entityType === \"investment\"\n ? \"investment\"\n : \"account\";\n const table = entityType === \"investment\"\n ? \"investment_wallet_mapping\"\n : \"account_wallet_mapping\";\n const column = entityType === \"investment\"\n ? \"investment_id\"\n : \"account_id\";\n if (!walletId) {\n await sqlite.execute({\n sql: `DELETE FROM ${table} WHERE ${column}=?`,\n args: [accountId],\n });\n } else {await sqlite.execute({\n sql:\n `INSERT INTO ${table}(${column},wallet_id,updated_at) VALUES(?,?,?) ON CONFLICT(${column}) DO UPDATE SET wallet_id=excluded.wallet_id,updated_at=excluded.updated_at`,\n args: [accountId, walletId, new Date().toISOString()],\n });}\n return json({ ok: true });\n }\n return json({ error: \"Rota não encontrada.\" }, 404);\n } catch (error) {\n console.error(error);\n const message = error instanceof Error\n ? error.message\n : \"Erro inesperado no backend.\";\n return json(\n { error: message },\n /UNIQUE constraint failed/i.test(message) ? 409 : 500,\n );\n }\n}","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\n// Marcador de versao: aparece em GET / e GET /health, SEM precisar de token.\n// Serve para saber, de fora, qual versao esta realmente publicada no Val Town.\nconst BACKEND_VERSION = \"20260831-20\";\nconst BACKEND_FEATURES = [\"category-map\", \"wallet-opening\", \"transfers\"];\nconst PLUGGY_BASE_URL = \"https://api.pluggy.ai\";\nconst corsHeaders = {\n \"Access-Control-Allow-Origin\": \"*\",\n \"Access-Control-Allow-Headers\": \"Authorization, Content-Type\",\n \"Access-Control-Allow-Methods\": \"GET, POST, PUT, DELETE, OPTIONS\",\n \"Content-Type\": \"application/json; charset=utf-8\",\n};\n\nfunction json(data: unknown, status = 200) {\n return new Response(JSON.stringify(data), { status, headers: corsHeaders });\n}\n\nfunction requiredEnv(name: string): string {\n const value = Deno.env.get(name)?.trim();\n if (!value) throw new Error(`Variável ${name} não configurada no Val Town.`);\n return value;\n}\n\nfunction validateItemIds(values: string[]): string[] {\n const ids = values.map((id) => id.trim().replace(/^[\"']|[\"']$/g, \"\")).filter(\n Boolean,\n );\n const uuid =\n /^[0-9a-f]{8}-[0-9a-f]{4}-[1-8][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i;\n if (ids.some((id) => !uuid.test(id))) {\n throw new Error(\n \"PLUGGY_ITEM_IDS contém valor inválido. Use somente UUIDs completos.\",\n );\n }\n return [...new Set(ids)];\n}\n\nfunction parseItemIds(raw: string): string[] {\n if (raw.startsWith(\"[\")) {\n const parsed = JSON.parse(raw);\n if (!Array.isArray(parsed)) {\n throw new Error(\n \"PLUGGY_ITEM_IDS deve ser uma lista ou IDs separados por vírgula.\",\n );\n }\n return validateItemIds(parsed.map(String));\n }\n return validateItemIds(raw.split(/[\\s,;]+/));\n}\n\nasync function ensureSchema() {\n await sqlite.batch([\n `CREATE TABLE IF NOT EXISTS items (id TEXT PRIMARY KEY,connector_id TEXT,bank_name TEXT NOT NULL,status TEXT,updated_at TEXT NOT NULL)`,\n `CREATE TABLE IF NOT EXISTS accounts (id TEXT PRIMARY KEY,item_id TEXT NOT NULL,name TEXT NOT NULL,type TEXT,subtype TEXT,currency_code TEXT,updated_at TEXT NOT NULL)`,\n `CREATE TABLE IF NOT EXISTS wallet_groups (id TEXT PRIMARY KEY,name TEXT NOT NULL UNIQUE,sort_order INTEGER NOT NULL DEFAULT 0,created_at TEXT NOT NULL)`,\n `CREATE TABLE IF NOT EXISTS wallets (id TEXT PRIMARY KEY,name TEXT NOT NULL UNIQUE,created_at TEXT NOT NULL)`,\n `CREATE TABLE IF NOT EXISTS account_wallet_mapping (account_id TEXT PRIMARY KEY,wallet_id TEXT NOT NULL,updated_at TEXT NOT NULL,FOREIGN KEY(account_id) REFERENCES accounts(id),FOREIGN KEY(wallet_id) REFERENCES wallets(id))`,\n `CREATE TABLE IF NOT EXISTS investments (id TEXT PRIMARY KEY,item_id TEXT NOT NULL,name TEXT NOT NULL,number TEXT,type TEXT,subtype TEXT,balance REAL,amount REAL,currency_code TEXT,owner TEXT,status TEXT,institution_name TEXT,date TEXT,raw_details TEXT,updated_at TEXT NOT NULL,is_deleted INTEGER NOT NULL DEFAULT 0)`,\n `CREATE TABLE IF NOT EXISTS investment_wallet_mapping (investment_id TEXT PRIMARY KEY,wallet_id TEXT NOT NULL,updated_at TEXT NOT NULL,FOREIGN KEY(investment_id) REFERENCES investments(id),FOREIGN KEY(wallet_id) REFERENCES wallets(id))`,\n `CREATE TABLE IF NOT EXISTS transactions (id TEXT PRIMARY KEY,account_id TEXT NOT NULL,item_id TEXT,date TEXT NOT NULL,description TEXT,description_raw TEXT,category TEXT,category_id TEXT,amount REAL NOT NULL,currency_code TEXT,type TEXT,status TEXT,content_hash TEXT NOT NULL,is_deleted INTEGER NOT NULL DEFAULT 0,first_seen_at TEXT NOT NULL,updated_at TEXT NOT NULL,last_seen_sync TEXT NOT NULL)`,\n `CREATE INDEX IF NOT EXISTS idx_transactions_date ON transactions(date)`,\n `CREATE INDEX IF NOT EXISTS idx_transactions_account ON transactions(account_id)`,\n `CREATE TABLE IF NOT EXISTS sync_runs (id TEXT PRIMARY KEY,date_from TEXT NOT NULL,started_at TEXT NOT NULL,finished_at TEXT,new_count INTEGER DEFAULT 0,updated_count INTEGER DEFAULT 0,unchanged_count INTEGER DEFAULT 0,removed_count INTEGER DEFAULT 0,status TEXT NOT NULL,error TEXT)`,\n `CREATE TABLE IF NOT EXISTS transaction_change_log (id TEXT PRIMARY KEY,sync_id TEXT NOT NULL,transaction_id TEXT NOT NULL,changed_at TEXT NOT NULL,changed_fields TEXT NOT NULL,date_before TEXT,date_after TEXT,description_before TEXT,description_after TEXT,amount_before REAL,amount_after REAL,category_before TEXT,category_after TEXT,status_before TEXT,status_after TEXT,FOREIGN KEY(transaction_id) REFERENCES transactions(id))`,\n `CREATE INDEX IF NOT EXISTS idx_transaction_change_sync ON transaction_change_log(sync_id)`,\n `CREATE INDEX IF NOT EXISTS idx_transaction_change_transaction ON transaction_change_log(transaction_id,changed_at)`,\n `CREATE TABLE IF NOT EXISTS category2_groups (id TEXT PRIMARY KEY,nature TEXT NOT NULL,code TEXT NOT NULL,name TEXT NOT NULL,sort_order INTEGER NOT NULL DEFAULT 0,created_at TEXT NOT NULL,UNIQUE(nature,code))`,\n `CREATE TABLE IF NOT EXISTS category2_items (id TEXT PRIMARY KEY,group_id TEXT NOT NULL,nature TEXT NOT NULL,code TEXT NOT NULL,name TEXT NOT NULL,is_reducer INTEGER NOT NULL DEFAULT 0,sort_order INTEGER NOT NULL DEFAULT 0,created_at TEXT NOT NULL,FOREIGN KEY(group_id) REFERENCES category2_groups(id),UNIQUE(nature,code))`,\n `CREATE TABLE IF NOT EXISTS transaction_category2 (transaction_id TEXT PRIMARY KEY,category_id TEXT NOT NULL,updated_at TEXT NOT NULL,FOREIGN KEY(transaction_id) REFERENCES transactions(id),FOREIGN KEY(category_id) REFERENCES category2_items(id))`,\n `CREATE INDEX IF NOT EXISTS idx_category2_items_group ON category2_items(group_id,sort_order)`,\n `CREATE INDEX IF NOT EXISTS idx_transaction_category2_category ON transaction_category2(category_id)`,\n `CREATE TABLE IF NOT EXISTS category2_meta (key TEXT PRIMARY KEY,value TEXT NOT NULL)`,\n `CREATE TABLE IF NOT EXISTS pluggy_category_map (pluggy_category TEXT PRIMARY KEY,category_id TEXT NOT NULL,updated_at TEXT NOT NULL,FOREIGN KEY(category_id) REFERENCES category2_items(id))`,\n // Uma transferência é UM registro com dois lados, não duas pernas soltas.\n // kind='linked': casa dois lançamentos que a Pluggy já trouxe (não cria dinheiro).\n // kind='manual': dinheiro que a Pluggy não vê; só existem as carteiras, sem lançamento.\n `CREATE TABLE IF NOT EXISTS transfers (id TEXT PRIMARY KEY,kind TEXT NOT NULL,date TEXT NOT NULL,amount REAL NOT NULL,from_wallet_id TEXT,to_wallet_id TEXT,from_transaction_id TEXT,to_transaction_id TEXT,note TEXT,created_at TEXT NOT NULL)`,\n // Um lançamento só pode ser lado de UMA transferência. Garantido pelo banco, não por código.\n `CREATE UNIQUE INDEX IF NOT EXISTS idx_transfer_from_tx ON transfers(from_transaction_id) WHERE from_transaction_id IS NOT NULL`,\n `CREATE UNIQUE INDEX IF NOT EXISTS idx_transfer_to_tx ON transfers(to_transaction_id) WHERE to_transaction_id IS NOT NULL`,\n `CREATE INDEX IF NOT EXISTS idx_transfers_date ON transfers(date)`,\n ]);\n await ensureColumn(\"accounts\", \"number\", \"TEXT\");\n await ensureColumn(\"accounts\", \"balance\", \"REAL\");\n await ensureColumn(\"accounts\", \"owner\", \"TEXT\");\n await ensureColumn(\"accounts\", \"tax_number\", \"TEXT\");\n await ensureColumn(\"accounts\", \"bank_data\", \"TEXT\");\n await ensureColumn(\"accounts\", \"credit_data\", \"TEXT\");\n await ensureColumn(\"items\", \"products\", \"TEXT\");\n await ensureColumn(\"items\", \"bank_cnpj\", \"TEXT\");\n await ensureColumn(\"transactions\", \"source_type\", \"TEXT\");\n await ensureColumn(\"transactions\", \"investment_id\", \"TEXT\");\n await ensureColumn(\"transactions\", \"reviewed\", \"INTEGER NOT NULL DEFAULT 0\");\n await ensureColumn(\"transactions\", \"reviewed_at\", \"TEXT\");\n await ensureColumn(\"transactions\", \"manual_note\", \"TEXT\");\n await ensureColumn(\"transactions\", \"manual_note_updated_at\", \"TEXT\");\n await ensureColumn(\"transactions\", \"raw_details\", \"TEXT\");\n await ensureColumn(\"wallets\", \"sort_order\", \"INTEGER NOT NULL DEFAULT 0\");\n await ensureColumn(\"wallets\", \"group_id\", \"TEXT\");\n await ensureColumn(\"wallets\", \"opening_balance\", \"REAL NOT NULL DEFAULT 0\");\n await ensureColumn(\"wallets\", \"opening_date\", \"TEXT\");\n await ensureCategory2Seeds();\n // O histórico é acumulativo: registros já armazenados nunca são ocultados\n // apenas porque a Pluggy deixou de devolvê-los em uma sincronização posterior.\n await sqlite.batch([\n `UPDATE transactions SET is_deleted=0 WHERE is_deleted<>0`,\n `UPDATE investments SET is_deleted=0 WHERE is_deleted<>0`,\n ]);\n}\n\nconst CATEGORY2_GROUPS = [\n [\"DESPESA\", \"1.0\", \"CASA\"],\n [\"DESPESA\", \"2.0\", \"ALIMENTAÇÃO\"],\n [\"DESPESA\", \"3.0\", \"SAÚDE\"],\n [\"DESPESA\", \"4.0\", \"COMPRAS\"],\n [\"DESPESA\", \"5.0\", \"TRANSPORTE\"],\n [\"DESPESA\", \"6.0\", \"DEPENDENTES\"],\n [\"DESPESA\", \"7.0\", \"VIAGEM\"],\n [\"DESPESA\", \"8.0\", \"EDUCAÇÃO\"],\n [\"DESPESA\", \"9.0\", \"FINANCEIRO\"],\n [\"DESPESA\", \"9.1\", \"EMPRESA\"],\n [\"DESPESA\", \"10.0\", \"REEMBOLSO\"],\n [\"RECEITA\", \"0.0\", \"CORRENTE\"],\n [\"RECEITA\", \"0.1\", \"RECEITA INVESTIMENTO (FÍSICO)\"],\n [\"RECEITA\", \"0.2\", \"RECEITA FINANCEIRA\"],\n [\"RECEITA\", \"0.4\", \"TRANSACIONAL\"],\n [\"RECEITA\", \"0.5\", \"OUTROS RECEITA\"],\n [\"MOVIMENTACAO\", \"11.0\", \"MOVIMENTAÇÃO\"],\n] as const;\n\nconst CATEGORY2_ITEMS = [\n [\"1.0\", \"1.01\", \"FINANCIAMENTO\", 0],\n [\"1.0\", \"1.02\", \"CONDOMÍNIO 1\", 0],\n [\"1.0\", \"1.03\", \"CONDOMÍNIO 2\", 0],\n [\"1.0\", \"1.04\", \"TELEFONE\", 0],\n [\"1.0\", \"1.05\", \"GÁS\", 0],\n [\"1.0\", \"1.06\", \"INTERNET\", 0],\n [\"1.0\", \"1.07\", \"LUZ\", 0],\n [\"1.0\", \"1.08\", \"MOTORISTA\", 0],\n [\"1.0\", \"1.09\", \"EMPREGADA\", 0],\n [\"1.0\", \"1.10\", \"STREAMING\", 0],\n [\"1.0\", \"1.11\", \"IPTU\", 0],\n [\"1.0\", \"1.12\", \"CASA OUTROS\", 0],\n [\"1.0\", \"1.13\", \"ALUGUEL\", 0],\n [\"2.0\", \"2.01\", \"MERCADO\", 0],\n [\"2.0\", \"2.02\", \"IFOOD\", 0],\n [\"2.0\", \"2.03\", \"RESTAURANTE\", 0],\n [\"2.0\", \"2.04\", \"ALIMENTAÇÃO POSTO\", 0],\n [\"2.0\", \"2.05\", \"ALIMENTAÇÃO OUTROS\", 0],\n [\"3.0\", \"3.01\", \"PLANO SAÚDE\", 0],\n [\"3.0\", \"3.02\", \"FARMÁCIA\", 0],\n [\"3.0\", \"3.03\", \"ACADEMIA\", 0],\n [\"3.0\", \"3.04\", \"PERSONAL\", 0],\n [\"3.0\", \"3.05\", \"DENTISTA\", 0],\n [\"3.0\", \"3.06\", \"CABELO\", 0],\n [\"3.0\", \"3.07\", \"SAÚDE OUTROS\", 0],\n [\"4.0\", \"4.01\", \"COMPRAS CASA\", 0],\n [\"4.0\", \"4.02\", \"COMPRAS PESSOAL\", 0],\n [\"4.0\", \"4.03\", \"COMPRAS PRESENTES\", 0],\n [\"4.0\", \"4.04\", \"COMPRAS ROUPAS\", 0],\n [\"4.0\", \"4.05\", \"COMPRAS LAZER\", 0],\n [\"4.0\", \"4.06\", \"COMPRAS OUTROS\", 0],\n [\"5.0\", \"5.01\", \"COMBUSTÍVEL\", 0],\n [\"5.0\", \"5.02\", \"SEGURO\", 0],\n [\"5.0\", \"5.03\", \"UBER\", 0],\n [\"5.0\", \"5.04\", \"IPVA\", 0],\n [\"5.0\", \"5.05\", \"REVISÃO\", 0],\n [\"5.0\", \"5.06\", \"ESTACIONAMENTO\", 0],\n [\"5.0\", \"5.07\", \"TRANSPORTE OUTROS\", 0],\n [\"6.0\", \"6.01\", \"KUMON CECÍLIA\", 0],\n [\"6.0\", \"6.02\", \"NATAÇÃO CECÍLIA\", 0],\n [\"6.0\", \"6.03\", \"PLANO SAÚDE VERE\", 0],\n [\"6.0\", \"6.04\", \"DEPENDENTES OUTROS\", 0],\n [\"7.0\", \"7.01\", \"PASSAGEM\", 0],\n [\"7.0\", \"7.02\", \"VIAGEM OUTROS\", 0],\n [\"8.0\", \"8.01\", \"EDUCAÇÃO OUTROS\", 0],\n [\"9.0\", \"9.01\", \"PMT EMPRÉSTIMO PESSOAL\", 0],\n [\"9.0\", \"9.02\", \"PMT EMPRÉSTIMO PJ\", 0],\n [\"9.0\", \"9.05\", \"JUROS\", 0],\n [\"9.0\", \"9.06\", \"IOF\", 0],\n [\"9.0\", \"9.07\", \"TARIFA\", 0],\n [\"9.0\", \"9.08\", \"FINANCEIRO OUTROS\", 0],\n [\"9.1\", \"9.1.1\", \"CONTADOR PRACO\", 0],\n [\"9.1\", \"9.1.2\", \"CONTADOR CAVENAGUE\", 0],\n [\"9.1\", \"9.1.3\", \"IMPOSTOS CORRENTE\", 0],\n [\"9.1\", \"9.1.4\", \"IMPOSTOS PARCELADOS FEDERAL\", 0],\n [\"9.1\", \"9.1.5\", \"IMPOSTOS PARCELADOS MUNICIPAL\", 0],\n [\"10.0\", \"10.01\", \"REEMBOLSO DRYVE\", 1],\n [\"10.0\", \"10.02\", \"REEMBOLSO OUTROS\", 1],\n [\"0.0\", \"0.0.1\", \"SALÁRIO\", 0],\n [\"0.0\", \"0.0.2\", \"13º\", 0],\n [\"0.0\", \"0.0.3\", \"FÉRIAS\", 0],\n [\"0.0\", \"0.0.4\", \"BÔNUS EMPRESA\", 0],\n [\"0.1\", \"0.1.1\", \"ALUGUEL 3º\", 0],\n [\"0.2\", \"0.2.1\", \"RENDIMENTO\", 0],\n [\"0.4\", \"0.4.1\", \"PIX TRANSACIONAL\", 0],\n [\"0.4\", \"0.4.2\", \"TRANSFERÊNCIA TRANSACIONAL\", 0],\n [\"0.5\", \"0.5.1\", \"ENTRADA OUTROS\", 0],\n [\"11.0\", \"11.01\", \"PAGAMENTO CARTÃO (BUSINESS/AMEX)\", 0],\n [\"11.0\", \"11.02\", \"APORTE INVESTIMENTO\", 0],\n [\"11.0\", \"11.03\", \"RESGATE INVESTIMENTO\", 0],\n [\"11.0\", \"11.04\", \"INVESTIMENTO INICIAL\", 0],\n [\"11.0\", \"11.05\", \"EMPRÉSTIMO (ENTRADA)\", 0],\n [\"11.0\", \"11.06\", \"AJUSTE SALDO\", 0],\n [\"11.0\", \"11.07\", \"TRANSFERÊNCIA ENTRE CONTAS\", 0],\n] as const;\n\nfunction category2Id(prefix: string, code: string) {\n return `${prefix}-${code.replace(/\\./g, \"-\")}`;\n}\n\nasync function ensureCategory2Seeds() {\n const marker = await sqlite.execute({\n sql: `SELECT value FROM category2_meta WHERE key='base-seeded'`,\n args: [],\n });\n if (marker.rows.length) return;\n const now = new Date().toISOString();\n const groups = CATEGORY2_GROUPS.map((group, index) => ({\n sql:\n `INSERT OR IGNORE INTO category2_groups(id,nature,code,name,sort_order,created_at) VALUES(?,?,?,?,?,?)`,\n args: [\n category2Id(\"base-group\", group[1]),\n group[0],\n group[1],\n group[2],\n index,\n now,\n ],\n }));\n const groupMap = new Map(\n CATEGORY2_GROUPS.map((\n group,\n ) => [group[1], {\n id: category2Id(\"base-group\", group[1]),\n nature: group[0],\n }]),\n );\n const counters = new Map();\n const items = CATEGORY2_ITEMS.map((item) => {\n const group = groupMap.get(item[0])!;\n const order = counters.get(item[0]) || 0;\n counters.set(item[0], order + 1);\n return {\n sql:\n `INSERT OR IGNORE INTO category2_items(id,group_id,nature,code,name,is_reducer,sort_order,created_at) VALUES(?,?,?,?,?,?,?,?)`,\n args: [\n category2Id(\"base-item\", item[1]),\n group.id,\n group.nature,\n item[1],\n item[2],\n item[3],\n order,\n now,\n ],\n };\n });\n await executeBatches([...groups, ...items]);\n await sqlite.execute({\n sql:\n `INSERT OR REPLACE INTO category2_meta(key,value) VALUES('base-seeded','1')`,\n args: [],\n });\n}\n\nasync function ensureColumn(table: string, column: string, type: string) {\n const info = await sqlite.execute(`PRAGMA table_info(${table})`);\n if (!(info.rows as any[]).some((row) => row.name === column)) {\n await sqlite.execute(`ALTER TABLE ${table} ADD COLUMN ${column} ${type}`);\n }\n}\n\nasync function pluggyFetch(pathOrUrl: string, apiKey: string) {\n const url = pathOrUrl.startsWith(\"http\")\n ? pathOrUrl\n : `${PLUGGY_BASE_URL}${pathOrUrl}`;\n const response = await fetch(url, {\n headers: { \"X-API-KEY\": apiKey, Accept: \"application/json\" },\n });\n const body = await response.json().catch(() => null);\n if (!response.ok) {\n throw new Error(\n `Pluggy: ${\n body?.message || body?.error?.message || `HTTP ${response.status}`\n }`,\n );\n }\n return body;\n}\n\nasync function createApiKey() {\n const response = await fetch(`${PLUGGY_BASE_URL}/auth`, {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/json\", Accept: \"application/json\" },\n body: JSON.stringify({\n clientId: requiredEnv(\"PLUGGY_CLIENT_ID\"),\n clientSecret: requiredEnv(\"PLUGGY_CLIENT_SECRET\"),\n }),\n });\n const body = await response.json().catch(() => null);\n if (!response.ok || !body?.apiKey) {\n throw new Error(\"Não foi possível autenticar na Pluggy.\");\n }\n return body.apiKey as string;\n}\n\nasync function getAccounts(itemIds: string[], apiKey: string) {\n const groups = await Promise.all(itemIds.map(async (itemId) => {\n const body = await pluggyFetch(\n `/accounts?itemId=${encodeURIComponent(itemId)}`,\n apiKey,\n );\n return (body?.results || []).map((account: any) => ({\n ...account,\n itemId,\n }));\n }));\n return groups.flat();\n}\n\nasync function getItemDetails(itemIds: string[], apiKey: string) {\n return await Promise.all(itemIds.map(async (itemId) => {\n const item = await pluggyFetch(\n `/items/${encodeURIComponent(itemId)}`,\n apiKey,\n );\n let connector: any = item?.connector || null;\n const connectorId = item?.connectorId || connector?.id || null;\n if (!connector?.name && connectorId) {\n connector = await pluggyFetch(\n `/connectors/${encodeURIComponent(connectorId)}`,\n apiKey,\n ).catch(() => null);\n }\n const bankCnpj = firstDocument([\n connector?.cnpj,\n connector?.taxNumber,\n connector?.documentNumber,\n connector?.institution?.cnpj,\n connector?.institution?.taxNumber,\n item?.connector?.cnpj,\n item?.connector?.taxNumber,\n item?.institution?.cnpj,\n item?.institution?.taxNumber,\n item?.bankCnpj,\n ]);\n return {\n id: itemId,\n connectorId: connectorId ? String(connectorId) : null,\n bankName: connector?.name ||\n (typeof item?.connector === \"string\" ? item.connector : \"\") ||\n item?.connectorName || item?.name || `Conexão ${itemId.slice(0, 8)}`,\n bankCnpj,\n status: item?.status || item?.executionStatus || null,\n products: Array.isArray(item?.products) ? item.products : [],\n };\n }));\n}\n\nfunction firstDocument(values: unknown[]): string | null {\n for (const value of values) {\n const text = typeof value === \"object\" && value !== null\n ? String((value as any).value || \"\")\n : String(value || \"\");\n const digits = text.replace(/\\D/g, \"\");\n if (digits.length === 14) return digits;\n }\n return null;\n}\n\nasync function getInvestments(itemIds: string[], apiKey: string) {\n const groups = await Promise.all(itemIds.map(async (itemId) => {\n const all: any[] = [];\n for (let page = 1; page <= 100; page++) {\n const body = await pluggyFetch(\n `/investments?itemId=${\n encodeURIComponent(itemId)\n }&pageSize=500&page=${page}`,\n apiKey,\n );\n const results = Array.isArray(body?.results) ? body.results : [];\n all.push(\n ...results.map((investment: any) => ({ ...investment, itemId })),\n );\n const totalPages = Number(body?.totalPages);\n if (\n Number.isFinite(totalPages) && totalPages > 0\n ? page >= totalPages\n : results.length < 500\n ) break;\n }\n return all;\n }));\n return groups.flat();\n}\n\nasync function getEveryTransaction(\n account: any,\n apiKey: string,\n dateFrom: string,\n) {\n const params = new URLSearchParams({ accountId: account.id, dateFrom });\n params.set(\"pageSize\", \"500\");\n let path = `/v2/transactions?${params.toString()}`;\n const transactions: any[] = [];\n const visited = new Set();\n while (path) {\n if (visited.has(path)) {\n throw new Error(`Cursor repetido na conta ${account.id}.`);\n }\n visited.add(path);\n const page = await pluggyFetch(path, apiKey);\n if (!Array.isArray(page?.results)) {\n throw new Error(`Resposta inválida para a conta ${account.id}.`);\n }\n transactions.push(\n ...page.results.map((transaction: any) => ({\n ...transaction,\n itemId: account.itemId,\n sourceType: \"account\",\n investmentId: null,\n rawDetails: transaction,\n })),\n );\n path = nextTransactionsPath(path, page.next);\n }\n return transactions;\n}\n\nfunction nextTransactionsPath(currentPath: string, next: unknown): string {\n if (!next) return \"\";\n const value = String(next).trim();\n if (!value) return \"\";\n const current = new URL(currentPath, PLUGGY_BASE_URL);\n let candidate: URL;\n if (/^https?:\\/\\//i.test(value)) {\n candidate = new URL(value);\n if (candidate.origin !== PLUGGY_BASE_URL) {\n throw new Error(\"A Pluggy devolveu paginação de outra origem.\");\n }\n } else if (value.startsWith(\"/\")) candidate = new URL(value, PLUGGY_BASE_URL);\n else if (value.startsWith(\"?\")) {\n candidate = new URL(current.pathname + value, PLUGGY_BASE_URL);\n } else candidate = new URL(current.pathname + \"?\" + value, PLUGGY_BASE_URL);\n for (const [key, paramValue] of current.searchParams.entries()) {\n if (!candidate.searchParams.has(key)) {\n candidate.searchParams.set(key, paramValue);\n }\n }\n const resolved = candidate.pathname +\n (candidate.searchParams.toString()\n ? `?${candidate.searchParams.toString()}`\n : \"\");\n return resolved === current.pathname + current.search ? \"\" : resolved;\n}\n\nfunction movementFingerprint(investmentId: string, movement: any) {\n return [\n investmentId,\n movement.date,\n movement.tradeDate,\n movement.type,\n movement.amount,\n movement.netAmount,\n movement.quantity,\n movement.value,\n ].map((value) => String(value ?? \"\")).join(\"|\");\n}\n\nasync function collectInvestmentMovements(\n investment: any,\n apiKey: string,\n dateFrom: string,\n) {\n const movements: any[] = [];\n const signatures = new Set();\n for (let page = 1; page <= 100; page++) {\n const body = await pluggyFetch(\n `/investments/${\n encodeURIComponent(investment.id)\n }/transactions?pageSize=500&page=${page}`,\n apiKey,\n );\n const results = Array.isArray(body?.results) ? body.results : [];\n const signature = results.length\n ? `${results.length}:${results[0]?.id || JSON.stringify(results[0])}:${\n results.at(-1)?.id || JSON.stringify(results.at(-1))\n }`\n : \"empty\";\n if (signatures.has(signature)) {\n throw new Error(\n `A Pluggy repetiu a página ${page} do investimento ${investment.id}.`,\n );\n }\n signatures.add(signature);\n movements.push(...results);\n const totalPages = Number(body?.totalPages);\n if (\n Number.isFinite(totalPages) && totalPages > 0\n ? page >= totalPages\n : results.length < 500\n ) break;\n }\n const decorated = movements.map((movement, index) => ({\n movement,\n index,\n fingerprint: movementFingerprint(investment.id, movement),\n }));\n const ordered = decorated.slice().sort((a, b) =>\n `${\n a.movement.date || a.movement.tradeDate || \"\"\n }|${a.fingerprint}|${a.index}`.localeCompare(\n `${\n b.movement.date || b.movement.tradeDate || \"\"\n }|${b.fingerprint}|${b.index}`,\n )\n );\n const counters = new Map();\n const occurrences = new Map();\n for (const entry of ordered) {\n const count = (counters.get(entry.fingerprint) || 0) + 1;\n counters.set(entry.fingerprint, count);\n occurrences.set(entry.index, count);\n }\n const output: any[] = [];\n for (const entry of decorated) {\n const movement = entry.movement;\n const date = String(movement.date || movement.tradeDate || \"\").slice(0, 10);\n if (!/^\\d{4}-\\d{2}-\\d{2}$/.test(date) || date < dateFrom) continue;\n const movementType = String(movement.type || \"\").toUpperCase();\n const rawAmount = movement.netAmount ?? movement.amount ??\n ((Number(movement.quantity) || 0) * (Number(movement.value) || 0));\n const providerId = movement.id || movement.providerId;\n const synthetic = await hashText(entry.fingerprint);\n output.push({\n id: providerId\n ? `inv:${investment.id}:${providerId}`\n : `inv:${investment.id}:synthetic-${synthetic}-${\n occurrences.get(entry.index)\n }`,\n accountId: investment.id,\n investmentId: investment.id,\n itemId: investment.itemId,\n sourceType: \"investment\",\n date,\n description: [\n movementType || \"Movimentação\",\n movement.description,\n investment.name,\n ].filter(Boolean).join(\" · \"),\n descriptionRaw: movement.description || null,\n category: \"Investimentos\",\n categoryId: null,\n amount: Math.abs(Number(rawAmount) || 0),\n currencyCode: investment.currencyCode || \"BRL\",\n type: movementType === \"INTEREST\"\n ? \"CREDIT\"\n : movementType === \"TAX\"\n ? \"DEBIT\"\n : \"MOVEMENT\",\n status: \"POSTED\",\n rawDetails: movement,\n });\n }\n return output;\n}\n\nasync function hashText(value: string) {\n const digest = await crypto.subtle.digest(\n \"SHA-256\",\n new TextEncoder().encode(value),\n );\n return [...new Uint8Array(digest)].map((byte) =>\n byte.toString(16).padStart(2, \"0\")\n ).join(\"\");\n}\n\nasync function hashTransaction(transaction: any) {\n const comparable = JSON.stringify({\n accountId: transaction.accountId,\n investmentId: transaction.investmentId,\n sourceType: transaction.sourceType,\n date: transaction.date,\n description: transaction.description,\n descriptionRaw: transaction.descriptionRaw,\n category: transaction.category,\n categoryId: transaction.categoryId,\n amount: transaction.amount,\n currencyCode: transaction.currencyCode,\n type: transaction.type,\n status: transaction.status,\n });\n const digest = await crypto.subtle.digest(\n \"SHA-256\",\n new TextEncoder().encode(comparable),\n );\n return [...new Uint8Array(digest)].map((byte) =>\n byte.toString(16).padStart(2, \"0\")\n ).join(\"\");\n}\n\nasync function executeBatches(statements: any[], size = 100) {\n for (let index = 0; index < statements.length; index += size) {\n await sqlite.batch(statements.slice(index, index + size));\n }\n}\n\nasync function syncTransactions(dateFrom: string) {\n const syncId = crypto.randomUUID();\n const startedAt = new Date().toISOString();\n await sqlite.execute({\n sql:\n `INSERT INTO sync_runs(id,date_from,started_at,status) VALUES(?,?,?,?)`,\n args: [syncId, dateFrom, startedAt, \"RUNNING\"],\n });\n try {\n const apiKey = await createApiKey();\n const itemIds = parseItemIds(requiredEnv(\"PLUGGY_ITEM_IDS\"));\n const [itemDetails, accounts, investments] = await Promise.all([\n getItemDetails(itemIds, apiKey),\n getAccounts(itemIds, apiKey),\n getInvestments(itemIds, apiKey),\n ]);\n for (const item of itemDetails) {\n const relatedAccount = accounts.find((account: any) =>\n account.itemId === item.id\n );\n const relatedInvestment = investments.find((investment: any) =>\n investment.itemId === item.id\n );\n if (!item.bankName || /^meupluggy$/i.test(item.bankName)) {\n item.bankName = relatedAccount?.marketingName || relatedAccount?.name ||\n relatedInvestment?.institution?.name || relatedInvestment?.issuer ||\n item.bankName || `Conexão ${item.id.slice(0, 8)}`;\n }\n }\n const now = new Date().toISOString();\n await executeBatches(itemDetails.map((item: any) => ({\n sql:\n `INSERT INTO items(id,connector_id,bank_name,status,updated_at,products,bank_cnpj) VALUES(?,?,?,?,?,?,?) ON CONFLICT(id) DO UPDATE SET connector_id=excluded.connector_id,bank_name=excluded.bank_name,status=excluded.status,updated_at=excluded.updated_at,products=excluded.products,bank_cnpj=excluded.bank_cnpj`,\n args: [\n item.id,\n item.connectorId,\n item.bankName,\n item.status,\n now,\n JSON.stringify(item.products),\n item.bankCnpj,\n ],\n })));\n await executeBatches(accounts.map((account: any) => ({\n sql:\n `INSERT INTO accounts(id,item_id,name,type,subtype,currency_code,updated_at,number,balance,owner,tax_number,bank_data,credit_data) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?) ON CONFLICT(id) DO UPDATE SET item_id=excluded.item_id,name=excluded.name,type=excluded.type,subtype=excluded.subtype,currency_code=excluded.currency_code,updated_at=excluded.updated_at,number=excluded.number,balance=excluded.balance,owner=excluded.owner,tax_number=excluded.tax_number,bank_data=excluded.bank_data,credit_data=excluded.credit_data`,\n args: [\n account.id,\n account.itemId,\n account.marketingName || account.name || account.number || \"Conta\",\n account.type,\n account.subtype,\n account.currencyCode,\n now,\n account.number || account.bankData?.transferNumber || null,\n Number(account.balance) || 0,\n account.owner || null,\n account.taxNumber || null,\n JSON.stringify(account.bankData || null),\n JSON.stringify(account.creditData || null),\n ],\n })));\n await executeBatches(investments.map((investment: any) => ({\n sql:\n `INSERT INTO investments(id,item_id,name,number,type,subtype,balance,amount,currency_code,owner,status,institution_name,date,raw_details,updated_at,is_deleted) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0) ON CONFLICT(id) DO UPDATE SET item_id=excluded.item_id,name=excluded.name,number=excluded.number,type=excluded.type,subtype=excluded.subtype,balance=excluded.balance,amount=excluded.amount,currency_code=excluded.currency_code,owner=excluded.owner,status=excluded.status,institution_name=excluded.institution_name,date=excluded.date,raw_details=excluded.raw_details,updated_at=excluded.updated_at,is_deleted=0`,\n args: [\n investment.id,\n investment.itemId,\n investment.name || investment.code || \"Investimento\",\n investment.number || investment.code || null,\n investment.type,\n investment.subtype,\n Number(investment.balance) || 0,\n Number(investment.amount) || 0,\n investment.currencyCode || \"BRL\",\n investment.owner || null,\n investment.status || null,\n investment.institution?.name || investment.issuer || null,\n investment.date || null,\n JSON.stringify({\n code: investment.code,\n issuer: investment.issuer,\n quantity: investment.quantity,\n value: investment.value,\n amountProfit: investment.amountProfit,\n amountWithdrawal: investment.amountWithdrawal,\n amountOriginal: investment.amountOriginal,\n dueDate: investment.dueDate,\n rate: investment.rate,\n rateType: investment.rateType,\n fixedAnnualRate: investment.fixedAnnualRate,\n }),\n now,\n ],\n })));\n const warnings: string[] = [];\n if (!accounts.length) {\n warnings.push(\n \"Nenhuma conta ou subconta foi devolvida pelos Items configurados.\",\n );\n }\n if (!investments.length) {\n warnings.push(\n \"Nenhuma posição de investimento foi devolvida pelos Items configurados.\",\n );\n }\n const completeAccountIds = new Set();\n const accountGroups = await Promise.all(\n accounts.map(async (account: any) => {\n try {\n const rows = await getEveryTransaction(account, apiKey, dateFrom);\n completeAccountIds.add(account.id);\n return rows;\n } catch (error) {\n warnings.push(\n `Conta ${account.name || account.id}: ${\n error instanceof Error ? error.message : \"falha na coleta\"\n }`,\n );\n return [];\n }\n }),\n );\n const completeInvestmentIds = new Set();\n const investmentGroups = await Promise.all(\n investments.map(async (investment: any) => {\n try {\n const rows = await collectInvestmentMovements(\n investment,\n apiKey,\n dateFrom,\n );\n completeInvestmentIds.add(investment.id);\n return rows;\n } catch (error) {\n warnings.push(\n `Investimento ${investment.name || investment.id}: ${\n error instanceof Error ? error.message : \"falha na coleta\"\n }`,\n );\n return [];\n }\n }),\n );\n const incoming = [...accountGroups.flat(), ...investmentGroups.flat()];\n const existingResult = await sqlite.execute({\n sql:\n `SELECT id,content_hash,is_deleted,account_id AS accountId,investment_id AS investmentId,source_type AS sourceType,date,description,amount,category,status,type FROM transactions WHERE date >= ?`,\n args: [dateFrom],\n });\n const existing = new Map(\n (existingResult.rows as any[]).map((row) => [String(row.id), row]),\n );\n const seen = new Set();\n const statements: any[] = [];\n const changeStatements: any[] = [];\n const pastChanges: any[] = [];\n let newCount = 0, updatedCount = 0, unchangedCount = 0;\n for (const transaction of incoming) {\n const hash = await hashTransaction(transaction);\n const previous: any = existing.get(transaction.id);\n seen.add(transaction.id);\n if (!previous) newCount++;\n else if (\n previous.content_hash !== hash || Number(previous.is_deleted) === 1\n ) {\n updatedCount++;\n const changedFields: string[] = [];\n if (String(previous.date || \"\") !== String(transaction.date || \"\")) {\n changedFields.push(\"DATA\");\n }\n if (\n String(previous.description || \"\") !==\n String(transaction.description || \"\")\n ) changedFields.push(\"DESCRIÇÃO\");\n if (Number(previous.amount || 0) !== Number(transaction.amount || 0)) {\n changedFields.push(\"VALOR\");\n }\n if (\n String(previous.category || \"\") !== String(transaction.category || \"\")\n ) changedFields.push(\"CATEGORIA PLUGGY\");\n if (\n String(previous.status || \"\") !== String(transaction.status || \"\")\n ) changedFields.push(\"STATUS\");\n if (String(previous.type || \"\") !== String(transaction.type || \"\")) {\n changedFields.push(\"TIPO\");\n }\n if (!changedFields.length) changedFields.push(\"OUTROS DADOS DA PLUGGY\");\n const change = {\n transactionId: transaction.id,\n date: transaction.date,\n description: transaction.description || \"Sem descrição\",\n changedFields,\n before: {\n date: previous.date,\n description: previous.description,\n amount: Number(previous.amount || 0),\n category: previous.category,\n status: previous.status,\n type: previous.type,\n },\n after: {\n date: transaction.date,\n description: transaction.description,\n amount: Number(transaction.amount || 0),\n category: transaction.category,\n status: transaction.status,\n type: transaction.type,\n },\n };\n pastChanges.push(change);\n changeStatements.push({\n sql:\n `INSERT INTO transaction_change_log(id,sync_id,transaction_id,changed_at,changed_fields,date_before,date_after,description_before,description_after,amount_before,amount_after,category_before,category_after,status_before,status_after) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,\n args: [\n crypto.randomUUID(),\n syncId,\n transaction.id,\n now,\n JSON.stringify(changedFields),\n previous.date,\n transaction.date,\n previous.description,\n transaction.description,\n Number(previous.amount || 0),\n Number(transaction.amount || 0),\n previous.category,\n transaction.category,\n previous.status,\n transaction.status,\n ],\n });\n } else unchangedCount++;\n statements.push({\n sql:\n `INSERT INTO transactions(id,account_id,item_id,date,description,description_raw,category,category_id,amount,currency_code,type,status,content_hash,source_type,investment_id,raw_details,is_deleted,first_seen_at,updated_at,last_seen_sync) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0,?,?,?) ON CONFLICT(id) DO UPDATE SET account_id=excluded.account_id,item_id=excluded.item_id,date=excluded.date,description=excluded.description,description_raw=excluded.description_raw,category=excluded.category,category_id=excluded.category_id,amount=excluded.amount,currency_code=excluded.currency_code,type=excluded.type,status=excluded.status,content_hash=excluded.content_hash,source_type=excluded.source_type,investment_id=excluded.investment_id,raw_details=excluded.raw_details,is_deleted=0,updated_at=CASE WHEN transactions.content_hash<>excluded.content_hash OR transactions.is_deleted=1 THEN excluded.updated_at ELSE transactions.updated_at END,last_seen_sync=excluded.last_seen_sync`,\n args: [\n transaction.id,\n transaction.accountId,\n transaction.itemId,\n transaction.date,\n transaction.description || null,\n transaction.descriptionRaw || null,\n transaction.category || null,\n transaction.categoryId || null,\n Number(transaction.amount) || 0,\n transaction.currencyCode || \"BRL\",\n transaction.type || null,\n transaction.status || null,\n hash,\n transaction.sourceType || \"account\",\n transaction.investmentId || null,\n JSON.stringify(transaction.rawDetails || null),\n now,\n now,\n syncId,\n ],\n });\n }\n await executeBatches(statements);\n await executeBatches(changeStatements);\n // Sincronização acumulativa: novos registros são inseridos e registros\n // existentes são atualizados, mas ausências na resposta nunca apagam dados.\n const removedCount = 0;\n await sqlite.execute({\n sql:\n `UPDATE sync_runs SET finished_at=?,new_count=?,updated_count=?,unchanged_count=?,removed_count=?,status='SUCCESS' WHERE id=?`,\n args: [now, newCount, updatedCount, unchangedCount, removedCount, syncId],\n });\n return {\n syncId,\n dateFrom,\n newCount,\n updatedCount,\n unchangedCount,\n removedCount,\n pastChangeCount: pastChanges.length,\n pastChanges: pastChanges.slice(0, 50),\n totalReceived: incoming.length,\n accountCount: accounts.length,\n investmentCount: investments.length,\n investmentTransactionCount: investmentGroups.flat().length,\n warnings,\n complete: warnings.length === 0,\n };\n } catch (error) {\n await sqlite.execute({\n sql:\n `UPDATE sync_runs SET finished_at=?,status='ERROR',error=? WHERE id=?`,\n args: [\n new Date().toISOString(),\n error instanceof Error ? error.message : \"Erro inesperado\",\n syncId,\n ],\n });\n throw error;\n }\n}\n\nasync function listTransactions(url: URL) {\n const dateFrom = url.searchParams.get(\"dateFrom\") || null;\n const result = await sqlite.execute({\n sql:\n `SELECT t.id,t.account_id AS accountId,t.investment_id AS investmentId,t.item_id AS itemId,COALESCE(t.source_type,'account') AS sourceType,t.date,t.description,t.description_raw AS descriptionRaw,COALESCE(t.category,'Não classificado') AS category,c2.id AS category2Id,c2.name AS category2,c2.code AS category2Code,g2.id AS category2GroupId,g2.name AS category2Group,g2.nature AS category2Nature,c2.is_reducer AS category2Reducer,COALESCE(t.reviewed,0) AS reviewed,t.reviewed_at AS reviewedAt,COALESCE(t.manual_note,'') AS manualNote,t.manual_note_updated_at AS manualNoteUpdatedAt,t.raw_details AS rawDetails,t.amount,t.currency_code AS currencyCode,t.type,t.status,COALESCE(a.name,i.name,'Origem não identificada') AS accountName,COALESCE(wa.name,wi.name,'Não classificada') AS walletName,COALESCE(wa.id,wi.id) AS walletId,trf.id AS transferId,CASE WHEN trf.from_transaction_id=t.id THEN 'out' WHEN trf.to_transaction_id=t.id THEN 'in' END AS transferSide,t.updated_at AS updatedAt FROM transactions t LEFT JOIN accounts a ON a.id=t.account_id AND COALESCE(t.source_type,'account')='account' LEFT JOIN investments i ON i.id=COALESCE(t.investment_id,CASE WHEN t.source_type='investment' THEN t.account_id END) LEFT JOIN account_wallet_mapping ma ON ma.account_id=a.id LEFT JOIN wallets wa ON wa.id=ma.wallet_id LEFT JOIN investment_wallet_mapping mi ON mi.investment_id=i.id LEFT JOIN wallets wi ON wi.id=mi.wallet_id LEFT JOIN transaction_category2 tc2 ON tc2.transaction_id=t.id LEFT JOIN category2_items c2 ON c2.id=tc2.category_id LEFT JOIN category2_groups g2 ON g2.id=c2.group_id LEFT JOIN transfers trf ON trf.from_transaction_id=t.id OR trf.to_transaction_id=t.id WHERE (? IS NULL OR t.date>=?) ORDER BY t.date DESC`,\n args: [dateFrom, dateFrom],\n });\n return (result.rows as any[]).map((row) => {\n try {\n row.rawDetails = JSON.parse(row.rawDetails || \"null\");\n } catch {\n row.rawDetails = null;\n }\n return row;\n });\n}\n\nasync function listWalletData() {\n const groups =\n (await sqlite.execute(\n `SELECT id,name,sort_order AS sortOrder,created_at AS createdAt FROM wallet_groups ORDER BY sort_order,name`,\n )).rows;\n const wallets =\n (await sqlite.execute(\n `SELECT w.id,w.name,w.created_at AS createdAt,w.sort_order AS sortOrder,w.group_id AS groupId,g.name AS groupName,COALESCE(w.opening_balance,0) AS openingBalance,w.opening_date AS openingDate FROM wallets w LEFT JOIN wallet_groups g ON g.id=w.group_id ORDER BY COALESCE(g.sort_order,999999),w.sort_order,w.name`,\n )).rows;\n const items =\n (await sqlite.execute(\n `SELECT id,connector_id AS connectorId,bank_name AS bankName,bank_cnpj AS bankCnpj,status,products FROM items ORDER BY bank_name,id`,\n )).rows as any[];\n const accounts =\n (await sqlite.execute(\n `SELECT a.id,a.item_id AS itemId,a.name,a.number,a.type,a.subtype,a.balance,a.currency_code AS currencyCode,a.owner,a.tax_number AS taxNumber,a.bank_data AS bankData,a.credit_data AS creditData,m.wallet_id AS walletId,COALESCE(w.name,'Não classificada') AS walletName FROM accounts a LEFT JOIN account_wallet_mapping m ON m.account_id=a.id LEFT JOIN wallets w ON w.id=m.wallet_id ORDER BY a.name`,\n )).rows as any[];\n const investments =\n (await sqlite.execute(\n `SELECT i.id,i.item_id AS itemId,i.name,i.number,i.type,i.subtype,i.balance,i.amount,i.currency_code AS currencyCode,i.owner,i.status,i.institution_name AS institutionName,i.date,i.raw_details AS rawDetails,m.wallet_id AS walletId,COALESCE(w.name,'Não classificada') AS walletName FROM investments i LEFT JOIN investment_wallet_mapping m ON m.investment_id=i.id LEFT JOIN wallets w ON w.id=m.wallet_id ORDER BY i.name`,\n )).rows;\n for (const account of accounts) {\n try {\n account.bankData = JSON.parse(account.bankData || \"null\");\n } catch {\n account.bankData = null;\n }\n try {\n account.creditData = JSON.parse(account.creditData || \"null\");\n } catch {\n account.creditData = null;\n }\n }\n for (const item of items) {\n try {\n item.products = JSON.parse(item.products || \"[]\");\n } catch {\n item.products = [];\n }\n }\n for (const investment of investments as any[]) {\n try {\n investment.rawDetails = JSON.parse(investment.rawDetails || \"null\");\n } catch {\n investment.rawDetails = null;\n }\n }\n return { groups, wallets, items, accounts, investments };\n}\n\nasync function listCategory2() {\n const groups =\n (await sqlite.execute(\n `SELECT g.id,g.nature,g.code,g.name,g.sort_order AS sortOrder,COUNT(i.id) AS categoryCount FROM category2_groups g LEFT JOIN category2_items i ON i.group_id=g.id GROUP BY g.id ORDER BY CASE g.nature WHEN 'RECEITA' THEN 1 WHEN 'DESPESA' THEN 2 ELSE 3 END,g.sort_order,g.code`,\n )).rows;\n const categories =\n (await sqlite.execute(\n `SELECT i.id,i.group_id AS groupId,i.nature,i.code,i.name,i.is_reducer AS isReducer,i.sort_order AS sortOrder,COUNT(tc.transaction_id) AS transactionCount FROM category2_items i LEFT JOIN transaction_category2 tc ON tc.category_id=i.id GROUP BY i.id ORDER BY i.group_id,i.sort_order,i.code`,\n )).rows;\n return { groups, categories };\n}\n\n// Categorias que a Pluggy já devolveu, com quantos lançamentos cada uma tem, quantos ainda estão\n// sem Categoria 2, e para onde a categoria está mapeada. É a base da classificação automática.\nasync function listCategoryMap() {\n const rows =\n (await sqlite.execute(\n `SELECT COALESCE(t.category,'Não classificado') AS pluggyCategory,COUNT(*) AS total,SUM(CASE WHEN tc.transaction_id IS NULL THEN 1 ELSE 0 END) AS pending,m.category_id AS categoryId,c.code AS categoryCode,c.name AS categoryName,g.name AS categoryGroup,g.nature AS categoryNature FROM transactions t LEFT JOIN transaction_category2 tc ON tc.transaction_id=t.id LEFT JOIN pluggy_category_map m ON m.pluggy_category=COALESCE(t.category,'Não classificado') LEFT JOIN category2_items c ON c.id=m.category_id LEFT JOIN category2_groups g ON g.id=c.group_id GROUP BY pluggyCategory,m.category_id,c.code,c.name,g.name,g.nature ORDER BY pending DESC,total DESC`,\n )).rows;\n return { categories: rows };\n}\n\n// Aplica os mapeamentos SOMENTE onde ainda não existe Categoria 2. Nunca sobrescreve classificação\n// feita à mão e NUNCA toca em reviewed: preencher categoria não é o mesmo que conferir o lançamento.\nasync function applyCategoryMap() {\n const result = await sqlite.execute({\n sql:\n `INSERT INTO transaction_category2(transaction_id,category_id,updated_at) SELECT t.id,m.category_id,? FROM transactions t JOIN pluggy_category_map m ON m.pluggy_category=COALESCE(t.category,'Não classificado') LEFT JOIN transaction_category2 tc ON tc.transaction_id=t.id WHERE tc.transaction_id IS NULL`,\n args: [new Date().toISOString()],\n });\n return { classified: Number(result.rowsAffected) || 0 };\n}\n\nasync function listTransfers() {\n const rows =\n (await sqlite.execute(\n `SELECT tr.id,tr.kind,tr.date,tr.amount,tr.note,tr.created_at AS createdAt,tr.from_transaction_id AS fromTransactionId,tr.to_transaction_id AS toTransactionId,COALESCE(wf.name,wff.name,'Não classificada') AS fromWalletName,COALESCE(wt.name,wtt.name,'Não classificada') AS toWalletName,ft.description AS fromDescription,tt.description AS toDescription FROM transfers tr LEFT JOIN wallets wf ON wf.id=tr.from_wallet_id LEFT JOIN wallets wt ON wt.id=tr.to_wallet_id LEFT JOIN transactions ft ON ft.id=tr.from_transaction_id LEFT JOIN transactions tt ON tt.id=tr.to_transaction_id LEFT JOIN account_wallet_mapping mf ON mf.account_id=ft.account_id LEFT JOIN wallets wff ON wff.id=mf.wallet_id LEFT JOIN account_wallet_mapping mt ON mt.account_id=tt.account_id LEFT JOIN wallets wtt ON wtt.id=mt.wallet_id ORDER BY tr.date DESC,tr.created_at DESC`,\n )).rows;\n return { transfers: rows };\n}\n\n// Sugere pares: um DEBIT e um CREDIT de MESMO valor ao centavo, em contas DIFERENTES,\n// com até 2 dias de distância. substr(date,1,10) neutraliza o formato de data misturado.\n// Nada é aplicado aqui — isto só devolve candidatos para o humano confirmar.\nasync function suggestTransfers() {\n const rows =\n (await sqlite.execute(\n `SELECT d.id AS fromTransactionId,d.date AS fromDate,d.description AS fromDescription,af.name AS fromAccountName,COALESCE(wf.name,'Não classificada') AS fromWalletName,c.id AS toTransactionId,c.date AS toDate,c.description AS toDescription,at.name AS toAccountName,COALESCE(wt.name,'Não classificada') AS toWalletName,ROUND(ABS(d.amount),2) AS amount,CAST(ABS(julianday(substr(c.date,1,10))-julianday(substr(d.date,1,10))) AS INTEGER) AS dayDiff FROM transactions d JOIN transactions c ON c.type='CREDIT' AND c.account_id<>d.account_id AND ROUND(ABS(c.amount),2)=ROUND(ABS(d.amount),2) AND ABS(julianday(substr(c.date,1,10))-julianday(substr(d.date,1,10)))<=2 AND COALESCE(c.source_type,'account')='account' LEFT JOIN accounts af ON af.id=d.account_id LEFT JOIN account_wallet_mapping mf ON mf.account_id=d.account_id LEFT JOIN wallets wf ON wf.id=mf.wallet_id LEFT JOIN accounts at ON at.id=c.account_id LEFT JOIN account_wallet_mapping mt ON mt.account_id=c.account_id LEFT JOIN wallets wt ON wt.id=mt.wallet_id WHERE d.type='DEBIT' AND COALESCE(d.source_type,'account')='account' AND d.id NOT IN (SELECT from_transaction_id FROM transfers WHERE from_transaction_id IS NOT NULL) AND d.id NOT IN (SELECT to_transaction_id FROM transfers WHERE to_transaction_id IS NOT NULL) AND c.id NOT IN (SELECT from_transaction_id FROM transfers WHERE from_transaction_id IS NOT NULL) AND c.id NOT IN (SELECT to_transaction_id FROM transfers WHERE to_transaction_id IS NOT NULL) ORDER BY dayDiff,d.date DESC LIMIT 300`,\n )).rows as any[];\n // Ambiguidade importa mais que quantidade: se um lado tem mais de um candidato, o humano decide.\n const fromCount = new Map(),\n toCount = new Map();\n for (const row of rows) {\n fromCount.set(\n String(row.fromTransactionId),\n (fromCount.get(String(row.fromTransactionId)) || 0) + 1,\n );\n toCount.set(\n String(row.toTransactionId),\n (toCount.get(String(row.toTransactionId)) || 0) + 1,\n );\n }\n for (const row of rows) {\n const alone = fromCount.get(String(row.fromTransactionId)) === 1 &&\n toCount.get(String(row.toTransactionId)) === 1;\n row.unambiguous = alone;\n // \"exato\" = mesma data E par único dos dois lados. Só isso pode ser confirmado em massa.\n row.exact = alone && Number(row.dayDiff) === 0;\n }\n return {\n suggestions: rows,\n exactCount: rows.filter((row) => row.exact).length,\n };\n}\n\nconst CATEGORY2_NATURES = new Set([\"RECEITA\", \"DESPESA\", \"MOVIMENTACAO\"]);\nfunction upperText(value: unknown) {\n return String(value || \"\").trim().toLocaleUpperCase(\"pt-BR\");\n}\nfunction validCategory2Nature(value: unknown) {\n const nature = upperText(value).replace(\"Ç\", \"C\").replace(\"Ã\", \"A\");\n return CATEGORY2_NATURES.has(nature) ? nature : \"\";\n}\n\nasync function requireCategory2Group(groupId: string) {\n const result = await sqlite.execute({\n sql: `SELECT id,nature FROM category2_groups WHERE id=?`,\n args: [groupId],\n });\n return result.rows[0] as any;\n}\n\nasync function readBody(request: Request) {\n return await request.json().catch(() => ({}));\n}\n\nexport default async function handler(request: Request): Promise {\n if (request.method === \"OPTIONS\") {\n return new Response(null, { status: 204, headers: corsHeaders });\n }\n const url = new URL(request.url);\n if (\n request.method === \"GET\" &&\n (url.pathname === \"/\" || url.pathname === \"/health\")\n ) {\n return json({\n ok: true,\n service: \"financas-pluggy-db\",\n version: BACKEND_VERSION,\n features: BACKEND_FEATURES,\n });\n }\n try {\n const receivedToken = request.headers.get(\"Authorization\")?.replace(\n /^Bearer\\s+/i,\n \"\",\n );\n if (!receivedToken || receivedToken !== requiredEnv(\"APP_TOKEN\")) {\n return json({ error: \"APP_TOKEN inválido ou ausente.\" }, 401);\n }\n await ensureSchema();\n if (request.method === \"GET\" && url.pathname === \"/transactions\") {\n const transactions = await listTransactions(url);\n return json({\n transactions,\n meta: { transactionCount: transactions.length, source: \"database\" },\n });\n }\n if (request.method === \"POST\" && url.pathname === \"/sync\") {\n const body = await readBody(request);\n if (!/^\\d{4}-\\d{2}$/.test(body.startMonth || \"\")) {\n return json({ error: \"Escolha o mês e o ano iniciais.\" }, 400);\n }\n const dateFrom = `${body.startMonth}-01`;\n if (dateFrom > new Date().toISOString().slice(0, 10)) {\n return json({ error: \"A data inicial não pode estar no futuro.\" }, 400);\n }\n return json(await syncTransactions(dateFrom));\n }\n if (request.method === \"GET\" && url.pathname === \"/wallets\") {\n return json(await listWalletData());\n }\n if (request.method === \"POST\" && url.pathname === \"/wallet-groups\") {\n const body = await readBody(request);\n const name = String(body.name || \"\").trim();\n if (!name) return json({ error: \"Informe o nome do grupo.\" }, 400);\n const id = crypto.randomUUID();\n await sqlite.execute({\n sql:\n `INSERT INTO wallet_groups(id,name,sort_order,created_at) VALUES(?,?,(SELECT COALESCE(MAX(sort_order),-1)+1 FROM wallet_groups),?)`,\n args: [id, name, new Date().toISOString()],\n });\n return json({ id, name }, 201);\n }\n const walletGroupPath = url.pathname.match(\n /^\\/wallet-groups\\/([0-9a-f-]+)$/i,\n );\n if (request.method === \"PUT\" && walletGroupPath) {\n const body = await readBody(request);\n const name = String(body.name || \"\").trim();\n if (!name) return json({ error: \"Informe o novo nome do grupo.\" }, 400);\n const result = await sqlite.execute({\n sql: `UPDATE wallet_groups SET name=? WHERE id=?`,\n args: [name, walletGroupPath[1]],\n });\n if (!result.rowsAffected) {\n return json({ error: \"Grupo não encontrado.\" }, 404);\n }\n return json({ ok: true });\n }\n if (request.method === \"DELETE\" && walletGroupPath) {\n await sqlite.batch([\n {\n sql: `UPDATE wallets SET group_id=NULL WHERE group_id=?`,\n args: [walletGroupPath[1]],\n },\n {\n sql: `DELETE FROM wallet_groups WHERE id=?`,\n args: [walletGroupPath[1]],\n },\n ]);\n return json({ ok: true });\n }\n if (request.method === \"PUT\" && url.pathname === \"/wallet-group-mapping\") {\n const body = await readBody(request);\n const walletId = String(body.walletId || \"\"),\n groupId = String(body.groupId || \"\");\n const wallet = await sqlite.execute({\n sql: `SELECT id FROM wallets WHERE id=?`,\n args: [walletId],\n });\n if (!wallet.rows.length) {\n return json({ error: \"Carteira não encontrada.\" }, 404);\n }\n if (groupId) {\n const group = await sqlite.execute({\n sql: `SELECT id FROM wallet_groups WHERE id=?`,\n args: [groupId],\n });\n if (!group.rows.length) {\n return json({ error: \"Grupo não encontrado.\" }, 404);\n }\n }\n await sqlite.execute({\n sql: `UPDATE wallets SET group_id=? WHERE id=?`,\n args: [groupId || null, walletId],\n });\n return json({ ok: true });\n }\n if (request.method === \"PUT\" && url.pathname === \"/wallet-opening\") {\n const body = await readBody(request);\n const walletId = String(body.walletId || \"\");\n const openingBalance = Number(body.openingBalance);\n const openingDate = String(body.openingDate || \"\").trim();\n if (!Number.isFinite(openingBalance)) {\n return json({ error: \"Saldo inicial inválido.\" }, 400);\n }\n if (openingDate && !/^\\d{4}-\\d{2}-\\d{2}$/.test(openingDate)) {\n return json({\n error: \"A data do saldo inicial deve estar no formato AAAA-MM-DD.\",\n }, 400);\n }\n const wallet = await sqlite.execute({\n sql: `SELECT id FROM wallets WHERE id=?`,\n args: [walletId],\n });\n if (!wallet.rows.length) {\n return json({ error: \"Carteira não encontrada.\" }, 404);\n }\n await sqlite.execute({\n sql: `UPDATE wallets SET opening_balance=?,opening_date=? WHERE id=?`,\n args: [openingBalance, openingDate || null, walletId],\n });\n return json({ ok: true });\n }\n if (request.method === \"GET\" && url.pathname === \"/category2\") {\n return json(await listCategory2());\n }\n if (request.method === \"POST\" && url.pathname === \"/category2/groups\") {\n const body = await readBody(request);\n const nature = validCategory2Nature(body.nature),\n code = upperText(body.code),\n name = upperText(body.name);\n if (!nature || !code || !name) {\n return json({\n error: \"NATUREZA, CÓDIGO E NOME DO GRUPO SÃO OBRIGATÓRIOS.\",\n }, 400);\n }\n const id = crypto.randomUUID();\n await sqlite.execute({\n sql:\n `INSERT INTO category2_groups(id,nature,code,name,sort_order,created_at) VALUES(?,?,?,?,(SELECT COALESCE(MAX(sort_order),-1)+1 FROM category2_groups WHERE nature=?),?)`,\n args: [id, nature, code, name, nature, new Date().toISOString()],\n });\n return json({ id }, 201);\n }\n const category2GroupPath = url.pathname.match(\n /^\\/category2\\/groups\\/([0-9a-z.-]+)$/i,\n );\n if (request.method === \"PUT\" && category2GroupPath) {\n const body = await readBody(request);\n const nature = validCategory2Nature(body.nature),\n code = upperText(body.code),\n name = upperText(body.name);\n if (!nature || !code || !name) {\n return json({\n error: \"NATUREZA, CÓDIGO E NOME DO GRUPO SÃO OBRIGATÓRIOS.\",\n }, 400);\n }\n const id = category2GroupPath[1];\n const result = await sqlite.execute({\n sql: `UPDATE category2_groups SET nature=?,code=?,name=? WHERE id=?`,\n args: [nature, code, name, id],\n });\n if (!result.rowsAffected) {\n return json({ error: \"GRUPO NÃO ENCONTRADO.\" }, 404);\n }\n await sqlite.execute({\n sql: `UPDATE category2_items SET nature=? WHERE group_id=?`,\n args: [nature, id],\n });\n return json({ ok: true });\n }\n if (request.method === \"DELETE\" && category2GroupPath) {\n const id = category2GroupPath[1];\n const children = await sqlite.execute({\n sql: `SELECT COUNT(*) AS total FROM category2_items WHERE group_id=?`,\n args: [id],\n });\n if (Number((children.rows[0] as any)?.total) > 0) {\n return json({\n error: \"MOVA OU EXCLUA AS CATEGORIAS DESTE GRUPO PRIMEIRO.\",\n }, 409);\n }\n const result = await sqlite.execute({\n sql: `DELETE FROM category2_groups WHERE id=?`,\n args: [id],\n });\n if (!result.rowsAffected) {\n return json({ error: \"GRUPO NÃO ENCONTRADO.\" }, 404);\n }\n return json({ ok: true });\n }\n if (request.method === \"POST\" && url.pathname === \"/category2/items\") {\n const body = await readBody(request);\n const groupId = String(body.groupId || \"\"),\n group = await requireCategory2Group(groupId);\n const code = upperText(body.code), name = upperText(body.name);\n if (!group || !code || !name) {\n return json({\n error: \"GRUPO, CÓDIGO E NOME DA CATEGORIA SÃO OBRIGATÓRIOS.\",\n }, 400);\n }\n const id = crypto.randomUUID();\n await sqlite.execute({\n sql:\n `INSERT INTO category2_items(id,group_id,nature,code,name,is_reducer,sort_order,created_at) VALUES(?,?,?,?,?,?,(SELECT COALESCE(MAX(sort_order),-1)+1 FROM category2_items WHERE group_id=?),?)`,\n args: [\n id,\n groupId,\n group.nature,\n code,\n name,\n body.isReducer ? 1 : 0,\n groupId,\n new Date().toISOString(),\n ],\n });\n return json({ id }, 201);\n }\n const category2ItemPath = url.pathname.match(\n /^\\/category2\\/items\\/([0-9a-z.-]+)$/i,\n );\n if (request.method === \"PUT\" && category2ItemPath) {\n const body = await readBody(request);\n const groupId = String(body.groupId || \"\"),\n group = await requireCategory2Group(groupId);\n const code = upperText(body.code), name = upperText(body.name);\n if (!group || !code || !name) {\n return json({\n error: \"GRUPO, CÓDIGO E NOME DA CATEGORIA SÃO OBRIGATÓRIOS.\",\n }, 400);\n }\n const result = await sqlite.execute({\n sql:\n `UPDATE category2_items SET group_id=?,nature=?,code=?,name=?,is_reducer=? WHERE id=?`,\n args: [\n groupId,\n group.nature,\n code,\n name,\n body.isReducer ? 1 : 0,\n category2ItemPath[1],\n ],\n });\n if (!result.rowsAffected) {\n return json({ error: \"CATEGORIA NÃO ENCONTRADA.\" }, 404);\n }\n return json({ ok: true });\n }\n if (request.method === \"DELETE\" && category2ItemPath) {\n const id = category2ItemPath[1];\n const linked = await sqlite.execute({\n sql:\n `SELECT COUNT(*) AS total FROM transaction_category2 WHERE category_id=?`,\n args: [id],\n });\n if (Number((linked.rows[0] as any)?.total) > 0) {\n return json({\n error:\n \"ESTA CATEGORIA POSSUI LANÇAMENTOS VINCULADOS E NÃO PODE SER EXCLUÍDA.\",\n }, 409);\n }\n const result = await sqlite.execute({\n sql: `DELETE FROM category2_items WHERE id=?`,\n args: [id],\n });\n if (!result.rowsAffected) {\n return json({ error: \"CATEGORIA NÃO ENCONTRADA.\" }, 404);\n }\n return json({ ok: true });\n }\n if (request.method === \"PUT\" && url.pathname === \"/category2/reorder\") {\n const body = await readBody(request);\n const groups = Array.isArray(body.groups) ? body.groups : [],\n categories = Array.isArray(body.categories) ? body.categories : [];\n const storedGroups =\n (await sqlite.execute(`SELECT id,nature FROM category2_groups`))\n .rows as any[];\n const storedCategories =\n (await sqlite.execute(`SELECT id FROM category2_items`)).rows as any[];\n const groupMap = new Map(\n storedGroups.map((row) => [String(row.id), String(row.nature)]),\n );\n const groupIds = groups.map((row: any) => String(row.id)),\n categoryIds = categories.map((row: any) => String(row.id));\n if (\n new Set(groupIds).size !== storedGroups.length ||\n groupIds.some((id: string) => !groupMap.has(id))\n ) return json({ error: \"ORDEM DE GRUPOS INCOMPLETA OU INVÁLIDA.\" }, 400);\n const storedCategoryIds = new Set(\n storedCategories.map((row) => String(row.id)),\n );\n if (\n new Set(categoryIds).size !== storedCategories.length ||\n categoryIds.some((id: string) => !storedCategoryIds.has(id))\n ) {\n return json(\n { error: \"ORDEM DE CATEGORIAS INCOMPLETA OU INVÁLIDA.\" },\n 400,\n );\n }\n if (categories.some((row: any) => !groupMap.has(String(row.groupId)))) {\n return json({\n error: \"UMA CATEGORIA FOI MOVIDA PARA UM GRUPO INVÁLIDO.\",\n }, 400);\n }\n const statements = [\n ...groups.map((row: any, index: number) => ({\n sql: `UPDATE category2_groups SET sort_order=? WHERE id=?`,\n args: [index, String(row.id)],\n })),\n ...categories.map((row: any, index: number) => ({\n sql:\n `UPDATE category2_items SET group_id=?,nature=?,code=?,sort_order=? WHERE id=?`,\n args: [\n String(row.groupId),\n groupMap.get(String(row.groupId)),\n upperText(row.code),\n Number(row.sortOrder ?? index),\n String(row.id),\n ],\n })),\n ];\n await executeBatches(statements);\n return json({ ok: true });\n }\n if (request.method === \"PUT\" && url.pathname === \"/transaction-category2\") {\n const body = await readBody(request);\n const transactionId = String(body.transactionId || \"\"),\n categoryId = String(body.categoryId || \"\");\n const transaction = await sqlite.execute({\n sql: `SELECT id FROM transactions WHERE id=?`,\n args: [transactionId],\n });\n if (!transaction.rows.length) {\n return json({ error: \"LANÇAMENTO NÃO ENCONTRADO.\" }, 404);\n }\n if (!categoryId) {\n await sqlite.execute({\n sql: `DELETE FROM transaction_category2 WHERE transaction_id=?`,\n args: [transactionId],\n });\n } else {\n const category = await sqlite.execute({\n sql: `SELECT id FROM category2_items WHERE id=?`,\n args: [categoryId],\n });\n if (!category.rows.length) {\n return json({ error: \"CATEGORIA 2 NÃO ENCONTRADA.\" }, 404);\n }\n await sqlite.execute({\n sql:\n `INSERT INTO transaction_category2(transaction_id,category_id,updated_at) VALUES(?,?,?) ON CONFLICT(transaction_id) DO UPDATE SET category_id=excluded.category_id,updated_at=excluded.updated_at`,\n args: [transactionId, categoryId, new Date().toISOString()],\n });\n }\n return json({ ok: true });\n }\n if (\n request.method === \"PUT\" && url.pathname === \"/transaction-category2/bulk\"\n ) {\n const body = await readBody(request);\n const ids = Array.isArray(body.transactionIds)\n ? [...new Set(body.transactionIds.map(String).filter(Boolean))]\n : [];\n const categoryId = String(body.categoryId || \"\");\n if (!ids.length) {\n return json({ error: \"NENHUM LANÇAMENTO INFORMADO.\" }, 400);\n }\n if (ids.length > 1000) {\n return json({ error: \"NO MÁXIMO 1.000 LANÇAMENTOS POR VEZ.\" }, 400);\n }\n if (categoryId) {\n const category = await sqlite.execute({\n sql: `SELECT id FROM category2_items WHERE id=?`,\n args: [categoryId],\n });\n if (!category.rows.length) {\n return json({ error: \"CATEGORIA 2 NÃO ENCONTRADA.\" }, 404);\n }\n }\n const now = new Date().toISOString();\n // O INSERT...SELECT ignora id inexistente. reviewed fica intocado de propósito:\n // classificar em lote não é conferir em lote.\n await executeBatches(ids.map((id) =>\n categoryId\n ? {\n sql:\n `INSERT INTO transaction_category2(transaction_id,category_id,updated_at) SELECT id,?,? FROM transactions WHERE id=? ON CONFLICT(transaction_id) DO UPDATE SET category_id=excluded.category_id,updated_at=excluded.updated_at`,\n args: [categoryId, now, id],\n }\n : {\n sql: `DELETE FROM transaction_category2 WHERE transaction_id=?`,\n args: [id],\n }\n ));\n return json({ ok: true, count: ids.length });\n }\n if (request.method === \"GET\" && url.pathname === \"/transfers\") {\n return json(await listTransfers());\n }\n if (request.method === \"GET\" && url.pathname === \"/transfer-suggestions\") {\n return json(await suggestTransfers());\n }\n if (request.method === \"POST\" && url.pathname === \"/transfers\") {\n const body = await readBody(request);\n const kind = body.kind === \"manual\" ? \"manual\" : \"linked\";\n const note = String(body.note || \"\").trim().slice(0, 300) || null;\n const id = crypto.randomUUID(), now = new Date().toISOString();\n if (kind === \"linked\") {\n const fromId = String(body.fromTransactionId || \"\"),\n toId = String(body.toTransactionId || \"\");\n if (!fromId || !toId || fromId === toId) {\n return json(\n { error: \"Informe os dois lados da transferência.\" },\n 400,\n );\n }\n const legs = await sqlite.execute({\n sql:\n `SELECT id,type,date,amount,account_id AS accountId FROM transactions WHERE id IN (?,?)`,\n args: [fromId, toId],\n });\n const from = (legs.rows as any[]).find((row) =>\n String(row.id) === fromId\n );\n const to = (legs.rows as any[]).find((row) => String(row.id) === toId);\n if (!from || !to) {\n return json({ error: \"Lançamento não encontrado.\" }, 404);\n }\n if (from.type !== \"DEBIT\" || to.type !== \"CREDIT\") {\n return json({\n error:\n \"A transferência precisa de uma saída (débito) e uma entrada (crédito).\",\n }, 400);\n }\n if (String(from.accountId) === String(to.accountId)) {\n return json({ error: \"Os dois lados estão na mesma conta.\" }, 400);\n }\n const used = await sqlite.execute({\n sql:\n `SELECT id FROM transfers WHERE from_transaction_id IN (?,?) OR to_transaction_id IN (?,?)`,\n args: [fromId, toId, fromId, toId],\n });\n if (used.rows.length) {\n return json({\n error: \"Um desses lançamentos já faz parte de outra transferência.\",\n }, 409);\n }\n await sqlite.execute({\n sql:\n `INSERT INTO transfers(id,kind,date,amount,from_transaction_id,to_transaction_id,note,created_at) VALUES(?,'linked',?,?,?,?,?,?)`,\n args: [\n id,\n String(from.date).slice(0, 10),\n Math.abs(Number(from.amount)) || 0,\n fromId,\n toId,\n note,\n now,\n ],\n });\n return json({ id, kind }, 201);\n }\n const fromWalletId = String(body.fromWalletId || \"\"),\n toWalletId = String(body.toWalletId || \"\");\n const amount = Number(body.amount), date = String(body.date || \"\").trim();\n if (!fromWalletId || !toWalletId || fromWalletId === toWalletId) {\n return json({ error: \"Escolha duas carteiras diferentes.\" }, 400);\n }\n if (!Number.isFinite(amount) || amount <= 0) {\n return json({ error: \"Informe um valor maior que zero.\" }, 400);\n }\n if (!/^\\d{4}-\\d{2}-\\d{2}$/.test(date)) {\n return json({ error: \"Informe a data no formato AAAA-MM-DD.\" }, 400);\n }\n const wallets = await sqlite.execute({\n sql: `SELECT id FROM wallets WHERE id IN (?,?)`,\n args: [fromWalletId, toWalletId],\n });\n if (wallets.rows.length !== 2) {\n return json({ error: \"Carteira não encontrada.\" }, 404);\n }\n await sqlite.execute({\n sql:\n `INSERT INTO transfers(id,kind,date,amount,from_wallet_id,to_wallet_id,note,created_at) VALUES(?,'manual',?,?,?,?,?,?)`,\n args: [id, date, amount, fromWalletId, toWalletId, note, now],\n });\n return json({ id, kind }, 201);\n }\n const transferPath = url.pathname.match(/^\\/transfers\\/([0-9a-f-]+)$/i);\n if (request.method === \"DELETE\" && transferPath) {\n const result = await sqlite.execute({\n sql: `DELETE FROM transfers WHERE id=?`,\n args: [transferPath[1]],\n });\n if (!result.rowsAffected) {\n return json({ error: \"Transferência não encontrada.\" }, 404);\n }\n return json({ ok: true });\n }\n if (request.method === \"GET\" && url.pathname === \"/category-map\") {\n return json(await listCategoryMap());\n }\n if (request.method === \"POST\" && url.pathname === \"/category-map/apply\") {\n return json(await applyCategoryMap());\n }\n if (request.method === \"PUT\" && url.pathname === \"/category-map\") {\n const body = await readBody(request);\n const pluggyCategory = String(body.pluggyCategory || \"\").trim();\n const categoryId = String(body.categoryId || \"\");\n if (!pluggyCategory) {\n return json({ error: \"INFORME A CATEGORIA DA PLUGGY.\" }, 400);\n }\n if (!categoryId) {\n await sqlite.execute({\n sql: `DELETE FROM pluggy_category_map WHERE pluggy_category=?`,\n args: [pluggyCategory],\n });\n return json({ ok: true, removed: true });\n }\n const category = await sqlite.execute({\n sql: `SELECT id FROM category2_items WHERE id=?`,\n args: [categoryId],\n });\n if (!category.rows.length) {\n return json({ error: \"CATEGORIA 2 NÃO ENCONTRADA.\" }, 404);\n }\n await sqlite.execute({\n sql:\n `INSERT INTO pluggy_category_map(pluggy_category,category_id,updated_at) VALUES(?,?,?) ON CONFLICT(pluggy_category) DO UPDATE SET category_id=excluded.category_id,updated_at=excluded.updated_at`,\n args: [pluggyCategory, categoryId, new Date().toISOString()],\n });\n return json({ ok: true });\n }\n if (request.method === \"PUT\" && url.pathname === \"/transaction-reviewed\") {\n const body = await readBody(request);\n const transactionId = String(body.transactionId || \"\");\n const reviewed = body.reviewed === true || body.reviewed === 1;\n const transaction = await sqlite.execute({\n sql: `SELECT id FROM transactions WHERE id=?`,\n args: [transactionId],\n });\n if (!transaction.rows.length) {\n return json({ error: \"LANÇAMENTO NÃO ENCONTRADO.\" }, 404);\n }\n await sqlite.execute({\n sql: `UPDATE transactions SET reviewed=?,reviewed_at=? WHERE id=?`,\n args: [\n reviewed ? 1 : 0,\n reviewed ? new Date().toISOString() : null,\n transactionId,\n ],\n });\n return json({ ok: true, reviewed });\n }\n if (request.method === \"PUT\" && url.pathname === \"/transaction-note\") {\n const body = await readBody(request);\n const transactionId = String(body.transactionId || \"\");\n const manualNote = String(body.manualNote || \"\").trim();\n if (manualNote.length > 1000) {\n return json({\n error: \"A observação pode ter no máximo 1.000 caracteres.\",\n }, 400);\n }\n const transaction = await sqlite.execute({\n sql: `SELECT id FROM transactions WHERE id=?`,\n args: [transactionId],\n });\n if (!transaction.rows.length) {\n return json({ error: \"LANÇAMENTO NÃO ENCONTRADO.\" }, 404);\n }\n await sqlite.execute({\n sql:\n `UPDATE transactions SET manual_note=?,manual_note_updated_at=? WHERE id=?`,\n args: [manualNote || null, new Date().toISOString(), transactionId],\n });\n return json({ ok: true, manualNote });\n }\n if (request.method === \"POST\" && url.pathname === \"/wallets\") {\n const body = await readBody(request);\n const name = String(body.name || \"\").trim();\n if (!name) return json({ error: \"Informe o nome da carteira.\" }, 400);\n const id = crypto.randomUUID();\n await sqlite.execute({\n sql:\n `INSERT INTO wallets(id,name,created_at,sort_order) VALUES(?,?,?,(SELECT COALESCE(MAX(sort_order),0)+1 FROM wallets))`,\n args: [id, name, new Date().toISOString()],\n });\n return json({ id, name }, 201);\n }\n if (request.method === \"PUT\" && url.pathname === \"/wallets/reorder\") {\n const body = await readBody(request);\n const walletIds = Array.isArray(body.walletIds)\n ? body.walletIds.map(String)\n : [];\n if (!walletIds.length) {\n return json({ error: \"Nenhuma carteira informada para ordenar.\" }, 400);\n }\n if (new Set(walletIds).size !== walletIds.length) {\n return json(\n { error: \"A lista de carteiras contém IDs repetidos.\" },\n 400,\n );\n }\n const storedWallets = await sqlite.execute(`SELECT id FROM wallets`);\n const storedIds = new Set(\n (storedWallets.rows as any[]).map((row) => String(row.id)),\n );\n if (\n storedIds.size !== walletIds.length ||\n walletIds.some((id) => !storedIds.has(id))\n ) {\n return json({\n error: \"A lista de carteiras está incompleta ou inválida.\",\n }, 400);\n }\n await sqlite.batch(\n walletIds.map((id, index) => ({\n sql: `UPDATE wallets SET sort_order=? WHERE id=?`,\n args: [index, id],\n })),\n );\n return json({ ok: true });\n }\n const walletPath = url.pathname.match(/^\\/wallets\\/([0-9a-f-]+)$/i);\n if (request.method === \"PUT\" && walletPath) {\n const body = await readBody(request);\n const name = String(body.name || \"\").trim();\n if (!name) {\n return json({ error: \"Informe o novo nome da carteira.\" }, 400);\n }\n const result = await sqlite.execute({\n sql: `UPDATE wallets SET name=? WHERE id=?`,\n args: [name, walletPath[1]],\n });\n if (!result.rowsAffected) {\n return json({ error: \"Carteira não encontrada.\" }, 404);\n }\n return json({ ok: true });\n }\n if (request.method === \"DELETE\" && walletPath) {\n await sqlite.batch([\n {\n sql: `DELETE FROM account_wallet_mapping WHERE wallet_id=?`,\n args: [walletPath[1]],\n },\n {\n sql: `DELETE FROM investment_wallet_mapping WHERE wallet_id=?`,\n args: [walletPath[1]],\n },\n { sql: `DELETE FROM wallets WHERE id=?`, args: [walletPath[1]] },\n ]);\n return json({ ok: true });\n }\n if (request.method === \"PUT\" && url.pathname === \"/wallet-mapping\") {\n const body = await readBody(request);\n const accountId = String(body.accountId || \"\");\n const walletId = String(body.walletId || \"\");\n if (!accountId) return json({ error: \"Conta inválida.\" }, 400);\n const entityType = body.entityType === \"investment\"\n ? \"investment\"\n : \"account\";\n const table = entityType === \"investment\"\n ? \"investment_wallet_mapping\"\n : \"account_wallet_mapping\";\n const column = entityType === \"investment\"\n ? \"investment_id\"\n : \"account_id\";\n if (!walletId) {\n await sqlite.execute({\n sql: `DELETE FROM ${table} WHERE ${column}=?`,\n args: [accountId],\n });\n } else {await sqlite.execute({\n sql:\n `INSERT INTO ${table}(${column},wallet_id,updated_at) VALUES(?,?,?) ON CONFLICT(${column}) DO UPDATE SET wallet_id=excluded.wallet_id,updated_at=excluded.updated_at`,\n args: [accountId, walletId, new Date().toISOString()],\n });}\n return json({ ok: true });\n }\n return json({ error: \"Rota não encontrada.\" }, 404);\n } catch (error) {\n console.error(error);\n const message = error instanceof Error\n ? error.message\n : \"Erro inesperado no backend.\";\n return json(\n { error: message },\n /UNIQUE constraint failed/i.test(message) ? 409 : 500,\n );\n }\n}","createdAt",["D",1788195562601],"branchVersion","branchId","69282a9b-8bc7-445a-a7ce-81d840fa597c","id","4b4a41c3-1181-46c5-aa33-d8a4b741f97b","FINANCAS",["D",1787844304507],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","","imageUrl","b6cb00f2-3fad-4699-a03a-e0e430f69dd5","pinnedFileId","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",0,"orgId","019faa2d-a13d-705a-b623-f22f372371e8","tursoName","4b4a41c3118146c5aa33d8a4b741f97b","tursoHostname","4b4a41c3118146c5aa33d8a4b741f97b-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789511441255],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_99":98,"_107":103},"org",{"_99":98,"_100":101,"_102":103,"_18":55,"_104":105,"_106":25},"branch",{"_13":88,"_32":89,"_43":42,"_90":44,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":45,"_97":98},"isOwned",26,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","otavio2404","handle","displayName","Otavio Martins","avatar","https://lh3.googleusercontent.com/a/ACg8ocLYMUOct65Fi4wwERA14BKyNAD3lsJXr5sJtIz10SwUaPtEHw=s96-c","tier","free","canRestrictVals","profileImageUrl","branchName","adoptOrgId",{"_43":44,"_32":45,"_38":154,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":31,"_57":58,"_59":-5,"_60":-5,"_61":62,"_63":64,"_65":66,"_67":68,"_69":155,"_71":-5,"_72":73,"_74":73,"_75":76,"_77":76,"_78":76,"_79":73,"_80":73,"_81":156,"_83":157,"_85":158,"_87":25},"tree",[149],"files",[149],"counts",{"_137":62,"_138":139,"_140":62,"_141":142,"_143":144},"contents",[129],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_43":130,"_32":28,"_30":31,"_131":-5,"_41":42,"_40":14,"_132":25,"_38":133,"_13":134,"_33":34,"_35":37,"_24":25,"_135":-5,"_27":136},"f1e1d7e6-a0e2-4219-adbc-14e001da3320","parentId","latest",["D",1788195562601],24,"label",[28],"pulls","branches",1,"forks","envVars",{"_145":147,"_148":62},"connections",{"_145":62,"_146":62},"count","unfilledCount",7,"emptyCount",{"_43":130,"_32":28,"_30":31,"_131":-5,"_41":42,"_40":14,"_132":25,"_38":150,"_13":134,"_33":34,"_35":37,"_24":25,"_135":-5,"_151":152,"_27":153},["D",1788195562601],"children",[],[28],["D",1787844304507],["D",1789511441255],{"_99":98,"_107":103},{"_99":98,"_100":101,"_102":103,"_18":55,"_104":105,"_106":25},{"_13":88,"_32":89,"_43":42,"_90":44,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":45,"_97":98},"ctx",{"_81":-5,"_126":192,"_193":194,"_195":196,"_197":25,"_198":25,"_199":25,"_200":58,"_201":-5,"_202":-7,"_203":-7,"_204":62,"_205":25,"_206":25,"_207":-5,"_208":25,"_209":25,"_210":25,"_211":-5},"ENV",{"_163":164,"_165":166,"_167":89,"_168":169,"_170":171,"_172":173,"_174":175,"_176":177,"_178":179,"_180":181,"_182":183,"_184":185,"_186":187,"_188":189,"_190":191},"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",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Diff · v25