<?php
/**
 * Database installer and schema manager.
 *
 * @package TutorHelper_Bookings
 */

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

/**
 * Manages the TutorHelper Bookings database schema.
 */
final class TutorHelper_Bookings_Database {

	/**
	 * Option used to store the installed database version.
	 *
	 * @var string
	 */
	const VERSION_OPTION = 'tutorhelper_bookings_db_version';

	/**
	 * Create or update the plugin database tables.
	 *
	 * @return void
	 */
	public static function create_tables() {
		global $wpdb;

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

		$charset_collate = $wpdb->get_charset_collate();

		$availability_table = self::get_table_name( 'availability' );
		$types_table        = self::get_table_name( 'types' );
		$exceptions_table   = self::get_table_name( 'availability_exceptions' );
		$bookings_table     = self::get_table_name( 'bookings' );
		$student_audit_table     = self::get_table_name( 'student_audit' );
		$date_exclusions_table = self::get_table_name( 'date_exclusions' );



		$availability_sql = "CREATE TABLE {$availability_table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			tutor_user_id bigint(20) unsigned NOT NULL,
			title varchar(255) NOT NULL DEFAULT '',
			start_date date NOT NULL,
			end_date date DEFAULT NULL,
			weekdays varchar(50) NOT NULL DEFAULT '',
			start_time time NOT NULL,
			end_time time NOT NULL,
			timezone varchar(100) NOT NULL DEFAULT 'Europe/London',
			location_type varchar(50) NOT NULL DEFAULT 'online',
			location_types varchar(255) NOT NULL DEFAULT 'online',
			allowed_booking_type_ids text NULL,
			location_details text NULL,
			is_active tinyint(1) NOT NULL DEFAULT 1,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY tutor_user_id (tutor_user_id),
			KEY start_date (start_date),
			KEY end_date (end_date),
			KEY is_active (is_active)
		) {$charset_collate};";

