-- FaroDash Restaurant Management System Database Schema

-- Create database
CREATE DATABASE IF NOT EXISTS farodash_db;
USE farodash_db;

-- Food categories table
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    icon_url VARCHAR(255),
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Food items table
CREATE TABLE food_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    restaurant_id INT NOT NULL,
    category_id INT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(8,2) NOT NULL,
    image_url VARCHAR(255),
    is_available BOOLEAN DEFAULT 1,
    preparation_time INT DEFAULT 15,
    ingredients TEXT,
    allergens TEXT,
    calories INT,
    is_vegetarian BOOLEAN DEFAULT 0,
    is_spicy BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Customers table
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    phone VARCHAR(20) UNIQUE NOT NULL,
    address TEXT,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    customer_id INT NOT NULL,
    restaurant_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    delivery_fee DECIMAL(8,2) DEFAULT 0.00,
    tax_amount DECIMAL(8,2) DEFAULT 0.00,
    discount_amount DECIMAL(8,2) DEFAULT 0.00,
    final_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'confirmed', 'preparing', 'ready', 'picked_up', 'delivered', 'cancelled') DEFAULT 'pending',
    payment_status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending',
    payment_method VARCHAR(50),
    delivery_address TEXT NOT NULL,
    delivery_instructions TEXT,
    delivery_distance_km DECIMAL(6,2),
    estimated_delivery_time DATETIME,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
);

-- Order items table
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    food_item_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(8,2) NOT NULL,
    total_price DECIMAL(8,2) NOT NULL,
    special_instructions TEXT,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (food_item_id) REFERENCES food_items(id)
);

-- **NEW** Order addons table
CREATE TABLE order_addons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_item_id INT NOT NULL,
    addon_name VARCHAR(255) NOT NULL,
    addon_price DECIMAL(8,2) NOT NULL,
    FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE
);

-- **NEW** Food item addons table (for customization)
CREATE TABLE food_item_addons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    restaurant_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
);

-- **NEW** Link addons to food items (many-to-many relationship)
CREATE TABLE food_item_addon_links (
    food_item_id INT NOT NULL,
    addon_id INT NOT NULL,
    PRIMARY KEY (food_item_id, addon_id),
    FOREIGN KEY (food_item_id) REFERENCES food_items(id) ON DELETE CASCADE,
    FOREIGN KEY (addon_id) REFERENCES food_item_addons(id) ON DELETE CASCADE
);

-- Restaurant earnings table
CREATE TABLE restaurant_earnings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    restaurant_id INT NOT NULL,
    order_id INT NOT NULL,
    gross_amount DECIMAL(10,2) NOT NULL,
    commission_rate DECIMAL(5,2) DEFAULT 15.00,
    commission_amount DECIMAL(10,2) NOT NULL,
    net_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'paid', 'on_hold') DEFAULT 'pending',
    payment_date DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id),
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- Restaurant reviews table
CREATE TABLE restaurant_reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    restaurant_id INT NOT NULL,
    customer_id INT NOT NULL,
    order_id INT NOT NULL,
    rating INT CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- Support messages table
CREATE TABLE support_messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    restaurant_id INT NOT NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    status ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    admin_response TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
);

-- Insert sample categories
INSERT INTO categories (name, icon_url) VALUES
('Jollof Rice', 'images/jollof.png'),
('Pasta', 'images/pasta.png'),
('Chicken', 'images/chicken.png'),
('Local Food', 'images/local-food.png'),
('Amala', 'images/amala.png'),
('Ice Cream', 'images/ice-cream.png'),
('Pizza', 'images/pizza.png'),
('Shawarma', 'images/shawarma.png'),
('Breakfast', 'images/breakfast.png'),
('Groceries', 'images/groceries.png'),
('Protein', 'images/protein.png'),
('Beauty', 'images/beauty.png');

-- Insert sample food items
INSERT INTO food_items (restaurant_id, category_id, name, description, price, image_url, preparation_time) VALUES
(1, 1, 'Fried Rice with chicken and mixed vegetables beef', 'Delicious fried rice with tender chicken pieces and fresh vegetables', 4500.00, 'images/food.png', 25),
(1, 2, 'Noodles with groundnut oil and spicy seasoning', 'Spicy noodles cooked with groundnut oil and special seasoning', 3500.00, 'images/food.png', 15),
(1, 4, 'Amala with ewes and chicken', 'Traditional amala served with ewes and chicken', 4500.00, 'images/food.png', 30),
(1, 1, 'Jollof Rice with grilled chicken and plantain', 'Classic jollof rice with grilled chicken and fried plantain', 6500.00, 'images/food.png', 35);
CREATE INDEX idx_orders_restaurant_status ON orders(restaurant_id, status);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_food_items_restaurant ON food_items(restaurant_id, is_available);
CREATE INDEX idx_earnings_restaurant ON restaurant_earnings(restaurant_id, status);
CREATE INDEX idx_addons_restaurant ON food_item_addons(restaurant_id);