<?php
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * 4.6.2: single source of truth for "does this option belong in WordPress's
 * alloptions cache?".
 *
 * What changed and why: 4.6.1 decided autoload from a hand-written list of ~80
 * option names (class-yatoon-ajax.php) plus a second, different 18-name list in
 * the activator migration. Both lists were wrong in opposite directions. The
 * ajax list swept in dozens of short front-end labels - yatoon_tip_label,
 * yatoon_venue_kicker, yatoon_portal_btn_label, yatoon_success_next_title - and
 * pushed them out of alloptions. Those are 20-60 byte strings that a single
 * booking-page render reads dozens of times, so demoting them replaced ~2 KB of
 * an already-loaded cache blob with dozens of extra SELECTs per page: slower
 * than not touching them at all. The activator list, meanwhile, only ever
 * covered 18 of the keys, so upgraded sites kept the rest autoloaded anyway.
 *
 * The rule below is deliberately conservative: it only demotes long-form bodies
 * that are read by admin screens and notification jobs, never by a page render.
 * Anything it is not sure about stays autoloaded, because a wrong "keep" costs
 * a few hundred bytes once per request while a wrong "demote" costs a query per
 * read on the hottest page in the plugin.
 *
 * @param string   $key          Option name.
 * @param int|null $value_length Byte length of the stored value when known.
 *                               Null means "unknown", which is treated as
 *                               "too small to bother demoting".
 * @return bool True to keep autoload = yes, false to demote to autoload = no.
 */
function yatoon_option_wants_autoload( $key, $value_length = null ) {
    $key = (string) $key;

    // 1. Hot keys the suffix families below would otherwise catch. These are
    //    read while rendering the booking form / portal, so they must stay in
    //    alloptions no matter what their name ends with.
    static $always_autoload = array(
        // A template *slug* ("classic", "venue"), not a template body. 4.6.1's
        // "_template" instinct would have demoted the single hottest key here.
        'yatoon_booking_template'       => true,
        'yatoon_portal_banner_text'     => true,
        'yatoon_portal_signin_text'     => true,
        'yatoon_rebooking_nudge_text'   => true,
        'yatoon_availability_help_text' => true,
        'yatoon_success_pay_later_text' => true,
        'yatoon_success_deposit_text'   => true,
        'yatoon_success_followup_text'  => true,
        'sbs_success_pay_later_text'    => true,
        'sbs_success_deposit_text'      => true,
        'sbs_success_followup_text'     => true,
        // Read by get_available_times() on every slot lookup.
        'yatoon_closed_dates'           => true,
        'yatoon_closed_label'           => true,
    );
    if ( isset( $always_autoload[ $key ] ) ) {
        return true;
    }

    // 2. Known long-form bodies whose names do not match the suffix families
    //    (they end in _text, which is size-gated below, but these are always
    //    kilobytes of policy / consent copy).
    static $always_demote = array(
        'yatoon_policy_text'     => true,
        'yatoon_consent_text'    => true,
        'yatoon_cof_policy_text' => true,
        'sbs_policy_text'        => true,
        'sbs_consent_text'       => true,
    );
    if ( isset( $always_demote[ $key ] ) ) {
        return false;
    }

    // 3. Suffix families that are long-form by construction. Message bodies,
    //    rendered HTML and rolling log arrays are never read during a front-end
    //    render, so size does not need checking.
    static $cold_suffixes = array( '_template', '_logs', '_html', '_policy', '_consent' );
    foreach ( $cold_suffixes as $suffix ) {
        if ( substr( $key, -strlen( $suffix ) ) === $suffix ) {
            return false;
        }
    }

    // 4. "_text" is the ambiguous family: it holds both multi-kilobyte policy
    //    copy and 30-byte button subtitles. Only demote when the stored value
    //    really is large. Unknown length stays autoloaded.
    if ( '_text' === substr( $key, -5 ) ) {
        return ! ( null !== $value_length && (int) $value_length >= 512 );
    }

    // 5. Everything else - _label, _title, _btn, _kicker, _subtext, flags,
    //    ids, credentials - keeps autoload.
    return true;
}

/**
 * 4.6.2: option names that 4.6.1 demoted by name and that must be promoted back
 * into alloptions.
 *
 * Only short front-end copy families are eligible, so the repair pass can never
 * accidentally promote a lock, a URL cache, a page id or a log blob that some
 * other code path deliberately stored with autoload = no.
 *
 * @param string $key Option name.
 * @return bool True when the key is eligible for re-promotion.
 */
function yatoon_option_is_repromotable( $key ) {
    $key = (string) $key;
    if ( 0 !== strpos( $key, 'yatoon_' ) && 0 !== strpos( $key, 'sbs_' ) ) {
        return false;
    }
    static $copy_suffixes = array(
        '_label', '_title', '_subtitle', '_btn', '_kicker', '_subtext',
        '_note', '_prefill', '_text',
    );
    foreach ( $copy_suffixes as $suffix ) {
        if ( substr( $key, -strlen( $suffix ) ) === $suffix ) {
            return true;
        }
    }
    return false;
}

class YATOON_Activator {

    /**
     * Marker for the 4.6.2 autoload pass.
     *
     * 4.6.2: the old marker was named ...migrated_461 but is written by this
     * 4.6.2 migration, and the logic behind it has changed completely. A fresh
     * marker name is what makes sites that already ran the 4.6.1 pass run the
     * new one exactly once - the presence of the old marker says nothing about
     * whether the new rules were ever applied.
     */
    const AUTOLOAD_MIGRATION_MARKER = 'yatoon_option_autoload_migrated_462';

