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

class Inf_solicitudes_contra_voto extends MY_Model
{
    protected $_table_logs     = 'evi11logrecon';   // Log recordar contraseña
    protected $_table_partic   = 'evi05partic';     // Participantes
    protected $_table_agencia  = 'gen01agencias';   // Agencias/segmento
    protected $_table_votos    = 'evi07audpro';     // Auditoría de voto

    // Columnas para DataTables (orden/búsqueda)
    protected $_column = [
        'p.NUM_IDETER',                            // CC
        "CONCAT(p.TEX_NOMPAR,' ',p.TEX_APEPAR)",   // Nombre completo
        'a.TEX_NOMAGE',                            // Segmento
        'FEC_ULT_SOL',                             // Última solicitud
        'VECES',                                   // Veces que solicitó
        'VOTO'                                     // Sí/No
    ];
    protected $_order = ['FEC_ULT_SOL' => 'DESC'];

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

    private function _base_query()
    {
        // Subselect para saber si votó (al menos un registro en evi07audpro)
        $voto_case = "CASE WHEN (SELECT COUNT(1) FROM {$this->_table_votos} v WHERE v.NUM_IDETER = p.NUM_IDETER) > 0 THEN 'Sí' ELSE 'No' END";

        $this->db
            ->select("
                p.NUM_IDETER,
                p.TEX_NOMPAR, 
                p.TEX_APEPAR, 
                a.TEX_NOMAGE,
                MAX(l.FEC_GENERACION) AS FEC_ULT_SOL,
                COUNT(l.ID_LOGRECON)  AS VECES,
                {$voto_case} AS VOTO
            ", false)
            ->from("{$this->_table_logs} l")
            ->join("{$this->_table_partic} p", "p.NUM_IDETER = l.NUM_IDETER")
            ->join("{$this->_table_agencia} a", "a.COD_AGENCIA = p.COD_AGENCIA")
            ->where('p.IS_DELETE', 0)
            ->group_by('p.NUM_IDETER, p.TEX_NOMPAR, p.TEX_APEPAR, a.TEX_NOMAGE');
    }

    private function _get_datatables_query()
    {
        $this->_base_query();

        // Búsqueda global (DataTables)
        $i = 0;
        if (!empty($_POST['search']['value'])) {
            $search_value = $_POST['search']['value'];
            $this->db->group_start();
            foreach ($this->_column as $item) {
                if ($i === 0) {
                    $this->db->like($item, $search_value, 'both', false);
                } else {
                    $this->db->or_like($item, $search_value, 'both', false);
                }
                $i++;
            }
            $this->db->group_end();
        }

        // Orden
        if (isset($_POST['order'])) {
            $col = $_POST['order'][0]['column'];
            $dir = $_POST['order'][0]['dir'];
            $orderBy = $this->_column[$col] ?? key($this->_order);
            $this->db->order_by($orderBy, $dir);
        } else {
            $this->db->order_by(key($this->_order), $this->_order[key($this->_order)]);
        }
    }

    public function get_datatables()
    {
        $this->_get_datatables_query();
        if ($_POST['length'] != -1) {
            $this->db->limit($_POST['length'], $_POST['start']);
        }
        return $this->db->get()->result();
    }

    public function count_filtered()
    {
        $this->_get_datatables_query();
        return $this->db->get()->num_rows();
    }

    public function count_all()
    {
        // Para count_all respetamos los mismos joins/where pero sin search ni limit
        $this->_base_query();
        return $this->db->get()->num_rows();
    }
}
