<?php
require_once '../auth.php';
require_once '../config.php';
checkLogin();

// --- 1. FILTERS & INPUTS ---
// --- 1. FILTERS & INPUTS ---
$assigned_ships = $_SESSION['assigned_ships'] ?? [];
$buque_filtro = $_GET['buque_id'] ?? null;

// Si no hay buque, buscar el primero disponible
if (!$buque_filtro && !empty($assigned_ships)) {
    $buque_filtro = $assigned_ships[0];
}

// Verify access
if (!$buque_filtro || !hasShipAccess($buque_filtro)) {
    die("Error: Acceso denegado o buque no especificado.");
}

$pdo = getDBConnection();

// --- 2. FETCH METADATA (Ship Info & Sensor Labels) ---

// A. Ship Details
$stmtShip = $pdo->prepare("SELECT name, max_temp, min_temp FROM ships WHERE ship_id = :id");
$stmtShip->execute([':id' => $buque_filtro]);
$shipData = $stmtShip->fetch(PDO::FETCH_ASSOC);

$shipName = $shipData ? $shipData['name'] : $buque_filtro;
$maxTemp = $shipData ? (float) $shipData['max_temp'] : 25.0;
$minTemp = $shipData ? (float) ($shipData['min_temp'] ?? 0.0) : 0.0;

// B. Sensor Labels & Active List
$sensorLabels = [];
$active_sensors_list = [];

try {
    $stmtL = $pdo->prepare("SELECT sensor_id, label FROM sensor_positions WHERE buque_id = :buque ORDER BY sensor_id ASC");
    $stmtL->execute([':buque' => $buque_filtro]);
    $active_sensors_list = $stmtL->fetchAll(PDO::FETCH_ASSOC);

    foreach ($active_sensors_list as $row) {
        $sensorLabels[(int) $row['sensor_id']] = !empty($row['label']) ? $row['label'] : "Sensor " . $row['sensor_id'];
    }
} catch (PDOException $e) {
}

// Fallback legacy (16 sensors)
if (empty($active_sensors_list)) {
    for ($i = 1; $i <= 16; $i++) {
        $active_sensors_list[] = ['sensor_id' => $i];
        $sensorLabels[$i] = "Sensor $i";
    }
}

$total_cols = 3 + count($active_sensors_list); // ID, Fecha, IP + Sensores

// --- 3. FETCH DATA ---
$sql = "SELECT * FROM mediciones WHERE buque_id = :buque";
$params = [':buque' => $buque_filtro];

if ($fecha_inicio) {
    if ($fecha_fin) {
        $sql .= " AND fecha BETWEEN :start AND :end";
        $params[':start'] = "$fecha_inicio 00:00:00";
        $params[':end'] = "$fecha_fin 23:59:59";
    } else {
        $sql .= " AND fecha >= :start";
        $params[':start'] = "$fecha_inicio 00:00:00";
    }
}

$sql .= " ORDER BY fecha DESC LIMIT 5000";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// --- 4. GENERATE EXCEL (HTML FORMAT) ---
$filename = "Reporte_" . str_replace(' ', '_', $shipName) . "_" . date('Ymd_His') . ".xls";

header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment; filename=' . $filename);
header('Pragma: no-cache');
header('Expires: 0');

echo "\xEF\xBB\xBF"; // UTF-8 BOM
?>
<!DOCTYPE html>
<html lang="es">

