<?php

namespace Core\App\Models;

use PDO;

class TeamMessageModel
{
    private PDO $db;
    private const DELETED_PLACEHOLDER = '__deleted__';

    public function __construct()
    {
        $this->db = \db();
        $this->ensureSchema();
    }

    /**
     * Get all admins except current (for conversation list)
     */
    public function getAdminsForChat(int $currentId): array
    {
        $stmt = $this->db->prepare("
            SELECT a.id, a.username, a.email, a.avatar, a.role, a.is_active,
                   a.last_seen_at,
                   (SELECT COUNT(*) FROM team_messages
                    WHERE sender_id = a.id
                      AND receiver_id = :me
                      AND is_read = 0
                      AND deleted_at IS NULL) AS unread_count,
                   (SELECT MAX(created_at) FROM team_messages
                    WHERE ((sender_id = a.id AND receiver_id = :me3)
                        OR (sender_id = :me4 AND receiver_id = a.id))) AS last_message_at
            FROM admins a
            WHERE a.id != :me2 AND a.is_active = 1
            ORDER BY last_message_at DESC, a.role ASC, a.username ASC
        ");
        $stmt->execute([':me' => $currentId, ':me2' => $currentId, ':me3' => $currentId, ':me4' => $currentId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Get messages between two admins
     */
    public function getConversation(int $adminA, int $adminB, int $limit = 50, int $offset = 0): array
    {
        $stmt = $this->db->prepare("
            SELECT m.id, m.sender_id, m.receiver_id, m.content,
                   m.attachment_path, m.attachment_type, m.attachment_kind, m.attachment_name, m.attachment_size,
                   m.is_read, m.delivered_at, m.seen_at, m.is_pinned, m.created_at,
                   m.deleted_at, m.deleted_by_admin_id,
                   s.username AS sender_name, s.avatar AS sender_avatar,
                   r.username AS receiver_name,
                   d.username AS deleted_by_name
            FROM team_messages m
            JOIN admins s ON s.id = m.sender_id
            JOIN admins r ON r.id = m.receiver_id
            LEFT JOIN admins d ON d.id = m.deleted_by_admin_id
            WHERE (m.sender_id = :a AND m.receiver_id = :b)
               OR (m.sender_id = :b2 AND m.receiver_id = :a2)
            ORDER BY m.is_pinned DESC, m.id ASC
            LIMIT :lim OFFSET :off
        ");
        $stmt->bindValue(':a',   $adminA, PDO::PARAM_INT);
        $stmt->bindValue(':b',   $adminB, PDO::PARAM_INT);
        $stmt->bindValue(':b2',  $adminB, PDO::PARAM_INT);
        $stmt->bindValue(':a2',  $adminA, PDO::PARAM_INT);
        $stmt->bindValue(':lim', $limit,  PDO::PARAM_INT);
        $stmt->bindValue(':off', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return array_map([$this, 'normalizeForParticipant'], $stmt->fetchAll(PDO::FETCH_ASSOC) ?: []);
    }

    /**
     * Send a message (with optional attachment)
     */
    public function send(int $senderId, int $receiverId, string $content, ?array $attachment = null): int
    {
        $stmt = $this->db->prepare("
            INSERT INTO team_messages
                (sender_id, receiver_id, content, attachment_path, attachment_type, attachment_kind, attachment_name, attachment_size)
            VALUES
                (:sender, :receiver, :content, :apath, :atype, :akind, :aname, :asize)
        ");
        $stmt->execute([
            ':sender'   => $senderId,
            ':receiver' => $receiverId,
            ':content'  => trim($content),
            ':apath'    => $attachment['path'] ?? null,
            ':atype'    => $attachment['type'] ?? null,
            ':akind'    => $attachment['kind'] ?? null,
            ':aname'    => $attachment['name'] ?? null,
            ':asize'    => $attachment['size'] ?? null,
        ]);
        return (int)$this->db->lastInsertId();
    }

    /**
     * Get a single message by ID
     */
    public function getById(int $id): ?array
    {
        $stmt = $this->db->prepare("
            SELECT m.id, m.sender_id, m.receiver_id, m.content,
                   m.attachment_path, m.attachment_type, m.attachment_kind, m.attachment_name, m.attachment_size,
                   m.is_read, m.delivered_at, m.seen_at, m.is_pinned, m.created_at,
                   m.deleted_at, m.deleted_by_admin_id,
                   s.username AS sender_name, s.avatar AS sender_avatar,
                   d.username AS deleted_by_name
            FROM team_messages m
            JOIN admins s ON s.id = m.sender_id
            LEFT JOIN admins d ON d.id = m.deleted_by_admin_id
            WHERE m.id = :id
        ");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ? $this->normalizeForParticipant($row) : null;
    }

    /**
     * Mark messages as read (all from sender to receiver)
     */
    public function markRead(int $senderId, int $receiverId): void
    {
        $stmt = $this->db->prepare("
            UPDATE team_messages
            SET is_read = 1,
                delivered_at = COALESCE(delivered_at, UTC_TIMESTAMP()),
                seen_at = COALESCE(seen_at, UTC_TIMESTAMP())
            WHERE sender_id = :sender AND receiver_id = :receiver AND is_read = 0
        ");
        $stmt->execute([':sender' => $senderId, ':receiver' => $receiverId]);
    }

    /**
     * Get messages after a given ID (for polling)
     */
    public function getAfter(int $adminA, int $adminB, int $lastId): array
    {
        $stmt = $this->db->prepare("
            SELECT m.id, m.sender_id, m.receiver_id, m.content,
                   m.attachment_path, m.attachment_type, m.attachment_kind, m.attachment_name, m.attachment_size,
                   m.is_read, m.delivered_at, m.seen_at, m.is_pinned, m.created_at,
                   m.deleted_at, m.deleted_by_admin_id,
                   s.username AS sender_name, s.avatar AS sender_avatar,
                   d.username AS deleted_by_name
            FROM team_messages m
            JOIN admins s ON s.id = m.sender_id
            LEFT JOIN admins d ON d.id = m.deleted_by_admin_id
            WHERE ((m.sender_id = :a AND m.receiver_id = :b)
                OR (m.sender_id = :b2 AND m.receiver_id = :a2))
              AND m.id > :last
            ORDER BY m.created_at ASC
        ");
        $stmt->bindValue(':a',    $adminA, PDO::PARAM_INT);
        $stmt->bindValue(':b',    $adminB, PDO::PARAM_INT);
        $stmt->bindValue(':b2',   $adminB, PDO::PARAM_INT);
        $stmt->bindValue(':a2',   $adminA, PDO::PARAM_INT);
        $stmt->bindValue(':last', $lastId, PDO::PARAM_INT);
        $stmt->execute();
        return array_map([$this, 'normalizeForParticipant'], $stmt->fetchAll(PDO::FETCH_ASSOC) ?: []);
    }

    public function markDelivered(int $senderId, int $receiverId): void
    {
        $stmt = $this->db->prepare("
            UPDATE team_messages
            SET delivered_at = COALESCE(delivered_at, UTC_TIMESTAMP())
            WHERE sender_id = :sender AND receiver_id = :receiver
              AND delivered_at IS NULL
        ");
        $stmt->execute([':sender' => $senderId, ':receiver' => $receiverId]);
    }

    public function markSeenByConversation(int $senderId, int $receiverId): void
    {
        $stmt = $this->db->prepare("
            UPDATE team_messages
            SET is_read = 1,
                delivered_at = COALESCE(delivered_at, UTC_TIMESTAMP()),
                seen_at = COALESCE(seen_at, UTC_TIMESTAMP())
            WHERE sender_id = :sender AND receiver_id = :receiver AND is_read = 0
        ");
        $stmt->execute([':sender' => $senderId, ':receiver' => $receiverId]);
    }

    public function togglePin(int $messageId, int $ownerA, int $ownerB): bool
    {
        $stmt = $this->db->prepare("
            UPDATE team_messages
            SET is_pinned = 1 - is_pinned
            WHERE id = :id
              AND deleted_at IS NULL
              AND ((sender_id = :a AND receiver_id = :b) OR (sender_id = :b2 AND receiver_id = :a2))
        ");
        $stmt->execute([':id' => $messageId, ':a' => $ownerA, ':b' => $ownerB, ':b2' => $ownerB, ':a2' => $ownerA]);
        return $stmt->rowCount() > 0;
    }

    public function deleteMessage(int $messageId, int $actorId, bool $isManager = false): ?array
    {
        $message = $this->getRawById($messageId);
        if (!$message) {
            return null;
        }

        $isParticipant = (int)$message['sender_id'] === $actorId || (int)$message['receiver_id'] === $actorId;
        if (!$isParticipant && !$isManager) {
            return null;
        }

        if (!empty($message['deleted_at'])) {
            return $this->normalizeForParticipant($message);
        }

        $stmt = $this->db->prepare("
            UPDATE team_messages
            SET deleted_at = UTC_TIMESTAMP(),
                deleted_by_admin_id = :actor,
                deleted_snapshot = :snapshot,
                is_pinned = 0
            WHERE id = :id
        ");
        $stmt->execute([
            ':actor' => $actorId,
            ':snapshot' => json_encode([
                'content' => (string)($message['content'] ?? ''),
                'attachment_path' => $message['attachment_path'] ?? null,
                'attachment_type' => $message['attachment_type'] ?? null,
                'attachment_kind' => $message['attachment_kind'] ?? null,
                'attachment_name' => $message['attachment_name'] ?? null,
                'attachment_size' => $message['attachment_size'] ?? null,
            ], JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE),
            ':id' => $messageId,
        ]);

        return $this->getById($messageId);
    }

    public function getDeletionAudit(int $limit = 25): array
    {
        $stmt = $this->db->prepare("
            SELECT m.id, m.sender_id, m.receiver_id, m.content, m.created_at, m.deleted_at,
                   m.deleted_snapshot, m.attachment_name, m.attachment_kind,
                   s.username AS sender_name,
                   r.username AS receiver_name,
                   d.username AS deleted_by_name
            FROM team_messages m
            JOIN admins s ON s.id = m.sender_id
            JOIN admins r ON r.id = m.receiver_id
            LEFT JOIN admins d ON d.id = m.deleted_by_admin_id
            WHERE m.deleted_at IS NOT NULL
            ORDER BY m.deleted_at DESC
            LIMIT :limit
        ");
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    private function ensureSchema(): void
    {
        try {
            $this->db->exec("
                ALTER TABLE team_messages
                ADD COLUMN IF NOT EXISTS delivered_at datetime DEFAULT NULL AFTER is_read,
                ADD COLUMN IF NOT EXISTS seen_at datetime DEFAULT NULL AFTER delivered_at,
                ADD COLUMN IF NOT EXISTS is_pinned tinyint(1) NOT NULL DEFAULT 0 AFTER seen_at,
                ADD COLUMN IF NOT EXISTS attachment_kind varchar(20) DEFAULT NULL AFTER attachment_type,
                ADD COLUMN IF NOT EXISTS deleted_at datetime DEFAULT NULL AFTER is_pinned,
                ADD COLUMN IF NOT EXISTS deleted_by_admin_id INT NULL AFTER deleted_at,
                ADD COLUMN IF NOT EXISTS deleted_snapshot LONGTEXT NULL AFTER deleted_by_admin_id
            ");
        } catch (\Throwable $e) {
        }
    }

    /**
     * Total unread count for an admin (across all conversations)
     */
    public function totalUnread(int $receiverId): int
    {
        $stmt = $this->db->prepare("
            SELECT COUNT(*) FROM team_messages
            WHERE receiver_id = :rid AND is_read = 0 AND deleted_at IS NULL
        ");
        $stmt->execute([':rid' => $receiverId]);
        return (int)$stmt->fetchColumn();
    }

    private function getRawById(int $id): ?array
    {
        $stmt = $this->db->prepare("
            SELECT *
            FROM team_messages
            WHERE id = :id
            LIMIT 1
        ");
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    private function normalizeForParticipant(array $row): array
    {
        if (empty($row['deleted_at'])) {
            $row['is_deleted'] = 0;
            return $row;
        }

        $row['is_deleted'] = 1;
        $row['original_content'] = (string)($row['content'] ?? '');
        $row['content'] = self::DELETED_PLACEHOLDER;
        $row['attachment_path'] = null;
        $row['attachment_type'] = null;
        $row['attachment_kind'] = null;
        $row['attachment_name'] = null;
        $row['attachment_size'] = null;
        $row['is_pinned'] = 0;

        return $row;
    }
}
