[{"_1":2,"_175":-5,"_176":-5},"loaderData",{"_3":4,"_92":93},"root",{"_5":6,"_35":36,"_65":66},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20,"_34":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","notificationCount",0,"flagDenols","isTeamOrgMember","ENV",{"_37":38,"_39":40,"_41":42,"_43":44,"_45":46,"_47":48,"_49":50,"_51":52,"_53":54,"_55":56,"_57":58,"_59":60,"_61":62,"_63":64},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","5aae8c594f4ceb0bfd0f0c6fa62a207a75f96ff1","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","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","API_URL","https://api.val.town","clerkState",{"_67":68},"__internal_clerk_state",{"_69":70,"_71":46,"_72":73,"_74":73,"_75":20,"_76":73,"_77":73,"_78":73,"_79":73,"_80":81,"_90":20,"_91":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_82":20,"_19":20,"_83":73,"_84":85,"_86":73,"_87":46,"_88":20,"_89":73},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_94":95,"_96":97,"_98":99,"_100":101,"_150":151,"_172":173,"_174":-5},"handle","sakwah","name","NdaulaPM","path","backend/database/queries.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":-5,"_111":-5,"_12":112,"_113":114,"_115":116,"_117":116,"_118":-5,"_119":32,"_14":120,"_121":122,"_123":122,"_124":125,"_126":125,"_127":125,"_128":122,"_129":122,"_23":130,"_133":134,"_136":137,"_149":20},"id","f9072a0c-3fab-11f0-8ba7-76b3cceeab13","createdAt",["D",1748866829842],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","1214b5b0-3fab-11f0-b6e9-ab0b2f88b858","pinnedFileId","8de99538-3fac-11f0-b7af-76b3cceeab13","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","c81ae74e-0bae-4445-97ed-1d826ddd8812","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_131":132},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yeHg0VnNVTHM1VEhha21ldTNTQk5VMnVEekgifQ","org",{"_94":95,"_135":132,"_12":112},"avatar","branch",{"_138":139,"_96":44,"_102":140,"_141":103,"_142":-5,"_143":-5,"_144":-5,"_145":-5,"_146":-5,"_147":97,"_148":95},"version",19,"f9077912-3fab-11f0-8ba7-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":152,"_96":153,"_154":155,"_156":157,"_158":140,"_159":160,"_161":116,"_104":162,"_138":32,"_163":164,"_165":166,"_167":20,"_168":-5,"_98":169},"75e2edb8-3fac-11f0-8185-76b3cceeab13","queries.ts","fileId","75e2ee58-3fac-11f0-8185-76b3cceeab13","parentId","e1d1b0f1-3887-4e65-a63a-f5f85db4535b","branchId","branchVersion",4,"latest",["D",1748867039315],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { \n Tenant, \n RentPayment, \n MaintenanceCost, \n StaffPayment, \n TaxCategory, \n FinancialSummary,\n MonthlyReport \n} from \"../../shared/types.ts\";\n\n// Tenant queries\nexport async function getAllTenants(): Promise {\n const result = await sqlite.execute(\"SELECT * FROM tenants ORDER BY unit\");\n return result.rows as Tenant[];\n}\n\nexport async function createTenant(tenant: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO tenants (name, email, phone, unit, lease_start, lease_end, monthly_rent, security_deposit)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)\n `, [tenant.name, tenant.email, tenant.phone, tenant.unit, tenant.lease_start, tenant.lease_end, tenant.monthly_rent, tenant.security_deposit]);\n return result.lastInsertRowId as number;\n}\n\n// Rent payment queries\nexport async function getAllRentPayments(): Promise {\n const result = await sqlite.execute(`\n SELECT rp.*, t.name as tenant_name \n FROM rent_payments rp \n JOIN tenants t ON rp.tenant_id = t.id \n ORDER BY rp.due_date DESC\n `);\n return result.rows as RentPayment[];\n}\n\nexport async function createRentPayment(payment: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO rent_payments (tenant_id, amount, due_date, paid_date, payment_method, notes, status)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n `, [payment.tenant_id, payment.amount, payment.due_date, payment.paid_date, payment.payment_method, payment.notes, payment.status]);\n return result.lastInsertRowId as number;\n}\n\nexport async function updateRentPayment(id: number, updates: Partial): Promise {\n const fields = Object.keys(updates).filter(key => key !== 'id' && key !== 'created_at' && key !== 'tenant_name');\n const setClause = fields.map(field => `${field} = ?`).join(', ');\n const values = fields.map(field => updates[field as keyof RentPayment]);\n \n await sqlite.execute(`UPDATE rent_payments SET ${setClause} WHERE id = ?`, [...values, id]);\n}\n\n// Maintenance cost queries\nexport async function getAllMaintenanceCosts(): Promise {\n const result = await sqlite.execute(\"SELECT * FROM maintenance_costs ORDER BY date DESC\");\n return result.rows as MaintenanceCost[];\n}\n\nexport async function createMaintenanceCost(cost: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO maintenance_costs (description, category, amount, date, vendor, unit, receipt_url, tax_deductible)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)\n `, [cost.description, cost.category, cost.amount, cost.date, cost.vendor, cost.unit, cost.receipt_url, cost.tax_deductible]);\n return result.lastInsertRowId as number;\n}\n\n// Staff payment queries\nexport async function getAllStaffPayments(): Promise {\n const result = await sqlite.execute(\"SELECT * FROM staff_payments ORDER BY payment_date DESC\");\n return result.rows as StaffPayment[];\n}\n\nexport async function createStaffPayment(payment: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO staff_payments (staff_name, role, amount, payment_date, payment_type, hours_worked, hourly_rate, description, tax_form_type)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [payment.staff_name, payment.role, payment.amount, payment.payment_date, payment.payment_type, payment.hours_worked, payment.hourly_rate, payment.description, payment.tax_form_type]);\n return result.lastInsertRowId as number;\n}\n\n// Tax category queries\nexport async function getAllTaxCategories(): Promise {\n const result = await sqlite.execute(\"SELECT * FROM tax_categories ORDER BY name\");\n return result.rows as TaxCategory[];\n}\n\n// Financial summary queries\nexport async function getFinancialSummary(): Promise {\n const [rentResult, maintenanceResult, staffResult, pendingResult, taxResult] = await Promise.all([\n sqlite.execute(\"SELECT COALESCE(SUM(amount), 0) as total FROM rent_payments WHERE status = 'paid'\"),\n sqlite.execute(\"SELECT COALESCE(SUM(amount), 0) as total FROM maintenance_costs\"),\n sqlite.execute(\"SELECT COALESCE(SUM(amount), 0) as total FROM staff_payments\"),\n sqlite.execute(\"SELECT COALESCE(SUM(amount), 0) as total FROM rent_payments WHERE status IN ('pending', 'late')\"),\n sqlite.execute(\"SELECT COALESCE(SUM(amount), 0) as total FROM maintenance_costs WHERE tax_deductible = 1\")\n ]);\n\n const totalRentCollected = (rentResult.rows[0] as any).total;\n const totalMaintenanceCosts = (maintenanceResult.rows[0] as any).total;\n const totalStaffPayments = (staffResult.rows[0] as any).total;\n const pendingRent = (pendingResult.rows[0] as any).total;\n const taxDeductibleExpenses = (taxResult.rows[0] as any).total + totalStaffPayments;\n\n return {\n total_rent_collected: totalRentCollected,\n total_maintenance_costs: totalMaintenanceCosts,\n total_staff_payments: totalStaffPayments,\n net_income: totalRentCollected - totalMaintenanceCosts - totalStaffPayments,\n tax_deductible_expenses: taxDeductibleExpenses,\n pending_rent: pendingRent\n };\n}\n\n// Monthly report queries\nexport async function getMonthlyReports(year: number): Promise {\n const result = await sqlite.execute(`\n WITH months AS (\n SELECT 1 as month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION \n SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION \n SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12\n ),\n rent_income AS (\n SELECT \n strftime('%m', paid_date) as month,\n COALESCE(SUM(amount), 0) as income\n FROM rent_payments \n WHERE strftime('%Y', paid_date) = ? AND status = 'paid'\n GROUP BY strftime('%m', paid_date)\n ),\n maintenance_expenses AS (\n SELECT \n strftime('%m', date) as month,\n COALESCE(SUM(amount), 0) as expenses\n FROM maintenance_costs \n WHERE strftime('%Y', date) = ?\n GROUP BY strftime('%m', date)\n ),\n staff_expenses AS (\n SELECT \n strftime('%m', payment_date) as month,\n COALESCE(SUM(amount), 0) as expenses\n FROM staff_payments \n WHERE strftime('%Y', payment_date) = ?\n GROUP BY strftime('%m', payment_date)\n ),\n tax_deductions AS (\n SELECT \n strftime('%m', date) as month,\n COALESCE(SUM(amount), 0) as deductions\n FROM maintenance_costs \n WHERE strftime('%Y', date) = ? AND tax_deductible = 1\n GROUP BY strftime('%m', date)\n )\n SELECT \n printf('%02d', m.month) as month,\n ? as year,\n COALESCE(ri.income, 0) as rent_income,\n COALESCE(me.expenses, 0) as maintenance_expenses,\n COALESCE(se.expenses, 0) as staff_expenses,\n COALESCE(ri.income, 0) - COALESCE(me.expenses, 0) - COALESCE(se.expenses, 0) as net_profit,\n COALESCE(td.deductions, 0) + COALESCE(se.expenses, 0) as tax_deductions\n FROM months m\n LEFT JOIN rent_income ri ON printf('%02d', m.month) = ri.month\n LEFT JOIN maintenance_expenses me ON printf('%02d', m.month) = me.month\n LEFT JOIN staff_expenses se ON printf('%02d', m.month) = se.month\n LEFT JOIN tax_deductions td ON printf('%02d', m.month) = td.month\n ORDER BY m.month\n `, [year.toString(), year.toString(), year.toString(), year.toString(), year]);\n\n return result.rows as MonthlyReport[];\n}","deleted","label",[170,171,153],"backend","database","permalink","https://www.val.town/x/sakwah/NdaulaPM/code/backend/database/queries.ts","extension","actionData","errors"]