<?php
// app/db/SchemaTool.php

declare(strict_types=1);

namespace ScMedia\Db;

use Exception;

final class SchemaTool {
  private Db $db;
  private string $schemaPath;

  public function __construct(Db $db, string $schemaPath) {
    $this->db = $db;
    $this->schemaPath = $schemaPath;
  }

  public function clearIndex(): void {
    $this->db->begin();
    try {
      $this->db->exec("DELETE FROM items");
      $this->db->exec("DELETE FROM jobs");
      $this->db->commit();
    } catch (Exception $e) {
      $this->db->rollBack();
      throw $e;
    }
  }

  public function clearContent(): void {
    $this->db->begin();
    try {
      $this->db->exec("DELETE FROM media_metadata");
      $this->db->exec("DELETE FROM media_file_meta");
      $this->db->exec("DELETE FROM media_files");
      $this->db->exec("DELETE FROM items");
      $this->db->commit();
    } catch (Exception $e) {
      $this->db->rollBack();
      throw $e;
    }
  }

  public function getContentStats(): array {
    $files = $this->db->fetchOne("SELECT COUNT(*) AS c FROM media_files");
    $meta = $this->db->fetchOne("SELECT COUNT(*) AS c FROM media_metadata");
    $items = $this->db->fetchOne("SELECT COUNT(*) AS c FROM items");
    return [
      'files' => (int)($files['c'] ?? 0),
      'meta' => (int)($meta['c'] ?? 0),
      'items' => (int)($items['c'] ?? 0),
    ];
  }

  public function getDbStats(): array {
    $rows = $this->db->fetchAll("SHOW TABLE STATUS");
    $tables = 0;
    $size = 0;
    foreach ($rows as $row) {
      if (!isset($row['Name'])) continue;
      $tables += 1;
      $size += (int)($row['Data_length'] ?? 0) + (int)($row['Index_length'] ?? 0);
    }
    return [
      'tables' => $tables,
      'size_bytes' => $size,
    ];
  }

  public function getDbInfo(): array {
    $row = $this->db->fetchOne("SELECT DATABASE() AS db_name, USER() AS user_name, CURRENT_USER() AS current_user_name");
    return [
      'db_name' => (string)($row['db_name'] ?? ''),
      'user_name' => (string)($row['user_name'] ?? ''),
      'current_user' => (string)($row['current_user_name'] ?? ''),
    ];
  }

  public function listTablesWithCounts(): array {
    $rows = $this->db->fetchAll("SHOW TABLE STATUS");
    $out = [];
    foreach ($rows as $row) {
      $name = (string)($row['Name'] ?? '');
      if ($name === '') continue;
      $out[] = [
        'name' => $name,
        'rows' => (int)($row['Rows'] ?? 0),
      ];
    }
    return $out;
  }

  public function fetchTableRows(string $table, int $limit = 50): array {
    $table = trim($table);
    if ($table === '') {
      throw new Exception('Table required');
    }
    $tables = $this->listTableNames();
    if (!in_array($table, $tables, true)) {
      throw new Exception('Unknown table');
    }
    $limit = max(1, min(500, (int)$limit));
    $escaped = $this->escapeIdentifier($table);
    $rows = $this->db->fetchAll("SELECT * FROM {$escaped} LIMIT {$limit}");
    return [
      'name' => $table,
      'rows' => $rows,
    ];
  }

