<?php
/**
 * A Recursive Functional Method To Scan DB Fields & Process Them
 * This Script Will Be Running Continuously & 
 * Look For New Not Verified Emails & 
 * Will Check & update them accordingly
 **/

// Keep running the script no matter what
ignore_user_abort(true);
set_time_limit(0);
ini_set('memory_limit', '-1');

if ((isset($_REQUEST['filename']) && isset($_REQUEST['uid']))) {

    global $prev_path, $log, $db, $evp_options;

    // Get path
    $phpver = (float) phpversion();
    if ($phpver >= 7.0) {
        $prev_path = dirname(__DIR__, 2);
        $s_path = $prev_path;
    }

    include($prev_path . '/functions/pagename.php');
    // include($prev_path . '/functions/' . $session);

    // Session::checkSession_app();

    include($prev_path . '/db/db.cnf.php');
    include('scan/database.php');
    require_once($prev_path . '/functions/class.logger.php');
    $log = new Logger();
    $db = new DB();

    require_once($prev_path . '/functions/class.runtime.signal.php');
    $runtimeGate = RuntimeSignal::instance();
    if (!$runtimeGate->enforce('worker:batch')) {
        if ($log)
            $log->system_debug('[LICENSE] Batch worker denied: ' . $runtimeGate->message());
        exit();
    }

    $log->system_debug('Initializing script');

    // Fetch options from the database
    $evp_options = fetch_evp_options($db);
    extract($evp_options);

    include('scan/class.verify.email.php');
    require_once($prev_path . '/functions/class.warmup.manager.php');
    $warmupMgr = new WarmupManager($db, $log);

    $filename = $_REQUEST['filename'];
    $user_id = isset($_REQUEST['uid']) ? intval($_REQUEST['uid']) : 0;
    if (!$user_id) {
        if (isset($log))
            $log->system_debug('No user_id provided, exiting.');
        exit();
    }
    $worker_id = isset($_REQUEST['worker_id']) ? intval($_REQUEST['worker_id']) : 0;
    $total_workers = isset($_REQUEST['total_workers']) ? intval($_REQUEST['total_workers']) : 1;

    // Register shutdown function to clear PID and log fatal errors (merged)
    register_shutdown_function(function () use ($db, $filename, $user_id, $log) {
        $db->update("UPDATE evp_email_list_metadata SET pid = NULL WHERE csv_file_name = '$filename' AND user_id = '$user_id'");
        if ($log)
            $log->system_debug('PID cleared by shutdown function for: ' . $filename);
        $error = error_get_last();
        if ($error !== null && $log) {
            $log->system_debug('[SHUTDOWN ERROR] v_email.php fatal error: ' . print_r($error, true));
        }
    });

    // Initialize counters to prevent undefined variable issues
    $count_valid = 0;
    $count_role_acc = 0;
    $count_catch_all = 0;
    $count_unknown = 0;
    $count_invalid = 0;
    $count_syntax_error = 0;
    $count_disponsable_acc = 0;
    $count_free_acc = 0;

    $task_delete_current = "DELETE FROM evp_task WHERE csv_name = '$filename' AND user_id = '$user_id' AND status = 'scanning'";
    $csv_data = fetch_csv_data($filename, $user_id);

    $e_metadata = $csv_data['csv_file_name'];
    $count_valid = $csv_data['valid'];
    $count_invalid = $csv_data['invalid'];
    $count_catchall = $csv_data['catch_all'];
    $count_unknown = $csv_data['unknown'];
    $count_skipped = $csv_data['skipped'];
    $scan_status = $csv_data['scan_status'];
    $pid = $csv_data['pid'];
    $total_verify = $count_valid + $count_invalid + $count_catchall + $count_unknown + $count_skipped;
    $total_email = $csv_data['total'];
    $count_not_verify = $total_email - $total_verify;

    // user_id is not valid and background process exit call
    if ($user_id == 0 || $scan_status == 'completed') {
        exit_task($filename, $task_delete_current);
    }

    // Add the new evp_task for this user
    if (!task_exists($filename, $user_id)) {
        add_new_task($filename, $user_id, $total_verify, $total_email, $scan_status, $e_metadata, $task_delete_current);
    }

    if ($multi_scan == 'false') {
        update_task_status($filename, $user_id, $pid);
    }

    $log->system_debug('SCAN -> VERIFY START');
    $log->system_debug("FILENAME: -> $filename  |  USER: -> $user_id");

} else {
    // user_id is not provided so exit early
    $log->system_debug('SCAN EXIT -> NO USER ID');
    exit();
}

