-- ============================================================
-- Migration 003: Chamber Member Referral Program
-- ============================================================

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS referral_code VARCHAR(20) UNIQUE AFTER role,
  ADD COLUMN IF NOT EXISTS referred_by_user_id INT UNSIGNED AFTER referral_code,
  ADD INDEX IF NOT EXISTS idx_referral_code (referral_code);

-- Generate a unique referral code for every existing user that doesn't have one
UPDATE users SET referral_code = UPPER(SUBSTRING(MD5(CONCAT(id, UUID())), 1, 8)) WHERE referral_code IS NULL;

CREATE TABLE IF NOT EXISTS member_referrals (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  referrer_user_id INT UNSIGNED NOT NULL,
  referred_user_id INT UNSIGNED NOT NULL UNIQUE,
  status ENUM('pending','converted','expired') DEFAULT 'pending',
  referral_credit DECIMAL(10,2) DEFAULT 25.00,
  credited_at DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referrer_user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_referrer (referrer_user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
