<?php
namespace ARCHM\Migrator;

if ( ! defined('ABSPATH') ) {
    // phpcs:ignore Universal.PHP.RequireExitDieParentheses.Missing
    exit;
}

use ARCHM\Features\ActivityLogDB;
use ARCHM\Features\BackgroundProcess;
use ARCHM\Features\Sqlite;
use ARCHM\Helpers\DBHelper;
use ARCHM\Helpers\Logger;

class WpDbMigration {

    use DBHelper;

    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private string $post_type;
    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    public DatabaseCreator $database_creator;
    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found, WordPress.NamingConventions.ValidVariableName.PropertyNotSnakeCase
    protected ActivityLogDB $log_DB;
    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    protected BackgroundProcess $background_process;
    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private int $chunk_size;

    /**
     * Singleton instance
     *
     * @var self
     */
    private static $instance;

    /**
     * Returns the singleton instance
     *
     * @return self
     */
    public static function get_instance() {
        if ( ! self::$instance ) {
            self::$instance = new self();
        }

        return self::$instance;
    }

    /**
     * Constructor
     *
     * @throws Exception
     * @since 1.0
     */
    public function __construct() {
        $this->post_type = ARCHM_POST_TYPE;
        $this->chunk_size = ARCHM_CHUNK_SIZE;
        $this->set_remote_db_settings();
        $this->set_main_deb_settings();
        // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
        $this->log_DB = new ActivityLogDB();
        $this->background_process = new BackgroundProcess();
        // $this->check_background_process();

        add_action('admin_init', [ $this, 'retrieve_and_process_search_results' ], 10);
        add_action('woocommerce_order_query', [ $this, 'set_wc_order_query_arg' ], 10, 2);
        add_action('wp_ajax_archm_restore_post', [ $this, 'restore_order_ajax_handler' ]);
    }

