<?php

namespace RoosterLMS\Core;

use RoosterLMS\Data\Db;
use RoosterLMS\Data\SqlPart;

defined( 'ABSPATH' ) || exit;

final class Collation {

	public const MODERN = array(
		'utf8mb4_uca1400_ai_ci' => '14',
		'utf8mb4_0900_ai_ci'    => '9',
	);

	private const BINARY_MODIFY = array(
		'char'       => 'MODIFY %i CHAR(%d) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin',
		'varchar'    => 'MODIFY %i VARCHAR(%d) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin',
		'tinytext'   => 'MODIFY %i TINYTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin',
		'text'       => 'MODIFY %i TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin',
		'mediumtext' => 'MODIFY %i MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin',
		'longtext'   => 'MODIFY %i LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin',
	);

	private static ?string $site = null;

	public static function site(): string {
		global $wpdb;
		if ( null === self::$site ) {
			$found      = self::table_collation( $wpdb->posts );
			self::$site = '' !== $found ? $found : ( '' !== (string) $wpdb->collate ? (string) $wpdb->collate : 'utf8mb4_unicode_520_ci' );
		}
		return self::$site;
	}

	public static function forget(): void {
		self::$site = null;
	}

	public static function table_collation( string $table ): string {
		global $wpdb;
		return (string) $wpdb->get_var( $wpdb->prepare( 'SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
	}

	public static function best_available(): ?string {
		foreach ( array_keys( self::MODERN ) as $collation ) {
			if ( self::exists( $collation ) ) {
				return $collation;
			}
		}
		return null;
	}

	public static function exists( string $collation ): bool {
		global $wpdb;
		if ( 1 !== preg_match( '/^utf8mb4_[a-z0-9_]+$/', $collation ) ) {
			return false;
		}
		return null !== $wpdb->get_row( $wpdb->prepare( 'SHOW COLLATION LIKE %s', $collation ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
	}

	public static function is_modern( string $collation ): bool {
		return 1 === preg_match( '/^utf8mb4_(uca1400|0900)_/', $collation );
	}

	public static function server(): string {
		global $wpdb;
		return (string) $wpdb->get_var( 'SELECT VERSION()' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
	}

	public static function rooster_tables_off( string $collation ): array {
		global $wpdb;
		$rows = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				'SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE %s AND TABLE_COLLATION <> %s ORDER BY TABLE_NAME',
				$wpdb->esc_like( $wpdb->prefix . 'rooster_lms_' ) . '%',
				$collation
			)
		);
		return array_values( array_map( 'strval', (array) $rows ) );
	}

	public static function database_tables(): array {
		global $wpdb;
		$rows = $wpdb->get_col( "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return array_values( array_map( 'strval', (array) $rows ) );
	}

	public static function rooster_tables(): array {
		global $wpdb;
		$rows = $wpdb->get_col( $wpdb->prepare( 'SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE %s ORDER BY TABLE_NAME', $wpdb->esc_like( $wpdb->prefix . 'rooster_lms_' ) . '%' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return array_values( array_map( 'strval', (array) $rows ) );
	}

	public static function convert( array $tables, string $collation ): array {
		global $wpdb;
		if ( ! self::exists( $collation ) ) {
			throw new \InvalidArgumentException( 'Unknown collation ' . esc_html( $collation ) );
		}
		$done = array();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- the conversion.
		foreach ( $tables as $table ) {
			if ( self::table_collation( $table ) === $collation ) {
				continue;
			}
			$binary = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLLATION_NAME LIKE %s ORDER BY ORDINAL_POSITION',
					$table,
					'%' . $wpdb->esc_like( '_bin' )
				),
				ARRAY_A
			);
			$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i CONVERT TO CHARACTER SET utf8mb4 COLLATE %i', $table, $collation ) );
			$done[ $table ] = self::modify_binary( $table, (array) $binary );
		}
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange
		self::forget();
		return $done;
	}

	public static function modify_binary( string $table, array $columns ): array {
		$parts   = array();
		$changed = array();
		foreach ( $columns as $column ) {
			$type = strtolower( trim( (string) ( $column['COLUMN_TYPE'] ?? '' ) ) );
			if ( 1 === preg_match( '/^(char|varchar)\((\d+)\)$/', $type, $m ) ) {
				$part = SqlPart::of( self::BINARY_MODIFY[ $m[1] ], (string) $column['COLUMN_NAME'], (int) $m[2] );
			} elseif ( isset( self::BINARY_MODIFY[ $type ] ) && ! in_array( $type, array( 'char', 'varchar' ), true ) ) {
				$part = SqlPart::of( self::BINARY_MODIFY[ $type ], (string) $column['COLUMN_NAME'] );
			} else {
				error_log( 'rooster-lms: binary collation skipped a column of type ' . preg_replace( '/[^a-z0-9(),]/', '', $type ) ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- operational log line, the column type only.
				continue;
			}
			$nullable = 'YES' === ( $column['IS_NULLABLE'] ?? '' );
			$part     = $part->add( $nullable ? ' NULL' : ' NOT NULL' );
			$default  = $column['COLUMN_DEFAULT'] ?? null;
			if ( null === $default || 'NULL' === strtoupper( (string) $default ) ) {
				$part = $part->add( $nullable ? ' DEFAULT NULL' : '' );
			} else {
				$value = (string) $default;
				$value = 1 === preg_match( "/^'(.*)'$/s", $value, $q ) ? str_replace( "''", "'", $q[1] ) : $value;
				$part  = $part->add( ' DEFAULT %s', $value );
			}
			$parts[]   = $part;
			$changed[] = (string) $column['COLUMN_NAME'];
		}
		if ( array() === $parts ) {
			return array();
		}
		Db::query( SqlPart::of( 'ALTER TABLE %i ', $table )->add( SqlPart::join( ', ', $parts ) ) );
		return $changed;
	}
}