    /**
     * Align option autoload flags with yatoon_option_wants_autoload().
     *
     * 4.6.2 - three defects fixed here:
     *  1. The old version carried its own 18-name list while the runtime save
     *     path used a different ~80-name list, so upgraded sites ended up with
     *     autoload flags that matched neither. It now scans every yatoon_ /
     *     sbs_ option and asks the shared predicate.
     *  2. It flipped autoload with raw SQL and never invalidated the options
     *     cache, so on Redis/Memcached sites the same request (and every other
     *     request until the cache expired) kept serving the pre-migration
     *     alloptions blob.
     *  3. It only ever demoted. Sites that ran 4.6.1 have short button labels
     *     stuck at autoload = no; this pass promotes those back.
     *
     * Idempotent: a second run finds nothing left to change and writes the same
     * marker. Safe to interrupt: partial work is simply re-detected next run
     * because the marker is written last.
     */
    public static function migrate_large_option_autoload() {
        if ( '1' === (string) get_option( self::AUTOLOAD_MIGRATION_MARKER, '0' ) ) {
            return;
        }
        global $wpdb;

        // Name + length only. Even on a site with a few hundred yatoon options
        // this reads a few KB, versus pulling every template body into PHP.
        $like_yatoon = $wpdb->esc_like( 'yatoon_' ) . '%';
        $like_sbs    = $wpdb->esc_like( 'sbs_' ) . '%';
        $rows        = $wpdb->get_results( $wpdb->prepare(
            "SELECT option_name, autoload, LENGTH(option_value) AS value_length
               FROM {$wpdb->options}
              WHERE option_name LIKE %s OR option_name LIKE %s",
            $like_yatoon,
            $like_sbs
        ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        if ( ! is_array( $rows ) ) {
            return; // Query failed - leave the marker unset so this retries.
        }

        // WordPress 6.6 replaced the 'yes'/'no' autoload values with
        // 'on'/'off' (plus 'auto-on'/'auto-off'/'auto' for values it manages
        // itself) and left older rows alone, so a site can hold a mix. Reading
        // only 'yes' would misclassify every option written by 6.6+.
        $autoloaded_values = array( 'yes', 'on', 'auto', 'auto-on' );

        $to_demote  = array();
        $to_promote = array();
        foreach ( $rows as $row ) {
            $name        = (string) $row->option_name;
            $is_autoload = in_array( strtolower( (string) $row->autoload ), $autoloaded_values, true );
            $wants       = yatoon_option_wants_autoload( $name, (int) $row->value_length );

            if ( $is_autoload && ! $wants ) {
                $to_demote[] = $name;
            } elseif ( ! $is_autoload && $wants && yatoon_option_is_repromotable( $name ) ) {
                $to_promote[] = $name;
            }
        }

        $changed = array_merge(
            self::flip_option_autoload( $to_demote, 'no' ),
            self::flip_option_autoload( $to_promote, 'yes' )
        );

        if ( ! empty( $changed ) ) {
            // 4.6.2: the missing half of the old migration. Without this the
            // persistent object cache keeps handing out the stale alloptions
            // blob, so the request that ran the migration - and every later
            // one until the cache is evicted - still sees the old flags.
            wp_cache_delete( 'alloptions', 'options' );
            wp_cache_delete( 'notoptions', 'options' );
            foreach ( $changed as $name ) {
                wp_cache_delete( $name, 'options' );
            }
        }

        // The 4.6.1 marker is itself an autoloaded orphan once this runs.
        delete_option( 'yatoon_large_text_autoload_migrated_461' );
        update_option( self::AUTOLOAD_MIGRATION_MARKER, '1', false );
    }

    /**
     * 4.6.2: flip autoload for a list of option names in bounded batches.
     *
     * Batched so a site with a large options table never builds a single
     * statement with hundreds of placeholders. Names come from option_name
     * values we just read back, and are still passed through prepare().
     *
     * @param string[] $names    Option names to change.
     * @param string   $autoload 'yes' or 'no'.
     * @return string[] Names actually included in an executed statement.
     */
    private static function flip_option_autoload( array $names, $autoload ) {
        if ( empty( $names ) ) {
            return array();
        }
        global $wpdb;

        $autoload = ( 'yes' === $autoload ) ? 'yes' : 'no';
        $applied  = array();
        $names    = array_values( array_unique( $names ) );

        // 4.6.2: WordPress 6.6+ ships a first-class API for exactly this. It
        // writes whichever autoload token the current schema uses and flushes
        // the option caches itself, so prefer it and keep the raw UPDATE only
        // as a fallback for older cores.
        if ( function_exists( 'wp_set_option_autoload_values' ) ) {
            foreach ( array_chunk( $names, 100 ) as $chunk ) {
                $values = array_fill_keys( $chunk, ( 'yes' === $autoload ) );
                $result = wp_set_option_autoload_values( $values );
                if ( is_array( $result ) ) {
                    $applied = array_merge( $applied, $chunk );
                }
            }
            return $applied;
        }

        foreach ( array_chunk( $names, 100 ) as $chunk ) {
            $placeholders = implode( ',', array_fill( 0, count( $chunk ), '%s' ) );
            $params       = array_merge( array( $autoload ), $chunk );
            $result       = $wpdb->query( $wpdb->prepare(
                "UPDATE {$wpdb->options} SET autoload = %s WHERE option_name IN ({$placeholders})",
                $params
            ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            if ( false !== $result ) {
                $applied = array_merge( $applied, $chunk );
            }
        }

        return $applied;
    }

    /** Create the customer account page once and remember its canonical URL. */
    public static function ensure_customer_portal_page() {
        if ( trim( (string) get_option( 'yatoon_portal_url', '' ) ) ) {
            return absint( get_option( 'yatoon_portal_page_id', 0 ) );
        }

        $page_id = absint( get_option( 'yatoon_portal_page_id', 0 ) );
        if ( $page_id && get_post( $page_id ) ) {
            update_option( 'yatoon_portal_url', get_permalink( $page_id ), false );
            return $page_id;
        }

        global $wpdb;
        $like = '%' . $wpdb->esc_like( '[yatoon_customer_portal' ) . '%';
        $page_id = absint( $wpdb->get_var( $wpdb->prepare(
            "SELECT ID FROM {$wpdb->posts} WHERE post_type='page' AND post_status NOT IN ('trash','auto-draft') AND post_content LIKE %s ORDER BY ID ASC LIMIT 1",
            $like
        ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

        if ( ! $page_id ) {
            $created = wp_insert_post( array(
                'post_title'   => __( 'My Appointments', 'yatoon-booking-system' ),
                'post_name'    => 'my-appointments',
                'post_content' => '[yatoon_customer_portal]',
                'post_status'  => 'publish',
                'post_type'    => 'page',
            ), true );
            if ( is_wp_error( $created ) ) {
                return 0;
            }
            $page_id = absint( $created );
        }

        update_option( 'yatoon_portal_page_id', $page_id, false );
        update_option( 'yatoon_portal_url', get_permalink( $page_id ), false );
        return $page_id;
    }

    public static function cron_schedules( $schedules ) {
        $schedules['5min'] = array(
            'interval' => 300,
            'display'  => __('Every 5 Minutes', 'yatoon-booking-system'),
        );
        $schedules['15min'] = array(
            'interval' => 900,
            'display'  => __('Every 15 Minutes', 'yatoon-booking-system'),
        );
        $schedules['weekly'] = array(
            'interval' => 604800,
            'display'  => __('Once a Week', 'yatoon-booking-system'),
        );
        $schedules['monthly'] = array(
            'interval' => 2592000,
            'display'  => __('Once a Month', 'yatoon-booking-system'),
        );
        return $schedules;
    }

    public static function seed_starter_data( $industry = 'nail' ) {
        global $wpdb;

        $industry = sanitize_key( $industry );
        if ( ! in_array( $industry, array( 'nail', 'hair', 'massage', 'medspa', 'tattoo' ), true ) ) {
            $industry = 'nail';
        }

        $table_services = $wpdb->prefix . 'yatoon_services';
        $table_staff = $wpdb->prefix . 'yatoon_staff';
        $table_locations = $wpdb->prefix . 'yatoon_locations';
        $table_bookings = $wpdb->prefix . 'yatoon_bookings';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $service_count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table_services}");
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $staff_count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table_staff}");
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $location_count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table_locations}");
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $booking_count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table_bookings}");

        if ($service_count > 0 || $staff_count > 0 || $location_count > 0 || $booking_count > 0) {
            return;
        }

        $site_name = wp_specialchars_decode(get_bloginfo('name'), ENT_QUOTES);
        $business_name = get_option('yatoon_business_name', '');
        if (empty($business_name)) {
            $business_name = $site_name ? $site_name : __( 'Yatoon Beauty Studio', 'yatoon-booking-system' );
            update_option('yatoon_business_name', $business_name);
        }

        $address = get_option('sbs_business_address', '');
        $phone = get_option('sbs_business_phone', '');
        $contact_email = get_option('yatoon_contact_email', '');

        $wpdb->insert(
            $table_locations,
            array(
                'square_id' => 'local-main-location',
                'name' => $business_name,
                'address' => $address,
                'phone' => $phone,
                'timezone' => yatoon_timezone_string(),
                'is_active' => 1,
            )
        );

        $staff_rows = array(
            array(
                'square_id' => 'local-staff-ava',
                'name' => 'Ava',
                'email' => $contact_email,
                'phone' => $phone,
                'bio' => __( 'Gel extensions, clean cuticle work, and glossy structured manicures.', 'yatoon-booking-system' ),
                'calendar_color' => '#ec4899',
                'is_bookable' => 1,
                'is_active' => 1,
                'sort_order' => 1,
            ),
            array(
                'square_id' => 'local-staff-mia',
                'name' => 'Mia',
                'email' => $contact_email,
                'phone' => $phone,
                'bio' => __( 'Pedicures, dipping powder, natural nail care, and relaxed appointments.', 'yatoon-booking-system' ),
                'calendar_color' => '#16a34a',
                'is_bookable' => 1,
                'is_active' => 1,
                'sort_order' => 2,
            ),
        );

        $staff_ids = array();
        foreach ($staff_rows as $staff_row) {
            $inserted = $wpdb->insert($table_staff, $staff_row);
            if ($inserted) {
                $staff_ids[] = (int) $wpdb->insert_id;
            }
        }

        $service_rows = array(
            array(
                'square_id' => 'local-service-acrylic-gel',
                'name' => __( 'Acrylic Gel', 'yatoon-booking-system' ),
                'description' => __( 'Modern nail enhancement with strength, shaping, and a polished finish.', 'yatoon-booking-system' ),
                'duration_minutes' => 60,
                'price' => 45.00,
                'category' => __( 'Enhancements', 'yatoon-booking-system' ),
                'is_active' => 1,
                'allow_staff_select' => 1,
                'allow_design_upload' => 1,
                'sort_order' => 1,
                'is_featured' => 1,
            ),
            array(
                'square_id' => 'local-service-builder-gel',
                'name' => __( 'Builder Gel', 'yatoon-booking-system' ),
                'description' => __( 'Builder Gel experience for lasting strength with a smooth natural look.', 'yatoon-booking-system' ),
                'duration_minutes' => 55,
                'price' => 55.00,
                'category' => __( 'Enhancements', 'yatoon-booking-system' ),
                'is_active' => 1,
                'allow_staff_select' => 1,
                'allow_design_upload' => 1,
                'sort_order' => 2,
                'is_featured' => 1,
            ),
            array(
                'square_id' => 'local-service-dipping-powder',
                'name' => __( 'Dipping Powder', 'yatoon-booking-system' ),
                'description' => __( 'Natural-looking powder service with extra strength and long-lasting color.', 'yatoon-booking-system' ),
                'duration_minutes' => 55,
                'price' => 45.00,
                'category' => __( 'Enhancements', 'yatoon-booking-system' ),
                'is_active' => 1,
                'allow_staff_select' => 1,
                'allow_design_upload' => 1,
                'sort_order' => 3,
                'is_featured' => 1,
            ),
            array(
                'square_id' => 'local-service-gel-mani',
                'name' => __( 'Gel Manicure', 'yatoon-booking-system' ),
                'description' => __( 'Cuticle care, shaping, premium gel color, and a glossy salon finish.', 'yatoon-booking-system' ),
                'duration_minutes' => 45,
                'price' => 38.00,
                'category' => __( 'Manicures', 'yatoon-booking-system' ),
                'is_active' => 1,
                'allow_staff_select' => 1,
                'allow_design_upload' => 1,
                'sort_order' => 4,
                'is_featured' => 1,
            ),
            array(
                'square_id' => 'local-service-spa-pedi',
                'name' => __( 'Luxury Spa Pedicure', 'yatoon-booking-system' ),
                'description' => __( 'Warm soak, exfoliation, massage, detailed nail care, and polish.', 'yatoon-booking-system' ),
                'duration_minutes' => 60,
                'price' => 60.00,
                'category' => __( 'Pedicures', 'yatoon-booking-system' ),
                'is_active' => 1,
                'allow_staff_select' => 1,
                'allow_design_upload' => 0,
                'sort_order' => 5,
                'is_featured' => 1,
            ),
            array(
                'square_id' => 'local-service-simple-art',
                'name' => __( 'Simple Nail Art', 'yatoon-booking-system' ),
                'description' => __( 'Accent art, chrome, French tips, or detail work added to a manicure.', 'yatoon-booking-system' ),
                'duration_minutes' => 20,
                'price' => 15.00,
                'category' => __( 'Nail Art', 'yatoon-booking-system' ),
                'is_active' => 1,
                'allow_staff_select' => 0,
                'allow_design_upload' => 1,
                'sort_order' => 6,
                'is_featured' => 0,
            ),
        );

        $industry_presets = array(
            'hair' => array(
                array( __( 'Haircut & Finish', 'yatoon-booking-system' ), 60, 65, __( 'Hair', 'yatoon-booking-system' ), __( 'Consultation, tailored cut, shampoo, and finished style.', 'yatoon-booking-system' ) ),
                array( __( 'Color Retouch', 'yatoon-booking-system' ), 90, 95, __( 'Color', 'yatoon-booking-system' ), __( 'Root color refresh with professional finish.', 'yatoon-booking-system' ) ),
                array( __( 'Blowout', 'yatoon-booking-system' ), 45, 50, __( 'Styling', 'yatoon-booking-system' ), __( 'Shampoo and polished blow-dry style.', 'yatoon-booking-system' ) ),
            ),
            'massage' => array(
                array( __( 'Swedish Massage', 'yatoon-booking-system' ), 60, 90, __( 'Massage', 'yatoon-booking-system' ), __( 'Relaxing full-body massage with customized pressure.', 'yatoon-booking-system' ) ),
                array( __( 'Deep Tissue Massage', 'yatoon-booking-system' ), 60, 110, __( 'Massage', 'yatoon-booking-system' ), __( 'Focused therapeutic pressure for persistent tension.', 'yatoon-booking-system' ) ),
                array( __( 'Hot Stone Massage', 'yatoon-booking-system' ), 75, 135, __( 'Massage', 'yatoon-booking-system' ), __( 'Heated stones and massage for deep relaxation.', 'yatoon-booking-system' ) ),
            ),
            'medspa' => array(
                array( __( 'Skin Consultation', 'yatoon-booking-system' ), 30, 50, __( 'Consultations', 'yatoon-booking-system' ), __( 'Personalized assessment and treatment plan.', 'yatoon-booking-system' ) ),
                array( __( 'Signature Facial', 'yatoon-booking-system' ), 60, 145, __( 'Facials', 'yatoon-booking-system' ), __( 'Customized cleanse, treatment, hydration, and aftercare.', 'yatoon-booking-system' ) ),
                array( __( 'Chemical Peel', 'yatoon-booking-system' ), 45, 175, __( 'Skin Treatments', 'yatoon-booking-system' ), __( 'Professional peel with suitability review and aftercare.', 'yatoon-booking-system' ) ),
            ),
            'tattoo' => array(
                array( __( 'Tattoo Consultation', 'yatoon-booking-system' ), 30, 0, __( 'Consultations', 'yatoon-booking-system' ), __( 'Discuss placement, sizing, design direction, and estimate.', 'yatoon-booking-system' ) ),
                array( __( 'Small Tattoo Session', 'yatoon-booking-system' ), 90, 150, __( 'Tattoo', 'yatoon-booking-system' ), __( 'Reserved session for a small custom tattoo.', 'yatoon-booking-system' ) ),
                array( __( 'Half-Day Tattoo Session', 'yatoon-booking-system' ), 240, 500, __( 'Tattoo', 'yatoon-booking-system' ), __( 'Extended custom tattoo appointment with planned breaks.', 'yatoon-booking-system' ) ),
            ),
        );
        if ( isset( $industry_presets[ $industry ] ) ) {
            $service_rows = array();
            foreach ( $industry_presets[ $industry ] as $index => $preset ) {
                $service_rows[] = array(
                    'square_id' => 'local-' . $industry . '-service-' . ( $index + 1 ),
                    'name' => $preset[0], 'description' => $preset[4], 'duration_minutes' => $preset[1],
                    'price' => $preset[2], 'category' => $preset[3], 'is_active' => 1,
                    'allow_staff_select' => 1, 'allow_design_upload' => 'tattoo' === $industry ? 1 : 0,
                    'sort_order' => $index + 1, 'is_featured' => 1,
                );
            }
        }

        $service_ids = array();
        foreach ($service_rows as $service_row) {
            $inserted = $wpdb->insert($table_services, $service_row);
            if ($inserted) {
                $service_ids[] = (int) $wpdb->insert_id;
            }
        }

        if (!empty($staff_ids) && !empty($service_ids)) {
            $table_staff_services = $wpdb->prefix . 'yatoon_staff_services';
            foreach ($staff_ids as $staff_id) {
                foreach ($service_ids as $service_id) {
                    $wpdb->insert(
                        $table_staff_services,
                        array(
                            'staff_id' => $staff_id,
                            'service_id' => $service_id,
                        )
                    );
                }
            }
        }

        update_option('yatoon_starter_data_seeded', '1');
        update_option('yatoon_starter_industry', $industry, false);
    }

    public static function ensure_default_capabilities() {

        $admin = get_role( 'administrator' );
        if ( ! $admin ) {
            return;
        }

        foreach ( array(
            'yatoon_manage_settings',
            'yatoon_manage_bookings',
            'yatoon_manage_services',
            'yatoon_manage_staff',
            'yatoon_view_reports',
        ) as $cap ) {
            if ( ! $admin->has_cap( $cap ) ) {
                $admin->add_cap( $cap );
            }
        }
    }

    public static function activate( $network_wide = false ) {
        if ( $network_wide && is_multisite() ) {
            $site_ids = get_sites( array( 'fields' => 'ids', 'number' => 0 ) );
            foreach ( $site_ids as $site_id ) {
                switch_to_blog( (int) $site_id );
                try {
                    self::activate( false );
                } finally {
                    restore_current_blog();
                }
            }
            return;
        }

        global $wpdb;

        yatoon_record_onboarding_milestone( 'activated' );
        add_filter('cron_schedules', array(__CLASS__, 'cron_schedules'));
        self::ensure_default_capabilities();

        $charset_collate = $wpdb->get_charset_collate(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

        // Create services table
        $table_services = $wpdb->prefix . 'yatoon_services'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        $sql_services = "CREATE TABLE $table_services (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            square_id varchar(255) NOT NULL,
            name varchar(255) NOT NULL,
            description text,
	            duration_minutes int(11) NOT NULL,
	            price decimal(10,2) NOT NULL,
	            estimated_cost decimal(10,2) NOT NULL DEFAULT 0,
            category varchar(100),
            image_url varchar(500),
            is_active tinyint(1) DEFAULT 1,
            require_policy tinyint(1) DEFAULT 0,
            require_consent tinyint(1) DEFAULT 0,
            allow_staff_select tinyint(1) DEFAULT 1,
            allow_design_upload tinyint(1) DEFAULT 0,
            buffer_before_min int(11) DEFAULT 0,
            buffer_after_min int(11) DEFAULT 0,
            min_lead_time_min int(11) DEFAULT 0,
            sort_order int(11) DEFAULT 0,
            is_featured tinyint(1) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY square_id (square_id)
        ) $charset_collate;";

        // Create service options table (sub-options)
        $table_service_options = $wpdb->prefix . 'yatoon_service_options'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        $sql_options = "CREATE TABLE $table_service_options (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            service_id bigint(20) NOT NULL,
            square_id varchar(255) NOT NULL,
            square_version bigint(20) DEFAULT NULL,
            name varchar(255) NOT NULL,
            description text,
            duration_minutes int(11) NOT NULL,
            price_modifier decimal(10,2) DEFAULT 0,
            is_required tinyint(1) DEFAULT 0,
            group_name varchar(100) DEFAULT NULL,
            badge_label varchar(50) DEFAULT '',
            sort_order int(11) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY service_id (service_id),
            KEY square_id (square_id)
        ) $charset_collate;";

        // Create staff table
        $table_staff = $wpdb->prefix . 'yatoon_staff'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        $sql_staff = "CREATE TABLE $table_staff (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            square_id varchar(255) NOT NULL,
            name varchar(255) NOT NULL,
            email varchar(255),
            phone varchar(50),
            bio text,
            image_url varchar(500),
            calendar_color varchar(20) DEFAULT '',
            is_bookable tinyint(1) DEFAULT 1,
            is_active tinyint(1) DEFAULT 1,
            sort_order int(11) DEFAULT 0,
            portal_pin varchar(255) DEFAULT '',
            portal_pin_lookup varchar(64) DEFAULT '',
            portal_role varchar(20) DEFAULT 'staff',
            portal_permissions varchar(255) DEFAULT 'view_schedule,update_status,view_clients,send_messages,request_time_off',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY square_id (square_id),
            KEY portal_pin_lookup (portal_pin_lookup)
        ) $charset_collate;";

        // Create staff-service association table
        $table_staff_services = $wpdb->prefix . 'yatoon_staff_services'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        $sql_staff_services = "CREATE TABLE $table_staff_services (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            staff_id bigint(20) NOT NULL,
            service_id bigint(20) NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY staff_id (staff_id),
            KEY service_id (service_id),
            UNIQUE KEY staff_service (staff_id, service_id)
        ) $charset_collate;";

        $table_time_off_requests = $wpdb->prefix . 'yatoon_staff_time_off_requests'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        $sql_time_off_requests = "CREATE TABLE $table_time_off_requests (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            staff_id bigint(20) NOT NULL,
            start_at datetime NOT NULL,
            end_at datetime NOT NULL,
            is_all_day tinyint(1) DEFAULT 0,
            reason varchar(255) DEFAULT '',
            status varchar(20) DEFAULT 'pending',
            reviewed_by bigint(20) DEFAULT NULL,
            review_note varchar(255) DEFAULT '',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            reviewed_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            KEY staff_id (staff_id),
            KEY status (status),
            KEY start_at (start_at)
        ) $charset_collate;";

