<?php
declare(strict_types=1);

/**
 * Exports the configured MySQL/MariaDB database to a phpMyAdmin-compatible SQL file.
 *
 * Usage:
 *   php scripts/export-db.php [output-file.sql]
 */

$config = require __DIR__ . '/../config/config.php';
$db = $config['db'] ?? [];

$dsn = (string)($db['dsn'] ?? '');
$user = (string)($db['user'] ?? '');
$pass = (string)($db['pass'] ?? '');

if ($dsn === '' || $user === '') {
  fwrite(STDERR, "DB config eksik.\n");
  exit(1);
}

$defaultName = __DIR__ . '/../deploy/cpanel/sql/db_' . date('Ymd_His') . '.sql';
$outPath = $argv[1] ?? $defaultName;
$outDir = dirname($outPath);
if (!is_dir($outDir) && !mkdir($outDir, 0777, true) && !is_dir($outDir)) {
  fwrite(STDERR, "Klasor olusturulamadi: {$outDir}\n");
  exit(1);
}

try {
  $pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NUM,
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false,
  ]);
} catch (Throwable $e) {
  fwrite(STDERR, "DB baglanti hatasi: " . $e->getMessage() . "\n");
  exit(1);
}

$fh = fopen($outPath, 'wb');
if ($fh === false) {
  fwrite(STDERR, "Dosya acilamadi: {$outPath}\n");
  exit(1);
}

$write = static function (string $s) use ($fh): void {
  fwrite($fh, $s);
};

$write("-- IVR database export\n");
$write("-- Generated at: " . date('Y-m-d H:i:s') . "\n");
$write("SET NAMES utf8mb4;\n");
$write("SET FOREIGN_KEY_CHECKS=0;\n\n");

/**
 * Resolve database name from DSN.
 */
$dbName = '';
if (preg_match('/(?:^|;)dbname=([^;]+)/i', $dsn, $m)) {
  $dbName = $m[1];
}
if ($dbName !== '') {
  $write("-- Database: `{$dbName}`\n\n");
}

$tableRows = $pdo->query("SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'")->fetchAll();
$tables = [];
foreach ($tableRows as $r) {
  $tables[] = (string)$r[0];
}

foreach ($tables as $table) {
  $qTable = '`' . str_replace('`', '``', $table) . '`';

  $write("-- ----------------------------\n");
  $write("-- Table structure for {$qTable}\n");
  $write("-- ----------------------------\n");
  $write("DROP TABLE IF EXISTS {$qTable};\n");

  $createStmt = $pdo->query("SHOW CREATE TABLE {$qTable}")->fetch();
  $createSql = (string)($createStmt[1] ?? '');
  $write($createSql . ";\n\n");

  $write("-- ----------------------------\n");
  $write("-- Data for {$qTable}\n");
  $write("-- ----------------------------\n");

  $colRes = $pdo->query("SHOW COLUMNS FROM {$qTable}");
  $cols = [];
  while ($c = $colRes->fetch()) {
    $cols[] = (string)$c[0];
  }
  $colList = implode(', ', array_map(static function (string $c): string {
    return '`' . str_replace('`', '``', $c) . '`';
  }, $cols));

  $select = $pdo->query("SELECT * FROM {$qTable}");
  $hasRows = false;
  while (($row = $select->fetch(PDO::FETCH_ASSOC)) !== false) {
    $hasRows = true;
    $vals = [];
    foreach ($cols as $col) {
      $v = $row[$col] ?? null;
      if ($v === null) {
        $vals[] = "NULL";
      } else {
        $vals[] = $pdo->quote((string)$v);
      }
    }
    $write("INSERT INTO {$qTable} ({$colList}) VALUES (" . implode(', ', $vals) . ");\n");
  }
  if (!$hasRows) {
    $write("-- (no rows)\n");
  }
  $write("\n");
}

$viewRows = $pdo->query("SHOW FULL TABLES WHERE Table_type = 'VIEW'")->fetchAll();
$views = [];
foreach ($viewRows as $r) {
  $views[] = (string)$r[0];
}
foreach ($views as $view) {
  $qView = '`' . str_replace('`', '``', $view) . '`';
  $write("-- ----------------------------\n");
  $write("-- View structure for {$qView}\n");
  $write("-- ----------------------------\n");
  $write("DROP VIEW IF EXISTS {$qView};\n");
  $createView = $pdo->query("SHOW CREATE VIEW {$qView}")->fetch(PDO::FETCH_ASSOC);
  $viewSql = (string)($createView['Create View'] ?? '');
  if ($viewSql !== '') {
    $write($viewSql . ";\n\n");
  }
}

$write("SET FOREIGN_KEY_CHECKS=1;\n");
fclose($fh);

echo "OK: {$outPath}\n";
