<?php
namespace App\Models;

use App\Core\Model;
use PDO;

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

    public static function getOrCreateForContact(int $contactId, ?int $assignedUserId = null): array {
        $conv = self::firstWhere('contact_id', $contactId);
        if ($conv) {
            return $conv;
        }

        $id = self::create([
            'contact_id'        => $contactId,
            'assigned_user_id'  => $assignedUserId,
            'status'            => 'open',
            'unread_count'      => 0,
            'last_message'      => '',
            'last_message_at'   => date('Y-m-d H:i:s'),
            'created_at'        => date('Y-m-d H:i:s')
        ]);

        return self::find($id);
    }

    public static function getInboxList(?int $userId = null, string $status = 'all', ?string $search = null): array {
        $sql = "
            SELECT conv.*, 
                   c.name as contact_name, c.phone_number, c.whatsapp_id, c.avatar as contact_avatar,
                   ps.name as stage_name, ps.color as stage_color,
                   u.name as assigned_agent_name
            FROM conversations conv
            JOIN contacts c ON conv.contact_id = c.id
            LEFT JOIN pipeline_stages ps ON c.stage_id = ps.id
            LEFT JOIN users u ON conv.assigned_user_id = u.id
            WHERE 1=1
        ";
        $params = [];

        if ($status !== 'all' && !empty($status)) {
            $sql .= " AND conv.status = ?";
            $params[] = $status;
        }

        if ($userId !== null) {
            $sql .= " AND (conv.assigned_user_id = ? OR conv.assigned_user_id IS NULL)";
            $params[] = $userId;
        }

        if (!empty($search)) {
            $sql .= " AND (c.name LIKE ? OR c.phone_number LIKE ? OR conv.last_message LIKE ?)";
            $searchVal = "%{$search}%";
            $params[] = $searchVal;
            $params[] = $searchVal;
            $params[] = $searchVal;
        }

        $sql .= " ORDER BY conv.last_message_at DESC";

        $stmt = self::db()->prepare($sql);
        $stmt->execute($params);
        $conversations = $stmt->fetchAll();

        // Attach tags to each contact in inbox
        foreach ($conversations as &$conv) {
            $conv['tags'] = Contact::getTags((int)$conv['contact_id']);
        }

        return $conversations;
    }

    public static function markAsRead(int $conversationId): void {
        $stmt = self::db()->prepare("UPDATE conversations SET unread_count = 0 WHERE id = ?");
        $stmt->execute([$conversationId]);

        // Also update message statuses to read
        $stmtMsg = self::db()->prepare("UPDATE messages SET status = 'read' WHERE conversation_id = ? AND direction = 'inbound' AND status != 'read'");
        $stmtMsg->execute([$conversationId]);
    }

    public static function updateLastMessage(int $conversationId, string $message, string $direction): void {
        $now = date('Y-m-d H:i:s');
        if ($direction === 'inbound') {
            $stmt = self::db()->prepare("UPDATE conversations SET last_message = ?, last_message_at = ?, unread_count = unread_count + 1 WHERE id = ?");
            $stmt->execute([$message, $now, $conversationId]);
        } else {
            $stmt = self::db()->prepare("UPDATE conversations SET last_message = ?, last_message_at = ? WHERE id = ?");
            $stmt->execute([$message, $now, $conversationId]);
        }
    }
}
