-- ============================================================
-- 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);


-- ────────────────────────────────────────────────────────────
-- 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;


-- ────────────────────────────────────────────────────────────
-- 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
-- ============================================================
