-- Brocco-Lee Local Database Setup
-- PostgreSQL 15+ compatible

-- Drop existing tables if they exist
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS users CASCADE;

-- Create users table for local authentication
CREATE TABLE users (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('user', 'admin')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create index for email lookups
CREATE INDEX idx_users_email ON users(email);

-- 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),
  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 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 indexes for orders
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- 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()
);

-- Create index for order_items
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- Insert sample products with 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, NULL, 100, true),
('Organic Carrots', 'vegetables', 6.00, NULL, 150, true),
('Sweet Bell Peppers', 'vegetables', 10.00, NULL, 80, true),
('Fresh Spinach', 'vegetables', 7.50, NULL, 120, true),
('Organic Tomatoes', 'vegetables', 7.00, NULL, 200, true),
('Fresh Cucumber', 'vegetables', 5.00, NULL, 180, true),
('Green Zucchini', 'vegetables', 7.50, NULL, 90, true),
('Fresh Cauliflower', 'vegetables', 11.00, NULL, 70, true),
('Organic Onions', 'vegetables', 4.00, NULL, 250, true),
('Fresh Garlic', 'vegetables', 9.50, NULL, 120, true),

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

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

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