-- ============================================================ -- Brasa Eats — Full Database Schema -- Run this script against your MySQL server to create the -- database, tables, and seed data. -- ============================================================ CREATE DATABASE IF NOT EXISTS `be` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE `be`; -- ──────────────────────────────────────────────────────────── -- 1. MENU CATEGORIES -- ──────────────────────────────────────────────────────────── CREATE TABLE menu_categories ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, slug VARCHAR(100) NOT NULL UNIQUE, description VARCHAR(255) DEFAULT NULL, image_url VARCHAR(255) DEFAULT NULL, sort_order TINYINT UNSIGNED DEFAULT 0, is_active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; INSERT INTO menu_categories (id, name, slug, description, image_url, sort_order) VALUES (1, 'Chicken', 'chicken', 'Served with 2 small sides and 2 sauces. Whole chicken comes with 2 large sides and 4 sauces.', 'images/menu1.avif', 1), (2, 'Family Meals', 'family-meals', 'Perfect for sharing with the whole family', 'images/menu2.avif', 2), (3, 'Subs', 'subs', 'Fresh baked bread with premium ingredients', 'images/menu3.avif', 3), (4, 'Soup', 'soup', 'Homemade soups', NULL, 4), (5, 'Salads', 'salads', 'Fresh and healthy options', 'images/menu4.avif', 5), (6, 'Ribs', 'ribs', 'Slow-cooked and packed with flavor', 'images/ribs.jpg', 6), (7, 'Pork', 'pork', 'Tender, juicy, and perfectly seasoned', 'images/pork.jpg', 7), (8, 'Peruvian Specialties', 'peruvian-specialties', 'Authentic Peruvian dishes', 'images/menu5.avif', 8), (9, 'Anticuchos', 'anticuchos', 'Traditional grilled skewers', NULL, 9), (10, 'Drinks', 'drinks', 'Refreshing beverages', NULL, 10), (11, 'Fish', 'fish', 'Freshly caught', NULL, 11), (12, 'Kids', 'kids', 'For our younger guests', NULL, 12), (13, 'Sides', 'sides', 'Complete your meal', NULL, 13), (14, 'Specials', 'specials', 'Limited-time offers', NULL, 14); -- ──────────────────────────────────────────────────────────── -- 2. 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(150) NOT NULL UNIQUE, description TEXT DEFAULT NULL, price DECIMAL(8,2) NOT NULL, image_url VARCHAR(255) DEFAULT NULL, includes_sides TINYINT UNSIGNED DEFAULT 0 COMMENT 'Number of sides included (0 = none)', side_size ENUM('small','large') DEFAULT NULL COMMENT 'Size of included sides', includes_sauces TINYINT UNSIGNED DEFAULT 0 COMMENT 'Number of sauces included', availability ENUM('daily','weekend_only') DEFAULT 'daily', is_combo TINYINT(1) DEFAULT 0, is_active TINYINT(1) DEFAULT 1, sort_order TINYINT UNSIGNED DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES menu_categories(id) ON DELETE CASCADE, INDEX idx_category (category_id), INDEX idx_active (is_active) ) ENGINE=InnoDB; -- Chicken INSERT INTO menu_items (category_id, name, slug, description, price, includes_sides, side_size, includes_sauces, sort_order) VALUES (1, 'Quarter Chicken - Dark Meat', 'quarter-chicken-dark', 'Quarter dark meat chicken with sides and sauces.', 11.79, 2, 'small', 2, 1), (1, 'Quarter Chicken - White Meat', 'quarter-chicken-white', 'Quarter white meat chicken with sides and sauces.', 11.79, 2, 'small', 2, 2), (1, 'Half Chicken', 'half-chicken', 'Half chicken with sides and sauces.', 15.49, 2, 'small', 2, 3), (1, 'Whole Chicken', 'whole-chicken', 'Whole chicken with large sides and sauces.', 26.99, 2, 'large', 4, 4), (1, 'Whole Chicken (No Sides)', 'whole-chicken-no-sides','Whole chicken with sauces but no side dishes.', 19.00, 0, NULL, 4, 5); -- Family Meals INSERT INTO menu_items (category_id, name, slug, description, price, includes_sides, side_size, includes_sauces, is_combo, sort_order) VALUES (2, 'Family Special #1', 'family-special-1', '1 whole chicken, 2 large sides and either a 2 liter soda or 4 cans of soda.', 30.99, 2, 'large', 4, 1, 1), (2, 'Family Special #2', 'family-special-2', '2 whole chickens, 4 large sides and either two 2 liter sodas or 8 cans of soda.', 50.99, 4, 'large', 8, 1, 2); -- Subs INSERT INTO menu_items (category_id, name, slug, description, price, sort_order) VALUES (3, 'Chicken Sub', 'chicken-sub', 'Charcoal Chicken, onions, lettuce, tomato, provolone cheese and mayo.', 11.99, 1), (3, 'Steak & Cheese Sub', 'steak-cheese-sub', 'Rib-Eye Steak, onions, lettuce, tomato, provolone cheese and mayo.', 12.99, 2), (3, 'Chicken/Steak Sub', 'chicken-steak-sub', 'Chicken, steak, onions, lettuce, tomato, provolone cheese and mayo.', 12.49, 3), (3, 'Veggie Sub', 'veggie-sub', 'Fresh baked bread with premium veggies.', 11.99, 4); -- Soup INSERT INTO menu_items (category_id, name, slug, description, price, availability, sort_order) VALUES (4, 'Sopa de Pollo', 'sopa-de-pollo', 'Homemade chicken soup.', 12.99, 'daily', 1), (4, 'Sopa de Res', 'sopa-de-res', 'Hearty beef soup. Available Saturday and Sunday only.', 14.99, 'weekend_only', 2); -- Salads INSERT INTO menu_items (category_id, name, slug, description, price, sort_order) VALUES (5, 'Chicken Caesar', 'chicken-caesar', 'Chicken Caesar salad.', 12.99, 1), (5, 'Rice Salad', 'rice-salad', 'Rice Salad.', 13.49, 2); -- Ribs INSERT INTO menu_items (category_id, name, slug, description, price, sort_order) VALUES (6, 'Ribs', 'ribs', 'Slow-cooked ribs packed with flavor.', 30.49, 1); -- Pork INSERT INTO menu_items (category_id, name, slug, description, price, sort_order) VALUES (7, 'Pork', 'pork', 'Tender, juicy, and perfectly seasoned pork.', 13.49, 1); -- Peruvian Specialties INSERT INTO menu_items (category_id, name, slug, description, price, includes_sides, side_size, availability, sort_order) VALUES (8, 'Pollo Saltado', 'pollo-saltado', 'Peruvian stirfry combining marinated chicken, red onions, spring onions, tomatoes and cilantro served over a bed of fries and rice.', 14.99, 0, NULL, 'daily', 1), (8, 'Bistek a lo Pobre', 'bistek-a-lo-pobre', 'Marinated steak, topped with an over easy egg, served with fries, plantains and rice.', 20.99, 0, NULL, 'daily', 2), (8, 'Salchipapas', 'salchipapas', 'Sliced hot dogs mixed with french fries and served with ketchup, mayonnaise, mustard and house salad.', 5.00, 0, NULL, 'daily', 3), (8, 'Chicharron de Cerdo', 'chicharron-de-cerdo', 'Fried chunks of pork served with 2 side orders.', 8.50, 2, 'small', 'daily', 4), (8, 'Ceviche de Pescado', 'ceviche-de-pescado', 'Fresh Tilapia marinated in lime juice and mixed with special sauce served with sweet potatoes and Peruvian corn. Saturday and Sunday only.', 10.99, 0, NULL, 'weekend_only', 5), (8, 'Lomo Saltado', 'lomo-saltado', 'Peruvian stirfry combining marinated beef, red onions, spring onions, tomatoes and cilantro served over a bed of fries and rice.', 19.99, 0, NULL, 'daily', 6), (8, 'Carne Asada', 'carne-asada', 'Grilled steak marinated in Peruvian spices, served with your choice of two side orders.', 19.99, 2, 'small', 'daily', 7), (8, 'Pescado Frito', 'pescado-frito', 'Fried Tilapia (whole fish or fillet) served with 2 side orders.', 11.50, 2, 'small', 'daily', 8), (8, 'Tallarin Saltado', 'tallarin-saltado', 'Spaghetti, fresh beef sauteed in olive oil, red onions, tomatoes, spring onions, and cilantro.', 10.99, 0, NULL, 'daily', 9), (8, 'Pollo A La Plancha', 'pollo-a-la-plancha', 'Grilled chicken marinated in Peruvian spices, served with your choice of two side orders.', 10.50, 2, 'small', 'daily', 10), (8, 'Ceviche de Marisco', 'ceviche-de-marisco', 'Fresh seafood mix marinated in lime juice. Saturday and Sunday only.', 12.99, 0, NULL, 'weekend_only',11); -- Anticuchos INSERT INTO menu_items (category_id, name, slug, description, price, sort_order) VALUES (9, 'Anticuchos de Corazon', 'anticuchos-de-corazon', 'Skewers of beef heart marinated in spices and grilled to perfection.', 13.99, 1); -- Drinks INSERT INTO menu_items (category_id, name, slug, description, price, sort_order) VALUES (10, '12 oz Can of Soda', 'can-soda-12oz', '12 oz can of soda.', 1.00, 1), (10, '20 oz Bottle of Soda', 'bottle-soda-20oz', '20 oz bottle of soda.', 1.99, 2), (10, 'Bottle Water', 'bottle-water', 'Bottled water.', 1.89, 3), (10, 'Fountain Drink', 'fountain-drink', 'Fountain drink.', 1.89, 4), (10, '2 Liter Soda', '2-liter-soda', '2 liter bottle of soda.', 3.49, 5); -- Fish INSERT INTO menu_items (category_id, name, slug, description, price, sort_order) VALUES (11, 'Tilapia', 'tilapia', 'Freshly caught tilapia.', 9.00, 1); -- Kids INSERT INTO menu_items (category_id, name, slug, description, price, includes_sides, side_size, sort_order) VALUES (12, 'Kids Rotisserie Chicken', 'kids-rotisserie-chicken', 'Quarter chicken with 1 small side.', 8.39, 1, 'small', 1); -- Specials INSERT INTO menu_items (category_id, name, slug, description, price, sort_order) VALUES (14, 'Meal of the Month', 'meal-of-the-month', 'Limited-time culinary masterpiece. Ask what is currently featured!', 19.00, 1); -- ──────────────────────────────────────────────────────────── -- 3. SIDES -- ──────────────────────────────────────────────────────────── CREATE TABLE sides ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, slug VARCHAR(100) NOT NULL UNIQUE, side_type ENUM('hot','cold') NOT NULL, price_small DECIMAL(8,2) NOT NULL DEFAULT 2.25, price_large DECIMAL(8,2) NOT NULL DEFAULT 3.50, is_active TINYINT(1) DEFAULT 1, sort_order TINYINT UNSIGNED DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; INSERT INTO sides (name, slug, side_type, sort_order) VALUES ('French Fries', 'french-fries', 'hot', 1), ('Yuca', 'yuca', 'hot', 2), ('Plantains', 'plantains', 'hot', 3), ('White Rice', 'white-rice', 'hot', 4), ('Fried Rice', 'fried-rice', 'hot', 5), ('Pinto Beans', 'pinto-beans', 'hot', 6), ('Black Beans', 'black-beans', 'hot', 7), ('Chick Peas', 'chick-peas', 'hot', 8), ('Steamed Veggies', 'steamed-veggies', 'hot', 9), ('Corn', 'corn', 'hot', 10), ('House Salad', 'house-salad', 'cold', 11), ('Onion Salad', 'onion-salad', 'cold', 12), ('Pasta Salad', 'pasta-salad', 'cold', 13), ('Cole Slaw', 'cole-slaw', 'cold', 14); -- ──────────────────────────────────────────────────────────── -- 4. SAUCES -- ──────────────────────────────────────────────────────────── CREATE TABLE sauces ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, slug VARCHAR(100) NOT NULL UNIQUE, is_active TINYINT(1) DEFAULT 1, sort_order TINYINT UNSIGNED DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; INSERT INTO sauces (name, slug, sort_order) VALUES ('Green Sauce', 'green-sauce', 1), ('Hot Sauce', 'hot-sauce', 2), ('Garlic Sauce', 'garlic-sauce', 3), ('BBQ Sauce', 'bbq-sauce', 4), ('Ketchup', 'ketchup', 5), ('Mayonnaise', 'mayonnaise', 6), ('Mustard', 'mustard', 7); -- ──────────────────────────────────────────────────────────── -- 5. CUSTOMERS / USER PROFILES -- ──────────────────────────────────────────────────────────── CREATE TABLE customers ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, email VARCHAR(180) NOT NULL UNIQUE, phone VARCHAR(30) DEFAULT NULL, password_hash VARCHAR(255) DEFAULT NULL COMMENT 'NULL if guest checkout', address_line1 VARCHAR(200) DEFAULT NULL, address_line2 VARCHAR(200) DEFAULT NULL, city VARCHAR(100) DEFAULT NULL, state VARCHAR(50) DEFAULT NULL, zip_code VARCHAR(20) DEFAULT NULL, is_scenekey_member TINYINT(1) DEFAULT 0, scenekey_member_id VARCHAR(100) DEFAULT NULL, loyalty_points INT UNSIGNED DEFAULT 0, pay_later_eligible TINYINT(1) DEFAULT 0 COMMENT '1 = approved for pay-over-time', pay_later_limit DECIMAL(8,2) DEFAULT 0.00 COMMENT 'Max pay-later balance allowed', is_active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_email (email), INDEX idx_scenekey (is_scenekey_member) ) ENGINE=InnoDB; -- ──────────────────────────────────────────────────────────── -- 6. COUPONS / PROMOTIONS -- ──────────────────────────────────────────────────────────── CREATE TABLE coupons ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(255) DEFAULT NULL, discount_type ENUM('fixed','percent') NOT NULL DEFAULT 'fixed', discount_value DECIMAL(8,2) NOT NULL, min_order_total DECIMAL(8,2) DEFAULT 0.00, max_uses INT UNSIGNED DEFAULT NULL COMMENT 'NULL = unlimited', times_used INT UNSIGNED DEFAULT 0, scenekey_only TINYINT(1) DEFAULT 0, valid_from DATETIME DEFAULT NULL, valid_until DATETIME DEFAULT NULL, is_active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; INSERT INTO coupons (code, description, discount_type, discount_value, min_order_total) VALUES ('WELCOME2', '$2 OFF your order of $30+', 'fixed', 2.00, 30.00), ('SKDEAL10', '10% OFF for SceneKey members', 'percent', 10.00, 0.00); UPDATE coupons SET scenekey_only = 1 WHERE code = 'SKDEAL10'; -- ──────────────────────────────────────────────────────────── -- 7. DAILY SPECIALS -- ──────────────────────────────────────────────────────────── CREATE TABLE daily_specials ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, menu_item_id INT UNSIGNED NOT NULL, special_price DECIMAL(8,2) DEFAULT NULL COMMENT 'NULL = use regular price', discount_pct TINYINT UNSIGNED DEFAULT NULL COMMENT 'e.g. 15 = 15% off', label VARCHAR(100) DEFAULT NULL COMMENT 'e.g. "Today Only!", "Weekend Feature"', day_of_week ENUM('mon','tue','wed','thu','fri','sat','sun','all') DEFAULT 'all', valid_from DATE DEFAULT NULL, valid_until DATE DEFAULT NULL, is_active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ──────────────────────────────────────────────────────────── -- 8. ORDERS -- ──────────────────────────────────────────────────────────── CREATE TABLE orders ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_number VARCHAR(20) NOT NULL UNIQUE COMMENT 'Human-readable e.g. BE-20260215-0042', customer_id INT UNSIGNED DEFAULT NULL COMMENT 'NULL = guest', customer_name VARCHAR(160) NOT NULL, customer_email VARCHAR(180) DEFAULT NULL, customer_phone VARCHAR(30) NOT NULL, -- Fulfillment order_type ENUM('pickup','delivery') NOT NULL DEFAULT 'pickup', delivery_address VARCHAR(500) DEFAULT NULL, estimated_ready DATETIME DEFAULT NULL, -- Totals subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00, tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, delivery_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00, total DECIMAL(10,2) NOT NULL DEFAULT 0.00, coupon_id INT UNSIGNED DEFAULT NULL, coupon_code VARCHAR(50) DEFAULT NULL, -- Status status ENUM('pending','confirmed','preparing','ready','out_for_delivery','completed','cancelled') NOT NULL DEFAULT 'pending', payment_status ENUM('unpaid','paid','pay_later','refunded','failed') NOT NULL DEFAULT 'unpaid', notes TEXT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL, FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE SET NULL, INDEX idx_status (status), INDEX idx_customer (customer_id), INDEX idx_order_number (order_number) ) ENGINE=InnoDB; -- ──────────────────────────────────────────────────────────── -- 9. ORDER ITEMS (line items in an order) -- ──────────────────────────────────────────────────────────── CREATE TABLE order_items ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id INT UNSIGNED NOT NULL, menu_item_id INT UNSIGNED NOT NULL, item_name VARCHAR(150) NOT NULL COMMENT 'Snapshot at time of order', quantity TINYINT UNSIGNED NOT NULL DEFAULT 1, unit_price DECIMAL(8,2) NOT NULL COMMENT 'Price at time of order', line_total DECIMAL(10,2) NOT NULL COMMENT 'quantity * unit_price', special_requests TEXT DEFAULT NULL, 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 RESTRICT, INDEX idx_order (order_id) ) ENGINE=InnoDB; -- ──────────────────────────────────────────────────────────── -- 10. ORDER ITEM SIDES (sides chosen per line item) -- ──────────────────────────────────────────────────────────── CREATE TABLE order_item_sides ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_item_id INT UNSIGNED NOT NULL, side_id INT UNSIGNED NOT NULL, size ENUM('small','large') NOT NULL DEFAULT 'small', price DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT '0 if included with meal', FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE, FOREIGN KEY (side_id) REFERENCES sides(id) ON DELETE RESTRICT ) ENGINE=InnoDB; -- ──────────────────────────────────────────────────────────── -- 11. ORDER ITEM SAUCES (sauces chosen per line item) -- ──────────────────────────────────────────────────────────── CREATE TABLE order_item_sauces ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_item_id INT UNSIGNED NOT NULL, sauce_id INT UNSIGNED NOT NULL, FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE, FOREIGN KEY (sauce_id) REFERENCES sauces(id) ON DELETE RESTRICT ) ENGINE=InnoDB; -- ──────────────────────────────────────────────────────────── -- 12. PAYMENTS -- ──────────────────────────────────────────────────────────── CREATE TABLE payments ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED DEFAULT NULL, payment_method ENUM('credit_card','debit_card','apple_pay','google_pay','cash','pay_later') NOT NULL, gateway VARCHAR(50) DEFAULT NULL COMMENT 'e.g. stripe, square', gateway_txn_id VARCHAR(255) DEFAULT NULL COMMENT 'External transaction reference', amount DECIMAL(10,2) NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'USD', status ENUM('pending','completed','failed','refunded') NOT NULL DEFAULT 'pending', -- Pay-later fields is_pay_later TINYINT(1) DEFAULT 0, pay_later_due_date DATE DEFAULT NULL, pay_later_paid TINYINT(1) DEFAULT 0, paid_at DATETIME DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL, INDEX idx_order (order_id), INDEX idx_gateway_txn (gateway_txn_id) ) ENGINE=InnoDB; -- ──────────────────────────────────────────────────────────── -- 13. PAY-LATER INSTALLMENTS (for SceneKey loyalty) -- ──────────────────────────────────────────────────────────── CREATE TABLE pay_later_installments ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, payment_id INT UNSIGNED NOT NULL, installment_num TINYINT UNSIGNED NOT NULL, amount DECIMAL(10,2) NOT NULL, due_date DATE NOT NULL, status ENUM('pending','paid','overdue') NOT NULL DEFAULT 'pending', paid_at DATETIME DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ──────────────────────────────────────────────────────────── -- 14. ORDER STATUS LOG (audit trail) -- ──────────────────────────────────────────────────────────── CREATE TABLE order_status_log ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id INT UNSIGNED NOT NULL, old_status VARCHAR(30) DEFAULT NULL, new_status VARCHAR(30) NOT NULL, changed_by VARCHAR(100) DEFAULT 'system', notes VARCHAR(255) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, INDEX idx_order (order_id) ) ENGINE=InnoDB; -- ──────────────────────────────────────────────────────────── -- 15. SAVED CARTS (for profile / session persistence) -- ──────────────────────────────────────────────────────────── CREATE TABLE saved_carts ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id INT UNSIGNED DEFAULT NULL, session_id VARCHAR(128) DEFAULT NULL COMMENT 'For guest users', cart_data JSON NOT NULL COMMENT 'Serialized cart items', expires_at DATETIME NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE, INDEX idx_session (session_id), INDEX idx_customer (customer_id) ) ENGINE=InnoDB; -- ──────────────────────────────────────────────────────────── -- 16. RESTAURANT SETTINGS (hours, tax rate, etc.) -- ──────────────────────────────────────────────────────────── CREATE TABLE restaurant_settings ( setting_key VARCHAR(100) PRIMARY KEY, setting_value TEXT NOT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; INSERT INTO restaurant_settings (setting_key, setting_value) VALUES ('restaurant_name', 'Brasa Eats'), ('address', '7757 Dodge Plaza, Hyattsville, MD 20785'), ('phone', '(301) 555-0123'), ('tax_rate', '6.00'), ('delivery_fee', '3.99'), ('delivery_radius_miles', '5'), ('min_order_delivery', '15.00'), ('hours_mon', 'Closed'), ('hours_tue', '11:00-21:00'), ('hours_wed', '11:00-22:00'), ('hours_thu', '11:00-22:00'), ('hours_fri', '11:00-23:00'), ('hours_sat', '11:00-23:00'), ('hours_sun', '10:00-21:00'), ('order_prefix', 'BE'), ('pay_later_max_amount', '50.00'), ('pay_later_installments', '2'); -- ============================================================ -- DONE. Summary of tables created: -- ============================================================ -- menu_categories 14 categories -- menu_items 39 items -- sides 14 sides -- sauces 7 sauces -- customers User profiles + SceneKey -- coupons Discount codes -- daily_specials Rotating specials -- orders Order header -- order_items Line items -- order_item_sides Sides per item -- order_item_sauces Sauces per item -- payments Payment records -- pay_later_installments SceneKey pay-over-time -- order_status_log Audit trail -- saved_carts Cart persistence -- restaurant_settings Config key-value -- ============================================================