<?php
require_once __DIR__ . '/auth.php';
require_login();
// sem check_csrf aqui para facilitar link de export

function get($k, $def=''){ return isset($_GET[$k]) ? trim($_GET[$k]) : $def; }
function ymd($d){
  if (!$d) return null;
  if (preg_match('~^\d{2}/\d{2}/\d{4}$~',$d)) { [$dd,$mm,$yy]=explode('/',$d); return "$yy-$mm-$dd"; }
  if (preg_match('~^\d{4}-\d{2}-\d{2}$~',$d)) return $d;
  return null;
}

$tab = get('tab','loans');

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="'.$tab.'_'.date('Ymd_His').'.csv"');

$out = fopen('php://output','w');
// BOM para Excel abrir com acentos
fwrite($out, chr(0xEF).chr(0xBB).chr(0xBF));

if ($tab==='loans') {
  $status = get('status','');
  $op_id  = (int)get('operation_id','0');
  $has_due= get('has_due','');
  $q      = get('q','');
  $from   = ymd(get('from',''));
  $to     = ymd(get('to',''));

  $where=[]; $args=[];
  if ($status!==''){ $where[]='l.status=?'; $args[]=$status; }
  if ($op_id>0){ $where[]='l.operation_id=?'; $args[]=$op_id; }
  if ($has_due==='yes'){ $where[]='l.due_date IS NOT NULL'; }
  if ($q!==''){ $where[]='(e.matricula LIKE ? OR e.nome LIKE ? OR l.chamado_glpi LIKE ?)'; array_push($args,"%$q%","%$q%","%$q%"); }
  if ($from){ $where[]='l.data_entrega >= ?'; $args[]=$from; }
  if ($to){ $where[]='l.data_entrega <= ?'; $args[]=$to; }

  $sql="SELECT l.id, e.matricula, e.nome, COALESCE(o.name,'') op, l.quantidade, COALESCE(l.chamado_glpi,'') glpi,
               l.data_entrega, l.due_date, l.status
        FROM loans l
        JOIN employees e ON e.id=l.employee_id
        LEFT JOIN operations o ON o.id=l.operation_id";
  if ($where) $sql.=' WHERE '.implode(' AND ',$where);
  $sql.=' ORDER BY l.id DESC LIMIT 50000';
  $st=$pdo->prepare($sql); $st->execute($args);

  fputcsv($out, ['ID','Matrícula','Nome','Operação','Qtd','GLPI','Entrega','Prev. Devolução','Status']);
  while ($r=$st->fetch(PDO::FETCH_ASSOC)) {
    fputcsv($out, [
      $r['id'], $r['matricula'], $r['nome'], $r['op'], $r['quantidade'], $r['glpi'],
      $r['data_entrega'] ? date('d/m/Y', strtotime($r['data_entrega'])) : '',
      $r['due_date'] ? date('d/m/Y', strtotime($r['due_date'])) : '',
      t_status($r['status'])
    ]);
  }
  exit;
}

if ($tab==='equipment') {
  $st = get('status','');
  $type_id=(int)get('type_id','0');
  $loc_id=(int)get('location_id','0');
  $q = get('q','');

  $where=[]; $args=[];
  if ($st!==''){ $where[]='e.status=?'; $args[]=$st; }
  if ($type_id>0){ $where[]='e.type_id=?'; $args[]=$type_id; }
  if ($loc_id>0){ $where[]='e.location_id=?'; $args[]=$loc_id; }
  if ($q!==''){ $where[]='(e.patrimonio LIKE ? OR e.serial LIKE ? OR e.modelo LIKE ? OR e.tag_qr LIKE ?)'; array_push($args,"%$q%","%$q%","%$q%","%$q%"); }

  $sql="SELECT e.id, et.name tipo, COALESCE(e.modelo,'') modelo, COALESCE(e.patrimonio,'') pat,
               COALESCE(e.serial,'') sn, COALESCE(l.name,'') local_nome, e.status, COALESCE(e.tag_qr,'') tag
        FROM equipment e
        JOIN equipment_types et ON et.id=e.type_id
        LEFT JOIN locations l ON l.id=e.location_id";
  if ($where) $sql.=' WHERE '.implode(' AND ',$where);
  $sql.=' ORDER BY e.id DESC LIMIT 50000';
  $st=$pdo->prepare($sql); $st->execute($args);

  fputcsv($out, ['ID','Tipo','Modelo','Patrimônio','Serial','Local','Status','TAG']);
  while ($r=$st->fetch(PDO::FETCH_ASSOC)) {
    fputcsv($out, [
      $r['id'], $r['tipo'], $r['modelo'], $r['pat'], $r['sn'], $r['local_nome'], t_status($r['status']), $r['tag']
    ]);
  }
  exit;
}

