<?php
namespace Bookzenix;

defined( 'ABSPATH' ) || exit;

/**
 * Creates / upgrades database tables and default settings.
 */
class Installer {

	public static function activate() {
		self::migrate_prefix();
		self::create_tables();
		self::copy_old_tables();
		self::seed_defaults();
		self::migrate_orders();
		// Capability for staff members.
		$admin = get_role( 'administrator' );
		if ( $admin ) {
			$admin->add_cap( 'bookzenix_manage' );
		}
		update_option( 'bookzenix_db_version', BOOKZENIX_DB_VERSION );
		update_option( 'bookzenix_edition', self::edition(), false );
	}

	public static function deactivate() {
		wp_clear_scheduled_hook( 'bookzenix_cron_tick' );
	}

	/**
	 * Builds before 1.2.0 used the short "bkz_" prefix. Move their tables, options and
	 * people to the "bookzenix_" names once, so test sites keep their bookings.
	 */
	private static function migrate_prefix() {
		global $wpdb;
		if ( get_option( 'bookzenix_prefix_migrated' ) ) {
			return;
		}
		$old = $wpdb->prefix . 'bkz_';
		$new = $wpdb->prefix . 'bookzenix_';
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange
		foreach ( (array) $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $old ) . '%' ) ) as $table ) {
			$target = $new . substr( $table, strlen( $old ) );
			if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $target ) ) ) {
				$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i RENAME TO %i', $table, $target ) );
			}
		}
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT option_name, option_value, autoload FROM {$wpdb->options} WHERE option_name LIKE %s", $wpdb->esc_like( 'bkz_' ) . '%' ), ARRAY_A );
		// phpcs:enable
		foreach ( (array) $rows as $o ) {
			$name = 'bookzenix_' . substr( $o['option_name'], 4 );
			if ( false === get_option( $name ) ) {
				add_option( $name, maybe_unserialize( $o['option_value'] ), '', 'no' === $o['autoload'] ? 'no' : 'yes' );
			}
			delete_option( $o['option_name'] );
		}
		foreach ( array( 'bkz_employee' => 'bookzenix_employee', 'bkz_customer' => 'bookzenix_customer' ) as $from => $to ) {
			foreach ( get_users( array( 'role' => $from, 'fields' => 'all' ) ) as $u ) {
				$u->add_role( $to );
				$u->remove_role( $from );
			}
			remove_role( $from );
		}
		$admin = get_role( 'administrator' );
		if ( $admin && $admin->has_cap( 'bkz_manage' ) ) {
			$admin->remove_cap( 'bkz_manage' );
		}
		wp_clear_scheduled_hook( 'bkz_cron_tick' );
		update_option( 'bookzenix_prefix_migrated', 1, false );
	}

	/**
	 * Fallback for databases that could not rename tables: copy the rows of every old
	 * "bkz_" table into its new, still empty "bookzenix_" table (columns both have).
	 * The old table is left in place untouched.
	 */
	private static function copy_old_tables() {
		global $wpdb;
		if ( get_option( 'bookzenix_tables_copied' ) ) {
			return;
		}
		$old = $wpdb->prefix . 'bkz_';
		$new = $wpdb->prefix . 'bookzenix_';
		// phpcs:disable WordPress.DB.DirectDatabaseQuery
		foreach ( (array) $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $old ) . '%' ) ) as $table ) {
			$target = $new . substr( $table, strlen( $old ) );
			if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $target ) ) || (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $target ) ) > 0 ) {
				continue;
			}
			$cols = array_values( array_intersect( (array) $wpdb->get_col( $wpdb->prepare( 'DESCRIBE %i', $table ), 0 ), (array) $wpdb->get_col( $wpdb->prepare( 'DESCRIBE %i', $target ), 0 ) ) );
			if ( ! $cols ) {
				continue;
			}
			$list = implode( ', ', array_fill( 0, count( $cols ), '%i' ) );
			// INSERT INTO new (a, b) SELECT a, b FROM old – every identifier goes through %i.
			$wpdb->query( $wpdb->prepare( "INSERT INTO %i ({$list}) SELECT {$list} FROM %i", array_merge( array( $target ), $cols, $cols, array( $table ) ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}
		// phpcs:enable
		update_option( 'bookzenix_tables_copied', 1, false );
	}

	/** Runs the installer after an update, and when the free and the full plugin are swapped. */
	public static function maybe_upgrade() {
		if ( get_option( 'bookzenix_db_version' ) !== BOOKZENIX_DB_VERSION || get_option( 'bookzenix_edition' ) !== self::edition() ) {
			self::activate();
		}
	}

	private static function edition() {
		return BOOKZENIX_LITE ? 'lite' : 'full';
	}

	public static function create_tables() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$c = $wpdb->get_charset_collate();
		$t = function ( $n ) {
			return Plugin::table( $n );
		};

		$sql = array();

		$sql[] = "CREATE TABLE {$t('categories')} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(191) NOT NULL,
			color VARCHAR(20) NOT NULL DEFAULT '#6366f1',
			position INT NOT NULL DEFAULT 0,
			PRIMARY KEY  (id)
		) $c;";

		$sql[] = "CREATE TABLE {$t('services')} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			category_id BIGINT UNSIGNED NULL,
			name VARCHAR(191) NOT NULL,
			description TEXT NULL,
			duration INT NOT NULL DEFAULT 60,
			price DECIMAL(12,2) NOT NULL DEFAULT 0,
			deposit_type VARCHAR(10) NOT NULL DEFAULT 'none',
			deposit_value DECIMAL(12,2) NOT NULL DEFAULT 0,
			buffer_before INT NOT NULL DEFAULT 0,
			buffer_after INT NOT NULL DEFAULT 0,
			capacity INT NOT NULL DEFAULT 1,
			color VARCHAR(20) NOT NULL DEFAULT '#6366f1',
			image_url VARCHAR(500) NULL,
			location_type VARCHAR(20) NOT NULL DEFAULT 'in_person',
			location_text VARCHAR(255) NULL,
			durations TEXT NULL,
			gallery TEXT NULL,
			recurring_max INT NOT NULL DEFAULT 0,
			tax_rate DECIMAL(6,3) NULL,
			booking_unit VARCHAR(10) NOT NULL DEFAULT 'time',
			min_units INT NOT NULL DEFAULT 1,
			max_units INT NOT NULL DEFAULT 30,
			checkin_time VARCHAR(5) NOT NULL DEFAULT '15:00',
			checkout_time VARCHAR(5) NOT NULL DEFAULT '11:00',
			resource_choice TINYINT(1) NOT NULL DEFAULT 0,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			position INT NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY category_id (category_id)
		) $c;";

		$sql[] = "CREATE TABLE {$t('employees')} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			wp_user_id BIGINT UNSIGNED NULL,
			first_name VARCHAR(100) NOT NULL,
			last_name VARCHAR(100) NOT NULL DEFAULT '',
			email VARCHAR(191) NOT NULL,
			phone VARCHAR(50) NULL,
			bio TEXT NULL,
			avatar_url VARCHAR(500) NULL,
			location_id BIGINT UNSIGNED NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'active',
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY email (email),
			KEY wp_user_id (wp_user_id)
		) $c;";

		$sql[] = "CREATE TABLE {$t('employee_services')} (
			employee_id BIGINT UNSIGNED NOT NULL,
			service_id BIGINT UNSIGNED NOT NULL,
			price DECIMAL(12,2) NULL,
			PRIMARY KEY  (employee_id,service_id)
		) $c;";

		// day_of_week: 1 = Monday ... 7 = Sunday (ISO-8601).
		$sql[] = "CREATE TABLE {$t('working_hours')} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			employee_id BIGINT UNSIGNED NOT NULL,
			day_of_week TINYINT NOT NULL,
			start_time TIME NOT NULL,
			end_time TIME NOT NULL,
			PRIMARY KEY  (id),
			KEY employee_day (employee_id,day_of_week)
		) $c;";

		// employee_id NULL = company-wide holiday.
		$sql[] = "CREATE TABLE {$t('days_off')} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			employee_id BIGINT UNSIGNED NULL,
			name VARCHAR(191) NOT NULL DEFAULT '',
			start_date DATE NOT NULL,
			end_date DATE NOT NULL,
			PRIMARY KEY  (id),
			KEY employee_id (employee_id)
		) $c;";

		$sql[] = "CREATE TABLE {$t('customers')} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			wp_user_id BIGINT UNSIGNED NULL,
			first_name VARCHAR(100) NOT NULL,
			last_name VARCHAR(100) NOT NULL DEFAULT '',
			email VARCHAR(191) NOT NULL,
			phone VARCHAR(50) NULL,
			note TEXT NULL,
			access_token VARCHAR(64) NOT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY email (email),
			KEY access_token (access_token)
		) $c;";

		// All datetimes stored in UTC.
		$sql[] = "CREATE TABLE {$t('appointments')} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			service_id BIGINT UNSIGNED NOT NULL,
			employee_id BIGINT UNSIGNED NOT NULL,
			customer_id BIGINT UNSIGNED NOT NULL,
			order_id BIGINT UNSIGNED NULL,
			location_id BIGINT UNSIGNED NULL,
			package_purchase_id BIGINT UNSIGNED NULL,
			resource_id BIGINT UNSIGNED NULL,
			recurring_group VARCHAR(40) NULL,
			start_utc DATETIME NOT NULL,
			end_utc DATETIME NOT NULL,
			persons INT NOT NULL DEFAULT 1,
			status VARCHAR(20) NOT NULL DEFAULT 'pending',
			price DECIMAL(12,2) NOT NULL DEFAULT 0,
			discount DECIMAL(12,2) NOT NULL DEFAULT 0,
			tax DECIMAL(12,2) NOT NULL DEFAULT 0,
			coupon_code VARCHAR(50) NULL,
			paid DECIMAL(12,2) NOT NULL DEFAULT 0,
			payment_status VARCHAR(20) NOT NULL DEFAULT 'unpaid',
			internal_note TEXT NULL,
			customer_note TEXT NULL,
			custom_fields LONGTEXT NULL,
			reminder_sent TINYINT(1) NOT NULL DEFAULT 0,
			sms_reminder_sent TINYINT(1) NOT NULL DEFAULT 0,
			meeting_provider VARCHAR(20) NULL,
			meeting_id VARCHAR(191) NULL,
			meeting_url VARCHAR(500) NULL,
			external_ids TEXT NULL,
			token VARCHAR(64) NOT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY employee_start (employee_id,start_utc),
			KEY customer_id (customer_id),
			KEY order_id (order_id),
			KEY package_purchase_id (package_purchase_id),
			KEY resource_id (resource_id),
			KEY status (status),
			KEY token (token)
		) $c;";

		$sql[] = "CREATE TABLE {$t('payments')} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			appointment_id BIGINT UNSIGNED NULL,
			order_id BIGINT UNSIGNED NULL,
			kind VARCHAR(10) NOT NULL DEFAULT 'payment',
			gateway VARCHAR(20) NOT NULL,
			gateway_ref VARCHAR(191) NULL,
			gateway_txn VARCHAR(191) NULL,
			amount DECIMAL(12,2) NOT NULL,
			currency VARCHAR(10) NOT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'pending',
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY appointment_id (appointment_id),
			KEY order_id (order_id),
			KEY gateway_ref (gateway_ref)
		) $c;";

		$sql[] = "CREATE TABLE {$t('notification_log')} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			appointment_id BIGINT UNSIGNED NULL,
			template VARCHAR(50) NOT NULL,
			recipient VARCHAR(191) NOT NULL,
			success TINYINT(1) NOT NULL DEFAULT 1,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id)
		) $c;";

		// One checkout = one order (cart, recurring series, package or event tickets).
		$sql[] = "CREATE TABLE {$t('orders')} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			customer_id BIGINT UNSIGNED NOT NULL,
			kind VARCHAR(20) NOT NULL DEFAULT 'booking',
			subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
			discount DECIMAL(12,2) NOT NULL DEFAULT 0,
			tax DECIMAL(12,2) NOT NULL DEFAULT 0,
			total DECIMAL(12,2) NOT NULL DEFAULT 0,
			deposit DECIMAL(12,2) NOT NULL DEFAULT 0,
			paid DECIMAL(12,2) NOT NULL DEFAULT 0,
			refunded DECIMAL(12,2) NOT NULL DEFAULT 0,
			payment_status VARCHAR(20) NOT NULL DEFAULT 'unpaid',
			gateway VARCHAR(20) NOT NULL DEFAULT 'on_site',
			coupon_code VARCHAR(50) NULL,
			currency VARCHAR(10) NOT NULL DEFAULT 'USD',
			invoice_number VARCHAR(40) NULL,
			invoice_date DATETIME NULL,
			token VARCHAR(64) NOT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY customer_id (customer_id),
			KEY token (token)
		) $c;";

		foreach ( $sql as $q ) {
			dbDelta( $q );
		}
	}

	/** 1.2: every appointment gets an order so payments, refunds and invoices work uniformly. */
	public static function migrate_orders() {
		global $wpdb;
		$a    = Plugin::table( 'appointments' );
		$rows = $wpdb->get_results( "SELECT * FROM {$a} WHERE order_id IS NULL LIMIT 5000", ARRAY_A ); // phpcs:ignore
		foreach ( $rows as $r ) {
			$total = round( (float) $r['price'] - (float) $r['discount'], 2 );
			$wpdb->insert(
				Plugin::table( 'orders' ),
				array(
					'customer_id'    => $r['customer_id'],
					'subtotal'       => $r['price'],
					'discount'       => $r['discount'],
					'total'          => $total,
					'paid'           => $r['paid'],
					'payment_status' => $r['payment_status'],
					'coupon_code'    => $r['coupon_code'],
					'currency'       => Settings::get( 'currency', 'USD' ),
					'token'          => Repository::token(),
					'created_at'     => $r['created_at'],
				)
			);
			$oid = (int) $wpdb->insert_id;
			$wpdb->update( $a, array( 'order_id' => $oid ), array( 'id' => $r['id'] ) );
			$wpdb->update( Plugin::table( 'payments' ), array( 'order_id' => $oid ), array( 'appointment_id' => $r['id'] ) );
		}
	}

	public static function seed_defaults() {
		if ( false === get_option( 'bookzenix_settings' ) ) {
			add_option( 'bookzenix_settings', Settings::defaults() );
		}
	}
}