<?php

namespace App\Support;

use Illuminate\Support\Facades\DB;
use PDO;
use RuntimeException;

class DatabaseSqlDumpExporter
{
    public function __construct(
        protected SqliteToMysqlDumpExporter $sqliteExporter,
    ) {
    }

    public function export(string $outputDir, ?string $databaseName = null, ?string $connectionName = null): string
    {
        $connectionName ??= (string) config('database.default', 'mysql');
        $connection = DB::connection($connectionName);
        $driver = $connection->getDriverName();

        return match ($driver) {
            'mysql', 'mariadb' => $this->exportMysqlConnection(
                $connection->getPdo(),
                $outputDir,
                $databaseName ?? (string) config("database.connections.{$connectionName}.database", 'database')
            ),
            'sqlite' => $this->exportSqliteConnection($outputDir, $databaseName, $connectionName),
            default => throw new RuntimeException(sprintf('SQL export is not supported for the [%s] driver.', $driver)),
        };
    }

    protected function exportSqliteConnection(string $outputDir, ?string $databaseName, string $connectionName): string
    {
        $databaseName ??= 'database';
        $sqliteDatabase = (string) config("database.connections.{$connectionName}.database", database_path('database.sqlite'));

        if ($sqliteDatabase === ':memory:') {
            return $this->sqliteExporter->exportFromPdo(DB::connection($connectionName)->getPdo(), $outputDir, $databaseName);
        }

        if (! filled($sqliteDatabase) || ! file_exists($sqliteDatabase)) {
            throw new RuntimeException('SQLite database file was not found for export.');
        }

        return $this->sqliteExporter->export($sqliteDatabase, $outputDir, $databaseName);
    }

    protected function exportMysqlConnection(PDO $pdo, string $outputDir, string $databaseName): string
    {
        if (! is_dir($outputDir) && ! mkdir($outputDir, 0777, true) && ! is_dir($outputDir)) {
            throw new RuntimeException("Unable to create backup directory at {$outputDir}");
        }

        $outputPath = rtrim($outputDir, DIRECTORY_SEPARATOR) . DIRECTORY_SEPARATOR . $databaseName . '_' . date('Y-m-d_His') . '.sql';
        $tables = $pdo->query("SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'")->fetchAll(PDO::FETCH_NUM);

        $sql = [];
        $sql[] = '-- Saleem Sial Analytics SQL backup';
        $sql[] = '-- Generated from ' . $databaseName . ' on ' . date('Y-m-d H:i:s');
        $sql[] = 'SET NAMES utf8mb4;';
        $sql[] = 'SET FOREIGN_KEY_CHECKS=0;';
        $sql[] = 'CREATE DATABASE IF NOT EXISTS `' . $databaseName . '` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;';
        $sql[] = 'USE `' . $databaseName . '`;';
        $sql[] = '';

        foreach ($tables as $tableRow) {
            $table = (string) ($tableRow[0] ?? '');

            if ($table === '') {
                continue;
            }

            $createStatement = $pdo
                ->query('SHOW CREATE TABLE `' . str_replace('`', '``', $table) . '`')
                ->fetch(PDO::FETCH_ASSOC);

            $createTableSql = is_array($createStatement)
                ? ($createStatement['Create Table'] ?? array_values($createStatement)[1] ?? null)
                : null;

            if (! is_string($createTableSql) || $createTableSql === '') {
                throw new RuntimeException(sprintf('Unable to read the CREATE TABLE statement for [%s].', $table));
            }

            $sql[] = 'DROP TABLE IF EXISTS `' . $table . '`;';
            $sql[] = $createTableSql . ';';
            $sql[] = '';

            $rows = $pdo->query('SELECT * FROM `' . str_replace('`', '``', $table) . '`')->fetchAll(PDO::FETCH_ASSOC);

            if ($rows === []) {
                continue;
            }

            $columnNames = array_keys($rows[0]);
            $quotedColumns = implode(', ', array_map(static fn (string $columnName): string => '`' . $columnName . '`', $columnNames));

            foreach (array_chunk($rows, 100) as $chunk) {
                $valueSets = [];

                foreach ($chunk as $row) {
                    $values = [];

                    foreach ($columnNames as $columnName) {
                        $values[] = $this->exportValue($row[$columnName] ?? null);
                    }

                    $valueSets[] = '(' . implode(', ', $values) . ')';
                }

                $sql[] = 'INSERT INTO `' . $table . '` (' . $quotedColumns . ') VALUES';
                $sql[] = implode(",\n", $valueSets) . ';';
                $sql[] = '';
            }
        }

        $sql[] = 'SET FOREIGN_KEY_CHECKS=1;';
        $sql[] = '';

        file_put_contents($outputPath, implode(PHP_EOL, $sql));

        return $outputPath;
    }

    protected function exportValue(mixed $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 "'" . str_replace(
            ["\\", "'", "\0", "\n", "\r", "\x1a"],
            ["\\\\", "\\'", "\\0", "\\n", "\\r", "\\Z"],
            (string) $value
        ) . "'";
    }
}
