-- =============================================================
-- Step 7: Order System — Additional columns & test data
-- Run in phpMyAdmin on database: newchin1_food
-- =============================================================

-- Add missing columns to orders table (safe — ignore if already exists)
ALTER TABLE `orders`
  ADD COLUMN IF NOT EXISTS `order_type`    ENUM('delivery','pickup','dine_in') NOT NULL DEFAULT 'delivery' AFTER `order_number`,
  ADD COLUMN IF NOT EXISTS `cancelled_reason` TEXT NULL;

-- Add invoices table
CREATE TABLE IF NOT EXISTS `invoices` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `order_id`       INT UNSIGNED NOT NULL UNIQUE,
  `invoice_number` VARCHAR(50)  NOT NULL UNIQUE,
  `created_at`     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_inv_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================
-- Test customer user
-- =============================================================
INSERT IGNORE INTO `users` (`name`, `email`, `password_hash`, `phone`, `role_id`, `is_active`)
VALUES (
  'Test Customer',
  'customer@test.com',
  '$2y$12$hV.1EHLizMJPbJD3jBCkT.hfHNANvCm4DjHCFGzWDrv1bMBHEBXry',
  '03001234567',
  (SELECT id FROM roles WHERE name = 'customer' LIMIT 1),
  1
);

-- Link to customers table
INSERT IGNORE INTO `customers` (`user_id`)
SELECT id FROM users WHERE email = 'customer@test.com';

-- =============================================================
-- Sample order for UI testing
-- =============================================================
INSERT IGNORE INTO `orders`
  (`order_number`, `customer_id`, `order_type`, `status`, `payment_method`,
   `delivery_address`, `subtotal`, `discount_amount`, `delivery_fee`, `tax_amount`, `total_amount`, `placed_at`)
SELECT
  'ORD-20260619-0001',
  c.id,
  'delivery',
  'pending',
  'cash',
  '123 Main Street, Sahiwal',
  850.00, 0.00, 50.00, 0.00, 900.00,
  NOW()
FROM customers c
JOIN users u ON u.id = c.user_id
WHERE u.email = 'customer@test.com'
LIMIT 1;

-- Sample order item
INSERT IGNORE INTO `order_items` (`order_id`, `menu_item_id`, `item_name`, `unit_price`, `quantity`, `line_total`)
SELECT
  o.id,
  (SELECT id FROM menu_items LIMIT 1),
  COALESCE((SELECT name FROM menu_items LIMIT 1), 'Sample Item'),
  425.00,
  2,
  850.00
FROM orders o
WHERE o.order_number = 'ORD-20260619-0001'
LIMIT 1;

-- Initial status log entry
INSERT IGNORE INTO `order_status_log` (`order_id`, `status`, `note`)
SELECT id, 'pending', 'Order placed by customer'
FROM orders
WHERE order_number = 'ORD-20260619-0001';