<?php
namespace ARCHM\Helpers;

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

trait DBHelper {


    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private string $db_user;
    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private string $db_pass;
    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private string $db_name;
    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private int $db_port;
    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private string $db_host;
    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private string $db_remote_host;

    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private string $main_db_name;
    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private string $main_db_host;
    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private string $main_db_user;
    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private string $main_db_pass;

    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private \wpdb $main_db;
    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private ?\wpdb $remote_db = null;
    // phpcs:ignore PHPCompatibility.Classes.NewTypedProperties.Found
    private \wpdb $wpdb;

    /**
     * Generate a unique hash for the database file
     *
     * @return string
     */
    private function generate_db_file_hash() {
        // Use site-specific data to generate a unique hash
        $site_data = array(
            'site_url' => get_site_url(),
            'admin_email' => get_option('admin_email'),
            'db_name' => DB_NAME,
            'table_prefix' => $GLOBALS['wpdb']->prefix,
            'timestamp' => time(),
        );

        $hash_input = serialize($site_data);
        return hash('sha256', $hash_input);
    }

    /**
     * Get or generate database file hash
     *
     * @return string
     */
    private function get_or_generate_db_file_hash() {
        $option_name = 'archm_sqlite_db_file_hash';
        $existing_hash = get_option($option_name);

        if ( $existing_hash ) {
            return $existing_hash;
        }

        // Generate a new hash
        $hash = $this->generate_db_file_hash();

        // Save the hash to options table
        update_option($option_name, $hash);

        return $hash;
    }

    /**
     * Get the database directory path
     *
     * @return string
     */
    private function get_db_directory() {
        return WP_CONTENT_DIR . '/archive-master-db';
    }

    /**
     * Get the database file path
     *
     * @return string
     */
    private function get_db_file_path() {
        $hash = $this->get_or_generate_db_file_hash();
        return $this->get_db_directory() . '/' . $hash . '.db';
    }

    /**
     * Get the relative database file path for display
     *
     * @return string
     */
    private function get_relative_db_file_path() {
        $db_file_path = $this->get_db_file_path();

        // Convert to relative path from wp-content
        if ( strpos($db_file_path, WP_CONTENT_DIR) === 0 ) {
            return str_replace(WP_CONTENT_DIR, '/wp-content', $db_file_path);
        }

        return $db_file_path;
    }

    /**
     * Set remot DB credential
     *
     * @return void
     */
    private function set_remote_db_settings() {
        $selected_database = get_option('archm_selected_database', 'sqlite');

        // Check for Custom MySQL connection (Pro feature)
        $is_cmysql_connected = get_option('archm_cmysql_db_connection', false);

        if ( 'custom_mysql' === $selected_database && $is_cmysql_connected ) {
            // Load Custom MySQL credentials and decrypt sensitive fields
            $encrypted_username = get_option('archm_cmysql_username');
            $encrypted_password = get_option('archm_cmysql_password');

            $this->db_name        = get_option('archm_cmysql_dbname');
            $this->db_host        = get_option('archm_cmysql_endpoint');
            $this->db_port        = get_option('archm_cmysql_port');

            // Decrypt credentials - ensure encryption class is loaded
            if ( class_exists('\ARCHM_PRO\Credential_Encryption') ) {
                $decrypted_username = \ARCHM_PRO\Credential_Encryption::decrypt($encrypted_username);
                $decrypted_password = \ARCHM_PRO\Credential_Encryption::decrypt($encrypted_password);

                // Validate decryption succeeded - don't use encrypted values
                if ( false === $decrypted_username || false === $decrypted_password ) {
                    // Decryption failed - don't set credentials if decryption fails
                    return;
                }

                $this->db_user = $decrypted_username;
                $this->db_pass = $decrypted_password;
            } else {
                // Encryption class not available - check if credentials are encrypted
                // If they appear encrypted (base64-like), don't use them
                if ( ! empty($encrypted_username) && false !== base64_decode($encrypted_username, true) ) {
                    // Looks encrypted but can't decrypt - don't use
                    return;
                }
                // Plain text credentials (backward compatibility)
                $this->db_user = $encrypted_username;
                $this->db_pass = $encrypted_password;
            }

            $this->db_remote_host = "$this->db_host:$this->db_port";
            return;
        }

        // Check for RDS connection
        $is_rds_connected = get_option('archm_rds_db_connection', false);

        if ( $is_rds_connected ) {
            $this->db_name        = get_option('archm_rds_dbname');
            $this->db_host        = get_option('archm_rds_endpoint');
            $this->db_user        = get_option('archm_rds_username');
            $this->db_port        = get_option('archm_rds_port');
            $this->db_pass        = get_option('archm_rds_password');
            $this->db_remote_host = "$this->db_host:$this->db_port";

            $rds_credential = [
                'rds_dbname'   => $this->db_name,
                'rds_endpoint' => $this->db_host,
                'rds_username' => $this->db_user,
                'rds_port'     => $this->db_port,
                'rds_password' => $this->db_pass,
            ];

            add_filter('archm_options', function ( $options ) use ( $rds_credential ) {
                $options['rds_credential'] = $rds_credential;
                $options['rds_connected']  = get_option('archm_rds_db_connection', false);
                return $options;
            });
        }
    }

