<?php

namespace Unlockafe_addons\Classes;

use function current_user_can;
use function wp_send_json_error;
use function wp_send_json_success;
use function wp_verify_nonce;
use function sanitize_text_field;

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

class Sync_Advantage_Core {

    private static $_instance = null;
    private $option_name = 'unlockafe_sync_advantage_options';

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

    private function __construct() {
        add_action('woocommerce_checkout_order_processed', [$this, 'export_new_order_to_google_sheet'], 10, 3);
        add_action('woocommerce_order_status_changed', [$this, 'update_order_status_in_google_sheet'], 10, 4);
        
        // AJAX handlers - admin only (no nopriv needed for admin functions)
        add_action('wp_ajax_sync_advantage_export_all', [$this, 'export_all_orders_to_google_sheet']);
        add_action('wp_ajax_unlockafe_manual_sync', [$this, 'ajax_manual_sync']);

        // Hooks for Webhook Sync
        add_action('woocommerce_checkout_order_processed', [$this, 'send_order_to_webhook'], 10, 1);
        add_action('woocommerce_order_status_changed', [$this, 'send_order_to_webhook_on_status_change'], 10, 4);

        // Cron job for background sync
        add_action('unlockafe_sync_advantage_cron', [$this, 'check_google_sheet_for_new_orders']);

        // Hooks for Google Sheet Webhook Sync
        add_action('woocommerce_checkout_order_processed', [$this, 'send_order_to_google_sheets'], 10, 1);
        add_action('woocommerce_order_status_changed', [$this, 'send_order_to_google_sheets'], 10, 4);
        add_action('update_option_' . $this->option_name, [$this, 'toggle_background_sync_cron'], 10, 2);

        // Add custom cron schedules
        add_filter('cron_schedules', function ($schedules) {
            $schedules['fifteen_minutes'] = [
                'interval' => 900, // 15 * 60 seconds
                'display'  => __('Every Fifteen Minutes'),
            ];
            return $schedules;
        });

        // Initial check to ensure cron is scheduled if it should be
        $options = get_option($this->option_name);
        if (!empty($options['enable_background_sync']) && $options['enable_background_sync'] === '1') {
            if (!wp_next_scheduled('unlockafe_sync_advantage_cron')) {
                wp_schedule_event(time(), 'fifteen_minutes', 'unlockafe_sync_advantage_cron');
            }
        }
        
    }

    private function set_status_column_data_validation($sheet_id, $access_token) {
        $statuses = ['processing', 'on-hold', 'completed', 'cancelled', 'pending', 'refunded', 'failed', 'draft'];

        $request_body = [
            'requests' => [
                [
                    'setDataValidation' => [
                        'range' => [
                            'sheetId' => 0, // Assumes the first sheet
                            'startRowIndex' => 1, // Start from the second row (after header)
                            'startColumnIndex' => 1, // Column B for Status
                            'endColumnIndex' => 2
                        ],
                        'rule' => [
                            'condition' => [
                                'type' => 'ONE_OF_LIST',
                                'values' => array_map(function($status) {
                                    return ['userEnteredValue' => str_replace('wc-', '', $status)];
                                }, $statuses)
                            ],
                            'strict' => true,
                            'showCustomUi' => true
                        ]
                    ]
                ]
            ]
        ];

        $response = wp_remote_post(
            "https://sheets.googleapis.com/v4/spreadsheets/{$sheet_id}:batchUpdate",
            [
                'headers' => [
                    'Authorization' => 'Bearer ' . $access_token,
                    'Content-Type'  => 'application/json'
                ],
                'body' => json_encode($request_body)
            ]
        );

        if (is_wp_error($response)) {
            error_log('Sync Advantage: Failed to set data validation. ' . $response->get_error_message());
        } else {
            $response_code = wp_remote_retrieve_response_code($response);
            if ($response_code !== 200) {
                error_log('Sync Advantage: Error setting data validation. Response Code: ' . $response_code . ' Body: ' . wp_remote_retrieve_body($response));
            }
        }
    }

