<?php
if ( ! defined( 'ABSPATH' ) ) {
    exit; // Direct access is not allowed.
}

/**
 * Return the duration of the appointment that was actually booked.
 *
 * Notification code must not use the first service's catalog duration for a
 * multi-service/add-on booking. The persisted start/end boundary is the source
 * of truth and also reflects any duration normalization returned by Square.
 *
 * @param array|object $booking Booking payload or database row.
 * @param object|null  $service Optional fallback service row.
 * @return int Duration in minutes.
 */
function yatoon_effective_booking_duration_minutes( $booking, $service = null ) {
    $get = static function ( $source, $key, $default = '' ) {
        if ( is_array( $source ) ) { return $source[ $key ] ?? $default;
        }
        if ( is_object( $source ) ) { return $source->{$key} ?? $default;
        }
        return $default;
    };

    $start_value = $get( $booking, 'start_time' );
    $end_value   = $get( $booking, 'end_time' );
    if ( $start_value && $end_value ) {
        try {
            $start = new DateTime( $start_value );
            $end   = new DateTime( $end_value );
            $minutes = (int) round( ( $end->getTimestamp() - $start->getTimestamp() ) / 60 );
            if ( $minutes > 0 ) { return $minutes;
            }
        } catch ( Exception $e ) {
            // Fall through to explicit/catalog duration for legacy records.
        }
    }

    $explicit = absint( $get( $booking, 'duration_minutes', 0 ) );
    if ( $explicit > 0 ) { return $explicit;
    }
    return max( 1, absint( is_object( $service ) ? ( $service->duration_minutes ?? 0 ) : 0 ) );
}

/**
 * Reconcile all local service rows belonging to one Square booking.
 *
 * A Square booking can contain several sequential appointment segments while
 * Yatoon stores one row per primary service. This maps segment boundaries back
 * to those rows, includes intervening Design/Add-on segments in the preceding
 * service, and updates local start/end/staff values after edits made in Square.
 *
 * @param array $square_booking Square Booking API object.
 * @return int Number of local rows changed.
 */
