<?php
namespace SmoothBundle\Sandbox;
defined( 'ABSPATH' ) || exit;

/** Prepare a portable SQLite image once, after the existing redaction/export policy. */
final class SQLite_Snapshot {
    public static function available(): bool {
        // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO -- Checks the SQLite export driver; the source WordPress database uses $wpdb.
        return class_exists( '\\PDO' ) && in_array( 'sqlite', \PDO::getAvailableDrivers(), true )
            && extension_loaded( 'mbstring' )
            && ( ! defined( 'SQLITE_DRIVER_VERSION' ) || SQLITE_DRIVER_VERSION === '3.1.1' );
    }

    public static function build( array &$export, ?callable $progress = null ): void {
        // Existing JSON snapshots remain the fallback on hosts without PDO SQLite.
        if ( ! self::available() ) { return; }
        require_once dirname( __DIR__ ) . '/vendor/sqlite-database-integration/database/load.php';
        $directory = $export['databaseDirectory'];
        $path = $directory . '/snapshot.sqlite';
        $database = $export['manifest']['database'];
        $driver = new \WP_MySQL_On_SQLite( 'mysql-on-sqlite:path=' . str_replace( ';', ';;', $path ) . ';dbname=wordpress', null, null, [ 'sqlite_journal_mode' => 'DELETE' ] );
        Filesystem::local()->chmod( $path, 0600 );
        $pdo = $driver->get_sqlite_pdo();
        $identifier = static function ( $name ) {
            if ( ! is_string( $name ) || ! preg_match( '/^[A-Za-z0-9_]{1,64}$/D', $name ) ) { throw new \RuntimeException( 'Invalid database identifier.' ); }
            return '`' . $name . '`';
        };
        $decode = static function ( $value ) {
            $decoded = base64_decode( $value, true );
            if ( false === $decoded ) { throw new \RuntimeException( 'Invalid database cell.' ); }
            return $decoded;
        };
        $targets = [];
        $driver->beginTransaction();
        try {
            foreach ( $database['tables'] as $number => $table ) {
                if ( $progress ) { $progress( 'sqlite', $number, count( $database['tables'] ), 'tables' ); }
                $target = ( ( $table['prefixed'] ?? true ) ? $database['prefix'] : '' ) . $table['name'];
                if ( isset( $targets[ strtolower( $target ) ] ) || preg_match( '/^(?:sqlite_|_mysql_|_wp_sqlite_)/i', $target ) ) { throw new \RuntimeException( 'A table conflicts with the SQLite schema.' ); }
                $targets[ strtolower( $target ) ] = true;
                $quoted = $identifier( $target ); $definitions = [];
                foreach ($table['columns'] as $column) {
                    $type = $column['type'];
                    if (!preg_match('/^(?:(?:tinyint|smallint|mediumint|int|integer|bigint)(?:\(\d{1,3}\))?(?: unsigned)?|(?:decimal|numeric|float|double|real)(?:\(\d{1,3}(?:,\d{1,3})?\))?(?: unsigned)?|(?:var)?(?:char|binary)\(\d{1,5}\)|(?:tiny|medium|long)?(?:text|blob)|(?:date|datetime|timestamp|time|year)(?:\(\d\))?|boolean|bool|json|bit(?:\(\d{1,2}\))?|enum|set)$/iD', $type)) throw new \RuntimeException('Unsupported database column type.');
                    if (in_array($type, ['enum', 'set'], true)) $type .= '(' . implode(',', array_map(static fn($value) => $driver->quote($value), $column['values'])) . ')';
                    $definition = $identifier($column['name']) . ' ' . $type . ($column['nullable'] ? ' NULL' : ' NOT NULL');
                    if (isset($column['defaultExpression'])) {
                        if (!in_array($column['defaultExpression'], ['CURRENT_TIMESTAMP', 'CURRENT_TIMESTAMP()'], true)) throw new \RuntimeException('Invalid default expression.');
                        $definition .= ' DEFAULT CURRENT_TIMESTAMP';
                    } elseif ($column['default'] !== null) { $definition .= ' DEFAULT ' . $driver->quote($decode($column['default'])); }
                    elseif ($column['nullable']) { $definition .= ' DEFAULT NULL'; }
                    if ($column['autoIncrement']) $definition .= ' AUTO_INCREMENT';
                    $definitions[] = $definition;
                }
                foreach ($table['indexes'] as $index) {
                    $columns = implode(',', array_map($identifier, $index['columns']));
                    $definitions[] = $index['name'] === 'PRIMARY' ? 'PRIMARY KEY (' . $columns . ')' : ($index['unique'] ? 'UNIQUE ' : '') . 'KEY ' . $identifier($index['name']) . ' (' . $columns . ')';
                }

                $driver->exec( 'CREATE TABLE ' . $quoted . ' (' . implode( ',', $definitions ) . ')' );
                $columns = implode( ',', array_map( $identifier, array_column( $table['columns'], 'name' ) ) );
                $insert = $pdo->prepare( 'INSERT INTO ' . $quoted . ' (' . $columns . ') VALUES (' . implode( ',', array_fill( 0, count( $table['columns'] ), '?' ) ) . ')' );
                $count = 0;
                foreach ( $table['files'] as $file ) {
                    $rows = json_decode( file_get_contents( $export['paths'][ $file ] ), true, 64, JSON_THROW_ON_ERROR );
                    foreach ( $rows as $row ) {
                        if ( count( $row ) !== count( $table['columns'] ) ) { throw new \RuntimeException( 'Invalid database row width.' ); }
                        foreach ( $row as $index => $cell ) {
                            $value = null === $cell ? null : $decode( $cell );
                            $binary = preg_match( '/binary|blob|^bit/', $table['columns'][ $index ]['type'] );
                            // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO -- Typed parameters preserve NULL and binary cells in a separate SQLite export, not the source database.
                            $insert->bindValue( $index + 1, $value, null === $value ? \PDO::PARAM_NULL : ( $binary ? \PDO::PARAM_LOB : \PDO::PARAM_STR ) );
                        }
                        $insert->execute(); $count++;
                    }
                    unset( $rows );
                }
                if ( $count !== $table['rows'] ) { throw new \RuntimeException( 'SQLite row count mismatch.' ); }
                unset( $insert );
            }
            $driver->commit();
            if ( $pdo->query( 'PRAGMA quick_check' )->fetchColumn() !== 'ok' ) { throw new \RuntimeException( 'SQLite integrity check failed.' ); }
        } catch ( \Throwable $error ) {
            if ( $driver->inTransaction() ) { $driver->rollBack(); }
            throw $error;
        } finally { unset( $pdo, $driver ); }
        clearstatcache( true, $path );
        $size = filesize( $path );
        $other_files = array_values( array_filter( $export['manifest']['files'], static fn( $file ) => ! str_starts_with( $file['path'], 'database/' ) ) );
        if ( $size + array_sum( array_column( $other_files, 'bytes' ) ) > Environment::MAX_BYTES ) { throw new \RuntimeException( 'SQLite Snapshot exceeds the 256 MiB transfer limit.' ); }
        $files = []; $paths = []; $parts = [];
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- Read the SQLite image in bounded chunks using a native stream; do not load the full database into memory.
        $input = fopen( $path, 'rb' );
        try {
            for ( $part = 0; ! feof( $input ); $part++ ) {
                // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fread -- Read the SQLite image in bounded chunks using a native stream; do not load the full database into memory.
                $bytes = fread( $input, 20 * 1024 * 1024 );
                if ( false === $bytes ) { throw new \RuntimeException( 'Cannot read prepared SQLite database.' ); }
                if ( '' === $bytes ) { break; }
                $name = sprintf( 'database/sqlite-%06d.bin', $part ); $local = $directory . '/' . basename( $name );
                if ( file_put_contents( $local, $bytes, LOCK_EX ) !== strlen( $bytes ) ) { throw new \RuntimeException( 'Cannot write SQLite transfer part.' ); }
                Filesystem::local()->chmod( $local, 0600 );
                $parts[] = $name; $paths[ $name ] = $local;
                $files[] = [ 'path' => $name, 'bytes' => strlen( $bytes ), 'sha256' => hash( 'sha256', $bytes ) ];
            }
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- Read the SQLite image in bounded chunks using a native stream; do not load the full database into memory.
        } finally { fclose( $input ); }
        if ( count( $other_files ) + count( $files ) > Environment::MAX_FILES ) { throw new \RuntimeException( 'SQLite Snapshot exceeds the file limit.' ); }
        $database['format'] = 2;
        $database['sqlite'] = [ 'parts' => $parts, 'bytes' => $size, 'sha256' => hash_file( 'sha256', $path ), 'driver' => '3.1.1' ];
        foreach ( $database['tables'] as &$table ) {
            foreach ( $table['files'] as $file ) { Filesystem::local()->delete( $export['paths'][ $file ], false, 'f' ); unset( $export['paths'][ $file ] ); }
            $table['files'] = [];
        } unset( $table );
        $export['paths'] += $paths;
        $export['manifest']['files'] = array_merge( $other_files, $files );
        $export['manifest']['database'] = $database;
        Filesystem::local()->delete( $path, false, 'f' );
        if ( $progress ) { $progress( 'sqlite', count( $database['tables'] ), count( $database['tables'] ), 'tables' ); }
    }
}