-- Migration: Create orders table
-- Description: Stores user orders placed for services
-- Date: 2026-01-26

CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  service_id INT NOT NULL,
  link VARCHAR(1024) NOT NULL,
  quantity INT NOT NULL,
  charge DECIMAL(10, 4) NOT NULL,
  provider_order_id VARCHAR(255),
  status ENUM('pending', 'processing', 'in_progress', 'complete', 'cancelled', 'refunded', 'failed', 'partial') DEFAULT 'pending',
  status_description VARCHAR(255),
  start_count INT,
  remains INT,
  completed_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE RESTRICT,

  INDEX idx_user_id (user_id),
  INDEX idx_service_id (service_id),
  INDEX idx_status (status),
  INDEX idx_provider_order_id (provider_order_id),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
