<?php
namespace ARCHM\Migrator;

use ARCHM\Features\ActivityLogDB;
use ARCHM\Features\BackgroundProcess;
use ARCHM\Helpers\WcStorageHandler;
use ARCHM\Helpers\BGProcessHelper;
use ARCHM\Helpers\DBHelper;
use ARCHM\Helpers\Logger;

class DatabaseCreator {

    use BGProcessHelper;
    use DBHelper;
    use WcStorageHandler;

    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    protected ActivityLogDB $log_db;

    /**
     * Constructor
     *
     * @since 1.0
     */
    public function __construct() {
        $this->set_remote_db_settings();
        $this->set_main_deb_settings();
        $this->set_db_instance();
        $this->log_db = new ActivityLogDB();
    }

    /**
     * Check remote schema
     *
     * @since 1.0
     * @author Siam Maruf
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.arrayFound
    public function check_remote_schema( $main_db_name ): array {
        $message = [];

        $tables = $this->remote_db->get_col('SHOW TABLES');

        if ( empty($tables) ) {
            $message['message'] = 'Empty Database. creating schema!';
            $message['status']  = false;
        } else {
            $message['status']  = true;
            $message['message'] = 'WordPress schema is already installed! start archiving';
        }

        return $message;
    }

    /**
     * Install WordPress schema remotely
     *
     * @since 1.0
     * @author Siam Maruf
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.arrayFound
    public function install_word_press_schema_remote(): array {
        $result['status']  = false;
        $result['message'] = 'Error to create database!';

        $charset_collate = $this->wpdb->get_charset_collate();

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

        $sql = "CREATE TABLE IF NOT EXISTS `$table_name` (
            `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            `archived_order_id` INT(11) NOT NULL,
            `order_data` LONGTEXT NOT NULL,
            `ordered_products` LONGTEXT NOT NULL,
            `date` datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            PRIMARY KEY (`id`)
        ) $charset_collate;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        global $wpdb;
        $original_wpdb = $wpdb;
        $wpdb          = $this->remote_db; // phpcs:ignore WordPress.WP.GlobalVariablesOverride.Prohibited

        // Run dbDelta
        dbDelta($sql);

        // Restore the original $wpdb
        $wpdb = $original_wpdb; // phpcs:ignore WordPress.WP.GlobalVariablesOverride.Prohibited

        // Check if the table exists after running the query
        $table_exists = $this->remote_db->get_var("SHOW TABLES LIKE '$table_name'");

        // Return true if the table exists, false otherwise
        if ( $table_exists === $table_name ) {
            $result['status']  = true;
            $result['message'] = 'Schema created successfully! Start archiving';

            // Create analytics cache table
            $this->create_analytics_cache_table_remote();
        }

        return $result;
    }

    /**
     * Create analytics cache table in remote database (Pro feature)
     *
     * @return bool True on success, false on failure
     */
    private function create_analytics_cache_table_remote() {
        // Analytics cache table is Pro-only
        if ( ! function_exists('master_archive_pro') || ! class_exists('\ARCHM_PRO\Features\AnalyticsCacheTable') ) {
            return false;
        }

        try {
            $cache_table = \ARCHM_PRO\Features\AnalyticsCacheTable::get_instance();
            return $cache_table->create_table_remote();
        } catch ( \Exception $e ) {
            // Log error but don't fail the main schema creation
            // \ARCHM\Helpers\Logger::logMessage('Failed to create analytics cache table in remote database: ' . $e->getMessage());
            return false;
        }
    }

    /**
     * Install WooCommerce schema remotely
     *
     * @return void
     */
    public function install_woo_commerce_schema_remote( $remote_db_name ) {
        if ( ! class_exists('WooCommerce') ) {
            return;
        }

        \WC_Install::create_tables(); // Create WooCommerce tables
    }

