-- ============================================
-- CHECK REFERRAL SYSTEM STATUS
-- Run this to see what's missing or causing errors
-- ============================================

-- Check if columns exist in users table
SELECT 'Checking users table columns...' AS status;
SHOW COLUMNS FROM `users` LIKE 'referralCode';
SHOW COLUMNS FROM `users` LIKE 'referredByCode';

-- Check if tables exist
SELECT 'Checking if tables exist...' AS status;
SHOW TABLES LIKE 'referrals';
SHOW TABLES LIKE 'referral_rewards';

-- If referrals table exists, check its structure
SELECT 'Checking referrals table structure...' AS status;
DESCRIBE `referrals`;

-- If referral_rewards table exists, check its structure
SELECT 'Checking referral_rewards table structure...' AS status;
DESCRIBE `referral_rewards`;

-- Check for unique constraints on referralCode in referrals table
SELECT 'Checking for unique indexes on referralCode in referrals table...' AS status;
SHOW INDEX FROM `referrals` WHERE Column_name = 'referralCode';

-- Check foreign key constraints
SELECT 'Checking foreign key constraints...' AS status;
SELECT 
  CONSTRAINT_NAME,
  TABLE_NAME,
  COLUMN_NAME,
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME IN ('referrals', 'referral_rewards')
AND REFERENCED_TABLE_NAME IS NOT NULL;