    private function add_header_row_if_needed($access_token, $sheet_id) {
        $range = 'A1:Z1';
        $response = wp_remote_get("https://sheets.googleapis.com/v4/spreadsheets/{$sheet_id}/values/{$range}", [
            'headers' => ['Authorization' => 'Bearer ' . $access_token]
        ]);

        if (is_wp_error($response)) {
            error_log('Sync Advantage: Failed to check for header row. ' . $response->get_error_message());
            return;
        }

        $body = json_decode(wp_remote_retrieve_body($response), true);
        $header_row = ['ID', 'Status', 'Name', 'Phone', 'Billing', 'Products', 'Total', 'Date'];
        $existing_header = !empty($body['values'][0]) ? array_map('trim', $body['values'][0]) : [];

        if (empty($existing_header) || count(array_diff($header_row, $existing_header)) > 0) {
            $api_url = "https://sheets.googleapis.com/v4/spreadsheets/{$sheet_id}:batchUpdate";
            $request_body = [
                'requests' => [
                    [
                        'insertDimension' => [
                            'range' => [
                                'sheetId' => 0,
                                'dimension' => 'ROWS',
                                'startIndex' => 0,
                                'endIndex' => 1
                            ],
                            'inheritFromBefore' => false
                        ]
                    ],
                    [
                        'updateCells' => [
                            'rows' => [
                                [
                                    'values' => array_map(function ($value) {
                                        return [
                                            'userEnteredValue' => ['stringValue' => strval($value)],
                                            'userEnteredFormat' => [
                                                'backgroundColor' => ['red' => 0.93, 'green' => 0.93, 'blue' => 0.93],
                                                'textFormat' => ['bold' => true]
                                            ]
                                        ];
                                    }, $header_row)
                                ]
                            ],
                            'fields' => 'userEnteredValue,userEnteredFormat',
                            'start'  => ['sheetId' => 0, 'rowIndex' => 0, 'columnIndex' => 0]
                        ]
                    ],
                    [
                        'repeatCell' => [
                            'range' => [
                                'sheetId' => 0,
                                'startRowIndex' => 1,
                                'endRowIndex' => 1000,
                            ],
                            'cell' => [
                                'userEnteredFormat' => [
                                    'backgroundColor' => ['red' => 1, 'green' => 1, 'blue' => 1],
                                    'textFormat' => ['bold' => false]
                                ]
                            ],
                            'fields' => 'userEnteredFormat(backgroundColor,textFormat)'
                        ]
                    ]
                ]
            ];

            wp_remote_post($api_url, [
                'headers' => ['Authorization' => 'Bearer ' . $access_token, 'Content-Type'  => 'application/json'],
                'body'    => json_encode($request_body),
            ]);
        }
    }

    public function export_new_order_to_google_sheet($order_id, $posted_data, $order) {
        $options = get_option($this->option_name);
        $sheet_url = $options['google_sheet_id'] ?? '';
        $sheet_id = $this->extract_sheet_id_from_url($sheet_url);
        if (empty($sheet_id)) {
            error_log('Sync Advantage: Google Sheet ID is not configured or invalid.');
            return;
        }

        $access_token = $this->refresh_google_token_if_needed();
        if (empty($access_token)) {
            error_log('Sync Advantage: Failed to get valid Google access token. Aborting new order export.');
            return;
        }

        // Ensure header row exists before appending a new order
        $this->add_header_row_if_needed($access_token, $sheet_id);

        $products_list = [];
        foreach ($order->get_items() as $item) {
            $product = $item->get_product();
            $sku = $product ? $product->get_sku() : 'N/A';
            $products_list[] = sprintf('%s | %s | %d', $item->get_name(), $sku, $item->get_quantity());
        }

        $values = [
            [
                $order->get_id(),
                str_replace('wc-', '', $order->get_status()),
                $order->get_formatted_billing_full_name(),
                $order->get_billing_phone(),
                $order->get_formatted_billing_address(),
                implode("\n", $products_list),
                $order->get_total(),
                $order->get_date_created()->date('Y-m-d H:i:s'),
            ]
        ];

        $this->append_to_google_sheet($sheet_id, $access_token, $values);
    }

