<?php
declare(strict_types=1);

namespace App\Models;

use PDO;

class Conversation extends BaseModel
{
    protected string $table = 'conversations';

    public function findOrCreatePrivate(int $userA, int $userB): int
    {
        $existing = $this->findPrivateBetween($userA, $userB);
        if ($existing) {
            return (int) $existing['id'];
        }

        $pdo = $this->db();
        $pdo->beginTransaction();

        try {
            $statement = $pdo->prepare('INSERT INTO conversations (type, created_at, updated_at) VALUES ("private", NOW(), NOW())');
            $statement->execute();
            $conversationId = (int) $pdo->lastInsertId();

            $participants = $pdo->prepare(
                'INSERT INTO conversation_participants (conversation_id, user_id, created_at)
                 VALUES (:conversation_id, :user_id, NOW())'
            );
            $participants->execute(['conversation_id' => $conversationId, 'user_id' => $userA]);
            $participants->execute(['conversation_id' => $conversationId, 'user_id' => $userB]);
            $pdo->commit();

            return $conversationId;
        } catch (\Throwable $exception) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }

            throw $exception;
        }
    }

    public function findPrivateBetween(int $userA, int $userB): ?array
    {
        $statement = $this->db()->prepare(
            'SELECT c.*
             FROM conversations c
             INNER JOIN conversation_participants p1 ON p1.conversation_id = c.id AND p1.user_id = :user_a
             INNER JOIN conversation_participants p2 ON p2.conversation_id = c.id AND p2.user_id = :user_b
             WHERE c.type = "private"
             LIMIT 1'
        );
        $statement->execute(['user_a' => $userA, 'user_b' => $userB]);
        $row = $statement->fetch();

        return $row ?: null;
    }

    public function userCanAccess(int $conversationId, int $userId): bool
    {
        $statement = $this->db()->prepare(
            'SELECT 1 FROM conversation_participants
             WHERE conversation_id = :conversation_id AND user_id = :user_id AND archived_at IS NULL
             LIMIT 1'
        );
        $statement->execute(['conversation_id' => $conversationId, 'user_id' => $userId]);

        return (bool) $statement->fetchColumn();
    }

    public function listForUser(int $userId): array
    {
        $statement = $this->db()->prepare(
            'SELECT c.id,
                    c.type,
                    c.updated_at,
                    other_user.id AS other_user_id,
                    other_user.username AS other_username,
                    COALESCE(other_profile.display_name, legacy_profile.display_name, other_user.nickname, other_user.username) AS other_display_name,
                    COALESCE(other_profile.avatar, legacy_profile.avatar_path) AS other_avatar,
                    m.id AS last_message_id,
                    m.sender_id AS last_sender_id,
                    m.body AS last_body,
                    m.message_type AS last_message_type,
                    m.status AS last_status,
                    m.is_deleted AS last_is_deleted,
                    m.created_at AS last_message_at,
                    SUM(CASE WHEN unread.id IS NOT NULL THEN 1 ELSE 0 END) AS unread_count
             FROM conversations c
             INNER JOIN conversation_participants me ON me.conversation_id = c.id AND me.user_id = :user_id_me
             LEFT JOIN conversation_participants other_participant
                    ON other_participant.conversation_id = c.id AND other_participant.user_id <> :user_id_other
             LEFT JOIN users other_user ON other_user.id = other_participant.user_id
             LEFT JOIN user_profiles other_profile ON other_profile.user_id = other_user.id
             LEFT JOIN profiles legacy_profile ON legacy_profile.user_id = other_user.id
             LEFT JOIN messages m ON m.id = (
                 SELECT mx.id FROM messages mx
                 WHERE mx.conversation_id = c.id
                 ORDER BY mx.created_at DESC, mx.id DESC
                 LIMIT 1
             )
             LEFT JOIN messages unread
                    ON unread.conversation_id = c.id
                   AND unread.sender_id <> :user_id_unread
                   AND unread.is_deleted = 0
                   AND (me.last_read_at IS NULL OR unread.created_at > me.last_read_at)
             WHERE me.archived_at IS NULL
             GROUP BY c.id, c.type, c.updated_at, other_user.id, other_user.username, other_profile.display_name,
                      legacy_profile.display_name, other_user.nickname, other_profile.avatar, legacy_profile.avatar_path,
                      m.id, m.sender_id, m.body, m.message_type, m.status, m.is_deleted, m.created_at
             ORDER BY COALESCE(m.created_at, c.updated_at) DESC'
        );
        $statement->execute([
            'user_id_me' => $userId,
            'user_id_other' => $userId,
            'user_id_unread' => $userId,
        ]);

        return $statement->fetchAll();
    }

    public function markRead(int $conversationId, int $userId): void
    {
        $statement = $this->db()->prepare(
            'UPDATE conversation_participants
             SET last_read_at = NOW()
             WHERE conversation_id = :conversation_id AND user_id = :user_id'
        );
        $statement->execute(['conversation_id' => $conversationId, 'user_id' => $userId]);
    }

    public function participants(int $conversationId): array
    {
        $statement = $this->db()->prepare('SELECT user_id FROM conversation_participants WHERE conversation_id = :conversation_id');
        $statement->execute(['conversation_id' => $conversationId]);

        return array_map('intval', $statement->fetchAll(PDO::FETCH_COLUMN));
    }
}
