-- Add referral_code column to registrations table
-- This tracks which special access link was used for the registration

ALTER TABLE `registrations` 
ADD COLUMN `referral_code` VARCHAR(50) DEFAULT NULL COMMENT 'Special access link code used for this registration' AFTER `payment_reference`;

-- Add index for performance
CREATE INDEX `idx_referral_code` ON `registrations` (`referral_code`);

-- Add foreign key constraint (optional - for referential integrity)
-- Note: This will fail if the link is deleted, so we use SET NULL
ALTER TABLE `registrations`
ADD CONSTRAINT `fk_referral_code`
FOREIGN KEY (`referral_code`) 
REFERENCES `special_access_links` (`link_code`)
ON DELETE SET NULL
ON UPDATE CASCADE;

-- Verify the changes
DESCRIBE registrations;



