<?php

declare(strict_types=1);

class Ocorrencia extends Model
{
    /** @var array<string,bool> */
    private static $columnCache = [];

    public function criar(array $dados): int
    {
        $sql = "INSERT INTO ocorrencias (
                    protocolo, categoria_id, descricao, latitude, longitude,
                    endereco_referencia, cidadao_nome, cidadao_telefone, cidadao_email,
                    anonima, status, imagem, criado_em
                ) VALUES (
                    :protocolo, :categoria_id, :descricao, :latitude, :longitude,
                    :endereco_referencia, :cidadao_nome, :cidadao_telefone, :cidadao_email,
                    :anonima, 'ABERTA', :imagem, NOW()
                )";

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

        $id = (int) $this->db->lastInsertId();
        $this->registrarHistorico($id, null, null, 'ABERTA', 'Ocorrência aberta pelo cidadão.');

        return $id;
    }

    public function listar(array $scope = []): array
    {
        $sql = "SELECT o.*, c.nome AS categoria_nome, " . $this->categoryColorSql() . " AS categoria_cor, s.nome AS setor_nome, " . $this->slaDiasSql() . " AS sla_dias
                FROM ocorrencias o
                INNER JOIN categorias c ON c.id = o.categoria_id
                LEFT JOIN setores s ON s.id = o.setor_id
                WHERE 1=1";
        $params = [];
        $this->applyScope($sql, $params, $scope);
        $sql .= " ORDER BY o.id DESC";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $this->appendSlaMeta($stmt->fetchAll());
    }

    public function listarComFiltros(array $filtros = []): array
    {
        $scope = $this->extractScope($filtros);

        $sql = "SELECT o.*, c.nome AS categoria_nome, " . $this->categoryColorSql() . " AS categoria_cor, s.nome AS setor_nome, " . $this->slaDaysRemainingSql() . " AS sla_dias
                FROM ocorrencias o
                INNER JOIN categorias c ON c.id = o.categoria_id
                LEFT JOIN setores s ON s.id = o.setor_id
                WHERE 1=1";

        $params = [];
        $this->applyScope($sql, $params, $scope);

        if (!empty($filtros['protocolo'])) {
            $sql .= " AND o.protocolo LIKE :protocolo";
            $params['protocolo'] = '%' . $filtros['protocolo'] . '%';
        }

        if (!empty($filtros['status'])) {
            $sql .= " AND o.status = :status";
            $params['status'] = $filtros['status'];
        }

        if (!empty($filtros['categoria_id'])) {
            $sql .= " AND o.categoria_id = :categoria_id";
            $params['categoria_id'] = (int) $filtros['categoria_id'];
        }

        if (array_key_exists('setor_id', $scope)) {
            unset($filtros['setor_id']);
        }

        if (!empty($filtros['setor_id'])) {
            $sql .= " AND o.setor_id = :setor_id_filtro";
            $params['setor_id_filtro'] = (int) $filtros['setor_id'];
        }

        if (!empty($filtros['data_inicio'])) {
            $sql .= " AND DATE(o.criado_em) >= :data_inicio";
            $params['data_inicio'] = $filtros['data_inicio'];
        }

        if (!empty($filtros['data_fim'])) {
            $sql .= " AND DATE(o.criado_em) <= :data_fim";
            $params['data_fim'] = $filtros['data_fim'];
        }

        $sql .= " ORDER BY o.id DESC";

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

        return $this->appendSlaMeta($stmt->fetchAll());
    }

    public function listarRecentes(int $limite = 5, array $scope = []): array
    {
        $limite = max(1, $limite);
        $sql = "SELECT o.id, o.protocolo, o.status, o.criado_em, c.nome AS categoria_nome, " . $this->categoryColorSql() . " AS categoria_cor, " . $this->slaDaysRemainingSql() . " AS sla_dias
                FROM ocorrencias o
                INNER JOIN categorias c ON c.id = o.categoria_id
                WHERE 1=1";
        $params = [];
        $this->applyScope($sql, $params, $scope);
        $sql .= " ORDER BY o.id DESC LIMIT {$limite}";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $this->appendSlaMeta($stmt->fetchAll());
    }

    public function listarCriticas(int $limite = 10, array $scope = []): array
    {
        $limite = max(1, $limite);
        $sql = "SELECT o.id, o.protocolo, o.status, o.criado_em, c.nome AS categoria_nome, " . $this->categoryColorSql() . " AS categoria_cor, " . $this->slaDaysRemainingSql() . " AS sla_dias
                FROM ocorrencias o
                INNER JOIN categorias c ON c.id = o.categoria_id
                WHERE o.status NOT IN ('CONCLUIDA','NAO_PROCEDE','DUPLICADA')";
        $params = [];
        $this->applyScope($sql, $params, $scope);
        $sql .= " ORDER BY sla_dias ASC, o.id DESC LIMIT {$limite}";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $items = $this->appendSlaMeta($stmt->fetchAll());

        return array_values(array_filter($items, static function (array $item): bool {
            return in_array((string) ($item['sla_status'] ?? ''), ['ATRASADO', 'VENCE_HOJE', 'ALERTA'], true);
        }));
    }

    public function listarParaMapa(int $limite = 200, array $filtros = []): array
    {
        $limite = max(1, $limite);
        $scope = $this->extractScope($filtros);

        $sql = "SELECT o.id, o.protocolo, o.latitude, o.longitude, o.status, o.endereco_referencia,
                       c.nome AS categoria_nome, " . $this->categoryColorSql() . " AS categoria_cor
                FROM ocorrencias o
                INNER JOIN categorias c ON c.id = o.categoria_id
                WHERE o.latitude IS NOT NULL
                  AND o.longitude IS NOT NULL";

        $params = [];
        $this->applyScope($sql, $params, $scope);

        if (!empty($filtros['status'])) {
            $sql .= " AND o.status = :status";
            $params['status'] = (string) $filtros['status'];
        } else {
            $sql .= " AND o.status IN " . $this->mapStatusesSql();
        }

        if (!empty($filtros['categoria_id'])) {
            $sql .= " AND o.categoria_id = :categoria_id";
            $params['categoria_id'] = (int) $filtros['categoria_id'];
        }

        $sql .= " ORDER BY o.id DESC LIMIT {$limite}";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function buscarPorId(int $id, array $scope = []): ?array
    {
        $sql = "SELECT o.*, c.nome AS categoria_nome, " . $this->categoryColorSql() . " AS categoria_cor, s.nome AS setor_nome, " . $this->slaDaysRemainingSql() . " AS sla_dias
                FROM ocorrencias o
                INNER JOIN categorias c ON c.id = o.categoria_id
                LEFT JOIN setores s ON s.id = o.setor_id
                WHERE o.id = :id";
        $params = ['id' => $id];
        $this->applyScope($sql, $params, $scope);
        $sql .= " LIMIT 1";

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

        $row = $stmt->fetch();
        if (!$row) {
            return null;
        }
        $items = $this->appendSlaMeta([$row]);
        return $items[0] ?? null;
    }

    public function buscarPorProtocolo(string $protocolo): ?array
    {
        $sql = "SELECT o.*, c.nome AS categoria_nome, " . $this->categoryColorSql() . " AS categoria_cor, s.nome AS setor_nome, " . $this->slaDaysRemainingSql() . " AS sla_dias
                FROM ocorrencias o
                INNER JOIN categorias c ON c.id = o.categoria_id
                LEFT JOIN setores s ON s.id = o.setor_id
                WHERE o.protocolo = :protocolo
                LIMIT 1";

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

        $row = $stmt->fetch();
        if (!$row) {
            return null;
        }
        $items = $this->appendSlaMeta([$row]);
        return $items[0] ?? null;
    }

    public function contarPorStatus(array $scope = []): array
    {
        $sql = "SELECT status, COUNT(*) AS total FROM ocorrencias o WHERE 1=1";
        $params = [];
        $this->applyScope($sql, $params, $scope);
        $sql .= " GROUP BY status";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll();

        $base = [
            'ABERTA' => 0,
            'RECEBIDA' => 0,
            'EM_ANALISE' => 0,
            'ENCAMINHADA' => 0,
            'EM_ATENDIMENTO' => 0,
            'CONCLUIDA' => 0,
            'NAO_PROCEDE' => 0,
            'DUPLICADA' => 0,
        ];
        foreach ($rows as $row) {
            $base[$row['status']] = (int) $row['total'];
        }
        $base['TOTAL'] = array_sum($base);
        $base['EM_ANDAMENTO'] = $base['ABERTA'] + $base['RECEBIDA'] + $base['EM_ANALISE'] + $base['ENCAMINHADA'] + $base['EM_ATENDIMENTO'];
        return $base;
    }

    public function contarSla(array $scope = []): array
    {
        $sql = "SELECT o.id, o.status, o.criado_em, c.nome AS categoria_nome, " . $this->slaDaysRemainingSql() . " AS sla_dias
                FROM ocorrencias o
                INNER JOIN categorias c ON c.id = o.categoria_id
                WHERE 1=1";
        $params = [];
        $this->applyScope($sql, $params, $scope);
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);

        $items = $this->appendSlaMeta($stmt->fetchAll());

        $summary = [
            'NO_PRAZO' => 0,
            'ALERTA' => 0,
            'VENCE_HOJE' => 0,
            'ATRASADAS' => 0,
            'FINALIZADAS' => 0,
        ];

        foreach ($items as $item) {
            switch ((string) ($item['sla_status'] ?? 'NO_PRAZO')) {
                case 'FINALIZADA':
                    $summary['FINALIZADAS']++;
                    break;
                case 'ATRASADO':
                    $summary['ATRASADAS']++;
                    break;
                case 'VENCE_HOJE':
                    $summary['VENCE_HOJE']++;
                    break;
                case 'ALERTA':
                    $summary['ALERTA']++;
                    break;
                default:
                    $summary['NO_PRAZO']++;
            }
        }

        return $summary;
    }

    public function contarPorCategoria(array $scope = [], int $limite = 6): array
    {
        $limite = max(1, $limite);
        $sql = "SELECT c.nome, " . $this->categoryColorSql() . " AS cor, COUNT(*) AS total
                FROM ocorrencias o
                INNER JOIN categorias c ON c.id = o.categoria_id
                WHERE 1=1";
        $params = [];
        $this->applyScope($sql, $params, $scope);
        $sql .= " GROUP BY c.id, c.nome ORDER BY total DESC, c.nome ASC LIMIT {$limite}";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function contarPorFaixaPrazo(array $scope = []): array
    {
        $sql = "SELECT o.status, " . $this->slaDaysRemainingSql() . " AS sla_dias
                FROM ocorrencias o
                INNER JOIN categorias c ON c.id = o.categoria_id
                WHERE 1=1";
        $params = [];
        $this->applyScope($sql, $params, $scope);
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $items = $this->appendSlaMeta($stmt->fetchAll());

        $faixas = [
            'ATRASADAS' => 0,
            'VENCE_HOJE' => 0,
            '1_DIA' => 0,
            '2_A_3_DIAS' => 0,
            '4_MAIS_DIAS' => 0,
            'FINALIZADAS' => 0,
        ];

        foreach ($items as $item) {
            $status = (string) ($item['sla_status'] ?? 'NO_PRAZO');
            $restante = (int) ($item['sla_restante'] ?? 0);

            if ($status === 'FINALIZADA') {
                $faixas['FINALIZADAS']++;
            } elseif ($status === 'ATRASADO') {
                $faixas['ATRASADAS']++;
            } elseif ($status === 'VENCE_HOJE') {
                $faixas['VENCE_HOJE']++;
            } elseif ($restante === 1) {
                $faixas['1_DIA']++;
            } elseif ($restante >= 2 && $restante <= 3) {
                $faixas['2_A_3_DIAS']++;
            } else {
                $faixas['4_MAIS_DIAS']++;
            }
        }

        return $faixas;
    }

    public function atualizarStatus(int $id, string $status, ?int $usuarioId = null): void
    {
        $atual = $this->buscarPorId($id);
        if (!$atual) {
            throw new RuntimeException('Ocorrência não encontrada.');
        }

        if ((string) $atual['status'] === $status) {
            return;
        }

        $sql = "UPDATE ocorrencias
                SET status = :status,
                    atualizado_em = NOW(),
                    concluido_em = CASE
                        WHEN :status = 'CONCLUIDA' THEN NOW()
                        WHEN status = 'CONCLUIDA' AND :status <> 'CONCLUIDA' THEN NULL
                        ELSE concluido_em
                    END
                WHERE id = :id";

        try {
            $this->db->beginTransaction();

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

            if ($stmt->rowCount() < 1) {
                throw new RuntimeException('Nenhuma alteracao de status foi salva.');
            }

            $this->registrarHistorico($id, $usuarioId, $atual['status'], $status, 'Status atualizado no painel administrativo.');
            $this->db->commit();
        } catch (Throwable $e) {
            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }
            throw $e;
        }
    }

    public function historico(int $ocorrenciaId): array
    {
        $stmt = $this->db->prepare("SELECT h.*, u.nome AS usuario_nome
                                    FROM ocorrencia_historico h
                                    LEFT JOIN usuarios u ON u.id = h.usuario_id
                                    WHERE h.ocorrencia_id = :id
                                    ORDER BY h.id DESC");
        $stmt->execute(['id' => $ocorrenciaId]);
        return $stmt->fetchAll();
    }

    public function atualizarSetor(int $id, ?int $setorId, ?int $usuarioId = null): void
    {
        $stmt = $this->db->prepare("UPDATE ocorrencias SET setor_id = :setor_id, atualizado_em = NOW() WHERE id = :id");
        $stmt->bindValue(':id', $id, PDO::PARAM_INT);
        if ($setorId === null) {
            $stmt->bindValue(':setor_id', null, PDO::PARAM_NULL);
        } else {
            $stmt->bindValue(':setor_id', $setorId, PDO::PARAM_INT);
        }
        $stmt->execute();

        if ($stmt->rowCount() > 0) {
            $descricao = $setorId ? 'Setor vinculado/alterado no painel administrativo.' : 'Setor removido no painel administrativo.';
            $this->registrarHistorico($id, $usuarioId, null, 'SETOR_ATUALIZADO', $descricao);
        }
    }

    private function registrarHistorico(int $ocorrenciaId, ?int $usuarioId, ?string $statusAnterior, string $statusNovo, ?string $observacao = null): void
    {
        $stmt = $this->db->prepare("INSERT INTO ocorrencia_historico (ocorrencia_id, usuario_id, status_anterior, status_novo, observacao, criado_em)
                                    VALUES (:ocorrencia_id, :usuario_id, :status_anterior, :status_novo, :observacao, NOW())");
        $stmt->execute([
            'ocorrencia_id' => $ocorrenciaId,
            'usuario_id' => $usuarioId,
            'status_anterior' => $statusAnterior,
            'status_novo' => $statusNovo,
            'observacao' => $observacao,
        ]);
    }

    private function appendSlaMeta(array $items): array
    {
        foreach ($items as &$item) {
            $remaining = isset($item['sla_dias']) ? (int) $item['sla_dias'] : 0;
            $status = (string) ($item['status'] ?? '');
            $slaStatus = $this->resolveSlaStatus($remaining, $status);

            $item['sla_restante'] = $remaining;
            $item['sla_status'] = $slaStatus;
            $item['sla_classe'] = strtolower(str_replace('_', '-', $slaStatus));
            $item['sla_prazo_texto'] = $this->formatSlaLabel($remaining, $status);
            $item['sla_atrasado'] = $slaStatus === 'ATRASADO';
        }
        unset($item);

        return $items;
    }

    private function resolveSlaStatus(int $remaining, string $status): string
    {
        if ($this->isClosedStatus($status)) {
            return 'FINALIZADA';
        }

        if ($remaining < 0) {
            return 'ATRASADO';
        }

        if ($remaining === 0) {
            return 'VENCE_HOJE';
        }

        if ($remaining <= 2) {
            return 'ALERTA';
        }

        return 'NO_PRAZO';
    }

    private function formatSlaLabel(int $remaining, string $status): string
    {
        if ($this->isClosedStatus($status)) {
            return 'Finalizada';
        }

        if ($remaining > 1) {
            return $remaining . ' dias';
        }

        if ($remaining === 1) {
            return '1 dia';
        }

        if ($remaining === 0) {
            return 'Vence hoje';
        }

        return 'Atrasada ' . abs($remaining) . ' dia' . (abs($remaining) > 1 ? 's' : '');
    }

    private function isClosedStatus(string $status): bool
    {
        return in_array($status, ['CONCLUIDA', 'NAO_PROCEDE', 'DUPLICADA'], true);
    }

    private function categoryColorSql(): string
    {
        return $this->hasCategoryColorColumn() ? "COALESCE(NULLIF(c.cor, ''), '#1351b4')" : "'#1351b4'";
    }

    private function slaDiasSql(): string
    {
        return $this->hasCategorySlaColumn() ? 'COALESCE(c.sla_dias, 0)' : '0';
    }

    private function slaDaysRemainingSql(): string
    {
        if (!$this->hasCategorySlaColumn()) {
            return '0';
        }

        return "CASE
                    WHEN o.status IN ('CONCLUIDA','NAO_PROCEDE','DUPLICADA') THEN 0
                    ELSE DATEDIFF(DATE_ADD(DATE(o.criado_em), INTERVAL COALESCE(c.sla_dias, 0) DAY), CURDATE())
                END";
    }

    private function hasCategoryColorColumn(): bool
    {
        return $this->hasColumn('categorias', 'cor');
    }

    private function hasCategorySlaColumn(): bool
    {
        return $this->hasColumn('categorias', 'sla_dias');
    }

    private function hasColumn(string $table, string $column): bool
    {
        $key = $table . '.' . $column;
        if (array_key_exists($key, self::$columnCache)) {
            return self::$columnCache[$key];
        }

        $stmt = $this->db->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table AND COLUMN_NAME = :column");
        $stmt->execute([
            'table' => $table,
            'column' => $column,
        ]);

        self::$columnCache[$key] = (int) $stmt->fetchColumn() > 0;
        return self::$columnCache[$key];
    }

    private function mapStatusesSql(): string
    {
        return "('ABERTA','RECEBIDA','EM_ANALISE','ENCAMINHADA','EM_ATENDIMENTO')";
    }

    private function extractScope(array $filters): array
    {
        $scope = [];

        if (array_key_exists('categoria_ids_scope', $filters) && is_array($filters['categoria_ids_scope'])) {
            $scope['categoria_ids'] = array_values(array_unique(array_filter(array_map('intval', $filters['categoria_ids_scope']), static function ($id) {
                return $id > 0 || $id === -1;
            })));
        }

        if (array_key_exists('setor_id_scope', $filters)) {
            $scope['setor_id'] = (int) $filters['setor_id_scope'];
        } elseif (array_key_exists('setor_id', $filters) && isset($filters['__scope_setor'])) {
            $scope['setor_id'] = (int) $filters['setor_id'];
        }

        return $scope;
    }

    private function applyScope(string &$sql, array &$params, array $scope): void
    {
        if (array_key_exists('setor_id', $scope)) {
            $sql .= " AND o.setor_id = :scope_setor_id";
            $params['scope_setor_id'] = (int) $scope['setor_id'];
        }

        if (array_key_exists('categoria_ids', $scope)) {
            $categoriaIds = array_values(array_unique(array_filter(array_map('intval', (array) $scope['categoria_ids']), static function ($id) {
                return $id > 0 || $id === -1;
            })));

            if (empty($categoriaIds)) {
                $sql .= " AND 1 = 0";
                return;
            }

            $placeholders = [];
            foreach ($categoriaIds as $index => $categoriaId) {
                $placeholder = ':scope_categoria_' . $index;
                $placeholders[] = $placeholder;
                $params[substr($placeholder, 1)] = $categoriaId;
            }

            $sql .= " AND o.categoria_id IN (" . implode(', ', $placeholders) . ")";
        }
    }
}