    private function append_to_google_sheet($sheet_id, $access_token, $values) {
        $api_url = "https://sheets.googleapis.com/v4/spreadsheets/{$sheet_id}:batchUpdate";

        $rows_data = [];
        // Ensure $values is an array of arrays for consistency
        if (!empty($values) && !is_array($values[0])) {
            $values = [$values];
        }

        foreach ($values as $row_values) {
            $cells = array_map(function ($value) {
                return [
                    'userEnteredValue' => ['stringValue' => strval($value)],
                    'userEnteredFormat' => [
                        'backgroundColor' => ['red' => 1, 'green' => 1, 'blue' => 1], // White background
                        'textFormat' => ['bold' => false] // Not bold
                    ]
                ];
            }, $row_values);
            $rows_data[] = ['values' => $cells];
        }

        $request_body = [
            'requests' => [
                [
                    'appendCells' => [
                        'sheetId' => 0, // Assuming the first sheet
                        'rows' => $rows_data,
                        'fields' => 'userEnteredValue,userEnteredFormat'
                    ]
                ]
            ]
        ];

        $response = wp_remote_post($api_url, [
            'headers' => ['Authorization' => 'Bearer ' . $access_token, 'Content-Type'  => 'application/json'],
            'body'    => json_encode($request_body),
        ]);

        if (is_wp_error($response) || wp_remote_retrieve_response_code($response) >= 400) {
            $error_message = is_wp_error($response) ? $response->get_error_message() : wp_remote_retrieve_body($response);
            error_log('SYNC_ADVANTAGE_ERROR: Failed to append data to Google Sheet. Response: ' . $error_message);
        }
    }

    private function refresh_google_token_if_needed() {
        $options = get_option($this->option_name);
        $access_token = $options['google_access_token'] ?? '';
        $refresh_token = $options['google_refresh_token'] ?? '';
        $expires_in = $options['google_token_expires_in'] ?? 0;
        $client_id = $options['google_client_id'] ?? '';
        $client_secret = $options['google_client_secret'] ?? '';

        if (time() > $expires_in - 60) {
            $response = wp_remote_post('https://oauth2.googleapis.com/token', [
                'body' => [
                    'client_id' => $client_id,
                    'client_secret' => $client_secret,
                    'refresh_token' => $refresh_token,
                    'grant_type' => 'refresh_token',
                ],
            ]);

            if (!is_wp_error($response) && wp_remote_retrieve_response_code($response) === 200) {
                $token_data = json_decode(wp_remote_retrieve_body($response), true);
                $options['google_access_token'] = $token_data['access_token'];
                $options['google_token_expires_in'] = time() + $token_data['expires_in'];
                update_option($this->option_name, $options);
                return $options['google_access_token'];
            } else {
                error_log('Sync Advantage: Failed to refresh Google token.');
                return '';
            }
        }
        return $access_token;
    }

    public function update_order_status_in_google_sheet($order_id, $old_status, $new_status, $order) {
        $options = get_option($this->option_name);
        $sheet_id = $options['google_sheet_id'] ?? '';
        if (empty($sheet_id)) return;

        $access_token = $this->refresh_google_token_if_needed();
        if (empty($access_token)) return;

        $row_number = $this->find_row_by_order_id($order_id, $access_token, $sheet_id);
        if ($row_number) {
            $this->update_sheet_cell($sheet_id, $access_token, "B{$row_number}", str_replace('wc-', '', $new_status));
        }
    }

    private function find_row_by_order_id($order_id, $access_token, $sheet_id) {
        $api_url = "https://sheets.googleapis.com/v4/spreadsheets/{$sheet_id}/values/A:A";
        $response = wp_remote_get($api_url, ['headers' => ['Authorization' => 'Bearer ' . $access_token]]);

        if (is_wp_error($response) || wp_remote_retrieve_response_code($response) !== 200) {
            return null;
        }

        $body = json_decode(wp_remote_retrieve_body($response), true);
        $column_values = $body['values'] ?? [];

        foreach ($column_values as $index => $cell) {
            if (isset($cell[0]) && $cell[0] == $order_id) {
                return $index + 1;
            }
        }
        return null;
    }

