<?php

/**
 * Database schema setup
 */
class Ekc_Database_Setup {

	const DATABASE_VERSION_OPTION = 'ekc_database_version';

	public function check_database_version() {
		$database_installed_version = get_option(self::DATABASE_VERSION_OPTION);
		
		if ( $database_installed_version != EKC_DATABASE_VERSION ) {
			$this->setup_database();
		}
	}

	public function setup_database() {
		global $wpdb;

		$tournament_table = $wpdb->prefix . "ekc_tournament";
		$tournament_round_table = $wpdb->prefix . "ekc_tournament_round";
		$team_table = $wpdb->prefix . "ekc_team";
		$player_table = $wpdb->prefix . "ekc_player";
		$result_table = $wpdb->prefix . "ekc_result";
		$result_log_table = $wpdb->prefix . "ekc_result_log";
		$email_status_table = $wpdb->prefix . "ekc_email_status";

		$charset_collate = $wpdb->get_charset_collate();

		$tournament_table_sql = 
			"CREATE TABLE $tournament_table (
			tournament_id integer(10) NOT NULL AUTO_INCREMENT,
			code_name varchar(50) NOT NULL,			
			name varchar(500) NOT NULL,
			owner_user_id integer(20),
			tournament_date date,
			max_teams integer(10),
			team_size varchar(20),
			is_wait_list_enabled bit(1),
			is_player_names_required bit(1),
			is_auto_backup_enabled bit(1),
			is_check_in_enabled bit(1),
			is_check_in_active bit(1),
			tournament_system varchar(20),
			elimination_rounds varchar(20),
			elimination_silver_rounds varchar(20),
			elimination_max_points_per_round integer(10),
			swiss_system_rounds integer(10),
			swiss_system_max_points_per_round integer(10),
			swiss_system_bye_points integer(10),
			swiss_system_virtual_result_points integer(10),
			swiss_system_additional_rounds integer(10),
			swiss_system_slide_match_rounds integer(10),
			swiss_system_round_time integer(10),
			swiss_system_tiebreak_time integer(10),
			swiss_system_start_pitch integer(10),
			swiss_system_pitch_limit integer(10),
			shareable_link_url_prefix varchar(500),
			shareable_link_email_text mediumtext,
			shareable_link_sender_email varchar(500),
			PRIMARY KEY  (tournament_id),
			UNIQUE KEY uc_code_name (code_name)
		) $charset_collate;";

		$tournament_round_table_sql = 
			"CREATE TABLE $tournament_round_table (
			tournament_id integer(10) NOT NULL,
			tournament_round integer(10) NOT NULL,
			round_start_time timestamp,
			PRIMARY KEY  (tournament_id, tournament_round)
		) $charset_collate;";

		$team_table_sql = 
			"CREATE TABLE $team_table (
			team_id integer(10) NOT NULL AUTO_INCREMENT,
			tournament_id integer(10) NOT NULL,
			name varchar(500) NOT NULL,
			country varchar(20),
			club varchar(500),
			is_active bit(1),
			email varchar(500),
			phone varchar(50),
			registration_date datetime,
			camping_count integer(10), 
			breakfast_count integer(10),
			is_registration_fee_paid bit(1),
			is_on_wait_list bit(1),
			registration_order double precision,
			is_checked_in bit(1),
			seeding_score double precision,
			initial_score double precision,
			virtual_rank integer(10),
			shareable_link_id varchar(50),
			external_id varchar(50),
			PRIMARY KEY  (team_id),
			KEY i_tournament_id (tournament_id),
			KEY i_shareable_link_id (shareable_link_id)
			KEY i_external_id (external_id)
		) $charset_collate;";

		$player_table_sql = 
			"CREATE TABLE $player_table (
			player_id integer(10) NOT NULL AUTO_INCREMENT,
			team_id integer(10) NOT NULL,
			last_name varchar(500) NOT NULL,
			first_name varchar(500),
			country varchar(20),
			is_active bit(1),
			is_captain bit(1),		
			PRIMARY KEY  (player_id),
			KEY i_team_id (team_id)
		) $charset_collate;";

		$result_table_sql = 
			"CREATE TABLE $result_table (
			result_id integer(10) NOT NULL AUTO_INCREMENT,
			tournament_id integer(10) NOT NULL,
			team1_id integer(10),
			team2_id integer(10),
			team1_placeholder varchar(500),
			team2_placeholder varchar(500),
			team1_score double precision,
			team2_score double precision,
			pitch varchar(20),
			stage varchar(20),
			tournament_round integer(10),
			result_type varchar(20),
			is_virtual_result bit(1),
			PRIMARY KEY  (result_id),
			KEY i_tournament_id (tournament_id),
			KEY i_team1_id (team1_id),
			KEY i_team2_id (team2_id),
    		KEY i_stage (stage),
    		KEY i_result_type (result_type),
    		KEY i_tournament_round (tournament_round)
		) $charset_collate;";

		$result_log_table_sql = 
			"CREATE TABLE $result_log_table (
			result_log_id integer(10) NOT NULL AUTO_INCREMENT,
			result_id integer(10) NOT NULL,
		    log_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), /* fractional seconds (milliseconds) */
    		log_team_id integer(10),
			team1_score double precision,
			team2_score double precision,
			PRIMARY KEY (result_log_id),
			KEY i_result_id (result_id),
			KEY i_log_team_id (log_team_id)
		) $charset_collate;";

		$email_status_table_sql = 
			"CREATE TABLE $email_status_table (
			email_status_id integer(10) NOT NULL AUTO_INCREMENT,
			team_id integer(10) NOT NULL,
		    email_message_id varchar(500),
			email_status varchar(20) NOT NULL,
			email_address varchar(500),
			status_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), /* fractional seconds (milliseconds) */
			status_message varchar(5000),
			scheduled_batch integer(10),
			PRIMARY KEY (email_status_id),
			KEY i_team_id (team_id),
			KEY i_email_status (email_status)
		) $charset_collate;";

		require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
		dbDelta( $tournament_table_sql );
		dbDelta( $tournament_round_table_sql );
		dbDelta( $team_table_sql );
		dbDelta( $player_table_sql );
		dbDelta( $result_table_sql );
		dbDelta( $result_log_table_sql );
		dbDelta( $email_status_table_sql );

		update_option(self::DATABASE_VERSION_OPTION, EKC_DATABASE_VERSION);
	}

	public function delete_database() {
		global $wpdb;

		$tournament_table = $wpdb->prefix . "ekc_tournament";
		$tournament_round_table = $wpdb->prefix . "ekc_tournament_round";
		$team_table = $wpdb->prefix . "ekc_team";
		$player_table = $wpdb->prefix . "ekc_player";
		$result_table = $wpdb->prefix . "ekc_result";
		$result_log_table = $wpdb->prefix . "ekc_result_log";
		$email_status_table  = $wpdb->prefix . "ekc_email_status";

		$wpdb->query( "DROP TABLE IF EXISTS $tournament_table" );
		$wpdb->query( "DROP TABLE IF EXISTS $tournament_round_table" );
		$wpdb->query( "DROP TABLE IF EXISTS $team_table" );
		$wpdb->query( "DROP TABLE IF EXISTS $player_table" );
		$wpdb->query( "DROP TABLE IF EXISTS $result_table" );
		$wpdb->query( "DROP TABLE IF EXISTS $result_log_table" );
		$wpdb->query( "DROP TABLE IF EXISTS $email_status_table" );

		delete_option(self::DATABASE_VERSION_OPTION);
	}
}