    /**
     * Set main db instance
     *
     * @return void
     */
    private function set_main_deb_settings() {
        global $wpdb;
        $this->wpdb         = $wpdb;
        $this->main_db_host = DB_HOST;
        $this->main_db_user = DB_USER;
        $this->main_db_pass = DB_PASSWORD;
        $this->main_db_name = DB_NAME;
    }

    /**
     * Set the database instance
     *
     * @return void
     */
    private function set_db_instance() {
        $selected_database = get_option('archm_selected_database', 'sqlite');

        if ( ! $this->is_bd_connected() ) {
            return;
        }

        // Custom MySQL connection (Pro feature)
        if ( 'custom_mysql' === $selected_database ) {
            $is_cmysql_connected = get_option('archm_cmysql_db_connection', false);
            if ( $is_cmysql_connected ) {
                $this->remote_db = new \wpdb($this->db_user, $this->db_pass, $this->db_name, $this->db_host . ':' . $this->db_port);
            }
            return;
        }

        // RDS connection
        $is_rds_connected = get_option('archm_rds_db_connection', false);
        if ( $is_rds_connected ) {
            $this->remote_db = new \wpdb($this->db_user, $this->db_pass, $this->db_name, $this->db_host . ':' . $this->db_port);
        }
    }

    /**
     * Get database connection status
     *
     * @return boolean
     */
    private function is_bd_connected() {
        // Ensure settings are populated in case caller forgot to initialize
        if ( empty($this->db_host) || empty($this->db_name) ) {
            $this->set_remote_db_settings();
        }

        $selected_database = get_option('archm_selected_database', 'sqlite');

        // For custom_mysql allow blank password; for RDS require non-empty password
        $has_host = ! empty($this->db_host);
        $has_user = ! empty($this->db_user);
        $has_name = ! empty($this->db_name);
        $has_port = ! empty($this->db_port) && (int) $this->db_port > 0;
        $has_pass = ( 'custom_mysql' === $selected_database ) ? true : ! empty($this->db_pass);

        if ( $has_host && $has_user && $has_name && $has_port && $has_pass ) {
            return true;
        }

        return false;
    }

