-- Migration: Add Orders Table
-- Run this on existing installations that were set up before v1.1
-- Safe to run multiple times (uses IF NOT EXISTS)

CREATE TABLE IF NOT EXISTS `orders` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `business_id` BIGINT UNSIGNED NOT NULL,
    `order_code` VARCHAR(20) NOT NULL UNIQUE,
    `customer_name` VARCHAR(150) NOT NULL,
    `customer_phone` VARCHAR(30) NOT NULL DEFAULT '',
    `fulfillment_type` ENUM('pickup', 'delivery') NOT NULL DEFAULT 'pickup',
    `delivery_address` TEXT NULL,
    `items_json` JSON NOT NULL,
    `subtotal` 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,
    `status` ENUM('sent_to_whatsapp', 'completed', 'cancelled') NOT NULL DEFAULT 'sent_to_whatsapp',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_orders_business_date` (`business_id`, `created_at`),
    INDEX `idx_orders_code` (`order_code`),
    CONSTRAINT `fk_orders_business` FOREIGN KEY (`business_id`) REFERENCES `businesses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;