<?php

/**
 * Database migration
 *
 * This file is used to handle the database migration process for the Arena Scheduler plugin.
 *
 * @link       https://level5.se
 * @since      1.0.0
 *
 * @package    Arena_Scheduler
 * @subpackage Arena_Scheduler/includes
 */

defined('ABSPATH') || exit;

/**
 * Class Arena_Scheduler_Database_Migration
 *
 * This class handles the database migration process for the Arena Scheduler plugin.
 * It is responsible for creating or updating database tables, migrating data, etc.
 *
 * @since      1.0.0
 * @package    Arena_Scheduler
 * @subpackage Arena_Scheduler/includes
 * @author     Level5 <support@level5.se>
 */
class Arena_Scheduler_Database_Migration
{
	// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange

	/**
	 * Plugin version.
	 *
	 * @var string
	 */
	public $version;

	/**
	 * Last released version of the plugin.
	 *
	 * @var string
	 */
	public $release = '1.0.2'; // Keep last released version

	/**
	 * Get the charset collate string for table creation.
	 *
	 * @return string
	 */
	private function arena_scheduler_get_charset_collate()
	{
		global $wpdb;
		return $wpdb->get_charset_collate();
	}

	/**
	 * Constructor for the Arena Scheduler class.
	 *
	 * This method initializes the Arena Scheduler plugin by retrieving the version from the WordPress options table.
	 * If the version is found, it sets the plugin's version property to the retrieved version.
	 * If the version is not found, it sets the plugin's version property to the first version ('1.0.0') and updates
	 * the version stored in the WordPress options table accordingly.
	 */
	public function __construct()
	{
		// Checks the plugin version and performs necessary updates if required.
		$this->arena_scheduler_check_version();
	}

	/**
	 * Checks the plugin version and performs necessary updates if required.
	 *
	 * This method retrieves the version from the WordPress options table and compares it
	 * with the release version. If the versions do not match, it triggers the arena_scheduler_release_version()
	 * method to perform necessary updates. If the version is not found in the options table,
	 * it sets the plugin's version property to the first version ('1.0.0') and updates the
	 * version stored in the options table accordingly.
	 *
	 * @return void
	 */
	public function arena_scheduler_check_version()
	{
		// Retrieve the version from the WordPress options table
		$version = $this->arena_scheduler_get_version();

		// Check if the version is found
		if ($version) {
			// If found, set the plugin's version property to the retrieved version
			$this->version = $version;
		} else {
			// No version found — this is a fresh install on this site.
			// Set to '1.0.0' so run() starts from the beginning.
			$this->version = '1.0.0';
			update_option('arena_scheduler_migration_version', '1.0.0');
		}
	}

	/**
	 * Get the database migration version of the Arena Scheduler plugin.
	 *
	 * This method retrieves the database migration version of the Arena Scheduler plugin from the WordPress options table.
	 * The database migration version indicates the version of the plugin's database schema after migration.
	 *
	 * @return string|null The database migration version of the plugin, or null if not found.
	 */
	public function arena_scheduler_get_version()
	{
		return get_option('arena_scheduler_migration_version');
	}

	/**
	 * Release the migration version of the Arena Scheduler plugin.
	 *
	 * This method sets the release migration version of the Arena Scheduler plugin and updates the version
	 * stored in the WordPress options table.
	 *
	 * @return void
	 */
	public function arena_scheduler_release_version()
	{
		// Update the plugin's version property
		$this->version = $this->release;

		// Update the version stored in the WordPress options table
		update_option('arena_scheduler_migration_version', $this->release);
	}

	/**
	 * Run the database migration process.
	 *
	 * This method is responsible for executing the database migration process
	 * for the Arena Scheduler plugin. It performs actions such as creating or
	 * updating database tables, migrating data, etc.
	 *
	 * @since 1.0.0
	 * @return void
	 */
	public function arena_scheduler_run()
	{
		$version = $this->arena_scheduler_get_version();

		// If no version exists or it's the first install, run all migrations sequentially
		if (!$version || $version === '1.0.0') {
			$this->arena_scheduler_create_arena_table();
			$this->arena_scheduler_create_arena_categories_table();
			$this->arena_scheduler_create_arena_scheduled_timesheet_table();
			update_option('arena_scheduler_migration_version', '1.0.0');

			// Fall through to run subsequent migrations
			$version = '1.0.0';
		}

		if ($version === '1.0.0') {
			$this->arena_scheduler_alter_arena_table();
			$this->arena_scheduler_alter_arena_categories_table();
			update_option('arena_scheduler_migration_version', '1.0.1');
			$version = '1.0.1';
		}

		if ($version === '1.0.1') {
			$this->arena_scheduler_add_text_color_to_arena_categories_table();
			update_option('arena_scheduler_migration_version', '1.0.2');
		}
	}