function yatoon_reconcile_square_booking_segments( $square_booking ) {
    global $wpdb;

    $square_id = sanitize_text_field( (string) ( $square_booking['id'] ?? '' ) );
    $segments  = isset( $square_booking['appointment_segments'] ) && is_array( $square_booking['appointment_segments'] )
        ? array_values( $square_booking['appointment_segments'] )
        : array();
    if ( !$square_id || !$segments || empty( $square_booking['start_at'] ) ) { return 0;
    }

    $bookings_table = $wpdb->prefix . 'yatoon_bookings';
    $options_table  = $wpdb->prefix . 'yatoon_service_options';
    $services_table = $wpdb->prefix . 'yatoon_services';
    $links_table    = $wpdb->prefix . 'yatoon_booking_options';
    $staff_table    = $wpdb->prefix . 'yatoon_staff';

    $rows = yatoon_db_get_results( $wpdb->prepare(
        "SELECT id, service_id, staff_id, start_time, end_time, service_order,
                status, group_booking_id, customer_name, customer_email,
                customer_phone, notes, payment_status
         FROM {$bookings_table}
         WHERE square_booking_id = %s
           AND status NOT IN ('cancelled','cancelled_by_customer','CANCELLED_BY_CUSTOMER','CANCELLED_BY_SELLER','DECLINED')
         ORDER BY service_order ASC, start_time ASC, id ASC",
        $square_id
    ) );
    if ( !$rows ) { return 0;
    }

    try {
        $cursor = new DateTime( $square_booking['start_at'] );
        $cursor->setTimezone( yatoon_timezone() );
    } catch ( Exception $e ) {
        return 0;
    }

    // Work out the TRUE set of groups this Square booking should have locally,
    // independent of how many rows currently exist: walk every segment in
    // order and start a new group whenever the staff member changes or a new
    // (non Add-on) main service begins, folding intervening Design/Add-on
    // segments into the group they belong to. This is authoritative - it
    // doesn't depend on guessing boundaries from whatever rows happen to
    // exist already, which is what made the previous version of this
    // function silently swallow a second staff member's segment into the
    // first staff member's row instead of ever noticing it was missing.
    $groups  = array();
    $current = null;
    foreach ( $segments as $seg_item ) {
        $seg_var  = (string) ( $seg_item['service_variation_id'] ?? '' );
        $seg_team = (string) ( $seg_item['team_member_id'] ?? '' );
        $seg_dur  = max( 0, intval( $seg_item['duration_minutes'] ?? 0 ) );
        $seg_opt  = $seg_var ? yatoon_db_get_row( $wpdb->prepare(
            "SELECT o.id, o.service_id, o.duration_minutes, o.group_name, o.name AS option_name,
                    s.name AS service_name
             FROM {$options_table} o
             LEFT JOIN {$services_table} s ON s.id = o.service_id
             WHERE o.square_id = %s LIMIT 1", $seg_var
        ) ) : null;
        $design_label = $seg_opt ? ( (string) $seg_opt->option_name . ' ' . (string) $seg_opt->service_name ) : '';
        $is_design_segment = $seg_opt && preg_match( '/(?:custom\s*design|nail\s*art)/i', $design_label );
        // Some older Square catalogs stored design choices as a regular option
        // instead of group_name=Add-on. They still belong to the preceding
        // manicure/service and must not replace its parent service locally.
        $is_addon = $seg_opt && ( (string) $seg_opt->group_name === 'Add-on' || $is_design_segment );
        if ( $seg_dur <= 0 ) { $seg_dur = $seg_opt ? intval( $seg_opt->duration_minutes ) : 0;
        }

        $starts_new_group = ( $current === null ) || ( $current['team_member_id'] !== $seg_team ) || ! $is_addon;
        if ( $starts_new_group ) {
            if ( $current !== null ) { $groups[] = $current;
            }
            $current = array(
                'team_member_id' => $seg_team,
                'service_id'     => $seg_opt ? intval( $seg_opt->service_id ) : 0,
                'duration'       => $seg_dur,
                'option_ids'     => $seg_opt ? array( intval( $seg_opt->id ) ) : array(),
                'fully_mapped'   => (bool) $seg_opt,
            );
        } else {
            $current['duration'] += $seg_dur;
            if ( $seg_opt ) { $current['option_ids'][] = intval( $seg_opt->id );
            } else { $current['fully_mapped'] = false;
            }
        }
    }
    if ( $current !== null ) { $groups[] = $current;
    }
    if ( empty( $groups ) ) { return 0;
    }

    // Turn each group's duration into a concrete start/end time by walking
    // the shared cursor forward group by group, in order.
    foreach ( $groups as &$group ) {
        $group['start'] = clone $cursor;
        $duration       = max( 1, intval( $group['duration'] ) );
        $group['end']   = ( clone $group['start'] )->modify( "+{$duration} minutes" );
        $cursor         = clone $group['end'];
    }
    unset( $group );

    // Existing rows and computed groups are both already ordered the same
    // way (creation/segment order), so match them 1:1 by position: update
    // whichever rows already exist, then backfill any leftover groups that
    // don't have a row yet (e.g. a second staff member's portion that never
    // got synced).
    $changed  = 0;
    $row_count   = count( $rows );
    $group_count = count( $groups );
    $match_count = min( $row_count, $group_count );

    $group_booking_id = '';
    foreach ( $rows as $existing_row ) {
        if ( ! empty( $existing_row->group_booking_id ) ) { $group_booking_id = $existing_row->group_booking_id;
break; }
    }
    if ( ! $group_booking_id ) { $group_booking_id = wp_generate_uuid4();
    }

    for ( $i = 0; $i < $match_count; $i++ ) {
        $row   = $rows[ $i ];
        $group = $groups[ $i ];

        $update = array(
            'start_time' => $group['start']->format( 'Y-m-d H:i:s' ),
            'end_time'   => $group['end']->format( 'Y-m-d H:i:s' ),
            'booking_timezone' => yatoon_timezone_string( yatoon_location_id_for_external( $square_booking['location_id'] ?? '' ) ?: null, 'square' ),
            'updated_at' => current_time( 'mysql' ),
        );
        if ( $group['service_id'] ) { $update['service_id'] = intval( $group['service_id'] );
        }
        if ( $group['team_member_id'] ) {
            $staff_id = yatoon_db_get_var( $wpdb->prepare( "SELECT id FROM {$staff_table} WHERE square_id = %s LIMIT 1", $group['team_member_id'] ) );
            if ( $staff_id ) { $update['staff_id'] = intval( $staff_id );
            }
        }
        if ( empty( $row->group_booking_id ) ) { $update['group_booking_id'] = $group_booking_id;
        }

        if ( $update['start_time'] !== $row->start_time
            || $update['end_time'] !== $row->end_time
            || ( isset( $update['service_id'] ) && intval( $update['service_id'] ) !== intval( $row->service_id ) )
            || ( isset( $update['staff_id'] ) && intval( $update['staff_id'] ) !== intval( $row->staff_id ) )
            || isset( $update['group_booking_id'] ) ) {
            yatoon_db_update( $bookings_table, $update, array( 'id' => $row->id ) );
            ++$changed;
        }

        // Square is authoritative here. Replace stale links as well as adding
        // new ones; merely appending left an old Gel/Builder variation first
        // in the list after Square had changed it to Dipping Powder, so the
        // customer change screen selected the wrong service. Only do an exact
        // replacement when every segment was mapped locally.
        if ( ! empty( $group['fully_mapped'] ) ) {
            $desired_ids  = array_values( array_unique( array_filter( array_map( 'absint', $group['option_ids'] ) ) ) );
            $existing_ids = array_map( 'absint', (array) yatoon_db_get_col( $wpdb->prepare(
                "SELECT option_id FROM {$links_table} WHERE booking_id = %d ORDER BY id ASC",
                $row->id
            ) ) );
            foreach ( array_diff( $existing_ids, $desired_ids ) as $option_id ) {
                yatoon_db_delete( $links_table, array( 'booking_id' => $row->id, 'option_id' => $option_id ), array( '%d', '%d' ) );
            }
            foreach ( array_diff( $desired_ids, $existing_ids ) as $option_id ) {
                yatoon_db_insert( $links_table, array( 'booking_id' => $row->id, 'option_id' => $option_id ), array( '%d', '%d' ) );
            }
        }
    }

    // Backfill: more groups than we have rows for - most commonly a
    // multi-staff visit (one guest getting a service from staff A, then a
    // different service from staff B) where only the first staff member's
    // portion was ever synced locally. Create the missing row(s) now instead
    // of silently leaving that staff member's calendar without the visit.
    if ( $group_count > $row_count ) {
        $template   = $rows[0];
        $next_order = 0;
        foreach ( $rows as $existing_row ) {
            $next_order = max( $next_order, intval( $existing_row->service_order ) + 1 );
        }

        for ( $i = $row_count; $i < $group_count; $i++ ) {
            $group    = $groups[ $i ];
            if ( ! $group['service_id'] ) { continue; // Unmapped variation - can't place it safely.
            }
            $staff_id = $group['team_member_id'] ? yatoon_db_get_var( $wpdb->prepare( "SELECT id FROM {$staff_table} WHERE square_id = %s LIMIT 1", $group['team_member_id'] ) ) : 0;
            if ( ! $staff_id ) { continue; // Can't place it without a matching local staff record.
            }

            $insert = array(
                'square_booking_id' => $square_id,
                'customer_name'     => $template->customer_name,
                'customer_email'    => $template->customer_email,
                'customer_phone'    => $template->customer_phone,
                'status'            => $template->status,
                'notes'             => $template->notes,
                'payment_status'    => $template->payment_status,
                'service_id'        => $group['service_id'],
                'staff_id'          => intval( $staff_id ),
                'location_id'       => yatoon_location_id_for_external( $square_booking['location_id'] ?? '' ) ?: null,
                'booking_timezone'  => yatoon_timezone_string( yatoon_location_id_for_external( $square_booking['location_id'] ?? '' ) ?: null, 'square' ),
                'start_time'        => $group['start']->format( 'Y-m-d H:i:s' ),
                'end_time'          => $group['end']->format( 'Y-m-d H:i:s' ),
                'square_synced'     => 1,
                'total_price'       => 0,
                'group_booking_id'  => $group_booking_id,
                'service_order'     => $next_order,
            );
            yatoon_db_insert( $bookings_table, $insert );
            $new_id = $wpdb->insert_id;
            ++$next_order;
            if ( $new_id ) {
                foreach ( array_unique( $group['option_ids'] ) as $option_id ) {
                    yatoon_db_insert( $links_table, array( 'booking_id' => $new_id, 'option_id' => $option_id ) );
                }
                ++$changed;
            }
        }
    }

    // Square is authoritative for a synced booking. Older Change Services
    // builds could reconcile a newly added segment and then insert it locally
    // a second time. Cancel any unmatched active local rows so those historical
    // duplicates disappear on the next refresh without deleting audit data.
    if ( $row_count > $group_count ) {
        for ( $i = $group_count; $i < $row_count; $i++ ) {
            $extra = $rows[ $i ];
            yatoon_db_update(
                $bookings_table,
                array( 'status' => 'cancelled', 'updated_at' => current_time( 'mysql' ) ),
                array( 'id' => absint( $extra->id ) ),
                array( '%s', '%s' ),
                array( '%d' )
            );
            ++$changed;
        }
    }

    return $changed;
}
/**
 * Shared runtime contract for every Yatoon Booking package.
 */

