-- ============================================================
-- Restaurant Products Demo Data Migration
-- Purpose: Seed realistic restaurant products organized by categories
-- Date: 2025-11-26
-- ============================================================

-- Optional: Clean up existing test products (uncomment if needed)
-- DELETE FROM inventory WHERE org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid;
-- DELETE FROM product_batch_number WHERE product_id IN (SELECT product_id FROM products WHERE org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid);
-- DELETE FROM products WHERE org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid AND (name LIKE 'Cosmetic%' OR name LIKE 'Personal Care%');
-- Insert restaurant product categories
INSERT INTO product_categories (category_id, name, org_id, isDeleted, updatedAt) VALUES
  (uuid_generate_v7(), 'Appetizers', '0194a8b2-1234-7123-8123-123456789abc'::uuid, FALSE, NOW()),
  (uuid_generate_v7(), 'Main Course', '0194a8b2-1234-7123-8123-123456789abc'::uuid, FALSE, NOW()),
  (uuid_generate_v7(), 'Beverages', '0194a8b2-1234-7123-8123-123456789abc'::uuid, FALSE, NOW()),
  (uuid_generate_v7(), 'Desserts', '0194a8b2-1234-7123-8123-123456789abc'::uuid, FALSE, NOW()),
  (uuid_generate_v7(), 'Seafood', '0194a8b2-1234-7123-8123-123456789abc'::uuid, FALSE, NOW()),
  (uuid_generate_v7(), 'Salads', '0194a8b2-1234-7123-8123-123456789abc'::uuid, FALSE, NOW()),
  (uuid_generate_v7(), 'Sides', '0194a8b2-1234-7123-8123-123456789abc'::uuid, FALSE, NOW())
ON CONFLICT DO NOTHING;

-- Insert restaurant products with realistic Fijian/Pacific restaurant menu items
INSERT INTO products (product_id, name, description, price, org_id, barcode, brand, category_id, isDeleted, updatedAt) VALUES
  -- Appetizers (5 products)
  (uuid_generate_v7(), 'Kokoda (Fijian Ceviche)', 'Fresh fish marinated in lime and coconut cream', 18.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'APP001', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Appetizers' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Chicken Spring Rolls', 'Crispy rolls with vegetables and chicken', 12.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'APP002', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Appetizers' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Samosas (Vegetable)', 'Spiced potato and pea pastries', 10.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'APP003', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Appetizers' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Garlic Prawns', 'Sautéed prawns in garlic butter', 22.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'APP004', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Appetizers' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Palusami Bites', 'Taro leaves in coconut cream (mini portions)', 14.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'APP005', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Appetizers' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),

  -- Main Course (8 products)
  (uuid_generate_v7(), 'Lovo Platter', 'Traditional earth oven cooked meats and vegetables', 35.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'MAIN001', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Main Course' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Chicken Curry', 'Aromatic curry with rice and roti', 24.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'MAIN002', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Main Course' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Beef Chop Suey', 'Stir-fried beef with mixed vegetables', 26.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'MAIN003', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Main Course' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Lamb Rogan Josh', 'Tender lamb in rich tomato curry sauce', 32.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'MAIN004', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Main Course' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Fish & Chips', 'Beer-battered fish with fries and tartare sauce', 23.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'MAIN005', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Main Course' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Vegetable Biryani', 'Fragrant rice with spiced vegetables', 20.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'MAIN006', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Main Course' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'BBQ Pork Ribs', 'Grilled ribs with house BBQ sauce', 29.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'MAIN007', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Main Course' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Chicken Teriyaki', 'Grilled chicken with teriyaki glaze and steamed rice', 25.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'MAIN008', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Main Course' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),

  -- Seafood (4 products)
  (uuid_generate_v7(), 'Grilled Mahi Mahi', 'Fresh local fish with lemon butter sauce', 34.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'SEA001', 'Ocean Fresh', (SELECT category_id FROM product_categories WHERE name = 'Seafood' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Coconut Curry Fish', 'Fish cooked in coconut curry sauce', 28.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'SEA002', 'Ocean Fresh', (SELECT category_id FROM product_categories WHERE name = 'Seafood' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Prawn Masala', 'Prawns in spicy masala sauce with rice', 32.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'SEA003', 'Ocean Fresh', (SELECT category_id FROM product_categories WHERE name = 'Seafood' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Seafood Platter', 'Mixed grilled seafood with vegetables', 45.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'SEA004', 'Ocean Fresh', (SELECT category_id FROM product_categories WHERE name = 'Seafood' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),

  -- Salads (3 products)
  (uuid_generate_v7(), 'Caesar Salad', 'Crisp romaine with parmesan and croutons', 16.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'SAL001', 'Fresh Garden', (SELECT category_id FROM product_categories WHERE name = 'Salads' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Garden Salad', 'Mixed greens with house dressing', 12.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'SAL002', 'Fresh Garden', (SELECT category_id FROM product_categories WHERE name = 'Salads' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Tropical Fruit Salad', 'Fresh seasonal tropical fruits', 14.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'SAL003', 'Fresh Garden', (SELECT category_id FROM product_categories WHERE name = 'Salads' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),

  -- Sides (4 products)
  (uuid_generate_v7(), 'Cassava Chips', 'Crispy fried cassava', 8.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'SIDE001', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Sides' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Roti Bread', 'Traditional flatbread (2 pieces)', 5.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'SIDE002', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Sides' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Steamed Rice', 'Jasmine rice', 6.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'SIDE003', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Sides' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'French Fries', 'Crispy golden fries', 7.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'SIDE004', 'House Special', (SELECT category_id FROM product_categories WHERE name = 'Sides' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),

  -- Beverages (5 products)
  (uuid_generate_v7(), 'Fresh Coconut Water', 'Chilled fresh coconut', 8.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'BEV001', 'Tropical Drinks', (SELECT category_id FROM product_categories WHERE name = 'Beverages' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Mango Lassi', 'Creamy mango yogurt drink', 9.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'BEV002', 'Tropical Drinks', (SELECT category_id FROM product_categories WHERE name = 'Beverages' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Fiji Bitter (330ml)', 'Local beer', 7.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'BEV003', 'Paradise Beverages', (SELECT category_id FROM product_categories WHERE name = 'Beverages' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Fiji Gold (330ml)', 'Premium local beer', 8.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'BEV004', 'Paradise Beverages', (SELECT category_id FROM product_categories WHERE name = 'Beverages' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Soft Drink (Can)', 'Assorted soft drinks', 4.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'BEV005', 'Coca-Cola', (SELECT category_id FROM product_categories WHERE name = 'Beverages' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),

  -- Desserts (3 products)
  (uuid_generate_v7(), 'Cassava Cake', 'Traditional baked cassava dessert', 11.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'DES001', 'Sweet House', (SELECT category_id FROM product_categories WHERE name = 'Desserts' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Coconut Ice Cream', 'Homemade coconut ice cream', 9.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'DES002', 'Sweet House', (SELECT category_id FROM product_categories WHERE name = 'Desserts' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW()),
  (uuid_generate_v7(), 'Tropical Fruit Tart', 'Pastry with fresh tropical fruits', 13.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'DES003', 'Sweet House', (SELECT category_id FROM product_categories WHERE name = 'Desserts' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), FALSE, NOW())
ON CONFLICT DO NOTHING;


-- Summary comment
-- Total products seeded: 32
-- Categories: Appetizers (5), Main Course (8), Seafood (4), Salads (3), Sides (4), Beverages (5), Desserts (3)
-- All products are linked to the Cybex PTE LTD organization
-- Products include realistic Fijian/Pacific restaurant menu items with appropriate pricing in FJD
