<?php

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

class AIEO_ERP {

    const TABLE          = 'aieo_erp_connections';
    const ADMIN_SLUG     = 'aieo-erp-database';
    const NONCE_KEY      = 'aieo_erp_admin';
    const CAPABILITY     = 'manage_woocommerce';
    const SCHEMA_VERSION = 1;
    const SCHEMA_OPT     = 'aieo_erp_schema_version';

    private static $instance = null;

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

    private function __construct() {
        
        
        
        add_action('admin_init',                                array($this, 'maybe_install_schema'));
        add_action('wp_ajax_aieo_erp_save_connection',          array($this, 'ajax_save_connection'));
        add_action('wp_ajax_aieo_erp_delete_connection',        array($this, 'ajax_delete_connection'));
        add_action('wp_ajax_aieo_erp_test_connection',          array($this, 'ajax_test_connection'));
    }

    
    
    

    public static function table() {
        global $wpdb;
        return $wpdb->prefix . self::TABLE;
    }

    public function maybe_install_schema() {
        $current = (int) get_option(self::SCHEMA_OPT, 0);
        if ($current >= self::SCHEMA_VERSION) { return; }
        $this->install_schema();
        update_option(self::SCHEMA_OPT, self::SCHEMA_VERSION, false);
    }

    private function install_schema() {
        global $wpdb;
        $table = self::table();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS {$table} (
            `id`              bigint unsigned NOT NULL AUTO_INCREMENT,
            `name`            varchar(80)     NOT NULL,
            `host`            varchar(255)    NOT NULL,
            `port`            smallint unsigned NOT NULL DEFAULT 3306,
            `db_name`         varchar(64)     NOT NULL,
            `db_user`         varchar(64)     NOT NULL,
            `db_pass_enc`     varbinary(512)  DEFAULT NULL,
            `ssl_mode`        varchar(16)     NOT NULL DEFAULT 'prefer',
            `is_default`      tinyint(1)      NOT NULL DEFAULT 0,
            `table_prefix`        varchar(20) NOT NULL DEFAULT 'wp_',
            `erp_orders_table`    varchar(64) NOT NULL DEFAULT '',
            `erp_customers_table` varchar(64) NOT NULL DEFAULT '',
            `erp_products_table`  varchar(64) NOT NULL DEFAULT '',
            `predictor_table`     varchar(64) NOT NULL DEFAULT '',
            `notes`               text        DEFAULT NULL,
            `last_tested_at`  datetime        DEFAULT NULL,
            `last_test_ok`    tinyint(1)      NOT NULL DEFAULT 0,
            `last_test_error` varchar(500)    NOT NULL DEFAULT '',
            `created_at`      datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `updated_at`      datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            UNIQUE KEY `name` (`name`),
            KEY `is_default` (`is_default`)
        ) {$charset_collate};";
        $wpdb->query($sql);
    }

    
    
    

    
    public static function key_configured() {
        return defined('AIEO_ERP_ENCRYPTION_KEY')
            && is_string(AIEO_ERP_ENCRYPTION_KEY)
            && strlen(AIEO_ERP_ENCRYPTION_KEY) >= 32;
    }

    
    public static function suggest_key() {
        if (function_exists('random_bytes')) {
            return base64_encode(random_bytes(32));
        }
        return base64_encode(openssl_random_pseudo_bytes(32));
    }

    private static function key_bytes() {
        $raw = (string) AIEO_ERP_ENCRYPTION_KEY;
        
        if (strlen($raw) === 32) { return $raw; }
        $decoded = base64_decode($raw, true);
        if ($decoded !== false && strlen($decoded) === 32) { return $decoded; }
        
        return substr(hash('sha256', $raw, true), 0, 32);
    }

    private static function encrypt($plaintext) {
        if (!self::key_configured()) { return null; }
        if (!function_exists('sodium_crypto_secretbox')) { return null; }
        $nonce  = random_bytes(SODIUM_CRYPTO_SECRETBOX_NONCEBYTES);
        $cipher = sodium_crypto_secretbox((string) $plaintext, $nonce, self::key_bytes());
        return $nonce . $cipher;
    }

    private static function decrypt($blob) {
        if (!self::key_configured() || $blob === null || $blob === '') { return null; }
        if (!function_exists('sodium_crypto_secretbox_open')) { return null; }
        $blob = (string) $blob;
        $nonce_len = SODIUM_CRYPTO_SECRETBOX_NONCEBYTES;
        if (strlen($blob) <= $nonce_len) { return null; }
        $nonce  = substr($blob, 0, $nonce_len);
        $cipher = substr($blob, $nonce_len);
        $plain  = sodium_crypto_secretbox_open($cipher, $nonce, self::key_bytes());
        return $plain === false ? null : $plain;
    }

    
    
    