if ( !function_exists( 'yatoon_db_call' ) ) {
    function yatoon_db_call( $method, $args ) {
        global $wpdb;
        $allowed = array( 'get_var', 'get_row', 'get_results', 'get_col', 'query', 'insert', 'update', 'delete' );
        if ( !in_array( $method, $allowed, true ) ) {
            return null;
        }
        return call_user_func_array( array( $wpdb, $method ), $args );
    }
}

if ( !function_exists( 'yatoon_db_get_var' ) ) {
    function yatoon_db_get_var() {
 return yatoon_db_call( 'get_var', func_get_args() ); }
    function yatoon_db_get_row() {
 return yatoon_db_call( 'get_row', func_get_args() ); }
    function yatoon_db_get_results() {
 return yatoon_db_call( 'get_results', func_get_args() ); }
    function yatoon_db_get_col() {
 return yatoon_db_call( 'get_col', func_get_args() ); }
    function yatoon_db_query() {
 return yatoon_db_call( 'query', func_get_args() ); }
    function yatoon_db_insert() {
        global $wpdb;
        $args = func_get_args();
        if ( isset( $args[0], $args[1] ) && $args[0] === $wpdb->prefix . 'yatoon_bookings' && is_array( $args[1] ) ) {
            $added_formats = 0;
            if ( ! array_key_exists( 'created_at', $args[1] ) ) {
                $args[1]['created_at'] = current_time( 'mysql' );
                ++$added_formats;
            }
            if ( ! array_key_exists( 'created_at_gmt', $args[1] ) ) {
                $args[1]['created_at_gmt'] = current_time( 'mysql', true );
                ++$added_formats;
            }
            if ( $added_formats && isset( $args[2] ) && is_array( $args[2] ) ) {
                $args[2] = array_merge( $args[2], array_fill( 0, $added_formats, '%s' ) );
            }
        }
        return yatoon_db_call( 'insert', $args );
    }
    function yatoon_db_update() {
 return yatoon_db_call( 'update', func_get_args() ); }
    function yatoon_db_delete() {
 return yatoon_db_call( 'delete', func_get_args() ); }
}