    public function check_google_sheet_for_new_orders() {
        $options = get_option($this->option_name);
        $sheet_url = $options['google_sheet_id'] ?? '';
        $sheet_id = $this->extract_sheet_id_from_url($sheet_url);
        $results = ['updated' => 0, 'created' => 0];

        if (empty($sheet_id)) {
            error_log('Sync Advantage: Google Sheet ID is not configured or invalid.');
            return $results;
        }

        $access_token = $this->refresh_google_token_if_needed();
        if (empty($access_token)) {
            return $results;
        }

        $api_url = "https://sheets.googleapis.com/v4/spreadsheets/{$sheet_id}/values/Sheet1!A2:Z";
        $response = wp_remote_get($api_url, ['headers' => ['Authorization' => 'Bearer ' . $access_token]]);

        if (is_wp_error($response) || wp_remote_retrieve_response_code($response) !== 200) {
            error_log('Sync Advantage: Failed to fetch sheet data. ' . wp_remote_retrieve_body($response));
            return $results;
        }

        $body = json_decode(wp_remote_retrieve_body($response), true);
        $rows = $body['values'] ?? [];

        if (empty($rows)) {
            return $results;
        }

        foreach ($rows as $row_index => $row) {
            $order_id_val = $row[0] ?? null;

            // Skip row if Order ID is completely empty.
            if (empty($order_id_val)) {
                continue;
            }

            // Check if the Order ID is a valid numeric ID.
            $is_existing_order = is_numeric($order_id_val) && intval($order_id_val) > 0;
            $order = $is_existing_order ? wc_get_order(intval($order_id_val)) : null;
            $sheet_status_val = isset($row[1]) ? strtolower(trim($row[1])) : '';

            if (empty($sheet_status_val)) {
                continue; // Skip if status is empty.
            }

            if ($order) {
                // Existing order: Update status if it has changed.
                $current_status = $order->get_status();
                if ($current_status !== $sheet_status_val) {
                    $valid_statuses = array_keys(wc_get_order_statuses());
                    if (in_array('wc-' . $sheet_status_val, $valid_statuses)) {
                        $order->update_status($sheet_status_val, 'Order status updated from Google Sheet.', true);
                        $results['updated']++;
                    } else {
                        error_log("Sync Advantage: Attempted to sync invalid status '{$sheet_status_val}' for order #{$order_id_val}. Current status is: {$current_status}");
                    }
                }
            } else {
                // New order: Create it in WooCommerce.
                try {
                    $new_order = $this->create_wc_order_from_sheet_row($row);
                    if ($new_order) {
                        $results['created']++;
                        $new_order_id = $new_order->get_id();
                        // Update the Google Sheet with the new Order ID.
                        $range = 'Sheet1!A' . ($row_index + 2); // +2 because sheets are 1-indexed and we skip the header.
                        $this->update_sheet_cell($sheet_id, $access_token, $range, $new_order_id);
                    }
                } catch (\Exception $e) {
                    error_log('Sync Advantage: Error creating order from sheet: ' . $e->getMessage());
                }
            }
        }

        return $results;
    }

    private function update_sheet_cell($sheet_id, $access_token, $range, $value) {
        $api_url = "https://sheets.googleapis.com/v4/spreadsheets/{$sheet_id}:batchUpdate";

        $request_body = [
            'requests' => [
                [
                    'updateCells' => [
                        'rows' => [
                            [
                                'values' => [
                                    [
                                        'userEnteredValue' => ['stringValue' => strval($value)],
                                        'userEnteredFormat' => [
                                            'backgroundColor' => ['red' => 1, 'green' => 1, 'blue' => 1], // White background
                                            'textFormat' => ['bold' => false] // Not bold
                                        ]
                                    ]
                                ]
                            ]
                        ],
                        'fields' => 'userEnteredValue,userEnteredFormat',
                        'start'  => ['sheetId' => 0, 'rowIndex' => 0, 'columnIndex' => 0],
                    ]
                ]
            ]
        ];

        wp_remote_post($api_url, [
            'headers' => [
                'Authorization' => 'Bearer ' . $access_token,
                'Content-Type'  => 'application/json'
            ],
            'body'    => json_encode($request_body),
        ]);
    }

