<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * ChatConversation Model
 *
 * Manages chat conversations between website visitors and admin.
 */
class ChatConversation extends Model
{
    protected string $table = 'chat_conversations';

    /**
     * Find or create an active conversation by session ID.
     */
    public function findActiveBySession(string $sessionId): ?array
    {
        $sql = "SELECT * FROM `{$this->table}`
                WHERE `session_id` = :sid AND `status` != 'closed'
                ORDER BY `created_at` DESC LIMIT 1";

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':sid' => $sessionId]);

        $result = $stmt->fetch();
        return $result !== false ? $result : null;
    }

    /**
     * Create a new conversation.
     */
    public function createConversation(string $sessionId, ?string $name = null, ?string $email = null, ?string $phone = null): int
    {
        return $this->create([
            'session_id'      => $sessionId,
            'visitor_name'    => $name,
            'visitor_email'   => $email,
            'visitor_phone'   => $phone,
            'status'          => 'pending',
            'admin_unread'    => 0,
            'visitor_unread'  => 0,
            'last_message_at' => date('Y-m-d H:i:s'),
        ]);
    }

    /**
     * Update visitor info in a conversation.
     */
    public function updateVisitorInfo(int $id, ?string $name, ?string $email, ?string $phone): bool
    {
        $data = [];
        if ($name !== null)  $data['visitor_name']  = $name;
        if ($email !== null) $data['visitor_email'] = $email;
        if ($phone !== null) $data['visitor_phone'] = $phone;

        if (empty($data)) return false;

        return $this->update($id, $data);
    }

    /**
     * Get all conversations with latest message info, ordered by most recent.
     */
    public function getAllWithLastMessage(): array
    {
        $sql = "SELECT c.*,
                    (SELECT COUNT(*) FROM `chat_messages` WHERE `conversation_id` = c.`id`) AS message_count,
                    (SELECT `message` FROM `chat_messages` WHERE `conversation_id` = c.`id` ORDER BY `created_at` DESC LIMIT 1) AS last_message
                FROM `{$this->table}` c
                ORDER BY c.`last_message_at` DESC, c.`created_at` DESC";

        $stmt = $this->db->prepare($sql);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    /**
     * Get active conversations count (for admin notification badge).
     */
    public function getActiveCount(): int
    {
        $sql = "SELECT COUNT(*) AS total FROM `{$this->table}`
                WHERE `status` IN ('active', 'pending')";

        $stmt = $this->db->prepare($sql);
        $stmt->execute();

        return (int) ($stmt->fetch()['total'] ?? 0);
    }

    /**
     * Get total unread messages for admin across all conversations.
     */
    public function getTotalAdminUnread(): int
    {
        $sql = "SELECT SUM(`admin_unread`) AS total FROM `{$this->table}`
                WHERE `status` IN ('active', 'pending')";

        $stmt = $this->db->prepare($sql);
        $stmt->execute();

        return (int) ($stmt->fetch()['total'] ?? 0);
    }

    /**
     * Increment admin unread count for a conversation.
     */
    public function incrementAdminUnread(int $id): bool
    {
        $sql = "UPDATE `{$this->table}` SET `admin_unread` = `admin_unread` + 1,
                `last_message_at` = NOW() WHERE `id` = :id";

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':id' => $id]);

        return $stmt->rowCount() > 0;
    }

    /**
     * Reset admin unread count to 0 (when admin opens the chat).
     */
    public function resetAdminUnread(int $id): bool
    {
        return $this->update($id, ['admin_unread' => 0]);
    }

    /**
     * Increment visitor unread count for a conversation.
     */
    public function incrementVisitorUnread(int $id): bool
    {
        $sql = "UPDATE `{$this->table}` SET `visitor_unread` = `visitor_unread` + 1,
                `last_message_at` = NOW() WHERE `id` = :id";

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':id' => $id]);

        return $stmt->rowCount() > 0;
    }

    /**
     * Reset visitor unread count (when visitor opens the chat).
     */
    public function resetVisitorUnread(int $id): bool
    {
        return $this->update($id, ['visitor_unread' => 0]);
    }

    /**
     * Mark conversation as active (when first message is sent).
     */
    public function markActive(int $id): bool
    {
        return $this->update($id, ['status' => 'active']);
    }

    /**
     * Close a conversation.
     */
    public function closeConversation(int $id): bool
    {
        return $this->update($id, ['status' => 'closed']);
    }

    /**
     * Delete old closed conversations older than N days.
     */
    public function deleteOldClosed(int $days = 30): int
    {
        $sql = "DELETE FROM `{$this->table}`
                WHERE `status` = 'closed' AND `updated_at` < DATE_SUB(NOW(), INTERVAL :days DAY)";

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':days' => $days]);

        return $stmt->rowCount();
    }
}
