<?php
/**
 * Custom table definitions and migrations.
 *
 * @package Unbreak
 */

namespace Unbreak\Install;

defined( 'ABSPATH' ) || exit;

/**
 * Creates and updates the plugin's custom tables with dbDelta.
 */
final class Schema {

	/**
	 * Current schema version. Bump it whenever a table definition changes and add a migration step.
	 */
	public const VERSION = 3;

	/**
	 * Option that stores the installed schema version.
	 */
	public const OPTION = 'unbreak_db_version';

	/**
	 * Table name suffixes (without prefix).
	 */
	public const TABLES = array( 'links', 'instances', 'redirects', 'log_404' );

	/**
	 * Full table name for a suffix, using the current site's prefix.
	 *
	 * @param string $name Table suffix, e.g. "links".
	 */
	public static function table( string $name ): string {
		global $wpdb;
		return $wpdb->prefix . 'unbreak_' . $name;
	}

	/**
	 * Install or update the tables when the stored version differs from the code version.
	 *
	 * Cheap to call on every request: the version option is autoloaded.
	 */
	public static function maybe_update_schema(): void {
		$installed = (int) get_option( self::OPTION, 0 );
		if ( self::VERSION !== $installed ) {
			self::install( $installed );
		}
	}

	/**
	 * Run dbDelta for every table, then version-specific migrations. Idempotent.
	 *
	 * @param int $from Previously installed schema version (0 = fresh install).
	 */
	public static function install( int $from = 0 ): void {
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		foreach ( self::definitions() as $sql ) {
			dbDelta( $sql );
		}

		self::migrate( $from );

		update_option( self::OPTION, self::VERSION, true );
	}

	/**
	 * Version-specific data migrations that dbDelta cannot express.
	 *
	 * Each step must be idempotent. Steps run for every version greater than $from.
	 *
	 * @param int $from Previously installed schema version.
	 */
	private static function migrate( int $from ): void {
		foreach ( self::migration_steps() as $version => $callback ) {
			if ( $version > $from && is_callable( $callback ) ) {
				$callback();
			}
		}
	}

	/**
	 * Migration callbacks keyed by the schema version that introduced them.
	 * Version 1 is the initial schema; version 3 only added links.last_result (dbDelta adds it, no data to move).
	 *
	 * @return array<int,callable>
	 */
	private static function migration_steps(): array {
		return array(
			2 => array( self::class, 'migrate_2_host_buckets' ),
		);
	}

	/**
	 * Version 2 added links.host_bucket (used to partition hosts between check lanes). Backfill it in PHP
	 * so the migration does not depend on MySQL-only functions.
	 */
	public static function migrate_2_host_buckets(): void {
		global $wpdb;
		$table = self::table( 'links' );
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- One-off migration on a plugin table.
		$hosts = (array) $wpdb->get_col( $wpdb->prepare( 'SELECT DISTINCT host FROM %i WHERE host_bucket = 0', $table ) );
		foreach ( $hosts as $host ) {
			$bucket = \Unbreak\Support\Url::host_bucket( (string) $host );
			if ( 0 !== $bucket ) {
				$wpdb->query( $wpdb->prepare( 'UPDATE %i SET host_bucket = %d WHERE host = %s', $table, $bucket, $host ) );
			}
		}
		// phpcs:enable
	}

	/**
	 * CREATE TABLE statements in the exact format dbDelta expects.
	 *
	 * @return array<string,string>
	 */
	public static function definitions(): array {
		global $wpdb;
		$collate = $wpdb->get_charset_collate();

		$links     = self::table( 'links' );
		$instances = self::table( 'instances' );
		$redirects = self::table( 'redirects' );
		$log404    = self::table( 'log_404' );

		return array(
			'links'     => "CREATE TABLE {$links} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  url text NOT NULL,
  url_hash char(40) NOT NULL,
  host varchar(191) NOT NULL DEFAULT '',
  host_bucket tinyint(3) unsigned NOT NULL DEFAULT 0,
  is_internal tinyint(1) NOT NULL DEFAULT 0,
  status varchar(20) NOT NULL DEFAULT 'pending',
  last_result varchar(20) DEFAULT NULL,
  http_code smallint(5) DEFAULT NULL,
  final_url text NULL,
  redirect_count tinyint(3) unsigned NOT NULL DEFAULT 0,
  error_message varchar(255) DEFAULT NULL,
  response_ms int(10) unsigned DEFAULT NULL,
  first_failure_at datetime DEFAULT NULL,
  last_checked_at datetime DEFAULT NULL,
  next_check_at datetime DEFAULT NULL,
  status_changed_at datetime DEFAULT NULL,
  fail_count smallint(5) unsigned NOT NULL DEFAULT 0,
  dismissed tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY  (id),
  UNIQUE KEY url_hash (url_hash),
  KEY host (host),
  KEY bucket_due (host_bucket,next_check_at),
  KEY status (status),
  KEY last_checked_at (last_checked_at),
  KEY next_check_at (next_check_at)
) {$collate};",
			'instances' => "CREATE TABLE {$instances} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  link_id bigint(20) unsigned NOT NULL,
  source_type varchar(30) NOT NULL,
  source_id bigint(20) unsigned NOT NULL DEFAULT 0,
  source_key varchar(191) DEFAULT NULL,
  anchor_text varchar(255) DEFAULT NULL,
  element varchar(10) NOT NULL DEFAULT 'a',
  context text NULL,
  PRIMARY KEY  (id),
  KEY link_id (link_id),
  KEY source_id (source_id,source_type)
) {$collate};",
			'redirects' => "CREATE TABLE {$redirects} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  source_path varchar(2048) NOT NULL,
  source_hash char(40) NOT NULL,
  target_url text NOT NULL,
  code smallint(3) unsigned NOT NULL DEFAULT 301,
  match_mode varchar(10) NOT NULL DEFAULT '404',
  hits bigint(20) unsigned NOT NULL DEFAULT 0,
  last_hit_at datetime DEFAULT NULL,
  created_at datetime NOT NULL,
  enabled tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY  (id),
  UNIQUE KEY source_hash (source_hash),
  KEY enabled (enabled)
) {$collate};",
			'log_404'   => "CREATE TABLE {$log404} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  path varchar(2048) NOT NULL,
  path_hash char(40) NOT NULL,
  referrer varchar(2048) DEFAULT NULL,
  hits bigint(20) unsigned NOT NULL DEFAULT 0,
  first_seen datetime NOT NULL,
  last_seen datetime NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY path_hash (path_hash),
  KEY hits (hits),
  KEY last_seen (last_seen)
) {$collate};",
		);
	}

	/**
	 * Drop every plugin table for the current site.
	 */
	public static function drop_all(): void {
		global $wpdb;
		foreach ( self::TABLES as $name ) {
			$table = self::table( $name );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Dropping the plugin's own tables on uninstall.
			$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table ) );
		}
		delete_option( self::OPTION );
	}
}