<?php
/**
 * Database schema definition
 *
 * @package Karate_Club_Manager
 */

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

/**
 * Get the database schema
 *
 * @return string SQL statements for creating all tables.
 */
function macm_get_schema() {
	global $wpdb;

	$charset_collate = $wpdb->get_charset_collate();
	$table_prefix    = $wpdb->prefix;

	$schema = "
	CREATE TABLE {$table_prefix}macm_members (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		user_id bigint(20) UNSIGNED NOT NULL,
		full_name varchar(255) NOT NULL,
		date_of_birth date NOT NULL,
		membership_type_id bigint(20) UNSIGNED DEFAULT NULL,
		belt_color varchar(50) NOT NULL,
		group_id bigint(20) UNSIGNED DEFAULT NULL,
		club_id bigint(20) UNSIGNED DEFAULT NULL,
		weight decimal(5,2) DEFAULT NULL,
		height decimal(5,2) DEFAULT NULL,
		license_number varchar(100) DEFAULT NULL,
		license_expiration date DEFAULT NULL,
		photo_id bigint(20) UNSIGNED DEFAULT NULL,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		status varchar(20) NOT NULL DEFAULT 'active',
		PRIMARY KEY (id),
		KEY user_id (user_id),
		KEY group_id (group_id),
		KEY club_id (club_id),
		KEY membership_type_id (membership_type_id),
		KEY belt_color (belt_color),
		KEY status (status)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_classes (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		class_name varchar(255) NOT NULL,
		description text DEFAULT NULL,
		day_of_week tinyint(1) NOT NULL,
		start_time time NOT NULL,
		end_time time NOT NULL,
		location_id bigint(20) UNSIGNED NOT NULL,
		max_capacity int(11) DEFAULT NULL,
		sort_order int(11) DEFAULT 0,
		is_archived tinyint(1) NOT NULL DEFAULT 0,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		KEY location_id (location_id),
		KEY day_of_week (day_of_week),
		KEY is_archived (is_archived)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_locations (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		location_name varchar(255) NOT NULL,
		address text DEFAULT NULL,
		is_active tinyint(1) NOT NULL DEFAULT 1,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		KEY is_active (is_active)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_groups (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		group_name varchar(255) NOT NULL,
		description text DEFAULT NULL,
		is_active tinyint(1) NOT NULL DEFAULT 1,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		KEY is_active (is_active)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_member_groups (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		member_id bigint(20) UNSIGNED NOT NULL,
		group_id bigint(20) UNSIGNED NOT NULL,
		assigned_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		UNIQUE KEY member_group (member_id, group_id),
		KEY member_id (member_id),
		KEY group_id (group_id)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_clubs (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		club_name varchar(255) NOT NULL,
		description text DEFAULT NULL,
		is_active tinyint(1) NOT NULL DEFAULT 1,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		KEY is_active (is_active)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_class_enrollments (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		member_id bigint(20) UNSIGNED NOT NULL,
		class_id bigint(20) UNSIGNED NOT NULL,
		enrolled_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		removed_at datetime DEFAULT NULL,
		PRIMARY KEY (id),
		UNIQUE KEY member_class (member_id, class_id),
		KEY member_id (member_id),
		KEY class_id (class_id)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_attendance (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		member_id bigint(20) UNSIGNED NOT NULL,
		class_id bigint(20) UNSIGNED NOT NULL,
		attendance_date date NOT NULL,
		attendance_time time NOT NULL,
		marked_by bigint(20) UNSIGNED NOT NULL,
		instructor_ids varchar(255) DEFAULT NULL,
		notes text DEFAULT NULL,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		UNIQUE KEY attendance_record (member_id, class_id, attendance_date),
		KEY member_id (member_id),
		KEY class_id (class_id),
		KEY attendance_date (attendance_date),
		KEY marked_by (marked_by)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_product_members (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		order_id bigint(20) UNSIGNED NOT NULL,
		order_item_id bigint(20) UNSIGNED NOT NULL,
		product_id bigint(20) UNSIGNED NOT NULL,
		member_id bigint(20) UNSIGNED NOT NULL,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		KEY order_id (order_id),
		KEY member_id (member_id),
		KEY product_id (product_id)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_belt_colors (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		color_key varchar(50) NOT NULL,
		color_name varchar(100) NOT NULL,
		sort_order int(11) NOT NULL DEFAULT 0,
		is_active tinyint(1) NOT NULL DEFAULT 1,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		UNIQUE KEY color_key (color_key),
		KEY sort_order (sort_order),
		KEY is_active (is_active)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_membership_types (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		type_name varchar(100) NOT NULL,
		description text DEFAULT NULL,
		sort_order int(11) NOT NULL DEFAULT 0,
		is_active tinyint(1) NOT NULL DEFAULT 1,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		KEY sort_order (sort_order),
		KEY is_active (is_active)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_page_access (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		page_id bigint(20) UNSIGNED NOT NULL,
		access_type varchar(20) NOT NULL DEFAULT 'login_required',
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		UNIQUE KEY page_id (page_id),
		KEY access_type (access_type)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_page_access_rules (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		page_access_id bigint(20) UNSIGNED NOT NULL,
		rule_type varchar(20) NOT NULL,
		rule_value bigint(20) UNSIGNED NOT NULL,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		KEY page_access_id (page_access_id),
		KEY rule_type (rule_type),
		KEY rule_value (rule_value)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_training_videos (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		title varchar(255) NOT NULL,
		description text DEFAULT NULL,
		video_url varchar(500) NOT NULL,
		video_type varchar(20) NOT NULL DEFAULT 'youtube',
		duration varchar(20) DEFAULT NULL,
		thumbnail_id bigint(20) UNSIGNED DEFAULT NULL,
		belt_level varchar(100) DEFAULT NULL,
		category varchar(100) DEFAULT NULL,
		difficulty varchar(20) DEFAULT NULL,
		instructor_id bigint(20) UNSIGNED DEFAULT NULL,
		display_order int(11) NOT NULL DEFAULT 0,
		status varchar(20) NOT NULL DEFAULT 'published',
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		KEY display_order (display_order),
		KEY status (status),
		KEY belt_level (belt_level),
		KEY category (category),
		KEY difficulty (difficulty),
		KEY instructor_id (instructor_id)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_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)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_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)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_trial_bookings (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		full_name varchar(255) NOT NULL,
		is_for_child tinyint(1) DEFAULT 0,
		parent_name varchar(255) DEFAULT NULL,
		mobile varchar(20) DEFAULT NULL,
		email varchar(100) NOT NULL,
		class_id bigint(20) UNSIGNED DEFAULT NULL,
		status varchar(20) DEFAULT 'pending',
		admin_note text DEFAULT NULL,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		completed_at datetime DEFAULT NULL,
		PRIMARY KEY (id),
		KEY class_id (class_id),
		KEY status (status),
		KEY created_at (created_at)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_events (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		title varchar(255) NOT NULL,
		location varchar(255) DEFAULT NULL,
		start_date date NOT NULL,
		end_date date NOT NULL,
		event_time time NOT NULL,
		description text DEFAULT NULL,
		cost_info text DEFAULT NULL,
		image_id bigint(20) UNSIGNED DEFAULT NULL,
		closing_date date NOT NULL,
		status varchar(20) NOT NULL DEFAULT 'active',
		notification_email varchar(255) DEFAULT NULL,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		KEY start_date (start_date),
		KEY end_date (end_date),
		KEY status (status),
		KEY closing_date (closing_date)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_event_registrations (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		event_id bigint(20) UNSIGNED NOT NULL,
		user_id bigint(20) UNSIGNED NOT NULL,
		member_id bigint(20) UNSIGNED NOT NULL,
		registration_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		status varchar(20) NOT NULL DEFAULT 'confirmed',
		notes text DEFAULT NULL,
		PRIMARY KEY (id),
		UNIQUE KEY event_member (event_id, member_id),
		KEY event_id (event_id),
		KEY user_id (user_id),
		KEY member_id (member_id),
		KEY registration_date (registration_date),
		KEY status (status)
	) $charset_collate;

	CREATE TABLE {$table_prefix}macm_grading_history (
		id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		member_id bigint(20) UNSIGNED NOT NULL,
		belt_color_key varchar(50) NOT NULL,
		grading_date date NOT NULL,
		examiner_id bigint(20) UNSIGNED DEFAULT NULL,
		examiner_name varchar(255) DEFAULT NULL,
		score varchar(50) DEFAULT NULL,
		notes text DEFAULT NULL,
		created_by bigint(20) UNSIGNED NOT NULL,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		KEY member_id (member_id),
		KEY belt_color_key (belt_color_key),
		KEY grading_date (grading_date),
		KEY examiner_id (examiner_id),
		KEY created_by (created_by)
	) $charset_collate;
	";

	return $schema;
}
