-- Brocco-Lee Database Setup
-- Run this in your Supabase SQL Editor

-- Drop existing tables if they exist

-- Create products table with new weight system
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
);

-- Create orders table
CREATE TABLE orders (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  order_number VARCHAR(20) UNIQUE NOT NULL, -- Custom order number format: yyyy-mm-dd_A-xx
  total_price DECIMAL(10,2) NOT NULL,
  status VARCHAR(20) DEFAULT 'processing' CHECK (status IN ('processing', 'ready', 'picked')),
  customer_name VARCHAR(255),
  customer_phone VARCHAR(20),
  delivery_address TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create order_items table
CREATE TABLE order_items (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
  product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
  quantity_in_grams INTEGER, -- For vegetables and fruits
  quantity_units INTEGER, -- For juices and salads
  price_total DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable Row Level Security
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE order_items ENABLE ROW LEVEL SECURITY;

-- RLS Policies
-- Products: Anyone can read
CREATE POLICY "Products are viewable by everyone" ON products FOR SELECT USING (true);

-- Orders: Users can only see their own orders, admins can see all
CREATE POLICY "Users can view own orders" ON orders FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Admins can view all orders" ON orders FOR ALL USING (auth.jwt() ->> 'email' = 'soufyane.sba@gmail.com');

-- Order items: Users can only see their own order items, admins can see all
CREATE POLICY "Users can view own order items" ON order_items FOR SELECT USING (
  EXISTS (
    SELECT 1 FROM orders WHERE orders.id = order_items.order_id AND orders.user_id = auth.uid()
  )
);
CREATE POLICY "Admins can view all order items" ON order_items FOR ALL USING (auth.jwt() ->> 'email' = 'soufyane.sba@gmail.com');

-- Insert policies for orders and order_items
CREATE POLICY "Users can insert own orders" ON orders FOR INSERT WITH CHECK (auth.uid() = user_id);

-- FIXED: Allow users to insert order items for orders they own
CREATE POLICY "Users can insert own order items" ON order_items FOR INSERT WITH CHECK (
  EXISTS (
    SELECT 1 FROM orders 
    WHERE orders.id = order_items.order_id 
    AND orders.user_id = auth.uid()
  )
);

-- Update policies for admins
CREATE POLICY "Admins can update orders" ON orders FOR UPDATE USING (auth.jwt() ->> 'email' = 'soufyane.sba@gmail.com');

-- Insert sample products with new pricing (in Dhs)
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);

-- Enable real-time for orders table
ALTER PUBLICATION supabase_realtime ADD TABLE orders;

-- Create admin user (optional - you can also create this through the app)
-- INSERT INTO auth.users (email, encrypted_password, email_confirmed_at, created_at, updated_at) 
-- VALUES ('soufyane.sba@gmail.com', crypt('your_password_here', gen_salt('bf')), now(), now(), now()); 