database-schema.sql

-- Enable RLS (Row Level Security)

-- Create custom types
CREATE TYPE user_role AS ENUM ('customer', 'admin', 'delivery');
CREATE TYPE order_status AS ENUM ('preparing', 'out_for_delivery', 'delivered');
CREATE TYPE restaurant_status AS ENUM ('open', 'closed');

-- Create profiles table
CREATE TABLE profiles (
  id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  full_name TEXT,
  role user_role NOT NULL DEFAULT 'customer',
  phone TEXT,
  address TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create restaurants table
CREATE TABLE restaurants (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  logo_url TEXT,
  description TEXT,
  phone TEXT,
  address TEXT,
  delivery_time_minutes INTEGER DEFAULT 30,
  status restaurant_status DEFAULT 'open',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create categories table
CREATE TABLE categories (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  restaurant_id UUID REFERENCES restaurants(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create menu_items table
CREATE TABLE menu_items (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  image_url TEXT,
  category_id UUID REFERENCES categories(id) ON DELETE CASCADE,
  restaurant_id UUID REFERENCES restaurants(id) ON DELETE CASCADE,
  is_available BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create orders table
CREATE TABLE orders (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  token_number INTEGER NOT NULL,
  customer_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  restaurant_id UUID REFERENCES restaurants(id) ON DELETE CASCADE,
  total_amount DECIMAL(10,2) NOT NULL,
  status order_status DEFAULT 'preparing',
  note TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  order_date DATE DEFAULT CURRENT_DATE
);

-- 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,
  menu_item_id UUID REFERENCES menu_items(id) ON DELETE CASCADE,
  quantity INTEGER NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create delivery_times table for different time slots
CREATE TABLE delivery_times (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  restaurant_id UUID REFERENCES restaurants(id) ON DELETE CASCADE,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  delivery_time_minutes INTEGER NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for better performance
CREATE INDEX idx_profiles_role ON profiles(role);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_restaurant_id ON orders(restaurant_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_order_date ON orders(order_date);
CREATE INDEX idx_orders_token_number ON orders(token_number);
CREATE INDEX idx_menu_items_restaurant_id ON menu_items(restaurant_id);
CREATE INDEX idx_menu_items_category_id ON menu_items(category_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- Create function to generate daily token numbers
CREATE OR REPLACE FUNCTION generate_daily_token()
RETURNS INTEGER AS $$
DECLARE
  max_token INTEGER;
  today DATE := CURRENT_DATE;
BEGIN
  SELECT COALESCE(MAX(token_number), 0) + 1
  INTO max_token
  FROM orders
  WHERE order_date = today;
  
  RETURN max_token;
END;
$$ LANGUAGE plpgsql;

-- Create function to clean up old orders (runs at 11:59 PM daily)
CREATE OR REPLACE FUNCTION cleanup_old_orders()
RETURNS void AS $$
BEGIN
  DELETE FROM orders 
  WHERE order_date < CURRENT_DATE 
  AND status = 'delivered';
END;
$$ LANGUAGE plpgsql;

-- Create trigger to automatically generate token numbers
CREATE OR REPLACE FUNCTION set_token_number()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.token_number IS NULL THEN
    NEW.token_number := generate_daily_token();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_set_token_number
  BEFORE INSERT ON orders
  FOR EACH ROW
  EXECUTE FUNCTION set_token_number();

-- Create trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_profiles_updated_at
  BEFORE UPDATE ON profiles
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER trigger_restaurants_updated_at
  BEFORE UPDATE ON restaurants
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER trigger_menu_items_updated_at
  BEFORE UPDATE ON menu_items
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER trigger_orders_updated_at
  BEFORE UPDATE ON orders
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- Enable Row Level Security
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE restaurants ENABLE ROW LEVEL SECURITY;
ALTER TABLE categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE menu_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE order_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE delivery_times ENABLE ROW LEVEL SECURITY;

-- Create RLS policies

-- Profiles policies
CREATE POLICY "Users can view their own profile" ON profiles
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Users can update their own profile" ON profiles
  FOR UPDATE USING (auth.uid() = id);

CREATE POLICY "Users can insert their own profile" ON profiles
  FOR INSERT WITH CHECK (auth.uid() = id);

-- Restaurants policies
CREATE POLICY "Anyone can view restaurants" ON restaurants
  FOR SELECT USING (true);

CREATE POLICY "Admins can manage restaurants" ON restaurants
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM profiles 
      WHERE id = auth.uid() AND role = 'admin'
    )
  );

-- Categories policies
CREATE POLICY "Anyone can view categories" ON categories
  FOR SELECT USING (true);

CREATE POLICY "Admins can manage categories" ON categories
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM profiles 
      WHERE id = auth.uid() AND role = 'admin'
    )
  );

-- Menu items policies
CREATE POLICY "Anyone can view available menu items" ON menu_items
  FOR SELECT USING (is_available = true);

CREATE POLICY "Admins can manage menu items" ON menu_items
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM profiles 
      WHERE id = auth.uid() AND role = 'admin'
    )
  );

-- Orders policies
CREATE POLICY "Customers can view their own orders" ON orders
  FOR SELECT USING (
    customer_id = auth.uid() OR
    EXISTS (
      SELECT 1 FROM profiles 
      WHERE id = auth.uid() AND role IN ('admin', 'delivery')
    )
  );

CREATE POLICY "Customers can create orders" ON orders
  FOR INSERT WITH CHECK (
    customer_id = auth.uid() AND
    EXISTS (
      SELECT 1 FROM profiles 
      WHERE id = auth.uid() AND role = 'customer'
    )
  );

CREATE POLICY "Admins and delivery can update orders" ON orders
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM profiles 
      WHERE id = auth.uid() AND role IN ('admin', 'delivery')
    )
  );

