<?php

namespace ABeCe\Booking\Infrastructure\Database;

use RuntimeException;

final class Schema {
	/** @return array<string, string> Table name => dbDelta-compatible CREATE TABLE. */
	public static function initial( \wpdb $wpdb ): array {
		return self::read( $wpdb, '001_initial.sql', 11 );
	}

	public static function operatingSchedule( \wpdb $wpdb ): array {
		return self::read( $wpdb, '002_operating_schedule.sql', 2 );
	}

	public static function bookingModel( \wpdb $wpdb ): array { return self::read( $wpdb, '003_booking_model.sql', 1 ); }

	public static function pricing( \wpdb $wpdb ): array { return self::read( $wpdb, '004_pricing.sql', 3 ); }

	public static function creditPayments( \wpdb $wpdb ): array { return self::read( $wpdb, '005_credit_payments.sql', 3 ); }

	public static function cancellation( \wpdb $wpdb ): array { return self::read( $wpdb, '006_cancellation.sql', 5 ); }

	public static function resourceSurface( \wpdb $wpdb ): array { return self::read( $wpdb, '007_resource_surface.sql', 1 ); }

	public static function externalContacts( \wpdb $wpdb ): array { return self::read( $wpdb, '008_external_contacts.sql', 2 ); }

	public static function settingsPayments( \wpdb $wpdb ): array { return self::read( $wpdb, '009_settings_payments.sql', 2 ); }

	public static function usageRestrictions( \wpdb $wpdb ): array { return self::read( $wpdb, '010_usage_restrictions.sql', 2 ); }

 public static function usageCountingMode(\wpdb $wpdb):array{return self::read($wpdb,'011_usage_counting_mode.sql',1);}

	public static function pilotOperations(\wpdb $wpdb): array { return self::read($wpdb, '012_pilot_operations.sql', 4); }

	public static function current( \wpdb $wpdb ): array { return array_replace( self::initial( $wpdb ) + self::operatingSchedule( $wpdb ), self::bookingModel( $wpdb ), self::pricing( $wpdb ), self::creditPayments( $wpdb ), self::cancellation( $wpdb ), self::resourceSurface( $wpdb ), self::externalContacts( $wpdb ), self::settingsPayments( $wpdb ), self::usageRestrictions( $wpdb ), self::usageCountingMode($wpdb), self::pilotOperations($wpdb) ); }

	public static function adoptedPaymentShape( \wpdb $wpdb, string $table ): ?string {
        if ( ! in_array($table,[\ABeCe\Booking\Infrastructure\Database\Tables::name( $wpdb, 'resources' ),\ABeCe\Booking\Infrastructure\Database\Tables::name( $wpdb, 'payment_transactions' )],true) ) return null;
        $columns=$wpdb->get_col($wpdb->prepare('SHOW COLUMNS FROM %i',$table));
        if (in_array('payment_methods_override',$columns,true)||in_array('payment_medium',$columns,true)) return self::settingsPayments($wpdb)[$table];
        return null;
    }

	private static function read( \wpdb $wpdb, string $filename, int $expected_count ): array {
		$file = dirname( __DIR__, 3 ) . '/database/migrations/' . $filename;
		if ( (! is_readable( $file ) && ! is_readable($file.'.php')) || ! preg_match( '/^[A-Za-z0-9_]*$/', $wpdb->prefix ) ) {
			throw new RuntimeException( __( 'ABeCe Booking could not read a valid database schema.', 'abece-booking' ) );
		}
        $template = is_readable($file.'.php') ? require $file.'.php' : file_get_contents($file);
		if ( false === $template ) {
			throw new RuntimeException( __( 'ABeCe Booking could not read a valid database schema.', 'abece-booking' ) );
		}
		// Compile immutable historical definitions through the single logical registry.
		// Historical upgrade code explicitly scopes Tables::historical(); runtime never falls back.
		$sql = preg_replace_callback( '/\{\{prefix\}\}asb_([a-z_]+)/', static fn( array $match ): string => Tables::name( $wpdb, $match[1] ), $template );
		$sql = str_replace( '{{charset_collate}}', $wpdb->get_charset_collate(), $sql );
		$sql = preg_replace( '/^--[^\n]*\n/m', '', $sql );
		$statements = [];
		foreach ( explode( ';', $sql ) as $statement ) {
			$statement = trim( $statement );
			if ( '' !== $statement && preg_match( '/^CREATE TABLE ([A-Za-z0-9_]+) /', $statement, $match ) ) {
				$statements[ $match[1] ] = $statement . ';';
			}
		}
		if ( $expected_count !== count( $statements ) ) {
			throw new RuntimeException( __( 'ABeCe Booking could not read a valid database schema.', 'abece-booking' ) );
		}
		return $statements;
	}

