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

/**
 * MYAIO_DB_Backup
 *
 * Creates a SQL dump (schema + data) using SHOW CREATE TABLE + INSERT batches.
 * WordPress.org compliant: uses WP_Filesystem for file writes.
 */
class MYAIO_DB_Backup {

	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 ) {
			// Keep exception messages plain (escape when outputting in UI).
			throw new Exception( 'WP_Filesystem could not be initialized.' );
		}
	}

	/**
	 * Validate a table name using:
	 * 1) strict regex
	 * 2) whitelist from SHOW TABLES
	 *
	 * @param string $table Raw table name.
	 * @param array  $allowed_tables Map of allowed tables [table => true].
	 * @return string Safe raw table name (unquoted).
	 * @throws Exception
	 */
	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;
	}

	private static function estimate_db_bytes() {
		global $wpdb;

		// Backup export requires direct DB metadata query (no caching appropriate here).
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results( 'SHOW TABLE STATUS', ARRAY_A );

		$total = 0;
		if ( is_array( $rows ) ) {
			foreach ( $rows as $r ) {
				$total += (int) ( $r['Data_length'] ?? 0 );
				$total += (int) ( $r['Index_length'] ?? 0 );
			}
		}

		return (int) ( $total * 1.2 );
	}

	private static function ensure_free_space_or_throw( $dest_dir, $needed_bytes ) {
		$free = disk_free_space( $dest_dir );
		if ( false === $free ) {
			return;
		}

		$buffer = defined( 'MYAIO_DISK_SPACE_BUFFER' ) ? (int) MYAIO_DISK_SPACE_BUFFER : ( 50 * 1024 * 1024 );

		if ( $free < ( (int) $needed_bytes + $buffer ) ) {
			throw new Exception( 'Your server does not have enough free disk space to create the database backup.' );
		}
	}

	/**
	 * 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
			wp_delete_file( $file );
		}
	}

	public static function run( $dest_dir, $run_id, $batch_size = 500 ) {
		global $wpdb, $wp_filesystem;

		self::init_filesystem();

		$dest_dir   = (string) $dest_dir;
		$run_id     = (string) $run_id;
		$batch_size = max( 1, (int) $batch_size );

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

		$estimated = self::estimate_db_bytes();
		self::ensure_free_space_or_throw( $dest_dir, $estimated );

		$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 );

		if ( ! $wp_filesystem->put_contents( $path, '', FS_CHMOD_FILE ) ) {
			throw new Exception( 'Cannot create DB dump file.' );
		}

		// Backup export requires direct schema query.
		// 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 for SHOW TABLES.' );
		}

		// Build whitelist map to satisfy PluginCheck identifier safety concerns.
		$safe_table_map = array();
		foreach ( (array) $tables as $t ) {
			if ( is_string( $t ) && '' !== $t ) {
				$safe_table_map[ $t ] = true;
			}
		}

		self::append( $path, "-- MYAIO DB Backup\n" );
		self::append( $path, '-- Site: ' . home_url( '/' ) . "\n" );
		self::append( $path, '-- Generated: ' . gmdate( 'c' ) . "\n" );
		self::append( $path, "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n" );
		self::append( $path, "SET time_zone='+00:00';\n" );
		self::append( $path, "SET foreign_key_checks = 0;\n\n" );

		foreach ( $tables as $table_raw ) {

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

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

			// Identifiers cannot be passed via placeholders. We whitelist table names from SHOW TABLES + regex validate.
			// 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 a table during backup.' );
			}

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

			// 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 ) {
				self::append( $path, "-- (no rows)\n" );
				continue;
			}

			$offset = 0;

			while ( $offset < $count ) {

				// LIMIT/OFFSET are prepared; table identifier is validated/whitelisted and cannot be placeholdered.
        $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 -- Placeholders used for LIMIT/OFFSET; identifier validated/whitelisted; export requires direct read.
        $rows = $wpdb->get_results(
          // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Placeholders used for LIMIT/OFFSET; identifier validated/whitelisted; export requires direct read.
          $wpdb->prepare( $sql, $batch_size, $offset ),
          ARRAY_A
        );

				if ( empty( $rows ) ) {
					break;
				}

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

				$values_sql = array();

				foreach ( $rows as $row ) {
					$vals = array();
					foreach ( $cols as $c ) {
						$vals[] = self::sql_value( $wpdb, $row[ $c ] ?? null );
					}
					$values_sql[] = '(' . implode( ',', $vals ) . ')';
				}

				$sql = "INSERT INTO {$table_q} ({$col_sql}) VALUES\n" . implode( ",\n", $values_sql ) . ";\n\n";
				self::append( $path, $sql );

				$offset += count( $rows );
			}
		}

		self::append( $path, "\nSET foreign_key_checks = 1;\n" );

		$bytes = 0;
		if ( $wp_filesystem->exists( $path ) ) {
			$bytes = (int) $wp_filesystem->size( $path );
		}

		return array(
			'name'  => $filename,
			'path'  => $path,
			'bytes' => $bytes,
		);
	}

	private static function append( $path, $content ) {
		global $wp_filesystem;

		$existing = '';
		if ( $wp_filesystem->exists( $path ) ) {
			$existing = (string) $wp_filesystem->get_contents( $path );
		}

		$wp_filesystem->put_contents(
			$path,
			$existing . (string) $content,
			FS_CHMOD_FILE
		);
	}

	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 ); // strip surrounding quotes.

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