<?php
ob_clean();
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/db.php';
require_once __DIR__ . '/../vendor/autoload.php';

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


use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

require_role(['admin', 'superadmin']);

$user_id = $_SESSION['uid'] ?? 0;
$account_ids = $_POST['account_ids'] ?? [];
$clienteSeleccionado = $_POST['client_id'] ?? 'all';
$platformSeleccionada = $_POST['platform_id'] ?? 'todas';
$filtroEstado = $_POST['filter_expired'] ?? 'todas';

if (empty($account_ids)) {
    header("Content-Type: text/plain");
    die('Debe seleccionar al menos una cuenta.');
}

$account_ids = array_map('intval', $account_ids);
$placeholders = implode(',', array_fill(0, count($account_ids), '?'));

$params = [];
$sqlWhere = "WHERE 1=1"; // condición base para concatenar filtros

if ($clienteSeleccionado !== '' && $clienteSeleccionado !== 'all') {
    $sqlWhere .= " AND a.client_id = ? ";
    $params[] = $clienteSeleccionado;
}

if ($platformSeleccionada !== '' && $platformSeleccionada !== 'todas') {
    $sqlWhere .= " AND a.platform_id = ? ";
    $params[] = $platformSeleccionada;
}

if ($filtroEstado === 'vencidas') {
    $sqlWhere .= " AND a.expiration_date < NOW() ";
} elseif ($filtroEstado === 'activas') {
    $sqlWhere .= " AND a.expiration_date >= NOW() ";
}

$sqlWhere .= " AND a.id IN ($placeholders) ";
$params = array_merge($params, $account_ids);

$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);
$accounts = $stmt->fetchAll();

if (!$accounts) {
    header("Content-Type: text/plain");
    die('No se encontraron cuentas válidas para el reporte.');
}

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Reporte de Cuentas');

// Encabezados con negrita
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Cliente');
$sheet->setCellValue('C1', 'Plataforma');
$sheet->setCellValue('D1', 'Correo');
$sheet->setCellValue('E1', 'Fecha Expiración');
$sheet->setCellValue('F1', 'Precio');

$sheet->getStyle('A1:F1')->getFont()->setBold(true);

$rowNum = 2;
$totalPrecio = 0;

// Formato para columnas específicas
$sheet->getColumnDimension('A')->setWidth(10);
$sheet->getColumnDimension('B')->setWidth(25);
$sheet->getColumnDimension('C')->setWidth(25);
$sheet->getColumnDimension('D')->setWidth(30);
$sheet->getColumnDimension('E')->setWidth(18);
$sheet->getColumnDimension('F')->setWidth(12);
$sheet->getStyle('F')->getNumberFormat()->setFormatCode('#,##0.00');

foreach ($accounts as $acc) {
    $sheet->setCellValue("A{$rowNum}", $acc['id']);
    $sheet->setCellValue("B{$rowNum}", $acc['client_name']);
    $sheet->setCellValue("C{$rowNum}", $acc['platform_name']);
    $sheet->setCellValue("D{$rowNum}", $acc['email']);
    $sheet->setCellValue("E{$rowNum}", formatDateForDisplay($acc['expiration_date']));
    $sheet->setCellValue("F{$rowNum}", $acc['price']);
    $totalPrecio += $acc['price'];
    $rowNum++;
}

// Total con negrita y relleno de celda para destacar
$sheet->setCellValue("E{$rowNum}", 'TOTAL');
$sheet->setCellValue("F{$rowNum}", $totalPrecio);
$sheet->getStyle("E{$rowNum}:F{$rowNum}")->getFont()->setBold(true);
$sheet->getStyle("E{$rowNum}:F{$rowNum}")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
$sheet->getStyle("E{$rowNum}:F{$rowNum}")->getFill()->getStartColor()->setARGB('FFFFE599');

$writer = new Xlsx($spreadsheet);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="reporte_cuentas.xlsx"');
header('Cache-Control: max-age=0');

$writer->save('php://output');
exit;
