<?php
include 'auth.php';
include 'db.php';
include 'formulario_utils.php';

$formId = isset($_GET['id']) ? (int) $_GET['id'] : 0;
if ($formId <= 0) {
    die('Formulário inválido!');
}

$q = preg_replace('/\s+/u', ' ', trim((string) ($_GET['q'] ?? ''))) ?? '';
$campoFiltro = isset($_GET['campo']) && is_numeric($_GET['campo']) ? (int) $_GET['campo'] : 0;
$dataInicio = normalizeDateInput((string) ($_GET['dataInicio'] ?? ''));
$dataFim = normalizeDateInput((string) ($_GET['dataFim'] ?? ''));

if ((string) ($_GET['dataInicio'] ?? '') !== '' && $dataInicio === null) {
    die('Data inicial inválida.');
}
if ((string) ($_GET['dataFim'] ?? '') !== '' && $dataFim === null) {
    die('Data final inválida.');
}
if ($dataInicio !== null && $dataFim !== null && $dataInicio > $dataFim) {
    die('A data inicial não pode ser maior que a data final.');
}

$stmtForm = $pdo->prepare('SELECT nome FROM formularios WHERE id = :id');
$stmtForm->execute(['id' => $formId]);
$formulario = $stmtForm->fetch(PDO::FETCH_ASSOC);
if (!$formulario) {
    die('Formulário não encontrado.');
}

$stmt = $pdo->prepare("SELECT id, label, tipo FROM formulario_campos WHERE formulario_id = :id AND COALESCE(tipo, '') <> 'warning' AND COALESCE(hidden, 0) = 0 ORDER BY id ASC");
$stmt->execute(['id' => $formId]);
$campos = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
$campoIdsValidos = array_map(static fn(array $campo): int => (int) $campo['id'], $campos);
if ($campoFiltro > 0 && !in_array($campoFiltro, $campoIdsValidos, true)) {
    $campoFiltro = 0;
}

$where = ' WHERE r.formulario_id = :fid ';
$params = ['fid' => $formId];

if ($dataInicio !== null) {
    $where .= ' AND r.created_at >= :dataInicio ';
    $params['dataInicio'] = $dataInicio . ' 00:00:00';
}
if ($dataFim !== null) {
    $where .= ' AND r.created_at <= :dataFim ';
    $params['dataFim'] = $dataFim . ' 23:59:59';
}
if ($q !== '') {
    if ($campoFiltro > 0) {
        $where .= " AND EXISTS (
            SELECT 1
            FROM formulario_respostas_campos frc
            INNER JOIN formulario_campos fc ON fc.id = frc.campo_id
            WHERE frc.resposta_id = r.id
              AND frc.campo_id = :campoFiltro
              AND COALESCE(fc.hidden, 0) = 0
              AND COALESCE(fc.tipo, '') <> 'warning'
              AND frc.valor LIKE :q ESCAPE '\\'
        ) ";
        $params['campoFiltro'] = $campoFiltro;
    } else {
        $where .= " AND EXISTS (
            SELECT 1
            FROM formulario_respostas_campos frc
            INNER JOIN formulario_campos fc ON fc.id = frc.campo_id
            WHERE frc.resposta_id = r.id
              AND COALESCE(fc.hidden, 0) = 0
              AND COALESCE(fc.tipo, '') <> 'warning'
              AND frc.valor LIKE :q ESCAPE '\\'
        ) ";
    }
    $params['q'] = '%' . escapeLike($q) . '%';
}

$stmt = $pdo->prepare("SELECT r.id, r.created_at FROM formulario_respostas r {$where} ORDER BY r.created_at DESC, r.id DESC");
$stmt->execute($params);
$respostas = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

$respostasPorId = [];
foreach ($respostas as $resposta) {
    $respostasPorId[(int) $resposta['id']] = $resposta;
}

$valoresMap = [];
if ($respostas !== []) {
    $respostaIds = array_keys($respostasPorId);
    $placeholders = implode(',', array_fill(0, count($respostaIds), '?'));
    $stmtValores = $pdo->prepare(
        "SELECT frc.resposta_id, frc.campo_id, frc.valor
         FROM formulario_respostas_campos frc
         INNER JOIN formulario_campos fc ON fc.id = frc.campo_id
         WHERE frc.resposta_id IN ({$placeholders})
           AND COALESCE(fc.hidden, 0) = 0
           AND COALESCE(fc.tipo, '') <> 'warning'"
    );
    $stmtValores->execute($respostaIds);

    foreach ($stmtValores->fetchAll(PDO::FETCH_ASSOC) as $linha) {
        $valor = (string) ($linha['valor'] ?? '');
        $campoId = (int) $linha['campo_id'];
        $tipoCampo = '';
        foreach ($campos as $campo) {
            if ((int) $campo['id'] === $campoId) {
                $tipoCampo = (string) ($campo['tipo'] ?? '');
                break;
            }
        }

        if (in_array($tipoCampo, ['image', 'file'], true)) {
            $valor = storedFileDisplayName($valor);
        }

        $valoresMap[(int) $linha['resposta_id']][$campoId] = $valor;
    }
}

$filenameBase = 'respostas_formulario_' . $formId . '_' . date('Ymd_His');
$filename = $filenameBase . '.xls';
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment; filename=' . $filename);
header('Cache-Control: max-age=0');

echo "\xEF\xBB\xBF";
echo '<html><head><meta charset="UTF-8"></head><body>';
echo '<table border="1">';
echo '<tr>';
echo '<th>ID</th><th>Data de Resposta</th>';
foreach ($campos as $campo) {
    echo '<th>' . htmlspecialchars((string) $campo['label'], ENT_QUOTES, 'UTF-8') . '</th>';
}
echo '</tr>';

foreach ($respostas as $resposta) {
    $respostaId = (int) $resposta['id'];
    echo '<tr>';
    echo '<td>' . $respostaId . '</td>';
    echo '<td>' . htmlspecialchars((string) $resposta['created_at'], ENT_QUOTES, 'UTF-8') . '</td>';
    foreach ($campos as $campo) {
        $valor = $valoresMap[$respostaId][(int) $campo['id']] ?? '';
        echo '<td style="white-space:pre-wrap">' . nl2br(htmlspecialchars((string) $valor, ENT_QUOTES, 'UTF-8')) . '</td>';
    }
    echo '</tr>';
}

echo '</table></body></html>';
exit;
