<?php
namespace AccessNow\SimpleLMS;

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

// This class reads and writes the plugin's own {prefix}slms_* tables, so direct
// queries are required; the data is per-user and changes on every action, so it is
// not cached. Table names are built from $wpdb->prefix and fixed strings, and every
// value goes through $wpdb->prepare() or $wpdb->insert/update/delete.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange

/**
 * One-time, versioned upgrade routine.
 *
 * Runs on plugins_loaded (and on activation) only when the stored database
 * version is older than DB_VERSION. Every step is safe to run twice.
 *
 * 5.0.0 moves the plugin's option-level data to the accessnow_slms_ prefix:
 *   slms_settings            -> accessnow_slms_settings            (copied, verified, then old deleted)
 *   slms_db_version          -> accessnow_slms_db_version          (written after all steps succeed)
 *   slms_rewrite_version     -> accessnow_slms_rewrite_version     (cache flag: old deleted, rules re-flushed)
 *   user meta slms_dismiss_reg_notice -> accessnow_slms_dismiss_reg_notice (copied per user, then old deleted)
 *   transient slms_import_notice      -> per-user accessnow_slms_import_notice_{id} (old deleted, 30 s lifetime)
 *   transients slms_reg_{hash}        -> accessnow_slms_reg_{hash} (old ones expire within an hour)
 *
 * Custom post types, post meta (_slms_*), custom tables ({prefix}slms_*), the
 * slms_student role and the manage_slms* capabilities keep their names.
 *
 * 5.1.0 gives each post type its own capabilities (edit_slms_courses, publish_slms_lessons, ...)
 * and grants them to the roles that can edit others' posts (administrators, editors), matching
 * their post capabilities (Capabilities::grant_matching_post_caps()).
 *
 * 5.2.0 adds slms_enrollments.expires_at (UTC; NULL means access never ends) through dbDelta.
 *
 * 5.3.0 adds slms_quiz_attempts.status ('graded' for every existing attempt, or 'pending').
 */
class Upgrader {

	/** Current database/data version. */
	const DB_VERSION = '5.3.0';

	/** Stored version (was `slms_db_version` before 5.0.0). */
	const VERSION_OPTION = 'accessnow_slms_db_version';

	/** Pre-5.0.0 version option. */
	const LEGACY_VERSION_OPTION = 'slms_db_version';

	/** Short-lived lock so two simultaneous requests don't upgrade at once. */
	const LOCK_OPTION = 'accessnow_slms_upgrade_lock';

	public static function init() {
		add_action( 'plugins_loaded', array( __CLASS__, 'maybe_upgrade' ), 1 );
	}

	/**
	 * The stored data version: the new option, else the pre-5.0.0 option, else ''.
	 *
	 * @return string
	 */
	public static function stored_version() {
		$version = get_option( self::VERSION_OPTION, '' );
		if ( ! is_string( $version ) || '' === $version ) {
			$version = get_option( self::LEGACY_VERSION_OPTION, '' );
		}
		return is_string( $version ) ? $version : '';
	}

