<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * MYAIO_DB_Backup_Optimized
 *
 * Optimized database backup with chunked processing to prevent timeouts.
 * Key improvements:
 * - Stateful resumable backups (can continue across multiple requests)
 * - Time-based execution limits
 * - Streaming writes (no full file reads on append)
 * - Skip large tables option
 * - Progress tracking
 */
class MYAIO_DB_Backup_Optimized {

	private static function init_filesystem() {
		if ( ! function_exists( 'WP_Filesystem' ) ) {
			require_once ABSPATH . 'wp-admin/includes/file.php';
		}

		WP_Filesystem();
		global $wp_filesystem;

		if ( ! $wp_filesystem ) {
			throw new Exception( 'WP_Filesystem could not be initialized.' );
		}
	}

	private static function validate_table_name( $table, array $allowed_tables ) {
		$table = (string) $table;

		if ( '' === $table || ! preg_match( '/^[A-Za-z0-9_]+$/', $table ) ) {
			throw new Exception( 'Invalid table name encountered during backup.' );
		}

		if ( ! isset( $allowed_tables[ $table ] ) ) {
			throw new Exception( 'Unexpected table name encountered during backup.' );
		}

		return $table;
	}

	/**
	 * Get backup state from options
	 */
	private static function get_state( $run_id ) {
		$key = 'myaio_db_backup_state_' . md5( $run_id );
		return get_option( $key, null );
	}

	/**
	 * Save backup state to options
	 */
	private static function save_state( $run_id, $state ) {
		$key = 'myaio_db_backup_state_' . md5( $run_id );
		update_option( $key, $state, false );
	}

	/**
	 * Clear backup state
	 */
	private static function clear_state( $run_id ) {
		$key = 'myaio_db_backup_state_' . md5( $run_id );
		delete_option( $key );
	}

	/**
	 * Clean up old backup files in the destination directory
	 *
	 * @param string $dest_dir Backup directory path
	 * @param string $exclude_file Optional file to exclude from deletion
	 */
	private static function cleanup_old_backups( $dest_dir, $exclude_file = '' ) {
		if ( ! is_dir( $dest_dir ) ) {
			return;
		}

		// Get all .sql and .sql.gz files
		$pattern = trailingslashit( $dest_dir ) . '*.sql*';
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_glob
		$files = glob( $pattern );

		if ( ! is_array( $files ) ) {
			return;
		}

		$exclude_basename = $exclude_file ? basename( $exclude_file ) : '';

		foreach ( $files as $file ) {
			// Skip if this is the file we want to keep
			if ( $exclude_basename && basename( $file ) === $exclude_basename ) {
				continue;
			}

			// Skip if not a file
			if ( ! is_file( $file ) ) {
				continue;
			}

			// Delete old backup file
			// phpcs:ignore WordPress.WP.AlternativeFunctions.unlink_unlink
			@unlink( $file );
		}
	}

	/**
	 * Optimized append using fopen/fwrite (much faster than reading entire file)
	 */
	private static function append_direct( $path, $content ) {
		// Use direct file operations for performance (WP_Filesystem is too slow for streaming)
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
		$handle = @fopen( $path, 'a' );
		if ( ! $handle ) {
			throw new Exception( 'Cannot open backup file for writing.' );
		}

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
		$written = @fwrite( $handle, $content );
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
		@fclose( $handle );

		if ( false === $written ) {
			throw new Exception( 'Failed to write to backup file.' );
		}
	}

