<?php
/**
 * Database migration: Add instructors tables
 *
 * Run this file once to add the instructors and class_instructors tables
 *
 * @package KarateClubManager
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit; // Exit if accessed directly.
}

// Load WordPress.
require_once dirname( dirname( dirname( dirname( __DIR__ ) ) ) ) . '/wp-load.php';

if ( ! current_user_can( 'manage_options' ) ) {
	wp_die( 'You do not have permission to run this script.' );
}

global $wpdb;

$macm_charset_collate = $wpdb->get_charset_collate();

// Create instructors table.
$macm_sql_instructors = "CREATE TABLE {$wpdb->prefix}kcm_instructors (
	id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	full_name varchar(255) NOT NULL,
	email varchar(255) DEFAULT NULL,
	phone varchar(50) DEFAULT NULL,
	specialization text DEFAULT NULL,
	bio text DEFAULT NULL,
	is_active tinyint(1) NOT NULL DEFAULT 1,
	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
	KEY is_active (is_active)
) $macm_charset_collate;";

// Create class_instructors junction table.
$macm_sql_class_instructors = "CREATE TABLE {$wpdb->prefix}kcm_class_instructors (
	id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	class_id bigint(20) UNSIGNED NOT NULL,
	instructor_id bigint(20) UNSIGNED NOT NULL,
	assigned_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id),
	UNIQUE KEY class_instructor (class_id, instructor_id),
	KEY class_id (class_id),
	KEY instructor_id (instructor_id)
) $macm_charset_collate;";

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

echo '<h2>Adding Instructors Tables</h2>';

// Create instructors table.
echo '<p>Creating kcm_instructors table...</p>';
dbDelta( $macm_sql_instructors );

$macm_instructors_table = $wpdb->prefix . 'kcm_instructors';
$macm_table_check_key   = 'macm_table_exists_' . $macm_instructors_table;
wp_cache_delete( $macm_table_check_key, 'macm' );
$macm_table_exists = $wpdb->get_var(
	$wpdb->prepare( 'SHOW TABLES LIKE %s', $macm_instructors_table )
);
wp_cache_set( $macm_table_check_key, $macm_table_exists, 'macm', 60 );

if ( $macm_table_exists ) {
	echo '<p style="color: green;">✓ kcm_instructors table created successfully!</p>';
} else {
	echo '<p style="color: red;">✗ Failed to create kcm_instructors table</p>';
}

// Create class_instructors table.
echo '<p>Creating kcm_class_instructors table...</p>';
dbDelta( $macm_sql_class_instructors );

$macm_class_instructors_table = $wpdb->prefix . 'kcm_class_instructors';
$macm_table_check_key2        = 'macm_table_exists_' . $macm_class_instructors_table;
wp_cache_delete( $macm_table_check_key2, 'macm' );
$macm_table_exists2 = $wpdb->get_var(
	$wpdb->prepare( 'SHOW TABLES LIKE %s', $macm_class_instructors_table )
);
wp_cache_set( $macm_table_check_key2, $macm_table_exists2, 'macm', 60 );

if ( $macm_table_exists2 ) {
	echo '<p style="color: green;">✓ kcm_class_instructors table created successfully!</p>';
} else {
	echo '<p style="color: red;">✗ Failed to create kcm_class_instructors table</p>';
}

echo '<h3>Migration Complete!</h3>';
echo '<p>You can now manage instructors and assign them to classes.</p>';
