<?php
/**
 * Schema and migration versioning.
 *
 * @package Carticy\Conversions\Storage
 */

declare(strict_types=1);

namespace Carticy\Conversions\Storage;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema installer/migrator for the plugin-owned dispatch table (carticy_conversion_dispatches). CREATE/ALTER/DROP/SHOW INDEX on a custom table have no WordPress abstraction and run only at activation and migration; the trusted prefixed table name is interpolated, with line-level PreparedSQL notes.
/**
 * Installs storage primitives for the current schema version.
 */
final class SchemaManager {

	public const CURRENT_DB_VERSION = '4';
	public const DB_VERSION_OPTION  = 'carticy_conversions_db_version';
	public const DISPATCH_TABLE     = 'carticy_conversion_dispatches';

	/**
	 * Install or migrate storage.
	 *
	 * Order-bound snapshots live in WooCommerce order meta, while dispatch
	 * claims use a unique-key custom table so provider workers can claim work
	 * atomically.
	 */
	public static function install(): void {
		$stored_version = get_option( self::DB_VERSION_OPTION, false );

		if ( false === $stored_version ) {
			add_option( self::DB_VERSION_OPTION, '0' );
			$stored_version = '0';
		}

		if ( version_compare( (string) $stored_version, self::CURRENT_DB_VERSION, '<' ) ) {
			self::install_dispatch_table();
			update_option( self::DB_VERSION_OPTION, self::CURRENT_DB_VERSION );
		}
	}

	/**
	 * Get the dispatch claim table name.
	 *
	 * @return string
	 */
	public static function dispatch_table_name(): string {
		global $wpdb;

		$prefix = isset( $wpdb ) && is_object( $wpdb ) && isset( $wpdb->prefix ) ? (string) $wpdb->prefix : '';
		return $prefix . self::DISPATCH_TABLE;
	}

	/**
	 * Install the atomic dispatch claim table when WordPress DB APIs are present.
	 */
	private static function install_dispatch_table(): void {
		global $wpdb;

		if ( ! isset( $wpdb ) || ! is_object( $wpdb ) || ! method_exists( $wpdb, 'get_charset_collate' ) ) {
			return;
		}

		$table_name      = self::dispatch_table_name();
		$charset_collate = $wpdb->get_charset_collate();
		$sql             = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			order_id bigint(20) unsigned NOT NULL,
			event_type varchar(32) NOT NULL,
			destination varchar(64) NOT NULL,
			idempotency_key varchar(191) NOT NULL,
			event_id varchar(191) NOT NULL DEFAULT '',
			status varchar(32) NOT NULL DEFAULT 'pending',
			attempts int(10) unsigned NOT NULL DEFAULT 0,
			next_attempt_at datetime DEFAULT NULL,
			last_attempt_at datetime DEFAULT NULL,
			last_http_status int(10) unsigned DEFAULT NULL,
			last_error_code varchar(64) NOT NULL DEFAULT '',
			last_error_message varchar(500) NOT NULL DEFAULT '',
			response_summary varchar(500) NOT NULL DEFAULT '',
			payload_hash varchar(64) NOT NULL DEFAULT '',
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY dispatch_key (idempotency_key),
			KEY event_destination (order_id,event_type,destination),
			KEY status_updated (status,updated_at),
			KEY queue_due (status,next_attempt_at,updated_at)
		) {$charset_collate};";

		if ( ! function_exists( 'dbDelta' ) && defined( 'ABSPATH' ) && file_exists( ABSPATH . 'wp-admin/includes/upgrade.php' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		}

		if ( function_exists( 'dbDelta' ) ) {
			self::ensure_dispatch_indexes( $table_name );
			dbDelta( $sql );
			self::ensure_dispatch_indexes( $table_name );
		}
	}

	/**
	 * Keep the dispatch claim index on the idempotency key so repeatable event
	 * instances, such as partial refunds, can be claimed independently.
	 *
	 * @param string $table_name Dispatch table name.
	 */
	private static function ensure_dispatch_indexes( string $table_name ): void {
		global $wpdb;

		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
		if ( $table_exists !== $table_name ) {
			return;
		}

		$dispatch_key_columns = $wpdb->get_results( "SHOW INDEX FROM `{$table_name}` WHERE Key_name = 'dispatch_key'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is plugin-owned.
		$columns              = array();
		foreach ( is_array( $dispatch_key_columns ) ? $dispatch_key_columns : array() as $index_row ) {
			if ( isset( $index_row->Column_name ) ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase -- MySQL SHOW INDEX column name.
				$columns[] = (string) $index_row->Column_name; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase -- MySQL SHOW INDEX column name.
			}
		}

		if ( array( 'idempotency_key' ) !== $columns ) {
			if ( array() !== $columns ) {
				$wpdb->query( "ALTER TABLE `{$table_name}` DROP INDEX `dispatch_key`" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is plugin-owned.
			}

			$wpdb->query( "ALTER TABLE `{$table_name}` ADD UNIQUE KEY `dispatch_key` (`idempotency_key`)" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is plugin-owned.
		}

		$event_destination_index = $wpdb->get_var( "SHOW INDEX FROM `{$table_name}` WHERE Key_name = 'event_destination'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is plugin-owned.
		if ( null === $event_destination_index ) {
			$wpdb->query( "ALTER TABLE `{$table_name}` ADD KEY `event_destination` (`order_id`,`event_type`,`destination`)" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is plugin-owned.
		}
	}
}
