<?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 = normalizeSearchTerm(isset($_GET['q']) ? $_GET['q'] : '');
$campoFiltro = (isset($_GET['campo']) && is_numeric($_GET['campo'])) ? (int) $_GET['campo'] : 0;
$dataInicioRaw = isset($_GET['dataInicio']) ? (string) $_GET['dataInicio'] : '';
$dataFimRaw = isset($_GET['dataFim']) ? (string) $_GET['dataFim'] : '';
$dataInicio = normalizeDateInput($dataInicioRaw);
$dataFim = normalizeDateInput($dataFimRaw);

if ($dataInicioRaw !== '' && $dataInicio === null) {
    die('Data inicial inválida.');
}
if ($dataFimRaw !== '' && $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(array('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(array('id' => $formId));
$campos = $stmt->fetchAll(PDO::FETCH_ASSOC);
$campoIdsValidos = array();
$fieldTypeMap = array();
foreach ((array) $campos as $campo) {
    $campoId = (int) $campo['id'];
    $campoIdsValidos[] = $campoId;
    $fieldTypeMap[$campoId] = (string) (isset($campo['tipo']) ? $campo['tipo'] : '');
}

if ($campoFiltro > 0 && !in_array($campoFiltro, $campoIdsValidos, true)) {
    $campoFiltro = 0;
}

$where = ' WHERE r.formulario_id = :fid ';
$params = array('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 = array();
foreach ((array) $respostas as $resposta) {
    $respostasPorId[(int) $resposta['id']] = $resposta;
}

$valoresMap = array();
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) {
        $respostaId = (int) $linha['resposta_id'];
        $campoId = (int) $linha['campo_id'];
        $valor = (string) (isset($linha['valor']) ? $linha['valor'] : '');

        if (isset($fieldTypeMap[$campoId]) && in_array($fieldTypeMap[$campoId], array('image', 'file'), true)) {
            $valor = storedFileDisplayName($valor);
        }

        $valoresMap[$respostaId][$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 ((array) $campos as $campo) {
    echo '<th>' . htmlspecialchars((string) $campo['label'], ENT_QUOTES, 'UTF-8') . '</th>';
}
echo '</tr>';

foreach ((array) $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 ((array) $campos as $campo) {
        $campoId = (int) $campo['id'];
        $valor = isset($valoresMap[$respostaId][$campoId]) ? $valoresMap[$respostaId][$campoId] : '';
        echo '<td style="white-space:pre-wrap">' . nl2br(htmlspecialchars((string) $valor, ENT_QUOTES, 'UTF-8')) . '</td>';
    }
    echo '</tr>';
}

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