<?php
require __DIR__.'/../../inc/bootstrap.php';

// Cabeceras de descarga
$fname = "backup_farmacia_".date('Ymd_His').".sql";
header('Content-Type: application/sql; charset=utf-8');
header("Content-Disposition: attachment; filename=\"$fname\"");

// Helper: volcar una tabla
function dump_table($pdo, $table) {
  echo "-- --------------------------------------------------\n";
  echo "-- Estructura de tabla: `$table`\n\n";
  // Estructura: usa SHOW CREATE TABLE
  $res = $pdo->query("SHOW CREATE TABLE `$table`")->fetch();
  echo "DROP TABLE IF EXISTS `$table`;\n";
  echo $res['Create Table'].";\n\n";

  echo "-- Datos de `$table`\n";
  $stmt = $pdo->query("SELECT * FROM `$table`");
  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $cols = array_map(fn($c)=>"`$c`", array_keys($row));
    $vals = array_map(function($v) use ($pdo){
      if (is_null($v)) return "NULL";
      return $pdo->quote($v);
    }, array_values($row));
    echo "INSERT INTO `$table` (".implode(",", $cols).") VALUES (".implode(",", $vals).");\n";
  }
  echo "\n";
}

// Encabezado
echo "-- Backup Farmacia\n";
echo "-- Fecha: ".date('Y-m-d H:i:s')."\n\n";
echo "SET NAMES utf8mb4;\nSET FOREIGN_KEY_CHECKS=0;\n\n";

// Orden recomendado por FKs
$tables = ['pacientes','doctores','componentes','preparados','componentes_preparado'];
foreach ($tables as $t) dump_table($pdo, $t);

echo "SET FOREIGN_KEY_CHECKS=1;\n";
