<?php
/**
 * Database migration class
 *
 * Handles database schema changes between versions
 *
 * @package KarateClubManager
 */

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

/**
 * Database Migration Class
 */
class MACM_Database_Migration {

	/**
	 * Run migrations
	 */
	public static function run_migrations() {
		$current_db_version = get_option( 'macm_db_version', '0.0.0' );

		// Migration for v1.0.73: event_date -> start_date/end_date.
		if ( version_compare( $current_db_version, '1.0.73', '<' ) ) {
			self::migrate_to_1_0_73();
		}

		// Migration for v1.0.275: Clean up orphaned member data.
		if ( version_compare( $current_db_version, '1.0.275', '<' ) ) {
			self::migrate_to_1_0_275();
		}

		// Migration for v1.0.307: Add instructor_ids to attendance records.
		if ( version_compare( $current_db_version, '1.0.307', '<' ) ) {
			self::migrate_to_1_0_307();
		}

		// Update database version.
		update_option( 'macm_db_version', MACM_VERSION );
	}

	/**
	 * Migrate to v1.0.73
	 * Changes: event_date -> start_date, adds end_date
	 */
	private static function migrate_to_1_0_73() {
		global $wpdb;
		$table_name = $wpdb->prefix . 'macm_events';

		// Check if table exists - with caching.
		$cache_key    = 'macm_migration_table_check_' . $table_name;
		$table_exists = wp_cache_get( $cache_key, 'macm' );

		if ( false === $table_exists ) {
			$table_check  = $wpdb->get_var(
				$wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name )
			);
			$table_exists = ( $table_check === $table_name ) ? 'yes' : 'no';
			wp_cache_set( $cache_key, $table_exists, 'macm', 60 );
		}

		if ( 'yes' !== $table_exists ) {
			return; // Table doesn't exist, will be created fresh.
		}

		// Check if start_date already exists (migration already done).
		$column_cache_key = 'macm_migration_column_check_start_date';
		$column_exists    = wp_cache_get( $column_cache_key, 'macm' );

