<?php
class SC_Database_Endpoint {
    public function register() {
        register_rest_route(SC_REST_NAMESPACE, '/database/query', [
            'methods'             => 'POST',
            'callback'            => [$this, 'execute_query'],
            'permission_callback' => ['SC_Api_Auth', 'verify_secure'],
        ]);
        register_rest_route(SC_REST_NAMESPACE, '/database/tables', [
            'methods'             => 'GET',
            'callback'            => [$this, 'list_tables'],
            'permission_callback' => ['SC_Api_Auth', 'verify_secure'],
        ]);
    }

    public function execute_query($request) {
        global $wpdb;
        $params = $request->get_json_params();
        $sql = $params['query'] ?? '';

        if (empty($sql)) {
            return new WP_Error('empty_query', 'SQL query required', ['status' => 400]);
        }

        $sql_upper = strtoupper(trim($sql));

        // SELECT queries
        if (strpos($sql_upper, 'SELECT') === 0 || strpos($sql_upper, 'SHOW') === 0 || strpos($sql_upper, 'DESCRIBE') === 0) {
            $results = $wpdb->get_results($sql, ARRAY_A);
            if ($wpdb->last_error) {
                return new WP_Error('query_error', $wpdb->last_error, ['status' => 400]);
            }
            return rest_ensure_response([
                'results'       => $results,
                'rows_affected' => count($results),
            ]);
        }

        // Non-SELECT queries
        $result = $wpdb->query($sql);
        if ($result === false) {
            return new WP_Error('query_error', $wpdb->last_error, ['status' => 400]);
        }

        return rest_ensure_response([
            'success'       => true,
            'rows_affected' => $result,
        ]);
    }

    public function list_tables($request) {
        global $wpdb;
        $tables = $wpdb->get_results("SHOW TABLES", ARRAY_N);
        $result = [];
        foreach ($tables as $t) {
            $name = $t[0];
            $info = $wpdb->get_row("SELECT COUNT(*) as row_count FROM `{$name}`", ARRAY_A);
            $result[] = [
                'name'      => $name,
                'row_count' => (int)($info['row_count'] ?? 0),
            ];
        }
        return rest_ensure_response($result);
    }
}