-- Migration 013: Promo Codes
-- Creates promo_codes and promo_code_usages tables

CREATE TABLE IF NOT EXISTS promo_codes (
  id             INT           AUTO_INCREMENT PRIMARY KEY,
  code           VARCHAR(50)   NOT NULL UNIQUE,
  discount_type  ENUM('fixed', 'percentage') NOT NULL,
  discount_value DECIMAL(10,2) NOT NULL,
  -- fixed: amount in dollars (e.g. 5.00 = $5 off)
  -- percentage: 1–100 (e.g. 20 = 20% off)
  valid_until    DATE          NOT NULL,
  max_uses       INT           NOT NULL DEFAULT 100,
  used_count     INT           NOT NULL DEFAULT 0,
  is_active      TINYINT(1)   NOT NULL DEFAULT 1,
  stripe_coupon_id           VARCHAR(100) NULL,
  stripe_promotion_code_id   VARCHAR(100) NULL,
  created_at     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS promo_code_usages (
  id             INT  AUTO_INCREMENT PRIMARY KEY,
  promo_code_id  INT  NOT NULL,
  user_id        INT  NOT NULL,
  used_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_pcu_promo FOREIGN KEY (promo_code_id) REFERENCES promo_codes(id) ON DELETE CASCADE,
  CONSTRAINT fk_pcu_user  FOREIGN KEY (user_id)       REFERENCES users(id)       ON DELETE CASCADE,
  UNIQUE KEY unique_user_promo (user_id, promo_code_id)
);