/**
 * Resolve the business location attached to the current booking request.
 *
 * @param int|string|null $location_id Yatoon location id/slug. Null reads the request.
 * @return object|null
 */
if ( ! function_exists( 'yatoon_booking_location' ) ) {
    function yatoon_booking_location( $location_id = null ) {
        if ( null === $location_id ) {
            $location_id = isset( $_REQUEST['yatoon_location_id'] )
                ? sanitize_text_field( wp_unslash( (string) $_REQUEST['yatoon_location_id'] ) )
                : '';
        }
        if ( '' === (string) $location_id || ! class_exists( 'YATOON_Locations' ) ) {
            return null;
        }
        return YATOON_Locations::resolve( $location_id );
    }
}

/** Return the selected external location id without falling back to browser data. */
if ( ! function_exists( 'yatoon_external_location_id' ) ) {
    function yatoon_external_location_id( $location_id = null, $platform = null ) {
        $platform = $platform ?: (string) get_option( 'yatoon_active_platform', 'local' );
        if ( ! in_array( $platform, array( 'local', 'square', 'vagaro' ), true ) ) { $platform = 'local';
        }
        $location = yatoon_booking_location( $location_id );
        if ( 'square' === $platform ) {
            return $location && ! empty( $location->square_id )
                ? (string) $location->square_id
                : (string) get_option( 'yatoon_square_location_id', '' );
        }
        return '';
    }
}

