<?php
/**
 * Migrate a salon in from Fresha, Booksy, Vagaro or a plain spreadsheet.
 *
 * Why this is separate from class-yatoon-data-portability.php: that importer
 * reads another WordPress plugin's database tables directly (wp_bookly_*,
 * wp_amelia_*, wp_os_*). Every source it supports is a WordPress plugin. The
 * salons worth winning are not on WordPress plugins -- they are on Fresha,
 * Booksy and Vagaro, and the only thing those hand you is a CSV export. That is
 * a different problem: no schema to read, column names that differ per vendor
 * and per export, and data that has to be matched against customers you may
 * already have.
 *
 * Three decisions worth knowing about:
 *
 * 1. Columns are matched by alias, not by a mapping screen. Every vendor's
 *    export is inspected against a list of known header names first; only the
 *    columns that fail to match are shown for manual mapping. A salon owner
 *    should not have to hand-map "Client name" on their first day.
 *
 * 2. Every import is a batch, and every batch can be undone. An import that
 *    cannot be reversed is one a cautious owner will never run, which means the
 *    migration never happens. Rows created are deleted on rollback; rows
 *    updated are restored from a snapshot taken before the write.
 *
 * 3. Remaining package visits and gift-card balances are first-class. Those are
 *    money the customer has already paid. A migration that drops them turns
 *    every loyal regular into a dispute on day one, and it is precisely the
 *    data no other WordPress booking plugin has anywhere to put.
 */

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

class YATOON_SaaS_Import {

    const SCHEMA_OPTION  = 'yatoon_saas_import_schema';
    const SCHEMA_VERSION = '4.6.1';
    const MAX_ROWS       = 20000;
    const MAX_FILE_BYTES = 12582912; // 12 MB — a mid-size salon's client export.
    const STAGE_TTL      = 1800;     // Half an hour to look at a preview.

    /* ---------------------------------------------------------------- boot */

    public static function init() {
        add_action( 'plugins_loaded', array( __CLASS__, 'maybe_install_schema' ), 6 );
        add_action( 'admin_menu', array( __CLASS__, 'add_menu' ), 41 );
        add_action( 'admin_post_yatoon_saas_preview', array( __CLASS__, 'handle_preview' ) );
        add_action( 'admin_post_yatoon_saas_commit', array( __CLASS__, 'handle_commit' ) );
        add_action( 'admin_post_yatoon_saas_rollback', array( __CLASS__, 'handle_rollback' ) );
    }

    public static function maybe_install_schema() {
        if ( self::SCHEMA_VERSION === (string) get_option( self::SCHEMA_OPTION, '' ) ) { return; }
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $charset = $wpdb->get_charset_collate();

        dbDelta( "CREATE TABLE {$wpdb->prefix}yatoon_import_batches (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            source varchar(32) NOT NULL DEFAULT '',
            entity varchar(32) NOT NULL DEFAULT '',
            file_name varchar(255) NOT NULL DEFAULT '',
            rows_created int(11) NOT NULL DEFAULT 0,
            rows_updated int(11) NOT NULL DEFAULT 0,
            rows_skipped int(11) NOT NULL DEFAULT 0,
            status varchar(20) NOT NULL DEFAULT 'imported',
            created_by bigint(20) NOT NULL DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY status_created (status, created_at)
        ) {$charset};" );

        dbDelta( "CREATE TABLE {$wpdb->prefix}yatoon_import_batch_rows (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            batch_id bigint(20) NOT NULL,
            target_table varchar(64) NOT NULL DEFAULT '',
            target_id bigint(20) NOT NULL DEFAULT 0,
            action varchar(12) NOT NULL DEFAULT 'created',
            previous longtext DEFAULT NULL,
            PRIMARY KEY (id),
            KEY batch_id (batch_id)
        ) {$charset};" );

        $ok = self::table_exists( $wpdb->prefix . 'yatoon_import_batches' )
            && self::table_exists( $wpdb->prefix . 'yatoon_import_batch_rows' );
        if ( $ok ) {
            update_option( self::SCHEMA_OPTION, self::SCHEMA_VERSION, false );
        }
    }

    private static function table_exists( $table ) {
        global $wpdb;
        return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table; // phpcs:ignore WordPress.DB.DirectDatabaseQuery
    }

    /* ------------------------------------------------------------ sources */

    public static function sources() {
        return array(
            'fresha' => __( 'Fresha', 'yatoon-booking-system' ),
            'booksy' => __( 'Booksy', 'yatoon-booking-system' ),
            'vagaro' => __( 'Vagaro', 'yatoon-booking-system' ),
            'other'  => __( 'Another system (plain CSV)', 'yatoon-booking-system' ),
        );
    }

    public static function entities() {
        return array(
            'clients'   => __( 'Customers', 'yatoon-booking-system' ),
            'bookings'  => __( 'Appointment history', 'yatoon-booking-system' ),
            'packages'  => __( 'Packages and remaining visits', 'yatoon-booking-system' ),
            'giftcards' => __( 'Gift card balances', 'yatoon-booking-system' ),
        );
    }

