<?php
/**
 * Database schema migration runner.
 *
 * @package Abientot
 */

namespace Abientot\Database;

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

final class Migrator {
	public const SCHEMA_VERSION = '1.7.0';

	/**
	 * Runs all database migrations.
	 */
	public function run(): bool {
		global $wpdb;

		// Never reinterpret an unknown/future schema, including direct import callers.
		if ( ! \Abientot\Edition::schema_supported() ) {
			return false;
		}

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

		$charset_collate = $wpdb->get_charset_collate();

		$schema               = $this->schema( $charset_collate );
		$previous_suppression = $wpdb->suppress_errors( true );
		try {
			foreach ( $schema as $sql ) {
				dbDelta( $sql );
			}
			// dbDelta's messages describe attempted changes, not successful writes.
			// A read-only second pass must find no outstanding table, column or index changes.
			foreach ( $schema as $sql ) {
				if ( dbDelta( $sql, false ) || $wpdb->last_error ) {
					if ( self::SCHEMA_VERSION === get_option( 'abientot_db_version' ) ) {
						delete_option( 'abientot_db_version' );
					}
					return false;
				}
			}
			update_option( 'abientot_db_version', self::SCHEMA_VERSION, false );
			return self::SCHEMA_VERSION === get_option( 'abientot_db_version' );
		} finally {
			$wpdb->suppress_errors( $previous_suppression );
		}
	}

	/**
	 * Returns a full plugin table name.
	 *
	 * @param string $suffix Table suffix without the WordPress prefix.
	 */
	private function table( string $suffix ): string {
		global $wpdb;

		return $wpdb->prefix . 'abientot_' . $suffix;
	}

