<?php

declare(strict_types=1);

namespace App\Models;

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

final class LeadModel extends BaseModel
{
    protected string $table = 'leads';

    protected array $fillable = [
        'business_line_id', 'lead_source_id', 'lead_stage_id', 'priority', 'company_id', 'owner_id',
        'first_name', 'last_name', 'email', 'phone', 'estimated_value', 'currency', 'notes', 'lost_reason',
        'converted_customer_id',
    ];

    protected bool $softDeletes = true;
    protected bool $auditStamps = true;

    public function findWithRelations(int $id): ?array
    {
        $stmt = $this->db->prepare(
            'SELECT l.*, bl.name AS business_line_name, bl.slug AS business_line_slug,
                    ls.name AS stage_name, ls.slug AS stage_slug, ls.is_won, ls.is_lost,
                    src.name AS source_name, c.name AS company_name,
                    CONCAT(u.first_name, " ", u.last_name) AS owner_name
             FROM leads l
             LEFT JOIN business_lines bl ON bl.id = l.business_line_id
             LEFT JOIN lead_stages ls ON ls.id = l.lead_stage_id
             LEFT JOIN lead_sources src ON src.id = l.lead_source_id
             LEFT JOIN companies c ON c.id = l.company_id
             LEFT JOIN users u ON u.id = l.owner_id
             WHERE l.id = :id AND l.deleted_at IS NULL'
        );
        $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 leads l {$where}");
        $countStmt->execute($bindings);
        $paginator->setTotal((int) $countStmt->fetchColumn());

        $sql = "SELECT l.*, bl.name AS business_line_name, ls.name AS stage_name, ls.slug AS stage_slug,
                       ls.color_hex, src.name AS source_name, CONCAT(u.first_name, ' ', u.last_name) AS owner_name
                FROM leads l
                LEFT JOIN business_lines bl ON bl.id = l.business_line_id
                LEFT JOIN lead_stages ls ON ls.id = l.lead_stage_id
                LEFT JOIN lead_sources src ON src.id = l.lead_source_id
                LEFT JOIN users u ON u.id = l.owner_id
                {$where}
                ORDER BY l.created_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();
    }

    /** Grouped by stage for the Kanban pipeline view, scoped by the same filters as the table view. */
    public function pipelineGrouped(array $filters): array
    {
        [$where, $bindings] = $this->buildFilterClause($filters);

        $stmt = $this->db->prepare(
            "SELECT l.*, CONCAT(u.first_name, ' ', u.last_name) AS owner_name, bl.name AS business_line_name
             FROM leads l
             LEFT JOIN users u ON u.id = l.owner_id
             LEFT JOIN business_lines bl ON bl.id = l.business_line_id
             {$where}
             ORDER BY l.created_at DESC"
        );
        $stmt->execute($bindings);
        $rows = $stmt->fetchAll();

        $stages = $this->db->query('SELECT * FROM lead_stages WHERE is_active = 1 ORDER BY sort_order')->fetchAll();

        $grouped = [];
        foreach ($stages as $stage) {
            $grouped[$stage['id']] = ['stage' => $stage, 'leads' => []];
        }
        foreach ($rows as $row) {
            if (isset($grouped[$row['lead_stage_id']])) {
                $grouped[$row['lead_stage_id']]['leads'][] = $row;
            }
        }

        return array_values($grouped);
    }

    /**
     * Duplicate detection: any OTHER active lead sharing this email or
     * phone. Called before create (warn) and on-demand from a "Find
     * Duplicates" action on an existing lead.
     */
    public function findPotentialDuplicates(?string $email, ?string $phone, ?int $excludeId = null): array
    {
        if ($email === null && $phone === null) {
            return [];
        }

        $conditions = [];
        $bindings = [];

        if ($email) {
            $conditions[] = 'email = :email';
            $bindings['email'] = $email;
        }
        if ($phone) {
            $conditions[] = 'phone = :phone';
            $bindings['phone'] = $phone;
        }

        $sql = 'SELECT * FROM leads WHERE deleted_at IS NULL AND merged_into_lead_id IS NULL AND (' . implode(' OR ', $conditions) . ')';

        if ($excludeId !== null) {
            $sql .= ' AND id != :exclude_id';
            $bindings['exclude_id'] = $excludeId;
        }

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

        return $stmt->fetchAll();
    }

    public function moveStage(int $leadId, int $stageId, ?int $actingUserId = null): bool
    {
        return $this->update($leadId, ['lead_stage_id' => $stageId], $actingUserId);
    }

    public function forSelect(?int $businessLineId = null): array
    {
        $sql = 'SELECT id, CONCAT(first_name, " ", COALESCE(last_name, "")) AS name FROM leads WHERE deleted_at IS NULL';
        $bindings = [];
        if ($businessLineId !== null) {
            $sql .= ' AND business_line_id = :bl';
            $bindings['bl'] = $businessLineId;
        }
        $sql .= ' ORDER BY first_name';

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

        return $stmt->fetchAll();
    }

    private function buildFilterClause(array $filters): array
    {
        $conditions = ['l.deleted_at IS NULL', 'l.merged_into_lead_id IS NULL'];
        $bindings = [];

        if (!empty($filters['search'])) {
            $conditions[] = '(l.first_name LIKE :search1 OR l.last_name LIKE :search2 OR l.email LIKE :search3 OR l.phone LIKE :search4)';
            $searchTerm = '%' . $filters['search'] . '%';
            $bindings['search1'] = $searchTerm;
            $bindings['search2'] = $searchTerm;
            $bindings['search3'] = $searchTerm;
            $bindings['search4'] = $searchTerm;
        }
        if (!empty($filters['business_line_id'])) {
            $conditions[] = 'l.business_line_id = :bl';
            $bindings['bl'] = $filters['business_line_id'];
        }
        if (!empty($filters['lead_stage_id'])) {
            $conditions[] = 'l.lead_stage_id = :stage';
            $bindings['stage'] = $filters['lead_stage_id'];
        }
        if (!empty($filters['priority'])) {
            $conditions[] = 'l.priority = :priority';
            $bindings['priority'] = $filters['priority'];
        }
        if (!empty($filters['owner_id'])) {
            $conditions[] = 'l.owner_id = :owner';
            $bindings['owner'] = $filters['owner_id'];
        }
        if (!empty($filters['lead_source_id'])) {
            $conditions[] = 'l.lead_source_id = :source';
            $bindings['source'] = $filters['lead_source_id'];
        }

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