<?php

namespace App\Libraries;

class QueryVariableResolver
{
    private const SYSTEM_NAMES = ['today', 'now', 'yesterday', 'last_7_days', 'last_30_days'];

    public function isSystemName(string $name): bool
    {
        return in_array(strtolower($name), self::SYSTEM_NAMES, true);
    }

    /**
     * @param array<string, mixed> $inputValues
     * @return array{sql:string, bindings:array<int, mixed>}
     */
    public function resolve(string $query, array $inputValues = []): array
    {
        $bindings = [];
        $sql = preg_replace_callback(
            '/\{\{\s*([a-zA-Z_][a-zA-Z0-9_]*)\s*\}\}/',
            function (array $match) use (&$bindings, $inputValues): string {
                $name = strtolower((string) ($match[1] ?? ''));
                $systemValue = $this->isSystemName($name) ? $this->systemValue($name) : null;

                if ($systemValue !== null) {
                    $bindings[] = $systemValue;
                    return '?';
                }

                $value = $inputValues[$name] ?? null;
                if (is_array($value)) {
                    $value = array_values(array_filter($value, static fn($v) => $v !== '' && $v !== null));
                    if ($value === []) {
                        $bindings[] = null;
                        return '?';
                    }

                    foreach ($value as $single) {
                        $bindings[] = $single;
                    }

                    return implode(', ', array_fill(0, count($value), '?'));
                }

                $bindings[] = $value;
                return '?';
            },
            $query
        );

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

    /**
     * Inline substitution for identifiers and literals (e.g. visual builder fields). Not for SQL ? placeholders.
     *
     * @param array<string, mixed> $inputValues
     */
    public function resolveTemplateString(string $template, array $inputValues = []): string
    {
        $out = preg_replace_callback(
            '/\{\{\s*([a-zA-Z_][a-zA-Z0-9_]*)\s*\}\}/',
            function (array $match) use ($inputValues): string {
                $name = strtolower((string) ($match[1] ?? ''));
                if ($this->isSystemName($name)) {
                    return (string) ($this->systemValue($name) ?? '');
                }
                $value = $inputValues[$name] ?? null;
                if (is_array($value)) {
                    $flat = array_values(array_filter($value, static fn ($v) => $v !== '' && $v !== null));

                    return $flat === [] ? '' : (string) $flat[0];
                }
                if ($value === null) {
                    return '';
                }

                return (string) $value;
            },
            $template
        );

        return (string) ($out ?? $template);
    }

    private function systemValue(string $name): ?string
    {
        return match ($name) {
            'today' => date('Y-m-d'),
            'now' => date('Y-m-d H:i:s'),
            'yesterday' => date('Y-m-d', strtotime('-1 day')),
            'last_7_days' => date('Y-m-d', strtotime('-7 days')),
            'last_30_days' => date('Y-m-d', strtotime('-30 days')),
            default => null,
        };
    }
}