        // Create bookings table
        //
        // 4.6.2: `KEY staff_id (staff_id)`, `KEY location_id (location_id)` and
        // `KEY reminder_sent (reminder_sent)` were removed from the definition
        // below. Each one was the exact leftmost prefix of a composite index
        // that is still declared here - staff_schedule (staff_id, start_time,
        // status), location_schedule (location_id, start_time, status) and
        // reminder_due (reminder_sent, start_time, status) - so MySQL could
        // already answer every query they served from the wider index. They
        // only added write amplification and disk on the plugin's largest
        // table. yatoon_bookings_redundant_index_cleanup() drops them from
        // databases that already have them.
        //
        // `KEY start_time (start_time)` is deliberately KEPT. status_start is
        // (status, start_time), so start_time is NOT its leftmost prefix and a
        // date-range scan with no status filter would fall back to a table scan
        // without this index.
        $table_bookings = $wpdb->prefix . 'yatoon_bookings'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        $sql_bookings = "CREATE TABLE $table_bookings (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            square_booking_id varchar(255),
            client_reference varchar(64) DEFAULT '',
            customer_name varchar(255) NOT NULL,
            customer_email varchar(255) NOT NULL,
            customer_phone varchar(50),
            client_timezone varchar(100) DEFAULT NULL,
            booking_timezone varchar(100) DEFAULT NULL,
            location_id bigint(20) DEFAULT NULL,
            service_id bigint(20) NOT NULL,
            staff_id bigint(20) NOT NULL,
            start_time datetime NOT NULL,
            end_time datetime NOT NULL,
            status varchar(50) DEFAULT 'pending',
            notes text,
            design_attachment_id bigint(20) DEFAULT NULL,
            design_image_url varchar(500) DEFAULT NULL,
            design_upload_token varchar(64) DEFAULT NULL,
            total_price decimal(10,2) NOT NULL,
            square_synced tinyint(1) DEFAULT 0,
            is_any_staff tinyint(1) DEFAULT 0,
            google_event_id varchar(255) DEFAULT NULL,
            virtual_provider varchar(20) DEFAULT NULL,
            virtual_meeting_id varchar(255) DEFAULT NULL,
            virtual_meeting_url text DEFAULT NULL,
            consent_accepted tinyint(1) DEFAULT 0,
            consent_signature varchar(255) DEFAULT NULL,
            consent_signed_at datetime DEFAULT NULL,
            consent_text_snapshot text DEFAULT NULL,
            policy_acknowledged tinyint(1) DEFAULT 0,
            policy_acknowledged_at datetime DEFAULT NULL,
            policy_text_snapshot text DEFAULT NULL,
            payment_status varchar(30) DEFAULT 'unpaid',
            payment_note text DEFAULT NULL,
            payment_updated_at datetime DEFAULT NULL,
            manage_token varchar(64) DEFAULT NULL,
            manage_token_expires datetime DEFAULT NULL,
            deposit_amount decimal(10,2) DEFAULT NULL,
            deposit_paid tinyint(1) DEFAULT 0,
            deposit_intent_id varchar(255) DEFAULT NULL,
            reminder_sent tinyint(1) DEFAULT 0,
            review_requested tinyint(1) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            created_at_gmt datetime DEFAULT NULL,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY service_id (service_id),
            KEY start_time (start_time),
            KEY staff_schedule (staff_id, start_time, status),
            KEY location_schedule (location_id, start_time, status),
            KEY status_start (status, start_time),
            KEY reminder_due (reminder_sent, start_time, status),
            KEY customer_email (customer_email),
            KEY customer_phone (customer_phone),
            KEY square_booking_id (square_booking_id),
            KEY client_reference (client_reference),
            KEY review_requested (review_requested),
            KEY wc_hold_due (payment_status, status, created_at_gmt),
            UNIQUE KEY deposit_intent_id (deposit_intent_id),
            UNIQUE KEY manage_token (manage_token)
        ) $charset_collate;";

        // Create booking options table
        $table_booking_options = $wpdb->prefix . 'yatoon_booking_options'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        $sql_booking_options = "CREATE TABLE $table_booking_options (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            booking_id bigint(20) NOT NULL,
            option_id bigint(20) NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY booking_id (booking_id),
            KEY option_id (option_id)
        ) $charset_collate;";

        // Create locations table
        $table_locations = $wpdb->prefix . 'yatoon_locations'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        $sql_locations = "CREATE TABLE $table_locations (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            square_id varchar(255) NOT NULL,
            name varchar(255) NOT NULL,
            address text,
            phone varchar(50),
            timezone varchar(100),
            is_active tinyint(1) DEFAULT 1,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY square_id (square_id)
        ) $charset_collate;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql_services);
        dbDelta($sql_options);
        dbDelta($sql_staff);
        dbDelta($sql_staff_services);
        dbDelta($sql_time_off_requests);
        dbDelta($sql_bookings);
        dbDelta($sql_booking_options);
        dbDelta($sql_locations);

        // Create contact messages table
        $table_contact = $wpdb->prefix . 'yatoon_contact_messages'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        $sql_contact = "CREATE TABLE $table_contact (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            phone varchar(50) NOT NULL,
            message text NOT NULL,
            ip_address varchar(45),
            status varchar(20) DEFAULT 'new',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY created_at (created_at),
            KEY status (status)
        ) $charset_collate;";
        dbDelta($sql_contact);

        // Create clients table
        $table_clients = $wpdb->prefix . 'yatoon_clients'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        $sql_clients = "CREATE TABLE $table_clients (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            customer_name varchar(255) NOT NULL DEFAULT '',
            customer_email varchar(255) NOT NULL DEFAULT '',
            customer_phone varchar(50) NOT NULL DEFAULT '',
            notes text DEFAULT NULL,
            allergies text DEFAULT NULL,
            preferences text DEFAULT NULL,
            preferred_staff varchar(255) DEFAULT NULL,
            tags varchar(500) DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY customer_phone (customer_phone),
            KEY customer_email (customer_email)
        ) $charset_collate;";
        dbDelta( $sql_clients );

        $client_columns = array(
            'preferences' => "ALTER TABLE {$wpdb->prefix}yatoon_clients ADD COLUMN preferences text DEFAULT NULL AFTER allergies",
        );
        foreach ($client_columns as $column => $sql) {
            $exists = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}yatoon_clients LIKE '{$column}'");
            if (empty($exists)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $wpdb->query($sql);
            }
        }

        // Migration: add google_event_id column (for existing databases)
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $columns = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}yatoon_bookings LIKE 'google_event_id'");
        if (empty($columns)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->query("ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN google_event_id varchar(255) DEFAULT NULL");
        }

        // Migration: add group_booking_id - links multiple bookings that belong to the same multi-service visit
        $col_group = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}yatoon_bookings LIKE 'group_booking_id'");
        if (empty($col_group)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->query("ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN group_booking_id varchar(36) DEFAULT NULL");
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->query("ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD INDEX group_booking_id (group_booking_id)");
        }
        // Migration: add service_order - the position of this service within its group (0 = first)
        $col_order = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}yatoon_bookings LIKE 'service_order'");
        if (empty($col_order)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->query("ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN service_order tinyint(3) DEFAULT 0");
        }

        // Migration: add is_any_staff - marks whether the staff was auto-assigned (Any available)
        $col_any_staff = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}yatoon_bookings LIKE 'is_any_staff'");
        if (empty($col_any_staff)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->query("ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN is_any_staff tinyint(1) DEFAULT 0");
        }

        // Migration: add optional consent / waiver signature fields.
        $consent_columns = array();