-- Order items policies
CREATE POLICY "Users can view order items for their orders" ON order_items
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM orders 
      WHERE id = order_items.order_id AND (
        customer_id = auth.uid() OR
        EXISTS (
          SELECT 1 FROM profiles 
          WHERE id = auth.uid() AND role IN ('admin', 'delivery')
        )
      )
    )
  );

CREATE POLICY "Customers can create order items" ON order_items
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM orders 
      WHERE id = order_items.order_id AND customer_id = auth.uid()
    )
  );

-- Delivery times policies
CREATE POLICY "Anyone can view delivery times" ON delivery_times
  FOR SELECT USING (true);

CREATE POLICY "Admins can manage delivery times" ON delivery_times
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM profiles 
      WHERE id = auth.uid() AND role = 'admin'
    )
  );

-- Insert default restaurant
INSERT INTO restaurants (name, description, phone, address, delivery_time_minutes, status) 
VALUES ('FoodHub', 'Delicious food delivered to your doorstep', '+1234567890', '123 Main St, City', 30, 'open');

-- Insert default categories
INSERT INTO categories (name, description, restaurant_id) 
SELECT 'Appetizers', 'Start your meal right', id FROM restaurants WHERE name = 'FoodHub';

INSERT INTO categories (name, description, restaurant_id) 
SELECT 'Main Course', 'Hearty main dishes', id FROM restaurants WHERE name = 'FoodHub';

INSERT INTO categories (name, description, restaurant_id) 
SELECT 'Desserts', 'Sweet endings', id FROM restaurants WHERE name = 'FoodHub';

INSERT INTO categories (name, description, restaurant_id) 
SELECT 'Beverages', 'Refreshing drinks', id FROM restaurants WHERE name = 'FoodHub';

-- Insert sample menu items
INSERT INTO menu_items (name, description, price, category_id, restaurant_id, is_available)
SELECT 'Chicken Wings', 'Crispy chicken wings with your choice of sauce', 12.99, c.id, r.id, true
FROM categories c, restaurants r 
WHERE c.name = 'Appetizers' AND r.name = 'FoodHub';

INSERT INTO menu_items (name, description, price, category_id, restaurant_id, is_available)
SELECT 'Caesar Salad', 'Fresh romaine lettuce with caesar dressing', 8.99, c.id, r.id, true
FROM categories c, restaurants r 
WHERE c.name = 'Appetizers' AND r.name = 'FoodHub';

INSERT INTO menu_items (name, description, price, category_id, restaurant_id, is_available)
SELECT 'Grilled Chicken', 'Tender grilled chicken breast with vegetables', 16.99, c.id, r.id, true
FROM categories c, restaurants r 
WHERE c.name = 'Main Course' AND r.name = 'FoodHub';

INSERT INTO menu_items (name, description, price, category_id, restaurant_id, is_available)
SELECT 'Beef Burger', 'Juicy beef patty with fresh vegetables', 14.99, c.id, r.id, true
FROM categories c, restaurants r 
WHERE c.name = 'Main Course' AND r.name = 'FoodHub';

INSERT INTO menu_items (name, description, price, category_id, restaurant_id, is_available)
SELECT 'Chocolate Cake', 'Rich chocolate cake with vanilla ice cream', 6.99, c.id, r.id, true
FROM categories c, restaurants r 
WHERE c.name = 'Desserts' AND r.name = 'FoodHub';

INSERT INTO menu_items (name, description, price, category_id, restaurant_id, is_available)
SELECT 'Soft Drink', 'Coca Cola, Pepsi, or Sprite', 2.99, c.id, r.id, true
FROM categories c, restaurants r 
WHERE c.name = 'Beverages' AND r.name = 'FoodHub';

-- Insert default delivery times
INSERT INTO delivery_times (restaurant_id, start_time, end_time, delivery_time_minutes)
SELECT id, '09:00', '12:00', 25 FROM restaurants WHERE name = 'FoodHub';

INSERT INTO delivery_times (restaurant_id, start_time, end_time, delivery_time_minutes)
SELECT id, '12:00', '15:00', 35 FROM restaurants WHERE name = 'FoodHub';

INSERT INTO delivery_times (restaurant_id, start_time, end_time, delivery_time_minutes)
SELECT id, '15:00', '18:00', 30 FROM restaurants WHERE name = 'FoodHub';

INSERT INTO delivery_times (restaurant_id, start_time, end_time, delivery_time_minutes)
SELECT id, '18:00', '22:00', 40 FROM restaurants WHERE name = 'FoodHub';