<?php

namespace SugarCalendarBookings\Services;

use SugarCalendarBookings\Support\Database\AbstractTableMigration;
use WP_Error;

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

/**
 * Services table.
 *
 * @since 1.0.0
 */
class ServicesTableMigration extends AbstractTableMigration {

	/**
	 * Table name.
	 *
	 * @since 1.0.0
	 *
	 * @var string
	 */
	const TABLE_NAME = 'services';

	/**
	 * Latest DB schema version.
	 *
	 * @since 1.0.0
	 *
	 * @var int
	 */
	const LATEST_VERSION = 2;

	/**
	 * Create the table.
	 *
	 * @since 1.0.0
	 *
	 * @return WP_Error|true
	 */
	protected function migrate_to_1() {

		global $wpdb;

		$table   = self::get_table_name();
		$collate = '';

		if ( $wpdb->has_cap( 'collation' ) ) {
			$collate = $wpdb->get_charset_collate();
		}

		$sql = "CREATE TABLE $table (
				id INT UNSIGNED NOT NULL AUTO_INCREMENT,
				name VARCHAR(255) NOT NULL,
				status VARCHAR(30) NOT NULL,
				description TEXT,
				duration INT NOT NULL,
				price DECIMAL(26,8) NULL,
				color VARCHAR(7) NULL,
				author_id INT UNSIGNED NOT NULL,
				created_at DATETIME NOT NULL,
				updated_at DATETIME NOT NULL,
				PRIMARY KEY  (id),
				INDEX name (name),
				INDEX status (status),
				INDEX created_at (created_at)
		) {$collate};";

		dbDelta( $sql );

		return ! empty( $wpdb->last_error ) ? new WP_Error( 'migration_failed', $wpdb->last_error ) : true;
	}

	/**
	 * Add the `slug` column and backfill existing rows.
	 *
	 * @since 1.5.0
	 *
	 * @return WP_Error|true
	 */
	protected function migrate_to_2() { // phpcs:ignore Generic.Metrics.CyclomaticComplexity.MaxExceeded, WPForms.PHP.HooksMethod.InvalidPlaceForAddingHooks

		global $wpdb;

		$table = self::get_table_name();

		// Direct DB: dbDelta() can't backfill, and the UNIQUE index must come
		// after the backfill so duplicate empty-string rows don't block it.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
		// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter

		// 1. Add the column if it doesn't exist yet.
		$column_exists = $wpdb->get_var(
			$wpdb->prepare( "SHOW COLUMNS FROM `{$table}` LIKE %s", 'slug' )
		);

		if ( ! $column_exists ) {
			$result = $wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `slug` VARCHAR(200) NOT NULL DEFAULT '' AFTER `name`" );

			if ( $result === false ) {
				return new WP_Error( 'migration_failed', $wpdb->last_error );
			}
		}

		// 2. Backfill existing rows with a sluggified unique name.
		$rows       = $wpdb->get_results( "SELECT id, name FROM `{$table}` WHERE slug = '' OR slug IS NULL" );
		$used_slugs = array_flip( $wpdb->get_col( "SELECT slug FROM `{$table}` WHERE slug != ''" ) );

		$updates = [];

		foreach ( $rows as $row ) {

			$base = sanitize_title( $row->name );

			if ( $base === '' ) {
				$base = 'service';
			}

			$candidate = $base;
			$suffix    = 2;

			while ( isset( $used_slugs[ $candidate ] ) ) {

				$candidate = $base . '-' . $suffix;

				++$suffix;
			}

			$updates[ (int) $row->id ] = $candidate;
			$used_slugs[ $candidate ]  = true;
		}

		if ( ! empty( $updates ) ) {

			$cases = [];
			$args  = [];

			foreach ( $updates as $id => $slug ) {
				$cases[] = 'WHEN %d THEN %s';
				$args[]  = $id;
				$args[]  = $slug;
			}

			$ids_placeholders = implode( ',', array_fill( 0, count( $updates ), '%d' ) );
			$args             = array_merge( $args, array_keys( $updates ) );

			$sql = $wpdb->prepare(
				"UPDATE `{$table}` SET slug = CASE id " . implode( ' ', $cases ) . " END WHERE id IN ({$ids_placeholders})",
				$args
			);

			if ( $wpdb->query( $sql ) === false ) {
				return new WP_Error( 'migration_failed', $wpdb->last_error );
			}
		}

		// 3. Add the UNIQUE index (after backfill, so duplicate '' rows can't block it).
		$index_exists = $wpdb->get_var(
			$wpdb->prepare( "SHOW INDEX FROM `{$table}` WHERE Key_name = %s", 'slug' )
		);

		if ( ! $index_exists ) {
			$result = $wpdb->query( "ALTER TABLE `{$table}` ADD UNIQUE INDEX `slug` (`slug`)" );

			if ( $result === false ) {
				return new WP_Error( 'migration_failed', $wpdb->last_error );
			}
		}

		// phpcs:enable

		return true;
	}
}
