1,911 products and 6 commodity indexes tracked

import type Database from "better-sqlite3";

interface ShopifyVariant {
  id: number;
  title: string;
  price: string;
  compare_at_price: string | null;
  sku: string | null;
  available: boolean;
}

interface ShopifyProduct {
  id: number;
  title: string;
  vendor: string;
  product_type: string;
  tags: string[];
  images: { src: string }[];
  variants: ShopifyVariant[];
  handle: string;
}

interface SupplierConfig {
  id: string;
  name: string;
  base_url: string;
  collections: string[];
  delay: number;
}

export const SUPPLIERS: SupplierConfig[] = [
  {
    id: "barista_underground",
    name: "Barista Underground",
    base_url: "https://www.baristaunderground.com",
    collections: [
      "syrups", "sauces", "chai", "matcha", "coffee",
      "alt-milk-collection", "best-sellers", "tea",
    ],
    delay: 3000,
  },
  {
    id: "westrock_coffee",
    name: "Westrock Coffee",
    base_url: "https://shop.westrockcoffee.com",
    collections: [],
    delay: 3000,
  },
  {
    id: "elmhurst_1925",
    name: "Elmhurst 1925",
    base_url: "https://www.elmhurst1925.com",
    collections: [],
    delay: 3000,
  },
];

function extractUnitCount(variantTitle: string): number {
  // "(24 cartons)" or "(24 total)"
  const parenMatch = variantTitle.match(/\((\d+)\s*(?:cartons?|total|units?|bottles?|cans?)\)/i);
  if (parenMatch) return parseInt(parenMatch[1]);

  // "N cases of M"
  const casesOfMatch = variantTitle.match(/(\d+)\s*cases?\s*of\s*(\d+)/i);
  if (casesOfMatch) return parseInt(casesOfMatch[1]) * parseInt(casesOfMatch[2]);

  // "Case of N"
  const caseMatch = variantTitle.match(/case\s*of\s*(\d+)/i);
  if (caseMatch) return parseInt(caseMatch[1]);

  // "N-pack" or "N pack"
  const packMatch = variantTitle.match(/(\d+)\s*-?\s*pack/i);
  if (packMatch) return parseInt(packMatch[1]);

  return 1;
}

async function fetchWithRetry(url: string, retries = 3): Promise<Response | null> {
  for (let i = 0; i < retries; i++) {
    try {

import { getDb } from "./db";

export interface ProductWithPrice {
  product_id: string;
  title: string;
  vendor: string | null;
  product_type: string | null;
  image_url: string | null;
  product_url: string | null;
  supplier_name: string;
  supplier_id: string;
  variant_id: string;
  variant_title: string;
  price: number;
  compare_at_price: number | null;
  per_unit_price: number;
  unit_count: number;
  available: number;
  discount_pct: number | null;
}

export interface CommodityData {
  series_id: string;
  series_name: string;
  source: string;
  unit: string;
  latest_value: number;
  latest_period: string;
  prev_value: number | null;
  prev_period: string | null;
  change_pct: number | null;
}

export interface DashboardStats {
  total_products: number;
  total_suppliers: number;
  items_on_sale: number;
  commodity_series: number;
}

export function getDashboardStats(): DashboardStats {
  const db = getDb();
  const products = db.prepare("SELECT COUNT(DISTINCT product_id) as count FROM variants").get() as { count: number };
  const suppliers = db.prepare("SELECT COUNT(*) as count FROM suppliers").get() as { count: number };
  const sales = db.prepare(`
    SELECT COUNT(DISTINCT p2.variant_id) as count
    FROM prices p2
    WHERE p2.compare_at_price IS NOT NULL
    AND p2.id = (SELECT MAX(id) FROM prices WHERE variant_id = p2.variant_id)
  `).get() as { count: number };
  const commodities = db.prepare("SELECT COUNT(DISTINCT series_id) as count FROM commodity_prices").get() as { count: number };

  return {
    total_products: products.count,
    total_suppliers: suppliers.count,
    items_on_sale: sales.count,
    commodity_series: commodities.count,
  };
}

export function getSales(): ProductWithPrice[] {
  const db = getDb();
  return db.prepare(`
    SELECT
      p.id as product_id,
      p.title,
      p.vendor,
      p.product_type,
      p.image_url,
      p.product_url,
      s.name as supplier_name,
      s.id as supplier_id,
      v.id as variant_id,
      v.title as variant_title,
      pr.price,
      pr.compare_at_price,
      pr.per_unit_price,
      v.unit_count,
      v.available,
      ROUND((1.0 - pr.price / pr.compare_at_price) * 100, 1) as discount_pct