-- grid12 - Demo database setup.
--
-- Consolidated SQL for every example form's own tables (each example form
-- adds its own section below as new demo tables are introduced). Run the
-- whole file once against the database configured in grid12/config.php.
--
-- Currently covers: the Master-Detail example (orders / order_items).
-- The other example forms (payments, suppliers, products, notes) run
-- against tables that already exist on the live demo database and are not
-- (re)created here.

-- ============================================================
-- Master-Detail example: Orders / Order Items
-- Used by examples/orders_master_detail.php.
-- ============================================================

CREATE TABLE IF NOT EXISTS orders (
  aid           INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(120) NOT NULL,
  order_date    DATE NOT NULL,
  status        VARCHAR(20) NOT NULL DEFAULT 'pending'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_items (
  aid          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  order_id     INT UNSIGNED NOT NULL,
  product_name VARCHAR(120) NOT NULL,
  quantity     INT NOT NULL DEFAULT 1,
  unit_price   DECIMAL(10,2) NOT NULL DEFAULT 0,
  KEY idx_order_items_order_id (order_id),
  CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(aid)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Optional sample data: 10 orders, each with 4-20 order_items (120 total).
INSERT INTO orders (customer_name, order_date, status) VALUES
  ('Acme Retail Ltd', '2026-01-05', 'pending'),
  ('Blue Harbor Foods', '2026-01-11', 'confirmed'),
  ('Northwind Traders', '2026-01-17', 'shipped'),
  ('Summit Hardware', '2026-01-23', 'completed'),
  ('Green Valley Organics', '2026-01-29', 'cancelled'),
  ('Cedar Point Supplies', '2026-02-04', 'pending'),
  ('Atlas Logistics', '2026-02-10', 'confirmed'),
  ('Harbor View Cafe', '2026-02-16', 'shipped'),
  ('Silverline Electronics', '2026-02-22', 'completed'),
  ('Maple Street Bakery', '2026-02-28', 'cancelled');

INSERT INTO order_items (order_id, product_name, quantity, unit_price) VALUES
  (1, 'LED Bulb 9W', 2, 4.75),
  (1, 'Packing Tape', 3, 5.5),
  (1, 'Cardboard Box M', 4, 6.25),
  (1, 'Cardboard Box L', 5, 7),
  (1, 'Olive Oil 1L', 6, 7.75),
  (1, 'Coffee Beans 1kg', 7, 8.5),
  (1, 'Flour 5kg', 8, 9.25),
  (2, 'Coffee Beans 1kg', 3, 6.5),
  (2, 'Flour 5kg', 4, 7.25),
  (2, 'Sugar 1kg', 5, 8),
  (2, 'Notebook A5', 6, 8.75),
  (2, 'Ballpoint Pen', 7, 9.5),
  (2, 'USB Cable 1m', 8, 10.25),
  (2, 'HDMI Cable 2m', 9, 11),
  (2, 'Wireless Mouse', 10, 11.75),
  (2, 'Keyboard Compact', 11, 12.5),
  (2, 'Office Chair', 12, 13.25),
  (3, 'USB Cable 1m', 4, 8.25),
  (3, 'HDMI Cable 2m', 5, 9),
  (3, 'Wireless Mouse', 6, 9.75),
  (3, 'Keyboard Compact', 7, 10.5),
  (3, 'Office Chair', 8, 11.25),
  (3, 'Widget A', 9, 12),
  (3, 'Widget B', 10, 12.75),
  (3, 'Widget C', 11, 13.5),
  (3, 'Steel Bracket', 12, 14.25),
  (3, 'Copper Pipe 1m', 1, 15),
  (3, 'LED Bulb 9W', 2, 15.75),
  (3, 'Packing Tape', 3, 16.5),
  (3, 'Cardboard Box M', 4, 17.25),
  (4, 'Widget A', 5, 10),
  (4, 'Widget B', 6, 10.75),
  (4, 'Widget C', 7, 11.5),
  (4, 'Steel Bracket', 8, 12.25),
  (4, 'Copper Pipe 1m', 9, 13),
  (4, 'LED Bulb 9W', 10, 13.75),
  (4, 'Packing Tape', 11, 14.5),
  (4, 'Cardboard Box M', 12, 15.25),
  (4, 'Cardboard Box L', 1, 16),
  (4, 'Olive Oil 1L', 2, 16.75),
  (4, 'Coffee Beans 1kg', 3, 17.5),
  (4, 'Flour 5kg', 4, 18.25),
  (4, 'Sugar 1kg', 5, 19),
  (4, 'Notebook A5', 6, 19.75),
  (4, 'Ballpoint Pen', 7, 20.5),
  (4, 'USB Cable 1m', 8, 21.25),
  (5, 'LED Bulb 9W', 6, 11.75),
  (5, 'Packing Tape', 7, 12.5),
  (5, 'Cardboard Box M', 8, 13.25),
  (5, 'Cardboard Box L', 9, 14),
  (5, 'Olive Oil 1L', 10, 14.75),
  (5, 'Coffee Beans 1kg', 11, 15.5),
  (5, 'Flour 5kg', 12, 16.25),
  (5, 'Sugar 1kg', 1, 17),
  (5, 'Notebook A5', 2, 17.75),
  (5, 'Ballpoint Pen', 3, 18.5),
  (5, 'USB Cable 1m', 4, 19.25),
  (5, 'HDMI Cable 2m', 5, 20),
  (5, 'Wireless Mouse', 6, 20.75),
  (5, 'Keyboard Compact', 7, 21.5),
  (5, 'Office Chair', 8, 22.25),
  (5, 'Widget A', 9, 23),
  (5, 'Widget B', 10, 23.75),
  (5, 'Widget C', 11, 24.5),
  (5, 'Steel Bracket', 12, 25.25),
  (6, 'Coffee Beans 1kg', 7, 13.5),
  (6, 'Flour 5kg', 8, 14.25),
  (6, 'Sugar 1kg', 9, 15),
  (6, 'Notebook A5', 10, 15.75),
  (6, 'Ballpoint Pen', 11, 16.5),
  (7, 'USB Cable 1m', 8, 15.25),
  (7, 'HDMI Cable 2m', 9, 16),
  (7, 'Wireless Mouse', 10, 16.75),
  (7, 'Keyboard Compact', 11, 17.5),
  (7, 'Office Chair', 12, 18.25),
  (7, 'Widget A', 1, 19),
  (7, 'Widget B', 2, 19.75),
  (7, 'Widget C', 3, 20.5),
  (8, 'Widget A', 9, 17),
  (8, 'Widget B', 10, 17.75),
  (8, 'Widget C', 11, 18.5),
  (8, 'Steel Bracket', 12, 19.25),
  (8, 'Copper Pipe 1m', 1, 20),
  (8, 'LED Bulb 9W', 2, 20.75),
  (8, 'Packing Tape', 3, 21.5),
  (8, 'Cardboard Box M', 4, 22.25),
  (8, 'Cardboard Box L', 5, 23),
  (8, 'Olive Oil 1L', 6, 23.75),
  (8, 'Coffee Beans 1kg', 7, 24.5),
  (9, 'LED Bulb 9W', 10, 18.75),
  (9, 'Packing Tape', 11, 19.5),
  (9, 'Cardboard Box M', 12, 20.25),
  (9, 'Cardboard Box L', 1, 21),
  (9, 'Olive Oil 1L', 2, 21.75),
  (9, 'Coffee Beans 1kg', 3, 22.5),
  (9, 'Flour 5kg', 4, 23.25),
  (9, 'Sugar 1kg', 5, 24),
  (9, 'Notebook A5', 6, 24.75),
  (9, 'Ballpoint Pen', 7, 25.5),
  (9, 'USB Cable 1m', 8, 26.25),
  (9, 'HDMI Cable 2m', 9, 27),
  (9, 'Wireless Mouse', 10, 27.75),
  (9, 'Keyboard Compact', 11, 28.5),
  (10, 'Coffee Beans 1kg', 11, 20.5),
  (10, 'Flour 5kg', 12, 21.25),
  (10, 'Sugar 1kg', 1, 22),
  (10, 'Notebook A5', 2, 22.75),
  (10, 'Ballpoint Pen', 3, 23.5),
  (10, 'USB Cable 1m', 4, 24.25),
  (10, 'HDMI Cable 2m', 5, 25),
  (10, 'Wireless Mouse', 6, 25.75),
  (10, 'Keyboard Compact', 7, 26.5),
  (10, 'Office Chair', 8, 27.25),
  (10, 'Widget A', 9, 28),
  (10, 'Widget B', 10, 28.75),
  (10, 'Widget C', 11, 29.5),
  (10, 'Steel Bracket', 12, 30.25),
  (10, 'Copper Pipe 1m', 1, 31),
  (10, 'LED Bulb 9W', 2, 31.75),
  (10, 'Packing Tape', 3, 32.5);
