<?php
session_start();
$title = 'Generar reportes por cliente y plataforma';
require_once __DIR__ . '/../templates/header.php';
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/db.php';
require_once __DIR__ . '/../includes/helpers.php';
require_role(['admin', 'superadmin']);
$user_id = $_SESSION['uid'] ?? 0;

function formatDateForDisplay($dateString) {
    if ($dateString === null || $dateString === '') return '';
    $dateObj = DateTime::createFromFormat('Y-m-d', $dateString);
    return $dateObj ? $dateObj->format('d-m-Y') : $dateString;
}


// Obtener clientes y plataformas disponibles
$clientsStmt = pdo()->prepare('SELECT id, name, email FROM users WHERE role = :role AND admin_id = :admin_id ORDER BY name');
$clientsStmt->execute(['role' => 'client', 'admin_id' => $user_id]);
$clients = $clientsStmt->fetchAll();

$platsStmt = pdo()->prepare('SELECT id, name FROM platforms ORDER BY name');
$platsStmt->execute();
$plats = $platsStmt->fetchAll();

$clienteSeleccionado = $_POST['client_id'] ?? 'all'; // default a todos los clientes
$platformSeleccionada = $_POST['platform_id'] ?? 'todas';
$filtroEstado = $_POST['filter_expired'] ?? 'todas';
$cuentasSeleccionadas = $_POST['account_ids'] ?? [];
$totalPrecio = 0.0;
$cuentasReporte = [];
$reporteGenerado = false;
$accion = '';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_POST['generate_report'])) $accion = 'generate_report';
    else $accion = 'load_accounts';
}

function esPlataformaTodas($val) {
    return $val === '' || $val === 'todas';
}

// Construir cláusula WHERE y parámetros dinámicamente
$params = [];
$sqlWhere = "WHERE 1=1";  // condición base para concatenar filtros

// Filtro por cliente sólo si no es 'all'
if ($clienteSeleccionado !== '' && $clienteSeleccionado !== 'all') {
    $sqlWhere .= " AND a.client_id = ?";
    $params[] = $clienteSeleccionado;
}

// Filtro por plataforma si es diferente a 'todas'
if (!esPlataformaTodas($platformSeleccionada)) {
    $sqlWhere .= " AND a.platform_id = ?";
    $params[] = $platformSeleccionada;
}

// Filtro por estado de cuenta
if ($filtroEstado === 'vencidas') {
    $sqlWhere .= " AND a.expiration_date < NOW()";
} elseif ($filtroEstado === 'activas') {
    $sqlWhere .= " AND a.expiration_date >= NOW()";
}

// Filtro por IDs de cuentas seleccionadas
if (!empty($cuentasSeleccionadas)) {
    $account_ids = array_map('intval', $cuentasSeleccionadas);
    $placeholders = implode(',', array_fill(0, count($account_ids),'?'));
    $sqlWhere .= " AND a.id IN ($placeholders)";
    $params = array_merge($params, $account_ids);
}

// Consulta principal
$sql = "
    SELECT a.id, a.email, a.price, a.expiration_date, u.name AS client_name, p.name AS platform_name
    FROM assigned_accounts a
    INNER JOIN users u ON a.client_id = u.id
    INNER JOIN platforms p ON a.platform_id = p.id
    $sqlWhere
    ORDER BY u.name, p.name, a.email
";

$stmt = pdo()->prepare($sql);
$stmt->execute($params);
$cuentasReporte = $stmt->fetchAll();

foreach ($cuentasReporte as $cuenta) {
    $totalPrecio += $cuenta['price'];
}

if ($accion === 'generate_report') {
    $reporteGenerado = true;
}

// Para mostrar cuentas en la tabla de selección
$accounts = $cuentasReporte;

