<?php
$authApp = 'litigantes';
session_start();
require __DIR__ . '/../../config/auth.php';
require __DIR__ . '/../../../../../config/db2.php';
require __DIR__ . '/../../lib/helpers.php';
if (isset($_GET['toggle'])) {
    $pdo->prepare("UPDATE mrbs_juez SET activo = 1 - activo WHERE juez_id=?")->execute([$_GET['toggle']]);
    $fRedir = ['f_nombre' => trim($_GET['f_nombre'] ?? ''), 'f_instancia' => $_GET['f_instancia'] ?? '', 'f_activo' => $_GET['f_activo'] ?? '1', 'f_comp' => $_GET['f_comp'] ?? '', 'f_colegio' => $_GET['f_colegio'] ?? '', 'f_distrito' => $_GET['f_distrito'] ?? ''];
    header('Location: listarJueces.php?' . http_build_query($fRedir));
    exit;
}

$paginaActual = 'litigantes';
require __DIR__ . '/../../config/menu.php';

$cols_ok = ['j.juez_id','j.nombreCompleto','j.nombre','j.instancia','j.colegio','j.distrito','j.activo','comp_actual'];
$sort    = in_array($_GET['sort'] ?? '', $cols_ok, true) ? $_GET['sort'] : 'j.juez_id';
$dir     = strtoupper($_GET['dir'] ?? 'ASC') === 'DESC' ? 'DESC' : 'ASC';
$nextDir = $dir === 'ASC' ? 'DESC' : 'ASC';
$ind     = $dir === 'ASC' ? '▲' : '▼';
function sl($col,$lbl,$sort,$dir,$nextDir,$ind,$f){
    $d = $sort===$col ? $nextDir : 'ASC';
    return '<a href="?'.http_build_query(array_merge($f,['sort'=>$col,'dir'=>$d])).'">'.$lbl.($sort===$col?' '.$ind:'').'</a>';
}

$hoy = date('Y-m-d');

// Valores para combos de filtro
$competenciasDisponibles = $pdo->query(
    "SELECT DISTINCT competencia FROM mrbs_competencia_jueces WHERE competencia IS NOT NULL AND competencia <> '' ORDER BY competencia"
)->fetchAll(PDO::FETCH_COLUMN);

$colegiosDisponibles  = ['Rosario', 'Interdistrital'];
$distritosDisponibles = ['Rosario', 'Villa Constitución', 'Cañada de Gomez', 'Casilda', 'San Lorenzo'];

$f = [
    'f_nombre'    => trim($_GET['f_nombre'] ?? ''),
    'f_instancia' => $_GET['f_instancia'] ?? '',
    'f_activo'    => $_GET['f_activo'] ?? '1',
    'f_comp'      => $_GET['f_comp'] ?? '',
    'f_colegio'   => $_GET['f_colegio'] ?? '',
    'f_distrito'  => $_GET['f_distrito'] ?? '',
];

// Construir WHERE sobre la tabla base
$whereBase  = [];
$paramsBase = [];
if ($f['f_nombre'])     { $whereBase[] = "(j.nombreCompleto LIKE ? OR j.nombre LIKE ?)"; $paramsBase[] = '%'.$f['f_nombre'].'%'; $paramsBase[] = '%'.$f['f_nombre'].'%'; }
if ($f['f_instancia'] !== '') { $whereBase[] = "j.instancia = ?";  $paramsBase[] = (int)$f['f_instancia']; }
if ($f['f_activo'] !== '')    { $whereBase[] = "j.activo = ?";     $paramsBase[] = (int)$f['f_activo']; }
if ($f['f_colegio'] !== '')   { $whereBase[] = "j.colegio = ?";    $paramsBase[] = $f['f_colegio']; }
if ($f['f_distrito'] !== '')  { $whereBase[] = "j.distrito = ?";   $paramsBase[] = $f['f_distrito']; }

$wBaseSql = $whereBase ? 'WHERE '.implode(' AND ', $whereBase) : '';