    public static function get($id) {
        global $wpdb;
        $row = $wpdb->get_row($wpdb->prepare('SELECT * FROM ' . self::table() . ' WHERE id=%d', (int) $id), ARRAY_A);
        return is_array($row) ? $row : null;
    }

    public static function get_by_name($name) {
        global $wpdb;
        $row = $wpdb->get_row($wpdb->prepare('SELECT * FROM ' . self::table() . ' WHERE name=%s', (string) $name), ARRAY_A);
        return is_array($row) ? $row : null;
    }

    public static function all() {
        global $wpdb;
        $rows = $wpdb->get_results('SELECT * FROM ' . self::table() . ' ORDER BY is_default DESC, name ASC', ARRAY_A);
        return is_array($rows) ? $rows : array();
    }

    
    public static function password_for($connection_id) {
        $row = self::get($connection_id);
        if (!$row || empty($row['db_pass_enc'])) { return null; }
        return self::decrypt($row['db_pass_enc']);
    }

    
    
    

    
    public static function for_connection_id($connection_id) {
        $row = self::get($connection_id);
        if (!$row) {
            return new WP_Error('aieo_erp_no_connection', __('Connection not found.', 'ai-eshop-optimizer'));
        }
        if (!self::key_configured()) {
            return new WP_Error('aieo_erp_no_key', __('AIEO_ERP_ENCRYPTION_KEY constant is not configured in wp-config.php.', 'ai-eshop-optimizer'));
        }
        $pass = self::decrypt($row['db_pass_enc']);
        if ($pass === null) {
            return new WP_Error('aieo_erp_decrypt_failed', __('Could not decrypt password — encryption key may have been rotated.', 'ai-eshop-optimizer'));
        }

        
        
        $previous_die = $GLOBALS['EZSQL_ERROR'] ?? null; 
        $remote = new wpdb(
            (string) $row['db_user'],
            (string) $pass,
            (string) $row['db_name'],
            (string) $row['host'] . ':' . (int) $row['port']
        );
        $remote->show_errors(false);
        $remote->suppress_errors(true);

        if (!empty($remote->error) || empty($remote->dbh)) {
            $err = is_object($remote->error) ? $remote->error->get_error_message() : (string) $remote->error;
            
            return new WP_Error('aieo_erp_connect_failed', sprintf( __( 'Connection failed: %s', 'ai-eshop-optimizer' ), $err ));
        }

        
        
        
        $remote->prefix = (string) ($row['table_prefix'] ?: 'wp_');

        return $remote;
    }

    
    public static function table_for($connection_id, $logical) {
        $row = self::get($connection_id);
        if (!$row) { return ''; }
        $key = $logical . '_table';
        if (!isset($row[$key])) { return ''; }
        $name = trim((string) $row[$key]);
        if ($name === '') { return ''; }
        
        
        
        if (!preg_match('/^[A-Za-z0-9_]+$/', $name)) { return ''; }
        return $name;
    }

    
    public static function tables_for($connection_id) {
        return array(
            'erp_orders'    => self::table_for($connection_id, 'erp_orders'),
            'erp_customers' => self::table_for($connection_id, 'erp_customers'),
            'erp_products'  => self::table_for($connection_id, 'erp_products'),
            'predictor'     => self::table_for($connection_id, 'predictor'),
        );
    }

    
    public static function query_table($connection_id, $logical, $sql, array $params = array()) {
        $table = self::table_for($connection_id, $logical);
        if ($table === '') {
            
            return array('ok' => false, 'error' => sprintf( __('Connection %1$d has no %2$s_table configured.', 'ai-eshop-optimizer'), (int) $connection_id, $logical));
        }
        $remote = self::for_connection_id($connection_id);
        if (is_wp_error($remote)) {
            return array('ok' => false, 'error' => $remote->get_error_message());
        }
        
        $sql_with_table = str_replace('{table}', '`' . $table . '`', $sql);
        $rows = empty($params)
            ? $remote->get_results($sql_with_table, ARRAY_A)
            : $remote->get_results($remote->prepare($sql_with_table, $params), ARRAY_A);
        if ($remote->last_error) {
            return array('ok' => false, 'error' => $remote->last_error);
        }
        return array('ok' => true, 'rows' => is_array($rows) ? $rows : array());
    }

    
    public static function test_credentials(array $creds) {
        $required = array('host', 'port', 'db_name', 'db_user');
        foreach ($required as $k) {
            if (!isset($creds[$k]) || $creds[$k] === '') {
                
                return array('ok' => false, 'error' => sprintf( __('Missing %s.', 'ai-eshop-optimizer'), $k));
            }
        }
        $pass = isset($creds['db_pass']) ? (string) $creds['db_pass'] : '';

        $remote = new wpdb(
            (string) $creds['db_user'],
            $pass,
            (string) $creds['db_name'],
            (string) $creds['host'] . ':' . (int) $creds['port']
        );
        $remote->show_errors(false);
        $remote->suppress_errors(true);
        if (!empty($remote->error) || empty($remote->dbh)) {
            $err = is_object($remote->error) ? $remote->error->get_error_message() : (string) $remote->error;
            return array('ok' => false, 'error' => 'Connection failed: ' . ($err ?: 'unknown error'));
        }

        $version = $remote->get_var('SELECT VERSION()');
        $now     = $remote->get_var('SELECT NOW()');
        if ($version === null) {
            return array('ok' => false, 'error' => 'Connected but query failed: ' . $remote->last_error);
        }
        return array(
            'ok'      => true,
            'version' => (string) $version,
            'now'     => (string) $now,
        );
    }

    
    
    

    
    public function build_page_context() {
        $this->maybe_install_schema();
        return array(
            'connections' => self::all(),
            'key_ok'      => self::key_configured(),
            'suggested'   => self::key_configured() ? '' : self::suggest_key(),
            'nonce'       => wp_create_nonce(self::NONCE_KEY),
        );
    }

    
    
    