?>
<!DOCTYPE html>
<html lang="es">
<head>
<meta charset="UTF-8" />
<title><?= e($title) ?></title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet" />
</head>
<body>
<div class="container py-4">
<h3><?= e($title) ?></h3>
<form id="report-form" method="post" action="">
  <input type="hidden" name="csrf" value="<?= e(csrf_token()) ?>">
  <div class="mb-3">
    <label for="client-select" class="form-label">Cliente: <span class="text-danger">*</span></label>
    <select id="client-select" name="client_id" class="form-select" required>
      <option value="all" <?= ($clienteSeleccionado === 'all') ? 'selected' : '' ?>>Todos los clientes</option>
      <?php foreach ($clients as $c): ?>
        <option value="<?= e($c['id']) ?>" <?= ($clienteSeleccionado == $c['id']) ? 'selected' : '' ?>>
          <?= e($c['name']) ?> (<?= e($c['email']) ?>)
        </option>
      <?php endforeach; ?>
    </select>
  </div>
  <div class="mb-3">
    <label for="platform-select" class="form-label">Filtrar por plataforma (opcional):</label>
    <select id="platform-select" name="platform_id" class="form-select">
      <option value="todas" <?= ($platformSeleccionada === 'todas' || $platformSeleccionada === '') ? 'selected' : '' ?>>Todas</option>
      <?php foreach ($plats as $plat): ?>
        <option value="<?= e($plat['id']) ?>" <?= ($platformSeleccionada == $plat['id']) ? 'selected' : '' ?>>
          <?= e($plat['name']) ?>
        </option>
      <?php endforeach; ?>
    </select>
  </div>
  <div class="mb-3">
    <label for="filter-expired" class="form-label">Filtrar por estado de cuenta:</label>
    <select id="filter-expired" name="filter_expired" class="form-select">
      <option value="todas" <?= ($filtroEstado === 'todas') ? 'selected' : '' ?>>Todas</option>
      <option value="vencidas" <?= ($filtroEstado === 'vencidas') ? 'selected' : '' ?>>Solo Vencidas</option>
      <option value="activas" <?= ($filtroEstado === 'activas') ? 'selected' : '' ?>>Solo Activas</option>
    </select>
  </div>
  <?php if ($accounts): ?>
    <div class="table-responsive mb-3">
      <table class="table table-bordered table-hover align-middle">
        <thead>
          <tr>
            <th><input type="checkbox" id="select-all"></th>
            <th>Plataforma</th>
            <th>Correo</th>
            <th>Precio</th>
            <th>Fecha Expiración</th>
            <th>Estado</th>
          </tr>
        </thead>
        <tbody>
          <?php foreach ($accounts as $acc):
            $expDate = $acc['expiration_date'];
            $esVencida = (strtotime($expDate) < time());
            $estado = $esVencida ? 'Vencida' : 'Activa';
          ?>
            <tr>
              <td><input type="checkbox" class="account-checkbox" name="account_ids[]" value="<?= e($acc['id']) ?>" data-price="<?= e($acc['price']) ?>"></td>
              <td><?= e($acc['platform_name']) ?></td>
              <td><?= e($acc['email']) ?></td>
              <td>$<?= number_format($acc['price'], 2) ?></td>
	      <td><?= e(formatDateForDisplay($expDate)) ?></td>
              <td><span class="<?= $esVencida ? 'text-danger' : 'text-success' ?>"><?= $estado ?></span></td>
            </tr>
          <?php endforeach; ?>
        </tbody>
      </table>
    </div>
    <p>
      <strong>Cuentas seleccionadas:</strong> <span id="count-selected">0</span><br>
      <strong>Precio total:</strong> $<span id="price-total">0.00</span>
    </p>
    <button type="submit" name="generate_report" class="btn btn-primary">Generar Reporte</button>
    <button type="button" id="exportpdfbtn" class="btn btn-danger">Exportar PDF</button>
    <button type="button" id="exportexcelbtn" class="btn btn-success">Exportar Excel</button>
    <button type="button" id="exportcsvbtn" class="btn btn-warning">Exportar CSV</button>
  <?php else: ?>
    <p>No hay cuentas para este cliente/plataforma.</p>
  <?php endif; ?>