// each time fetch how many emails to verify, public domains don't like it to be increased! We recommend 10 at a time.
$limit = $batch_limit;
$start_time = time();

// Ensure $s_path is defined
if (!isset($s_path)) {
    $s_path = sys_get_temp_dir(); // Use system temp directory as fallback
    $log->system_debug('Fallback $s_path set to: ' . $s_path);
}

$pid = getmypid();
$log->system_debug('Current process PID: ' . $pid);

// Save the PID to the database
$db->update("UPDATE evp_email_list_metadata SET pid = '$pid' WHERE csv_file_name = '$filename' AND user_id = '$user_id'");
$log->system_debug('Saved PID to database: ' . $pid . ' for ' . $filename);

if (($total_verify == $total_email) && ($scan_status == 'processing')) {
    $sendmail = new SendMail;
    if ($check_mailbox == 'true') {
        $sendmail->verify_mailbox($check_mailbox);
        $log->system_debug('Send Email Func Call: ' . $filename);
    } else {
        $sendmail->scan_done();
        $log->system_debug('Scan Complete Email (v_email): ' . $filename);

        // Clear the PID in the database after scan completion
        $db->update("UPDATE evp_email_list_metadata SET pid = NULL WHERE csv_file_name = '$filename'");
        $log->system_debug('Cleared PID in database for: ' . $filename);

        exit();
    }
}

if (!function_exists('evp_escape_nullable')) {
    function evp_escape_nullable($link, $value)
    {
        if ($value === null) {
            return '';
        }
        if ($value === true) {
            $value = '1';
        } elseif ($value === false) {
            $value = '';
        }
        if (is_array($value) || is_object($value)) {
            $encoded = json_encode($value);
            $value = $encoded === false ? '' : $encoded;
        }
        if (!is_string($value)) {
            $value = (string) $value;
        }
        if ($link instanceof mysqli) {
            return mysqli_real_escape_string($link, $value);
        }
        return addslashes($value);
    }
}

// Set unlimited execution time to prevent timeout
set_time_limit(0);

// Call the email verification function
prefix_verify_emails();

function fetch_csv_data($filename, $user_id)
{
    global $db, $log;
    $csv_file_info = "SELECT * FROM evp_email_list_metadata WHERE csv_file_name = '$filename' AND user_id = '$user_id'";
    $csv_info = $db->select($csv_file_info);
    if ($csv_info === false) {
        $log->system_debug('Database query failed: ' . $db->error_log);
        return [];
    }
    $csv_data = $csv_info->fetch_assoc();
    if ($csv_data === null) {
        $log->system_debug('No CSV data found for filename: ' . $filename . ' and user_id: ' . $user_id);
        return [];
    }
    return $csv_data;
}

function exit_task($filename, $task_delete_current)
{
    global $log, $db;
    $log->system_debug('Exiting task for filename: ' . $filename);
    $db->delete($task_delete_current);
    exit();
}

function task_exists($filename, $user_id)
{
    global $db, $log;
    $log->system_debug('Checking if task exists for filename: ' . $filename . ' and user_id: ' . $user_id);
    $task_email_check_q = "SELECT * FROM evp_task WHERE csv_name = '$filename' AND user_id = '$user_id' AND status = 'scanning'";
    $task_email_check_r = $db->select($task_email_check_q);
    return mysqli_num_rows($task_email_check_r) > 0;
}

function add_new_task($filename, $user_id, $total_verify, $total_email, $scan_status, $e_metadata, $task_delete_current)
{
    global $db, $log;
    $task_insert = "INSERT INTO evp_task (csv_name, status, user_id) VALUES ('$filename', 'scanning', '$user_id')";
    if (($total_verify != $total_email) && (($scan_status == 'pending') || ($scan_status == 'processing'))) {
        if ($db->insert($task_insert) === true) {
            $csvdata_update = "UPDATE evp_email_list_metadata SET scan_status = 'processing' WHERE csv_file_name = '$filename' AND user_id = '$user_id'";
            $db->insert($csvdata_update);
        }
    } elseif (($e_metadata == $filename) && ($total_verify == $total_email) && ($scan_status == 'completed')) {
        $db->delete($task_delete_current);
        exit();
    }
}

