<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class diligencias_model extends CI_Model {

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

    public function get_all(array $f = [])
    {
        $this->db->select('d.*, c.case_no, c.title AS case_title, u.name AS client_nome, e.name AS responsavel_nome');
        $this->db->from('diligencias d');
        $this->db->join('cases c', 'c.id = d.case_id', 'left');
        $this->db->join('users u', 'u.id = d.client_id', 'left');
        $this->db->join('users e', 'e.id = d.responsavel_id', 'left');
        if (!empty($f['status']))    $this->db->where('d.status', $f['status']);
        if (!empty($f['tipo']))      $this->db->where('d.tipo', $f['tipo']);
        if (!empty($f['client_id'])) $this->db->where('d.client_id', $f['client_id']);
        if (!empty($f['data_ini']))  $this->db->where('DATE(d.data_hora) >=', $f['data_ini']);
        if (!empty($f['data_fim']))  $this->db->where('DATE(d.data_hora) <=', $f['data_fim']);
        $this->db->order_by('d.data_hora', 'ASC');
        return $this->db->get()->result();
    }

    public function get_proximas($limit = 5)
    {
        $this->db->select('d.*, c.case_no, u.name AS client_nome');
        $this->db->from('diligencias d');
        $this->db->join('cases c', 'c.id = d.case_id', 'left');
        $this->db->join('users u', 'u.id = d.client_id', 'left');
        $this->db->where('d.data_hora >=', date('Y-m-d H:i:s'));
        $this->db->where_in('d.status', ['agendada', 'em_andamento']);
        $this->db->order_by('d.data_hora', 'ASC');
        $this->db->limit($limit);
        return $this->db->get()->result();
    }

    public function get_totais()
    {
        $sql = "SELECT COUNT(*) AS total,
            SUM(CASE WHEN status='agendada' THEN 1 ELSE 0 END) AS agendadas,
            SUM(CASE WHEN status='em_andamento' THEN 1 ELSE 0 END) AS em_andamento,
            SUM(CASE WHEN status='concluida' THEN 1 ELSE 0 END) AS concluidas,
            SUM(CASE WHEN data_prazo < CURDATE() AND status NOT IN ('concluida','cancelada') THEN 1 ELSE 0 END) AS vencidas
            FROM diligencias";
        return $this->db->query($sql)->row();
    }

    public function get($id)
    {
        $this->db->select('d.*, c.case_no, c.title AS case_title, u.name AS client_nome, e.name AS responsavel_nome');
        $this->db->from('diligencias d');
        $this->db->join('cases c', 'c.id = d.case_id', 'left');
        $this->db->join('users u', 'u.id = d.client_id', 'left');
        $this->db->join('users e', 'e.id = d.responsavel_id', 'left');
        $this->db->where('d.id', $id);
        return $this->db->get()->row();
    }

    public function save(array $d)
    {
        $this->db->insert('diligencias', $d);
        return $this->db->insert_id();
    }

    public function update($id, array $d)
    {
        $this->db->where('id', $id)->update('diligencias', $d);
    }

    public function delete($id)
    {
        $this->db->where('id', $id)->delete('diligencias');
    }
}