if ($tab==='returns') {
  $st = get('rr_status','');
  $over = get('over','');
  $q  = get('q','');
  $from = ymd(get('from',''));
  $to   = ymd(get('to',''));

  $where=[]; $args=[];
  if ($st!==''){ $where[]='rr.status=?'; $args[]=$st; }
  if ($q!==''){ $where[]='(e.matricula LIKE ? OR e.nome LIKE ?)'; array_push($args,"%$q%","%$q%"); }
  if ($from){ $where[]='rr.created_at >= ?'; $args[]=$from.' 00:00:00'; }
  if ($to){ $where[]='rr.created_at <= ?'; $args[]=$to.' 23:59:59'; }
  if ($over==='yes'){ $where[]="rr.status IN ('ABERTA','EM_ANDAMENTO') AND rr.deadline IS NOT NULL AND rr.deadline < CURDATE()"; }

  $sql="SELECT rr.id, e.matricula, e.nome, rr.status, rr.deadline, rr.created_at, rr.updated_at,
               (SELECT COUNT(*) FROM return_request_items i WHERE i.request_id=rr.id) AS itens
        FROM return_requests rr
        JOIN employees e ON e.id=rr.employee_id";
  if ($where) $sql.=' WHERE '.implode(' AND ',$where);
  $sql.=' ORDER BY rr.id DESC LIMIT 50000';
  $st=$pdo->prepare($sql); $st->execute($args);

  fputcsv($out, ['ID','Matrícula','Nome','Status','Prazo','Abertura','Atualização','Itens']);
  while ($r=$st->fetch(PDO::FETCH_ASSOC)) {
    fputcsv($out, [
      $r['id'], $r['matricula'], $r['nome'], t_rr_status($r['status']),
      $r['deadline'] ? date('d/m/Y', strtotime($r['deadline'])) : '',
      $r['created_at'] ? date('d/m/Y H:i', strtotime($r['created_at'])) : '',
      $r['updated_at'] ? date('d/m/Y H:i', strtotime($r['updated_at'])) : '',
      $r['itens']
    ]);
  }
  exit;
}

if ($tab==='logs') {
  $action = get('action','');
  $user_id= (int)get('user_id','0');
  $eq_id  = (int)get('equipment_id','0');
  $q      = get('q','');
  $from   = ymd(get('from',''));
  $to     = ymd(get('to',''));

  $where=[]; $args=[];
  if ($action!==''){ $where[]='l.action=?'; $args[]=$action; }
  if ($user_id>0){ $where[]='l.user_id=?'; $args[]=$user_id; }
  if ($eq_id>0){ $where[]='l.equipment_id=?'; $args[]=$eq_id; }
  if ($from){ $where[]='l.created_at >= ?'; $args[]=$from.' 00:00:00'; }
  if ($to){ $where[]='l.created_at <= ?'; $args[]=$to.' 23:59:59'; }
  if ($q!==''){ $where[]='(u.name LIKE ? OR l.notes LIKE ?)'; array_push($args,"%$q%","%$q%"); }

  $sql="SELECT l.created_at, l.action, COALESCE(l.from_status,'') fs, COALESCE(l.to_status,'') ts,
               COALESCE(u.name,'') user_name, lo.id AS loan_num,
               COALESCE(em.matricula,'') AS matricula, COALESCE(em.nome,'') AS emp_nome, COALESCE(l.notes,'') notes
        FROM equipment_log l
        LEFT JOIN users u ON u.id=l.user_id
        LEFT JOIN loans lo ON lo.id=l.loan_id
        LEFT JOIN employees em ON em.id=lo.employee_id";
  if ($where) $sql.=' WHERE '.implode(' AND ',$where);
  $sql.=' ORDER BY l.created_at DESC, l.id DESC LIMIT 50000';
  $st=$pdo->prepare($sql); $st->execute($args);

  fputcsv($out, ['Quando','Ação','De','Para','Por','Empréstimo','Matrícula','Nome','Notas']);
  while ($r=$st->fetch(PDO::FETCH_ASSOC)) {
    fputcsv($out, [
      $r['created_at'] ? date('d/m/Y H:i', strtotime($r['created_at'])) : '',
      t_action($r['action']),
      $r['fs'] ? t_status($r['fs']) : '',
      $r['ts'] ? t_status($r['ts']) : '',
      $r['user_name'],
      $r['loan_num'] ? '#'.$r['loan_num'] : '',
      $r['matricula'], $r['emp_nome'], $r['notes']
    ]);
  }
  exit;
}

// fallback
fputcsv($out, ['Sem dados']);