		$types_sql = "CREATE TABLE {$types_table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			title varchar(255) NOT NULL,
			description text NULL,
			duration_minutes int(11) NOT NULL DEFAULT 60,
			price_minor_units bigint(20) unsigned NOT NULL DEFAULT 0,
			location_type varchar(50) NOT NULL DEFAULT 'online',
			location_types varchar(255) NOT NULL DEFAULT 'online',
			location_details text NULL,
			colour varchar(20) NOT NULL DEFAULT '#FF6326',
			online_booking_visibility varchar(50) NOT NULL DEFAULT 'bookable',
			notice_period_hours int(11) NOT NULL DEFAULT 48,
			cancellations_allowed tinyint(1) NOT NULL DEFAULT 0,
			cancellation_notice_hours int(11) NOT NULL DEFAULT 24,
			is_active tinyint(1) NOT NULL DEFAULT 1,
			sort_order int(11) NOT NULL DEFAULT 0,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY is_active (is_active),
			KEY online_booking_visibility (online_booking_visibility),
			KEY sort_order (sort_order)
		) {$charset_collate};";

		$exceptions_sql = "CREATE TABLE {$exceptions_table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			availability_id bigint(20) unsigned NOT NULL,
			exception_date date NOT NULL,
			exception_type varchar(50) NOT NULL DEFAULT 'unavailable',
			override_title varchar(255) DEFAULT NULL,
			override_start_time time DEFAULT NULL,
			override_end_time time DEFAULT NULL,
			override_timezone varchar(100) DEFAULT NULL,
			override_location_type varchar(50) DEFAULT NULL,
			override_location_types varchar(255) DEFAULT NULL,
			override_allowed_booking_type_ids text NULL,
			override_location_details text NULL,
			notes text NULL,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY availability_id (availability_id),
			KEY exception_date (exception_date),
			UNIQUE KEY availability_date_type (availability_id, exception_date, exception_type)
		) {$charset_collate};";

		$bookings_sql = "CREATE TABLE {$bookings_table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			booking_type_id bigint(20) unsigned NOT NULL,
			student_user_id bigint(20) unsigned NOT NULL,
			tutor_user_id bigint(20) unsigned NOT NULL,
			student_name varchar(255) NOT NULL DEFAULT '',
			student_email varchar(255) NOT NULL DEFAULT '',
			booking_start datetime NOT NULL,
			booking_end datetime NOT NULL,
			price_minor_units bigint(20) unsigned NOT NULL DEFAULT 0,
			currency char(3) NOT NULL DEFAULT 'GBP',
			pricing_basis varchar(50) NOT NULL DEFAULT 'booking_type',
			pricing_exception tinyint(1) NOT NULL DEFAULT 0,
			payment_method varchar(50) NOT NULL DEFAULT 'free',
			payment_status varchar(50) NOT NULL DEFAULT 'not_required',
			payment_provider varchar(50) NOT NULL DEFAULT '',
			payment_reference varchar(255) NOT NULL DEFAULT '',
			timezone varchar(100) NOT NULL DEFAULT 'Europe/London',
			location_type varchar(50) NOT NULL DEFAULT 'online',
			location_details text NULL,
			status varchar(50) NOT NULL DEFAULT 'confirmed',
			source varchar(50) NOT NULL DEFAULT 'student_booking',
			notes text NULL,
			confirmation_sent_at datetime DEFAULT NULL,
			cancelled_at datetime DEFAULT NULL,
			cancelled_by varchar(20) NOT NULL DEFAULT '',
			cancellation_reason text NULL,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY booking_type_id (booking_type_id),
			KEY student_user_id (student_user_id),
			KEY tutor_user_id (tutor_user_id),
			KEY booking_start (booking_start),
			KEY booking_end (booking_end),
			KEY status (status)
		) {$charset_collate};";


		$date_exclusions_sql =
		"CREATE TABLE {$date_exclusions_table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			tutor_user_id bigint(20) unsigned NOT NULL,
			title varchar(255) NOT NULL DEFAULT '',
			start_date date NOT NULL,
			end_date date NOT NULL,
			exclusion_type varchar(50) NOT NULL DEFAULT 'outside_term_time',
			notes text NULL,
			is_active tinyint(1) NOT NULL DEFAULT 1,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY tutor_user_id (tutor_user_id),
			KEY start_date (start_date),
			KEY end_date (end_date),
			KEY exclusion_type (exclusion_type),
			KEY is_active (is_active),
			KEY tutor_date_range (
				tutor_user_id,
				start_date,
				end_date
			)
		) {$charset_collate};";

		$student_audit_sql = "CREATE TABLE {$student_audit_table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			student_user_id bigint(20) unsigned NOT NULL,
			changed_by_user_id bigint(20) unsigned NOT NULL,
			action varchar(100) NOT NULL DEFAULT '',
			field_key varchar(100) NOT NULL DEFAULT '',
			old_value longtext NULL,
			new_value longtext NULL,
			context varchar(100) NOT NULL DEFAULT 'admin',
			created_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY student_user_id (student_user_id),
			KEY changed_by_user_id (changed_by_user_id),
			KEY action (action),
			KEY created_at (created_at)
		) {$charset_collate};";


		dbDelta( $availability_sql );
		dbDelta( $types_sql );
		dbDelta( $exceptions_sql );
		dbDelta( $bookings_sql );
		dbDelta( $date_exclusions_sql );
		dbDelta( $student_audit_sql );

		update_option(
			self::VERSION_OPTION,
			TUTORHELPER_BOOKINGS_DB_VERSION
		);

	}

	/**
	 * Check whether the database schema needs updating.
	 *
	 * @return void
	 */
	public static function update_check() {
		$installed_version = get_option(
			self::VERSION_OPTION,
			'0.0.0'
		);

		if (
			version_compare(
				$installed_version,
				TUTORHELPER_BOOKINGS_DB_VERSION,
				'<'
			)
		) {
			self::create_tables();
			
			TutorHelper_Logger::success(
				'Booking database schema checked.',
				array(
					'database_version' => TUTORHELPER_BOOKINGS_DB_VERSION,
				)
			);
		}
	}

	/**
	 * Get one of the plugin's database table names.
	 *
	 * @param string $table Short table identifier.
	 *
	 * @return string
	 */
	public static function get_table_name( $table ) {
		global $wpdb;

		$tables = array(
			'availability'            	=> $wpdb->prefix . 'tutorhelper_booking_availability',
			'types'                   	=> $wpdb->prefix . 'tutorhelper_booking_types',
			'availability_exceptions' 	=> $wpdb->prefix . 'tutorhelper_booking_availability_exceptions',
			'bookings'                	=> $wpdb->prefix . 'tutorhelper_bookings',
			'date_exclusions' 			=>$wpdb->prefix  . 'tutorhelper_booking_date_exclusions',
			'student_audit'             => $wpdb->prefix . 'tutorhelper_student_audit_log',
		);

		if ( ! isset( $tables[ $table ] ) ) {
			return '';
		}

		return $tables[ $table ];
	}
}
