<?php
if (!defined('ABSPATH')) {
    exit;
}

class GW_Analytics_Dashboard
{

    const ORIGIN_FIELD_KEY = 'origen';
    const FILTER_EMPTY_ORIGINS = false;
    const FALLBACK_ORIGIN_NAME = 'Organico';
    const SPAM_OVERRIDE_KEY = 'efa_spam_override';
    const CRM_STATUS_KEY = 'efa_crm_status';
    const CRM_NOTES_KEY = 'efa_crm_notes';
    const READ_STATUS_KEY = 'efa_read_status';
    const REPLIED_STATUS_KEY = 'efa_replied_status';
    const REPLIED_MESSAGE_KEY = 'efa_replied_message';
    const REPLIED_SUBJECT_KEY = 'efa_replied_subject';
    const REPLIES_KEY = 'efa_replies'; // JSON array de todas las respuestas

    private static $instance = null;

    /**
     * Normaliza el nombre de un origen eliminando terminaciones de dominio y unificando mayúsculas/minúsculas
     * 
     * @param string $origin Nombre del origen a normalizar
     * @return string Nombre normalizado
     */
    public static function normalize_origin_name($origin)
    {
        if (empty($origin)) {
            return self::FALLBACK_ORIGIN_NAME;
        }
        
        $trimmed = trim($origin);
        if ($trimmed === '') {
            return self::FALLBACK_ORIGIN_NAME;
        }
        
        // Convertir a minúsculas para procesamiento
        $normalized = strtolower($trimmed);
        
        // Eliminar terminaciones de dominio comunes (.com, .es, .net, .org, .io, .co, etc.)
        $domain_patterns = [
            '/\.com$/i', '/\.es$/i', '/\.net$/i', '/\.org$/i', '/\.io$/i', '/\.co$/i',
            '/\.eu$/i', '/\.uk$/i', '/\.fr$/i', '/\.de$/i', '/\.it$/i', '/\.pt$/i',
            '/\.mx$/i', '/\.ar$/i', '/\.cl$/i', '/\.br$/i', '/\.us$/i', '/\.ca$/i'
        ];
        
        foreach ($domain_patterns as $pattern) {
            $normalized = preg_replace($pattern, '', $normalized);
        }
        
        // Capitalizar primera letra
        $normalized = ucfirst($normalized);
        
        return $normalized;
    }

