<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\BaseModel;
use App\Helpers\Paginator;
use PDO;

final class ConversationModel extends BaseModel
{
    protected string $table = 'conversations';
    protected array $fillable = [
        'business_line_id', 'channel', 'direction', 'visitor_identifier', 'external_ref',
        'related_lead_id', 'related_customer_id', 'outcome', 'handled_by_user_id',
        'duration_seconds', 'recording_url', 'transcript_text', 'ended_at',
    ];
    protected bool $timestamps = false;
    protected bool $softDeletes = false;
    protected bool $auditStamps = false;

    public function start(string $channel, int $businessLineId, ?string $visitorIdentifier = null, ?string $externalRef = null, ?string $direction = null): int
    {
        return $this->insert([
            'business_line_id' => $businessLineId,
            'channel' => $channel,
            'direction' => $direction,
            'visitor_identifier' => $visitorIdentifier,
            'external_ref' => $externalRef,
        ]);
    }

    public function addMessage(int $conversationId, string $sender, string $message): int
    {
        $stmt = $this->db->prepare(
            'INSERT INTO conversation_messages (conversation_id, sender, message, created_at) VALUES (:id, :sender, :message, NOW())'
        );
        $stmt->execute(['id' => $conversationId, 'sender' => $sender, 'message' => $message]);

        return (int) $this->db->lastInsertId();
    }

    public function messages(int $conversationId): array
    {
        $stmt = $this->db->prepare('SELECT * FROM conversation_messages WHERE conversation_id = :id ORDER BY created_at, id');
        $stmt->execute(['id' => $conversationId]);

        return $stmt->fetchAll();
    }

    public function end(int $conversationId, ?string $outcome = null): void
    {
        $this->update($conversationId, [
            'ended_at' => now(),
            'outcome' => $outcome,
        ]);
    }

    public function findByExternalRef(string $externalRef): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM conversations WHERE external_ref = :ref LIMIT 1');
        $stmt->execute(['ref' => $externalRef]);
        $row = $stmt->fetch();

        return $row === false ? null : $row;
    }

    public function findWithRelations(int $id): ?array
    {
        $stmt = $this->db->prepare(
            "SELECT c.*, bl.name AS business_line_name,
                    CONCAT(l.first_name, ' ', COALESCE(l.last_name,'')) AS lead_name,
                    CONCAT(cu.first_name, ' ', COALESCE(cu.last_name,'')) AS customer_name,
                    CONCAT(u.first_name, ' ', u.last_name) AS handled_by_name
             FROM conversations c
             LEFT JOIN business_lines bl ON bl.id = c.business_line_id
             LEFT JOIN leads l ON l.id = c.related_lead_id
             LEFT JOIN customers cu ON cu.id = c.related_customer_id
             LEFT JOIN users u ON u.id = c.handled_by_user_id
             WHERE c.id = :id"
        );
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();

        return $row === false ? null : $row;
    }

    public function paginatedList(array $filters, Paginator $paginator): array
    {
        [$where, $bindings] = $this->buildFilterClause($filters);

        $countStmt = $this->db->prepare(
            "SELECT COUNT(*) FROM conversations c
             LEFT JOIN leads l ON l.id = c.related_lead_id
             LEFT JOIN customers cu ON cu.id = c.related_customer_id
             {$where}"
        );
        $countStmt->execute($bindings);
        $paginator->setTotal((int) $countStmt->fetchColumn());

        $sql = "SELECT c.*, bl.name AS business_line_name,
                       CONCAT(l.first_name, ' ', COALESCE(l.last_name,'')) AS lead_name,
                       CONCAT(cu.first_name, ' ', COALESCE(cu.last_name,'')) AS customer_name
                FROM conversations c
                LEFT JOIN business_lines bl ON bl.id = c.business_line_id
                LEFT JOIN leads l ON l.id = c.related_lead_id
                LEFT JOIN customers cu ON cu.id = c.related_customer_id
                {$where}
                ORDER BY c.started_at DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($bindings as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue('limit', $paginator->perPage(), PDO::PARAM_INT);
        $stmt->bindValue('offset', $paginator->offset(), PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function dashboardStats(): array
    {
        return $this->db->query(
            "SELECT channel,
                    COUNT(*) AS total,
                    SUM(outcome = 'lead_created') AS leads_captured,
                    SUM(outcome = 'booked') AS appointments_booked,
                    SUM(outcome = 'escalated') AS escalated
             FROM conversations
             WHERE started_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
             GROUP BY channel"
        )->fetchAll();
    }

    private function buildFilterClause(array $filters): array
    {
        $conditions = ['1=1'];
        $bindings = [];

        if (!empty($filters['channel'])) {
            $conditions[] = 'c.channel = :channel';
            $bindings['channel'] = $filters['channel'];
        }
        if (!empty($filters['outcome'])) {
            $conditions[] = 'c.outcome = :outcome';
            $bindings['outcome'] = $filters['outcome'];
        }
        if (!empty($filters['business_line_id'])) {
            $conditions[] = 'c.business_line_id = :bl';
            $bindings['bl'] = $filters['business_line_id'];
        }
        if (!empty($filters['search'])) {
            $conditions[] = '(c.visitor_identifier LIKE :search1 OR l.first_name LIKE :search2 OR cu.first_name LIKE :search3)';
            $searchTerm = '%' . $filters['search'] . '%';
            $bindings['search1'] = $searchTerm;
            $bindings['search2'] = $searchTerm;
            $bindings['search3'] = $searchTerm;
        }

        return ['WHERE ' . implode(' AND ', $conditions), $bindings];
    }
}