/** Map a Square Location id back to its Yatoon location row. */
if ( ! function_exists( 'yatoon_location_id_for_external' ) ) {
    function yatoon_location_id_for_external( $external_location_id ) {
        if ( ! $external_location_id || ! class_exists( 'YATOON_Locations' ) ) { return 0;
        }
        foreach ( (array) YATOON_Locations::get_all( true ) as $location ) {
            if ( (string) ( $location->square_id ?? '' ) === (string) $external_location_id ) {
                return (int) $location->id;
            }
        }
        return 0;
    }
}

/**
 * The authoritative booking timezone, as a DateTimeZone.
 *
 * Every date/time calculation in this plugin MUST go through this (or
 * yatoon_timezone_string()). Do not call get_option( 'timezone_string' )
 * directly:
 *
 *   WordPress stores the timezone in one of two mutually exclusive ways.
 *   If the admin picked a city ("Asia/Shanghai") it lives in the
 *   timezone_string option. If they picked a raw UTC offset ("UTC+8") then
 *   timezone_string is an EMPTY STRING and the real value lives in
 *   gmt_offset. Code that does `get_option('timezone_string') ?: 'Some/Zone'`
 *   silently runs the entire availability engine in the wrong timezone on
 *   every offset-configured site.
 *
 * wp_timezone() handles both cases and never returns an empty value, so
 * there is no fallback zone here by design -- a hardcoded fallback is what
 * caused the bug this helper exists to prevent.
 *
 * @since 4.4.3
 * @return DateTimeZone
 */
if ( !function_exists( 'yatoon_timezone' ) ) {
    function yatoon_timezone( $location_id = null, $platform = null ) {
        $platform = $platform ?: (string) get_option( 'yatoon_active_platform', 'local' );
        if ( ! in_array( $platform, array( 'local', 'square', 'vagaro' ), true ) ) { $platform = 'local';
        }
        $location = yatoon_booking_location( $location_id );

        // An explicitly configured Yatoon location always describes the
        // calendar being rendered, regardless of where WordPress is hosted.
        if ( $location && ! empty( $location->timezone ) ) {
            try {
                return new DateTimeZone( (string) $location->timezone );
            } catch ( Exception $exception ) {
                // Fall through to the connected platform / WordPress setting.
            }
        }

        if ( 'square' === $platform && class_exists( 'YATOON_Square_API' ) ) {
            $square_location_id = yatoon_external_location_id( $location_id, 'square' );
            if ( $square_location_id ) {
                $square = new YATOON_Square_API( $square_location_id );
                if ( method_exists( $square, 'get_location_timezone' ) ) {
                    $timezone = $square->get_location_timezone();
                    if ( is_string( $timezone ) && '' !== $timezone ) {
                        try {
                            return new DateTimeZone( $timezone );
                        } catch ( Exception $exception ) {
                            // Invalid remote data must never break booking.
                        }
                    }
                }
            }
        }

        if ( 'vagaro' === $platform ) {
            $vagaro_timezone = trim( (string) get_option( 'yatoon_vagaro_timezone', '' ) );
            if ( $vagaro_timezone ) {
                try {
                    return new DateTimeZone( $vagaro_timezone );
                } catch ( Exception $exception ) {
                    // Fall through.
                }
            }
        }

        return wp_timezone();
    }
}

/**
 * The site's booking timezone as a string.
 *
 * Returns either an Olson identifier ("Asia/Shanghai") or a fixed offset
 * ("+08:00"), both of which DateTimeZone accepts.
 *
 * @since 4.4.3
 * @return string
 */
if ( !function_exists( 'yatoon_timezone_string' ) ) {
    function yatoon_timezone_string( $location_id = null, $platform = null ) {
        return yatoon_timezone( $location_id, $platform )->getName();
    }
}

/**
 * Format a site-local datetime string for display.
 *
 * Booking timestamps (start_time, created_at on our own tables, etc.) are
 * stored as 'Y-m-d H:i:s' in SITE LOCAL time. Two things used to go wrong when
 * printing them:
 *
 *   1. date() formats using PHP's default timezone. WordPress sets that to
 *      UTC, so a 2pm appointment printed as 2pm only by coincidence on UTC
 *      sites and was silently correct-looking everywhere else because the
 *      string was already local -- but any arithmetic around it was not.
 *   2. date() never translates. "March" stayed "March" on a Spanish site.
 *
 * The naive fix, wp_date( $format, strtotime( $local_string ) ), is WORSE:
 * strtotime() parses the string in PHP's default zone (UTC) and wp_date()
 * then converts to site time, shifting the value by the site's offset. This
 * helper parses in the site zone first, so there is exactly one conversion.
 *
 * @since 4.4.3
 * @param string $format   PHP date format.
 * @param string $datetime Site-local 'Y-m-d H:i:s' (or anything DateTime parses).
 * @param string $fallback Returned when $datetime is empty or unparseable.
 * @return string
 */