    private function create_wc_order_from_sheet_row($row) {
        $billing_name = $row[3] ?? '';
        $billing_email = $row[4] ?? '';
        $products_list_str = $row[5] ?? '';
        $total = $row[6] ?? 0;
        $currency = $row[7] ?? get_woocommerce_currency();
        $payment_method_title = $row[8] ?? '';
        $shipping_address_str = $row[10] ?? '';
        $customer_note = $row[12] ?? '';

        if (empty($billing_email) || empty($products_list_str)) {
            throw new \Exception('Email and product information are required to create an order.');
        }

        $address = ['email' => $billing_email];
        $name_parts = explode(' ', $billing_name, 2);
        $address['first_name'] = $name_parts[0];
        $address['last_name'] = $name_parts[1] ?? '';

        $order = wc_create_order();
        $order->set_address($address, 'billing');
        if (!empty($shipping_address_str)) {
            $shipping_address = ['first_name' => $address['first_name'], 'last_name' => $address['last_name'], 'address_1' => $shipping_address_str];
            $order->set_address($shipping_address, 'shipping');
        }

        // Parse and add products
        $product_lines = explode("\n", $products_list_str);
        foreach ($product_lines as $line) {
            $parts = explode('|', $line);
            if (count($parts) < 3) continue;

            $product_name = trim($parts[0]);
            $sku = trim($parts[1]);
            $quantity = intval(trim($parts[2]));

            $product_id = !empty($sku) && $sku !== 'N/A' ? wc_get_product_id_by_sku($sku) : 0;
            if (!$product_id) {
                $posts = get_posts(['post_type' => 'product', 'name' => sanitize_title($product_name), 'posts_per_page' => 1, 'fields' => 'ids']);
                $product_id = $posts[0] ?? 0;
            }

            if ($product_id) {
                $product = wc_get_product($product_id);
                if ($product) {
                    $order->add_product($product, $quantity);
                }
            } else {
                $order->add_fee($product_name, 0);
            }
        }

        $order->set_customer_note($customer_note);
        $order->set_currency($currency);
        $order->set_payment_method_title($payment_method_title);
        $order->set_total($total);

        $order->calculate_totals();
        $order->save();

        return $order;
    }

    public function export_new_orders_to_google_sheet() {
        $options = get_option($this->option_name);
        $access_token = $options['google_access_token'] ?? '';
        $sheet_id = $options['google_sheet_id'] ?? '';

        if (empty($access_token) || empty($sheet_id)) {
            return 0;
        }

        $this->refresh_google_token_if_needed();
        $access_token = get_option($this->option_name)['google_access_token'];

        $orders = wc_get_orders([
            'limit' => -1,
            'meta_query' => [
                [
                    'key' => '_unlockafe_synced_to_google_sheets',
                    'compare' => 'NOT EXISTS'
                ]
            ]
        ]);

        if (empty($orders)) {
            return 0;
        }

        $values = [];
        foreach ($orders as $order) {
            if (!is_a($order, 'WC_Order')) {
                continue;
            }
            $products_list = [];
            foreach ($order->get_items() as $item) {
                $product = $item->get_product();
                $sku = $product ? $product->get_sku() : 'N/A';
                $products_list[] = sprintf('%s | %s | %d', $item->get_name(), $sku, $item->get_quantity());
            }
            $values[] = [
                $order->get_id(),
                $order->get_status(),
                $order->get_date_created()->date('Y-m-d H:i:s'),
                $order->get_total(),
                $order->get_billing_email(),
                $order->get_billing_phone(),
                $order->get_formatted_shipping_address(),
                implode("\n", $products_list),
                $order->get_customer_note()
            ];
        }

        if (!empty($values)) {
            $this->append_to_google_sheet($sheet_id, $access_token, $values);
            foreach ($orders as $order) {
            if (!is_a($order, 'WC_Order')) {
                continue;
            }
                $order->update_meta_data('_unlockafe_synced_to_google_sheets', true);
                $order->save();
            }
        }

        return count($orders);
    }

