<?php
/**
 * Migration #005 - stem-frequency table.
 *
 * @package Postlinkly
 * @since   1.10.0
 */

declare(strict_types=1);

namespace Postlinkly\Bootstrap\Migrations;

use Postlinkly\Database\TableNames;

/**
 * Creates the corpus stem document-frequency table.
 *
 * @since 1.10.0
 */
final class Migration_005_AddStemFrequencyTable implements MigrationInterface {

	/**
	 * @since 1.10.0
	 */
	public function get_version(): int {
		return 5;
	}

	/**
	 * @since 1.10.0
	 *
	 * @param \wpdb $wpdb WordPress database instance.
	 */
	public function run( \wpdb $wpdb ): bool {
		if ( ! function_exists( 'dbDelta' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		}
		\dbDelta( $this->schema_sql( $wpdb->get_charset_collate() ) );

		return $this->table_exists( $wpdb, $wpdb->prefix . TableNames::STEM_FREQUENCY );
	}

	/**
	 * Checks whether a table exists.
	 *
	 * `dbDelta()` does not report success or failure directly, so this is
	 * the only reliable signal that the `CREATE TABLE` actually took effect.
	 *
	 * @since 1.10.0
	 *
	 * @param \wpdb  $wpdb  Database instance.
	 * @param string $table Fully prefixed table name.
	 *
	 * @return bool
	 */
	private function table_exists( \wpdb $wpdb, string $table ): bool {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );

		return $table === $found;
	}

	/**
	 * Returns the table creation SQL.
	 *
	 * `stem` is the primary key directly (no surrogate id) since every
	 * lookup and write is keyed by stem. 190 characters keeps the primary
	 * key index within the 767-byte prefix limit older MySQL/InnoDB
	 * enforces under utf8mb4 (4 bytes/char): 190 * 4 = 760 bytes, versus
	 * 255 * 4 = 1020 bytes, which would exceed it. The existing
	 * `target_keywords` and `urls` tables index long text columns with a
	 * `(191)` prefix for the same reason; here the column itself is bounded
	 * instead because the whole value — not just an index prefix — has to
	 * be usable as the batched lookup key.
	 *
	 * @since 1.10.0
	 * @since 1.18.3 Added the $table parameter, so the rebuild's staging table
	 *        is created from this same definition.
	 *
	 * @param string $collate Charset/collation clause.
	 * @param string $table   Optional. Fully prefixed table name. Default the
	 *                        live stem-frequency table.
	 */
	public function schema_sql( string $collate, string $table = '' ): string {
		$table = '' !== $table ? $table : TableNames::stem_frequency();

		return "CREATE TABLE {$table} (
			stem varchar(190) NOT NULL,
			doc_count bigint(20) unsigned NOT NULL DEFAULT 0,
			PRIMARY KEY  (stem)
		) {$collate};";
	}
}