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

class YATOON_Activator {

    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() {
        global $wpdb;

        $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';
            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' => wp_timezone_string() ? wp_timezone_string() : 'America/New_York',
                '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.',
                '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.',
                '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',
                'description' => 'Modern nail enhancement with strength, shaping, and a polished finish.',
                'duration_minutes' => 60,
                'price' => 45.00,
                'category' => 'Enhancements',
                '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',
                'description' => 'Builder Gel experience for lasting strength with a smooth natural look.',
                'duration_minutes' => 55,
                'price' => 55.00,
                'category' => 'Enhancements',
                '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',
                'description' => 'Natural-looking powder service with extra strength and long-lasting color.',
                'duration_minutes' => 55,
                'price' => 45.00,
                'category' => 'Enhancements',
                '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',
                'description' => 'Cuticle care, shaping, premium gel color, and a glossy salon finish.',
                'duration_minutes' => 45,
                'price' => 38.00,
                'category' => 'Manicures',
                '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',
                'description' => 'Warm soak, exfoliation, massage, detailed nail care, and polish.',
                'duration_minutes' => 60,
                'price' => 60.00,
                'category' => 'Pedicures',
                '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',
                'description' => 'Accent art, chrome, French tips, or detail work added to a manicure.',
                'duration_minutes' => 20,
                'price' => 15.00,
                'category' => 'Nail Art',
                'is_active' => 1,
                'allow_staff_select' => 0,
                'allow_design_upload' => 1,
                'sort_order' => 6,
                'is_featured' => 0,
            ),
        );

        $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');
    }

    private static function grant_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 ) {
            $admin->add_cap( $cap );
        }
    }
    
    public static function activate() {
        global $wpdb;

        yatoon_record_onboarding_milestone( 'activated' );
        add_filter('cron_schedules', array(__CLASS__, 'cron_schedules'));
        self::grant_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 IF NOT EXISTS $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,
            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 IF NOT EXISTS $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 IF NOT EXISTS $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_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)
        ) $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 IF NOT EXISTS $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 IF NOT EXISTS $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
        $table_bookings = $wpdb->prefix . 'yatoon_bookings'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
        $sql_bookings = "CREATE TABLE IF NOT EXISTS $table_bookings (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            square_booking_id varchar(255),
            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) 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,
            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,
            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 staff_id (staff_id),
            KEY start_time (start_time),
            KEY square_booking_id (square_booking_id),
            KEY reminder_sent (reminder_sent),
            KEY review_requested (review_requested),
            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 IF NOT EXISTS $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 IF NOT EXISTS $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 IF NOT EXISTS $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 IF NOT EXISTS $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, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $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(
            'consent_accepted'      => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN consent_accepted tinyint(1) DEFAULT 0 AFTER google_event_id",
            'consent_signature'     => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN consent_signature varchar(255) DEFAULT NULL AFTER consent_accepted",
            'consent_signed_at'     => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN consent_signed_at datetime DEFAULT NULL AFTER consent_signature",
            'consent_text_snapshot' => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN consent_text_snapshot text DEFAULT NULL AFTER consent_signed_at",
            'policy_acknowledged'   => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN policy_acknowledged tinyint(1) DEFAULT 0 AFTER consent_text_snapshot",
            'policy_acknowledged_at'=> "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN policy_acknowledged_at datetime DEFAULT NULL AFTER policy_acknowledged",
            'policy_text_snapshot'  => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN policy_text_snapshot text DEFAULT NULL AFTER policy_acknowledged_at",
            'payment_status'        => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN payment_status varchar(30) DEFAULT 'unpaid' AFTER policy_text_snapshot",
            'payment_note'          => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN payment_note text DEFAULT NULL AFTER payment_status",
            'payment_updated_at'    => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN payment_updated_at datetime DEFAULT NULL AFTER payment_note",
            'design_attachment_id'  => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN design_attachment_id bigint(20) DEFAULT NULL AFTER notes",
            'design_image_url'      => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN design_image_url varchar(500) DEFAULT NULL AFTER design_attachment_id",
            'design_upload_token'   => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN design_upload_token varchar(64) DEFAULT NULL AFTER design_image_url",
            'manage_token'          => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN manage_token varchar(64) DEFAULT NULL AFTER payment_updated_at",
            'manage_token_expires'  => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN manage_token_expires datetime DEFAULT NULL AFTER manage_token",
            'deposit_amount'        => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN deposit_amount decimal(10,2) DEFAULT NULL AFTER manage_token_expires",
            'deposit_paid'          => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN deposit_paid tinyint(1) DEFAULT 0 AFTER deposit_amount",
            'deposit_intent_id'     => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN deposit_intent_id varchar(255) DEFAULT NULL AFTER deposit_paid",
            'reminder_sent'         => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN reminder_sent tinyint(1) DEFAULT 0",
            'review_requested'      => "ALTER TABLE {$wpdb->prefix}yatoon_bookings ADD COLUMN review_requested tinyint(1) DEFAULT 0",
        );
        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",
        );
        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.');
        // 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);
        
        // 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');
        
        // Flush rewrite rules
        flush_rewrite_rules();
    }
}

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

// 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 IF NOT EXISTS $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,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY service_id (service_id),
        KEY preferred_date (preferred_date),
        KEY status (status)
    ) $charset_collate;";
    
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
}
add_action('plugins_loaded', 'yatoon_create_waitlist_table', 5);

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",
    );
    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);
        }
    }
    add_option('yatoon_waitlist_auto_notify', '1');
}
add_action('plugins_loaded', 'yatoon_waitlist_table_migrations', 6);

// -- Reviews table ------------------------------------------------------------
function yatoon_create_reviews_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'yatoon_reviews';
    $charset = $wpdb->get_charset_collate();
    $sql = "CREATE TABLE IF NOT EXISTS $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);
}
add_action('plugins_loaded', 'yatoon_create_reviews_table', 5);

// -- Recurring appointments table ---------------------------------------------
function yatoon_create_recurring_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'yatoon_recurring';
    $charset = $wpdb->get_charset_collate();
    $sql = "CREATE TABLE IF NOT EXISTS $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);
}
add_action('plugins_loaded', 'yatoon_create_recurring_table', 5);

// -- 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 IF NOT EXISTS $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);
}
add_action('plugins_loaded', 'yatoon_create_service_images_table', 5);

// -- 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');
}
add_action('plugins_loaded', 'yatoon_catalog_defaults', 7);

// -- 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 IF NOT EXISTS $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);
}
add_action('plugins_loaded', 'yatoon_create_form_fields_table', 5);

// -- Packages table ------------------------------------------------------------
function yatoon_create_packages_table() {
    global $wpdb;
    $table   = $wpdb->prefix . 'yatoon_packages';
    $charset = $wpdb->get_charset_collate();
    $sql = "CREATE TABLE IF NOT EXISTS $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);
}
add_action('plugins_loaded', 'yatoon_create_packages_table', 5);

// -- 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 IF NOT EXISTS $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);
}
add_action('plugins_loaded', 'yatoon_create_package_redemptions_table', 5);

// -- 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);
    }
}
add_action('plugins_loaded', 'yatoon_bookings_batch_cd_migrations', 6);

// 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');
}
add_action('plugins_loaded', 'yatoon_tip_defaults', 7);

// -- 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 IF NOT EXISTS $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 IF NOT EXISTS $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);
}
add_action('plugins_loaded', 'yatoon_create_gift_cards_tables', 5);