  public function dumpDatabase(): string {
    $rows = $this->db->fetchAll("
      SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = DATABASE()
        AND table_type = 'BASE TABLE'
      ORDER BY table_name
    ");

    $tables = array_map(fn($r) => (string)$r['table_name'], $rows);
    $out = "-- scMedia database dump\n";
    $out .= "SET FOREIGN_KEY_CHECKS=0;\n\n";

    foreach ($tables as $table) {
      $escaped = "`" . str_replace("`", "``", $table) . "`";
      $create = $this->db->fetchOne("SHOW CREATE TABLE {$escaped}");
      $ddl = $create['Create Table'] ?? '';
      if ($ddl === '') {
        continue;
      }
      $out .= "DROP TABLE IF EXISTS {$escaped};\n";
      $out .= $ddl . ";\n";

      $data = $this->db->fetchAll("SELECT * FROM {$escaped}");
      if (count($data) > 0) {
        $cols = array_keys($data[0]);
        $colList = implode(', ', array_map(fn($c) => "`" . str_replace("`", "``", $c) . "`", $cols));
        foreach ($data as $row) {
          $values = [];
          foreach ($cols as $col) {
            $values[] = $this->quoteValue($row[$col] ?? null);
          }
          $out .= "INSERT INTO {$escaped} ({$colList}) VALUES (" . implode(', ', $values) . ");\n";
        }
      }

      $out .= "\n";
    }

    $out .= "SET FOREIGN_KEY_CHECKS=1;\n";
    return $out;
  }

  private function listTableNames(): array {
    $rows = $this->db->fetchAll("SHOW TABLES");
    $out = [];
    foreach ($rows as $row) {
      $vals = array_values($row);
      $name = (string)($vals[0] ?? '');
      if ($name !== '') $out[] = $name;
    }
    return $out;
  }

  private function escapeIdentifier(string $name): string {
    return '`' . str_replace('`', '``', $name) . '`';
  }

  public function restoreDatabaseFromSql(string $sql): array {
    $log = [];
    $log[] = "Restore DB: dropping all tables in current database...";
    $this->dropAllTables($log);

    $log[] = "Restore DB: importing dump...";
    $this->importSql($sql, $log);

    $log[] = "Restore DB: done.";
    return $log;
  }

  public function resetDatabase(): array {
    $log = [];

    $log[] = "Reset DB: dropping all tables in current database...";
    $this->dropAllTables($log);

    $log[] = "Reset DB: importing schema.sql...";
    $this->importSchema($log);

    $log[] = "Reset DB: done.";
    return $log;
  }

  public function applySchema(): array {
    $log = [];
    $log[] = "Apply schema: importing schema.sql...";
    $this->importSchema($log);
    $log[] = "Apply schema: done.";
    return $log;
  }

  private function dropAllTables(array &$log): void {
    $this->db->exec("SET FOREIGN_KEY_CHECKS=0");

    $rows = $this->db->fetchAll("
      SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = DATABASE()
        AND table_type = 'BASE TABLE'
    ");

    if (count($rows) === 0) {
      $log[] = "No tables found. Nothing to drop.";
      $this->db->exec("SET FOREIGN_KEY_CHECKS=1");
      return;
    }

    $tables = [];
    foreach ($rows as $r) {
      $tables[] = "`" . str_replace("`", "``", (string)$r['table_name']) . "`";
    }

    $sql = "DROP TABLE IF EXISTS " . implode(", ", $tables);
    $this->db->exec($sql);

    $this->db->exec("SET FOREIGN_KEY_CHECKS=1");
    $log[] = "Dropped " . count($tables) . " tables.";
  }

  protected function importSchema(array &$log): void {
    if (!is_file($this->schemaPath)) {
      throw new Exception("schema.sql not found: " . $this->schemaPath);
    }

    $sql = file_get_contents($this->schemaPath);
    if ($sql === false) {
      throw new Exception("Failed to read schema.sql");
    }

    $this->importSql($sql, $log);
  }

  private function splitSqlStatements(string $sql): array {
    // Simple splitter: handles -- comments and /* */ comments reasonably well.
    // Does not support DELIMITER blocks (we don't use them).
    $out = [];
    $len = strlen($sql);
    $buf = '';
    $inString = false;
    $stringChar = '';
    $inLineComment = false;
    $inBlockComment = false;

    for ($i = 0; $i < $len; $i++) {
      $ch = $sql[$i];
      $next = ($i + 1 < $len) ? $sql[$i + 1] : '';

      if ($inLineComment) {
        if ($ch === "\n") {
          $inLineComment = false;
        }
        continue;
      }

      if ($inBlockComment) {
        if ($ch === '*' && $next === '/') {
          $inBlockComment = false;
          $i++;
        }
        continue;
      }

      if (!$inString) {
        if ($ch === '-' && $next === '-') {
          $inLineComment = true;
          $i++;
          continue;
        }
        if ($ch === '/' && $next === '*') {
          $inBlockComment = true;
          $i++;
          continue;
        }
      }

      if ($inString) {
        $buf .= $ch;
        if ($ch === $stringChar) {
          // Handle escaped quotes: '' or \" not fully, but good enough for schema.sql
          $prev = ($i > 0) ? $sql[$i - 1] : '';
          if ($prev !== '\\') {
            $inString = false;
            $stringChar = '';
          }
        }
        continue;
      }

      if ($ch === '\'' || $ch === '"') {
        $inString = true;
        $stringChar = $ch;
        $buf .= $ch;
        continue;
      }

      if ($ch === ';') {
        $out[] = $buf;
        $buf = '';
        continue;
      }

      $buf .= $ch;
    }

    if (trim($buf) !== '') {
      $out[] = $buf;
    }

    return $out;
  }

  private function importSql(string $sql, array &$log): void {
    $statements = $this->splitSqlStatements($sql);
    $log[] = "Statements: " . count($statements);

    foreach ($statements as $i => $stmt) {
      $trim = trim($stmt);
      if ($trim === '') {
        continue;
      }

      try {
        $this->db->pdo()->exec($trim);
      } catch (Exception $e) {
        $log[] = "ERROR at statement #" . ($i + 1) . ": " . $e->getMessage();
        $log[] = "Statement: " . mb_substr($trim, 0, 5000);
        throw $e;
      }
    }
  }

  private function quoteValue($value): string {
    if ($value === null) {
      return 'NULL';
    }
    if (is_bool($value)) {
      return $value ? '1' : '0';
    }
    if (is_int($value) || is_float($value)) {
      return (string)$value;
    }
    return $this->db->pdo()->quote((string)$value);
  }
}