	/**
	 * Create the 'arena' table in the WordPress database.
	 *
	 * This function creates a new table named 'arena' with the specified structure:
	 * - id: An auto-incrementing primary key field of type INT(10) UNSIGNED.
	 * - name: A field to store the arena name of type VARCHAR(255).
	 * - interval_time: A field to store the interval time of type VARCHAR(255), nullable.
	 * - start_time: A field to store the start time of type VARCHAR(255), nullable.
	 * - end_time: A field to store the end time of type VARCHAR(255), nullable.
	 * - is_default: A field to indicate if the arena is the default one, of type TINYINT(4), nullable.
	 * - status: A field to store the status of type INT(11) with a default value of 1.
	 * - created_at: A field to store the creation timestamp of type DATETIME with a default value of the current timestamp.
	 *
	 * @global wpdb $wpdb WordPress database access abstraction object.
	 * @return void
	 */
	public function arena_scheduler_create_arena_table()
	{
		global $wpdb;

		$table           = $wpdb->prefix . 'arena';
		$charset_collate = $this->arena_scheduler_get_charset_collate();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query(
			"CREATE TABLE IF NOT EXISTS `{$table}` (
				id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
				name VARCHAR(255),
				interval_time INT(11) NULL,
				start_time VARCHAR(255) NULL,
				end_time VARCHAR(255) NULL,
				is_default TINYINT(4) NULL,
				status INT(11) DEFAULT 1,
				created_at DATETIME DEFAULT CURRENT_TIMESTAMP
			) {$charset_collate}"
		);
	}

	/**
	 * Create the 'arena_categories' table in the WordPress database.
	 *
	 * This function creates a new table named 'arena_categories' with the specified structure:
	 * - id: An auto-incrementing primary key field of type INT(10) UNSIGNED.
	 * - name: A field to store the arena category name of type VARCHAR(255).
	 * - color: A field to store the category color of type VARCHAR(255).
	 * - text_color: A field to store the text color of type VARCHAR(255) with a default value of '#000000'.
	 * - is_default: A field to indicate if the category is the default one, of type TINYINT(4).
	 * - status: A field to store the status of type INT(11) with a default value of 1.
	 * - created_at: A field to store the creation timestamp of type DATETIME with a default value of the current timestamp.
	 *
	 * @global wpdb $wpdb WordPress database access abstraction object.
	 * @return void
	 */
	public function arena_scheduler_create_arena_categories_table()
	{
		global $wpdb;

		$table           = $wpdb->prefix . 'arena_categories';
		$charset_collate = $this->arena_scheduler_get_charset_collate();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query(
			"CREATE TABLE IF NOT EXISTS `{$table}` (
				id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
				name VARCHAR(255) NULL,
				color VARCHAR(255) NULL,
				text_color VARCHAR(255) DEFAULT '#000000',
				is_default TINYINT(4) NULL,
				status INT(11) DEFAULT 1,
				created_at DATETIME DEFAULT CURRENT_TIMESTAMP
			) {$charset_collate}"
		);
	}

	/**
	 * Create the 'arena_scheduled_timesheet' table in the WordPress database.
	 *
	 * This function creates a new table named 'arena_scheduled_timesheet' with the specified structure:
	 * - id: An auto-incrementing primary key field of type INT(10) UNSIGNED.
	 * - arena_id: A field to store the Arena Id of type INT(10).
	 * - timeslot_id: A field to store the dropdown slot id of type BIGINT(15) UNSIGNED.
	 * - category: A field to store the category of type INT(11).
	 * - comment: A field to store comments of type VARCHAR(255) (nullable).
	 * - scheduled_date: A field to store the slot booked date of type DATE.
	 * - created_at: A field to store the creation timestamp of type DATETIME with a default value of the current timestamp.
	 *
	 * @global wpdb $wpdb WordPress database access abstraction object.
	 * @return void
	 */
	public function arena_scheduler_create_arena_scheduled_timesheet_table()
	{
		global $wpdb;

		$table            = $wpdb->prefix . 'arena_scheduled_timesheet';
		$arena_table      = $wpdb->prefix . 'arena';
		$arena_categories = $wpdb->prefix . 'arena_categories';
		$charset_collate  = $this->arena_scheduler_get_charset_collate();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query(
			"CREATE TABLE IF NOT EXISTS `{$table}` (
				id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
				arena_id INT(10) UNSIGNED,
				timeslot_id BIGINT(15) UNSIGNED,
				category INT(10) UNSIGNED,
				comment VARCHAR(255) NULL,
				scheduled_date DATE,
				created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
				FOREIGN KEY (arena_id) REFERENCES `{$arena_table}`(id) ON DELETE CASCADE ON UPDATE CASCADE,
				FOREIGN KEY (category) REFERENCES `{$arena_categories}`(id) ON DELETE CASCADE ON UPDATE CASCADE
			) {$charset_collate}"
		);
	}

	/**
	 * Alter the 'arena' table in the database.
	 *
	 * This method checks if specific columns ('interval_time', 'start_time', 'end_time', 'is_default')
	 * exist in the 'arena' table. If any of them does not exist, it adds the missing columns.
	 *
	 * @return void
	 */
	public function arena_scheduler_alter_arena_table()
	{
		global $wpdb; // Access WordPress database object

		// Check if the columns already exist
		$interval_exists = $wpdb->get_var("SHOW COLUMNS FROM {$wpdb->prefix}arena LIKE 'interval_time'");
		$start_exists = $wpdb->get_var("SHOW COLUMNS FROM {$wpdb->prefix}arena LIKE 'start_time'");
		$end_exists = $wpdb->get_var("SHOW COLUMNS FROM {$wpdb->prefix}arena LIKE 'end_time'");
		$is_default_exists = $wpdb->get_var("SHOW COLUMNS FROM {$wpdb->prefix}arena LIKE 'is_default'");

		// Add 'interval_time' column if it does not exist
		if (!$interval_exists) {
			$wpdb->query("ALTER TABLE {$wpdb->prefix}arena ADD COLUMN interval_time INT(11) NULL AFTER name");
		}

		// Add 'start_time' column if it does not exist
		if (!$start_exists) {
			$wpdb->query("ALTER TABLE {$wpdb->prefix}arena ADD COLUMN start_time VARCHAR(255) NULL AFTER interval_time");
		}

		// Add 'end_time' column if it does not exist
		if (!$end_exists) {
			$wpdb->query("ALTER TABLE {$wpdb->prefix}arena ADD COLUMN end_time VARCHAR(255) NULL AFTER start_time");
		}

		// Add 'is_default' column if it does not exist
		if (!$is_default_exists) {
			$wpdb->query("ALTER TABLE {$wpdb->prefix}arena ADD COLUMN is_default TINYINT(4) NULL AFTER end_time");
		}
	}

	/**
	 * Alter the 'arena_categories' table in the database.
	 *
	 * This method checks if the 'is_default' column exists in the 'arena_categories' table.
	 * If it does not exist, it adds the missing column.
	 *
	 * @return void
	 */
	public function arena_scheduler_alter_arena_categories_table()
	{
		global $wpdb; // Access WordPress database object

		// Check if the 'is_default' column already exists
		$is_default_exists = $wpdb->get_var("SHOW COLUMNS FROM {$wpdb->prefix}arena_categories LIKE 'is_default'");

		// Add 'is_default' column if it does not exist
		if (!$is_default_exists) {
			$wpdb->query("ALTER TABLE {$wpdb->prefix}arena_categories ADD COLUMN is_default TINYINT(4) NULL AFTER color");
		}
	}

	/**
	 * Add or alter the 'text_color' column in the 'arena_categories' table in the database.
	 *
	 * This method checks if the 'text_color' column exists in the 'arena_categories' table.
	 * If it does not exist, it adds the missing column with a default value of '#000000'.
	 *
	 * @return void
	 */
	public function arena_scheduler_add_text_color_to_arena_categories_table()
	{
		global $wpdb; // Access WordPress database object

		// Check if the 'text_color' column already exists
		$text_color_exists = $wpdb->get_var("SHOW COLUMNS FROM {$wpdb->prefix}arena_categories LIKE 'text_color'");

		// Add 'text_color' column if it does not exist
		if (!$text_color_exists) {
			$wpdb->query("ALTER TABLE {$wpdb->prefix}arena_categories ADD COLUMN text_color VARCHAR(255) DEFAULT '#000000' AFTER color");
		}
	}
}