	/** Verify the complete columns and indexes before advancing the version. */
	public static function verify( \wpdb $wpdb, string $table, string $sql ): void {
		$engine = $wpdb->get_var( $wpdb->prepare( 'SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s', $table ) );
		if ( 'InnoDB' !== $engine ) {
			self::fail();
		}
		$actual_columns = $wpdb->get_results( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $table ), OBJECT_K );
		$actual_indexes = $wpdb->get_results( $wpdb->prepare( 'SHOW INDEX FROM %i', $table ), ARRAY_A );
		if ( ! $actual_columns || ! $actual_indexes || $wpdb->last_error ) {
			self::fail();
		}
		if(!str_contains($sql,'series_occurrence_key')&&!str_contains($sql,'group_uuid')&&(isset($actual_columns['series_occurrence_key'])||isset($actual_columns['group_uuid']))){$sql=self::pilotOperations($wpdb)[$table]??$sql;}
		$expected_columns = [];
		$expected_indexes = [];
		foreach ( explode( "\n", $sql ) as $line ) {
			$line = trim( $line, " \t\r," );
			if ( preg_match( '/^(PRIMARY KEY|UNIQUE KEY ([a-z0-9_]+)|KEY ([a-z0-9_]+))\s+\(([^)]+)\)$/i', $line, $match ) ) {
				$name = 'PRIMARY KEY' === $match[1] ? 'PRIMARY' : ( $match[2] ?: $match[3] );
				$expected_indexes[ $name ] = [
					'columns' => explode( ',', $match[4] ),
					'unique' => ! str_starts_with( $match[1], 'KEY ' ),
				];
			} elseif ( preg_match( '/^([a-z0-9_]+) ((?:bigint|smallint|tinyint|int|decimal|varchar|char|datetime|date|time|text|longtext)(?:\(\d+(?:,\d+)?\))?(?: unsigned)?)(.*)$/', $line, $match ) ) {
				[ , $name, $type, $attributes ] = $match;
				$expected_columns[] = $name;
				$column = $actual_columns[ $name ] ?? null;
				$default = preg_match( "/DEFAULT '([^']*)'/", $attributes, $default_match ) ? $default_match[1] : null;
				if ( ! $column || self::normalizeType( $column->Type ) !== self::normalizeType( $type )
					|| $column->Null !== ( str_contains( $attributes, 'NOT NULL' ) ? 'NO' : 'YES' )
					|| $column->Default !== $default
					|| str_contains( $column->Extra, 'auto_increment' ) !== str_contains( $attributes, 'AUTO_INCREMENT' ) ) {
					self::fail();
				}
			}
		}
		if ( array_keys( $actual_columns ) !== $expected_columns ) {
			self::fail();
		}
		$indexes = [];
		foreach ( $actual_indexes as $index ) {
			$indexes[ $index['Key_name'] ]['columns'][] = $index['Column_name'];
			$indexes[ $index['Key_name'] ]['unique'] = 0 === (int) $index['Non_unique'];
		}
		foreach ( $expected_indexes as $name => $definition ) {
			if ( ( $indexes[ $name ] ?? null ) !== $definition ) {
				self::fail();
			}
		}
	}

	private static function normalizeType( string $type ): string {
		// MySQL 8 removes integer display widths; MariaDB still reports them.
		return preg_replace( '/\b(bigint|smallint|tinyint|int)\(\d+\)/', '$1', strtolower( $type ) );
	}

	private static function fail(): never {
		throw new RuntimeException( __( 'ABeCe Booking database verification failed. The schema version was not advanced.', 'abece-booking' ) );
	}
}