<?php

namespace SmartSlotAppointmentScheduler;

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

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

if ( ! defined( 'SSAS_DB_VERSION' ) ) {
	// Bumped for the provider/category/service generalization migration.
	define( 'SSAS_DB_VERSION', '1.2.0' );
}

if ( ! class_exists( '\SmartSlotAppointmentScheduler\Schedule_database' ) ) {

	/**
	 * Class to create the database tables for the plugin
	 * and run one-time upgrade migrations for existing installations.
	 */
	class Schedule_database {

		/**
		 * Plugin database table prefix.
		 *
		 * @var string
		 */
		private $table_prefix;

		/**
		 * WordPress database object.
		 *
		 * @var wpdb
		 */
		private $wp_database;

		/**
		 * Database character set and collation.
		 *
		 * @var string
		 */
		private $charset_collate;

		/**
		 * Constructor.
		 */
		public function __construct() {
			global $wpdb;

			$this->wp_database     = $wpdb;
			$this->table_prefix    = 'pkart_';
			$this->charset_collate = $wpdb->get_charset_collate();

			$this->create_booking_appointment();
			$this->create_booking_tokens();
			$this->maybe_run_legacy_migration();
		}

		/**
		 * Create a database table if it does not already exist.
		 *
		 * @param string $create_table Table name without prefix.
		 * @param string $query        Table schema.
		 * @return void
		 */
		private function init_table( $create_table, $query ) {
			$table_name = $this->table_prefix . $create_table;

			// Check if the table already exists.
			$table_exists = $this->wp_database->get_var(
				$this->wp_database->prepare(
					'SHOW TABLES LIKE %s',
					$table_name
				)
			);

			if ( $table_exists === $table_name ) {
				return;
			}

			$sql = "CREATE TABLE $table_name $query $this->charset_collate;";
			dbDelta( $sql );
		}

		/**
		 * Create the booking appointments table.
		 *
		 * @return void
		 */
		private function create_booking_appointment() {
			$query = "(
				id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
				token_number VARCHAR(255) NOT NULL,
				first_name VARCHAR(100) NOT NULL,
				last_name VARCHAR(100),
				email VARCHAR(100) NOT NULL,
				phone VARCHAR(30) NOT NULL,
				address VARCHAR(255) NOT NULL,
				notes TEXT NOT NULL,
				appointment_date DATE NOT NULL,
				appointment_time TIME NOT NULL,
				provider_id VARCHAR(255) NOT NULL,
				category_id VARCHAR(255) NOT NULL,
				status BOOLEAN DEFAULT 1,
				created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
				updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
			)";

			$this->init_table(
				'ssas_booking_appointment',
				$query
			);
		}

		/**
		 * Create the booking sequence option.
		 *
		 * @return void
		 */
		private function create_booking_tokens() {
			if ( get_option( 'ssas_booking_sequence' ) !== false ) {
				return;
			}

			add_option(
				'ssas_booking_sequence',
				1,
				'',
				'yes'
			);
		}

		/**
		 * One-time upgrade path for sites created before the provider/category/service
		 * generalization. Existing bookings, terms and meta are preserved; only the
		 * legacy healthcare-era identifiers are renamed. No-ops on a fresh install.
		 *
		 * @return void
		 */
		private function maybe_run_legacy_migration() {
			$installed_version = get_option(
				'ssas_db_version',
				'0'
			);

			if ( version_compare(
				$installed_version,
				SSAS_DB_VERSION,
				'>='
			) ) {
				return;
			}

			$this->migrate_doctor_id_column();
			$this->migrate_taxonomy_slugs();
			$this->migrate_availability_meta_key();

			update_option(
				'ssas_db_version',
				SSAS_DB_VERSION
			);
		}

		/**
		 * Rename the legacy `doctor_id` booking column to `provider_id`,
		 * preserving existing values.
		 *
		 * @return void
		 */
		private function migrate_doctor_id_column() {
			$table_name = $this->table_prefix . 'ssas_booking_appointment';

			$legacy_column = $this->wp_database->get_var(
				$this->wp_database->prepare(
					'SHOW COLUMNS FROM ' . $table_name . ' LIKE %s',
					'doctor_id'
				)
			);

			if ( ! $legacy_column ) {
				return;
			}

			/*
			 * The table name is a plugin-controlled value and is not user input.
			 * The column names and data type are also fixed migration values.
			 */
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Required for one-time schema migration.
			$this->wp_database->query(
				"ALTER TABLE $table_name CHANGE doctor_id provider_id VARCHAR(255) NOT NULL"
			);
		}

		/**
		 * Rename the legacy `ssas_specialities`/`ssas_checkups` taxonomies to
		 * `ssas_categories`/`ssas_services`. Existing term assignments are preserved
		 * because only the taxonomy label on wp_term_taxonomy rows changes.
		 *
		 * @return void
		 */
		private function migrate_taxonomy_slugs() {
			$renames = [
				'ssas_specialities' => 'ssas_categories',
				'ssas_checkups'     => 'ssas_services',
			];

			foreach ( $renames as $old_taxonomy => $new_taxonomy ) {
				$this->wp_database->update(
					$this->wp_database->term_taxonomy,
					[
						'taxonomy' => $new_taxonomy,
					],
					[
						'taxonomy' => $old_taxonomy,
					]
				);
			}

			wp_cache_flush();
		}

		/**
		 * Rename the legacy `_doctor_availability` post meta key
		 * to `_provider_availability`.
		 *
		 * This meta key was never exposed via REST, so no external
		 * consumer can depend on it.
		 *
		 * @return void
		 */
		private function migrate_availability_meta_key() {
			/*
			 * This is an intentional one-time migration of a custom post meta key.
			 * Direct database access is required to rename the legacy meta key.
			 */
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- Intentional one-time migration of the legacy custom post meta key.
			$this->wp_database->query(
				$this->wp_database->prepare(
					'UPDATE %i SET meta_key = %s WHERE meta_key = %s',
					$this->wp_database->postmeta,
					'_provider_availability',
					'_doctor_availability'
				)
			);
		}
	}

	new \SmartSlotAppointmentScheduler\Schedule_database();
}