-- Migration: QR menus can now carry a hand-picked list of products/dishes.
-- When a QR menu has rows here, the public /api/qr/public/:code page shows
-- ONLY these items (grouped by their category); with no rows it falls back to
-- the outlet's full online-available menu, exactly as before.
--
-- Run once against an existing database (new installs get this from schema.sql):
--   mysql -u root -p petzy_pos < database/add_qr_menu_items.sql

USE petzy_pos;

CREATE TABLE IF NOT EXISTS qr_menu_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    qr_menu_id INT UNSIGNED NOT NULL,
    item_id INT UNSIGNED NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_qmi_menu_item (qr_menu_id, item_id),
    CONSTRAINT fk_qmi_menu FOREIGN KEY (qr_menu_id) REFERENCES qr_menus(id) ON DELETE CASCADE,
    CONSTRAINT fk_qmi_item FOREIGN KEY (item_id) REFERENCES menu_items(id) ON DELETE CASCADE
) ENGINE=InnoDB;