-- 018_promo_codes.sql — Promo code system

CREATE TABLE IF NOT EXISTS promo_codes (
  id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code               VARCHAR(32)  NOT NULL UNIQUE,
  type               ENUM('free_year','extend_year') NOT NULL,
  is_used            TINYINT(1)   NOT NULL DEFAULT 0,
  used_by_tenant_id  INT UNSIGNED NULL,
  used_by_user_id    INT UNSIGNED NULL,
  used_at            DATETIME     NULL,
  created_by_admin   INT UNSIGNED NULL,
  created_at         TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_promo_tenant FOREIGN KEY (used_by_tenant_id) REFERENCES tenants(id)
);

ALTER TABLE billing_purchases
  ADD COLUMN IF NOT EXISTS promo_code_id INT UNSIGNED NULL AFTER stripe_payment_intent,
  ADD COLUMN IF NOT EXISTS notes         VARCHAR(255)  NULL AFTER promo_code_id;
