const db = require('../db');
const { sendVerificationReminderEmail } = require('../utils/emailService');
const { sendPushNotifications } = require('../utils/notificationService');

/**
 * Service to handle user-related business logic, 
 * specifically cleanup and retention tasks.
 */

/**
 * Perform cleanup of unverified users:
 * 1. 1-day reminder (Initial Push)
 * 2. 6-day reminder (Critical Email + Push)
 * 3. 7-day deletion (Account Wipe)
 */
async function performUnverifiedCleanup() {
    let remindersSent = 0;
    let accountsDeleted = 0;

    // -1. Self-Healing Phase: Auto-Cleanup Typos (Fixes existing production data on next run)
    try {
        // Identify unverified accounts with double dots (common typo that caused the current spam)
        const typoUsers = await db.query("SELECT id, email FROM users WHERE (is_verified = 0 OR is_verified IS NULL) AND email LIKE '%..%'");
        if (typoUsers.rows.length > 0) {
            console.log(`[Auto-Cleanup] Found ${typoUsers.rows.length} typo accounts. Purging...`);
            for (const user of typoUsers.rows) {
                // Delete related data first (mirroring logic below)
                await db.query('DELETE FROM investigations WHERE user_id = ?', [user.id]);
                await db.query('DELETE FROM user_path_progress WHERE user_id = ?', [user.id]);
                await db.query('DELETE FROM mobile_lesson_completions WHERE user_id = ?', [user.id]);
                await db.query('DELETE FROM user_xp WHERE user_id = ?', [user.id]);
                await db.query('DELETE FROM notifications WHERE user_id = ?', [user.id]);
                await db.query('DELETE FROM users WHERE id = ?', [user.id]);
                accountsDeleted++;
                console.log(`[Auto-Cleanup] Deleted typo account: ${user.email}`);
            }
        }
    } catch (e) {
        console.error('[Auto-Cleanup] Typo cleanup failed:', e.message);
    }

    // 0. Initial Phase: Find users unverified for 1+ day but less than 2 days
    try {
        const firstReminderUsers = await db.query(`
            SELECT id, email, expo_push_token 
            FROM users 
            WHERE (is_verified = 0 OR is_verified IS NULL)
              AND created_at < NOW() - INTERVAL 1 DAY
              AND created_at >= NOW() - INTERVAL 2 DAY
              AND (first_push_sent = 0 OR first_push_sent IS NULL)
        `);

        for (const user of firstReminderUsers.rows) {
            if (user.expo_push_token) {
                try {
                    // Atomic Claim: Ensure only one process sends this push
                    // We check if it's currently 0 or NULL, and update to 1. 
                    // ROW_COUNT() (affectedRows) will be 1 only for the "winner" process.
                    const claimResult = await db.query(
                        'UPDATE users SET first_push_sent = 1 WHERE id = ? AND (first_push_sent = 0 OR first_push_sent IS NULL)', 
                        [user.id]
                    );
                    
                    if (claimResult.rowCount === 1) {
                        console.log(`[ACL] Claim SUCCESS for 1st Push (User: ${user.email}, PID: ${process.pid})`);
                        await sendPushNotifications(
                            [user.expo_push_token],
                            '[Action Required] Verify Identity 🛰️',
                            'Please verify your email to gain full access to the Analyst Dashboard.'
                        );
                    } else {
                        // Skip if already claimed by another process
                        continue;
                    }
                } catch (pushErr) {
                    console.error(`Failed to send initial push to ${user.email}:`, pushErr.message);
                }
            }
        }
    } catch (e) {
        console.error('Initial reminder query failed:', e.message);
    }

    // 1. Critical Phase: Find users unverified for 6+ days (24h before deletion)
    try {
        const reminderUsers = await db.query(`
            SELECT id, email, name, verification_token, expo_push_token 
            FROM users 
            WHERE (is_verified = 0 OR is_verified IS NULL)
              AND created_at < NOW() - INTERVAL 6 DAY
              AND created_at >= NOW() - INTERVAL 7 DAY
              AND (verification_reminder_sent = 0 OR verification_reminder_sent IS NULL)
        `);

        for (const user of reminderUsers.rows) {
            try {
                // Atomic Claim: Ensure only one process sends this critical reminder
                // This is the CRITICAL fix for the "3x Email Spam" issue.
                const claimResult = await db.query(
                    'UPDATE users SET verification_reminder_sent = 1 WHERE id = ? AND (verification_reminder_sent = 0 OR verification_reminder_sent IS NULL)', 
                    [user.id]
                );
                
                if (claimResult.rowCount === 1) {
                    const timestamp = new Date().toISOString();
                    console.log(`[ACL] Claim SUCCESS for Critical Reminder (User: ${user.email}, PID: ${process.pid}, Time: ${timestamp})`);
                    
                    // Send Email Reminder
                    await sendVerificationReminderEmail(user.email, user.name, user.verification_token);
                    
                    // Send Mobile Push Reminder (if token exists)
                    if (user.expo_push_token) {
                        try {
                            await sendPushNotifications(
                                [user.expo_push_token],
                                '[CRITICAL] Account Deletion Warning ⚠️',
                                'Your account is scheduled for automatic deletion in 24 hours. Verify your email now to retain access.'
                            );
                        } catch (pushErr) {
                            console.error(`Failed to send critical push to ${user.email}:`, pushErr.message);
                        }
                    }

                    remindersSent++;
                } else {
                    // Already processed by another worker/process
                    console.log(`[ACL] Claim SKIPPED for ${user.email} (Already processed or locked by another worker)`);
                    continue;
                }
            } catch (e) {
                console.error(`Failed to send critical reminder to ${user.email}:`, e.message);
            }
        }
    } catch (e) {
        console.error('Critical reminder query failed:', e.message);
    }

    // 2. Find users unverified for 7+ days -> delete them
    try {
        const deleteUsers = await db.query(`
            SELECT id, email, name 
            FROM users 
            WHERE (is_verified = 0 OR is_verified IS NULL)
              AND created_at < NOW() - INTERVAL 7 DAY
        `);

        for (const user of deleteUsers.rows) {
            try {
                // Delete related data first
                await db.query('DELETE FROM investigations WHERE user_id = ?', [user.id]);
                await db.query('DELETE FROM user_path_progress WHERE user_id = ?', [user.id]);
                await db.query('DELETE FROM mobile_lesson_completions WHERE user_id = ?', [user.id]);
                await db.query('DELETE FROM user_xp WHERE user_id = ?', [user.id]);
                await db.query('DELETE FROM notifications WHERE user_id = ?', [user.id]);
                
                // Finally delete user
                await db.query('DELETE FROM users WHERE id = ?', [user.id]);
                accountsDeleted++;
                console.log(`Successfully deleted unverified user: ${user.email}`);
            } catch (e) {
                console.error(`Failed to delete unverified user ${user.email}:`, e.message);
            }
        }
    } catch (e) {
        console.error('Deletion query failed:', e.message);
    }

    return { remindersSent, accountsDeleted };
}

module.exports = {
    performUnverifiedCleanup
};
