Tul xxx Tul

Viewing: Chat.php

<?php
class Chat {
    private $db;

    public function __construct() {
        $this->db = (new Database())->connect();
        // Asegurar esquema para chat entre administradores (idempotente)
        try { $this->ensureAdminSchema(); } catch (Throwable $e) { /* noop */ }
    }

    public function getAdminUsers() {
        $stmt = $this->db->prepare("SELECT id, name, email, role FROM users WHERE role IN ('admin','manager')");
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    public function getAllClients() {
        $stmt = $this->db->prepare("SELECT id, name, email FROM users WHERE role IN ('cliente','emprendedor') ORDER BY name");
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Determina el admin primario (prefiere rol 'admin' sobre 'manager').
     * Devuelve null si no hay administradores.
     */
    public function getPrimaryAdminId() {
        try {
            $stmt = $this->db->prepare("SELECT id, role FROM users WHERE role IN ('admin','manager') ORDER BY CASE WHEN role='admin' THEN 0 ELSE 1 END, id ASC LIMIT 1");
            $stmt->execute();
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            return $row ? (int)$row['id'] : null;
        } catch (Throwable $e) {
            return null;
        }
    }

    /**
     * ¿El cliente tiene algún mensaje en cualquier conversación?
     */
    public function hasAnyMessagesForClient($client_id) {
        $sql = 'SELECT COUNT(*) AS cnt
                FROM messages m
                JOIN conversations c ON c.id = m.conversation_id
                WHERE c.client_id = ?';
        $stmt = $this->db->prepare($sql);
        $stmt->execute([(int)$client_id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row && (int)$row['cnt'] > 0;
    }

    /**
     * Envía mensaje de bienvenida desde admin al cliente SOLO si no tiene historial.
     * Retorna true si insertó el mensaje; false si no aplicó o falló.
     */
    public function sendWelcomeIfEmpty($client_id, $client_name) {
        try {
            $client_id = (int)$client_id;
            if ($client_id <= 0) return false;
            // Evitar duplicados: si ya hay mensajes, no enviar
            if ($this->hasAnyMessagesForClient($client_id)) return false;
            $admin_id = $this->getPrimaryAdminId();
            if (!$admin_id) return false;
            $convId = $this->ensureConversation($admin_id, $client_id);
            $name = trim((string)$client_name);
            if ($name === '') { $name = 'Emprendedor'; }
            $message = "👋 ¡Hola, {$name}!\nBienvenido a Cuéntame, soy Emprendo y estoy aquí para ayudarte en todo lo que necesites.\n¡Te deseo mucho éxito y que enciendas tus ideas al 100%! 🚀✨";
            return (bool)$this->insertMessage($convId, $admin_id, $message, 'text', null);
        } catch (Throwable $e) {
            return false;
        }
    }

    public function ensureConversation($admin_id, $client_id) {
        $stmt = $this->db->prepare('SELECT id FROM conversations WHERE admin_id = ? AND client_id = ? LIMIT 1');
        $stmt->execute([$admin_id, $client_id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row && isset($row['id'])) {
            return (int)$row['id'];
        }
        $ins = $this->db->prepare('INSERT INTO conversations (admin_id, client_id, status) VALUES (?, ?, "active")');
        $ins->execute([$admin_id, $client_id]);
        return (int)$this->db->lastInsertId();
    }

    public function insertMessage($conversation_id, $sender_id, $message, $message_type = 'text', $file_url = null) {
        $sql = 'INSERT INTO messages (conversation_id, sender_id, message, message_type, file_url, is_read) VALUES (?, ?, ?, ?, ?, 0)';
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([$conversation_id, $sender_id, $message, $message_type, $file_url]);
    }

    public function fetchMessagesForClient($client_id) {
        $sql = 'SELECT m.id, m.message, m.message_type, m.file_url, m.created_at, m.sender_id,
                       u.role AS sender_role, u.name AS sender_name, u.profile_image AS sender_avatar
                FROM messages m
                JOIN conversations c ON c.id = m.conversation_id
                LEFT JOIN users u ON u.id = m.sender_id
                WHERE c.client_id = ?
                ORDER BY m.created_at ASC, m.id ASC';
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$client_id]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function fetchMessagesForAdminAll($admin_id) {
        $sql = 'SELECT m.id, m.message, m.message_type, m.file_url, m.created_at, m.sender_id,
                       u.role AS sender_role, u.name AS sender_name, u.profile_image AS sender_avatar,
                       c.client_id, uc.name AS client_name, uc.profile_image AS client_avatar
                FROM messages m
                JOIN conversations c ON c.id = m.conversation_id AND c.admin_id = ?
                LEFT JOIN users u ON u.id = m.sender_id
                LEFT JOIN users uc ON uc.id = c.client_id
                ORDER BY m.created_at ASC, m.id ASC';
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$admin_id]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function fetchMessagesForAdminByClient($admin_id, $client_id) {
        $sql = 'SELECT m.id, m.message, m.message_type, m.file_url, m.created_at, m.sender_id,
                       u.role AS sender_role, u.name AS sender_name, u.profile_image AS sender_avatar,
                       c.client_id, uc.name AS client_name, uc.profile_image AS client_avatar
                FROM messages m
                JOIN conversations c ON c.id = m.conversation_id AND c.admin_id = ? AND c.client_id = ?
                LEFT JOIN users u ON u.id = m.sender_id
                LEFT JOIN users uc ON uc.id = c.client_id
                ORDER BY m.created_at ASC, m.id ASC';
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$admin_id, $client_id]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function markReadForAdmin($admin_id, $client_id = null) {
        if ($client_id) {
            $sql = 'UPDATE messages m
                    JOIN conversations c ON c.id = m.conversation_id
                    SET m.is_read = 1
                    WHERE c.admin_id = ? AND c.client_id = ? AND m.sender_id <> ? AND m.is_read = 0';
            $stmt = $this->db->prepare($sql);
            return $stmt->execute([$admin_id, $client_id, $admin_id]);
        } else {
            $sql = 'UPDATE messages m
                    JOIN conversations c ON c.id = m.conversation_id
                    SET m.is_read = 1
                    WHERE c.admin_id = ? AND m.sender_id <> ? AND m.is_read = 0';
            $stmt = $this->db->prepare($sql);
            return $stmt->execute([$admin_id, $admin_id]);
        }
    }

    public function countUnreadForAdmin($admin_id) {
        $sql = 'SELECT COUNT(*) AS cnt
                FROM messages m
                JOIN conversations c ON c.id = m.conversation_id
                WHERE c.admin_id = ? AND m.sender_id <> ? AND m.is_read = 0';
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$admin_id, $admin_id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ? (int)$row['cnt'] : 0;
    }

    public function unreadCountsByClientForAdmin($admin_id) {
        $sql = 'SELECT c.client_id, COUNT(*) AS unread
                FROM messages m
                JOIN conversations c ON c.id = m.conversation_id
                WHERE c.admin_id = ? AND m.sender_id <> ? AND m.is_read = 0
                GROUP BY c.client_id';
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$admin_id, $admin_id]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $map = [];
        foreach ($rows as $r) {
            $map[(int)$r['client_id']] = (int)$r['unread'];
        }
        return $map;
    }

    /**
     * Marcar como leídos los mensajes entrantes para un cliente.
     * Considera entrantes aquellos cuyo sender_id NO es el propio cliente.
     */
    public function markReadForClient($client_id) {
        $sql = 'UPDATE messages m
                JOIN conversations c ON c.id = m.conversation_id
                SET m.is_read = 1
                WHERE c.client_id = ? AND m.sender_id <> ? AND m.is_read = 0';
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([(int)$client_id, (int)$client_id]);
    }

    /**
     * Contar mensajes no leídos para un cliente.
     */
    public function countUnreadForClient($client_id) {
        $sql = 'SELECT COUNT(*) AS cnt
                FROM messages m
                JOIN conversations c ON c.id = m.conversation_id
                WHERE c.client_id = ? AND m.sender_id <> ? AND m.is_read = 0';
        $stmt = $this->db->prepare($sql);
        $stmt->execute([(int)$client_id, (int)$client_id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ? (int)$row['cnt'] : 0;
    }

    /**
     * Último mensaje recibido para un administrador (de cualquier cliente), sin marcar leído
     */
    public function fetchLatestReceivedForAdmin($admin_id) {
        $sql = 'SELECT m.id, m.message, m.message_type, m.file_url, m.created_at, m.sender_id,
                       c.client_id, uc.name AS client_name
                FROM messages m
                JOIN conversations c ON c.id = m.conversation_id AND c.admin_id = ?
                LEFT JOIN users uc ON uc.id = c.client_id
                WHERE m.sender_id <> ?
                ORDER BY m.created_at DESC, m.id DESC
                LIMIT 1';
        $stmt = $this->db->prepare($sql);
        $stmt->execute([(int)$admin_id, (int)$admin_id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    /**
     * Último mensaje recibido para un cliente (de cualquier admin), sin marcar leído
     */
    public function fetchLatestReceivedForClient($client_id) {
        $sql = 'SELECT m.id, m.message, m.message_type, m.file_url, m.created_at, m.sender_id,
                       u.name AS sender_name
                FROM messages m
                JOIN conversations c ON c.id = m.conversation_id AND c.client_id = ?
                LEFT JOIN users u ON u.id = m.sender_id
                WHERE m.sender_id <> ?
                ORDER BY m.created_at DESC, m.id DESC
                LIMIT 1';
        $stmt = $this->db->prepare($sql);
        $stmt->execute([(int)$client_id, (int)$client_id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    /* ==========================
     *  CHAT ENTRE ADMINISTRADORES
     * ========================== */
    private function ensureAdminSchema() {
        // admin_conversations: par ordenado (admin_a_id < admin_b_id) único
        $this->db->exec("CREATE TABLE IF NOT EXISTS admin_conversations (
            id INT(11) NOT NULL AUTO_INCREMENT,
            admin_a_id INT(11) NOT NULL,
            admin_b_id INT(11) NOT NULL,
            status ENUM('active','archived','closed') DEFAULT 'active',
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
            PRIMARY KEY (id),
            UNIQUE KEY pair (admin_a_id, admin_b_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $this->db->exec("CREATE TABLE IF NOT EXISTS admin_messages (
            id INT(11) NOT NULL AUTO_INCREMENT,
            conversation_id INT(11) NOT NULL,
            sender_id INT(11) NOT NULL,
            message TEXT NOT NULL,
            message_type ENUM('text','image','file','system') DEFAULT 'text',
            file_url VARCHAR(500) DEFAULT NULL,
            is_read TINYINT(1) DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
            PRIMARY KEY (id),
            KEY conv_idx (conversation_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
    }

    public function getOtherAdmins($self_id) {
        $stmt = $this->db->prepare("SELECT id, name, email FROM users WHERE role IN ('admin','manager') AND id <> ? ORDER BY name");
        $stmt->execute([$self_id]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function ensureAdminPeerConversation($admin1_id, $admin2_id) {
        $a = min((int)$admin1_id, (int)$admin2_id);
        $b = max((int)$admin1_id, (int)$admin2_id);
        $sel = $this->db->prepare('SELECT id FROM admin_conversations WHERE admin_a_id = ? AND admin_b_id = ? LIMIT 1');
        $sel->execute([$a, $b]);
        $row = $sel->fetch(PDO::FETCH_ASSOC);
        if ($row && isset($row['id'])) { return (int)$row['id']; }
        $ins = $this->db->prepare('INSERT INTO admin_conversations (admin_a_id, admin_b_id, status) VALUES (?, ?, "active")');
        $ins->execute([$a, $b]);
        return (int)$this->db->lastInsertId();
    }

    public function insertAdminPeerMessage($conversation_id, $sender_id, $message, $message_type = 'text', $file_url = null) {
        $sql = 'INSERT INTO admin_messages (conversation_id, sender_id, message, message_type, file_url, is_read) VALUES (?, ?, ?, ?, ?, 0)';
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([$conversation_id, $sender_id, $message, $message_type, $file_url]);
    }

    public function fetchAdminMessagesForAdminAll($admin_id) {
        // Determinar el par opuesto (peer) por fila
        $sql = 'SELECT am.id, am.message, am.message_type, am.file_url, am.created_at, am.sender_id,
                       CASE WHEN ac.admin_a_id = ? THEN ac.admin_b_id ELSE ac.admin_a_id END AS peer_id,
                       upeer.name AS peer_name, upeer.profile_image AS peer_avatar,
                       usender.profile_image AS sender_avatar
                FROM admin_messages am
                JOIN admin_conversations ac ON ac.id = am.conversation_id AND (ac.admin_a_id = ? OR ac.admin_b_id = ?)
                LEFT JOIN users upeer ON upeer.id = (CASE WHEN ac.admin_a_id = ? THEN ac.admin_b_id ELSE ac.admin_a_id END)
                LEFT JOIN users usender ON usender.id = am.sender_id
                ORDER BY am.created_at ASC, am.id ASC';
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$admin_id, $admin_id, $admin_id, $admin_id]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function fetchAdminMessagesForAdminByPeer($admin_id, $peer_id) {
        $a = min((int)$admin_id, (int)$peer_id);
        $b = max((int)$admin_id, (int)$peer_id);
        $sql = 'SELECT am.id, am.message, am.message_type, am.file_url, am.created_at, am.sender_id,
                       ? AS peer_id, upeer.name AS peer_name, upeer.profile_image AS peer_avatar,
                       usender.profile_image AS sender_avatar
                FROM admin_messages am
                JOIN admin_conversations ac ON ac.id = am.conversation_id AND ac.admin_a_id = ? AND ac.admin_b_id = ?
                LEFT JOIN users upeer ON upeer.id = ?
                LEFT JOIN users usender ON usender.id = am.sender_id
                ORDER BY am.created_at ASC, am.id ASC';
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$peer_id, $a, $b, $peer_id]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function markReadForAdminPeers($admin_id, $peer_id = null) {
        if ($peer_id) {
            $a = min((int)$admin_id, (int)$peer_id);
            $b = max((int)$admin_id, (int)$peer_id);
            $sql = 'UPDATE admin_messages am
                    JOIN admin_conversations ac ON ac.id = am.conversation_id
                    SET am.is_read = 1
                    WHERE ac.admin_a_id = ? AND ac.admin_b_id = ? AND am.sender_id <> ? AND am.is_read = 0';
            $stmt = $this->db->prepare($sql);
            return $stmt->execute([$a, $b, $admin_id]);
        } else {
            $sql = 'UPDATE admin_messages am
                    JOIN admin_conversations ac ON ac.id = am.conversation_id
                    SET am.is_read = 1
                    WHERE (ac.admin_a_id = ? OR ac.admin_b_id = ?) AND am.sender_id <> ? AND am.is_read = 0';
            $stmt = $this->db->prepare($sql);
            return $stmt->execute([$admin_id, $admin_id, $admin_id]);
        }
    }

    public function countUnreadForAdminPeers($admin_id) {
        $sql = 'SELECT COUNT(*) AS cnt
                FROM admin_messages am
                JOIN admin_conversations ac ON ac.id = am.conversation_id
                WHERE (ac.admin_a_id = ? OR ac.admin_b_id = ?) AND am.sender_id <> ? AND am.is_read = 0';
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$admin_id, $admin_id, $admin_id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ? (int)$row['cnt'] : 0;
    }

    public function unreadCountsByPeerForAdmin($admin_id) {
        $sql = 'SELECT CASE WHEN ac.admin_a_id = ? THEN ac.admin_b_id ELSE ac.admin_a_id END AS peer_id,
                       COUNT(*) AS unread
                FROM admin_messages am
                JOIN admin_conversations ac ON ac.id = am.conversation_id
                WHERE (ac.admin_a_id = ? OR ac.admin_b_id = ?) AND am.sender_id <> ? AND am.is_read = 0
                GROUP BY peer_id';
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$admin_id, $admin_id, $admin_id, $admin_id]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $map = [];
        foreach ($rows as $r) {
            $map[(int)$r['peer_id']] = (int)$r['unread'];
        }
        return $map;
    }
}
User / IP	: 216.73.216.159
Host / Server	: 45.84.207.204 / aircan.me
System	: Linux lt-bnk-web1726.main-hosting.eu 5.14.0-611.36.1.el9_7.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Mar 3 11:23:52 EST 2026 x86_64