    /**
     * Initialize database creator
     *
     * @return void
     * @throws Exception
     * @author Siam Maruf
     * @since 1.0
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.voidFound
    private function initialize_classes(): void {
        try {
            $this->database_creator = new DatabaseCreator();
        } catch ( \Exception $e ) { // phpcs:ignore Generic.CodeAnalysis.EmptyStatement.DetectedCatch
            // Logger::logError('Error: ' . $e->getMessage());
        }
    }

    /**
     * Crete Database
     *
     * @return array
     * @since 1.0
     * @author Siam Maruf
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.arrayFound
    public function create_database(): array {
        return $this->check_db_connection($this->db_host, $this->db_port, $this->db_user, $this->db_pass, $this->db_name, true);
    }

    /**
     * Create database schema
     *
     * @return array
     * @throws Exception
     * @author Siam Maruf
     * @since 1.0
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.arrayFound
    public function create_schema(): array {
        global $wp_rewrite;
        $this->initialize_classes();
        $result['status'] = false;
        $result['message'] = 'Error to create database!';
        $response = $this->database_creator->install_word_press_schema_remote();
        if ( $response['status'] ) {
            $result['status'] = true;
            $result['message'] = 'Schema created successfully! Start archiving';
        }
        $result['wpSchema'] = true;

        $wp_rewrite->set_permalink_structure('/%postname%/');
        $wp_rewrite->flush_rules();

        return $result;
    }

    /**
     * Get schema status
     *
     * @return array
     * @throws Exception
     * @author Siam Maruf
     * @since 1.0
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.arrayFound
    public function get_schema_status(): array {
        $this->initialize_classes();
        return $this->database_creator->check_remote_schema($this->main_db_name);
    }

    /**
     * Start archiving process
     *
     * @return array
     * @throws Exception
     * @author Siam Maruf
     * @since 1.0
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.arrayFound
    public function start_archiving(): array {
        try {
            $this->initialize_classes();
            $response = [];
            $archive_time_frame = get_option('archm_archive_range');

            if ( ! empty($archive_time_frame) ) {
                $archive_schedule = get_option('archm_archive_range_datetime');
                $result = $this->database_creator->copy_post_data($archive_schedule, $this->background_process);

                if ( $result['status'] ) {
                    if ( isset($result['error']) && $result['error'] ) {
                        $response['status'] = true;
                        $response['error'] = true;
                        $response['message'] = 'No orders found for archive!';
                    } else {
                        $response['message'] = 'Post data copied successfully for post type';
                        $response['status'] = true;
                        $response['remotePostCount'] = (int) $result['queue'];
                        $response['mainPostCount'] = $result['mainPostCount'];
                        $response['bgProcess'] = false;
                        if ( $response['remotePostCount'] === $response['mainPostCount'] && 0 !== $response['remotePostCount'] && 0 !== $response['mainPostCount'] ) {
                            $response['archiveLoop'] = false;
                            delete_option('archm_queue_ids');
                        } else {
                            $response['archiveLoop'] = true;
                        }

                        if ( $response['archiveLoop'] ) {
                            $bg_process = get_option('archm_background_process_running');
                            if ( true === $bg_process ) {
                                $response['bgProcess'] = true;
                            }
                        }
                    }
                } else {
                    $response['message'] = 'There are no orders to archive according to your selected option. Update the old order range to archive older orders.';
                    $response['status'] = false;
                }
            } else {
                $response['message'] = 'Invalid or empty archive schedule';
                $response['status'] = false;
            }

            return $response;
        } catch ( \Exception $exception ) {
            return array(
				'status' => false,
				'message' => $exception->getMessage(),
			);
        }
    }

    /**
     * Delete archive orders from host database
     *
     * @return array
     * @throws Exception
     * @author Siam Maruf
     * @since 1.0
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.arrayFound
    public function delete_orders(): array {
        $this->initialize_classes();
        return $this->database_creator->remove_orders($this->post_type, $this->main_db_name, $this->chunk_size);
    }

    /**
     * Modify Post Search
     *
     * @param $query
     *
     * @return void
     * @since 1.0
     * @author Siam Maruf
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.voidFound
    public function modify_all_order_search( $query ): void {
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        if ( is_admin() && isset($_GET['post_type']) && $_GET['post_type'] === $this->post_type && isset($_GET['s']) ) {
            $query->set('s', sanitize_textarea_field(wp_unslash($_GET['s']))); // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        }
    }

    /**
     * Retrieve and process search result
     *
     * @return void
     * @since 1.0
     * @author Siam Maruf
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.voidFound
    public function retrieve_and_process_search_results(): void {

        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        if ( isset($_GET['page']) && 'wc-orders' === $_GET['page'] ) {
            // phpcs:ignore WordPress.Security.NonceVerification.Recommended
            if ( ! empty($_GET['s']) ) {
                $search_term = sanitize_text_field(wp_unslash($_GET['s'])); // phpcs:ignore WordPress.Security.NonceVerification.Recommended

                // phpcs:ignore WordPress.Security.NonceVerification.Recommended
                if ( isset($_GET['search-filter']) ) {
                    $search_filter = sanitize_text_field(wp_unslash($_GET['search-filter'])); // phpcs:ignore WordPress.Security.NonceVerification.Recommended
                }

                $wc_order_q = new \WC_Order_Query();
                $wc_args = $wc_order_q->get_query_vars();
                $wc_args['s'] = $search_term;
                $wc_args['search_filter'] = $search_filter;

                $wc_orders = \WC_Data_Store::load('order')->query($wc_args);

                if ( ! empty($wc_orders) ) {
                    update_option('archm_db_switch', 0);
                } else {
                    update_option('archm_db_switch', 1);
                }
            } else {
                update_option('archm_db_switch', 0);
            }
        }
    }

    /**
     * Restore The Order
     *
     * @return void
     * @since 1.0
     * @author Siam Maruf
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.voidFound
    public function restore_order_ajax_handler(): void {
        check_ajax_referer('archm-admin-nonce', 'nonce');

        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        if ( ! isset($_POST['post_id']) ) {
            wp_send_json_error(__('Post ID is missing.', 'archive-master'));
            wp_die();
        }

        $post_id = intval($_POST['post_id']);
        $selected_database = get_option('archm_selected_database');
        $archived_order = '';

        // Always check SQLite first (primary storage)
        $sqdb = Sqlite::get_instance();
        $archived_order = $sqdb->get_archived_order($post_id);

        // If not found in SQLite and RDS/Custom MySQL is selected, check remote DB as fallback
        if ( ! $archived_order && ( 'rds' === $selected_database || 'custom_mysql' === $selected_database ) ) {
            // Set remote db instance
            $this->set_db_instance();

            // Check if remote_db was initialized
            if ( null === $this->remote_db ) {
                wp_send_json_error(__('Remote database connection not available.', 'archive-master'));
                wp_die();
            }

            $table_name = $this->wpdb->prefix . 'archived_orders';
            $query = $this->remote_db->prepare('select * from %i where archived_order_id = %d', $table_name, $post_id); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $result = $this->remote_db->get_results($query, ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            if ( ! empty($result[0]) ) {
                $archived_order = $result[0];
            }
        }

        if ( ! $archived_order ) {
            // Logger::logMessage('Post not found in the remote database.');
            wp_send_json_error(__('Post not found in the remote database.', 'archive-master'));
            wp_die();
        }

        if ( $archived_order ) {

            $order_data = json_decode($archived_order['order_data'], true);
            $ordered_product = json_decode($archived_order['ordered_products'], true);

            //Create a new order in the remote database
            $order = new \WC_Order(); // Instantiate a new WC_Order object

            // Map SQLite fields to WooCommerce order fields
            $order->set_id($order_data['id']);
            $order->set_customer_id($order_data['customer_id']);
            $order->set_date_created($order_data['date_created']['date']);
            $order->set_currency($order_data['currency']);
            $order->set_total($order_data['total']);
            $order->set_status($order_data['status']);
            $order->set_prices_include_tax($order_data['prices_include_tax']);

            // Copy billing and shipping data
            $order->set_billing_first_name($order_data['billing']['first_name']);
            $order->set_billing_last_name($order_data['billing']['last_name']);
            $order->set_billing_city($order_data['billing']['city']);
            $order->set_billing_postcode($order_data['billing']['postcode']);
            $order->set_billing_country($order_data['billing']['country']);
            $order->set_billing_email($order_data['billing']['email']);
            $order->set_billing_phone($order_data['billing']['phone']);

            $order->set_shipping_address_1($order_data['shipping']['address_1']);
            $order->set_shipping_address_2($order_data['shipping']['address_2']);
            $order->set_shipping_city($order_data['shipping']['city']);
            $order->set_shipping_postcode($order_data['shipping']['postcode']);
            $order->set_shipping_country($order_data['shipping']['country']);

            //add product in main order
            foreach ( $ordered_product as $item ) {
                // Check if this is a variable product with a variation
                // If variation_id exists and is not 0, load the variation product instead of parent
                $product_id = isset($item['variation_id']) && ! empty($item['variation_id']) && (int) $item['variation_id'] > 0
                    ? (int) $item['variation_id']
                    : (int) $item['product_id'];

                $product = wc_get_product($product_id); // Retrieve the product object

                if ( $product ) {
                    $quantity = $item['quantity'];
                    $order->add_product($product, $quantity); // Add the product to the new order
                }
            }

            // Save the new order in the remote database
            $main_order_id = $order->save();

            // Always delete from SQLite first (primary storage)
            $sqdb->delete_archived_order($post_id);

            // Delete from secondary storage if RDS is selected
            if ( 'rds' === $selected_database ) {
                // Set remote db instance
                $this->set_db_instance();

                $where = [ 'archived_order_id' => $post_id ];
                $format = [ '%d' ];
                $deleted = $this->remote_db->delete($table_name, $where, $format);

                // if ( false === $deleted ) {
                //     // Logger::logMessage('Warning: Failed to delete from RDS (secondary storage) for order ' . esc_html($post_id));
                //     // Don't fail the entire operation if secondary storage deletion fails
                // }
            }

            if ( ! $main_order_id ) {
                wp_send_json_error(__('Failed to restore the order.', 'archive-master'));
                wp_die();
            }

            wp_send_json_success(__('Order restored successfully.', 'archive-master'));
            wp_die();
        } else {
            wp_send_json_error(__('Failed to restore the order.', 'archive-master'));
            wp_die();
        }
    }

    /**
     * Get Total Orders for archive
     *
     * @throws Exception
     * @since 1.0
     * @author Siam Maruf
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.intFound, PHPCompatibility.FunctionDeclarations.NewNullableTypes.returnTypeFound
    public function get_total_orders(): ?int {
        $this->initialize_classes();
        $archive_schedule = get_option('archm_archive_range_datetime');
        return $this->database_creator->get_total_post_count($this->post_type, $archive_schedule);
    }

    /**
     * Check Background Process
     *
     * @return void
     * @throws Exception
     * @author Siam Maruf
     * @since 1.0
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.voidFound
    public function check_background_process(): void {
        if ( $this->background_process->is_processing() ) {
            update_option('archm_archive_completed', false);
            // Logger::logMessage('Background processing is already running.');
            // phpcs:ignore Generic.CodeAnalysis.EmptyStatement.DetectedElse
        } else {
            // Logger::logMessage('Background process completed !');
            // phpcs:ignore Generic.CodeAnalysis.EmptyStatement.DetectedElse
        }
    }

    /**
     * Set WooCommerce Query Argument
     *
     * @return orders
     */
    public function set_wc_order_query_arg( $wc_orders, $args ) {
        if ( empty($wc_orders->orders) ) {
            if ( isset($args['search_filter']) ) {
                $this->initialize_classes();
                if ( 'order_id' === $args['search_filter'] ) {
                    $selected_database = get_option('archm_selected_database');

                    // Always check SQLite first (primary storage)
                    $sqdb = Sqlite::get_instance();
                    $archived_order = $sqdb->get_archived_order($args['s']);
                    $data_source = 'sqlite';

                    // If not found in SQLite and RDS is selected, check RDS as fallback
                    if ( ! $archived_order && 'rds' === $selected_database ) {
                        // Set remote db instance
                        $this->set_db_instance();

                        $table_name = $this->wpdb->prefix . 'archived_orders';

                        $query = $this->remote_db->prepare('select * from %i where archived_order_id = %d', $table_name, (int) $args['s']); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

                        $get_archived_orders = $this->remote_db->get_results($query, ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

                        if ( ! empty($get_archived_orders[0]) ) {
                            $archived_order = $get_archived_orders[0];
                            $data_source = 'rds';
                        }
                    }

                    if ( $archived_order ) {
                        // Logger::logMessage('Data found: ' . $data_source);
                        // Logger::logMessage(json_decode($archived_order['order_data'], true));
                        // Map SQLite order to WooCommerce order object
                        $wc_orders->orders = [ $this->map_sqlite_order_to_wc_order($archived_order) ];
                        return $wc_orders;
                    }

                    $wc_orders = \WC_Data_Store::load('order')->query($args);
                    return $wc_orders;
                }
            }
        }
        return $wc_orders;
    }

    /**
     * Map SQLite to WooCommerce order
     *
     * @param array $archived_orde
     * @return object
     */
    public function map_sqlite_order_to_wc_order( $archived_orde ) {
        $order = new \WC_Order();

        $oder_data = json_decode($archived_orde['order_data'], true);

        // Map SQLite fields to WooCommerce order fields
        $order->set_id($oder_data['id']);
        $order->set_customer_id($oder_data['customer_id']);
        $order->set_date_created($oder_data['date_created']['date']);
        $order->set_currency($oder_data['currency']);
        $order->set_total($oder_data['total']);
        $order->set_status($oder_data['status']);
        $order->set_prices_include_tax($oder_data['prices_include_tax']);

        // Copy billing and shipping data
        $order->set_billing_first_name($oder_data['billing']['first_name']);
        $order->set_billing_last_name($oder_data['billing']['last_name']);

        // Add more mappings as needed
        return $order;
    }
}