    public function send_order_to_webhook_on_status_change($order_id, $old_status, $new_status, $order) {
        $this->send_order_to_webhook($order_id);
    }

    public function send_order_to_webhook($order_id) {
        if (!$order_id) {
            return;
        }

        $options = get_option($this->option_name);
        $webhook_url = isset($options['google_apps_script_url']) ? $options['google_apps_script_url'] : '';

        if (empty($webhook_url) || !filter_var($webhook_url, FILTER_VALIDATE_URL)) {
            return; // No valid URL, so we do nothing.
        }

        $order = wc_get_order($order_id);
        if (!$order) {
            return;
        }

        // Prevent sending data for refunds or other non-order types
        if (!is_a($order, 'WC_Order')) {
            return;
        }

        $line_items = [];
        foreach ($order->get_items() as $item_id => $item) {
            $product = $item->get_product();
            $line_items[] = [
                'product_id' => $item->get_product_id(),
                'name'       => $item->get_name(),
                'quantity'   => $item->get_quantity(),
                'total'      => $item->get_total(),
                'sku'        => $product ? $product->get_sku() : '',
            ];
        }

        $data = [
            'order_id'       => $order->get_id(),
            'status'         => $order->get_status(),
            'total'          => $order->get_total(),
            'currency'       => $order->get_currency(),
            'payment_method' => $order->get_payment_method_title(),
            'billing'        => $order->get_address('billing'),
            'shipping'       => $order->get_address('shipping'),
            'line_items'     => $line_items
        ];

        $response = wp_remote_post($webhook_url, [
            'method'      => 'POST',
            'timeout'     => 45,
            'redirection' => 5,
            'httpversion' => '1.0',
            'blocking'    => true, // Use true to wait for response, false to fire and forget
            'headers'     => ['Content-Type' => 'application/json; charset=utf-8'],
            'body'        => json_encode($data),
            'cookies'     => []
        ]);

        if (is_wp_error($response)) {
            error_log('UnlockAddons Webhook Error for Order ' . $order_id . ': ' . $response->get_error_message());
        } else {
            $response_code = wp_remote_retrieve_response_code($response);
            if ($response_code >= 400) {
                error_log('UnlockAddons Webhook HTTP Error for Order ' . $order_id . ': ' . $response_code . ' - ' . wp_remote_retrieve_body($response));
            } else {
                error_log('UnlockAddons: Order ' . $order_id . ' data sent to webhook successfully.');
            }
        }
    }

    public function toggle_background_sync_cron($old_value, $new_value) {
        $cron_hook = 'unlockafe_sync_advantage_cron';
        $is_enabled_now = !empty($new_value['enable_background_sync']) && $new_value['enable_background_sync'] === '1';
        $is_scheduled = wp_next_scheduled($cron_hook);

        if ($is_enabled_now && !$is_scheduled) {
            wp_schedule_event(time(), 'fifteen_minutes', $cron_hook);
            error_log('SYNC_ADVANTAGE_DEBUG: Background sync cron job scheduled.');
        } elseif (!$is_enabled_now && $is_scheduled) {
            wp_clear_scheduled_hook($cron_hook);
            error_log('SYNC_ADVANTAGE_DEBUG: Background sync cron job cleared.');
        }
    }

    /**
     * Extracts the Google Sheet ID from a URL.
     *
     * @param string $url The Google Sheet URL.
     * @return string|null The sheet ID or null if not found.
     */
    private function extract_sheet_id_from_url($url) {
        if (empty($url) || !is_string($url)) {
            return null;
        }

        // If it doesn't look like a URL, assume it's already an ID.
        if (filter_var($url, FILTER_VALIDATE_URL) === FALSE) {
            return $url;
        }

        $matches = [];
        // Regex to find the ID in a Google Sheet URL.
        if (preg_match('/\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/', $url, $matches)) {
            return $matches[1];
        }

        return null; // Return null if no ID is found
    }

