CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_ref` VARCHAR(50) NOT NULL UNIQUE,
  `user_id` INT DEFAULT 0,
  `subtotal` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `status_code` INT DEFAULT 2001,
  `status_label` VARCHAR(50) DEFAULT 'pending',
  `payment_method` VARCHAR(50) DEFAULT 'stripe',
  `shipping_method` VARCHAR(50) DEFAULT 'standard',
  `shipping_name` TEXT,
  `email` TEXT,
  `phone` TEXT,
  `address` TEXT,
  `city` TEXT,
  `province` TEXT,
  `postal_code` TEXT,
  `ip_address` VARCHAR(45),
  `user_agent` TEXT,
  `tracking_number` VARCHAR(100) DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `order_items` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_id` INT NOT NULL,
  `listing_id` INT NOT NULL,
  `listing_name` TEXT NOT NULL,
  `unit_label` TEXT,
  `unit_price` TEXT NOT NULL,
  `quantity` TEXT NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