foreach ( yatoon_booking_required_column_definitions() as $column => $definition ) {
            $consent_columns[ $column ] = "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN `{$column}` {$definition}";
        }
        foreach ($consent_columns as $column => $sql) {
            $exists = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}yatoon_bookings LIKE '{$column}'");
            if (empty($exists)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $wpdb->query($sql);
            }
        }

        // Migration: service-level booking requirements and scheduling hints.
        $service_columns = array(
            'require_policy'       => "ALTER TABLE {$wpdb->prefix}yatoon_services ADD COLUMN require_policy tinyint(1) DEFAULT 0 AFTER is_active",
            'require_consent'      => "ALTER TABLE {$wpdb->prefix}yatoon_services ADD COLUMN require_consent tinyint(1) DEFAULT 0 AFTER require_policy",
            'allow_staff_select'   => "ALTER TABLE {$wpdb->prefix}yatoon_services ADD COLUMN allow_staff_select tinyint(1) DEFAULT 1 AFTER require_consent",
            'allow_design_upload'  => "ALTER TABLE {$wpdb->prefix}yatoon_services ADD COLUMN allow_design_upload tinyint(1) DEFAULT 0 AFTER allow_staff_select",
            'buffer_before_min'    => "ALTER TABLE {$wpdb->prefix}yatoon_services ADD COLUMN buffer_before_min int(11) DEFAULT 0 AFTER allow_design_upload",
            'buffer_after_min'     => "ALTER TABLE {$wpdb->prefix}yatoon_services ADD COLUMN buffer_after_min int(11) DEFAULT 0 AFTER buffer_before_min",
            'min_lead_time_min'    => "ALTER TABLE {$wpdb->prefix}yatoon_services ADD COLUMN min_lead_time_min int(11) DEFAULT 0 AFTER buffer_after_min",
            'image_url'            => "ALTER TABLE {$wpdb->prefix}yatoon_services ADD COLUMN image_url varchar(500) DEFAULT '' AFTER min_lead_time_min",
			'is_featured'          => "ALTER TABLE {$wpdb->prefix}yatoon_services ADD COLUMN is_featured tinyint(1) DEFAULT 0",
			'estimated_cost'       => "ALTER TABLE {$wpdb->prefix}yatoon_services ADD COLUMN estimated_cost decimal(10,2) NOT NULL DEFAULT 0 AFTER price",
        );
        foreach ($service_columns as $column => $sql) {
            $exists = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}yatoon_services LIKE '{$column}'");
            if (empty($exists)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $wpdb->query($sql);
            }
        }

        // Drop UNIQUE constraint on yatoon_service_options.square_id so the same
        // Square Variation ID can be shared across multiple services (e.g. Custom Design add-on).
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $idx_rows = $wpdb->get_results("SHOW INDEX FROM {$wpdb->prefix}yatoon_service_options WHERE Key_name = 'square_id' AND Non_unique = 0");
        if (!empty($idx_rows)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->query("ALTER TABLE {$wpdb->prefix}yatoon_service_options DROP INDEX square_id");
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->query("ALTER TABLE {$wpdb->prefix}yatoon_service_options ADD INDEX square_id (square_id)");
        }

        // Migration: add group_name column to service_options (for existing databases)
        $opt_columns = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}yatoon_service_options LIKE 'group_name'");
        if (empty($opt_columns)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->query("ALTER TABLE {$wpdb->prefix}yatoon_service_options ADD COLUMN group_name varchar(100) DEFAULT NULL AFTER is_required");
        }
        $badge_columns = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}yatoon_service_options LIKE 'badge_label'");
        if (empty($badge_columns)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->query("ALTER TABLE {$wpdb->prefix}yatoon_service_options ADD COLUMN badge_label varchar(50) DEFAULT '' AFTER group_name");
        }

        // Set default options
        add_option('yatoon_square_access_token', '');
        add_option('yatoon_square_location_id', '');
        add_option('yatoon_square_booking_widget_id', 's5gplmudidnxck'); // Default values
        add_option('yatoon_square_environment', 'production'); // production or sandbox
        add_option('yatoon_auto_sync_enabled', ''); // off by default - manual sync is sufficient
        add_option('yatoon_sync_interval', 'monthly'); // default: monthly
        add_option('yatoon_use_hybrid_mode', '1'); // default: use hybrid mode
        add_option('yatoon_contact_email', ''); // leave empty - set in Settings
        add_option('yatoon_slot_interval', 30);   // default 30-minute slots
        add_option('yatoon_latest_end_hour', 20);    // default: no appointment ends after 8:00 PM
        add_option('yatoon_latest_end_minute', 0);
        add_option('yatoon_active_platform', 'local'); // default: local mode
        add_option('yatoon_booking_template', 'beauty');
        add_option('yatoon_show_advanced_menu', '0');
        add_option('yatoon_test_booking_mode', '0');
        add_option('yatoon_consent_enabled', '0');
        add_option('yatoon_consent_title', 'Consent & Release');
        add_option('yatoon_consent_text', 'I understand the nature of the selected service(s), have disclosed any relevant allergies, sensitivities, medical conditions, or concerns, and agree to receive the service(s).');
        add_option('yatoon_consent_signature_label', 'Type your full name as your signature');
        add_option('yatoon_policy_enabled', '0');
        add_option('yatoon_policy_require_ack', '1');
        add_option('yatoon_policy_title', 'Cancellation & No-Show Policy');
        add_option('yatoon_policy_text', 'Please let us know as soon as possible if you need to cancel or reschedule. This helps us offer the time to another customer. At this time, we do not charge cancellation or no-show fees.');
        add_option('yatoon_policy_ack_label', 'I have read and understand the cancellation and no-show policy.');
        // Customer self-service is discoverable immediately on a fresh install.
        // add_option() preserves an existing site's explicit on/off choices.
        add_option('yatoon_show_portal_banner', '1');
        add_option('yatoon_portal_email_login_enabled', '1');
        add_option('yatoon_portal_sms_login_enabled', '1');
        add_option('yatoon_portal_change_service_enabled', '1');
        add_option('yatoon_manage_allow_reschedule', '1');
        // Default business hours (Mon-Sat 9am-6pm, Sun closed)
        if (!get_option('yatoon_business_hours')) {
            $default_hours = YATOON_Business_Hours::get_default_hours();
            add_option('yatoon_business_hours', json_encode($default_hours));
        }
        add_option('yatoon_version', SBS_VERSION);

        self::ensure_customer_portal_page();

        // Set up scheduled tasks
        if (!wp_next_scheduled('yatoon_sync_square_data')) {
            $interval = get_option('yatoon_sync_interval', 'hourly');
            wp_schedule_event(time(), $interval, 'yatoon_sync_square_data');
        }
        if (!wp_next_scheduled('yatoon_check_waitlist_availability')) {
            wp_schedule_event(time() + 300, '15min', 'yatoon_check_waitlist_availability');
        }

        add_option('yatoon_starter_data_seeded', 'not_selected');
// Build every optional table and index during activation. Updates that
        // replace the plugin directory are handled by the version gate below.
        yatoon_run_versioned_schema_migrations( true );

        // Flush rewrite rules
        flush_rewrite_rules();
    }
}

// Register custom cron schedules.
add_filter('cron_schedules', array('YATOON_Activator', 'cron_schedules'));
add_action('admin_init', array('YATOON_Activator', 'ensure_customer_portal_page'));

// Add waitlist table
function yatoon_create_waitlist_table() {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

    $table_waitlist = $wpdb->prefix . 'yatoon_waitlist'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
    $sql = "CREATE TABLE $table_waitlist (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        customer_name varchar(255) NOT NULL,
        customer_email varchar(255) NOT NULL,
        customer_phone varchar(50),
        service_id bigint(20) NOT NULL,
        staff_id bigint(20),
        preferred_date date NOT NULL,
        preferred_time varchar(50),
        flexible_dates tinyint(1) DEFAULT 1,
        flexible_times tinyint(1) DEFAULT 1,
        notes text,
        status varchar(20) DEFAULT 'active',
        notified tinyint(1) DEFAULT 0,
        notified_at datetime DEFAULT NULL,
        matched_date date DEFAULT NULL,
        matched_time varchar(50) DEFAULT NULL,
        matched_start_at varchar(100) DEFAULT NULL,
        claim_token varchar(64) DEFAULT NULL,
        claim_expires_at datetime DEFAULT NULL,
        claim_status varchar(20) DEFAULT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY service_id (service_id),
        KEY preferred_date (preferred_date),
        KEY status (status),
        UNIQUE KEY claim_token (claim_token),
        KEY claim_expiry (claim_status, claim_expires_at)
    ) $charset_collate;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}

/** Privacy-safe, cross-device customer service favorites. */
function yatoon_create_customer_favorites_table() {
    global $wpdb;
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    $table = $wpdb->prefix . 'yatoon_customer_favorites';
    $charset = $wpdb->get_charset_collate();
    dbDelta( "CREATE TABLE {$table} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        customer_key char(64) NOT NULL,
        service_id bigint(20) unsigned NOT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY customer_service (customer_key, service_id),
        KEY service_id (service_id)
    ) {$charset};" );
}

/**
 * Add composite indexes used by calendar, availability, reminders, and reports.
 * Existing installations receive these once without rebuilding booking data.
 */
/**
 * Repair notification columns for sites that already stored the current
 * schema version before these columns were added to the 4.4.3 migration map.
 * This must run before the performance-index migration and reminder cron.
 */
function yatoon_booking_notification_schema_repair() {

    if ( get_option( 'yatoon_booking_notification_schema', '' ) === '4.4.3r1' ) {
        return;
}
    global $wpdb;
$table = $wpdb->prefix . 'yatoon_bookings';
if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) {
        return;
}

    $columns = array(
        'reminder_sent'   => "ALTER TABLE {$table} ADD COLUMN reminder_sent tinyint(1) DEFAULT 0 AFTER deposit_intent_id",
        'review_requested'=> "ALTER TABLE {$table} ADD COLUMN review_requested tinyint(1) DEFAULT 0 AFTER reminder_sent",
    );
    foreach ( $columns as $column => $sql ) {
        if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", $column ) ) ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- fixed ALTER statements; table name derives from $wpdb->prefix.
            $wpdb->query( $sql );
}
    }

    // Only mark complete after both columns really exist. A failed ALTER will
    // retry safely on the next request instead of hiding a broken reminder job.
    $complete = true;
foreach ( array_keys( $columns ) as $column ) {
        if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", $column ) ) ) {
            $complete = false;
}
    }
    if ( $complete ) {
        $review_index = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", 'review_requested' ) );
if ( ! $review_index ) {
            // The review-request cron filters this flag on every hourly run.
            $wpdb->query( "ALTER TABLE {$table} ADD INDEX review_requested (review_requested)" );
}
        $review_index = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", 'review_requested' ) );
if ( $review_index ) {
            update_option( 'yatoon_booking_notification_schema', '4.4.3r1', false );
        }
    }
}

function yatoon_booking_performance_indexes() {

    if ( get_option( 'yatoon_booking_index_schema', '' ) === '4.6.1' ) {
        return;
    }
    global $wpdb;
    $table = $wpdb->prefix . 'yatoon_bookings';
    $indexes = array(
        'staff_schedule'    => '(staff_id, start_time, status)',
        'location_schedule' => '(location_id, start_time, status)',
        'status_start'      => '(status, start_time)',
        'reminder_due'      => '(reminder_sent, start_time, status)',
        'customer_email'    => '(customer_email)',
        'customer_phone'    => '(customer_phone)',
    );
    $complete = true;
    foreach ( $indexes as $name => $columns ) {
        $exists = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", $name ) );
        if ( ! $exists && false === $wpdb->query( "ALTER TABLE {$table} ADD INDEX {$name} {$columns}" ) ) {
            $complete = false;
        }
    }
    if ( $complete ) {
        update_option( 'yatoon_booking_index_schema', '4.6.1', false );
    }
}

/**
 * Database-level backstop against double booking (4.4.3).
 *
 * WHY
 * ---
 * 4.4.3 fixes the application-level race (see acquire_booking_day_locks()),
 * but an application lock can still be defeated: MySQL's GET_LOCK is scoped
 * to one server, so on a multi-writer cluster (Galera, Percona XtraDB,
 * Aurora multi-master) two nodes can hold "the same" lock simultaneously.
 * A UNIQUE index is enforced by the storage engine, so it still holds there.
 *
 * WHAT THIS DOES **NOT** COVER
 * ----------------------------
 * Read this before treating the index as a general double-booking guarantee.
 *
 * The guard value is (staff_id, start_time). A UNIQUE index can only reject
 * rows that are IDENTICAL on those columns -- SQL unique constraints cannot
 * express arbitrary interval overlap. So:
 *
 *   blocked : Anna 10:00-11:00  vs  Anna 10:00-10:30   (same start)
 *   NOT blocked: Anna 10:00-11:00  vs  Anna 10:30-11:00 (overlapping start)
 *
 * Overlapping-but-different-start pairs are stopped by the application day
 * lock, which is reliable on a single writer. On a multi-writer cluster that
 * lock is node-local, so such a pair CAN still slip through. Multi-writer
 * installations must route booking writes to a single writer.
 *
 * Closing that gap properly needs an occupancy table -- one row per staff
 * member per time granule, with UNIQUE (staff_id, granule) -- so that every
 * granule a booking spans collides. That is a larger change than 4.4.3 takes
 * on; this index plus the day lock covers every single-writer deployment.
 *
 * HOW
 * ---
 * slot_guard is a STORED GENERATED column, not a normal one. That matters:
 * MySQL recomputes it on every INSERT and UPDATE, so cancelling, moving,
 * reassigning or rescheduling a booking updates the guard automatically.
 * A hand-maintained column would have required patching every write path in
 * a 400KB controller, and the failure mode of missing one is worse than the
 * bug -- a stale guard would block a customer from legitimately rebooking a
 * slot that is actually free.
 *
 * The expression yields NULL (and MySQL allows unlimited NULLs in a UNIQUE
 * index) when the row does not occupy a slot:
 *
 *   - cancelled / no-show      -> slot is free again, must be rebookable
 *   - no staff assigned        -> nothing to double-book
 *   - owned by Square          -> Square is the source of truth for its own
 *                                 calendar and its segment model can produce
 *                                 rows we should not police
 *
 * SAFETY ON LIVE DATA
 * -------------------
 * A site that already suffered the 4.4.2 race will contain rows that violate
 * the new constraint. We never delete or mutate a customer's booking to make
 * a schema change fit. Instead we detect the collisions first, and if any
 * exist we record them for the admin and skip the index -- the day lock still
 * protects new bookings. The migration retries on later requests, so the
 * index appears automatically once the admin resolves the duplicates.
 *
 * Requires MySQL 5.7+ / MariaDB 10.2+ for generated columns. On anything
 * older every step fails harmlessly and the plugin runs on the day lock alone.
 */