</form>
<?php if ($reporteGenerado): ?>
  <h4>Reporte generado</h4>
  <div class="table-responsive">
    <table class="table table-bordered table-hover align-middle">
      <thead>
        <tr>
          <th>ID</th><th>Cliente</th><th>Plataforma</th><th>Correo</th><th>Precio</th><th>Fecha Expiración</th><th>Estado</th>
        </tr>
      </thead>
      <tbody>
        <?php foreach ($cuentasReporte as $cuenta):
          $exp = $cuenta['expiration_date'];
          $estado = (strtotime($exp) < time()) ? 'Vencida' : 'Activa';
        ?>
          <tr>
            <td><?= e($cuenta['id']) ?></td>
            <td><?= e($cuenta['client_name']) ?></td>
            <td><?= e($cuenta['platform_name']) ?></td>
            <td><?= e($cuenta['email']) ?></td>
            <td>$<?= number_format($cuenta['price'], 2) ?></td>
	    <td><?= e(formatDateForDisplay($exp)) ?></td>
            <td><?= $estado ?></td>
          </tr>
        <?php endforeach; ?>
        <tr>
          <td colspan="4" class="text-end"><strong>Total:</strong></td>
          <td><strong>$<?= number_format($totalPrecio, 2) ?></strong></td>
          <td></td>
          <td></td>
        </tr>
      </tbody>
    </table>
  </div>
<?php endif; ?>
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
<script>
document.getElementById('client-select').addEventListener('change', () => document.getElementById('report-form').submit());
document.getElementById('platform-select').addEventListener('change', () => document.getElementById('report-form').submit());
document.getElementById('filter-expired').addEventListener('change', () => document.getElementById('report-form').submit());
document.getElementById('select-all')?.addEventListener('change', e => {
  document.querySelectorAll('.account-checkbox').forEach(chk => chk.checked = e.target.checked);
  actualizarResumen();
});
document.querySelectorAll('.account-checkbox').forEach(chk => chk.addEventListener('change', actualizarResumen));
function actualizarResumen() {
  const checkboxes = document.querySelectorAll('.account-checkbox:checked');
  let count = checkboxes.length, total = 0;
  checkboxes.forEach(chk => total += parseFloat(chk.getAttribute('data-price')) || 0);
  document.getElementById('count-selected').textContent = count;
  document.getElementById('price-total').textContent = total.toFixed(2);
}
actualizarResumen();
function exportarReporte(tipo) {
  const form = document.getElementById('report-form');
  const urlMap = {
    pdf: 'reportes_pdf.php',
    excel: 'reportes_excel.php',
    csv: 'reportes_download.php'
  };
  
  const formData = new FormData(form);
  formData.append('export_type', tipo);
  fetch(urlMap[tipo], {
    method: 'POST',
    body: formData,
  })
  .then(response => {
    if (!response.ok) throw new Error('Error en red');
    return response.blob();
  })
  .then(blob => {
    if (blob.size === 0) throw new Error('Archivo vacío o sin datos.');
    const filename = {
      pdf: 'reporte_cuentas.pdf',
      excel: 'reporte_cuentas.xlsx',
      csv: 'reporte_cuentas.csv'
    }[tipo] || 'reporte';
    const link = document.createElement('a');
    link.href = window.URL.createObjectURL(blob);
    link.download = filename;
    document.body.appendChild(link);
    link.click();
    link.remove();
  })
  .catch(e => {
    alert('Error al generar el reporte: ' + e.message);
  });
}
document.getElementById('exportpdfbtn').addEventListener('click', e => {
  e.preventDefault();
  if (document.querySelectorAll('.account-checkbox:checked').length === 0) {
    alert('Debe seleccionar al menos una cuenta para exportar.');
    return;
  }
  exportarReporte('pdf');
});
document.getElementById('exportexcelbtn').addEventListener('click', e => {
  e.preventDefault();
  if (document.querySelectorAll('.account-checkbox:checked').length === 0) {
    alert('Debe seleccionar al menos una cuenta para exportar.');
    return;
  }
  exportarReporte('excel');
});
document.getElementById('exportcsvbtn').addEventListener('click', e => {
  e.preventDefault();
  if (document.querySelectorAll('.account-checkbox:checked').length === 0) {
    alert('Debe seleccionar al menos una cuenta para exportar.');
    return;
  }
  exportarReporte('csv');
});
</script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
<?php require_once __DIR__ . '/../templates/footer.php'; ?>
</body>
</html>
