<?php
/**
 * Pure-PHP chunked database dump via $wpdb.
 *
 * @package BRVerify
 */

declare(strict_types=1);

namespace BRVerify\Backup;

use BRVerify\Support\Logger;
use BRVerify\Support\State_Exception;
use BRVerify\Support\State_Files;

/**
 * Db_Dumper — streams CREATE TABLE + INSERT batches into dump.sql.
 */
final class Db_Dumper {

	/** Rows per batch (soft). */
	public const ROW_BATCH = 500;

	/**
	 * Target size of one fetched batch (bytes of SQL).
	 */
	public const BATCH_BYTES = 4194304;

	/**
	 * Rows in the first batch of a table in each request (row size still unknown).
	 */
	public const FIRST_BATCH = 8;

	/** Soft byte budget per tick for SQL writes. */
	public const BYTE_BUDGET = 67108864; // 64 MiB per tick (the time budget usually ends a tick first)

	/**
	 * Advance dump one tick.
	 *
	 * Cursor keys: tables (list), table_index, offset, dump_path, header_written, finished.
	 *
	 * @param int                 $job_id     Job id.
	 * @param string              $job_dir    Absolute job working dir.
	 * @param array<string,mixed> $cursor     Resume cursor.
	 * @param float               $deadline   microtime(true) deadline.
	 * @return array{done:bool,cursor:array<string,mixed>,error?:string}
	 */
	public function tick( int $job_id, string $job_dir, array $cursor, float $deadline ): array {
		global $wpdb;

		$dump_path           = trailingslashit( $job_dir ) . 'database/dump.sql';
		$tables_path         = trailingslashit( $job_dir ) . 'database/tables.json';
		$cursor['dump_path'] = $dump_path;
		if ( ! is_dir( dirname( $dump_path ) ) ) {
			wp_mkdir_p( dirname( $dump_path ) );
		}

		// Older cursors kept the table list inline; move it to the working file.
		if ( isset( $cursor['tables'] ) && is_array( $cursor['tables'] ) ) {
			State_Files::write_atomic( $tables_path, (string) wp_json_encode( array_values( $cursor['tables'] ) ) );
			$cursor['table_count'] = count( $cursor['tables'] );
			unset( $cursor['tables'] );
		}

		if ( empty( $cursor['table_count'] ) ) {
			$tables = $this->list_prefixed_tables();
			if ( empty( $tables ) ) {
				return array(
					'done'   => false,
					'cursor' => $cursor,
					'error'  => __( 'No database tables found for this site prefix.', '007aj-backup-restore-verifier' ),
				);
			}
			State_Files::write_atomic( $tables_path, (string) wp_json_encode( $tables ) );
			$cursor['table_count']    = count( $tables );
			$cursor['table_index']    = 0;
			$cursor['offset']         = 0;
			$cursor['last_key']       = null;
			$cursor['header_written'] = false;
			$cursor['dump_bytes']     = 0;
			State_Files::truncate( $dump_path, 0 );
			Logger::info( $job_id, sprintf( 'DB dump: discovered %d table(s).', count( $tables ) ) );
		}

		$tables = json_decode( (string) file_get_contents( $tables_path ), true ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_get_contents_file_get_contents
		if ( ! is_array( $tables ) ) {
			return array(
				'done'   => false,
				'cursor' => $cursor,
				'error'  => sprintf(
					/* translators: %s: path */
					__( 'Database table list working file is missing or corrupt: %s', '007aj-backup-restore-verifier' ),
					$tables_path
				),
			);
		}

		// Drop anything a crashed tick appended after the last committed position.
		State_Files::truncate( $dump_path, (int) ( $cursor['dump_bytes'] ?? 0 ) );

		if ( empty( $cursor['header_written'] ) ) {
			State_Files::append( $dump_path, $this->build_header() );
			$cursor['header_written'] = true;
		}

		$index  = (int) ( $cursor['table_index'] ?? 0 );
		$offset = (int) ( $cursor['offset'] ?? 0 );
		$last   = $cursor['last_key'] ?? null;
		$bytes  = 0;
		$buf    = '';
		$keys   = array();
		$batch  = array();

		while ( $index < count( $tables ) && microtime( true ) < $deadline && $bytes < self::BYTE_BUDGET ) {
			$table = (string) $tables[ $index ];

			if ( 0 === $offset && null === $last ) {
				$ddl    = $this->get_create_table( $table );
				$chunk  = "\n\n--\n-- Table structure for `{$table}`\n--\n\nDROP TABLE IF EXISTS `{$table}`;\n{$ddl};\n\n--\n-- Dumping data for `{$table}`\n--\n\n";
				$buf   .= $chunk;
				$bytes += strlen( $chunk );
			}

			if ( ! array_key_exists( $table, $keys ) ) {
				$keys[ $table ]  = $this->integer_primary_key( $table );
				$batch[ $table ] = $this->initial_batch( $table );
			}
			$key   = $keys[ $table ];
			$limit = $batch[ $table ];
			$rows  = null !== $key
				? $this->fetch_rows_after( $table, $key, $last, $limit )
				: $this->fetch_rows( $table, $offset, $limit );

			if ( ! empty( $rows ) ) {
				$sql = $this->rows_to_insert( $table, $rows );
				// Keep each fetched batch near BATCH_BYTES so tables with large rows stay within memory.
				$per             = max( 1, (int) ceil( strlen( $sql ) / count( $rows ) ) );
				$batch[ $table ] = (int) max( 1, min( self::ROW_BATCH, (int) floor( self::BATCH_BYTES / $per ) ) );
				$buf            .= $sql;
				$bytes          += strlen( $sql );
				$offset         += count( $rows );
				if ( null !== $key ) {
					$tail = end( $rows );
					$last = isset( $tail[ $key ] ) ? (string) $tail[ $key ] : null;
				}
			}

			if ( empty( $rows ) || count( $rows ) < $limit ) {
				Logger::info( $job_id, sprintf( 'DB dump: finished table %s (%d rows).', $table, $offset ) );
				++$index;
				$offset = 0;
				$last   = null;
			}

			if ( strlen( $buf ) > 1048576 ) {
				State_Files::append( $dump_path, $buf );
				$buf = '';
			}
		}
		State_Files::append( $dump_path, $buf );

		$cursor['table_index'] = $index;
		$cursor['offset']      = $offset;
		$cursor['last_key']    = $last;

		$done = $index >= count( $tables );
		if ( $done ) {
			State_Files::append( $dump_path, "\n-- Dump completed at " . gmdate( 'c' ) . " UTC\n" );
			$cursor['finished'] = true;
			Logger::info( $job_id, 'DB dump completed.' );
		}
		$cursor['dump_bytes'] = State_Files::size( $dump_path );

		return array(
			'done'   => $done,
			'cursor' => $cursor,
		);
	}

	/**
	 * List tables matching the site prefix (MySQL + SQLite-friendly).
	 *
	 * @return list<string>
	 */
	public function list_prefixed_tables(): array {
		global $wpdb;

		$prefix = $wpdb->prefix;
		$tables = array();

		// MySQL / MariaDB (and SQLite integration that emulates SHOW TABLES).
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$found = $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $prefix ) . '%' ) );
		if ( is_array( $found ) && ! empty( $found ) ) {
			foreach ( $found as $t ) {
				$tables[] = (string) $t;
			}
			sort( $tables );
			return array_values( array_unique( $tables ) );
		}

		// SQLite fallback via sqlite_master.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$sqlite = $wpdb->get_col( "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'" );
		if ( is_array( $sqlite ) ) {
			foreach ( $sqlite as $t ) {
				$t = (string) $t;
				if ( \brverify_str_starts_with( $t, $prefix ) ) {
					$tables[] = $t;
				}
			}
		}

		sort( $tables );
		return array_values( array_unique( $tables ) );
	}

	/**
	 * Dump file header.
	 */
	private function build_header(): string {
		global $wpdb;

		$lines = array(
			'-- 007AJ Backup Restore Verifier database dump',
			'-- Format: brverify-v1',
			'-- Site: ' . home_url( '/' ),
			'-- Generated: ' . gmdate( 'c' ) . ' UTC',
			'-- WordPress: ' . get_bloginfo( 'version' ),
			'-- PHP: ' . PHP_VERSION,
			'-- Table prefix: ' . $wpdb->prefix,
			'-- Charset: ' . ( $wpdb->charset ? $wpdb->charset : 'utf8mb4' ),
			'',
			'SET NAMES utf8mb4;',
			'SET foreign_key_checks = 0;',
			'',
		);

		return implode( "\n", $lines );
	}

	/**
	 * CREATE TABLE statement for a table.
	 *
	 * @param string $table Table name.
	 */
	private function get_create_table( string $table ): string {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row( "SHOW CREATE TABLE `{$table}`", ARRAY_N );
		if ( is_array( $row ) && isset( $row[1] ) && is_string( $row[1] ) && '' !== $row[1] ) {
			return $row[1];
		}

		// SQLite: read schema from sqlite_master.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$sql = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT sql FROM sqlite_master WHERE type='table' AND name=%s",
				$table
			)
		);
		if ( is_string( $sql ) && '' !== $sql ) {
			return $sql;
		}

		return "CREATE TABLE `{$table}` (`id` bigint NOT NULL)";
	}

	/**
	 * First batch size for a table, from its average row length.
	 *
	 * @param string $table Table.
	 */
	private function initial_batch( string $table ): int {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$avg = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT AVG_ROW_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s', $table ) );
		// Table statistics can be stale or ignore off-page BLOB/TEXT data, so start small and let
		// the measured row size of each batch decide the next one.
		if ( $avg <= 0 ) {
			return self::FIRST_BATCH;
		}
		return (int) max( 1, min( self::FIRST_BATCH, (int) floor( self::BATCH_BYTES / ( 2 * $avg ) ) ) );
	}

	/**
	 * Single-column integer primary key (enables keyset paging), or null.
	 *
	 * @param string $table Table.
	 */
	private function integer_primary_key( string $table ): ?string {
		global $wpdb;
		static $cache = array();
		if ( array_key_exists( $table, $cache ) ) {
			return $cache[ $table ];
		}
		$cache[ $table ] = null;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$keys = $wpdb->get_results( "SHOW KEYS FROM `{$table}` WHERE Key_name = 'PRIMARY'", ARRAY_A );
		if ( ! is_array( $keys ) || 1 !== count( $keys ) ) {
			return null;
		}
		$col = (string) ( $keys[0]['Column_name'] ?? '' );
		if ( '' === $col ) {
			return null;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$cols = $wpdb->get_results( $wpdb->prepare( "SHOW COLUMNS FROM `{$table}` LIKE %s", $col ), ARRAY_A );
		$type = is_array( $cols ) && isset( $cols[0]['Type'] ) ? strtolower( (string) $cols[0]['Type'] ) : '';
		if ( 1 === preg_match( '/^(tiny|small|medium|big)?int/', $type ) ) {
			$cache[ $table ] = $col;
		}
		return $cache[ $table ];
	}

	/**
	 * Keyset page: rows with key > $after, ordered by key.
	 *
	 * @param string      $table Table.
	 * @param string      $key   Integer primary key column.
	 * @param string|null $after Last key seen (null = start).
	 * @param int         $limit Limit.
	 * @return list<array<string,mixed>>
	 */
	private function fetch_rows_after( string $table, string $key, ?string $after, int $limit ): array {
		global $wpdb;
		if ( null === $after ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY %i ASC LIMIT %d', $table, $key, $limit ), ARRAY_A );
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE %i > %d ORDER BY %i ASC LIMIT %d', $table, $key, (int) $after, $key, $limit ), ARRAY_A );
		}
		if ( '' !== (string) $wpdb->last_error ) {
			throw new State_Exception( esc_html( sprintf( /* translators: 1: table, 2: error */ __( 'Database read failed for table %1$s: %2$s', '007aj-backup-restore-verifier' ), $table, (string) $wpdb->last_error ) ) );
		}
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Fetch a page of rows as associative arrays.
	 *
	 * @param string $table  Table.
	 * @param int    $offset Offset.
	 * @param int    $limit  Limit.
	 * @return list<array<string,mixed>>
	 */
	private function fetch_rows( string $table, int $offset, int $limit ): array {
		global $wpdb;

		$offset = max( 0, $offset );
		$limit  = max( 1, $limit );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i LIMIT %d OFFSET %d',
				$table,
				$limit,
				$offset
			),
			ARRAY_A
		);
		if ( '' !== (string) $wpdb->last_error ) {
			throw new State_Exception( esc_html( sprintf( /* translators: 1: table, 2: error */ __( 'Database read failed for table %1$s: %2$s', '007aj-backup-restore-verifier' ), $table, (string) $wpdb->last_error ) ) );
		}

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Convert rows to INSERT statements.
	 *
	 * @param string                    $table Table.
	 * @param list<array<string,mixed>> $rows  Rows.
	 */
	private function rows_to_insert( string $table, array $rows ): string {
		global $wpdb;

		$out = '';
		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) || empty( $row ) ) {
				continue;
			}
			// Phase L: never dump the transient global job lock (restoring it stalls the restore).
			if ( $table === $wpdb->options && isset( $row['option_name'] ) && 'brverify_global_lock' === $row['option_name'] ) {
				continue;
			}
			$cols = array();
			$vals = array();
			foreach ( $row as $col => $val ) {
				$cols[] = '`' . str_replace( '`', '``', (string) $col ) . '`';
				if ( null === $val ) {
					$vals[] = 'NULL';
				} else {
					// Phase L: _real_escape() appends wpdb's per-request placeholder token for '%';
					// strip it so dumps hold a literal '%' (otherwise serialized lengths / URLs break).
					$vals[] = "'" . $wpdb->remove_placeholder_escape( $wpdb->_real_escape( (string) $val ) ) . "'";
				}
			}
			$out .= 'INSERT INTO `' . str_replace( '`', '``', $table ) . '` (' . implode( ',', $cols ) . ') VALUES (' . implode( ',', $vals ) . ");\n";
		}
		return $out;
	}
}