<?php
namespace Unlockafe_addons\API;

use WP_REST_Server;
use WP_REST_Response;
use WP_Error;

if (!defined('ABSPATH')) {
    exit; // Exit if accessed directly.
}

/**
 * Database API class for external database operations
 */
class Database_API {
    /**
     * Instance variable
     *
     * @var Database_API
     */
    private static $instance = null;

    /**
     * Namespace for RestAPI
     *
     * @since 1.0.0
     * @var string
     */
    protected $namespace = 'unlockafe-addons/v1';

    /**
     * Constructor
     */
    private function __construct() {
        add_action('rest_api_init', [$this, 'register_routes']);
    }

    /**
     * Get instance
     *
     * @return Database_API
     */
    public static function getInstance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Register REST API routes
     */
    public function register_routes() {
        // Store data API
        register_rest_route($this->namespace, '/database/store', [
            'methods' => WP_REST_Server::CREATABLE,
            'callback' => [$this, 'store_data'],
            'permission_callback' => [$this, 'verify_api_key'],
        ]);

        // Get data API
        register_rest_route($this->namespace, '/database/get', [
            'methods' => WP_REST_Server::READABLE,
            'callback' => [$this, 'get_data'],
            'permission_callback' => [$this, 'verify_api_key'],
        ]);

        // Update data API
        register_rest_route($this->namespace, '/database/update', [
            'methods' => WP_REST_Server::CREATABLE,
            'callback' => [$this, 'update_data'],
            'permission_callback' => [$this, 'verify_api_key'],
        ]);

        // Delete data API
        register_rest_route($this->namespace, '/database/delete', [
            'methods' => WP_REST_Server::DELETABLE,
            'callback' => [$this, 'delete_data'],
            'permission_callback' => [$this, 'verify_api_key'],
        ]);

        // Get database list (for widget) – allow public read (structure only)
        register_rest_route($this->namespace, '/database/list', [
            'methods'  => WP_REST_Server::READABLE,
            'callback' => [$this, 'get_database_list'],
            'permission_callback' => '__return_true',
        ]);

        // Generate API key
        register_rest_route($this->namespace, '/database/generate-key', [
            'methods' => WP_REST_Server::CREATABLE,
            'callback' => [$this, 'generate_api_key'],
            'permission_callback' => function() {
                return current_user_can('manage_options');
            },
        ]);

        // Get API keys list
        register_rest_route($this->namespace, '/database/api-keys', [
            'methods' => WP_REST_Server::READABLE,
            'callback' => [$this, 'get_api_keys'],
            'permission_callback' => function() {
                return current_user_can('manage_options');
            },
        ]);

        // Delete API key
        register_rest_route($this->namespace, '/database/api-keys/delete', [
            'methods' => WP_REST_Server::DELETABLE,
            'callback' => [$this, 'delete_api_key'],
            'permission_callback' => function() {
                return current_user_can('manage_options');
            },
        ]);
    }

    /**
     * Verify API key
     *
     * @param WP_REST_Request $request
     * @return bool|WP_Error
     */
    public function verify_api_key($request) {
        // Check params first (for GET requests), then JSON body (for POST/DELETE), then header as fallback
        $api_key = $request->get_param('api_key');
        
        if (empty($api_key)) {
            $json_params = $request->get_json_params();
            if (isset($json_params['api_key'])) {
                $api_key = sanitize_text_field($json_params['api_key']);
            }
        }
        
        if (empty($api_key)) {
            $api_key = $request->get_header('X-API-Key');
        }

        if (empty($api_key)) {
            return new WP_Error('missing_api_key', 'API key is required. Pass it as api_key parameter in URL or JSON body.', ['status' => 401]);
        }

        $valid_keys = get_option('unlockafe_db_api_keys', []);
        
        if (!is_array($valid_keys)) {
            return new WP_Error('invalid_api_keys', 'No API keys configured', ['status' => 401]);
        }

        // Check if API key exists
        foreach ($valid_keys as $key_data) {
            if (isset($key_data['key']) && $key_data['key'] === $api_key) {
                // Check if key is active
                if (isset($key_data['active']) && $key_data['active'] === true) {
                    // Update last used timestamp
                    $key_data['last_used'] = current_time('mysql');
                    $this->update_api_key($key_data['key'], $key_data);
                    return true;
                }
            }
        }

        return new WP_Error('invalid_api_key', 'Invalid or inactive API key', ['status' => 401]);
    }

    /**
     * Update API key data
     *
     * @param string $api_key
     * @param array $key_data
     * @return void
     */
    private function update_api_key($api_key, $key_data) {
        $keys = get_option('unlockafe_db_api_keys', []);
        if (!is_array($keys)) {
            $keys = [];
        }
        
        foreach ($keys as $index => $key) {
            if (isset($key['key']) && $key['key'] === $api_key) {
                $keys[$index] = $key_data;
                update_option('unlockafe_db_api_keys', $keys);
                break;
            }
        }
    }