	public static function maybe_upgrade() {
		$stored = self::stored_version();
		if ( '' !== $stored && version_compare( $stored, self::DB_VERSION, '>=' ) ) {
			return;
		}

		if ( ! self::acquire_lock() ) {
			return;
		}

		if ( '' === $stored ) {
			// Never recorded (very old install, or tables missing): create what's missing.
			// As before 5.0.0, an unknown version is treated as 1.0.0.
			Activator::create_tables();
			$stored = '1.0.0';
		}

		if ( version_compare( $stored, '2.0.0', '<' ) ) {
			self::migrate_enrollment_periods();
			$stored = self::set_version( '2.0.0' );
		}
		if ( version_compare( $stored, '3.0.0', '<' ) ) {
			self::migrate_race_condition_indexes();
			$stored = self::set_version( '3.0.0' );
		}
		if ( version_compare( $stored, '4.0.0', '<' ) ) {
			self::migrate_content_hash();
			$stored = self::set_version( '4.0.0' );
		}
		if ( version_compare( $stored, '5.0.0', '<' ) ) {
			// Only record 5.0.0 when every key moved; otherwise retry on the next request.
			if ( self::migrate_prefixed_keys() ) {
				$stored = self::set_version( '5.0.0' );
				delete_option( self::LEGACY_VERSION_OPTION );
			}
		}
		if ( version_compare( $stored, '5.0.0', '>=' ) && version_compare( $stored, '5.1.0', '<' ) ) {
			// The post types now have their own capabilities: admins and editors keep what they had.
			Capabilities::grant_matching_post_caps();
			$stored = self::set_version( '5.1.0' );
		}
		if ( version_compare( $stored, '5.1.0', '>=' ) && version_compare( $stored, '5.2.0', '<' ) ) {
			// slms_enrollments.expires_at: when a student's access to a course ends (NULL: never).
			Activator::create_tables();
			$stored = self::set_version( '5.2.0' );
		}
		if ( version_compare( $stored, '5.2.0', '>=' ) && version_compare( $stored, '5.3.0', '<' ) ) {
			// slms_quiz_attempts.status: 'graded', or 'pending' while a question waits to be marked.
			Activator::create_tables();
			$stored = self::set_version( '5.3.0' );
		}

		self::release_lock();
	}

	private static function set_version( $version ) {
		update_option( self::VERSION_OPTION, $version );
		return $version;
	}

	private static function acquire_lock() {
		$now = time();
		if ( add_option( self::LOCK_OPTION, $now, '', 'no' ) ) {
			return true;
		}
		$since = (int) get_option( self::LOCK_OPTION, 0 );
		if ( $since > 0 && ( $now - $since ) < 10 * MINUTE_IN_SECONDS ) {
			return false;
		}
		// Stale lock (a previous run died): take it over.
		update_option( self::LOCK_OPTION, $now, 'no' );
		return true;
	}

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

	/**
	 * Re-run the 3.0.0 index step (remove duplicate point awards, then add the unique index).
	 *
	 * Called on activation before Activator::create_tables(): some installs recorded a version
	 * past 3.0.0 without that step having run. Does nothing when the indexes exist.
	 */
	public static function repair_unique_indexes() {
		self::migrate_race_condition_indexes();
	}

	/* ─── 5.0.0: accessnow_slms_ prefix for options, user meta, transients ── */

	/**
	 * @return bool True when everything was moved (or there was nothing to move).
	 */
	private static function migrate_prefixed_keys() {
		$ok = self::move_option( Settings::LEGACY_OPTION, Settings::OPTION );

		// Cache flag only: dropping it makes Rewrites flush once with the new query vars.
		delete_option( 'slms_rewrite_version' );

		$ok = self::move_user_meta( 'slms_dismiss_reg_notice', 'accessnow_slms_dismiss_reg_notice' ) && $ok;

		// Short-lived notice (30 seconds); it is now stored per user.
		delete_transient( 'slms_import_notice' );

		return $ok;
	}

	/**
	 * Copy an option to its new name, check the copy, then delete the old one.
	 * An existing new option is never overwritten.
	 *
	 * @return bool
	 */
	private static function move_option( $old_key, $new_key ) {
		$old_value = get_option( $old_key, null );
		if ( null === $old_value ) {
			return true;
		}

		if ( null === get_option( $new_key, null ) ) {
			add_option( $new_key, $old_value );
			if ( get_option( $new_key, null ) !== $old_value ) {
				return false; // Copy failed: keep the old option and retry later.
			}
		}

		delete_option( $old_key );
		return null === get_option( $old_key, null );
	}

