-- =====================================================
-- ORDERFOOD PORTAL - MySQL Database Schema
-- =====================================================

CREATE DATABASE IF NOT EXISTS orderfood_db;
USE orderfood_db;

-- =====================================================
-- Categories Table
-- =====================================================
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    image VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =====================================================
-- Menu Items Table
-- =====================================================
CREATE TABLE menu_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    discount_price DECIMAL(10, 2),
    image VARCHAR(255),
    is_vegetarian BOOLEAN DEFAULT FALSE,
    is_spicy BOOLEAN DEFAULT FALSE,
    is_featured BOOLEAN DEFAULT FALSE,
    is_available BOOLEAN DEFAULT TRUE,
    preparation_time INT DEFAULT 30, -- in minutes
    calories INT,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

-- =====================================================
-- Menu Item Sizes/Variants
-- =====================================================
CREATE TABLE item_variants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    menu_item_id INT NOT NULL,
    name VARCHAR(50) NOT NULL, -- Small, Medium, Large
    price_addition DECIMAL(10, 2) DEFAULT 0,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE
);

-- =====================================================
-- Addons/Toppings
-- =====================================================
CREATE TABLE addons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category VARCHAR(50), -- cheese, sauce, vegetable, meat
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =====================================================
-- Orders Table
-- =====================================================
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(20) UNIQUE NOT NULL,
    user_id INT,
    customer_name VARCHAR(100) NOT NULL,
    customer_email VARCHAR(100),
    customer_phone VARCHAR(20) NOT NULL,
    delivery_address TEXT NOT NULL,
    subtotal DECIMAL(10, 2) NOT NULL,
    delivery_fee DECIMAL(10, 2) DEFAULT 0,
    tax DECIMAL(10, 2) DEFAULT 0,
    discount DECIMAL(10, 2) DEFAULT 0,
    total_amount DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('cash', 'card', 'online') DEFAULT 'cash',
    payment_status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending',
    order_status ENUM('pending', 'confirmed', 'preparing', 'ready', 'out_for_delivery', 'delivered', 'cancelled') DEFAULT 'pending',
    order_type ENUM('delivery', 'pickup') DEFAULT 'delivery',
    special_instructions TEXT,
    estimated_delivery_time DATETIME,
    delivered_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- =====================================================
-- Order Items Table
-- =====================================================
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    menu_item_id INT NOT NULL,
    variant_id INT,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    special_instructions TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE,
    FOREIGN KEY (variant_id) REFERENCES item_variants(id) ON DELETE SET NULL
);

-- =====================================================
-- Order Item Addons
-- =====================================================
CREATE TABLE order_item_addons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_item_id INT NOT NULL,
    addon_id INT NOT NULL,
    quantity INT DEFAULT 1,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE,
    FOREIGN KEY (addon_id) REFERENCES addons(id) ON DELETE CASCADE
);

-- =====================================================
-- Coupons/Discounts
-- =====================================================
CREATE TABLE coupons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    discount_type ENUM('percentage', 'fixed') DEFAULT 'percentage',
    discount_value DECIMAL(10, 2) NOT NULL,
    min_order_amount DECIMAL(10, 2) DEFAULT 0,
    max_discount DECIMAL(10, 2),
    usage_limit INT,
    used_count INT DEFAULT 0,
    start_date DATE,
    end_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =====================================================
-- Reviews/Ratings
-- =====================================================
CREATE TABLE reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_id INT,
    menu_item_id INT,
    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    is_approved BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
    FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE
);

-- =====================================================
-- Settings Table
-- =====================================================
CREATE TABLE settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type VARCHAR(50) DEFAULT 'text',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =====================================================
-- Contact Messages
-- =====================================================
CREATE TABLE contact_messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    subject VARCHAR(200),
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Default Categories
INSERT INTO categories (name, description, image, sort_order) VALUES
('Pizza', 'Delicious stone-baked pizzas with fresh toppings', 'pizza.jpg', 1),
('Burgers', 'Juicy grill burgers with special sauce', 'burgers.jpg', 2),
('Pasta', 'Italian pasta with authentic flavors', 'pasta.jpg', 3),
('Drinks', 'Refreshing beverages and soft drinks', 'drinks.jpg', 4),
('Desserts', 'Sweet treats to end your meal', 'desserts.jpg', 5),
('Sides', 'Perfect companions for your meal', 'sides.jpg', 6);