		if ( false === $column_exists ) {
			$column_count  = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
					WHERE TABLE_SCHEMA = %s
					AND TABLE_NAME = %s
					AND COLUMN_NAME = 'start_date'",
					DB_NAME,
					$table_name
				)
			);
			$column_exists = $column_count ? 'yes' : 'no';
			wp_cache_set( $column_cache_key, $column_exists, 'macm', 60 );
		}

		if ( 'yes' === $column_exists ) {
			return; // Already migrated.
		}

		// Invalidate cache before schema changes.
		wp_cache_delete( $cache_key, 'macm' );
		wp_cache_delete( $column_cache_key, 'macm' );

		// Perform migration using %i placeholder for table identifier.
		// 1. Rename event_date to start_date.
		$wpdb->query(
			$wpdb->prepare( 'ALTER TABLE %i CHANGE COLUMN event_date start_date DATE NOT NULL', $table_name )
		);

		// 2. Add end_date column (copy value from start_date)
		$wpdb->query(
			$wpdb->prepare( 'ALTER TABLE %i ADD COLUMN end_date DATE NOT NULL AFTER start_date', $table_name )
		);

		// 3. Set end_date to same as start_date for existing events
		$wpdb->query(
			$wpdb->prepare( "UPDATE %i SET end_date = start_date WHERE end_date IS NULL OR end_date = '0000-00-00'", $table_name )
		);

		// 4. Drop old index on event_date if exists (suppress errors if index doesn't exist)
		$wpdb->query(
			$wpdb->prepare( 'ALTER TABLE %i DROP INDEX event_date', $table_name )
		);

		// 5. Add new indexes
		$wpdb->query(
			$wpdb->prepare( 'ALTER TABLE %i ADD KEY start_date (start_date)', $table_name )
		);
		$wpdb->query(
			$wpdb->prepare( 'ALTER TABLE %i ADD KEY end_date (end_date)', $table_name )
		);

		// Invalidate cache after schema changes.
		wp_cache_flush();
	}

	/**
	 * Migrate to v1.0.275
	 * Cleanup: Remove orphaned records from member-related tables
	 *
	 * Previous versions had a bug where deleting a member didn't clean up
	 * related records in macm_member_groups, macm_event_registrations,
	 * and macm_grading_history tables.
	 *
	 * @since 1.0.275
	 */
	private static function migrate_to_1_0_275() {
		global $wpdb;

		$members_table             = $wpdb->prefix . 'macm_members';
		$member_groups_table       = $wpdb->prefix . 'macm_member_groups';
		$event_registrations_table = $wpdb->prefix . 'macm_event_registrations';
		$grading_history_table     = $wpdb->prefix . 'macm_grading_history';

		// Delete orphaned member group associations.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query(
			$wpdb->prepare(
				'DELETE FROM %i WHERE member_id NOT IN (SELECT id FROM %i)',
				$member_groups_table,
				$members_table
			)
		);

		// Delete orphaned event registrations.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query(
			$wpdb->prepare(
				'DELETE FROM %i WHERE member_id NOT IN (SELECT id FROM %i)',
				$event_registrations_table,
				$members_table
			)
		);

		// Delete orphaned grading history.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query(
			$wpdb->prepare(
				'DELETE FROM %i WHERE member_id NOT IN (SELECT id FROM %i)',
				$grading_history_table,
				$members_table
			)
		);

		// Invalidate all member-related caches.
		wp_cache_flush();
	}

	/**
	 * Migrate to v1.0.307
	 * Add instructor_ids column to attendance table and populate existing records
	 *
	 * This ensures historical attendance records preserve instructor information
	 * instead of dynamically joining to current class-instructor assignments.
	 * When an instructor is changed/added for a class, old attendance records
	 * will continue showing the instructor who was teaching at the time.
	 *
	 * @since 1.0.307
	 */
	private static function migrate_to_1_0_307() {
		global $wpdb;

		$table_attendance        = $wpdb->prefix . 'macm_attendance';
		$table_class_instructors = $wpdb->prefix . 'macm_class_instructors';

		// Check if table exists.
		$cache_key    = 'macm_migration_table_check_' . $table_attendance;
		$table_exists = wp_cache_get( $cache_key, 'macm' );

		if ( false === $table_exists ) {
			$table_check  = $wpdb->get_var(
				$wpdb->prepare( 'SHOW TABLES LIKE %s', $table_attendance )
			);
			$table_exists = ( $table_check === $table_attendance ) ? 'yes' : 'no';
			wp_cache_set( $cache_key, $table_exists, 'macm', 60 );
		}

		if ( 'yes' !== $table_exists ) {
			return; // Table doesn't exist, will be created fresh with the column.
		}

		// Check if instructor_ids column already exists.
		$column_cache_key = 'macm_migration_column_check_instructor_ids';
		$column_exists    = wp_cache_get( $column_cache_key, 'macm' );

		if ( false === $column_exists ) {
			$column_count  = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
					WHERE TABLE_SCHEMA = %s
					AND TABLE_NAME = %s
					AND COLUMN_NAME = 'instructor_ids'",
					DB_NAME,
					$table_attendance
				)
			);
			$column_exists = $column_count ? 'yes' : 'no';
			wp_cache_set( $column_cache_key, $column_exists, 'macm', 60 );
		}

		// Invalidate cache before potential schema changes.
		wp_cache_delete( $cache_key, 'macm' );
		wp_cache_delete( $column_cache_key, 'macm' );

		// Add column if it doesn't exist.
		if ( 'yes' !== $column_exists ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query(
				$wpdb->prepare(
					'ALTER TABLE %i ADD COLUMN instructor_ids varchar(255) DEFAULT NULL AFTER marked_by',
					$table_attendance
				)
			);
		}

		// Populate existing attendance records with current instructor IDs.
		// This is the best we can do for historical data - use current assignments.
		// Get all distinct class_ids from attendance records that don't have instructor_ids set.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$records_to_update = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT DISTINCT class_id FROM %i WHERE instructor_ids IS NULL',
				$table_attendance
			)
		);

		foreach ( $records_to_update as $record ) {
			$class_id = absint( $record->class_id );

			// Get current instructors for this class.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$instructor_ids = $wpdb->get_col(
				$wpdb->prepare(
					'SELECT instructor_id FROM %i WHERE class_id = %d ORDER BY instructor_id',
					$table_class_instructors,
					$class_id
				)
			);

			if ( ! empty( $instructor_ids ) ) {
				$instructor_ids_str = implode( ',', array_map( 'absint', $instructor_ids ) );

				// Update all attendance records for this class that don't have instructor_ids.
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->query(
					$wpdb->prepare(
						'UPDATE %i SET instructor_ids = %s WHERE class_id = %d AND instructor_ids IS NULL',
						$table_attendance,
						$instructor_ids_str,
						$class_id
					)
				);
			}
		}

		// Invalidate cache after schema changes.
		wp_cache_flush();
	}
}