    /**
     * Known header names per field.
     *
     * Order matters: the first alias present in a row wins. Vendor-specific
     * names come before generic ones so "Client Name" beats a stray "name"
     * column somewhere else in the same export.
     */
    public static function field_aliases( $entity ) {
        $maps = array(
            'clients' => array(
                'name'       => array( 'client_name', 'customer_name', 'full_name', 'name', 'client', 'customer' ),
                'first_name' => array( 'first_name', 'firstname', 'given_name' ),
                'last_name'  => array( 'last_name', 'lastname', 'surname', 'family_name' ),
                'email'      => array( 'email', 'client_email', 'customer_email', 'email_address', 'e_mail' ),
                'phone'      => array( 'phone', 'mobile', 'mobile_number', 'client_phone', 'customer_phone', 'phone_number', 'telephone' ),
                'notes'      => array( 'notes', 'note', 'client_notes', 'comments', 'remarks' ),
                'created_at' => array( 'created_at', 'client_since', 'date_created', 'joined', 'registration_date' ),
            ),
            'bookings' => array(
                'name'       => array( 'client_name', 'customer_name', 'client', 'customer', 'name' ),
                'email'      => array( 'client_email', 'customer_email', 'email' ),
                'phone'      => array( 'client_phone', 'customer_phone', 'phone', 'mobile' ),
                'service'    => array( 'service_name', 'service', 'item', 'treatment', 'appointment_type' ),
                'staff'      => array( 'team_member', 'staff_name', 'staff', 'employee', 'provider', 'stylist', 'technician' ),
                'start'      => array( 'start_time', 'appointment_date', 'date_time', 'scheduled_at', 'start', 'date' ),
                'duration'   => array( 'duration', 'duration_minutes', 'length', 'service_duration' ),
                'price'      => array( 'price', 'total', 'amount', 'service_price', 'gross_total' ),
                'status'     => array( 'status', 'appointment_status', 'booking_status', 'state' ),
            ),
            'packages' => array(
                'name'       => array( 'client_name', 'customer_name', 'client', 'customer', 'name' ),
                'email'      => array( 'client_email', 'customer_email', 'email' ),
                'phone'      => array( 'client_phone', 'customer_phone', 'phone', 'mobile' ),
                'package'    => array( 'package_name', 'package', 'membership', 'course', 'series', 'product' ),
                'remaining'  => array( 'remaining', 'sessions_remaining', 'visits_remaining', 'balance', 'uses_left', 'redemptions_left' ),
                'total'      => array( 'total_sessions', 'sessions', 'visits', 'quantity', 'uses' ),
                'expires'    => array( 'expires', 'expiry', 'expiry_date', 'valid_until', 'expires_at' ),
                'price'      => array( 'price', 'paid', 'amount', 'value' ),
            ),
            'giftcards' => array(
                'code'       => array( 'code', 'gift_card_code', 'voucher_code', 'card_number', 'certificate_number' ),
                'name'       => array( 'client_name', 'customer_name', 'recipient', 'issued_to', 'name' ),
                'email'      => array( 'client_email', 'customer_email', 'recipient_email', 'email' ),
                'balance'    => array( 'balance', 'remaining_balance', 'remaining', 'current_value', 'value_remaining' ),
                'original'   => array( 'original_value', 'initial_value', 'face_value', 'amount', 'value' ),
                'expires'    => array( 'expires', 'expiry', 'expiry_date', 'valid_until' ),
            ),
        );
        return $maps[ $entity ] ?? array();
    }

    /** Fields that must resolve to something or the row is meaningless. */
    private static function required_fields( $entity ) {
        $req = array(
            'clients'   => array( array( 'name', 'first_name' ), array( 'email', 'phone' ) ),
            'bookings'  => array( array( 'name', 'email', 'phone' ), array( 'start' ) ),
            'packages'  => array( array( 'name', 'email', 'phone' ), array( 'package' ) ),
            'giftcards' => array( array( 'code' ), array( 'balance', 'original' ) ),
        );
        return $req[ $entity ] ?? array();
    }

    /* ---------------------------------------------------------- CSV input */

    private static function normalize_header( $header ) {
        $header = preg_replace( '/^\xEF\xBB\xBF/', '', (string) $header );
        $header = strtolower( trim( $header ) );
        $header = str_replace( array( ' ', '-', '/', '.', '(', ')' ), '_', $header );
        return preg_replace( '/_+/', '_', preg_replace( '/[^a-z0-9_]/', '', $header ) );
    }