if ( !function_exists( 'yatoon_format_local' ) ) {
    function yatoon_format_local( $format, $datetime, $fallback = '-' ) {
        $datetime = trim( (string) $datetime );
        if ( '' === $datetime || '0000-00-00 00:00:00' === $datetime ) {
            return $fallback;
        }
        try {
            $dt = new DateTime( $datetime, yatoon_timezone() );
        } catch ( Exception $e ) {
            return $fallback;
        }
        return wp_date( $format, $dt->getTimestamp() );
    }
}

/** Convert a site-local database datetime to Unix time without a UTC shift. */
if ( ! function_exists( 'yatoon_local_datetime_timestamp' ) ) {
function yatoon_local_datetime_timestamp( $datetime ) {
        $datetime = trim( (string) $datetime );
        if ( '' === $datetime || '0000-00-00 00:00:00' === $datetime ) { return false;
        }
        try {
            return ( new DateTimeImmutable( $datetime, yatoon_timezone() ) )->getTimestamp();
        } catch ( Exception $exception ) {
            return false;
        }
    }
}

/** Return the usable balance for a valid gift card, capped at the amount due. */
function yatoon_gift_card_available_amount( $code, $amount_due ) {
    global $wpdb;
    $code       = strtoupper( sanitize_text_field( (string) $code ) );
    $amount_due = max( 0, round( (float) $amount_due, 2 ) );
    if ( $code === '' || $amount_due <= 0 ) {
        return 0.0;
    }
    $table = $wpdb->prefix . 'yatoon_gift_cards';
    $row   = yatoon_db_get_row( $wpdb->prepare(
        "SELECT balance FROM {$table} WHERE code = %s AND status = 'active' AND balance > 0 AND (expires_at IS NULL OR expires_at >= %s) LIMIT 1",
        $code,
        current_time( 'Y-m-d' )
    ) );
    return $row ? min( $amount_due, round( (float) $row->balance, 2 ) ) : 0.0;
}

/**
 * Atomically redeem at most the booking amount and record the ledger entry.
 * The row lock prevents two simultaneous checkouts from spending one balance.
 */
function yatoon_redeem_gift_card( $code, $amount_due, $booking_id ) {
    global $wpdb;
    $code       = strtoupper( sanitize_text_field( (string) $code ) );
    $amount_due = max( 0, round( (float) $amount_due, 2 ) );
    $booking_id = absint( $booking_id );
    if ( $code === '' || $amount_due <= 0 || $booking_id <= 0 ) {
        return 0.0;
    }

    $cards = $wpdb->prefix . 'yatoon_gift_cards';
    $tx    = $wpdb->prefix . 'yatoon_gift_card_transactions';
    $wpdb->query( 'START TRANSACTION' );
    $card = yatoon_db_get_row( $wpdb->prepare(
        "SELECT id, balance FROM {$cards} WHERE code = %s AND status = 'active' AND balance > 0 AND (expires_at IS NULL OR expires_at >= %s) LIMIT 1 FOR UPDATE",
        $code,
        current_time( 'Y-m-d' )
    ) );
    if ( ! $card ) {
        $wpdb->query( 'ROLLBACK' );
        return 0.0;
    }

    $redeem = min( $amount_due, round( (float) $card->balance, 2 ) );
    $balance = max( 0, round( (float) $card->balance - $redeem, 2 ) );
    $updated = yatoon_db_update(
        $cards,
        array( 'balance' => $balance, 'status' => $balance <= 0 ? 'used' : 'active' ),
        array( 'id' => (int) $card->id )
    );
    $logged = false !== $updated && false !== yatoon_db_insert( $tx, array(
        'gift_card_id'  => (int) $card->id,
        'booking_id'    => $booking_id,
        'type'          => 'redeem',
        'amount'        => $redeem,
        'balance_after' => $balance,
        'note'          => 'Booking #' . $booking_id,
    ) );
    if ( ! $logged ) {
        $wpdb->query( 'ROLLBACK' );
        return 0.0;
    }
    $wpdb->query( 'COMMIT' );
    return $redeem;
}