    public function send_order_to_google_sheets($order_id, $old_status = '', $new_status = '', $order = null) {
        $options = get_option($this->option_name);
        $url = $options['webhook_url'] ?? '';

        error_log('SYNC_ADVANTAGE_DEBUG: Attempting to send webhook to URL: ' . $url);

        if (empty($url)) {
            return; // No webhook URL configured
        }

        if (!$order) {
            $order = wc_get_order($order_id);
        }
        if (!$order) {
            error_log('Sync Advantage Webhook: Invalid order ID: ' . $order_id);
            return;
        }

        $order_data = array(
            'id'       => $order->get_id(),
            'status'   => $order->get_status(),
            'name'     => $order->get_billing_first_name() . ' ' . $order->get_billing_last_name(),
            'phone'    => $order->get_billing_phone(),
            'billing'  => implode(', ', array_filter(array(
                $order->get_billing_address_1(),
                $order->get_billing_address_2(),
                $order->get_billing_city(),
                $order->get_billing_state(),
                $order->get_billing_postcode(),
                $order->get_billing_country(),
                $order->get_billing_email()
            ))) ,
            'products' => implode(', ', array_map(function($item) {
                return $item->get_name();
            }, $order->get_items())),
            'total'    => $order->get_total(),
            'date'     => $order->get_date_created() ? $order->get_date_created()->date('Y-m-d') : ''
        );

        $data_json = wp_json_encode($order_data);
        error_log('SYNC_ADVANTAGE_DEBUG: Webhook JSON Payload: ' . $data_json);
        $args = array(
            'body'    => $data_json,
            'headers' => array(
                'Content-Type' => 'application/json',
            ),
        );

        $response = wp_remote_post($url, $args);

        if (is_wp_error($response)) {
            error_log('Sync Advantage Webhook Error: ' . $response->get_error_message());
        } else {
            $response_code = wp_remote_retrieve_response_code($response);
            $response_body = wp_remote_retrieve_body($response);
            $response_headers = wp_remote_retrieve_headers($response);

            if ($response_code !== 200) {
                error_log('Sync Advantage Webhook: Unexpected response code: ' . $response_code);
                error_log('Sync Advantage Webhook Response Body: ' . $response_body);
                error_log('Sync Advantage Webhook Response Headers: ' . print_r($response_headers, true));
            } else {
                error_log('Sync Advantage Webhook: Successfully sent. Response code: ' . $response_code);
            }
        }
    }

    public function ajax_manual_sync() {
        error_log('SYNC_ADVANTAGE_DEBUG: ajax_manual_sync started.');
        error_log('SYNC_ADVANTAGE_DEBUG: POST data: ' . print_r($_POST, true));

        try {
            // Check user permissions first
            if (!current_user_can('manage_woocommerce')) {
                wp_send_json_error(['message' => 'Permission denied.']);
                return;
            }
            
            // Verify nonce
            if (!isset($_POST['nonce']) || !wp_verify_nonce(sanitize_text_field($_POST['nonce']), 'sync_advantage_ajax_nonce')) {
                error_log('SYNC_ADVANTAGE_DEBUG: Nonce verification failed. Received: ' . ($_POST['nonce'] ?? 'none'));
                wp_send_json_error(['message' => 'Security check failed.']);
                return;
            }

            $exported_count = $this->export_new_orders_to_google_sheet();
            $import_results = $this->check_google_sheet_for_new_orders();

            // Defensive coding: ensure keys exist before accessing them.
            $created_count = isset($import_results['created']) ? $import_results['created'] : 0;
            $updated_count = isset($import_results['updated']) ? $import_results['updated'] : 0;

            $message = sprintf(
                __('Manual sync completed.<br> - Orders exported to Google Sheet: %d<br> - New orders created from Google Sheet: %d<br> - Orders updated from Google Sheet: %d', 'unlock-addons-for-elementor'),
                $exported_count,
                $created_count,
                $updated_count
            );

            wp_send_json_success(['message' => $message]);
        } catch (\Throwable $e) {
            error_log('SYNC_ADVANTAGE_ERROR: ' . $e->getMessage() . ' in ' . $e->getFile() . ' on line ' . $e->getLine());
            wp_send_json_error(['message' => 'Fatal error during sync: ' . $e->getMessage()]);
        }
    }

