-- ------------------------------------------------------------
-- Migration 005: Member referrals.
-- Run this ONLY if you already have an existing ltda_association
-- database from before this feature was added. Fresh installs
-- using schema.sql already include this.
--
-- Every member gets their own referral_code to share. When someone
-- registers using that code, referred_by is set on their account.
-- Once that new member's profile documents are verified by an admin
-- (a "completed profile"), a one-time KES 100 bonus is recorded in
-- referral_bonuses and credited to the referrer's savings balance.
-- ------------------------------------------------------------

ALTER TABLE users
    ADD COLUMN referral_code VARCHAR(20) DEFAULT NULL UNIQUE AFTER documents_verified_by,
    ADD COLUMN referred_by INT DEFAULT NULL AFTER referral_code,
    ADD FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS referral_bonuses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer_id INT NOT NULL,
    referred_id INT NOT NULL UNIQUE,
    amount DECIMAL(10,2) NOT NULL DEFAULT 100.00,
    awarded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Backfill a referral_code for every existing MEMBER account that doesn't
-- have one yet, so current members can start referring people right away.
-- Admin accounts are management-only and intentionally never get a referral
-- code - they aren't members and can't refer people or be referred.
UPDATE users
SET referral_code = CONCAT('LTDA-R-', LPAD(id, 5, '0'))
WHERE referral_code IS NULL AND role = 'member';