<?php
/**
 * SQLite information schema compatibility for WP Data Optimizer.
 *
 * @package WP_Data_Optimizer
 */

declare(strict_types=1);

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Patches the SQLite info_schema emulation table so dbDelta does not
 * re-run CREATE TABLE on every page load.
 *
 * The SQLite drop-in reads _wp_sqlite_mysql_information_schema_columns
 * to know which columns already exist. We must insert a row for each
 * column in each WPDO table immediately after dbDelta().
 *
 * Columns are inserted via INSERT OR IGNORE so the method is idempotent.
 */
class TMDO_SQLite_Compat {

	/**
	 * Patch all WPDO system tables into the SQLite info_schema.
	 */
	public static function patch_all(): void {
		if ( ! TMDO_IS_SQLITE ) {
			return;
		}

		global $wpdb;
		$p = $wpdb->prefix;

		$tables       = self::get_system_column_definitions( $p );
		$schema_table = '_wp_sqlite_mysql_information_schema_columns';

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- SQLite driver's fixed internal table name.
		foreach ( $tables as $table_name => $columns ) {
			foreach ( $columns as $pos => $col ) {
				$wpdb->query(
					$wpdb->prepare(
						"INSERT OR IGNORE INTO `{$schema_table}`
							(table_name, column_name, data_type, is_nullable, column_default, ordinal_position)
						VALUES (%s, %s, %s, %s, %s, %d)",
						$table_name,
						$col['name'],
						$col['type'],
						$col['nullable'] ? 'YES' : 'NO',
						$col['default'],
						$pos + 1
					)
				);
			}
		}
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Patch a dynamic zone table (e.g. wpdo_hot_hp_listing) into the SQLite info_schema.
	 *
	 * @param string $table_name Full table name with prefix.
	 * @param array  $columns    Array of column definitions.
	 */
	public static function patch_table( string $table_name, array $columns ): void {
		if ( ! TMDO_IS_SQLITE ) {
			return;
		}

		global $wpdb;
		$schema_table = '_wp_sqlite_mysql_information_schema_columns';

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- SQLite driver's fixed internal table name.
		foreach ( $columns as $pos => $col ) {
			$wpdb->query(
				$wpdb->prepare(
					"INSERT OR IGNORE INTO `{$schema_table}`
						(table_name, column_name, data_type, is_nullable, column_default, ordinal_position)
					VALUES (%s, %s, %s, %s, %s, %d)",
					$table_name,
					$col['name'],
					$col['type'],
					$col['nullable'] ? 'YES' : 'NO',
					$col['default'],
					$pos + 1
				)
			);
		}
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Column definitions for WPDO system tables (migrations, errors, benchmarks)
	 * and zone infrastructure tables (warm, archive).
	 *
	 * @param string $p Table prefix.
	 * @return array Table column definitions array.
	 */
	private static function get_system_column_definitions( string $p ): array {
		return array(
			"{$p}wpdo_migrations" => array(
				array(
					'name'     => 'id',
					'type'     => 'bigint',
					'nullable' => false,
					'default'  => null,
				),
				array(
					'name'     => 'module',
					'type'     => 'varchar',
					'nullable' => false,
					'default'  => '',
				),
				array(
					'name'     => 'zone',
					'type'     => 'varchar',
					'nullable' => false,
					'default'  => '',
				),
				array(
					'name'     => 'state',
					'type'     => 'varchar',
					'nullable' => false,
					'default'  => 'idle',
				),
				array(
					'name'     => 'total_rows',
					'type'     => 'bigint',
					'nullable' => false,
					'default'  => '0',
				),
				array(
					'name'     => 'processed_rows',
					'type'     => 'bigint',
					'nullable' => false,
					'default'  => '0',
				),
				array(
					'name'     => 'last_offset',
					'type'     => 'bigint',
					'nullable' => false,
					'default'  => '0',
				),
				array(
					'name'     => 'error_count',
					'type'     => 'int',
					'nullable' => false,
					'default'  => '0',
				),
				array(
					'name'     => 'started_at',
					'type'     => 'datetime',
					'nullable' => true,
					'default'  => null,
				),
				array(
					'name'     => 'completed_at',
					'type'     => 'datetime',
					'nullable' => true,
					'default'  => null,
				),
				array(
					'name'     => 'created_at',
					'type'     => 'datetime',
					'nullable' => false,
					'default'  => '0000-00-00 00:00:00',
				),
				array(
					'name'     => 'updated_at',
					'type'     => 'datetime',
					'nullable' => false,
					'default'  => '0000-00-00 00:00:00',
				),
			),
			"{$p}wpdo_errors"     => array(
				array(
					'name'     => 'id',
					'type'     => 'bigint',
					'nullable' => false,
					'default'  => null,
				),
				array(
					'name'     => 'module',
					'type'     => 'varchar',
					'nullable' => false,
					'default'  => '',
				),
				array(
					'name'     => 'zone',
					'type'     => 'varchar',
					'nullable' => false,
					'default'  => '',
				),
				array(
					'name'     => 'hook',
					'type'     => 'varchar',
					'nullable' => false,
					'default'  => '',
				),
				array(
					'name'     => 'message',
					'type'     => 'longtext',
					'nullable' => false,
					'default'  => null,
				),
				array(
					'name'     => 'context',
					'type'     => 'longtext',
					'nullable' => true,
					'default'  => null,
				),
				array(
					'name'     => 'created_at',
					'type'     => 'datetime',
					'nullable' => false,
					'default'  => '0000-00-00 00:00:00',
				),
			),
			"{$p}wpdo_benchmarks" => array(
				array(
					'name'     => 'id',
					'type'     => 'bigint',
					'nullable' => false,
					'default'  => null,
				),
				array(
					'name'     => 'module',
					'type'     => 'varchar',
					'nullable' => false,
					'default'  => '',
				),
				array(
					'name'     => 'zone',
					'type'     => 'varchar',
					'nullable' => false,
					'default'  => '',
				),
				array(
					'name'     => 'query_type',
					'type'     => 'varchar',
					'nullable' => false,
					'default'  => '',
				),
				array(
					'name'     => 'native_ms',
					'type'     => 'decimal',
					'nullable' => false,
					'default'  => '0',
				),
				array(
					'name'     => 'custom_ms',
					'type'     => 'decimal',
					'nullable' => false,
					'default'  => '0',
				),
				array(
					'name'     => 'sample_size',
					'type'     => 'int',
					'nullable' => false,
					'default'  => '0',
				),
				array(
					'name'     => 'created_at',
					'type'     => 'datetime',
					'nullable' => false,
					'default'  => '0000-00-00 00:00:00',
				),
			),
			"{$p}wpdo_warm"       => array(
				array(
					'name'     => 'id',
					'type'     => 'bigint',
					'nullable' => false,
					'default'  => null,
				),
				array(
					'name'     => 'post_id',
					'type'     => 'bigint',
					'nullable' => false,
					'default'  => '0',
				),
				array(
					'name'     => 'meta_key',
					'type'     => 'varchar',
					'nullable' => false,
					'default'  => '',
				),
				array(
					'name'     => 'meta_value',
					'type'     => 'longtext',
					'nullable' => true,
					'default'  => null,
				),
				array(
					'name'     => 'expires_at',
					'type'     => 'datetime',
					'nullable' => true,
					'default'  => null,
				),
				array(
					'name'     => 'created_at',
					'type'     => 'datetime',
					'nullable' => false,
					'default'  => '0000-00-00 00:00:00',
				),
			),
			"{$p}wpdo_archive"    => array(
				array(
					'name'     => 'id',
					'type'     => 'bigint',
					'nullable' => false,
					'default'  => null,
				),
				array(
					'name'     => 'post_id',
					'type'     => 'bigint',
					'nullable' => false,
					'default'  => '0',
				),
				array(
					'name'     => 'post_type',
					'type'     => 'varchar',
					'nullable' => false,
					'default'  => '',
				),
				array(
					'name'     => 'meta_key',
					'type'     => 'varchar',
					'nullable' => false,
					'default'  => '',
				),
				array(
					'name'     => 'meta_value',
					'type'     => 'longtext',
					'nullable' => true,
					'default'  => null,
				),
				array(
					'name'     => 'compressed',
					'type'     => 'tinyint',
					'nullable' => false,
					'default'  => '0',
				),
				array(
					'name'     => 'archived_at',
					'type'     => 'datetime',
					'nullable' => false,
					'default'  => '0000-00-00 00:00:00',
				),
				array(
					'name'     => 'original_meta_id',
					'type'     => 'bigint',
					'nullable' => false,
					'default'  => '0',
				),
			),
		);
	}
}