<?php

namespace RoosterLMS\Core;

use RoosterLMS\Assessment\AttemptRepository;
use RoosterLMS\Assessment\AttemptStatus;
use RoosterLMS\Assessment\QuizMode;
use RoosterLMS\Assessment\QuizResult;
use RoosterLMS\Categories\CategoryJobs;
use RoosterLMS\Categories\CategoryTaxonomy;
use RoosterLMS\Content\PostTypes;
use RoosterLMS\Data\Db;
use RoosterLMS\Data\Lock;
use RoosterLMS\Data\SqlPart;
use RoosterLMS\Domain\Services;
use RoosterLMS\Notify\NotifyJobs;
use RoosterLMS\Orgs\Roles;
use RoosterLMS\Progress\ProgressJobs;

defined( 'ABSPATH' ) || exit;

final class Migrator {

	public const VERSION_OPTION = 'rooster_lms_db_version';

	public const FLUSH_OPTION = 'rooster_lms_flush_rewrite';

	public const AQ_COUNTS_OPTION = 'rooster_lms_aq_step43';

	private const AQ_CHUNK = 500;

	public const RULES_OPTION = 'rooster_lms_rewrite_rules';

	public const REWRITE_RULES = 5;

	private const LOCK_OPTION = 'rooster_lms_migration_lock';

	private const LOCK_SECONDS = 600;

	public static function maybe_migrate(): void {
		if ( self::installed_version() === Schema::VERSION ) {
			return;
		}
		if ( ! self::is_full_request() ) {
			return;
		}
		self::migrate();
	}

	public static function maybe_flush_rewrite_rules(): void {
		$flag = get_option( self::FLUSH_OPTION, null );
		if ( null === $flag ) {
			add_option( self::FLUSH_OPTION, 0, '', true );
			$flag = 0;
		}
		if ( (int) get_option( self::RULES_OPTION, 0 ) < self::REWRITE_RULES ) {
			update_option( self::RULES_OPTION, self::REWRITE_RULES, true );
			$flag = 1;
		}
		if ( (int) $flag ) {
			update_option( self::FLUSH_OPTION, 0, true );
			flush_rewrite_rules( false );
		}
	}

	public static function installed_version(): int {
		return (int) get_option( self::VERSION_OPTION, 0 );
	}

	public static function migrate(): bool {
		if ( ! self::acquire_lock() ) {
			return false;
		}
		try {
			$from = self::installed_version();
			foreach ( self::before_install() as $version => $step ) {
				if ( $version > $from && $version <= Schema::VERSION ) {
					$step();
				}
			}
			Schema::install();
			foreach ( self::steps() as $version => $step ) {
				if ( $version > $from && $version <= Schema::VERSION ) {
					$step();
				}
			}
			if ( array() !== Schema::missing_tables() ) {
				return false;
			}
			update_option( self::VERSION_OPTION, Schema::VERSION, true );
			Capabilities::grant_to_administrators();
			Roles::install_editor();
			Roles::install_org_administrator();
			Settings::ensure_defaults();
			update_option( self::FLUSH_OPTION, 1, true );
			ProgressJobs::install();
			CategoryJobs::install();
			\RoosterLMS\Assessment\ReleaseSweeper::install();
			\RoosterLMS\Live\LiveModule::install();
			\RoosterLMS\Jobs\DailyJobs::install();
			\RoosterLMS\H5P\Cron::install();
			try {
				if ( \RoosterLMS\H5P\Engine::available() ) {
					\RoosterLMS\H5P\Engine::install_bundled( \RoosterLMS\Domain\Actor::system() );
				}
			} catch ( \Throwable $e ) {
				unset( $e );
			}
			\RoosterLMS\Content\CourseStaffService::invalidate_staff_users();
			\RoosterLMS\Blocks\LiveData::invalidate_units();
			\RoosterLMS\Persons\LinkedTargets::rebuild();
			return true;
		} finally {
			self::release_lock();
		}
	}

