-- ============================================================ -- FOOD DELIVERY SYSTEM — Database Schema -- Engine: MySQL 8+ Charset: utf8mb4 -- ============================================================ CREATE DATABASE IF NOT EXISTS food_delivery CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE food_delivery; -- ============================================================ -- 1. ROLES & PERMISSIONS (RBAC) -- ============================================================ CREATE TABLE roles ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, -- super_admin, manager, kitchen, rider, customer label VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE permissions ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE, -- e.g. menu.create, order.view label VARCHAR(150) NOT NULL, module VARCHAR(50) NOT NULL, -- menu, order, report, user, voucher created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE role_permissions ( role_id TINYINT UNSIGNED NOT NULL, permission_id SMALLINT UNSIGNED NOT NULL, PRIMARY KEY (role_id, permission_id), FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE, FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ============================================================ -- 2. USERS -- ============================================================ CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, role_id TINYINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, email VARCHAR(150) NOT NULL UNIQUE, phone VARCHAR(20) NOT NULL, password_hash VARCHAR(255) NOT NULL, profile_image VARCHAR(255) DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, email_verified TINYINT(1) NOT NULL DEFAULT 0, login_attempts TINYINT NOT NULL DEFAULT 0, locked_until DATETIME DEFAULT NULL, last_login DATETIME DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (role_id) REFERENCES roles(id) ) ENGINE=InnoDB; -- Password reset tokens CREATE TABLE password_resets ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, token VARCHAR(100) NOT NULL UNIQUE, expires_at DATETIME NOT NULL, used TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- API / Flutter JWT tokens CREATE TABLE api_tokens ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, token VARCHAR(512) NOT NULL UNIQUE, device VARCHAR(100) DEFAULT NULL, expires_at DATETIME NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ============================================================ -- 3. CUSTOMERS (extended profile for customer role) -- ============================================================ CREATE TABLE customers ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL UNIQUE, fcm_token VARCHAR(255) DEFAULT NULL, -- Firebase push token 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 ) ENGINE=InnoDB; CREATE TABLE customer_addresses ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id INT UNSIGNED NOT NULL, label VARCHAR(50) NOT NULL DEFAULT 'Home', -- Home, Office, Other address_line VARCHAR(255) NOT NULL, city VARCHAR(100) NOT NULL, latitude DECIMAL(10,8) DEFAULT NULL, longitude DECIMAL(11,8) DEFAULT NULL, is_default TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ============================================================ -- 4. MENU — CATEGORIES -- ============================================================ CREATE TABLE categories ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, slug VARCHAR(120) NOT NULL UNIQUE, icon_image VARCHAR(255) DEFAULT NULL, -- path to uploaded icon sort_order SMALLINT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT UNSIGNED DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- ============================================================ -- 5. MENU — ITEMS -- ============================================================ CREATE TABLE menu_items ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_id INT UNSIGNED NOT NULL, name VARCHAR(150) NOT NULL, slug VARCHAR(170) NOT NULL UNIQUE, description TEXT DEFAULT NULL, base_price DECIMAL(10,2) NOT NULL DEFAULT 0.00, item_image VARCHAR(255) DEFAULT NULL, is_available TINYINT(1) NOT NULL DEFAULT 1, sort_order SMALLINT NOT NULL DEFAULT 0, created_by INT UNSIGNED DEFAULT NULL, 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 RESTRICT, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- Choices / variants per item (e.g. Size: Small/Medium/Large) CREATE TABLE item_choice_groups ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, menu_item_id INT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, -- "Size", "Crust", "Extras" is_required TINYINT(1) NOT NULL DEFAULT 1, -- must pick one? min_select TINYINT NOT NULL DEFAULT 1, max_select TINYINT NOT NULL DEFAULT 1, sort_order SMALLINT NOT NULL DEFAULT 0, FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE item_choices ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, choice_group_id INT UNSIGNED NOT NULL, label VARCHAR(100) NOT NULL, -- "Small", "Medium", "Large" price_modifier DECIMAL(10,2) NOT NULL DEFAULT 0.00, -- added to base_price is_default TINYINT(1) NOT NULL DEFAULT 0, sort_order SMALLINT NOT NULL DEFAULT 0, FOREIGN KEY (choice_group_id) REFERENCES item_choice_groups(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ============================================================ -- 6. PROMOTIONS — DISCOUNTS, DEALS, VOUCHERS -- ============================================================ -- Item/category level discount offers CREATE TABLE discount_offers ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(150) NOT NULL, discount_type ENUM('percentage','fixed') NOT NULL DEFAULT 'percentage', discount_value DECIMAL(10,2) NOT NULL, applies_to ENUM('item','category','order') NOT NULL DEFAULT 'item', target_id INT UNSIGNED DEFAULT NULL, -- item_id or category_id (NULL = whole order) min_order_amt DECIMAL(10,2) NOT NULL DEFAULT 0.00, max_discount DECIMAL(10,2) DEFAULT NULL, -- cap for percentage type (NULL = no cap) starts_at DATETIME NOT NULL, expires_at DATETIME NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT UNSIGNED DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- Combo deals CREATE TABLE deals ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(150) NOT NULL, description TEXT DEFAULT NULL, deal_price DECIMAL(10,2) NOT NULL, deal_image VARCHAR(255) DEFAULT NULL, starts_at DATETIME NOT NULL, expires_at DATETIME NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT UNSIGNED DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE deal_items ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, deal_id INT UNSIGNED NOT NULL, menu_item_id INT UNSIGNED NOT NULL, quantity TINYINT NOT NULL DEFAULT 1, FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE, FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- Voucher / coupon codes CREATE TABLE vouchers ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(30) NOT NULL UNIQUE, description VARCHAR(255) DEFAULT NULL, discount_type ENUM('percentage','fixed') NOT NULL DEFAULT 'percentage', discount_value DECIMAL(10,2) NOT NULL, min_order_amt DECIMAL(10,2) NOT NULL DEFAULT 0.00, max_discount DECIMAL(10,2) DEFAULT NULL, -- cap for percentage type usage_limit INT UNSIGNED DEFAULT NULL, -- NULL = unlimited used_count INT UNSIGNED NOT NULL DEFAULT 0, per_user_limit TINYINT NOT NULL DEFAULT 1, starts_at DATETIME NOT NULL, expires_at DATETIME NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT UNSIGNED DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE voucher_usages ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, voucher_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, order_id INT UNSIGNED DEFAULT NULL, -- linked after order placed used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (voucher_id) REFERENCES vouchers(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ============================================================ -- 7. ORDERS -- ============================================================ CREATE TABLE orders ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_number VARCHAR(20) NOT NULL UNIQUE, -- e.g. ORD-20240619-0001 customer_id INT UNSIGNED NOT NULL, address_id INT UNSIGNED DEFAULT NULL, delivery_address VARCHAR(300) DEFAULT NULL, -- snapshot at order time rider_id INT UNSIGNED DEFAULT NULL, status ENUM( 'pending', 'confirmed', 'cooking', 'ready', 'picked_up', 'on_the_way', 'delivered', 'cancelled' ) NOT NULL DEFAULT 'pending', payment_method ENUM('cash','online','card') NOT NULL DEFAULT 'cash', payment_status ENUM('unpaid','paid','refunded') NOT NULL DEFAULT 'unpaid', subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00, discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, voucher_id INT UNSIGNED DEFAULT NULL, voucher_discount DECIMAL(10,2) NOT NULL DEFAULT 0.00, delivery_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00, tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, special_notes TEXT DEFAULT NULL, cancelled_reason VARCHAR(255) DEFAULT NULL, placed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT, FOREIGN KEY (address_id) REFERENCES customer_addresses(id) ON DELETE SET NULL, FOREIGN KEY (rider_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (voucher_id) REFERENCES vouchers(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE order_items ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id INT UNSIGNED NOT NULL, menu_item_id INT UNSIGNED DEFAULT NULL, -- NULL if item deleted later deal_id INT UNSIGNED DEFAULT NULL, -- set if item came from a deal item_name VARCHAR(150) NOT NULL, -- snapshot item_image VARCHAR(255) DEFAULT NULL, -- snapshot choices_summary VARCHAR(255) DEFAULT NULL, -- e.g. "Size: Large, Crust: Thin" unit_price DECIMAL(10,2) NOT NULL, quantity SMALLINT NOT NULL DEFAULT 1, item_discount DECIMAL(10,2) NOT NULL DEFAULT 0.00, line_total 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 SET NULL, FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- Choices selected per order item (detailed) CREATE TABLE order_item_choices ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_item_id INT UNSIGNED NOT NULL, group_name VARCHAR(100) NOT NULL, -- "Size" choice_label VARCHAR(100) NOT NULL, -- "Large" price_modifier DECIMAL(10,2) NOT NULL DEFAULT 0.00, FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- Full status history log CREATE TABLE order_status_log ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id INT UNSIGNED NOT NULL, status VARCHAR(30) NOT NULL, note VARCHAR(255) DEFAULT NULL, changed_by INT UNSIGNED DEFAULT NULL, changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- ============================================================ -- 8. INVOICES -- ============================================================ CREATE TABLE invoices ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id INT UNSIGNED NOT NULL UNIQUE, invoice_number VARCHAR(20) NOT NULL UNIQUE, -- INV-20240619-0001 issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, pdf_path VARCHAR(255) DEFAULT NULL, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ============================================================ -- 9. NOTIFICATIONS -- ============================================================ CREATE TABLE notifications ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, type VARCHAR(50) NOT NULL, -- new_order, status_update, promo title VARCHAR(150) NOT NULL, body TEXT DEFAULT NULL, data JSON DEFAULT NULL, -- extra payload is_read TINYINT(1) NOT NULL DEFAULT 0, sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ============================================================ -- 10. SETTINGS -- ============================================================ CREATE TABLE settings ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, key_name VARCHAR(100) NOT NULL UNIQUE, value TEXT DEFAULT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- ============================================================ -- SEED DATA -- ============================================================ -- Roles INSERT INTO roles (name, label) VALUES ('super_admin', 'Super Admin'), ('manager', 'Manager'), ('kitchen', 'Kitchen Staff'), ('rider', 'Rider'), ('customer', 'Customer'); -- Core permissions INSERT INTO permissions (name, label, module) VALUES -- User management ('user.view', 'View Users', 'user'), ('user.create', 'Create User', 'user'), ('user.edit', 'Edit User', 'user'), ('user.delete', 'Delete User', 'user'), -- Menu ('menu.view', 'View Menu', 'menu'), ('menu.create', 'Create Menu Item', 'menu'), ('menu.edit', 'Edit Menu Item', 'menu'), ('menu.delete', 'Delete Menu Item', 'menu'), -- Category ('category.view', 'View Categories', 'menu'), ('category.create', 'Create Category', 'menu'), ('category.edit', 'Edit Category', 'menu'), ('category.delete', 'Delete Category', 'menu'), -- Orders ('order.view', 'View Orders', 'order'), ('order.manage', 'Manage Order Status', 'order'), ('order.cancel', 'Cancel Order', 'order'), -- Vouchers / Deals ('voucher.view', 'View Vouchers', 'voucher'), ('voucher.create', 'Create Voucher', 'voucher'), ('voucher.edit', 'Edit Voucher', 'voucher'), ('voucher.delete', 'Delete Voucher', 'voucher'), ('deal.view', 'View Deals', 'deal'), ('deal.create', 'Create Deal', 'deal'), ('deal.edit', 'Edit Deal', 'deal'), ('deal.delete', 'Delete Deal', 'deal'), -- Reports ('report.view', 'View Reports', 'report'), ('report.export', 'Export Reports', 'report'), -- Settings ('settings.manage', 'Manage Settings', 'settings'); -- Assign ALL permissions to super_admin (role id=1) INSERT INTO role_permissions (role_id, permission_id) SELECT 1, id FROM permissions; -- Manager: everything except user.delete and settings.manage INSERT INTO role_permissions (role_id, permission_id) SELECT 2, id FROM permissions WHERE name NOT IN ('user.delete','settings.manage'); -- Kitchen: view orders + manage status only INSERT INTO role_permissions (role_id, permission_id) SELECT 3, id FROM permissions WHERE name IN ('order.view','order.manage','menu.view','category.view'); -- Rider: view and update own orders INSERT INTO role_permissions (role_id, permission_id) SELECT 4, id FROM permissions WHERE name IN ('order.view','order.manage'); -- Default settings INSERT INTO settings (key_name, value) VALUES ('restaurant_name', 'My Restaurant'), ('restaurant_phone', ''), ('restaurant_address', ''), ('restaurant_logo', ''), ('currency_symbol', 'Rs.'), ('tax_rate', '0'), ('delivery_fee', '0'), ('order_alert_sound', '1'), ('min_order_amount', '0'), ('max_image_size_kb', '2048'), ('app_maintenance_mode', '0'); -- Default super admin (password: Admin@1234) INSERT INTO users (role_id, name, email, phone, password_hash, is_active, email_verified) VALUES (1, 'Super Admin', 'admin@restaurant.com', '0300-0000000', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1, 1);