    /**
     * Store data to database
     *
     * @param WP_REST_Request $request
     * @return WP_REST_Response|WP_Error
     */
    public function store_data($request) {
        global $wpdb;

        $params = $request->get_json_params();
        $table_name = isset($params['table']) ? sanitize_text_field($params['table']) : '';
        $data = isset($params['data']) ? $params['data'] : [];

        if (empty($table_name)) {
            return new WP_Error('missing_table', 'Table name is required', ['status' => 400]);
        }

        if (empty($data) || !is_array($data)) {
            return new WP_Error('missing_data', 'Data is required and must be an array', ['status' => 400]);
        }

        // Validate table name (prevent SQL injection)
        $table_name = preg_replace('/[^a-zA-Z0-9_]/', '', $table_name);
        
        // Check if table exists
        $table_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s AND table_name = %s",
            DB_NAME,
            $table_name
        ));

        if (!$table_exists) {
            return new WP_Error('table_not_found', 'Table does not exist', ['status' => 404]);
        }

        // Sanitize data
        $sanitized_data = [];
        foreach ($data as $key => $value) {
            $sanitized_key = preg_replace('/[^a-zA-Z0-9_]/', '', $key);
            $sanitized_data[$sanitized_key] = sanitize_text_field($value);
        }

        // Insert data
        $result = $wpdb->insert($table_name, $sanitized_data);

        if ($result === false) {
            return new WP_Error('insert_failed', 'Failed to insert data: ' . $wpdb->last_error, ['status' => 500]);
        }

        return new WP_REST_Response([
            'success' => true,
            'message' => 'Data stored successfully',
            'insert_id' => $wpdb->insert_id,
        ], 200);
    }

    /**
     * Get data from database
     *
     * @param WP_REST_Request $request
     * @return WP_REST_Response|WP_Error
     */
    public function get_data($request) {
        global $wpdb;

        $table_name = $request->get_param('table');
        $where = $request->get_param('where'); // JSON string or array
        $limit = $request->get_param('limit') ? intval($request->get_param('limit')) : 100;
        $offset = $request->get_param('offset') ? intval($request->get_param('offset')) : 0;

        if (empty($table_name)) {
            return new WP_Error('missing_table', 'Table name is required', ['status' => 400]);
        }

        // Validate table name
        $table_name = preg_replace('/[^a-zA-Z0-9_]/', '', $table_name);

        // Check if table exists
        $table_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s AND table_name = %s",
            DB_NAME,
            $table_name
        ));

        if (!$table_exists) {
            return new WP_Error('table_not_found', 'Table does not exist', ['status' => 404]);
        }

        // Build WHERE clause (values prepared, identifiers sanitized)
        $where_clause = '';
        $where_values = [];

        if (!empty($where)) {
            if (is_string($where)) {
                $where = json_decode($where, true);
            }

            if (is_array($where) && !empty($where)) {
                $where_conditions = [];
                foreach ($where as $key => $value) {
                    $sanitized_key = preg_replace('/[^a-zA-Z0-9_]/', '', (string)$key);
                    if ($sanitized_key === '') {
                        continue;
                    }
                    $where_conditions[] = "`{$sanitized_key}` = %s";
                    $where_values[] = sanitize_text_field($value);
                }
                if (!empty($where_conditions)) {
                    $where_clause = 'WHERE ' . implode(' AND ', $where_conditions);
                }
            }
        }

        // Get data
        $query = "SELECT * FROM `{$table_name}` {$where_clause} LIMIT %d OFFSET %d";
        $query_values = array_merge($where_values, [$limit, $offset]);
        $results = $wpdb->get_results($wpdb->prepare($query, $query_values), ARRAY_A);

        // Get total count
        if (!empty($where_clause)) {
            $count_query = "SELECT COUNT(*) FROM `{$table_name}` {$where_clause}";
            $total = $wpdb->get_var($wpdb->prepare($count_query, $where_values));
        } else {
            $count_query = "SELECT COUNT(*) FROM `{$table_name}`";
            $total = $wpdb->get_var($count_query);
        }

        return new WP_REST_Response([
            'success' => true,
            'data' => $results,
            'total' => intval($total),
            'limit' => $limit,
            'offset' => $offset,
        ], 200);
    }

    /**
     * Update data in database
     *
     * @param WP_REST_Request $request
     * @return WP_REST_Response|WP_Error
     */
    public function update_data($request) {
        global $wpdb;

        $params = $request->get_json_params();
        $table_name = isset($params['table']) ? sanitize_text_field($params['table']) : '';
        $data = isset($params['data']) ? $params['data'] : [];
        $where = isset($params['where']) ? $params['where'] : [];

        if (empty($table_name)) {
            return new WP_Error('missing_table', 'Table name is required', ['status' => 400]);
        }

        if (empty($data) || !is_array($data)) {
            return new WP_Error('missing_data', 'Data to update is required', ['status' => 400]);
        }

        if (empty($where) || !is_array($where)) {
            return new WP_Error('missing_where', 'WHERE conditions are required', ['status' => 400]);
        }

        // Validate table name
        $table_name = preg_replace('/[^a-zA-Z0-9_]/', '', $table_name);

        // Check if table exists
        $table_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s AND table_name = %s",
            DB_NAME,
            $table_name
        ));

        if (!$table_exists) {
            return new WP_Error('table_not_found', 'Table does not exist', ['status' => 404]);
        }

        // Sanitize data
        $sanitized_data = [];
        foreach ($data as $key => $value) {
            $sanitized_key = preg_replace('/[^a-zA-Z0-9_]/', '', $key);
            $sanitized_data[$sanitized_key] = sanitize_text_field($value);
        }

        if (empty($sanitized_data)) {
            return new WP_Error('invalid_data', 'No valid data to update', ['status' => 400]);
        }

        // Build WHERE clause
        $where_conditions = [];
        $where_values = [];
        foreach ($where as $key => $value) {
            $sanitized_key = preg_replace('/[^a-zA-Z0-9_]/', '', (string)$key);
            if ($sanitized_key === '') {
                continue;
            }
            $where_conditions[] = "`{$sanitized_key}` = %s";
            $where_values[] = sanitize_text_field($value);
        }

        if (empty($where_conditions)) {
            return new WP_Error('invalid_where', 'WHERE conditions cannot be empty', ['status' => 400]);
        }

        $where_clause = implode(' AND ', $where_conditions);

        // Build SET clause
        $set_clause = [];
        $set_values = [];
        foreach ($sanitized_data as $key => $value) {
            $set_clause[] = "`{$key}` = %s";
            $set_values[] = $value;
        }
        $set_clause = implode(', ', $set_clause);

        // Update data
        $update_sql = "UPDATE `{$table_name}` SET {$set_clause} WHERE {$where_clause}";
        $all_values = array_merge($set_values, $where_values);
        $result = $wpdb->query($wpdb->prepare($update_sql, $all_values));

        if ($result === false) {
            return new WP_Error('update_failed', 'Failed to update data: ' . $wpdb->last_error, ['status' => 500]);
        }

        return new WP_REST_Response([
            'success' => true,
            'message' => 'Data updated successfully',
            'updated_rows' => $result,
        ], 200);
    }

    /**
     * Delete data from database
     *
     * @param WP_REST_Request $request
     * @return WP_REST_Response|WP_Error
     */
    public function delete_data($request) {
        global $wpdb;

        $params = $request->get_json_params();
        $table_name = isset($params['table']) ? sanitize_text_field($params['table']) : '';
        $where = isset($params['where']) ? $params['where'] : [];

        if (empty($table_name)) {
            return new WP_Error('missing_table', 'Table name is required', ['status' => 400]);
        }

        if (empty($where) || !is_array($where)) {
            return new WP_Error('missing_where', 'WHERE conditions are required', ['status' => 400]);
        }

        // Validate table name
        $table_name = preg_replace('/[^a-zA-Z0-9_]/', '', $table_name);

        // Check if table exists
        $table_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s AND table_name = %s",
            DB_NAME,
            $table_name
        ));

        if (!$table_exists) {
            return new WP_Error('table_not_found', 'Table does not exist', ['status' => 404]);
        }

        // Build WHERE clause (values prepared, identifiers sanitized)
        $where_conditions = [];
        $where_values = [];
        foreach ($where as $key => $value) {
            $sanitized_key = preg_replace('/[^a-zA-Z0-9_]/', '', (string)$key);
            if ($sanitized_key === '') {
                continue;
            }
            $where_conditions[] = "`{$sanitized_key}` = %s";
            $where_values[] = sanitize_text_field($value);
        }

        if (empty($where_conditions)) {
            return new WP_Error('invalid_where', 'WHERE conditions cannot be empty', ['status' => 400]);
        }

        $where_clause = implode(' AND ', $where_conditions);

        // Delete data
        $delete_sql = "DELETE FROM `{$table_name}` WHERE {$where_clause}";
        $result = $wpdb->query($wpdb->prepare($delete_sql, $where_values));

        if ($result === false) {
            return new WP_Error('delete_failed', 'Failed to delete data: ' . $wpdb->last_error, ['status' => 500]);
        }

        return new WP_REST_Response([
            'success' => true,
            'message' => 'Data deleted successfully',
            'deleted_rows' => $result,
        ], 200);
    }

    /**
     * Get database list (for widget display)
     *
     * @param WP_REST_Request $request
     * @return WP_REST_Response
     */
    public function get_database_list($request) {
        global $wpdb;

        $tables = $wpdb->get_results("SHOW TABLES", ARRAY_N);
        $table_list = [];

        foreach ($tables as $table) {
            $table_name = $table[0];
            
            // Get table columns
            $safe_table = preg_replace('/[^a-zA-Z0-9_]/', '', (string)$table_name);
            if ($safe_table === '') {
                continue;
            }
            // Identifiers cannot be parameterized; sanitize then interpolate.
            $columns = $wpdb->get_results("DESCRIBE `{$safe_table}`", ARRAY_A);
            
            $table_list[] = [
                'name' => $safe_table,
                'columns' => array_map(function($col) {
                    return [
                        'field' => $col['Field'],
                        'type' => $col['Type'],
                        'null' => $col['Null'],
                        'key' => $col['Key'],
                        'default' => $col['Default'],
                        'extra' => $col['Extra'],
                    ];
                }, $columns),
            ];
        }

        return new WP_REST_Response([
            'success' => true,
            'tables' => $table_list,
        ], 200);
    }

    /**
     * Generate API key
     *
     * @param WP_REST_Request $request
     * @return WP_REST_Response
     */
    public function generate_api_key($request) {
        $params = $request->get_json_params();
        $name = isset($params['name']) ? sanitize_text_field($params['name']) : 'API Key ' . date('Y-m-d H:i:s');
        
        // Generate random API key
        $api_key = 'unlockafe_' . bin2hex(random_bytes(32));

        $keys = get_option('unlockafe_db_api_keys', []);
        
        if (!is_array($keys)) {
            $keys = [];
        }

        $new_key = [
            'key' => $api_key,
            'name' => $name,
            'active' => true,
            'created' => current_time('mysql'),
            'last_used' => null,
        ];

        $keys[] = $new_key;
        update_option('unlockafe_db_api_keys', $keys);

        return new WP_REST_Response([
            'success' => true,
            'api_key' => $api_key,
            'name' => $name,
            'message' => 'API key generated successfully',
        ], 200);
    }

    /**
     * Get API keys list
     *
     * @param WP_REST_Request $request
     * @return WP_REST_Response
     */
    public function get_api_keys($request) {
        $keys = get_option('unlockafe_db_api_keys', []);
        
        if (!is_array($keys)) {
            $keys = [];
        }

        // Return full keys for admin users (they can manage keys)
        $formatted_keys = array_map(function($key_data) {
            $key = isset($key_data['key']) ? $key_data['key'] : '';
            $masked = '';
            if (!empty($key) && strlen($key) > 10) {
                $masked = substr($key, 0, 10) . '...' . substr($key, -6);
            }
            
            return [
                'name' => isset($key_data['name']) ? $key_data['name'] : '',
                'full_key' => $key, // Return full key for admin
                'masked_key' => $masked,
                'active' => isset($key_data['active']) ? $key_data['active'] : false,
                'created' => isset($key_data['created']) ? $key_data['created'] : '',
                'last_used' => isset($key_data['last_used']) ? $key_data['last_used'] : null,
            ];
        }, $keys);

        return new WP_REST_Response([
            'success' => true,
            'keys' => $formatted_keys,
        ], 200);
    }

    /**
     * Delete API key
     *
     * @param WP_REST_Request $request
     * @return WP_REST_Response|WP_Error
     */
    public function delete_api_key($request) {
        $params = $request->get_json_params();
        $api_key = isset($params['api_key']) ? sanitize_text_field($params['api_key']) : '';

        if (empty($api_key)) {
            return new WP_Error('missing_api_key', 'API key is required', ['status' => 400]);
        }

        $keys = get_option('unlockafe_db_api_keys', []);
        
        if (!is_array($keys)) {
            return new WP_Error('invalid_api_keys', 'No API keys configured', ['status' => 400]);
        }

        $found = false;
        foreach ($keys as $index => $key_data) {
            if (isset($key_data['key']) && $key_data['key'] === $api_key) {
                unset($keys[$index]);
                $found = true;
                break;
            }
        }

        if (!$found) {
            return new WP_Error('key_not_found', 'API key not found', ['status' => 404]);
        }

        // Re-index array
        $keys = array_values($keys);
        update_option('unlockafe_db_api_keys', $keys);

        return new WP_REST_Response([
            'success' => true,
            'message' => 'API key deleted successfully',
        ], 200);
    }
}
