<?php
if (!defined('ABSPATH')) exit;

if (!class_exists('VMO_Business_Flow_Engine')) {
    class VMO_Business_Flow_Engine {
        private static $instance = null;
        private $columns_cache = array();

        public static function get_instance() {
            if (self::$instance === null) {
                self::$instance = new self();
            }
            return self::$instance;
        }

        public function init() {
            add_action('init', array($this, 'ensure_tables'));
            add_action('vmo_checkout_completed', array($this, 'handle_checkout_completed'), 20, 2);
            add_action('wp_ajax_vmo_get_business_flow_queue', array($this, 'ajax_get_queue'));
            add_action('wp_ajax_vmo_mark_business_flow_status', array($this, 'ajax_mark_status'));
            add_action('wp_ajax_vmo_mark_business_flow_complete', array($this, 'ajax_mark_status'));
        }

        public function ensure_tables() {
            static $done = false;
            if ($done) {
                return;
            }
            $done = true;

            global $wpdb;
            if (!is_object($wpdb)) {
                return;
            }

            $upgrade = defined('ABSPATH') ? ABSPATH . 'wp-admin/includes/upgrade.php' : '';
            if ($upgrade && file_exists($upgrade)) {
                require_once $upgrade;
            }
            if (!function_exists('dbDelta')) {
                return;
            }

            $charset = method_exists($wpdb, 'get_charset_collate') ? $wpdb->get_charset_collate() : '';
            $instances = $wpdb->prefix . 'vmo_business_flow_instances';
            $steps = $wpdb->prefix . 'vmo_business_flow_steps';
            $tasks = $wpdb->prefix . 'vmo_fulfillment_tasks';
            $links = $wpdb->prefix . 'vmo_transaction_links';
            $flow_links = $wpdb->prefix . 'vmo_transaction_flow_links';

            dbDelta("CREATE TABLE {$instances} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                user_id BIGINT UNSIGNED NOT NULL,
                flow_key VARCHAR(120) NOT NULL DEFAULT '',
                flow_type VARCHAR(120) NOT NULL DEFAULT 'custom_business_flow',
                source_type VARCHAR(80) NOT NULL DEFAULT '',
                source_id BIGINT UNSIGNED DEFAULT NULL,
                payment_order_id BIGINT UNSIGNED DEFAULT NULL,
                stripe_checkout_session_id VARCHAR(255) DEFAULT NULL,
                payment_status VARCHAR(60) DEFAULT 'paid',
                current_status VARCHAR(80) DEFAULT 'awaiting_fulfillment',
                completion_status VARCHAR(80) DEFAULT 'completed',
                completion_definition TEXT DEFAULT NULL,
                modules_required LONGTEXT DEFAULT NULL,
                visitor_steps LONGTEXT DEFAULT NULL,
                owner_steps LONGTEXT DEFAULT NULL,
                status_sequence LONGTEXT DEFAULT NULL,
                notification_plan LONGTEXT DEFAULT NULL,
                dashboard_mapping LONGTEXT DEFAULT NULL,
                cfo_mapping LONGTEXT DEFAULT NULL,
                customer_name VARCHAR(255) DEFAULT NULL,
                customer_email VARCHAR(255) DEFAULT NULL,
                amount_total BIGINT UNSIGNED DEFAULT 0,
                currency VARCHAR(10) DEFAULT 'usd',
                metadata LONGTEXT DEFAULT NULL,
                completed_at DATETIME DEFAULT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                UNIQUE KEY uniq_source (source_type, source_id),
                KEY idx_user_status (user_id, current_status),
                KEY idx_payment_order (payment_order_id),
                KEY idx_session (stripe_checkout_session_id)
            ) {$charset};");

            dbDelta("CREATE TABLE {$steps} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                instance_id BIGINT UNSIGNED NOT NULL,
                user_id BIGINT UNSIGNED NOT NULL,
                step_key VARCHAR(120) NOT NULL DEFAULT '',
                label VARCHAR(255) NOT NULL DEFAULT '',
                actor VARCHAR(60) DEFAULT 'owner',
                status VARCHAR(80) DEFAULT 'pending',
                sort_order INT DEFAULT 0,
                metadata LONGTEXT DEFAULT NULL,
                completed_at DATETIME DEFAULT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY idx_instance (instance_id),
                KEY idx_user (user_id),
                KEY idx_status (status)
            ) {$charset};");

            dbDelta("CREATE TABLE {$tasks} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                user_id BIGINT UNSIGNED NOT NULL,
                flow_instance_id BIGINT UNSIGNED DEFAULT NULL,
                payment_order_id BIGINT UNSIGNED DEFAULT NULL,
                task_type VARCHAR(120) DEFAULT 'business_flow_completion',
                current_status VARCHAR(80) DEFAULT 'awaiting_fulfillment',
                owner_action TEXT DEFAULT NULL,
                customer_name VARCHAR(255) DEFAULT NULL,
                customer_email VARCHAR(255) DEFAULT NULL,
                amount_total BIGINT UNSIGNED DEFAULT 0,
                currency VARCHAR(10) DEFAULT 'usd',
                shipping_order_id BIGINT UNSIGNED DEFAULT NULL,
                internal_order_id BIGINT UNSIGNED DEFAULT NULL,
                metadata LONGTEXT DEFAULT NULL,
                completed_at DATETIME DEFAULT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY idx_user_status (user_id, current_status),
                KEY idx_payment_order (payment_order_id),
                KEY idx_flow (flow_instance_id)
            ) {$charset};");

            dbDelta("CREATE TABLE {$links} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                user_id BIGINT UNSIGNED NOT NULL,
                payment_order_id BIGINT UNSIGNED DEFAULT NULL,
                business_flow_instance_id BIGINT UNSIGNED DEFAULT NULL,
                fulfillment_task_id BIGINT UNSIGNED DEFAULT NULL,
                shipping_order_id BIGINT UNSIGNED DEFAULT NULL,
                internal_order_id BIGINT UNSIGNED DEFAULT NULL,
                stripe_checkout_session_id VARCHAR(255) DEFAULT NULL,
                stripe_payment_intent_id VARCHAR(255) DEFAULT NULL,
                source_table VARCHAR(120) DEFAULT 'vmo_store_orders',
                source_id BIGINT UNSIGNED DEFAULT NULL,
                metadata LONGTEXT DEFAULT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                UNIQUE KEY uniq_payment_order (payment_order_id),
                KEY idx_user (user_id),
                KEY idx_flow (business_flow_instance_id),
                KEY idx_shipping (shipping_order_id),
                KEY idx_internal (internal_order_id)
            ) {$charset};");

            dbDelta("CREATE TABLE {$flow_links} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                user_id BIGINT UNSIGNED NOT NULL,
                payment_order_id BIGINT UNSIGNED DEFAULT NULL,
                business_flow_instance_id BIGINT UNSIGNED DEFAULT NULL,
                fulfillment_task_id BIGINT UNSIGNED DEFAULT NULL,
                shipping_order_id BIGINT UNSIGNED DEFAULT NULL,
                internal_order_id BIGINT UNSIGNED DEFAULT NULL,
                stripe_checkout_session_id VARCHAR(255) DEFAULT NULL,
                stripe_payment_intent_id VARCHAR(255) DEFAULT NULL,
                source_table VARCHAR(120) DEFAULT 'vmo_store_orders',
                source_id BIGINT UNSIGNED DEFAULT NULL,
                metadata LONGTEXT DEFAULT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                UNIQUE KEY uniq_payment_order (payment_order_id),
                KEY idx_user (user_id),
                KEY idx_flow (business_flow_instance_id),
                KEY idx_shipping (shipping_order_id),
                KEY idx_internal (internal_order_id)
            ) {$charset};");

            $this->ensure_store_order_columns();
        }

        public static function design_flow_plan_from_context(array $metadata, array $order = array(), array $session_data = array()) {
            $ai_plan = self::extract_ai_plan($metadata);
            if ($ai_plan) {
                return self::normalize_plan($ai_plan, $metadata);
            }

            $text = strtolower(wp_json_encode(array($metadata, $order, $session_data)));
            $product_type = self::key($metadata['product_type'] ?? $metadata['type'] ?? '');
            $checkout_type = self::key($metadata['checkout_type'] ?? '');
            $modules = array('payment', 'crm', 'email_notification', 'invoice_receipt', 'dashboard_kpi', 'fulfillment_status');
            $visitor_steps = array('view offer', 'choose option', 'checkout with VMO Pay', 'receive confirmation');
            $owner_steps = array('review paid transaction');
            $statuses = array('paid', 'owner_review', 'completed');
            $initial_status = 'owner_review';
            $flow_type = 'custom_business_flow';
            $completion_definition = 'Owner confirms the AI-defined business outcome is complete.';
            $owner_action = 'Review this paid transaction and mark the business outcome complete when delivered.';
            $customer_next_step = 'Your order is confirmed. The business will follow up with the next step.';
            $requires_owner_action = true;
            $requires_shipping_queue = false;
            $auto_complete = false;

            $is_reservation = preg_match('/reservation|reserve|table|party_size|guest|served|no_show|no-show/', $text);
            $is_booking = preg_match('/booking|appointment|schedule|consultation|deposit|service_date|service_time/', $text);
            $is_ticket = preg_match('/ticket|event|attendee|qr|check.?in|rsvp/', $text);
            $is_donation = preg_match('/donation|donor|campaign|cause|fundraiser|nonprofit/', $text);
            $is_digital = in_array($product_type, array('downloadable', 'digital', 'course', 'membership'), true) || preg_match('/download_url|digital_access|course|license|access link|member/', $text);
            $is_subscription = $product_type === 'subscription' || $checkout_type === 'subscription' || preg_match('/subscription|membership|recurring/', $text);
            $is_physical = in_array($product_type, array('item', 'product', 'physical'), true) || !empty($metadata['shipping_required']) || preg_match('/shipping|required|ship|delivery|package|pickup|fulfillment/', $text);

            if ($is_donation) {
                $flow_type = 'donation_flow';
                $modules = array_merge($modules, array('donation', 'campaign_tracking', 'thank_you_email'));
                $visitor_steps = array('choose donation amount', 'pay securely', 'receive tax receipt or thank-you confirmation');
                $owner_steps = array('review donor record', 'send impact update if appropriate');
                $statuses = array('donated', 'receipt_sent', 'impact_update_sent', 'completed');
                $initial_status = 'receipt_sent';
                $completion_definition = 'Donation is recorded, receipt is sent, and campaign totals are updated.';
                $owner_action = 'Review the donor record and schedule any thank-you or impact follow-up.';
                $customer_next_step = 'Your donation receipt is being sent now.';
                $auto_complete = true;
                $requires_owner_action = false;
            } elseif ($is_ticket) {
                $flow_type = 'ticketing_flow';
                $modules = array_merge($modules, array('ticketing', 'event_check_in'));
                $visitor_steps = array('choose ticket', 'pay', 'receive ticket or confirmation', 'check in at event');
                $owner_steps = array('monitor attendee list', 'scan or mark attendee checked in', 'close event attendance');
                $statuses = array('purchased', 'issued', 'checked_in', 'attended', 'completed');
                $initial_status = 'issued';
                $completion_definition = 'Ticket is issued and attendance is checked in or marked attended.';
                $owner_action = 'Confirm the ticket was issued and mark attendance when the attendee checks in.';
                $customer_next_step = 'Your ticket or event confirmation is being prepared.';
            } elseif ($is_reservation) {
                $flow_type = 'reservation_flow';
                $modules = array_merge($modules, array('reservation', 'calendar_queue'));
                $visitor_steps = array('select reservation details', 'pay or reserve', 'receive confirmation');
                $owner_steps = array('confirm reservation', 'prepare service', 'mark served, no-show, or cancelled');
                $statuses = array('reserved', 'confirmed', 'preparing', 'completed', 'no_show', 'cancelled');
                $initial_status = 'reserved';
                $completion_definition = 'Reservation is confirmed and then marked served/completed, no-show, or cancelled.';
                $owner_action = 'Confirm and prepare this reservation, then mark it completed, no-show, or cancelled.';
                $customer_next_step = 'Your reservation request is received. Watch for confirmation details.';
            } elseif ($is_booking || $product_type === 'service') {
                $flow_type = 'service_booking_flow';
                $modules = array_merge($modules, array('booking', 'appointment', 'service_delivery'));
                $visitor_steps = array('choose service package', 'pay deposit or full amount', 'schedule or request time', 'receive confirmation');
                $owner_steps = array('review intake', 'confirm appointment', 'deliver service', 'mark completed');
                $statuses = array('paid_deposit', 'scheduled', 'confirmed', 'in_progress', 'completed');
                $initial_status = 'paid_deposit';
                $completion_definition = 'Service is scheduled, delivered, and marked completed by the owner.';
                $owner_action = 'Schedule or confirm the service, then mark it completed after delivery.';
                $customer_next_step = 'Your service request is confirmed. The business will schedule or confirm details.';
            } elseif ($is_subscription) {
                $flow_type = 'subscription_activation_flow';
                $modules = array_merge($modules, array('subscription', 'membership', 'digital_access'));
                $visitor_steps = array('choose subscription', 'pay', 'receive activation confirmation');
                $owner_steps = array('monitor subscription status', 'support onboarding if needed');
                $statuses = array('paid', 'activated', 'active', 'completed');
                $initial_status = 'activated';
                $completion_definition = 'Subscription is activated and available to the customer.';
                $owner_action = 'Confirm subscription access is active and help with onboarding if needed.';
                $customer_next_step = 'Your subscription is being activated.';
                $auto_complete = true;
                $requires_owner_action = false;
            } elseif ($is_digital) {
                $flow_type = 'digital_access_flow';
                $modules = array_merge($modules, array('digital_access'));
                $visitor_steps = array('view digital product', 'pay', 'receive access link');
                $owner_steps = array('monitor access delivery', 'support buyer if needed');
                $statuses = array('paid', 'access_granted', 'completed');
                $initial_status = 'access_granted';
                $completion_definition = 'Digital access is granted automatically after payment.';
                $owner_action = 'Delivery/access should be automatic; confirm the access link remains available.';
                $customer_next_step = 'Your access link or digital delivery instructions are being sent now.';
                $auto_complete = true;
                $requires_owner_action = false;
            } elseif ($is_physical) {
                $flow_type = 'product_fulfillment_flow';
                $modules = array_merge($modules, array('ecommerce', 'shipping_fulfillment'));
                $visitor_steps = array('view product', 'pay', 'receive order confirmation', 'receive shipping or pickup update');
                $owner_steps = array('package order', 'ship or prepare pickup/delivery', 'mark delivered or fulfilled');
                $statuses = array('awaiting_fulfillment', 'preparing', 'packed', 'shipped', 'delivered', 'fulfilled', 'completed', 'cancelled', 'refunded');
                $initial_status = 'awaiting_fulfillment';
                $completion_definition = 'Product is delivered, picked up, or otherwise marked fulfilled by the owner.';
                $owner_action = 'Package and ship this order, prepare pickup/delivery, or mark it fulfilled when complete.';
                $customer_next_step = 'Your order is confirmed. You will receive fulfillment or delivery updates.';
                $requires_shipping_queue = true;
            }

            return self::normalize_plan(array(
                'business_flow_type' => $flow_type,
                'modules_required' => array_values(array_unique($modules)),
                'visitor_steps' => $visitor_steps,
                'owner_steps' => $owner_steps,
                'payment_actions' => array('stripe_checkout_completed', 'local_order_paid', 'business_flow_instance_created'),
                'customer_confirmations' => array('buyer_receipt', 'order_confirmation', $customer_next_step),
                'completion_statuses' => $statuses,
                'initial_status' => $auto_complete ? 'completed' : $initial_status,
                'completion_status' => 'completed',
                'completion_definition' => $completion_definition,
                'owner_action' => $owner_action,
                'customer_next_step' => $customer_next_step,
                'requires_owner_action' => $requires_owner_action,
                'requires_shipping_queue' => $requires_shipping_queue,
                'auto_complete' => $auto_complete,
                'cfo_mapping' => array('paid_revenue', 'pending_fulfillment', 'completed_fulfilled_revenue', 'refunds', 'fees', 'taxes', 'net_revenue'),
                'dashboard_mapping' => array('orders', 'fulfillment_queue', 'today_actions', 'cfo', 'crm'),
            ), $metadata);
        }

        public function handle_checkout_completed($order_id, $session_data = array()) {
            $order_id = absint($order_id);
            if ($order_id <= 0) {
                return false;
            }
            return $this->sync_paid_store_order($order_id, is_array($session_data) ? $session_data : array());
        }

        public function sync_paid_session($session_id, array $session_data = array()) {
            $session_id = sanitize_text_field((string) $session_id);
            if ($session_id === '') {
                return $this->error('missing_session', 'Stripe checkout session id is required.');
            }
            global $wpdb;
            $orders = $wpdb->prefix . 'vmo_store_orders';
            if (!$this->table_exists($orders)) {
                return $this->error('orders_table_missing', 'VMO store orders table is missing.');
            }
            $order_id = (int) $wpdb->get_var($wpdb->prepare("SELECT id FROM {$orders} WHERE stripe_checkout_session_id = %s LIMIT 1", $session_id));
            if ($order_id <= 0) {
                return $this->error('order_missing', 'VMO Pay order was not found for this checkout session.');
            }
            return $this->sync_paid_store_order($order_id, array_merge($session_data, array('session_id' => $session_id)));
        }

        public function sync_paid_store_order($order_id, array $session_data = array()) {
            $this->ensure_tables();
            global $wpdb;
            $orders = $wpdb->prefix . 'vmo_store_orders';
            $order_id = absint($order_id);
            if ($order_id <= 0 || !$this->table_exists($orders)) {
                return $this->error('order_missing', 'VMO Pay order was not found.');
            }

            $order = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$orders} WHERE id = %d LIMIT 1", $order_id), ARRAY_A);
            if (!$order) {
                return $this->error('order_missing', 'VMO Pay order was not found.');
            }
            if (($order['status'] ?? '') !== 'paid') {
                return $this->error('order_not_paid', 'Only paid VMO Pay orders can create business flow instances.');
            }

            $metadata = $this->decode_json($order['metadata'] ?? '');
            $metadata = array_merge($metadata, $this->session_metadata($session_data));
            $plan = self::design_flow_plan_from_context($metadata, $order, $session_data);
            $now = current_time('mysql');
            $user_id = absint($order['user_id'] ?? $metadata['merchant_user_id'] ?? 0);
            $customer_name = sanitize_text_field((string) ($metadata['buyer_name'] ?? $session_data['buyer_name'] ?? $session_data['customer_name'] ?? ''));
            $customer_email = sanitize_email((string) ($metadata['buyer_email'] ?? $session_data['buyer_email'] ?? $session_data['customer_email'] ?? ''));
            $current_status = sanitize_key((string) ($plan['initial_status'] ?? 'awaiting_fulfillment'));
            $is_complete = $this->is_completed_status($current_status) || !empty($plan['auto_complete']);

            $metadata['business_flow_plan'] = $plan;
            $metadata['fulfillment_guidance'] = $plan['owner_action'] ?? '';
            if ($customer_name !== '') {
                $metadata['buyer_name'] = $customer_name;
            }
            if ($customer_email !== '') {
                $metadata['buyer_email'] = $customer_email;
            }

            $store_update = array(
                'fulfillment_status' => $is_complete ? 'completed' : $current_status,
                'business_flow_status' => $is_complete ? 'completed' : $current_status,
                'business_flow_type' => sanitize_key((string) ($plan['business_flow_type'] ?? 'custom_business_flow')),
                'metadata' => wp_json_encode($metadata),
                'updated_at' => $now,
            );
            if ($is_complete) {
                $store_update['fulfillment_completed_at'] = $now;
            }
            $this->update_existing_columns($orders, $store_update, array('id' => $order_id));
            $order = array_merge($order, $store_update);

            $flow_id = $this->upsert_flow_instance($order, $metadata, $plan, $customer_name, $customer_email);
            $task_id = $this->upsert_fulfillment_task($order, $metadata, $plan, $flow_id, $customer_name, $customer_email);
            $link = $this->get_transaction_link($order_id);
            $shipping_id = !empty($plan['requires_shipping_queue']) ? $this->sync_shipping_order($order, $metadata, $plan, $flow_id, $link) : 0;
            $internal_id = !empty($plan['requires_owner_action']) ? $this->sync_internal_store_order($order, $metadata, $plan, $link) : 0;

            $this->upsert_transaction_link($order, $flow_id, $task_id, $shipping_id, $internal_id, $metadata);
            $this->update_existing_columns($orders, array(
                'business_flow_instance_id' => $flow_id ?: null,
                'fulfillment_task_id' => $task_id ?: null,
                'updated_at' => $now,
            ), array('id' => $order_id));

            do_action('vmo_business_flow_created_from_payment', $order_id, $flow_id, $task_id, $plan);

            return array(
                'order_id' => $order_id,
                'business_flow_instance_id' => $flow_id,
                'fulfillment_task_id' => $task_id,
                'shipping_order_id' => $shipping_id,
                'internal_order_id' => $internal_id,
                'fulfillment_status' => $is_complete ? 'completed' : $current_status,
                'business_flow_type' => $plan['business_flow_type'] ?? 'custom_business_flow',
                'owner_action' => $plan['owner_action'] ?? '',
            );
        }

        public function mark_order_status($user_id, $order_id, $status = 'completed', $notes = '', $notify_buyer = false) {
            $this->ensure_tables();
            global $wpdb;
            $orders = $wpdb->prefix . 'vmo_store_orders';
            $user_id = absint($user_id);
            $order_id = absint($order_id);
            $status = sanitize_key((string) $status);
            $notes = sanitize_textarea_field((string) $notes);
            if ($order_id <= 0 || $user_id <= 0 || $status === '') {
                return $this->error('invalid_status_request', 'Order, user, and status are required.');
            }
            $allowed = array('awaiting_fulfillment', 'reserved', 'preparing', 'packed', 'shipped', 'delivered', 'fulfilled', 'completed', 'cancelled', 'refunded', 'confirmed', 'no_show', 'paid_deposit', 'scheduled', 'in_progress', 'access_granted', 'activated', 'checked_in', 'attended', 'receipt_sent', 'impact_update_sent');
            if (!in_array($status, $allowed, true)) {
                return $this->error('invalid_status', 'Unsupported business flow status.');
            }
            $order = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$orders} WHERE id = %d AND user_id = %d LIMIT 1", $order_id, $user_id), ARRAY_A);
            if (!$order) {
                return $this->error('order_missing', 'Order not found for this user.');
            }

            $metadata = $this->decode_json($order['metadata'] ?? '');
            $history = isset($metadata['business_flow_status_history']) && is_array($metadata['business_flow_status_history']) ? $metadata['business_flow_status_history'] : array();
            $history[] = array('status' => $status, 'notes' => $notes, 'changed_at' => current_time('mysql'), 'changed_by' => $user_id);
            $metadata['business_flow_status_history'] = $history;
            $metadata['last_business_flow_status'] = $status;
            $completed = $this->is_completed_status($status);
            $now = current_time('mysql');

            $store_update = array(
                'fulfillment_status' => $status,
                'business_flow_status' => $status,
                'metadata' => wp_json_encode($metadata),
                'updated_at' => $now,
            );
            if ($completed) {
                $store_update['fulfillment_completed_at'] = $now;
            }
            if ($status === 'refunded') {
                $store_update['status'] = 'refunded';
            }
            $this->update_existing_columns($orders, $store_update, array('id' => $order_id, 'user_id' => $user_id));

            $link = $this->get_transaction_link($order_id);
            if (!empty($link['business_flow_instance_id'])) {
                $this->update_existing_columns($wpdb->prefix . 'vmo_business_flow_instances', array(
                    'current_status' => $status,
                    'metadata' => wp_json_encode(array_merge($metadata, array('completion_notes' => $notes))),
                    'completed_at' => $completed ? $now : null,
                    'updated_at' => $now,
                ), array('id' => (int) $link['business_flow_instance_id'], 'user_id' => $user_id));
            }
            if (!empty($link['fulfillment_task_id'])) {
                $this->update_existing_columns($wpdb->prefix . 'vmo_fulfillment_tasks', array(
                    'current_status' => $status,
                    'completed_at' => $completed ? $now : null,
                    'updated_at' => $now,
                ), array('id' => (int) $link['fulfillment_task_id'], 'user_id' => $user_id));
            }
            if (!empty($link['shipping_order_id'])) {
                $this->sync_shipping_status((int) $link['shipping_order_id'], $user_id, $status, $notes);
            }
            if (!empty($link['internal_order_id'])) {
                $this->sync_internal_status((int) $link['internal_order_id'], $user_id, $status);
            }

            $this->log_crm_completion($user_id, array_merge($order, $store_update), $metadata, $status, $notes);
            $this->log_shipping_analytics($user_id, $order_id, $order, $status);
            $this->tag_financial_event($user_id, $order, $status, $notes);
            if ($notify_buyer) {
                $this->send_completion_email($order, $metadata, $status, $notes);
            }

            do_action('vmo_business_flow_status_updated', $order_id, $status, array('notes' => $notes, 'user_id' => $user_id));

            return array('order_id' => $order_id, 'status' => $status, 'completed' => $completed);
        }

        public function mark_from_shipping_order($shipping_order_id, $user_id, $status, $notes = '') {
            $shipping_order_id = absint($shipping_order_id);
            $user_id = absint($user_id);
            if ($shipping_order_id <= 0 || $user_id <= 0) {
                return false;
            }
            global $wpdb;
            $links = $wpdb->prefix . 'vmo_transaction_links';
            if (!$this->table_exists($links)) {
                return false;
            }
            $order_id = (int) $wpdb->get_var($wpdb->prepare("SELECT payment_order_id FROM {$links} WHERE shipping_order_id = %d AND user_id = %d LIMIT 1", $shipping_order_id, $user_id));
            return $order_id > 0 ? $this->mark_order_status($user_id, $order_id, $this->map_shipping_status($status), $notes, false) : false;
        }

        public function mark_from_internal_order($internal_order_id, $user_id, $status, $notes = '') {
            $internal_order_id = absint($internal_order_id);
            $user_id = absint($user_id);
            if ($internal_order_id <= 0 || $user_id <= 0) {
                return false;
            }
            global $wpdb;
            $links = $wpdb->prefix . 'vmo_transaction_links';
            if (!$this->table_exists($links)) {
                return false;
            }
            $order_id = (int) $wpdb->get_var($wpdb->prepare("SELECT payment_order_id FROM {$links} WHERE internal_order_id = %d AND user_id = %d LIMIT 1", $internal_order_id, $user_id));
            return $order_id > 0 ? $this->mark_order_status($user_id, $order_id, $this->map_shipping_status($status), $notes, false) : false;
        }

        public function revenue_summary($user_id, $days = 365) {
            global $wpdb;
            $orders = $wpdb->prefix . 'vmo_store_orders';
            $user_id = absint($user_id);
            $days = max(1, absint($days));
            $summary = array(
                'paid_revenue' => 0,
                'pending_fulfillment_revenue' => 0,
                'completed_fulfilled_revenue' => 0,
                'refunds' => 0,
                'fees' => 0,
                'taxes' => 0,
                'net_revenue' => 0,
                'pending_fulfillment_count' => 0,
                'completed_count' => 0,
                'refunded_count' => 0,
            );
            if ($user_id <= 0 || !$this->table_exists($orders)) {
                return $summary;
            }
            $since = date('Y-m-d H:i:s', strtotime('-' . $days . ' days', current_time('timestamp')));
            $rows = $wpdb->get_results($wpdb->prepare("SELECT * FROM {$orders} WHERE user_id = %d AND created_at >= %s AND status IN ('paid','refunded')", $user_id, $since), ARRAY_A);
            foreach ((array) $rows as $row) {
                $amount = (int) ($row['amount_total'] ?? 0);
                $meta = $this->decode_json($row['metadata'] ?? '');
                $fulfillment_status = sanitize_key((string) ($row['fulfillment_status'] ?? $meta['last_business_flow_status'] ?? 'awaiting_fulfillment'));
                $stripe_fee = (int) ($meta['stripe_fee_cents'] ?? 0);
                $platform_fee = (int) ($meta['platform_fee_cents'] ?? $row['platform_fee_amount'] ?? 0);
                $taxes = (int) ($meta['tax_cents'] ?? $meta['advisory_tax_cents'] ?? 0);
                $net = isset($meta['net_to_merchant_cents']) ? (int) $meta['net_to_merchant_cents'] : max(0, $amount - $stripe_fee - $platform_fee);
                $summary['fees'] += $stripe_fee + $platform_fee;
                $summary['taxes'] += $taxes;
                $summary['net_revenue'] += $net;
                if (($row['status'] ?? '') === 'refunded') {
                    $summary['refunds'] += $amount;
                    $summary['refunded_count']++;
                    continue;
                }
                $summary['paid_revenue'] += $amount;
                if ($this->is_completed_status($fulfillment_status)) {
                    $summary['completed_fulfilled_revenue'] += $amount;
                    $summary['completed_count']++;
                } else {
                    $summary['pending_fulfillment_revenue'] += $amount;
                    $summary['pending_fulfillment_count']++;
                }
            }
            return $summary;
        }

        public function ajax_get_queue() {
            if (!current_user_can('read')) {
                wp_send_json_error(array('message' => 'Unauthorized'), 403);
            }
            if (function_exists('vmo_verify_admin_security') && (isset($_POST['security']) || isset($_POST['nonce'])) && !vmo_verify_admin_security()) {
                wp_send_json_error(array('message' => 'Invalid nonce', 'status' => 'invalid_nonce'), 403);
            }
            global $wpdb;
            $tasks = $wpdb->prefix . 'vmo_fulfillment_tasks';
            $user_id = get_current_user_id();
            $rows = array();
            if ($this->table_exists($tasks)) {
                $rows = $wpdb->get_results($wpdb->prepare("SELECT * FROM {$tasks} WHERE user_id = %d AND current_status NOT IN ('completed','fulfilled','delivered','cancelled','refunded') ORDER BY created_at DESC LIMIT 50", $user_id), ARRAY_A);
            }
            wp_send_json_success(array('orders' => $this->format_queue_rows($rows), 'total' => count($rows)));
        }

        public function ajax_mark_status() {
            if (!current_user_can('read')) {
                wp_send_json_error(array('message' => 'Unauthorized'), 403);
            }
            if (function_exists('vmo_verify_admin_security') && (isset($_POST['security']) || isset($_POST['nonce'])) && !vmo_verify_admin_security()) {
                wp_send_json_error(array('message' => 'Invalid nonce', 'status' => 'invalid_nonce'), 403);
            }
            $order_id = absint($_POST['order_id'] ?? $_POST['payment_order_id'] ?? 0);
            $status = sanitize_key(wp_unslash($_POST['status'] ?? $_POST['new_status'] ?? 'completed'));
            $notes = sanitize_textarea_field(wp_unslash($_POST['notes'] ?? $_POST['completion_notes'] ?? ''));
            $notify = !empty($_POST['notify_buyer']) && $_POST['notify_buyer'] !== '0';
            $result = $this->mark_order_status(get_current_user_id(), $order_id, $status, $notes, $notify);
            if (is_wp_error($result)) {
                wp_send_json_error(array('message' => $result->get_error_message()), 400);
            }
            wp_send_json_success($result);
        }

        private static function extract_ai_plan(array $metadata) {
            foreach (array('business_flow_plan', 'vmo_business_flow_plan', 'operational_flow_plan', 'business_flow') as $key) {
                if (empty($metadata[$key])) {
                    continue;
                }
                $plan = $metadata[$key];
                if (is_string($plan)) {
                    $decoded = json_decode($plan, true);
                    $plan = is_array($decoded) ? $decoded : array();
                }
                if (is_array($plan) && $plan) {
                    return $plan;
                }
            }
            return array();
        }

        private static function normalize_plan(array $plan, array $metadata = array()) {
            $statuses = $plan['completion_statuses'] ?? $plan['statuses'] ?? $plan['status_stages'] ?? array('paid', 'owner_review', 'completed');
            $statuses = array_values(array_filter(array_map(array(__CLASS__, 'key'), (array) $statuses)));
            if (!$statuses) {
                $statuses = array('paid', 'owner_review', 'completed');
            }
            $modules = array_values(array_unique(array_filter(array_map(array(__CLASS__, 'key'), (array) ($plan['modules_required'] ?? array())))));
            foreach (array('payment', 'crm', 'email_notification', 'dashboard_kpi', 'fulfillment_status') as $required_module) {
                if (!in_array($required_module, $modules, true)) {
                    $modules[] = $required_module;
                }
            }
            $initial = self::key($plan['initial_status'] ?? $statuses[0] ?? 'owner_review');
            $completion = self::key($plan['completion_status'] ?? 'completed');
            if (!in_array($completion, $statuses, true)) {
                $statuses[] = $completion;
            }
            $requires_shipping = !empty($plan['requires_shipping_queue']) || in_array('shipping_fulfillment', $modules, true) || in_array('shipping', $modules, true) || !empty($metadata['shipping_required']);
            return array(
                'business_flow_type' => self::key($plan['business_flow_type'] ?? $plan['flow_type'] ?? 'custom_business_flow'),
                'modules_required' => $modules,
                'visitor_steps' => array_values(array_filter(array_map('strval', (array) ($plan['visitor_steps'] ?? array('view offer', 'checkout', 'receive confirmation'))))),
                'owner_steps' => array_values(array_filter(array_map('strval', (array) ($plan['owner_steps'] ?? array('review transaction', 'mark complete'))))),
                'payment_actions' => array_values(array_filter(array_map('strval', (array) ($plan['payment_actions'] ?? array('stripe_checkout_completed', 'business_flow_instance_created'))))),
                'customer_confirmations' => array_values(array_filter(array_map('strval', (array) ($plan['customer_confirmations'] ?? array('buyer_receipt', 'order_confirmation'))))),
                'completion_statuses' => $statuses,
                'initial_status' => $initial,
                'completion_status' => $completion,
                'completion_definition' => sanitize_textarea_field((string) ($plan['completion_definition'] ?? 'The AI-defined business outcome is complete.')),
                'owner_action' => sanitize_textarea_field((string) ($plan['owner_action'] ?? 'Review the paid transaction and mark the business outcome complete.')),
                'customer_next_step' => sanitize_textarea_field((string) ($plan['customer_next_step'] ?? 'Your payment is confirmed. Watch for the next update.')),
                'requires_owner_action' => !array_key_exists('requires_owner_action', $plan) ? true : (bool) $plan['requires_owner_action'],
                'requires_shipping_queue' => (bool) $requires_shipping,
                'auto_complete' => !empty($plan['auto_complete']),
                'cfo_mapping' => array_values(array_filter(array_map('strval', (array) ($plan['cfo_mapping'] ?? array('paid_revenue', 'pending_fulfillment', 'completed_fulfilled_revenue', 'refunds', 'fees', 'taxes', 'net_revenue'))))),
                'dashboard_mapping' => array_values(array_filter(array_map('strval', (array) ($plan['dashboard_mapping'] ?? array('orders', 'fulfillment_queue', 'cfo', 'crm'))))),
            );
        }

        private function upsert_flow_instance(array $order, array $metadata, array $plan, $customer_name, $customer_email) {
            global $wpdb;
            $table = $wpdb->prefix . 'vmo_business_flow_instances';
            $order_id = absint($order['id'] ?? 0);
            $user_id = absint($order['user_id'] ?? 0);
            $existing = (int) $wpdb->get_var($wpdb->prepare("SELECT id FROM {$table} WHERE source_type = %s AND source_id = %d LIMIT 1", 'vmo_pay_order', $order_id));
            $status = sanitize_key((string) ($order['fulfillment_status'] ?? $plan['initial_status'] ?? 'awaiting_fulfillment'));
            $row = array(
                'user_id' => $user_id,
                'flow_key' => sanitize_key((string) ($plan['business_flow_type'] ?? 'custom_business_flow')) . '_' . $order_id,
                'flow_type' => sanitize_key((string) ($plan['business_flow_type'] ?? 'custom_business_flow')),
                'source_type' => 'vmo_pay_order',
                'source_id' => $order_id,
                'payment_order_id' => $order_id,
                'stripe_checkout_session_id' => sanitize_text_field((string) ($order['stripe_checkout_session_id'] ?? '')),
                'payment_status' => sanitize_key((string) ($order['status'] ?? 'paid')),
                'current_status' => $status,
                'completion_status' => sanitize_key((string) ($plan['completion_status'] ?? 'completed')),
                'completion_definition' => sanitize_textarea_field((string) ($plan['completion_definition'] ?? '')),
                'modules_required' => wp_json_encode($plan['modules_required'] ?? array()),
                'visitor_steps' => wp_json_encode($plan['visitor_steps'] ?? array()),
                'owner_steps' => wp_json_encode($plan['owner_steps'] ?? array()),
                'status_sequence' => wp_json_encode($plan['completion_statuses'] ?? array()),
                'notification_plan' => wp_json_encode($plan['customer_confirmations'] ?? array()),
                'dashboard_mapping' => wp_json_encode($plan['dashboard_mapping'] ?? array()),
                'cfo_mapping' => wp_json_encode($plan['cfo_mapping'] ?? array()),
                'customer_name' => sanitize_text_field((string) $customer_name),
                'customer_email' => sanitize_email((string) $customer_email),
                'amount_total' => absint($order['amount_total'] ?? 0),
                'currency' => sanitize_text_field((string) ($order['currency'] ?? 'usd')),
                'metadata' => wp_json_encode(array('order_metadata' => $metadata, 'plan' => $plan)),
                'completed_at' => $this->is_completed_status($status) ? current_time('mysql') : null,
                'updated_at' => current_time('mysql'),
            );
            if ($existing > 0) {
                $this->update_existing_columns($table, $row, array('id' => $existing));
                return $existing;
            }
            $row['created_at'] = current_time('mysql');
            $wpdb->insert($table, $row);
            return (int) $wpdb->insert_id;
        }

        private function upsert_fulfillment_task(array $order, array $metadata, array $plan, $flow_id, $customer_name, $customer_email) {
            global $wpdb;
            $table = $wpdb->prefix . 'vmo_fulfillment_tasks';
            $order_id = absint($order['id'] ?? 0);
            $user_id = absint($order['user_id'] ?? 0);
            $existing = (int) $wpdb->get_var($wpdb->prepare("SELECT id FROM {$table} WHERE payment_order_id = %d AND user_id = %d LIMIT 1", $order_id, $user_id));
            $status = sanitize_key((string) ($order['fulfillment_status'] ?? $plan['initial_status'] ?? 'awaiting_fulfillment'));
            if (!empty($plan['auto_complete'])) {
                $status = 'completed';
            }
            $row = array(
                'user_id' => $user_id,
                'flow_instance_id' => absint($flow_id) ?: null,
                'payment_order_id' => $order_id,
                'task_type' => sanitize_key((string) ($plan['business_flow_type'] ?? 'custom_business_flow')),
                'current_status' => $status,
                'owner_action' => sanitize_textarea_field((string) ($plan['owner_action'] ?? 'Review and complete this paid transaction.')),
                'customer_name' => sanitize_text_field((string) $customer_name),
                'customer_email' => sanitize_email((string) $customer_email),
                'amount_total' => absint($order['amount_total'] ?? 0),
                'currency' => sanitize_text_field((string) ($order['currency'] ?? 'usd')),
                'metadata' => wp_json_encode(array('order_metadata' => $metadata, 'plan' => $plan)),
                'completed_at' => $this->is_completed_status($status) ? current_time('mysql') : null,
                'updated_at' => current_time('mysql'),
            );
            if ($existing > 0) {
                $this->update_existing_columns($table, $row, array('id' => $existing));
                return $existing;
            }
            $row['created_at'] = current_time('mysql');
            $wpdb->insert($table, $row);
            return (int) $wpdb->insert_id;
        }

        private function sync_shipping_order(array $order, array $metadata, array $plan, $flow_id, array $link = array()) {
            global $wpdb;
            $table = $wpdb->prefix . 'vmo_shipping_orders';
            if (!$this->table_exists($table)) {
                return 0;
            }
            $order_id = absint($order['id'] ?? 0);
            $user_id = absint($order['user_id'] ?? 0);
            $existing = absint($link['shipping_order_id'] ?? 0);
            if (!$existing) {
                $existing = (int) $wpdb->get_var($wpdb->prepare("SELECT id FROM {$table} WHERE user_id = %d AND order_ref = %s LIMIT 1", $user_id, 'VMO-PAY-' . $order_id));
            }
            $address = $metadata['shipping_address'] ?? $metadata['address'] ?? array();
            if (!is_array($address)) {
                $address = array('address_line1' => (string) $address);
            }
            $items = $metadata['line_items'] ?? array(array(
                'product_id' => $metadata['product_id'] ?? 0,
                'name' => $metadata['product_name'] ?? 'VMO Pay order',
                'quantity' => $metadata['quantity'] ?? 1,
            ));
            $row = array(
                'user_id' => $user_id,
                'order_ref' => 'VMO-PAY-' . $order_id,
                'customer_name' => sanitize_text_field((string) ($metadata['buyer_name'] ?? $metadata['customer_name'] ?? '')),
                'customer_email' => sanitize_email((string) ($metadata['buyer_email'] ?? $metadata['customer_email'] ?? '')),
                'address_line1' => sanitize_text_field((string) ($address['line1'] ?? $address['address_line1'] ?? '')),
                'address_line2' => sanitize_text_field((string) ($address['line2'] ?? $address['address_line2'] ?? '')),
                'city' => sanitize_text_field((string) ($address['city'] ?? '')),
                'state' => sanitize_text_field((string) ($address['state'] ?? '')),
                'postal_code' => sanitize_text_field((string) ($address['postal_code'] ?? $address['zip'] ?? '')),
                'country' => sanitize_text_field((string) ($address['country'] ?? '')),
                'items' => wp_json_encode($items),
                'total' => round(((int) ($order['amount_total'] ?? 0)) / 100, 2),
                'currency' => strtoupper(sanitize_text_field((string) ($order['currency'] ?? 'USD'))),
                'status' => 'pending',
                'delivery_type' => sanitize_key((string) ($metadata['delivery_type'] ?? 'carrier')),
                'delivery_notes' => sanitize_textarea_field((string) ($plan['owner_action'] ?? '')),
                'updated_at' => current_time('mysql'),
            );
            if ($existing > 0) {
                $this->update_existing_columns($table, $row, array('id' => $existing, 'user_id' => $user_id));
                return $existing;
            }
            if (class_exists('VMO_Shipping_Manager') && method_exists('VMO_Shipping_Manager', 'get_instance')) {
                $shipping_id = VMO_Shipping_Manager::get_instance()->create_order_from_payment($user_id, array(
                    'order_ref' => 'VMO-PAY-' . $order_id,
                    'customer_name' => $row['customer_name'],
                    'customer_email' => $row['customer_email'],
                    'address_line1' => $row['address_line1'],
                    'address_line2' => $row['address_line2'],
                    'city' => $row['city'],
                    'state' => $row['state'],
                    'postal_code' => $row['postal_code'],
                    'country' => $row['country'],
                    'items' => $items,
                    'total' => $row['total'],
                    'currency' => $row['currency'],
                ));
                if ($shipping_id) {
                    $this->update_existing_columns($table, array(
                        'order_ref' => 'VMO-PAY-' . $order_id,
                        'delivery_type' => $row['delivery_type'],
                        'delivery_notes' => $row['delivery_notes'],
                        'updated_at' => current_time('mysql'),
                    ), array('id' => (int) $shipping_id, 'user_id' => $user_id));
                    return (int) $shipping_id;
                }
            }
            $row['created_at'] = current_time('mysql');
            $wpdb->insert($table, $row);
            return (int) $wpdb->insert_id;
        }

        private function sync_internal_store_order(array $order, array $metadata, array $plan, array $link = array()) {
            if (function_exists('vmo_create_internal_store_orders_table')) {
                vmo_create_internal_store_orders_table();
            }
            global $wpdb;
            $table = $wpdb->prefix . 'vmo_internal_store_orders';
            if (!$this->table_exists($table)) {
                return 0;
            }
            $user_id = absint($order['user_id'] ?? 0);
            $existing = absint($link['internal_order_id'] ?? 0);
            $payment_intent = sanitize_text_field((string) ($order['stripe_payment_intent_id'] ?? ''));
            if (!$existing && $payment_intent !== '') {
                $existing = (int) $wpdb->get_var($wpdb->prepare("SELECT id FROM {$table} WHERE user_id = %d AND stripe_payment_intent = %s LIMIT 1", $user_id, $payment_intent));
            }
            $address = $metadata['shipping_address'] ?? $metadata['address'] ?? '';
            if (is_array($address)) {
                $address = trim(implode(' ', array_filter(array($address['line1'] ?? '', $address['line2'] ?? '', $address['city'] ?? '', $address['state'] ?? '', $address['postal_code'] ?? '', $address['country'] ?? ''))));
            }
            $items = $metadata['line_items'] ?? array(array('name' => $metadata['product_name'] ?? 'VMO Pay order', 'quantity' => $metadata['quantity'] ?? 1));
            $row = array(
                'user_id' => $user_id,
                'customer_name' => sanitize_text_field((string) ($metadata['buyer_name'] ?? $metadata['customer_name'] ?? '')),
                'customer_email' => sanitize_email((string) ($metadata['buyer_email'] ?? $metadata['customer_email'] ?? '')),
                'shipping_address' => sanitize_textarea_field((string) $address),
                'items' => wp_json_encode($items),
                'items_count' => max(1, count((array) $items)),
                'total_amount' => round(((int) ($order['amount_total'] ?? 0)) / 100, 2),
                'currency' => strtoupper(sanitize_text_field((string) ($order['currency'] ?? 'USD'))),
                'status' => 'paid',
                'shipping_status' => 'pending',
                'payment_status' => 'paid',
                'stripe_payment_intent' => $payment_intent,
            );
            if ($existing > 0) {
                $this->update_existing_columns($table, $row, array('id' => $existing, 'user_id' => $user_id));
                return $existing;
            }
            $row['created_at'] = current_time('mysql');
            $wpdb->insert($table, $row);
            return (int) $wpdb->insert_id;
        }

        private function upsert_transaction_link(array $order, $flow_id, $task_id, $shipping_id, $internal_id, array $metadata) {
            global $wpdb;
            $table = $wpdb->prefix . 'vmo_transaction_links';
            $order_id = absint($order['id'] ?? 0);
            $user_id = absint($order['user_id'] ?? 0);
            $row = array(
                'user_id' => $user_id,
                'payment_order_id' => $order_id,
                'business_flow_instance_id' => absint($flow_id) ?: null,
                'fulfillment_task_id' => absint($task_id) ?: null,
                'shipping_order_id' => absint($shipping_id) ?: null,
                'internal_order_id' => absint($internal_id) ?: null,
                'stripe_checkout_session_id' => sanitize_text_field((string) ($order['stripe_checkout_session_id'] ?? '')),
                'stripe_payment_intent_id' => sanitize_text_field((string) ($order['stripe_payment_intent_id'] ?? '')),
                'source_table' => 'vmo_store_orders',
                'source_id' => $order_id,
                'metadata' => wp_json_encode($metadata),
                'updated_at' => current_time('mysql'),
            );
            $primary_id = $this->upsert_transaction_link_row($table, $row);
            $flow_links = $wpdb->prefix . 'vmo_transaction_flow_links';
            if ($this->table_exists($flow_links)) {
                $this->upsert_transaction_link_row($flow_links, $row);
            }
            return $primary_id;
        }

        private function upsert_transaction_link_row($table, array $row) {
            global $wpdb;
            $existing = (int) $wpdb->get_var($wpdb->prepare("SELECT id FROM {$table} WHERE payment_order_id = %d LIMIT 1", absint($row['payment_order_id'] ?? 0)));
            if ($existing > 0) {
                $this->update_existing_columns($table, $row, array('id' => $existing));
                return $existing;
            }
            $row['created_at'] = current_time('mysql');
            $wpdb->insert($table, $row);
            return (int) $wpdb->insert_id;
        }

        private function get_transaction_link($order_id) {
            global $wpdb;
            $table = $wpdb->prefix . 'vmo_transaction_links';
            if (!$this->table_exists($table)) {
                return array();
            }
            $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE payment_order_id = %d LIMIT 1", absint($order_id)), ARRAY_A);
            if (!is_array($row)) {
                $flow_links = $wpdb->prefix . 'vmo_transaction_flow_links';
                if ($this->table_exists($flow_links)) {
                    $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$flow_links} WHERE payment_order_id = %d LIMIT 1", absint($order_id)), ARRAY_A);
                }
            }
            return is_array($row) ? $row : array();
        }

        private function sync_shipping_status($shipping_order_id, $user_id, $status, $notes = '') {
            global $wpdb;
            $table = $wpdb->prefix . 'vmo_shipping_orders';
            $status = $this->map_flow_status_to_shipping($status);
            $row = array('status' => $status, 'updated_at' => current_time('mysql'));
            if (in_array($status, array('shipped', 'in_transit'), true)) {
                $row['shipped_at'] = current_time('mysql');
            }
            if ($status === 'delivered') {
                $row['delivered_at'] = current_time('mysql');
            }
            $this->update_existing_columns($table, $row, array('id' => absint($shipping_order_id), 'user_id' => absint($user_id)));
            $log = $wpdb->prefix . 'vmo_shipping_status_log';
            if ($this->table_exists($log)) {
                $wpdb->insert($log, array(
                    'order_id' => absint($shipping_order_id),
                    'user_id' => absint($user_id),
                    'status' => $status,
                    'notes' => $notes ?: 'Business flow status updated to ' . $status,
                    'created_at' => current_time('mysql'),
                ));
            }
        }

        private function sync_internal_status($internal_order_id, $user_id, $status) {
            global $wpdb;
            $table = $wpdb->prefix . 'vmo_internal_store_orders';
            $shipping_status = $this->map_flow_status_to_internal($status);
            $row = array('shipping_status' => $shipping_status);
            if ($shipping_status === 'approved') {
                $row['shipping_approved_at'] = current_time('mysql');
            }
            if ($shipping_status === 'shipped' || $shipping_status === 'delivered') {
                $row['shipped_at'] = current_time('mysql');
            }
            $this->update_existing_columns($table, $row, array('id' => absint($internal_order_id), 'user_id' => absint($user_id)));
        }

        private function log_crm_completion($user_id, array $order, array $metadata, $status, $notes) {
            $email = sanitize_email((string) ($metadata['buyer_email'] ?? $metadata['customer_email'] ?? ''));
            if ($email === '' || !function_exists('vmo_upsert_contact') || !function_exists('vmo_crm_log_interaction')) {
                return;
            }
            $contact_id = vmo_upsert_contact($user_id, array(
                'full_name' => sanitize_text_field((string) ($metadata['buyer_name'] ?? $metadata['customer_name'] ?? '')),
                'email' => $email,
                'source' => 'vmo_pay',
            ));
            if ($contact_id === '') {
                return;
            }
            vmo_crm_log_interaction($user_id, $contact_id, array(
                'interaction_type' => 'manual_note',
                'subtype' => 'business_flow_status_update',
                'subject' => 'Business flow status updated',
                'summary' => 'Order #' . (int) ($order['id'] ?? 0) . ' marked ' . sanitize_key($status) . '.',
                'outcome' => $this->is_completed_status($status) ? 'successful' : 'pending',
                'metadata' => array('order_id' => (int) ($order['id'] ?? 0), 'status' => $status, 'notes' => $notes),
            ));
        }

        private function log_shipping_analytics($user_id, $order_id, array $order, $status) {
            if (!$this->is_completed_status($status)) {
                return;
            }
            global $wpdb;
            $table = $wpdb->prefix . 'vmo_shipping_analytics';
            if (!$this->table_exists($table)) {
                return;
            }
            $created = strtotime((string) ($order['created_at'] ?? current_time('mysql')));
            $hours = max(0.1, (current_time('timestamp') - ($created ?: current_time('timestamp'))) / 3600);
            $wpdb->insert($table, array(
                'user_id' => absint($user_id),
                'order_id' => (string) absint($order_id),
                'carrier' => 'business_flow',
                'service_type' => 'VMO Business Flow',
                'shipping_cost' => 0,
                'fulfillment_hours' => round($hours, 2),
                'on_time_delivery' => $hours <= 48 ? 1 : 0,
                'shipped_date' => current_time('mysql'),
            ));
        }

        private function tag_financial_event($user_id, array $order, $status, $notes) {
            global $wpdb;
            $events = $wpdb->prefix . 'vmo_financial_events';
            if (!$this->table_exists($events)) {
                return;
            }
            $reference = sanitize_text_field((string) ($order['stripe_payment_intent_id'] ?? $order['stripe_charge_id'] ?? ''));
            if ($reference === '') {
                return;
            }
            $event = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$events} WHERE user_id = %d AND source_reference = %s LIMIT 1", absint($user_id), $reference), ARRAY_A);
            if (!$event) {
                return;
            }
            $meta = $this->decode_json($event['metadata'] ?? '');
            $meta['business_flow_status'] = $status;
            $meta['business_flow_notes'] = $notes;
            $this->update_existing_columns($events, array('metadata' => wp_json_encode($meta), 'updated_at' => current_time('mysql')), array('id' => (int) $event['id']));
        }

        private function send_completion_email(array $order, array $metadata, $status, $notes) {
            $email = sanitize_email((string) ($metadata['buyer_email'] ?? $metadata['customer_email'] ?? ''));
            if ($email === '') {
                return;
            }
            $site_name = get_bloginfo('name');
            $product = sanitize_text_field((string) ($metadata['product_name'] ?? 'your order'));
            $message = "Hi " . sanitize_text_field((string) ($metadata['buyer_name'] ?? 'there')) . ",\n\n";
            $message .= "Your {$product} status is now " . str_replace('_', ' ', sanitize_key($status)) . ".\n";
            if ($notes !== '') {
                $message .= "\nNote: " . sanitize_textarea_field($notes) . "\n";
            }
            $message .= "\nThank you,\n{$site_name}";
            wp_mail($email, 'Order status update - ' . $site_name, $message);
        }

        private function ensure_store_order_columns() {
            global $wpdb;
            $table = $wpdb->prefix . 'vmo_store_orders';
            if (!$this->table_exists($table)) {
                return;
            }
            $columns = $this->columns_for_table($table, true);
            $defs = array(
                'fulfillment_status' => "ALTER TABLE {$table} ADD COLUMN fulfillment_status VARCHAR(80) DEFAULT 'not_required' AFTER status",
                'business_flow_status' => "ALTER TABLE {$table} ADD COLUMN business_flow_status VARCHAR(80) DEFAULT 'created' AFTER fulfillment_status",
                'business_flow_type' => "ALTER TABLE {$table} ADD COLUMN business_flow_type VARCHAR(120) DEFAULT 'custom_business_flow' AFTER business_flow_status",
                'business_flow_instance_id' => "ALTER TABLE {$table} ADD COLUMN business_flow_instance_id BIGINT UNSIGNED NULL AFTER business_flow_type",
                'fulfillment_task_id' => "ALTER TABLE {$table} ADD COLUMN fulfillment_task_id BIGINT UNSIGNED NULL AFTER business_flow_instance_id",
                'fulfillment_completed_at' => "ALTER TABLE {$table} ADD COLUMN fulfillment_completed_at DATETIME NULL AFTER fulfillment_task_id",
            );
            foreach ($defs as $column => $sql) {
                if (!in_array($column, $columns, true)) {
                    $wpdb->query($sql);
                }
            }
            unset($this->columns_cache[$table]);
        }

        private function format_queue_rows(array $rows) {
            $out = array();
            foreach ($rows as $row) {
                $meta = $this->decode_json($row['metadata'] ?? '');
                $plan = is_array($meta['plan'] ?? null) ? $meta['plan'] : array();
                $out[] = array(
                    'id' => (int) ($row['id'] ?? 0),
                    'payment_order_id' => (int) ($row['payment_order_id'] ?? 0),
                    'customer_name' => (string) ($row['customer_name'] ?? ''),
                    'customer_email' => (string) ($row['customer_email'] ?? ''),
                    'total' => number_format(((int) ($row['amount_total'] ?? 0)) / 100, 2),
                    'currency' => (string) ($row['currency'] ?? 'usd'),
                    'shipping_status' => (string) ($row['current_status'] ?? ''),
                    'business_flow_type' => (string) ($plan['business_flow_type'] ?? $row['task_type'] ?? 'custom_business_flow'),
                    'owner_action' => (string) ($row['owner_action'] ?? ''),
                    'created_at' => (string) ($row['created_at'] ?? ''),
                );
            }
            return $out;
        }

        private function session_metadata(array $session_data) {
            $out = array();
            foreach (array('buyer_email', 'buyer_name', 'customer_email', 'customer_name', 'currency', 'amount_total', 'session_id', 'stripe_payment_intent_id') as $key) {
                if (isset($session_data[$key]) && $session_data[$key] !== '') {
                    $out[$key] = $session_data[$key];
                }
            }
            if (!empty($session_data['shipping_address']) && is_array($session_data['shipping_address'])) {
                $out['shipping_address'] = $session_data['shipping_address'];
            }
            return $out;
        }

        private function update_existing_columns($table, array $row, array $where) {
            global $wpdb;
            $columns = $this->columns_for_table($table);
            if ($columns) {
                $row = array_intersect_key($row, array_flip($columns));
            }
            $row = array_filter($row, static function($value) { return $value !== '__vmo_skip__'; });
            if (!$row) {
                return false;
            }
            return $wpdb->update($table, $row, $where);
        }

        private function columns_for_table($table, $fresh = false) {
            if (!$fresh && isset($this->columns_cache[$table])) {
                return $this->columns_cache[$table];
            }
            global $wpdb;
            $columns = $wpdb->get_col("SHOW COLUMNS FROM {$table}", 0);
            $this->columns_cache[$table] = is_array($columns) ? array_map('strval', $columns) : array();
            return $this->columns_cache[$table];
        }

        private function table_exists($table) {
            if (function_exists('vmo_table_exists')) {
                return (bool) vmo_table_exists($table);
            }
            global $wpdb;
            return is_object($wpdb) && $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table)) === $table;
        }

        private function decode_json($value) {
            if (is_array($value)) {
                return $value;
            }
            $decoded = json_decode((string) $value, true);
            return is_array($decoded) ? $decoded : array();
        }

        private static function key($value) {
            return function_exists('sanitize_key') ? sanitize_key((string) $value) : strtolower(preg_replace('/[^a-z0-9_\-]/', '', (string) $value));
        }

        private function is_completed_status($status) {
            return in_array(sanitize_key((string) $status), array('delivered', 'fulfilled', 'completed', 'access_granted', 'activated', 'attended', 'served', 'receipt_sent', 'impact_update_sent'), true);
        }

        private function map_shipping_status($status) {
            $status = sanitize_key((string) $status);
            $map = array('pending' => 'awaiting_fulfillment', 'processing' => 'preparing', 'picked_up' => 'preparing', 'in_transit' => 'shipped', 'out_for_delivery' => 'shipped', 'shipped' => 'shipped', 'delivered' => 'delivered', 'returned' => 'cancelled', 'cancelled' => 'cancelled');
            return $map[$status] ?? $status;
        }

        private function map_flow_status_to_shipping($status) {
            $status = sanitize_key((string) $status);
            $map = array('awaiting_fulfillment' => 'pending', 'reserved' => 'pending', 'preparing' => 'processing', 'packed' => 'processing', 'shipped' => 'shipped', 'delivered' => 'delivered', 'fulfilled' => 'delivered', 'completed' => 'delivered', 'cancelled' => 'cancelled', 'refunded' => 'cancelled');
            return $map[$status] ?? $status;
        }

        private function map_flow_status_to_internal($status) {
            $status = sanitize_key((string) $status);
            $map = array('awaiting_fulfillment' => 'pending', 'reserved' => 'pending', 'preparing' => 'processing', 'packed' => 'approved', 'shipped' => 'shipped', 'delivered' => 'delivered', 'fulfilled' => 'delivered', 'completed' => 'delivered', 'cancelled' => 'cancelled', 'refunded' => 'cancelled');
            return $map[$status] ?? $status;
        }

        private function error($code, $message) {
            return class_exists('WP_Error') ? new WP_Error($code, $message) : array('error' => $code, 'message' => $message);
        }

        public static function owner_guidance_for_metadata(array $metadata) {
            $plan = self::design_flow_plan_from_context($metadata);
            return sanitize_textarea_field((string) ($plan['owner_action'] ?? 'Review this paid transaction and complete the next business step.'));
        }

        public static function customer_next_step_for_metadata(array $metadata) {
            $plan = self::design_flow_plan_from_context($metadata);
            return sanitize_textarea_field((string) ($plan['customer_next_step'] ?? 'Your payment is confirmed. Watch for the next update.'));
        }
    }
}