    private function verify_ajax() {
        if (!current_user_can(self::CAPABILITY)) {
            wp_send_json_error(array('message' => 'forbidden'), 403);
        }
        check_ajax_referer(self::NONCE_KEY, 'nonce');
    }

    public function ajax_test_connection() {
        $this->verify_ajax();
        $creds = array(
            'host'    => isset($_POST['host'])    ? sanitize_text_field((string) wp_unslash($_POST['host']))    : '',
            'port'    => isset($_POST['port'])    ? (int) $_POST['port']                                         : 3306,
            'db_name' => isset($_POST['db_name']) ? sanitize_text_field((string) wp_unslash($_POST['db_name'])) : '',
            'db_user' => isset($_POST['db_user']) ? sanitize_text_field((string) wp_unslash($_POST['db_user'])) : '',
            'db_pass' => isset($_POST['db_pass']) ? (string) wp_unslash($_POST['db_pass'])                       : '',
        );

        
        
        
        $existing_id = isset($_POST['id']) ? (int) $_POST['id'] : 0;
        if ($creds['db_pass'] === '' && $existing_id > 0) {
            $stored = self::password_for($existing_id);
            if ($stored !== null) { $creds['db_pass'] = $stored; }
        }

        $r = self::test_credentials($creds);
        if (empty($r['ok'])) {
            wp_send_json_error(array('message' => $r['error']));
        }
        wp_send_json_success(array(
            
            'message' => sprintf(__('Connected. MariaDB %1$s · server time %2$s', 'ai-eshop-optimizer'), $r['version'], $r['now']),
            'version' => $r['version'],
            'now'     => $r['now'],
        ));
    }

