<?php


namespace Vizyt\Core;

defined( 'ABSPATH' ) || exit;
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- upgrades Vizyt's own tables ({prefix}vizyt_*) under a lock; the stored version is read past the object cache on purpose.

class Migrator {

    /**
     * Maps version => callable. Null means no migration needed (initial install).
     *
     * @var array<string, callable|null>
     */
    private array $migrations = [
        '1.0.0' => null, // Initial schema handled by Activator
        '1.1.0' => [ self::class, 'add_booking_lang' ],
        '1.2.0' => [ self::class, 'add_staff_job_title' ],
        '1.3.0' => [ self::class, 'add_booking_idempotency_key' ],
        '1.4.0' => [ self::class, 'migrate_1_4_0' ],
        '1.5.0' => [ self::class, 'migrate_1_5_0' ],
        '1.6.0' => [ self::class, 'migrate_1_6_0' ],
        '1.7.0' => null, // No schema change (Elementor widget); keeps the stored version in step with VIZYT_VERSION.
        '1.8.0' => null, // No schema change (Freemius licensing).
        '1.8.1' => null, // No schema change (wp.org review fixes).
    ];

    /**
     * Option: the migration that failed and why ({version, error}); shown
     * to the admin on Vizyt screens until a later run gets through.
     */
    const ERROR_OPTION = 'vizyt_db_error';

    /**
     * 1.5.0 — business-wide days off (special_days.staff_id NULL = the whole
     * business), the covering index of the customer list's aggregates, and
     * customer tags in one format ("a,b": trimmed, no empty or repeated tags,
     * at most Customer::TAGS_MAX).
     *
     * @return bool false when the column still takes no NULL (run() then
     *              keeps the stored version, so the next load tries again).
     */
    public static function migrate_1_5_0(): bool {
        if ( ! self::special_days_nullable_staff() ) {
            return false;
        }
        self::add_booking_customer_agg_index();
        self::normalize_customer_tags();

        return true;
    }

    /**
     * 1.6.0 — customer reschedule: bookings.ics_sequence (the calendar
     * file's SEQUENCE, raised on every move) and the change/cancel deadline.
     * Sites updated from an older version get 0 (no limit, as before); new
     * installs get 24 from the Activator; a saved value is kept.
     *
     * @return bool false when the column is still missing (run() then keeps
     *              the stored version, so the next load tries again).
     */
    public static function migrate_1_6_0(): bool {
        if ( ! self::add_booking_ics_sequence() ) {
            return false;
        }
        add_option( \Vizyt\Admin\Settings::OPTION_CHANGE_DEADLINE_HOURS, 0 );

        return true;
    }

