citrus-cms/resources/views/admin-ajax/dynamic-resources.blade.php

@php
/**
 * Dynamic Resources API - Admin Ajax Endpoint
 * 
 * Auth-protected endpoint for dynamic resource operations.
 * Replaces the unauthenticated web.php routes.
 *
 * Usage:
 *   /admin-ajax/dynamic-resources?action=tables
 *   /admin-ajax/dynamic-resources?action=columns&table=TABLE_NAME
 *   /admin-ajax/dynamic-resources?action=values&table=TABLE_NAME&column=COLUMN_NAME
 *   /admin-ajax/dynamic-resources?action=calculate&table=TABLE_NAME (POST: column, operation, filter)
 */

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;

$action = $request->input('action', '');
$table = $request->input('table', '');

try {
    switch ($action) {

        case 'tables':
            // List all available tables with module info
            $dbTables = [];
            foreach (DB::select('SHOW TABLES') as $t) {
                $dbTables[] = array_values((array)$t)[0];
            }

            $modules = DB::table('types')
                ->where('enabled', true)
                ->whereNotNull('slug')
                ->where('slug', '!=', '')
                ->get(['id', 'slug', 'title']);

            $moduleMap = [];
            foreach ($modules as $module) {
                $tableName = Str::snake($module->slug);
                $moduleMap[$tableName] = $module;
            }

            $tables = [];
            foreach ($dbTables as $tableName) {
                $entry = [
                    'table_name' => $tableName,
                ];
                if (isset($moduleMap[$tableName])) {
                    $mod = $moduleMap[$tableName];
                    $entry['id'] = $mod->id;
                    $entry['slug'] = $mod->slug;
                    $entry['title'] = $mod->title;
                    $entry['is_module'] = true;
                } else {
                    $entry['is_module'] = false;
                }
                $tables[] = $entry;
            }

            response()->json(['status' => 'success', 'data' => $tables])->send();
            exit();
            break;

        case 'columns':
            // Get columns for a specific table
            if (!$table || !Schema::hasTable($table)) {
                echo json_encode(['error' => "Table '$table' not found"], JSON_UNESCAPED_UNICODE);
                break;
            }
            $columns = Schema::getColumnListing($table);
            response()->json(['status' => 'success', 'table' => $table, 'data' => $columns])->send();
            exit();
            break;

        case 'values':
            // Get distinct values for a specific column
            $column = $request->input('column');
            if (!$column) {
                echo json_encode(['error' => 'Column param is required']);
                break;
            }
            if (!Schema::hasTable($table)) {
                echo json_encode(['error' => "Table '$table' not found"]);
                break;
            }
            if (!Schema::hasColumn($table, $column)) {
                echo json_encode(['error' => "Column '$column' not found"]);
                break;
            }
            $values = DB::table($table)
                ->select($column)
                ->distinct()
                ->orderBy($column)
                ->limit(100)
                ->pluck($column);

            response()->json(['status' => 'success', 'table' => $table, 'column' => $column, 'data' => $values])->send();
            exit();
            break;

        case 'calculate':
            // Calculate aggregates
            $column = $request->input('column');
            $operation = strtolower($request->input('operation', 'count'));
            $filter = $request->input('filter');

            if (!$column && $operation !== 'count') {
                echo json_encode(['error' => 'Column is required for this operation']);
                break;
            }

            $allowedOperations = ['sum', 'avg', 'min', 'max', 'count'];
            if (!in_array($operation, $allowedOperations)) {
                echo json_encode(['error' => 'Invalid operation']);
                break;
            }

            if (!Schema::hasTable($table)) {
                echo json_encode(['error' => "Table '$table' not found"]);
                break;
            }

            $query = DB::table($table);

            // Apply Filters via QueryBuilderService
            if ($filter) {
                $queryBuilder = app(\App\Services\QueryBuilderService::class);
                $query = $queryBuilder->apply($query, $filter);
            }

            if ($operation === 'count') {
                $result = $query->count();
            } else {
                if (!Schema::hasColumn($table, $column)) {
                    echo json_encode(['error' => "Column '$column' does not exist in table '$table'"]);
                    break;
                }
                $castedColumn = DB::raw("CAST(`$column` AS DECIMAL(20, 2))");
                switch ($operation) {
                    case 'sum': $result = $query->sum($castedColumn); break;
                    case 'avg': $result = $query->avg($castedColumn); break;
                    case 'min': $result = $query->min($castedColumn); break;
                    case 'max': $result = $query->max($castedColumn); break;
                }
            }

            response()->json([
                'table' => $table,
                'operation' => $operation,
                'column' => $column,
                'result' => $result,
                'status' => 'success'
            ])->send();
            exit();
            break;

        default:
            echo json_encode(['error' => 'Invalid action. Use: tables, columns, values, calculate']);
            break;
    }
} catch (\Exception $e) {
    echo json_encode(['error' => $e->getMessage()]);
}
@endphp