    public function ajax_save_connection() {
        $this->verify_ajax();

        if (!self::key_configured()) {
            wp_send_json_error(array('message' => __('AIEO_ERP_ENCRYPTION_KEY constant is missing in wp-config.php — passwords cannot be stored safely. Add the suggested constant first, then save.', 'ai-eshop-optimizer')));
        }

        $id = isset($_POST['id']) ? (int) $_POST['id'] : 0;
        $name = isset($_POST['name']) ? sanitize_text_field((string) wp_unslash($_POST['name'])) : '';
        if ($name === '') { wp_send_json_error(array('message' => 'name required')); }

        $host = isset($_POST['host']) ? sanitize_text_field((string) wp_unslash($_POST['host'])) : '';
        if ($host === '') { wp_send_json_error(array('message' => 'host required')); }

        $port = isset($_POST['port']) ? max(1, min(65535, (int) $_POST['port'])) : 3306;
        $db_name = isset($_POST['db_name']) ? sanitize_text_field((string) wp_unslash($_POST['db_name'])) : '';
        if ($db_name === '') { wp_send_json_error(array('message' => 'db_name required')); }

        $db_user = isset($_POST['db_user']) ? sanitize_text_field((string) wp_unslash($_POST['db_user'])) : '';
        if ($db_user === '') { wp_send_json_error(array('message' => 'db_user required')); }

        $ssl_mode = isset($_POST['ssl_mode']) ? sanitize_key((string) wp_unslash($_POST['ssl_mode'])) : 'prefer';
        if (!in_array($ssl_mode, array('off', 'prefer', 'require'), true)) { $ssl_mode = 'prefer'; }

        $is_default = !empty($_POST['is_default']) ? 1 : 0;
        $table_prefix = isset($_POST['table_prefix']) ? sanitize_text_field((string) wp_unslash($_POST['table_prefix'])) : 'wp_';
        if ($table_prefix === '') { $table_prefix = 'wp_'; }
        $notes = isset($_POST['notes']) ? sanitize_textarea_field((string) wp_unslash($_POST['notes'])) : '';

        
        
        $sanitize_ident = static function ($v) {
            $v = trim((string) $v);
            return preg_match('/^[A-Za-z0-9_]+$/', $v) ? $v : '';
        };
        $erp_orders_table    = isset($_POST['erp_orders_table'])    ? $sanitize_ident(wp_unslash($_POST['erp_orders_table']))    : '';
        $erp_customers_table = isset($_POST['erp_customers_table']) ? $sanitize_ident(wp_unslash($_POST['erp_customers_table'])) : '';
        $erp_products_table  = isset($_POST['erp_products_table'])  ? $sanitize_ident(wp_unslash($_POST['erp_products_table']))  : '';
        $predictor_table     = isset($_POST['predictor_table'])     ? $sanitize_ident(wp_unslash($_POST['predictor_table']))     : '';

        $row = array(
            'name'                => $name,
            'host'                => $host,
            'port'                => $port,
            'db_name'             => $db_name,
            'db_user'             => $db_user,
            'ssl_mode'            => $ssl_mode,
            'is_default'          => $is_default,
            'table_prefix'        => $table_prefix,
            'erp_orders_table'    => $erp_orders_table,
            'erp_customers_table' => $erp_customers_table,
            'erp_products_table'  => $erp_products_table,
            'predictor_table'     => $predictor_table,
            'notes'               => $notes,
        );
        $formats = array('%s', '%s', '%d', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s');

        
        
        $new_pass = isset($_POST['db_pass']) ? (string) wp_unslash($_POST['db_pass']) : '';
        if ($new_pass !== '') {
            $enc = self::encrypt($new_pass);
            if ($enc === null) {
                wp_send_json_error(array('message' => __('Encryption failed — the AIEO_ERP_ENCRYPTION_KEY constant looks malformed.', 'ai-eshop-optimizer')));
            }
            $row['db_pass_enc'] = $enc;
            $formats[] = '%s';
        }

        global $wpdb;
        $table = self::table();

        if ($is_default) {
            
            
            $wpdb->query("UPDATE {$table} SET is_default = 0");
        }

        if ($id > 0) {
            $existing = self::get($id);
            if (!$existing) { wp_send_json_error(array('message' => 'connection_not_found')); }
            $r = $wpdb->update($table, $row, array('id' => $id), $formats, array('%d'));
            if ($r === false) {
                wp_send_json_error(array('message' => 'UPDATE failed: ' . $wpdb->last_error));
            }
            wp_send_json_success(array('id' => $id, 'message' => __('Connection updated.', 'ai-eshop-optimizer')));
        }

        
        if (empty($row['db_pass_enc'])) {
            wp_send_json_error(array('message' => __('Password is required for a new connection.', 'ai-eshop-optimizer')));
        }
        $r = $wpdb->insert($table, $row, $formats);
        if ($r === false) {
            wp_send_json_error(array('message' => 'INSERT failed: ' . $wpdb->last_error));
        }
        wp_send_json_success(array('id' => (int) $wpdb->insert_id, 'message' => __('Connection created.', 'ai-eshop-optimizer')));
    }

    public function ajax_delete_connection() {
        $this->verify_ajax();
        $id = isset($_POST['id']) ? (int) $_POST['id'] : 0;
        if ($id <= 0) { wp_send_json_error(array('message' => 'invalid id')); }
        global $wpdb;
        $rows = $wpdb->delete(self::table(), array('id' => $id), array('%d'));
        if ($rows === false) {
            wp_send_json_error(array('message' => 'DELETE failed: ' . $wpdb->last_error));
        }
        wp_send_json_success(array('deleted' => (int) $rows));
    }
}

AIEO_ERP::instance();
