<?php
/**
 * Separate additive queue migration. Unknown layouts and foreign transactions fail closed.
 *
 * @package Abientot
 */

namespace Abientot\Reminders;

use Abientot\Repositories\BookingLock;
use Abientot\Support\TransactionGuard;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Schema checks and creation of the plugin's own queue table.

final class Migrator {
	public const OPTION  = 'abientot_reminders_schema_version';
	public const VERSION = '1';

	public static function table(): string {
		global $wpdb;
		return $wpdb->prefix . 'abientot_reminder_queue';
	}

	public static function ensure(): bool {
		global $wpdb;
		$owned = ! BookingLock::is_held();
		if ( $owned && ! BookingLock::acquire() ) {
			return false;
		}
		try {
			TransactionGuard::prepare_write();
			$version = get_option( self::OPTION, '' );
			if ( ! in_array( $version, array( '', self::VERSION ), true ) ) {
				return false;
			}
			$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( self::table() ) ) );
			if ( $wpdb->last_error ) {
				return false;
			}
			if ( ! $exists ) {
				$created = $wpdb->query(
					"CREATE TABLE {$wpdb->prefix}abientot_reminder_queue (
				 id bigint unsigned NOT NULL AUTO_INCREMENT,
				 appointment_id bigint unsigned NOT NULL,
				 start_digest char(64) NOT NULL, booking_digest char(64) NOT NULL,
				 due_at datetime NOT NULL, next_attempt_at datetime NOT NULL,
				 state varchar(20) NOT NULL, attempts int unsigned NOT NULL DEFAULT 0,
				 lease_hash char(64) NULL, lease_until datetime NULL,
				 last_code varchar(40) NULL, sent_at datetime NULL,
				 created_at datetime NOT NULL, updated_at datetime NOT NULL,
				 PRIMARY KEY(id), UNIQUE KEY appointment_start(appointment_id,start_digest),
				 KEY ready(state,next_attempt_at,id), KEY lease(state,lease_until), KEY appointment_id(appointment_id)
				) ENGINE=InnoDB " . $wpdb->get_charset_collate()
				);
				if ( false === $created ) {
					return false;
				}
			}
			if ( ! self::verified() ) {
				return false;
			}
			if ( $version !== self::VERSION ) {
				update_option( self::OPTION, self::VERSION, false );
				if ( get_option( self::OPTION ) !== self::VERSION ) {
					return false;
				}
			}
			return true;
		} catch ( \Throwable $error ) {
			return false;
		} finally {
			if ( $owned ) {
				BookingLock::release();
			}
		}
	}

	public static function verified(): bool {
		global $wpdb;
		$columns = $wpdb->get_results( "SHOW COLUMNS FROM {$wpdb->prefix}abientot_reminder_queue", ARRAY_A );
		if ( $wpdb->last_error || ! is_array( $columns ) ) {
			return false;
		}
		$expected = array(
			'id'              => 'bigint',
			'appointment_id'  => 'bigint',
			'start_digest'    => 'char(64)',
			'booking_digest'  => 'char(64)',
			'due_at'          => 'datetime',
			'next_attempt_at' => 'datetime',
			'state'           => 'varchar(20)',
			'attempts'        => 'int',
			'lease_hash'      => 'char(64)',
			'lease_until'     => 'datetime',
			'last_code'       => 'varchar(40)',
			'sent_at'         => 'datetime',
			'created_at'      => 'datetime',
			'updated_at'      => 'datetime',
		);
		$seen     = array();
		foreach ( $columns as $column ) {
			$key = $column['Field'];
			if ( ! isset( $expected[ $key ] ) || ! str_starts_with( strtolower( $column['Type'] ), $expected[ $key ] ) ) {
				return false;
			}
			if ( $key === 'id' && ( $column['Extra'] ?? '' ) !== 'auto_increment' ) {
				return false;
			}
			$nullable = in_array( $key, array( 'lease_hash', 'lease_until', 'last_code', 'sent_at' ), true );
			if ( ( $column['Null'] === 'YES' ) !== $nullable ) {
				return false;
			}
			$seen[ $key ] = true;
		}
		if ( array_diff_key( $expected, $seen ) ) {
			return false;
		}
		$indexes = $wpdb->get_results( "SHOW INDEX FROM {$wpdb->prefix}abientot_reminder_queue", ARRAY_A );
		if ( $wpdb->last_error || ! is_array( $indexes ) ) {
			return false;
		}
		$unique = array();
		foreach ( $indexes as $index ) {
			if ( (int) $index['Non_unique'] === 0 ) {
				$unique[ $index['Key_name'] ][] = $index['Column_name'];
			}
		}
		if ( ( $unique['PRIMARY'] ?? array() ) !== array( 'id' ) || ! in_array( array( 'appointment_id', 'start_digest' ), $unique, true ) ) {
			return false;
		}
		$engine = $wpdb->get_var( $wpdb->prepare( 'SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=%s', self::table() ) );
		return ! $wpdb->last_error && strtoupper( (string) $engine ) === 'INNODB';
	}
}