function yatoon_booking_slot_guard_migration() {
    if ( get_option( 'yatoon_booking_slot_guard_schema', '' ) === '4.4.3' ) {
        return;
    }

    global $wpdb;
    $table = $wpdb->prefix . 'yatoon_bookings';

    // Table may not exist yet on a brand-new install; the activator creates it.
    if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) {
        return;
    }

    $free_statuses = yatoon_non_occupying_statuses();
    $placeholders  = implode( ',', array_fill( 0, count( $free_statuses ), '%s' ) );

    // -- 1. Refuse to proceed while colliding rows exist ---------------------
    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- placeholders are generated; values go through prepare().
    $collisions = (int) $wpdb->get_var( $wpdb->prepare(
        "SELECT COUNT(*) FROM (
             SELECT staff_id, start_time
               FROM {$table}
              WHERE staff_id > 0
                AND ( square_booking_id IS NULL OR square_booking_id = '' )
                AND status NOT IN ( {$placeholders} )
              GROUP BY staff_id, start_time
             HAVING COUNT(*) > 1
         ) AS dupes",
        $free_statuses
    ) );

    if ( $collisions > 0 ) {
        update_option( 'yatoon_booking_slot_guard_conflicts', $collisions, false );
        if ( function_exists( 'yatoon_safe_log' ) ) {
            yatoon_safe_log( sprintf(
                'Yatoon 4.4.3: %d staff/time slot(s) already hold more than one active booking, so the double-booking database constraint was not installed. Resolve them under Bookings and the constraint will be added automatically.',
                $collisions
            ) );
        }
        return; // retried on a later request
    }
    delete_option( 'yatoon_booking_slot_guard_conflicts' );

    // -- 2. Generated column -------------------------------------------------
    $has_column = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", 'slot_guard' ) );
    if ( ! $has_column ) {
        $quoted = array();
        foreach ( $free_statuses as $status ) {
            $quoted[] = "'" . esc_sql( $status ) . "'";
        }
        $status_list = implode( ',', $quoted );

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix; status list is esc_sql'd above.
        $added = $wpdb->query(
            "ALTER TABLE {$table}
             ADD COLUMN slot_guard varchar(64)
             GENERATED ALWAYS AS (
                 CASE
                     WHEN staff_id > 0
                      AND ( square_booking_id IS NULL OR square_booking_id = '' )
                      AND status NOT IN ( {$status_list} )
                     THEN CONCAT( staff_id, '|', start_time )
                     ELSE NULL
                 END
             ) STORED"
        );
        if ( false === $added ) {
            if ( function_exists( 'yatoon_safe_log' ) ) {
                yatoon_safe_log( 'Yatoon 4.4.3: could not add the slot_guard generated column (MySQL 5.7+/MariaDB 10.2+ required): ' . $wpdb->last_error );
            }
            return;
        }
    }

    // -- 3. Unique index -----------------------------------------------------
    $has_index = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", 'slot_guard' ) );
    if ( ! $has_index ) {
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
        if ( false === $wpdb->query( "ALTER TABLE {$table} ADD UNIQUE INDEX slot_guard (slot_guard)" ) ) {
            if ( function_exists( 'yatoon_safe_log' ) ) {
                yatoon_safe_log( 'Yatoon 4.4.3: could not create the slot_guard unique index: ' . $wpdb->last_error );
            }
            return;
        }
    }

    update_option( 'yatoon_booking_slot_guard_schema', '4.4.3', false );
}

/**
 * Enforce one booking per external payment reference at the database layer.
 *
 * SELF-CONTAINED BY DESIGN
 * ------------------------
 * This migration creates its own column. It must not rely on the versioned
 * column map in yatoon_run_migrations(), because that whole block is gated on
 *
 *     yatoon_should_run_schema_migration( $installed, YATOON_DB_VERSION )
 *
 * which is FALSE as soon as the stored yatoon_schema_version equals the
 * current version. Any site that ran an earlier 4.4.3 build already stored
 * '4.4.3', so a column added to that map later in the same version number is
 * never created -- the gate is permanently closed and there is no error to
 * notice. Verified on a live install: deposit_intent_id was missing while
 * yatoon_schema_version read '4.4.3', and this function then re-ran a doomed
 * ALTER on every single page load.
 *
 * The plugin has been bitten by this before (see the note about the version
 * being pinned at 3.3.20 in the main plugin file). The lesson is that a
 * version-gated migration list must never be edited in place under a version
 * number that has already shipped -- new schema needs either a version bump
 * or, as here, its own idempotent migration guarded by its own option.
 */
function yatoon_payment_reference_unique_migration() {

    global $wpdb;
    if ( get_option( 'yatoon_payment_reference_schema', '' ) === '4.4.3' ) {
        return;
    }

    $table = $wpdb->prefix . 'yatoon_bookings';
    if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) {
        return;
    }

    // 1. Column. NULL-able on purpose: bookings without a payment reference
    //    must all be allowed, and MySQL exempts NULL from UNIQUE.
    if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", 'deposit_intent_id' ) ) ) {
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix.
        if ( false === $wpdb->query( "ALTER TABLE {$table} ADD COLUMN deposit_intent_id varchar(255) DEFAULT NULL" ) ) {
            if ( function_exists( 'yatoon_safe_log' ) ) {
                yatoon_safe_log( 'Yatoon 4.4.3: could not add deposit_intent_id column: ' . $wpdb->last_error );
            }
            return;
        }
    }

    // 2. Normalise empty strings to NULL. The duplicate probe below skips ''
    //    but MySQL treats it as a real value, so two blank rows would make the
    //    unique index impossible to create while reporting no conflict.
    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    $wpdb->query( "UPDATE {$table} SET deposit_intent_id = NULL WHERE deposit_intent_id = ''" );

    // 3. Refuse to proceed while genuine duplicates exist; never mutate a
    //    customer's payment reference to make a schema change fit.
    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    $duplicate = $wpdb->get_var(
        "SELECT deposit_intent_id FROM {$table}
          WHERE deposit_intent_id IS NOT NULL
          GROUP BY deposit_intent_id HAVING COUNT(*) > 1 LIMIT 1"
    );
    if ( $duplicate ) {
        update_option( 'yatoon_payment_reference_conflict', (string) $duplicate, false );
        return;
    }
    delete_option( 'yatoon_payment_reference_conflict' );

    // 4. Unique index.
    $exists = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", 'deposit_intent_id' ) );
    if ( ! $exists ) {
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        if ( false === $wpdb->query( "ALTER TABLE {$table} ADD UNIQUE INDEX deposit_intent_id (deposit_intent_id)" ) ) {
            if ( function_exists( 'yatoon_safe_log' ) ) {
                yatoon_safe_log( 'Yatoon 4.4.3: could not create the deposit_intent_id unique index: ' . $wpdb->last_error );
            }
            return;
        }
    }

    update_option( 'yatoon_payment_reference_schema', '4.4.3', false );
}

/**
 * Tax and invoice schema (4.4.3).
 *
 * The tax breakdown is stored per booking rather than recomputed on demand,
 * because a shop that changes its VAT rate must not retroactively alter what
 * past invoices say it charged.
 *
 * All columns are nullable with sane defaults, so existing rows are untouched
 * and a booking created before the migration finishes still saves.
 */
function yatoon_tax_invoice_migration() {
    if ( get_option( 'yatoon_tax_schema', '' ) === '4.4.3' ) {
        return;
    }

    global $wpdb;
    $bookings = $wpdb->prefix . 'yatoon_bookings';
    $services = $wpdb->prefix . 'yatoon_services';

    if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $bookings ) ) ) {
        return;
    }

    $columns = array(
        $bookings => array(
            'tax_rate'       => "ALTER TABLE {$bookings} ADD COLUMN tax_rate decimal(6,3) DEFAULT NULL",
            'tax_amount'     => "ALTER TABLE {$bookings} ADD COLUMN tax_amount decimal(12,2) DEFAULT NULL",
            'net_amount'     => "ALTER TABLE {$bookings} ADD COLUMN net_amount decimal(12,2) DEFAULT NULL",
            'tax_inclusive'  => "ALTER TABLE {$bookings} ADD COLUMN tax_inclusive tinyint(1) DEFAULT 0",
            'invoice_number' => "ALTER TABLE {$bookings} ADD COLUMN invoice_number varchar(40) DEFAULT NULL",
            'invoice_issued' => "ALTER TABLE {$bookings} ADD COLUMN invoice_issued_at datetime DEFAULT NULL",
        ),
        // NULL here means "use the shop default rate". A stored 0 is different:
        // it means the service is deliberately zero-rated or exempt.
        $services => array(
            'tax_rate' => "ALTER TABLE {$services} ADD COLUMN tax_rate decimal(6,3) DEFAULT NULL",
        ),
    );

    $complete = true;
    foreach ( $columns as $table => $definitions ) {
        if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) {
            continue;
        }
        foreach ( $definitions as $column => $sql ) {
            // invoice_issued_at is keyed by a shorter name above for readability.
            $probe  = ( 'invoice_issued' === $column ) ? 'invoice_issued_at' : $column;
            $exists = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", $probe ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            if ( $exists ) {
                continue;
            }
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- SQL is from the fixed map above.
            if ( false === $wpdb->query( $sql ) ) {
                $complete = false;
            }
        }
    }

    // Invoice numbers must be unique for audit purposes, and the lookup by
    // number needs to be fast for the customer-facing download link.
    if ( $complete && ! $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM {$bookings} WHERE Key_name = %s", 'invoice_number' ) ) ) {
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        if ( false === $wpdb->query( "ALTER TABLE {$bookings} ADD UNIQUE INDEX invoice_number (invoice_number)" ) ) {
            $complete = false;
        }
    }

    if ( $complete ) {
        update_option( 'yatoon_tax_schema', '4.4.3', false );
    }
}

/** Self-contained repair for builds already marked as schema 4.4.3. */
function yatoon_booking_timezone_migration() {

    if ( get_option( 'yatoon_booking_timezone_schema', '' ) === '4.4.3r1' ) {
        return;
}
    global $wpdb;
$table = $wpdb->prefix . 'yatoon_bookings';
if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) { return;

}
    if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", 'booking_timezone' ) ) ) {
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $wpdb->query( "ALTER TABLE {$table} ADD COLUMN booking_timezone varchar(100) DEFAULT NULL AFTER client_timezone" );
}
    if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", 'booking_timezone' ) ) ) {
        update_option( 'yatoon_booking_timezone_schema', '4.4.3r1', false );
}
}

/**
 * Repair every booking column required by current runtime paths.
 *
 * This is intentionally independent from yatoon_schema_version because a site
 * may already have stored 4.5.0 while an earlier release candidate omitted one
 * or more ALTER statements. One shared manifest drives activation, normal
 * migration, completion checks, and this repair.
 */
function yatoon_booking_required_columns_migration() {

    global $wpdb;
$schema_marker = '4.5.0-r3';
if ( $schema_marker === get_option( 'yatoon_booking_required_columns_schema', '' ) ) { return;

}
    if ( ! function_exists( 'yatoon_booking_required_column_definitions' ) ) { return;

}

    $table = $wpdb->prefix . 'yatoon_bookings';
if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) { return;

}
    $existing = array_map( 'sanitize_key', (array) $wpdb->get_col( "SHOW COLUMNS FROM `{$table}`", 0 ) );