	/**
	 * Main backup method with time-based chunking
	 *
	 * @param string $dest_dir Destination directory
	 * @param string $run_id Unique run identifier
	 * @param array $options Options array:
	 *   - batch_size: Rows per batch (default: 1000)
	 *   - max_execution_time: Max seconds to run (default: 180)
	 *   - skip_large_tables: Skip tables over X MB (default: 0 = no skip)
	 *   - resume: Whether to resume from previous state (default: true)
	 * @return array Status with 'complete', 'progress', 'name', 'path', 'bytes'
	 */
	public static function run( $dest_dir, $run_id, $options = array() ) {
		global $wpdb;

		// Parse options
		$batch_size         = isset( $options['batch_size'] ) ? max( 100, (int) $options['batch_size'] ) : 1000;
		$max_execution_time = isset( $options['max_execution_time'] ) ? (int) $options['max_execution_time'] : 180;
		$skip_large_tables  = isset( $options['skip_large_tables'] ) ? (int) $options['skip_large_tables'] : 0;
		$resume             = isset( $options['resume'] ) ? (bool) $options['resume'] : true;

		$start_time = microtime( true );

		self::init_filesystem();

		$dest_dir = (string) $dest_dir;
		$run_id   = (string) $run_id;

		if ( ! is_dir( $dest_dir ) && ! wp_mkdir_p( $dest_dir ) ) {
			throw new Exception( 'Cannot create DB backup directory.' );
		}

		// Get or initialize state
		$state = $resume ? self::get_state( $run_id ) : null;

		if ( ! $state ) {
			// Initialize new backup
			$ts       = time();
			$safe_id  = preg_replace( '/[^a-zA-Z0-9_\-]/', '', $run_id );
			$filename = 'db_' . $safe_id . '_' . $ts . '.sql';
			$path     = trailingslashit( $dest_dir ) . $filename;

			// Clean up old backup files before creating new one
			self::cleanup_old_backups( $dest_dir, $filename );

			// Create empty file
			// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_touch
			if ( ! @touch( $path ) ) {
				throw new Exception( 'Cannot create DB dump file.' );
			}

			// Get all tables
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$tables = $wpdb->get_col( 'SHOW TABLES' );

			if ( empty( $tables ) ) {
				throw new Exception( 'No tables found or insufficient DB permissions.' );
			}

			// Build whitelist
			$safe_table_map = array();
			foreach ( (array) $tables as $t ) {
				if ( is_string( $t ) && '' !== $t ) {
					$safe_table_map[ $t ] = true;
				}
			}

			// Write SQL header
			$header = "-- MYAIO DB Backup\n";
			$header .= '-- Site: ' . home_url( '/' ) . "\n";
			$header .= '-- Generated: ' . gmdate( 'c' ) . "\n";
			$header .= "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\n\n";
			$header .= "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n";
			$header .= "SET time_zone='+00:00';\n";
			$header .= "SET foreign_key_checks = 0;\n\n";

			self::append_direct( $path, $header );

			$state = array(
				'filename'       => $filename,
				'path'           => $path,
				'tables'         => array_keys( $safe_table_map ),
				'safe_table_map' => $safe_table_map,
				'current_table'  => 0,
				'current_offset' => 0,
				'complete'       => false,
				'started_at'     => $start_time,
			);

			self::save_state( $run_id, $state );
		}

		$path           = $state['path'];
		$tables         = $state['tables'];
		$safe_table_map = $state['safe_table_map'];
		$current_table  = $state['current_table'];
		$current_offset = $state['current_offset'];

		// Set transaction isolation to READ UNCOMMITTED to avoid table locks
		// This allows the backup to read data without being blocked by write operations
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query( 'SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED' );

		// Process tables until time limit
		while ( $current_table < count( $tables ) ) {
			// Check time limit
			$elapsed = microtime( true ) - $start_time;
			if ( $elapsed >= $max_execution_time ) {
				// Save state and return
				$state['current_table']  = $current_table;
				$state['current_offset'] = $current_offset;
				self::save_state( $run_id, $state );

				return array(
					'complete' => false,
					'progress' => round( ( $current_table / count( $tables ) ) * 100, 1 ),
					'name'     => $state['filename'],
					'path'     => $path,
					'bytes'    => file_exists( $path ) ? filesize( $path ) : 0,
					'message'  => sprintf( 'Processing table %d of %d', $current_table + 1, count( $tables ) ),
				);
			}

			$table_raw = $tables[ $current_table ];
			$table     = self::validate_table_name( $table_raw, $safe_table_map );
			$table_q   = '`' . $table . '`';

			// If starting new table (offset = 0), write schema
			if ( 0 === $current_offset ) {
				// Check table size if skip_large_tables is set
				if ( $skip_large_tables > 0 ) {
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$table_status = $wpdb->get_row( $wpdb->prepare( 'SHOW TABLE STATUS LIKE %s', $table ), ARRAY_A );
					if ( $table_status ) {
						$table_size_mb = ( (int) $table_status['Data_length'] + (int) $table_status['Index_length'] ) / 1024 / 1024;
						if ( $table_size_mb > $skip_large_tables ) {
							// Skip this table
							self::append_direct( $path, "\n-- Skipped large table: {$table_q} (" . round( $table_size_mb, 2 ) . " MB)\n\n" );
							$current_table++;
							$current_offset = 0;
							continue;
						}
					}
				}

				self::append_direct( $path, "\n-- --------------------------------------------------------\n" );
				self::append_direct( $path, '-- Table structure for ' . $table_q . "\n" );
				self::append_direct( $path, "-- --------------------------------------------------------\n" );

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$create = $wpdb->get_row( "SHOW CREATE TABLE {$table_q}", ARRAY_N );

				if ( ! $create || empty( $create[1] ) ) {
					throw new Exception( 'Failed to read schema for table: ' . esc_html( $table ) );
				}

				self::append_direct( $path, "DROP TABLE IF EXISTS {$table_q};\n" );
				self::append_direct( $path, $create[1] . ";\n\n" );
				self::append_direct( $path, '-- Dumping data for ' . $table_q . "\n" );
			}

			// Get row count
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table_q}" );

			if ( $count <= 0 ) {
				if ( 0 === $current_offset ) {
					self::append_direct( $path, "-- (no rows)\n" );
				}
				$current_table++;
				$current_offset = 0;
				continue;
			}

			// Process rows in batches
			if ( $current_offset < $count ) {
				$sql = 'SELECT * FROM ' . $table_q . ' LIMIT %d OFFSET %d';

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$rows = $wpdb->get_results(
					// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
					$wpdb->prepare( $sql, $batch_size, $current_offset ),
					ARRAY_A
				);

				if ( ! empty( $rows ) ) {
					$cols    = array_keys( $rows[0] );
					$col_sql = '`' . implode( '`,`', array_map( 'sanitize_key', $cols ) ) . '`';

					// Store row count before processing (needed for offset calculation)
					$rows_count = count( $rows );

					// Build INSERT statements in smaller chunks to avoid memory issues
					$chunk_size = 50; // Process 50 rows at a time
					$row_chunks = array_chunk( $rows, $chunk_size );
					
					foreach ( $row_chunks as $chunk ) {
						$values_sql = array();
						
						foreach ( $chunk as $row ) {
							$vals = array();
							foreach ( $cols as $c ) {
								$vals[] = self::sql_value( $wpdb, $row[ $c ] ?? null );
							}
							$values_sql[] = '(' . implode( ',', $vals ) . ')';
						}

						$insert_sql = "INSERT INTO {$table_q} ({$col_sql}) VALUES\n" . implode( ",\n", $values_sql ) . ";\n\n";
						self::append_direct( $path, $insert_sql );
						
						// Free memory after each chunk
						unset( $values_sql, $insert_sql );
					}
					
					// Free memory after processing all chunks
					unset( $rows, $row_chunks );

					// Use stored count (rows variable is now unset)
					$current_offset += $rows_count;

					// Check time limit after processing this batch
					$elapsed = microtime( true ) - $start_time;
					if ( $elapsed >= $max_execution_time ) {
						// Save state and return (will resume from current offset in this table)
						$state['current_table']  = $current_table;
						$state['current_offset'] = $current_offset;
						self::save_state( $run_id, $state );

						return array(
							'complete' => false,
							'progress' => round( ( $current_table / count( $tables ) ) * 100, 1 ),
							'name'     => $state['filename'],
							'path'     => $path,
							'bytes'    => file_exists( $path ) ? filesize( $path ) : 0,
							'message'  => sprintf( 'Processing table %d of %d (%d rows)', $current_table + 1, count( $tables ), $current_offset ),
						);
					}
				} else {
					// No more rows
					$current_table++;
					$current_offset = 0;
				}
			} else {
				// Done with this table
				$current_table++;
				$current_offset = 0;
			}
		}

