<?php

declare(strict_types=1);

namespace MailerPress\Api;

\defined('ABSPATH') || exit;

use DI\DependencyException;
use DI\NotFoundException;
use MailerPress\Core\Attributes\Endpoint;
use MailerPress\Core\Enums\Tables;
use MailerPress\Core\Kernel;
use MailerPress\Models\CustomFields;
use WP_Error;
use WP_REST_Request;
use WP_REST_Response;

class Contacts
{

    #[Endpoint(
        'contact/(?P<contact_id>\d+)/activity',
        methods: 'GET',
        permissionCallback: [Permissions::class, 'canManageAudience']
    )]
    public static function getContactActivity(WP_REST_Request $request)
    {
        global $wpdb;

        $contact_id = (int)$request['contact_id'];
        $page = max(1, (int)$request->get_param('page'));
        $per_page = 4;
        $offset = ($page - 1) * $per_page;

        if (empty($contact_id)) {
            return rest_ensure_response(['error' => __('Invalid contact ID.', 'mailerpress')]);
        }

        $contactStatsTable = $wpdb->prefix . Tables::MAILERPRESS_CONTACT_STATS;
        $clickTrackingTable = $wpdb->prefix . Tables::MAILERPRESS_CLICK_TRACKING;
        $campaignsTable = $wpdb->prefix . Tables::MAILERPRESS_CAMPAIGNS;

        // Get campaigns with last activity
        $campaigns = $wpdb->get_results($wpdb->prepare("
        SELECT c.*, MAX(cs.updated_at) AS last_activity
        FROM {$campaignsTable} c
        INNER JOIN {$contactStatsTable} cs ON cs.campaign_id = c.campaign_id
        WHERE cs.contact_id = %d
        GROUP BY c.campaign_id
        ORDER BY last_activity DESC
    ", $contact_id));

        $total_campaigns = count($campaigns);
        $paginated_campaigns = array_slice($campaigns, $offset, $per_page);

        // ✅ Optimisation: Précharger toutes les données en une seule fois
        $campaign_ids = array_map(fn($c) => (int)$c->campaign_id, $paginated_campaigns);

        $stats_by_campaign = [];
        $clicks_by_campaign = [];

        if (!empty($campaign_ids)) {
            $campaign_placeholders = implode(',', array_fill(0, count($campaign_ids), '%d'));

            // Récupérer toutes les stats en une seule requête
            $all_stats = $wpdb->get_results($wpdb->prepare("
                SELECT * FROM {$contactStatsTable}
                WHERE contact_id = %d AND campaign_id IN ({$campaign_placeholders})
                ORDER BY campaign_id, id ASC
            ", $contact_id, ...$campaign_ids));

            // Grouper les stats par campagne
            foreach ($all_stats as $stats) {
                $stats_by_campaign[(int)$stats->campaign_id][] = $stats;
            }

            // Récupérer tous les clics en une seule requête
            $all_clicks = $wpdb->get_results($wpdb->prepare("
                SELECT campaign_id, url, COUNT(*) AS click_count, MAX(created_at) AS last_clicked
                FROM {$clickTrackingTable}
                WHERE contact_id = %d AND campaign_id IN ({$campaign_placeholders})
                GROUP BY campaign_id, url
            ", $contact_id, ...$campaign_ids));

            // Grouper les clics par campagne
            foreach ($all_clicks as $click) {
                $clicks_by_campaign[(int)$click->campaign_id][] = $click;
            }
        }

        $grouped = [];

        foreach ($paginated_campaigns as $campaign) {
            $activities = [];
            $campaignScore = 0;

            // Utiliser les stats préchargées
            $statsRows = $stats_by_campaign[(int)$campaign->campaign_id] ?? [];

            foreach ($statsRows as $stats) {
                // Sent
                $activities[] = [
                    'type' => 'sent',
                    'timestamp' => $stats->created_at,
                    'details' => sprintf(__('Sent campaign "%s"', 'mailerpress'), $campaign->name),
                ];

                // Opened
                if (!empty($stats->opened)) {
                    $activities[] = [
                        'type' => 'opened',
                        'timestamp' => $stats->updated_at ?: $stats->created_at,
                        'details' => sprintf(__('Opened campaign "%s"', 'mailerpress'), $campaign->name),
                    ];
                    $campaignScore = max($campaignScore, 80);
                }

                // Unsubscribed
                if (!empty($stats->unsubscribed)) {
                    $activities[] = [
                        'type' => 'unsubscribed',
                        'timestamp' => $stats->updated_at ?: $stats->created_at,
                        'details' => sprintf(__('Unsubscribed from campaign "%s"', 'mailerpress'), $campaign->name),
                    ];
                    $campaignScore = 0;
                }

                // Revenue
                if (!empty($stats->revenue) && (float)$stats->revenue > 0) {
                    if (function_exists('get_woocommerce_currency_symbol') && function_exists('wc_get_price_decimals')) {
                        $revenue = (float)$stats->revenue;
                        $currency_symbol = html_entity_decode(get_woocommerce_currency_symbol(), ENT_QUOTES, 'UTF-8');
                        $formatted_price = number_format(
                            $revenue,
                            wc_get_price_decimals(),
                            wc_get_price_decimal_separator(),
                            wc_get_price_thousand_separator()
                        );

                        $activities[] = [
                            'type' => 'revenue',
                            'timestamp' => $stats->updated_at ?: $stats->created_at,
                            'details' => sprintf(
                                __('Generated %s %s in revenue from campaign "%s"', 'mailerpress'),
                                $formatted_price,
                                $currency_symbol,
                                $campaign->name
                            ),
                        ];
                    }
                }
            }

            // Utiliser les clics préchargés
            $clicks = $clicks_by_campaign[(int)$campaign->campaign_id] ?? [];

            foreach ($clicks as $click) {
                $click_count = (int)$click->click_count;
                $details = sprintf(
                    __('Clicked link in campaign "%s": %s', 'mailerpress'),
                    $campaign->name,
                    esc_url($click->url)
                );

                if ($click_count > 1) {
                    $details .= sprintf(__(' — clicked %d times', 'mailerpress'), $click_count);
                }

                $activities[] = [
                    'type' => 'clicked',
                    'timestamp' => $click->last_clicked,
                    'details' => $details,
                ];
                $campaignScore = max($campaignScore, 100);
            }

            usort($activities, function ($a, $b) {
                $timeA = strtotime($a['timestamp']);
                $timeB = strtotime($b['timestamp']);

                // Primary sort: descending timestamp
                if ($timeA !== $timeB) {
                    return $timeB <=> $timeA; // latest first
                }

                // Secondary sort: priority for same timestamp
                $priority = [
                    'opened' => 1,
                    'clicked' => 2,
                    'revenue' => 3,
                    'sent' => 4,
                    'unsubscribed' => 5,
                ];

                $a_priority = $priority[$a['type']] ?? 99;
                $b_priority = $priority[$b['type']] ?? 99;

                return $a_priority <=> $b_priority;
            });





            $grouped[] = [
                'campaign_id' => $campaign->campaign_id,
                'campaign_name' => $campaign->name,
                'engagement_score' => $campaignScore,
                'activities' => $activities,
            ];
        }

        return rest_ensure_response([
            'activities' => $grouped,
            'pagination' => [
                'page' => $page,
                'per_page' => $per_page,
                'total' => $total_campaigns,
                'total_pages' => ceil($total_campaigns / $per_page),
            ],
        ]);
    }

    #[Endpoint(
        'contact/(?P<contact_id>\d+)/campaigns',
        methods: 'GET',
        permissionCallback: [Permissions::class, 'canManageAudience']
    )]
    public static function getContactCampaigns(WP_REST_Request $request)
    {
        global $wpdb;

        $contact_id = (int)$request['contact_id'];

        $campaigns = $wpdb->get_results($wpdb->prepare(
            "
        SELECT DISTINCT c.campaign_id, c.name
        FROM {$wpdb->prefix}mailerpress_campaigns c
        INNER JOIN {$wpdb->prefix}mailerpress_contact_stats cs
            ON cs.campaign_id = c.campaign_id
        WHERE cs.contact_id = %d
        ORDER BY c.created_at DESC
        ",
            $contact_id
        ), ARRAY_A);

        return rest_ensure_response($campaigns);
    }

    #[Endpoint(
        'stats/(?P<contact_id>\d+)',
        methods: 'GET',
        permissionCallback: [Permissions::class, 'canManageAudience']
    )]
    public static function contactStats(WP_REST_Request $request)
    {
        global $wpdb;

        $contact_id = (int)$request['contact_id'];
        $campaign_id = $request->get_param('campaign_id'); // optional

        $contactStatsTable = $wpdb->prefix . 'mailerpress_contact_stats';
        $conditions = ["contact_id = %d"];
        $params = [$contact_id];

        if (!empty($campaign_id)) {
            $conditions[] = "campaign_id = %d";
            $params[] = (int)$campaign_id;
        }

        $whereClause = "WHERE " . implode(" AND ", $conditions);

        $stats = [
            'total_opened' => (int)$wpdb->get_var($wpdb->prepare(
                "SELECT SUM(opened) FROM {$contactStatsTable} {$whereClause}",
                ...$params
            )),
            'total_clicked' => (int)$wpdb->get_var($wpdb->prepare(
                "SELECT SUM(click_count) FROM {$contactStatsTable} {$whereClause}",
                ...$params
            )),
            'total_unsubscribed' => (int)$wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$contactStatsTable} {$whereClause} AND status = 'bad'",
                ...$params
            )),
            'total_revenue' => (float)$wpdb->get_var($wpdb->prepare(
                "SELECT SUM(revenue) FROM {$contactStatsTable} {$whereClause}",
                ...$params
            )),
            'last_activity' => $wpdb->get_var($wpdb->prepare(
                "SELECT MAX(updated_at) FROM {$contactStatsTable} {$whereClause}",
                ...$params
            )),
        ];

        // Normalize nulls to 0
        foreach ($stats as $key => $value) {
            if ($value === null) {
                $stats[$key] = 0;
            }
        }

        return rest_ensure_response($stats);
    }

    #[Endpoint(
        'export/(?P<export_id>[a-zA-Z0-9-]+)',
        methods: 'GET',
    )]
    public static function handleExportDownload(WP_REST_Request $request)
    {
        $export_id = sanitize_text_field($request->get_param('export_id'));
        $token = sanitize_text_field($request->get_param('token'));

        $export_data = get_option("mailerpress_export_{$export_id}");
        if (!$export_data) {
            return new \WP_REST_Response(['message' => __('Export not found.', 'mailerpress')], 404);
        }

        if ($token !== $export_data['token']) {
            return new \WP_REST_Response(['message' => __('Invalid token.', 'mailerpress')], 403);
        }

        if (time() > $export_data['expires']) {
            return new \WP_REST_Response(['message' => __('Link expired.', 'mailerpress')], 410);
        }

        $zip_path = $export_data['zip_path'];
        if (!file_exists($zip_path)) {
            return new \WP_REST_Response(['message' => __('File not found.', 'mailerpress')], 404);
        }

        header('Content-Type: application/zip');
        header('Content-Disposition: attachment; filename="' . basename($zip_path) . '"');
        header('Content-Length: ' . filesize($zip_path));
        readfile($zip_path);
        exit;
    }

    #[Endpoint(
        'contact-note/(?P<contact_id>\d+)',
        methods: 'GET',
        permissionCallback: [Permissions::class, 'canManageAudience'],
    )]
    public function getContactNote(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;

        $contact_id = intval($request['contact_id']);
        $table_name = Tables::get(Tables::MAILERPRESS_CONTACT_NOTE);

        $results = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT note_id, content, created_at, updated_at 
             FROM $table_name 
             WHERE contact_id = %d 
             ORDER BY created_at DESC",
                $contact_id
            ),
            ARRAY_A
        );

        return new WP_REST_Response([
            'success' => true,
            'notes' => $results
        ]);
    }

    #[Endpoint(
        'contact/note',
        methods: 'POST',
        permissionCallback: [Permissions::class, 'canManageAudience'],
    )]
    public function addContactNote(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;

        $contact_id = intval($request->get_param('contact_id'));
        $content = sanitize_textarea_field($request->get_param('content'));

        $table_name = Tables::get(Tables::MAILERPRESS_CONTACT_NOTE);

        $result = $wpdb->insert(
            $table_name,
            [
                'contact_id' => $contact_id,
                'content' => $content,
                'created_at' => current_time('mysql'),
                'updated_at' => current_time('mysql'),
            ],
            [
                '%d',
                '%s',
                '%s',
                '%s',
            ]
        );

        if ($result === false) {
            return new WP_Error('insert_failed', __('Failed to insert contact note.', 'mailerpress'), ['status' => 500]);
        }

        $note_id = $wpdb->insert_id;

        // Fetch the newly inserted row
        $new_note = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT note_id, contact_id, content, created_at, updated_at FROM $table_name WHERE note_id = %d",
                $note_id
            ),
            ARRAY_A
        );

        return new WP_REST_Response([
            'success' => true,
            'note' => $new_note,
        ]);
    }

    #[Endpoint(
        'contacts/all',
        methods: 'GET',
        permissionCallback: [Permissions::class, 'canManageAudience'],
    )]
    public function all(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;

        $contact_table = Tables::get(Tables::MAILERPRESS_CONTACT);
        $contact_tags_table = Tables::get(Tables::CONTACT_TAGS);
        $tags_table = Tables::get(Tables::MAILERPRESS_TAGS);
        $lists_table = Tables::get(Tables::MAILERPRESS_LIST);
        $contact_lists_table = Tables::get(Tables::MAILERPRESS_CONTACT_LIST);
        $custom_fields_table = Tables::get(Tables::MAILERPRESS_CONTACT_CUSTOM_FIELDS);
        $field_definitions_table = Tables::get(Tables::MAILERPRESS_CUSTOM_FIELD_DEFINITIONS);

        $per_page = isset($_GET['perPages']) ? (int)(wp_unslash($_GET['perPages'])) : 20;
        $page = isset($_GET['paged']) ? (int)(wp_unslash($_GET['paged'])) : 1;
        $offset = ($page - 1) * $per_page;
        $search = $request->get_param('search');

        $where = '1=1';
        $params = [];
        $joins = '';

        // Search filter
        if (!empty($search)) {
            // Si la recherche est un nombre, chercher aussi par contact_id
            if (is_numeric($search)) {
                $where .= ' AND (c.email LIKE %s OR c.contact_id = %d)';
                $params[] = '%' . $wpdb->esc_like($search) . '%';
                $params[] = (int)$search;
            } else {
                $where .= ' AND c.email LIKE %s';
                $params[] = '%' . $wpdb->esc_like($search) . '%';
            }
        }

        // Subscription status filter
        if (!empty($_GET['subscription_status'])) {
            $where .= ' AND c.subscription_status = %s';
            $params[] = sanitize_text_field(wp_unslash($_GET['subscription_status']));
        }

        // Filter by lists
        $listParam = $request->get_param('list');
        $listIds = [];

        if (!empty($listParam) && is_array($listParam)) {
            foreach ($listParam as $list) {
                if (isset($list['id'])) {
                    $listIds[] = (int)$list['id'];
                }
            }
        }

        if (!empty($listIds)) {
            $joins .= " INNER JOIN {$contact_lists_table} cl ON cl.contact_id = c.contact_id ";
            $placeholders = implode(',', array_fill(0, count($listIds), '%d'));
            $where .= " AND cl.list_id IN ($placeholders)";
            $params = array_merge($params, $listIds);
        }

        // Filter by tags
        $tagParam = $request->get_param('tag');
        $tagIds = [];

        if (!empty($tagParam) && is_array($tagParam)) {
            foreach ($tagParam as $tag) {
                if (isset($tag['id'])) {
                    $tagIds[] = (int)$tag['id'];
                }
            }
        }

        if (!empty($tagIds)) {
            $joins .= " INNER JOIN {$contact_tags_table} ct_filter ON ct_filter.contact_id = c.contact_id ";
            $placeholders = implode(',', array_fill(0, count($tagIds), '%d'));
            $where .= " AND ct_filter.tag_id IN ($placeholders)";
            $params = array_merge($params, $tagIds);
        }

        // Order - Use whitelist to prevent SQL injection
        $allowed_orderby = ['contact_id', 'email', 'first_name', 'last_name', 'created_at', 'updated_at', 'subscription_status'];
        $allowed_order = ['ASC', 'DESC'];
        $orderby_param = $request->get_param('orderby');
        $order_param = strtoupper($request->get_param('order') ?? 'DESC');
        $orderby = in_array($orderby_param, $allowed_orderby, true) ? $orderby_param : 'contact_id';
        $order = in_array($order_param, $allowed_order, true) ? $order_param : 'DESC';
        $orderBy = sprintf('c.%s %s', esc_sql($orderby), esc_sql($order));

        // Fetch contacts
        $contacts = $wpdb->get_results($wpdb->prepare("
        SELECT c.*, c.contact_id as id
        FROM {$contact_table} c
        {$joins}
        WHERE {$where}
        ORDER BY {$orderBy}
        LIMIT %d OFFSET %d
    ", [...$params, $per_page, $offset]));

        $contact_ids = array_map(static fn($c) => $c->contact_id, $contacts);

        if (empty($contact_ids)) {
            return new \WP_REST_Response([
                'posts' => [],
                'pages' => 0,
                'count' => 0,
            ], 200);
        }

        $placeholders = implode(',', array_fill(0, count($contact_ids), '%d'));

        // Fetch tags
        $tags_results = $wpdb->get_results($wpdb->prepare("
        SELECT ct.contact_id, t.tag_id, t.name
        FROM {$contact_tags_table} ct
        INNER JOIN {$tags_table} t ON ct.tag_id = t.tag_id
        WHERE ct.contact_id IN ({$placeholders})
    ", ...$contact_ids));

        $tags_by_contact = [];
        foreach ($tags_results as $tag) {
            $tags_by_contact[$tag->contact_id][] = [
                'tag_id' => $tag->tag_id,
                'tag_name' => $tag->name,
            ];
        }

        // Fetch lists
        $lists_results = $wpdb->get_results($wpdb->prepare("
        SELECT cl.contact_id, cl.list_id, l.name as list_name
        FROM {$contact_lists_table} cl
        INNER JOIN {$lists_table} l ON cl.list_id = l.list_id
        WHERE cl.contact_id IN ({$placeholders})
    ", ...$contact_ids));

        $lists_by_contact = [];
        foreach ($lists_results as $list) {
            $lists_by_contact[$list->contact_id][] = [
                'list_id' => $list->list_id,
                'list_name' => $list->list_name,
            ];
        }

        // Fetch custom field definitions
        $field_definitions = $wpdb->get_results("SELECT * FROM {$field_definitions_table}");
        foreach ($field_definitions as $def) {
            $def->options = maybe_unserialize($def->options);
        }

        // Fetch contact custom field values
        $custom_field_results = $wpdb->get_results($wpdb->prepare("
        SELECT contact_id, field_key, field_value
        FROM {$custom_fields_table}
        WHERE contact_id IN ({$placeholders})
    ", ...$contact_ids));

        $custom_fields_by_contact = [];
        foreach ($custom_field_results as $field) {
            $custom_fields_by_contact[$field->contact_id][$field->field_key] = maybe_unserialize($field->field_value);
        }

        // Merge data
        foreach ($contacts as &$contact) {
            $contact->tags = $tags_by_contact[$contact->contact_id] ?? [];
            $contact->contact_lists = $lists_by_contact[$contact->contact_id] ?? [];

            $contact->custom_fields = [];
            foreach ($field_definitions as $def) {
                $contact->custom_fields[] = [
                    'field_key' => $def->field_key,
                    'label' => $def->label,
                    'type' => $def->type,
                    'required' => (bool)$def->required,
                    'options' => $def->options,
                    'value' => $custom_fields_by_contact[$contact->contact_id][$def->field_key] ?? '',
                ];
            }
        }

        // Pagination count
        $total_count = $wpdb->get_var($wpdb->prepare("
        SELECT COUNT(DISTINCT c.contact_id)
        FROM {$contact_table} c
        {$joins}
        WHERE {$where}
    ", $params));

        $total_pages = ceil($total_count / $per_page);

        return new \WP_REST_Response([
            'posts' => $contacts,
            'pages' => $total_pages,
            'count' => $total_count,
        ], 200);
    }

    #[Endpoint(
        'contact',
        methods: 'POST'
    )]
    public function add(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;

        $table_name = Tables::get(Tables::MAILERPRESS_CONTACT);

        // Récupérer et sécuriser les données
        $email = sanitize_email($request->get_param('contactEmail'));
        $first_name = sanitize_text_field($request->get_param('contactFirstName'));
        $last_name = sanitize_text_field($request->get_param('contactLastName'));
        $subscription_status = sanitize_text_field($request->get_param('contactStatus'));
        $contactTags = $request->get_param('tags') ?? [];
        $contactLists = $request->get_param('lists') ?? [];
        $optinSource = $request->get_param('opt_in_source') ?? 'unknown';
        $optinDetails = $request->get_param('optin_details') ?? '';
        $customFields = $request->get_param('custom_fields') ?? [];

        // Vérifier si le contact existe déjà
        $existing_contact = $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM {$table_name} WHERE email = %s", $email)
        );

        if ($existing_contact) {
            // Update contact existant
            $wpdb->update(
                $table_name,
                [
                    'first_name' => $first_name,
                    'last_name' => $last_name,
                    'updated_at' => current_time('mysql'),
                ],
                ['contact_id' => $existing_contact->contact_id],
                ['%s', '%s', '%s'],
                ['%d']
            );

            $contactId = $existing_contact->contact_id;
            do_action('mailerpress_contact_updated', $contactId);

            $message = __('Contact updated successfully.', 'mailerpress');
        } else {
            // Nouveau contact
            $unsubscribe_token = wp_generate_uuid4();
            $singUpConfirmation = get_option('mailerpress_signup_confirmation', wp_json_encode([
                'enableSignupConfirmation' => true
            ]));

            if (is_string($singUpConfirmation)) {
                $singUpConfirmation = json_decode($singUpConfirmation, true);
            }

            $wpdb->insert(
                $table_name,
                [
                    'email' => $email,
                    'first_name' => $first_name,
                    'last_name' => $last_name,
                    'subscription_status' => $optinSource !== 'manual'
                        ? (!empty($singUpConfirmation) && true === $singUpConfirmation['enableSignupConfirmation'] ? 'pending' : $subscription_status)
                        : $subscription_status,
                    'created_at' => current_time('mysql'),
                    'updated_at' => current_time('mysql'),
                    'unsubscribe_token' => $unsubscribe_token,
                    'opt_in_source' => $optinSource,
                    'opt_in_details' => $optinDetails,
                    'access_token' => bin2hex(random_bytes(32)),
                ],
                ['%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s']
            );

            $contactId = $wpdb->insert_id;

            $message = __('Contact added successfully.', 'mailerpress');
        }

        // --- Tags ---
        if (!empty($contactTags)) {
            $tagsTable = Tables::get(Tables::CONTACT_TAGS);
            foreach ($contactTags as $tag) {
                $exists = $wpdb->get_var($wpdb->prepare(
                    "SELECT 1 FROM {$tagsTable} WHERE contact_id = %d AND tag_id = %d",
                    $contactId,
                    (int) $tag['id']
                ));

                if (!$exists) {
                    $wpdb->insert(
                        $tagsTable,
                        [
                            'contact_id' => $contactId,
                            'tag_id' => $tag['id'],
                        ],
                        ['%d', '%d']
                    );
                    do_action('mailerpress_contact_tag_added', $contactId, $tag['id']);
                }
            }
        }

        // --- Lists ---
        if (!empty($contactLists)) {
            $listsTable = Tables::get(Tables::MAILERPRESS_CONTACT_LIST);
            foreach ($contactLists as $list) {
                // Vérifier si l'association existe déjà
                $exists = $wpdb->get_var($wpdb->prepare(
                    "SELECT 1 FROM {$listsTable} WHERE contact_id = %d AND list_id = %d",
                    $contactId,
                    (int) $list['id']
                ));

                if (!$exists) {
                    $wpdb->insert(
                        $listsTable,
                        [
                            'contact_id' => $contactId,
                            'list_id' => $list['id'],
                        ],
                        ['%d', '%d']
                    );
                    do_action('mailerpress_contact_list_added', $contactId, $list['id']);
                }
            }
        }

        // Trigger contact_created hook after lists and tags are added
        // This ensures the workflow context includes complete contact data
        if (isset($contactId)) {
            do_action('mailerpress_contact_created', $contactId);
        }

        // --- Custom Fields ---
        if (!empty($customFields)) {
            $customFieldsTable = Tables::get(Tables::MAILERPRESS_CONTACT_CUSTOM_FIELDS);

            foreach ($customFields as $field_key => $field_value) {
                // Vérifier si le champ existe déjà
                $existing = $wpdb->get_row(
                    $wpdb->prepare(
                        "SELECT * FROM {$customFieldsTable} WHERE contact_id = %d AND field_key = %s",
                        $contactId,
                        $field_key
                    )
                );

                if ($existing) {
                    $wpdb->update(
                        $customFieldsTable,
                        ['field_value' => $field_value, 'updated_at' => current_time('mysql')],
                        ['contact_id' => $contactId, 'field_key' => $field_key],
                        ['%s', '%s'],
                        ['%d', '%s']
                    );
                } else {
                    $wpdb->insert(
                        $customFieldsTable,
                        [
                            'contact_id' => $contactId,
                            'field_key' => $field_key,
                            'field_value' => $field_value,
                        ],
                        ['%d', '%s', '%s']
                    );
                }
            }
        }

        return new \WP_REST_Response([
            'success' => true,
            'message' => $message,
            'data' => [
                'contact_id' => $contactId,
            ],
        ]);
    }

    #[Endpoint(
        'contacts',
        methods: 'PUT',
        permissionCallback: [Permissions::class, 'canManageAudience'],
    )]
    public function edit(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;

        $table_name = Tables::get(Tables::MAILERPRESS_CONTACT);
        $tags_table = Tables::get(Tables::CONTACT_TAGS);
        $lists_table = Tables::get(Tables::MAILERPRESS_CONTACT_LIST);
        $customFieldsTable = Tables::get(Tables::MAILERPRESS_CONTACT_CUSTOM_FIELDS);

        $newStatus = $request->get_param('newStatus');
        $ids = $request->get_param('ids');
        $tags = $request->get_param('tags') ?? [];
        $lists = $request->get_param('lists') ?? [];
        $removeTags = $request->get_param('removeTags') ?? [];
        $removeLists = $request->get_param('removeLists') ?? [];
        $customFields = $request->get_param('custom_fields') ?? [];

        $firstName = $request->get_param('first_name');
        $lastName = $request->get_param('last_name');
        $email = $request->get_param('email');

        // ===============================
        // 🟢 BULK UPDATE (All contacts)
        // ===============================
        if (null === $ids) {
            $updateClauses = [];
            $params = [];

            if (!empty($newStatus)) {
                $updateClauses[] = 'subscription_status = %s';
                $params[] = esc_html($newStatus);
            }
            if (!empty($firstName)) {
                $updateClauses[] = 'first_name = %s';
                $params[] = sanitize_text_field($firstName);
            }
            if (!empty($lastName)) {
                $updateClauses[] = 'last_name = %s';
                $params[] = sanitize_text_field($lastName);
            }
            if (!empty($email)) {
                $updateClauses[] = 'email = %s';
                $params[] = sanitize_email($email);
            }

            if (!empty($updateClauses)) {
                $query = "UPDATE {$table_name} SET " . implode(', ', $updateClauses);
                $wpdb->query($wpdb->prepare($query, ...$params));
            }

            // Add tags to all contacts
            foreach ($tags as $tag) {
                $wpdb->query($wpdb->prepare(
                    "INSERT IGNORE INTO {$tags_table} (contact_id, tag_id)
                 SELECT contact_id, %d FROM {$table_name}",
                    $tag['id']
                ));
                do_action('mailerpress_contact_tag_added_bulk', $tag['id']);
            }

            // Add lists to all contacts
            foreach ($lists as $list) {
                $wpdb->query($wpdb->prepare(
                    "INSERT IGNORE INTO {$lists_table} (contact_id, list_id)
                 SELECT contact_id, %d FROM {$table_name}",
                    $list['id']
                ));
                do_action('mailerpress_contact_list_added_bulk', $list['id']);
            }

            do_action('mailerpress_all_contacts_updated');
        }

        // ===============================
        // 🟠 SPECIFIC CONTACT IDS UPDATE
        // ===============================
        else {
            foreach ($ids as $id) {
                $id = (int) $id;
                $updateData = [];
                $updateFormat = [];

                if (!empty($newStatus)) {
                    $updateData['subscription_status'] = esc_html($newStatus);
                    $updateFormat[] = '%s';
                }
                if ($firstName !== null) {
                    $updateData['first_name'] = sanitize_text_field($firstName);
                    $updateFormat[] = '%s';
                }
                if ($lastName !== null) {
                    $updateData['last_name'] = sanitize_text_field($lastName);
                    $updateFormat[] = '%s';
                }
                if ($email !== null) {
                    $sanitizedEmail = sanitize_email($email);

                    // Check if email already exists for another contact
                    $existingContact = $wpdb->get_row(
                        $wpdb->prepare(
                            "SELECT contact_id FROM {$table_name} WHERE email = %s AND contact_id != %d",
                            $sanitizedEmail,
                            $id
                        )
                    );

                    if ($existingContact) {
                        return new \WP_Error(
                            'email_exists',
                            __('This email address is already in use by another contact.', 'mailerpress'),
                            ['status' => 400]
                        );
                    }

                    $updateData['email'] = $sanitizedEmail;
                    $updateFormat[] = '%s';
                }

                if (!empty($updateData)) {
                    $wpdb->update(
                        $table_name,
                        $updateData,
                        ['contact_id' => $id],
                        $updateFormat,
                        ['%d']
                    );
                }

                // Add new tags (only if tag doesn't already exist)
                foreach ($tags as $tag) {
                    $exists = $wpdb->get_var($wpdb->prepare(
                        "SELECT 1 FROM {$tags_table} WHERE contact_id = %d AND tag_id = %d",
                        $id,
                        (int) $tag['id']
                    ));

                    if (!$exists) {
                        $wpdb->insert(
                            $tags_table,
                            ['contact_id' => $id, 'tag_id' => (int) $tag['id']],
                            ['%d', '%d']
                        );
                        do_action('mailerpress_contact_tag_added', $id, $tag['id']);
                    }
                }

                // Remove tags
                foreach ($removeTags as $tag) {
                    $deleted = $wpdb->delete(
                        $tags_table,
                        ['contact_id' => $id, 'tag_id' => (int) $tag['id']],
                        ['%d', '%d']
                    );
                    // Déclencher le hook seulement si la suppression a réussi
                    if ($deleted !== false && $deleted > 0) {
                        do_action('mailerpress_contact_tag_removed', $id, $tag['id']);
                    }
                }

                // Add new lists
                foreach ($lists as $list) {
                    // Vérifier si l'association existe déjà
                    $exists = $wpdb->get_var($wpdb->prepare(
                        "SELECT 1 FROM {$lists_table} WHERE contact_id = %d AND list_id = %d",
                        $id,
                        (int) $list['id']
                    ));

                    if (!$exists) {
                        $wpdb->insert(
                            $lists_table,
                            ['contact_id' => $id, 'list_id' => (int) $list['id']],
                            ['%d', '%d']
                        );
                        do_action('mailerpress_contact_list_added', $id, $list['id']);
                    }
                }

                // Remove lists
                foreach ($removeLists as $list) {
                    $wpdb->delete(
                        $lists_table,
                        ['contact_id' => $id, 'list_id' => (int) $list['id']],
                        ['%d', '%d']
                    );
                    do_action('mailerpress_contact_list_removed', $id, $list['id']);
                }

                // Update custom fields
                foreach ($customFields as $field) {
                    $existing = $wpdb->get_var($wpdb->prepare(
                        "SELECT field_id FROM {$customFieldsTable} WHERE contact_id = %d AND field_key = %s",
                        $id,
                        $field['field_key']
                    ));

                    if ($existing) {
                        $wpdb->update(
                            $customFieldsTable,
                            ['field_value' => $field['field_value']],
                            ['field_id' => $existing],
                            ['%s'],
                            ['%d']
                        );
                    } else {
                        $wpdb->insert(
                            $customFieldsTable,
                            [
                                'contact_id' => $id,
                                'field_key' => $field['field_key'],
                                'field_value' => $field['field_value']
                            ],
                            ['%d', '%s', '%s']
                        );
                    }
                }
            }
        }

        return new \WP_REST_Response([
            'success' => true,
            'message' => __('Contacts updated successfully.', 'mailerpress'),
        ], 200);
    }

    #[Endpoint(
        'contact/(?P<id>\d+)',
        methods: 'PUT',
        permissionCallback: [Permissions::class, 'canManageAudience'],
    )]
    public function editSingle(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;

        $table_name = Tables::get(Tables::MAILERPRESS_CONTACT);
        $tags_table = Tables::get(Tables::CONTACT_TAGS);
        $lists_table = Tables::get(Tables::MAILERPRESS_CONTACT_LIST);

        $id = (int)$request->get_param('id');
        $newStatus = $request->get_param('newStatus');
        $tags = $request->get_param('tags') ?? [];
        $lists = $request->get_param('lists') ?? [];
        $removeTags = $request->get_param('removeTags') ?? [];
        $removeLists = $request->get_param('removeLists') ?? [];

        if (empty($id)) {
            return new \WP_REST_Response([
                'success' => false,
                'message' => __('Missing contact ID.', 'mailerpress'),
            ], 400);
        }

        if (!empty($newStatus)) {
            $wpdb->update(
                $table_name,
                ['subscription_status' => esc_html($newStatus)],
                ['contact_id' => $id],
                ['%s'],
                ['%d']
            );
        }

        // Add tags (only if tag doesn't already exist)
        foreach ($tags as $tag) {
            $exists = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$tags_table} WHERE contact_id = %d AND tag_id = %d",
                $id,
                (int) $tag['id']
            ));

            if (!$exists) {
                $wpdb->insert(
                    $tags_table,
                    [
                        'contact_id' => $id,
                        'tag_id' => $tag['id']
                    ],
                    ['%d', '%d']
                );
                do_action('mailerpress_contact_tag_added', $id, $tag['id']);
            }
        }

        // Remove tags
        foreach ($removeTags as $tag) {
            $deleted = $wpdb->delete(
                $tags_table,
                [
                    'contact_id' => $id,
                    'tag_id' => $tag['id']
                ],
                ['%d', '%d']
            );
            // Déclencher le hook seulement si la suppression a réussi
            if ($deleted !== false && $deleted > 0) {
                do_action('mailerpress_contact_tag_removed', $id, $tag['id']);
            }
        }

        // Add lists
        foreach ($lists as $list) {
            // Vérifier si l'association existe déjà
            $exists = $wpdb->get_var($wpdb->prepare(
                "SELECT 1 FROM {$lists_table} WHERE contact_id = %d AND list_id = %d",
                $id,
                (int) $list['id']
            ));

            if (!$exists) {
                $wpdb->insert(
                    $lists_table,
                    [
                        'contact_id' => $id,
                        'list_id' => $list['id']
                    ],
                    ['%d', '%d']
                );
                do_action('mailerpress_contact_list_added', $id, $list['id']);
            }
        }

        // Remove lists
        foreach ($removeLists as $list) {
            $wpdb->delete(
                $lists_table,
                [
                    'contact_id' => $id,
                    'list_id' => $list['id']
                ],
                ['%d', '%d']
            );
            do_action('mailerpress_contact_list_removed', $id, $list['id']);
        }

        return new \WP_REST_Response([
            'success' => true,
            'message' => __('Contact updated successfully.', 'mailerpress'),
        ], 200);
    }

    #[Endpoint(
        '/contact',
        methods: 'DELETE',
        permissionCallback: [Permissions::class, 'canManageAudience'],
    )]
    public function delete(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;

        // Get the contact IDs from the request (expects an array)
        $contact_ids = $request->get_param('ids'); // Assuming 'ids' is an array of contact IDs

        // Validate that the input is an array
        if (!\is_array($contact_ids) || empty($contact_ids)) {
            return new \WP_Error(
                'invalid_input',
                __('Contact IDs must be an array and cannot be empty', 'mailerpress'),
                ['status' => 400]
            );
        }

        // Verify if the contacts exist
        $table_name = Tables::get(Tables::MAILERPRESS_CONTACT);
        $placeholders = implode(',', array_fill(0, \count($contact_ids), '%d'));
        $existing_contacts = $wpdb->get_results($wpdb->prepare(
            "SELECT contact_id FROM {$table_name} WHERE contact_id IN ({$placeholders})",
            ...$contact_ids
        ));

        // If any contact does not exist, return an error
        if (\count($existing_contacts) !== \count($contact_ids)) {
            return new \WP_Error(
                'contact_not_found',
                __('One or more contacts were not found.', 'mailerpress'),
                ['status' => 404]
            );
        }

        // Delete the contacts
        $deleted = $wpdb->query($wpdb->prepare("DELETE FROM {$table_name} WHERE contact_id IN ({$placeholders})", ...$contact_ids));

        if ($deleted) {
            return new \WP_REST_Response([
                'success' => true,
                'message' => __('Contacts deleted successfully', 'mailerpress'),
                'deleted_contacts' => $contact_ids,
            ], 200);
        }

        return new \WP_Error(
            'delete_failed',
            __('Failed to delete the contacts.', 'mailerpress'),
            ['status' => 500]
        );
    }


    #[Endpoint(
        '/contact/list/(?P<id>\d+)',
        methods: 'DELETE',
        permissionCallback: [Permissions::class, 'canManageAudience'],
    )]
    public function deleteContactList(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;

        // Route param: contact ID from URL
        $contact_id = (int)$request->get_param('id');
        // Query param: list ID from ?listId=123
        $list_id = (int)$request->get_param('listId');

        if (!$contact_id || !$list_id) {
            return new \WP_REST_Response([
                'success' => false,
                'message' => __('Missing contact_id or list_id.', 'mailerpress'),
            ], 400);
        }

        $table = Tables::get(Tables::MAILERPRESS_CONTACT_LIST);

        $deleted = $wpdb->delete(
            $table,
            [
                'contact_id' => $contact_id,
                'list_id' => $list_id,
            ],
            [
                '%d',
                '%d',
            ]
        );

        if ($deleted === false) {
            return new \WP_REST_Response([
                'success' => false,
                'message' => __('Database error occurred.', 'mailerpress'),
            ], 500);
        }

        if ($deleted === 0) {
            return new \WP_REST_Response([
                'success' => false,
                'message' => __('No matching record found.', 'mailerpress'),
            ], 404);
        }

        // Déclencher le hook seulement si la suppression a réussi
        if ($deleted > 0) {
            do_action('mailerpress_contact_list_removed', $contact_id, $list_id);
        }

        return new \WP_REST_Response([
            'success' => true,
            'message' => __('List removed from contact.', 'mailerpress'),
        ], 200);
    }


    #[Endpoint(
        '/contact/tag/(?P<id>\d+)',
        methods: 'DELETE',
        permissionCallback: [Permissions::class, 'canManageLists'],
    )]
    public function deleteContactTag(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;

        // Route param: contact ID from URL
        $contact_id = (int)$request->get_param('id');

        // Pour les requêtes DELETE, récupérer tagId depuis le body JSON ou les query params
        $body_params = $request->get_json_params();
        $tag_id = 0;

        if (!empty($body_params['tagId'])) {
            $tag_id = (int)$body_params['tagId'];
        } elseif ($request->get_param('tagId')) {
            // Fallback sur query param si disponible
            $tag_id = (int)$request->get_param('tagId');
        }

        if (!$tag_id || !$contact_id) {
            return new \WP_REST_Response([
                'success' => false,
                'message' => __('Missing contact_id or tag_id.', 'mailerpress'),
            ], 400);
        }

        $table = Tables::get(Tables::CONTACT_TAGS);

        $deleted = $wpdb->delete(
            $table,
            [
                'contact_id' => $contact_id,
                'tag_id' => $tag_id,
            ],
            [
                '%d',
                '%d',
            ]
        );

        if ($deleted === false) {
            return new \WP_REST_Response([
                'success' => false,
                'message' => __('Database error occurred.', 'mailerpress'),
            ], 500);
        }

        if ($deleted === 0) {
            return new \WP_REST_Response([
                'success' => false,
                'message' => __('No matching record found.', 'mailerpress'),
            ], 404);
        }

        // Déclencher le hook seulement si la suppression a réussi
        if ($deleted > 0) {
            do_action('mailerpress_contact_tag_removed', $contact_id, $tag_id);
        }

        return new \WP_REST_Response([
            'success' => true,
            'message' => __('Tag removed from contact.', 'mailerpress'),
        ], 200);
    }


    #[Endpoint(
        '/contact/all',
        methods: 'DELETE',
        permissionCallback: [Permissions::class, 'canManageAudience'],
    )]
    public function deleteAll(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;
        $table_name = Tables::get(Tables::MAILERPRESS_CONTACT);

        // Delete all rows in the table
        $result = $wpdb->query("DELETE FROM {$table_name}");

        if (false === $result) {
            return new \WP_REST_Response(['message' => __('Failed to delete contacts.', 'mailerpress')], 500);
        }

        return new \WP_REST_Response(
            ['message' => __('All contacts have been deleted successfully.', 'mailerpress'), 'deleted' => $result],
            200
        );
    }

    #[Endpoint(
        'contacts/bactches/pending',
        methods: 'GET',
        permissionCallback: [Permissions::class, 'canEdit'],
    )]
    public function contactBatchImport(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;

        // Replace `wp_contact_batches` with your actual table name
        $table_name = Tables::get(Tables::MAILERPRESS_CONTACT_BATCHES);

        // Query all batches with status "pending"
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM `{$table_name}` WHERE `status` = %s",
            'pending'
        ), ARRAY_A);

        if (\is_array($results) && !empty($results)) {
            return new \WP_REST_Response(
                $results,  // This will be returned as a valid JSON array
                200
            );
        }

        return new \WP_REST_Response(
            ['message' => __('No pending records found', 'mailerpress')],
            200
        );
    }

    #[Endpoint(
        'contacts',
        methods: 'GET',
        permissionCallback: [Permissions::class, 'canView'],
        args: [
            'tags' => [
                'required' => false,
                'type' => 'array',
            ],
        ],
    )]
    public function response(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        $tags = $request->get_param('tags') ?? '';
        $lists = $request->get_param('lists') ?? '';

        return new \WP_REST_Response(
            Kernel::getContainer()->get(\MailerPress\Models\Contacts::class)->getContactsWithTagsAndLists(
                $lists,
                $tags
            ),
            200
        );
    }

    #[Endpoint(
        'contact/export',
        methods: 'POST',
        permissionCallback: [Permissions::class, 'canEdit'],
    )]
    public function export(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;

        $table_name = Tables::get(Tables::MAILERPRESS_CONTACT);
        $contact_ids = $request->get_param('contact_ids');
        $email = sanitize_email($request->get_param('email'));
        $export_id = uniqid(); // utilisé pour stocker les fichiers temporairement
        $batch_size = 200;
        $i = 0;

        if (is_array($contact_ids) && !empty($contact_ids)) {
            // Mode ciblé : on divise les contact_ids en batchs
            $chunks = array_chunk($contact_ids, $batch_size);

            foreach ($chunks as $index => $chunk) {
                as_schedule_single_action(
                    time() + $index,
                    'mailerpress_export_contact_batch',
                    [
                        'export_id' => $export_id,
                        'status' => null,
                        'offset' => null,
                        'contact_ids' => $chunk,
                    ],
                    'mailerpress'
                );
            }

            $total_batches = count($chunks);
        } else {
            // Mode complet : on récupère tous les contacts par status
            $count = (int)$wpdb->get_var("SELECT COUNT(*) FROM $table_name");

            if ($count === 0) {
                return new \WP_REST_Response([
                    'success' => false,
                    'message' => __('No contacts to export.', 'mailerpress'),
                ], 200);
            }

            $statuses = ['subscribed', 'unsubscribed'];
            $total_batches = 0;

            foreach ($statuses as $status) {
                $status_count = (int)$wpdb->get_var($wpdb->prepare(
                    "SELECT COUNT(*) FROM $table_name WHERE subscription_status = %s",
                    $status
                ));

                if ($status_count === 0) {
                    continue;
                }

                $offsets = [];
                for ($offset = 0; $offset < $status_count; $offset += $batch_size) {
                    $offsets[] = $offset;
                }

                foreach ($offsets as $offset) {
                    as_schedule_single_action(
                        time() + $i,
                        'mailerpress_export_contact_batch',
                        [
                            'export_id' => $export_id,
                            'status' => $status,
                            'offset' => $offset,
                        ],
                        'mailerpress'
                    );
                    $i++;
                }

                $total_batches += count($offsets);
            }
        }

        // Planifie la finalisation une fois tous les batchs théoriquement terminés
        as_schedule_single_action(
            time() + ($total_batches * 2),
            'mailerpress_finalize_export_contact_zip',
            ['export_id' => $export_id, 'email' => $email],
            'mailerpress'
        );

        return new \WP_REST_Response([
            'success' => true,
            'message' => __('Export started', 'mailerpress'),
            'export_id' => $export_id,
        ]);
    }


    /**
     * @throws DependencyException
     * @throws NotFoundException
     * @throws \Exception
     */
    #[Endpoint(
        'contacts/import',
        methods: 'POST',
        permissionCallback: [Permissions::class, 'canEdit'],
    )]
    public function batchImport(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;
        $data = $request->get_param('data') ?? [];

        $batch_data = [
            'tags' => wp_json_encode($data['tags']),
            'lists' => wp_json_encode($data['lists']),
            'count' => \count($data['mapping']),
            'subscription_status' => $data['status'],
        ];

        $wpdb->insert(
            Tables::get(Tables::MAILERPRESS_CONTACT_BATCHES),
            $batch_data
        );

        $batch_id = $wpdb->insert_id;

        if ($batch_id) {
            // Split contacts into chunks (e.g., 500 per chunk)
            $chunks = array_chunk($data['mapping'], 100);

            foreach ($chunks as $chunk) {
                // Save chunk to the temporary table
                $wpdb->insert(Tables::get(Tables::MAILERPRESS_IMPORT_CHUNKS), [
                    'batch_id' => $batch_id,
                    'chunk_data' => wp_json_encode($chunk),
                ]);

                $chunk_id = $wpdb->insert_id;

                if (!wp_next_scheduled('process_import_chunk', [$chunk_id])) {
                    // Planifie un événement unique dans 1 minute
                    as_schedule_single_action(
                        time() + 10,
                        'process_import_chunk',
                        [$chunk_id, $data['forceUpdate']]
                    );
                }
            }
        }

        return new \WP_REST_Response(
            [],
            200
        );
    }

    /**
     * @throws DependencyException
     * @throws NotFoundException
     * @throws \Exception
     */
    #[Endpoint(
        'contact/import',
        methods: 'POST',
        permissionCallback: [Permissions::class, 'canEdit'],
    )]
    public function importContact(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;
        $contact = $request->get_param('item');
        $status = sanitize_text_field($request->get_param('status')) ?? 'pending';
        $contactTags = $request->get_param('tags');
        $contactLists = $request->get_param('lists');
        $customFields = $contact['custom_fields'] ?? []; // <-- get custom_fields from $contact
        $forceUpdate = $request->get_param('forceUpdate');
        $contactTable = Tables::get(Tables::MAILERPRESS_CONTACT);
        $contactCustomFieldsTable = Tables::get(Tables::MAILERPRESS_CONTACT_CUSTOM_FIELDS); // NEW

        $contact_id = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT contact_id FROM {$contactTable} WHERE email = %s LIMIT 1",
                trim($contact['email'], '"')
            )
        );

        if (null === $contact_id) {
            // Extract and clean first_name and last_name
            // Handle both array_key_exists and isset to catch empty strings
            $first_name = '';
            $last_name = '';

            if (array_key_exists('first_name', $contact)) {
                $first_name = is_string($contact['first_name']) ? trim($contact['first_name'], ' "\'') : '';
            }

            if (array_key_exists('last_name', $contact)) {
                $last_name = is_string($contact['last_name']) ? trim($contact['last_name'], ' "\'') : '';
            }

            $contact_data = [
                'email' => sanitize_email($contact['email'] ?? ''),
                'first_name' => sanitize_text_field($first_name),
                'last_name' => sanitize_text_field($last_name),
                'subscription_status' => sanitize_text_field($status),
                'unsubscribe_token' => wp_generate_uuid4(),
                'created_at' => current_time('mysql'),
                'updated_at' => current_time('mysql'),
                'opt_in_source' => 'batch_import_file',
                'access_token' => bin2hex(random_bytes(32))
            ];

            $result = $wpdb->insert($contactTable, $contact_data);

            if (false !== $result) {
                $contactId = $wpdb->insert_id;

                // Tags (only if tag doesn't already exist)
                foreach ($contactTags ?? [] as $tag) {
                    $tagsTable = Tables::get(Tables::CONTACT_TAGS);
                    $exists = $wpdb->get_var($wpdb->prepare(
                        "SELECT 1 FROM {$tagsTable} WHERE contact_id = %d AND tag_id = %d",
                        $contactId,
                        (int) $tag['id']
                    ));

                    if (!$exists) {
                        $wpdb->insert($tagsTable, [
                            'contact_id' => $contactId,
                            'tag_id' => $tag['id'],
                        ]);
                        do_action('mailerpress_contact_tag_added', $contactId, $tag['id']);
                    }
                }

                // Lists
                foreach ($contactLists ?? [] as $list) {
                    $listsTable = Tables::get(Tables::MAILERPRESS_CONTACT_LIST);
                    // Vérifier si l'association existe déjà
                    $exists = $wpdb->get_var($wpdb->prepare(
                        "SELECT 1 FROM {$listsTable} WHERE contact_id = %d AND list_id = %d",
                        $contactId,
                        (int) $list['id']
                    ));

                    if (!$exists) {
                        $wpdb->insert($listsTable, [
                            'contact_id' => $contactId,
                            'list_id' => $list['id'],
                        ]);
                        do_action('mailerpress_contact_list_added', $contactId, $list['id']);
                    }
                }

                // Trigger contact_created hook after lists and tags are added
                // This ensures the workflow context includes complete contact data
                do_action('mailerpress_contact_created', $contactId);

                // Custom fields - skip standard fields that shouldn't be in custom_fields
                $standardFields = ['email', 'first_name', 'last_name', 'created_at', 'updated_at'];
                foreach ($customFields as $key => $value) {
                    // Skip if this is a standard field (shouldn't be in custom_fields)
                    if (in_array($key, $standardFields, true)) {
                        continue;
                    }

                    // Sanitize value according to field type
                    $sanitized_value = CustomFields::sanitizeValue($key, $value);

                    // Skip null values (empty or invalid)
                    if ($sanitized_value === null) {
                        continue;
                    }

                    // Convert to string for database storage (handles int, float, string, etc.)
                    $db_value = is_numeric($sanitized_value)
                        ? (string) $sanitized_value
                        : sanitize_text_field((string) $sanitized_value);

                    $wpdb->insert($contactCustomFieldsTable, [
                        'contact_id' => $contactId,
                        'field_key' => sanitize_text_field($key),
                        'field_value' => $db_value,
                    ]);
                }

                return new \WP_REST_Response($result, 200);
            }
        } else {
            if (true === $forceUpdate || '1' === $forceUpdate) {
                // Extract and clean first_name and last_name
                // Handle both array_key_exists and isset to catch empty strings
                $first_name = '';
                $last_name = '';

                if (array_key_exists('first_name', $contact)) {
                    $first_name = is_string($contact['first_name']) ? trim($contact['first_name'], ' "\'') : '';
                }

                if (array_key_exists('last_name', $contact)) {
                    $last_name = is_string($contact['last_name']) ? trim($contact['last_name'], ' "\'') : '';
                }

                $result = $wpdb->update(
                    $contactTable,
                    [
                        'subscription_status' => $status,
                        'updated_at' => current_time('mysql'),
                        'first_name' => sanitize_text_field($first_name),
                        'last_name' => sanitize_text_field($last_name),
                    ],
                    ['contact_id' => $contact_id]
                );

                if (false !== $result) {
                    // Tags (only if tag doesn't already exist)
                    foreach ($contactTags ?? [] as $tag) {
                        $tagsTable = Tables::get(Tables::CONTACT_TAGS);
                        $exists = $wpdb->get_var($wpdb->prepare(
                            "SELECT 1 FROM {$tagsTable} WHERE contact_id = %d AND tag_id = %d",
                            $contact_id,
                            (int) $tag['id']
                        ));

                        if (!$exists) {
                            $wpdb->insert($tagsTable, [
                                'contact_id' => $contact_id,
                                'tag_id' => $tag['id'],
                            ]);
                            do_action('mailerpress_contact_tag_added', $contact_id, $tag['id']);
                        }
                    }

                    // Lists
                    foreach ($contactLists ?? [] as $list) {
                        $listsTable = Tables::get(Tables::MAILERPRESS_CONTACT_LIST);
                        // Vérifier si l'association existe déjà
                        $exists = $wpdb->get_var($wpdb->prepare(
                            "SELECT 1 FROM {$listsTable} WHERE contact_id = %d AND list_id = %d",
                            $contact_id,
                            (int) $list['id']
                        ));

                        if (!$exists) {
                            $wpdb->insert($listsTable, [
                                'contact_id' => $contact_id,
                                'list_id' => $list['id'],
                            ]);
                            do_action('mailerpress_contact_list_added', $contact_id, $list['id']);
                        }
                    }

                    // Custom fields - skip standard fields that shouldn't be in custom_fields
                    $standardFields = ['email', 'first_name', 'last_name', 'created_at', 'updated_at'];
                    foreach ($customFields as $key => $value) {
                        // Skip if this is a standard field (shouldn't be in custom_fields)
                        if (in_array($key, $standardFields, true)) {
                            continue;
                        }

                        // Sanitize value according to field type
                        $sanitized_value = CustomFields::sanitizeValue($key, $value);

                        // Delete existing field first
                        $wpdb->delete($contactCustomFieldsTable, [
                            'contact_id' => $contact_id,
                            'field_key' => $key,
                        ]);

                        // Skip null values (empty or invalid)
                        if ($sanitized_value === null) {
                            continue;
                        }

                        // Convert to string for database storage (handles int, float, string, etc.)
                        $db_value = is_numeric($sanitized_value)
                            ? (string) $sanitized_value
                            : sanitize_text_field((string) $sanitized_value);

                        $wpdb->insert($contactCustomFieldsTable, [
                            'contact_id' => $contact_id,
                            'field_key' => sanitize_text_field($key),
                            'field_value' => $db_value,
                        ]);
                    }

                    return new \WP_REST_Response($result, 200);
                }
            }
        }

        usleep(100000);

        return new \WP_REST_Response([], 400);
    }

    #[Endpoint(
        'contact/check-email',
        methods: 'GET',
        permissionCallback: [Permissions::class, 'canManageAudience'],
    )]
    public function checkEmail(\WP_REST_Request $request): \WP_Error|\WP_HTTP_Response|\WP_REST_Response
    {
        global $wpdb;

        $email = sanitize_email($request->get_param('email'));
        $excludeId = (int) $request->get_param('exclude_id');

        if (empty($email) || !is_email($email)) {
            return new \WP_Error(
                'invalid_email',
                __('Invalid email address.', 'mailerpress'),
                ['status' => 400]
            );
        }

        $table_name = Tables::get(Tables::MAILERPRESS_CONTACT);

        $query = "SELECT contact_id FROM {$table_name} WHERE email = %s";
        $params = [$email];

        if ($excludeId > 0) {
            $query .= " AND contact_id != %d";
            $params[] = $excludeId;
        }

        $existing = $wpdb->get_row(
            $wpdb->prepare($query, ...$params)
        );

        return new \WP_REST_Response([
            'exists' => !empty($existing),
        ]);
    }
}
