-- Migration 028: Referral tracking scaffold
-- Each user gets a unique ref code. Referred users are linked at registration.

CREATE TABLE IF NOT EXISTS referrals (
    id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    referrer_user_id BIGINT UNSIGNED NOT NULL,
    referred_email   VARCHAR(255) NOT NULL,
    code             VARCHAR(20)  NOT NULL,
    status           ENUM('pending','registered','converted') NOT NULL DEFAULT 'pending',
    referred_user_id BIGINT UNSIGNED NULL COMMENT 'set when referred user registers',
    created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_referral_code (code),
    KEY idx_referrer  (referrer_user_id),
    KEY idx_ref_email (referred_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