/**
 * Booking statuses that do NOT occupy a slot on a staff member's calendar.
 *
 * Historically this list was copy-pasted into roughly a dozen SQL strings,
 * which meant adding a status meant finding every copy. Anything not in this
 * list is treated as occupying the slot.
 *
 * @since 4.4.3
 * @return string[]
 */
if ( !function_exists( 'yatoon_non_occupying_statuses' ) ) {
    function yatoon_non_occupying_statuses() {
        return array(
            'cancelled',
            'cancelled_by_customer',
            'CANCELLED_BY_CUSTOMER',
            'CANCELLED_BY_SELLER',
            'DECLINED',
            'no_show',
        );
    }
}

/**
 * Whether a booking status occupies its slot.
 *
 * @since 4.4.3
 * @param string $status
 * @return bool
 */
if ( !function_exists( 'yatoon_status_occupies_slot' ) ) {
    function yatoon_status_occupies_slot( $status ) {
        $status = (string) $status;
        foreach ( yatoon_non_occupying_statuses() as $free ) {
            if ( 0 === strcasecmp( $status, $free ) ) {
                return false;
            }
        }
        return true;
    }
}

/**
 * Whether the last $wpdb error was a duplicate-key violation.
 *
 * Used to turn a slot_guard collision into a friendly "that slot was just
 * taken" message instead of a generic failure.
 *
 * @since 4.4.3
 * @return bool
 */
if ( !function_exists( 'yatoon_last_error_is_duplicate_key' ) ) {
    function yatoon_last_error_is_duplicate_key() {
        global $wpdb;
        $error = isset( $wpdb->last_error ) ? (string) $wpdb->last_error : '';
        if ( '' === $error ) {
            return false;
        }
        return ( false !== stripos( $error, 'duplicate entry' ) )
            || ( false !== stripos( $error, 'slot_guard' ) );
    }
}

if ( !function_exists( 'yatoon_should_run_schema_migration' ) ) {
    function yatoon_should_run_schema_migration( $installed_version, $target_version ) {
        return !version_compare( (string) $installed_version, (string) $target_version, '>=' );
    }
}

if ( !function_exists( 'yatoon_booking_required_column_definitions' ) ) {
	/**
	 * Columns required by current booking write/read paths beyond the original base table.
	 *
	 * Activation, versioned migration, completion checks, and same-version repair all
	 * consume this single manifest so a newly added field cannot be silently omitted
	 * from one of those paths.
	 */
	function yatoon_booking_required_column_definitions() {
		return array(
			'client_reference'       => "varchar(64) DEFAULT '' AFTER square_booking_id",
			'virtual_provider'       => 'varchar(20) DEFAULT NULL AFTER google_event_id',
			'virtual_meeting_id'     => 'varchar(255) DEFAULT NULL AFTER virtual_provider',
			'virtual_meeting_url'    => 'text DEFAULT NULL AFTER virtual_meeting_id',
			'consent_accepted'       => 'tinyint(1) DEFAULT 0 AFTER google_event_id',
			'consent_signature'      => 'varchar(255) DEFAULT NULL AFTER consent_accepted',
			'consent_signed_at'      => 'datetime DEFAULT NULL AFTER consent_signature',
			'consent_text_snapshot'  => 'text DEFAULT NULL AFTER consent_signed_at',
			'policy_acknowledged'    => 'tinyint(1) DEFAULT 0 AFTER consent_text_snapshot',
			'policy_acknowledged_at' => 'datetime DEFAULT NULL AFTER policy_acknowledged',
			'policy_text_snapshot'   => 'text DEFAULT NULL AFTER policy_acknowledged_at',
			'payment_status'         => "varchar(30) DEFAULT 'unpaid' AFTER policy_text_snapshot",
			'payment_note'           => 'text DEFAULT NULL AFTER payment_status',
			'payment_updated_at'     => 'datetime DEFAULT NULL AFTER payment_note',
			'design_attachment_id'   => 'bigint(20) DEFAULT NULL AFTER notes',
			'design_image_url'       => 'varchar(500) DEFAULT NULL AFTER design_attachment_id',
			'design_upload_token'    => 'varchar(64) DEFAULT NULL AFTER design_image_url',
			'manage_token'           => 'varchar(64) DEFAULT NULL AFTER payment_updated_at',
			'manage_token_expires'   => 'datetime DEFAULT NULL AFTER manage_token',
			'deposit_amount'         => 'decimal(10,2) DEFAULT NULL AFTER manage_token_expires',
			'deposit_paid'           => 'tinyint(1) DEFAULT 0 AFTER deposit_amount',
			'deposit_intent_id'      => 'varchar(255) DEFAULT NULL AFTER deposit_paid',
			'reminder_sent'          => 'tinyint(1) DEFAULT 0 AFTER deposit_intent_id',
			'review_requested'       => 'tinyint(1) DEFAULT 0 AFTER reminder_sent',
			'client_timezone'        => 'varchar(100) DEFAULT NULL AFTER customer_phone',
			'booking_timezone'       => 'varchar(100) DEFAULT NULL AFTER client_timezone',
			'location_id'            => 'bigint(20) DEFAULT NULL AFTER booking_timezone',
			'created_at_gmt'         => 'datetime DEFAULT NULL AFTER created_at',
		);
	}
}

