<?php
// cron_daily_digest.php — Resumo diário de alertas
require_once __DIR__ . '/auth.php';

// ===== CONFIG =====
$SEND_TO = [
  'ti@empresa.com.br',
  // 'gestao@empresa.com.br',
];
$SUBJECT = 'SDTrack — Resumo diário de alertas';

// ===== COLETA (mesmos critérios do alerts.php) =====
$soon_days  = 3; $no_due_age = 90; $maint_days = 15; $term_grace = 3;

$overdue = $pdo->query("
  SELECT l.id, e.matricula, e.nome, 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()
")->fetchAll();

$st_soon=$pdo->prepare("
  SELECT l.id, e.matricula, e.nome, l.due_date FROM loans l
  JOIN employees e ON e.id=l.employee_id
  WHERE l.status='DISPONIBILIZADO' AND l.due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL ? DAY)
");
$st_soon->execute([$soon_days]); $due_soon=$st_soon->fetchAll();

$st_nodue=$pdo->prepare("
  SELECT l.id, e.matricula, e.nome, 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) > ?
");
$st_nodue->execute([$no_due_age]); $no_due_long=$st_nodue->fetchAll();

$st_term=$pdo->prepare("
  SELECT l.id, e.matricula, e.nome, 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)
");
$st_term->execute([$term_grace]); $no_term=$st_term->fetchAll();

$rr_over=$pdo->query("
  SELECT rr.id, e.matricula, e.nome, rr.deadline 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()
")->fetchAll();

$st_maint=$pdo->prepare("
  SELECT e.id, et.name tipo, e.modelo,
         (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)) > ?
");
$st_maint->execute([$maint_days]); $maint_long=$st_maint->fetchAll();

$no_qr=$pdo->query("
  SELECT COUNT(*) FROM equipment e WHERE e.status='EM_ESTOQUE' AND (e.tag_qr IS NULL OR e.tag_qr='')
")->fetchColumn();

$lines = [];
$lines[] = 'Vencidos: '.count($overdue);
$lines[] = 'A vencer ('.$soon_days.'d): '.count($due_soon);
$lines[] = 'Sem prazo > '.$no_due_age.'d: '.count($no_due_long);
$lines[] = 'Sem termo > '.$term_grace.'d: '.count($no_term);
$lines[] = 'Solicitações vencidas: '.count($rr_over);
$lines[] = 'Manutenção > '.$maint_days.'d: '.count($maint_long);
$lines[] = 'Estoque sem QR: '.$no_qr;

$body = "<h3>Resumo diário de alertas</h3><ul>";
foreach ($lines as $l) { $body .= "<li>".htmlspecialchars($l)."</li>"; }
$body .= "</ul><p>Acesse: ".$_SERVER['HTTP_HOST']."/alerts.php</p>";

// ===== ENVIO (mail simples) =====
$headers  = "MIME-Version: 1.0\r\n";
$headers .= "Content-type: text/html; charset=UTF-8\r\n";
$headers .= "From: SDTrack  <no-reply@".$_SERVER['HTTP_HOST'].">\r\n";

foreach ($SEND_TO as $to) {
  @mail($to, $SUBJECT, $body, $headers);
}

echo "OK\n";