foreach ( yatoon_booking_required_column_definitions() as $column => $definition ) {
        if ( in_array( $column, $existing, true ) ) { continue;

}
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- fixed manifest; table derives from wpdb prefix.
        if ( false === $wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}" ) ) {
            if ( function_exists( 'yatoon_safe_log' ) ) { yatoon_safe_log( 'Yatoon 4.5.0 booking schema repair failed for ' . $column . ': ' . $wpdb->last_error );

}
            continue;
}
        $existing[] = $column;
}

    $verified = array_map( 'sanitize_key', (array) $wpdb->get_col( "SHOW COLUMNS FROM `{$table}`", 0 ) );
$missing = array_diff( array_keys( yatoon_booking_required_column_definitions() ), $verified );
if ( ! $missing ) {
        update_option( 'yatoon_booking_required_columns_schema', $schema_marker, false );
} elseif ( function_exists( 'yatoon_safe_log' ) ) {
        yatoon_safe_log( 'Yatoon 4.5.0 booking schema remains incomplete: ' . implode( ', ', $missing ) );
}
}

/**
 * Add an explicit UTC creation/hold clock without relying on MySQL's session timezone.
 * Self-contained because 4.5.0 release candidates may already have stored the 4.5.0 schema marker.
 */
function yatoon_booking_created_gmt_migration() {

    global $wpdb;
if ( '4.5.0-r2' === get_option( 'yatoon_booking_created_gmt_schema', '' ) ) { return;

}
    $table = $wpdb->prefix . 'yatoon_bookings';
if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) { return;

}
    if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", 'created_at_gmt' ) ) ) {
        if ( false === $wpdb->query( "ALTER TABLE {$table} ADD COLUMN created_at_gmt datetime DEFAULT NULL AFTER created_at" ) ) { return;

}
    }
    if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", 'wc_hold_due' ) ) ) {
        if ( false === $wpdb->query( "ALTER TABLE {$table} ADD INDEX wc_hold_due (payment_status, status, created_at_gmt)" ) ) { return;

}
    }
    update_option( 'yatoon_booking_created_gmt_schema', '4.5.0-r2', false );
}

function yatoon_waitlist_table_migrations() {

    global $wpdb;
    $columns = array(
        'notified_at'      => "ALTER TABLE {$wpdb->prefix}yatoon_waitlist ADD COLUMN notified_at datetime DEFAULT NULL AFTER notified",
        'matched_date'     => "ALTER TABLE {$wpdb->prefix}yatoon_waitlist ADD COLUMN matched_date date DEFAULT NULL AFTER notified_at",
        'matched_time'     => "ALTER TABLE {$wpdb->prefix}yatoon_waitlist ADD COLUMN matched_time varchar(50) DEFAULT NULL AFTER matched_date",
        'matched_start_at' => "ALTER TABLE {$wpdb->prefix}yatoon_waitlist ADD COLUMN matched_start_at varchar(100) DEFAULT NULL AFTER matched_time",
        'claim_token'      => "ALTER TABLE {$wpdb->prefix}yatoon_waitlist ADD COLUMN claim_token varchar(64) DEFAULT NULL AFTER matched_start_at",
        'claim_expires_at' => "ALTER TABLE {$wpdb->prefix}yatoon_waitlist ADD COLUMN claim_expires_at datetime DEFAULT NULL AFTER claim_token",
        'claim_status'     => "ALTER TABLE {$wpdb->prefix}yatoon_waitlist ADD COLUMN claim_status varchar(20) DEFAULT NULL AFTER claim_expires_at",
    );
    foreach ($columns as $column => $sql) {
        $exists = $wpdb->get_col("SHOW COLUMNS FROM {$wpdb->prefix}yatoon_waitlist LIKE '{$column}'");
        if (empty($exists)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->query($sql);
        }
    }
    $table = $wpdb->prefix . 'yatoon_waitlist';
    if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM `{$table}` WHERE Key_name=%s", 'claim_token' ) ) ) {
        $wpdb->query( "ALTER TABLE `{$table}` ADD UNIQUE KEY claim_token (claim_token)" );
    }
    if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM `{$table}` WHERE Key_name=%s", 'claim_expiry' ) ) ) {
        $wpdb->query( "ALTER TABLE `{$table}` ADD KEY claim_expiry (claim_status, claim_expires_at)" );
    }
    add_option('yatoon_waitlist_auto_notify', '1');
    add_option('yatoon_waitlist_claim_minutes', '30');
}

// -- Reviews table ------------------------------------------------------------
function yatoon_create_reviews_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'yatoon_reviews';
    $charset = $wpdb->get_charset_collate();
    $sql = "CREATE TABLE $table (
        id              bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        booking_id      bigint(20) unsigned NOT NULL,
        customer_name   varchar(255) NOT NULL DEFAULT '',
        customer_email  varchar(255) NOT NULL DEFAULT '',
        service_name    varchar(255) NOT NULL DEFAULT '',
        staff_name      varchar(255) NOT NULL DEFAULT '',
        rating          tinyint(1) NOT NULL DEFAULT 5,
        review_text     text DEFAULT NULL,
        token           varchar(64) NOT NULL DEFAULT '',
        status          varchar(20) NOT NULL DEFAULT 'pending',
        is_featured     tinyint(1) NOT NULL DEFAULT 0,
        submitted_at    datetime DEFAULT NULL,
        created_at      datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY token (token),
        KEY booking_id (booking_id),
        KEY status (status),
        KEY rating (rating)
    ) $charset;";
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}

// -- Recurring appointments table ---------------------------------------------
function yatoon_create_recurring_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'yatoon_recurring';
    $charset = $wpdb->get_charset_collate();
    $sql = "CREATE TABLE $table (
        id              bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        series_id       varchar(64) NOT NULL DEFAULT '',
        booking_id      bigint(20) unsigned NOT NULL,
        customer_name   varchar(255) NOT NULL DEFAULT '',
        customer_email  varchar(255) NOT NULL DEFAULT '',
        customer_phone  varchar(50) NOT NULL DEFAULT '',
        service_id      bigint(20) unsigned NOT NULL DEFAULT 0,
        staff_id        bigint(20) unsigned NOT NULL DEFAULT 0,
        frequency_weeks tinyint(1) NOT NULL DEFAULT 2,
        total_count     tinyint(3) NOT NULL DEFAULT 6,
        occurrence_num  tinyint(3) NOT NULL DEFAULT 1,
        status          varchar(20) NOT NULL DEFAULT 'active',
        cancelled_at    datetime DEFAULT NULL,
        created_at      datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY series_id (series_id),
        KEY booking_id (booking_id),
        KEY customer_email (customer_email),
        KEY status (status)
    ) $charset;";
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}

// -- Service images table ------------------------------------------------------
function yatoon_create_service_images_table() {
    global $wpdb;
    $table   = $wpdb->prefix . 'yatoon_service_images';
    $charset = $wpdb->get_charset_collate();
    $sql = "CREATE TABLE $table (
        id          bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        service_id  bigint(20) unsigned NOT NULL,
        image_url   varchar(1000) NOT NULL DEFAULT '',
        caption     varchar(255) NOT NULL DEFAULT '',
        sort_order  int(11) NOT NULL DEFAULT 0,
        created_at  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY service_id (service_id),
        KEY sort_order (sort_order)
    ) $charset;";
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}

// -- Catalog settings defaults -------------------------------------------------
function yatoon_catalog_defaults() {
    add_option('yatoon_catalog_cols',          '3');
    add_option('yatoon_catalog_show_price',    '1');
    add_option('yatoon_catalog_show_duration', '1');
    add_option('yatoon_catalog_show_desc',     '1');
    add_option('yatoon_catalog_show_tabs',     '1');
    add_option('yatoon_catalog_book_label',    'Book Now');
    add_option('yatoon_catalog_popup',         '1');
    // Gallery defaults
    add_option('yatoon_gallery_mode',       'grid');
    add_option('yatoon_gallery_cols',       '5');
    add_option('yatoon_gallery_gap',        '6');
    add_option('yatoon_gallery_ratio',      '1/1');
    add_option('yatoon_gallery_lightbox',   '1');
    add_option('yatoon_gallery_height',     '460px');
    add_option('yatoon_gallery_autoplay',   '1');
    add_option('yatoon_gallery_interval',   '3500');
    add_option('yatoon_gallery_captions',   '0');
    add_option('yatoon_gallery_book_label', 'Book Now');
}

// -- Custom form fields table --------------------------------------------------
function yatoon_create_form_fields_table() {
    global $wpdb;
    $table   = $wpdb->prefix . 'yatoon_form_fields';
    $charset = $wpdb->get_charset_collate();
    $sql = "CREATE TABLE $table (
        id          bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        field_key   varchar(100) NOT NULL DEFAULT '',
        label       varchar(255) NOT NULL DEFAULT '',
        type        varchar(30)  NOT NULL DEFAULT 'text',
        placeholder varchar(255) NOT NULL DEFAULT '',
        options     text DEFAULT NULL,
        required    tinyint(1)   NOT NULL DEFAULT 0,
        is_active   tinyint(1)   NOT NULL DEFAULT 1,
        sort_order  int(11)      NOT NULL DEFAULT 0,
        created_at  datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY is_active (is_active),
        KEY sort_order (sort_order)
    ) $charset;";
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}

// -- Packages table ------------------------------------------------------------
function yatoon_create_packages_table() {
    global $wpdb;
    $table   = $wpdb->prefix . 'yatoon_packages';
    $charset = $wpdb->get_charset_collate();
    $sql = "CREATE TABLE $table (
        id            bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        name          varchar(255) NOT NULL DEFAULT '',
        description   text DEFAULT NULL,
        service_id    bigint(20) unsigned NOT NULL DEFAULT 0,
        sessions_total tinyint(3) NOT NULL DEFAULT 5,
        price         decimal(10,2) NOT NULL DEFAULT 0.00,
        expiry_days   int(11)      NOT NULL DEFAULT 180,
        is_active     tinyint(1)   NOT NULL DEFAULT 1,
        created_at    datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY service_id (service_id),
        KEY is_active (is_active)
    ) $charset;";
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}

// -- Package redemptions table -------------------------------------------------
function yatoon_create_package_redemptions_table() {
    global $wpdb;
    $table   = $wpdb->prefix . 'yatoon_package_redemptions';
    $charset = $wpdb->get_charset_collate();
    $sql = "CREATE TABLE $table (
        id              bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        package_id      bigint(20) unsigned NOT NULL,
        customer_email  varchar(255) NOT NULL DEFAULT '',
        customer_name   varchar(255) NOT NULL DEFAULT '',
        customer_phone  varchar(50)  NOT NULL DEFAULT '',
        sessions_used   tinyint(3)   NOT NULL DEFAULT 0,
        sessions_total  tinyint(3)   NOT NULL DEFAULT 5,
        purchased_at    datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        expires_at      datetime     DEFAULT NULL,
        status          varchar(20)  NOT NULL DEFAULT 'active',
        PRIMARY KEY (id),
        KEY package_id (package_id),
        KEY customer_email (customer_email),
        KEY status (status)
    ) $charset;";
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}

// -- Bookings table migrations: tip_amount + custom_field_values ---------------
function yatoon_bookings_batch_cd_migrations() {
    global $wpdb;
    $table = $wpdb->prefix . 'yatoon_bookings';
    $cols = array(
        'tip_amount'          => "ALTER TABLE $table ADD COLUMN tip_amount decimal(10,2) NOT NULL DEFAULT 0.00 AFTER total_price",
        'custom_field_values' => "ALTER TABLE $table ADD COLUMN custom_field_values longtext DEFAULT NULL AFTER notes",
        'package_redemption_id' => "ALTER TABLE $table ADD COLUMN package_redemption_id bigint(20) unsigned DEFAULT NULL AFTER custom_field_values",
    );
    foreach ($cols as $col => $sql) {
        $exists = $wpdb->get_var("SHOW COLUMNS FROM $table LIKE '$col'");
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        if (!$exists) { $wpdb->query($sql);
        }
    }
}

// Tip settings defaults
function yatoon_tip_defaults() {
    add_option('yatoon_tip_enabled', '1');
    add_option('yatoon_tip_presets', '15,18,20');
    add_option('yatoon_tip_label', 'Add a tip for your technician');
}

