<?php
/**
 * Database management class
 *
 * @package Karate_Club_Manager
 */

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

/**
 * Database class
 */
class MACM_Database {
	/**
	 * Create all database tables
	 */
	public static function create_tables() {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		require_once MACM_PLUGIN_DIR . 'includes/database/schema.php';

		// Get the schema.
		$schema = macm_get_schema();

		// Split schema into individual table statements.
		$tables = array_filter( explode( ';', $schema ) );

		// Create each table.
		foreach ( $tables as $table ) {
			$table = trim( $table );
			if ( ! empty( $table ) ) {
				dbDelta( $table );
			}
		}

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

	/**
	 * Drop all database tables
	 *
	 * WARNING: This will permanently delete all data!
	 * Should only be called during plugin uninstall.
	 */
	public static function drop_tables() {
		global $wpdb;

		$table_prefix = $wpdb->prefix;

		$tables = array(
			"{$table_prefix}macm_event_registrations",
			"{$table_prefix}macm_events",
			"{$table_prefix}macm_trial_bookings",
			"{$table_prefix}macm_class_instructors",
			"{$table_prefix}macm_instructors",
			"{$table_prefix}macm_product_members",
			"{$table_prefix}macm_attendance",
			"{$table_prefix}macm_class_enrollments",
			"{$table_prefix}macm_member_groups",
			"{$table_prefix}macm_clubs",
			"{$table_prefix}macm_groups",
			"{$table_prefix}macm_locations",
			"{$table_prefix}macm_classes",
			"{$table_prefix}macm_members",
			"{$table_prefix}macm_training_videos",
			"{$table_prefix}macm_belt_colors",
			"{$table_prefix}macm_membership_types",
			"{$table_prefix}macm_page_access_rules",
			"{$table_prefix}macm_page_access",
		);

		foreach ( $tables as $table ) {
			$cache_key = 'macm_drop_table_' . $table;
			wp_cache_delete( $cache_key, 'macm' );
			$wpdb->query(
				$wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table )
			);
			wp_cache_delete( $cache_key, 'macm' );
		}

		// Delete database version option.
		delete_option( 'macm_db_version' );
	}

	/**
	 * Check if database needs to be updated
	 *
	 * @return bool True if update needed, false otherwise.
	 */
	public static function needs_update() {
		$current_version = get_option( 'macm_db_version', '0.0.0' );
		return version_compare( $current_version, MACM_VERSION, '<' );
	}

	/**
	 * Run database migrations for version upgrades
	 *
	 * @since 1.0.307
	 */
	public static function run_migrations() {
		$current_version = get_option( 'macm_db_version', '0.0.0' );

		// Migration: Add instructor_ids column to attendance table (v1.0.307).
		if ( version_compare( $current_version, '1.0.307', '<' ) ) {
			self::migrate_attendance_instructor_ids();
		}
	}

	/**
	 * Migration: 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.
	 *
	 * @since 1.0.307
	 */
	private static function migrate_attendance_instructor_ids() {
		global $wpdb;

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

		// Check if column already exists.
		$column_exists = $wpdb->get_results(
			$wpdb->prepare(
				'SHOW COLUMNS FROM %i LIKE %s',
				$table_attendance,
				'instructor_ids'
			)
		);

		// Add column if it doesn't exist.
		if ( empty( $column_exists ) ) {
			$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 attendance records that don't have instructor_ids set.
		$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.
			$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.
				$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
					)
				);
			}
		}
	}

	/**
	 * Update database version
	 */
	public static function update_version() {
		update_option( 'macm_db_version', MACM_VERSION );
	}

	/**
	 * Check if all tables exist
	 *
	 * @return bool True if all tables exist, false otherwise.
	 */
	public static function tables_exist() {
		global $wpdb;

		$table_prefix = $wpdb->prefix;

		$tables = array(
			"{$table_prefix}macm_members",
			"{$table_prefix}macm_classes",
			"{$table_prefix}macm_locations",
			"{$table_prefix}macm_groups",
			"{$table_prefix}macm_member_groups",
			"{$table_prefix}macm_clubs",
			"{$table_prefix}macm_class_enrollments",
			"{$table_prefix}macm_attendance",
			"{$table_prefix}macm_product_members",
			"{$table_prefix}macm_belt_colors",
			"{$table_prefix}macm_membership_types",
			"{$table_prefix}macm_page_access",
			"{$table_prefix}macm_page_access_rules",
			"{$table_prefix}macm_training_videos",
			"{$table_prefix}macm_instructors",
			"{$table_prefix}macm_class_instructors",
			"{$table_prefix}macm_trial_bookings",
			"{$table_prefix}macm_events",
			"{$table_prefix}macm_event_registrations",
		);

		foreach ( $tables as $table ) {
			$cache_key    = 'macm_table_exists_' . $table;
			$table_exists = wp_cache_get( $cache_key, 'macm' );

			if ( false === $table_exists ) {
				$table_exists = $wpdb->get_var(
					$wpdb->prepare( 'SHOW TABLES LIKE %s', $table )
				);
				wp_cache_set( $cache_key, $table_exists ? $table_exists : 'not_found', 'macm', 300 );
			}

			if ( 'not_found' === $table_exists || $table_exists !== $table ) {
				return false;
			}
		}

		return true;
	}
}