function update_task_status($filename, $user_id, $pid)
{
    global $db;
    $task_update = "UPDATE evp_email_list_metadata e_mdata
                    LEFT JOIN evp_task e_task
                    ON e_task.csv_name = e_mdata.csv_file_name
                    SET e_mdata.scan_status = 'pending'
                    WHERE e_mdata.scan_status = 'processing' AND
                          e_mdata.user_id = '$user_id' AND
                          e_mdata.csv_file_name <> '$filename' AND
                          e_task.status != 'retry_emailsend'";

    if ($db->update($task_update) === true) {
        $task_delete = "DELETE FROM evp_task WHERE csv_name <> '$filename' AND user_id = '$user_id' AND status = 'scanning'";
        $db->delete($task_delete);
        if (!empty($pid)) {
            // mysqli_kill($db->link, intval($pid));
        }
    }
}

function prefix_verify_emails()
{
    global $db, $log, $task_delete, $start_time, $limit, $multi_scan, $site_logo, $site_title, $smtp_debug, $system_debug, $scan_debug, $system_email, $sys_smtp_data, $sys_smtp, $smtp_host, $smtp_port, $smtp_security, $smtp_auth, $smtp_user, $smtp_pass, $smtp_ssl_verify, $ic_send_mail, $ic_smtp, $ic_smtp_host, $ic_smtp_port, $ic_smtp_security, $ic_smtp_auth, $ic_smtp_user, $ic_smtp_pass, $ic_smtp_ssl_verify, $a_url, $s_url, $task_delete_current, $user_id, $filename, $count_valid, $count_role_acc, $count_catch_all, $count_unknown, $count_invalid, $count_syntax_error, $count_disponsable_acc, $count_free_acc, $total_verify, $total_email, $scan_status, $task_insert, $prev_path, $e_metadata, $toemail, $mailbox_status, $status, $check_mailbox, $toemail, $worker_id, $warmupMgr;

    $log->system_debug('Starting email verification process');

    if (!$db->error) {
        $db->connect();
    }

    if ($scan_status == 'processing') {
        $log->system_debug('SCAN -> PROCESSING -> ' . $filename);

        $processed_email_ids_this_run = []; // Safeguard for current script execution

        while (true) { // Main batch processing loop
            $scan_status_check_loop = $db->select("SELECT scan_status FROM evp_email_list_metadata WHERE csv_file_name = '$filename' AND user_id = '$user_id'");
            $scan_status_row_loop = $scan_status_check_loop ? $scan_status_check_loop->fetch_assoc() : null;
            if (!$scan_status_row_loop || $scan_status_row_loop['scan_status'] !== 'processing') {
                $log->system_debug("[BATCH EXIT LOOP] scan_status is not 'processing' for $filename (user: $user_id). Exiting main verification loop.");
                break; // Exit while(true)
            }

            $log->system_debug("ASSIGN BATCH: filename=$filename, user_id=$user_id, worker_id=$worker_id, limit=$limit");
            if (empty($worker_id) || !is_numeric($worker_id) || $worker_id <= 0) {
                $log->system_debug('[ERROR] worker_id is not set or invalid. Exiting batch assignment.');
                break;
            }
            // Use subquery workaround for LIMIT in UPDATE
            $assign_batch_sql = "UPDATE evp_email_list SET processing_worker_id = $worker_id, worker_status = 'processing', processing_assigned_at = NOW()\n"
                . "WHERE id IN (\n"
                . "  SELECT id FROM (\n"
                . "    SELECT id FROM evp_email_list\n"
                . "    WHERE csv_file_name = '$filename' AND user_id = '$user_id'\n"
                . "      AND (email_status = 'Not Verify' OR LTRIM(RTRIM(email_status)) = '')\n"
                . "      AND (worker_status = 'pending' OR worker_status IS NULL)\n"
                . "      AND processing_worker_id IS NULL\n"
                . "      AND (processing_assigned_at IS NULL OR processing_assigned_at < NOW() - INTERVAL 10 MINUTE)\n"
                . "      AND worker_status != 'done'\n"
                . "      AND email_status != 'Invalid'\n"
                . "      AND email_status != 'Valid'\n"
                . "      AND email_status != 'Catch All'\n"
                . "      AND email_status != 'Unknown'\n"
                . "      AND email_status != 'Skipped'\n"
                . "    LIMIT $limit\n"
                . "  ) as t\n"
                . ")";
            $log->system_debug("ASSIGN BATCH SQL: $assign_batch_sql");
            $db->update($assign_batch_sql);
            $log->system_debug("ASSIGN BATCH affected_rows: " . $db->link->affected_rows);
            $log->system_debug("ASSIGN BATCH SQL ERROR: " . $db->link->error);

            // --- ATOMIC BATCH ASSIGNMENT + METADATA UPDATE ---
            $db->link->begin_transaction();
            try {
                // Update metadata to reflect processing_now count
                $processing_now_count = 0;
                if ($db->link->affected_rows > 0) {
                    $processing_now_count = $db->link->affected_rows;
                } else {
                    // If no new assignments, count current processing for this worker
                    $processing_now_sql = "SELECT COUNT(*) as cnt FROM evp_email_list\n"
                        . "WHERE csv_file_name = '$filename' AND user_id = '$user_id'\n"
                        . "AND processing_worker_id = $worker_id AND worker_status = 'processing'\n"
                        . "AND (email_status = 'Not Verify' OR LTRIM(RTRIM(email_status)) = '')";
                    $processing_now_res = $db->select($processing_now_sql);
                    $processing_now_row = $processing_now_res ? $processing_now_res->fetch_assoc() : null;
                    $processing_now_count = $processing_now_row ? (int) $processing_now_row['cnt'] : 0;
                }
                // $db->update("UPDATE evp_email_list_metadata SET processing_now = $processing_now_count WHERE csv_file_name = '$filename' AND user_id = '$user_id'");
                $db->link->commit();
            } catch (Exception $e) {
                $db->link->rollback();
                $log->system_debug('Atomic batch assignment or metadata update failed: ' . $e->getMessage());
                break;
            }
            // --- END ATOMIC BATCH ASSIGNMENT ---

            // Fetch the batch for this worker (only those assigned recently)
            $fetch_current_batch_sql = "SELECT * FROM evp_email_list\n"
                . "WHERE csv_file_name = '$filename' AND user_id = '$user_id'\n"
                . "AND processing_worker_id = $worker_id AND worker_status = 'processing'\n"
                . "AND (email_status = 'Not Verify' OR LTRIM(RTRIM(email_status)) = '')\n"
                . "AND processing_assigned_at > NOW() - INTERVAL 10 MINUTE\n"
                . "LIMIT $limit";
            $log->system_debug("FETCH BATCH SQL: $fetch_current_batch_sql");
            $email_st_current_batch = $db->select($fetch_current_batch_sql);
            $count_emails_in_current_batch = $email_st_current_batch ? mysqli_num_rows($email_st_current_batch) : 0;
            $log->system_debug("FETCH BATCH count: $count_emails_in_current_batch");

            if ($count_emails_in_current_batch == 0) {
                $log->system_debug("No emails found for worker $worker_id to process for $filename in this iteration. Exiting main verification loop.");

                // --- SOLUTION: Check if scan is actually complete or stuck ---
                $meta = $db->select("SELECT valid, invalid, catch_all, unknown, skipped, total, scan_status FROM evp_email_list_metadata WHERE csv_file_name = '$filename' AND user_id = '$user_id'");
                if ($meta && $meta->num_rows > 0) {
                    $meta_row = $meta->fetch_assoc();
                    $total_verify = $meta_row['valid'] + $meta_row['invalid'] + $meta_row['catch_all'] + $meta_row['unknown'] + $meta_row['skipped'];
                    $total_email = $meta_row['total'];
                    if ($total_verify == $total_email && $meta_row['scan_status'] == 'processing') {
                        $db->update("UPDATE evp_email_list_metadata SET scan_status = 'completed' WHERE csv_file_name = '$filename' AND user_id = '$user_id'");
                        $log->system_debug('[COMPLETE] All emails verified for ' . $filename . '. Marked scan_status as completed.');
                    } else if ($total_verify < $total_email) {
                        // Check if there are any emails left in Not Verify or pending state
                        $pending_check = $db->select("SELECT COUNT(*) as cnt FROM evp_email_list WHERE csv_file_name = '$filename' AND user_id = '$user_id' AND (email_status = 'Not Verify' OR LTRIM(RTRIM(email_status)) = '' OR worker_status = 'pending' OR worker_status IS NULL)");
                        $pending_row = $pending_check ? $pending_check->fetch_assoc() : null;
                        $pending_count = $pending_row ? (int) $pending_row['cnt'] : 0;
                        if ($pending_count == 0) {
                            $log->system_debug('[STUCK] Scan appears stuck for ' . $filename . ': not all emails verified, but no pending emails left. Manual review may be required.');
                        } else {
                            $log->system_debug('[INFO] Scan still in progress for ' . $filename . ': ' . $pending_count . ' emails remain to be processed.');
                        }
                    }
                }
                break; // Exit while(true)
            }

            $log->system_debug("Worker $worker_id processing batch of $count_emails_in_current_batch emails for $filename.");

            $batch_summary = [
                'processed' => 0,
                'updated' => 0,
                'skipped' => 0,
                'failed' => 0
            ];
            $update_queries = [];
            $batch_log_lines = [];

            // --- MAIN SERVER (LOCAL) VERIFICATION, UNCHANGED ---
            while ($row = $email_st_current_batch->fetch_assoc()) {
                $batch_summary['processed']++;
                $email_id = $row['id'];
                $toemail = $row['email_name'];

                $email_arr = explode('@', $toemail);
                if (count($email_arr) < 2 || empty($email_arr[0]) || empty($email_arr[1]) || !is_string($email_arr[0]) || !is_string($email_arr[1])) {
                    $log->system_debug("[SKIP] Invalid email format or uninitialized string offset: $toemail (ID: $email_id)");
                    $batch_summary['skipped']++;
                    continue;
                }
                $email_acc = $email_arr[0];
                $email_dom = $email_arr[1];

                // Log batch assignment for this email
                $batch_log_lines[] = "[BATCH ASSIGN] Email assigned: $toemail (ID: $email_id) | CSV: $filename | User: $user_id | Worker: $worker_id | worker_status: " . ($row['worker_status'] ?? 'N/A');

                $status_from_verify = null;
                $verify = new VerifyEmail($toemail, $db, $user_id);

                // Warmup Check
                if (!$warmupMgr->canScan($toemail)) {
                    $log->system_debug("[WARMUP LIMIT] Daily limit reached for $toemail. Skipping.");
                    // Reset status to allow re-queueing later (or keep as pending)
                    $db->update("UPDATE evp_email_list SET processing_worker_id = NULL, worker_status = 'pending', processing_assigned_at = NULL WHERE id = $email_id");
                    $batch_summary['skipped']++;
                    continue;
                }

                $status_from_verify = $verify->status;

                // Log verification result for this email
                $batch_log_lines[] = "[VERIFY RESULT] $toemail (ID: $email_id) | CSV: $filename | User: $user_id | Worker: $worker_id | worker_status: " . ($row['worker_status'] ?? 'N/A') . " | Status: " . ($status_from_verify['status'] ?? 'N/A');

                if (!empty($status_from_verify['status'])) {
                    $email_status_db = evp_escape_nullable($db->link, $status_from_verify['status'] ?? '');
                    $email_type_db = evp_escape_nullable($db->link, $status_from_verify['type'] ?? '');
                    $safe_to_send_db = evp_escape_nullable($db->link, $status_from_verify['safe_to_send'] ?? '');
                    $verification_response_db = evp_escape_nullable($db->link, $status_from_verify['v_response'] ?? '');
                    $domain_dns_db = evp_escape_nullable($db->link, $status_from_verify['domain_dns'] ?? '');
                    $mx_server_db = evp_escape_nullable($db->link, $status_from_verify['mx_server'] ?? '');
                    $mx_response_db = evp_escape_nullable($db->link, $status_from_verify['mx_response'] ?? '');
                    $score_db = evp_escape_nullable($db->link, isset($status_from_verify['email_score']) ? $status_from_verify['email_score'] : '');
                    $bounce_type_db = evp_escape_nullable($db->link, $status_from_verify['bounce_type'] ?? '');
                    $email_acc_esc_db = evp_escape_nullable($db->link, $email_acc);
                    $email_dom_esc_db = evp_escape_nullable($db->link, $email_dom);
                    $toemail_esc_db = evp_escape_nullable($db->link, $toemail);
                    $filename_esc_db = evp_escape_nullable($db->link, $filename);
                    $user_id_esc_db = evp_escape_nullable($db->link, $user_id);

                    $update_query = "UPDATE evp_email_list SET
                        email_status = '$email_status_db',
                        email_type = '$email_type_db',
                        safe_to_send = '$safe_to_send_db',
                        verification_response = '$verification_response_db',
                        domain_dns = '$domain_dns_db',
                        mx_server = '$mx_server_db',
                        mx_response = '$mx_response_db',
                        score = '$score_db',
                        bounce_type = '$bounce_type_db',
                        email_acc = '$email_acc_esc_db',
                        email_dom = '$email_dom_esc_db',

                        last_update = NOW(),
                        processing_worker_id = NULL,
                        worker_status = 'done',
                        processing_assigned_at = NULL
                    WHERE user_id = '$user_id_esc_db' AND csv_file_name = '$filename_esc_db' AND email_name = '$toemail_esc_db' AND processing_worker_id = $worker_id";
                    $update_queries[] = $update_query;

                    if (isset($status_from_verify['status'])) {
                        $warmupMgr->incrementCount($toemail);
                    }
                } else {
                    $log->system_debug("[SKIP] Empty status from verification for $toemail (ID: $email_id)");
                    $db->update("UPDATE evp_email_list SET processing_worker_id = NULL, processing_assigned_at = NULL WHERE id = $email_id AND processing_worker_id = $worker_id");
                    $batch_summary['skipped']++;
                }
            }
            if ($email_st_current_batch)
                $email_st_current_batch->free();

            // After processing the batch, log all lines as a single batch log, including counts
            if (!empty($batch_log_lines)) {
                $pid = getmypid();
                $sent_count = $count_emails_in_current_batch;
                $verified_count = $batch_summary['updated'];
                $log->system_debug("\n==================== [BATCH LOG FOR Worker $worker_id | PID $pid] ====================");
                $log->system_debug("[BATCH SUMMARY] Emails sent for verification: $sent_count | Emails with verify result: $verified_count");
                foreach ($batch_log_lines as $line) {
                    $log->system_debug($line);
                }
                $log->system_debug("================== [END BATCH LOG FOR Worker $worker_id | PID $pid] ==================\n");
            }

            if (!empty($update_queries)) {
                $db->link->begin_transaction();
                $log->system_debug('[TRANSACTION] BEGIN');
                try {
                    $transaction_successful_updates = 0;

                    // Log all update queries before execution
                    foreach ($update_queries as $uq_log) {
                        $log->system_debug('[BATCH UPDATE QUERY] ' . $uq_log);
                    }

                    foreach ($update_queries as $query) {
                        $email_name_extracted = null;
                        $processing_worker_id_extracted = null;
                        if (preg_match("/email_name = '([^']+)'/", $query, $matches_email)) {
                            $email_name_extracted = addslashes($matches_email[1]);
                        }
                        if (preg_match("/processing_worker_id = ([0-9]+)/", $query, $matches_worker_id)) {
                            $processing_worker_id_extracted = $matches_worker_id[1];
                        }
                        // Log the current state before update (always for this email)
                        if ($email_name_extracted !== null && $processing_worker_id_extracted !== null) {
                            $pre_update_check = $db->select("SELECT email_status, worker_status, processing_worker_id FROM evp_email_list WHERE user_id = '$user_id' AND csv_file_name = '$filename' AND email_name = '$email_name_extracted' AND processing_worker_id = $processing_worker_id_extracted LIMIT 1");
                            if ($pre_update_check) {
                                $pre_update_row = $pre_update_check->fetch_assoc();
                            } else {
                                $log->system_debug('[SQL ERROR] Pre-update query failed: ' . $db->link->error);
                                $pre_update_row = null;
                            }
                            $log->system_debug('[PRE-UPDATE STATE] For query: ' . $query . ' | email_status: ' . ($pre_update_row['email_status'] ?? 'N/A') . ' | worker_status: ' . ($pre_update_row['worker_status'] ?? 'N/A') . ' | processing_worker_id: ' . ($pre_update_row['processing_worker_id'] ?? 'N/A'));
                        } else {
                            $log->system_debug('[PRE-UPDATE STATE] Could not extract email_name or processing_worker_id for query: ' . $query);
                        }
                        $log->system_debug('[BATCH UPDATE EXEC] Executing: ' . $query);
                        $db->update($query);
                        // Immediately check the row after update, before commit (always for this email)
                        if ($email_name_extracted !== null) {
                            $immediate_post_update_check = $db->select("SELECT email_status, worker_status, processing_worker_id FROM evp_email_list WHERE user_id = '$user_id' AND csv_file_name = '$filename' AND email_name = '$email_name_extracted' LIMIT 1");
                            if ($immediate_post_update_check) {
                                $immediate_post_update_row = $immediate_post_update_check->fetch_assoc();
                            } else {
                                $log->system_debug('[SQL ERROR] Immediate post-update query failed: ' . $db->link->error);
                                $immediate_post_update_row = null;
                            }
                            $log->system_debug('[IMMEDIATE POST-UPDATE STATE] For email: ' . $email_name_extracted . ' | email_status: ' . ($immediate_post_update_row['email_status'] ?? 'N/A') . ' | worker_status: ' . ($immediate_post_update_row['worker_status'] ?? 'N/A') . ' | processing_worker_id: ' . ($immediate_post_update_row['processing_worker_id'] ?? 'N/A'));
                        }
                        if ($db->link->affected_rows > 0) {
                            $transaction_successful_updates++;
                            $log->system_debug('[BATCH UPDATE SUCCESS] Query succeeded: ' . $query);
                        } else {
                            $log->system_debug('[BATCH UPDATE NO EFFECT] No rows affected for: ' . $query);
                        }
                        if ($db->link->error) {
                            $log->system_debug('[BATCH UPDATE SQL ERROR] ' . $db->link->error . ' Query: ' . $query);
                            throw new Exception('SQL error during batch update: ' . $db->link->error);
                        }
                    }
                    // After commit, re-select and log the updated state for each query
                    $meta_update_query = "UPDATE evp_email_list_metadata m JOIN (SELECT csv_file_name, user_id, SUM(email_status = 'Valid') AS valid, SUM(email_status = 'Invalid') AS invalid, SUM(email_status = 'Catch All') AS catch_all, SUM(email_status = 'Unknown') AS unknown, SUM(email_status = 'Skipped') AS skipped, SUM(email_type = 'Disposable Account') AS disposable, SUM(email_type = 'Free Account') AS free_account, SUM(email_type = 'Role Account') AS role_account FROM evp_email_list WHERE csv_file_name = '$filename' AND user_id = '$user_id' GROUP BY csv_file_name, user_id) e ON m.csv_file_name = e.csv_file_name AND m.user_id = e.user_id SET m.valid = e.valid, m.invalid = e.invalid, m.catch_all = e.catch_all, m.unknown = e.unknown, m.skipped = e.skipped, m.disposable = e.disposable, m.free_account = e.free_account, m.role_account = e.role_account";
                    $db->update($meta_update_query);
                    $db->link->commit();
                    if ($db->link->errno) {
                        $log->system_debug('[COMMIT ERROR] ' . $db->link->error);
                    }
                    $log->system_debug('[TRANSACTION] COMMIT');
                    // Log post-commit state for each updated email
                    foreach ($update_queries as $query) {
                        $email_name_extracted = null;
                        if (preg_match("/email_name = '([^']+)'/", $query, $matches_email_post)) {
                            $email_name_extracted = addslashes($matches_email_post[1]);
                        }
                        $post_update_check = $db->select("SELECT email_status, worker_status, processing_worker_id FROM evp_email_list WHERE user_id = '$user_id' AND csv_file_name = '$filename' AND email_name = '$email_name_extracted' LIMIT 1");
                        if ($post_update_check) {
                            $post_update_row = $post_update_check->fetch_assoc();
                        } else {
                            $log->system_debug('[SQL ERROR] Post-commit query failed: ' . $db->link->error);
                            $post_update_row = null;
                        }
                        $log->system_debug('[POST-COMMIT STATE] For email: ' . $email_name_extracted . ' | email_status: ' . ($post_update_row['email_status'] ?? 'N/A') . ' | worker_status: ' . ($post_update_row['worker_status'] ?? 'N/A') . ' | processing_worker_id: ' . ($post_update_row['processing_worker_id'] ?? 'N/A'));
                    }
                    $batch_summary['updated'] += $transaction_successful_updates;
                } catch (Exception $e) {
                    $db->link->rollback();
                    $log->system_debug('[TRANSACTION] ROLLBACK');
                    $log->system_debug('Batch email_list update failed: ' . $e->getMessage() . '. Batch rolled back.');
                    $log->system_debug('Rollback occurred. All attempted queries: ' . print_r($update_queries, true));
                }
            }
        }
    }
}