// -- Gift Cards tables ---------------------------------------------------------
function yatoon_create_gift_cards_tables() {
    global $wpdb;
    $charset = $wpdb->get_charset_collate();

    // Gift cards
    $table_gc = $wpdb->prefix . 'yatoon_gift_cards';
    $sql_gc = "CREATE TABLE $table_gc (
        id              bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        code            varchar(32)  NOT NULL DEFAULT '',
        initial_amount  decimal(10,2) NOT NULL DEFAULT 0.00,
        balance         decimal(10,2) NOT NULL DEFAULT 0.00,
        purchased_by    varchar(255) NOT NULL DEFAULT '',
        purchased_email varchar(255) NOT NULL DEFAULT '',
        recipient_name  varchar(255) NOT NULL DEFAULT '',
        recipient_email varchar(255) NOT NULL DEFAULT '',
        message         text DEFAULT NULL,
        status          varchar(20)  NOT NULL DEFAULT 'active',
        expires_at      date DEFAULT NULL,
        created_at      datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY code (code),
        KEY status (status),
        KEY purchased_email (purchased_email),
        KEY recipient_email (recipient_email)
    ) $charset;";

    // Transactions
    $table_tx = $wpdb->prefix . 'yatoon_gift_card_transactions';
    $sql_tx = "CREATE TABLE $table_tx (
        id              bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        gift_card_id    bigint(20) unsigned NOT NULL,
        booking_id      bigint(20) unsigned DEFAULT NULL,
        type            varchar(20)  NOT NULL DEFAULT 'redeem',
        amount          decimal(10,2) NOT NULL DEFAULT 0.00,
        balance_after   decimal(10,2) NOT NULL DEFAULT 0.00,
        note            varchar(255) NOT NULL DEFAULT '',
        created_at      datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY gift_card_id (gift_card_id),
        KEY booking_id (booking_id)
    ) $charset;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql_gc);
    dbDelta($sql_tx);
}

/* -------------------------------------------------------------------------
 * 4.5.0 -- Runtime DDL elimination and index hygiene.
 *
 * Until now three schema changes were applied lazily, at the moment a page
 * happened to need the column:
 *
 *   - admin/views/service-menu.php ran ALTER TABLE ... ADD is_featured while
 *     rendering the Service Menu screen.
 *   - YATOON_Loyalty::maybe_create_tables() added clients.loyalty_points on
 *     every plugins_loaded, re-running SHOW COLUMNS on each request.
 *   - YATOON_Staff_Portal::ensure_pin_lookup_column() added the PIN lookup
 *     column from inside the unauthenticated login handler.
 *
 * That pattern fails in three real deployments: two concurrent requests can
 * issue the same ALTER simultaneously; a slow ALTER on shared hosting blows
 * the request timeout and white-screens the admin; and on a read replica the
 * DDL simply errors out. Schema changes belong in one guarded migration pass,
 * which is what the functions below do. The call sites keep their read-only
 * existence checks so a site that has not yet migrated degrades gracefully
 * instead of fataling.
 * ---------------------------------------------------------------------- */

/**
 * Add services.is_featured outside of page render.
 *
 * @since 4.5.0
 * @return void
 */
function yatoon_services_featured_migration() {

    global $wpdb;
if ( '4.5.0' === get_option( 'yatoon_services_featured_schema', '' ) ) { return;

}
    $table = $wpdb->prefix . 'yatoon_services';
if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) { return;

}
    if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", 'is_featured' ) ) ) {
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table derives from wpdb prefix.
        if ( false === $wpdb->query( "ALTER TABLE {$table} ADD COLUMN is_featured tinyint(1) NOT NULL DEFAULT 0" ) ) {
            if ( function_exists( 'yatoon_safe_log' ) ) { yatoon_safe_log( 'Yatoon 4.5.0: services.is_featured migration failed: ' . $wpdb->last_error );

}
            return;
}
    }
    update_option( 'yatoon_services_featured_schema', '4.5.0', false );
}

/**
 * Add clients.loyalty_points outside of the Loyalty bootstrap.
 *
 * @since 4.5.0
 * @return void
 */
function yatoon_clients_loyalty_points_migration() {

    global $wpdb;
if ( '4.5.0' === get_option( 'yatoon_clients_loyalty_schema', '' ) ) { return;

}
    $table = $wpdb->prefix . 'yatoon_clients';
if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) { return;

}
    if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", 'loyalty_points' ) ) ) {
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table derives from wpdb prefix.
        if ( false === $wpdb->query( "ALTER TABLE {$table} ADD COLUMN loyalty_points int(11) NOT NULL DEFAULT 0" ) ) {
            if ( function_exists( 'yatoon_safe_log' ) ) { yatoon_safe_log( 'Yatoon 4.5.0: clients.loyalty_points migration failed: ' . $wpdb->last_error );

}
            return;
}
    }
    update_option( 'yatoon_clients_loyalty_schema', '4.5.0', false );
}

/**
 * Add staff.portal_pin_lookup + its index outside of the login handler.
 *
 * @since 4.5.0
 * @return void
 */
function yatoon_staff_pin_lookup_migration() {

    global $wpdb;
if ( '4.5.0' === get_option( 'yatoon_staff_pin_lookup_schema', '' ) ) { return;

}
    $table = $wpdb->prefix . 'yatoon_staff';
if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) { return;

}
    if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", 'portal_pin' ) ) ) {
        if ( false === $wpdb->query( "ALTER TABLE {$table} ADD COLUMN `portal_pin` varchar(255) DEFAULT '' AFTER `sort_order`" ) ) {
            if ( function_exists( 'yatoon_safe_log' ) ) { yatoon_safe_log( 'Staff portal PIN migration failed: ' . $wpdb->last_error );

}
            return;
        }
    }
    if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", 'portal_pin_lookup' ) ) ) {
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table derives from wpdb prefix.
        if ( false === $wpdb->query( "ALTER TABLE {$table} ADD COLUMN `portal_pin_lookup` varchar(64) DEFAULT '' AFTER `portal_pin`" ) ) {
            if ( function_exists( 'yatoon_safe_log' ) ) { yatoon_safe_log( 'Yatoon 4.5.0: staff.portal_pin_lookup migration failed: ' . $wpdb->last_error );

}
            return;
        }
    }
    if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", 'portal_pin_lookup' ) ) ) {
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table derives from wpdb prefix.
        $wpdb->query( "ALTER TABLE {$table} ADD INDEX portal_pin_lookup (portal_pin_lookup)" );
}
    update_option( 'yatoon_staff_pin_lookup_schema', '4.5.0', false );
}

/**
 * Drop redundant indexes from wp_yatoon_bookings.
 *
 * The table accumulated 13 secondary indexes as features were added, four of
 * which are strict prefixes of a wider composite index that was added later:
 *
 *   staff_id(staff_id)              is a prefix of staff_schedule(staff_id, start_time, status)
 *   location_id(location_id)        is a prefix of location_schedule(location_id, start_time, status)
 *   reminder_sent(reminder_sent)    is a prefix of reminder_due(reminder_sent, start_time, status)
 *   start_time(start_time)          is covered by status_start(status, start_time) for the
 *                                   planner's purposes on every query the plugin actually issues
 *
 * MySQL can satisfy a leftmost-prefix lookup from the wider index, so the
 * narrow duplicates buy no read performance while every INSERT and UPDATE on
 * the busiest table in the plugin has to maintain them. On a salon doing a few
 * hundred bookings a day this is not dramatic, but it is pure waste, and the
 * write amplification shows up exactly during the booking-creation path that
 * already holds a day lock.
 *
 * start_time is dropped last and separately: if a site has custom reporting
 * SQL that filters on start_time without a status predicate, the composite
 * status_start cannot serve it. Sites can opt out of that one specifically.
 *
 * @since 4.5.0
 * @return void
 */
function yatoon_bookings_redundant_index_cleanup() {

    global $wpdb;
    // 4.6.2: the marker moves from '4.5.0' to '4.6.2' so this runs exactly once
    // more on sites that were cleaned by 4.5.0. Those sites still carry the
    // redundant indexes whenever the bookings table was re-created afterwards
    // (restore, staging clone, table repair), because the CREATE TABLE kept
    // declaring them until this release.
if ( '4.6.2' === get_option( 'yatoon_bookings_index_hygiene', '' ) ) { return;

}
    $table = $wpdb->prefix . 'yatoon_bookings';
if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) { return;

}

    /**
     * 4.6.2: read the leftmost column of an index, or '' when absent.
     *
     * The old code only asked "does the covering index exist?". That is not
     * the same question as "does it actually cover this one": an index only
     * supersedes a single-column index when that column is its FIRST column.
     * status_start is (status, start_time), so it never covered
     * KEY start_time (start_time) - dropping start_time on its authority made
     * unfiltered date-range scans slower, which is the opposite of the point.
     *
     * @param string $table    Table name.
     * @param string $key_name Index name.
     * @return string Leftmost column name, or '' when the index does not exist.
     */
    $leftmost_column = function ( $table, $key_name ) use ( $wpdb ) {
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is built from $wpdb->prefix.
        $row = $wpdb->get_row( $wpdb->prepare(
            "SHOW INDEX FROM {$table} WHERE Key_name = %s AND Seq_in_index = 1",
            $key_name
        ) );
        return ( $row && isset( $row->Column_name ) ) ? (string) $row->Column_name : '';
    };

    $redundant = array(
        'staff_id'      => 'staff_schedule',
        'location_id'   => 'location_schedule',
        'reminder_sent' => 'reminder_due',
    );
    // Kept for backwards compatibility with sites that set this filter, but the
    // prefix check below now refuses the drop anyway: status_start starts with
    // `status`, not `start_time`.
if ( apply_filters( 'yatoon_drop_redundant_start_time_index', true ) ) {
        $redundant['start_time'] = 'status_start';
}

    foreach ( $redundant as $drop => $covered_by ) {
        // The index to drop must exist and must be the single-column index we
        // think it is.
        $drop_leftmost = $leftmost_column( $table, $drop );
        if ( '' === $drop_leftmost || $drop_leftmost !== $drop ) { continue;

}
        // 4.6.2: and the covering index must actually START with that column.
        if ( $leftmost_column( $table, $covered_by ) !== $drop ) { continue;

}
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- key names from the fixed manifest above.
        if ( false === $wpdb->query( "ALTER TABLE {$table} DROP INDEX `{$drop}`" ) ) {
            if ( function_exists( 'yatoon_safe_log' ) ) { yatoon_safe_log( 'Yatoon 4.6.2: could not drop redundant index ' . $drop . ': ' . $wpdb->last_error );

}
        }
    }

    // 4.6.2: repair the one index 4.5.0 dropped wrongly. Nothing else in the
    // schema starts with start_time, so without this a plain
    // "WHERE start_time BETWEEN ..." (calendar and report queries with no
    // status filter) table-scans. Guarded so it is a no-op on healthy sites
    // and on sites that deliberately turned the drop off.
    if ( '' === $leftmost_column( $table, 'start_time' )
        && $leftmost_column( $table, 'status_start' ) !== 'start_time' ) {
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is built from $wpdb->prefix.
        if ( false === $wpdb->query( "ALTER TABLE {$table} ADD INDEX start_time (start_time)" ) ) {
            if ( function_exists( 'yatoon_safe_log' ) ) { yatoon_safe_log( 'Yatoon 4.6.2: could not restore start_time index: ' . $wpdb->last_error );

}
        }
    }

    update_option( 'yatoon_bookings_index_hygiene', '4.6.2', false );
}

/**
 * Run the complete schema manifest once per database version.
 *
 * Normal requests perform one autoloaded option comparison and no schema
 * probes. The non-autoloaded lock prevents concurrent update requests from
 * racing DDL. A failed batch never writes the completion marker and can be
 * retried after the lock expires.
 *
 * @since 4.5.1
 * @param bool $force Run even when the completion marker is current.
 * @return bool True when the manifest is complete.
 */
/**
 * Create the customer change-request queue during the versioned schema batch.
 *
 * @since 4.5.1
 * @return void
 */
function yatoon_create_change_requests_table() {

    global $wpdb;
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
$table = $wpdb->prefix . 'yatoon_change_requests';
dbDelta(
        "CREATE TABLE {$table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            booking_id bigint(20) unsigned NOT NULL DEFAULT 0,
            group_booking_id varchar(64) DEFAULT '',
            square_booking_id varchar(255) DEFAULT '',
            customer_name varchar(255) DEFAULT '',
            customer_email varchar(255) DEFAULT '',
            customer_phone varchar(64) DEFAULT '',
            request_type varchar(64) DEFAULT 'change_services',
            status varchar(32) DEFAULT 'pending',
            old_data longtext,
            requested_data longtext,
            error_message text,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY booking_id (booking_id),
            KEY square_booking_id (square_booking_id),
            KEY status (status)
        ) {$wpdb->get_charset_collate()};"
    );
}

