<?php
/**
 * Table level statements the transfer, restore and cleanup code share.
 *
 * @package Shiftora\Support\Db
 */

declare(strict_types=1);

namespace Shiftora\Support\Db;

use Shiftora\Migration\SchemaFingerprint;

// Prevent direct file access.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Every name goes through %i and every pattern through esc_like(), so a caller
 * hands over a table name and nothing else.
 */
final class TableDdl {

	/**
	 * Whether a table exists. SHOW TABLES LIKE reads a pattern, so the name is
	 * escaped or a sibling that differs at an underscore would answer for it.
	 */
	public static function exists( \wpdb $wpdb, string $table ): bool {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- server metadata, nothing to cache
		$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) );

		return is_string( $found ) && '' !== $found;
	}

	/** @return int|bool Rows affected, or false when the statement failed. */
	public static function drop( \wpdb $wpdb, string $table ): int|bool {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- the transfer reads and writes the site's own tables, a cached copy would be stale
		return $wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table ) );
	}

	/**
	 * Renames tables in one atomic statement.
	 *
	 * @param list<array{0: string, 1: string}> $pairs From and to names, in the order they apply.
	 *
	 * @return int|bool Rows affected, or false when the statement failed.
	 */
	public static function rename( \wpdb $wpdb, array $pairs ): int|bool {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- the transfer reads and writes the site's own tables, a cached copy would be stale
		return $wpdb->query(
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- one %i TO %i per pair is generated, so the sniff cannot count the names.
			$wpdb->prepare(
				'RENAME TABLE ' . implode( ', ', array_fill( 0, count( $pairs ), '%i TO %i' ) ),
				array_merge( ...$pairs )
			)
		);
	}

	/** A table's own CREATE statement, or '' when it is not there. Reads the second column; get_var() would return the name. */
	public static function create_statement( \wpdb $wpdb, string $table ): string {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- server metadata, nothing to cache
		$rows = $wpdb->get_results( $wpdb->prepare( 'SHOW CREATE TABLE %i', $table ), ARRAY_A ) ?: [];
		$row  = is_array( $rows[0] ?? null ) ? $rows[0] : [];

		return (string) ( $row['Create Table'] ?? $row[1] ?? '' );
	}

	/** Switches foreign key checks for this session; $state is the '0' or '1' the server reported. */
	public static function set_foreign_key_checks( \wpdb $wpdb, string $state ): void {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- the transfer reads and writes the site's own tables, a cached copy would be stale
		$wpdb->query( $wpdb->prepare( 'SET FOREIGN_KEY_CHECKS = %d', (int) $state ) );
	}

	/**
	 * Detaches a foreign key or check from a table, under the keyword this server
	 * accepts for it.
	 *
	 * @param string $clause The constraint's definition, which tells a foreign key from a check.
	 *
	 * @return bool Whether the statement was accepted.
	 */
	public static function drop_constraint( \wpdb $wpdb, string $table, string $name, string $clause ): bool {
		return false !== match ( SchemaFingerprint::constraint_drop_keyword( $clause, $wpdb ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- the transfer reads and writes the site's own tables, a cached copy would be stale
			'FOREIGN KEY' => $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i DROP FOREIGN KEY %i', $table, $name ) ),
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- the transfer reads and writes the site's own tables, a cached copy would be stale
			'CHECK'       => $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i DROP CHECK %i', $table, $name ) ),
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- the transfer reads and writes the site's own tables, a cached copy would be stale
			default       => $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i DROP CONSTRAINT %i', $table, $name ) ),
		};
	}

	/**
	 * Adds a constraint clause taken from a CREATE TABLE.
	 *
	 * @return bool Whether the statement was accepted.
	 */
	public static function add_constraint( \wpdb $wpdb, string $table, string $clause ): bool {
		// A clause can hold a '%' (a CHECK with a LIKE), so it must not pass through prepare().
		$sql = 'ALTER TABLE ' . SqlLiteral::identifier( $table ) . ' ADD ' . $clause;

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery -- the clause is part of a CREATE TABLE already allow-listed or written by this plugin; DDL text cannot be bound; the transfer reads and writes the site's own tables, a cached copy would be stale
		return false !== $wpdb->query( $sql );
	}

	/**
	 * Adds a column, with the definition this plugin wrote for it.
	 *
	 * @return bool Whether the statement was accepted.
	 */
	public static function add_column( \wpdb $wpdb, string $table, string $column, string $definition ): bool {
		$sql = 'ALTER TABLE ' . SqlLiteral::identifier( $table ) . ' ADD COLUMN ' . SqlLiteral::identifier( $column ) . ' ' . $definition;

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery -- the definition is DDL text written in this plugin and takes no placeholder; the transfer reads and writes the site's own tables, a cached copy would be stale
		return false !== $wpdb->query( $sql );
	}

	/**
	 * Redefines a column, with the definition this plugin wrote for it.
	 *
	 * @return bool Whether the statement was accepted.
	 */
	public static function modify_column( \wpdb $wpdb, string $table, string $column, string $definition ): bool {
		$sql = 'ALTER TABLE ' . SqlLiteral::identifier( $table ) . ' MODIFY COLUMN ' . SqlLiteral::identifier( $column ) . ' ' . $definition;

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery -- the definition is DDL text written in this plugin and takes no placeholder; the transfer reads and writes the site's own tables, a cached copy would be stale
		return false !== $wpdb->query( $sql );
	}

	/** @return bool Whether the statement was accepted. */
	public static function drop_column( \wpdb $wpdb, string $table, string $column ): bool {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- the transfer reads and writes the site's own tables, a cached copy would be stale
		return false !== $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i DROP COLUMN %i', $table, $column ) );
	}

	/** @return bool Whether the statement was accepted. */
	public static function drop_index( \wpdb $wpdb, string $table, string $index ): bool {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- the transfer reads and writes the site's own tables, a cached copy would be stale
		return false !== $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i DROP INDEX %i', $table, $index ) );
	}

	/**
	 * @param list<string> $columns
	 *
	 * @return bool Whether the statement was accepted.
	 */
	public static function add_unique_key( \wpdb $wpdb, string $table, string $name, array $columns ): bool {
		$sql = 'ALTER TABLE ' . SqlLiteral::identifier( $table ) . ' ADD UNIQUE KEY ' . SqlLiteral::identifier( $name ) . ' (' . SqlLiteral::identifiers( $columns ) . ')';

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery -- the column list is quoted name by name by SqlLiteral; the transfer reads and writes the site's own tables, a cached copy would be stale
		return false !== $wpdb->query( $sql );
	}

	/** The rows of SHOW INDEX for a table; empty when it cannot be read, so check last_error. */
	public static function indexes( \wpdb $wpdb, string $table ): array {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- server metadata, nothing to cache
		return (array) $wpdb->get_results( $wpdb->prepare( 'SHOW INDEX FROM %i', $table ) );
	}
}