    /**
     * Check Database
     *
     * @since 1.0
     * @author Siam Maruf
     */
    // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.arrayFound
    final public function check_db_connection( $endpoint, $port, $db_user, $db_pass, $db_name, $db_create = false ): array {
        $response = [
            'database' => false,
            'success' => false,
            'message' => '',
        ];

        $selected_database = get_option('archm_selected_database', 'sqlite');
        $mysqli            = null;

        // Validate database name - only allow alphanumeric, underscore, and hyphen
        // This prevents SQL injection through database names
        if ( ! preg_match('/^[a-zA-Z0-9_\-]+$/', $db_name) ) {
            $response['message'] = 'Invalid database name. Only alphanumeric characters, underscores, and hyphens are allowed.';
            return $response;
        }

        // Additional validation: length check
        if ( strlen($db_name) > 64 ) {
            $response['message'] = 'Database name is too long. Maximum 64 characters allowed.';
            return $response;
        }

        // Use mysqli directly for more reliable database detection and creation
        // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__mysqli -- Required for external database connections
        try {
            // First, connect to MySQL server without specifying a database
            // Try multiple connection strategies
            // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__mysqli -- Required for external database connections
            $mysqli = @new \mysqli($endpoint, $db_user, $db_pass, '', $port);

            if ( $mysqli->connect_error ) {
                // Try with information_schema if empty connection fails
                // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__mysqli -- Required for external database connections
                $mysqli = @new \mysqli($endpoint, $db_user, $db_pass, 'information_schema', $port);

                if ( $mysqli->connect_error ) {
                    // Try with mysql database as last resort
                    // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__mysqli -- Required for external database connections
                    $mysqli = @new \mysqli($endpoint, $db_user, $db_pass, 'mysql', $port);

                    if ( $mysqli->connect_error ) {
                        // Generic error message to prevent information leakage
                        $response['message'] = "Connection can't be established. Please check your credentials and server details.";
                        if ( defined('WP_DEBUG') && WP_DEBUG ) {
                            $response['message'] .= ' (' . $mysqli->connect_error . ')';
                        }
                        return $response;
                    }
                }
            }

            // Check if target database exists
            // Note: SHOW DATABASES LIKE doesn't support prepared statement placeholders
            // But this is safe because we've already validated the database name with regex
            // and we're using real_escape_string as an additional security layer
            $escaped_db_name_for_query = $mysqli->real_escape_string($db_name);
            $query = "SHOW DATABASES LIKE '$escaped_db_name_for_query'";
            $result = $mysqli->query($query);

            if ( false === $result ) {
                $error_msg = 'Error checking database existence';
                if ( defined('WP_DEBUG') && WP_DEBUG ) {
                    $error_msg .= ': ' . $mysqli->error;
                }
                $response['message'] = $error_msg;
                $mysqli->close();
                return $response;
            }

            $database_exists = ( $result->num_rows > 0 );
            $result->free();

            if ( ! $database_exists ) {
                // Database does not exist, attempt to create it
                // Since database names are validated above, we can safely use backticks
                // But we still use real_escape_string as an extra layer
                $escaped_db_name = $mysqli->real_escape_string($db_name);
                $create_query = "CREATE DATABASE IF NOT EXISTS `$escaped_db_name`";

                if ( $mysqli->query($create_query) === false ) {
                    $error_msg = 'Failed to create database';
                    if ( defined('WP_DEBUG') && WP_DEBUG ) {
                        $error_msg .= ': ' . $mysqli->error;
                    }
                    $response['message'] = $error_msg;
                    $mysqli->close();
                    return $response;
                }

                $response['message'] = 'Database created successfully.';
            } else {
                $response['message'] = 'Database already exists.';
            }

            // Close the connection
            $mysqli->close();

            // Now verify connection to the target database using wpdb (WordPress's secure way)
            $db_host = $endpoint . ':' . $port;
            $final_db = new \wpdb($db_user, $db_pass, $db_name, $db_host);
            $final_db->query('SELECT 1');

            if ( $final_db->last_error ) {
                $error_msg = 'Database exists but connection failed';
                if ( defined('WP_DEBUG') && WP_DEBUG ) {
                    $error_msg .= ': ' . $final_db->last_error;
                }
                $response['message'] = $error_msg;
                return $response;
            }

            $response['database'] = true;
            $response['success']  = true;
            $response['message']  = "Successful connection to database '$db_name'.";

        } catch ( \Exception $e ) {
            $error_msg = 'Connection error';
            if ( defined('WP_DEBUG') && WP_DEBUG ) {
                $error_msg .= ': ' . $e->getMessage();
            }
            $response['message'] = $error_msg;
            if ( $mysqli && ! $mysqli->connect_error ) {
                $mysqli->close();
            }
        }

        return $response;
    }

    /**
     * Check if MySQL/MariaDB database supports JSON functions.
     *
     * @return bool True if JSON functions are supported, false otherwise.
     */
    private function check_mysql_json_support() {
        if ( ! $this->remote_db ) {
            return false;
        }

        // Cache the result to avoid checking every time.
        $cache_key = 'archm_mysql_json_support_' . md5( $this->remote_db->dbhost . $this->remote_db->dbname );
        $cached_result = wp_cache_get( $cache_key, 'archive-master' );

        if ( false !== $cached_result ) {
            return (bool) $cached_result;
        }

        $json_supported = false;

        // First, try to check MySQL version (JSON_EXTRACT available in MySQL 5.7.8+).
        $version = $this->remote_db->get_var( 'SELECT VERSION()' );

        if ( $version ) {
            // Extract major and minor version numbers.
            if ( preg_match( '/^(\d+)\.(\d+)\.(\d+)/', $version, $matches ) ) {
                $major = (int) $matches[1];
                $minor = (int) $matches[2];
                $patch = (int) $matches[3];

                // MySQL 5.7.8+ supports JSON functions.
                // MariaDB 10.2.7+ also supports JSON functions, but check for MariaDB first.
                if ( false !== strpos( strtolower( $version ), 'mariadb' ) ) {
                    // MariaDB: JSON functions available from 10.2.7+.
                    if ( $major > 10 || ( 10 === $major && ( $minor > 2 || ( 2 === $minor && $patch >= 7 ) ) ) ) {
                        $json_supported = true;
                    }
                } else {
                    // MySQL: JSON functions available from 5.7.8+.
                    if ( $major > 5 || ( 5 === $major && ( $minor > 7 || ( 7 === $minor && $patch >= 8 ) ) ) ) {
                        $json_supported = true;
                    }
                }
            }
        }

        // If version check indicates support, cache and return.
        if ( $json_supported ) {
            wp_cache_set( $cache_key, 1, 'archive-master', HOUR_IN_SECONDS );
            return true;
        }

        // If version is too old, assume JSON functions are not supported.
        // Don't test with JSON_EXTRACT query as it will cause errors to be displayed.
        // Based on version check, if version is too old, JSON functions won't be available.
        wp_cache_set( $cache_key, 0, 'archive-master', HOUR_IN_SECONDS );
        return false;
    }
}
