[{"_1":2,"_172":-5,"_173":-5},"loaderData",{"_3":4,"_91":92},"root",{"_5":6,"_34":35,"_64":65},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":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","ENV",{"_36":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},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","4049fd8e684531193de8d0777b9ab11a81dae27e","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",{"_66":67},"__internal_clerk_state",{"_68":69,"_70":45,"_71":72,"_73":72,"_74":20,"_75":72,"_76":72,"_77":72,"_78":72,"_79":80,"_89":20,"_90":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",{"_81":20,"_19":20,"_82":72,"_83":84,"_85":72,"_86":45,"_87":20,"_88":72},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_93":94,"_95":96,"_97":98,"_99":100,"_146":147,"_169":170,"_171":-5},"handle","priyanshu2307","name","untitled-7020","path","backend/database/queries.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":-5,"_110":-5,"_12":111,"_112":-5,"_113":20,"_114":-5,"_115":32,"_14":116,"_117":118,"_119":118,"_120":121,"_122":121,"_123":121,"_124":118,"_125":118,"_23":126,"_129":130,"_132":133,"_145":20},"id","a82574ee-3d72-11f0-81d4-76b3cceeab13","createdAt",["D",1748622310505],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","6cd7bf50-3d72-11f0-95a9-57761b0d8d23","pinnedFileId","pinPreview","screenshotVersion","likeCount","497815fb-ce53-4192-99d5-4acbeeceb0c3","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4cDVTWU1PelBxb1RVVWdRbXhGYmRSaUtzUCJ9","org",{"_93":94,"_131":128,"_12":111},"avatar","branch",{"_134":135,"_95":43,"_101":136,"_137":102,"_138":-5,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":96,"_144":94},"version",21,"a825d434-3d72-11f0-81d4-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":148,"_95":149,"_150":151,"_152":153,"_154":136,"_155":156,"_157":158,"_103":159,"_134":32,"_160":161,"_162":163,"_164":20,"_165":-5,"_97":166},"487ff018-3d73-11f0-aa85-76b3cceeab13","queries.ts","fileId","487ff0e0-3d73-11f0-aa85-76b3cceeab13","parentId","2d0e823d-dcaa-438e-ac27-2b1dedda0472","branchId","branchVersion",5,"latest",true,["D",1748622579529],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport type { User, Book, Transaction, Fine, AuditLog, DashboardStats } from \"../../shared/types.ts\";\n\n// User queries\nexport async function getUserByEmail(email: string): Promise {\n const result = await sqlite.execute(\"SELECT * FROM users WHERE email = ?\", [email]);\n return result[0] as User || null;\n}\n\nexport async function getUserById(id: number): Promise {\n const result = await sqlite.execute(\"SELECT * FROM users WHERE id = ?\", [id]);\n return result[0] as User || null;\n}\n\nexport async function createUser(userData: {\n email: string;\n password_hash: string;\n name: string;\n role: string;\n phone?: string;\n address?: string;\n}): Promise {\n const result = await sqlite.execute(`\n INSERT INTO users (email, password_hash, name, role, phone, address)\n VALUES (?, ?, ?, ?, ?, ?)\n `, [userData.email, userData.password_hash, userData.name, userData.role, userData.phone, userData.address]);\n \n return result.lastInsertRowId as number;\n}\n\nexport async function getAllUsers(): Promise {\n const result = await sqlite.execute(\"SELECT * FROM users ORDER BY created_at DESC\");\n return result as User[];\n}\n\nexport async function updateUser(id: number, updates: Partial): Promise {\n const fields = Object.keys(updates).filter(key => key !== 'id').map(key => `${key} = ?`);\n const values = Object.values(updates).filter((_, index) => Object.keys(updates)[index] !== 'id');\n \n if (fields.length > 0) {\n await sqlite.execute(\n `UPDATE users SET ${fields.join(', ')} WHERE id = ?`,\n [...values, id]\n );\n }\n}\n\n// Book queries\nexport async function getAllBooks(): Promise {\n const result = await sqlite.execute(\"SELECT * FROM books ORDER BY title\");\n return result as Book[];\n}\n\nexport async function getBookById(id: number): Promise {\n const result = await sqlite.execute(\"SELECT * FROM books WHERE id = ?\", [id]);\n return result[0] as Book || null;\n}\n\nexport async function getBookByISBN(isbn: string): Promise {\n const result = await sqlite.execute(\"SELECT * FROM books WHERE isbn = ?\", [isbn]);\n return result[0] as Book || null;\n}\n\nexport async function searchBooks(params: {\n query?: string;\n author?: string;\n genre?: string;\n subject?: string;\n level?: string;\n available_only?: boolean;\n}): Promise {\n let sql = \"SELECT * FROM books WHERE 1=1\";\n const values: any[] = [];\n\n if (params.query) {\n sql += \" AND (title LIKE ? OR author LIKE ? OR isbn LIKE ?)\";\n const searchTerm = `%${params.query}%`;\n values.push(searchTerm, searchTerm, searchTerm);\n }\n\n if (params.author) {\n sql += \" AND author LIKE ?\";\n values.push(`%${params.author}%`);\n }\n\n if (params.genre) {\n sql += \" AND genre = ?\";\n values.push(params.genre);\n }\n\n if (params.subject) {\n sql += \" AND subject = ?\";\n values.push(params.subject);\n }\n\n if (params.level) {\n sql += \" AND level = ?\";\n values.push(params.level);\n }\n\n if (params.available_only) {\n sql += \" AND available_copies > 0\";\n }\n\n sql += \" ORDER BY title\";\n\n const result = await sqlite.execute(sql, values);\n return result as Book[];\n}\n\nexport async function createBook(bookData: Omit): Promise {\n const result = await sqlite.execute(`\n INSERT INTO books (isbn, title, author, publisher, edition, genre, subject, level, \n total_copies, available_copies, location, description, cover_url)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n bookData.isbn, bookData.title, bookData.author, bookData.publisher, bookData.edition,\n bookData.genre, bookData.subject, bookData.level, bookData.total_copies,\n bookData.available_copies, bookData.location, bookData.description, bookData.cover_url\n ]);\n \n return result.lastInsertRowId as number;\n}\n\nexport async function updateBook(id: number, updates: Partial): Promise {\n const fields = Object.keys(updates).filter(key => !['id', 'created_at'].includes(key)).map(key => `${key} = ?`);\n const values = Object.values(updates).filter((_, index) => !['id', 'created_at'].includes(Object.keys(updates)[index]));\n \n if (fields.length > 0) {\n fields.push('updated_at = CURRENT_TIMESTAMP');\n await sqlite.execute(\n `UPDATE books SET ${fields.join(', ')} WHERE id = ?`,\n [...values, id]\n );\n }\n}\n\nexport async function deleteBook(id: number): Promise {\n await sqlite.execute(\"DELETE FROM books WHERE id = ?\", [id]);\n}\n\n// Transaction queries\nexport async function createTransaction(transactionData: {\n user_id: number;\n book_id: number;\n type: string;\n issue_date: string;\n due_date: string;\n notes?: string;\n}): Promise {\n const result = await sqlite.execute(`\n INSERT INTO transactions (user_id, book_id, type, issue_date, due_date, status, notes)\n VALUES (?, ?, ?, ?, ?, 'active', ?)\n `, [\n transactionData.user_id, transactionData.book_id, transactionData.type,\n transactionData.issue_date, transactionData.due_date, transactionData.notes\n ]);\n \n return result.lastInsertRowId as number;\n}\n\nexport async function getActiveTransactions(userId?: number): Promise {\n let sql = `\n SELECT t.*, u.name as user_name, u.email as user_email, b.title as book_title, b.author as book_author\n FROM transactions t\n JOIN users u ON t.user_id = u.id\n JOIN books b ON t.book_id = b.id\n WHERE t.status = 'active'\n `;\n const values: any[] = [];\n\n if (userId) {\n sql += \" AND t.user_id = ?\";\n values.push(userId);\n }\n\n sql += \" ORDER BY t.due_date ASC\";\n\n const result = await sqlite.execute(sql, values);\n return result as Transaction[];\n}\n\nexport async function getTransactionHistory(userId?: number): Promise {\n let sql = `\n SELECT t.*, u.name as user_name, u.email as user_email, b.title as book_title, b.author as book_author\n FROM transactions t\n JOIN users u ON t.user_id = u.id\n JOIN books b ON t.book_id = b.id\n `;\n const values: any[] = [];\n\n if (userId) {\n sql += \" WHERE t.user_id = ?\";\n values.push(userId);\n }\n\n sql += \" ORDER BY t.created_at DESC\";\n\n const result = await sqlite.execute(sql, values);\n return result as Transaction[];\n}\n\nexport async function returnBook(transactionId: number, returnDate: string): Promise {\n await sqlite.execute(`\n UPDATE transactions \n SET status = 'returned', return_date = ?\n WHERE id = ? AND status = 'active'\n `, [returnDate, transactionId]);\n}\n\nexport async function updateOverdueTransactions(): Promise {\n await sqlite.execute(`\n UPDATE transactions \n SET status = 'overdue'\n WHERE status = 'active' AND due_date < datetime('now')\n `);\n}\n\n// Fine queries\nexport async function createFine(fineData: {\n user_id: number;\n transaction_id: number;\n amount: number;\n reason: string;\n}): Promise {\n const result = await sqlite.execute(`\n INSERT INTO fines (user_id, transaction_id, amount, reason)\n VALUES (?, ?, ?, ?)\n `, [fineData.user_id, fineData.transaction_id, fineData.amount, fineData.reason]);\n \n return result.lastInsertRowId as number;\n}\n\nexport async function getUserFines(userId: number): Promise {\n const result = await sqlite.execute(`\n SELECT f.*, t.issue_date, t.due_date, b.title as book_title\n FROM fines f\n JOIN transactions t ON f.transaction_id = t.id\n JOIN books b ON t.book_id = b.id\n WHERE f.user_id = ?\n ORDER BY f.created_at DESC\n `, [userId]);\n return result as Fine[];\n}\n\nexport async function payFine(fineId: number): Promise {\n await sqlite.execute(`\n UPDATE fines \n SET paid = TRUE, paid_date = CURRENT_TIMESTAMP\n WHERE id = ?\n `, [fineId]);\n}\n\n// Audit log queries\nexport async function createAuditLog(logData: {\n user_id: number;\n action: string;\n entity_type: string;\n entity_id: number;\n details?: string;\n}): Promise {\n await sqlite.execute(`\n INSERT INTO audit_logs (user_id, action, entity_type, entity_id, details)\n VALUES (?, ?, ?, ?, ?)\n `, [logData.user_id, logData.action, logData.entity_type, logData.entity_id, logData.details]);\n}\n\nexport async function getAuditLogs(limit: number = 100): Promise {\n const result = await sqlite.execute(`\n SELECT a.*, u.name as user_name, u.email as user_email\n FROM audit_logs a\n JOIN users u ON a.user_id = u.id\n ORDER BY a.timestamp DESC\n LIMIT ?\n `, [limit]);\n return result as AuditLog[];\n}\n\n// Dashboard stats\nexport async function getDashboardStats(): Promise {\n const [totalBooks] = await sqlite.execute(\"SELECT COUNT(*) as count FROM books\");\n const [totalUsers] = await sqlite.execute(\"SELECT COUNT(*) as count FROM users WHERE role != 'admin'\");\n const [booksIssued] = await sqlite.execute(\"SELECT COUNT(*) as count FROM transactions WHERE status = 'active'\");\n const [overdueBooks] = await sqlite.execute(\"SELECT COUNT(*) as count FROM transactions WHERE status = 'overdue'\");\n const [totalFines] = await sqlite.execute(\"SELECT COALESCE(SUM(amount), 0) as total FROM fines\");\n const [unpaidFines] = await sqlite.execute(\"SELECT COALESCE(SUM(amount), 0) as total FROM fines WHERE paid = FALSE\");\n\n return {\n total_books: totalBooks.count,\n total_users: totalUsers.count,\n books_issued: booksIssued.count,\n overdue_books: overdueBooks.count,\n total_fines: totalFines.total,\n unpaid_fines: unpaidFines.total\n };\n}","deleted","label",[167,168,149],"backend","database","permalink","https://www.val.town/x/priyanshu2307/untitled-7020/code/backend/database/queries.ts","extension","actionData","errors"]