import productsData from './products.json';
import { getDb } from './db';

export interface Product {
  id: number;
  name: string;
  name_en: string;
  slug: string;
  description: string;
  description_en: string;
  price_cents: number;
  unit_size: number;
  unit: string;
  images: string;
  category: string;
  stock_count: number;
  harvest_year: number | null;
  usage: string;
  usage_en: string;
  ord: number;
  created_at: string;
}

export function seedProducts() {
  const db = getDb();
  const count = db.query('SELECT COUNT(*) as c FROM products').get() as { c: number };
  if (count.c > 0) return count.c;
  const stmt = db.prepare(
    `INSERT INTO products (name, name_en, slug, description, description_en, price_cents, unit_size, unit, images, category, stock_count, harvest_year, usage, usage_en, ord)
     VALUES ($name, $name_en, $slug, $description, $description_en, $price_cents, $unit_size, $unit, $images, $category, $stock_count, $harvest_year, $usage, $usage_en, $ord)`
  );
  productsData.forEach((p: any, i: number) => {
    const img = p.image || '';
    stmt.run({
      $name: p.name,
      $name_en: p.name_en || '',
      $slug: p.slug,
      $description: p.description || '',
      $description_en: p.description_en || '',
      $price_cents: p.price_cents,
      $unit_size: p.unit_size || (p.unit === 'kg' ? 1000 : 100),
      $unit: 'g',
      $images: img ? JSON.stringify([img]) : '[]',
      $category: p.category || 'bilki',
      $stock_count: p.in_stock !== undefined ? p.in_stock : 1,
      $harvest_year: p.harvest_year || null,
      $usage: p.usage || '',
      $usage_en: p.usage_en || '',
      $ord: i,
    });
  });
  return (productsData as any[]).length;
}

export function getAllProducts(): Product[] {
  return getDb().query('SELECT * FROM products ORDER BY ord, id').all() as Product[];
}

export function getProductBySlug(slug: string): Product | null {
  return (getDb().query('SELECT * FROM products WHERE slug = ?').get(slug) as Product) || null;
}

export function getProductsByCategory(category: string): Product[] {
  return getDb().query('SELECT * FROM products WHERE category = ? ORDER BY id').all(category) as Product[];
}

export function getCategories(): { category: string; count: number }[] {
  return getDb().query('SELECT category, COUNT(*) as count FROM products GROUP BY category ORDER BY category').all() as { category: string; count: number }[];
}

export function firstImage(images: string): string {
  try {
    const arr = JSON.parse(images);
    return arr?.[0] || '/images/logo.png';
  } catch { return images || '/images/logo.png'; }
}

export function parseImages(images: string): string[] {
  try { return JSON.parse(images); } catch { return []; }
}