if (!function_exists('vmo_business_flow_engine')) {
    function vmo_business_flow_engine() {
        return VMO_Business_Flow_Engine::get_instance();
    }
}

if (!function_exists('vmo_business_flow_sync_paid_order')) {
    function vmo_business_flow_sync_paid_order($order_id, array $session_data = array()) {
        return vmo_business_flow_engine()->sync_paid_store_order($order_id, $session_data);
    }
}

if (!function_exists('vmo_business_flow_sync_paid_session')) {
    function vmo_business_flow_sync_paid_session($session_id, array $session_data = array()) {
        return vmo_business_flow_engine()->sync_paid_session($session_id, $session_data);
    }
}

if (!function_exists('vmo_business_flow_mark_order_status')) {
    function vmo_business_flow_mark_order_status($user_id, $order_id, $status = 'completed', $notes = '', $notify_buyer = false) {
        return vmo_business_flow_engine()->mark_order_status($user_id, $order_id, $status, $notes, $notify_buyer);
    }
}

if (!function_exists('vmo_business_flow_mark_from_shipping_order')) {
    function vmo_business_flow_mark_from_shipping_order($shipping_order_id, $user_id, $status, $notes = '') {
        return vmo_business_flow_engine()->mark_from_shipping_order($shipping_order_id, $user_id, $status, $notes);
    }
}

if (!function_exists('vmo_business_flow_mark_from_internal_order')) {
    function vmo_business_flow_mark_from_internal_order($internal_order_id, $user_id, $status, $notes = '') {
        return vmo_business_flow_engine()->mark_from_internal_order($internal_order_id, $user_id, $status, $notes);
    }
}

if (!function_exists('vmo_business_flow_revenue_summary')) {
    function vmo_business_flow_revenue_summary($user_id, $days = 365) {
        return vmo_business_flow_engine()->revenue_summary($user_id, $days);
    }
}

if (!function_exists('vmo_business_flow_guidance_for_order_meta')) {
    function vmo_business_flow_guidance_for_order_meta(array $metadata) {
        return VMO_Business_Flow_Engine::owner_guidance_for_metadata($metadata);
    }
}

if (!function_exists('vmo_business_flow_customer_next_step_for_order_meta')) {
    function vmo_business_flow_customer_next_step_for_order_meta(array $metadata) {
        return VMO_Business_Flow_Engine::customer_next_step_for_metadata($metadata);
    }
}