    private static function read_upload() {
        // The upload array cannot be text-sanitized; the capability check, the
        // nonce, and the size/extension/MIME checks below are the validation.
        $file = isset( $_FILES['import_file'] ) ? $_FILES['import_file'] : array(); // phpcs:ignore WordPress.Security.NonceVerification.Missing,WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
        if ( empty( $file['tmp_name'] ) || ! is_uploaded_file( $file['tmp_name'] ) ) {
            throw new RuntimeException( __( 'Choose the CSV you exported, then try again.', 'yatoon-booking-system' ) );
        }
        if ( UPLOAD_ERR_OK !== (int) $file['error'] ) {
            throw new RuntimeException( __( 'The file did not upload completely. Try again.', 'yatoon-booking-system' ) );
        }
        if ( (int) $file['size'] > self::MAX_FILE_BYTES ) {
            throw new RuntimeException( __( 'That file is larger than 12 MB. Split the export and import it in parts.', 'yatoon-booking-system' ) );
        }
        $clean_name = sanitize_file_name( (string) $file['name'] );
        if ( 'csv' !== strtolower( (string) pathinfo( $clean_name, PATHINFO_EXTENSION ) ) ) {
            throw new RuntimeException( __( 'Only .csv files can be imported. Most systems offer CSV next to Excel in their export screen.', 'yatoon-booking-system' ) );
        }
        $checked = wp_check_filetype_and_ext( $file['tmp_name'], $clean_name, array( 'csv' => 'text/csv' ) );
        if ( ! empty( $checked['ext'] ) && 'csv' !== $checked['ext'] ) {
            throw new RuntimeException( __( 'That file is not a CSV.', 'yatoon-booking-system' ) );
        }

        $handle = fopen( $file['tmp_name'], 'rb' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
        if ( ! $handle ) {
            throw new RuntimeException( __( 'The file could not be opened.', 'yatoon-booking-system' ) );
        }
        $headers = fgetcsv( $handle );
        if ( ! is_array( $headers ) ) {
            fclose( $handle ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
            throw new RuntimeException( __( 'The first row of the CSV should be column names.', 'yatoon-booking-system' ) );
        }
        $raw_headers = array_map( 'strval', $headers );
        $headers     = array_map( array( __CLASS__, 'normalize_header' ), $raw_headers );

        $rows = array();
        while ( ( $values = fgetcsv( $handle ) ) !== false && count( $rows ) < self::MAX_ROWS ) {
            if ( 1 === count( $values ) && ( null === $values[0] || '' === trim( (string) $values[0] ) ) ) { continue; }
            $values = array_pad( $values, count( $headers ), '' );
            $rows[] = array_combine( $headers, array_slice( $values, 0, count( $headers ) ) );
        }
        fclose( $handle ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose

        return array( 'headers' => $headers, 'raw_headers' => $raw_headers, 'rows' => $rows, 'file_name' => $clean_name );
    }

    /** Resolve one logical field from a row, honouring any manual override. */
    private static function field( $row, $entity, $key, $overrides = array() ) {
        if ( ! empty( $overrides[ $key ] ) && isset( $row[ $overrides[ $key ] ] ) ) {
            return trim( (string) $row[ $overrides[ $key ] ] );
        }
        foreach ( (array) ( self::field_aliases( $entity )[ $key ] ?? array() ) as $alias ) {
            $alias = self::normalize_header( $alias );
            if ( isset( $row[ $alias ] ) && '' !== trim( (string) $row[ $alias ] ) ) {
                return trim( (string) $row[ $alias ] );
            }
        }
        return '';
    }

    /** Which logical fields were matched, and which columns went unused. */
    private static function analyse( $headers, $entity ) {
        $matched = array();
        $used    = array();
        foreach ( self::field_aliases( $entity ) as $key => $aliases ) {
            foreach ( $aliases as $alias ) {
                $alias = self::normalize_header( $alias );
                if ( in_array( $alias, $headers, true ) ) {
                    $matched[ $key ] = $alias;
                    $used[] = $alias;
                    break;
                }
            }
        }
        return array( 'matched' => $matched, 'unused' => array_values( array_diff( $headers, $used ) ) );
    }

    /* ------------------------------------------------------ preview / commit */

    public static function handle_preview() {
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_die( esc_html__( 'Permission denied.', 'yatoon-booking-system' ) );
        }
        check_admin_referer( 'yatoon_saas_preview' );
        $source = sanitize_key( wp_unslash( $_POST['source'] ?? 'other' ) );
        $entity = sanitize_key( wp_unslash( $_POST['entity'] ?? 'clients' ) );
        if ( ! isset( self::sources()[ $source ] ) ) { $source = 'other'; }
        if ( ! isset( self::entities()[ $entity ] ) ) { $entity = 'clients'; }

        try {
            $parsed = self::read_upload();
            $token  = wp_generate_password( 20, false );
            set_transient( self::stage_key( $token ), array(
                'source' => $source,
                'entity' => $entity,
                'parsed' => $parsed,
            ), self::STAGE_TTL );
            wp_safe_redirect( add_query_arg( array(
                'page'  => 'yatoon-migrate',
                'stage' => $token,
            ), admin_url( 'admin.php' ) ) );
            exit;
        } catch ( Throwable $e ) {
            set_transient( 'yatoon_saas_notice_' . get_current_user_id(), array( 'error' => true, 'message' => $e->getMessage() ), MINUTE_IN_SECONDS );
            wp_safe_redirect( admin_url( 'admin.php?page=yatoon-migrate' ) );
            exit;
        }
    }

    private static function stage_key( $token ) {
        return 'yatoon_saas_stage_' . get_current_user_id() . '_' . sanitize_key( $token );
    }

    public static function handle_commit() {
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_die( esc_html__( 'Permission denied.', 'yatoon-booking-system' ) );
        }
        check_admin_referer( 'yatoon_saas_commit' );
        $token = sanitize_key( wp_unslash( $_POST['stage'] ?? '' ) );
        $stage = get_transient( self::stage_key( $token ) );
        if ( ! is_array( $stage ) ) {
            set_transient( 'yatoon_saas_notice_' . get_current_user_id(), array( 'error' => true, 'message' => __( 'That preview expired. Upload the file again.', 'yatoon-booking-system' ) ), MINUTE_IN_SECONDS );
            wp_safe_redirect( admin_url( 'admin.php?page=yatoon-migrate' ) );
            exit;
        }

        $overrides = array();
        foreach ( (array) ( $_POST['map'] ?? array() ) as $field => $column ) { // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
            $field  = sanitize_key( $field );
            $column = self::normalize_header( wp_unslash( $column ) );
            if ( '' !== $column ) { $overrides[ $field ] = $column; }
        }

        $result = self::import( $stage['source'], $stage['entity'], $stage['parsed'], $overrides );
        delete_transient( self::stage_key( $token ) );

        set_transient( 'yatoon_saas_notice_' . get_current_user_id(), array(
            'message' => sprintf(
                /* translators: 1: created, 2: updated, 3: skipped. */
                __( 'Imported: %1$d added, %2$d updated, %3$d skipped. You can undo this from the history below.', 'yatoon-booking-system' ),
                $result['created'],
                $result['updated'],
                $result['skipped']
            ),
        ), MINUTE_IN_SECONDS );
        wp_safe_redirect( admin_url( 'admin.php?page=yatoon-migrate' ) );
        exit;
    }

    /* ------------------------------------------------------------- import */

    public static function import( $source, $entity, $parsed, $overrides = array() ) {
        global $wpdb;
        $rows = (array) ( $parsed['rows'] ?? array() );

        $wpdb->insert( $wpdb->prefix . 'yatoon_import_batches', array(
            'source'     => sanitize_key( $source ),
            'entity'     => sanitize_key( $entity ),
            'file_name'  => sanitize_text_field( $parsed['file_name'] ?? '' ),
            'status'     => 'imported',
            'created_by' => get_current_user_id(),
        ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        $batch_id = (int) $wpdb->insert_id;

        $result = array( 'created' => 0, 'updated' => 0, 'skipped' => 0 );
        foreach ( $rows as $row ) {
            $outcome = self::import_row( $batch_id, $source, $entity, (array) $row, $overrides );
            $result[ $outcome ] = ( $result[ $outcome ] ?? 0 ) + 1;
        }

        $wpdb->update( $wpdb->prefix . 'yatoon_import_batches', array(
            'rows_created' => $result['created'],
            'rows_updated' => $result['updated'],
            'rows_skipped' => $result['skipped'],
        ), array( 'id' => $batch_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery

        $result['batch_id'] = $batch_id;
        return $result;
    }

    /** Record what a batch touched so it can be undone. */
    private static function track( $batch_id, $table, $target_id, $action, $previous = null ) {
        global $wpdb;
        $wpdb->insert( $wpdb->prefix . 'yatoon_import_batch_rows', array(
            'batch_id'     => (int) $batch_id,
            'target_table' => $table,
            'target_id'    => (int) $target_id,
            'action'       => $action,
            'previous'     => null === $previous ? null : wp_json_encode( $previous ),
        ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
    }

    private static function has_required( $row, $entity, $overrides ) {
        foreach ( self::required_fields( $entity ) as $group ) {
            $ok = false;
            foreach ( $group as $key ) {
                if ( '' !== self::field( $row, $entity, $key, $overrides ) ) { $ok = true; break; }
            }
            if ( ! $ok ) { return false; }
        }
        return true;
    }

    /**
     * Parse a date from an export.
     *
     * Vendors export local wall-clock time in their own format. The value is
     * stored as a site-local string, matching how every other date in this
     * plugin is stored -- so it must be parsed in the site's zone, not UTC.
     */
    private static function parse_datetime( $value ) {
        $value = trim( (string) $value );
        if ( '' === $value ) { return ''; }
        $zone = function_exists( 'yatoon_timezone' ) ? yatoon_timezone() : wp_timezone();
        // The leading '!' matters: without it createFromFormat() fills any
        // field the format does not mention from the CURRENT time, so a
        // date-only cell like "2026-08-23" would import with whatever hour the
        // migration happened to run at.
        foreach ( array( '!Y-m-d H:i:s', '!Y-m-d H:i', '!Y-m-d', '!d/m/Y H:i', '!m/d/Y H:i', '!d/m/Y', '!m/d/Y', '!d M Y H:i', '!j M Y H:i' ) as $format ) {
            $dt = DateTime::createFromFormat( $format, $value, $zone );
            if ( $dt instanceof DateTime && ! array_sum( DateTime::getLastErrors() ?: array() ) ) {
                return $dt->format( 'Y-m-d H:i:s' );
            }
        }
        try {
            return ( new DateTime( $value, $zone ) )->format( 'Y-m-d H:i:s' );
        } catch ( Exception $e ) {
            return '';
        }
    }

    private static function find_client_id( $email, $phone ) {
        global $wpdb;
        $email = sanitize_email( $email );
        $digits = preg_replace( '/\D/', '', (string) $phone );
        if ( '' !== $digits ) {
            $id = (int) $wpdb->get_var( $wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}yatoon_clients WHERE REPLACE(REPLACE(REPLACE(customer_phone,' ',''),'-',''),'+','') LIKE %s LIMIT 1",
                '%' . $wpdb->esc_like( $digits ) . '%'
            ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            if ( $id ) { return $id; }
        }
        if ( '' !== $email ) {
            return (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}yatoon_clients WHERE customer_email=%s LIMIT 1", $email ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        }
        return 0;
    }

    /**
     * Find, or create, the service an imported appointment refers to.
     *
     * Created services are inactive: they exist so history joins resolve and
     * reporting adds up, not so a customer can book "Gel Removal (2019)".
     */
    private static function resolve_service( $batch_id, $source, $name, $duration, $price ) {
        global $wpdb;
        $name = sanitize_text_field( $name );
        if ( '' === $name ) { $name = __( 'Imported appointment', 'yatoon-booking-system' ); }
        $id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}yatoon_services WHERE name=%s ORDER BY is_active DESC, id ASC LIMIT 1", $name ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        if ( $id ) { return $id; }
        $wpdb->insert( $wpdb->prefix . 'yatoon_services', array(
            'square_id'        => 'import:' . sanitize_key( $source ) . ':svc:' . substr( md5( $name ), 0, 24 ),
            'name'             => $name,
            'duration_minutes' => $duration > 0 ? $duration : 60,
            'price'            => $price,
            'is_active'        => 0,
            'description'      => sprintf(
                /* translators: %s: the system the service came from. */
                __( 'Created automatically while importing history from %s. Switch it on if you still offer it.', 'yatoon-booking-system' ),
                self::sources()[ $source ] ?? $source
            ),
        ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        $id = (int) $wpdb->insert_id;
        self::track( $batch_id, 'yatoon_services', $id, 'created' );
        return $id;
    }

    /** Same idea for the person who did the work. */
    private static function resolve_staff( $batch_id, $source, $name ) {
        global $wpdb;
        $name = sanitize_text_field( $name );
        if ( '' === $name ) { return 0; }
        $id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}yatoon_staff WHERE name=%s ORDER BY is_active DESC, id ASC LIMIT 1", $name ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        if ( $id ) { return $id; }
        $wpdb->insert( $wpdb->prefix . 'yatoon_staff', array(
            'square_id'   => 'import:' . sanitize_key( $source ) . ':staff:' . substr( md5( $name ), 0, 22 ),
            'name'        => $name,
            'is_active'   => 0,
            'is_bookable' => 0,
        ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        $id = (int) $wpdb->insert_id;
        self::track( $batch_id, 'yatoon_staff', $id, 'created' );
        return $id;
    }

    private static function import_row( $batch_id, $source, $entity, $row, $overrides ) {
        global $wpdb;
        if ( ! self::has_required( $row, $entity, $overrides ) ) { return 'skipped'; }

        $get = function ( $key ) use ( $row, $entity, $overrides ) {
            return self::field( $row, $entity, $key, $overrides );
        };

        $name = $get( 'name' );
        if ( '' === $name ) {
            $name = trim( $get( 'first_name' ) . ' ' . $get( 'last_name' ) );
        }
        $email = sanitize_email( $get( 'email' ) );
        $phone = sanitize_text_field( $get( 'phone' ) );

        /* ------------------------------------------------------- clients */
        if ( 'clients' === $entity ) {
            if ( '' === $name || ( '' === $email && '' === $phone ) ) { return 'skipped'; }
            $existing_id = self::find_client_id( $email, $phone );
            $data = array(
                'customer_name'  => sanitize_text_field( $name ),
                'customer_email' => $email,
                'customer_phone' => $phone,
                'notes'          => sanitize_textarea_field( $get( 'notes' ) ),
            );
            if ( $existing_id ) {
                $before = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}yatoon_clients WHERE id=%d", $existing_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                // Never blank out a field the export left empty.
                $data = array_filter( $data, function ( $v ) { return '' !== $v; } );
                $wpdb->update( $wpdb->prefix . 'yatoon_clients', $data, array( 'id' => $existing_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                self::track( $batch_id, 'yatoon_clients', $existing_id, 'updated', $before );
                return 'updated';
            }
            $wpdb->insert( $wpdb->prefix . 'yatoon_clients', $data ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            self::track( $batch_id, 'yatoon_clients', (int) $wpdb->insert_id, 'created' );
            return 'created';
        }

        /* ------------------------------------------------------ bookings */
        if ( 'bookings' === $entity ) {
            $start = self::parse_datetime( $get( 'start' ) );
            if ( '' === $start ) { return 'skipped'; }
            $duration = max( 0, (int) $get( 'duration' ) );
            $end = $duration > 0
                ? gmdate( 'Y-m-d H:i:s', strtotime( $start . ' +' . $duration . ' minutes' ) )
                : gmdate( 'Y-m-d H:i:s', strtotime( $start . ' +60 minutes' ) );

            // An imported appointment must not look like a live one to the
            // availability engine: past history is recorded as completed, and
            // anything already cancelled keeps that status.
            $raw_status = strtolower( $get( 'status' ) );
            if ( false !== strpos( $raw_status, 'cancel' ) ) {
                $status = 'cancelled';
            } elseif ( false !== strpos( $raw_status, 'no' ) && false !== strpos( $raw_status, 'show' ) ) {
                $status = 'no_show';
            } else {
                $status = strtotime( $start ) < time() ? 'completed' : 'confirmed';
            }

            $external = 'import:' . sanitize_key( $source ) . ':' . md5( $start . '|' . $name . '|' . $get( 'service' ) );
            $dupe = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}yatoon_bookings WHERE square_booking_id=%s LIMIT 1", $external ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            if ( $dupe ) { return 'skipped'; }

            // yatoon_bookings stores service_id and staff_id; the familiar
            // service_name / staff_name are join aliases, not columns. An
            // export only gives us names, so resolve them to rows -- creating
            // the missing ones switched OFF, so historical services and former
            // employees do not turn up in the live booking form.
            $price      = max( 0, (float) preg_replace( '/[^0-9.\-]/', '', $get( 'price' ) ) );
            $service_id = self::resolve_service( $batch_id, $source, $get( 'service' ), $duration, $price );
            $staff_id   = self::resolve_staff( $batch_id, $source, $get( 'staff' ) );

            $wpdb->insert( $wpdb->prefix . 'yatoon_bookings', array(
                'customer_name'     => sanitize_text_field( $name ),
                'customer_email'    => $email,
                'customer_phone'    => $phone,
                'service_id'        => $service_id,
                'staff_id'          => $staff_id,
                'start_time'        => $start,
                'end_time'          => $end,
                'total_price'       => $price,
                'status'            => $status,
                'square_booking_id' => $external,
                'notes'             => sprintf(
                    /* translators: %s: the system the appointment came from. */
                    __( 'Imported from %s.', 'yatoon-booking-system' ),
                    self::sources()[ $source ] ?? $source
                ),
                'created_at'        => current_time( 'mysql' ),
            ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            self::track( $batch_id, 'yatoon_bookings', (int) $wpdb->insert_id, 'created' );
            return 'created';
        }

        /* ------------------------------------------------------ packages */
        if ( 'packages' === $entity ) {
            $package = sanitize_text_field( $get( 'package' ) );
            if ( '' === $package ) { return 'skipped'; }
            $remaining = (int) $get( 'remaining' );
            $total     = max( $remaining, (int) $get( 'total' ) );
            if ( $remaining <= 0 ) { return 'skipped'; } // Nothing left to honour.
            $client_id = self::find_client_id( $email, $phone );
            if ( ! $client_id && '' !== $name ) {
                $wpdb->insert( $wpdb->prefix . 'yatoon_clients', array(
                    'customer_name'  => sanitize_text_field( $name ),
                    'customer_email' => $email,
                    'customer_phone' => $phone,
                ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                $client_id = (int) $wpdb->insert_id;
                self::track( $batch_id, 'yatoon_clients', $client_id, 'created' );
            }
            if ( ! $client_id ) { return 'skipped'; }

            // The balance belongs to a package definition, so make sure one
            // exists with this name before attaching the customer's remaining
            // visits to it. Reusing a definition across rows keeps the
            // Packages screen readable instead of listing the same course once
            // per customer.
            $package_id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}yatoon_packages WHERE name=%s LIMIT 1", $package ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            if ( ! $package_id ) {
                $wpdb->insert( $wpdb->prefix . 'yatoon_packages', array(
                    'name'            => $package,
                    'description'     => sprintf(
                        /* translators: %s: the system the package came from. */
                        __( 'Imported from %s.', 'yatoon-booking-system' ),
                        self::sources()[ $source ] ?? $source
                    ),
                    'included_visits' => $total > 0 ? $total : $remaining,
                    'price'           => max( 0, (float) preg_replace( '/[^0-9.\-]/', '', $get( 'price' ) ) ),
                    'is_active'       => 1,
                ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                $package_id = (int) $wpdb->insert_id;
                self::track( $batch_id, 'yatoon_packages', $package_id, 'created' );
            }

            $expires = self::parse_datetime( $get( 'expires' ) );
            $wpdb->insert( $wpdb->prefix . 'yatoon_customer_packages', array(
                'package_id'       => $package_id,
                'customer_name'    => sanitize_text_field( $name ),
                'customer_email'   => $email,
                'customer_phone'   => $phone,
                'purchased_visits' => $total > 0 ? $total : $remaining,
                'remaining_visits' => $remaining,
                'paid_amount'      => max( 0, (float) preg_replace( '/[^0-9.\-]/', '', $get( 'price' ) ) ),
                // expires_at is a DATE column, so hand it a date.
                'expires_at'       => $expires ? substr( $expires, 0, 10 ) : null,
                'status'           => 'active',
                'notes'            => sprintf(
                    /* translators: %s: the system the balance came from. */
                    __( 'Balance carried over from %s.', 'yatoon-booking-system' ),
                    self::sources()[ $source ] ?? $source
                ),
            ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            self::track( $batch_id, 'yatoon_customer_packages', (int) $wpdb->insert_id, 'created' );
            return 'created';
        }

        /* ----------------------------------------------------- gift cards */
        $code = strtoupper( preg_replace( '/[^A-Za-z0-9\-]/', '', $get( 'code' ) ) );
        if ( '' === $code ) { return 'skipped'; }
        $balance  = (float) preg_replace( '/[^0-9.\-]/', '', $get( 'balance' ) );
        $original = (float) preg_replace( '/[^0-9.\-]/', '', $get( 'original' ) );
        if ( $balance <= 0 && $original <= 0 ) { return 'skipped'; }
        if ( $original <= 0 ) { $original = $balance; }

        $dupe = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}yatoon_gift_cards WHERE code=%s LIMIT 1", $code ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        if ( $dupe ) { return 'skipped'; }

        $wpdb->insert( $wpdb->prefix . 'yatoon_gift_cards', array(
            'code'            => $code,
            'initial_amount'  => $original,
            'balance'         => max( 0, $balance ),
            'recipient_name'  => sanitize_text_field( $name ),
            'recipient_email' => $email,
            // expires_at is a DATE column here too.
            'expires_at'      => ( $gc_expires = self::parse_datetime( $get( 'expires' ) ) ) ? substr( $gc_expires, 0, 10 ) : null,
            'status'          => 'active',
            'created_at'      => current_time( 'mysql' ),
        ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        self::track( $batch_id, 'yatoon_gift_cards', (int) $wpdb->insert_id, 'created' );
        return 'created';
    }

    /* ----------------------------------------------------------- rollback */

    public static function handle_rollback() {
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_die( esc_html__( 'Permission denied.', 'yatoon-booking-system' ) );
        }
        check_admin_referer( 'yatoon_saas_rollback' );
        $batch_id = absint( wp_unslash( $_POST['batch_id'] ?? 0 ) );
        $undone   = self::rollback( $batch_id );
        set_transient( 'yatoon_saas_notice_' . get_current_user_id(), array(
            'message' => sprintf(
                /* translators: %d: number of rows reversed. */
                __( 'Undone: %d rows were reversed.', 'yatoon-booking-system' ),
                $undone
            ),
        ), MINUTE_IN_SECONDS );
        wp_safe_redirect( admin_url( 'admin.php?page=yatoon-migrate' ) );
        exit;
    }

    /**
     * Reverse a batch: delete what it created, restore what it changed.
     *
     * Deliberately not wrapped in a single transaction. A partially reversed
     * batch that records its progress is recoverable; a transaction that times
     * out halfway through 20,000 rows and rolls back leaves the owner with no
     * idea what state their data is in.
     */
    public static function rollback( $batch_id ) {
        global $wpdb;
        $batch_id = absint( $batch_id );
        if ( ! $batch_id ) { return 0; }

        $rows = (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}yatoon_import_batch_rows WHERE batch_id=%d ORDER BY id DESC",
            $batch_id
        ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery

        // Only tables this importer writes to may be reversed. Anything else
        // in the payload is ignored rather than trusted.
        $allowed = array( 'yatoon_clients', 'yatoon_bookings', 'yatoon_services', 'yatoon_staff', 'yatoon_packages', 'yatoon_customer_packages', 'yatoon_gift_cards' );
        $undone  = 0;
        foreach ( $rows as $row ) {
            $table = (string) $row->target_table;
            if ( ! in_array( $table, $allowed, true ) ) { continue; }
            $full = $wpdb->prefix . $table;
            if ( 'created' === $row->action ) {
                $wpdb->delete( $full, array( 'id' => (int) $row->target_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                $undone++;
            } elseif ( 'updated' === $row->action && $row->previous ) {
                $before = json_decode( (string) $row->previous, true );
                if ( is_array( $before ) ) {
                    unset( $before['id'] );
                    $wpdb->update( $full, $before, array( 'id' => (int) $row->target_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                    $undone++;
                }
            }
        }

        $wpdb->delete( $wpdb->prefix . 'yatoon_import_batch_rows', array( 'batch_id' => $batch_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        $wpdb->update( $wpdb->prefix . 'yatoon_import_batches', array( 'status' => 'rolled_back' ), array( 'id' => $batch_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        return $undone;
    }

    /* --------------------------------------------------------- admin page */

    public static function add_menu() {
        add_submenu_page(
            'yatoon-booking-system',
            __( 'Move In From Another System', 'yatoon-booking-system' ),
            __( 'Move In', 'yatoon-booking-system' ),
            'manage_options',
            'yatoon-migrate',
            array( __CLASS__, 'render_page' )
        );
    }

    public static function render_page() {
        if ( ! current_user_can( 'manage_options' ) ) { return; }
        global $wpdb;

        $sources          = self::sources();
        $requested_source = isset( $_GET['source'] ) ? sanitize_key( wp_unslash( $_GET['source'] ) ) : 'fresha'; // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- display-only allowlisted selection.
        if ( ! isset( $sources[ $requested_source ] ) ) {
            $requested_source = 'fresha';
        }

        $notice = get_transient( 'yatoon_saas_notice_' . get_current_user_id() );
        if ( $notice ) { delete_transient( 'yatoon_saas_notice_' . get_current_user_id() ); }

        $token = isset( $_GET['stage'] ) ? sanitize_key( wp_unslash( $_GET['stage'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        $stage = $token ? get_transient( self::stage_key( $token ) ) : null;

        $batches = (array) $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}yatoon_import_batches ORDER BY id DESC LIMIT 20" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        ?>
        <div class="wrap">
            <h1><?php esc_html_e( 'Move In From Another System', 'yatoon-booking-system' ); ?></h1>

            <?php if ( is_array( $notice ) ) : ?>
                <div class="notice <?php echo empty( $notice['error'] ) ? 'notice-success' : 'notice-error'; ?> is-dismissible">
                    <p><?php echo esc_html( $notice['message'] ); ?></p>
                </div>
            <?php endif; ?>

            <?php if ( is_array( $stage ) ) : self::render_preview( $token, $stage ); else : ?>

                <p class="description" style="max-width:74ch">
                    <?php esc_html_e( 'Bring your customers, appointment history, remaining package visits and gift card balances across from Fresha, Booksy, Vagaro, or any system that exports a CSV. Nothing is written until you have seen a preview, and every import can be undone.', 'yatoon-booking-system' ); ?>
                </p>

                <form method="post" enctype="multipart/form-data" action="<?php echo esc_url( admin_url( 'admin-post.php' ) ); ?>" class="card" style="max-width:640px;padding:20px">
                    <input type="hidden" name="action" value="yatoon_saas_preview">
                    <?php wp_nonce_field( 'yatoon_saas_preview' ); ?>
                    <p>
                        <label for="yatoon-saas-source"><strong><?php esc_html_e( 'Where are you coming from?', 'yatoon-booking-system' ); ?></strong></label><br>
                        <select name="source" id="yatoon-saas-source" style="min-width:280px">
                            <?php foreach ( $sources as $key => $label ) : ?>
                                <option value="<?php echo esc_attr( $key ); ?>" <?php selected( $requested_source, $key ); ?>><?php echo esc_html( $label ); ?></option>
                            <?php endforeach; ?>
                        </select>
                    </p>
                    <p>
                        <label for="yatoon-saas-entity"><strong><?php esc_html_e( 'What is in this file?', 'yatoon-booking-system' ); ?></strong></label><br>
                        <select name="entity" id="yatoon-saas-entity" style="min-width:280px">
                            <?php foreach ( self::entities() as $key => $label ) : ?>
                                <option value="<?php echo esc_attr( $key ); ?>"><?php echo esc_html( $label ); ?></option>
                            <?php endforeach; ?>
                        </select>
                        <br><span class="description"><?php esc_html_e( 'Import one file at a time. Customers first, so appointments and packages can be matched to them.', 'yatoon-booking-system' ); ?></span>
                    </p>
                    <p>
                        <label for="yatoon-saas-file"><strong><?php esc_html_e( 'CSV file', 'yatoon-booking-system' ); ?></strong></label><br>
                        <input type="file" name="import_file" id="yatoon-saas-file" accept=".csv,text/csv" required>
                    </p>
                    <?php submit_button( __( 'Preview import', 'yatoon-booking-system' ), 'primary', 'submit', false ); ?>
                </form>

            <?php endif; ?>

            <h2 style="margin-top:32px"><?php esc_html_e( 'Import history', 'yatoon-booking-system' ); ?></h2>
            <table class="widefat striped">
                <thead><tr>
                    <th><?php esc_html_e( 'When', 'yatoon-booking-system' ); ?></th>
                    <th><?php esc_html_e( 'From', 'yatoon-booking-system' ); ?></th>
                    <th><?php esc_html_e( 'Contents', 'yatoon-booking-system' ); ?></th>
                    <th><?php esc_html_e( 'Result', 'yatoon-booking-system' ); ?></th>
                    <th></th>
                </tr></thead>
                <tbody>
                <?php if ( empty( $batches ) ) : ?>
                    <tr><td colspan="5"><?php esc_html_e( 'Nothing imported yet.', 'yatoon-booking-system' ); ?></td></tr>
                <?php endif; ?>
                <?php foreach ( $batches as $b ) : ?>
                    <tr>
                        <td><?php echo esc_html( function_exists( 'yatoon_format_local' ) ? yatoon_format_local( 'M j, Y g:i A', $b->created_at ) : $b->created_at ); ?></td>
                        <td><?php echo esc_html( self::sources()[ $b->source ] ?? $b->source ); ?><br><small><?php echo esc_html( $b->file_name ); ?></small></td>
                        <td><?php echo esc_html( self::entities()[ $b->entity ] ?? $b->entity ); ?></td>
                        <td><?php echo esc_html( sprintf(
                            /* translators: 1: added, 2: updated, 3: skipped. */
                            __( '%1$d added, %2$d updated, %3$d skipped', 'yatoon-booking-system' ),
                            (int) $b->rows_created, (int) $b->rows_updated, (int) $b->rows_skipped
                        ) ); ?></td>
                        <td>
                            <?php if ( 'imported' === $b->status ) : ?>
                                <form method="post" action="<?php echo esc_url( admin_url( 'admin-post.php' ) ); ?>" onsubmit="return confirm(<?php echo esc_attr( wp_json_encode( __( 'Undo this import? Rows it added will be deleted and rows it changed will be put back.', 'yatoon-booking-system' ) ) ); ?>);">
                                    <input type="hidden" name="action" value="yatoon_saas_rollback">
                                    <input type="hidden" name="batch_id" value="<?php echo esc_attr( $b->id ); ?>">
                                    <?php wp_nonce_field( 'yatoon_saas_rollback' ); ?>
                                    <button class="button button-small"><?php esc_html_e( 'Undo', 'yatoon-booking-system' ); ?></button>
                                </form>
                            <?php else : ?>
                                <span style="color:#8a8a8a"><?php esc_html_e( 'undone', 'yatoon-booking-system' ); ?></span>
                            <?php endif; ?>
                        </td>
                    </tr>
                <?php endforeach; ?>
                </tbody>
            </table>
        </div>
        <?php
    }

    private static function render_preview( $token, $stage ) {
        $entity  = $stage['entity'];
        $parsed  = $stage['parsed'];
        $headers = (array) $parsed['headers'];
        $rows    = (array) $parsed['rows'];
        $info    = self::analyse( $headers, $entity );
        $labels  = self::field_aliases( $entity );
        ?>
        <h2><?php echo esc_html( sprintf(
            /* translators: 1: number of rows, 2: file name. */
            __( '%1$d rows found in %2$s', 'yatoon-booking-system' ),
            count( $rows ),
            $parsed['file_name']
        ) ); ?></h2>

        <form method="post" action="<?php echo esc_url( admin_url( 'admin-post.php' ) ); ?>">
            <input type="hidden" name="action" value="yatoon_saas_commit">
            <input type="hidden" name="stage" value="<?php echo esc_attr( $token ); ?>">
            <?php wp_nonce_field( 'yatoon_saas_commit' ); ?>

            <h3><?php esc_html_e( 'Columns', 'yatoon-booking-system' ); ?></h3>
            <table class="widefat striped" style="max-width:760px">
                <thead><tr>
                    <th><?php esc_html_e( 'We need', 'yatoon-booking-system' ); ?></th>
                    <th><?php esc_html_e( 'Matched to', 'yatoon-booking-system' ); ?></th>
                </tr></thead>
                <tbody>
                <?php foreach ( array_keys( $labels ) as $field ) :
                    $matched = $info['matched'][ $field ] ?? ''; ?>
                    <tr>
                        <td><code><?php echo esc_html( $field ); ?></code></td>
                        <td>
                            <?php if ( $matched ) : ?>
                                <strong><?php echo esc_html( $matched ); ?></strong>
                            <?php else : ?>
                                <select name="map[<?php echo esc_attr( $field ); ?>]">
                                    <option value=""><?php esc_html_e( '— not in this file —', 'yatoon-booking-system' ); ?></option>
                                    <?php foreach ( $headers as $h ) : ?>
                                        <option value="<?php echo esc_attr( $h ); ?>"><?php echo esc_html( $h ); ?></option>
                                    <?php endforeach; ?>
                                </select>
                            <?php endif; ?>
                        </td>
                    </tr>
                <?php endforeach; ?>
                </tbody>
            </table>

            <?php if ( ! empty( $info['unused'] ) ) : ?>
                <p class="description" style="margin-top:10px">
                    <?php esc_html_e( 'Columns in your file that will not be imported:', 'yatoon-booking-system' ); ?>
                    <code><?php echo esc_html( implode( ', ', array_slice( $info['unused'], 0, 25 ) ) ); ?></code>
                </p>
            <?php endif; ?>

            <h3><?php esc_html_e( 'First five rows', 'yatoon-booking-system' ); ?></h3>
            <div style="overflow-x:auto">
                <table class="widefat striped">
                    <thead><tr><?php foreach ( $headers as $h ) : ?><th><?php echo esc_html( $h ); ?></th><?php endforeach; ?></tr></thead>
                    <tbody>
                    <?php foreach ( array_slice( $rows, 0, 5 ) as $r ) : ?>
                        <tr><?php foreach ( $headers as $h ) : ?><td><?php echo esc_html( mb_substr( (string) ( $r[ $h ] ?? '' ), 0, 40 ) ); ?></td><?php endforeach; ?></tr>
                    <?php endforeach; ?>
                    </tbody>
                </table>
            </div>

            <p style="margin-top:18px">
                <?php submit_button( __( 'Import these rows', 'yatoon-booking-system' ), 'primary', 'submit', false ); ?>
                <a class="button" href="<?php echo esc_url( admin_url( 'admin.php?page=yatoon-migrate' ) ); ?>"><?php esc_html_e( 'Start over', 'yatoon-booking-system' ); ?></a>
            </p>
        </form>
        <?php
    }
}