/** Create provider-neutral calendar event mappings for two-way sync. */
function yatoon_create_calendar_event_map_table() {
    global $wpdb;
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    $table = $wpdb->prefix . 'yatoon_calendar_event_map';
    dbDelta(
        "CREATE TABLE {$table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            booking_id bigint(20) unsigned NOT NULL,
            provider varchar(32) NOT NULL,
            provider_event_id varchar(255) NOT NULL,
            staff_id bigint(20) unsigned NOT NULL DEFAULT 0,
            calendar_id varchar(255) DEFAULT '',
            change_key varchar(255) DEFAULT '',
            last_synced_at datetime DEFAULT NULL,
            last_error text,
            PRIMARY KEY (id),
            UNIQUE KEY booking_provider (booking_id,provider),
            KEY provider_event (provider,provider_event_id),
            KEY staff_provider (staff_id,provider)
        ) {$wpdb->get_charset_collate()};"
    );
}

/**
 * Add the lookup used to make customer booking submissions duplicate-safe.
 * The column itself is also part of the shared required-column manifest; this
 * separate index migration remains safe on sites whose schema marker was
 * written by an earlier build.
 */
function yatoon_booking_attempt_reference_migration() {
    global $wpdb;
    $table = $wpdb->prefix . 'yatoon_bookings';
    if ( $table !== $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) {
        return;
    }
    if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", 'client_reference' ) ) ) {
        $wpdb->query( "ALTER TABLE {$table} ADD COLUMN client_reference varchar(64) DEFAULT '' AFTER square_booking_id" );
    }
    if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", 'client_reference' ) )
        && ! $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", 'client_reference' ) ) ) {
        $wpdb->query( "ALTER TABLE {$table} ADD INDEX client_reference (client_reference)" );
    }
}


function yatoon_run_versioned_schema_migrations( $force = false ) {

    global $wpdb;
$marker_option = 'yatoon_versioned_schema_manifest';
// 4.6.2: bumped 6 -> 7 so already-migrated sites re-run the batch once for the
// new yatoon_cleanup_deprecated_options step and the re-armed index hygiene.
$manifest_revision = '7';
if ( ! $force && YATOON_DB_VERSION === (string) get_option( $marker_option, '' ) && $manifest_revision === (string) get_option( 'yatoon_schema_manifest_revision', '' ) ) {
        return true;
}

    // 4.6.2: stop the endless retry loop on databases that can never finish.
    //
    // What was wrong: when $complete came back false the marker was not written
    // and nothing else was recorded, so the plugins_loaded hook re-ran all 25
    // migration steps plus ~40 SHOW COLUMNS probes every time the 10-minute
    // lock expired - forever. On a site whose MySQL user has no ALTER
    // privilege that is a permanent background load with no way for the owner
    // to find out, because the only signal was a debug-log line.
    //
    // Retryable failures (a lock timeout, a transient deadlock, a step that
    // succeeds on the next pass) still get several attempts. A hard
    // environmental failure burns through the budget and then stops, leaving an
    // admin notice with a manual retry link.
    $failure_option         = 'yatoon_schema_migration_failures';
    $failure_version_option = 'yatoon_schema_migration_failed_version';
    $max_failures           = 5;

    $failures = (int) get_option( $failure_option, 0 );
    if ( YATOON_DB_VERSION !== (string) get_option( $failure_version_option, '' ) ) {
        // A new schema target gets a fresh budget - the previous failures were
        // about a different manifest.
        $failures = 0;
    }
    if ( ! $force && $failures >= $max_failures ) {
        return false;
    }

    $lock_option = 'yatoon_schema_migration_lock';
$now         = time();
$lock_time   = (int) get_option( $lock_option, 0 );
if ( $lock_time && ( $now - $lock_time ) < 10 * MINUTE_IN_SECONDS ) {
        return false;
}
    if ( $lock_time ) {
        delete_option( $lock_option );
}
    if ( ! add_option( $lock_option, (string) $now, '', false ) ) {
        return false;
}

    $steps = array(
        'yatoon_create_waitlist_table',
        'yatoon_create_customer_favorites_table',
        'yatoon_booking_notification_schema_repair',
        'yatoon_booking_performance_indexes',
        'yatoon_booking_slot_guard_migration',
        'yatoon_payment_reference_unique_migration',
        'yatoon_tax_invoice_migration',
        'yatoon_booking_required_columns_migration',
        'yatoon_booking_created_gmt_migration',
        'yatoon_booking_timezone_migration',
        'yatoon_waitlist_table_migrations',
        'yatoon_create_reviews_table',
        'yatoon_create_recurring_table',
        'yatoon_create_service_images_table',
        'yatoon_catalog_defaults',
        'yatoon_create_form_fields_table',
        'yatoon_create_packages_table',
        'yatoon_create_package_redemptions_table',
        'yatoon_bookings_batch_cd_migrations',
        'yatoon_tip_defaults',
        'yatoon_create_gift_cards_tables',
        'yatoon_create_change_requests_table',
        'yatoon_create_calendar_event_map_table',
        'yatoon_booking_attempt_reference_migration',
        'yatoon_services_featured_migration',
        'yatoon_clients_loyalty_points_migration',
        'yatoon_staff_pin_lookup_migration',
        'yatoon_staff_portal_permissions_migration',
        'yatoon_bookings_redundant_index_cleanup',
        // 4.6.2: one-time removal of options whose settings UI is gone.
        'yatoon_cleanup_deprecated_options',
    );
foreach ( $steps as $step ) {
        call_user_func( $step );
}
    if ( class_exists( 'YATOON_Locations' ) ) {
        YATOON_Locations::maybe_upgrade_table();
}

    $required_tables = array(
        'yatoon_waitlist',
        'yatoon_customer_favorites',
        'yatoon_reviews',
        'yatoon_recurring',
        'yatoon_service_images',
        'yatoon_form_fields',
        'yatoon_packages',
        'yatoon_package_redemptions',
        'yatoon_gift_cards',
        'yatoon_gift_card_transactions',
        'yatoon_change_requests',
        'yatoon_calendar_event_map',
    );
$complete = true;
foreach ( $required_tables as $table ) {
        $full_table = $wpdb->prefix . $table;
if ( $full_table !== $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $full_table ) ) ) {
            $complete = false;
break;
}
    }

    $required_columns = array(
        'yatoon_bookings' => array_merge(
            array_keys( yatoon_booking_required_column_definitions() ),
            array( 'reminder_sent', 'review_requested', 'deposit_intent_id', 'tax_rate', 'tax_amount', 'net_amount', 'tax_inclusive', 'invoice_number', 'invoice_issued_at', 'booking_timezone', 'created_at_gmt', 'tip_amount', 'custom_field_values', 'package_redemption_id' )
        ),
        'yatoon_waitlist' => array( 'notified_at', 'matched_date', 'matched_time', 'matched_start_at', 'claim_token', 'claim_expires_at', 'claim_status' ),
        'yatoon_services' => array( 'is_featured', 'tax_rate' ),
        'yatoon_clients'  => array( 'loyalty_points' ),
        'yatoon_staff'    => array( 'portal_pin', 'portal_pin_lookup', 'portal_permissions', 'portal_role' ),
        'yatoon_locations'=> array( 'slug', 'email', 'description', 'color_accent', 'is_default' ),
    );
foreach ( $required_columns as $table => $columns ) {
        $full_table = $wpdb->prefix . $table;
foreach ( $columns as $column ) {
            if ( ! $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$full_table}` LIKE %s", $column ) ) ) {
                $complete = false;
break 2;
}
        }
    }

    // Duplicate-safe retries depend on this lookup remaining indexed. Do not
    // publish the manifest marker when a restricted database account allowed
    // the column but rejected the index migration.
    $bookings_table = $wpdb->prefix . 'yatoon_bookings';
    foreach ( array( 'client_reference', 'customer_email', 'customer_phone' ) as $required_index ) {
        if ( $complete && ! $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM `{$bookings_table}` WHERE Key_name = %s", $required_index ) ) ) {
            $complete = false;
        }
    }

    if ( $complete ) {
        update_option( $marker_option, YATOON_DB_VERSION, true );
        update_option( 'yatoon_schema_manifest_revision', $manifest_revision, true );
        // 4.6.2: a success clears the retry budget and any standing notice, so
        // a site that recovers (host grants ALTER, disk freed) goes quiet.
        delete_option( $failure_option );
        delete_option( $failure_version_option );
        delete_option( 'yatoon_schema_migration_blocked' );
} else {
        // 4.6.2: record the failure so the retry budget can run out.
        ++$failures;
        update_option( $failure_option, $failures, false );
        update_option( $failure_version_option, YATOON_DB_VERSION, false );
        if ( $failures >= $max_failures ) {
            update_option( 'yatoon_schema_migration_blocked', YATOON_DB_VERSION, false );
        }
        if ( function_exists( 'yatoon_safe_log' ) ) {
            yatoon_safe_log( sprintf(
                'Schema manifest %s remains incomplete (attempt %d of %d).%s',
                YATOON_DB_VERSION,
                $failures,
                $max_failures,
                $failures >= $max_failures ? ' Automatic retries stopped; see the admin notice.' : ''
            ) );
        }
}

    delete_option( $lock_option );
return $complete;
}

add_action( 'plugins_loaded', 'yatoon_run_versioned_schema_migrations', 11 );

/**
 * 4.6.2: drop options whose settings UI no longer exists.
 *
 * yatoon_load_google_fonts lost its control in 4.6.2 but nothing ever deleted
 * the row, so every site kept an autoloaded orphan that no code reads. Also
 * clears the 4.6.1 autoload marker, itself an orphan once
 * migrate_large_option_autoload() has run under its new name.
 *
 * Idempotent by construction: delete_option() on a missing option is a no-op.
 *
 * @since 4.6.2
 * @return void
 */
function yatoon_cleanup_deprecated_options() {
    $deprecated = array(
        'yatoon_load_google_fonts',
        'sbs_load_google_fonts',
        'yatoon_large_text_autoload_migrated_461',
    );
    foreach ( $deprecated as $option_name ) {
        delete_option( $option_name );
    }
}

/**
 * 4.6.2: tell the site owner when schema migrations have given up.
 *
 * Modelled on the credential-storage notices in class-yatoon-crypto.php: the
 * failure used to be silent, which is how a site could run for months on an
 * incomplete schema while re-running the whole migration batch every ten
 * minutes.
 */
add_action( 'admin_notices', function () {
    if ( ! current_user_can( 'manage_options' ) ) {
        return;
    }
    if ( '' === (string) get_option( 'yatoon_schema_migration_blocked', '' ) ) {
        return;
    }

    $retry_url = wp_nonce_url(
        add_query_arg( 'yatoon_retry_schema', '1', admin_url() ),
        'yatoon_retry_schema'
    );

    echo '<div class="notice notice-error"><p><strong>' .
        esc_html__( 'Yatoon: database updates could not be completed.', 'yatoon-booking-system' ) .
        '</strong> ' .
        esc_html__( 'Several attempts to create or alter Yatoon tables failed, so automatic retries have been stopped to keep them from hammering the database every few minutes. This usually means the WordPress database user is missing CREATE or ALTER privileges, or the database is out of disk space. Ask your host to grant those privileges, then retry.', 'yatoon-booking-system' ) .
        ' <a href="' . esc_url( $retry_url ) . '">' .
        esc_html__( 'Retry now', 'yatoon-booking-system' ) .
        '</a></p></div>';
} );

/**
 * 4.6.2: manual retry for the notice above.
 *
 * Clears the retry budget and runs the batch once, forced past both the
 * completion marker and the exhausted-budget guard.
 */
add_action( 'admin_init', function () {
    if ( empty( $_GET['yatoon_retry_schema'] ) ) { // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        return;
    }
    if ( ! current_user_can( 'manage_options' ) ) {
        return;
    }
    if ( ! isset( $_GET['_wpnonce'] ) || ! wp_verify_nonce( sanitize_key( wp_unslash( $_GET['_wpnonce'] ) ), 'yatoon_retry_schema' ) ) {
        return;
    }

    delete_option( 'yatoon_schema_migration_failures' );
    delete_option( 'yatoon_schema_migration_failed_version' );
    delete_option( 'yatoon_schema_migration_blocked' );
    delete_option( 'yatoon_schema_migration_lock' );

    yatoon_run_versioned_schema_migrations( true );

    wp_safe_redirect( remove_query_arg( array( 'yatoon_retry_schema', '_wpnonce' ) ) );
    exit;
}, 9 );
