<?php
class Communication
{
    public static function actorByTypeAndId(string $type, int $id): ?array
    {
        Database::migrate();
        $type = normalize_internal_actor_type($type);
        if ($id <= 0) {
            return null;
        }

        if (in_array($type, ['student', 'admin'], true)) {
            $stmt = Database::pdo()->prepare('SELECT id,name,email,whatsapp,internal_code,role,profile_photo_path FROM users WHERE id=? AND deleted_at IS NULL LIMIT 1');
            $stmt->execute([$id]);
            $row = $stmt->fetch();
            if (!$row) {
                return null;
            }
            $row['actor_type'] = ($row['role'] ?? '') === 'admin' ? 'admin' : 'student';
            return $row;
        }

        if ($type === 'professional') {
            $stmt = Database::pdo()->prepare('SELECT id,name,username AS email,whatsapp,internal_code,profile_photo_path FROM professionals WHERE id=? LIMIT 1');
        } else {
            $stmt = Database::pdo()->prepare('SELECT id,name,email,whatsapp,internal_code,NULL AS profile_photo_path FROM beginner_instructors WHERE id=? LIMIT 1');
        }
        $stmt->execute([$id]);
        $row = $stmt->fetch();
        if (!$row) {
            return null;
        }
        $row['actor_type'] = $type;
        return $row;
    }

    public static function actorByCode(string $code): ?array
    {
        Database::migrate();
        $code = only_digits($code);
        if (strlen($code) !== 9) {
            return null;
        }

        $stmt = Database::pdo()->prepare('SELECT id,name,email,whatsapp,internal_code,role,profile_photo_path FROM users WHERE internal_code=? AND deleted_at IS NULL LIMIT 1');
        $stmt->execute([$code]);
        if ($row = $stmt->fetch()) {
            $row['actor_type'] = ($row['role'] ?? '') === 'admin' ? 'admin' : 'student';
            return $row;
        }

        $stmt = Database::pdo()->prepare('SELECT id,name,username AS email,whatsapp,internal_code,profile_photo_path FROM professionals WHERE internal_code=? LIMIT 1');
        $stmt->execute([$code]);
        if ($row = $stmt->fetch()) {
            $row['actor_type'] = 'professional';
            return $row;
        }

        $stmt = Database::pdo()->prepare('SELECT id,name,email,whatsapp,internal_code,NULL AS profile_photo_path FROM beginner_instructors WHERE internal_code=? LIMIT 1');
        $stmt->execute([$code]);
        if ($row = $stmt->fetch()) {
            $row['actor_type'] = 'beginner';
            return $row;
        }
        return null;
    }

    public static function allActors(): array
    {
        Database::migrate();
        $actors = [];
        foreach (Database::pdo()->query("SELECT id,name,email,whatsapp,internal_code,role,profile_photo_path FROM users WHERE deleted_at IS NULL ORDER BY name")->fetchAll() as $row) {
            $row['actor_type'] = ($row['role'] ?? '') === 'admin' ? 'admin' : 'student';
            $actors[] = $row;
        }
        foreach (Database::pdo()->query("SELECT id,name,username AS email,whatsapp,internal_code,profile_photo_path FROM professionals WHERE is_active=1 ORDER BY name")->fetchAll() as $row) {
            $row['actor_type'] = 'professional';
            $actors[] = $row;
        }
        foreach (Database::pdo()->query('SELECT id,name,email,whatsapp,internal_code,NULL AS profile_photo_path FROM beginner_instructors ORDER BY name')->fetchAll() as $row) {
            $row['actor_type'] = 'beginner';
            $actors[] = $row;
        }
        usort($actors, static fn(array $a, array $b): int => strcasecmp((string)$a['name'], (string)$b['name']));
        return $actors;
    }


    /**
     * Define quem pode iniciar/abrir uma conversa direta.
     * Aluno e instrutor somente são vinculados depois que existe um agendamento
     * entre os dois. O administrador continua podendo falar com todos.
     */
    public static function canDirectContact(string $ownerType, int $ownerId, string $contactType, int $contactId): bool
    {
        Database::migrate();
        $ownerType = normalize_internal_actor_type($ownerType);
        $contactType = normalize_internal_actor_type($contactType);
        if ($ownerId <= 0 || $contactId <= 0 || ($ownerType === $contactType && $ownerId === $contactId)) {
            return false;
        }
        if ($ownerType === 'admin' || $contactType === 'admin') {
            return true;
        }
        if ($ownerType === 'student' && $contactType === 'professional') {
            return self::hasAppointmentRelation($ownerId, $contactId);
        }
        if ($ownerType === 'professional' && $contactType === 'student') {
            return self::hasAppointmentRelation($contactId, $ownerId);
        }
        if ($ownerType === 'professional' && $contactType === 'professional') {
            return true;
        }
        // Pessoas em formação podem conversar entre si; com a administração
        // a permissão já foi liberada acima. Alunos e instrutores não acessam
        // outros cadastros por código sem vínculo de aula.
        return $ownerType === 'beginner' && $contactType === 'beginner';
    }

    private static function hasAppointmentRelation(int $studentId, int $professionalId): bool
    {
        if ($studentId <= 0 || $professionalId <= 0 || !Database::tableExists('appointments')) {
            return false;
        }
        $stmt = Database::pdo()->prepare('SELECT 1 FROM appointments WHERE student_id=? AND professional_id=? LIMIT 1');
        $stmt->execute([$studentId, $professionalId]);
        return (bool)$stmt->fetchColumn();
    }

