<?php
$title = 'Alertas';
require_once __DIR__ . '/auth.php';
require_login();
check_csrf();

// Parâmetros (GET) com defaults
$soon_days   = max(1, (int)($_GET['soon_days']   ?? 3));   // "a vencer" em X dias
$no_due_age  = max(1, (int)($_GET['no_due_age']  ?? 90));  // sem prazo há X dias
$maint_days  = max(1, (int)($_GET['maint_days']  ?? 15));  // manutenção há X dias
$term_grace  = max(0, (int)($_GET['term_grace']  ?? 3));   // prazo p/ anexar termo

// 1) Empréstimos vencidos (com due_date < hoje)
$overdue = $pdo->query("
  SELECT l.id, l.employee_id, e.matricula, e.nome, l.chamado_glpi, l.data_entrega, l.due_date
  FROM loans l
  JOIN employees e ON e.id=l.employee_id
  WHERE l.status='DISPONIBILIZADO' AND l.due_date IS NOT NULL AND l.due_date < CURDATE()
  ORDER BY l.due_date ASC, l.id ASC
")->fetchAll();

// 2) Empréstimos a vencer (due_date entre hoje e +N dias)
$st_soon = $pdo->prepare("
  SELECT l.id, l.employee_id, e.matricula, e.nome, l.chamado_glpi, l.data_entrega, l.due_date
  FROM loans l
  JOIN employees e ON e.id=l.employee_id
  WHERE l.status='DISPONIBILIZADO'
    AND l.due_date IS NOT NULL
    AND l.due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL ? DAY)
  ORDER BY l.due_date ASC, l.id ASC
");
$st_soon->execute([$soon_days]);
$due_soon = $st_soon->fetchAll();

// 3) Empréstimos sem prazo há muito tempo (due_date NULL e data_entrega > X dias atrás)
$st_nodue = $pdo->prepare("
  SELECT l.id, l.employee_id, e.matricula, e.nome, l.chamado_glpi, l.data_entrega
  FROM loans l
  JOIN employees e ON e.id=l.employee_id
  WHERE l.status='DISPONIBILIZADO'
    AND l.due_date IS NULL
    AND DATEDIFF(CURDATE(), l.data_entrega) > ?
  ORDER BY l.data_entrega ASC, l.id ASC
");
$st_nodue->execute([$no_due_age]);
$no_due_long = $st_nodue->fetchAll();

// 4) Empréstimos sem termo anexado (graça de N dias após entrega)
$st_term = $pdo->prepare("
  SELECT l.id, l.employee_id, e.matricula, e.nome, l.chamado_glpi, l.data_entrega
  FROM loans l
  JOIN employees e ON e.id=l.employee_id
  WHERE l.status='DISPONIBILIZADO'
    AND l.data_entrega <= DATE_SUB(CURDATE(), INTERVAL ? DAY)
    AND NOT EXISTS (SELECT 1 FROM loan_terms t WHERE t.loan_id=l.id)
  ORDER BY l.data_entrega ASC, l.id ASC
");
$st_term->execute([$term_grace]);
$no_term = $st_term->fetchAll();

// 5) Solicitações de devolução vencidas (prazo ultrapassado)
$rr_over = $pdo->query("
  SELECT rr.id, rr.employee_id, e.matricula, e.nome, rr.deadline, rr.status
  FROM return_requests rr
  JOIN employees e ON e.id=rr.employee_id
  WHERE rr.status IN ('ABERTA','EM_ANDAMENTO')
    AND rr.deadline IS NOT NULL AND rr.deadline < CURDATE()
  ORDER BY rr.deadline ASC, rr.id ASC
")->fetchAll();

// 6) Equipamentos em manutenção há muito tempo
$st_maint = $pdo->prepare("
  SELECT e.id, et.name tipo, e.modelo, e.patrimonio, e.serial,
         (SELECT MAX(l.created_at)
            FROM equipment_log l
           WHERE l.equipment_id=e.id AND l.to_status='MANUTENCAO') AS since
  FROM equipment e
  JOIN equipment_types et ON et.id=e.type_id
  WHERE e.status='MANUTENCAO'
  HAVING since IS NULL OR DATEDIFF(CURDATE(), DATE(since)) > ?
  ORDER BY since IS NULL DESC, since ASC, e.id ASC
");
$st_maint->execute([$maint_days]);
$maint_long = $st_maint->fetchAll();

// 7) Equipamentos sem TAG/QR (apenas os em estoque)
$no_qr = $pdo->query("
  SELECT e.id, et.name tipo, e.modelo, e.patrimonio, e.serial
  FROM equipment e
  JOIN equipment_types et ON et.id=e.type_id
  WHERE e.status='EM_ESTOQUE' AND (e.tag_qr IS NULL OR e.tag_qr='')
  ORDER BY e.id DESC
  LIMIT 300
")->fetchAll();

// Totais
$tot_overdue  = count($overdue);
$tot_soon     = count($due_soon);
$tot_nodue    = count($no_due_long);
$tot_no_term  = count($no_term);
$tot_rr_over  = count($rr_over);
$tot_maint    = count($maint_long);
$tot_no_qr    = count($no_qr);
$tot_all      = $tot_overdue+$tot_soon+$tot_nodue+$tot_no_term+$tot_rr_over+$tot_maint+$tot_no_qr;

include __DIR__ . '/header.php';
?>
<div class="container-fluid">
  <div class="d-flex justify-content-between align-items-center mb-3">
    <h2 class="h5 m-0">Alertas</h2>
    <span class="badge bg-danger-subtle text-danger fs-6">Total: <?= (int)$tot_all ?></span>
  </div>

  <!-- Filtros/Parâmetros -->
  <form class="row g-2 align-items-end mb-4" method="get">
    <div class="col-md-2">
      <label class="form-label">A vencer (dias)</label>
      <input type="number" min="1" name="soon_days" class="form-control" value="<?= (int)$soon_days ?>">
    </div>
    <div class="col-md-2">
      <label class="form-label">Sem prazo há (dias)</label>
      <input type="number" min="1" name="no_due_age" class="form-control" value="<?= (int)$no_due_age ?>">
    </div>
    <div class="col-md-2">
      <label class="form-label">Manutenção há (dias)</label>
      <input type="number" min="1" name="maint_days" class="form-control" value="<?= (int)$maint_days ?>">
    </div>
    <div class="col-md-3">
      <label class="form-label">Prazo p/ termo (dias)</label>
      <input type="number" min="0" name="term_grace" class="form-control" value="<?= (int)$term_grace ?>">
    </div>
    <div class="col-md-3 d-flex gap-2">
      <button class="btn btn-outline-primary flex-fill">Aplicar</button>
      <a class="btn btn-outline-secondary" href="<?= base_url('alerts.php') ?>">Limpar</a>
    </div>
  </form>

  <!-- 1) Empréstimos vencidos -->
  <div class="card border-0 shadow-sm mb-4">
    <div class="card-header bg-white d-flex justify-content-between align-items-center">
      <div><i class="bi bi-exclamation-octagon me-2 text-danger"></i><strong>Empréstimos vencidos</strong></div>
      <span class="badge bg-danger"><?= (int)$tot_overdue ?></span>
    </div>
    <div class="card-body p-0">
      <div class="table-responsive">
        <table class="table align-middle mb-0">
          <thead><tr><th>#</th><th>Matrícula</th><th>Nome</th><th>GLPI</th><th>Entrega</th><th>Prev. devolução</th><th></th></tr></thead>
          <tbody>
          <?php foreach ($overdue as $r): ?>
            <tr class="table-danger">
              <td>#<?= (int)$r['id'] ?></td>
              <td><?= h($r['matricula']) ?></td>
              <td><?= h($r['nome']) ?></td>
              <td><?= h($r['chamado_glpi'] ?? '') ?></td>
              <td><?= br_date($r['data_entrega']) ?></td>
              <td><?= br_date($r['due_date']) ?></td>
              <td class="text-end"><a class="btn btn-sm btn-outline-primary" href="<?= base_url('loan_view.php?id='.(int)$r['id']) ?>">Abrir</a></td>
            </tr>
          <?php endforeach; if (!$overdue): ?>
            <tr><td colspan="7" class="text-muted">Sem itens vencidos.</td></tr>
          <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>

  <!-- 2) Empréstimos a vencer -->
  <div class="card border-0 shadow-sm mb-4">
    <div class="card-header bg-white d-flex justify-content-between align-items-center">
      <div><i class="bi bi-hourglass-split me-2 text-warning"></i><strong>Empréstimos a vencer (próx. <?= (int)$soon_days ?> dias)</strong></div>
      <span class="badge bg-warning text-dark"><?= (int)$tot_soon ?></span>
    </div>
    <div class="card-body p-0">
      <div class="table-responsive">
        <table class="table align-middle mb-0">
          <thead><tr><th>#</th><th>Matrícula</th><th>Nome</th><th>GLPI</th><th>Entrega</th><th>Prev. devolução</th><th></th></tr></thead>
          <tbody>
          <?php foreach ($due_soon as $r): ?>
            <tr class="table-warning">
              <td>#<?= (int)$r['id'] ?></td>
              <td><?= h($r['matricula']) ?></td>
              <td><?= h($r['nome']) ?></td>
              <td><?= h($r['chamado_glpi'] ?? '') ?></td>
              <td><?= br_date($r['data_entrega']) ?></td>
              <td><?= br_date($r['due_date']) ?></td>
              <td class="text-end"><a class="btn btn-sm btn-outline-primary" href="<?= base_url('loan_view.php?id='.(int)$r['id']) ?>">Abrir</a></td>
            </tr>
          <?php endforeach; if (!$due_soon): ?>
            <tr><td colspan="7" class="text-muted">Nada a vencer no período.</td></tr>
          <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>

  <!-- 3) Empréstimos sem prazo há muito tempo -->
  <div class="card border-0 shadow-sm mb-4">
    <div class="card-header bg-white d-flex justify-content-between align-items-center">
      <div><i class="bi bi-calendar-x me-2 text-secondary"></i><strong>Sem previsão há mais de <?= (int)$no_due_age ?> dias</strong></div>
      <span class="badge bg-secondary"><?= (int)$tot_nodue ?></span>
    </div>
    <div class="card-body p-0">
      <div class="table-responsive">
        <table class="table align-middle mb-0">
          <thead><tr><th>#</th><th>Matrícula</th><th>Nome</th><th>GLPI</th><th>Entrega</th><th></th></tr></thead>
          <tbody>
          <?php foreach ($no_due_long as $r): ?>
            <tr>
              <td>#<?= (int)$r['id'] ?></td>
              <td><?= h($r['matricula']) ?></td>
              <td><?= h($r['nome']) ?></td>
              <td><?= h($r['chamado_glpi'] ?? '') ?></td>
              <td><?= br_date($r['data_entrega']) ?></td>
              <td class="text-end"><a class="btn btn-sm btn-outline-primary" href="<?= base_url('loan_view.php?id='.(int)$r['id']) ?>">Abrir</a></td>
            </tr>
          <?php endforeach; if (!$no_due_long): ?>
            <tr><td colspan="6" class="text-muted">Nenhum empréstimo antigo sem prazo.</td></tr>
          <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>

  <!-- 4) Empréstimos sem termo anexado -->
  <div class="card border-0 shadow-sm mb-4">
    <div class="card-header bg-white d-flex justify-content-between align-items-center">
      <div><i class="bi bi-file-earmark-excel me-2 text-danger"></i><strong>Sem termo anexado (graça de <?= (int)$term_grace ?> dias)</strong></div>
      <span class="badge bg-danger"><?= (int)$tot_no_term ?></span>
    </div>
    <div class="card-body p-0">
      <div class="table-responsive">
        <table class="table align-middle mb-0">
          <thead><tr><th>#</th><th>Matrícula</th><th>Nome</th><th>GLPI</th><th>Entrega</th><th></th></tr></thead>
          <tbody>
          <?php foreach ($no_term as $r): ?>
            <tr class="table-danger">
              <td>#<?= (int)$r['id'] ?></td>
              <td><?= h($r['matricula']) ?></td>
              <td><?= h($r['nome']) ?></td>
              <td><?= h($r['chamado_glpi'] ?? '') ?></td>
              <td><?= br_date($r['data_entrega']) ?></td>
              <td class="text-end"><a class="btn btn-sm btn-outline-primary" href="<?= base_url('loan_view.php?id='.(int)$r['id']) ?>">Anexar termo</a></td>
            </tr>
          <?php endforeach; if (!$no_term): ?>
            <tr><td colspan="6" class="text-muted">Todos com termo anexado dentro do prazo.</td></tr>
          <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>

  <!-- 5) Solicitações de devolução vencidas -->
  <div class="card border-0 shadow-sm mb-4">
    <div class="card-header bg-white d-flex justify-content-between align-items-center">
      <div><i class="bi bi-flag-fill me-2 text-warning"></i><strong>Solicitações de devolução vencidas</strong></div>
      <span class="badge bg-warning text-dark"><?= (int)$tot_rr_over ?></span>
    </div>
    <div class="card-body p-0">
      <div class="table-responsive">
        <table class="table align-middle mb-0">
          <thead><tr><th>#</th><th>Matrícula</th><th>Nome</th><th>Prazo</th><th>Status</th><th></th></tr></thead>
          <tbody>
          <?php foreach ($rr_over as $r): ?>
            <tr class="table-warning">
              <td>#<?= (int)$r['id'] ?></td>
              <td><?= h($r['matricula']) ?></td>
              <td><?= h($r['nome']) ?></td>
              <td><?= br_date($r['deadline'] ?? null) ?></td>
              <td><span class="badge bg-secondary"><?= t_rr_status($r['status']) ?></span></td>
              <td class="text-end"><a class="btn btn-sm btn-outline-primary" href="<?= base_url('return_request_view.php?id='.(int)$r['id']) ?>">Abrir solicitação</a></td>
            </tr>
          <?php endforeach; if (!$rr_over): ?>
            <tr><td colspan="6" class="text-muted">Nenhuma solicitação vencida.</td></tr>
          <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>

  <!-- 6) Equipamentos em manutenção há muito tempo -->
  <div class="card border-0 shadow-sm mb-4">
    <div class="card-header bg-white d-flex justify-content-between align-items-center">
      <div><i class="bi bi-tools me-2 text-secondary"></i><strong>Manutenção acima de <?= (int)$maint_days ?> dias</strong></div>
      <span class="badge bg-secondary"><?= (int)$tot_maint ?></span>
    </div>
    <div class="card-body p-0">
      <div class="table-responsive">
        <table class="table align-middle mb-0">
          <thead><tr><th>#</th><th>Tipo/Modelo</th><th>PAT</th><th>SN</th><th>Desde</th><th></th></tr></thead>
          <tbody>
          <?php foreach ($maint_long as $r): ?>
            <?php $since = $r['since'] ? br_datetime($r['since']) : '—'; ?>
            <tr>
              <td>#<?= (int)$r['id'] ?></td>
              <td><?= h(($r['tipo'] ?? '—') . ($r['modelo'] ? ' • '.$r['modelo'] : '')) ?></td>
              <td><?= h($r['patrimonio'] ?? '—') ?></td>
              <td><?= h($r['serial'] ?? '—') ?></td>
              <td><?= $since ?></td>
              <td class="text-end"><a class="btn btn-sm btn-outline-secondary" href="<?= base_url('equipment_history.php?id='.(int)$r['id']) ?>">Histórico</a></td>
            </tr>
          <?php endforeach; if (!$maint_long): ?>
            <tr><td colspan="6" class="text-muted">Nenhum item com manutenção prolongada.</td></tr>
          <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>

  <!-- 7) Equipamentos em estoque sem TAG/QR -->
  <div class="card border-0 shadow-sm mb-5">
    <div class="card-header bg-white d-flex justify-content-between align-items-center">
      <div><i class="bi bi-qr-code me-2 text-info"></i><strong>Em estoque sem etiqueta QR</strong></div>
      <span class="badge bg-info text-dark"><?= (int)$tot_no_qr ?></span>
    </div>
    <div class="card-body p-0">
      <div class="table-responsive">
        <table class="table align-middle mb-0">
          <thead><tr><th>#</th><th>Tipo/Modelo</th><th>PAT</th><th>SN</th><th></th></tr></thead>
          <tbody>
          <?php foreach ($no_qr as $r): ?>
            <tr>
              <td>#<?= (int)$r['id'] ?></td>
              <td><?= h(($r['tipo'] ?? '—') . ($r['modelo'] ? ' • '.$r['modelo'] : '')) ?></td>
              <td><?= h($r['patrimonio'] ?? '—') ?></td>
              <td><?= h($r['serial'] ?? '—') ?></td>
              <td class="text-end"><a class="btn btn-sm btn-outline-secondary" href="<?= base_url('qr_label.php?id='.(int)$r['id']) ?>" target="_blank">Gerar/Imprimir etiqueta</a></td>
            </tr>
          <?php endforeach; if (!$no_qr): ?>
            <tr><td colspan="5" class="text-muted">Todos os itens em estoque possuem TAG/QR.</td></tr>
          <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>

</div>
<?php include __DIR__ . '/footer.php'; ?>