    /**
     * Copy post data
     *
     * @since 1.0
     * @author Siam Maruf
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.arrayFound
    public function copy_post_data( $schedule_date, BackgroundProcess $background_process ): array {
        $response = [
            'status'        => false,
            'error'         => false,
            'message'       => '',
            'queue'         => 0,
            'mainPostCount' => 0,
        ];

        try {
            // Get cached total count first
            $total_orders = get_option('archm_archive_orders_count');

            // If cache doesn't exist, calculate and store it
            if ( false === $total_orders ) {
                $total_orders = $this->get_total_post_count('shop_order', $schedule_date);
                update_option('archm_archive_orders_count', $total_orders);
            }

            $batch_ids = get_option('archm_queue_batch_ids', []);
            $ids       = get_option('archm_queue_ids', []);

            $query = $this->get_orders_query($schedule_date, $ids, $batch_ids);

            $post_ids = $this->wpdb->get_col($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

            if ( ! empty($post_ids) ) {
                if ( $this->start_background_process($background_process, $post_ids) ) {
                    $output = $this->combine_arrays( (array) $ids, $post_ids);
                    update_option('archm_queue_ids', $output);
                    $response['status']        = true;
                    $response['queue']         = count($output);
                    $response['mainPostCount'] = (int) $total_orders;
                    $response['complete']      = false;
                    $response['message']       = 'Orders copied successfully.';
                }
            } else {
                $response['message'] = 'No posts found for archive.';
                $response['error']   = true;
                if ( count($ids) !== 0 && ( count($ids) === (int) $total_orders ) ) {
                    $response['status']        = true;
                    $response['error']         = false;
                    $response['complete']      = true;
                    $response['queue']         = count($ids);
                    $response['mainPostCount'] = (int) $total_orders;
                    delete_option('archm_queue_ids');
                }
            }
        } catch ( \Exception $e ) {
            // Logger::logError($e->getMessage());
            $response['error']   = true;
            $response['message'] = 'Error: ' . $e->getMessage();
        }
        return $response;
    }

    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.arrayFound
    private function combine_arrays( $array1, $array2 ): array {
        $merged_array   = array_merge($array1, $array2);
        $filtered_array = array_filter($merged_array, function ( $value ) {
            return false !== $value;
        });
        $unique_array = array_unique($filtered_array);
        return array_values($unique_array);
    }

    /**
     * Total Post Count
     *
     * @param $postType
     * @param $scheduleDate
     *
     * @return int
     * @since 1.0
     * @author Siam Maruf
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.intFound
    public function get_total_post_count( $post_type, $schedule_date ): int {
        $post_count = 0;
        $batch_ids  = get_option('archm_queue_batch_ids', []);

        $query   = $this->get_count_query($schedule_date, $batch_ids);
        $results = $this->wpdb->get_results($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        if ( $results ) {
            $post_count = $results[0]->post_count;
        }

        return $post_count;
    }

    /**
     * Get Host Order Ids From Remote DB
     *
     * @param $postType
     * @param $main_db_name
     *
     * @return array
     * @since 1.0
     * @author Siam Maruf
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.arrayFound
    private function count_post_ids_for_remove( $post_type, $main_db_name ): array {
        $this->wpdb->dbh = $this->remote_db;
        $posts_with_meta = get_posts([
            'post_type'      => $post_type,
            'posts_per_page' => -1,
            // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
            'meta_query'     => [
                [
                    'key'     => 'host_post_id',
                    'compare' => 'EXISTS',
                ],
            ],
            'fields'         => 'ids',
        ]);

        $posts_ids = array_map(function ( $post_id ) {
            return (int) get_post_meta($post_id, 'host_post_id', true);
        }, $posts_with_meta);

        $this->wpdb->select($main_db_name, $this->main_db);
        return ! empty($posts_ids) ? $posts_ids : [];
    }

    /**
     * Remove Orders
     *
     * @param $postType
     * @param $main_db_name
     * @param $cnkSize
     *
     * @return false[]
     * @since 1.0
     * @author Siam Maruf
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.arrayFound
    public function remove_orders( $post_type, $main_db_name, $cnk_size ): array {
        $response      = [
			'status' => false,
			'error' => false,
		];
        $cnk_size     = $cnk_size;
        $deleted_count = 0;

        $post_ids = $this->count_post_ids_for_remove($post_type, $main_db_name);

        if ( empty($post_ids) ) {
            $response['status']  = true;
            $response['error']   = true;
            $response['message'] = 'No archives found for removal.';
            return $response;
        }

        $ids_counter     = count($post_ids);
        $this->wpdb->dbh = $this->main_db;

        while ( ! empty($post_ids) ) {
            $args = [
                'post_type'      => $post_type,
                'posts_per_page' => $cnk_size,
                'post__in'       => $post_ids,
                'fields'         => 'ids',
            ];

            $query   = new \WP_Query($args);
            $counter = $query->post_count;

            if ( 0 === $counter ) {
                $response['status']  = true;
                $response['message'] = 'All archives removed from host database';
                return $response;
            }

            foreach ( $query->posts as $post_id ) {
                if ( ! wp_delete_post($post_id, true) ) {
                    $response['message'] = 'Error occurred while removing archives';
                    $response['error']   = true;
                    return $response;
                }
            }

            $post_ids      = array_diff($post_ids, $query->posts);
            $deleted_count = $ids_counter - $counter;
        }

        $response['message'] = $deleted_count . ' of ' . $ids_counter . ' removed, Continue removing';
        return $response;
    }

    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.boolFound
    private function post_exists_by_title( $title, $remote_db, $post_type = 'post' ): bool {
        $post_table = $this->wpdb->prefix . 'posts';
        $query      = $remote_db->prepare(
            "SELECT ID
	         FROM `$post_table`
	         WHERE post_title = %s
	         AND post_type = %s
	         LIMIT 1", $title, $post_type
        );

        $post_id = $remote_db->get_var($query);
        return ! is_null($post_id);
    }
}