    public static function get_instance()
    {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    private function __construct()
    {
        add_action('admin_enqueue_scripts', [$this, 'enqueue_assets']);
        // TODOS LOS AJAX CENTRALIZADOS AQUÍ
        add_action('wp_ajax_efa_get_data', [$this, 'ajax_handle_data']);
        add_action('wp_ajax_efa_get_submission_details', [$this, 'ajax_get_submission_details']);
        add_action('wp_ajax_efa_toggle_spam_status', [$this, 'ajax_toggle_spam_status']);
        add_action('wp_ajax_efa_delete_submission', [$this, 'ajax_delete_submission']);
        add_action('wp_ajax_efa_delete_bulk_spam', [$this, 'ajax_delete_bulk_spam']);

        // CRM AJAX
        add_action('wp_ajax_efa_update_crm_status', [$this, 'ajax_update_crm_status']);
        add_action('wp_ajax_efa_update_crm_status', [$this, 'ajax_update_crm_status']);
        add_action('wp_ajax_efa_add_crm_note', [$this, 'ajax_add_crm_note']);
        add_action('wp_ajax_efa_mark_as_read', [$this, 'ajax_mark_as_read']);

        // SPAM BLACKLIST AJAX
        add_action('wp_ajax_efa_blacklist_email', [$this, 'ajax_blacklist_email']);
        add_action('wp_ajax_efa_delete_all_from_sender', [$this, 'ajax_delete_all_from_sender']);

        // EMAIL REPLY AJAX
        add_action('wp_ajax_efa_send_reply_email', [$this, 'ajax_send_reply_email']);
    }

    public function enqueue_assets($hook)
    {
        if ('toplevel_page_gw-elementor' !== $hook)
            return;

        $current_tab = isset($_GET['tab']) ? $_GET['tab'] : 'dashboard';
        if (!in_array($current_tab, ['dashboard', 'emails', 'settings', 'tutorial', 'license', 'reports', 'forms']))
            return;

        wp_enqueue_script('chart-js', 'https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.min.js', [], '4.4.0', true);
        wp_enqueue_script('chart-js-datalabels', 'https://cdn.jsdelivr.net/npm/chartjs-plugin-datalabels@2.2.0/dist/chartjs-plugin-datalabels.min.js', ['chart-js'], '2.2.0', true);

        // SweetAlert2 for advanced spam handling
        wp_enqueue_style('sweetalert2-css', 'https://cdn.jsdelivr.net/npm/sweetalert2@11/dist/sweetalert2.min.css', [], '11.0.0');
        wp_enqueue_script('sweetalert2-js', 'https://cdn.jsdelivr.net/npm/sweetalert2@11/dist/sweetalert2.all.min.js', [], '11.0.0', true);

        wp_enqueue_style('gw-dashboard-css', GW_PLUGIN_URL . 'assets/css/admin-dashboard.css', [], '2.2.2');
        wp_enqueue_script('gw-dashboard-js', GW_PLUGIN_URL . 'assets/js/admin-dashboard.js', ['jquery', 'chart-js'], '2.2.2-' . time(), true);

        $highlight_keywords_str = get_option('efa_highlight_keywords', 'presupuesto, cita, agendar, llamar');
        $highlight_keywords = array_map('trim', explode(',', $highlight_keywords_str));
        $highlight_keywords = array_filter($highlight_keywords);

        wp_localize_script('gw-dashboard-js', 'efa_vars', [
            'ajax_url' => admin_url('admin-ajax.php'),
            'nonce' => wp_create_nonce('efa_nonce'),
            'crm_labels' => $this->get_crm_labels(),
            'site_logo' => get_site_icon_url(512) ?: get_site_icon_url(),
            'highlight_keywords' => $highlight_keywords,
            'batch_size' => intval(get_option('efa_batch_size', 100)),
            'default_theme' => get_option('efa_default_theme', 'light'),
            // Report configuration
            'report_config' => [
                'show_kpis' => get_option('efa_report_show_kpis', '1') === '1',
                'show_timeline' => get_option('efa_report_show_timeline', '1') === '1',
                'show_forms' => get_option('efa_report_show_forms', '1') === '1',
                'show_origins' => get_option('efa_report_show_origins', '1') === '1',
                'show_crm' => get_option('efa_report_show_crm', '1') === '1',
                'show_read' => get_option('efa_report_show_read', '1') === '1',
                'show_spam' => get_option('efa_report_show_spam', '1') === '1',
                'show_logo' => get_option('efa_report_show_logo', '1') === '1',
                'blocks_order' => explode(',', get_option('efa_report_blocks_order', 'kpis,origins,timeline,spam,forms,crm,read'))
            ]
        ]);
    }

    public function render_admin_view()
    {
        global $wpdb;
        $t = $wpdb->prefix . 'e_submissions';
        $tv = $wpdb->prefix . 'e_submissions_values';

        if ($wpdb->get_var("SHOW TABLES LIKE '$t'") != $t) {
            echo '<div class="notice notice-error"><p>Error: No se encuentra la tabla de envíos de Elementor Pro.</p></div>';
            return;
        }

        $forms = $wpdb->get_col("SELECT DISTINCT form_name FROM {$t} WHERE status != 'trash'");
        $spam_sql = self::FILTER_EMPTY_ORIGINS ? " AND value != '' " : "";
        $raw_origins = $wpdb->get_col($wpdb->prepare("SELECT DISTINCT value FROM {$tv} WHERE `key` = %s $spam_sql LIMIT 200", self::ORIGIN_FIELD_KEY));

        $normalized_origins = [];
        $fallback_norm = $this->normalize_origin_name(self::FALLBACK_ORIGIN_NAME);
        $normalized_origins[$fallback_norm] = $fallback_norm;
        foreach ($raw_origins as $ro) {
            if (empty($ro))
                $ro = self::FALLBACK_ORIGIN_NAME;
            $norm = self::normalize_origin_name($ro);
            $normalized_origins[$norm] = $norm;
        }
        ksort($normalized_origins);
        $origins = array_values($normalized_origins);

        // Banner de trial eliminado - La información se muestra en la página de Licencias

        ?>
        <div class="efa-wrapper">
            <div class="efa-page-title-row">
                <h2 style="margin:0;">Resumen de leads</h2>
            </div>

            <hr class="wp-header-end">

            <div class="efa-kpi-grid">
                <div class="efa-kpi-card" style="border-top-color: #5d5dfa;">
                    <div class="efa-kpi-header">
                        <h4>Últimos 7 Días</h4>
                        <div class="efa-kpi-value" id="kpi-7d-val">-</div>
                    </div>
                    <div class="efa-kpi-list" id="kpi-7d-list"></div>
                </div>
                <div class="efa-kpi-card" style="border-top-color: #00e676;">
                    <div class="efa-kpi-header">
                        <h4>Últimos 30 Días</h4>
                        <div class="efa-kpi-value" id="kpi-30d-val">-</div>
                    </div>
                    <div class="efa-kpi-list" id="kpi-30d-list"></div>
                </div>
                <div class="efa-kpi-card" style="border-top-color: #ffeb3b;">
                    <div class="efa-kpi-header">
                        <h4>Últimos 365 Días</h4>
                        <div class="efa-kpi-value" id="kpi-365d-val">-</div>
                    </div>
                    <div class="efa-kpi-list" id="kpi-365d-list"></div>
                </div>
            </div>

            <div class="efa-history-wide">
                <div class="efa-hw-title-box">
                    <h4 class="efa-hw-title">HISTÓRICO<br>12 MESES</h4>
                </div>
                <div id="efa-hw-scroll-container" class="efa-hw-scroll-container"></div>
                <div class="efa-hw-mini-chart"><canvas id="kpi-history-chart"></canvas></div>
            </div>

            <h3 style="margin-top:30px;margin-bottom:15px;">Desglose Mensual (Último Año)</h3>
            <div id="efa-12months-grid" class="efa-year-grid"></div>

            <hr style="margin-top: 30px; margin-bottom: 30px; border: 0; border-top: 1px solid #ddd;">

            <div style="display:flex; justify-content:space-between; align-items:center; margin-bottom:15px;">
                <h3 style="margin:0;">Gráficas filtradas</h3>
                <div class="efa-filters">
                    <select id="efa-form-filter">
                        <option value="">Todos los Formularios</option>
                        <?php foreach ($forms as $f)
                            echo '<option value="' . esc_attr($f) . '">' . esc_html($f) . '</option>'; ?>
                    </select>
                    <select id="efa-date-range">
                        <option value="last_7_days">Últimos 7 días</option>
                        <option value="last_month" selected>Último mes</option>
                        <option value="all">Todo el tiempo</option>
                        <option value="custom">Personalizado</option>
                    </select>
                    <div id="efa-custom-dates" class="efa-filters efa-hidden" style="display:none;"><input type="date"
                            id="efa-start-date"><span>a</span><input type="date" id="efa-end-date"></div>
                    <button id="efa-apply-filters" class="efa-btn">Aplicar</button>
                </div>
            </div>

            <div class="efa-card full-width-card">
                <div class="efa-card-header-row">
                    <h3>Tendencia</h3>
                </div>
                <div id="efa-origin-toggles" class="efa-origin-toggles"><?php if (empty($origins)): ?><span
                            style="font-size:12px;color:var(--efa-text-muted);">No se detectaron
                            orígenes.</span><?php else:
                    foreach ($origins as $o): ?>
                            <div class="efa-toggle-btn active" data-origin="<?php echo esc_attr($o); ?>"><span class="efa-dot"></span>
                                <?php echo esc_html($o); ?></div><?php endforeach; endif; ?>
                </div>
                <div class="efa-chart-container full-width"><canvas id="efa-chart-timeline"></canvas></div>
            </div>

            <div class="efa-grid">
                <div class="efa-card efa-simple-header">
                    <h3>Formularios totales: <span id="kpi-total-view-val" style="color:var(--efa-accent);">-</span>
                    </h3>
                    <div class="efa-chart-container"><canvas id="efa-chart-forms"></canvas></div>
                </div>
                <div class="efa-card efa-simple-header">
                    <h3>DESGLOSE ORÍGENES</h3>
                    <div class="efa-chart-container"><canvas id="efa-chart-origins"></canvas></div>
                </div>
                <div class="efa-card efa-simple-header">
                    <h3>ESTADO</h3>
                    <div class="efa-chart-container"><canvas id="efa-chart-status"></canvas></div>
                </div>
                <div class="efa-card efa-simple-header">
                    <h3>VISTO</h3>
                    <div class="efa-chart-container"><canvas id="efa-chart-read"></canvas></div>
                </div>
                <div class="efa-card efa-simple-header">
                    <h3>SPAM</h3>
                    <div class="efa-chart-container"><canvas id="efa-chart-spam"></canvas></div>
                </div>
                <div class="efa-card efa-simple-header">
                    <h3>PROMEDIO DIARIO</h3>
                    <div class="efa-kpi-widget-content">
                        <div id="efa-avg-daily" class="efa-kpi-big-number">-</div>
                        <div class="efa-kpi-label">Correos / Día</div>
                        <div class="efa-kpi-sub">En el periodo seleccionado</div>
                    </div>
                </div>
            </div>
        </div>
        <?php
    }

    private function get_trial_notice_html()
    {
        // Ahora delegamos en la clase de licencia que obtiene info desde la API
        $license_instance = GW_Elementor_Plugin::get_instance()->license_instance;
        
        // Capturar el output del banner
        ob_start();
        $license_instance->render_trial_notice();
        return ob_get_clean();
    }

    public function ajax_toggle_spam_status()
    {
        check_ajax_referer('efa_nonce', 'nonce');
        if (!current_user_can(get_option('efa_access_role', 'manage_options')))
            wp_send_json_error('Sin permisos');

        global $wpdb;
        $tv = $wpdb->prefix . 'e_submissions_values';

        $submission_id = intval($_POST['id']);
        $status = sanitize_text_field($_POST['status']);
        $value_to_store = ($status === 'spam') ? '1' : '0';

        $exists = $wpdb->get_var($wpdb->prepare("SELECT id FROM $tv WHERE submission_id = %d AND `key` = %s", $submission_id, self::SPAM_OVERRIDE_KEY));
        if ($exists) {
            $wpdb->update($tv, ['value' => $value_to_store], ['id' => $exists]);
        } else {
            $wpdb->insert($tv, ['submission_id' => $submission_id, 'key' => self::SPAM_OVERRIDE_KEY, 'value' => $value_to_store]);
        }

        // Removed automatic blacklisting - now handled explicitly via efa_blacklist_email endpoint
        wp_send_json_success();
    }

    public function ajax_delete_submission()
    {
        check_ajax_referer('efa_nonce', 'nonce');
        if (!current_user_can(get_option('efa_access_role', 'manage_options')))
            wp_send_json_error('Sin permisos');
        $id = intval($_POST['id']);
        global $wpdb;
        $table = $wpdb->prefix . 'e_submissions';

        // Check current status
        $current_status = $wpdb->get_var($wpdb->prepare("SELECT status FROM $table WHERE id = %d", $id));

        if ($current_status === 'trash') {
            // Delete permanently
            $tv = $wpdb->prefix . 'e_submissions_values';
            $wpdb->delete($tv, ['submission_id' => $id]);
            $result = $wpdb->delete($table, ['id' => $id]);
        } else {
            // Move to trash
            $result = $wpdb->update($table, ['status' => 'trash'], ['id' => $id]);
        }

        if ($result !== false)
            wp_send_json_success();
        else
            wp_send_json_error('Error DB');
    }

    public function ajax_delete_bulk_spam()
    {
        check_ajax_referer('efa_nonce', 'nonce');
        if (!current_user_can(get_option('efa_access_role', 'manage_options')))
            wp_send_json_error('Sin permisos');

        global $wpdb;
        $ts = $wpdb->prefix . 'e_submissions';
        $tv = $wpdb->prefix . 'e_submissions_values';
        $k = self::ORIGIN_FIELD_KEY;
        $spam_filter = self::FILTER_EMPTY_ORIGINS ? " AND v.value IS NOT NULL AND v.value != '' " : "";

        $range = sanitize_text_field($_POST['range_type']);
        $start_date = null;
        if ($range === 'custom' && !empty($_POST['start_date']) && !empty($_POST['end_date'])) {
            $start_date = new DateTime($_POST['start_date']);
            $end_date = new DateTime($_POST['end_date']);
        } elseif ($range === 'last_7_days') {
            $start_date = new DateTime('-6 days');
            $end_date = new DateTime();
        } elseif ($range === 'last_month') {
            $start_date = new DateTime('-1 month');
            $end_date = new DateTime();
        } elseif ($range === 'last_year') {
            $start_date = new DateTime('-1 year');
            $end_date = new DateTime();
        } else {
            $min_date_db = $wpdb->get_var("SELECT MIN(created_at) FROM $ts WHERE status != 'trash'");
            $start_date = $min_date_db ? new DateTime($min_date_db) : new DateTime('-30 days');
            $end_date = new DateTime();
        }

        $origin_sql = "";
        $params_main = [$k];
        if (isset($_POST['origins']) && is_array($_POST['origins'])) {
            $requested_origins_raw = array_map('sanitize_text_field', $_POST['origins']);
            if (!empty($requested_origins_raw)) {
                $include_empty = false;
                $clean_list = [];
                foreach ($requested_origins_raw as $req_org) {
                    if (strcasecmp($req_org, self::FALLBACK_ORIGIN_NAME) === 0) {
                        $include_empty = true;
                        $clean_list[] = $req_org;
                    } else {
                        $clean_list[] = $req_org;
                    }
                }
                $placeholders = implode(',', array_fill(0, count($clean_list), '%s'));
                if ($include_empty) {
                    $origin_sql = " AND (v.value IN ($placeholders) OR v.value IS NULL OR v.value = '') ";
                } else {
                    $origin_sql = " AND v.value IN ($placeholders) ";
                }
                $params_main = array_merge($params_main, $clean_list);
            }
        } elseif (isset($_POST['is_filtering']) && $_POST['is_filtering'] == '1') {
            $origin_sql = " AND 1=0 ";
        }

        $sql_base = "SELECT s.id, s.created_at FROM $ts s LEFT JOIN $tv v ON (s.id = v.submission_id AND v.key = %s) WHERE s.status != 'trash' $spam_filter $origin_sql";

        if (!empty($_POST['form_name'])) {
            $forms_input = $_POST['form_name'];
            if (is_array($forms_input)) {
                // Array of forms (checkboxes)
                $clean_forms = array_map('sanitize_text_field', $forms_input);
                if (!empty($clean_forms)) {
                    $placeholders = implode(',', array_fill(0, count($clean_forms), '%s'));
                    $sql_base .= " AND s.form_name IN ($placeholders)";
                    $params_main = array_merge($params_main, $clean_forms);
                }
            } else {
                // Single value (legacy or dashboard select)
                $sql_base .= " AND s.form_name = %s";
                $params_main[] = sanitize_text_field($forms_input);
            }
        }
        $sql_base .= " AND s.created_at >= %s AND s.created_at <= %s";
        $params_main[] = $start_date->format('Y-m-d 00:00:00');
        $params_main[] = $end_date->format('Y-m-d 23:59:59');

        $results = $wpdb->get_results($wpdb->prepare($sql_base, $params_main));
        $ids = array_column($results, 'id');

        if (empty($ids))
            wp_send_json_success(['deleted' => 0]);

        $ids_to_trash = [];
        $chunks = array_chunk($ids, 200);
        foreach ($chunks as $chunk_ids) {
            $ids_placeholder = implode(',', array_fill(0, count($chunk_ids), '%d'));
            $extra_values = $wpdb->get_results($wpdb->prepare("SELECT submission_id, `key`, `value` FROM $tv WHERE submission_id IN ($ids_placeholder)", $chunk_ids));

            $details = [];
            foreach ($extra_values as $ev) {
                $sid = $ev->submission_id;
                $key = strtolower($ev->key);
                if (strpos($key, 'name') !== false || strpos($key, 'nombre') !== false)
                    $details[$sid]['name'] = $ev->value;
                if (strpos($key, 'email') !== false || strpos($key, 'correo') !== false)
                    $details[$sid]['email'] = $ev->value;
                if ($ev->key === self::SPAM_OVERRIDE_KEY)
                    $details[$sid]['spam_override'] = $ev->value;
                $details[$sid]['full_text'] = (isset($details[$sid]['full_text']) ? $details[$sid]['full_text'] : '') . ' ' . $ev->value;
            }

            foreach ($chunk_ids as $sid) {
                $full_text = isset($details[$sid]['full_text']) ? $details[$sid]['full_text'] : '';
                $name = isset($details[$sid]['name']) ? $details[$sid]['name'] : '';
                $email = isset($details[$sid]['email']) ? $details[$sid]['email'] : '';

                if ($this->is_hard_banned($email)) {
                    $risk_score = 2;
                } elseif (isset($details[$sid]['spam_override'])) {
                    $risk_score = ($details[$sid]['spam_override'] == '1') ? 2 : 0;
                } else {
                    $risk_score = $this->detect_spam_risk($full_text, $name, $email);
                }

                if ($risk_score === 2) {
                    $ids_to_trash[] = $sid;
                }
            }
        }

        if (!empty($ids_to_trash)) {
            $ids_to_trash_placeholder = implode(',', array_fill(0, count($ids_to_trash), '%d'));
            $wpdb->query($wpdb->prepare("UPDATE $ts SET status = 'trash' WHERE id IN ($ids_to_trash_placeholder)", $ids_to_trash));
        }

        wp_send_json_success(['deleted' => count($ids_to_trash)]);
    }

    public function ajax_handle_data()
    {
        check_ajax_referer('efa_nonce', 'nonce');
        if (!current_user_can(get_option('efa_access_role', 'manage_options')))
            wp_send_json_error('Sin permisos');

        global $wpdb;
        $ts = $wpdb->prefix . 'e_submissions';
        $tv = $wpdb->prefix . 'e_submissions_values';
        $k = self::ORIGIN_FIELD_KEY;
        $spam_filter = self::FILTER_EMPTY_ORIGINS ? " AND v.value IS NOT NULL AND v.value != '' " : "";

        $range = sanitize_text_field($_POST['range_type']);

        // --- NUEVO: Filtro de Estado (Papelera vs Activos) ---
        $status_req = isset($_POST['status_filter']) ? sanitize_text_field($_POST['status_filter']) : 'active';
        $status_sql = ($status_req === 'trash') ? "s.status = 'trash'" : "s.status != 'trash'";

        // Recuperar filtro de riesgo (Spam/Safe) - Mapeamos a $status_filter para compatibilidad con el bucle de abajo
        $status_filter = isset($_POST['risk_filter']) ? sanitize_text_field($_POST['risk_filter']) : 'all';
        
        // Recuperar filtro de replied
        $replied_filter = isset($_POST['replied_filter']) ? sanitize_text_field($_POST['replied_filter']) : 'all';
        if ($replied_filter === 'replied') {
            $status_filter = 'replied';
        }
        
        // Recuperar filtro de unread
        $unread_filter = isset($_POST['unread_filter']) ? sanitize_text_field($_POST['unread_filter']) : 'all';
        if ($unread_filter === 'unread') {
            $status_filter = 'unread';
        }
        // -----------------------------------------------------

        $start_date = null;
        $commercial_filter = isset($_POST['commercial_filter']) ? sanitize_text_field($_POST['commercial_filter']) : 'all';

        $start_date = null;
        $end_date = new DateTime();
        if ($range === 'custom' && !empty($_POST['start_date']) && !empty($_POST['end_date'])) {
            $start_date = new DateTime($_POST['start_date']);
            $end_date = new DateTime($_POST['end_date']);
        } elseif ($range === 'last_7_days') {
            $start_date = new DateTime('-6 days');
        } elseif ($range === 'last_month') {
            $start_date = new DateTime('-1 month');
        } elseif ($range === 'last_year') {
            $start_date = new DateTime('-1 year');
        } else {
            $min_date_db = $wpdb->get_var("SELECT MIN(created_at) FROM $ts WHERE status != 'trash'");
            $start_date = $min_date_db ? new DateTime($min_date_db) : new DateTime('-30 days');
        }

        $period = new DatePeriod($start_date, new DateInterval('P1D'), (clone $end_date)->modify('+1 day'));
        $master_dates = [];
        foreach ($period as $dt) {
            $master_dates[$dt->format('Y-m-d')] = 0;
        }
        $chart_labels = array_keys($master_dates);

        $origin_sql = "";
        $params_base = [$k];
        $requested_origins_normalized = [];
        if (isset($_POST['origins']) && is_array($_POST['origins'])) {
            $requested_origins_raw = array_map('sanitize_text_field', $_POST['origins']);
            if (!empty($requested_origins_raw)) {
                $include_empty = false;
                $clean_list = [];
                foreach ($requested_origins_raw as $req_org) {
                    if (strcasecmp($req_org, self::FALLBACK_ORIGIN_NAME) === 0) {
                        $include_empty = true;
                        $clean_list[] = $req_org;
                    } else {
                        $clean_list[] = $req_org;
                    }
                    $requested_origins_normalized[] = self::normalize_origin_name($req_org);
                }
                $placeholders = implode(',', array_fill(0, count($clean_list), '%s'));
                if ($include_empty) {
                    $origin_sql = " AND (v.value IN ($placeholders) OR v.value IS NULL OR v.value = '') ";
                } else {
                    $origin_sql = " AND v.value IN ($placeholders) ";
                }
                $params_base = array_merge($params_base, $clean_list);
            }
        } elseif (isset($_POST['is_filtering']) && $_POST['is_filtering'] == '1') {
            $origin_sql = " AND 1=0 ";
        }

        // History Chart
        $sql_history = "SELECT DATE_FORMAT(s.created_at, '%Y-%m') as yyyymm, DATE_FORMAT(s.created_at, '%M %Y') as nice_date, COUNT(*) as total FROM $ts s LEFT JOIN $tv v ON (s.id = v.submission_id AND v.key = %s) WHERE $status_sql $spam_filter $origin_sql AND s.created_at >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 11 MONTH), '%Y-%m-01') ";
        $history_params = $params_base;
        if (!empty($_POST['form_name'])) {
            $forms_input = $_POST['form_name'];
            if (is_array($forms_input)) {
                $clean_forms = array_map('sanitize_text_field', $forms_input);
                if (!empty($clean_forms)) {
                    $placeholders = implode(',', array_fill(0, count($clean_forms), '%s'));
                    $sql_history .= " AND s.form_name IN ($placeholders)";
                    $history_params = array_merge($history_params, $clean_forms);
                }
            } else {
                $sql_history .= " AND s.form_name = %s";
                $history_params[] = sanitize_text_field($forms_input);
            }
        }
        $sql_history .= " GROUP BY yyyymm ORDER BY yyyymm DESC";
        $history_rows = $wpdb->get_results($wpdb->prepare($sql_history, $history_params));
        $history_data = [];
        $months_es = ['January' => 'Enero', 'February' => 'Febrero', 'March' => 'Marzo', 'April' => 'Abril', 'May' => 'Mayo', 'June' => 'Junio', 'July' => 'Julio', 'August' => 'Agosto', 'September' => 'Septiembre', 'October' => 'Octubre', 'November' => 'Noviembre', 'December' => 'Diciembre'];
        foreach ($history_rows as $h) {
            $parts = explode(' ', $h->nice_date);
            $mes_es = isset($months_es[$parts[0]]) ? $months_es[$parts[0]] : $parts[0];
            $history_data[] = ['label' => $mes_es . ' ' . $parts[1], 'count' => $h->total];
        }

        // Year Grid
        $sql_year_grid = "SELECT DATE_FORMAT(s.created_at, '%Y-%m') as yyyymm, DATE_FORMAT(s.created_at, '%M') as month_name, v.value as origin, COUNT(*) as count FROM $ts s LEFT JOIN $tv v ON (s.id = v.submission_id AND v.key = %s) WHERE s.status != 'trash' $spam_filter $origin_sql AND s.created_at >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 11 MONTH), '%Y-%m-01') ";
        $yg_params = $params_base;
        if (!empty($_POST['form_name'])) {
            $forms_input = $_POST['form_name'];
            if (is_array($forms_input)) {
                $clean_forms = array_map('sanitize_text_field', $forms_input);
                if (!empty($clean_forms)) {
                    $placeholders = implode(',', array_fill(0, count($clean_forms), '%s'));
                    $sql_year_grid .= " AND s.form_name IN ($placeholders)";
                    $yg_params = array_merge($yg_params, $clean_forms);
                }
            } else {
                $sql_year_grid .= " AND s.form_name = %s";
                $yg_params[] = sanitize_text_field($forms_input);
            }
        }
        $sql_year_grid .= " GROUP BY yyyymm, v.value";
        $yg_rows = $wpdb->get_results($wpdb->prepare($sql_year_grid, $yg_params));
        $year_grid_data = [];
        for ($i = 0; $i <= 11; $i++) {
            $dt = new DateTime("first day of -$i month");
            $key = $dt->format('Y-m');
            $eng_month = $dt->format('F');
            $es_month = isset($months_es[$eng_month]) ? $months_es[$eng_month] : $eng_month;
            $year_grid_data[$key] = ['label' => $es_month . ' ' . $dt->format('Y'), 'total' => 0, 'breakdown' => []];
        }
        foreach ($yg_rows as $r) {
            if (isset($year_grid_data[$r->yyyymm])) {
                $origin_raw = $r->origin ? $r->origin : self::FALLBACK_ORIGIN_NAME;
                $origin = self::normalize_origin_name($origin_raw);
                $year_grid_data[$r->yyyymm]['total'] += $r->count;
                if (!isset($year_grid_data[$r->yyyymm]['breakdown'][$origin]))
                    $year_grid_data[$r->yyyymm]['breakdown'][$origin] = 0;
                $year_grid_data[$r->yyyymm]['breakdown'][$origin] += $r->count;
            }
        }

        // Consulta ligera
        $sql_base = "SELECT s.id, s.form_name, s.created_at, v.value as origin_val FROM $ts s LEFT JOIN $tv v ON (s.id = v.submission_id AND v.key = %s) WHERE $status_sql $spam_filter $origin_sql";
        $params_main = $params_base;
        if (!empty($_POST['form_name'])) {
            $forms_input = $_POST['form_name'];
            if (is_array($forms_input)) {
                $clean_forms = array_map('sanitize_text_field', $forms_input);
                if (!empty($clean_forms)) {
                    $placeholders = implode(',', array_fill(0, count($clean_forms), '%s'));
                    $sql_base .= " AND s.form_name IN ($placeholders)";
                    $params_main = array_merge($params_main, $clean_forms);
                }
            } else {
                $sql_base .= " AND s.form_name = %s";
                $params_main[] = sanitize_text_field($forms_input);
            }
        }
        $sql_base .= " AND s.created_at >= %s AND s.created_at <= %s ORDER BY s.created_at ASC";
        $params_main[] = $start_date->format('Y-m-d 00:00:00');
        $params_main[] = $end_date->format('Y-m-d 23:59:59');

        $all_results_light = $wpdb->get_results($wpdb->prepare($sql_base, $params_main));

        $data = [
            'kpis' => ['last_7' => ['total' => 0, 'breakdown' => []], 'last_30' => ['total' => 0, 'breakdown' => []], 'last_365' => ['total' => 0, 'breakdown' => []], 'history' => $history_data, 'year_grid' => array_values($year_grid_data), 'total_in_view' => 0, 'daily_average' => 0],
            'timeline' => ['labels' => $chart_labels, 'series' => []],
            'forms_distribution' => [],
            'origins_distribution' => [],
            'crm_distribution' => [],
            'read_distribution' => [],
            'spam_distribution' => [],
            'table_rows' => [],
            'date_range' => [
                'start' => $start_date->format('d/m/Y'),
                'end' => $end_date->format('d/m/Y'),
                'days' => $start_date->diff($end_date)->days + 1
            ]
        ];

        // --- NUEVO CÁLCULO DE KPIS (Independiente del rango de fecha seleccionado) ---
        // Usamos $params_base que ya incluye la key y los orígenes seleccionados (si los hay)
        $kpi_sql = "SELECT 
                        v.value as origin,
                        SUM(CASE WHEN s.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) as count_7,
                        SUM(CASE WHEN s.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) as count_30,
                        SUM(CASE WHEN s.created_at >= DATE_SUB(NOW(), INTERVAL 365 DAY) THEN 1 ELSE 0 END) as count_365
                    FROM $ts s 
                    LEFT JOIN $tv v ON (s.id = v.submission_id AND v.key = %s) 
                    WHERE $status_sql $spam_filter $origin_sql ";

        $kpi_params = $params_base;

        if (!empty($_POST['form_name'])) {
            $forms_input = $_POST['form_name'];
            if (is_array($forms_input)) {
                $clean_forms = array_map('sanitize_text_field', $forms_input);
                if (!empty($clean_forms)) {
                    $placeholders = implode(',', array_fill(0, count($clean_forms), '%s'));
                    $kpi_sql .= " AND s.form_name IN ($placeholders)";
                    $kpi_params = array_merge($kpi_params, $clean_forms);
                }
            } else {
                $kpi_sql .= " AND s.form_name = %s";
                $kpi_params[] = sanitize_text_field($forms_input);
            }
        }

        $kpi_sql .= " GROUP BY v.value";

        $kpi_results = $wpdb->get_results($wpdb->prepare($kpi_sql, $kpi_params));

        foreach ($kpi_results as $kpi_row) {
            $origin_raw = $kpi_row->origin ? $kpi_row->origin : self::FALLBACK_ORIGIN_NAME;
            $origin = self::normalize_origin_name($origin_raw);

            // 7 Days
            if ($kpi_row->count_7 > 0) {
                $data['kpis']['last_7']['total'] += $kpi_row->count_7;
                if (!isset($data['kpis']['last_7']['breakdown'][$origin]))
                    $data['kpis']['last_7']['breakdown'][$origin] = 0;
                $data['kpis']['last_7']['breakdown'][$origin] += $kpi_row->count_7;
            }

            // 30 Days
            if ($kpi_row->count_30 > 0) {
                $data['kpis']['last_30']['total'] += $kpi_row->count_30;
                if (!isset($data['kpis']['last_30']['breakdown'][$origin]))
                    $data['kpis']['last_30']['breakdown'][$origin] = 0;
                $data['kpis']['last_30']['breakdown'][$origin] += $kpi_row->count_30;
            }

            // 365 Days
            if ($kpi_row->count_365 > 0) {
                $data['kpis']['last_365']['total'] += $kpi_row->count_365;
                if (!isset($data['kpis']['last_365']['breakdown'][$origin]))
                    $data['kpis']['last_365']['breakdown'][$origin] = 0;
                $data['kpis']['last_365']['breakdown'][$origin] += $kpi_row->count_365;
            }
        }

        // --- NUEVO CÁLCULO DE DISTRIBUCIÓN DE ESTADOS CRM ---
        // Usamos una query dedicada para agrupar por estado
        $crm_sql = "SELECT 
                        v_crm.value as crm_status,
                        COUNT(*) as count
                    FROM $ts s 
                    LEFT JOIN $tv v ON (s.id = v.submission_id AND v.key = %s) 
                    LEFT JOIN $tv v_crm ON (s.id = v_crm.submission_id AND v_crm.key = %s)
                    WHERE $status_sql $spam_filter $origin_sql ";

        $crm_params = $params_base;
        // Añadimos la key de CRM status a los params, pero OJO: $params_base ya tiene el key de origen al principio.
        // La query usa 2 placeholders %s fijos al inicio: uno para v.key (origen) y otro para v_crm.key (estado).
        // $params_base tiene [ORIGIN_KEY, ...origenes...].
        // Necesitamos insertar CRM_STATUS_KEY en la segunda posición.

        // Reconstruimos params para esta query específica:
        $crm_query_params = [self::ORIGIN_FIELD_KEY, self::CRM_STATUS_KEY];

        // Añadimos los orígenes si existen en $params_base (saltando el primero que es la key)
        for ($i = 1; $i < count($params_base); $i++) {
            $crm_query_params[] = $params_base[$i];
        }

        if (!empty($_POST['form_name'])) {
            $forms_input = $_POST['form_name'];
            if (is_array($forms_input)) {
                $clean_forms = array_map('sanitize_text_field', $forms_input);
                if (!empty($clean_forms)) {
                    $placeholders = implode(',', array_fill(0, count($clean_forms), '%s'));
                    $crm_sql .= " AND s.form_name IN ($placeholders)";
                    $crm_query_params = array_merge($crm_query_params, $clean_forms);
                }
            } else {
                $crm_sql .= " AND s.form_name = %s";
                $crm_query_params[] = sanitize_text_field($forms_input);
            }
        }

        // Filtro de fecha (respetando el seleccionado)
        $crm_sql .= " AND s.created_at >= %s AND s.created_at <= %s";
        $crm_query_params[] = $start_date->format('Y-m-d 00:00:00');
        $crm_query_params[] = $end_date->format('Y-m-d 23:59:59');

        $crm_sql .= " GROUP BY v_crm.value";

        $crm_results = $wpdb->get_results($wpdb->prepare($crm_sql, $crm_query_params));

        // Mapeo de IDs a Etiquetas legibles (si es posible, si no se usa el ID)
        // Las etiquetas están en el frontend, pero aquí podemos intentar normalizar 'new' a 'Nuevo' si queremos, 
        // o dejar que el frontend lo haga. El usuario pidió "grafica circular con los estados".
        // Lo mejor es mandar los IDs (new, contacted, etc) y que el JS use los labels correctos.
        // Pero para simplificar el JS, podemos mandar ya las etiquetas si tenemos acceso a ellas.
        // $this->get_crm_labels() devuelve array de objetos.

        $crm_labels_map = [];
        $labels_def = $this->get_crm_labels();
        foreach ($labels_def as $ld) {
            $crm_labels_map[$ld['id']] = $ld['label'];
        }
        // Fallback para 'new' si no está definido, aunque debería.
        if (!isset($crm_labels_map['new']))
            $crm_labels_map['new'] = 'Nuevo';

        foreach ($crm_results as $cr) {
            $status_key = $cr->crm_status ? $cr->crm_status : 'new';
            $label = isset($crm_labels_map[$status_key]) ? $crm_labels_map[$status_key] : ucfirst($status_key);

            if (!isset($data['crm_distribution'][$label]))
                $data['crm_distribution'][$label] = 0;
            $data['crm_distribution'][$label] += $cr->count;
        }

        // --- CÁLCULO DE DISTRIBUCIÓN DE VISTO / NO VISTO ---
        $read_sql = "SELECT 
                        v_read.value as read_status,
                        COUNT(*) as count
                    FROM $ts s 
                    LEFT JOIN $tv v ON (s.id = v.submission_id AND v.key = %s) 
                    LEFT JOIN $tv v_read ON (s.id = v_read.submission_id AND v_read.key = %s)
                    WHERE $status_sql $spam_filter $origin_sql ";

        $read_params = $params_base;
        $read_query_params = [self::ORIGIN_FIELD_KEY, self::READ_STATUS_KEY];
        for ($i = 1; $i < count($params_base); $i++) {
            $read_query_params[] = $params_base[$i];
        }

        if (!empty($_POST['form_name'])) {
            $forms_input = $_POST['form_name'];
            if (is_array($forms_input)) {
                $clean_forms = array_map('sanitize_text_field', $forms_input);
                if (!empty($clean_forms)) {
                    $placeholders = implode(',', array_fill(0, count($clean_forms), '%s'));
                    $read_sql .= " AND s.form_name IN ($placeholders)";
                    $read_query_params = array_merge($read_query_params, $clean_forms);
                }
            } else {
                $read_sql .= " AND s.form_name = %s";
                $read_query_params[] = sanitize_text_field($forms_input);
            }
        }
        $read_sql .= " AND s.created_at >= %s AND s.created_at <= %s";
        $read_query_params[] = $start_date->format('Y-m-d 00:00:00');
        $read_query_params[] = $end_date->format('Y-m-d 23:59:59');

        $read_sql .= " GROUP BY v_read.value";
        $read_results = $wpdb->get_results($wpdb->prepare($read_sql, $read_query_params));

        $data['read_distribution'] = ['Visto' => 0, 'No Visto' => 0];
        $total_read_count = 0;
        foreach ($read_results as $rr) {
            if ($rr->read_status == '1') {
                $data['read_distribution']['Visto'] += $rr->count;
            } else {
                $data['read_distribution']['No Visto'] += $rr->count;
            }
            $total_read_count += $rr->count;
        }
        // Ajuste: Los que no tienen registro en v_read son "No Visto"
        // El total de registros en el periodo es $total_view_count (calculado antes en la query ligera, pero ojo, esa query ligera tiene limit? No, no tiene limit, pero es filtrada)
        // Mejor usamos la suma de counts de esta query vs el total real si quisiéramos ser exactos, pero
        // como hacemos LEFT JOIN, si v_read.value es NULL, aparecerá como NULL en el group by.
        // Vamos a iterar de nuevo para asegurar que NULL se suma a No Visto.
        $data['read_distribution'] = ['Visto' => 0, 'No Visto' => 0];
        foreach ($read_results as $rr) {
            if ($rr->read_status == '1') {
                $data['read_distribution']['Visto'] += $rr->count;
            } else {
                $data['read_distribution']['No Visto'] += $rr->count;
            }
        }

        // --- CÁLCULO DE DISTRIBUCIÓN DE SPAM / CORRECTO ---
        // Nota: Esto se basa en efa_spam_override. El spam automático no se guarda en DB como tal salvo que lo marquemos.
        // Asumiremos que lo que el usuario quiere ver es lo que está marcado como spam vs lo que no.
        // OJO: El filtro $spam_filter en las queries anteriores EXCLUYE el spam si FILTER_EMPTY_ORIGINS está activo? No, eso es para origenes vacios.
        // Pero espera, la query principal tiene `WHERE s.status != 'trash'`.
        // Si un correo es SPAM, ¿lo mandamos a la papelera? 
        // En `ajax_delete_bulk_spam` sí. Pero el marcado manual `efa_spam_override` = 1 NO lo borra, solo lo marca.
        // Así que podemos contar los que tienen override=1 vs los que no.

        // --- CÁLCULO DE DISTRIBUCIÓN DE SPAM / CORRECTO (PHP CALCULATION) ---
        // Replicamos la lógica exacta de la tabla para que coincidan los datos.
        $spam_count = 0;
        $safe_count = 0;

        $all_ids = array_column($all_results_light, 'id');

        if (!empty($all_ids)) {
            // Procesamos en lotes para no saturar la memoria si hay muchos registros
            $chunks = array_chunk($all_ids, 500);

            foreach ($chunks as $chunk_ids) {
                $ids_placeholder = implode(',', array_fill(0, count($chunk_ids), '%d'));
                $chunk_values = $wpdb->get_results($wpdb->prepare("SELECT submission_id, `key`, `value` FROM $tv WHERE submission_id IN ($ids_placeholder)", $chunk_ids));

                // Agrupar valores por submission_id
                $submissions_data = [];
                foreach ($chunk_values as $cv) {
                    $submissions_data[$cv->submission_id][] = $cv;
                }

                foreach ($chunk_ids as $sid) {
                    if (!isset($submissions_data[$sid]))
                        continue;

                    $vals = $submissions_data[$sid];
                    $name = '';
                    $email = '';
                    $full_text = '';
                    $spam_override = null;

                    foreach ($vals as $v) {
                        $k_lower = strtolower($v->key);
                        if (strpos($k_lower, 'name') !== false || strpos($k_lower, 'nombre') !== false)
                            $name = $v->value;
                        if (strpos($k_lower, 'email') !== false || strpos($k_lower, 'correo') !== false)
                            $email = $v->value;
                        if ($v->key === self::SPAM_OVERRIDE_KEY)
                            $spam_override = $v->value;

                        $full_text .= ' ' . $v->value;
                    }

                    $risk_score = 0;

                    // 0. Whitelist
                    if ($this->is_whitelisted($email)) {
                        $risk_score = 0;
                    }
                    // 1. Hard Ban
                    elseif ($this->is_hard_banned($email)) {
                        $risk_score = 2;
                    }
                    // 2. Override
                    elseif ($spam_override !== null) {
                        $risk_score = ($spam_override == '1') ? 2 : 0;
                    }
                    // 3. Auto Detect
                    else {
                        $risk_score = $this->detect_spam_risk($full_text, $name, $email);
                    }

                    if ($risk_score === 2) {
                        $spam_count++;
                    } else {
                        $safe_count++;
                    }
                }
            }
        }

        $data['spam_distribution'] = ['Spam' => $spam_count, 'Correcto' => $safe_count];

        $requested_origins_normalized = array_unique($requested_origins_normalized);
        foreach ($requested_origins_normalized as $ro) {
            $data['timeline']['series'][$ro] = $master_dates;
        }

        $total_view_count = count($all_results_light);
        $data['kpis']['total_in_view'] = $total_view_count;
        $all_ids_desc = [];

        foreach ($all_results_light as $row) {
            $date_str = date('Y-m-d', strtotime($row->created_at));
            $origin_raw = $row->origin_val ? $row->origin_val : self::FALLBACK_ORIGIN_NAME;
            $origin = self::normalize_origin_name($origin_raw);

            $ts_row = strtotime($row->created_at);
            // KPI Calculation moved to dedicated query above to be independent of date range filter

            if (isset($master_dates[$date_str])) {
                if (!isset($data['timeline']['series'][$origin]))
                    $data['timeline']['series'][$origin] = $master_dates;
                $data['timeline']['series'][$origin][$date_str]++;
            }
            if (!isset($data['forms_distribution'][$row->form_name]))
                $data['forms_distribution'][$row->form_name] = 0;
            $data['forms_distribution'][$row->form_name]++;
            if (!isset($data['origins_distribution'][$origin]))
                $data['origins_distribution'][$origin] = 0;
            $data['origins_distribution'][$origin]++;

            array_unshift($all_ids_desc, $row->id);
        }

        // Consulta Pesada
        $ids_for_table = array_slice($all_ids_desc, 0, 500);

        if (!empty($ids_for_table)) {
            $ids_placeholder = implode(',', array_fill(0, count($ids_for_table), '%d'));
            $extra_values = $wpdb->get_results($wpdb->prepare("SELECT submission_id, `key`, `value` FROM $tv WHERE submission_id IN ($ids_placeholder)", $ids_for_table));

            $details = [];
            foreach ($extra_values as $ev) {
                $k_lower = strtolower($ev->key);
                $sid = $ev->submission_id;

                if (strpos($k_lower, 'name') !== false || strpos($k_lower, 'nombre') !== false)
                    $details[$sid]['name'] = $ev->value;
                if (strpos($k_lower, 'email') !== false || strpos($k_lower, 'correo') !== false)
                    $details[$sid]['email'] = $ev->value;
                if (strpos($k_lower, 'tel') !== false || strpos($k_lower, 'phone') !== false || strpos($k_lower, 'movil') !== false)
                    $details[$sid]['phone'] = $ev->value;

                if ($ev->key === 'landing_url')
                    $details[$sid]['landing'] = $ev->value;
                if ($ev->key === 'prev_url')
                    $details[$sid]['prev'] = $ev->value;
                if ($ev->key === 'current_url')
                    $details[$sid]['lead_page'] = $ev->value;
                if (strpos($k_lower, 'comercial') !== false || strpos($k_lower, 'aceptacion') !== false)
                    $details[$sid]['commercial'] = $ev->value;

                if ($ev->key === self::SPAM_OVERRIDE_KEY)
                    $details[$sid]['spam_override'] = $ev->value;
                if ($ev->key === self::CRM_STATUS_KEY)
                    $details[$sid]['crm_status'] = $ev->value;
                if ($ev->key === self::CRM_NOTES_KEY)
                    $details[$sid]['crm_notes_count'] = count(json_decode($ev->value, true) ?: []);
                if ($ev->key === self::READ_STATUS_KEY)
                    $details[$sid]['read_status'] = $ev->value;
                if ($ev->key === self::REPLIED_STATUS_KEY)
                    $details[$sid]['replied_status'] = $ev->value;
                if ($ev->key === self::REPLIES_KEY) {
                    $decoded = json_decode($ev->value, true);
                    if (is_array($decoded)) {
                        $details[$sid]['replies_count'] = count($decoded);
                    }
                }

                $details[$sid]['full_text'] = (isset($details[$sid]['full_text']) ? $details[$sid]['full_text'] : '') . ' ' . $ev->value;
            }

            $rows_map = [];
            foreach ($all_results_light as $r) {
                $rows_map[$r->id] = $r;
            }

            $highlight_keywords_str = get_option('efa_highlight_keywords', 'presupuesto, cita, agendar, llamar');
            $highlight_keywords = array_map('trim', explode(',', $highlight_keywords_str));
            $highlight_keywords = array_filter($highlight_keywords); // Remove empty

            foreach ($ids_for_table as $sid) {
                if (!isset($rows_map[$sid]))
                    continue;
                $base_row = $rows_map[$sid];

                $full_text = isset($details[$sid]['full_text']) ? $details[$sid]['full_text'] : '';
                $name = isset($details[$sid]['name']) ? $details[$sid]['name'] : '';
                $email = isset($details[$sid]['email']) ? $details[$sid]['email'] : '';
                $is_comm = isset($details[$sid]['commercial']) && !empty($details[$sid]['commercial']);
                $crm_status = isset($details[$sid]['crm_status']) ? $details[$sid]['crm_status'] : 'new';

                // Check for Highlight Keywords
                $is_highlighted = false;
                if (!empty($highlight_keywords)) {
                    $search_text = mb_strtolower($full_text . ' ' . $name . ' ' . $email);
                    foreach ($highlight_keywords as $kw) {
                        if (mb_strpos($search_text, mb_strtolower($kw)) !== false) {
                            $is_highlighted = true;
                            break;
                        }
                    }
                }

                // 0. PRIORIDAD SUPREMA: Lista Blanca (Inmunidad)
                if ($this->is_whitelisted($email)) {
                    $risk_score = 0; // Siempre SEGURO (verde)
                }
                // 1. PRIORIDAD ALTA: Bloqueo Duro (Lista Negra / TLDs)
                elseif ($this->is_hard_banned($email)) {
                    $risk_score = 2; // Siempre SPAM (rojo)
                }
                // 2. PRIORIDAD MEDIA: Decisión Manual (Override)
                elseif (isset($details[$sid]['spam_override'])) {
                    $risk_score = ($details[$sid]['spam_override'] == '1') ? 2 : 0;
                }
                // 3. PRIORIDAD BAJA: Detección Automática
                else {
                    $risk_score = $this->detect_spam_risk($full_text, $name, $email);
                }

                if ($status_filter === 'none')
                    continue;
                if ($status_filter === 'spam' && $risk_score !== 2)
                    continue;
                if ($status_filter === 'safe' && $risk_score === 2)
                    continue;
                if ($status_filter === 'replied') {
                    $replied_status = isset($details[$sid]['replied_status']) ? $details[$sid]['replied_status'] : '';
                    if (empty($replied_status))
                        continue;
                }
                if ($status_filter === 'unread') {
                    $read_status = isset($details[$sid]['read_status']) ? $details[$sid]['read_status'] : '0';
                    if ($read_status == '1')
                        continue;
                }
                if ($commercial_filter === 'accepted' && !$is_comm)
                    continue;

                $origin_raw = $base_row->origin_val ? $base_row->origin_val : self::FALLBACK_ORIGIN_NAME;

                $data['table_rows'][] = [
                    'id' => $sid,
                    'date' => date('d/m/Y H:i', strtotime($base_row->created_at)),
                    'form' => $base_row->form_name,
                    'origin' => $this->normalize_origin_name($origin_raw),
                    'name' => $name,
                    'email' => $email,
                    'phone' => isset($details[$sid]['phone']) ? $details[$sid]['phone'] : '',
                    'landing' => isset($details[$sid]['landing']) ? $details[$sid]['landing'] : '',
                    'prev' => isset($details[$sid]['prev']) ? $details[$sid]['prev'] : '',
                    'lead_page' => isset($details[$sid]['lead_page']) ? $details[$sid]['lead_page'] : '',
                    'is_commercial' => $is_comm,
                    'risk' => $risk_score,
                    'crm_status' => $crm_status,
                    'notes_count' => isset($details[$sid]['crm_notes_count']) ? $details[$sid]['crm_notes_count'] : 0,
                    'read_status' => isset($details[$sid]['read_status']) ? $details[$sid]['read_status'] : '0',
                    'replied_status' => isset($details[$sid]['replied_status']) ? $details[$sid]['replied_status'] : '',
                    'replies_count' => isset($details[$sid]['replies_count']) ? intval($details[$sid]['replies_count']) : (isset($details[$sid]['replied_status']) && !empty($details[$sid]['replied_status']) ? 1 : 0),
                    'highlight' => $is_highlighted
                ];
            }
        }

        $interval = $start_date->diff($end_date);
        $days_count = $interval->days + 1;
        $data['kpis']['daily_average'] = ($days_count > 0) ? round($total_view_count / $days_count, 1) : $total_view_count;

        // NOTE: highlighted_unread is now calculated below in the same loop as count_starred

        // Calculate sidebar counts from ENTIRE database (unfiltered)
        // These counts should always show total database counts, not filtered results
        $count_all = intval($wpdb->get_var("SELECT COUNT(*) FROM $ts WHERE status != 'trash'"));
        $count_trash = intval($wpdb->get_var("SELECT COUNT(*) FROM $ts WHERE status = 'trash'"));

        // For starred, spam, and CRM states, we need to process ALL rows
        // Get ALL submissions (not filtered by date/form)
        // Note: status field can be empty string '' for active records, or 'trash' for deleted
        // crm_status is NOT a column - it's stored in metadata table
        $all_submissions_query = "
            SELECT s.id, s.status, s.form_name, s.created_at,
                   GROUP_CONCAT(DISTINCT CONCAT(v.key, ':', v.value) SEPARATOR '|||') AS meta_data
            FROM $ts s
            LEFT JOIN $tv v ON s.id = v.submission_id
            WHERE (s.status IS NULL OR s.status = '' OR s.status = 'active' OR s.status != 'trash')
            GROUP BY s.id
        ";

        $all_submissions = $wpdb->get_results($all_submissions_query);

        $count_starred = 0;
        $count_replied = 0;
        $count_spam = 0;
        $count_highlighted_unread = 0; // Count highlighted AND unread
        $crm_state_counts = [];

        // Initialize CRM state counts and create label-to-ID mapping
        $crm_labels = $this->get_crm_labels();
        $label_to_id = []; // Map Spanish labels to English IDs
        foreach ($crm_labels as $label) {
            $crm_state_counts[$label['id']] = 0;
            // Create both lowercase and original case mappings
            $label_to_id[mb_strtolower($label['label'])] = $label['id'];
            $label_to_id[$label['label']] = $label['id'];
        }

        // Get highlight keywords
        $highlight_keywords_str = get_option('efa_highlight_keywords', 'presupuesto, cita, agendar, llamar');
        $highlight_keywords = array_map('trim', explode(',', $highlight_keywords_str));
        $highlight_keywords = array_filter($highlight_keywords);

        foreach ($all_submissions as $submission) {
            // Parse metadata
            $details = [];
            $full_text_parts = [];
            $name = '';
            $email = '';

            if ($submission->meta_data) {
                $pairs = explode('|||', $submission->meta_data);
                foreach ($pairs as $pair) {
                    $parts = explode(':', $pair, 2);
                    if (count($parts) == 2) {
                        $key = $parts[0];
                        $value = $parts[1];
                        $key_lower = strtolower($key);

                        // Store in details array
                        $details[$key] = $value;

                        // Build full_text from all values (excluding spam_override)
                        if ($key !== self::SPAM_OVERRIDE_KEY) {
                            $full_text_parts[] = $value;
                        }

                        // Extract name field
                        if (empty($name) && (strpos($key_lower, 'name') !== false || strpos($key_lower, 'nombre') !== false)) {
                            $name = $value;
                        }

                        // Extract email field
                        if (empty($email) && (strpos($key_lower, 'email') !== false || strpos($key_lower, 'correo') !== false)) {
                            $email = $value;
                        }
                    }
                }
            }

            $full_text = implode(' ', $full_text_parts);

            // CRM status is stored in metadata
            // Check both new key (efa_crm_status) and legacy key (crm_status)
            $raw_crm_status = $details[self::CRM_STATUS_KEY] ?? $details['crm_status'] ?? 'new';

            // Normalize: try to map label to ID (handles both "Presupuesto" and "budget")
            // First check if it's already a valid ID
            if (isset($crm_state_counts[$raw_crm_status])) {
                $crm_status = $raw_crm_status;
            } elseif (isset($label_to_id[$raw_crm_status])) {
                // Map label to ID
                $crm_status = $label_to_id[$raw_crm_status];
            } else {
                // Default to 'new' if unknown
                $crm_status = 'new';
            }


            // Check highlight
            $is_highlighted = false;
            if (!empty($highlight_keywords)) {
                $search_text = mb_strtolower($full_text . ' ' . $name . ' ' . $email);
                foreach ($highlight_keywords as $kw) {
                    if (mb_strpos($search_text, mb_strtolower($kw)) !== false) {
                        $is_highlighted = true;
                        break;
                    }
                }
            }
            if ($is_highlighted) {
                $count_starred++;
                
                // Check if it's also unread
                $read_status = $details[self::READ_STATUS_KEY] ?? '0';
                if ($read_status == '0') {
                    $count_highlighted_unread++;
                }
            }

            // Check spam using same logic as main function
            if ($this->is_whitelisted($email)) {
                $risk_score = 0;
            } elseif ($this->is_hard_banned($email)) {
                $risk_score = 2;
            } elseif (isset($details['spam_override'])) {
                $risk_score = ($details['spam_override'] == '1') ? 2 : 0;
            } else {
                $risk_score = $this->detect_spam_risk($full_text, $name, $email);
            }

            if ($risk_score == 2) {
                $count_spam++;
            }

            // Count unread
            $read_status = $details[self::READ_STATUS_KEY] ?? '0';
            if ($read_status == '0') {
                $count_unread++;
            }

            // Count replied
            $replied_status = $details[self::REPLIED_STATUS_KEY] ?? '';
            if (!empty($replied_status)) {
                $count_replied++;
            }

            // Count CRM states
            if (isset($crm_state_counts[$crm_status])) {
                $crm_state_counts[$crm_status]++;
            }
        }

        // Calculate form counts (unfiltered)
        $form_counts_query = "SELECT form_name, COUNT(*) as count FROM $ts WHERE status != 'trash' GROUP BY form_name";
        $form_counts_results = $wpdb->get_results($form_counts_query);
        $form_counts = [];
        foreach ($form_counts_results as $row) {
            if (!empty($row->form_name)) {
                $form_counts[$row->form_name] = intval($row->count);
            }
        }

        $data['sidebar_counts'] = [
            'all' => $count_all,
            'unread' => $count_unread,
            'starred' => $count_starred,
            'replied' => $count_replied,
            'spam' => $count_spam,
            'trash' => $count_trash,
            'crm_states' => $crm_state_counts,
            'forms' => $form_counts
        ];
        
        // Set highlighted_unread from full database count
        $data['highlighted_unread'] = $count_highlighted_unread;

        wp_send_json_success($data);
    }

    // CAMBIADO A PUBLIC: Necesario para que GW_Analytics_Emails pueda usarlo
    public function detect_spam_risk($text, $name, $email)
    {
        if (preg_match('/[0-9_]/', $name))
            return 2;
        if (strpos($name, 'http') !== false || strpos($name, 'www.') !== false)
            return 2;
        if (str_word_count($name) > 5)
            return 2;
        if (empty($text))
            return 0;
        if (preg_match('/[\x{0400}-\x{04FF}]/u', $text) || preg_match('/[\x{4E00}-\x{9FBF}]/u', $text)) {
            return 2;
        }

        $keywords_str = get_option('efa_spam_keywords', 'crypto, investment, wallet, forex, casino, bitcoin');
        $keywords_arr = array_map('trim', explode(',', $keywords_str));

        foreach ($keywords_arr as $word) {
            if (empty($word))
                continue;
            if (stripos($text, $word) !== false)
                return 2;
        }

        return 0;
    }

    public function ajax_get_submission_details()
    {
        check_ajax_referer('efa_nonce', 'nonce');
        if (!current_user_can(get_option('efa_access_role', 'manage_options')))
            wp_send_json_error('Sin permisos');
        $id = intval($_POST['id']);
        global $wpdb;
        $ts = $wpdb->prefix . 'e_submissions';
        $tv = $wpdb->prefix . 'e_submissions_values';
        $meta = $wpdb->get_row($wpdb->prepare("SELECT * FROM $ts WHERE id = %d", $id));
        if (!$meta)
            wp_send_json_error('No encontrado');
        $values = $wpdb->get_results($wpdb->prepare("SELECT `key`, `value` FROM $tv WHERE submission_id = %d", $id));

        // Process URL Titles
        $landing = '';
        $prev = '';
        $lead = '';
        foreach ($values as $v) {
            if ($v->key === 'landing_url')
                $landing = $this->get_pretty_title($v->value);
            if ($v->key === 'prev_url')
                $prev = $this->get_pretty_title($v->value);
            if ($v->key === 'current_url')
                $lead = $this->get_pretty_title($v->value);
        }

        // Process CRM Data
        $crm_status = 'new';
        $crm_notes = [];

        // Process Reply Data
        $replied_subject = '';
        $replied_message = '';
        $replied_date = '';
        $all_replies = [];

        foreach ($values as $v) {
            if ($v->key === self::CRM_STATUS_KEY) {
                $crm_status = $v->value;
            }
            if ($v->key === self::CRM_NOTES_KEY) {
                $decoded = json_decode($v->value, true);
                if (is_array($decoded)) {
                    $crm_notes = $decoded;
                }
            }
            if ($v->key === self::REPLIES_KEY) {
                $decoded = json_decode($v->value, true);
                if (is_array($decoded)) {
                    $all_replies = $decoded;
                }
            }
            if ($v->key === self::REPLIED_SUBJECT_KEY) {
                $replied_subject = $v->value;
            }
            if ($v->key === self::REPLIED_MESSAGE_KEY) {
                $replied_message = $v->value;
            }
            if ($v->key === self::REPLIED_STATUS_KEY) {
                $replied_date = $v->value;
            }
        }
        
        // Si no hay respuestas en el array pero hay datos individuales (compatibilidad con datos antiguos)
        if (empty($all_replies) && !empty($replied_date)) {
            $all_replies = [[
                'date' => $replied_date,
                'subject' => $replied_subject,
                'message' => $replied_message
            ]];
        }

        wp_send_json_success([
            'meta' => $meta,
            'values' => $values,
            'path_info' => ['landing' => $landing, 'prev' => $prev, 'lead' => $lead],
            'crm' => [
                'status' => $crm_status,
                'notes' => $crm_notes
            ],
            'replies' => $all_replies, // Array de todas las respuestas
            'reply' => !empty($all_replies) ? end($all_replies) : [ // Última respuesta para compatibilidad
                'subject' => $replied_subject,
                'message' => $replied_message,
                'date' => $replied_date
            ]
        ]);
    }

    public function ajax_mark_as_read()
    {
        check_ajax_referer('efa_nonce', 'nonce');
        if (!current_user_can(get_option('efa_access_role', 'manage_options')))
            wp_send_json_error('Sin permisos');

        global $wpdb;
        $tv = $wpdb->prefix . 'e_submissions_values';
        $submission_id = intval($_POST['id']);

        // Check if exists
        $exists = $wpdb->get_var($wpdb->prepare("SELECT id FROM $tv WHERE submission_id = %d AND `key` = %s", $submission_id, self::READ_STATUS_KEY));

        if ($exists) {
            $wpdb->update($tv, ['value' => '1'], ['id' => $exists]);
        } else {
            $wpdb->insert($tv, ['submission_id' => $submission_id, 'key' => self::READ_STATUS_KEY, 'value' => '1']);
        }

        wp_send_json_success();
    }

    private function get_pretty_title($url)
    {
        if (empty($url))
            return '';
        $parsed = parse_url($url);
        $path = isset($parsed['path']) ? $parsed['path'] : '';
        if ($path === '/' || empty($path))
            return 'Inicio';
        $path = trim($path, '/');
        $segments = explode('/', $path);
        $slug = end($segments);
        $title = ucwords(str_replace(['-', '_'], ' ', $slug));
        return $title;
    }

    // --- NUEVA FUNCIÓN: LISTA BLANCA (Inmunidad) ---
    private function is_whitelisted($email)
    {
        $email_lower = strtolower(trim($email));
        $whitelist_str = get_option('efa_whitelist_emails', '');

        if (empty($whitelist_str))
            return false;

        $whitelist_arr = array_map('trim', explode(',', $whitelist_str));

        foreach ($whitelist_arr as $allowed) {
            if (empty($allowed))
                continue;
            $allowed = strtolower($allowed);

            // Si lo que guardaste empieza por '@' (ej: @cliente.com), es un dominio
            if (strpos($allowed, '@') === 0) {
                // Comprobamos si el email termina con ese dominio
                if (substr($email_lower, -strlen($allowed)) === $allowed)
                    return true;
            }
            // Si no, es un email exacto
            elseif ($allowed === $email_lower) {
                return true;
            }
        }
        return false;
    }

    private function is_hard_banned($email)
    {
        $email_lower = strtolower(trim($email));

        $tlds_str = get_option('efa_spam_tlds', '.ru, .cn, .pl');
        $tlds_arr = array_map('trim', explode(',', $tlds_str));
        foreach ($tlds_arr as $tld) {
            if (empty($tld))
                continue;
            if (stripos($email_lower, strtolower($tld)) !== false)
                return true;
        }

        $blocked_emails_str = get_option('efa_spam_emails', '');
        $blocked_emails_arr = array_map('trim', explode(',', $blocked_emails_str));
        foreach ($blocked_emails_arr as $blocked_email) {
            if (strtolower($blocked_email) === $email_lower)
                return true;
        }

        return false;
    }

    // --- CRM FUNCTIONS ---

    public function ajax_update_crm_status()
    {
        check_ajax_referer('efa_nonce', 'nonce');
        if (!current_user_can(get_option('efa_access_role', 'manage_options')))
            wp_send_json_error('Sin permisos');

        global $wpdb;
        $tv = $wpdb->prefix . 'e_submissions_values';
        $submission_id = intval($_POST['id']);
        $status = sanitize_text_field($_POST['status']);


        // Status will be sanitized, no need for hardcoded validation since we support custom labels

        $exists = $wpdb->get_var($wpdb->prepare("SELECT id FROM $tv WHERE submission_id = %d AND `key` = %s", $submission_id, self::CRM_STATUS_KEY));

        if ($exists) {
            $wpdb->update($tv, ['value' => $status], ['id' => $exists]);
        } else {
            $wpdb->insert($tv, ['submission_id' => $submission_id, 'key' => self::CRM_STATUS_KEY, 'value' => $status]);
        }

        wp_send_json_success();
    }

    public function ajax_add_crm_note()
    {
        check_ajax_referer('efa_nonce', 'nonce');
        if (!current_user_can(get_option('efa_access_role', 'manage_options')))
            wp_send_json_error('Sin permisos');

        global $wpdb;
        $tv = $wpdb->prefix . 'e_submissions_values';
        $submission_id = intval($_POST['id']);
        $note_text = sanitize_textarea_field($_POST['note']);

        if (empty($note_text))
            wp_send_json_error('Nota vacía');

        $current_user = wp_get_current_user();
        $author_name = $current_user->exists() ? $current_user->display_name : 'Sistema';

        $new_note = [
            'text' => $note_text,
            'author' => $author_name,
            'date' => current_time('mysql'),
            'ts' => time()
        ];

        // Obtener notas existentes
        $existing_json = $wpdb->get_var($wpdb->prepare("SELECT value FROM $tv WHERE submission_id = %d AND `key` = %s", $submission_id, self::CRM_NOTES_KEY));

        $notes = [];
        if ($existing_json) {
            $decoded = json_decode($existing_json, true);
            if (is_array($decoded))
                $notes = $decoded;
        }

        // Añadir nueva nota
        $notes[] = $new_note;

        // Guardar
        $json_to_save = json_encode($notes, JSON_UNESCAPED_UNICODE);

        if ($existing_json) {
            $wpdb->update($tv, ['value' => $json_to_save], ['submission_id' => $submission_id, 'key' => self::CRM_NOTES_KEY]);
        } else {
            $wpdb->insert($tv, ['submission_id' => $submission_id, 'key' => self::CRM_NOTES_KEY, 'value' => $json_to_save]);
        }

        wp_send_json_success(['note' => $new_note, 'notes_count' => count($notes)]);
    }


    private function get_crm_labels()
    {
        $default_labels = [
            ['id' => 'new', 'label' => 'Nuevo', 'color' => '#2271b1'],
            ['id' => 'contacted', 'label' => 'Contactado', 'color' => '#f59e0b'],
            ['id' => 'budget', 'label' => 'Presupuesto', 'color' => '#9333ea'],
            ['id' => 'won', 'label' => 'Ganado', 'color' => '#16a34a'],
            ['id' => 'lost', 'label' => 'Perdido', 'color' => '#dc2626']
        ];
        return get_option('efa_crm_labels', $default_labels);
    }

    /**
     * AJAX: Add email to blacklist
     */
    public function ajax_blacklist_email()
    {
        check_ajax_referer('efa_nonce', 'nonce');
        if (!current_user_can(get_option('efa_access_role', 'manage_options')))
            wp_send_json_error('Sin permisos');

        $email = sanitize_email($_POST['email']);

        if (empty($email) || !is_email($email))
            wp_send_json_error('Email inválido');

        // Get existing blacklist
        $blacklist_str = get_option('efa_spam_emails', '');
        $blacklist_arr = array_map('trim', explode(',', $blacklist_str));

        // Check if already blacklisted
        $email_lower = strtolower($email);
        foreach ($blacklist_arr as $blocked) {
            if (strtolower(trim($blocked)) === $email_lower) {
                wp_send_json_success(['message' => 'Email ya estaba en la lista negra', 'already_exists' => true]);
                return;
            }
        }

        // Add to blacklist
        if (empty($blacklist_str)) {
            $blacklist_arr = [$email];
        } else {
            $blacklist_arr[] = $email;
        }

        // Save
        $new_blacklist = implode(', ', array_filter($blacklist_arr));
        update_option('efa_spam_emails', $new_blacklist);

        wp_send_json_success(['message' => 'Email añadido a la lista negra', 'already_exists' => false]);
    }

    /**
     * AJAX: Delete all emails from a sender and blacklist them
     */
    public function ajax_delete_all_from_sender()
    {
        check_ajax_referer('efa_nonce', 'nonce');
        if (!current_user_can(get_option('efa_access_role', 'manage_options')))
            wp_send_json_error('Sin permisos');

        $email = sanitize_email($_POST['email']);

        if (empty($email) || !is_email($email))
            wp_send_json_error('Email inválido');

        global $wpdb;
        $ts = $wpdb->prefix . 'e_submissions';
        $tv = $wpdb->prefix . 'e_submissions_values';

        // Find all submission IDs with this email
        $submission_ids = $wpdb->get_col($wpdb->prepare(
            "SELECT DISTINCT submission_id 
            FROM $tv 
            WHERE (`key` LIKE %s OR `key` LIKE %s) 
            AND LOWER(value) = %s",
            '%email%',
            '%correo%',
            strtolower($email)
        ));

        if (empty($submission_ids)) {
            wp_send_json_error('No se encontraron correos de este remitente');
            return;
        }

        $count = count($submission_ids);

        // Mark all as spam override
        foreach ($submission_ids as $sid) {
            $wpdb->replace($tv, [
                'submission_id' => $sid,
                'key' => self::SPAM_OVERRIDE_KEY,
                'value' => '1'
            ]);
        }

        // Move to trash
        $ids_placeholder = implode(',', array_fill(0, $count, '%d'));
        $wpdb->query($wpdb->prepare(
            "UPDATE $ts SET status = 'trash' WHERE id IN ($ids_placeholder)",
            $submission_ids
        ));

        // Add to blacklist
        $blacklist_str = get_option('efa_spam_emails', '');
        $blacklist_arr = array_map('trim', explode(',', $blacklist_str));

        // Check if already blacklisted
        $email_lower = strtolower($email);
        $already_blacklisted = false;
        foreach ($blacklist_arr as $blocked) {
            if (strtolower(trim($blocked)) === $email_lower) {
                $already_blacklisted = true;
                break;
            }
        }

        if (!$already_blacklisted) {
            if (empty($blacklist_str)) {
                $blacklist_arr = [$email];
            } else {
                $blacklist_arr[] = $email;
            }
            $new_blacklist = implode(', ', array_filter($blacklist_arr));
            update_option('efa_spam_emails', $new_blacklist);
        }

        wp_send_json_success([
            'count' => $count,
            'message' => "$count correo(s) eliminado(s) y remitente añadido a la lista negra"
        ]);
    }

    /**
     * AJAX: Enviar correo de respuesta a un lead
     */
    public function ajax_send_reply_email()
    {
        check_ajax_referer('efa_nonce', 'nonce');
        if (!current_user_can(get_option('efa_access_role', 'manage_options')))
            wp_send_json_error('Sin permisos');

        $submission_id = intval($_POST['id']);
        $subject = isset($_POST['subject']) ? sanitize_text_field($_POST['subject']) : '';
        $message = isset($_POST['message']) ? wp_kses_post($_POST['message']) : '';

        if (empty($subject)) {
            wp_send_json_error('El asunto es obligatorio');
        }

        if (empty($message)) {
            wp_send_json_error('El mensaje es obligatorio');
        }

        global $wpdb;
        $tv = $wpdb->prefix . 'e_submissions_values';

        // Obtener email del lead
        $email = '';
        $name = '';
        $values = $wpdb->get_results($wpdb->prepare("SELECT `key`, `value` FROM $tv WHERE submission_id = %d", $submission_id));

        foreach ($values as $v) {
            $k_lower = strtolower($v->key);
            if (strpos($k_lower, 'email') !== false || strpos($k_lower, 'correo') !== false) {
                $email = $v->value;
            }
            if (strpos($k_lower, 'name') !== false || strpos($k_lower, 'nombre') !== false) {
                $name = $v->value;
            }
        }

        if (empty($email) || !is_email($email)) {
            wp_send_json_error('No se pudo obtener un email válido del lead');
        }

        // Obtener firma configurada
        $signature = get_option('efa_email_signature', '');
        
        // Generar HTML profesional del email
        $full_message = $this->generate_professional_email_html($message, $signature);

        // Headers del correo
        $current_user = wp_get_current_user();
        $from_name = $current_user->exists() ? $current_user->display_name : get_bloginfo('name');
        $from_email = $current_user->exists() ? $current_user->user_email : get_option('admin_email');

        $headers = [
            'Content-Type: text/html; charset=UTF-8',
            'From: ' . $from_name . ' <' . $from_email . '>',
            'Reply-To: ' . $from_email
        ];

        // Enviar correo
        $sent = wp_mail($email, $subject, $full_message, $headers);

        if ($sent) {
            // Marcar como respondido en la base de datos y guardar mensaje
            global $wpdb;
            $tv = $wpdb->prefix . 'e_submissions_values';
            $replied_date = current_time('mysql');
            
            // Guardar fecha de respuesta
            $exists = $wpdb->get_var($wpdb->prepare("SELECT id FROM $tv WHERE submission_id = %d AND `key` = %s", $submission_id, self::REPLIED_STATUS_KEY));
            if ($exists) {
                $wpdb->update($tv, ['value' => $replied_date], ['id' => $exists]);
            } else {
                $wpdb->insert($tv, [
                    'submission_id' => $submission_id,
                    'key' => self::REPLIED_STATUS_KEY,
                    'value' => $replied_date
                ]);
            }
            
            // Guardar asunto de respuesta
            $exists_subject = $wpdb->get_var($wpdb->prepare("SELECT id FROM $tv WHERE submission_id = %d AND `key` = %s", $submission_id, self::REPLIED_SUBJECT_KEY));
            if ($exists_subject) {
                $wpdb->update($tv, ['value' => $subject], ['id' => $exists_subject]);
            } else {
                $wpdb->insert($tv, [
                    'submission_id' => $submission_id,
                    'key' => self::REPLIED_SUBJECT_KEY,
                    'value' => $subject
                ]);
            }
            
            // Guardar mensaje de respuesta (sin HTML, solo texto)
            $message_text = wp_strip_all_tags($message);
            
            // Obtener respuestas existentes
            $replies_row = $wpdb->get_row($wpdb->prepare("SELECT id, value FROM $tv WHERE submission_id = %d AND `key` = %s", $submission_id, self::REPLIES_KEY));
            $replies = [];
            if ($replies_row) {
                $decoded = json_decode($replies_row->value, true);
                if (is_array($decoded)) {
                    $replies = $decoded;
                }
            }
            
            // Agregar nueva respuesta al array
            $new_reply = [
                'date' => $replied_date,
                'subject' => $subject,
                'message' => $message_text
            ];
            $replies[] = $new_reply;
            
            // Guardar array completo de respuestas
            $replies_json = json_encode($replies, JSON_UNESCAPED_UNICODE);
            if ($replies_row) {
                $wpdb->update($tv, ['value' => $replies_json], ['id' => $replies_row->id]);
            } else {
                $wpdb->insert($tv, [
                    'submission_id' => $submission_id,
                    'key' => self::REPLIES_KEY,
                    'value' => $replies_json
                ]);
            }
            
            $exists_message = $wpdb->get_var($wpdb->prepare("SELECT id FROM $tv WHERE submission_id = %d AND `key` = %s", $submission_id, self::REPLIED_MESSAGE_KEY));
            if ($exists_message) {
                $wpdb->update($tv, ['value' => $message_text], ['id' => $exists_message]);
            } else {
                $wpdb->insert($tv, [
                    'submission_id' => $submission_id,
                    'key' => self::REPLIED_MESSAGE_KEY,
                    'value' => $message_text
                ]);
            }
            
            wp_send_json_success([
                'message' => 'Correo enviado correctamente a ' . $email
            ]);
        } else {
            wp_send_json_error('Error al enviar el correo. Verifica tu configuración SMTP.');
        }
    }

    /**
     * Genera HTML profesional para email de marketing
     */
    private function generate_professional_email_html($message, $signature = '')
    {
        // Obtener información del sitio
        $site_name = get_bloginfo('name');
        $site_url = home_url();
        
        // Prioridad: logo personalizado > logo de la web > texto
        $email_logo = get_option('efa_email_logo', '');
        $site_logo_url = '';
        
        if (!empty($email_logo)) {
            // Usar logo personalizado
            $site_logo_url = $email_logo;
        } else {
            // Usar logo de la web como fallback
            $site_logo_url = get_site_icon_url(512) ?: get_site_icon_url();
        }
        
        // Obtener color del header (por defecto #667eea)
        $header_color = get_option('efa_email_header_color', '#667eea');
        $header_color_darker = $this->darken_color($header_color, 15);
        $header_gradient = 'linear-gradient(135deg, ' . esc_attr($header_color) . ' 0%, ' . esc_attr($header_color_darker) . ' 100%)';
        
        $admin_email = get_option('admin_email');
        $current_year = date('Y');
        
        // Si no hay logo, usar texto
        $logo_html = '';
        if ($site_logo_url) {
            $logo_html = '<img src="' . esc_url($site_logo_url) . '" alt="' . esc_attr($site_name) . '" style="max-width: 200px; height: auto; display: block; margin: 0 auto;">';
        } else {
            $logo_html = '<h1 style="margin: 0; font-size: 28px; font-weight: 700; color: #ffffff; text-align: center;">' . esc_html($site_name) . '</h1>';
        }

        // Procesar mensaje (convertir saltos de línea a HTML)
        $message_html = wpautop(wp_kses_post($message));
        
        // Procesar firma si existe
        $signature_html = '';
        if (!empty($signature)) {
            $signature_html = '<div style="margin-top: 30px; padding-top: 30px; border-top: 1px solid #e5e7eb;">' . wp_kses_post($signature) . '</div>';
        }

        // Generar HTML completo del email
        $email_html = '
<!DOCTYPE html>
<html lang="es">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>Mensaje</title>
    <!--[if mso]>
    <style type="text/css">
        body, table, td {font-family: Arial, sans-serif !important;}
    </style>
    <![endif]-->
</head>
<body style="margin: 0; padding: 0; background-color: #f3f4f6; font-family: -apple-system, BlinkMacSystemFont, \'Segoe UI\', Roboto, \'Helvetica Neue\', Arial, sans-serif;">
    <table role="presentation" cellspacing="0" cellpadding="0" border="0" width="100%" style="background-color: #f3f4f6;">
        <tr>
            <td align="center" style="padding: 40px 20px;">
                <!-- Contenedor principal -->
                <table role="presentation" cellspacing="0" cellpadding="0" border="0" width="600" style="max-width: 600px; background-color: #ffffff; border-radius: 12px; box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1); overflow: hidden;">
                    
                    <!-- Header con logo -->
                    <tr>
                        <td style="background: ' . $header_gradient . '; padding: 40px 30px; text-align: center;">
                            ' . $logo_html . '
                        </td>
                    </tr>
                    
                    <!-- Espaciador -->
                    <tr>
                        <td style="height: 30px; background-color: #ffffff;"></td>
                    </tr>
                    
                    <!-- Contenido principal -->
                    <tr>
                        <td style="padding: 0 40px 40px 40px;">
                            <div style="color: #1f2937; font-size: 16px; line-height: 1.8;">
                                ' . $message_html . '
                            </div>
                            
                            ' . $signature_html . '
                        </td>
                    </tr>
                    
                    <!-- Footer -->
                    <tr>
                        <td style="background-color: #f9fafb; padding: 30px 40px; border-top: 1px solid #e5e7eb;">
                            <table role="presentation" cellspacing="0" cellpadding="0" border="0" width="100%">
                                <tr>
                                    <td align="center" style="padding-bottom: 20px;">
                                        <p style="margin: 0; font-size: 13px; color: #6b7280; line-height: 1.6;">
                                            <strong style="color: #1f2937;">' . esc_html($site_name) . '</strong><br>
                                            <a href="' . esc_url($site_url) . '" style="color: #667eea; text-decoration: none;">' . esc_html($site_url) . '</a>
                                        </p>
                                    </td>
                                </tr>
                                <tr>
                                    <td align="center" style="padding-top: 20px; border-top: 1px solid #e5e7eb;">
                                        <p style="margin: 0; font-size: 11px; color: #9ca3af; line-height: 1.6;">
                                            Este correo fue enviado desde <a href="' . esc_url($site_url) . '" style="color: #667eea; text-decoration: none;">' . esc_html($site_name) . '</a><br>
                                            Si no deseas recibir más correos, puedes <a href="mailto:' . esc_attr($admin_email) . '?subject=Dar de baja" style="color: #667eea; text-decoration: none;">contactarnos</a>.<br><br>
                                            © ' . $current_year . ' ' . esc_html($site_name) . '. Todos los derechos reservados.
                                        </p>
                                    </td>
                                </tr>
                            </table>
                        </td>
                    </tr>
                    
                </table>
                
                <!-- Footer adicional para móviles -->
                <table role="presentation" cellspacing="0" cellpadding="0" border="0" width="100%" style="max-width: 600px; margin-top: 20px;">
                    <tr>
                        <td align="center" style="padding: 20px;">
                            <p style="margin: 0; font-size: 12px; color: #9ca3af;">
                                Este correo fue enviado a través de GW Elementor Analytics
                            </p>
                        </td>
                    </tr>
                </table>
                
            </td>
        </tr>
    </table>
</body>
</html>';

        return $email_html;
    }

    /**
     * Oscurece un color hexadecimal en un porcentaje
     */
    private function darken_color($hex, $percent = 15)
    {
        $hex = str_replace('#', '', $hex);
        $r = hexdec(substr($hex, 0, 2));
        $g = hexdec(substr($hex, 2, 2));
        $b = hexdec(substr($hex, 4, 2));
        
        $r = floor($r * (100 - $percent) / 100);
        $g = floor($g * (100 - $percent) / 100);
        $b = floor($b * (100 - $percent) / 100);
        
        $r = str_pad(dechex($r), 2, '0', STR_PAD_LEFT);
        $g = str_pad(dechex($g), 2, '0', STR_PAD_LEFT);
        $b = str_pad(dechex($b), 2, '0', STR_PAD_LEFT);
        
        return '#' . $r . $g . $b;
    }
}