	private static function steps(): array {
		return array(
			1  => static function (): void {
			},
			2  => static function (): void {
				Lock::ensure( Lock::CATEGORY_TREE . CategoryTaxonomy::COURSE->value );
				Lock::ensure( Lock::CATEGORY_TREE . CategoryTaxonomy::LESSON->value );
				global $wpdb;
				foreach ( CategoryTaxonomy::cases() as $taxonomy ) {
					if ( (int) Db::value( 'SELECT COUNT(*) FROM %i WHERE taxonomy = %s', $wpdb->term_taxonomy, $taxonomy->taxonomy() ) > 0 ) {
						Services::categories()->schedule_rebuild( $taxonomy );
					}
				}
			},
			3  => static function (): void {
			},
			4  => static function (): void {
				$keys = array();
				foreach ( Db::rows( 'SHOW INDEX FROM %i', Schema::table( 'import_map' ) ) as $row ) {
					$keys[ (string) $row['Key_name'] ] = true;
				}
				if ( isset( $keys['by_run'], $keys['by_run_kind'] ) ) {
					Db::query( 'ALTER TABLE %i DROP INDEX by_run', Schema::table( 'import_map' ) );
				}
				Roles::install();
				NotifyJobs::install();
			},
			5  => static function (): void {
				global $wpdb;
				$keys = array();
				foreach ( Db::rows( 'SHOW INDEX FROM %i', Schema::table( 'questions' ) ) as $row ) {
					$keys[ (string) $row['Key_name'] ] = true;
				}
				if ( isset( $keys['uuid'], $keys['by_unit_uuid'] ) ) {
					Db::query( 'ALTER TABLE %i DROP INDEX uuid', Schema::table( 'questions' ) );
				}
				Db::query(

						'UPDATE %i c LEFT JOIN ( SELECT course_id, COUNT(*) AS n FROM %i GROUP BY course_id ) p ON p.course_id = c.id SET c.prereq_count = COALESCE(p.n, 0) WHERE c.prereq_count <> COALESCE(p.n, 0)',
						Schema::table( 'courses' ),
						Schema::table( 'course_prereqs' )

				);
				$old = 'rooster_certificate_tpl';
				Db::query( 'UPDATE %i SET post_type = %s WHERE post_type IN (%s, %s)', $wpdb->posts, PostTypes::CERTIFICATE_TPL, $old, substr( $old, 0, 20 ) );
				$icl = $wpdb->prefix . 'icl_translations';
				if ( Db::value( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $icl ) ) === $icl ) {
					Db::query( 'UPDATE %i SET element_type = %s WHERE element_type = %s', $icl, 'post_' . PostTypes::CERTIFICATE_TPL, 'post_' . $old );
				}
			},
			6  => static function (): void {
			},
			7  => static function (): void {
				global $wpdb;
				$keys = array();
				foreach ( Db::rows( 'SHOW INDEX FROM %i', Schema::table( 'attempt_answers' ) ) as $row ) {
					$keys[ (string) $row['Key_name'] ] = true;
				}
				if ( isset( $keys['by_question'], $keys['by_question_state'] ) ) {
					Db::query( 'ALTER TABLE %i DROP INDEX by_question', Schema::table( 'attempt_answers' ) );
				}
				Db::query( 'UPDATE %i a JOIN %i u ON u.id = a.unit_id SET a.pass_percent = u.pass_percent WHERE a.pass_percent IS NULL AND u.pass_percent IS NOT NULL', Schema::table( 'attempts' ), Schema::table( 'units' ) );
				Db::query( 'UPDATE %i SET flags = flags | 1 WHERE flags & 1 = 0 AND response LIKE %s', Schema::table( 'attempt_answers' ), '%' . $wpdb->esc_like( '"imported":1' ) . '%' );
				\RoosterLMS\Assessment\SnapshotBackfillHandler::start();
			},
			8  => static function (): void {
				Db::query( 'UPDATE %i aa JOIN %i a ON a.id = aa.attempt_id SET aa.flags = aa.flags | %d WHERE a.mode = %d AND aa.flags & %d = 0', Schema::table( 'attempt_answers' ), Schema::table( 'attempts' ), AttemptRepository::FLAG_SURVEY, QuizMode::SURVEY->value, AttemptRepository::FLAG_SURVEY );
				Db::query( 'UPDATE %i SET pass_percent = FLOOR(score_pct + 0.005) WHERE mode = %d AND status = %d AND result = %d AND score_pct IS NOT NULL AND pass_percent IS NOT NULL AND score_pct + 0.005 < pass_percent', Schema::table( 'attempts' ), QuizMode::GRADED->value, AttemptStatus::GRADED->value, QuizResult::PASSED->value );
				Db::query( 'UPDATE %i SET pass_percent = FLOOR(score_pct + 0.005) + 1 WHERE mode = %d AND status = %d AND result = %d AND score_pct IS NOT NULL AND score_pct + 0.005 < 100 AND ( pass_percent IS NULL OR score_pct + 0.005 >= pass_percent )', Schema::table( 'attempts' ), QuizMode::GRADED->value, AttemptStatus::GRADED->value, QuizResult::FAILED->value );
				Db::query( 'UPDATE %i aa JOIN %i q ON q.id = aa.question_id SET aa.position = q.position WHERE aa.position = 0 AND q.position > 0', Schema::table( 'attempt_answers' ), Schema::table( 'questions' ) );
			},
			9  => static function (): void {
				$table   = Schema::table( 'org_locations' );
				$columns = array_map( 'strval', Db::col( 'SHOW COLUMNS FROM %i', $table ) );
				$at      = array_search( 'address', $columns, true );
				if ( false === $at ) {
					Db::query( "ALTER TABLE %i ADD COLUMN address varchar(255) NOT NULL DEFAULT '' AFTER name", $table );
				} elseif ( ( $columns[ $at - 1 ] ?? '' ) !== 'name' ) {
					Db::query( "ALTER TABLE %i MODIFY COLUMN address varchar(255) NOT NULL DEFAULT '' AFTER name", $table );
				}
			},
			10 => static function (): void {
			},
			11 => static function (): void {
			},
			12 => static function (): void {
				$after = 0;
				do {
					$ids   = array_map( 'intval', Db::col( 'SELECT id FROM %i WHERE id > %d ORDER BY id LIMIT 500', Schema::table( 'courses' ), $after ) );
					$batch = count( $ids );
					if ( array() !== $ids ) {
						\RoosterLMS\Data\Transaction::run( static fn(): int => Services::course_staff()->repair( $ids, true ) );
						$after = end( $ids );
					}
				} while ( 500 === $batch );
			},
			13 => static function (): void {
				$table   = Schema::table( 'orgs' );
				$columns = array_map( 'strval', Db::col( 'SHOW COLUMNS FROM %i', $table ) );
				$at      = array_search( 'external_id', $columns, true );
				if ( false === $at ) {
					Db::query( 'ALTER TABLE %i ADD COLUMN external_id varchar(64) DEFAULT NULL AFTER slug', $table );
				} elseif ( ( $columns[ $at - 1 ] ?? '' ) !== 'slug' ) {
					Db::query( 'ALTER TABLE %i MODIFY COLUMN external_id varchar(64) DEFAULT NULL AFTER slug', $table );
				}
				$keys = array();
				foreach ( Db::rows( 'SHOW INDEX FROM %i', $table ) as $row ) {
					$keys[ (string) $row['Key_name'] ] = true;
				}
				if ( ! isset( $keys['external_id'] ) ) {
					Db::query( 'ALTER TABLE %i ADD UNIQUE KEY external_id (external_id)', $table );
				}
			},
			15 => static function (): void {
				Schema::utf8_columns();
			},
			16 => static function (): void {
			},
			17 => static function (): void {
				\RoosterLMS\Registration\FormService::ensure_default();
				global $wpdb;
				Db::query( 'UPDATE %i c JOIN %i s ON s.course_id = c.id SET c.attendance_required = 1 WHERE c.attendance_required = 0 AND s.options LIKE %s', Db::table( 'courses' ), Db::table( 'live_sessions' ), '%' . $wpdb->esc_like( '"attendance_required":true' ) . '%' );
				\RoosterLMS\Messages\Seeder::seed();
				\RoosterLMS\Messages\Seeder::migrate_live_reminders();
			},
			18 => static function (): void {
				\RoosterLMS\Live\Lesson\ProtectedFiles::make_existing_private();
			},
			19 => static function (): void {
				foreach ( array( 'live_sessions', 'live_occurrences' ) as $name ) {
					$table  = Db::table( $name );
					$column = Db::row( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'provider_ref' );
					if ( null !== $column && 'varchar(255)' !== strtolower( (string) ( $column['Type'] ?? '' ) ) ) {
						Db::query( "ALTER TABLE %i MODIFY provider_ref varchar(255) NOT NULL DEFAULT ''", $table );
					}
				}
			},
			20 => static function (): void {
				self::step_20();
				$keys = array();
				foreach ( Db::rows( 'SHOW INDEX FROM %i', Schema::table( 'offering_enrolments' ) ) as $row ) {
					$keys[ (string) $row['Key_name'] ] = true;
				}
				if ( isset( $keys['one_cycle'], $keys['by_cycle'] ) ) {
					Db::query( 'ALTER TABLE %i DROP INDEX one_cycle', Schema::table( 'offering_enrolments' ) );
				}
			},
			21 => static function (): void {
				self::step_21();
			},
			22 => static function (): void {
				self::step_22();
			},
			23 => static function (): void {
			},
			24 => static function (): void {
				self::step_24();
			},
			25 => static function (): void {
				\RoosterLMS\Orgs\People::repair_member_keys();
				\RoosterLMS\Ledger\UnitStats::schedule_fill();
			},
			26 => static function (): void {
				self::step_26();
			},
			29 => static function (): void {
				self::step_29();
			},
			30 => static function (): void {
				self::step_30();
			},
			31 => static function (): void {
				self::step_31();
			},
			32 => static function (): void {
				self::step_32();
			},
			33 => static function (): void {
				\RoosterLMS\Attendance\DoorAccess::install_role();
			},
			34 => static function (): void {
				self::step_34();
			},
			35 => static function (): void {
				self::step_35();
			},
			36 => static function (): void {
				self::step_36();
			},
			37 => static function (): void {
				self::step_37();
			},
			38 => static function (): void {},
			39 => static function (): void {
				Roles::install_org_administrator();
			},
			40 => static function (): void {},
			41 => static function (): void {},
			42 => static function (): void {
				$keys = array();
				foreach ( Db::rows( 'SHOW INDEX FROM %i', Schema::table( 'feedback_responses' ) ) as $row ) {
					$keys[ (string) $row['Key_name'] ] = true;
				}
				if ( isset( $keys['one_per'], $keys['one_point'] ) ) {
					Db::query( 'ALTER TABLE %i DROP INDEX one_per', Schema::table( 'feedback_responses' ) );
				}
			},
			43 => static function (): void {
				self::step_43();
			},
			44 => static function (): void {
				$keys = self::index_names( Schema::table( 'feedback_responses' ) );
				if ( ! isset( $keys['one_response'] ) ) {
					return;
				}
				foreach ( array( 'one_point', 'one_per' ) as $old ) {
					if ( isset( $keys[ $old ] ) ) {
						Db::query( 'ALTER TABLE %i DROP INDEX %i', Schema::table( 'feedback_responses' ), $old );
					}
				}
			},
			14 => static function (): void {
			},
		);
	}

	private static function step_20(): void {
		$units = Schema::table( 'org_units' );
		$paths = Schema::table( 'org_unit_paths' );
		$locs  = Schema::table( 'org_locations' );
		$orgs  = Schema::table( 'orgs' );
		$locks = Schema::table( 'locks' );
		$now   = Clock::now();
		Db::query( 'INSERT IGNORE INTO %i (id, org_id, parent_id, depth, root_slot, position, name, address, seat_limit, state, created_at, updated_at) SELECT l.id, l.org_id, 0, 1, NULL, l.position, l.name, l.address, NULL, l.state, %s, %s FROM %i l', $units, $now, $now, $locs );
		self::raise_auto_increment( $units, self::auto_increment( $locs ) );
		Db::query( "INSERT INTO %i (org_id, parent_id, depth, root_slot, position, name, address, seat_limit, state, created_at, updated_at) SELECT o.id, 0, 0, 1, 0, '', '', NULL, 1, %s, %s FROM %i o LEFT JOIN %i r ON r.org_id = o.id AND r.root_slot = 1 WHERE r.id IS NULL", $units, $now, $now, $orgs, $units );
		Db::query( 'UPDATE %i u JOIN %i r ON r.org_id = u.org_id AND r.root_slot = 1 LEFT JOIN %i p ON p.id = u.parent_id SET u.parent_id = r.id WHERE u.depth = 1 AND ( u.parent_id = 0 OR p.id IS NULL )', $units, $units, $units );
		Db::query( 'INSERT IGNORE INTO %i (ancestor_id, descendant_id, depth) SELECT id, id, 0 FROM %i', $paths, $units );
		Db::query( 'INSERT IGNORE INTO %i (ancestor_id, descendant_id, depth) SELECT r.id, p.descendant_id, p.depth + 1 FROM %i p JOIN %i u ON u.id = p.ancestor_id AND u.depth = 1 JOIN %i r ON r.org_id = u.org_id AND r.root_slot = 1', $paths, $paths, $units, $units );
		Db::query( 'DELETE p FROM %i p LEFT JOIN %i a ON a.id = p.ancestor_id LEFT JOIN %i d ON d.id = p.descendant_id WHERE a.id IS NULL OR d.id IS NULL', $paths, $units, $units );
		Db::query( "INSERT IGNORE INTO %i (name) SELECT CONCAT('org_tree:', id) FROM %i", $locks, $orgs );
		$gaps = self::hc_copy_gaps();
		if ( $gaps['missing'] > 0 || $gaps['roots'] > 0 ) {
			$taken = array_map( 'intval', Db::col( 'SELECT u.id FROM %i l JOIN %i u ON u.id = l.id WHERE u.org_id <> l.org_id OR u.depth = 0 ORDER BY u.id LIMIT 20', $locs, $units ) );
			$error = new \RoosterLMS\Data\DbError( 'org_units copy incomplete' . ( array() !== $taken ? '; unit IDs in the way: ' . implode( ',', $taken ) : '' ), 0, 'org_units' );
			throw $error;
		}
		\RoosterLMS\Messages\Seeder::seed();
		foreach ( array_map( 'intval', Db::col( 'SELECT id FROM %i ORDER BY id', $orgs ) ) as $org_id ) {
			\RoosterLMS\Orgs\OrgUnitRepository::bump( $org_id );
		}
		Services::org_unit_repo()->forget();
		Services::location_repo()->forget();
	}

	private static function step_21(): void {
		$after_of = array(
			'audiences'        => 'name',
			'access_templates' => 'updated_at',
		);
		foreach ( $after_of as $name => $after ) {
			$table = Schema::table( $name );
			if ( null === Db::row( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'state' ) ) {
				Db::query( 'ALTER TABLE %i ADD COLUMN state tinyint(3) unsigned NOT NULL DEFAULT 1 AFTER %i', $table, $after );
			}
		}
	}

	private static function step_22(): void {
		global $wpdb;
		$columns = array(
			'org_units' => array( 'type_id', 'smallint(5) unsigned NOT NULL DEFAULT 0', 'depth' ),
			'progress'  => array( 'steer_org_id', 'bigint(20) unsigned DEFAULT NULL', 'flags' ),
		);
		foreach ( $columns as $name => $column ) {
			$table = Schema::table( $name );
			if ( null === Db::row( 'SHOW COLUMNS FROM %i LIKE %s', $table, $column[0] ) ) {
				Db::query( SqlPart::of( 'ALTER TABLE %i ADD COLUMN %i ', $table, $column[0] )->add( $column[1] )->add( SqlPart::of( ' AFTER %i', $column[2] ) ) );
			}
		}
		if ( ! self::hc11_columns_ready() ) {
			$error = new \RoosterLMS\Data\DbError( 'schema 22 columns missing', 0, 'org_units' );
			throw $error;
		}
		foreach ( array_map( 'intval', Db::col( 'SELECT id FROM %i ORDER BY id', Schema::table( 'orgs' ) ) ) as $org_id ) {
			\RoosterLMS\Orgs\OrgUnitRepository::bump( $org_id );
		}
		Services::org_unit_repo()->forget();
	}

	private static function step_24(): void {
		\RoosterLMS\Persons\Vocabulary::seed();
	}

	private static function step_26(): void {
		global $wpdb;
		$certificates = Schema::table( 'certificates' );
		$sessions     = Schema::table( 'live_sessions' );
		$columns      = array(
			'certificates'  => array(
				'kind'      => 'tinyint(3) unsigned NOT NULL DEFAULT 1',
				'source_id' => 'bigint(20) unsigned NOT NULL DEFAULT 0',
			),
			'live_sessions' => array(
				'certificate_tpl_id'  => 'bigint(20) unsigned DEFAULT NULL',
				'certificate_rule'    => 'tinyint(3) unsigned NOT NULL DEFAULT 1',
				'certificate_min_pct' => 'tinyint(3) unsigned NOT NULL DEFAULT 100',
			),
		);
		foreach ( $columns as $name => $set ) {
			$table = Schema::table( $name );
			foreach ( $set as $column => $definition ) {
				if ( null === Db::row( 'SHOW COLUMNS FROM %i LIKE %s', $table, $column ) ) {
					Db::query( SqlPart::of( 'ALTER TABLE %i ADD COLUMN %i ', $table, $column )->add( $definition ) );
				}
			}
		}
		if ( ! self::certificates_columns_ready() ) {
			$error = new \RoosterLMS\Data\DbError( 'schema 26 columns missing', 0, 'certificates' );
			throw $error;
		}
		$keys = self::index_names( $certificates );
		if ( ! isset( $keys['one_per_source'] ) ) {
			Db::query( 'ALTER TABLE %i ADD UNIQUE KEY one_per_source (user_id,course_id,cycle,kind,source_id)', $certificates );
			$keys['one_per_source'] = true;
		}
		if ( ! isset( $keys['by_source'] ) ) {
			Db::query( 'ALTER TABLE %i ADD KEY by_source (kind,source_id,user_id)', $certificates );
		}
		if ( ! isset( $keys['by_kind'] ) ) {
			Db::query( 'ALTER TABLE %i ADD KEY by_kind (kind,issued_at)', $certificates );
		}
		if ( isset( $keys['one_per_cycle'], $keys['one_per_source'] ) ) {
			Db::query( 'ALTER TABLE %i DROP INDEX one_per_cycle', $certificates );
		}
		if ( ! isset( self::index_names( $sessions )['by_certificate'] ) ) {
			Db::query( 'ALTER TABLE %i ADD KEY by_certificate (certificate_tpl_id)', $sessions );
		}
		\RoosterLMS\Certificates\TemplateMap::map_all();
		\RoosterLMS\Certificates\AttendanceCertificates::refresh_flag();
		Services::reset();
	}

	private static function step_29(): void {
		$units = Schema::table( 'units' );
		$keys  = self::index_names( $units );
		if ( ! isset( $keys['by_kind'] ) ) {
			Db::query( 'ALTER TABLE %i ADD KEY by_kind (unit_type,lesson_kind,state)', $units );
			$keys['by_kind'] = true;
		}
		if ( isset( $keys['by_type'], $keys['by_kind'] ) ) {
			Db::query( 'ALTER TABLE %i DROP INDEX by_type', $units );
		}
		\RoosterLMS\CourseInfo\Seeds::seed();
		if ( ! self::ma_columns_ready() ) {
			$error = new \RoosterLMS\Data\DbError( 'schema 29 columns missing', 0, 'courses' );
			throw $error;
		}
		self::step_29_g();
		self::step_29_q();
		self::step_29_m();
		Services::reset();
	}

	private static function step_29_g(): void {
		\RoosterLMS\Points\PointsService::seed();
	}

	private static function step_29_q(): void {
		global $wpdb;
		foreach ( array( 'question_categories', 'submissions' ) as $name ) {
			if ( null === Db::value( 'SHOW TABLES LIKE %s', $wpdb->esc_like( Schema::table( $name ) ) ) ) {
				$error = new \RoosterLMS\Data\DbError( 'schema 29 table missing', 0, $name );
				throw $error;
			}
		}
	}

	private static function step_29_m(): void {
		foreach ( array( 'media' => 'pages', 'media_progress' => 'pages_map' ) as $name => $column ) {
			if ( null === Db::row( 'SHOW COLUMNS FROM %i LIKE %s', Schema::table( $name ), $column ) ) {
				$error = new \RoosterLMS\Data\DbError( 'schema 29 column missing', 0, $name );
				throw $error;
			}
		}
	}

	private static function step_30(): void {
		if ( ! self::w2_columns_ready() ) {
			$error = new \RoosterLMS\Data\DbError( 'schema 30 table or column missing', 0, 'external_courses' );
			throw $error;
		}
		Services::reset();
	}

	private static function step_31(): void {
		global $wpdb;
		if ( null === Db::value( 'SHOW TABLES LIKE %s', $wpdb->esc_like( Schema::table( 'import_run_events' ) ) ) ) {
			$error = new \RoosterLMS\Data\DbError( 'schema 31 table missing', 0, 'import_run_events' );
			throw $error;
		}
		\RoosterLMS\Import\Lms\RunEvents::mark_start();
	}

	private static function step_34(): void {
		global $wpdb;
		if ( null === Db::value( 'SHOW TABLES LIKE %s', $wpdb->esc_like( Schema::table( 'material_qr' ) ) ) ) {
			$error = new \RoosterLMS\Data\DbError( 'schema 34 table missing', 0, 'material_qr' );
			throw $error;
		}
	}

	private static function step_35(): void {
		global $wpdb;
		$table   = Schema::table( 'invitees' );
		$columns = array_map( 'strval', Db::col( 'SHOW COLUMNS FROM %i', $table ) );
		$old     = array_values( array_intersect( array( 'checked_in_at', 'checked_in_by', 'checked_in_occurrence_id' ), $columns ) );
		if ( array() === $old ) {
			return;
		}
		if ( in_array( 'checked_in_at', $old, true ) && in_array( 'checked_in_occurrence_id', $old, true ) ) {
			$by = in_array( 'checked_in_by', $old, true ) ? 'checked_in_by' : '0';
			Db::query( SqlPart::of( 'INSERT IGNORE INTO %i (invitee_id, occurrence_id, checked_in_at, checked_in_by) SELECT id, checked_in_occurrence_id, checked_in_at, ', Schema::table( 'guest_checkins' ) )->add( $by )->add( SqlPart::of( ' FROM %i WHERE checked_in_at IS NOT NULL AND checked_in_occurrence_id > 0', $table ) ) );
		}
		$drops = array();
		foreach ( $old as $column ) {
			$drops[] = SqlPart::of( 'DROP COLUMN %i', $column );
		}
		Db::query( SqlPart::of( 'ALTER TABLE %i ', $table )->add( SqlPart::join( ', ', $drops ) ) );
	}

	private static function step_36(): void {
		global $wpdb;
		foreach ( array( 'quiz_reports', 'answer_corrections', 'attempt_allowances' ) as $name ) {
			if ( null === Db::value( 'SHOW TABLES LIKE %s', $wpdb->esc_like( Schema::table( $name ) ) ) ) {
				$error = new \RoosterLMS\Data\DbError( 'schema 36 table missing', 0, $name );
				throw $error;
			}
		}
	}

	private static function step_37(): void {
		global $wpdb;
		foreach ( Schema::H5P_TABLES as $name ) {
			if ( null === Db::value( 'SHOW TABLES LIKE %s', $wpdb->esc_like( Schema::table( $name ) ) ) ) {
				$error = new \RoosterLMS\Data\DbError( 'schema 37 table missing', 0, $name );
				throw $error;
			}
		}
	}

	private static function before_install(): array {
		return array(
			43 => static function (): void {
				self::step_43_before();
			},
		);
	}

	private static function step_43_before(): void {
		global $wpdb;
		$questions = Schema::table( 'questions' );
		if ( null === Db::value( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $questions ) ) ) {
			return;
		}
		if ( isset( self::index_names( $questions )['by_global_uuid'] ) ) {
			return;
		}
		$columns = Db::col( 'SHOW COLUMNS FROM %i', $questions );
		if ( ! in_array( 'global_uuid', $columns, true ) ) {
			Db::query( "ALTER TABLE %i ADD COLUMN global_uuid char(36) NOT NULL DEFAULT ''", $questions );
		}
		if ( ! in_array( 'origin_uuid', $columns, true ) ) {
			Db::query( 'ALTER TABLE %i ADD COLUMN origin_uuid char(36) DEFAULT NULL', $questions );
		}
		$rows = Db::rows( "SELECT id, uuid FROM %i WHERE global_uuid = '' ORDER BY id", $questions );
		if ( array() === $rows ) {
			return;
		}
		$heads = array();
		foreach ( Db::rows( "SELECT uuid, global_uuid, origin_uuid FROM %i WHERE global_uuid <> '' ORDER BY id", $questions ) as $row ) {
			if ( ! isset( $heads[ (string) $row['uuid'] ] ) ) {
				$heads[ (string) $row['uuid'] ] = \RoosterLMS\Assessment\Lineage::from_row( $row );
			}
		}
		$renewed = 0;
		$updates = array();
		foreach ( $rows as $row ) {
			$uuid = (string) $row['uuid'];
			if ( isset( $heads[ $uuid ] ) ) {
				$updates[ (int) $row['id'] ] = array( wp_generate_uuid4(), $heads[ $uuid ] );
				++$renewed;
			} else {
				$updates[ (int) $row['id'] ] = array( $uuid, null );
				$heads[ $uuid ]              = $uuid;
			}
		}
		foreach ( array_chunk( $updates, self::AQ_CHUNK, true ) as $chunk ) {
			$globals = array();
			$origins = array();
			foreach ( $chunk as $id => list( $global, $origin ) ) {
				$globals[] = SqlPart::of( 'WHEN %d THEN %s', $id, $global );
				$origins[] = null === $origin ? SqlPart::of( 'WHEN %d THEN NULL', $id ) : SqlPart::of( 'WHEN %d THEN %s', $id, $origin );
			}
			Db::query(
				SqlPart::of( 'UPDATE %i SET global_uuid = CASE id ', $questions )
					->add( SqlPart::join( ' ', $globals ) )
					->add( ' END, origin_uuid = CASE id ' )
					->add( SqlPart::join( ' ', $origins ) )
					->add( ' END WHERE id IN (' . implode( ',', array_map( 'intval', array_keys( $chunk ) ) ) . ')' )
			);
		}
		update_option( self::AQ_COUNTS_OPTION, array( 'renewed' => $renewed ), false );
	}

	private static function step_43(): void {
		$questions = Schema::table( 'questions' );
		if ( ! isset( self::index_names( $questions )['by_global_uuid'] ) ) {
			Db::query( 'ALTER TABLE %i ADD UNIQUE KEY by_global_uuid (global_uuid)', $questions );
		}
		self::step_43_option_counts();
		self::step_43_snapshots();
	}

	private static function step_43_option_counts(): void {
		$versions  = Schema::table( 'question_versions' );
		$snapshots = Schema::table( 'question_snapshots' );
		$options   = Schema::table( 'question_options' );
		$after     = 0;
		while ( true ) {
			$rows = Db::rows( "SELECT v.id, v.question_id, s.display FROM %i v LEFT JOIN %i s ON s.version_id = v.id AND s.lang = '' WHERE v.id > %d AND v.option_count = 0 ORDER BY v.id LIMIT %d", $versions, $snapshots, $after, self::AQ_CHUNK );
			if ( array() === $rows ) {
				return;
			}
			$counts  = array();
			$missing = array();
			foreach ( $rows as $row ) {
				$after   = (int) $row['id'];
				$display = is_string( $row['display'] ) ? \RoosterLMS\Assessment\QuestionDisplay::from_json( $row['display'] ) : null;
				if ( null !== $display ) {
					$counts[ (int) $row['id'] ] = \RoosterLMS\Assessment\Derived::option_count( $display->options );
				} else {
					$missing[ (int) $row['id'] ] = (int) $row['question_id'];
				}
			}
			if ( array() !== $missing ) {
				$by_question = array();
				$qids        = array_values( array_unique( $missing ) );
				foreach ( Db::rows( 'SELECT question_id, COUNT(*) AS n FROM %i WHERE side = 0 AND question_id IN (' . implode( ',', array_fill( 0, count( $qids ), '%d' ) ) . ') GROUP BY question_id', array_merge( array( $options ), $qids ) ) as $row ) {
					$by_question[ (int) $row['question_id'] ] = min( 255, (int) $row['n'] );
				}
				foreach ( $missing as $version_id => $question_id ) {
					$counts[ $version_id ] = $by_question[ $question_id ] ?? 0;
				}
			}
			$cases = array();
			$ids   = array();
			foreach ( $counts as $version_id => $n ) {
				if ( $n > 0 ) {
					$cases[] = SqlPart::of( 'WHEN %d THEN %d', $version_id, $n );
					$ids[]   = $version_id;
				}
			}
			if ( array() !== $cases ) {
				Db::query(
					SqlPart::of( 'UPDATE %i SET option_count = CASE id ', $versions )
						->add( SqlPart::join( ' ', $cases ) )
						->add( ' END WHERE id IN (' . implode( ',', array_map( 'intval', $ids ) ) . ')' )
				);
			}
		}
	}

	private static function step_43_snapshots(): void {
		$snapshots = Schema::table( 'question_snapshots' );
		$after_id  = 0;
		$after_l   = '';
		while ( true ) {
			$rows = Db::rows( 'SELECT version_id, lang, display FROM %i WHERE (version_id > %d OR (version_id = %d AND lang > %s)) ORDER BY version_id, lang LIMIT %d', $snapshots, $after_id, $after_id, $after_l, self::AQ_CHUNK );
			if ( array() === $rows ) {
				return;
			}
			$decoded     = array();
			$attachments = array();
			foreach ( $rows as $row ) {
				$after_id = (int) $row['version_id'];
				$after_l  = (string) $row['lang'];
				$data     = json_decode( (string) $row['display'], true );
				if ( ! is_array( $data ) ) {
					continue;
				}
				$decoded[] = array( $after_id, $after_l, $data );
				if ( '' === $after_l ) {
					$meta = is_array( $data['meta'] ?? null ) ? $data['meta'] : array();
					foreach ( array( (int) ( $meta['media'] ?? 0 ), (int) ( $meta['stimulus']['media'] ?? 0 ) ) as $attachment ) {
						if ( $attachment > 0 ) {
							$attachments[ $attachment ] = true;
						}
					}
				}
			}
			if ( array() !== $attachments ) {
				_prime_post_caches( array_keys( $attachments ), false, false );
			}
			$words = array();
			$media = array();
			$keys  = array();
			foreach ( $decoded as list( $version_id, $lang, $data ) ) {
				if ( '' === $lang ) {
					$display = \RoosterLMS\Assessment\QuestionDisplay::from_json( (string) wp_json_encode( $data ) );
					if ( null === $display ) {
						continue;
					}
					$derived = \RoosterLMS\Assessment\Derived::of_display( $display );
					$w       = $derived['word_count'];
					$m       = $derived['media'];
				} else {
					$w = \RoosterLMS\Assessment\Derived::words_of_texts( $data );
					$m = 0;
				}
				if ( 0 === $w && 0 === $m ) {
					continue;
				}
				$words[] = SqlPart::of( 'WHEN version_id = %d AND lang = %s THEN %d', $version_id, $lang, $w );
				$media[] = SqlPart::of( 'WHEN version_id = %d AND lang = %s THEN %d', $version_id, $lang, $m );
				$keys[]  = SqlPart::of( '(%d, %s)', $version_id, $lang );
			}
			if ( array() !== $keys ) {
				Db::query(
					SqlPart::of( 'UPDATE %i SET word_count = CASE ', $snapshots )
						->add( SqlPart::join( ' ', $words ) )
						->add( ' ELSE word_count END, media = CASE ' )
						->add( SqlPart::join( ' ', $media ) )
						->add( ' ELSE media END WHERE (version_id, lang) IN (' )
						->add( SqlPart::join( ', ', $keys ) )
						->add( ')' )
				);
			}
		}
	}

	public const REGF_COUNTS_OPTION = 'rooster_lms_regf_step32';

	private const REGF_CHUNK = 500;

	private static function step_32(): void {
		global $wpdb;
		if ( null === Db::value( 'SHOW TABLES LIKE %s', $wpdb->esc_like( Schema::table( 'member_field_values' ) ) ) ) {
			$error = new \RoosterLMS\Data\DbError( 'schema 32 table missing', 0, 'member_field_values' );
			throw $error;
		}
		$counts = array(
			'converted'  => 0,
			'dropped'    => 0,
			'flat_place' => 0,
			'no_roles'   => 0,
		);
		$after  = 0;
		do {
			$result = \RoosterLMS\Data\Transaction::run(
				static function () use ( $after ): array {
					return self::step_32_chunk( $after );
				}
			);
			foreach ( $result['counts'] as $name => $n ) {
				$counts[ $name ] += $n;
			}
			foreach ( $result['changed'] as $org_id ) {
				Services::org_repo()->forget( $org_id );
			}
			$after = $result['last'];
		} while ( $result['more'] );
		$stored = get_option( self::REGF_COUNTS_OPTION, array() );
		if ( ! is_array( $stored ) || array() === $stored || array_sum( $counts ) > 0 ) {
			update_option( self::REGF_COUNTS_OPTION, $counts, false );
		}
	}

	private static function step_32_chunk( int $after ): array {
		$counts = array(
			'converted'  => 0,
			'dropped'    => 0,
			'flat_place' => 0,
			'no_roles'   => 0,
		);
		$rows  = Db::rows( 'SELECT id, settings FROM %i WHERE id > %d ORDER BY id LIMIT %d FOR UPDATE', Schema::table( 'orgs' ), $after, self::REGF_CHUNK );
		$last  = $after;
		$todo  = array();
		$needs = array();
		foreach ( $rows as $row ) {
			$last = (int) $row['id'];
			$data = json_decode( (string) $row['settings'], true );
			if ( ! is_array( $data ) || ! array_key_exists( 'required_fields', $data ) ) {
				continue;
			}
			$todo[ $last ] = $data;
			if ( ! is_array( $data['registration'] ?? null ) && is_array( $data['required_fields'] ) && ! \RoosterLMS\Orgs\Registration\FieldSet::legacy_is_default( $data['required_fields'] ) ) {
				$needs[] = $last;
			}
		}
		$roles = array();
		if ( array() !== $needs ) {
			foreach ( Db::rows( 'SELECT o.org_id, COUNT(*) AS n FROM %i o JOIN %i r ON r.id = o.job_role_id AND r.state = 1 WHERE o.org_id IN (' . implode( ',', array_fill( 0, count( $needs ), '%d' ) ) . ') GROUP BY o.org_id', array_merge( array( Schema::table( 'org_job_roles' ), Schema::table( 'job_roles' ) ), $needs ) ) as $row ) {
				$roles[ (int) $row['org_id'] ] = (int) $row['n'];
			}
		}
		$writes = array();
		foreach ( $todo as $org_id => $data ) {
			$required = is_array( $data['required_fields'] ) ? $data['required_fields'] : array();
			unset( $data['required_fields'] );
			if ( is_array( $data['registration'] ?? null ) || \RoosterLMS\Orgs\Registration\FieldSet::legacy_is_default( $required ) ) {
				++$counts['dropped'];
			} else {
				$settings = \RoosterLMS\Orgs\OrgSettings::from_json( (string) wp_json_encode( $data ) );
				$set      = \RoosterLMS\Orgs\Registration\FieldSet::from_legacy( $required, $settings );
				$place    = $set->place;
				$entries  = $set->entries;
				if ( $set->place_required() && array() === $settings->types() ) {
					$place = \RoosterLMS\Orgs\Registration\FieldSet::PLACE_OPTIONAL;
					++$counts['flat_place'];
				}
				if ( $set->core_required( 'job_role' ) && 0 === ( $roles[ $org_id ] ?? 0 ) ) {
					$entries = array_map( static fn( $d ) => 'job_role' === $d->core ? $d->with_mode( \RoosterLMS\Orgs\Registration\FieldDef::OPTIONAL ) : $d, $entries );
					++$counts['no_roles'];
				}
				$data['registration'] = $set->with( $entries, 1, $place )->to_array();
				++$counts['converted'];
			}
			$writes[ $org_id ] = (string) wp_json_encode( $data );
		}
		if ( array() !== $writes ) {
			$args = array( Schema::table( 'orgs' ) );
			foreach ( $writes as $org_id => $json ) {
				$args[] = $org_id;
				$args[] = $json;
			}
			Db::query( 'UPDATE %i SET settings = CASE id' . implode( '', array_fill( 0, count( $writes ), ' WHEN %d THEN %s' ) ) . ' END WHERE id IN (' . implode( ',', array_fill( 0, count( $writes ), '%d' ) ) . ')', array_merge( $args, array_keys( $writes ) ) );
		}
		return array(
			'changed' => array_keys( $writes ),
			'last'    => $last,
			'more'    => count( $rows ) === self::REGF_CHUNK,
			'counts'  => $counts,
		);
	}

	public static function field_values_ready(): bool {
		return self::installed_version() >= 32;
	}

	public static function w2_columns_ready(): bool {
		global $wpdb;
		if ( null === Db::value( 'SHOW TABLES LIKE %s', $wpdb->esc_like( Schema::table( 'external_courses' ) ) ) ) {
			return false;
		}
		foreach ( array( 'course_reviews', 'discussions' ) as $name ) {
			if ( null === Db::row( 'SHOW COLUMNS FROM %i LIKE %s', Schema::table( $name ), 'author_label' ) ) {
				return false;
			}
		}
		return true;
	}

	public static function ma_columns_ready(): bool {
		$needed = array(
			'courses'      => array( 'level_id', 'badge_id', 'coming_soon_flags', 'page_template', 'grade_scale_id' ),
			'units'        => array( 'lesson_kind', 'duration_label' ),
			'course_stats' => array( 'rating_count', 'rating_avg' ),
			'questions'    => array( 'is_required', 'category_id' ),
			'categories'   => array( 'page_template' ),
		);
		foreach ( $needed as $name => $columns ) {
			foreach ( $columns as $column ) {
				if ( null === Db::row( 'SHOW COLUMNS FROM %i LIKE %s', Schema::table( $name ), $column ) ) {
					return false;
				}
			}
		}
		return true;
	}

	public static function certificates_columns_ready(): bool {
		return null !== Db::row( 'SHOW COLUMNS FROM %i LIKE %s', Schema::table( 'certificates' ), 'kind' )
			&& null !== Db::row( 'SHOW COLUMNS FROM %i LIKE %s', Schema::table( 'certificates' ), 'source_id' )
			&& null !== Db::row( 'SHOW COLUMNS FROM %i LIKE %s', Schema::table( 'live_sessions' ), 'certificate_tpl_id' );
	}

	private static function index_names( string $table ): array {
		$keys = array();
		foreach ( Db::rows( 'SHOW INDEX FROM %i', $table ) as $row ) {
			$keys[ (string) $row['Key_name'] ] = true;
		}
		return $keys;
	}

	public static function hc11_columns_ready(): bool {
		return null !== Db::row( 'SHOW COLUMNS FROM %i LIKE %s', Schema::table( 'org_units' ), 'type_id' )
			&& null !== Db::row( 'SHOW COLUMNS FROM %i LIKE %s', Schema::table( 'progress' ), 'steer_org_id' );
	}

	public static function assert_placements_ready(): void {
		if ( ! self::placements_ready() ) {
			$error = new \RoosterLMS\Data\DbError( 'schema 22 not installed yet', 0, 'member_placements' );
			throw $error;
		}
	}

	public static function placements_ready(): bool {
		return self::installed_version() >= 22;
	}

	public static function hc_copy_gaps(): array {
		$units = Schema::table( 'org_units' );
		return array(
			'missing' => (int) Db::value( 'SELECT COUNT(*) FROM %i l LEFT JOIN %i u ON u.id = l.id AND u.org_id = l.org_id AND u.depth > 0 WHERE u.id IS NULL', Schema::table( 'org_locations' ), $units ),
			'roots'   => (int) Db::value( 'SELECT COUNT(*) FROM %i o LEFT JOIN %i r ON r.org_id = o.id AND r.root_slot = 1 WHERE r.id IS NULL', Schema::table( 'orgs' ), $units ),
		);
	}

	public static function assert_units_ready(): void {
		if ( self::installed_version() < 20 ) {
			$error = new \RoosterLMS\Data\DbError( 'schema 20 not installed yet', 0, 'org_units' );
			throw $error;
		}
	}

	private static function auto_increment( string $table ): int {
		$row = Db::row( 'SHOW CREATE TABLE %i', $table );
		$sql = is_array( $row ) ? (string) ( array_values( $row )[1] ?? '' ) : '';
		return 1 === preg_match( '/\bAUTO_INCREMENT=(\d+)/', $sql, $m ) ? (int) $m[1] : 0;
	}

	private static function raise_auto_increment( string $units, int $next ): void {
		if ( $next <= 1 || self::auto_increment( $units ) >= $next ) {
			return;
		}
		$probe = $next - 1;
		if ( null !== Db::value( 'SELECT id FROM %i WHERE id = %d', $units, $probe ) ) {
			return;
		}
		Db::query( "INSERT INTO %i (id, org_id, parent_id, depth, root_slot, position, name, address, seat_limit, state, created_at, updated_at) VALUES (%d, 0, 0, 0, NULL, 0, '', '', NULL, 2, %s, %s)", $units, $probe, Clock::now(), Clock::now() );
		Db::query( 'DELETE FROM %i WHERE id = %d AND org_id = 0', $units, $probe );
	}

	private static function is_full_request(): bool {
		if ( wp_doing_ajax() ) {
			return false;
		}
		if ( defined( 'REST_REQUEST' ) && REST_REQUEST ) {
			return false;
		}
		return true;
	}

	private static function acquire_lock(): bool {
		$now = Clock::timestamp();
		if ( add_option( self::LOCK_OPTION, $now, '', false ) ) {
			return true;
		}
		$held_since = (int) get_option( self::LOCK_OPTION, 0 );
		if ( $held_since > 0 && $now - $held_since < self::LOCK_SECONDS ) {
			return false;
		}
		update_option( self::LOCK_OPTION, $now, false );
		return true;
	}

	private static function release_lock(): void {
		delete_option( self::LOCK_OPTION );
	}
}