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

/** Bounded snapshot of the source database's tables, with explicit exclusions rather than option allowlists. */
final class Database {
    private static array $excluded = [];
    private static ?Credentials $credentials = null;
    private static array $credential_context = [];
    private static string $credential_column = '';
    public static function secret_key( string $key ): bool {
        return (bool) preg_match( '/(?:^|[_\-\x00])(password|passwd|secret|api[_-]?key|access[_-]?token|refresh[_-]?token|auth[_-]?token|private[_-]?key|license[_-]?key|smtp[_-]?(?:pass|password))(?:$|[_\-])/i', $key );
    }
    private static function omit( string $reason ): void { self::$excluded[ $reason ] = ( self::$excluded[ $reason ] ?? 0 ) + 1; }
    private static function redact( $value, array $path = [] ) {
        if ( count( $path ) > 32 ) { throw new \RuntimeException( 'Credential fields are nested too deeply to inspect safely.' ); }
        if ( ! is_array( $value ) && ! $value instanceof \stdClass ) { return $value; }
        foreach ( $value as $key => &$item ) {
            if ( self::secret_key( (string) $key ) ) {
                $keep = self::$credentials && self::$credentials->keep( self::$credential_context, self::$credential_column, array_merge( $path, [ $key ] ), $item );
                if ( ! $keep ) { $item = null; self::omit( 'credential_fields' ); }
            } elseif ( is_array( $item ) || $item instanceof \stdClass ) { $item = self::redact( $item, array_merge( $path, [ $key ] ) ); }
        }
        return $value;
    }
    private static function clean_value( string $value ): string {
        $before = self::$excluded['credential_fields'] ?? 0;
        // Do not instantiate application classes while inspecting exported settings.
        if ( is_serialized( $value ) ) {
            $decoded = @unserialize( $value, [ 'allowed_classes' => false ] );
            if ( is_array( $decoded ) ) { $clean = self::redact( $decoded ); return ( self::$excluded['credential_fields'] ?? 0 ) === $before ? $value : serialize( $clean ); }
        }
        if ( str_starts_with( ltrim( $value ), '{' ) || str_starts_with( ltrim( $value ), '[' ) ) {
            $decoded = json_decode( $value );
            if ( is_array( $decoded ) || $decoded instanceof \stdClass ) { $clean = self::redact( $decoded ); return ( self::$excluded['credential_fields'] ?? 0 ) === $before ? $value : wp_json_encode( $clean, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE ); }
        }
        return $value;
    }
    private static function identifier( string $value ): string {
        if ( ! preg_match( '/^[a-zA-Z0-9_]{1,64}$/D', $value ) ) { throw new \RuntimeException( 'A database identifier cannot be imported.' ); }
        return $value;
    }
    public static function remove_files( string $directory ): void {
        $root = rtrim( sys_get_temp_dir(), '/' ) . '/pluginpeer-db-';
        if ( ! preg_match( '#^' . preg_quote( $root, '#' ) . '[a-f0-9]{32}$#D', $directory ) || is_link( $directory ) || ! is_dir( $directory ) ) { return; }
        foreach ( glob( $directory . '/*' ) ?: [] as $file ) { if ( is_file( $file ) && ! is_link( $file ) ) { Filesystem::local()->delete( $file, false, 'f' ); } }
        Filesystem::local()->rmdir( $directory );
    }
    public static function snapshot( array $selection, array $users, bool $catalog_only = false, ?callable $progress = null ): array {
        global $wpdb;
        self::$excluded = [];
        self::$credentials = new Credentials( $selection, $catalog_only );
        self::identifier( $wpdb->prefix );
        $directory = $catalog_only ? '' : rtrim( sys_get_temp_dir(), '/' ) . '/pluginpeer-db-' . bin2hex( random_bytes( 16 ) );
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_mkdir -- Create private directories with mode 0700 immediately; WP_Filesystem_Direct::mkdir applies permissions only after creation.
        if ( ! $catalog_only && ! mkdir( $directory, 0700 ) ) { throw new \RuntimeException( 'Cannot create a private database snapshot.' ); }
        // Remove abandoned temporary snapshots; the transfer capability expires sooner.
        foreach ( glob( rtrim( sys_get_temp_dir(), '/' ) . '/pluginpeer-db-*', GLOB_ONLYDIR ) ?: [] as $old ) {
            if ( filemtime( $old ) < time() - HOUR_IN_SECONDS ) { self::remove_files( $old ); }
        }
        $tables = []; $files = []; $paths = []; $bytes = 0; $scan_bytes = 0;
        $selected_users = array_fill_keys( array_column( $users, 'id' ), true );
        $private = ! empty( $selection['privateContent'] );
        if ( false === $wpdb->query( 'START TRANSACTION WITH CONSISTENT SNAPSHOT' ) ) { self::remove_files( $directory ); throw new \RuntimeException( 'Cannot start a consistent database snapshot.' ); }
        try {
            if ( $progress ) { $progress( 'database', 0, 0, 'tables' ); }
            $post_ids = []; $excluded_posts = [];
            foreach ( $wpdb->get_results( "SELECT ID, post_status, post_type, post_password, post_parent FROM {$wpdb->posts}", ARRAY_A ) as $post ) {
                $include = ! in_array( $post['post_status'], [ 'trash', 'auto-draft' ], true )
                    && ( $private || ( in_array( $post['post_status'], [ 'publish', 'inherit' ], true ) && '' === $post['post_password'] ) );
                if ( $include ) { $post_ids[ $post['ID'] ] = true; } else { $excluded_posts[ $post['ID'] ] = true; }
            }
            // Revisions of excluded posts must not reintroduce their content.
            foreach ( $wpdb->get_results( "SELECT ID, post_parent FROM {$wpdb->posts} WHERE post_type = 'revision'", ARRAY_A ) as $revision ) {
                if ( ! isset( $post_ids[ $revision['post_parent'] ] ) ) { unset( $post_ids[ $revision['ID'] ] ); $excluded_posts[ $revision['ID'] ] = true; }
            }
            $comment_ids = [];
            foreach ( $wpdb->get_results( "SELECT comment_ID, comment_post_ID FROM {$wpdb->comments}", ARRAY_A ) as $comment ) {
                if ( isset( $post_ids[ $comment['comment_post_ID'] ] ) || 0 === (int) $comment['comment_post_ID'] ) { $comment_ids[ $comment['comment_ID'] ] = true; }
            }
            $list = $wpdb->get_results( 'SHOW FULL TABLES', ARRAY_N );
            foreach ( $list as $table_info ) {
                if ( 'BASE TABLE' !== $table_info[1] ) { throw new \RuntimeException( 'Database views require a MySQL runtime; no partial snapshot was made.' ); }
            }
            usort( $list, static fn( $a, $b ) => strcmp( $a[0], $b[0] ) );
            if ( count( $list ) > 1000 ) { throw new \RuntimeException( 'The database exceeds 1,000 tables.' ); }
            foreach ( $list as $table_index => $table_info ) {
                if ( $progress ) { $progress( 'database', $table_index, count( $list ), 'tables' ); }
                $table = self::identifier( $table_info[0] ); $prefixed = str_starts_with( $table, $wpdb->prefix ); $name = $prefixed ? substr( $table, strlen( $wpdb->prefix ) ) : $table; self::identifier( $name );
                $core_name = $prefixed ? $name : '';
                $columns = [];
                foreach ( $wpdb->get_results( $wpdb->prepare( 'SHOW FULL COLUMNS FROM %i', $table ), ARRAY_A ) as $column ) {
                    if ( str_contains( strtolower( $column['Extra'] ), 'generated' ) && ! str_contains( strtolower( $column['Extra'] ), 'default_generated' ) ) { throw new \RuntimeException( 'Generated database columns are not yet supported: ' . $table ); }
                    $type = strtolower( $column['Type'] ); $values = null;
                    if ( preg_match( '/^(enum|set)\((.*)\)$/sD', $type, $match ) ) {
                        // Preserve enum case by reading the original declaration.
                        $values = str_getcsv( substr( $column['Type'], strlen( $match[1] ) + 1, -1 ), ',', "'", '\\' ); $type = $match[1];
                    }
                    $item = [ 'name' => $column['Field'], 'type' => $type, 'nullable' => 'YES' === $column['Null'], 'autoIncrement' => str_contains( $column['Extra'], 'auto_increment' ), 'default' => null === $column['Default'] ? null : base64_encode( $column['Default'] ) ];
                    if ( null !== $column['Default'] && self::secret_key( $column['Field'] ) && ! self::$credentials->keep( Credentials::context( $table, 'Column default', [ 'column' => $column['Field'] ], [ 'column' ] ), $column['Field'], [], $column['Default'] ) ) {
                        $item['default'] = $item['nullable'] ? null : base64_encode( '' ); self::omit( 'credential_defaults' );
                    }
                    if ( $values ) { $item['values'] = $values; }
                    if ( preg_match( '/^current_timestamp(?:\(\))?$/iD', (string) $column['Default'] ) ) { $item['defaultExpression'] = 'CURRENT_TIMESTAMP'; }
                    $columns[] = $item;
                }
                $indexes = [];
                foreach ( $wpdb->get_results( $wpdb->prepare( 'SHOW INDEX FROM %i', $table ), ARRAY_A ) as $index ) {
                    if ( empty( $index['Column_name'] ) || ! in_array( $index['Index_type'], [ 'BTREE', 'HASH' ], true ) ) { throw new \RuntimeException( 'An expression/full-text/spatial index requires a MySQL runtime: ' . $table ); }
                    $key = $index['Key_name'];
                    $indexes[ $key ] ??= [ 'name' => $key, 'unique' => ! $index['Non_unique'], 'columns' => [] ];
                    $indexes[ $key ]['columns'][ (int) $index['Seq_in_index'] ] = $index['Column_name'];
                }
                foreach ( $indexes as &$index ) { ksort( $index['columns'] ); $index['columns'] = array_values( $index['columns'] ); } unset( $index );
                $data_files = []; $rows = []; $rows_bytes = 2; $row_count = 0; $batch_index = 0;
                $flush = static function () use ( &$rows, &$rows_bytes, &$batch_index, &$files, &$paths, &$data_files, &$bytes, $directory, $table_index ) {
                    if ( ! $rows ) { return; }
                    $json = wp_json_encode( $rows );
                    if ( false === $json || strlen( $json ) > 20971520 ) { throw new \RuntimeException( 'A database row exceeds the 20 MiB transfer limit.' ); }
                    $path = sprintf( 'database/%04d-%06d.json', $table_index, $batch_index++ ); $local = $directory . '/' . basename( $path );
                    if ( false === file_put_contents( $local, $json, LOCK_EX ) ) { throw new \RuntimeException( 'Cannot write private snapshot data.' ); } Filesystem::local()->chmod( $local, 0600 );
                    $size = strlen( $json ); $bytes += $size;
                    if ( $bytes > Environment::MAX_BYTES || count( $files ) >= Environment::MAX_FILES ) { throw new \RuntimeException( 'The database exceeds the 256 MiB transfer limit.' ); }
                    $files[] = [ 'path' => $path, 'bytes' => $size, 'sha256' => hash( 'sha256', $json ) ]; $paths[ $path ] = $local; $data_files[] = $path;
                    $rows = []; $rows_bytes = 2;
                };
                $order = array_map( [ self::class, 'identifier' ], $indexes['PRIMARY']['columns'] ?? array_column( $columns, 'name' ) );
                $order_placeholders = implode( ',', array_fill( 0, count( $order ), '%i' ) );
                for ( $offset = 0; ; $offset += 500 ) {
                    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The generated ORDER BY contains only %i tokens; the argument array supplies the table, each column and the offset.
                    $batch = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY ' . $order_placeholders . ' LIMIT 500 OFFSET %d', array_merge( [ $table ], $order, [ $offset ] ) ), ARRAY_A );
                    if ( $wpdb->last_error ) { throw new \RuntimeException( 'Could not read database table: ' . $table ); }
                    foreach ( $batch as $row ) {
                        if ( in_array( $core_name, [ 'smooth_sandbox_recordings', 'smooth_sandbox_chunks', 'smooth_sandbox_callbacks', 'smooth_sandbox_recording_links', 'woocommerce_sessions', 'actionscheduler_claims' ], true ) ) { self::omit( $core_name ); continue; }
                        if ( 'users' === $core_name && ! isset( $selected_users[ $row['ID'] ] ) ) { self::omit( 'unselected_users' ); continue; }
                        if ( 'usermeta' === $core_name && ( ! isset( $selected_users[ $row['user_id'] ] ) || in_array( $row['meta_key'], [ 'session_tokens', '_application_passwords' ], true ) ) ) { self::omit( 'excluded_usermeta' ); continue; }
                        if ( 'posts' === $core_name && ! isset( $post_ids[ $row['ID'] ] ) ) { self::omit( 'excluded_posts' ); continue; }
                        if ( in_array( $core_name, [ 'postmeta', 'term_relationships' ], true ) && ! isset( $post_ids[ $row[ 'postmeta' === $core_name ? 'post_id' : 'object_id' ] ] ) ) { self::omit( 'excluded_post_relations' ); continue; }
                        if ( 'comments' === $core_name && ! isset( $comment_ids[ $row['comment_ID'] ] ) || 'commentmeta' === $core_name && ! isset( $comment_ids[ $row['comment_id'] ] ) ) { self::omit( 'excluded_comments' ); continue; }
                        $key = $row['option_name'] ?? $row['meta_key'] ?? '';
                        if ( 'smooth_sandbox_storage_root' === $key || preg_match( '/^_transient_(?:timeout_)?(?:ss|smooth_sandbox)_package_/', $key ) ) { self::omit( 'snapshot_storage_and_cache' ); continue; }
                        if ( in_array( $key, [ '_transient_doing_cron', '_transient_timeout_doing_cron', 'auth_key', 'auth_salt', 'secure_auth_key', 'secure_auth_salt', 'logged_in_key', 'logged_in_salt', 'nonce_key', 'nonce_salt' ], true ) || preg_match( '/^(?:_transient_(?:timeout_)?(?:ss|smooth_sandbox)_transfer_|(?:_transient_(?:timeout_)?)?action_scheduler_lock_)/', $key ) ) { self::omit( 'credentials_and_locks' ); continue; }
                        self::$credential_context = Credentials::context( $table, $key, $row, $indexes['PRIMARY']['columns'] ?? [] );
                        $whole_credential = self::secret_key( $key );
                        $value_column = array_key_exists( 'option_value', $row ) ? 'option_value' : 'meta_value';
                        if ( $whole_credential && ! self::$credentials->keep( self::$credential_context, $value_column, [], $row[ $value_column ] ?? null ) ) { self::omit( 'credentials_and_locks' ); continue; }
                        if ( 'users' === $core_name ) { $row['user_pass'] = ''; $row['user_activation_key'] = ''; }
                        if ( isset( $row['post_author'] ) && ! isset( $selected_users[ $row['post_author'] ] ) ) { $row['post_author'] = '0'; }
                        if ( 'posts' === $core_name && isset( $excluded_posts[ $row['post_parent'] ] ) ) { $row['post_parent'] = '0'; }
                        if ( 'comments' === $core_name && ! isset( $selected_users[ $row['user_id'] ] ) ) { $row['user_id'] = '0'; }
                        if ( 'actionscheduler_actions' === $core_name ) { $row['claim_id'] = '0'; if ( 'in-progress' === $row['status'] ) { $row['status'] = 'pending'; } }
                        $encoded = [];
                        foreach ( $columns as $column ) {
                            $value = $row[ $column['name'] ];
                            $scan_bytes += strlen( (string) $value );
                            if ( $catalog_only && $scan_bytes > Environment::MAX_BYTES ) { throw new \RuntimeException( 'Credential scan exceeds the 256 MiB database limit.' ); }
                            self::$credential_column = $column['name'];
                            if ( ! $whole_credential && null !== $value && in_array( $column['name'], [ 'option_value', 'meta_value' ], true ) ) {
                                $value = self::clean_value( $value );
                            }
                            if ( null !== $value && self::secret_key( $column['name'] ) && ! self::$credentials->keep( self::$credential_context, $column['name'], [], $value ) ) { $value = ''; self::omit( 'credential_columns' ); }
                            $encoded[] = null === $value ? null : base64_encode( $value );
                        }
                        if ( $catalog_only ) { continue; }
                        $rows[] = $encoded; $rows_bytes += strlen( wp_json_encode( $encoded ) ); $row_count++;
                        if ( $rows_bytes >= 1048576 || count( $rows ) >= 500 ) { $flush(); }
                    }
                    if ( count( $batch ) < 500 ) { break; }
                }
                $flush();
                $tables[] = [ 'name' => $name, 'prefixed' => $prefixed, 'columns' => $columns, 'indexes' => array_values( $indexes ), 'files' => $data_files, 'rows' => $row_count ];
            }
            self::$credentials->validate();
            $catalog = self::$credentials->catalog();
            $max_user_id = (int) $wpdb->get_var( "SELECT COALESCE(MAX(ID), 0) FROM {$wpdb->users}" );
            $wpdb->query( 'COMMIT' );
            return [ 'manifest' => [ 'format' => 1, 'prefix' => $wpdb->prefix, 'maxUserId' => $max_user_id, 'tables' => $tables, 'excluded' => (object) self::$excluded ], 'files' => $files, 'paths' => $paths, 'directory' => $directory, 'credentials' => $catalog ];
        } catch ( \Throwable $error ) { $wpdb->query( 'ROLLBACK' ); self::remove_files( $directory ); throw $error; }
        finally { self::$credentials = null; self::$credential_context = []; self::$credential_column = ''; }
    }
}