-- Sample Menu Items
INSERT INTO menu_items (category_id, name, description, price, discount_price, is_featured, is_vegetarian, is_spicy, preparation_time) VALUES
(1, 'Margherita Pizza', 'Classic pizza with tomato sauce, mozzarella, and fresh basil', 899.00, NULL, TRUE, TRUE, FALSE, 25),
(1, 'Pepperoni Feast', 'Loaded with pepperoni slices and extra cheese', 1199.00, 999.00, TRUE, FALSE, FALSE, 25),
(1, 'BBQ Chicken Pizza', 'Grilled chicken with BBQ sauce and onions', 1299.00, NULL, TRUE, FALSE, FALSE, 30),
(1, 'Veggie Supreme', 'Loaded with fresh vegetables and olives', 1099.00, NULL, FALSE, TRUE, FALSE, 25),
(2, 'Classic Beef Burger', 'Juicy beef patty with lettuce, tomato, and special sauce', 599.00, NULL, TRUE, FALSE, FALSE, 15),
(2, 'Chicken Zinger', 'Crispy fried chicken with spicy mayo', 549.00, NULL, TRUE, FALSE, TRUE, 15),
(2, 'Double Cheese Burger', 'Two beef patties with double cheese', 799.00, 699.00, FALSE, FALSE, FALSE, 20),
(3, 'Spaghetti Bolognese', 'Classic Italian pasta with meat sauce', 749.00, NULL, FALSE, FALSE, FALSE, 20),
(3, 'Alfredo Pasta', 'Creamy white sauce pasta with mushrooms', 699.00, NULL, FALSE, TRUE, FALSE, 20),
(4, 'Coca Cola', 'Chilled 500ml bottle', 120.00, NULL, FALSE, TRUE, FALSE, 2),
(4, 'Fresh Lime Soda', 'Refreshing lime drink with soda', 150.00, NULL, FALSE, TRUE, FALSE, 5),
(5, 'Chocolate Lava Cake', 'Warm chocolate cake with molten center', 399.00, NULL, TRUE, TRUE, FALSE, 15),
(5, 'Ice Cream Sundae', 'Vanilla ice cream with chocolate sauce', 299.00, NULL, FALSE, TRUE, FALSE, 5),
(6, 'French Fries', 'Crispy golden fries with ketchup', 199.00, NULL, FALSE, TRUE, FALSE, 10),
(6, 'Garlic Bread', 'Toasted bread with garlic butter', 249.00, NULL, FALSE, TRUE, FALSE, 10);

-- Pizza Sizes
INSERT INTO item_variants (menu_item_id, name, price_addition, is_default) VALUES
(1, 'Small (8")', 0, FALSE),
(1, 'Medium (10")', 200, TRUE),
(1, 'Large (12")', 400, FALSE),
(1, 'Family (14")', 600, FALSE),
(2, 'Small (8")', 0, FALSE),
(2, 'Medium (10")', 200, TRUE),
(2, 'Large (12")', 400, FALSE),
(3, 'Small (8")', 0, FALSE),
(3, 'Medium (10")', 200, TRUE),
(3, 'Large (12")', 400, FALSE),
(4, 'Small (8")', 0, FALSE),
(4, 'Medium (10")', 200, TRUE),
(4, 'Large (12")', 400, FALSE);

-- Addons
INSERT INTO addons (name, price, category) VALUES
('Extra Cheese', 100.00, 'cheese'),
('Mozzarella', 120.00, 'cheese'),
('Jalapenos', 50.00, 'vegetable'),
('Mushrooms', 80.00, 'vegetable'),
('Olives', 70.00, 'vegetable'),
('Pepperoni', 150.00, 'meat'),
('Chicken', 180.00, 'meat'),
('BBQ Sauce', 40.00, 'sauce'),
('Garlic Sauce', 40.00, 'sauce');

-- Default Settings
INSERT INTO settings (setting_key, setting_value, setting_type) VALUES
('site_name', 'OrderFood Portal', 'text'),
('site_tagline', 'Delicious Food Delivered Fresh', 'text'),
('delivery_fee', '100', 'number'),
('min_order_amount', '500', 'number'),
('tax_percentage', '5', 'number'),
('currency_symbol', 'Rs.', 'text'),
('contact_phone', '+92 300 1234567', 'text'),
('contact_email', 'info@orderfood.com', 'text'),
('contact_address', '123 Food Street, Lahore, Pakistan', 'text'),
('opening_hours', '10:00 AM - 11:00 PM', 'text'),
('facebook_url', 'https://facebook.com/orderfood', 'text'),
('instagram_url', 'https://instagram.com/orderfood', 'text');

-- Sample Coupon
INSERT INTO coupons (code, description, discount_type, discount_value, min_order_amount, max_discount, end_date) VALUES
('WELCOME20', 'Welcome discount - 20% off on first order', 'percentage', 20, 500, 300, '2025-12-31'),
('FLAT100', 'Flat Rs.100 off on orders above Rs.800', 'fixed', 100, 800, NULL, '2025-12-31');

