| import { randomUUID } from "crypto"; |
| import type pg from "pg"; |
| import { getReservationSummary, recordInitialStock, withTransaction } from "./inventory"; |
| import { getPool, round2 } from "./pool"; |
|
|
| export type Product = { |
| id: string; |
| barcode: string; |
| sku: string; |
| name: string; |
| category: string; |
| styleCode: string; |
| size: string; |
| color: string; |
| brand: string; |
| season: string; |
| gender: string; |
| unitPrice: number; |
| costPrice: number; |
| taxPercent: number; |
| inStock: number; |
| reservedQty: number; |
| availableQty: number; |
| reorderLevel: number; |
| demandScore: number; |
| imageUrl?: string | null; |
| }; |
|
|
| export type OrderLineSnapshot = { |
| productId: string; |
| name: string; |
| qty: number; |
| unitPrice: number; |
| taxPercent: number; |
| lineSubtotal: number; |
| lineTax: number; |
| lineTotal: number; |
| }; |
|
|
| export type Order = { |
| id: string; |
| sessionId: string; |
| storeCode: string; |
| total: number; |
| subtotal: number; |
| taxTotal: number; |
| lines: OrderLineSnapshot[]; |
| paymentMode: "ONLINE" | "COUNTER"; |
| paid: boolean; |
| tokenNumber?: number; |
| createdAt: string; |
| voided: boolean; |
| refunded: boolean; |
| receiptEmail?: string | null; |
| receiptPhone?: string | null; |
| }; |
|
|
| export type Session = { |
| id: string; |
| storeId: string; |
| storeCode: string; |
| cart: Map<string, number>; |
| customerPhone?: string; |
| createdAt: string; |
| expiresAt: string; |
| }; |
|
|
| type ProductRow = { |
| id: string; |
| barcode: string; |
| sku: string; |
| name: string; |
| category: string; |
| style_code: string; |
| size: string; |
| color: string; |
| brand: string; |
| season: string; |
| gender: string; |
| unit_price: string; |
| cost_price: string; |
| tax_percent: string; |
| in_stock: number; |
| reorder_level: number; |
| demand_score: string; |
| image_url: string | null; |
| }; |
|
|
| const PRODUCT_SELECT = `id, barcode, sku, name, category, style_code, size, color, brand, season, gender, |
| unit_price, cost_price, tax_percent, in_stock, reorder_level, demand_score, image_url`; |
|
|
| function buildVariantSku(styleCode: string, color: string, size: string, barcode: string): string { |
| const parts = [styleCode, color, size] |
| .map((s) => s.trim()) |
| .filter(Boolean) |
| .map((s) => s.toUpperCase().replace(/\s+/g, "-")); |
| if (parts.length > 0) return parts.join("-"); |
| return barcode.trim(); |
| } |
|
|
| function mapProduct(row: ProductRow, reservedQty = 0): Product { |
| const inStock = row.in_stock; |
| return { |
| id: row.id, |
| barcode: row.barcode, |
| sku: row.sku, |
| name: row.name, |
| category: row.category, |
| styleCode: row.style_code ?? "", |
| size: row.size ?? "", |
| color: row.color ?? "", |
| brand: row.brand ?? "", |
| season: row.season ?? "", |
| gender: row.gender ?? "Unisex", |
| unitPrice: Number(row.unit_price), |
| costPrice: Number(row.cost_price), |
| taxPercent: Number(row.tax_percent), |
| inStock, |
| reservedQty, |
| availableQty: Math.max(0, inStock - reservedQty), |
| reorderLevel: row.reorder_level ?? 10, |
| demandScore: Number(row.demand_score), |
| imageUrl: PRODUCT_IMAGE_MAP[row.name.trim().toLowerCase()] ?? row.image_url |
| }; |
| } |
|
|
| async function attachReservationCounts(products: Product[]): Promise<Product[]> { |
| const reserved = await getReservationSummary(); |
| return products.map((p) => { |
| const reservedQty = reserved.get(p.id) ?? 0; |
| return { |
| ...p, |
| reservedQty, |
| availableQty: Math.max(0, p.inStock - reservedQty) |
| }; |
| }); |
| } |
|
|
| const defaultStoreCode = process.env.DEFAULT_STORE_CODE?.trim().toUpperCase() || "BLR001"; |
|
|
| export async function getDefaultStoreId(): Promise<string> { |
| const { rows } = await getPool().query<{ id: string }>( |
| "SELECT id FROM stores WHERE code = $1 LIMIT 1", |
| [defaultStoreCode] |
| ); |
| if (!rows[0]) { |
| throw new Error(`Store ${defaultStoreCode} not found — run Supabase migrations`); |
| } |
| return rows[0].id; |
| } |
|
|
| export async function getStoreIdByCode(code: string): Promise<string | null> { |
| const { rows } = await getPool().query<{ id: string }>("SELECT id FROM stores WHERE code = $1 LIMIT 1", [ |
| code.toUpperCase() |
| ]); |
| return rows[0]?.id ?? null; |
| } |
|
|
| import { getFallbackProducts } from "./seed_full_catalog"; |
|
|
| export const PRODUCT_IMAGE_MAP: Record<string, string> = { |
| "classic oxford cotton shirt": "https://images.unsplash.com/photo-1596755094514-f87e34085b2c?w=600&h=600&fit=crop&q=80", |
| "vintage chambray workshirt": "https://images.unsplash.com/photo-1602810318383-e386cc2a3ccf?w=600&h=600&fit=crop&q=80", |
| "striped casual linen shirt": "https://images.unsplash.com/photo-1607345366928-199ea26cfe3e?w=600&h=600&fit=crop&q=80", |
| "basic white crewneck tee": "https://images.unsplash.com/photo-1521572163474-6864f9cf17ab?w=600&h=600&fit=crop&q=80", |
| "basic charcoal crewneck tee": "https://images.unsplash.com/photo-1618354691373-d851c5c3a990?w=600&h=600&fit=crop&q=80", |
| "textured knit henley shirt": "https://images.unsplash.com/photo-1618517351616-38fb9c5210c6?w=600&h=600&fit=crop&q=80", |
| "pique polo shirt": "https://images.unsplash.com/photo-1625910513413-5fc220e35985?w=600&h=600&fit=crop&q=80", |
| "indigo denim shirt": "https://images.unsplash.com/photo-1589310243389-96a5483213a8?w=600&h=600&fit=crop&q=80", |
| "handloom cotton kurta": "https://images.unsplash.com/photo-1610030469983-98e550d6193c?w=600&h=600&fit=crop&q=80", |
| "printed resort collar shirt": "https://images.unsplash.com/photo-1596609548086-85bbf8ddb6b9?w=600&h=600&fit=crop&q=80", |
| "flannel plaid overshirt": "https://images.unsplash.com/photo-1608063615781-e2ef8c73d114?w=600&h=600&fit=crop&q=80", |
| "slim fit graphic streetwear tee": "https://images.unsplash.com/photo-1503342217505-b0a15ec3261c?w=600&h=600&fit=crop&q=80", |
| "oversized satin button-down shirt": "https://images.unsplash.com/photo-1551163943-3f6a855d1153?w=600&h=600&fit=crop&q=80", |
| "sleeveless ribbed crop top": "https://images.unsplash.com/photo-1515886657613-9f3515b0c78f?w=600&h=600&fit=crop&q=80", |
| "floral print summer dress": "https://images.unsplash.com/photo-1572804013309-59a88b7e92f1?w=600&h=600&fit=crop&q=80", |
| "linen wrap midi dress": "https://images.unsplash.com/photo-1595777457583-95e059d581b8?w=600&h=600&fit=crop&q=80", |
| "silk embroidered kurti": "https://images.unsplash.com/photo-1617627143750-d86bc21e42bb?w=600&h=600&fit=crop&q=80", |
| "tiered cotton sundress": "https://images.unsplash.com/photo-1596783074918-c84cb06531ca?w=600&h=600&fit=crop&q=80", |
| "emerald silk anarkali suit": "https://images.unsplash.com/photo-1583391733956-3750e0ff4e8b?w=600&h=600&fit=crop&q=80", |
| "velvet cocktail party dress": "https://images.unsplash.com/photo-1518622358385-8ea7d0794bf6?w=600&h=600&fit=crop&q=80", |
| "handblock printed saree": "https://images.unsplash.com/photo-1610030469983-98e550d6193c?w=600&h=600&fit=crop&q=80", |
| "ribbed bodycon knit dress": "https://images.unsplash.com/photo-1515372039744-b8f02a3ae446?w=600&h=600&fit=crop&q=80", |
| "satin evening slip dress": "https://images.unsplash.com/photo-1566174053879-31528523f8ae?w=600&h=600&fit=crop&q=80", |
| "slim fit indigo jeans": "https://images.unsplash.com/photo-1542272604-787c3835535d?w=600&h=600&fit=crop&q=80", |
| "vintage stonewash jeans": "https://images.unsplash.com/photo-1541099649105-f69ad21f3246?w=600&h=600&fit=crop&q=80", |
| "utility khaki chinos": "https://images.unsplash.com/photo-1473966968600-fa801b869a1a?w=600&h=600&fit=crop&q=80", |
| "off-white linen trousers": "https://images.unsplash.com/photo-1594633312681-425c7b97ccd1?w=600&h=600&fit=crop&q=80", |
| "charcoal tailored trousers": "https://images.unsplash.com/photo-1507003211169-0a1dd7228f2d?w=600&h=600&fit=crop&q=80", |
| "black wide-leg trousers": "https://images.unsplash.com/photo-1509631179647-0177331693ae?w=600&h=600&fit=crop&q=80", |
| "cotton cargo joggers": "https://images.unsplash.com/photo-1552902865-b72c031ac5ea?w=600&h=600&fit=crop&q=80", |
| "flannel lounge pants": "https://images.unsplash.com/photo-1519568470290-c0c1fbfff16f?w=600&h=600&fit=crop&q=80", |
| "high-waisted mom jeans": "https://images.unsplash.com/photo-1582552938357-32b906df40cb?w=600&h=600&fit=crop&q=80", |
| "performance athletic shorts": "https://images.unsplash.com/photo-1562886877-aaaa5c16396e?w=600&h=600&fit=crop&q=80", |
| "suede trucker jacket": "https://images.unsplash.com/photo-1551028719-00167b16eac5?w=600&h=600&fit=crop&q=80", |
| "vintage wash denim jacket": "https://images.unsplash.com/photo-1576871337632-b9aef4c17ab9?w=600&h=600&fit=crop&q=80", |
| "structured navy wool blazer": "https://images.unsplash.com/photo-1507679799987-c73779587ccf?w=600&h=600&fit=crop&q=80", |
| "grey cable-knit cardigan": "https://images.unsplash.com/photo-1620799140408-edc6dcb6d633?w=600&h=600&fit=crop&q=80", |
| "heavyweight sherpa fleece": "https://images.unsplash.com/photo-1544022613-e87ca75a784a?w=600&h=600&fit=crop&q=80", |
| "hooded utility windbreaker": "https://images.unsplash.com/photo-1556821840-3a63f95609a7?w=600&h=600&fit=crop&q=80", |
| "fine merino wool sweater": "https://images.unsplash.com/photo-1576995853123-5a10305d93c0?w=600&h=600&fit=crop&q=80", |
| "lightweight down puffer jacket": "https://images.unsplash.com/photo-1548883354-7622d03aca27?w=600&h=600&fit=crop&q=80", |
| "denim jacket with shearling": "https://images.unsplash.com/photo-1543076447-215ad9ba6923?w=600&h=600&fit=crop&q=80", |
| "classic plaid shacket": "https://images.unsplash.com/photo-1516257984-b1b4d707412e?w=600&h=600&fit=crop&q=80", |
| "double-breasted trench coat": "https://images.unsplash.com/photo-1539533113208-f6df8cc8b543?w=600&h=600&fit=crop&q=80", |
| "natural canvas tote bag": "https://images.unsplash.com/photo-1544816155-12df9643f363?w=600&h=600&fit=crop&q=80", |
| "woolen knit scarf": "https://images.unsplash.com/photo-1520903920243-00d872a2d1c9?w=600&h=600&fit=crop&q=80", |
| "vintage leather sling bag": "https://images.unsplash.com/photo-1548036328-c9fa89d128fa?w=600&h=600&fit=crop&q=80", |
| "minimalist leather wallet": "https://images.unsplash.com/photo-1627123424574-724758594e93?w=600&h=600&fit=crop&q=80", |
| "woven straw tote bag": "https://images.unsplash.com/photo-1590874103328-eac38a683ce7?w=600&h=600&fit=crop&q=80", |
| "silk block-print scarf": "https://images.unsplash.com/photo-1601370552761-09e6b3f27bba?w=600&h=600&fit=crop&q=80", |
| "durable canvas backpack": "https://images.unsplash.com/photo-1546938576-6e6a64f317cc?w=600&h=600&fit=crop&q=80", |
| "classic leather chelsea boots": "https://images.unsplash.com/photo-1542291026-7eec264c27ff?w=600&h=600&fit=crop&q=80", |
| "canvas low-top sneakers": "https://images.unsplash.com/photo-1525966222134-fcfa99b8ae77?w=600&h=600&fit=crop&q=80", |
| "polarized retro sunglasses": "https://images.unsplash.com/photo-1511499767150-a48a237f0083?w=600&h=600&fit=crop&q=80" |
| }; |
|
|
| export const REMOVED_NAMES = new Set([ |
| "boho floral chiffon blouse", |
| "classic white silk blouse", |
| "denim button-up a-line skirt", |
| "oversized cotton hoodie", |
| "forest green pleated skirt", |
| "full grain leather belt", |
| "ribbed knit beanie hat", |
| "ribbed knit mockneck top", |
| "sage green cami top", |
| "chiffon pleated maxi dress", |
| "chikankari cotton kurta set", |
| "terracotta linen top", |
| "striped cotton boatneck tee", |
| "lace trim v-neck tank top", |
| "embroidered georgette tunic", |
| "polka dot a-line shirt dress" |
| ]); |
|
|
| function groupProductsByName(products: Product[]): Product[] { |
| const map = new Map<string, Product>(); |
| for (const p of products) { |
| const key = p.name.trim().toLowerCase(); |
| if (REMOVED_NAMES.has(key)) continue; |
| const mappedImage = PRODUCT_IMAGE_MAP[key] ?? p.imageUrl; |
| const updatedProduct = { ...p, imageUrl: mappedImage }; |
| if (!map.has(key)) { |
| map.set(key, updatedProduct); |
| } |
| } |
| return Array.from(map.values()); |
| } |
|
|
| export async function listProducts(): Promise<Product[]> { |
| try { |
| const storeId = await getDefaultStoreId(); |
| const { rows } = await getPool().query<ProductRow>( |
| `SELECT ${PRODUCT_SELECT} |
| FROM products WHERE store_id = $1 AND is_active = TRUE ORDER BY name`, |
| [storeId] |
| ); |
| if (rows.length > 0) { |
| const products = rows.map((row) => mapProduct(row)); |
| return attachReservationCounts(groupProductsByName(products)); |
| } |
| } catch (err) { |
| console.error("listProducts DB query error, serving fallback catalog:", err); |
| } |
| return getFallbackProducts(); |
| } |
|
|
| export async function findProductById(id: string): Promise<Product | null> { |
| const { rows } = await getPool().query<ProductRow>( |
| `SELECT ${PRODUCT_SELECT} |
| FROM products WHERE id = $1 AND is_active = TRUE LIMIT 1`, |
| [id] |
| ); |
| if (!rows[0]) return null; |
| const reserved = await getReservationSummary(); |
| return mapProduct(rows[0], reserved.get(id) ?? 0); |
| } |
|
|
| export async function findProductByBarcode(barcode: string): Promise<Product | null> { |
| const storeId = await getDefaultStoreId(); |
| const { rows } = await getPool().query<ProductRow>( |
| `SELECT ${PRODUCT_SELECT} |
| FROM products WHERE store_id = $1 AND barcode = $2 AND is_active = TRUE LIMIT 1`, |
| [storeId, barcode] |
| ); |
| if (!rows[0]) return null; |
| const reserved = await getReservationSummary(); |
| return mapProduct(rows[0], reserved.get(rows[0].id) ?? 0); |
| } |
|
|
| export async function createProduct( |
| input: Omit<Product, "id" | "sku" | "reservedQty" | "availableQty">, |
| actor = "staff" |
| ): Promise<Product> { |
| return withTransaction(async (client) => { |
| const storeId = await getDefaultStoreId(); |
| const id = randomUUID(); |
| const imageUrl = input.imageUrl?.trim() || null; |
| const sku = buildVariantSku(input.styleCode, input.color, input.size, input.barcode); |
| const { rows } = await client.query<ProductRow>( |
| `INSERT INTO products (id, store_id, barcode, sku, name, category, style_code, size, color, brand, season, gender, |
| unit_price, cost_price, tax_percent, in_stock, reorder_level, demand_score, image_url) |
| VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19) |
| RETURNING ${PRODUCT_SELECT}`, |
| [ |
| id, |
| storeId, |
| input.barcode, |
| sku, |
| input.name, |
| input.category, |
| input.styleCode?.trim() ?? "", |
| input.size?.trim() ?? "", |
| input.color?.trim() ?? "", |
| input.brand?.trim() ?? "", |
| input.season?.trim() ?? "", |
| input.gender?.trim() || "Unisex", |
| input.unitPrice, |
| input.costPrice, |
| input.taxPercent, |
| input.inStock, |
| input.reorderLevel ?? 10, |
| input.demandScore, |
| imageUrl |
| ] |
| ); |
| if (input.inStock > 0) { |
| await recordInitialStock(id, storeId, input.inStock, actor, client); |
| } |
| return mapProduct(rows[0]); |
| }); |
| } |
|
|
| export async function updateProductDetails( |
| id: string, |
| input: { |
| barcode: string; |
| name: string; |
| category: string; |
| styleCode: string; |
| size: string; |
| color: string; |
| brand: string; |
| season: string; |
| gender: string; |
| unitPrice: number; |
| costPrice: number; |
| reorderLevel: number; |
| taxPercent: number; |
| demandScore: number; |
| imageUrl?: string | null; |
| } |
| ): Promise<Product | null> { |
| const imageUrl = input.imageUrl?.trim() || null; |
| const sku = buildVariantSku(input.styleCode, input.color, input.size, input.barcode); |
| const { rows } = await getPool().query<ProductRow>( |
| `UPDATE products |
| SET barcode = $2, |
| sku = $3, |
| name = $4, |
| category = $5, |
| style_code = $6, |
| size = $7, |
| color = $8, |
| brand = $9, |
| season = $10, |
| gender = $11, |
| unit_price = $12, |
| cost_price = $13, |
| reorder_level = $14, |
| tax_percent = $15, |
| demand_score = $16, |
| image_url = $17 |
| WHERE id = $1 AND is_active = TRUE |
| RETURNING ${PRODUCT_SELECT}`, |
| [ |
| id, |
| input.barcode, |
| sku, |
| input.name, |
| input.category, |
| input.styleCode?.trim() ?? "", |
| input.size?.trim() ?? "", |
| input.color?.trim() ?? "", |
| input.brand?.trim() ?? "", |
| input.season?.trim() ?? "", |
| input.gender?.trim() || "Unisex", |
| input.unitPrice, |
| input.costPrice, |
| input.reorderLevel ?? 10, |
| input.taxPercent, |
| input.demandScore, |
| imageUrl |
| ] |
| ); |
| if (!rows[0]) return null; |
| const reserved = await getReservationSummary(); |
| return mapProduct(rows[0], reserved.get(id) ?? 0); |
| } |
|
|
| export async function updateProductImage(id: string, imageUrl: string | null): Promise<Product | null> { |
| const { rows } = await getPool().query<ProductRow>( |
| `UPDATE products SET image_url = $2 |
| WHERE id = $1 AND is_active = TRUE |
| RETURNING ${PRODUCT_SELECT}`, |
| [id, imageUrl?.trim() || null] |
| ); |
| if (!rows[0]) return null; |
| const reserved = await getReservationSummary(); |
| return mapProduct(rows[0], reserved.get(id) ?? 0); |
| } |
|
|
| export async function updateProductStock(id: string, inStock: number): Promise<Product | null> { |
| const { rows } = await getPool().query<ProductRow>( |
| `UPDATE products SET in_stock = $2 |
| WHERE id = $1 AND is_active = TRUE |
| RETURNING ${PRODUCT_SELECT}`, |
| [id, inStock] |
| ); |
| if (!rows[0]) return null; |
| const reserved = await getReservationSummary(); |
| return mapProduct(rows[0], reserved.get(id) ?? 0); |
| } |
|
|
| export async function getDiscountMap(): Promise<Map<string, number>> { |
| const { rows } = await getPool().query<{ product_id: string; discount_percent: number }>( |
| "SELECT product_id, discount_percent FROM product_discounts" |
| ); |
| return new Map(rows.map((r) => [r.product_id, r.discount_percent])); |
| } |
|
|
| export async function getDiscountPercent(productId: string): Promise<number> { |
| const { rows } = await getPool().query<{ discount_percent: number }>( |
| "SELECT discount_percent FROM product_discounts WHERE product_id = $1", |
| [productId] |
| ); |
| return rows[0]?.discount_percent ?? 0; |
| } |
|
|
| export async function setDiscount(productId: string, pct: number): Promise<void> { |
| await getPool().query( |
| `INSERT INTO product_discounts (product_id, discount_percent) VALUES ($1, $2) |
| ON CONFLICT (product_id) DO UPDATE SET discount_percent = EXCLUDED.discount_percent, updated_at = NOW()`, |
| [productId, pct] |
| ); |
| } |
|
|
| export async function deleteDiscount(productId: string): Promise<void> { |
| await getPool().query("DELETE FROM product_discounts WHERE product_id = $1", [productId]); |
| } |
|
|
| export async function pushAudit(actor: string, role: string, action: string, detail: string): Promise<void> { |
| await getPool().query( |
| "INSERT INTO audit_log (actor, role, action, detail) VALUES ($1, $2, $3, $4)", |
| [actor, role, action, detail.slice(0, 2000)] |
| ); |
| } |
|
|
| export async function listAudit(limit = 250) { |
| const { rows } = await getPool().query( |
| `SELECT id, at, actor, role, action, detail FROM audit_log ORDER BY at DESC LIMIT $1`, |
| [limit] |
| ); |
| return rows.map((r) => ({ |
| id: r.id, |
| at: new Date(r.at).toISOString(), |
| actor: r.actor, |
| role: r.role, |
| action: r.action, |
| detail: r.detail |
| })); |
| } |
|
|
| export async function createSession(storeCode: string, customerPhone?: string): Promise<Session> { |
| const storeId = (await getStoreIdByCode(storeCode)) ?? (await getDefaultStoreId()); |
| const id = randomUUID(); |
| const expiresAt = new Date(Date.now() + 60 * 60 * 1000); |
| await getPool().query( |
| `INSERT INTO customer_sessions (id, store_id, store_code, customer_phone, cart, expires_at) |
| VALUES ($1, $2, $3, $4, '{}', $5)`, |
| [id, storeId, storeCode.toUpperCase(), customerPhone ?? null, expiresAt.toISOString()] |
| ); |
| return { |
| id, |
| storeId, |
| storeCode: storeCode.toUpperCase(), |
| cart: new Map(), |
| customerPhone, |
| createdAt: new Date().toISOString(), |
| expiresAt: expiresAt.toISOString() |
| }; |
| } |
|
|
| export async function getSession(id: string): Promise<Session | null> { |
| const { rows } = await getPool().query<{ |
| id: string; |
| store_id: string; |
| store_code: string; |
| customer_phone: string | null; |
| cart: Record<string, number>; |
| created_at: Date; |
| expires_at: Date; |
| status: string; |
| }>("SELECT * FROM customer_sessions WHERE id = $1 AND status = 'active' AND expires_at > NOW() LIMIT 1", [id]); |
| const row = rows[0]; |
| if (!row) return null; |
| const cartObj = row.cart ?? {}; |
| const cart = new Map<string, number>(Object.entries(cartObj).map(([k, v]) => [k, Number(v)])); |
| return { |
| id: row.id, |
| storeId: row.store_id, |
| storeCode: row.store_code, |
| cart, |
| customerPhone: row.customer_phone ?? undefined, |
| createdAt: new Date(row.created_at).toISOString(), |
| expiresAt: new Date(row.expires_at).toISOString() |
| }; |
| } |
|
|
| export async function saveSessionCart(sessionId: string, cart: Map<string, number>): Promise<void> { |
| const obj: Record<string, number> = {}; |
| for (const [k, v] of cart) obj[k] = v; |
| await getPool().query("UPDATE customer_sessions SET cart = $2::jsonb WHERE id = $1", [sessionId, JSON.stringify(obj)]); |
| } |
|
|
| export async function getLatestSessionByPhone(phone: string): Promise<Session | null> { |
| const norm = phone.replace(/\s/g, ""); |
| const { rows } = await getPool().query<{ |
| id: string; |
| store_id: string; |
| store_code: string; |
| customer_phone: string | null; |
| cart: Record<string, number>; |
| created_at: Date; |
| expires_at: Date; |
| }>( |
| `SELECT id, store_id, store_code, customer_phone, cart, created_at, expires_at FROM customer_sessions |
| WHERE REPLACE(COALESCE(customer_phone, ''), ' ', '') = $1 AND status = 'active' AND expires_at > NOW() |
| ORDER BY created_at DESC LIMIT 1`, |
| [norm] |
| ); |
| const row = rows[0]; |
| if (!row) return null; |
| return { |
| id: row.id, |
| storeId: row.store_id, |
| storeCode: row.store_code, |
| cart: new Map(Object.entries(row.cart ?? {}).map(([k, v]) => [k, Number(v)])), |
| customerPhone: row.customer_phone ?? undefined, |
| createdAt: new Date(row.created_at).toISOString(), |
| expiresAt: new Date(row.expires_at).toISOString() |
| }; |
| } |
|
|
| function mapOrder(row: { |
| id: string; |
| session_id: string; |
| store_code: string; |
| subtotal: string; |
| tax_total: string; |
| total: string; |
| lines: OrderLineSnapshot[]; |
| payment_mode: string; |
| paid: boolean; |
| token_number: number | null; |
| voided: boolean; |
| refunded: boolean; |
| receipt_email: string | null; |
| receipt_phone: string | null; |
| created_at: Date; |
| }): Order { |
| return { |
| id: row.id, |
| sessionId: row.session_id, |
| storeCode: row.store_code, |
| subtotal: Number(row.subtotal), |
| taxTotal: Number(row.tax_total), |
| total: Number(row.total), |
| lines: row.lines, |
| paymentMode: row.payment_mode as "ONLINE" | "COUNTER", |
| paid: row.paid, |
| tokenNumber: row.token_number ?? undefined, |
| createdAt: new Date(row.created_at).toISOString(), |
| voided: row.voided, |
| refunded: row.refunded, |
| receiptEmail: row.receipt_email, |
| receiptPhone: row.receipt_phone |
| }; |
| } |
|
|
| const inMemoryOrdersStore: Map<string, Order> = new Map(); |
| let inMemoryCounterToken = 1000; |
|
|
| export async function createOrder(order: Order, client?: pg.PoolClient): Promise<void> { |
| |
| inMemoryOrdersStore.set(order.id, order); |
|
|
| try { |
| const storeId = (await getStoreIdByCode(order.storeCode)) || "00000000-0000-0000-0000-000000000001"; |
| const q = client ?? getPool(); |
| await q.query( |
| `INSERT INTO orders (id, session_id, store_id, store_code, subtotal, tax_total, total, lines, payment_mode, paid, token_number, voided, refunded, receipt_email, receipt_phone, created_at) |
| VALUES ($1,$2,$3,$4,$5,$6,$7,$8::jsonb,$9,$10,$11,$12,$13,$14,$15,$16)`, |
| [ |
| order.id, |
| order.sessionId, |
| storeId, |
| order.storeCode, |
| order.subtotal, |
| order.taxTotal, |
| order.total, |
| JSON.stringify(order.lines), |
| order.paymentMode, |
| order.paid, |
| order.tokenNumber ?? null, |
| order.voided, |
| order.refunded, |
| order.receiptEmail ?? null, |
| order.receiptPhone ?? null, |
| order.createdAt |
| ] |
| ); |
| } catch (e) { |
| console.warn("Failed to persist order to database, using memory fallback", e); |
| } |
| } |
|
|
| export async function getOrder(id: string): Promise<Order | null> { |
| try { |
| const { rows } = await getPool().query("SELECT * FROM orders WHERE id = $1 LIMIT 1", [id]); |
| if (rows[0]) return mapOrder(rows[0]); |
| } catch (e) { |
| console.warn("Failed to get order from DB, trying memory fallback", e); |
| } |
| return inMemoryOrdersStore.get(id) ?? null; |
| } |
|
|
| export async function getOrderByToken(token: number): Promise<Order | null> { |
| try { |
| const { rows } = await getPool().query( |
| "SELECT * FROM orders WHERE token_number = $1 AND voided = FALSE ORDER BY created_at DESC LIMIT 1", |
| [token] |
| ); |
| if (rows[0]) return mapOrder(rows[0]); |
| } catch (e) { |
| console.warn("Failed to get order by token from DB, trying memory fallback", e); |
| } |
| for (const o of inMemoryOrdersStore.values()) { |
| if (o.tokenNumber === token && !o.voided) return o; |
| } |
| return null; |
| } |
|
|
| export async function listOrders(limit: number, storeCode?: string) { |
| try { |
| const params: unknown[] = [limit]; |
| let sql = "SELECT * FROM orders"; |
| if (storeCode) { |
| sql += " WHERE store_code = $2"; |
| params.push(storeCode); |
| } |
| sql += " ORDER BY created_at DESC LIMIT $1"; |
| const { rows } = await getPool().query(sql, params); |
| if (rows.length > 0) return rows.map(mapOrder); |
| } catch (e) { |
| console.warn("Failed to list orders from DB, using memory fallback", e); |
| } |
| const list = Array.from(inMemoryOrdersStore.values()); |
| list.sort((a, b) => new Date(b.createdAt).getTime() - new Date(a.createdAt).getTime()); |
| return list.slice(0, limit); |
| } |
|
|
| export async function listPendingCounterOrders(): Promise<Order[]> { |
| try { |
| const { rows } = await getPool().query( |
| `SELECT * FROM orders WHERE payment_mode = 'COUNTER' AND paid = FALSE AND voided = FALSE AND refunded = FALSE ORDER BY created_at ASC` |
| ); |
| if (rows.length > 0) return rows.map(mapOrder); |
| } catch (e) { |
| console.warn("Failed to list pending counter orders from DB, using memory fallback", e); |
| } |
| const list = Array.from(inMemoryOrdersStore.values()).filter( |
| (o) => o.paymentMode === "COUNTER" && !o.paid && !o.voided && !o.refunded |
| ); |
| list.sort((a, b) => new Date(a.createdAt).getTime() - new Date(b.createdAt).getTime()); |
| return list; |
| } |
|
|
| export async function updateOrderPaid(id: string, paid: boolean, client?: pg.PoolClient): Promise<void> { |
| const o = inMemoryOrdersStore.get(id); |
| if (o) o.paid = paid; |
| try { |
| await (client ?? getPool()).query("UPDATE orders SET paid = $2 WHERE id = $1", [id, paid]); |
| } catch {} |
| } |
|
|
| export async function updateOrderVoided(id: string, voided: boolean, client?: pg.PoolClient): Promise<void> { |
| const o = inMemoryOrdersStore.get(id); |
| if (o) o.voided = voided; |
| try { |
| await (client ?? getPool()).query("UPDATE orders SET voided = $2 WHERE id = $1", [id, voided]); |
| } catch {} |
| } |
|
|
| export async function updateOrderRefunded(id: string, client?: pg.PoolClient): Promise<void> { |
| const o = inMemoryOrdersStore.get(id); |
| if (o) { |
| o.refunded = true; |
| o.paid = false; |
| } |
| try { |
| await (client ?? getPool()).query("UPDATE orders SET refunded = TRUE, paid = FALSE WHERE id = $1", [id]); |
| } catch {} |
| } |
|
|
| export async function nextCounterToken(): Promise<number> { |
| try { |
| const { rows } = await getPool().query<{ next_value: number }>( |
| "UPDATE app_counters SET next_value = next_value + 1 WHERE key = 'counter_token' RETURNING next_value" |
| ); |
| if (!rows[0]) { |
| await getPool().query( |
| "INSERT INTO app_counters (key, next_value) VALUES ('counter_token', 1000) ON CONFLICT (key) DO NOTHING" |
| ); |
| const retry = await getPool().query<{ next_value: number }>( |
| "UPDATE app_counters SET next_value = next_value + 1 WHERE key = 'counter_token' RETURNING next_value" |
| ); |
| if (retry.rows[0]) return retry.rows[0].next_value; |
| } else { |
| return rows[0].next_value; |
| } |
| } catch (e) { |
| console.warn("Failed to generate token from DB app_counters, using in-memory token fallback", e); |
| } |
| inMemoryCounterToken += 1; |
| return inMemoryCounterToken; |
| } |
|
|
| export async function insertReceipt(rec: { |
| id: string; |
| orderId: string; |
| receiptNumber: string; |
| total: number; |
| paymentMode: string; |
| whatsapp: string | null; |
| }): Promise<void> { |
| await getPool().query( |
| `INSERT INTO receipts (id, order_id, receipt_number, total, payment_mode, whatsapp) VALUES ($1,$2,$3,$4,$5,$6)`, |
| [rec.id, rec.orderId, rec.receiptNumber, rec.total, rec.paymentMode, rec.whatsapp] |
| ); |
| } |
|
|
| export async function listReceipts() { |
| const { rows } = await getPool().query("SELECT * FROM receipts ORDER BY created_at DESC LIMIT 500"); |
| return rows.map((r) => ({ |
| id: r.id, |
| orderId: r.order_id, |
| receiptNumber: r.receipt_number, |
| total: Number(r.total), |
| paymentMode: r.payment_mode, |
| whatsapp: r.whatsapp, |
| createdAt: new Date(r.created_at).toISOString() |
| })); |
| } |
|
|
| export async function getReceiptByOrderId(orderId: string) { |
| const { rows } = await getPool().query<{ |
| receipt_number: string; |
| total: string; |
| payment_mode: string; |
| created_at: Date; |
| }>("SELECT receipt_number, total, payment_mode, created_at FROM receipts WHERE order_id = $1 ORDER BY created_at DESC LIMIT 1", [ |
| orderId |
| ]); |
| const row = rows[0]; |
| if (!row) return null; |
| return { |
| receiptNumber: row.receipt_number, |
| total: Number(row.total), |
| paymentMode: row.payment_mode, |
| createdAt: new Date(row.created_at).toISOString() |
| }; |
| } |
|
|
| export async function markExitTokenUsed(tokenHash: string): Promise<void> { |
| await getPool().query( |
| "INSERT INTO exit_tokens_used (token_hash) VALUES ($1) ON CONFLICT DO NOTHING", |
| [tokenHash] |
| ); |
| } |
|
|
| export async function isExitTokenUsed(tokenHash: string): Promise<boolean> { |
| const { rows } = await getPool().query("SELECT 1 FROM exit_tokens_used WHERE token_hash = $1 LIMIT 1", [tokenHash]); |
| return rows.length > 0; |
| } |
|
|
| export async function getMetrics(storeCode?: string) { |
| const params: unknown[] = []; |
| let filter = "WHERE voided = FALSE"; |
| if (storeCode) { |
| filter += " AND store_code = $1"; |
| params.push(storeCode); |
| } |
| const { rows: orderRows } = await getPool().query<{ |
| paid: boolean; |
| refunded: boolean; |
| voided: boolean; |
| total: string; |
| }>(`SELECT paid, refunded, voided, total FROM orders ${filter}`, params); |
|
|
| const products = await listProducts(); |
| const discountMap = await getDiscountMap(); |
|
|
| const paid = orderRows.filter((o) => o.paid && !o.refunded && !o.voided); |
| const pending = orderRows.filter((o) => !o.paid && !o.voided && !o.refunded); |
| const revenue = paid.reduce((s, o) => s + Number(o.total), 0); |
| const pendingValue = pending.reduce((s, o) => s + Number(o.total), 0); |
|
|
| return { |
| orderCount: orderRows.filter((o) => !o.voided).length, |
| paidOrderCount: paid.length, |
| openOrderCount: pending.length, |
| totalRevenue: round2(revenue), |
| pendingOrderValue: round2(pendingValue), |
| averagePaidOrderValue: round2(paid.length ? revenue / paid.length : 0), |
| productCount: products.length, |
| lowStockSkuCount: products.filter((p) => p.availableQty < p.reorderLevel).length, |
| inventoryValueAtCost: round2(products.reduce((s, p) => s + p.costPrice * p.inStock, 0)), |
| inventoryValueAtList: round2(products.reduce((s, p) => s + p.unitPrice * p.inStock, 0)), |
| reservedUnitsTotal: products.reduce((s, p) => s + p.reservedQty, 0), |
| activeDiscountCount: discountMap.size |
| }; |
| } |
|
|
| export function shelfUnitPrice(p: Product, discountPct: number): number { |
| const d = Math.min(100, Math.max(0, discountPct)); |
| return round2(p.unitPrice * (1 - d / 100)); |
| } |
|
|
| export function profitPerUnit(p: Product, unitSell: number): number { |
| return round2(unitSell - p.costPrice); |
| } |
|
|
