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

class reports_model extends CI_Model
{
    function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

    // ═══════════════════════════════════════════════════════════
    // MÉTODOS ORIGINAIS (financeiro/honorários)
    // ═══════════════════════════════════════════════════════════

    function save($save)
    {
        $this->db->insert('acts', $save);
    }

    function get_earning_by_month()
    {
        $y = date("Y");
        $m = date("m");
        $d = @date('t', mktime(0, 0, 0, $m, 1, $y));

        $this->db->where('date >=', date("Y-m-d", strtotime("-" . $d . " days")));
        $this->db->group_by('date', 'ASC');
        $this->db->select('date');
        $this->db->select_sum('amount');
        return $this->db->get('fees')->result();
    }

    function get_earning_by_week()
    {
        $this->db->where('date >=', date("Y-m-d", strtotime("- 7 days")));
        $this->db->group_by('date', 'ASC');
        $this->db->select('date');
        $this->db->select_sum('amount');
        return $this->db->get('fees')->result();
    }

    function get_earning_by_year()
    {
        $this->db->group_by('YEAR(date)');
        $this->db->select('date');
        $this->db->select_sum('amount');
        return $this->db->get('fees')->result();
    }

    function get_earning_by_client()
    {
        $this->db->group_by('C.client_id');
        $this->db->select('date,U.name');
        $this->db->select_sum('amount');
        $this->db->join('cases C', 'C.id = F.case_id', 'LEFT');
        $this->db->join('users U', 'U.id = C.client_id', 'LEFT');
        return $this->db->get('fees F')->result();
    }