		// All tables processed
		self::append_direct( $path, "\nSET foreign_key_checks = 1;\n" );

		$bytes = file_exists( $path ) ? filesize( $path ) : 0;

		// Clear state
		self::clear_state( $run_id );

		return array(
			'complete' => true,
			'progress' => 100,
			'name'     => $state['filename'],
			'path'     => $path,
			'bytes'    => $bytes,
			'message'  => 'Backup complete',
		);
	}

	/**
	 * Zip the SQL backup file
	 *
	 * @param string $sql_path Path to the SQL file
	 * @return array Result with 'success', 'zip_path', 'zip_name', 'message'
	 */
	public static function zip_backup( $sql_path ) {
		if ( ! file_exists( $sql_path ) ) {
			return array(
				'success' => false,
				'message' => 'SQL file not found',
			);
		}

		// Check if ZipArchive is available
		if ( ! class_exists( 'ZipArchive' ) ) {
			return array(
				'success' => false,
				'message' => 'ZipArchive class not available',
			);
		}

		$zip_path = $sql_path . '.zip';
		$sql_name = basename( $sql_path );

		$zip = new ZipArchive();
		$result = $zip->open( $zip_path, ZipArchive::CREATE | ZipArchive::OVERWRITE );

		if ( true !== $result ) {
			return array(
				'success' => false,
				'message' => 'Failed to create zip file',
			);
		}

		// Add SQL file to zip
		if ( ! $zip->addFile( $sql_path, $sql_name ) ) {
			$zip->close();
			return array(
				'success' => false,
				'message' => 'Failed to add SQL file to zip',
			);
		}

		$zip->close();

		// Verify zip was created
		if ( ! file_exists( $zip_path ) ) {
			return array(
				'success' => false,
				'message' => 'Zip file was not created',
			);
		}

		// Delete original SQL file
		// phpcs:ignore WordPress.WP.AlternativeFunctions.unlink_unlink
		@unlink( $sql_path );

		return array(
			'success'  => true,
			'zip_path' => $zip_path,
			'zip_name' => basename( $zip_path ),
			'message'  => 'SQL file zipped successfully',
		);
	}

	private static function sql_value( $wpdb, $value ) {
		if ( is_null( $value ) ) {
			return 'NULL';
		}

		if ( is_bool( $value ) ) {
			return $value ? '1' : '0';
		}

		if ( is_int( $value ) || is_float( $value ) || ( is_string( $value ) && is_numeric( $value ) && preg_match( '/^-?\d+(\.\d+)?$/', $value ) ) ) {
			return (string) $value;
		}

		$raw      = (string) $value;
		$prepared = $wpdb->prepare( '%s', $raw );
		$escaped  = substr( $prepared, 1, -1 );

		return "'" . $escaped . "'";
	}

	/**
	 * Quick method: Run backup with sensible defaults for small-medium sites
	 */
	public static function run_quick( $dest_dir, $run_id ) {
		return self::run(
			$dest_dir,
			$run_id,
			array(
				'batch_size'         => 500,  // Reduced from 1000 for better memory usage
				'max_execution_time' => 180,  // 3 minutes for larger tables
				'skip_large_tables'  => 0,
				'resume'             => true,
			)
		);
	}

	/**
	 * Safe method: For large sites or shared hosting with strict limits
	 */
	public static function run_safe( $dest_dir, $run_id ) {
		return self::run(
			$dest_dir,
			$run_id,
			array(
				'batch_size'         => 250,  // Reduced from 500 for better memory usage
				'max_execution_time' => 180,  // 3 minutes for larger tables
				'skip_large_tables'  => 100, // Skip tables over 100MB
				'resume'             => true,
			)
		);
	}
}