function fetch_evp_options($db)
{
    global $log;
    $log->system_debug('Fetching EVP options');
    $evp_options_sql = "SELECT option_name, option_value FROM evp_options";
    $evp_options_read = $db->select($evp_options_sql);
    $options = [];
    if ($evp_options_read) {
        while ($row = $evp_options_read->fetch_assoc()) {
            $options[$row['option_name']] = $row['option_value'];
        }
        $evp_options_read->free();

        $multi_scan = $options['multi_scan'];
        $site_logo = $options['site_logo'];
        $site_title = $options['site_title'];
        $smtp_debug = $options['smtp_debug'];
        $system_email = $options['system_email'];
        $system_debug = $options['system_debug'];
        $scan_debug = $options['scan_debug'];
        $a_url = $options['app_url'];
        $s_url = $options['site_url'];
        $sys_smtp_data = $options['sys_smtp'];
        $ic_send_mail = $options['ic_send_mail'];
        $ic_smtp_data = $options['ic_smtp'];
        $batch_limit = $options['batch_limit'];
    }

    if (!empty($sys_smtp_data)) {
        $sys_smtp_data = @unserialize($options['sys_smtp']);
        if (is_array($sys_smtp_data) && !empty($sys_smtp_data['sys_smtp']) && $sys_smtp_data['sys_smtp'] !== 'false') {
            foreach ($sys_smtp_data as $key => $smtp_data) {
                if (is_int($key) && is_array($smtp_data)) {
                    if (isset($smtp_data['smtp_host']))
                        $options['smtp_host'] = $smtp_data['smtp_host'];
                    if (isset($smtp_data['smtp_port']))
                        $options['smtp_port'] = $smtp_data['smtp_port'];
                    if (isset($smtp_data['smtp_security']))
                        $options['smtp_security'] = $smtp_data['smtp_security'];
                    if (isset($smtp_data['smtp_auth']))
                        $options['smtp_auth'] = $smtp_data['smtp_auth'];
                    if (isset($smtp_data['smtp_user']))
                        $options['smtp_user'] = $smtp_data['smtp_user'];
                    if (isset($smtp_data['smtp_pass']))
                        $options['smtp_pass'] = $smtp_data['smtp_pass'];
                    if (isset($smtp_data['smtp_ssl_verify']))
                        $options['smtp_ssl_verify'] = $smtp_data['smtp_ssl_verify'];
                }
            }
        }
    }

    if (!empty($ic_smtp_data)) {
        $ic_smtp_data = @unserialize($options['ic_smtp']);
        if (is_array($ic_smtp_data) && array_key_exists('ic_smtp', $ic_smtp_data) && $ic_smtp_data['ic_smtp'] !== 'false') {
            foreach ($ic_smtp_data as $key => $smtp_data) {
                if (is_int($key) && is_array($smtp_data)) {
                    if (isset($smtp_data['imap_host']))
                        $options['ic_imap_host'] = $smtp_data['imap_host'];
                    if (isset($smtp_data['imap_port']))
                        $options['ic_imap_port'] = $smtp_data['imap_port'];
                    if (isset($smtp_data['smtp_host']))
                        $options['ic_smtp_host'] = $smtp_data['smtp_host'];
                    if (isset($smtp_data['smtp_port']))
                        $options['ic_smtp_port'] = $smtp_data['smtp_port'];
                    if (isset($smtp_data['smtp_security']))
                        $options['ic_smtp_security'] = $smtp_data['smtp_security'];
                    if (isset($smtp_data['smtp_auth']))
                        $options['ic_smtp_auth'] = $smtp_data['smtp_auth'];
                    if (isset($smtp_data['smtp_user']))
                        $options['ic_smtp_user'] = $smtp_data['smtp_user'];
                    if (isset($smtp_data['smtp_pass']))
                        $options['ic_smtp_pass'] = $smtp_data['smtp_pass'];
                    if (isset($smtp_data['smtp_ssl_verify']))
                        $options['ic_smtp_ssl_verify'] = $smtp_data['smtp_ssl_verify'];
                }
            }
        }
    }
    return $options;
}

$log->system_debug('v_email.php FINISHED: ' . date('Y-m-d H:i:s'));
?>