-- ============================================
-- DIAGNOSE REFERRAL SYSTEM ERRORS
-- Run this to find what's wrong
-- ============================================

-- 1. Check if tables exist
SELECT '=== CHECKING TABLES ===' AS step;
SHOW TABLES LIKE 'referrals';
SHOW TABLES LIKE 'referral_rewards';

-- 2. If referrals table exists, check for unique constraints on referralCode
SELECT '=== CHECKING UNIQUE CONSTRAINTS ===' AS step;
SHOW INDEX FROM `referrals` WHERE Column_name = 'referralCode';

-- 3. Check table structures
SELECT '=== CHECKING TABLE STRUCTURES ===' AS step;
-- Only run if table exists
SELECT 'Run this if referrals table exists:' AS note;
DESCRIBE `referrals`;

SELECT 'Run this if referral_rewards table exists:' AS note;
DESCRIBE `referral_rewards`;

-- 4. Check foreign key constraints
SELECT '=== CHECKING FOREIGN KEYS ===' AS step;
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;

-- 5. Check if users table has required columns
SELECT '=== CHECKING USERS TABLE ===' AS step;
SHOW COLUMNS FROM `users` WHERE Field IN ('referralCode', 'referredByCode');