    private function get_order_data($order) {
        if (!is_a($order, 'WC_Order')) {
            return []; // Return an empty array to avoid errors
        }

        $billing_address = implode(', ', array_filter([
            $order->get_billing_address_1(),
            $order->get_billing_address_2(),
            $order->get_billing_city(),
            $order->get_billing_state(),
            $order->get_billing_postcode(),
            $order->get_billing_country(),
            $order->get_billing_email()
        ]));

        $products_list = array_map(function($item) {
            return $item->get_name();
        }, $order->get_items());

        return [
            $order->get_id(),
            $order->get_status(),
            $order->get_billing_first_name() . ' ' . $order->get_billing_last_name(),
            $order->get_billing_phone(),
            $billing_address,
            implode(', ', $products_list),
            $order->get_total(),
            $order->get_date_created() ? $order->get_date_created()->date('Y-m-d') : '',
        ];
    }

    public function export_all_orders_to_google_sheet() {
        error_log('SYNC_ADVANTAGE_DEBUG: export_all_orders_to_google_sheet started.');
        error_log('SYNC_ADVANTAGE_DEBUG: POST data: ' . print_r($_POST, true));
        
        // Check user permissions first
        if (!current_user_can('manage_woocommerce')) {
            wp_send_json_error('Permission denied.');
            return;
        }
        
        // Verify nonce
        if (!isset($_POST['nonce']) || !wp_verify_nonce(sanitize_text_field($_POST['nonce']), 'sync_advantage_ajax_nonce')) {
            error_log('SYNC_ADVANTAGE_DEBUG: Export nonce verification failed. Received: ' . ($_POST['nonce'] ?? 'none'));
            wp_send_json_error('Security check failed.');
            return;
        }
        $options = get_option($this->option_name);
        $sheet_url = $options['google_sheet_id'] ?? '';
        $sheet_id = $this->extract_sheet_id_from_url($sheet_url);

        if (empty($sheet_id)) {
            wp_send_json_error('Google Sheet ID is not configured or invalid. Please check settings.');
            return;
        }

        $access_token = $this->refresh_google_token_if_needed();
        if (empty($access_token)) {
            wp_send_json_error('Failed to refresh Google access token. Please reconnect your account.');
            return;
        }

        $orders = wc_get_orders(['limit' => -1, 'orderby' => 'ID', 'order' => 'ASC']);
        if (empty($orders)) {
            wp_send_json_success('No orders to export.');
            return;
        }

        $clear_url = "https://sheets.googleapis.com/v4/spreadsheets/{$sheet_id}/values/A:Z:clear";
        error_log('SYNC_ADVANTAGE_DEBUG: Clearing sheet with URL: ' . $clear_url);
        $clear_response = wp_remote_post($clear_url, ['method'  => 'POST', 'headers' => ['Authorization' => 'Bearer ' . $access_token]]);

        if (is_wp_error($clear_response) || wp_remote_retrieve_response_code($clear_response) >= 400) {
            error_log('SYNC_ADVANTAGE_ERROR: Failed to clear Google Sheet. Response: ' . wp_remote_retrieve_body($clear_response));
        }

        $this->add_header_row_if_needed($access_token, $sheet_id);

        $values = [];
        foreach ($orders as $order) {
            if (is_a($order, 'WC_Order')) {
                $values[] = $this->get_order_data($order);
            }
        }

        $this->append_to_google_sheet($sheet_id, $access_token, $values);

        // Set data validation for the status column
        $this->set_status_column_data_validation($sheet_id, $access_token);

        wp_send_json_success(['message' => 'All orders have been exported successfully.']);
    }
}

Sync_Advantage_Core::instance();
