-- Promo codes for free billing extensions

CREATE TABLE IF NOT EXISTS promo_codes (
  id                CHAR(36)          NOT NULL DEFAULT (UUID()),
  code              VARCHAR(64)       NOT NULL,
  months_granted    SMALLINT UNSIGNED NOT NULL,
  max_redemptions   INT UNSIGNED      NOT NULL DEFAULT 1,
  redemption_count  INT UNSIGNED      NOT NULL DEFAULT 0,
  expires_at        DATETIME,
  is_active         BOOLEAN           NOT NULL DEFAULT TRUE,
  notes             TEXT,
  created_by        CHAR(36),
  created_at        DATETIME          NOT NULL DEFAULT NOW(),
  updated_at        DATETIME          NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_promo_code (code),
  INDEX idx_promo_active (is_active, expires_at),
  CONSTRAINT fk_promo_created_by FOREIGN KEY (created_by) REFERENCES system_admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS promo_code_redemptions (
  id                CHAR(36)          NOT NULL DEFAULT (UUID()),
  promo_code_id     CHAR(36)          NOT NULL,
  tenant_id         CHAR(36)          NOT NULL,
  user_id           CHAR(36),
  code              VARCHAR(64)       NOT NULL,
  months_granted    SMALLINT UNSIGNED NOT NULL,
  paid_until_before DATETIME,
  paid_until_after  DATETIME,
  redeemed_at       DATETIME          NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  INDEX idx_promo_redemptions_tenant (tenant_id, redeemed_at),
  INDEX idx_promo_redemptions_code (promo_code_id),
  CONSTRAINT fk_promo_redemption_code FOREIGN KEY (promo_code_id) REFERENCES promo_codes(id) ON DELETE CASCADE,
  CONSTRAINT fk_promo_redemption_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_promo_redemption_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;