<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use App\Helpers\BankFilter;
use PDO;
use PDOException;

class Lead extends Model
{
    private const LEAD_WITH_RELATIONS_SQL = <<<'SQL'
SELECT l.*, b.name AS broker_name, e.name AS employee_name, c.name AS creator_name
FROM leads l
LEFT JOIN users b ON l.broker_id = b.id
LEFT JOIN users e ON l.employee_id = e.id
LEFT JOIN users c ON l.created_by = c.id
SQL;

    public const STATUS_NEW = 'novo';
    public const STATUS_CONTACT = 'em_contato';
    public const STATUS_PROPOSAL = 'proposta';
    public const STATUS_CLOSED = 'fechado';
    public const STATUS_LOST = 'perdido';

    private static ?array $statusCache = null;
    private static ?array $contractStatusCache = null;
    private static ?bool $phoneAlternativesColumnAvailable = null;
    private static ?bool $validatedPhoneColumnsAvailable = null;
    private static ?bool $documentColumnAvailable = null;

    /**
     * @return array<int, string>
     */
    public function originOptions(): array
    {
        $sql = 'SELECT DISTINCT origin FROM leads WHERE origin IS NOT NULL AND origin <> \'\' ORDER BY origin ASC';
        $stmt = $this->db->query($sql);
        if ($stmt === false) {
            return [];
        }

        $values = $stmt->fetchAll(PDO::FETCH_COLUMN) ?: [];

        return array_values(array_map(
            static fn ($value): string => (string) $value,
            $values
        ));
    }

    /**
     * @var array<int, string>
     */
    private array $crefazColumns = [
        'crefaz_enabled',
        'crefaz_mode',
        'crefaz_proposta_id',
        'crefaz_status',
        'crefaz_status_description',
        'crefaz_renda_presumida',
        'crefaz_webhook_url',
        'crefaz_last_synced_at',
        'crefaz_last_status_at',
    ];

    public const BUSINESS_TYPES = [
        'fgts' => 'FGTS',
        'consignado_clt' => 'Consignado CLT',
        'consignado_inss' => 'Consignado INSS',
        'consignado_servidor' => 'Consignado Servidor Publico',
        'credito_pessoal' => 'Credito Pessoal',
        'credito_imobiliario' => 'Credito Imobiliario',
        'financiamento_auto' => 'Financiamento de Carro',
        'car_equity' => 'Car Equity',
        'cartao_credito' => 'Cartao de Credito',
        'cartao_beneficio' => 'Cartao Beneficio',
        'saque_complementar' => 'Saque Complementar',
    ];

    public const CONTRACT_STATUSES = [
        'nao_informado' => 'Nao informado',
        'aguardando_pagamento' => 'Aguardando pagamento',
        'pago' => 'Pago',
        'cancelado' => 'Cancelado',
    ];

    private const RVX_AUTO_SYNC_INTERVAL_MONTHS = 3;

    public static function statusOptions(): array
    {
        if (self::$statusCache !== null) {
            return self::$statusCache;
        }

        $statusModel = new LeadStatus();

        try {
            $options = $statusModel->options();
        } catch (PDOException $exception) {
            $options = LeadStatus::DEFAULT_STATUSES;
        }

        if ($options === []) {
            $options = LeadStatus::DEFAULT_STATUSES;
        }

        self::$statusCache = $options;

        return self::$statusCache;
    }

    public static function statusExists(string $slug): bool
    {
        return array_key_exists($slug, self::statusOptions());
    }

    public static function contractStatusOptions(): array
    {
        if (self::$contractStatusCache !== null) {
            return self::$contractStatusCache;
        }

        $catalog = new LoanProposalStatusCatalog();
        $records = $catalog->all();
        if ($records === []) {
            self::$contractStatusCache = self::CONTRACT_STATUSES;
            return self::$contractStatusCache;
        }

        $options = [
            'nao_informado' => self::CONTRACT_STATUSES['nao_informado'] ?? 'Nao informado',
        ];

        foreach ($records as $record) {
            $slug = trim((string) ($record['slug'] ?? ''));
            if ($slug === '') {
                continue;
            }
            $label = trim((string) ($record['label'] ?? $slug));
            $options[$slug] = $label !== '' ? $label : $slug;
        }

        foreach (self::CONTRACT_STATUSES as $slug => $label) {
            if (!isset($options[$slug])) {
                $options[$slug] = $label;
            }
        }

        self::$contractStatusCache = $options;

        return $options;
    }

    public static function refreshContractStatusOptions(): void
    {
        self::$contractStatusCache = null;
    }

    public static function isValidContractStatus(string $status): bool
    {
        return array_key_exists($status, self::contractStatusOptions());
    }

    public static function refreshStatusCache(): void
    {
        self::$statusCache = null;
    }

    /**
     * @param array<string, mixed> $owner
     * @return array{broker_id:int, employee_id:?int}
     */
    public static function resolveAssignmentForOwner(array $owner): array
    {
        $role = $owner['role'] ?? null;
        $brokerId = null;
        $employeeId = null;

        if ($role === User::ROLE_BROKER) {
            $brokerId = (int) ($owner['id'] ?? 0);
        } elseif ($role === User::ROLE_EMPLOYEE) {
            $employeeId = (int) ($owner['id'] ?? 0);
            if (!empty($owner['broker_id'])) {
                $brokerId = (int) $owner['broker_id'];
            }
        }

        if (($brokerId === null || $brokerId <= 0) && !empty($owner['broker_id'])) {
            $brokerId = (int) $owner['broker_id'];
        }

        if ($brokerId === null || $brokerId <= 0) {
            $brokerId = (int) ($owner['id'] ?? 0);
        }

        return [
            'broker_id' => $brokerId,
            'employee_id' => $employeeId ?: null,