    /**
     * 1.6.0 — bookings.ics_sequence, as the Activator schema declares it.
     * Safe to run again; false (and the admin notice) when the ALTER failed.
     */
    public static function add_booking_ics_sequence(): bool {
        global $wpdb;

        $table = $wpdb->prefix . 'vizyt_bookings';
        if ( $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'ics_sequence' ) ) ) {
            return true;
        }

        $ok    = false !== $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN ics_sequence int(10) unsigned NOT NULL DEFAULT 0 AFTER idempotency_key', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- the plugin's own tables (install, upgrade, uninstall).
        $error = $ok ? '' : (string) $wpdb->last_error;

        if ( $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'ics_sequence' ) ) ) {
            return true;
        }

        self::fail( '1.6.0', '' !== $error ? $error : "{$table}.ics_sequence is missing" );

        return false;
    }

    /**
     * Whether special_days.staff_id takes NULL (null = the table or the
     * column is missing).
     */
    public static function special_days_staff_nullable(): ?bool {
        global $wpdb;

        $table  = $wpdb->prefix . 'vizyt_special_days';
        $column = $wpdb->get_row( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'staff_id' ) );

        return $column ? 'YES' === $column->Null : null;
    }

    /**
     * 1.5.0 — special_days.staff_id NULL. The Activator schema declares it so
     * (dbDelta does not change a column's NULL on its own). Safe to run again.
     * The column is read again after the ALTER: when it still takes no NULL
     * (no ALTER right, a locked table…) the failure is logged and stored for
     * the admin notice, and false is returned.
     */
    public static function special_days_nullable_staff(): bool {
        global $wpdb;

        if ( false !== self::special_days_staff_nullable() ) {
            return true;
        }

        $table = $wpdb->prefix . 'vizyt_special_days';
        $ok    = false !== $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i MODIFY staff_id bigint(20) unsigned NULL', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- the plugin's own tables (install, upgrade, uninstall).
        $error = $ok ? '' : (string) $wpdb->last_error;

        if ( true === self::special_days_staff_nullable() ) {
            return true;
        }

        self::fail( '1.5.0', '' !== $error ? $error : "{$table}.staff_id still does not take NULL" );

        return false;
    }

    /**
     * 1.5.0 — KEY customer_agg (customer_id, currency, status, start_datetime,
     * price) on bookings: the customer list's aggregates read the index only.
     * As the Activator schema declares it. Safe to run again.
     */
    public static function add_booking_customer_agg_index(): void {
        global $wpdb;

        $table  = $wpdb->prefix . 'vizyt_bookings';
        $exists = $wpdb->get_var( $wpdb->prepare( 'SHOW INDEX FROM %i WHERE Key_name = %s', $table, 'customer_agg' ) );

        if ( ! $exists ) {
            $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD KEY customer_agg (customer_id, currency, status, start_datetime, price)', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- the plugin's own tables (install, upgrade, uninstall).
        }
    }

    /** Log a failed migration and keep it for the admin notice. */
    private static function fail( string $version, string $error ): void {
        error_log( "Vizyt: database update {$version} failed: {$error}" ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- a failed database update is also shown in an admin notice; the log keeps it for the host.
        update_option( self::ERROR_OPTION, [ 'version' => $version, 'error' => $error ], false );
    }

    /**
     * admin_notices: on Vizyt screens, the migration that failed (see fail()).
     */
    public static function render_notice(): void {
        if ( ! \Vizyt\Admin\AdminChrome::is_vizyt_screen() || ! current_user_can( 'manage_vizyt' ) ) {
            return;
        }
        $failed = get_option( self::ERROR_OPTION );
        if ( ! is_array( $failed ) || empty( $failed['version'] ) ) {
            return;
        }

        printf(
            '<div class="notice notice-error"><p>%s</p></div>',
            esc_html(
                sprintf(
                    /* translators: 1: database error message, 2: database version, e.g. 1.5.0 */
                    __( 'Vizyt could not update its database: %1$s (update %2$s). Vizyt tries again on the next page load; if this message stays, ask your host to check that the database user may change tables (ALTER).', 'vizyt' ),
                    (string) ( $failed['error'] ?? '' ),
                    (string) $failed['version']
                )
            )
        );
    }

    /** Customers per batch of the tag clean-up. */
    const TAGS_BATCH = 500;

    /**
     * 1.5.0 — tags were free text before (whatever the API got, up to 500
     * characters). Rewrite each row in the stored format; rows already in it
     * are not touched. When the format cannot keep everything (a tag cut at
     * TAG_MAX_LENGTH, tags past TAGS_MAX, characters sanitising removes) the
     * old text is added to the customer's notes ("Old tags: …"); spacing,
     * empty and repeated tags alone lose nothing. Walks the table by primary
     * key. Safe to run again.
     */
    public static function normalize_customer_tags( int $batch = self::TAGS_BATCH ): void {
        global $wpdb;

        $table = $wpdb->prefix . 'vizyt_customers';
        $batch = max( 1, $batch );
        $last  = 0;

        do {
            $rows = (array) $wpdb->get_results(
                $wpdb->prepare( "SELECT id, tags, notes FROM %i WHERE id > %d AND tags IS NOT NULL AND tags <> '' ORDER BY id ASC LIMIT %d", $table, $last, $batch )
            );
            foreach ( $rows as $row ) {
                $last = (int) $row->id;
                $list = \Vizyt\Models\Customer::normalizeTags( (string) $row->tags );
                $tags = \Vizyt\Models\Customer::tagString( $list );
                if ( $tags === (string) $row->tags ) {
                    continue;
                }
                $fields = [ 'tags' => '' === $tags ? null : $tags ];
                if ( self::tags_lose_text( (string) $row->tags, $list ) ) {
                    /* translators: %s: the customer's tags as they were stored before (free text) */
                    $old             = sprintf( __( 'Old tags: %s', 'vizyt' ), (string) $row->tags );
                    $notes           = trim( (string) $row->notes );
                    $fields['notes'] = wp_kses_post( '' === $notes ? $old : $notes . "\n\n" . $old );
                }
                $wpdb->update( $table, $fields, [ 'id' => $last ] );
            }
        } while ( count( $rows ) === $batch );
    }

    /**
     * Whether $list (normalizeTags() of $raw) lost text of $raw: anything
     * but spacing, empty and repeated (case-insensitive) tags.
     *
     * @param string[] $list
     */
    private static function tags_lose_text( string $raw, array $list ): bool {
        $kept = [];
        foreach ( (array) preg_split( '/[,;]/u', $raw ) as $tag ) {
            $tag = trim( (string) preg_replace( '/\s+/u', ' ', (string) $tag ) );
            if ( '' !== $tag && ! isset( $kept[ mb_strtolower( $tag ) ] ) ) {
                $kept[ mb_strtolower( $tag ) ] = $tag;
            }
        }

        return array_values( $kept ) !== $list;
    }

    /**
     * 1.4.0 — booking history table, price snapshot backfill, the
     * (status, start_datetime) index the dashboard and the lists filter by.
     * (Sites already at 1.3.0 have the idempotency key, so this is a separate step.)
     */
    public static function migrate_1_4_0(): void {
        Activator::createBookingEventsTable();
        self::add_booking_status_start_index();
        self::backfill_booking_prices();
    }

    /**
     * 1.4.0 — KEY status_start (status, start_datetime) on bookings, as the
     * Activator schema declares it (so a later dbDelta finds it in place).
     * Safe to run again.
     */
    public static function add_booking_status_start_index(): void {
        global $wpdb;

        $table  = $wpdb->prefix . 'vizyt_bookings';
        $exists = $wpdb->get_var( $wpdb->prepare( 'SHOW INDEX FROM %i WHERE Key_name = %s', $table, 'status_start' ) );

        if ( ! $exists ) {
            $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD KEY status_start (status, start_datetime)', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- the plugin's own tables (install, upgrade, uninstall).
        }
    }

    /** Bookings per UPDATE of the price backfill (primary key range). */
    const BACKFILL_BATCH = 2000;

    /**
     * 1.4.0 — bookings made before 1.4.0 never stored a price (always 0.00,
     * currency UAH). Copy the service's current price and currency into
     * those rows so the dashboard revenue has something to sum; bookings
     * of deleted or free services keep 0. Walks the table in primary key
     * ranges so a big table is never locked by one long UPDATE. Safe to
     * run again (only rows still at 0 change).
     */
    public static function backfill_booking_prices( int $batch = self::BACKFILL_BATCH ): void {
        global $wpdb;

        $batch = max( 1, $batch );
        $max   = (int) $wpdb->get_var( "SELECT MAX(id) FROM {$wpdb->prefix}vizyt_bookings" );

        for ( $from = 0; $from < $max; $from += $batch ) {
            $wpdb->query(
                $wpdb->prepare(
                    "UPDATE {$wpdb->prefix}vizyt_bookings b
                       INNER JOIN {$wpdb->prefix}vizyt_services s ON s.id = b.service_id
                        SET b.price = s.price, b.currency = s.currency
                      WHERE b.id > %d AND b.id <= %d AND b.price = 0 AND s.price > 0",
                    $from,
                    $from + $batch
                )
            );
        }
    }

    /**
     * 1.3.0 — the widget's per-review-screen key, so a re-sent booking
     * request (lost response, double tap) returns the first booking instead
     * of creating another. Not unique: dedupe is key + customer email.
     */
    public static function add_booking_idempotency_key(): void {
        global $wpdb;

        $table  = $wpdb->prefix . 'vizyt_bookings';
        $exists = $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'idempotency_key' ) );

        if ( ! $exists ) {
            $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN idempotency_key varchar(64) NULL DEFAULT NULL AFTER lang, ADD KEY idempotency_key (idempotency_key)', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- the plugin's own tables (install, upgrade, uninstall).
        }
    }

    /**
     * 1.2.0 — staff job title ("Certified practitioner · 9 years"), shown by
     * themes on team cards.
     */
    public static function add_staff_job_title(): void {
        global $wpdb;

        $table  = $wpdb->prefix . 'vizyt_staff';
        $exists = $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'job_title' ) );

        if ( ! $exists ) {
            $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN job_title varchar(150) NULL AFTER phone', $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- the plugin's own tables (install, upgrade, uninstall).
        }
    }

    /**
     * 1.1.0 — bookings remember the visitor's language (Polylang / WPML)
     * so notification emails are sent in it.
     */
    public static function add_booking_lang(): void {
        global $wpdb;

        $table  = $wpdb->prefix . 'vizyt_bookings';
        $exists = $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'lang' ) );

        if ( ! $exists ) {
            $wpdb->query( $wpdb->prepare( "ALTER TABLE %i ADD COLUMN lang varchar(20) NOT NULL DEFAULT '' AFTER source", $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- the plugin's own tables (install, upgrade, uninstall).
        }
    }

    /**
     * MySQL named lock held while migrations run. Server-wide, so suffixed
     * with the site (two installs on one MySQL server never block each other).
     */
    public static function lock_name(): string {
        global $wpdb;

        return 'vizyt_migrate_' . substr( md5( DB_NAME . $wpdb->prefix ), 0, 8 );
    }

    /**
     * Run every migration newer than the stored version. Concurrent requests
     * (the first page loads after an update) do not run them twice: only the
     * request holding the lock migrates, the others carry on at once, and the
     * version is re-read from the database once the lock is held. A
     * migration returning false stops the run without storing its version.
     */
    public function run(): void {
        global $wpdb;

        $lock = self::lock_name();
        if ( '1' !== (string) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 0)', $lock ) ) ) {
            return;
        }

        try {
            // Not get_option(): this request's copy may predate the migration
            // another request has just finished.
            $stored  = $wpdb->get_var( $wpdb->prepare( "SELECT option_value FROM {$wpdb->options} WHERE option_name = %s", 'vizyt_db_version' ) );
            $current = null === $stored ? '0.0.0' : (string) $stored;
            if ( get_option( 'vizyt_db_version', '0.0.0' ) !== $current ) {
                wp_cache_delete( 'alloptions', 'options' );
                wp_cache_delete( 'vizyt_db_version', 'options' );
            }

            uksort( $this->migrations, 'version_compare' );

            foreach ( $this->migrations as $version => $callback ) {
                if ( version_compare( $version, $current, '>' ) ) {
                    // A migration that returns false failed: the version
                    // stays, so the next load runs it (and the later ones) again.
                    if ( is_callable( $callback ) && false === call_user_func( $callback ) ) {
                        return;
                    }
                    update_option( 'vizyt_db_version', $version );
                }
            }
            delete_option( self::ERROR_OPTION );
        } finally {
            $wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock ) );
        }
    }
}