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> {
  // Always store in memory fallback
  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);
}