// Filtro de competencia sobre el resultado del subquery → usar derived table
$whereOuter  = [];
$paramsOuter = [];
if ($f['f_comp'] !== '') { $whereOuter[] = "comp_actual = ?"; $paramsOuter[] = $f['f_comp']; }
$wOuterSql = $whereOuter ? 'WHERE '.implode(' AND ', $whereOuter) : '';

// En la derived table el alias j. ya no existe; quitarlo para el ORDER BY externo
$sortCol = str_replace('j.', '', $sort);

$sql = "SELECT * FROM (
            SELECT j.*,
                (SELECT cj.competencia FROM mrbs_competencia_jueces cj
                 WHERE cj.juez_id = j.juez_id AND ? BETWEEN cj.desde AND cj.hasta
                 ORDER BY cj.desde DESC LIMIT 1) AS comp_actual
            FROM mrbs_juez j
            $wBaseSql
        ) AS sub
        $wOuterSql
        ORDER BY $sortCol $dir";

$params = array_merge([$hoy], $paramsBase, $paramsOuter);
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<div class="ph">
  <div>
    <h1>Jueces</h1>
    <div class="bc"><a href="<?=BASE_URL?>/index.php">Inicio</a> › Litigantes › Jueces</div>
  </div>
  <?php if (perm('litigantes','agregar')): ?><a href="crearJuez.php" class="btn btn-p">+ Nuevo Juez</a><?php endif; ?>
</div>

<?php if (isset($_GET['ok'])): ?>
  <div class="msg msg-ok">Juez guardado correctamente.</div>
<?php endif; ?>

<div class="card">
  <form method="GET" id="fForm" class="fb">
    <div class="fi">
      <label>Nombre</label>
      <input type="text" name="f_nombre" value="<?=htmlspecialchars($f['f_nombre'])?>" placeholder="Buscar...">
    </div>
    <div class="fi">
      <label>Instancia</label>
      <select name="f_instancia">
        <option value="">Todas</option>
        <option value="1" <?=$f['f_instancia']==='1'?'selected':''?>>1 - Primera</option>
        <option value="2" <?=$f['f_instancia']==='2'?'selected':''?>>2 - Segunda</option>
      </select>
    </div>
    <div class="fi">
      <label>Competencia actual</label>
      <select name="f_comp">
        <option value="">Todas</option>
        <?php foreach ($competenciasDisponibles as $cv): ?>
          <option value="<?=htmlspecialchars($cv)?>" <?=$f['f_comp']===$cv?'selected':''?>><?=htmlspecialchars($cv)?></option>
        <?php endforeach; ?>
      </select>
    </div>
    <div class="fi">
      <label>Colegio</label>
      <select name="f_colegio">
        <option value="">Todos</option>
        <?php foreach ($colegiosDisponibles as $c): ?>
          <option value="<?=htmlspecialchars($c)?>" <?=$f['f_colegio']===$c?'selected':''?>><?=htmlspecialchars($c)?></option>
        <?php endforeach; ?>
      </select>
    </div>
    <div class="fi">
      <label>Distrito</label>
      <select name="f_distrito">
        <option value="">Todos</option>
        <?php foreach ($distritosDisponibles as $d): ?>
          <option value="<?=htmlspecialchars($d)?>" <?=$f['f_distrito']===$d?'selected':''?>><?=htmlspecialchars($d)?></option>
        <?php endforeach; ?>
      </select>
    </div>
    <div class="fi">
      <label>Activo</label>
      <select name="f_activo">
        <option value="">Todos</option>
        <option value="1" <?=$f['f_activo']==='1'?'selected':''?>>Sí</option>
        <option value="0" <?=$f['f_activo']==='0'?'selected':''?>>No</option>
      </select>
    </div>
    <div class="fi" style="display:flex;align-items:flex-end;gap:6px">
      <button class="btn btn-p" type="submit">Filtrar</button>
      <a href="listarJueces.php" class="btn btn-g">Limpiar</a>
    </div>
    <input type="hidden" name="sort" value="<?=htmlspecialchars($sort)?>">
    <input type="hidden" name="dir"  value="<?=htmlspecialchars($dir)?>">
  </form>
</div>

