[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_109":110,"_111":112},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","eixixddizca","name","discord-shop-bot","path","lib/db.ts","project",{"_22":48,"_11":12,"_35":49,"_50":-5,"_51":44,"_52":53,"_54":53,"_55":56,"_57":-5,"_58":59,"_60":61,"_62":34,"_63":-5,"_64":-5,"_65":66,"_67":68,"_69":70,"_71":72,"_73":74,"_75":-5,"_76":77,"_78":77,"_79":80,"_81":80,"_82":80,"_83":77,"_84":77,"_85":86,"_87":88,"_89":90,"_91":44},"file",{"_22":23,"_11":24,"_25":26,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":44,"_45":-5,"_13":46},"permalink","https://www.val.town/x/eixixddizca/discord-shop-bot/code/lib/db.ts","extension","id","eed49767-2c61-43cf-9a82-17a62e80126c","db.ts","fileId","02143ee4-a231-11f1-bd07-1607ee4eb77e","parentId","0c5122a9-bcd5-498b-89dd-adb72a9f617b","branchId","dfaf1e7a-f8eb-4419-ac1e-aa00e1193983","branchVersion",27,"latest",true,"createdAt",["D",1787847051583],"version",2,"fileType","script","content","// Persistent data layer (val-scoped SQLite).\n// Schema is idempotent; every important record survives restarts.\n// All queries are parameterized.\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\nexport type ProductStatus = \"active\" | \"out_of_stock\" | \"hidden\" | \"disabled\";\n\nexport interface ProductRow {\n id: number;\n productId: string;\n name: string;\n description: string;\n category: string;\n price: number;\n currency: string;\n stock: number;\n image: string | null;\n deliveryType: string;\n status: ProductStatus;\n createdAt: string;\n updatedAt: string;\n}\n\nexport interface CustomerRow {\n userId: string;\n username: string;\n customerId: string;\n createdAt: string;\n customerSince: string;\n totalOrders: number;\n completedOrders: number;\n cancelledOrders: number;\n refundedOrders: number;\n totalSpent: number;\n ticketsOpened: number;\n ticketsCompleted: number;\n reviewsSubmitted: number;\n avgRating: number;\n vipLevel: string;\n reputation: number;\n lastPurchase: string | null;\n lastActivity: string | null;\n verified: number;\n blacklisted: number;\n notes: string;\n}\n\nexport interface OrderRow {\n id: number;\n orderId: string;\n customerId: string;\n customerName: string;\n productId: string;\n productName: string;\n quantity: number;\n unitPrice: number;\n discount: number;\n finalAmount: number;\n paymentStatus: string;\n orderStatus: string;\n createdAt: string;\n completedAt: string | null;\n staffId: string | null;\n notes: string;\n}\n\nexport async function initSchema(): Promise {\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS customers (\n userId TEXT PRIMARY KEY,\n username TEXT,\n customerId TEXT,\n createdAt TEXT,\n customerSince TEXT,\n totalOrders INTEGER DEFAULT 0,\n completedOrders INTEGER DEFAULT 0,\n cancelledOrders INTEGER DEFAULT 0,\n refundedOrders INTEGER DEFAULT 0,\n totalSpent REAL DEFAULT 0,\n ticketsOpened INTEGER DEFAULT 0,\n ticketsCompleted INTEGER DEFAULT 0,\n reviewsSubmitted INTEGER DEFAULT 0,\n avgRating REAL DEFAULT 0,\n vipLevel TEXT DEFAULT 'Member',\n reputation INTEGER DEFAULT 0,\n lastPurchase TEXT,\n lastActivity TEXT,\n verified INTEGER DEFAULT 0,\n blacklisted INTEGER DEFAULT 0,\n notes TEXT DEFAULT ''\n )`);\n\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS products (\n id INTEGER PRIMARY KEY,\n productId TEXT UNIQUE,\n name TEXT,\n description TEXT,\n category TEXT,\n price REAL,\n currency TEXT,\n stock INTEGER,\n image TEXT,\n deliveryType TEXT,\n status TEXT,\n createdAt TEXT,\n updatedAt TEXT\n )`);\n\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS orders (\n id INTEGER PRIMARY KEY,\n orderId TEXT UNIQUE,\n customerId TEXT,\n customerName TEXT,\n productId TEXT,\n productName TEXT,\n quantity INTEGER,\n unitPrice REAL,\n discount REAL,\n finalAmount REAL,\n paymentStatus TEXT,\n orderStatus TEXT,\n createdAt TEXT,\n completedAt TEXT,\n staffId TEXT,\n notes TEXT\n )`);\n\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS cases (\n id INTEGER PRIMARY KEY,\n caseId TEXT UNIQUE,\n userId TEXT,\n username TEXT,\n moderatorId TEXT,\n action TEXT,\n reason TEXT,\n evidence TEXT,\n duration INTEGER,\n createdAt TEXT\n )`);\n\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS tickets (\n id INTEGER PRIMARY KEY,\n ticketId TEXT UNIQUE,\n userId TEXT,\n username TEXT,\n category TEXT,\n subject TEXT,\n channelId TEXT,\n staffId TEXT,\n status TEXT,\n priority TEXT DEFAULT 'normal',\n rating INTEGER,\n closedBy TEXT,\n openedAt TEXT,\n closedAt TEXT\n )`);\n\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS giveaways (\n id INTEGER PRIMARY KEY,\n giveawayId TEXT UNIQUE,\n channelId TEXT,\n messageId TEXT,\n prize TEXT,\n hostId TEXT,\n requiredRole TEXT,\n winnerCount INTEGER DEFAULT 1,\n participants TEXT DEFAULT '[]',\n winners TEXT DEFAULT '[]',\n ended INTEGER DEFAULT 0,\n endTime TEXT,\n createdAt TEXT\n )`);\n\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS suggestions (\n id INTEGER PRIMARY KEY,\n suggestionId TEXT UNIQUE,\n userId TEXT,\n username TEXT,\n text TEXT,\n status TEXT DEFAULT 'pending',\n upvotes INTEGER DEFAULT 0,\n downvotes INTEGER DEFAULT 0,\n voters TEXT DEFAULT '[]',\n createdAt TEXT\n )`);\n\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS polls (\n id INTEGER PRIMARY KEY,\n pollId TEXT UNIQUE,\n question TEXT,\n options TEXT,\n votes TEXT DEFAULT '{}',\n userId TEXT,\n createdAt TEXT\n )`);\n\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS reviews (\n id INTEGER PRIMARY KEY,\n reviewId TEXT UNIQUE,\n userId TEXT,\n username TEXT,\n orderId TEXT,\n rating INTEGER,\n text TEXT,\n verified INTEGER DEFAULT 0,\n createdAt TEXT\n )`);\n\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS promotions (\n id INTEGER PRIMARY KEY,\n promoId TEXT UNIQUE,\n userId TEXT,\n username TEXT,\n package TEXT,\n price REAL,\n startTime TEXT,\n endTime TEXT,\n status TEXT DEFAULT 'active',\n createdAt TEXT\n )`);\n}\n\n// ----- customers -----\nexport async function ensureCustomer(userId: string, username: string): Promise {\n const existing = await getCustomer(userId);\n if (existing) return existing;\n const now = new Date().toISOString();\n const customerId = \"C-\" + generateCode(6);\n await sqlite.execute({\n sql: `INSERT INTO customers (userId, username, customerId, createdAt, customerSince)\n VALUES (?, ?, ?, ?, ?)`,\n args: [userId, username, customerId, now, now],\n });\n return (await getCustomer(userId))!;\n}\n\nexport async function getCustomer(userId: string): Promise {\n const res = await sqlite.execute({\n sql: \"SELECT * FROM customers WHERE userId = ?\",\n args: [userId],\n });\n return (res.rows[0] as CustomerRow) ?? null;\n}\n\nexport async function getCustomerByCode(code: string): Promise {\n const res = await sqlite.execute({\n sql: \"SELECT * FROM customers WHERE customerId = ?\",\n args: [code],\n });\n return (res.rows[0] as CustomerRow) ?? null;\n}\n\nexport async function addCustomerNote(userId: string, note: string, append = true): Promise {\n if (append) {\n const c = await getCustomer(userId);\n await sqlite.execute({\n sql: \"UPDATE customers SET notes = ?, lastActivity = ? WHERE userId = ?\",\n args: [`${c?.notes ?? \"\"}${c?.notes ? \"\\n\" : \"\"}${note}`, new Date().toISOString(), userId],\n });\n } else {\n await sqlite.execute({\n sql: \"UPDATE customers SET notes = ?, lastActivity = ? WHERE userId = ?\",\n args: [note, new Date().toISOString(), userId],\n });\n }\n}\n\n// ----- products -----\nexport async function createProduct(p: Omit): Promise {\n const productId = \"P-\" + generateCode(6);\n const now = new Date().toISOString();\n await sqlite.execute({\n sql: `INSERT INTO products (productId, name, description, category, price, currency, stock, image, deliveryType, status, createdAt, updatedAt)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'active', ?, ?)`,\n args: [productId, p.name, p.description, p.category, p.price, p.currency, p.stock, p.image, p.deliveryType, now, now],\n });\n return (await getProduct(productId))!;\n}\n\nexport async function getProduct(productId: string): Promise {\n const res = await sqlite.execute({\n sql: \"SELECT * FROM products WHERE productId = ?\",\n args: [productId],\n });\n return (res.rows[0] as ProductRow) ?? null;\n}\n\nexport async function listActiveProducts(category?: string): Promise {\n let sql = \"SELECT * FROM products WHERE status IN ('active','out_of_stock')\";\n const args: unknown[] = [];\n if (category) {\n sql += \" AND category = ?\";\n args.push(category);\n }\n sql += \" ORDER BY id ASC\";\n const res = await sqlite.execute({ sql, args });\n return res.rows as ProductRow[];\n}\n\nexport async function listCategories(): Promise {\n const res = await sqlite.execute(\n \"SELECT DISTINCT category FROM products WHERE status IN ('active','out_of_stock')\",\n );\n return res.rows.map((r) => (r as { category: string }).category).filter(Boolean);\n}\n\nexport async function setProductStatus(productId: string, status: string): Promise {\n await sqlite.execute({\n sql: \"UPDATE products SET status = ?, updatedAt = ? WHERE productId = ?\",\n args: [status, new Date().toISOString(), productId],\n });\n}\n\n// ----- orders -----\nexport async function createOrder(customer: CustomerRow, product: ProductRow, quantity: number, staffId: string | null): Promise {\n const discount = 0; // discount engine added with VIP/promos later\n const unitPrice = product.price;\n const finalAmount = Number((unitPrice * quantity * (1 - discount)).toFixed(2));\n const orderId = \"ORDER-\" + generateCode(5);\n const now = new Date().toISOString();\n await sqlite.execute({\n sql: `INSERT INTO orders (orderId, customerId, customerName, productId, productName, quantity, unitPrice, discount, finalAmount, paymentStatus, orderStatus, createdAt, staffId, notes)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', 'pending', ?, ?, ?)`,\n args: [orderId, customer.userId, customer.username, product.productId, product.name, quantity, unitPrice, discount, finalAmount, now, staffId, \"\"],\n });\n const res = await sqlite.execute({ sql: \"SELECT * FROM orders WHERE orderId = ?\", args: [orderId] });\n return res.rows[0] as OrderRow;\n}\n\nexport async function getOrder(orderId: string): Promise {\n const res = await sqlite.execute({ sql: \"SELECT * FROM orders WHERE orderId = ?\", args: [orderId] });\n return (res.rows[0] as OrderRow) ?? null;\n}\n\nexport async function listOrdersByCustomer(customerId: string, limit = 10): Promise {\n const res = await sqlite.execute({\n sql: \"SELECT * FROM orders WHERE customerId = ? ORDER BY createdAt DESC LIMIT ?\",\n args: [customerId, limit],\n });\n return res.rows as OrderRow[];\n}\n\n// ----- helpers -----\nexport function generateCode(len: number): string {\n let n = \"\";\n for (let i = 0; i < len; i++) n += Math.floor(Math.random() * 10);\n return n;\n}\n\n/** Money display in the configured currency. */\nexport function money(n: number, currency = \"USD\"): string {\n const sym: Record = { USD: \"$\", EUR: \"€\", GBP: \"£\", JPY: \"¥\", RUB: \"₽\" };\n const s = sym[currency] ?? \"\";\n return `${s}${Number(n).toFixed(2)}`;\n}\n","deleted",false,"label",[47,24],"lib","438fe6a4-2424-4e5d-9feb-81f46ecbe799",["D",1787846270953],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Modular Discord shop & community bot","imageUrl","userId","6b376f3a-5347-4c3a-b4ec-787b333560af","pinnedFileId","27082d74-a230-11f1-8efc-1607ee4eb77e","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","01a043e6-75ad-74fe-b7a7-17e3786f5114","tursoName","438fe6a424244e5d9feb81f46ecbe799","tursoHostname","438fe6a424244e5d9feb81f46ecbe799-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789497078049],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_108":103},"org",{"_9":10,"_102":103,"_104":103,"_58":59,"_105":106,"_107":44},"branch",{"_37":92,"_11":93,"_22":30,"_94":48,"_95":-5,"_96":-5,"_97":-5,"_98":-5,"_99":-5,"_100":12,"_101":10},"isOwned",61,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","","avatar","tier","free","canRestrictVals","profileImageUrl","ctx",{"_85":-5,"_142":143,"_144":145,"_146":147,"_148":44,"_149":44,"_150":44,"_151":34,"_152":-5,"_153":-7,"_154":-7,"_155":66,"_156":44,"_157":44,"_158":-5,"_159":44,"_160":44,"_161":44,"_162":-5},"ENV",{"_113":114,"_115":116,"_117":93,"_118":119,"_120":121,"_122":123,"_124":125,"_126":127,"_128":129,"_130":131,"_132":133,"_134":135,"_136":137,"_138":139,"_140":141},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town","orgs",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]