	/**
	 * Copy a user meta key to its new name for every user, then delete the old key.
	 *
	 * @return bool
	 */
	private static function move_user_meta( $old_key, $new_key ) {
		$user_ids = get_users(
			array(
				'blog_id'  => 0,
				'meta_key' => $old_key, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- one-time upgrade.
				'fields'   => 'ID',
			)
		);

		$ok = true;
		foreach ( $user_ids as $user_id ) {
			$value = get_user_meta( $user_id, $old_key, true );
			if ( '' === get_user_meta( $user_id, $new_key, true ) ) {
				update_user_meta( $user_id, $new_key, $value );
			}
			if ( (string) get_user_meta( $user_id, $new_key, true ) === '' && '' !== (string) $value ) {
				$ok = false; // Copy failed for this user: keep the old value.
				continue;
			}
			delete_user_meta( $user_id, $old_key );
		}
		return $ok;
	}

	/* ─── Schema steps from earlier versions (unchanged SQL) ─────────────── */

	/**
	 * 2.0.0: enrolment periods (enrollment_id columns, backfill, unique keys).
	 */
	private static function migrate_enrollment_periods() {
		global $wpdb;

		// Add enrollment_id columns if they don't exist
		$columns_to_add = array(
			'slms_progress'      => 'enrollment_id',
			'slms_quiz_attempts' => 'enrollment_id',
			'slms_certificates'  => 'enrollment_id',
			'slms_points'        => 'enrollment_id',
		);

		// Table and column names come from the fixed list above.
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		foreach ( $columns_to_add as $table => $column ) {
			$col_exists = $wpdb->get_var( "SHOW COLUMNS FROM {$wpdb->prefix}{$table} LIKE '{$column}'" );
			if ( ! $col_exists ) {
				$wpdb->query( "ALTER TABLE {$wpdb->prefix}{$table} ADD COLUMN {$column} BIGINT(20) UNSIGNED NOT NULL DEFAULT 0" );
			}
		}
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Backfill enrollment_id from existing enrollments
		$wpdb->query(
			"UPDATE {$wpdb->prefix}slms_progress p
            INNER JOIN {$wpdb->prefix}slms_enrollments e ON p.user_id = e.user_id AND p.course_id = e.course_id
            SET p.enrollment_id = e.id
            WHERE p.enrollment_id = 0"
		);

		$wpdb->query(
			"UPDATE {$wpdb->prefix}slms_quiz_attempts q
            INNER JOIN {$wpdb->prefix}slms_enrollments e ON q.user_id = e.user_id AND q.course_id = e.course_id
            SET q.enrollment_id = e.id
            WHERE q.enrollment_id = 0"
		);

		$wpdb->query(
			"UPDATE {$wpdb->prefix}slms_certificates c
            INNER JOIN {$wpdb->prefix}slms_enrollments e ON c.user_id = e.user_id AND c.course_id = e.course_id
            SET c.enrollment_id = e.id
            WHERE c.enrollment_id = 0"
		);

		// Drop old unique keys and add new ones for enrollments table
		$enrollments_indexes = $wpdb->get_results( "SHOW INDEX FROM {$wpdb->prefix}slms_enrollments WHERE Key_name = 'user_course'" );
		if ( ! empty( $enrollments_indexes ) && 0 === (int) $enrollments_indexes[0]->Non_unique ) {
			$wpdb->query( "ALTER TABLE {$wpdb->prefix}slms_enrollments DROP INDEX user_course, ADD INDEX user_course (user_id, course_id)" );
		}

		// Add status index if not exists
		$status_idx = $wpdb->get_var( "SHOW INDEX FROM {$wpdb->prefix}slms_enrollments WHERE Key_name = 'idx_status'" );
		if ( ! $status_idx ) {
			$wpdb->query( "ALTER TABLE {$wpdb->prefix}slms_enrollments ADD INDEX idx_status (user_id, course_id, status)" );
		}

		// Progress: change unique key from user_lesson to enrollment_lesson
		$progress_idx = $wpdb->get_var( "SHOW INDEX FROM {$wpdb->prefix}slms_progress WHERE Key_name = 'user_lesson'" );
		if ( $progress_idx ) {
			$wpdb->query( "ALTER TABLE {$wpdb->prefix}slms_progress DROP INDEX user_lesson" );
		}
		$enrollment_lesson_idx = $wpdb->get_var( "SHOW INDEX FROM {$wpdb->prefix}slms_progress WHERE Key_name = 'enrollment_lesson'" );
		if ( ! $enrollment_lesson_idx ) {
			$wpdb->query( "ALTER TABLE {$wpdb->prefix}slms_progress ADD UNIQUE INDEX enrollment_lesson (enrollment_id, lesson_id)" );
		}

		// Certificates: change unique key from user_course to enrollment_course
		$cert_idx = $wpdb->get_var( "SHOW INDEX FROM {$wpdb->prefix}slms_certificates WHERE Key_name = 'user_course'" );
		if ( $cert_idx ) {
			$wpdb->query( "ALTER TABLE {$wpdb->prefix}slms_certificates DROP INDEX user_course" );
		}
		$enrollment_course_idx = $wpdb->get_var( "SHOW INDEX FROM {$wpdb->prefix}slms_certificates WHERE Key_name = 'enrollment_course'" );
		if ( ! $enrollment_course_idx ) {
			$wpdb->query( "ALTER TABLE {$wpdb->prefix}slms_certificates ADD UNIQUE INDEX enrollment_course (enrollment_id, course_id)" );
		}

		// Quiz attempts: add enrollment_quiz index
		$eq_idx = $wpdb->get_var( "SHOW INDEX FROM {$wpdb->prefix}slms_quiz_attempts WHERE Key_name = 'idx_enrollment_quiz'" );
		if ( ! $eq_idx ) {
			$wpdb->query( "ALTER TABLE {$wpdb->prefix}slms_quiz_attempts ADD INDEX idx_enrollment_quiz (enrollment_id, quiz_id)" );
		}
	}

