-- نظام الكروت (Vouchers) - SQL
-- ملاحظة: الصفحات تقوم بإنشاء الجداول تلقائياً عند الدخول، هذا الملف للنسخ اليدوي فقط.

CREATE TABLE IF NOT EXISTS voucher_batches (
  id INT AUTO_INCREMENT PRIMARY KEY,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by INT NULL,
  title VARCHAR(160) NOT NULL DEFAULT '',
  type ENUM('time','data','both') NOT NULL DEFAULT 'time',
  duration_minutes INT NULL,
  data_bytes BIGINT NULL,
  rate_limit VARCHAR(64) NOT NULL DEFAULT '',
  price DECIMAL(10,2) NULL,
  qty INT NOT NULL DEFAULT 0,
  prefix VARCHAR(16) NOT NULL DEFAULT 'V',
  code_len INT NOT NULL DEFAULT 8,
  notes TEXT NULL,
  INDEX(created_at),
  INDEX(created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vouchers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  batch_id INT NULL,
  code VARCHAR(64) NOT NULL,
  passcode VARCHAR(64) NOT NULL,
  type ENUM('time','data','both') NOT NULL DEFAULT 'time',
  duration_minutes INT NULL,
  data_bytes BIGINT NULL,
  rate_limit VARCHAR(64) NOT NULL DEFAULT '',
  price DECIMAL(10,2) NULL,
  status ENUM('new','sold','used','expired','cancelled') NOT NULL DEFAULT 'new',
  sold_at DATETIME NULL,
  sold_by INT NULL,
  sold_to VARCHAR(160) NULL,
  used_at DATETIME NULL,
  used_ip VARCHAR(64) NULL,
  mt_username VARCHAR(64) NULL,
  mt_password VARCHAR(64) NULL,
  expires_at DATETIME NULL,
  last_seen_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_code (code),
  INDEX(batch_id),
  INDEX(status),
  INDEX(sold_at),
  INDEX(used_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS voucher_usage (
  id INT AUTO_INCREMENT PRIMARY KEY,
  voucher_id INT NOT NULL,
  event VARCHAR(40) NOT NULL,
  event_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  meta_json TEXT NULL,
  INDEX(voucher_id),
  INDEX(event),
  INDEX(event_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
