CREATE TABLE `tb_orders` (
    `order_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `order_number` VARCHAR(50) NOT NULL UNIQUE,
    `customer_id` INT UNSIGNED DEFAULT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    `total_amount` DECIMAL(10,2) NOT NULL,
    `subtotal` DECIMAL(10,2) NOT NULL,
    `discount_amount` DECIMAL(10,2) DEFAULT 0.00,
    `tax_amount` DECIMAL(10,2) DEFAULT 0.00,
    `payment_status` ENUM('paid', 'unpaid', 'partial') NOT NULL DEFAULT 'unpaid',
    `payment_method` ENUM('cash', 'card', 'mobile', 'mixed') NOT NULL,
    `note` TEXT,
    `order_status` ENUM('completed', 'pending', 'cancelled') NOT NULL DEFAULT 'completed',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`customer_id`) REFERENCES `tb_customers`(`customer_id`) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `tb_users`(`user_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