<div style="margin-bottom:6px;font-size:13px;color:#475569"><?=count($rows)?> resultado(s)</div>

<div class="tw">
  <table>
    <thead><tr>
      <th style="width:65px"><?=sl('j.juez_id','ID',$sort,$dir,$nextDir,$ind,$f)?></th>
      <th style="width:160px"><?=sl('j.nombreCompleto','Nombre Completo',$sort,$dir,$nextDir,$ind,$f)?></th>
      <th style="width:90px"><?=sl('j.nombre','Apellido Corto',$sort,$dir,$nextDir,$ind,$f)?></th>
      <th style="width:55px"><?=sl('j.instancia','Inst.',$sort,$dir,$nextDir,$ind,$f)?></th>
      <th style="width:105px"><?=sl('comp_actual','Competencia',$sort,$dir,$nextDir,$ind,$f)?></th>
      <th style="width:110px"><?=sl('j.colegio','Colegio',$sort,$dir,$nextDir,$ind,$f)?></th>
      <th style="width:100px"><?=sl('j.distrito','Distrito',$sort,$dir,$nextDir,$ind,$f)?></th>
      <th style="width:65px"><?=sl('j.activo','Activo',$sort,$dir,$nextDir,$ind,$f)?></th>
      <th style="width:190px">Acciones</th>
    </tr></thead>
    <tbody>
    <?php foreach ($rows as $r): ?>
      <tr>
        <td><?=htmlspecialchars($r['juez_id'])?></td>
        <td><?=htmlspecialchars($r['nombreCompleto'])?></td>
        <td><?=htmlspecialchars($r['nombre']??'')?></td>
        <td style="text-align:center"><?=$r['instancia']?></td>
        <td style="text-align:center">
          <?php if ($r['comp_actual']): ?>
            <span class="bj bj-t"><?=htmlspecialchars($r['comp_actual'])?></span>
          <?php else: ?>
            <span style="color:#94a3b8">—</span>
          <?php endif; ?>
        </td>
        <td><?=htmlspecialchars($r['colegio']??'')?></td>
        <td><?=htmlspecialchars($r['distrito']??'')?></td>
        <td style="text-align:center">
          <?=$r['activo'] ? '<span class="bp bp-l">Sí</span>' : '<span class="bp bp-r">No</span>'?>
        </td>
        <td>
          <div class="ac">
            <?php if (perm('litigantes','editar')): ?><a href="crearJuez.php?id=<?=urlencode($r['juez_id'])?>" class="btn btn-i btn-sm">Editar</a><?php endif; ?>
            <a href="verTurnosJuez.php?juez_id=<?=urlencode($r['juez_id'])?>" class="btn btn-w btn-sm">Turnos</a>
            <?php if (perm('litigantes','editar')): ?>
            <?php if ($r['activo']): ?>
            <a href="?toggle=<?=urlencode($r['juez_id'])?>&<?=http_build_query($f)?>"
               class="btn btn-r btn-sm" onclick="return confirm('¿Confirma la desactivación del juez?')">Desactivar</a>
            <?php else: ?>
            <a href="?toggle=<?=urlencode($r['juez_id'])?>&<?=http_build_query($f)?>"
               class="btn btn-s btn-sm" onclick="return confirm('¿Confirma la activación del juez?')">Activar</a>
            <?php endif; ?>
            <?php endif; ?>
          </div>
        </td>
      </tr>
    <?php endforeach; ?>
    <?php if (!$rows): ?>
      <tr><td colspan="9" style="text-align:center;color:#94a3b8">Sin resultados</td></tr>
    <?php endif; ?>
    </tbody>
  </table>
</div>

<script>
(function(){
  const f = document.getElementById('fForm');
  f.querySelectorAll('select').forEach(el =>
    el.addEventListener('change', () => f.submit())
  );
  f.querySelectorAll('input[type=text]').forEach(el => {
    el.addEventListener('keydown', e => { if (e.key === 'Enter') { e.preventDefault(); f.submit(); } });
    el.addEventListener('blur', () => f.submit());
  });
})();
</script>
</div></body></html>
