<?php
require_once APP_PATH . '/Core/BaseModel.php';

class Message extends BaseModel {
    protected string $table = 'messages';

    public function getConversations(int $userId): array {
        return $this->fetchAll(
            "SELECT c.*,
                    student.first_name AS student_first, student.last_name AS student_last, student.avatar AS student_avatar,
                    mentor.first_name  AS mentor_first,  mentor.last_name  AS mentor_last,  mentor.avatar  AS mentor_avatar,
                    (SELECT body FROM messages WHERE conversation_id = c.id ORDER BY created_at DESC LIMIT 1) AS last_message,
                    (SELECT created_at FROM messages WHERE conversation_id = c.id ORDER BY created_at DESC LIMIT 1) AS last_message_at,
                    (SELECT COUNT(*) FROM messages WHERE conversation_id = c.id AND is_read = 0 AND sender_id != ?) AS unread_count
             FROM conversations c
             JOIN users student ON student.id = c.student_id
             JOIN users mentor  ON mentor.id  = c.mentor_id
             WHERE c.student_id = ? OR c.mentor_id = ?
             ORDER BY last_message_at DESC",
            [$userId, $userId, $userId]
        );
    }

    public function getOrCreateConversation(int $studentId, int $mentorId): int {
        $conv = $this->fetchOne(
            "SELECT id FROM conversations WHERE student_id = ? AND mentor_id = ?",
            [$studentId, $mentorId]
        );
        if ($conv) return $conv['id'];

        $this->execute(
            "INSERT INTO conversations (student_id, mentor_id) VALUES (?, ?)",
            [$studentId, $mentorId]
        );
        return (int) $this->db->lastInsertId();
    }

    public function getMessages(int $conversationId, int $limit = 50): array {
        return $this->fetchAll(
            "SELECT m.*, u.first_name, u.last_name, u.avatar, u.role
             FROM messages m
             JOIN users u ON u.id = m.sender_id
             WHERE m.conversation_id = ?
             ORDER BY m.created_at ASC
             LIMIT ?",
            [$conversationId, $limit]
        );
    }

    public function sendMessage(int $conversationId, int $senderId, string $body): int {
        $this->execute(
            "INSERT INTO messages (conversation_id, sender_id, body) VALUES (?, ?, ?)",
            [$conversationId, $senderId, $body]
        );
        $id = (int) $this->db->lastInsertId();

        // Update conversation timestamp
        $this->execute(
            "UPDATE conversations SET last_message_at = NOW() WHERE id = ?",
            [$conversationId]
        );
        return $id;
    }

    public function markRead(int $conversationId, int $userId): void {
        $this->execute(
            "UPDATE messages SET is_read = 1 WHERE conversation_id = ? AND sender_id != ?",
            [$conversationId, $userId]
        );
    }

    public function getTotalUnread(int $userId): int {
        $result = $this->fetchOne(
            "SELECT COUNT(*) AS cnt FROM messages m
             JOIN conversations c ON c.id = m.conversation_id
             WHERE m.is_read = 0 AND m.sender_id != ?
             AND (c.student_id = ? OR c.mentor_id = ?)",
            [$userId, $userId, $userId]
        );
        return (int) ($result['cnt'] ?? 0);
    }

    public function canAccessConversation(int $conversationId, int $userId): bool {
        return $this->fetchOne(
            "SELECT id FROM conversations WHERE id = ? AND (student_id = ? OR mentor_id = ?)",
            [$conversationId, $userId, $userId]
        ) !== null;
    }
}