    // Gráfico financeiro por mês
    function get_financeiro_by_month($client_id = null)
    {
        $sql = "SELECT
            DATE_FORMAT(vencimento, '%m/%Y') AS mes_label,
            SUM(CASE WHEN tipo='receber' AND status='pago' THEN valor ELSE 0 END) AS recebido,
            SUM(CASE WHEN tipo='pagar' AND status='pago' THEN valor ELSE 0 END) AS pago,
            SUM(CASE WHEN tipo='receber' AND status='pendente' THEN valor ELSE 0 END) AS a_receber
            FROM fin_lancamentos
            WHERE vencimento >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)" . ($client_id ? " AND client_id=" . (int)$client_id : "") . "
            GROUP BY DATE_FORMAT(vencimento, '%Y-%m')
            ORDER BY MIN(vencimento) ASC";
        return $this->db->query($sql)->result();
    }

    // Financeiro por cliente
    function get_financeiro_by_client()
    {
        $sql = "SELECT u.name,
            SUM(CASE WHEN l.tipo='receber' AND l.status='pago' THEN l.valor ELSE 0 END) AS recebido,
            SUM(CASE WHEN l.tipo='receber' AND l.status='pendente' THEN l.valor ELSE 0 END) AS pendente
            FROM fin_lancamentos l
            LEFT JOIN users u ON u.id = l.client_id
            WHERE l.client_id IS NOT NULL
            GROUP BY l.client_id
            ORDER BY recebido DESC
            LIMIT 10";
        return $this->db->query($sql)->result();
    }

    // ═══════════════════════════════════════════════════════════
    // MÉTODOS NOVOS (gráficos ampliados do dashboard de relatórios)
    // ═══════════════════════════════════════════════════════════

    // Novos clientes por mês (últimos 12 meses)
    function get_new_clients_by_month()
    {
        $sql = "SELECT DATE_FORMAT(joining_date, '%m/%Y') AS mes_label, COUNT(*) AS total
                FROM users
                WHERE user_role = 2
                AND joining_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
                GROUP BY DATE_FORMAT(joining_date, '%Y-%m')
                ORDER BY MIN(joining_date) ASC";
        return $this->db->query($sql)->result();
    }

    // Novos processos por mês (últimos 12 meses)
    function get_new_cases_by_month()
    {
        $sql = "SELECT DATE_FORMAT(start_date, '%m/%Y') AS mes_label, COUNT(*) AS total
                FROM cases
                WHERE start_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
                GROUP BY DATE_FORMAT(start_date, '%Y-%m')
                ORDER BY MIN(start_date) ASC";
        return $this->db->query($sql)->result();
    }

    // Processos por fase (case_stage)
    function get_cases_by_stage()
    {
        $sql = "SELECT CS.name AS stage, COUNT(C.id) AS total
                FROM cases C
                LEFT JOIN case_stages CS ON CS.id = C.case_stage_id
                GROUP BY C.case_stage_id
                ORDER BY total DESC";
        return $this->db->query($sql)->result();
    }

    // Processos por categoria
    // OBS: case_category_id é armazenado como texto multi-valor (ex: "1,2,3" ou JSON),
    // por isso a contagem é feita em PHP após buscar os dados brutos.
    function get_cases_by_category()
    {
        $cases = $this->db->select('case_category_id')->get('cases')->result();
        $contagem = [];
        foreach ($cases as $c) {
            $raw = trim($c->case_category_id, '[] ');
            if ($raw === '') continue;
            $ids = array_filter(array_map('trim', explode(',', str_replace('"', '', $raw))));
            foreach ($ids as $id) {
                if (!is_numeric($id)) continue;
                $contagem[$id] = ($contagem[$id] ?? 0) + 1;
            }
        }
        if (empty($contagem)) return [];

        arsort($contagem);
        $contagem = array_slice($contagem, 0, 10, true);

        $ids_str = implode(',', array_keys($contagem));
        $nomes = $this->db->query("SELECT id, name FROM case_categories WHERE id IN ({$ids_str})")->result();
        $mapa_nomes = [];
        foreach ($nomes as $n) $mapa_nomes[$n->id] = $n->name;

        $resultado = [];
        foreach ($contagem as $id => $total) {
            $resultado[] = (object)[
                'categoria' => $mapa_nomes[$id] ?? "Categoria #{$id}",
                'total'     => $total,
            ];
        }
        return $resultado;
    }

    // Produtividade por usuário (tarefas concluídas)
    function get_productivity_by_user()
    {
        $sql = "SELECT U.name,
                COUNT(DISTINCT TA.task_id) AS total_tarefas,
                SUM(CASE WHEN CAST(T.progress AS UNSIGNED) = 100 THEN 1 ELSE 0 END) AS concluidas,
                SUM(CASE WHEN CAST(T.progress AS UNSIGNED) < 100 AND T.due_date < CURDATE() THEN 1 ELSE 0 END) AS vencidas
                FROM task_assigned TA
                LEFT JOIN tasks T ON T.id = TA.task_id
                LEFT JOIN users U ON U.id = TA.user_id
                WHERE U.user_role != 2
                GROUP BY TA.user_id
                ORDER BY concluidas DESC";
        return $this->db->query($sql)->result();
    }

    // Diligências por mês (últimos 6 meses)
    function get_diligencias_by_month()
    {
        // Tenta tabela diligencias, retorna vazio se não existir
        if (!$this->db->table_exists('diligencias')) return [];
        $sql = "SELECT DATE_FORMAT(data_hora, '%m/%Y') AS mes_label, COUNT(*) AS total
                FROM diligencias
                WHERE data_hora >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
                GROUP BY DATE_FORMAT(data_hora, '%Y-%m')
                ORDER BY MIN(data_hora) ASC";
        return $this->db->query($sql)->result();
    }

    // Agendamentos por mês (últimos 6 meses)
    function get_appointments_by_month()
    {
        $sql = "SELECT DATE_FORMAT(date_time, '%m/%Y') AS mes_label, COUNT(*) AS total
                FROM appointments
                WHERE date_time >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
                GROUP BY DATE_FORMAT(date_time, '%Y-%m')
                ORDER BY MIN(date_time) ASC";
        return $this->db->query($sql)->result();
    }

    // KPIs gerais do escritório
    function get_kpis()
    {
        $kpis = new stdClass();
        $kpis->total_clientes    = $this->db->where('user_role', 2)->count_all_results('users');
        $kpis->total_processos   = $this->db->count_all('cases');
        $kpis->processos_ativos  = $this->db->where('is_archived', 0)->count_all_results('cases');
        $kpis->tarefas_pendentes = (int)($this->db->query(
            "SELECT COUNT(*) AS t FROM tasks WHERE CAST(progress AS UNSIGNED) != 100"
        )->row()->t ?? 0);
        $kpis->honorarios_mes    = (float)($this->db->query(
            "SELECT COALESCE(SUM(amount),0) AS t FROM fees WHERE MONTH(date)=MONTH(CURDATE()) AND YEAR(date)=YEAR(CURDATE())"
        )->row()->t ?? 0);
        $kpis->a_receber = (float)($this->db->query(
            "SELECT COALESCE(SUM(valor),0) AS t FROM fin_lancamentos WHERE tipo='receber' AND status='pendente'"
        )->row()->t ?? 0);
        return $kpis;
    }
}
