-- Foodies Database Schema
-- Upload this to your cPanel phpMyAdmin

CREATE DATABASE IF NOT EXISTS xqyawlis_foodies;
USE xqyawlis_foodies;

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Restaurants Table
CREATE TABLE IF NOT EXISTS restaurants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    image VARCHAR(255),
    cuisine VARCHAR(50),
    rating DECIMAL(2,1) DEFAULT 0.0,
    delivery_time VARCHAR(50),
    delivery_fee DECIMAL(10,2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Menu Items Table
CREATE TABLE IF NOT EXISTS menu_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    restaurant_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    image VARCHAR(255),
    category VARCHAR(50),
    is_veg BOOLEAN DEFAULT TRUE,
    is_available BOOLEAN DEFAULT TRUE,
    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
);

-- Orders Table
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    restaurant_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    delivery_fee DECIMAL(10,2) DEFAULT 0.00,
    status ENUM('pending', 'confirmed', 'preparing', 'out_for_delivery', 'delivered', 'cancelled') DEFAULT 'pending',
    delivery_address TEXT,
    phone VARCHAR(20),
    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 CASCADE,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
);

-- Order Items Table
CREATE TABLE IF NOT EXISTS order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    menu_item_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE
);

-- Admin Users Table
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default admin user (username: admin, password: admin123)
INSERT INTO admin_users (username, password, email) 
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@foodies.com');

-- Insert sample restaurants
INSERT INTO restaurants (name, image, cuisine, rating, delivery_time, delivery_fee) VALUES
('Pizza Palace', '🍕', 'Italian', 4.5, '25-35 min', 49.00),
('Burger House', '🍔', 'American', 4.7, '20-30 min', 29.00),
('Sushi Master', '🍣', 'Japanese', 4.8, '30-40 min', 59.00),
('Taco Fiesta', '🌮', 'Mexican', 4.6, '15-25 min', 39.00);

-- Insert sample menu items
INSERT INTO menu_items (restaurant_id, name, description, price, image, category, is_veg) VALUES
-- Pizza Palace
(1, 'Margherita Pizza', 'Classic tomato sauce, mozzarella, and basil', 299.00, '🍕', 'Pizza', TRUE),
(1, 'Pepperoni Pizza', 'Loaded with pepperoni and cheese', 349.00, '🍕', 'Pizza', FALSE),
(1, 'Caesar Salad', 'Fresh romaine lettuce with caesar dressing', 199.00, '🥗', 'Salads', TRUE),
-- Burger House
(2, 'Classic Burger', 'Beef patty with lettuce, tomato, and cheese', 249.00, '🍔', 'Burgers', FALSE),
(2, 'Veggie Burger', 'Plant-based patty with fresh vegetables', 229.00, '🍔', 'Burgers', TRUE),
(2, 'French Fries', 'Crispy golden fries', 99.00, '🍟', 'Sides', TRUE),
-- Sushi Master
(3, 'California Roll', 'Crab, avocado, and cucumber', 399.00, '🍣', 'Rolls', FALSE),
(3, 'Salmon Nigiri', 'Fresh salmon over rice', 449.00, '🍣', 'Nigiri', FALSE),
(3, 'Miso Soup', 'Traditional Japanese soup', 149.00, '🍜', 'Soups', TRUE),
-- Taco Fiesta
(4, 'Beef Tacos', 'Three tacos with seasoned beef', 279.00, '🌮', 'Tacos', FALSE),
(4, 'Chicken Burrito', 'Large burrito with grilled chicken', 329.00, '🌯', 'Burritos', FALSE),
(4, 'Guacamole & Chips', 'Fresh guacamole with tortilla chips', 179.00, '🥑', 'Appetizers', TRUE);