	/**
	 * 3.0.0: unique index on slms_points to prevent duplicate awards,
	 * and a user index on slms_certificates.
	 */
	private static function migrate_race_condition_indexes() {
		global $wpdb;

		// Add unique index on slms_points to prevent duplicate point awards
		$points_idx = $wpdb->get_var( "SHOW INDEX FROM {$wpdb->prefix}slms_points WHERE Key_name = 'idx_points_dedup'" );
		if ( ! $points_idx ) {
			// Remove any existing duplicates before adding unique index
			$wpdb->query(
				"DELETE p1 FROM {$wpdb->prefix}slms_points p1
                INNER JOIN {$wpdb->prefix}slms_points p2
                WHERE p1.id > p2.id
                AND p1.user_id = p2.user_id
                AND p1.action = p2.action
                AND p1.reference_id = p2.reference_id
                AND p1.enrollment_id = p2.enrollment_id"
			);

			$wpdb->query( "ALTER TABLE {$wpdb->prefix}slms_points ADD UNIQUE INDEX idx_points_dedup (user_id, action, reference_id, enrollment_id)" );
		}

		// Add user index on slms_certificates if missing
		$cert_user_idx = $wpdb->get_var( "SHOW INDEX FROM {$wpdb->prefix}slms_certificates WHERE Key_name = 'idx_cert_user'" );
		if ( ! $cert_user_idx ) {
			$wpdb->query( "ALTER TABLE {$wpdb->prefix}slms_certificates ADD INDEX idx_cert_user (user_id)" );
		}
	}

	/**
	 * 4.0.0: content_hash column on slms_progress for content change detection.
	 */
	private static function migrate_content_hash() {
		global $wpdb;

		// Add content_hash column to slms_progress if it doesn't exist
		$col_exists = $wpdb->get_var( "SHOW COLUMNS FROM {$wpdb->prefix}slms_progress LIKE 'content_hash'" );
		if ( ! $col_exists ) {
			$wpdb->query( "ALTER TABLE {$wpdb->prefix}slms_progress ADD COLUMN content_hash VARCHAR(32) DEFAULT NULL AFTER completed_at" );
		}
	}
}