-- Update Brocco-Lee Database to new simplified pricing structure
-- Run this in your Supabase SQL Editor

-- Step 1: Drop existing products table (this will also drop any foreign key references)

-- Step 2: Create new products table with simplified pricing
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  category VARCHAR(50) NOT NULL,
  price_per_kg DECIMAL(10,2) NOT NULL,
  price_per_unit DECIMAL(10,2),
  description TEXT,
  image_url VARCHAR(500),
  stock_quantity INTEGER DEFAULT 0,
  is_available BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 3: Insert products with new pricing structure
INSERT INTO products (name, category, price_per_kg, price_per_unit, stock_quantity, is_available) VALUES
-- Vegetables (weight-based pricing)
('Fresh Broccoli', 'vegetables', 8.00, 0, 100, true),
('Organic Carrots', 'vegetables', 6.00, 0, 150, true),
('Sweet Bell Peppers', 'vegetables', 10.00, 0, 80, true),
('Fresh Spinach', 'vegetables', 7.50, 0, 120, true),
('Organic Tomatoes', 'vegetables', 7.00, 0, 200, true),
('Fresh Cucumber', 'vegetables', 5.00, 0, 180, true),
('Green Zucchini', 'vegetables', 7.50, 0, 90, true),
('Fresh Cauliflower', 'vegetables', 11.00, 0, 70, true),
('Organic Onions', 'vegetables', 4.00, 0, 250, true),
('Fresh Garlic', 'vegetables', 9.50, 0, 120, true),

-- Fruits (weight-based pricing)
('Organic Apples', 'fruits', 9.50, 0, 200, true),
('Fresh Bananas', 'fruits', 5.00, 0, 300, true),
('Sweet Oranges', 'fruits', 7.00, 0, 150, true),
('Fresh Strawberries', 'fruits', 15.00, 0, 100, true),
('Organic Grapes', 'fruits', 11.00, 0, 120, true),
('Fresh Pineapple', 'fruits', 13.00, 0, 80, true),
('Organic Pears', 'fruits', 8.00, 0, 110, true),
('Fresh Mango', 'fruits', 10.00, 0, 95, true),
('Organic Peaches', 'fruits', 12.00, 0, 85, true),
('Fresh Blueberries', 'fruits', 17.00, 0, 70, true),

-- Juices (bottle-based pricing)
('Green Juice', 'juices', 0, 25.00, 50, true),
('Orange Juice', 'juices', 0, 18.00, 75, true),
('Mixed Berry Juice', 'juices', 0, 30.00, 60, true),
('Apple Juice', 'juices', 0, 20.00, 80, true),
('Carrot Juice', 'juices', 0, 22.00, 45, true),
('Pineapple Juice', 'juices', 0, 24.00, 55, true),
('Grape Juice', 'juices', 0, 19.00, 70, true),
('Lemon Juice', 'juices', 0, 15.00, 90, true),
('Cucumber Juice', 'juices', 0, 28.00, 40, true),
('Beet Juice', 'juices', 0, 32.00, 35, true),

-- Salads (package-based pricing)
('Fresh Garden Salad', 'salads', 0, 45.00, 40, true),
('Caesar Salad', 'salads', 0, 55.00, 35, true),
('Greek Salad', 'salads', 0, 48.00, 45, true),
('Caprese Salad', 'salads', 0, 52.00, 30, true),
('Asian Slaw', 'salads', 0, 42.00, 50, true),
('Mediterranean Salad', 'salads', 0, 46.00, 40, true),
('Kale Caesar', 'salads', 0, 58.00, 35, true),
('Quinoa Bowl', 'salads', 0, 65.00, 25, true),
('Spinach Salad', 'salads', 0, 38.00, 55, true),
('Fruit Salad', 'salads', 0, 50.00, 45, true);

-- Step 4: Verify the data
SELECT 
  category,
  COUNT(*) as count,
  CASE 
    WHEN category IN ('vegetables', 'fruits') THEN 'Weight-based pricing'
    ELSE 'Unit-based pricing'
  END as pricing_type
FROM products 
GROUP BY category 
ORDER BY category;

-- Step 5: Show sample products
SELECT name, category, price_per_kg, price_per_unit, stock_quantity
FROM products 
ORDER BY category, name 
LIMIT 10; 