<?php defined('BASEPATH') OR exit('No direct script access allowed');

class Pat_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    // ═══════════════════════════════════════
    // EXPEDIENTES
    // ═══════════════════════════════════════

    public function get_expedientes($filtros = []) {
        $where = "WHERE 1=1";
        $params = [];
        if (!empty($filtros['status'])) {
            $where .= " AND E.status = ?";
            $params[] = $filtros['status'];
        }
        if (!empty($filtros['devedor_tipo'])) {
            $where .= " AND E.devedor_tipo = ?";
            $params[] = $filtros['devedor_tipo'];
        }
        if (!empty($filtros['busca'])) {
            $where .= " AND E.devedor_nome LIKE ?";
            $params[] = '%' . $filtros['busca'] . '%';
        }

        $sql = "SELECT E.*, C.title AS case_title, C.case_no, U.name AS created_by_name,
            (SELECT COUNT(*) FROM pat_diligencias D WHERE D.expediente_id = E.id) AS total_diligencias,
            (SELECT COUNT(*) FROM pat_diligencias D WHERE D.expediente_id = E.id
                AND D.data_vencimento <= DATE_ADD(CURDATE(), INTERVAL 30 DAY)
                AND D.data_vencimento >= CURDATE()) AS dilig_vencendo,
            (SELECT COUNT(*) FROM pat_diligencias D WHERE D.expediente_id = E.id
                AND D.data_vencimento < CURDATE()) AS dilig_vencidas,
            (SELECT COUNT(*) FROM pat_bens B WHERE B.expediente_id = E.id) AS total_bens
        FROM pat_expedientes E
        LEFT JOIN cases C ON C.id = E.case_id
        LEFT JOIN users U ON U.id = E.created_by
        {$where}
        ORDER BY E.created_at DESC";

        return $this->db->query($sql, $params)->result();
    }

    public function get_expediente($id) {
        $sql = "SELECT E.*, C.title AS case_title, C.case_no, C.fees AS case_fees,
            U.name AS created_by_name
        FROM pat_expedientes E
        LEFT JOIN cases C ON C.id = E.case_id
        LEFT JOIN users U ON U.id = E.created_by
        WHERE E.id = ?";
        return $this->db->query($sql, [(int)$id])->row();
    }

    public function salvar_expediente($dados) {
        $this->db->insert('pat_expedientes', $dados);
        return $this->db->insert_id();
    }

    public function atualizar_expediente($id, $dados) {
        $this->db->where('id', $id);
        $this->db->update('pat_expedientes', $dados);
    }

    public function atualizar_valor_encontrado($expediente_id) {
        $total = $this->db->query(
            "SELECT COALESCE(SUM(valor_estimado), 0) AS t FROM pat_bens WHERE expediente_id = ?",
            [(int)$expediente_id]
        )->row()->t;
        $this->db->where('id', $expediente_id);
        $this->db->update('pat_expedientes', ['valor_encontrado' => $total]);
    }

    // ═══════════════════════════════════════
    // DILIGÊNCIAS
    // ═══════════════════════════════════════

    public function get_diligencias($expediente_id) {
        $sql = "SELECT D.*, U.name AS created_by_name,
            CASE
                WHEN D.data_vencimento < CURDATE() THEN 'vencida'
                WHEN D.data_vencimento <= DATE_ADD(CURDATE(), INTERVAL 30 DAY) THEN 'vencendo'
                ELSE 'vigente'
            END AS situacao_prazo,
            DATEDIFF(D.data_vencimento, CURDATE()) AS dias_restantes
        FROM pat_diligencias D
        LEFT JOIN users U ON U.id = D.created_by
        WHERE D.expediente_id = ?
        ORDER BY D.data_requerimento DESC";
        return $this->db->query($sql, [(int)$expediente_id])->result();
    }

    public function get_diligencia($id) {
        $this->db->where('id', $id);
        return $this->db->get('pat_diligencias')->row();
    }

    public function salvar_diligencia($dados) {
        $this->db->insert('pat_diligencias', $dados);
        return $this->db->insert_id();
    }

    public function atualizar_diligencia($id, $dados) {
        $this->db->where('id', $id);
        $this->db->update('pat_diligencias', $dados);
    }

    public function get_diligencias_vencendo($dias = 30) {
        $sql = "SELECT D.*, E.devedor_nome, E.case_id, C.case_no, C.title AS case_title,
            DATEDIFF(D.data_vencimento, CURDATE()) AS dias_restantes
        FROM pat_diligencias D
        LEFT JOIN pat_expedientes E ON E.id = D.expediente_id
        LEFT JOIN cases C ON C.id = E.case_id
        WHERE D.data_vencimento <= DATE_ADD(CURDATE(), INTERVAL ? DAY)
        AND D.alerta_enviado = 0
        ORDER BY D.data_vencimento ASC";
        return $this->db->query($sql, [(int)$dias])->result();
    }

    // ═══════════════════════════════════════
    // BENS
    // ═══════════════════════════════════════

    public function get_bens($expediente_id) {
        $this->db->where('expediente_id', $expediente_id);
        $this->db->order_by('created_at', 'DESC');
        return $this->db->get('pat_bens')->result();
    }

    public function salvar_bem($dados) {
        $this->db->insert('pat_bens', $dados);
        $id = $this->db->insert_id();
        $this->atualizar_valor_encontrado($dados['expediente_id']);
        return $id;
    }

    public function atualizar_bem($id, $dados) {
        $this->db->where('id', $id);
        $this->db->update('pat_bens', $dados);
        $bem = $this->db->where('id', $id)->get('pat_bens')->row();
        if ($bem) $this->atualizar_valor_encontrado($bem->expediente_id);
    }

    public function deletar_bem($id) {
        $bem = $this->db->where('id', $id)->get('pat_bens')->row();
        $this->db->where('id', $id)->delete('pat_bens');
        if ($bem) $this->atualizar_valor_encontrado($bem->expediente_id);
    }

    // ═══════════════════════════════════════
    // CHECKLIST
    // ═══════════════════════════════════════

    public function get_checklist($expediente_id) {
        $this->db->where('expediente_id', $expediente_id);
        $this->db->order_by('id', 'ASC');
        return $this->db->get('pat_checklist')->result();
    }

    public function atualizar_checklist($id, $dados) {
        $this->db->where('id', $id);
        $this->db->update('pat_checklist', $dados);
    }

    public function get_pct_checklist($expediente_id) {
        $total  = $this->db->where('expediente_id', $expediente_id)->count_all_results('pat_checklist');
        $feitos = $this->db->where('expediente_id', $expediente_id)->where('realizado', 1)->count_all_results('pat_checklist');
        return $total > 0 ? round(($feitos / $total) * 100) : 0;
    }

    // ═══════════════════════════════════════
    // PETIÇÕES
    // ═══════════════════════════════════════

    public function get_peticoes($expediente_id) {
        $sql = "SELECT P.*, U.name AS created_by_name
        FROM pat_peticoes P
        LEFT JOIN users U ON U.id = P.created_by
        WHERE P.expediente_id = ?
        ORDER BY P.created_at DESC";
        return $this->db->query($sql, [(int)$expediente_id])->result();
    }

    public function get_peticao($id) {
        $this->db->where('id', $id);
        return $this->db->get('pat_peticoes')->row();
    }

    public function salvar_peticao($dados) {
        $this->db->insert('pat_peticoes', $dados);
        return $this->db->insert_id();
    }

    public function atualizar_peticao($id, $dados) {
        $this->db->where('id', $id);
        $this->db->update('pat_peticoes', $dados);
    }

    // ═══════════════════════════════════════
    // CONSULTAS API
    // ═══════════════════════════════════════

    public function salvar_consulta_api($dados) {
        $this->db->insert('pat_consultas_api', $dados);
        return $this->db->insert_id();
    }

    public function get_cache_api($documento, $api, $horas = 24) {
        $sql = "SELECT * FROM pat_consultas_api
        WHERE documento = ? AND api = ?
        AND created_at >= DATE_SUB(NOW(), INTERVAL ? HOUR)
        ORDER BY created_at DESC LIMIT 1";
        $r = $this->db->query($sql, [$documento, $api, (int)$horas])->row();
        return $r ? json_decode($r->resposta_json, true) : null;
    }

    // ═══════════════════════════════════════
    // KPIs
    // ═══════════════════════════════════════

    public function get_kpis() {
        $k = new stdClass();
        $k->total_expedientes = (int)$this->db->query(
            "SELECT COUNT(*) AS c FROM pat_expedientes WHERE status = 'ativo'"
        )->row()->c;
        $k->com_bens = (int)$this->db->query(
            "SELECT COUNT(DISTINCT expediente_id) AS c FROM pat_bens"
        )->row()->c;
        $k->sem_bens = $k->total_expedientes - $k->com_bens;
        $k->dilig_vencendo = (int)$this->db->query(
            "SELECT COUNT(*) AS c FROM pat_diligencias
             WHERE data_vencimento BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)"
        )->row()->c;
        $k->dilig_vencidas = (int)$this->db->query(
            "SELECT COUNT(*) AS c FROM pat_diligencias WHERE data_vencimento < CURDATE()"
        )->row()->c;
        $k->valor_encontrado = (float)$this->db->query(
            "SELECT COALESCE(SUM(valor_encontrado), 0) AS t FROM pat_expedientes WHERE status = 'ativo'"
        )->row()->t;
        $k->valor_execucao = (float)$this->db->query(
            "SELECT COALESCE(SUM(valor_execucao), 0) AS t FROM pat_expedientes WHERE status = 'ativo'"
        )->row()->t;
        return $k;
    }
}
