<?php


use \Psr\Http\Message\ServerRequestInterface as Request;
use \Psr\Http\Message\ResponseInterface as Response;
use GuzzleHttp\Client as HttpClient;


$app->map(['GET', 'POST'], '/facebook/webhook', function (Request $request, Response $response) {
    date_default_timezone_set('Europe/Lisbon');

    // ========== GET - Verificación ==========
    if ($request->getMethod() == 'GET') {
        $verifyToken = WEBHOOK_VERIFY_TOKEN;
        $data = $request->getQueryParams();

        if (!isset($data['hub_verify_token'])) {
            error_app("Error: hub_verify_token no está presente.");
            $response->getBody()->write("Token faltante");
            return $response->withStatus(400);
        }

        if ($data['hub_verify_token'] !== $verifyToken) {
            error_app("Error: Token inválido. Recibido: " . $data['hub_verify_token']);
            $response->getBody()->write("Token inválido");
            return $response->withStatus(403);
        }

        if (isset($data['hub_challenge'])) {
            $response->getBody()->write($data['hub_challenge']);
            return $response->withStatus(200);
        }

        $response->getBody()->write("hub_challenge faltante");
        return $response->withStatus(400);
    }

    // ========== POST - Procesar webhook ==========
    if ($request->getMethod() == 'POST') {
        $body = (string) $request->getBody();
        $data = json_decode($body, true);

        if (!isset($data['entry'])) {
            error_app("No entry found in webhook data.");
            return $response->withStatus(200);
        }

        // UNA sola conexión a BD para todo el request
        $db = new db();
        $conn = $db->connect();

        if (!$conn) {
            error_app("Database connection failed for bot config.");
            return $response->withStatus(200);
        }

        $conn->exec("SET NAMES 'utf8mb4'");

        // Obtener configuración UNA vez
        $BOT_CONFIG = getBotConfig($conn);

        // Obtener token UNA vez
        $accessTokenManager = new FacebookTokenManager();
        $accessToken = $accessTokenManager->getAccessToken();

        // Preparar statements UNA vez (reutilizables) - CON INSERT IGNORE
        $stmtLiveComment = $conn->prepare("
            INSERT IGNORE INTO live_comments 
            (id_user, user_name, user_picture, live_id, comment_text, comment_datetime, fbcomment_id, provider)
            VALUES (:id_user, :user_name, :user_picture, :live_id, :comment_text, :comment_datetime, :fbcomment_id, 'facebook')
        ");

        $stmtPostComment = $conn->prepare("
            INSERT IGNORE INTO post_comments 
            (id_user, user_name, user_picture, post_id, comment_text, comment_datetime, fbcomment_id, provider, parent_id)
            VALUES (:id_user, :user_name, :user_picture, :post_id, :comment_text, :comment_datetime, :fbcomment_id, 'facebook', :parent_id)
        ");

        $stmtCheckLive = $conn->prepare("
            SELECT COUNT(*) AS count 
            FROM latest_live_id 
            WHERE live_id = :live_id AND live_status = 'LIVE'
        ");

        $stmtInsertLive = $conn->prepare("
            INSERT IGNORE INTO latest_live_id (live_id, live_status, last_checked, created_at, notification_sent)
            VALUES (:live_id, :live_status, NOW(), NOW(), 0)
        ");

        $stmtLastLive = $conn->prepare("
            SELECT live_id FROM latest_live_id ORDER BY created_at DESC LIMIT 1
        ");

        // Cache de fotos de perfil
        $userPictureCache = [];

        // Clientes del bot (lazy loading)
        $wooClient = null;
        $httpApi = null;

        foreach ($data['entry'] as $entry) {

 
            // Procesar comentarios
            if (isset($entry['changes'])) {
                foreach ($entry['changes'] as $change) {
                    if ($change['field'] !== 'feed' || 
                        !isset($change['value']['item']) || 
                        $change['value']['item'] !== 'comment') {
                        continue;
                    }

                    $commentText = $change['value']['message'];
                    $commentId = $change['value']['comment_id'];
                    $userId = $change['value']['from']['id'];
                    $userName = $change['value']['from']['name'];
                    $parentId = $change['value']['parent_id'] ?? null;
                    $createdTime = date('Y-m-d H:i:s', $change['value']['created_time']);
                    
                    // ⬇️ Si no hay nombre, intentar obtenerlo de la API
                    if (empty(trim($userName))) {
                        $context = stream_context_create(['http' => ['timeout' => 3]]);
                        $userApiUrl = "https://graph.facebook.com/v13.0/{$userId}?fields=name&access_token={$accessToken}";
                        $userResponse = @file_get_contents($userApiUrl, false, $context);
                        
                        if ($userResponse !== false) {
                            $userData = json_decode($userResponse, true);
                            $userName = $userData['name'] ?? '';
                        }
                    }

                    // Registrar / procesar comandos de notificación
                    handleUserNotificationPref($conn, $userId, $userName, $commentText, $commentId, $accessToken);

                    // Filtrar comentarios del usuario "São Costureira"
                    $normalizedUserName = trim($userName);
                    if (stripos($normalizedUserName, 'São Costureira') !== false) {
                        continue;
                    }

                    // Obtener información del contenido
                    $permalinkUrl = $change['value']['post']['permalink_url'] ?? '';
                    $postId = $change['value']['post_id'] ?? $change['value']['post']['id'] ?? null;

                    // DETERMINAR SI ES UN LIVE VIDEO REAL
                    $isLiveVideo = false;
                    $liveId = null;
                    $liveStatus = null;

                    if (strpos($permalinkUrl, '/videos/') !== false) {
                        $pathParts = explode('/', parse_url($permalinkUrl, PHP_URL_PATH));
                        $potentialLiveId = $pathParts[3] ?? null;

                        if ($potentialLiveId) {
                            $context = stream_context_create(['http' => ['timeout' => 3]]);
                            $videoApiUrl = "https://graph.facebook.com/v13.0/$potentialLiveId?fields=live_status&access_token=$accessToken";
                            $videoResponse = @file_get_contents($videoApiUrl, false, $context);

                            if ($videoResponse !== false) {
                                $videoData = json_decode($videoResponse, true);
                                $liveStatus = $videoData['live_status'] ?? null;

                                if ($liveStatus === 'LIVE') {
                                    $isLiveVideo = true;
                                    $liveId = $potentialLiveId;
                                } else {
                                    $stmtLastLive->execute();
                                    $lastLive = $stmtLastLive->fetch(PDO::FETCH_ASSOC);

                                    if ($lastLive && $lastLive['live_id'] === $potentialLiveId) {
                                        $isLiveVideo = true;
                                        $liveId = $potentialLiveId;
                                    }
                                }
                            }
                        }
                    }

                    // Variables para controlar flujo
                    $shouldExecuteBot = false;
                    $useProcessingId = null;

                    // Obtener foto de perfil (con cache)
                    if (!isset($userPictureCache[$userId])) {
                        $userPictureCache[$userId] = getUserProfilePicture($userId, $accessToken, $conn);
                    }
                    $userProfilePicture = $userPictureCache[$userId];

                    // LÓGICA PARA DETERMINAR QUÉ HACER
                    if ($isLiveVideo && $liveId) {
                        $shouldExecuteBot = $BOT_CONFIG['ENABLE_BOT_ON_LIVE_VIDEOS'];
                        $useProcessingId = $liveId;

                        // Manejar notificaciones si está LIVE
                        if ($liveStatus === 'LIVE') {
                            try {
                                $stmtCheckLive->execute([':live_id' => $liveId]);
                                $liveExists = $stmtCheckLive->fetch(PDO::FETCH_ASSOC)['count'] > 0;

                                if (!$liveExists) {
                                    $stmtInsertLive->execute([
                                        ':live_id' => $liveId,
                                        ':live_status' => $liveStatus
                                    ]);

                                    sendLiveNotification($liveId);
                                    createNotificationJob($liveId, $conn);
                                }
                            } catch (Exception $e) {
                                error_app("Error processing live_id: " . $e->getMessage());
                            }
                        }

                        // Guardar comentario del live (INSERT IGNORE)
                        try {
                            $stmtLiveComment->execute([
                                ':id_user' => $userId,
                                ':user_name' => $userName,
                                ':user_picture' => $userProfilePicture,
                                ':live_id' => $liveId,
                                ':comment_text' => $commentText,
                                ':comment_datetime' => $createdTime,
                                ':fbcomment_id' => $commentId
                            ]);
                        } catch (Exception $e) {
                            error_app("Error saving live comment: " . $e->getMessage());
                        }

                    } else {
                        $shouldExecuteBot = $BOT_CONFIG['ENABLE_BOT_ON_POSTS'];

                        if ($shouldExecuteBot) {
                            $stmtLastLive->execute();
                            $lastLiveResult = $stmtLastLive->fetch(PDO::FETCH_ASSOC);

                            if (!$lastLiveResult || !$lastLiveResult['live_id']) {
                                error_app("No last live_id found for bot functions - bot will not execute");
                                $shouldExecuteBot = false;
                            } else {
                                $useProcessingId = $lastLiveResult['live_id'];
                            }
                        }

                        // Guardar comentario del post (INSERT IGNORE)
                        try {
                            $stmtPostComment->execute([
                                ':id_user' => $userId,
                                ':user_name' => $userName,
                                ':user_picture' => $userProfilePicture,
                                ':post_id' => $postId,
                                ':comment_text' => $commentText,
                                ':comment_datetime' => $createdTime,
                                ':fbcomment_id' => $commentId,
                                ':parent_id' => $parentId
                            ]);
                        } catch (Exception $e) {
                            error_app("Error saving post comment: " . $e->getMessage());
                        }
                    }

      
                    // EJECUTAR BOT SI ESTÁ CONFIGURADO
                    if ($shouldExecuteBot && $useProcessingId) {
                        // Lazy loading
                        if ($wooClient === null) {
                            $wooClient = (new WooCommerceClient())->getClient();
                            $httpApi = new HttpClient([
                                'base_uri' => APP_URL,
                                'timeout' => 5,
                            ]);
                        }

                        $normalized = mb_strtolower(trim($commentText), 'UTF-8');

                        // 1) Cancelación
                        if (processCancelRequest($conn, $commentText, $userId, $useProcessingId, $commentId, $wooClient)) {
                            continue;
                        }

                        // 2) Listado de carrito
                        if (processListCartRequest($conn, $commentText, $userId, $useProcessingId, $commentId)) {
                            continue;
                        }

                        // 3) Confirmación
                        if (processConfirmRequest($conn, $parentId, $normalized, $commentId, $useProcessingId, $wooClient, $httpApi)) {
                            continue;
                        }

                        // 4) Auto-add
                        processAutoAdd($conn, $useProcessingId, $commentId, $commentText, $userId, $wooClient, $httpApi);
                    }
                }
            }

// ========================================================
// PROCESAR EVENTOS DE MESSENGER
// ========================================================
if (isset($entry['messaging'])) {
    foreach ($entry['messaging'] as $event) {
        $senderId = $event['sender']['id'] ?? null;
        $recipientId = $event['recipient']['id'] ?? null;
        $timestamp = isset($event['timestamp']) ? (int)($event['timestamp'] / 1000) : time();
        $messageDate = date('Y-m-d H:i:s', $timestamp);

        // -----------------------------------------------
        // 1. DELIVERY RECEIPTS (message_deliveries)
        // -----------------------------------------------
        if (isset($event['delivery'])) {
            $watermark = $event['delivery']['watermark'] ?? null;
            $mids = $event['delivery']['mids'] ?? [];

            if (!empty($mids)) {
                $placeholders = implode(',', array_fill(0, count($mids), '?'));
                $stmtDelivery = $conn->prepare("
                    UPDATE messenger_messages 
                    SET is_delivered = 1 
                    WHERE fb_message_id IN ($placeholders) AND is_from_page = 1
                ");
                $stmtDelivery->execute($mids);
            } elseif ($watermark) {
              $watermarkDate = date('Y-m-d H:i:s', (int)($watermark / 1000));
                $userId = $senderId;
                
                $stmtDeliveryWm = $conn->prepare("
                    UPDATE messenger_messages m
                    JOIN messenger_conversations c ON m.conversation_id = c.id
                    SET m.is_delivered = 1 
                    WHERE c.user_id = :user_id 
                    AND m.is_from_page = 1 
                    AND m.created_at <= :watermark
                ");
                $stmtDeliveryWm->execute([
                    ':user_id' => $userId,
                    ':watermark' => $watermarkDate
                ]);
            }
            continue;
        }

        // -----------------------------------------------
        // 2. READ RECEIPTS (message_reads)
        // -----------------------------------------------
        if (isset($event['read'])) {
            $watermark = $event['read']['watermark'] ?? null;
            $userId = $senderId;

            if ($watermark) {
              $watermarkDate = date('Y-m-d H:i:s', (int)($watermark / 1000));

                $stmtRead = $conn->prepare("
                    UPDATE messenger_messages m
                    JOIN messenger_conversations c ON m.conversation_id = c.id
                    SET m.is_read = 1, m.is_delivered = 1
                    WHERE c.user_id = :user_id 
                    AND m.is_from_page = 1 
                    AND m.created_at <= :watermark
                ");
                $stmtRead->execute([
                    ':user_id' => $userId,
                    ':watermark' => $watermarkDate
                ]);
            }
            continue;
        }

        // -----------------------------------------------
        // 3. POSTBACKS
        // -----------------------------------------------
        if (isset($event['postback'])) {
            continue;
        }

        // -----------------------------------------------
        // 4. MESSAGES (text + attachments)
        // -----------------------------------------------
        if (!isset($event['message'])) {
            continue;
        }

        // ============================================================
        // DETECTAR Y SALTAR ECHOS DE WEBAPP
        // ============================================================
        $isEcho = isset($event['message']['is_echo']) && $event['message']['is_echo'] === true;
        $fbMessageId = $event['message']['mid'] ?? null;
        
        if ($isEcho) {
            if ($fbMessageId) {
                $stmtCheckEcho = $conn->prepare("
                    SELECT id, attachment_url FROM messenger_messages 
                    WHERE fb_message_id = :mid LIMIT 1
                ");
                $stmtCheckEcho->execute([':mid' => $fbMessageId]);
                $existing = $stmtCheckEcho->fetch(PDO::FETCH_ASSOC);
                
                if ($existing) {
                    if (isset($event['message']['attachments']) && empty($existing['attachment_url'])) {
                        $firstAtt = $event['message']['attachments'][0] ?? null;
                        $fbUrl = $firstAtt['payload']['url'] ?? null;
                        if ($fbUrl) {
                            $stmtUpdateUrl = $conn->prepare("
                                UPDATE messenger_messages SET attachment_url = :url WHERE id = :id
                            ");
                            $stmtUpdateUrl->execute([':url' => $fbUrl, ':id' => $existing['id']]);
                        }
                    }
                    continue;
                }
                
                $hasText = isset($event['message']['text']);
                if ($hasText) {
                    $echoText = $event['message']['text'];
                    $stmtCheckRecent = $conn->prepare("
                        SELECT id FROM messenger_messages 
                        WHERE message_text = :text 
                        AND is_from_page = 1 
                        AND sent_from = 'webapp'
                        AND created_at >= DATE_SUB(NOW(), INTERVAL 30 SECOND)
                        LIMIT 1
                    ");
                    $stmtCheckRecent->execute([':text' => $echoText]);
                    if ($stmtCheckRecent->fetch()) {
                        continue;
                    }
                }
                
                if (isset($event['message']['attachments'])) {
                    $userId = $recipientId;
                    $stmtCheckRecentFile = $conn->prepare("
                        SELECT id FROM messenger_messages 
                        WHERE user_id = :user_id
                        AND is_from_page = 1 
                        AND sent_from = 'webapp'
                        AND message_type != 'text'
                        AND created_at >= DATE_SUB(NOW(), INTERVAL 30 SECOND)
                        LIMIT 1
                    ");
                    $stmtCheckRecentFile->execute([':user_id' => $userId]);
                    if ($stmtCheckRecentFile->fetch()) {
                        continue;
                    }
                }
            } else {
                $hasText = isset($event['message']['text']);
                if ($hasText) {
                    $echoText = $event['message']['text'];
                    $stmtCheckRecent2 = $conn->prepare("
                        SELECT id FROM messenger_messages 
                        WHERE message_text = :text 
                        AND is_from_page = 1 
                        AND created_at >= DATE_SUB(NOW(), INTERVAL 30 SECOND)
                        LIMIT 1
                    ");
                    $stmtCheckRecent2->execute([':text' => $echoText]);
                    if ($stmtCheckRecent2->fetch()) {
                        continue;
                    }
                }
                
                if (isset($event['message']['attachments'])) {
                    $userId = $recipientId;
                    $stmtCheckRecentFile2 = $conn->prepare("
                        SELECT id FROM messenger_messages 
                        WHERE user_id = :user_id
                        AND is_from_page = 1
                        AND message_type != 'text'
                        AND created_at >= DATE_SUB(NOW(), INTERVAL 30 SECOND)
                        LIMIT 1
                    ");
                    $stmtCheckRecentFile2->execute([':user_id' => $userId]);
                    if ($stmtCheckRecentFile2->fetch()) {
                        continue;
                    }
                }
            }
        }

        // ============================================================
        // VERIFICAR DUPLICADOS NORMALES (no-echo)
        // ============================================================
        if ($fbMessageId && !$isEcho) {
            $stmtCheck = $conn->prepare("SELECT id FROM messenger_messages WHERE fb_message_id = :mid LIMIT 1");
            $stmtCheck->execute([':mid' => $fbMessageId]);
            if ($stmtCheck->fetch()) {
                continue;
            }
        }
        
        $hasText = isset($event['message']['text']);
        $hasAttachments = isset($event['message']['attachments']);

        if (!$hasText && !$hasAttachments) {
            continue;
        }

        // ============================================================
        // VERIFICACION DE FACEBOOK: Detectar codigo en mensaje del usuario
        // ============================================================
        if ($hasText && !$isEcho) {
            $msgText = trim($event['message']['text']);
            // Extraer numeros de 10 digitos del mensaje
            if (preg_match('/\b(\d{10})\b/', $msgText, $matches)) {
                $possibleCode = $matches[1];
                try {
                    $stmtCode = $conn->prepare("
                        SELECT id, user_id_wp FROM facebook_verifications
                        WHERE verification_code = :code
                        AND verified = 0
                        AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)
                        LIMIT 1
                    ");
                    $stmtCode->execute([':code' => $possibleCode]);
                    $codeRow = $stmtCode->fetch(PDO::FETCH_ASSOC);

                    if ($codeRow) {
                        $fbUserId = $senderId;
                        // Marcar como verificado
                        $stmtVerify = $conn->prepare("UPDATE facebook_verifications SET verified = 1, facebook_user_id = :fbId, verified_at = NOW() WHERE id = :id");
                        $stmtVerify->execute([':fbId' => $fbUserId, ':id' => $codeRow['id']]);

                        // Actualizar users_app
                        $stmtUpdate = $conn->prepare("UPDATE users_app SET user_id_facebook = :fbId WHERE user_id_wp = :wpId");
                        $stmtUpdate->execute([':fbId' => $fbUserId, ':wpId' => $codeRow['user_id_wp']]);

                        // Responder al usuario por Messenger
                        sendMessengerMessageLembrete($fbUserId, "Conta vinculada com sucesso! Ja pode ver os seus pedidos na app São Costureira.", $accessToken);
   
                    }
                } catch (Exception $e) {
                    error_app("[FB-VERIFY] Error: " . $e->getMessage());
                }
            }
        }

        // Determinar dirección del mensaje
        $isFromPage = $isEcho ? 1 : 0;
        $sentFrom = $isEcho ? 'messenger' : 'messenger';
        
        // En echo, el usuario es el recipient. En mensaje normal, es el sender.
        $userId = $isFromPage ? $recipientId : $senderId;

        // Limpiar typing indicator si es mensaje del usuario
        if (!$isFromPage) {
            try {
                $stmtClearTyping = $conn->prepare("
                    INSERT INTO messenger_typing_status (user_id, is_typing, updated_at)
                    VALUES (:user_id, 0, NOW())
                    ON DUPLICATE KEY UPDATE is_typing = 0, updated_at = NOW()
                ");
                $stmtClearTyping->execute([':user_id' => $userId]);
            } catch (Exception $e) {}
        }

        // Crear/actualizar conversación
        if (!$isFromPage) {
            $userName = getUserNameFromFacebook($userId, $accessToken);
            $userPicture = getUserProfilePicture($userId, $accessToken, $conn);

            $stmtConversation = $conn->prepare("
                INSERT INTO messenger_conversations (user_id, user_name, user_picture, last_message_at, unread_count)
                VALUES (:user_id, :user_name, :user_picture, :message_date, 1)
                ON DUPLICATE KEY UPDATE 
                    user_name = COALESCE(VALUES(user_name), user_name),
                    user_picture = COALESCE(VALUES(user_picture), user_picture),
                    last_message_at = VALUES(last_message_at),
                    unread_count = unread_count + 1
            ");
            $stmtConversation->execute([
                ':user_id' => $userId,
                ':user_name' => $userName,
                ':user_picture' => $userPicture,
                ':message_date' => $messageDate
            ]);
        } else {
            // Echo: crear conversación si no existe, o actualizar si ya existe
            $userName = getUserNameFromFacebook($userId, $accessToken);
            $userPicture = getUserProfilePicture($userId, $accessToken, $conn);

            $stmtConversation = $conn->prepare("
                INSERT INTO messenger_conversations (user_id, user_name, user_picture, last_message_at, unread_count)
                VALUES (:user_id, :user_name, :user_picture, :message_date, 0)
                ON DUPLICATE KEY UPDATE last_message_at = VALUES(last_message_at)
            ");
            $stmtConversation->execute([
                ':user_id' => $userId,
                ':user_name' => $userName,
                ':user_picture' => $userPicture,
                ':message_date' => $messageDate
            ]);
        }

        // Obtener conversation_id
        $stmtGetConv = $conn->prepare("SELECT id FROM messenger_conversations WHERE user_id = :user_id");
        $stmtGetConv->execute([':user_id' => $userId]);
        $conversationId = $stmtGetConv->fetchColumn();

        if (!$conversationId) {
            continue;
        }

        // Guardar mensaje de texto
        $lastMessageText = '';
        $lastMessageType = 'text';

        if ($hasText) {
            $messageText = $event['message']['text'];
            $lastMessageText = $messageText;

            $stmtMessage = $conn->prepare("
                INSERT INTO messenger_messages 
                (conversation_id, user_id, message_text, message_type, fb_message_id, is_from_page, sent_from, created_at)
                VALUES (:conv_id, :user_id, :message, 'text', :fb_mid, :is_from_page, :sent_from, :created_at)
            ");
            $stmtMessage->execute([
                ':conv_id' => $conversationId,
                ':user_id' => $userId,
                ':message' => $messageText,
                ':fb_mid' => $fbMessageId,
                ':is_from_page' => $isFromPage,
                ':sent_from' => $sentFrom,
                ':created_at' => $messageDate
            ]);
        }

        // Guardar attachments
        if ($hasAttachments) {
            $attachments = $event['message']['attachments'];

            foreach ($attachments as $index => $attachment) {
                $type = $attachment['type'] ?? 'file';
                $url = $attachment['payload']['url'] ?? null;
                $stickerUrl = $attachment['payload']['sticker_id'] ?? null;
                $attMid = ($index === 0 && !$hasText) ? $fbMessageId : $fbMessageId . '_att_' . $index;

                $messageType = 'attachment';
                switch ($type) {
                    case 'image':
                        $messageType = ($stickerUrl || isset($attachment['payload']['sticker_id'])) ? 'sticker' : 'image';
                        break;
                    case 'video': $messageType = 'video'; break;
                    case 'audio': $messageType = 'audio'; break;
                    case 'file': $messageType = 'file'; break;
                    case 'location':
                        $lat = $attachment['payload']['coordinates']['lat'] ?? '';
                        $lng = $attachment['payload']['coordinates']['long'] ?? '';
                        $stmtLocMsg = $conn->prepare("
                            INSERT INTO messenger_messages 
                            (conversation_id, user_id, message_text, message_type, fb_message_id, is_from_page, sent_from, created_at)
                            VALUES (:conv_id, :user_id, :message, 'text', :fb_mid, :is_from_page, :sent_from, :created_at)
                        ");
                        $stmtLocMsg->execute([
                            ':conv_id' => $conversationId,
                            ':user_id' => $userId,
                            ':message' => "Localizacao: {$lat}, {$lng}",
                            ':fb_mid' => $attMid,
                            ':is_from_page' => $isFromPage,
                            ':sent_from' => $sentFrom,
                            ':created_at' => $messageDate
                        ]);
                        $lastMessageText = 'Localizacao';
                        $lastMessageType = 'text';
                        continue 2;
                    case 'fallback':
                        $fallbackUrl = $attachment['payload']['url'] ?? ($attachment['url'] ?? null);
                        if ($fallbackUrl) { $url = $fallbackUrl; $messageType = 'attachment'; }
                        else { continue 2; }
                        break;
                    default:
                        $messageType = 'attachment';
                }

                if ($url || $messageType === 'sticker') {
                    $stmtAttachment = $conn->prepare("
                        INSERT INTO messenger_messages 
                        (conversation_id, user_id, message_type, attachment_url, fb_message_id, is_from_page, sent_from, created_at)
                        VALUES (:conv_id, :user_id, :type, :url, :fb_mid, :is_from_page, :sent_from, :created_at)
                    ");
                    $stmtAttachment->execute([
                        ':conv_id' => $conversationId,
                        ':user_id' => $userId,
                        ':type' => $messageType,
                        ':url' => $url,
                        ':fb_mid' => $attMid,
                        ':is_from_page' => $isFromPage,
                        ':sent_from' => $sentFrom,
                        ':created_at' => $messageDate
                    ]);

                    $lastMessageType = $messageType;
                    switch ($messageType) {
                        case 'image': $lastMessageText = 'Imagem'; break;
                        case 'video': $lastMessageText = 'Video'; break;
                        case 'audio': $lastMessageText = 'Audio'; break;
                        case 'sticker': $lastMessageText = 'Sticker'; break;
                        default: $lastMessageText = 'Anexo';
                    }
                }
            }
        }

        // Actualizar last_message
        if ($lastMessageText || $hasText) {
            $updateMsg = $hasText ? $event['message']['text'] : $lastMessageText;
            $stmtUpdateLast = $conn->prepare("
                UPDATE messenger_conversations 
                SET last_message = :msg, last_message_type = :msg_type 
                WHERE id = :id
            ");
            $stmtUpdateLast->execute([
                ':msg' => $updateMsg,
                ':msg_type' => $lastMessageType,
                ':id' => $conversationId
            ]);
        }

        // ========================================================
        // BOT DE MESSENGER — Procesar comandos del usuario
        // ========================================================
        if (!$isFromPage && $hasText && !empty($BOT_CONFIG['ENABLE_BOT_ON_MESSENGER'])) {
            $messengerText = $event['message']['text'];


            // Solo procesar si el texto parece un comando del bot
            $isBotCommand = (
                preg_match('/^\s*(ref|refer)/i', $messengerText) ||
                stripos($messengerText, 'cancelar') !== false ||
                preg_match('/\b(carrinho|quantos|artigos|itens|listar|ver\s+carrinho|meu\s+carrinho)\b/i', $messengerText)
            );


            if ($isBotCommand) {

                // 1) Obtener último live_id
                $stmtLastLiveMsg = $conn->prepare("
                    SELECT live_id FROM latest_live_id 
                    ORDER BY created_at DESC LIMIT 1
                ");
                $stmtLastLiveMsg->execute();
                $lastLiveMsg = $stmtLastLiveMsg->fetch(PDO::FETCH_ASSOC);
                $messengerLiveId = $lastLiveMsg ? $lastLiveMsg['live_id'] : null;


                if (!$messengerLiveId) {
                    sendPlainTextMessage(
                        $userId,
                        $accessToken,
                        "De momento não é possível processar pedidos. Tenta novamente mais tarde."
                    );
                } else {
                    // 2) Construir replyContext para Messenger
                    $messengerReplyContext = [
                        'channel'      => 'messenger',
                        'user_id'      => $userId,
                        'access_token' => $accessToken,
                    ];

                    // 3) Lazy loading de clientes
                    if (!isset($wooClient) || $wooClient === null) {
                        $wooClient = (new WooCommerceClient())->getClient();
                    }
                    if (!isset($httpApi) || $httpApi === null) {
                        $httpApi = new \GuzzleHttp\Client([
                            'base_uri' => APP_URL,
                            'timeout'  => 5,
                        ]);
                    }

                    // 4) Ejecutar comandos en orden
                    $handled = false;

                    // 4.a) Cancelación
                    $handled = processCancelRequest(
                        $conn,
                        $messengerText,
                        $userId,
                        $messengerLiveId,
                        $fbMessageId,
                        $wooClient,
                        $messengerReplyContext
                    );

                    // 4.b) Listado de carrito
                    if (!$handled) {
                        $handled = processListCartRequest(
                            $conn,
                            $messengerText,
                            $userId,
                            $messengerLiveId,
                            $fbMessageId,
                            $messengerReplyContext
                        );

                    }

                    // 4.c) Auto-add (ref XXX ...)
                    if (!$handled) {
                        $parsedData = parseAutoCartComment($messengerText);


                        if ($parsedData) {
   
                            // Verificar que el mensaje existe en BD antes de llamar
                            $stmtDebugCheck = $conn->prepare("SELECT id, fb_message_id FROM messenger_messages WHERE fb_message_id = :mid LIMIT 1");
                            $stmtDebugCheck->execute([':mid' => $fbMessageId]);
                            $debugRow = $stmtDebugCheck->fetch(PDO::FETCH_ASSOC);


                            processAutoAdd(
                                $conn,
                                $messengerLiveId,
                                $fbMessageId,
                                $messengerText,
                                $userId,
                                $wooClient,
                                $httpApi,
                                $messengerReplyContext,
                                'messenger'
                            );
                            $handled = true;
                        }
                    }

                    if (!$handled) {
                        bot_log("[MessengerBot] Texto parecía comando pero no matcheó: '{$messengerText}'");
                    }
                }
            }
        }
        // ========================================================
        // FIN BOT DE MESSENGER
        // ========================================================
    }
}

// ========================================================
// FIN DE PROCESAMIENTO DE MESSENGER
// ========================================================
        }

        return $response->withStatus(200);
    }
});

// ========== FUNCIÓN PARA OBTENER CONFIGURACIÓN DEL BOT DESDE BD ==========
function getBotConfig($conn) {
    try {
        $stmt = $conn->prepare("
            SELECT config_key, config_value 
            FROM bot_config
        ");
        $stmt->execute();
        $configs = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        $botConfig = [];
        foreach ($configs as $config) {
            // Convertir string/int a boolean
            $botConfig[$config['config_key']] = (bool)$config['config_value'];
        }
        
        // Configuraciones por defecto solo para las que vienen de BD
        $defaults = [
            'ENABLE_BOT_ON_LIVE_VIDEOS' => false,
            'ENABLE_BOT_ON_POSTS' => true,
        ];
        
        // Combinar defaults con configuración de BD
        return array_merge($defaults, $botConfig);
        
    } catch (Exception $e) {
        error_app("Error getting bot config from DB: " . $e->getMessage());
        // Retornar configuración por defecto en caso de error
        return [
            'ENABLE_BOT_ON_LIVE_VIDEOS' => false,
            'ENABLE_BOT_ON_POSTS' => true,
        ];
    }
}

/**
 * Obtiene el nombre del usuario de Facebook usando la API Graph
 */
function getUserNameFromFacebook($userId, $accessToken) {
    $userApiUrl = "https://graph.facebook.com/v22.0/$userId?fields=name&access_token=$accessToken";
    $userResponse = @file_get_contents($userApiUrl);
    
    if ($userResponse === false) {
        error_app("Failed to fetch user data from API");
        return 'Unknown User';
    }
    
    $userData = json_decode($userResponse, true);
    return $userData['name'] ?? 'Unknown User';
}

/**
 * Extrae el nombre del usuario de los datos de customer_information
 */
function extractNameFromCustomerInfo($info) {
    if (isset($info['messaging_customer_information']['screens'])) {
        foreach ($info['messaging_customer_information']['screens'] as $screen) {
            if (isset($screen['responses'])) {
                foreach ($screen['responses'] as $response) {
                    if ($response['key'] === 'name') {
                        return $response['value'];
                    }
                }
            }
        }
    }
    
    return 'Unknown User';
}

// ============================================================
// SISTEMA DE FOTOS DE PERFIL - VERSIÓN MEJORADA
// Threshold: 6 horas por usuario
// ============================================================
 
/**
 * getUserProfilePicture() - VERSIÓN MEJORADA
 * 
 * Estrategia:
 * 1. Busca en live_comments el último registro del usuario con foto
 * 2. Si tiene foto Y fue checkeada hace < 6 horas → devuelve sin validar
 * 3. Si tiene foto pero fue checkeada hace > 6 horas → refresca desde API
 * 4. Si no tiene foto en live_comments → busca en messenger_users (misma lógica)
 * 5. Si no tiene en ningún lado → llama a la API (usuario nuevo)
 * 
 * IMPORTANTE: Cuando refresca, actualiza TODOS los registros del usuario
 * para que la próxima búsqueda siempre encuentre la URL fresca.
 */
 
function getUserProfilePicture($userId, $accessToken, $conn) {
    
    $refreshSeconds = 6 * 3600; // 6 horas entre refrescos
    
    // ─────────────────────────────────────────────
    // PASO 1: Buscar en live_comments
    // ─────────────────────────────────────────────
    $stmt = $conn->prepare("
        SELECT user_picture, profile_picture_checked_at
        FROM live_comments
        WHERE id_user = :id_user 
          AND user_picture IS NOT NULL 
          AND user_picture != ''
        ORDER BY comment_datetime DESC 
        LIMIT 1
    ");
    $stmt->execute([':id_user' => $userId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
 
    if ($row && !empty($row['user_picture'])) {
        // Tiene foto guardada → verificar si necesita refresco
        if (!needsRefresh($row['profile_picture_checked_at'], $refreshSeconds)) {
            // Checkeada hace poco → devolver directo (0 requests externos)
            return $row['user_picture'];
        }
        
        // Necesita refresco → obtener nueva URL
        $newPicture = fetchUserProfilePictureQuick($userId, $accessToken);
        if ($newPicture) {
            updateAllUserPictures($conn, $userId, $newPicture);
            return $newPicture;
        }
        
        // Si la API falló, devolver la que teníamos (mejor una vieja que ninguna)
        // Pero actualizar el timestamp para no reintentar inmediatamente
        touchProfilePictureTimestamp($conn, $userId);
        return $row['user_picture'];
    }
 
    // ─────────────────────────────────────────────
    // PASO 2: Buscar en messenger_users
    // ─────────────────────────────────────────────
    $stmt2 = $conn->prepare("
        SELECT user_picture, profile_picture_checked_at
        FROM messenger_users
        WHERE user_id = :user_id 
          AND user_picture IS NOT NULL 
          AND user_picture != ''
        LIMIT 1
    ");
    $stmt2->execute([':user_id' => $userId]);
    $row2 = $stmt2->fetch(PDO::FETCH_ASSOC);
 
    if ($row2 && !empty($row2['user_picture'])) {
        if (!needsRefresh($row2['profile_picture_checked_at'], $refreshSeconds)) {
            return $row2['user_picture'];
        }
        
        $newPicture = fetchUserProfilePictureQuick($userId, $accessToken);
        if ($newPicture) {
            updateAllUserPictures($conn, $userId, $newPicture);
            return $newPicture;
        }
        
        // API falló → devolver la vieja y tocar el timestamp
        touchMessengerPictureTimestamp($conn, $userId);
        return $row2['user_picture'];
    }
 
    // ─────────────────────────────────────────────
    // PASO 3: Usuario completamente nuevo → API
    // ─────────────────────────────────────────────
    $newPicture = fetchUserProfilePictureQuick($userId, $accessToken);
    // No hay nada que actualizar en BD aún porque el INSERT del comentario
    // se hace DESPUÉS de esta función. La foto se guardará con el INSERT.
    return $newPicture;
}
 
 
/**
 * needsRefresh()
 * Determina si la foto necesita ser refrescada basándose en el timestamp.
 * 
 * @param string|null $checkedAt - Timestamp de último chequeo (formato Y-m-d H:i:s)
 * @param int $refreshSeconds - Segundos antes de considerar obsoleta
 * @return bool - true si necesita refresco
 */
function needsRefresh($checkedAt, $refreshSeconds) {
    // Si nunca se checkeó → necesita refresco
    if (empty($checkedAt)) {
        return true;
    }
    
    try {
        $now = new DateTime('now', new DateTimeZone('Europe/Lisbon'));
        $lastChecked = new DateTime($checkedAt, new DateTimeZone('Europe/Lisbon'));
        $diffSeconds = $now->getTimestamp() - $lastChecked->getTimestamp();
        
        return $diffSeconds >= $refreshSeconds;
    } catch (Exception $e) {
        // Si hay error parseando la fecha → refrescar por seguridad
        return true;
    }
}
 
 
/**
 * updateAllUserPictures()
 * Cuando obtenemos una URL nueva, actualizamos TODOS los sitios donde 
 * se guarda la foto del usuario para mantener consistencia.
 */
function updateAllUserPictures($conn, $userId, $newPictureUrl) {
    $now = date('Y-m-d H:i:s');
    
    // 1. Actualizar el comentario más reciente en live_comments
    try {
        $conn->prepare("
            UPDATE live_comments 
            SET user_picture = :pic, profile_picture_checked_at = :checked_at
            WHERE id_user = :id_user 
            ORDER BY comment_datetime DESC 
            LIMIT 1
        ")->execute([
            ':pic' => $newPictureUrl,
            ':id_user' => $userId,
            ':checked_at' => $now
        ]);
    } catch (Exception $e) {
        error_app("Error updating live_comments picture for user {$userId}: " . $e->getMessage());
    }
    
    // 2. Actualizar en messenger_users
    try {
        $conn->prepare("
            UPDATE messenger_users 
            SET user_picture = :pic, profile_picture_checked_at = :checked_at
            WHERE user_id = :user_id
        ")->execute([
            ':pic' => $newPictureUrl,
            ':user_id' => $userId,
            ':checked_at' => $now
        ]);
    } catch (Exception $e) {
        error_app("Error updating messenger_users picture for user {$userId}: " . $e->getMessage());
    }
 
    // 3. Actualizar en post_comments (último comentario)
    try {
        $conn->prepare("
            UPDATE post_comments 
            SET user_picture = :pic
            WHERE id_user = :id_user 
            ORDER BY comment_datetime DESC 
            LIMIT 1
        ")->execute([
            ':pic' => $newPictureUrl,
            ':id_user' => $userId
        ]);
    } catch (Exception $e) {
        error_app("Error updating post_comments picture for user {$userId}: " . $e->getMessage());
    }
}
 
 
/**
 * touchProfilePictureTimestamp()
 * Si la API falló, actualizamos solo el timestamp para no reintentar 
 * en cada comentario. Reintentará en el próximo ciclo de 6 horas.
 */
function touchProfilePictureTimestamp($conn, $userId) {
    try {
        $conn->prepare("
            UPDATE live_comments 
            SET profile_picture_checked_at = NOW()
            WHERE id_user = :id_user 
            ORDER BY comment_datetime DESC 
            LIMIT 1
        ")->execute([':id_user' => $userId]);
    } catch (Exception $e) {
        // Silenciar - no es crítico
    }
}
 
function touchMessengerPictureTimestamp($conn, $userId) {
    try {
        $conn->prepare("
            UPDATE messenger_users 
            SET profile_picture_checked_at = NOW()
            WHERE user_id = :user_id
        ")->execute([':user_id' => $userId]);
    } catch (Exception $e) {
        // Silenciar - no es crítico
    }
}
 
 
/**
 * fetchUserProfilePictureQuick() - SIN CAMBIOS
 * Timeout agresivo (3s) para no bloquear el webhook.
 */
function fetchUserProfilePictureQuick($userId, $accessToken) {
    $jsonUrl = "https://graph.facebook.com/v22.0/{$userId}/picture?type=large&redirect=0&access_token={$accessToken}";
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $jsonUrl);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, true);
    curl_setopt($ch, CURLOPT_TIMEOUT, 3);
    curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 2);
    $response = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);
    
    if ($httpCode === 200) {
        $data = json_decode($response, true);
        if (isset($data['data']['url'])) {
            return $data['data']['url'];
        }
    }
    return null;
}
 
 
// ============================================================
// FUNCIÓN saveMessengerUser() - SIMPLIFICADA
// Ahora usa la misma lógica centralizada
// ============================================================
 
function saveMessengerUser($conn, $userId, $userName, $messageDate) {
    try {
        $accessTokenManager = new FacebookTokenManager();
        $accessToken = $accessTokenManager->getAccessToken();
        
        // Obtener foto usando la función centralizada
        $userProfilePicture = getUserProfilePicture($userId, $accessToken, $conn);
        
        // Verificar si el usuario ya existe
        $checkStmt = $conn->prepare("
            SELECT COUNT(*) AS count FROM messenger_users WHERE user_id = :user_id
        ");
        $checkStmt->execute([':user_id' => $userId]);
        $userExists = $checkStmt->fetchColumn() > 0;
        
        if (!$userExists) {
            $conn->prepare("
                INSERT INTO messenger_users (
                    user_id, user_name, first_message_date, last_message_date, 
                    user_picture, profile_picture_checked_at
                ) VALUES (
                    :user_id, :user_name, :message_date, :message_date, 
                    :user_picture, NOW()
                )
            ")->execute([
                ':user_id' => $userId,
                ':user_name' => $userName,
                ':message_date' => $messageDate,
                ':user_picture' => $userProfilePicture
            ]);
        } else {
            $conn->prepare("
                UPDATE messenger_users 
                SET last_message_date = :message_date,
                    user_name = COALESCE(:user_name, user_name)
                WHERE user_id = :user_id
            ")->execute([
                ':user_id' => $userId,
                ':user_name' => $userName,
                ':message_date' => $messageDate
            ]);
            // La foto ya se actualizó dentro de getUserProfilePicture() si era necesario
        }
    } catch (Exception $e) {
        error_app("Error saving messenger user: " . $e->getMessage());
    }
}


$app->get('/comments', function (Request $request, Response $response) {
    $searchTerm = $request->getQueryParams()['searchTerm'] ?? '';
    $page = $request->getQueryParams()['page'] ?? 1;
    $limit = $searchTerm ? 200 : (int)($request->getQueryParams()['limit'] ?? 25);
    $offset = (int)($request->getQueryParams()['offset'] ?? 0);

    // Instanciar la base de datos
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");

    if (!$conn) {
        error_app("Database connection failed.");
        return $response->withStatus(500);
    }

    // Obtener los últimos 30 live_id
    $stmt = $conn->query("SELECT live_id FROM latest_live_id ORDER BY id DESC LIMIT 30");
    $lastLiveIds = $stmt->fetchAll(PDO::FETCH_COLUMN);

    if (empty($lastLiveIds)) {
        error_app("No live_id found.");
        return $response->withStatus(500);
    }

    // Crear placeholders para live_ids
    $placeholders = [];
    $params = [];
    foreach ($lastLiveIds as $index => $liveId) {
        $placeholder = ":live_id_$index";
        $placeholders[] = $placeholder;
        $params[$placeholder] = $liveId;
    }
    $liveIdWhereClause = implode(", ", $placeholders);

    // ============================================
    // QUERY 1: Obtener comentarios (optimizada)
    // ============================================
    $query = "
        SELECT 
            lc.id_comment,
            lc.id_user,
            lc.live_id,
            lc.comment_text,
            lc.comment_datetime,
            lc.user_name,
            lc.addedToCart,
            lc.fbcomment_id,
            lc.user_picture,
            lc.provider,
            cm.message AS custom_message,
            CASE WHEN bu.user_id IS NOT NULL THEN 1 ELSE 0 END AS isBlocked,
            ci_agg.added_by
        FROM live_comments AS lc
        LEFT JOIN custom_messages cm ON lc.id_comment = cm.id_comment
        LEFT JOIN blocked_users bu ON lc.id_user = bu.user_id
        LEFT JOIN (
            SELECT id_comment, MAX(added_by) as added_by 
            FROM cart_items 
            GROUP BY id_comment
        ) ci_agg ON lc.id_comment = ci_agg.id_comment
        WHERE lc.live_id IN ($liveIdWhereClause)
    ";

    if ($searchTerm) {
        $query .= " AND (lc.comment_text LIKE :searchTerm OR lc.user_name LIKE :searchTerm)";
        $params[':searchTerm'] = "%$searchTerm%";
    }

    $query .= " ORDER BY lc.comment_datetime DESC LIMIT $limit OFFSET $offset";

    $stmt = $conn->prepare($query);
    $stmt->execute($params);
    $comments = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Si no hay comentarios, retornar array vacío
    if (empty($comments)) {
        $response->getBody()->write(json_encode([]));
        return $response->withHeader('Content-Type', 'application/json')->withStatus(200);
    }

    // ============================================
    // QUERY 2: Obtener TODOS los carritos en una sola query
    // ============================================
    
    // Extraer pares únicos de user_id + live_id
    $userLivePairs = [];
    foreach ($comments as $comment) {
        $key = $comment['id_user'] . '|' . $comment['live_id'];
        if (!isset($userLivePairs[$key])) {
            $userLivePairs[$key] = [
                'user_id' => $comment['id_user'],
                'live_id' => $comment['live_id']
            ];
        }
    }

    // Construir condiciones para la query de carritos
    $cartConditions = [];
    $cartParams = [];
    $i = 0;
    foreach ($userLivePairs as $pair) {
        $cartConditions[] = "(c.id_user = :user_$i AND c.live_id = :live_$i)";
        $cartParams[":user_$i"] = $pair['user_id'];
        $cartParams[":live_$i"] = $pair['live_id'];
        $i++;
    }

    $cartsData = [];

    if (!empty($cartConditions)) {
        $cartQuery = "
            SELECT 
                c.id_cart,
                c.id_user,
                c.user_name,
                c.creation_date,
                c.status,
                c.live_id,
                ci.id_cart_item,
                ci.id_product,
                ci.sku,
                ci.name_product,
                ci.quantity,
                ci.unit_price,
                ci.obs,
                (ci.quantity * ci.unit_price) as total_price_item
            FROM cart c
            INNER JOIN cart_items ci ON ci.id_cart = c.id_cart
            WHERE (" . implode(" OR ", $cartConditions) . ")
            ORDER BY c.id_user, c.live_id, ci.id_cart_item ASC
        ";

        $stmtCarts = $conn->prepare($cartQuery);
        $stmtCarts->execute($cartParams);
        $allCartItems = $stmtCarts->fetchAll(PDO::FETCH_ASSOC);

        // ============================================
        // Estructurar carritos en memoria con lógica de OBS
        // ============================================
        foreach ($allCartItems as $row) {
            $userLiveKey = $row['id_user'] . '|' . $row['live_id'];
            $cartId = $row['id_cart'];

            if (!isset($cartsData[$userLiveKey])) {
                $cartsData[$userLiveKey] = [];
            }

            if (!isset($cartsData[$userLiveKey][$cartId])) {
                $cartsData[$userLiveKey][$cartId] = [
                    'id_cart' => $row['id_cart'],
                    'id_user' => $row['id_user'],
                    'user_name' => $row['user_name'],
                    'creation_date' => $row['creation_date'],
                    'status' => $row['status'],
                    'live_id' => $row['live_id'],
                    'total_items' => 0,
                    'total_price' => 0,
                    'items' => []
                ];
            }

            // Buscar si existe un item con mismo id_product, sku Y obs
            $itemIndex = null;
            foreach ($cartsData[$userLiveKey][$cartId]['items'] as $index => $item) {
                if ($item['id_product'] == $row['id_product'] && 
                    $item['sku'] == $row['sku'] && 
                    $item['obs'] === $row['obs']) {
                    $itemIndex = $index;
                    break;
                }
            }

            if ($itemIndex !== null) {
                // Si existe con mismo OBS, SUMAR las cantidades
                $cartsData[$userLiveKey][$cartId]['items'][$itemIndex]['quantity'] += $row['quantity'];
                $cartsData[$userLiveKey][$cartId]['items'][$itemIndex]['total_price_item'] += $row['total_price_item'];
            } else {
                // Si no existe o tiene diferente OBS, agregar como nuevo item
                $cartsData[$userLiveKey][$cartId]['items'][] = [
                    'id_cart_item' => $row['id_cart_item'],
                    'id_product' => $row['id_product'],
                    'name_product' => $row['name_product'],
                    'quantity' => $row['quantity'],
                    'unit_price' => $row['unit_price'],
                    'total_price_item' => $row['total_price_item'],
                    'sku' => $row['sku'],
                    'obs' => $row['obs']
                ];
            }

            // Siempre actualizar los totales
            $cartsData[$userLiveKey][$cartId]['total_items'] += $row['quantity'];
            $cartsData[$userLiveKey][$cartId]['total_price'] += $row['total_price_item'];
        }
    }

    // ============================================
    // Asignar carritos a cada comentario
    // ============================================
    foreach ($comments as &$comment) {
        $userLiveKey = $comment['id_user'] . '|' . $comment['live_id'];
        
        if (isset($cartsData[$userLiveKey])) {
            $comment['carts'] = array_values($cartsData[$userLiveKey]);
        } else {
            $comment['carts'] = [];
        }
    }

    $response->getBody()->write(json_encode($comments));
    return $response->withHeader('Content-Type', 'application/json')->withStatus(200);
});




$app->get('/getLiveStats', function (Request $request, Response $response) {
    // 1) Conexión y liveId
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    $liveId = $conn
        ->query("SELECT live_id FROM latest_live_id ORDER BY id DESC LIMIT 1")
        ->fetchColumn();
    if (!$liveId) {
        error_app("❌ No se encontró live_id en la base de datos");
        return $response->withStatus(404);
    }

    // 2) Token y Page ID
    $accessToken = (new FacebookTokenManager())->getAccessToken();
    $pageId = (new FacebookTokenManager())->getPageId();
    $postEdge = "{$pageId}_{$liveId}";

    // 3) Helper cURL
    $makeCurl = function (string $url) {
        $ch = curl_init($url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_TIMEOUT, 10);
        $raw = curl_exec($ch);
        $code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        curl_close($ch);
        return $code === 200 ? json_decode($raw, true) : null;
    };

    $stats = [];

    try {
        // 🔹 1. Viewers lifetime (views)
        $videoUrl = "https://graph.facebook.com/{$liveId}?fields=views&access_token={$accessToken}";
        $videoData = $makeCurl($videoUrl);
        $stats['live_viewers'] = isset($videoData['views']) ? intval($videoData['views']) : 0;

        // 🔹 2. Likes
        $likesUrl = "https://graph.facebook.com/{$liveId}?fields=likes.summary(true)&access_token={$accessToken}";
        $likesData = $makeCurl($likesUrl);
        $stats['live_likes'] = isset($likesData['likes']['summary']['total_count']) ? intval($likesData['likes']['summary']['total_count']) : 0;

        // 🔹 3. Comentarios totales
        $commentsUrl = "https://graph.facebook.com/{$liveId}?fields=comments.summary(total_count)&access_token={$accessToken}";
        $commentsData = $makeCurl($commentsUrl);
        $stats['live_comments_count'] = isset($commentsData['comments']['summary']['total_count']) ? intval($commentsData['comments']['summary']['total_count']) : 0;

        // 🔹 4. Shares lifetime (del video específico)
        $sharesUrl = "https://graph.facebook.com/{$postEdge}?fields=shares&access_token={$accessToken}";
        $sharesData = $makeCurl($sharesUrl);
        $stats['live_shares'] = isset($sharesData['shares']['count']) ? intval($sharesData['shares']['count']) : 0;

        // 🔹 5. Top commenters (desde base de datos)
        $stmt = $conn->prepare("
            SELECT user_name, COUNT(*) as count
            FROM live_comments
            WHERE live_id = :liveId
              AND LENGTH(TRIM(comment_text)) > 2
              AND LOWER(TRIM(comment_text)) NOT IN ('partilhado', 'like')
            GROUP BY id_user, user_name
            ORDER BY count DESC
            LIMIT 10
        ");
        $stmt->execute([':liveId' => $liveId]);
        $topCommenters = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stats['top_commenters'] = $topCommenters;
        $stats['top_commenter'] = $topCommenters[0] ?? ['user_name' => null, 'count' => 0];

    } catch (Exception $e) {
        error_app("❌ Error fetching stats: " . $e->getMessage());
        return $response->withStatus(500);
    }

    $payload = json_encode($stats, JSON_UNESCAPED_UNICODE);
    $response->getBody()->write($payload);
    return $response
        ->withHeader('Content-Type', 'application/json')
        ->withStatus(200);
});



$app->post('/postLiveComment', function (Request $request, Response $response) {
    // 1) Leer sólo el mensaje
    $data    = $request->getParsedBody();
    $message = trim($data['message'] ?? '');
    if ($message === '') {
        $payload = ['success' => false, 'error' => 'Falta o message'];
        $response->getBody()->write(json_encode($payload));
        return $response
            ->withHeader('Content-Type','application/json')
            ->withStatus(400);
    }

    // 2) Obtener liveId desde la BD
    $db   = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    $liveId = $conn
      ->query("SELECT live_id FROM latest_live_id ORDER BY id DESC LIMIT 1")
      ->fetchColumn();
    if (!$liveId) {
        $payload = ['success' => false, 'error' => 'Nenhum live_id disponível'];
        $response->getBody()->write(json_encode($payload));
        return $response->withHeader('Content-Type','application/json')->withStatus(404);
    }

    // 3) Obtener token de página
    $accessToken = (new FacebookTokenManager())->getAccessToken();

    // 4) Preparar y ejecutar POST a Graph API
    $url    = "https://graph.facebook.com/{$liveId}/comments";
    $params = [
        'message'      => $message,
        'access_token' => $accessToken
    ];
    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_POST,           true);
    curl_setopt($ch, CURLOPT_POSTFIELDS,     http_build_query($params));
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_TIMEOUT,        10);
    $result   = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);

    // 5) Manejar respuesta
    if ($httpCode !== 200) {
        $error = json_decode($result, true) ?: ['raw' => $result];
        $payload = ['success' => false, 'error' => 'Facebook API error', 'details' => $error];
        $response->getBody()->write(json_encode($payload));
        return $response
            ->withHeader('Content-Type','application/json')
            ->withStatus($httpCode);
    }

    $fbData = json_decode($result, true);
    $payload = ['success' => true, 'facebookResponse' => $fbData];
    $response->getBody()->write(json_encode($payload));
    return $response
        ->withHeader('Content-Type','application/json')
        ->withStatus(200);
});




$app->post('/block-user', function (Request $request, Response $response) {
    $data = $request->getParsedBody();
    $userId = $data['user_id'];
    $liveId = $data['live_id'];
    $commentText = $data['comment'];
    $userName = $data['user_name'];
    $userPicture = $data['user_picture'];

    // Instanciar la base de datos
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");

    if (!$conn) {
        error_app("Database connection failed.");
        return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Database connection failed']));
    }

    // 1. Obtener el token de acceso y el page ID
    $accessTokenManager = new FacebookTokenManager();
    $accessToken = $accessTokenManager->getAccessToken();
    $pageId = $accessTokenManager->getPageId();


    // 2. Banear al usuario de la página en Facebook
    $banUrl = "https://graph.facebook.com/v21.0/{$pageId}/blocked?access_token={$accessToken}";
    $data = json_encode(['asid' => [$userId]]); // Suponiendo que el userId es App Scoped



    try {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $banUrl);
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
        curl_setopt($ch, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        $result = curl_exec($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);

        error_app("Facebook Response: " . $result);
        error_app("HTTP Code: " . $httpCode);

        if ($httpCode !== 200) {
            return $response->withStatus(500)->write(json_encode([
                'success' => false,
                'error' => 'Facebook API error',
                'http_code' => $httpCode,
                'fb_response' => json_decode($result, true)
            ]));
        }

        // 3. Solo si el bloqueo en Facebook fue exitoso, bloquear al usuario en la base de datos
        $stmt = $conn->prepare("
            INSERT INTO blocked_users (user_id, live_id, comment_text, user_name, user_picture) 
            VALUES (:user_id, :live_id, :comment_text, :user_name, :user_picture)
        ");
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':live_id', $liveId);
        $stmt->bindParam(':comment_text', $commentText);
        $stmt->bindParam(':user_name', $userName);
        $stmt->bindParam(':user_picture', $userPicture);
        $stmt->execute();


        // 4. Enviar el mensaje al vivo indicando que el usuario fue bloqueado
        $messageUrl = "https://graph.facebook.com/v21.0/{$liveId}/comments?access_token={$accessToken}";
        $messageData = json_encode(['message' => "{$userName} foi bloqueado!"]);



        $chMessage = curl_init();
        curl_setopt($chMessage, CURLOPT_URL, $messageUrl);
        curl_setopt($chMessage, CURLOPT_POST, 1);
        curl_setopt($chMessage, CURLOPT_POSTFIELDS, $messageData);
        curl_setopt($chMessage, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
        curl_setopt($chMessage, CURLOPT_RETURNTRANSFER, true);
        $messageResult = curl_exec($chMessage);
        $messageHttpCode = curl_getinfo($chMessage, CURLINFO_HTTP_CODE);
        curl_close($chMessage);


        if ($messageHttpCode === 200) {
            error_app("Facebook live message sent: {$userName} foi bloqueado!");
        } else {
            error_app("Failed to send live message. HTTP Code: " . $messageHttpCode);
        }

        $response->getBody()->write(json_encode(['success' => true, 'message' => 'User blocked permanently']));
        return $response->withHeader('Content-Type', 'application/json')->withStatus(200);
        
    } catch (PDOException $e) {
        error_app("Database error: " . $e->getMessage());
        return $response->withStatus(500)->write(json_encode([
            'success' => false,
            'error' => 'Database error',
            'details' => $e->getMessage()
        ]));
    } catch (Exception $e) {
        error_app("General error: " . $e->getMessage());
        return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Banning error']));
    }
});




$app->post('/unblock-user', function (Request $request, Response $response) {
    $data = $request->getParsedBody();
    $userId = $data['user_id'];
    $liveId = $data['live_id'];  // Necesitamos el liveId para enviar el comentario
    $userName = $data['user_name'];

    // Instanciar la base de datos
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");

    if (!$conn) {
        error_app("Database connection failed.");
        return $response->withStatus(500);
    }

    // 1. Obtener el token de acceso y el page ID
    $accessTokenManager = new FacebookTokenManager();
    $accessToken = $accessTokenManager->getAccessToken();
    $pageId = $accessTokenManager->getPageId();

    // 2. Desbanear al usuario de la página en Facebook
    $unbanUrl = "https://graph.facebook.com/v21.0/{$pageId}/blocked?user={$userId}&access_token={$accessToken}";

    try {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $unbanUrl);
        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "DELETE");
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        $result = curl_exec($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        curl_close($ch);

        if ($httpCode === 200) {
            // 3. Eliminar el registro de bloqueo de la base de datos SOLO si el desbloqueo en Facebook fue exitoso
            try {
                $stmt = $conn->prepare("DELETE FROM blocked_users WHERE user_id = :user_id");
                $stmt->bindParam(':user_id', $userId);
                $stmt->execute();
            } catch (PDOException $e) {
                error_app("Database error: " . $e->getMessage());
                return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Database error']));
            }

            // 4. Enviar un comentario al video en vivo indicando que el usuario fue desbloqueado
            $commentUrl = "https://graph.facebook.com/{$liveId}/comments?access_token={$accessToken}";
            $message = "{$userName} foi desbloqueado!";
            $commentData = json_encode(['message' => $message]);

            $chComment = curl_init();
            curl_setopt($chComment, CURLOPT_URL, $commentUrl);
            curl_setopt($chComment, CURLOPT_POST, 1);
            curl_setopt($chComment, CURLOPT_POSTFIELDS, $commentData);
            curl_setopt($chComment, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
            curl_setopt($chComment, CURLOPT_RETURNTRANSFER, true);
            $commentResult = curl_exec($chComment);
            $commentHttpCode = curl_getinfo($chComment, CURLINFO_HTTP_CODE);
            curl_close($chComment);

            if ($commentHttpCode === 200) {
                $response->getBody()->write(json_encode(['success' => true, 'message' => 'User unblocked and comment posted']));
                return $response->withHeader('Content-Type', 'application/json')->withStatus(200);
            } else {
                error_app("Error posting comment on live video. HTTP Code: " . $commentHttpCode);
                return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Comment post error']));
            }
        } else {
            error_app("Error unblocking user on Facebook. HTTP Code: " . $httpCode);
            return $response->withStatus(500)->write(json_encode([
                'success' => false,
                'error' => 'Facebook API error',
                'http_code' => $httpCode,
                'fb_response' => json_decode($result, true)
            ]));
        }
    } catch (Exception $e) {
        error_app("Error unblocking user: " . $e->getMessage());
        return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Unbanning error']));
    }
});


$app->get('/getUsersBlocked', function (Request $request, Response $response) {

    // Instanciar la base de datos
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");

    if (!$conn) {
        error_app("Database connection failed.");
        return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Database connection failed']));
    }

    // Consulta para obtener todos los usuarios bloqueados
    try {
        $stmt = $conn->prepare("SELECT * FROM blocked_users ORDER BY blocked_at DESC");
        $stmt->execute();
        $blockedUsers = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if ($blockedUsers) {
            // Devolver los usuarios bloqueados en formato JSON
            return $response->withHeader('Content-Type', 'application/json')->withStatus(200)->write(json_encode([
                'success' => true,
                'data' => $blockedUsers
            ]));
        } else {
            // No hay usuarios bloqueados
            return $response->withHeader('Content-Type', 'application/json')->withStatus(200)->write(json_encode([
                'success' => true,
                'data' => []
            ]));
        }
    } catch (PDOException $e) {
        error_app("Database error: " . $e->getMessage());
        return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Database error']));
    }
});




$app->post('/registerDevice', function (Request $request, Response $response) {
    // Conectar a la base de datos
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");

    if (!$conn) {
        error_app("Database connection failed.");
        return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Database connection failed']));
    }

    // Obtener los datos enviados en el cuerpo de la solicitud
    $input = json_decode($request->getBody(), true);
    $userId = $input['userId'] ?? null;
    $token = $input['token'] ?? null;

    // Validar los valores
    if (!$userId || !$token || !is_string($token)) {
        return $response->withStatus(400)->write(json_encode(['success' => false, 'error' => 'Faltan parámetros o el formato del token es inválido']));
    }

    try {
        $conn->beginTransaction();

        // Eliminar tokens existentes para este userId en la tabla 'devices'
        $stmt = $conn->prepare("DELETE FROM devices WHERE user_id = :userId");
        $stmt->bindParam(':userId', $userId, PDO::PARAM_STR);
        $stmt->execute();

        // Insertar el nuevo token en la tabla 'devices'
        $stmt = $conn->prepare("INSERT INTO devices (user_id, token) VALUES (:userId, :token)");
        $stmt->bindParam(':userId', $userId, PDO::PARAM_STR);
        $stmt->bindParam(':token', $token, PDO::PARAM_STR);
        $stmt->execute();

        // Verificar si el user_id existe en la tabla users_app
        $stmt = $conn->prepare("SELECT id FROM users_app WHERE user_id = :userId");
        $stmt->bindParam(':userId', $userId, PDO::PARAM_STR);
        $stmt->execute();
        $userExists = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($userExists) {
            // Actualizar el token en la tabla users_app
            $stmt = $conn->prepare("UPDATE users_app SET token = :token WHERE user_id = :userId");
            $stmt->bindParam(':userId', $userId, PDO::PARAM_STR);
            $stmt->bindParam(':token', $token, PDO::PARAM_STR);
            $stmt->execute();
        }

        $conn->commit();

        return $response->withHeader('Content-Type', 'application/json')->withStatus(200)->write(json_encode([
            'success' => true,
            'message' => 'Dispositivo registrado y token actualizado correctamente'
        ]));
    } catch (PDOException $e) {
        $conn->rollBack();
        error_app("Database error: " . $e->getMessage());
        return $response->withStatus(500)->write(json_encode([
            'success' => false,
            'error' => 'Database error: ' . $e->getMessage()
        ]));
    }
});






$app->get('/notifications/{userId}', function (Request $request, Response $response, array $args) {
    $userId = $args['userId'];

    // Conectar a la base de datos
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");

    if (!$conn) {
        error_app("Database connection failed.");
        return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Database connection failed']));
    }

    try {
        // Obtener todos los IDs sociales del usuario
        $allIds = getAllUserSocialIds($conn, $userId);
        $placeholders = implode(',', array_fill(0, count($allIds), '?'));

        $stmt = $conn->prepare("SELECT * FROM notifications WHERE user_id IN ($placeholders) ORDER BY date DESC");
        $stmt->execute($allIds);
        $notifications = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return $response->withHeader('Content-Type', 'application/json')
                        ->write(json_encode(['success' => true, 'notifications' => $notifications]));
    } catch (PDOException $e) {
        error_app("Database error: " . $e->getMessage());
        return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Database error']));
    }
});


//El type no sirve en este endpoint, hay que arreglarlo mas adelante pero asi funciona
$app->get('/notifications/count/{userId}/{type}', function (Request $request, Response $response, array $args) {
    $userId = $args['userId'];
    $type = $args['type'];

    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");

    if (!$conn) {
        error_app("Database connection failed.");
        return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Database connection failed']));
    }

    try {
        $allIds = getAllUserSocialIds($conn, $userId);
        $placeholders = implode(',', array_fill(0, count($allIds), '?'));

        $stmt = $conn->prepare("SELECT COUNT(*) as unread_count FROM notifications WHERE user_id IN ($placeholders) AND status = 'unread'");
        $stmt->execute($allIds);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return $response->withHeader('Content-Type', 'application/json')
                        ->write(json_encode(['success' => true, 'unread_count' => $result['unread_count']]));
    } catch (PDOException $e) {
        error_app("Database error: " . $e->getMessage());
        return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Database error']));
    }
});


$app->put('/notifications/mark-as-read/{userId}', function (Request $request, Response $response, array $args) {
    $userId = $args['userId'];

    // Conectar a la base de datos
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");

    if (!$conn) {
        error_app("Database connection failed.");
        return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Database connection failed']));
    }

    try {
        $allIds = getAllUserSocialIds($conn, $userId);
        $placeholders = implode(',', array_fill(0, count($allIds), '?'));

        $stmt = $conn->prepare("UPDATE notifications SET status = 'read' WHERE user_id IN ($placeholders) AND status = 'unread'");
        $stmt->execute($allIds);

        return $response->withHeader('Content-Type', 'application/json')
                        ->write(json_encode(['success' => true, 'message' => 'Notificaciones marcadas como leídas']));
    } catch (PDOException $e) {
        error_app("Database error: " . $e->getMessage());
        return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Database error']));
    }
});



$app->post('/game/save-winner', function (Request $request, Response $response) {
    // Obtener datos del cuerpo de la solicitud
    $data = $request->getParsedBody();
    $idComment = $data['id_comment'] ?? null;
    $idLive = $data['id_live'] ?? null;
    $gameStartDatetime = $data['game_start_datetime'] ?? null;
    $winnerDatetime = $data['winner_datetime'] ?? null;
    $winningNumberText = $data['winning_number_text'] ?? null;
    $prizeDescription = $data['prize_description'] ?? null;
    $winnerName = $data['winner_name'] ?? null;

    if (!$idComment || !$idLive || !$gameStartDatetime || !$winnerDatetime || !$winningNumberText || !$prizeDescription || !$winnerName) {
        return $response->withStatus(400)->write(json_encode(['success' => false, 'error' => 'Missing required fields']));
    }

    // Conectar a la base de datos
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");

    if (!$conn) {
        error_app("Database connection failed.");
        return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Database connection failed']));
    }

    try {
        $conn->beginTransaction();

        // Insertar los datos del ganador en la tabla winner_records
        $stmt = $conn->prepare("
            INSERT INTO winner_records (id_comment, id_live, game_start_datetime, winner_datetime, winning_number_text, prize_description, winner_name)
            VALUES (:id_comment, :id_live, :game_start_datetime, :winner_datetime, :winning_number_text, :prize_description, :winner_name)
        ");
        $stmt->bindParam(':id_comment', $idComment, PDO::PARAM_STR);
        $stmt->bindParam(':id_live', $idLive, PDO::PARAM_STR);
        $stmt->bindParam(':game_start_datetime', $gameStartDatetime, PDO::PARAM_STR);
        $stmt->bindParam(':winner_datetime', $winnerDatetime, PDO::PARAM_STR);
        $stmt->bindParam(':winning_number_text', $winningNumberText, PDO::PARAM_STR);
        $stmt->bindParam(':prize_description', $prizeDescription, PDO::PARAM_STR);
        $stmt->bindParam(':winner_name', $winnerName, PDO::PARAM_STR);
        $stmt->execute();

        // Enviar mensaje al video en vivo
        $accessTokenManager = new FacebookTokenManager();
        $accessToken = $accessTokenManager->getAccessToken();

        $message = "Parabéns 🎉 {$winnerName} acertou no número {$winningNumberText} vencedor 🥇 acabou de ganhar {$prizeDescription}!";
        $messageUrl = "https://graph.facebook.com/v21.0/{$idLive}/comments?access_token={$accessToken}";
        $messageData = json_encode(['message' => $message]);

        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $messageUrl);
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $messageData);
        curl_setopt($ch, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

        $messageResult = curl_exec($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        curl_close($ch);

        if ($httpCode !== 200) {
            error_app("Error sending message to live video: " . json_encode($messageResult));
        } 

        $conn->commit();

        return $response->withHeader('Content-Type', 'application/json')
                        ->write(json_encode(['success' => true, 'message' => 'Winner saved and message sent successfully']));
    } catch (PDOException $e) {
        $conn->rollBack();

        // Registrar y devolver detalles del error
        error_app("Database error: " . $e->getMessage());
        return $response->withStatus(500)->write(json_encode([
            'success' => false,
            'error' => 'Database error'
        ]));
    }
});




$app->get('/getUsers', function (Request $request, Response $response) {
    // Instanciar la base de datos
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");

    // Verificar conexión a la base de datos
    if (!$conn) {
        error_app("Database connection failed.");
        return $response->withStatus(500)->withHeader('Content-Type', 'application/json')->write(json_encode([
            'success' => false,
            'error' => 'Database connection failed'
        ]));
    }

    try {
        // Consulta para obtener los datos relevantes de los usuarios
        $stmt = $conn->prepare("SELECT id, first_name, last_name, email, token FROM users_app");
        $stmt->execute();
        $users = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Verificar si se encontraron usuarios
        if ($users && count($users) > 0) {
            // Formatear los datos para que sean más útiles en el frontend
            $formattedUsers = array_map(function ($user) {
                return [
                    'id' => $user['id'],
                    'label' => "{$user['first_name']} {$user['last_name']} ({$user['email']})",
                    'token' => $user['token']
                ];
            }, $users);

            // Responder con los usuarios encontrados
            return $response->withHeader('Content-Type', 'application/json')->withStatus(200)->write(json_encode([
                'success' => true,
                'data' => $formattedUsers
            ]));
        } else {
            // Responder con una lista vacía si no hay usuarios
            return $response->withHeader('Content-Type', 'application/json')->withStatus(200)->write(json_encode([
                'success' => true,
                'data' => []
            ]));
        }
    } catch (PDOException $e) {
        // Registrar el error en los logs y responder con un mensaje de error
        error_app("Database error: " . $e->getMessage());
        return $response->withStatus(500)->withHeader('Content-Type', 'application/json')->write(json_encode([
            'success' => false,
            'error' => 'Database error: ' . $e->getMessage()
        ]));
    }
});




$app->get('/comments-out-of-stock', function (Request $request, Response $response) {
    $params = $request->getQueryParams();
    $searchTerm = $params['searchTerm'] ?? '';
    $skuFilter = $params['skus'] ?? '';
    $startDate = $params['startDate'] ?? null;
    $endDate = $params['endDate'] ?? null;
    $skuArray = array_filter(array_map('trim', explode(',', $skuFilter)));

    $page = $params['page'] ?? 1;
    $limit = $searchTerm || $skuArray || $startDate || $endDate ? 200 : 25;
    $offset = (int)($params['offset'] ?? 0);

    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    if (!$conn) return $response->withStatus(500);

    $bindsBot = [];
    $bindsManual = [];

    // (1) Filtro por fechas
    $dateConditionBot = '';
    $dateConditionManual = '';
    $liveIdConditionBot = '';
    $liveIdConditionManual = '';
    
    if ($startDate && $endDate) {
        $dateConditionBot = "lc.comment_datetime BETWEEN :bot_startDate AND :bot_endDate";
        $bindsBot[':bot_startDate'] = $startDate . ' 00:00:00';
        $bindsBot[':bot_endDate'] = $endDate . ' 23:59:59';
        
        $dateConditionManual = "lc.comment_datetime BETWEEN :man_startDate AND :man_endDate";
        $bindsManual[':man_startDate'] = $startDate . ' 00:00:00';
        $bindsManual[':man_endDate'] = $endDate . ' 23:59:59';
    } elseif ($startDate) {
        $dateConditionBot = "lc.comment_datetime >= :bot_startDate";
        $bindsBot[':bot_startDate'] = $startDate . ' 00:00:00';
        
        $dateConditionManual = "lc.comment_datetime >= :man_startDate";
        $bindsManual[':man_startDate'] = $startDate . ' 00:00:00';
    } elseif ($endDate) {
        $dateConditionBot = "lc.comment_datetime <= :bot_endDate";
        $bindsBot[':bot_endDate'] = $endDate . ' 23:59:59';
        
        $dateConditionManual = "lc.comment_datetime <= :man_endDate";
        $bindsManual[':man_endDate'] = $endDate . ' 23:59:59';
    } else {
        $stmt = $conn->query("SELECT live_id FROM latest_live_id ORDER BY id DESC LIMIT 10");
        $liveIds = $stmt->fetchAll(PDO::FETCH_COLUMN);
        if (empty($liveIds)) return $response->withStatus(404);

        foreach ($liveIds as $i => $id) {
            $bindsBot[":bot_live_id_$i"] = $id;
            $bindsManual[":man_live_id_$i"] = $id;
        }
        $botPlaceholders = implode(',', array_map(function($i) { return ":bot_live_id_$i"; }, array_keys($liveIds)));
        $manPlaceholders = implode(',', array_map(function($i) { return ":man_live_id_$i"; }, array_keys($liveIds)));
        
        $liveIdConditionBot = "lc.live_id IN ($botPlaceholders)";
        $liveIdConditionManual = "osm.live_id IN ($manPlaceholders)";
    }

    // (2) Filtro por búsqueda
    $searchConditionBot = '';
    $searchConditionManual = '';
    if ($searchTerm) {
        $searchConditionBot = "(lc.comment_text LIKE :bot_search OR lc.user_name LIKE :bot_search)";
        $bindsBot[':bot_search'] = "%$searchTerm%";
        
        $searchConditionManual = "(lc.comment_text LIKE :man_search OR lc.user_name LIKE :man_search)";
        $bindsManual[':man_search'] = "%$searchTerm%";
    }

    // (3) Filtro por SKU
    $skuConditionBot = '';
    $skuConditionManual = '';
if (!empty($skuArray)) {
    $botSkuPh = [];
    $manSkuPh = [];
    foreach ($skuArray as $i => $sku) {
        $botSkuPh[] = ":bot_sku_$i";
        $bindsBot[":bot_sku_$i"] = $sku;
        
        $manSkuPh[] = ":man_sku_$i";
        $bindsManual[":man_sku_$i"] = $sku;
    }
    $skuConditionBot = "lc.sku IN (" . implode(',', $botSkuPh) . ")";
    $skuConditionManual = "osm.sku IN (" . implode(',', $manSkuPh) . ")";
}

    // === CONSTRUIR QUERIES ===
    
    $whereBot = array_filter([$dateConditionBot, $liveIdConditionBot, $searchConditionBot, $skuConditionBot]);
    $whereManual = array_filter([$dateConditionManual, $liveIdConditionManual, $searchConditionManual, $skuConditionManual]);

    $sql1 = "
        SELECT 
            lc.id_comment, lc.id_user, lc.live_id, lc.comment_text,
            lc.comment_datetime, lc.user_name, lc.fbcomment_id, lc.user_picture,
            lc.sku, lc.name_product, lc.unit_price, lc.quantity,
            'bot' as source,
            cm.message AS custom_message,
            NULL AS message_available_sent_at,
            CASE WHEN bu.user_id IS NOT NULL THEN 1 ELSE 0 END AS isBlocked
        FROM live_comments_out_of_stock AS lc
        LEFT JOIN custom_messages cm ON lc.id_comment = cm.id_comment
        LEFT JOIN blocked_users bu ON lc.id_user = bu.user_id
        " . (count($whereBot) ? 'WHERE ' . implode(' AND ', $whereBot) : '');

    $sql2 = "
        SELECT 
            lc.id_comment, lc.id_user, osm.live_id, lc.comment_text,
            lc.comment_datetime, lc.user_name, lc.fbcomment_id, lc.user_picture,
            COALESCE(osm.sku, '') as sku, osm.name_product, osm.unit_price, COALESCE(osm.quantity, 1) as quantity,
            'manual' as source,
            cm.message AS custom_message,
            NULL AS message_available_sent_at,
            CASE WHEN bu.user_id IS NOT NULL THEN 1 ELSE 0 END AS isBlocked
        FROM out_of_stock_messages AS osm
        INNER JOIN live_comments AS lc ON osm.id_comment = lc.id_comment
        LEFT JOIN custom_messages cm ON lc.id_comment = cm.id_comment
        LEFT JOIN blocked_users bu ON lc.id_user = bu.user_id
        " . (count($whereManual) ? 'WHERE ' . implode(' AND ', $whereManual) : '');

    $sql = "
        SELECT * FROM (
            ($sql1)
            UNION ALL
            ($sql2)
        ) AS combined_results
        ORDER BY comment_datetime DESC
        LIMIT $limit OFFSET $offset
    ";

    $allBinds = array_merge($bindsBot, $bindsManual);

    $stmt = $conn->prepare($sql);
    $stmt->execute($allBinds);
    $comments = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Agregar carritos para cada comentario
    foreach ($comments as &$comment) {
        $stmtCarts = $conn->prepare("
            SELECT DISTINCT
                c.id_cart, c.id_user, c.user_name, c.creation_date, c.status, c.live_id,
                ci.id_product, ci.sku, ci.name_product, ci.quantity, ci.unit_price,
                (ci.quantity * ci.unit_price) as total_price_item
            FROM cart as c
            INNER JOIN cart_items as ci ON ci.id_cart = c.id_cart
            WHERE c.id_user = :id_user AND c.live_id = :live_id
        ");
        $stmtCarts->execute([
            ':id_user' => $comment['id_user'],
            ':live_id' => $comment['live_id']
        ]);
        $rows = $stmtCarts->fetchAll(PDO::FETCH_OBJ);

        $carts = [];
        foreach ($rows as $r) {
            if (!isset($carts[$r->id_cart])) {
                $carts[$r->id_cart] = [
                    'id_cart' => $r->id_cart,
                    'id_user' => $r->id_user,
                    'user_name' => $r->user_name,
                    'creation_date' => $r->creation_date,
                    'status' => $r->status,
                    'live_id' => $r->live_id,
                    'total_items' => 0,
                    'total_price' => 0,
                    'items' => []
                ];
            }

            $carts[$r->id_cart]['items'][] = [
                'id_product' => $r->id_product,
                'name_product' => $r->name_product,
                'quantity' => $r->quantity,
                'unit_price' => $r->unit_price,
                'total_price_item' => $r->total_price_item,
                'sku' => $r->sku
            ];

            $carts[$r->id_cart]['total_items'] += $r->quantity;
            $carts[$r->id_cart]['total_price'] += $r->total_price_item;
        }

        $comment['carts'] = array_values($carts);
        $comment['addedToCart'] = !empty($carts) ? 1 : null;
    }

    return $response->withJson($comments)->withHeader('Content-Type', 'application/json');
});



$app->get('/available-out-of-stock-skus', function (Request $request, Response $response) {
    $params = $request->getQueryParams();
    $startDate = $params['startDate'] ?? null;
    $endDate   = $params['endDate'] ?? null;

    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");

    if (!$conn) {
        return $response->withJson(['success' => false, 'message' => 'Falha na conexão.'], 500);
    }

    $binds1 = [];
    $binds2 = [];
    $where1 = "lc.sku IS NOT NULL AND lc.sku != ''";
    $where2 = "osm.sku IS NOT NULL AND osm.sku != ''";

    if ($startDate && $endDate) {
        $where1 .= " AND lc.comment_datetime BETWEEN :s1_start AND :s1_end";
        $binds1[':s1_start'] = $startDate . ' 00:00:00';
        $binds1[':s1_end'] = $endDate . ' 23:59:59';

        $where2 .= " AND lc2.comment_datetime BETWEEN :s2_start AND :s2_end";
        $binds2[':s2_start'] = $startDate . ' 00:00:00';
        $binds2[':s2_end'] = $endDate . ' 23:59:59';
    } else {
        $stmtLive = $conn->query("SELECT live_id FROM latest_live_id ORDER BY id DESC LIMIT 30");
        $liveIds = $stmtLive->fetchAll(PDO::FETCH_COLUMN);

        if (empty($liveIds)) {
            return $response->withJson(['success' => true, 'skus' => []]);
        }

        foreach ($liveIds as $i => $id) {
            $binds1[":s1_live_$i"] = $id;
            $binds2[":s2_live_$i"] = $id;
        }

        $ph1 = implode(',', array_map(function($i) { return ":s1_live_$i"; }, array_keys($liveIds)));
        $ph2 = implode(',', array_map(function($i) { return ":s2_live_$i"; }, array_keys($liveIds)));

        $where1 .= " AND lc.live_id IN ($ph1)";
        $where2 .= " AND osm.live_id IN ($ph2)";
    }

    $sql = "
        SELECT DISTINCT ref FROM (
            SELECT lc.sku AS ref
            FROM live_comments_out_of_stock lc
            WHERE $where1

            UNION

            SELECT osm.sku AS ref
            FROM out_of_stock_messages osm
            INNER JOIN live_comments lc2 ON osm.id_comment = lc2.id_comment
            WHERE $where2
        ) AS combined
        ORDER BY ref ASC
    ";

    $allBinds = array_merge($binds1, $binds2);
    $stmt = $conn->prepare($sql);
    $stmt->execute($allBinds);
    $skus = $stmt->fetchAll(PDO::FETCH_COLUMN);

    return $response->withJson(['success' => true, 'skus' => $skus]);
});



$app->get('/filtered-out-of-stock-products', function (Request $request, Response $response) {
    $params = $request->getQueryParams();
    $searchTerm = $params['searchTerm'] ?? '';
    $skuFilter = $params['skus'] ?? '';
    $startDate = $params['startDate'] ?? null;
    $endDate = $params['endDate'] ?? null;
    $skuArray = array_filter(array_map('trim', explode(',', $skuFilter)));

    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    if (!$conn) return $response->withStatus(500);

    $where = [];
    $binds = [];

    // Filtro por fechas
    if ($startDate && $endDate) {
        $where[] = "comment_datetime BETWEEN :startDate AND :endDate";
        $binds[':startDate'] = $startDate . ' 00:00:00';
        $binds[':endDate'] = $endDate . ' 23:59:59';
    } elseif ($startDate) {
        $where[] = "comment_datetime >= :startDate";
        $binds[':startDate'] = $startDate . ' 00:00:00';
    } elseif ($endDate) {
        $where[] = "comment_datetime <= :endDate";
        $binds[':endDate'] = $endDate . ' 23:59:59';
    }

    // Filtro por búsqueda libre
    if ($searchTerm) {
        $where[] = "(comment_text LIKE :searchTerm OR user_name LIKE :searchTerm)";
        $binds[':searchTerm'] = "%$searchTerm%";
    }

    // Filtro por SKU
    if (!empty($skuArray)) {
        $skuPlaceholders = [];
        foreach ($skuArray as $i => $sku) {
            $ph = ":sku_$i";
            $skuPlaceholders[] = $ph;
            $binds[$ph] = $sku;
        }
        $where[] = "sku IN (" . implode(',', $skuPlaceholders) . ")";
    }

    // Armar query agrupada
    $sql = "
        SELECT name_product, unit_price, SUM(quantity) AS quantity
        FROM live_comments_out_of_stock
        " . (count($where) ? 'WHERE ' . implode(' AND ', $where) : '') . "
        GROUP BY name_product, unit_price
        ORDER BY name_product ASC
    ";

    $stmt = $conn->prepare($sql);
    $stmt->execute($binds);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    return $response->withJson([
        'success' => true,
        'products' => $rows
    ]);
});



$app->get('/game/winners', function (Request $request, Response $response) {
    // Obtener parámetros opcionales
    $queryParams = $request->getQueryParams();
    $limit = $queryParams['limit'] ?? 10;
    $offset = $queryParams['offset'] ?? 0;
    
    // Conectar a la base de datos
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    if (!$conn) {
        error_app("Database connection failed.");
        return $response->withStatus(500)->write(json_encode(['success' => false, 'error' => 'Database connection failed']));
    }
    
    try {
        // Obtener los ganadores más recientes
        $stmt = $conn->prepare("
            SELECT 
                id,
                id_comment,
                id_live,
                game_start_datetime,
                winner_datetime,
                winning_number_text,
                prize_description,
                winner_name,
                created_at
            FROM winner_records
            ORDER BY winner_datetime DESC
            LIMIT :limit OFFSET :offset
        ");
        
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        
        $winners = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Obtener el total de ganadores
        $countStmt = $conn->prepare("SELECT COUNT(*) as total FROM winner_records");
        $countStmt->execute();
        $total = $countStmt->fetch(PDO::FETCH_ASSOC)['total'];
        
        return $response->withHeader('Content-Type', 'application/json')
                        ->write(json_encode([
                            'success' => true,
                            'winners' => $winners,
                            'total' => $total,
                            'limit' => $limit,
                            'offset' => $offset
                        ]));
                        
    } catch (PDOException $e) {
        error_app("Database error: " . $e->getMessage());
        return $response->withStatus(500)->write(json_encode([
            'success' => false,
            'error' => 'Database error'
        ]));
    }
});



$app->get('/post-comments', function (Request $request, Response $response) {
    $searchTerm = $request->getQueryParams()['searchTerm'] ?? '';
    $productFilter = $request->getQueryParams()['productFilter'] ?? 'all';
    $page = $request->getQueryParams()['page'] ?? 1;
    $limit = (int)($request->getQueryParams()['limit'] ?? 10);
    $offset = (int)($request->getQueryParams()['offset'] ?? 0);

    // Si se está filtrando, usar 200 registros, si no, el límite predeterminado
    if ($searchTerm) {
        $limit = 200;
    } else {
        $limit = (int)($request->getQueryParams()['limit'] ?? 25);
    }

    // Instanciar la base de datos
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");

    if (!$conn) {
        error_app("Database connection failed.");
        return $response->withStatus(500);
    }

    // Construir la consulta principal
    $query = "
        SELECT 
            pc.*, 
            cm.message AS custom_message, 
            CASE 
                WHEN bu.user_id IS NOT NULL THEN 1 
                ELSE 0 
            END AS isBlocked,
            MAX(ci.added_by) AS added_by  
        FROM post_comments AS pc
        LEFT JOIN custom_messages cm ON pc.id_comment = cm.id_comment
        LEFT JOIN blocked_users bu ON pc.id_user = bu.user_id
        LEFT JOIN cart_items ci ON pc.id_comment = ci.id_comment";

    // Construir WHERE basado en el filtro de productos
    $whereConditions = [];
    $params = [];

    // Aplicar filtro de productos
    if ($productFilter === 'with-products') {
        $whereConditions[] = "pc.addedToCart = 1";
    } else if ($productFilter === 'without-products') {
        $whereConditions[] = "(pc.addedToCart IS NULL OR pc.addedToCart = 0)";
    }
    // Si es 'all', no agregamos condición

    // Agregar el filtro de búsqueda
    if ($searchTerm) {
        $whereConditions[] = "(pc.comment_text LIKE :searchTerm OR pc.user_name LIKE :searchTerm)";
        $params[':searchTerm'] = "%$searchTerm%";
    }

    // Aplicar las condiciones WHERE si existen
    if (!empty($whereConditions)) {
        $query .= " WHERE " . implode(" AND ", $whereConditions);
    }

    // Agregar GROUP BY, ORDER BY y LIMIT
    $query .= " GROUP BY pc.id_comment";
    $query .= " ORDER BY pc.comment_datetime DESC LIMIT $limit OFFSET $offset";

    // Preparar y ejecutar la consulta
    $stmt = $conn->prepare($query);
    $stmt->execute($params);

    // Obtener resultados
    $comments = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Obtener SOLO los items agregados por cada comentario específico
    foreach ($comments as &$comment) {
        $userId = $comment['id_user'];
        $commentId = $comment['id_comment'];
        
        // Obtener solo los items del carrito que fueron agregados por este comentario específico
        $stmtCarts = $conn->prepare("
            SELECT DISTINCT
                c.id_cart,
                c.id_user,
                c.user_name,
                c.creation_date,
                c.status,
                c.live_id,
                c.sent_at,
                ci.id_cart_item,
                ci.id_product,
                ci.sku,
                ci.name_product,
                ci.quantity,
                ci.unit_price,
                ci.obs,
                ci.id_comment as cart_item_comment_id,
                ci.added_by as item_added_by,
                (ci.quantity * ci.unit_price) as total_price_item
            FROM cart as c
            INNER JOIN cart_items as ci ON ci.id_cart = c.id_cart
            WHERE c.id_user = :id_user 
            AND ci.id_comment = :id_comment
            ORDER BY ci.id_cart_item ASC
        ");
        
        $stmtCarts->bindParam(':id_user', $userId, PDO::PARAM_STR);
        $stmtCarts->bindParam(':id_comment', $commentId, PDO::PARAM_STR);
        $stmtCarts->execute();
        $result = $stmtCarts->fetchAll(PDO::FETCH_OBJ);

        // Estructurar los datos en un array por carrito
        $carts = [];
        foreach ($result as $row) {
            if (!isset($carts[$row->id_cart])) {
                $carts[$row->id_cart] = [
                    'id_cart' => $row->id_cart,
                    'id_user' => $row->id_user,
                    'user_name' => $row->user_name,
                    'creation_date' => $row->creation_date,
                    'status' => $row->status,
                    'live_id' => $row->live_id,
                    'sent_at' => $row->sent_at,
                    'total_items' => 0,
                    'total_price' => 0,
                    'items' => []
                ];
            }

            // Agregar el item al carrito
            $carts[$row->id_cart]['items'][] = [
                'id_cart_item' => $row->id_cart_item,
                'id_product' => $row->id_product,
                'name_product' => $row->name_product, 
                'quantity' => $row->quantity,
                'unit_price' => $row->unit_price,
                'total_price_item' => $row->total_price_item,
                'sku' => $row->sku,
                'obs' => $row->obs,
                'cart_item_comment_id' => $row->cart_item_comment_id,
                'item_added_by' => $row->item_added_by
            ];

            // Sumar totales (solo de este comentario)
            $carts[$row->id_cart]['total_items'] += $row->quantity;
            $carts[$row->id_cart]['total_price'] += $row->total_price_item;
        }

        $carts = array_values($carts);
        $comment['carts'] = $carts;
    }

    // Log para debugging
    $commentsWithCarts = array_filter($comments, function($c) { return !empty($c['carts']); });

    $response->getBody()->write(json_encode($comments));
    return $response->withHeader('Content-Type', 'application/json')->withStatus(200);
});




// ============================================================
// MESSENGER API ENDPOINTS - COMPLETE
// ============================================================


// ============================================================
// MODIFICAR el endpoint existente de GET /messenger/conversations
// para incluir conv_status y labels
// ============================================================

// REEMPLAZAR tu endpoint GET /messenger/conversations con este:
$app->get('/messenger/conversations', function (Request $request, Response $response) {
    $db = new db();
    $conn = $db->connect();
    
    if (!$conn) {
        $response->getBody()->write(json_encode(['error' => 'Database connection failed']));
        return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
    }
    
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $params = $request->getQueryParams();
    $offset = max(0, (int)($params['offset'] ?? 0));
    $limit = min(100, max(1, (int)($params['limit'] ?? 30)));
    $provider = $params['provider'] ?? null;
    $status = $params['status'] ?? null;

    $where = "EXISTS (SELECT 1 FROM messenger_messages m WHERE m.conversation_id = c.id)
        AND c.last_message IS NOT NULL
        AND c.last_message != ''";
    $binds = [];

    if ($provider && $provider !== 'all') {
        $where .= " AND COALESCE(c.provider, 'facebook') = :provider";
        $binds[':provider'] = $provider;
    }

    if ($status && $status !== 'all') {
        if ($status === 'unread') {
            $where .= " AND c.unread_count > 0";
        } else {
            $where .= " AND COALESCE(c.conv_status, 'active') = :status";
            $binds[':status'] = $status;
        }
    }

    $stmt = $conn->prepare("
        SELECT
            c.id,
            c.user_id,
            c.user_name,
            c.user_picture,
            c.last_message,
            c.last_message_type,
            c.last_message_at,
            c.unread_count,
            COALESCE(c.conv_status, 'active') as conv_status,
            COALESCE(c.provider, 'facebook') as provider
        FROM messenger_conversations c
        WHERE {$where}
        ORDER BY c.last_message_at DESC
        LIMIT :limit OFFSET :offset
    ");
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    foreach ($binds as $k => $v) {
        $stmt->bindValue($k, $v, PDO::PARAM_STR);
    }
    $stmt->execute();
    $conversations = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Obtener labels para cada conversación
    if (!empty($conversations)) {
        $userIds = array_map(function($c) { return $c['user_id']; }, $conversations);
        
        // Construir IN clause
        $placeholders = [];
        $bindVals = [];
        foreach ($userIds as $idx => $uid) {
            $ph = ':uid_' . $idx;
            $placeholders[] = $ph;
            $bindVals[$ph] = $uid;
        }
        $inClause = implode(', ', $placeholders);
        
        $stmtLabels = $conn->prepare("
            SELECT cl.conversation_user_id, l.id, l.name, l.color
            FROM messenger_conversation_labels cl
            INNER JOIN messenger_labels l ON cl.label_id = l.id
            WHERE cl.conversation_user_id IN ($inClause)
            ORDER BY l.name
        ");
        foreach ($bindVals as $ph => $val) {
            $stmtLabels->bindValue($ph, $val, PDO::PARAM_STR);
        }
        $stmtLabels->execute();
        $allLabels = $stmtLabels->fetchAll(PDO::FETCH_ASSOC);
        
        // Agrupar labels por user_id
        $labelsByUser = [];
        foreach ($allLabels as $label) {
            $uid = $label['conversation_user_id'];
            if (!isset($labelsByUser[$uid])) {
                $labelsByUser[$uid] = [];
            }
            $labelsByUser[$uid][] = [
                'id' => (int)$label['id'],
                'name' => $label['name'],
                'color' => $label['color']
            ];
        }
        
        // Asignar labels a cada conversación
        foreach ($conversations as &$conv) {
            $conv['labels'] = $labelsByUser[$conv['user_id']] ?? [];
        }
        unset($conv);
    }
    
    $response->getBody()->write(json_encode($conversations));
    return $response
        ->withHeader('Content-Type', 'application/json')
        ->withHeader('Cache-Control', 'no-store, no-cache, must-revalidate, max-age=0')
        ->withHeader('Pragma', 'no-cache');
});

// ============================================================
// ENDPOINT: Buscar conversaciones (con paginación, busca en TODA la BD)
// Supports: ?q=nombre&offset=0&limit=30
// ============================================================
$app->get('/messenger/conversations/search', function (Request $request, Response $response) {
    $params = $request->getQueryParams();
    $query = trim($params['q'] ?? '');
    $offset = max(0, (int)($params['offset'] ?? 0));
    $limit = min(100, max(1, (int)($params['limit'] ?? 30)));
    
    if (empty($query)) {
        $response->getBody()->write(json_encode([]));
        return $response->withHeader('Content-Type', 'application/json');
    }
    
    $db = new db();
    $conn = $db->connect();
    
    if (!$conn) {
        $response->getBody()->write(json_encode([]));
        return $response->withHeader('Content-Type', 'application/json');
    }
    
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $searchTerm = '%' . $query . '%';
    $provider = $params['provider'] ?? null;

    $where = "c.user_name LIKE :search_name";
    $binds = [':search_name' => $searchTerm];

    if ($provider && $provider !== 'all') {
        $where .= " AND COALESCE(c.provider, 'facebook') = :provider";
        $binds[':provider'] = $provider;
    }

    $stmt = $conn->prepare("
        SELECT
            c.id,
            c.user_id,
            c.user_name,
            c.user_picture,
            c.last_message,
            c.last_message_type,
            c.last_message_at,
            c.unread_count,
            COALESCE(c.provider, 'facebook') as provider
        FROM messenger_conversations c
        WHERE {$where}
        ORDER BY
            c.unread_count DESC,
            c.last_message_at DESC
        LIMIT :limit OFFSET :offset
    ");
    foreach ($binds as $k => $v) {
        $stmt->bindValue($k, $v, PDO::PARAM_STR);
    }
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    $conversations = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $response->getBody()->write(json_encode($conversations));
    return $response
        ->withHeader('Content-Type', 'application/json')
        ->withHeader('Cache-Control', 'no-store, no-cache, must-revalidate, max-age=0');
});


// ============================================================
// ENDPOINT: Obtener mensajes de una conversación
// ============================================================
$app->get('/messenger/conversations/{userId}/messages', function (Request $request, Response $response, $args) {
    $userId = $args['userId'];
    
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    // Marcar conversación como leída
    $stmtRead = $conn->prepare("
        UPDATE messenger_conversations SET unread_count = 0 WHERE user_id = :user_id
    ");
    $stmtRead->execute([':user_id' => $userId]);
    
    // Marcar mensajes como leídos
    $stmtReadMessages = $conn->prepare("
        UPDATE messenger_messages SET is_read = 1 
        WHERE user_id = :user_id AND is_from_page = 0 AND is_read = 0
    ");
    $stmtReadMessages->execute([':user_id' => $userId]);
    
    // Obtener mensajes con campos de delivery/read
    // Get last 500 messages (newest first), then reverse to ASC for display
    $stmt = $conn->prepare("
        SELECT * FROM (
            SELECT
                m.id,
                m.message_text,
                m.message_type,
                m.attachment_url,
                m.is_from_page,
                m.is_delivered,
                m.is_read,
                m.sent_from,
                m.created_at
            FROM messenger_messages m
            JOIN messenger_conversations c ON m.conversation_id = c.id
            WHERE c.user_id = :user_id
            ORDER BY m.created_at DESC
            LIMIT 500
        ) sub ORDER BY sub.created_at ASC
    ");
    $stmt->execute([':user_id' => $userId]);
    $messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Convert booleans
    foreach ($messages as &$msg) {
        $msg['is_from_page'] = (bool)$msg['is_from_page'];
        $msg['is_delivered'] = (bool)($msg['is_delivered'] ?? false);
        $msg['is_read'] = (bool)($msg['is_read'] ?? false);
    }
    
    $response->getBody()->write(json_encode($messages));
    return $response->withHeader('Content-Type', 'application/json');
});

// ============================================================
// ENDPOINT: Marcar conversación como leída
// ============================================================
$app->post('/messenger/conversations/{userId}/read', function (Request $request, Response $response, $args) {
    $userId = $args['userId'];
    
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $stmt = $conn->prepare("
        UPDATE messenger_conversations SET unread_count = 0 WHERE user_id = :user_id
    ");
    $stmt->execute([':user_id' => $userId]);
    
    $stmt2 = $conn->prepare("
        UPDATE messenger_messages m
        JOIN messenger_conversations c ON m.conversation_id = c.id
        SET m.is_read = 1 
        WHERE c.user_id = :user_id AND m.is_from_page = 0
    ");
    $stmt2->execute([':user_id' => $userId]);
    
    $response->getBody()->write(json_encode(['success' => true]));
    return $response->withHeader('Content-Type', 'application/json');
});

// ============================================================
// ENDPOINT: Enviar mensaje de texto
// ============================================================
$app->post('/messenger/send', function (Request $request, Response $response) {
    $data = json_decode($request->getBody(), true);
    $userId = $data['user_id'] ?? null;
    $message = $data['message'] ?? null;

    if (!$userId || !$message) {
        $response->getBody()->write(json_encode(['error' => 'Missing user_id or message']));
        return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
    }

    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");

    // Detectar provider de la conversación
    $stmtProvider = $conn->prepare("SELECT provider FROM messenger_conversations WHERE user_id = :user_id LIMIT 1");
    $stmtProvider->execute([':user_id' => $userId]);
    $provider = $stmtProvider->fetchColumn() ?: 'facebook';

    $sentMessageId = null;

    if ($provider === 'whatsapp') {
        // ── Enviar por WhatsApp Cloud API ──
        $waConfig = getWhatsAppConfig();
        $waAccessToken = $waConfig['ACCESS_TOKEN'];
        $waPhoneNumberId = $waConfig['PHONE_NUMBER_ID'];

        if (!$waAccessToken || !$waPhoneNumberId) {
            $response->getBody()->write(json_encode(['error' => 'WhatsApp not configured']));
            return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
        }

        // Extract phone number from user_id (wa_351912345678 -> 351912345678)
        $phoneNumber = str_replace('wa_', '', $userId);
        $sentMessageId = sendWhatsAppMessage($phoneNumber, $message, $waAccessToken, $waPhoneNumberId);

        if (!$sentMessageId) {
            $response->getBody()->write(json_encode([
                'error' => 'Failed to send WhatsApp message. Check the 24h customer service window.'
            ]));
            return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
        }
    } elseif ($provider === 'instagram') {
        // ── Enviar por Instagram DM ──
        $igSent = sendInstagramPrivateReply($userId, $message);

        if (!$igSent) {
            error_app("Error sending Instagram DM to {$userId}");
            $response->getBody()->write(json_encode([
                'error' => 'Failed to send Instagram message. The user may not have messaged you recently (24h window).'
            ]));
            return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
        }

        $sentMessageId = 'ig_sent_' . time() . '_' . rand(1000, 9999);
    } else {
        // ── Enviar por Facebook Messenger ──
        $accessTokenManager = new FacebookTokenManager();
        $accessToken = $accessTokenManager->getAccessToken();

        if (!$accessToken) {
            $response->getBody()->write(json_encode(['error' => 'Facebook token not available']));
            return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
        }

        $fbApiUrl = 'https://graph.facebook.com/v18.0/me/messages?access_token=' . $accessToken;

        $postData = [
            'recipient' => ['id' => $userId],
            'message' => ['text' => $message],
            'messaging_type' => 'RESPONSE'
        ];

        $ch = curl_init($fbApiUrl);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
        curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($postData));
        curl_setopt($ch, CURLOPT_TIMEOUT, 30);

        $fbResponse = curl_exec($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        curl_close($ch);

        $fbResult = json_decode($fbResponse, true);

        if ($httpCode !== 200 || !isset($fbResult['message_id'])) {
            error_app("Error sending Messenger message: " . $fbResponse);
            $response->getBody()->write(json_encode([
                'error' => 'Failed to send message',
                'fb_response' => $fbResult
            ]));
            return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
        }

        $sentMessageId = $fbResult['message_id'];
    }

    // Guardar en BD
    try {
        $stmtGetConv = $conn->prepare("SELECT id FROM messenger_conversations WHERE user_id = :user_id");
        $stmtGetConv->execute([':user_id' => $userId]);
        $conversationId = $stmtGetConv->fetchColumn();

        if (!$conversationId) {
            $stmtCreateConv = $conn->prepare("
                INSERT INTO messenger_conversations (user_id, provider, last_message, last_message_type, last_message_at)
                VALUES (:user_id, :provider, :message, 'text', NOW())
            ");
            $stmtCreateConv->execute([':user_id' => $userId, ':provider' => $provider, ':message' => $message]);
            $conversationId = $conn->lastInsertId();
        }

        $stmtMsg = $conn->prepare("
            INSERT INTO messenger_messages
            (conversation_id, user_id, message_text, message_type, is_from_page, sent_from, provider, fb_message_id, created_at)
            VALUES (:conv_id, :user_id, :message, 'text', 1, 'webapp', :provider, :fb_mid, NOW())
        ");
        $stmtMsg->execute([
            ':conv_id' => $conversationId,
            ':user_id' => $userId,
            ':message' => $message,
            ':provider' => $provider,
            ':fb_mid' => $sentMessageId
        ]);

        $stmtUpdate = $conn->prepare("
            UPDATE messenger_conversations
            SET last_message = :msg, last_message_type = 'text', last_message_at = NOW()
            WHERE user_id = :user_id
        ");
        $stmtUpdate->execute([':msg' => $message, ':user_id' => $userId]);

    } catch (Exception $e) {
        error_app("Error saving sent message: " . $e->getMessage());
    }

    $response->getBody()->write(json_encode([
        'success' => true,
        'message_id' => $sentMessageId
    ]));
    return $response->withHeader('Content-Type', 'application/json');
});



// ============================================================
// FIX: send-file - Muestra nombre real del archivo en vez de "Imagem"
// y guarda siempre el attachment_url para previsualización
// ============================================================
$app->post('/messenger/send-file', function (Request $request, Response $response) {
    $params = $request->getParsedBody();
    $userId = $params['user_id'] ?? $_POST['user_id'] ?? null;
    $caption = $params['caption'] ?? $_POST['caption'] ?? '';
    
    if (!$userId) {
        $response->getBody()->write(json_encode(['error' => 'Missing user_id']));
        return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
    }
    
    $uploadedFiles = $request->getUploadedFiles();
    $file = $uploadedFiles['file'] ?? null;
    
    if (!$file || $file->getError() !== UPLOAD_ERR_OK) {
        $response->getBody()->write(json_encode(['error' => 'No file uploaded or upload error']));
        return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
    }
    
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $accessTokenManager = new FacebookTokenManager();
    $accessToken = $accessTokenManager->getAccessToken();
    
    if (!$accessToken) {
        $response->getBody()->write(json_encode(['error' => 'Facebook token not available']));
        return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
    }
    
    $mimeType = $file->getClientMediaType();
    $fileName = $file->getClientFilename();
    $fileSize = $file->getSize();
    
    $fbAttachmentType = 'file';
    $messageType = 'file';
    
    if (strpos($mimeType, 'image/') === 0) {
        $fbAttachmentType = 'image';
        $messageType = 'image';
    } elseif (strpos($mimeType, 'video/') === 0) {
        $fbAttachmentType = 'video';
        $messageType = 'video';
    } elseif (strpos($mimeType, 'audio/') === 0) {
        $fbAttachmentType = 'audio';
        $messageType = 'audio';
    }
    
    // Guardar archivo local
    $publicUploadDir = __DIR__ . '/../public/uploads/messenger/';
    if (!is_dir($publicUploadDir)) {
        mkdir($publicUploadDir, 0755, true);
    }
    
    $extension = pathinfo($fileName, PATHINFO_EXTENSION);
    $safeName = preg_replace('/[^a-zA-Z0-9._-]/', '_', pathinfo($fileName, PATHINFO_FILENAME));
    $uniqueName = date('Ymd_His') . '_' . uniqid() . '_' . $safeName . '.' . $extension;
    $permanentPath = $publicUploadDir . $uniqueName;
    
    $stream = $file->getStream();
    $fileContents = $stream->getContents();
    file_put_contents($permanentPath, $fileContents);
    
    $publicUrl = APP_URL . '/api/src/public/uploads/messenger/' . $uniqueName;
    
    $tempPath = sys_get_temp_dir() . '/' . $uniqueName;
    file_put_contents($tempPath, $fileContents);
    
    // Detectar provider de la conversación
    $stmtProvider = $conn->prepare("SELECT provider FROM messenger_conversations WHERE user_id = :user_id LIMIT 1");
    $stmtProvider->execute([':user_id' => $userId]);
    $provider = $stmtProvider->fetchColumn() ?: 'facebook';

    $fbResult = null;

    if ($provider === 'whatsapp') {
        // WhatsApp: enviar media via Cloud API URL
        if (file_exists($tempPath)) {
            unlink($tempPath);
        }

        $waConfig = getWhatsAppConfig();
        $waAccessToken = $waConfig['ACCESS_TOKEN'];
        $waPhoneNumberId = $waConfig['PHONE_NUMBER_ID'];

        if (!$waAccessToken || !$waPhoneNumberId) {
            if (file_exists($permanentPath)) unlink($permanentPath);
            $response->getBody()->write(json_encode(['error' => 'WhatsApp not configured']));
            return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
        }

        $phoneNumber = str_replace('wa_', '', $userId);
        $waMessageId = sendWhatsAppMedia($phoneNumber, $messageType, $publicUrl, $caption ?: $fileName, $waAccessToken, $waPhoneNumberId);

        if (!$waMessageId) {
            if (file_exists($permanentPath)) unlink($permanentPath);
            $response->getBody()->write(json_encode(['error' => 'Failed to send file via WhatsApp']));
            return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
        }

        $fbResult = ['message_id' => $waMessageId];
    } elseif ($provider === 'instagram') {
        // Instagram: enviar attachment via URL (el archivo ya está guardado en publicUrl)
        if (file_exists($tempPath)) {
            unlink($tempPath);
        }

        $igMessageId = sendInstagramAttachment($userId, $fbAttachmentType, $publicUrl);

        if (!$igMessageId) {
            if (file_exists($permanentPath)) {
                unlink($permanentPath);
            }
            $response->getBody()->write(json_encode([
                'error' => 'Failed to send file to Instagram'
            ]));
            return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
        }

        $fbResult = ['message_id' => $igMessageId];
    } else {
        // Facebook: enviar archivo via multipart upload
        $fbApiUrl = 'https://graph.facebook.com/v18.0/me/messages?access_token=' . $accessToken;

        $postData = [
            'recipient' => json_encode(['id' => $userId]),
            'message' => json_encode([
                'attachment' => [
                    'type' => $fbAttachmentType,
                    'payload' => ['is_reusable' => false]
                ]
            ]),
            'filedata' => new \CURLFile($tempPath, $mimeType, $fileName),
            'messaging_type' => 'RESPONSE'
        ];

        $ch = curl_init($fbApiUrl);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $postData);
        curl_setopt($ch, CURLOPT_TIMEOUT, 120);

        $fbResponse = curl_exec($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        curl_close($ch);

        if (file_exists($tempPath)) {
            unlink($tempPath);
        }

        $fbResult = json_decode($fbResponse, true);

        if ($httpCode !== 200 || !isset($fbResult['message_id'])) {
            if (file_exists($permanentPath)) {
                unlink($permanentPath);
            }
            $response->getBody()->write(json_encode([
                'error' => 'Failed to send file to Facebook',
                'fb_response' => $fbResult
            ]));
            return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
        }
    }
    
    // ============================================================
    // GUARDAR EN BD
    // message_text = nombre real del archivo SIEMPRE
    // attachment_url = URL pública del archivo
    // ============================================================
    try {
        $stmtGetConv = $conn->prepare("SELECT id FROM messenger_conversations WHERE user_id = ?");
        $stmtGetConv->execute([$userId]);
        $conversationId = $stmtGetConv->fetchColumn();

        if (!$conversationId) {
            $stmtCreateConv = $conn->prepare("
                INSERT INTO messenger_conversations (user_id, last_message, last_message_type, last_message_at, provider)
                VALUES (?, ?, ?, NOW(), ?)
            ");
            $stmtCreateConv->execute([$userId, $fileName, $messageType, $provider]);
            $conversationId = $conn->lastInsertId();
        }

        // message_text = nombre original del archivo (ej: "foto_produto.jpg")
        // Si hay caption, usar caption. Si no, usar el nombre del archivo
        $displayMessage = !empty(trim($caption)) ? $caption : $fileName;

        $stmtMsg = $conn->prepare("
            INSERT INTO messenger_messages
            (conversation_id, user_id, message_text, message_type, attachment_url, is_from_page, sent_from, fb_message_id, provider, created_at)
            VALUES (?, ?, ?, ?, ?, 1, 'webapp', ?, ?, NOW())
        ");
        $stmtMsg->execute([
            $conversationId,
            $userId,
            $displayMessage,
            $messageType,
            $publicUrl,
            $fbResult['message_id'],
            $provider
        ]);
        
        // Actualizar conversación - mostrar nombre del archivo en la preview
        $convPreview = $fileName;
        $stmtUpdate = $conn->prepare("
            UPDATE messenger_conversations 
            SET last_message = ?, last_message_type = ?, last_message_at = NOW() 
            WHERE user_id = ?
        ");
        $stmtUpdate->execute([$convPreview, $messageType, $userId]);
        
    } catch (\Exception $e) {
        error_app("Error saving sent file message: " . $e->getMessage());
    }
    
    // Enviar caption como mensaje separado si existe
    if (!empty(trim($caption))) {
        $captionSent = false;
        $captionResult = null;

        if ($provider === 'instagram') {
            $igCaptionSent = sendInstagramPrivateReply($userId, $caption);
            if ($igCaptionSent) {
                $captionSent = true;
                // Instagram no devuelve message_id en sendInstagramPrivateReply, generar uno
                $captionResult = ['message_id' => 'ig_caption_' . uniqid()];
            }
        } else {
            $captionData = [
                'recipient' => ['id' => $userId],
                'message' => ['text' => $caption],
                'messaging_type' => 'RESPONSE'
            ];

            $ch2 = curl_init('https://graph.facebook.com/v18.0/me/messages?access_token=' . $accessToken);
            curl_setopt($ch2, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch2, CURLOPT_POST, true);
            curl_setopt($ch2, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
            curl_setopt($ch2, CURLOPT_POSTFIELDS, json_encode($captionData));
            curl_setopt($ch2, CURLOPT_TIMEOUT, 15);
            $captionResponse = curl_exec($ch2);
            curl_close($ch2);

            $captionResult = json_decode($captionResponse, true);
            if (isset($captionResult['message_id'])) {
                $captionSent = true;
            }
        }

        if ($captionSent && isset($captionResult['message_id'])) {
            try {
                $stmtCaption = $conn->prepare("
                    INSERT INTO messenger_messages
                    (conversation_id, user_id, message_text, message_type, is_from_page, sent_from, fb_message_id, provider, created_at)
                    VALUES (?, ?, ?, 'text', 1, 'webapp', ?, ?, NOW())
                ");
                $stmtCaption->execute([
                    $conversationId,
                    $userId,
                    $caption,
                    $captionResult['message_id'],
                    $provider
                ]);
            } catch (\Exception $e) {
                error_app("Error saving caption: " . $e->getMessage());
            }
        }
    }
    
    $response->getBody()->write(json_encode([
        'success' => true,
        'message_id' => $fbResult['message_id'],
        'type' => $messageType,
        'attachment_url' => $publicUrl
    ]));
    return $response->withHeader('Content-Type', 'application/json');
});

// ============================================================
// ENDPOINT: Typing indicator status
// ============================================================
$app->get('/messenger/conversations/{userId}/typing', function (Request $request, Response $response, $args) {
    $userId = $args['userId'];
    
    $db = new db();
    $conn = $db->connect();
    
    if (!$conn) {
        $response->getBody()->write(json_encode(['is_typing' => false]));
        return $response->withHeader('Content-Type', 'application/json');
    }
    
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $stmt = $conn->prepare("
        SELECT is_typing, updated_at 
        FROM messenger_typing_status 
        WHERE user_id = :user_id
    ");
    $stmt->execute([':user_id' => $userId]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    
    $isTyping = false;
    if ($result && $result['is_typing']) {
        $updatedAt = strtotime($result['updated_at']);
        // Typing es válido por 5 segundos
        if ((time() - $updatedAt) < 5) {
            $isTyping = true;
        } else {
            // Expirado, limpiar
            $stmtClean = $conn->prepare("
                UPDATE messenger_typing_status SET is_typing = 0 WHERE user_id = :user_id
            ");
            $stmtClean->execute([':user_id' => $userId]);
        }
    }
    
    $response->getBody()->write(json_encode(['is_typing' => $isTyping]));
    return $response
        ->withHeader('Content-Type', 'application/json')
        ->withHeader('Cache-Control', 'no-store, no-cache');
});

// ============================================================
// ENDPOINT: Obtener conteo total de no leídos
// ============================================================
$app->get('/messenger/unread-count', function (Request $request, Response $response) {
    $db = new db();
    $conn = $db->connect();
    
    $stmt = $conn->prepare("
        SELECT COALESCE(SUM(unread_count), 0) as total 
        FROM messenger_conversations
    ");
    $stmt->execute();
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    
    $response->getBody()->write(json_encode(['count' => (int)$result['total']]));
    return $response->withHeader('Content-Type', 'application/json');
});


// ============================================================
// MESSENGER: Labels, Notes & Conversation Status Endpoints
// Agregar estos endpoints a tu archivo de rutas de messenger
// ============================================================


// ============================================================
// CONVERSATION STATUS (follow_up, done, spam, active)
// ============================================================

// Actualizar estado de una conversación
$app->post('/messenger/conversations/{userId}/status', function (Request $request, Response $response, $args) {
    $userId = $args['userId'];
    $data = json_decode($request->getBody(), true);
    $status = $data['status'] ?? 'active';
    
    // Validar status
    $validStatuses = ['active', 'follow_up', 'done', 'spam'];
    if (!in_array($status, $validStatuses)) {
        $response->getBody()->write(json_encode(['error' => 'Invalid status']));
        return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
    }
    
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $stmt = $conn->prepare("
        UPDATE messenger_conversations SET conv_status = :status WHERE user_id = :user_id
    ");
    $stmt->execute([':status' => $status, ':user_id' => $userId]);
    
    $response->getBody()->write(json_encode(['success' => true, 'status' => $status]));
    return $response->withHeader('Content-Type', 'application/json');
});

// Marcar como no leída (poner unread_count = 1)
$app->post('/messenger/conversations/{userId}/mark-unread', function (Request $request, Response $response, $args) {
    $userId = $args['userId'];
    
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $stmt = $conn->prepare("
        UPDATE messenger_conversations 
        SET unread_count = GREATEST(unread_count, 1) 
        WHERE user_id = :user_id
    ");
    $stmt->execute([':user_id' => $userId]);
    
    $response->getBody()->write(json_encode(['success' => true]));
    return $response->withHeader('Content-Type', 'application/json');
});

// Eliminar conversación (soft delete: mover a status 'deleted')
$app->delete('/messenger/conversations/{userId}', function (Request $request, Response $response, $args) {
    $userId = $args['userId'];
    
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    try {
        $conn->beginTransaction();
        
        // Obtener conversation id
        $stmtConv = $conn->prepare("SELECT id FROM messenger_conversations WHERE user_id = :user_id");
        $stmtConv->execute([':user_id' => $userId]);
        $convId = $stmtConv->fetchColumn();
        
        if ($convId) {
            // Eliminar mensajes
            $conn->prepare("DELETE FROM messenger_messages WHERE conversation_id = :conv_id")
                 ->execute([':conv_id' => $convId]);
            
            // Eliminar labels asignadas
            $conn->prepare("DELETE FROM messenger_conversation_labels WHERE conversation_user_id = :user_id")
                 ->execute([':user_id' => $userId]);
            
            // Eliminar notas
            $conn->prepare("DELETE FROM messenger_conversation_notes WHERE conversation_user_id = :user_id")
                 ->execute([':user_id' => $userId]);
            
            // Eliminar conversación
            $conn->prepare("DELETE FROM messenger_conversations WHERE id = :conv_id")
                 ->execute([':conv_id' => $convId]);
        }
        
        $conn->commit();
        
        $response->getBody()->write(json_encode(['success' => true]));
        return $response->withHeader('Content-Type', 'application/json');
        
    } catch (Exception $e) {
        $conn->rollBack();
        $response->getBody()->write(json_encode(['error' => $e->getMessage()]));
        return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
    }
});


// ============================================================
// LABELS CRUD
// ============================================================

// Obtener todas las etiquetas
$app->get('/messenger/labels', function (Request $request, Response $response) {
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $stmt = $conn->prepare("SELECT id, name, color, created_at FROM messenger_labels ORDER BY name");
    $stmt->execute();
    $labels = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $response->getBody()->write(json_encode($labels));
    return $response->withHeader('Content-Type', 'application/json');
});

// Crear una etiqueta
$app->post('/messenger/labels', function (Request $request, Response $response) {
    $data = json_decode($request->getBody(), true);
    $name = trim($data['name'] ?? '');
    $color = $data['color'] ?? '#ba9652';
    
    if (empty($name)) {
        $response->getBody()->write(json_encode(['error' => 'Name is required']));
        return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
    }
    
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $stmt = $conn->prepare("INSERT INTO messenger_labels (name, color) VALUES (:name, :color)");
    $stmt->execute([':name' => $name, ':color' => $color]);
    
    $newId = $conn->lastInsertId();
    
    $response->getBody()->write(json_encode([
        'success' => true,
        'label' => ['id' => (int)$newId, 'name' => $name, 'color' => $color]
    ]));
    return $response->withHeader('Content-Type', 'application/json');
});

// Eliminar una etiqueta
$app->delete('/messenger/labels/{labelId}', function (Request $request, Response $response, $args) {
    $labelId = (int)$args['labelId'];
    
    $db = new db();
    $conn = $db->connect();
    
    // CASCADE eliminará también las asignaciones en messenger_conversation_labels
    $stmt = $conn->prepare("DELETE FROM messenger_labels WHERE id = :id");
    $stmt->execute([':id' => $labelId]);
    
    $response->getBody()->write(json_encode(['success' => true]));
    return $response->withHeader('Content-Type', 'application/json');
});


// ============================================================
// CONVERSATION LABELS (asignar/quitar etiquetas a conversaciones)
// ============================================================

// Obtener etiquetas de una conversación
$app->get('/messenger/conversations/{userId}/labels', function (Request $request, Response $response, $args) {
    $userId = $args['userId'];
    
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $stmt = $conn->prepare("
        SELECT l.id, l.name, l.color
        FROM messenger_labels l
        INNER JOIN messenger_conversation_labels cl ON cl.label_id = l.id
        WHERE cl.conversation_user_id = :user_id
        ORDER BY l.name
    ");
    $stmt->execute([':user_id' => $userId]);
    $labels = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $response->getBody()->write(json_encode($labels));
    return $response->withHeader('Content-Type', 'application/json');
});

// Asignar etiqueta a conversación
$app->post('/messenger/conversations/{userId}/labels', function (Request $request, Response $response, $args) {
    $userId = $args['userId'];
    $data = json_decode($request->getBody(), true);
    $labelId = (int)($data['label_id'] ?? 0);
    
    if (!$labelId) {
        $response->getBody()->write(json_encode(['error' => 'label_id is required']));
        return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
    }
    
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    // INSERT IGNORE para evitar duplicados
    $stmt = $conn->prepare("
        INSERT IGNORE INTO messenger_conversation_labels (conversation_user_id, label_id)
        VALUES (:user_id, :label_id)
    ");
    $stmt->execute([':user_id' => $userId, ':label_id' => $labelId]);
    
    $response->getBody()->write(json_encode(['success' => true]));
    return $response->withHeader('Content-Type', 'application/json');
});

// Quitar etiqueta de conversación
$app->delete('/messenger/conversations/{userId}/labels/{labelId}', function (Request $request, Response $response, $args) {
    $userId = $args['userId'];
    $labelId = (int)$args['labelId'];
    
    $db = new db();
    $conn = $db->connect();
    
    $stmt = $conn->prepare("
        DELETE FROM messenger_conversation_labels 
        WHERE conversation_user_id = :user_id AND label_id = :label_id
    ");
    $stmt->execute([':user_id' => $userId, ':label_id' => $labelId]);
    
    $response->getBody()->write(json_encode(['success' => true]));
    return $response->withHeader('Content-Type', 'application/json');
});


// ============================================================
// NOTES CRUD
// ============================================================

// Obtener notas de una conversación
$app->get('/messenger/conversations/{userId}/notes', function (Request $request, Response $response, $args) {
    $userId = $args['userId'];
    
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $stmt = $conn->prepare("
        SELECT id, note_text, created_at
        FROM messenger_conversation_notes
        WHERE conversation_user_id = :user_id
        ORDER BY created_at DESC
    ");
    $stmt->execute([':user_id' => $userId]);
    $notes = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $response->getBody()->write(json_encode($notes));
    return $response->withHeader('Content-Type', 'application/json');
});

// Agregar nota
$app->post('/messenger/conversations/{userId}/notes', function (Request $request, Response $response, $args) {
    $userId = $args['userId'];
    $data = json_decode($request->getBody(), true);
    $noteText = trim($data['note_text'] ?? '');
    
    if (empty($noteText)) {
        $response->getBody()->write(json_encode(['error' => 'note_text is required']));
        return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
    }
    
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $stmt = $conn->prepare("
        INSERT INTO messenger_conversation_notes (conversation_user_id, note_text)
        VALUES (:user_id, :note_text)
    ");
    $stmt->execute([':user_id' => $userId, ':note_text' => $noteText]);
    
    $newId = $conn->lastInsertId();
    
    // Devolver la nota creada
    $stmtGet = $conn->prepare("SELECT id, note_text, created_at FROM messenger_conversation_notes WHERE id = :id");
    $stmtGet->execute([':id' => $newId]);
    $note = $stmtGet->fetch(PDO::FETCH_ASSOC);
    
    $response->getBody()->write(json_encode(['success' => true, 'note' => $note]));
    return $response->withHeader('Content-Type', 'application/json');
});

// Eliminar nota
$app->delete('/messenger/conversations/{userId}/notes/{noteId}', function (Request $request, Response $response, $args) {
    $noteId = (int)$args['noteId'];
    
    $db = new db();
    $conn = $db->connect();
    
    $stmt = $conn->prepare("DELETE FROM messenger_conversation_notes WHERE id = :id");
    $stmt->execute([':id' => $noteId]);
    
    $response->getBody()->write(json_encode(['success' => true]));
    return $response->withHeader('Content-Type', 'application/json');
});


// ============================================================
// MESSENGER: Saved Responses (Respostas Pré-feitas) Endpoints
// Agregar a tu archivo de rutas
// ============================================================

// Obtener todas las respuestas guardadas
$app->get('/messenger/saved-responses', function (Request $request, Response $response) {
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $stmt = $conn->prepare("
        SELECT id, title, content, created_at 
        FROM messenger_saved_responses 
        ORDER BY title ASC
    ");
    $stmt->execute();
    $responses = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $response->getBody()->write(json_encode($responses));
    return $response->withHeader('Content-Type', 'application/json');
});

// Crear una respuesta guardada
$app->post('/messenger/saved-responses', function (Request $request, Response $response) {
    $data = json_decode($request->getBody(), true);
    $title = trim($data['title'] ?? '');
    $content = trim($data['content'] ?? '');
    
    if (empty($title) || empty($content)) {
        $response->getBody()->write(json_encode(['error' => 'Title and content are required']));
        return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
    }
    
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $stmt = $conn->prepare("
        INSERT INTO messenger_saved_responses (title, content) VALUES (:title, :content)
    ");
    $stmt->execute([':title' => $title, ':content' => $content]);
    
    $newId = $conn->lastInsertId();
    
    $response->getBody()->write(json_encode([
        'id' => (int)$newId,
        'title' => $title,
        'content' => $content
    ]));
    return $response->withHeader('Content-Type', 'application/json');
});

// Actualizar una respuesta guardada
$app->put('/messenger/saved-responses/{id}', function (Request $request, Response $response, $args) {
    $id = (int)$args['id'];
    $data = json_decode($request->getBody(), true);
    $title = trim($data['title'] ?? '');
    $content = trim($data['content'] ?? '');
    
    if (empty($title) || empty($content)) {
        $response->getBody()->write(json_encode(['error' => 'Title and content are required']));
        return $response->withStatus(400)->withHeader('Content-Type', 'application/json');
    }
    
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $stmt = $conn->prepare("
        UPDATE messenger_saved_responses SET title = :title, content = :content WHERE id = :id
    ");
    $stmt->execute([':title' => $title, ':content' => $content, ':id' => $id]);
    
    $response->getBody()->write(json_encode(['success' => true]));
    return $response->withHeader('Content-Type', 'application/json');
});

// Eliminar una respuesta guardada
$app->delete('/messenger/saved-responses/{id}', function (Request $request, Response $response, $args) {
    $id = (int)$args['id'];
    
    $db = new db();
    $conn = $db->connect();
    
    $stmt = $conn->prepare("DELETE FROM messenger_saved_responses WHERE id = :id");
    $stmt->execute([':id' => $id]);
    
    $response->getBody()->write(json_encode(['success' => true]));
    return $response->withHeader('Content-Type', 'application/json');
});


// ============================================================
// MESSENGER: Customer Orders Endpoint
// Obtener los carritos/órdenes de un cliente por su user_id
// ============================================================

$app->get('/messenger/conversations/{userId}/orders', function (Request $request, Response $response, $args) {
    $userId = $args['userId'];
    
    $db = new db();
    $conn = $db->connect();
    $conn->exec("SET NAMES 'utf8mb4'");
    
    $params = $request->getQueryParams();
    $limit = min(50, max(1, (int)($params['limit'] ?? 20)));
    $offset = max(0, (int)($params['offset'] ?? 0));
    
    try {
        // Obtener carritos del cliente con resumen de items
        $stmt = $conn->prepare("
            SELECT 
                c.id_cart,
                c.status,
                c.creation_date,
                c.url_checkout,
                c.live_id,
                COUNT(ci.id_cart_item) as total_items,
                SUM(ci.quantity) as total_quantity,
                SUM(ci.quantity * ci.unit_price) as total_value,
                GROUP_CONCAT(
                    DISTINCT CONCAT(ci.name_product, ' (', ci.quantity, ')') 
                    ORDER BY ci.name_product 
                    SEPARATOR ', '
                ) as items_summary
            FROM cart c
            LEFT JOIN cart_items ci ON c.id_cart = ci.id_cart
            WHERE c.id_user = :user_id
            GROUP BY c.id_cart
            ORDER BY c.creation_date DESC
            LIMIT :limit OFFSET :offset
        ");
        $stmt->bindParam(':user_id', $userId, PDO::PARAM_STR);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        $orders = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Total count for pagination
        $stmtCount = $conn->prepare("
            SELECT COUNT(*) as total FROM cart WHERE id_user = :user_id
        ");
        $stmtCount->execute([':user_id' => $userId]);
        $total = (int)$stmtCount->fetchColumn();
        
        // Obtener los items de cada carrito
        if (!empty($orders)) {
            $cartIds = array_map(function($o) { return $o['id_cart']; }, $orders);
            
            $placeholders = [];
            $bindVals = [];
            foreach ($cartIds as $idx => $cid) {
                $ph = ':cid_' . $idx;
                $placeholders[] = $ph;
                $bindVals[$ph] = $cid;
            }
            $inClause = implode(', ', $placeholders);
            
            $stmtItems = $conn->prepare("
                SELECT 
                    ci.id_cart,
                    ci.id_cart_item,
                    ci.id_product,
                    ci.name_product,
                    ci.sku,
                    ci.obs,
                    ci.quantity,
                    ci.unit_price,
                    ci.status as item_status
                FROM cart_items ci
                WHERE ci.id_cart IN ($inClause)
                ORDER BY ci.id_cart, ci.id_cart_item
            ");
            foreach ($bindVals as $ph => $val) {
                $stmtItems->bindValue($ph, $val, PDO::PARAM_INT);
            }
            $stmtItems->execute();
            $allItems = $stmtItems->fetchAll(PDO::FETCH_ASSOC);
            
            // Group items by cart
            $itemsByCart = [];
            foreach ($allItems as $item) {
                $cid = $item['id_cart'];
                if (!isset($itemsByCart[$cid])) {
                    $itemsByCart[$cid] = [];
                }
                unset($item['id_cart']);
                $itemsByCart[$cid][] = $item;
            }
            
            foreach ($orders as &$order) {
                $order['items'] = $itemsByCart[$order['id_cart']] ?? [];
            }
            unset($order);
        }
        
        $response->getBody()->write(json_encode([
            'orders' => $orders,
            'total' => $total,
            'has_more' => ($offset + $limit) < $total
        ]));
        return $response->withHeader('Content-Type', 'application/json');
        
    } catch (Exception $e) {
        $response->getBody()->write(json_encode(['error' => $e->getMessage()]));
        return $response->withStatus(500)->withHeader('Content-Type', 'application/json');
    }
});



