<?php
/***********************
 * Configuração DB
 ***********************/
$DB_HOST = "149.56.34.221";
$DB_USER = "sysadmin";
$DB_PASS = "Brasil@2023#";
$DB_NAME = "SGV";

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

function db() {
    global $DB_HOST, $DB_USER, $DB_PASS, $DB_NAME;
    static $conn = null;
    if ($conn === null) {
        $conn = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
        $conn->set_charset("utf8mb4");
    }
    return $conn;
}

/***********************
 * Rotas AJAX
 ***********************/
if (isset($_GET['action'])) {
    header('Content-Type: application/json; charset=utf-8');

    try {
        if ($_GET['action'] === 'animals') {
            // Busca animais pelo CPF (exato). Se quiser aceitar prefixo, troque para LIKE CONCAT(?, '%')
            $cpf = trim($_GET['cpf'] ?? '');
            if ($cpf === '') { echo json_encode([]); exit; }

            $sql = "
                SELECT ani.codigo, ani.nome_ani
                FROM cli
                JOIN animais ani ON ani.id = cli.CPF
                WHERE cli.CPF = ?
                ORDER BY ani.nome_ani
            ";
            $stmt = db()->prepare($sql);
            $stmt->bind_param('s', $cpf);
            $stmt->execute();
            $res = $stmt->get_result();
            $out = [];
            while ($row = $res->fetch_assoc()) {
                $out[] = $row;
            }
            echo json_encode($out);
            exit;
        }

        if ($_GET['action'] === 'query') {
            // Executa a sua query (UNION ALL) filtrando por CPF e por COD_ANIMAL selecionado
            $cpf = trim($_GET['cpf'] ?? '');
            $codAnimal = trim($_GET['cod_animal'] ?? '');

            if ($cpf === '' || $codAnimal === '') {
                echo json_encode(['error' => 'CPF e Animal são obrigatórios.']);
                exit;
            }

            // A query abaixo é a SUA, apenas adicionei o filtro do animal (ani.codigo = ?)
            $sql = "
            -- Parte 1: dados de procedimentos
            SELECT 
                cli.nome AS Tutor_Nome, 
                cli.CPF AS Tutor_CPF,
                ani.codigo AS Cod_Animal, 
                ani.nome_ani AS Nome_Animal, 
                lp.data_proced AS Data_Abertura, 
                lp.vet AS Vet_Responsavel,
                al.id_ana AS ID_Anaminese, 
                al.id_campo AS ID_CAMPO, 
                al.valor AS Anamnese,
                'Procedimento' AS Origem,
                NULL AS Retorno_Descricao,
                NULL AS Retorno_Data,
                NULL AS Retorno_Vet
            FROM cli
            JOIN animais AS ani        ON ani.id = cli.CPF
            JOIN lanc_proced AS lp     ON ani.codigo = lp.codigo_ani
            JOIN anamnese_lancamentos AS al ON lp.codigo = al.id_ana
            WHERE cli.CPF = ? AND ani.codigo = ?

            UNION ALL

            -- Parte 2: dados de retornos
            SELECT 
                cli.nome AS Tutor_Nome, 
                cli.CPF AS Tutor_CPF,
                ani.codigo AS Cod_Animal, 
                ani.nome_ani AS Nome_Animal, 
                r.DATA AS Data_Abertura, 
                r.VETERINARIO AS Vet_Responsavel,
                NULL AS ID_Anaminese, 
                NULL AS ID_CAMPO, 
                NULL AS Anaminese,
                'Retorno' AS Origem,
                r.DESCRICAO AS Retorno_Descricao,
                r.DATA AS Retorno_Data,
                r.VETERINARIO AS Retorno_Vet
            FROM cli
            JOIN animais AS ani ON ani.id = cli.CPF
            JOIN retornos AS r  ON ani.codigo = r.COD_ANIMAL
            WHERE cli.CPF = ? AND ani.codigo = ?
            ORDER BY Data_Abertura, Origem
            ";

            $stmt = db()->prepare($sql);
            // bind 4 params: CPF, COD_ANIMAL, CPF, COD_ANIMAL
            $stmt->bind_param('siss', $cpf, $codAnimal, $cpf, $codAnimal);
            $stmt->execute();
            $res = $stmt->get_result();

            $rows = [];
            while ($row = $res->fetch_assoc()) {
                // Proteção básica contra XSS (se for exibir como HTML)
                foreach ($row as $k => $v) {
                    if (is_string($v)) $row[$k] = $v;
                }
                $rows[] = $row;
            }
            echo json_encode(['data' => $rows]);
            exit;
        }

        echo json_encode(['error' => 'Ação inválida.']);
        exit;

    } catch (Throwable $e) {
        http_response_code(500);
        echo json_encode(['error' => $e->getMessage()]);
        exit;
    }
}
?>
<!doctype html>
<html lang="pt-br">
<head>
<meta charset="utf-8">
<title>Consulta de Procedimentos e Retornos</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
  body { font-family: system-ui, Arial, sans-serif; background:#f6f7fb; margin:0; padding:24px; color:#222; }
  h1 { margin:0 0 12px; font-size:20px; }
  .card { background:#fff; border:1px solid #e6e8ee; border-radius:14px; padding:18px; box-shadow:0 1px 2px rgba(16,24,40,.05); max-width:1100px; }
  .row { display:flex; gap:12px; flex-wrap:wrap; }
  .field { display:flex; flex-direction:column; gap:6px; min-width:240px; }
  input, select { padding:10px 12px; border:1px solid #cfd6e4; border-radius:10px; font-size:14px; background:#fff; }
  input:focus, select:focus { outline:2px solid #7da6ff33; border-color:#7da6ff; }
  button { padding:10px 16px; border:0; border-radius:10px; background:#2f6df6; color:#fff; font-weight:600; cursor:pointer; }
  button:disabled { background:#a6b6e6; cursor:not-allowed; }
  .help { color:#666; font-size:12px; }
  table { width:100%; border-collapse:collapse; margin-top:16px; font-size:13px; background:#fff; border:1px solid #e6e8ee; border-radius:12px; overflow:hidden; }
  thead th { background:#f0f3fa; text-align:left; padding:10px; border-bottom:1px solid #e6e8ee; }
  tbody td { padding:10px; border-bottom:1px solid #f2f4f8; }
  tbody tr:nth-child(even) td { background:#fafbff; }
  .badge { display:inline-block; padding:2px 8px; border-radius:999px; font-size:12px; background:#eef2ff; color:#2f6df6; }
  .muted { color:#636c80; }
</style>
</head>
<body>
  <div class="card">
    <h1>Procedimentos + Retornos (por CPF e Animal)</h1>

    <div class="row">
      <div class="field">
        <label for="cpf">CPF do Tutor</label>
        <input id="cpf" type="text" placeholder="Digite o CPF e pressione TAB">
        <div class="help">Ao sair do campo, buscarei os animais e preencherei o listbox.</div>
      </div>

      <div class="field" style="min-width:320px;">
        <label for="animal">Animal</label>
        <select id="animal" disabled>
          <option value="">— selecione —</option>
        </select>
        <div class="help">Selecione um animal para listar os registros.</div>
      </div>

      <div class="field" style="justify-content:flex-end;">
        <label>&nbsp;</label>
        <button id="btnBuscar" disabled>Listar</button>
      </div>
    </div>

    <div id="alert" class="help" style="margin-top:10px;"></div>

    <div id="results"></div>
  </div>

<script>
const elCPF     = document.getElementById('cpf');
const elAnimal  = document.getElementById('animal');
const elBuscar  = document.getElementById('btnBuscar');
const elAlert   = document.getElementById('alert');
const elResults = document.getElementById('results');

function escapeHTML(str) {
  return (str ?? '').toString()
    .replaceAll('&','&amp;')
    .replaceAll('<','&lt;')
    .replaceAll('>','&gt;')
    .replaceAll('"','&quot;')
    .replaceAll("'",'&#39;');
}

async function loadAnimalsByCPF(cpf) {
  elAlert.textContent = '';
  elAnimal.innerHTML = '<option value="">— selecione —</option>';
  elAnimal.disabled = true;
  elBuscar.disabled = true;

  if (!cpf) return;

  const url = new URL(window.location.href);
  url.searchParams.set('action','animals');
  url.searchParams.set('cpf', cpf);

  const res = await fetch(url.toString(), { headers: { 'Accept':'application/json' } });
  const data = await res.json();

  if (Array.isArray(data) && data.length) {
    for (const a of data) {
      const opt = document.createElement('option');
      opt.value = a.codigo;
      opt.textContent = `${a.codigo} — ${a.nome_ani}`;
      elAnimal.appendChild(opt);
    }
    elAnimal.disabled = false;
    elBuscar.disabled = false;
  } else {
    elAlert.textContent = 'Nenhum animal encontrado para este CPF.';
  }
}

async function runQuery() {
  elResults.innerHTML = '';
  elAlert.textContent = '';

  const cpf = elCPF.value.trim();
  const codAnimal = elAnimal.value.trim();
  if (!cpf || !codAnimal) {
    elAlert.textContent = 'Informe o CPF e selecione um animal.';
    return;
  }

  const url = new URL(window.location.href);
  url.searchParams.set('action','query');
  url.searchParams.set('cpf', cpf);
  url.searchParams.set('cod_animal', codAnimal);

  const res = await fetch(url.toString(), { headers: { 'Accept':'application/json' } });
  const payload = await res.json();

  if (payload.error) {
    elAlert.textContent = payload.error;
    return;
  }
  const rows = payload.data || [];

  if (!rows.length) {
    elResults.innerHTML = '<p class="muted" style="margin-top:16px;">Nenhum registro encontrado.</p>';
    return;
  }

  let html = '<table><thead><tr>';
  const cols = [
    'Tutor_Nome','Tutor_CPF','Cod_Animal','Nome_Animal',
    'Data_Abertura','Vet_Responsavel','ID_Anaminese','ID_CAMPO','Anamnese',
    'Origem','Retorno_Descricao','Retorno_Data','Retorno_Vet'
  ];
  for (const c of cols) html += `<th>${escapeHTML(c)}</th>`;
  html += '</tr></thead><tbody>';

  for (const r of rows) {
    html += '<tr>';
    for (const c of cols) {
      let v = r[c];
      if (c === 'Origem' && v) v = `<span class="badge">${escapeHTML(v)}</span>`;
      html += `<td>${v == null ? '' : v}</td>`;
    }
    html += '</tr>';
  }
  html += '</tbody></table>';
  elResults.innerHTML = html;
}

// dispara quando sai do campo (TAB ou clique fora)
elCPF.addEventListener('blur', () => loadAnimalsByCPF(elCPF.value.trim()));
// permite Enter no CPF para já buscar animais
elCPF.addEventListener('keydown', (e) => { if (e.key === 'Enter') { e.preventDefault(); loadAnimalsByCPF(elCPF.value.trim()); } });
// habilita o botão ao escolher animal
elAnimal.addEventListener('change', () => { elBuscar.disabled = !elAnimal.value; });
// consulta ao clicar
elBuscar.addEventListener('click', runQuery);
</script>
</body>
</html>