require('dotenv').config({ path: __dirname + '/../../.env' });
const db = require('../db');
const crypto = require('crypto');

async function generateReferralCode(username, id) {
    const randomStr = crypto.randomBytes(2).toString('hex').toUpperCase();
    const cleanName = (username || 'USER').replace(/[^A-Za-z0-9]/g, '').substring(0, 5).toUpperCase();
    return `${cleanName}-${id}-${randomStr}`;
}

async function runMigration() {
    console.log("Starting Referral System Database Migration...");
    try {
        // 1. Add Columns to Users safely
        console.log("1. Modifying 'users' table...");
        try {
            await db.query(`SELECT referral_code FROM users LIMIT 1`);
            console.log("   ✅ Columns already exist in users table.");
        } catch (e) {
            console.log("   --> Adding referral_code and referred_by columns...");
            await db.query(`ALTER TABLE users ADD COLUMN referral_code VARCHAR(50)`);
            await db.query(`ALTER TABLE users ADD COLUMN referred_by INT`);
            console.log("   ✅ Columns added successfully.");
        }

        // 2. Create Referrals Table
        console.log("2. Creating 'referrals' table...");
        await db.query(`
            CREATE TABLE IF NOT EXISTS referrals (
                id INT AUTO_INCREMENT PRIMARY KEY,
                referrer_id INT NOT NULL,
                referee_id INT NOT NULL,
                status ENUM('pending', 'active', 'subscribed') DEFAULT 'pending',
                reward_earned BOOLEAN DEFAULT FALSE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
                FOREIGN KEY (referee_id) REFERENCES users(id) ON DELETE CASCADE
            )
        `);
        console.log("   ✅ Referrals table is ready.");

        // 3. Generate Codes for Existing Users
        console.log("3. Backfilling referral codes for existing users...");
        const { rows: users } = await db.query(`SELECT id, username FROM users WHERE referral_code IS NULL`);
        let generatedCount = 0;
        
        for (const user of users) {
            let unique = false;
            let code = '';
            let attempts = 0;
            while (!unique && attempts < 5) {
                code = await generateReferralCode(user.username, user.id);
                const { rows: existing } = await db.query(`SELECT id FROM users WHERE referral_code = ?`, [code]);
                if (existing.length === 0) {
                    unique = true;
                }
                attempts++;
            }
            if (unique) {
                await db.query(`UPDATE users SET referral_code = ? WHERE id = ?`, [code, user.id]);
                generatedCount++;
            } else {
                console.log(`   ❌ Failed to generate unique code for user ${user.id}`);
            }
        }
        console.log(`   ✅ Generated codes for ${generatedCount} users.`);

        console.log("\n🎉 Migration completed successfully.");
        process.exit(0);
    } catch (error) {
        console.error("\n❌ Fatal Error during migration:");
        console.error(error);
        process.exit(1);
    }
}

runMigration();