if ( !function_exists( 'yatoon_main_schema_migration_complete' ) ) {
    /**
     * Verify the schema owned by yatoon_run_migrations().
     *
     * The version option is a completion marker, not an attempt marker. Keep
     * this manifest independent from wpdb so failed-migration behaviour can be
     * regression tested without a database.
     */
    function yatoon_main_schema_migration_complete( $table_exists, $column_exists ) {
        if ( !is_callable( $table_exists ) || !is_callable( $column_exists ) ) {
            return false;
        }

        $required = array(
            'yatoon_service_options' => array('group_name', 'square_version'),
            'yatoon_bookings'        => array_keys( yatoon_booking_required_column_definitions() ),
            'yatoon_services'        => array(
                'require_policy', 'require_consent', 'allow_staff_select',
                'allow_design_upload', 'buffer_before_min', 'buffer_after_min',
                'min_lead_time_min', 'image_url', 'estimated_cost',
            ),
            'yatoon_staff'           => array('calendar_color', 'portal_role'),
            'yatoon_staff_time_off_requests' => array('id', 'staff_id', 'start_at', 'end_at', 'status'),
            'yatoon_clients'         => array('id', 'preferences'),
            'yatoon_waitlist'        => array('notified_at', 'matched_date', 'matched_time', 'matched_start_at', 'claim_token', 'claim_expires_at', 'claim_status'),
            'yatoon_customer_favorites' => array('id', 'customer_key', 'service_id'),
        );

        foreach ( $required as $table => $columns ) {
            if ( !$table_exists( $table ) ) {
                return false;
            }
            foreach ( $columns as $column ) {
                if ( !$column_exists( $table, $column ) ) {
                    return false;
                }
            }
        }
        return true;
    }
}

if ( !function_exists( 'yatoon_record_onboarding_milestone' ) ) {
    function yatoon_record_onboarding_milestone( $milestone ) {
        $milestone = sanitize_key( $milestone );
        if ( '' === $milestone ) {
            return false;
        }
        $milestones = get_option( 'yatoon_onboarding_milestones', array() );
        if ( !is_array( $milestones ) ) {
            $milestones = array();
        }
        if ( isset( $milestones[ $milestone ] ) ) {
            return false;
        }
        $recorded_at = current_time( 'mysql' );
        $milestones[ $milestone ] = $recorded_at;
        update_option( 'yatoon_onboarding_milestones', $milestones, false );
        do_action( 'yatoon_onboarding_milestone_recorded', $milestone, $recorded_at );
        return true;
    }
}

if ( !function_exists( 'yatoon_track_created_booking_milestone' ) ) {
    function yatoon_track_created_booking_milestone( $booking_data ) {
        unset( $booking_data );
        $test_flag = filter_input( INPUT_POST, 'yatoon_test_booking', FILTER_SANITIZE_NUMBER_INT );
        $is_test = current_user_can( 'manage_options' )
            && '1' === get_option( 'yatoon_test_booking_mode', '0' )
            && '1' === (string) $test_flag;
        yatoon_record_onboarding_milestone( $is_test ? 'first_test_booking' : 'first_real_booking' );
    }
    add_action( 'yatoon_booking_created', 'yatoon_track_created_booking_milestone', 1, 1 );
}
