CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_number` varchar(20) NOT NULL,
  `customer_id` int(10) unsigned NOT NULL,
  `order_type` enum('ONLINE','INSTORE','wholesale','pos','ampere_credit') NOT NULL DEFAULT 'ONLINE',
  `status` enum('pending_payment','paid','processing','shipped','delivered','completed','cancelled','expired') NOT NULL DEFAULT 'pending_payment',
  `subtotal` bigint(20) unsigned NOT NULL,
  `discount_amount` bigint(20) unsigned NOT NULL DEFAULT 0,
  `shipping_fee` bigint(20) unsigned NOT NULL DEFAULT 0,
  `installation_fee` bigint(20) unsigned NOT NULL DEFAULT 0,
  `used_battery_credit` bigint(20) unsigned NOT NULL DEFAULT 0,
  `total_amount` bigint(20) unsigned NOT NULL,
  `shipping_address` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`shipping_address`)),
  `has_installation` tinyint(1) NOT NULL DEFAULT 0,
  `installation_date` date DEFAULT NULL,
  `installation_time_slot` enum('morning','noon','afternoon','evening','night') DEFAULT NULL,
  `customer_notes` text DEFAULT NULL,
  `admin_notes` text DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `shipped_at` datetime DEFAULT NULL,
  `delivered_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL COMMENT 'Auto-cancel if not paid',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_number` (`order_number`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created` (`created_at`),
  KEY `idx_expires` (`expires_at`),
  KEY `idx_customer_status_created` (`customer_id`,`status`,`created_at`),
  KEY `idx_order_type_status` (`order_type`,`status`),
  KEY `idx_pending_expires` (`status`,`expires_at`),
  CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  CONSTRAINT `chk_ord_total_positive` CHECK (`total_amount` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
