<?php

namespace App\Libraries;

class QueryBuilder
{
    /**
     * @param array<string, mixed> $visualConfig
     * @return array{sql:string, bindings:array<int, mixed>}
     */
    public function toSQL(array $visualConfig): array
    {
        $table = trim((string) ($visualConfig['table'] ?? ''));
        if ($table === '') {
            throw new \InvalidArgumentException('Table is required.');
        }

        $columns = $visualConfig['columns'] ?? ['*'];
        if (! is_array($columns) || $columns === []) {
            $columns = ['*'];
        }

        $safeColumns = [];
        foreach ($columns as $column) {
            $safeColumns[] = $this->safeIdentifier((string) $column);
        }
        $selectClause = implode(', ', $safeColumns);

        $sql = 'SELECT ' . $selectClause . ' FROM ' . $this->safeIdentifier($table);
        $bindings = [];

        $filters = $visualConfig['filters'] ?? [];
        if (is_array($filters) && $filters !== []) {
            $whereParts = [];
            foreach ($filters as $filter) {
                if (! is_array($filter)) {
                    continue;
                }

                $field = $this->safeIdentifier((string) ($filter['field'] ?? ''));
                $operator = strtoupper(trim((string) ($filter['operator'] ?? '=')));
                $value = $filter['value'] ?? null;

                if ($field === '' || $operator === '') {
                    continue;
                }

                if (in_array($operator, ['IS NULL', 'IS NOT NULL'], true)) {
                    $whereParts[] = $field . ' ' . $operator;
                    continue;
                }

                if (! in_array($operator, ['=', '!=', '>', '<', '>=', '<=', 'LIKE'], true)) {
                    throw new \InvalidArgumentException('Unsupported filter operator.');
                }

                $whereParts[] = $field . ' ' . $operator . ' ?';
                $bindings[] = $value;
            }

            if ($whereParts !== []) {
                $sql .= ' WHERE ' . implode(' AND ', $whereParts);
            }
        }

        $groupBy = $visualConfig['group_by'] ?? [];
        if (is_array($groupBy) && $groupBy !== []) {
            $groupColumns = [];
            foreach ($groupBy as $groupField) {
                $groupColumns[] = $this->safeIdentifier((string) $groupField);
            }
            $groupColumns = array_filter($groupColumns);
            if ($groupColumns !== []) {
                $sql .= ' GROUP BY ' . implode(', ', $groupColumns);
            }
        }

        $orderBy = $visualConfig['order_by'] ?? [];
        $orderClauses = [];
        if (is_array($orderBy) && array_key_exists('column', $orderBy)) {
            $orderBy = [$orderBy];
        }
        if (is_array($orderBy)) {
            foreach ($orderBy as $clause) {
                if (! is_array($clause)) {
                    continue;
                }
                $col = trim((string) ($clause['column'] ?? ''));
                if ($col === '') {
                    continue;
                }
                $direction = strtoupper((string) ($clause['direction'] ?? 'ASC'));
                $direction = $direction === 'DESC' ? 'DESC' : 'ASC';
                $orderClauses[] = $this->safeIdentifier($col) . ' ' . $direction;
            }
        }
        if ($orderClauses !== []) {
            $sql .= ' ORDER BY ' . implode(', ', $orderClauses);
        }

        $limitRaw = $visualConfig['limit'] ?? 500;
        $limit = is_int($limitRaw) ? $limitRaw : (int) (string) $limitRaw;
        if ($limit <= 0) {
            $limit = 500;
        }
        $sql .= ' LIMIT ' . $limit;

        return ['sql' => $sql, 'bindings' => $bindings];
    }

    private function safeIdentifier(string $value): string
    {
        $value = trim($value);
        if ($value === '*') {
            return $value;
        }

        if (! preg_match('/^[a-zA-Z0-9_.]+$/', $value)) {
            throw new \InvalidArgumentException('Invalid identifier in visual query.');
        }

        return $value;
    }
}