    private static function linkedActors(string $ownerType, int $ownerId): array
    {
        Database::migrate();
        $ownerType = normalize_internal_actor_type($ownerType);
        if ($ownerType === 'student') {
            $stmt = Database::pdo()->prepare("SELECT DISTINCT p.id,p.name,p.username AS email,NULL AS whatsapp,p.internal_code,p.profile_photo_path,'professional' AS actor_type
                FROM appointments a INNER JOIN professionals p ON p.id=a.professional_id
                WHERE a.student_id=? AND p.is_active=1 ORDER BY p.name");
            $stmt->execute([$ownerId]);
            return $stmt->fetchAll();
        }
        if ($ownerType === 'professional') {
            $stmt = Database::pdo()->prepare("SELECT DISTINCT u.id,u.name,u.email,NULL AS whatsapp,u.internal_code,u.profile_photo_path,'student' AS actor_type
                FROM appointments a INNER JOIN users u ON u.id=a.student_id
                WHERE a.professional_id=? AND u.role='student' AND u.deleted_at IS NULL ORDER BY u.name");
            $stmt->execute([$ownerId]);
            $actors=$stmt->fetchAll();
            $professionals=Database::pdo()->prepare("SELECT id,name,username AS email,NULL AS whatsapp,internal_code,profile_photo_path,'professional' AS actor_type
                FROM professionals WHERE is_active=1 AND id<>? ORDER BY name");
            $professionals->execute([$ownerId]);
            return array_merge($actors,$professionals->fetchAll());
        }
        return self::allActors();
    }

    public static function directory(string $ownerType, int $ownerId, string $search = ''): array
    {
        $ownerType = normalize_internal_actor_type($ownerType);
        $search = mb_strtolower(trim($search));
        $savedKeys = [];
        foreach (self::savedContacts($ownerType, $ownerId) as $saved) {
            $savedKeys[$saved['actor_type'] . ':' . (int)$saved['id']] = true;
        }

        $rows = [];
        foreach (self::linkedActors($ownerType, $ownerId) as $actor) {
            if ($actor['actor_type'] === $ownerType && (int)$actor['id'] === $ownerId) {
                continue;
            }
            if (!self::canDirectContact($ownerType, $ownerId, (string)$actor['actor_type'], (int)$actor['id'])) {
                continue;
            }
            if ($search !== '') {
                $haystack = mb_strtolower(implode(' ', [
                    (string)($actor['name'] ?? ''),
                    (string)($actor['internal_code'] ?? ''),
                    internal_actor_label($actor['actor_type'] ?? ''),
                ]));
                if (!str_contains($haystack, $search)) {
                    continue;
                }
            }
            // Aluno/instrutor são salvos automaticamente pelo agendamento.
            $actor['is_saved'] = in_array($ownerType, ['student', 'professional'], true)
                || isset($savedKeys[$actor['actor_type'] . ':' . (int)$actor['id']]);
            $rows[] = $actor;
        }
        return $rows;
    }

    public static function savedContacts(string $ownerType, int $ownerId): array
    {
        Database::migrate();
        $ownerType = normalize_internal_actor_type($ownerType);
        $stmt = Database::pdo()->prepare('SELECT contact_type,contact_id,created_at FROM internal_contacts WHERE owner_type=? AND owner_id=? ORDER BY id DESC');
        $stmt->execute([$ownerType, $ownerId]);
        $contacts = [];
        foreach ($stmt->fetchAll() as $row) {
            $actor = self::actorByTypeAndId((string)$row['contact_type'], (int)$row['contact_id']);
            if (!$actor || !self::canDirectContact($ownerType, $ownerId, (string)$actor['actor_type'], (int)$actor['id'])) {
                continue;
            }
            // Nunca expõe WhatsApp na agenda interna de aluno/instrutor.
            if (in_array($ownerType, ['student', 'professional'], true)) {
                $actor['whatsapp'] = null;
            }
            $actor['saved_at'] = $row['created_at'];
            $contacts[] = $actor;
        }
        usort($contacts, static fn(array $a, array $b): int => strcasecmp((string)$a['name'], (string)$b['name']));
        return $contacts;
    }

    public static function saveContact(string $ownerType, int $ownerId, string $contactType, int $contactId): array
    {
        Database::migrate();
        $ownerType = normalize_internal_actor_type($ownerType);
        $contactType = normalize_internal_actor_type($contactType);
        $contact = self::actorByTypeAndId($contactType, $contactId);
        if (!$contact || ($contact['actor_type'] ?? '') !== $contactType) {
            return ['ok' => false, 'message' => 'Contato do sistema não encontrado.'];
        }
        if ($ownerType === $contactType && $ownerId === $contactId) {
            return ['ok' => false, 'message' => 'Você não pode salvar o próprio cadastro como contato.'];
        }
        if (!self::canDirectContact($ownerType, $ownerId, $contactType, $contactId)) {
            return ['ok' => false, 'message' => 'Esse contato só será liberado quando existir um agendamento entre aluno e instrutor.'];
        }
        self::insertContact($ownerType, $ownerId, $contactType, $contactId);
        return ['ok' => true, 'message' => short_person_name($contact['name']) . ' foi salvo nos seus contatos.'];
    }

    public static function deleteContact(string $ownerType, int $ownerId, string $contactType, int $contactId): bool
    {
        Database::migrate();
        $stmt = Database::pdo()->prepare('DELETE FROM internal_contacts WHERE owner_type=? AND owner_id=? AND contact_type=? AND contact_id=?');
        $stmt->execute([
            normalize_internal_actor_type($ownerType),
            $ownerId,
            normalize_internal_actor_type($contactType),
            $contactId,
        ]);
        return $stmt->rowCount() > 0;
    }

    private static function insertContact(string $ownerType, int $ownerId, string $contactType, int $contactId): void
    {
        if ($ownerId <= 0 || $contactId <= 0 || ($ownerType === $contactType && $ownerId === $contactId)) {
            return;
        }
        if (Database::driver() === 'mysql') {
            $stmt = Database::pdo()->prepare('INSERT IGNORE INTO internal_contacts (owner_type,owner_id,contact_type,contact_id,created_at) VALUES (?,?,?,?,?)');
        } else {
            $stmt = Database::pdo()->prepare('INSERT OR IGNORE INTO internal_contacts (owner_type,owner_id,contact_type,contact_id,created_at) VALUES (?,?,?,?,?)');
        }
        $stmt->execute([$ownerType, $ownerId, $contactType, $contactId, date('Y-m-d H:i:s')]);
    }

    private static function ensureMutualContacts(string $firstType, int $firstId, string $secondType, int $secondId): void
    {
        self::insertContact($firstType, $firstId, $secondType, $secondId);
        self::insertContact($secondType, $secondId, $firstType, $firstId);
    }

    public static function sendDirect(string $senderType, int $senderId, string $recipientCode, string $body, array $attachmentFile = [], array $preparedAttachment = []): array
    {
        Database::migrate();
        $recipient = self::actorByCode($recipientCode);
        if (!$recipient) {
            $group = TheoryGroup::findByCode($recipientCode);
            if ($group) {
                if (!TheoryGroup::canAccess((int)$group['id'], normalize_internal_actor_type($senderType), $senderId)) {
                    PortalMedia::delete($preparedAttachment['path'] ?? null);
                    return ['ok' => false, 'message' => 'Este código pertence a uma turma da qual você não participa.'];
                }
                return self::sendGroup($senderType, $senderId, (int)$group['id'], $body, $attachmentFile, $preparedAttachment);
            }
            PortalMedia::delete($preparedAttachment['path'] ?? null);
            return ['ok' => false, 'message' => 'Nenhum usuário ou grupo foi encontrado com esse código de 9 dígitos.'];
        }
        return self::sendDirectToActor($senderType, $senderId, (string)$recipient['actor_type'], (int)$recipient['id'], $body, $attachmentFile, $preparedAttachment);
    }

    public static function sendDirectToActor(string $senderType, int $senderId, string $recipientType, int $recipientId, string $body, array $attachmentFile = [], array $preparedAttachment = []): array
    {
        Database::migrate();
        $senderType = normalize_internal_actor_type($senderType);
        $recipientType = normalize_internal_actor_type($recipientType);
        $body = trim($body);
        $hasPreparedAttachment = !empty($preparedAttachment['path']);
        $hasAttachment = self::hasUpload($attachmentFile) || $hasPreparedAttachment;
        if ($body === '' && !$hasAttachment) {
            return ['ok' => false, 'message' => 'Digite uma mensagem ou grave/selecione um áudio.'];
        }
        if (mb_strlen($body) > 10000) {
            PortalMedia::delete($preparedAttachment['path'] ?? null);
            return ['ok' => false, 'message' => 'A mensagem ultrapassa o limite permitido.'];
        }
        $recipient = self::actorByTypeAndId($recipientType, $recipientId);
        if (!$recipient || ($recipient['actor_type'] ?? '') !== $recipientType) {
            PortalMedia::delete($preparedAttachment['path'] ?? null);
            return ['ok' => false, 'message' => 'Destinatário não encontrado.'];
        }
        if ($recipientType === $senderType && $recipientId === $senderId) {
            PortalMedia::delete($preparedAttachment['path'] ?? null);
            return ['ok' => false, 'message' => 'Você não pode enviar mensagem para o próprio cadastro.'];
        }
        if (!self::canDirectContact($senderType, $senderId, $recipientType, $recipientId)) {
            PortalMedia::delete($preparedAttachment['path'] ?? null);
            return ['ok' => false, 'message' => 'Contato indisponível. Aluno e instrutor só podem conversar depois que uma aula for agendada entre eles.'];
        }

        $attachment = ['path' => null, 'original_name' => null, 'mime_type' => null, 'file_size' => null];
        if ($hasPreparedAttachment) {
            if (!self::isAudioAttachment($preparedAttachment)) {
                PortalMedia::delete($preparedAttachment['path'] ?? null);
                return ['ok' => false, 'message' => 'O arquivo preparado não foi reconhecido como áudio.'];
            }
            $attachment = $preparedAttachment;
        } elseif (self::hasUpload($attachmentFile)) {
            $saved = PortalMedia::saveMessageAttachment($attachmentFile);
            if (empty($saved['ok'])) {
                return $saved;
            }
            if (!self::isAudioAttachment($saved)) {
                PortalMedia::delete($saved['path'] ?? null);
                return ['ok' => false, 'message' => 'Nas conversas individuais é permitido anexar somente áudio.'];
            }
            $attachment = $saved;
        }

        try {
            $stmt = Database::pdo()->prepare("INSERT INTO internal_messages (conversation_type,group_id,sender_type,sender_id,recipient_type,recipient_id,message_body,attachment_path,attachment_name,attachment_mime,attachment_size,deleted_for_all_at,created_at) VALUES ('direct',NULL,?,?,?,?,?,?,?,?,?,NULL,?)");
            $stmt->execute([
                $senderType,
                $senderId,
                $recipientType,
                $recipientId,
                $body,
                $attachment['path'] ?? null,
                $attachment['original_name'] ?? null,
                $attachment['mime_type'] ?? null,
                $attachment['file_size'] ?? null,
                date('Y-m-d H:i:s'),
            ]);
        } catch (Throwable $e) {
            PortalMedia::delete($attachment['path'] ?? null);
            return ['ok' => false, 'message' => 'Não foi possível enviar a mensagem: ' . $e->getMessage()];
        }
        self::ensureMutualContacts($senderType, $senderId, $recipientType, $recipientId);
        return [
            'ok' => true,
            'message' => $hasAttachment ? 'Áudio enviado para ' . short_person_name($recipient['name']) . '.' : 'Mensagem enviada para ' . short_person_name($recipient['name']) . '.',
            'recipient_type' => $recipientType,
            'recipient_id' => $recipientId,
            'recipient_name' => $recipient['name'],
        ];
    }

    public static function sendGroup(string $senderType, int $senderId, int $groupId, string $body, array $attachmentFile = [], array $preparedAttachment = []): array
    {
        Database::migrate();
        $senderType = normalize_internal_actor_type($senderType);
        $body = trim($body);
        $hasPreparedAttachment = !empty($preparedAttachment['path']);
        $hasAttachment = self::hasUpload($attachmentFile) || $hasPreparedAttachment;
        if ($body === '' && !$hasAttachment) {
            return ['ok' => false, 'message' => 'Digite uma mensagem ou selecione um arquivo/áudio.'];
        }
        if (mb_strlen($body) > 10000) {
            PortalMedia::delete($preparedAttachment['path'] ?? null);
            return ['ok' => false, 'message' => 'A mensagem ultrapassa o limite permitido.'];
        }
        if (!TheoryGroup::canAccess($groupId, $senderType, $senderId)) {
            return ['ok' => false, 'message' => 'Você não participa deste grupo.'];
        }

        $attachment = ['path' => null, 'original_name' => null, 'mime_type' => null, 'file_size' => null];
        if ($hasPreparedAttachment) {
            if (!self::isAudioAttachment($preparedAttachment)) {
                PortalMedia::delete($preparedAttachment['path'] ?? null);
                return ['ok' => false, 'message' => 'O arquivo preparado não foi reconhecido como áudio.'];
            }
            $attachment = $preparedAttachment;
        } elseif (self::hasUpload($attachmentFile)) {
            $saved = PortalMedia::saveMessageAttachment($attachmentFile);
            if (empty($saved['ok'])) {
                return $saved;
            }
            $isAudio = self::isAudioAttachment($saved);
            if (!$isAudio && !in_array($senderType, ['admin', 'professional'], true)) {
                PortalMedia::delete($saved['path'] ?? null);
                return ['ok' => false, 'message' => 'Alunos podem enviar áudio. Imagens e PDFs ficam disponíveis somente para o administrador ou instrutor responsável.'];
            }
            $attachment = $saved;
        }

        try {
            $stmt = Database::pdo()->prepare("INSERT INTO internal_messages (conversation_type,group_id,sender_type,sender_id,recipient_type,recipient_id,message_body,attachment_path,attachment_name,attachment_mime,attachment_size,deleted_for_all_at,created_at) VALUES ('group',?,?,?,NULL,NULL,?,?,?,?,?,NULL,?)");
            $stmt->execute([
                $groupId,
                $senderType,
                $senderId,
                $body,
                $attachment['path'] ?? null,
                $attachment['original_name'] ?? null,
                $attachment['mime_type'] ?? null,
                $attachment['file_size'] ?? null,
                date('Y-m-d H:i:s'),
            ]);
        } catch (Throwable $e) {
            PortalMedia::delete($attachment['path'] ?? null);
            return ['ok' => false, 'message' => 'Não foi possível enviar ao grupo: ' . $e->getMessage()];
        }
        $label = $hasAttachment ? (self::isAudioAttachment($attachment) ? 'Áudio enviado ao grupo.' : 'Arquivo enviado ao grupo.') : 'Mensagem enviada ao grupo.';
        return ['ok' => true, 'message' => $label, 'group_id' => $groupId];
    }

    private static function hasUpload(array $file): bool
    {
        return !empty($file['tmp_name']) || (int)($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_NO_FILE;
    }

    private static function isAudioAttachment(array $attachment): bool
    {
        $mime = strtolower(trim((string)($attachment['mime_type'] ?? $attachment['attachment_mime'] ?? '')));
        $name = strtolower(trim((string)($attachment['original_name'] ?? $attachment['attachment_name'] ?? '')));
        $extension = pathinfo($name, PATHINFO_EXTENSION);
        return str_starts_with($mime, 'audio/')
            || in_array($mime, ['video/webm', 'application/ogg'], true)
            || in_array($extension, ['webm','ogg','oga','mp3','m4a','mp4','wav','aac','3gp'], true);
    }

    public static function conversationFolders(string $actorType, int $actorId): array
    {
        Database::migrate();
        $actorType = normalize_internal_actor_type($actorType);
        $stmt = Database::pdo()->prepare("SELECT m.* FROM internal_messages m
            WHERE m.conversation_type='direct' AND m.deleted_for_all_at IS NULL
              AND ((m.sender_type=? AND m.sender_id=?) OR (m.recipient_type=? AND m.recipient_id=?))
              AND NOT EXISTS (SELECT 1 FROM internal_message_deletions d WHERE d.message_id=m.id AND d.actor_type=? AND d.actor_id=?)
            ORDER BY m.id DESC LIMIT 1000");
        $stmt->execute([$actorType, $actorId, $actorType, $actorId, $actorType, $actorId]);

        $folders = [];
        foreach ($stmt->fetchAll() as $message) {
            $mine = $message['sender_type'] === $actorType && (int)$message['sender_id'] === $actorId;
            $peerType = $mine ? (string)$message['recipient_type'] : (string)$message['sender_type'];
            $peerId = $mine ? (int)$message['recipient_id'] : (int)$message['sender_id'];
            $key = $peerType . ':' . $peerId;
            if (isset($folders[$key])) {
                continue;
            }
            $peer = self::actorByTypeAndId($peerType, $peerId);
            if (!$peer || !self::canDirectContact($actorType, $actorId, $peerType, $peerId)) {
                continue;
            }
            $folders[$key] = [
                'contact_type' => $peerType,
                'contact_id' => $peerId,
                'contact_name' => $peer['name'],
                'contact_code' => $peer['internal_code'] ?? '',
                'contact_photo' => $peer['profile_photo_path'] ?? '',
                'last_message' => trim((string)$message['message_body']) !== ''
                    ? $message['message_body']
                    : (self::isAudioAttachment($message) ? '[Áudio]' : (!empty($message['attachment_path']) ? '[Arquivo]' : '')),
                'last_at' => $message['created_at'],
                'last_is_mine' => $mine,
                'unread_count' => self::conversationUnreadCount($actorType, $actorId, $peerType, $peerId),
            ];
        }
        return array_values($folders);
    }

    public static function conversationMessages(string $actorType, int $actorId, string $contactType, int $contactId): array
    {
        Database::migrate();
        $actorType = normalize_internal_actor_type($actorType);
        $contactType = normalize_internal_actor_type($contactType);
        if (!self::canDirectContact($actorType, $actorId, $contactType, $contactId)) {
            return [];
        }
        $stmt = Database::pdo()->prepare("SELECT m.* FROM internal_messages m
            WHERE m.conversation_type='direct' AND m.deleted_for_all_at IS NULL
              AND ((m.sender_type=? AND m.sender_id=? AND m.recipient_type=? AND m.recipient_id=?)
                OR (m.sender_type=? AND m.sender_id=? AND m.recipient_type=? AND m.recipient_id=?))
              AND NOT EXISTS (SELECT 1 FROM internal_message_deletions d WHERE d.message_id=m.id AND d.actor_type=? AND d.actor_id=?)
            ORDER BY m.id ASC LIMIT 1000");
        $stmt->execute([
            $actorType, $actorId, $contactType, $contactId,
            $contactType, $contactId, $actorType, $actorId,
            $actorType, $actorId,
        ]);
        $rows = $stmt->fetchAll();
        foreach ($rows as &$row) {
            $row['is_mine'] = $row['sender_type'] === $actorType && (int)$row['sender_id'] === $actorId;
        }
        unset($row);
        self::markConversationRead($actorType, $actorId, $contactType, $contactId);
        return $rows;
    }

    private static function conversationUnreadCount(string $actorType, int $actorId, string $contactType, int $contactId): int
    {
        $stmt = Database::pdo()->prepare("SELECT COUNT(*) FROM internal_messages m
            WHERE m.conversation_type='direct' AND m.deleted_for_all_at IS NULL
              AND m.sender_type=? AND m.sender_id=? AND m.recipient_type=? AND m.recipient_id=?
              AND NOT EXISTS (SELECT 1 FROM internal_message_reads r WHERE r.message_id=m.id AND r.actor_type=? AND r.actor_id=?)");
        $stmt->execute([$contactType, $contactId, $actorType, $actorId, $actorType, $actorId]);
        return (int)$stmt->fetchColumn();
    }

    private static function markConversationRead(string $actorType, int $actorId, string $contactType, int $contactId): void
    {
        $stmt = Database::pdo()->prepare("SELECT id FROM internal_messages
            WHERE conversation_type='direct' AND deleted_for_all_at IS NULL
              AND sender_type=? AND sender_id=? AND recipient_type=? AND recipient_id=?");
        $stmt->execute([$contactType, $contactId, $actorType, $actorId]);
        foreach ($stmt->fetchAll() as $row) {
            self::markRead((int)$row['id'], $actorType, $actorId);
        }
    }

    public static function directMessages(string $actorType, int $actorId): array
    {
        $rows = [];
        foreach (self::conversationFolders($actorType, $actorId) as $folder) {
            foreach (self::conversationMessages($actorType, $actorId, (string)$folder['contact_type'], (int)$folder['contact_id']) as $message) {
                $peerName = (string)$folder['contact_name'];
                $message['sender_name'] = !empty($message['is_mine']) ? 'Você' : $peerName;
                $message['recipient_name'] = !empty($message['is_mine']) ? $peerName : 'Você';
                $rows[] = $message;
            }
        }
        usort($rows, static fn(array $a, array $b): int => (int)$b['id'] <=> (int)$a['id']);
        return $rows;
    }

    public static function groupMessages(int $groupId, string $actorType, int $actorId): array
    {
        Database::migrate();
        if (!TheoryGroup::canAccess($groupId, $actorType, $actorId)) {
            return [];
        }
        $stmt = Database::pdo()->prepare("SELECT m.* FROM internal_messages m
            WHERE m.conversation_type='group' AND m.group_id=? AND m.deleted_for_all_at IS NULL
              AND NOT EXISTS (SELECT 1 FROM internal_message_deletions d WHERE d.message_id=m.id AND d.actor_type=? AND d.actor_id=?)
            ORDER BY m.id ASC LIMIT 500");
        $stmt->execute([$groupId, normalize_internal_actor_type($actorType), $actorId]);
        $rows = $stmt->fetchAll();
        foreach ($rows as &$row) {
            $sender = self::actorByTypeAndId($row['sender_type'], (int)$row['sender_id']);
            $row['sender_name'] = short_person_name($sender['name'] ?? 'Cadastro removido');
            $row['sender_label'] = internal_actor_label($row['sender_type']);
            $row['is_mine'] = $row['sender_type'] === normalize_internal_actor_type($actorType) && (int)$row['sender_id'] === $actorId;
        }
        unset($row);
        self::markGroupRead($groupId, $actorType, $actorId);
        return $rows;
    }


    public static function professionalGroupMessages(int $groupId, int $professionalId): array
    {
        Database::migrate();
        if(!ProfessionalChatGroup::canAccess($groupId,$professionalId)) return [];
        $stmt=Database::pdo()->prepare("SELECT m.* FROM internal_messages m
            WHERE m.conversation_type='professional_group' AND m.group_id=? AND m.deleted_for_all_at IS NULL
              AND NOT EXISTS (SELECT 1 FROM internal_message_deletions d WHERE d.message_id=m.id AND d.actor_type='professional' AND d.actor_id=?)
            ORDER BY m.id ASC LIMIT 1000");
        $stmt->execute([$groupId,$professionalId]);
        $rows=$stmt->fetchAll();
        foreach($rows as &$row){
            $sender=self::actorByTypeAndId('professional',(int)$row['sender_id']);
            $row['sender_name']=short_person_name($sender['name']??'Instrutor removido');
            $row['is_mine']=(int)$row['sender_id']===$professionalId;
            self::markRead((int)$row['id'],'professional',$professionalId);
        }
        unset($row);
        return $rows;
    }

    public static function sendProfessionalGroup(int $professionalId, int $groupId, string $body, array $attachmentFile=[], array $preparedAttachment=[]): array
    {
        Database::migrate();
        if(!ProfessionalChatGroup::canAccess($groupId,$professionalId)){
            PortalMedia::delete($preparedAttachment['path']??null);
            return ['ok'=>false,'message'=>'Grupo privado não encontrado ou sem acesso.'];
        }
        $body=trim($body);
        $hasPrepared=!empty($preparedAttachment['path']);
        $hasUpload=self::hasUpload($attachmentFile);
        if($body===''&&!$hasPrepared&&!$hasUpload) return ['ok'=>false,'message'=>'Digite uma mensagem ou grave/selecione um áudio.'];
        $attachment=['path'=>null,'original_name'=>null,'mime_type'=>null,'file_size'=>null];
        if($hasPrepared){
            if(!self::isAudioAttachment($preparedAttachment)){
                PortalMedia::delete($preparedAttachment['path']??null);
                return ['ok'=>false,'message'=>'O arquivo preparado não foi reconhecido como áudio.'];
            }
            $attachment=$preparedAttachment;
        }elseif($hasUpload){
            $saved=PortalMedia::saveMessageAttachment($attachmentFile);
            if(empty($saved['ok'])) return $saved;
            if(!self::isAudioAttachment($saved)){
                PortalMedia::delete($saved['path']??null);
                return ['ok'=>false,'message'=>'Nos grupos privados de instrutores é permitido anexar áudio.'];
            }
            $attachment=$saved;
        }
        try{
            $stmt=Database::pdo()->prepare("INSERT INTO internal_messages (conversation_type,group_id,sender_type,sender_id,recipient_type,recipient_id,message_body,attachment_path,attachment_name,attachment_mime,attachment_size,deleted_for_all_at,created_at) VALUES ('professional_group',?,'professional',?,NULL,NULL,?,?,?,?,?,NULL,?)");
            $stmt->execute([$groupId,$professionalId,$body,$attachment['path']??null,$attachment['original_name']??null,$attachment['mime_type']??null,$attachment['file_size']??null,date('Y-m-d H:i:s')]);
        }catch(Throwable $e){
            PortalMedia::delete($attachment['path']??null);
            return ['ok'=>false,'message'=>'Não foi possível enviar ao grupo: '.$e->getMessage()];
        }
        return ['ok'=>true,'message'=>($hasPrepared||$hasUpload)?'Áudio enviado ao grupo de instrutores.':'Mensagem enviada ao grupo de instrutores.'];
    }

    public static function attachmentForActor(int $messageId, string $actorType, int $actorId): ?array
    {
        Database::migrate();
        $actorType = normalize_internal_actor_type($actorType);
        $stmt = Database::pdo()->prepare("SELECT * FROM internal_messages WHERE id=? AND deleted_for_all_at IS NULL LIMIT 1");
        $stmt->execute([$messageId]);
        $message = $stmt->fetch();
        if (!$message || empty($message['attachment_path'])) {
            return null;
        }
        if (($message['conversation_type'] ?? '') === 'group') {
            return TheoryGroup::canAccess((int)$message['group_id'], $actorType, $actorId) ? $message : null;
        }
        if (($message['conversation_type'] ?? '') === 'professional_group') {
            return $actorType === 'professional' && ProfessionalChatGroup::canAccess((int)$message['group_id'], $actorId) ? $message : null;
        }
        $allowed = (($message['sender_type'] ?? '') === $actorType && (int)$message['sender_id'] === $actorId)
            || (($message['recipient_type'] ?? '') === $actorType && (int)$message['recipient_id'] === $actorId);
        return $allowed ? $message : null;
    }

    public static function groupAttachmentForActor(int $messageId, string $actorType, int $actorId): ?array
    {
        $message = self::attachmentForActor($messageId, $actorType, $actorId);
        return $message && ($message['conversation_type'] ?? '') === 'group' ? $message : null;
    }

    public static function unreadAudioCount(string $actorType, int $actorId): int
    {
        Database::migrate();
        $actorType = normalize_internal_actor_type($actorType);
        $audioSql = "(LOWER(COALESCE(m.attachment_mime,'')) LIKE 'audio/%'
            OR LOWER(COALESCE(m.attachment_mime,'')) IN ('video/webm','application/ogg')
            OR LOWER(COALESCE(m.attachment_name,'')) LIKE '%.webm'
            OR LOWER(COALESCE(m.attachment_name,'')) LIKE '%.ogg'
            OR LOWER(COALESCE(m.attachment_name,'')) LIKE '%.oga'
            OR LOWER(COALESCE(m.attachment_name,'')) LIKE '%.mp3'
            OR LOWER(COALESCE(m.attachment_name,'')) LIKE '%.m4a'
            OR LOWER(COALESCE(m.attachment_name,'')) LIKE '%.wav'
            OR LOWER(COALESCE(m.attachment_name,'')) LIKE '%.aac')";

        $direct = Database::pdo()->prepare("SELECT COUNT(*) FROM internal_messages m
            WHERE m.conversation_type='direct' AND m.deleted_for_all_at IS NULL
              AND m.recipient_type=? AND m.recipient_id=? AND {$audioSql}
              AND NOT EXISTS (SELECT 1 FROM internal_message_reads r WHERE r.message_id=m.id AND r.actor_type=? AND r.actor_id=?)");
        $direct->execute([$actorType, $actorId, $actorType, $actorId]);
        $count = (int)$direct->fetchColumn();

        foreach (TheoryGroup::forActor($actorType, $actorId) as $group) {
            $stmt = Database::pdo()->prepare("SELECT COUNT(*) FROM internal_messages m
                WHERE m.conversation_type='group' AND m.group_id=? AND m.deleted_for_all_at IS NULL
                  AND NOT (m.sender_type=? AND m.sender_id=?) AND {$audioSql}
                  AND NOT EXISTS (SELECT 1 FROM internal_message_reads r WHERE r.message_id=m.id AND r.actor_type=? AND r.actor_id=?)");
            $stmt->execute([(int)$group['id'], $actorType, $actorId, $actorType, $actorId]);
            $count += (int)$stmt->fetchColumn();
        }
        if($actorType==='professional'){
            foreach(ProfessionalChatGroup::forProfessional($actorId) as $privateGroup){
                $stmt=Database::pdo()->prepare("SELECT COUNT(*) FROM internal_messages m
                    WHERE m.conversation_type='professional_group' AND m.group_id=? AND m.deleted_for_all_at IS NULL
                      AND NOT (m.sender_type='professional' AND m.sender_id=?) AND {$audioSql}
                      AND NOT EXISTS (SELECT 1 FROM internal_message_reads r WHERE r.message_id=m.id AND r.actor_type='professional' AND r.actor_id=?)");
                $stmt->execute([(int)$privateGroup['id'],$actorId,$actorId]);
                $count+=(int)$stmt->fetchColumn();
            }
        }
        return $count;
    }

    public static function unreadCount(string $actorType, int $actorId): int
    {
        Database::migrate();
        $actorType = normalize_internal_actor_type($actorType);

        $direct = Database::pdo()->prepare("SELECT COUNT(*) FROM internal_messages m
            WHERE m.conversation_type='direct'
              AND m.deleted_for_all_at IS NULL
              AND m.recipient_type=? AND m.recipient_id=?
              AND NOT EXISTS (
                  SELECT 1 FROM internal_message_reads r
                  WHERE r.message_id=m.id AND r.actor_type=? AND r.actor_id=?
              )");
        $direct->execute([$actorType, $actorId, $actorType, $actorId]);
        $count = (int)$direct->fetchColumn();

        foreach (TheoryGroup::forActor($actorType, $actorId) as $group) {
            $groupUnread = Database::pdo()->prepare("SELECT COUNT(*) FROM internal_messages m
                WHERE m.conversation_type='group'
                  AND m.group_id=?
                  AND m.deleted_for_all_at IS NULL
                  AND NOT (m.sender_type=? AND m.sender_id=?)
                  AND NOT EXISTS (
                      SELECT 1 FROM internal_message_reads r
                      WHERE r.message_id=m.id AND r.actor_type=? AND r.actor_id=?
                  )");
            $groupUnread->execute([(int)$group['id'], $actorType, $actorId, $actorType, $actorId]);
            $count += (int)$groupUnread->fetchColumn();
        }
        if($actorType==='professional'){
            foreach(ProfessionalChatGroup::forProfessional($actorId) as $privateGroup){
                $groupUnread=Database::pdo()->prepare("SELECT COUNT(*) FROM internal_messages m
                    WHERE m.conversation_type='professional_group' AND m.group_id=? AND m.deleted_for_all_at IS NULL
                      AND NOT (m.sender_type='professional' AND m.sender_id=?)
                      AND NOT EXISTS (SELECT 1 FROM internal_message_reads r WHERE r.message_id=m.id AND r.actor_type='professional' AND r.actor_id=?)");
                $groupUnread->execute([(int)$privateGroup['id'],$actorId,$actorId]);
                $count+=(int)$groupUnread->fetchColumn();
            }
        }
        return $count;
    }

    private static function markGroupRead(int $groupId, string $actorType, int $actorId): void
    {
        $stmt = Database::pdo()->prepare("SELECT id FROM internal_messages WHERE conversation_type='group' AND group_id=? AND deleted_for_all_at IS NULL");
        $stmt->execute([$groupId]);
        foreach ($stmt->fetchAll() as $row) {
            self::markRead((int)$row['id'], $actorType, $actorId);
        }
    }

    private static function markRead(int $messageId, string $actorType, int $actorId): void
    {
        if (Database::driver() === 'mysql') {
            $stmt = Database::pdo()->prepare('INSERT IGNORE INTO internal_message_reads (message_id,actor_type,actor_id,read_at) VALUES (?,?,?,?)');
        } else {
            $stmt = Database::pdo()->prepare('INSERT OR IGNORE INTO internal_message_reads (message_id,actor_type,actor_id,read_at) VALUES (?,?,?,?)');
        }
        $stmt->execute([$messageId, normalize_internal_actor_type($actorType), $actorId, date('Y-m-d H:i:s')]);
    }

    public static function deleteForSelf(int $messageId, string $actorType, int $actorId): array
    {
        Database::migrate();
        $actorType = normalize_internal_actor_type($actorType);
        $stmt = Database::pdo()->prepare('SELECT * FROM internal_messages WHERE id=? AND deleted_for_all_at IS NULL');
        $stmt->execute([$messageId]);
        $message = $stmt->fetch();
        if (!$message) {
            return ['ok' => false, 'message' => 'Mensagem não encontrada.'];
        }
        $allowed = ($message['sender_type'] === $actorType && (int)$message['sender_id'] === $actorId)
            || ($message['conversation_type'] === 'direct' && $message['recipient_type'] === $actorType && (int)$message['recipient_id'] === $actorId)
            || ($message['conversation_type'] === 'group' && TheoryGroup::canAccess((int)$message['group_id'], $actorType, $actorId))
            || ($message['conversation_type'] === 'professional_group' && $actorType === 'professional' && ProfessionalChatGroup::canAccess((int)$message['group_id'], $actorId));
        if (!$allowed) {
            return ['ok' => false, 'message' => 'Você não pode apagar esta mensagem.'];
        }
        if (Database::driver() === 'mysql') {
            $insert = Database::pdo()->prepare('INSERT IGNORE INTO internal_message_deletions (message_id,actor_type,actor_id,deleted_at) VALUES (?,?,?,?)');
        } else {
            $insert = Database::pdo()->prepare('INSERT OR IGNORE INTO internal_message_deletions (message_id,actor_type,actor_id,deleted_at) VALUES (?,?,?,?)');
        }
        $insert->execute([$messageId, $actorType, $actorId, date('Y-m-d H:i:s')]);
        return ['ok' => true, 'message' => 'Mensagem apagada somente para você.'];
    }

    public static function deleteForAll(int $messageId, string $actorType, int $actorId): array
    {
        Database::migrate();
        $actorType = normalize_internal_actor_type($actorType);
        $stmt = Database::pdo()->prepare('SELECT * FROM internal_messages WHERE id=? AND deleted_for_all_at IS NULL');
        $stmt->execute([$messageId]);
        $message = $stmt->fetch();
        if (!$message) {
            return ['ok' => false, 'message' => 'Mensagem não encontrada.'];
        }
        $privateProfessionalConversation = ($message['conversation_type'] ?? '') === 'professional_group'
            || (($message['conversation_type'] ?? '') === 'direct'
                && ($message['sender_type'] ?? '') === 'professional'
                && ($message['recipient_type'] ?? '') === 'professional');
        if ($actorType === 'admin' && $privateProfessionalConversation) {
            return ['ok' => false, 'message' => 'Conversa privada entre instrutores.'];
        }
        if ($actorType !== 'admin' && !($message['sender_type'] === $actorType && (int)$message['sender_id'] === $actorId)) {
            return ['ok' => false, 'message' => 'Somente quem enviou ou o administrador pode apagar para todos.'];
        }
        $update = Database::pdo()->prepare('UPDATE internal_messages SET deleted_for_all_at=? WHERE id=?');
        $update->execute([date('Y-m-d H:i:s'), $messageId]);
        if (!empty($message['attachment_path'])) {
            PortalMedia::delete((string)$message['attachment_path']);
        }
        return ['ok' => true, 'message' => 'Mensagem apagada para todos.'];
    }

    public static function allRecentForAdmin(): array
    {
        Database::migrate();
        $rows = Database::pdo()->query("SELECT * FROM internal_messages m
            WHERE m.deleted_for_all_at IS NULL
              AND m.conversation_type <> 'professional_group'
              AND NOT (m.conversation_type='direct' AND m.sender_type='professional' AND m.recipient_type='professional')
            ORDER BY m.id DESC LIMIT 500")->fetchAll();
        foreach ($rows as &$row) {
            $sender = self::actorByTypeAndId($row['sender_type'], (int)$row['sender_id']);
            $row['sender_name'] = $row['conversation_type'] === 'group' ? short_person_name($sender['name'] ?? '') : ($sender['name'] ?? 'Cadastro removido');
            if ($row['conversation_type'] === 'direct') {
                $recipient = self::actorByTypeAndId($row['recipient_type'], (int)$row['recipient_id']);
                $row['destination_name'] = $recipient['name'] ?? 'Cadastro removido';
            } else {
                $group = TheoryGroup::find((int)$row['group_id']);
                $row['destination_name'] = $group['name'] ?? 'Grupo removido';
            }
        }
        unset($row);
        return $rows;
    }
}
