-- ============================================================================
-- Migration v9: Referral system
-- Run this ONCE on your existing database (phpMyAdmin > SQL tab).
-- Safe to run more than once (uses MariaDB's native ADD COLUMN IF NOT EXISTS,
-- which needs no special routine privileges).
-- ============================================================================

ALTER TABLE mess_accounts
    ADD COLUMN IF NOT EXISTS referral_code VARCHAR(20) DEFAULT NULL AFTER monthly_house_rent_payable,
    ADD COLUMN IF NOT EXISTS referred_by_mess_id INT DEFAULT NULL AFTER referral_code;

-- Backfill: give every existing mess account its own unique referral code so
-- the "রেফারেল" page works immediately for accounts created before this update.
-- UPDATE IGNORE so a rare code collision just skips that one row instead of
-- aborting the whole statement — referrals.php also lazily generates a code
-- on first visit for any account that still has NULL after this.
UPDATE IGNORE mess_accounts
SET referral_code = UPPER(SUBSTRING(MD5(CONCAT(id, '-', RAND())), 1, 6))
WHERE referral_code IS NULL;