-- Migration: Add Referral System
-- Description: Adds referral_code and referred_by to users, and creates referrals tracking table.

-- Add columns to users table
ALTER TABLE users ADD COLUMN IF NOT EXISTS referral_code VARCHAR(10) UNIQUE AFTER id;
ALTER TABLE users ADD COLUMN IF NOT EXISTS referred_by INT NULL AFTER referral_code;
ALTER TABLE users ADD CONSTRAINT fk_referred_by FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL;

-- Create referrals tracking table
CREATE TABLE IF NOT EXISTS referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer_id INT NOT NULL,
    referee_id INT NOT NULL,
    status ENUM('pending', 'active', 'subscribed') DEFAULT 'pending',
    reward_issued BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (referee_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY (referrer_id, referee_id)
);

-- Index for performance
CREATE INDEX idx_referral_code ON users(referral_code);
CREATE INDEX idx_referred_by ON users(referred_by);
