-- ============================================================
--  System Admin + Promo Codes migration
-- ============================================================

CREATE TABLE IF NOT EXISTS system_admins (
    id            INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
    email         VARCHAR(255)   NOT NULL UNIQUE,
    password_hash VARCHAR(255)   NOT NULL,
    first_name    VARCHAR(100)   NOT NULL,
    last_name     VARCHAR(100)   NOT NULL,
    totp_secret   VARCHAR(100)   NULL,
    totp_enabled  TINYINT(1)     NOT NULL DEFAULT 0,
    is_active     TINYINT(1)     NOT NULL DEFAULT 1,
    last_login    TIMESTAMP      NULL,
    created_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS promo_codes (
    id              INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
    code            VARCHAR(50)    NOT NULL UNIQUE,
    duration_months INT            NOT NULL COMMENT '3, 6, or 12',
    max_uses        INT            NOT NULL DEFAULT 1,
    uses_count      INT            NOT NULL DEFAULT 0,
    created_by      INT UNSIGNED   NOT NULL,
    expires_at      DATE           NULL,
    is_active       TINYINT(1)     NOT NULL DEFAULT 1,
    created_at      TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES system_admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS promo_code_redemptions (
    id              INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
    promo_code_id   INT UNSIGNED   NOT NULL,
    tenant_id       INT UNSIGNED   NOT NULL,
    extended_until  DATE           NOT NULL,
    redeemed_at     TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenant_code (promo_code_id, tenant_id),
    FOREIGN KEY (promo_code_id) REFERENCES promo_codes(id),
    FOREIGN KEY (tenant_id)     REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
