<?php

namespace App\Models;

use CodeIgniter\Model;

class MessageConversationModel extends Model
{
    protected $table         = 'message_conversations';
    protected $primaryKey    = 'id';
    protected $returnType    = 'array';
    protected $useTimestamps = true;
    protected $dateFormat    = 'datetime';

    protected $allowedFields = [
        'type', 'name', 'description', 'created_by', 'is_system',
    ];

    public function getPublicChannel(): ?array
    {
        return $this->where('type', 'public')
            ->where('is_system', 1)
            ->first();
    }

    /**
     * Conversations visible to a user: public + groups/DMs they belong to.
     *
     * @return list<array<string, mixed>>
     */
    public function listForUser(int $userId): array
    {
        $db = $this->db;

        $sql = "
            SELECT
                c.id,
                c.type,
                c.name,
                c.description,
                c.is_system,
                c.updated_at,
                (
                    SELECT m.body
                    FROM messages m
                    WHERE m.conversation_id = c.id AND m.deleted_at IS NULL
                    ORDER BY m.id DESC
                    LIMIT 1
                ) AS last_message,
                (
                    SELECT m.created_at
                    FROM messages m
                    WHERE m.conversation_id = c.id AND m.deleted_at IS NULL
                    ORDER BY m.id DESC
                    LIMIT 1
                ) AS last_message_at,
                (
                    SELECT COUNT(*)
                    FROM messages m
                    WHERE m.conversation_id = c.id
                      AND m.deleted_at IS NULL
                      AND m.sender_id != ?
                      AND mp.id IS NOT NULL
                      AND (mp.last_read_at IS NULL OR m.created_at > mp.last_read_at)
                ) AS unread_count,
                (
                    SELECT CONCAT(u.first_name, ' ', u.last_name)
                    FROM message_participants mp2
                    JOIN users u ON u.id = mp2.user_id AND u.deleted_at IS NULL
                    WHERE mp2.conversation_id = c.id
                      AND mp2.user_id != ?
                      AND c.type = 'direct'
                    LIMIT 1
                ) AS peer_name,
                (
                    SELECT mp2.user_id
                    FROM message_participants mp2
                    WHERE mp2.conversation_id = c.id
                      AND mp2.user_id != ?
                      AND c.type = 'direct'
                    LIMIT 1
                ) AS peer_id
            FROM message_conversations c
            LEFT JOIN message_participants mp
                ON mp.conversation_id = c.id AND mp.user_id = ?
            WHERE c.type = 'public'
               OR mp.user_id IS NOT NULL
            ORDER BY COALESCE(
                (
                    SELECT m.created_at
                    FROM messages m
                    WHERE m.conversation_id = c.id AND m.deleted_at IS NULL
                    ORDER BY m.id DESC
                    LIMIT 1
                ),
                c.updated_at,
                c.created_at
            ) DESC
        ";

        return $db->query($sql, [$userId, $userId, $userId, $userId])->getResultArray();
    }

    public function findDirectBetween(int $userA, int $userB): ?array
    {
        $sql = "
            SELECT c.*
            FROM message_conversations c
            JOIN message_participants p1 ON p1.conversation_id = c.id AND p1.user_id = ?
            JOIN message_participants p2 ON p2.conversation_id = c.id AND p2.user_id = ?
            WHERE c.type = 'direct'
            LIMIT 1
        ";

        $row = $this->db->query($sql, [$userA, $userB])->getRowArray();

        return $row ?: null;
    }
}