<head>
    <meta charset="UTF-8">
    <style>
        body {
            font-family: Arial, sans-serif;
            font-size: 12px;
        }

        table {
            border-collapse: collapse;
            width: 100%;
        }

        th,
        td {
            border: 1px solid #cccccc;
            padding: 8px;
            text-align: center;
            vertical-align: middle;
        }

        /* Headers */
        .header-main {
            background-color: #2c3e50;
            color: #ffffff;
            font-size: 16px;
            font-weight: bold;
            text-align: left;
        }

        .header-sub {
            background-color: #ecf0f1;
            color: #333333;
            font-weight: bold;
            text-align: left;
        }

        .col-header {
            background-color: #34495e;
            color: #ffffff;
            font-weight: bold;
        }

        /* Data Formatting */
        .date-col {
            mso-number-format: "dd\/mm\/yyyy hh\:mm";
            text-align: center;
        }

        /* Excel Date Format */
        .ip-col {
            text-align: left;
            color: #7f8c8d;
            font-size: 11px;
        }

        .temp-cell {
            mso-number-format: "0.00";
        }

        /* Alerts */
        .alert-high {
            background-color: #ffcccc;
            color: #c0392b;
            font-weight: bold;
        }

        .empty-cell {
            background-color: #ffffff;
        }
    </style>
</head>

<body>

    <table>
        <!-- METADATA HEADER -->
        <tr>
            <td colspan="<?php echo $total_cols; ?>" class="header-main">REPORTE DE MONITOREO - TECNOTRACK MARINE</td>
        </tr>
        <tr>
            <td colspan="<?php echo $total_cols; ?>" class="header-sub">
                <strong>Buque:</strong> <?php echo htmlspecialchars($shipName); ?> |
                <strong>Umbral Alerta:</strong> <?php echo number_format($maxTemp, 1); ?>°C
            </td>
        </tr>
        <tr>
            <td colspan="<?php echo $total_cols; ?>" class="header-sub">
                <strong>Rango de Fechas:</strong>
                <?php
                if ($fecha_inicio && $fecha_fin)
                    echo "Del " . date('d/m/Y H:i', strtotime($fecha_inicio)) . " al " . date('d/m/Y H:i', strtotime($fecha_fin));
                elseif ($fecha_inicio)
                    echo "Desde " . date('d/m/Y H:i', strtotime($fecha_inicio));
                else
                    echo "Últimos registros disponibles (General)";
                ?>
                | <strong>Generado el:</strong> <?php echo date('d/m/Y H:i'); ?>
            </td>
        </tr>
        <tr>
            <td colspan="<?php echo $total_cols; ?>" style="height: 10px; border: none;"></td>
        </tr>

        <!-- COLUMN HEADERS -->
        <tr>
            <th class="col-header">ID</th>
            <th class="col-header">FECHA REGISTRO</th>
            <th class="col-header">IP ORIGEN</th>
            <?php foreach ($active_sensors_list as $sensor):
                $id = $sensor['sensor_id'];
                $label = $sensorLabels[$id] ?? "Sensor $id";
                ?>
                <th class="col-header" style="min-width: 80px; vertical-align: top;">
                    Sensor <?php echo $id; ?><br>
                    <span style="font-size: 0.9em; font-weight: normal;"><?php echo htmlspecialchars($label); ?></span>
                </th>
            <?php endforeach; ?>
        </tr>

        <!-- DATA ROWS -->
        <?php foreach ($rows as $row): ?>
            <tr>
                <td><?php echo $row['id']; ?></td>
                <td class="date-col"><?php echo date('d/m/Y H:i', strtotime($row['fecha'])); ?></td>
                <td class="ip-col"><?php echo htmlspecialchars($row['ip'] ?? ''); ?></td>

                <?php foreach ($active_sensors_list as $sensor):
                    $i = $sensor['sensor_id'];
                    $val = $row["temp$i"] ?? null;
                    $numVal = is_numeric($val) ? (float) $val : null;
                    $class = "temp-cell";

                    // Conditional Formatting
                    if ($numVal !== null && ($numVal >= $maxTemp || $numVal < $minTemp)) {
                        $class .= " alert-high";
                    }
                    ?>
                    <td class="<?php echo $class; ?>">
                        <?php echo ($numVal !== null) ? number_format($numVal, 2, ',', '.') : ''; ?>
                    </td>
                <?php endforeach; ?>
            </tr>
        <?php endforeach; ?>

    </table>

</body>

</html>