	/**
	 * Builds schema statements.
	 *
	 * @param string $charset_collate WordPress charset/collation SQL fragment.
	 * @return string[]
	 */
	private function schema( string $charset_collate ): array {
		return array(
			"CREATE TABLE {$this->table( 'service_categories' )} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				name varchar(190) NOT NULL,
				slug varchar(190) NOT NULL,
				description text NULL,
				image_id bigint(20) unsigned NULL,
				sort_order int(11) NOT NULL DEFAULT 0,
				is_active tinyint(1) NOT NULL DEFAULT 1,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY slug (slug),
				KEY active_sort (is_active, sort_order)
			) {$charset_collate};",
			"CREATE TABLE {$this->table( 'services' )} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				category_id bigint(20) unsigned NULL,
				name varchar(190) NOT NULL,
				slug varchar(190) NOT NULL,
				description longtext NULL,
				image_id bigint(20) unsigned NULL,
				duration_minutes int(10) unsigned NOT NULL,
				buffer_before_minutes int(10) unsigned NOT NULL DEFAULT 0,
				buffer_after_minutes int(10) unsigned NOT NULL DEFAULT 0,
				price_cents bigint(20) unsigned NOT NULL DEFAULT 0,
				currency char(3) NOT NULL DEFAULT 'USD',
				color varchar(20) NULL,
				status varchar(40) NOT NULL DEFAULT 'active',
				visibility varchar(40) NOT NULL DEFAULT 'public',
				sort_order int(11) NOT NULL DEFAULT 0,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY category_id (category_id),
				KEY status_visibility_sort (status, visibility, sort_order),
				KEY slug (slug)
			) {$charset_collate};",
			"CREATE TABLE {$this->table( 'staff' )} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				display_name varchar(190) NOT NULL,
				email varchar(190) NULL,
				phone varchar(80) NULL,
				bio longtext NULL,
				photo_id bigint(20) unsigned NULL,
				timezone varchar(100) NULL,
				is_default tinyint(1) NOT NULL DEFAULT 0,
				status varchar(40) NOT NULL DEFAULT 'active',
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY status (status),
				KEY is_default (is_default)
			) {$charset_collate};",
			"CREATE TABLE {$this->table( 'locations' )} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				name varchar(190) NOT NULL,
				address_1 varchar(190) NULL,
				address_2 varchar(190) NULL,
				city varchar(100) NULL,
				region varchar(100) NULL,
				postal_code varchar(40) NULL,
				country varchar(2) NULL,
				phone varchar(80) NULL,
				email varchar(190) NULL,
				map_url text NULL,
				timezone varchar(100) NULL,
				is_default tinyint(1) NOT NULL DEFAULT 0,
				status varchar(40) NOT NULL DEFAULT 'active',
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY status (status),
				KEY is_default (is_default)
			) {$charset_collate};",
			"CREATE TABLE {$this->table( 'staff_services' )} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				staff_id bigint(20) unsigned NOT NULL,
				service_id bigint(20) unsigned NOT NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY staff_service (staff_id, service_id),
				KEY service_id (service_id)
			) {$charset_collate};",
			"CREATE TABLE {$this->table( 'staff_locations' )} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				staff_id bigint(20) unsigned NOT NULL,
				location_id bigint(20) unsigned NOT NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY staff_location (staff_id, location_id),
				KEY location_id (location_id)
			) {$charset_collate};",
			"CREATE TABLE {$this->table( 'customers' )} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				first_name varchar(100) NULL,
				last_name varchar(100) NULL,
				display_name varchar(190) NULL,
				email varchar(190) NULL,
				phone varchar(80) NULL,
				notes longtext NULL,
				marketing_opt_in tinyint(1) NOT NULL DEFAULT 0,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY email (email),
				KEY phone (phone),
				KEY last_first (last_name, first_name)
			) {$charset_collate};",
			"CREATE TABLE {$this->table( 'booking_statuses' )} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				slug varchar(80) NOT NULL,
				label varchar(120) NOT NULL,
				color varchar(20) NULL,
				blocks_availability tinyint(1) NOT NULL DEFAULT 1,
				counts_as_revenue tinyint(1) NOT NULL DEFAULT 0,
				is_default tinyint(1) NOT NULL DEFAULT 0,
				is_system tinyint(1) NOT NULL DEFAULT 0,
				sort_order int(11) NOT NULL DEFAULT 0,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY slug (slug),
				KEY blocks_availability (blocks_availability),
				KEY counts_as_revenue (counts_as_revenue)
			) {$charset_collate};",
			"CREATE TABLE {$this->table( 'appointments' )} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				customer_id bigint(20) unsigned NOT NULL,
				service_id bigint(20) unsigned NOT NULL,
				staff_id bigint(20) unsigned NOT NULL,
				location_id bigint(20) unsigned NOT NULL,
				status_id bigint(20) unsigned NOT NULL,
				start_utc datetime NOT NULL,
				end_utc datetime NOT NULL,
				timezone varchar(100) NOT NULL,
				duration_minutes int(10) unsigned NOT NULL,
				price_cents bigint(20) unsigned NOT NULL DEFAULT 0,
				currency char(3) NOT NULL,
				payment_status varchar(40) NOT NULL DEFAULT 'not_required',
				source varchar(40) NOT NULL DEFAULT 'admin',
				customer_fields_json longtext NULL,
				customer_note longtext NULL,
				internal_note longtext NULL,
				cancelled_at datetime NULL,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY start_utc (start_utc),
				KEY end_utc (end_utc),
				KEY customer_id (customer_id),
				KEY service_id (service_id),
				KEY staff_time (staff_id, start_utc, end_utc),
				KEY location_time (location_id, start_utc, end_utc),
				KEY status_id (status_id),
				KEY payment_status (payment_status)
			) {$charset_collate};",
			"CREATE TABLE {$this->table( 'availability_rules' )} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				staff_id bigint(20) unsigned NOT NULL,
				location_id bigint(20) unsigned NULL,
				service_id bigint(20) unsigned NULL,
				weekday tinyint(3) unsigned NOT NULL,
				start_time time NOT NULL,
				end_time time NOT NULL,
				rule_type varchar(40) NOT NULL DEFAULT 'working',
				effective_from date NULL,
				effective_to date NULL,
				is_active tinyint(1) NOT NULL DEFAULT 1,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY staff_weekday (staff_id, weekday),
				KEY location_id (location_id),
				KEY service_id (service_id),
				KEY is_active (is_active)
			) {$charset_collate};",
			"CREATE TABLE {$this->table( 'availability_exceptions' )} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				staff_id bigint(20) unsigned NOT NULL,
				location_id bigint(20) unsigned NULL,
				service_id bigint(20) unsigned NULL,
				exception_date date NOT NULL,
				start_time time NULL,
				end_time time NULL,
				exception_type varchar(40) NOT NULL DEFAULT 'closed',
				reason varchar(190) NULL,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY staff_date (staff_id, exception_date),
				KEY location_date (location_id, exception_date),
				KEY service_date (service_id, exception_date)
			) {$charset_collate};",
			"CREATE TABLE {$this->table( 'payment_records' )} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				appointment_id bigint(20) unsigned NOT NULL,
				provider varchar(80) NOT NULL DEFAULT 'manual',
				method varchar(80) NULL,
				status varchar(40) NOT NULL DEFAULT 'not_required',
				amount_cents bigint(20) unsigned NOT NULL DEFAULT 0,
				currency char(3) NOT NULL,
				transaction_reference varchar(190) NULL,
				paid_at datetime NULL,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY appointment_id (appointment_id),
				KEY provider (provider),
				KEY status (status),
				KEY paid_at (paid_at)
			) {$charset_collate};",
			"CREATE TABLE {$this->table( 'activity_log' )} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				object_type varchar(80) NULL,
				object_id bigint(20) unsigned NULL,
				event varchar(120) NOT NULL,
				actor_type varchar(40) NULL,
				actor_id bigint(20) unsigned NULL,
				message text NULL,
				context_json longtext NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY object_lookup (object_type, object_id),
				KEY event (event),
				KEY created_at (created_at)
			) {$charset_collate};",
		);
	}
}