<?php

declare(strict_types=1);

namespace PpPhp\Core;

/**
 * SqlVars — PHPRunner-style SQL variables for author-written WHERE clauses and
 * queries. Expands tokens like :session.key, :user.field, :userid, :username,
 * :get.key, :post.key, :master.field, :language and :{name with spaces} into
 * SAFE, correctly-quoted SQL literals.
 *
 *   "WHERE sales_rep = :user.username"   →   "WHERE sales_rep = 'jsmith'"
 *   "WHERE owner_id  = :userid"          →   "WHERE owner_id = 5"
 *   "WHERE id IN (:session.cartIds)"     →   "WHERE id IN (12,15,20)"
 *
 * Quoting is automatic and by type — strings are escaped + quoted, numbers go
 * in raw, arrays become a comma list (for IN). You never add quotes yourself.
 * A `:1` / `:2` positional placeholder is left untouched (that's PrepareSQL's
 * job) and a `::cast` (PostgreSQL) is not mistaken for a variable.
 *
 * Sources are read live from the request: $_SESSION, the current user
 * (Security::CurrentUserData), $_GET / $_POST, plus anything you pass in the
 * optional $context array (['master' => [...], 'old' => [...], 'new' => [...],
 * 'user' => [...], 'field' => value]).
 */
final class SqlVars
{
    /** @var array<string, mixed>|null cached current-user row for this request */
    private static ?array $userCache = null;

    /**
     * Expand every :variable in $sql to a safe literal.
     *
     * @param array<string, mixed> $context extra sources (master/old/new/user/fields)
     */
    public static function expand(string $sql, array $context = []): string
    {
        if ($sql === '' || strpos($sql, ':') === false) {
            return $sql;
        }

        // 1) :{braced name}  — allows spaces & dots inside, e.g. :{master.Customer ID}
        $sql = preg_replace_callback('/(?<!:):\{([^}]+)\}/', static function (array $m) use ($context): string {
            return self::literal(self::resolve(trim($m[1]), $context));
        }, $sql) ?? $sql;

        // 2) :scope.key  — :session.x, :user.x, :daysago.30, :cookie.x …  (not ::cast).
        //    The key may be numeric (relative-date offsets) or a name.
        $sql = preg_replace_callback('/(?<!:):([A-Za-z_][A-Za-z0-9_]*)\.([A-Za-z0-9_]+)/', static function (array $m) use ($context): string {
            return self::literal(self::resolve($m[1] . '.' . $m[2], $context));
        }, $sql) ?? $sql;

        // 3) :name  — :userid, :username, :language, or a context field.
        //    A leading digit (:1, :2) can't match — those stay for PrepareSQL.
        $sql = preg_replace_callback('/(?<!:):([A-Za-z_][A-Za-z0-9_]*)/', static function (array $m) use ($context): string {
            return self::literal(self::resolve($m[1], $context));
        }, $sql) ?? $sql;

        return $sql;
    }

    /** True if $sql contains any :variable (excluding positional :1 / ::casts). */
    public static function has(string $sql): bool
    {
        return (bool) preg_match('/(?<!:):(\{[^}]+\}|[A-Za-z_][A-Za-z0-9_]*)/', $sql);
    }

    /**
     * The form-field names a query references via :field.name / :form.name (or
     * their braced forms). Used to know which sibling fields a lookup depends on
     * so the control can send them and re-filter when they change.
     *
     * @return array<int, string>
     */
    public static function fieldRefs(string $sql): array
    {
        $out = [];
        if (preg_match_all('/(?<!:):(?:field|form)\.([A-Za-z_][A-Za-z0-9_]*)/i', $sql, $m)) {
            $out = $m[1];
        }
        if (preg_match_all('/:\{(?:field|form)\.([^}]+)\}/i', $sql, $m2)) {
            foreach ($m2[1] as $n) {
                $out[] = trim($n);
            }
        }
        return array_values(array_unique($out));
    }

    // ── resolution ────────────────────────────────────────────────────

    /** @param array<string, mixed> $ctx */
    private static function resolve(string $name, array $ctx): mixed
    {
        $name = trim($name);

        if (strpos($name, '.') !== false) {
            [$scope, $key] = explode('.', $name, 2);
            $scope = strtolower(trim($scope));
            $key   = trim($key);

            // Relative date/time offsets — :daysago.30, :daysfromnow.7, :hoursago.2 …
            if (in_array($scope, ['daysago', 'daysfromnow', 'hoursago', 'hoursfromnow', 'monthsago', 'monthsfromnow', 'yearsago', 'yearsfromnow'], true)) {
                $n    = (int) $key;
                $sign = str_contains($scope, 'ago') ? '-' : '+';
                $unit = str_starts_with($scope, 'days') ? 'days'
                    : (str_starts_with($scope, 'hours') ? 'hours'
                    : (str_starts_with($scope, 'months') ? 'months' : 'years'));
                return self::d("{$sign}{$n} {$unit}", $unit === 'hours');
            }

            return match ($scope) {
                'session'          => $_SESSION[$key] ?? ($ctx['session'][$key] ?? null),
                'user'             => self::user($ctx)[$key] ?? null,
                'get'              => $_GET[$key] ?? null,
                'post'             => $_POST[$key] ?? null,
                'request'          => $_REQUEST[$key] ?? null,
                'cookie'           => $_COOKIE[$key] ?? null,
                // Another field on the SAME form (Add/Edit) — the cascading-lookup
                // case: WHERE country_id = :field.country. Sent by the control.
                'field', 'form'    => $ctx['field'][$key] ?? null,
                'master'           => $ctx['master'][$key] ?? null,
                'old'              => $ctx['old'][$key] ?? null,
                'new'              => $ctx['new'][$key] ?? ($ctx[$key] ?? null),
                default            => $ctx[$name] ?? null,
            };
        }

        return match (strtolower($name)) {
            'userid'      => self::user($ctx)['id'] ?? self::user($ctx)['user_id'] ?? null,
            'username'    => self::user($ctx)['username'] ?? self::user($ctx)['login'] ?? self::currentUsername(),
            'userrole', 'usergroup' => self::userRole(),
            'language'    => self::language($ctx),
            // dynamic date/time — great for rolling-window filters, no PHP needed
            'now'         => self::d('now', true),
            'today'       => self::d('today'),
            'yesterday'   => self::d('yesterday'),
            'tomorrow'    => self::d('tomorrow'),
            'startofweek' => self::d('monday this week'),
            'endofweek'   => self::d('sunday this week'),
            'startofmonth'=> self::d('first day of this month'),
            'endofmonth'  => self::d('last day of this month'),
            'startofyear' => self::d('first day of January this year'),
            'endofyear'   => self::d('last day of December this year'),
            'ip'          => (string) ($_SERVER['REMOTE_ADDR'] ?? ''),
            'uuid'        => self::uuid(),
            default       => $ctx[$name] ?? null,   // a plain :field passed in $context
        };
    }

    /** Format a strtotime() offset as a date (or datetime). */
    private static function d(string $modify, bool $time = false): string
    {
        $t = strtotime($modify);
        if ($t === false) {
            $t = time();
        }
        return date($time ? 'Y-m-d H:i:s' : 'Y-m-d', $t);
    }

    private static function userRole(): ?string
    {
        if (class_exists('\PpPhp\Core\Security') && method_exists('\PpPhp\Core\Security', 'CurrentUserGroup')) {
            $g = Security::CurrentUserGroup();
            return $g !== '' ? $g : null;
        }
        return null;
    }

    private static function uuid(): string
    {
        $b = random_bytes(16);
        $b[6] = chr((ord($b[6]) & 0x0f) | 0x40);
        $b[8] = chr((ord($b[8]) & 0x3f) | 0x80);
        return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($b), 4));
    }

    /** @param array<string, mixed> $ctx @return array<string, mixed> */
    private static function user(array $ctx): array
    {
        if (isset($ctx['user']) && is_array($ctx['user'])) {
            return $ctx['user'];
        }
        if (self::$userCache !== null) {
            return self::$userCache;
        }
        $u = [];
        if (class_exists('\PpPhp\Core\Security') && method_exists('\PpPhp\Core\Security', 'CurrentUserData')) {
            $d = Security::CurrentUserData();
            if (is_array($d)) {
                $u = $d;
            }
        }
        return self::$userCache = $u;
    }

    private static function currentUsername(): ?string
    {
        if (class_exists('\PpPhp\Core\Security') && method_exists('\PpPhp\Core\Security', 'CurrentUsername')) {
            $n = Security::CurrentUsername();
            return $n !== '' ? $n : null;
        }
        return null;
    }

    /** @param array<string, mixed> $ctx */
    private static function language(array $ctx): string
    {
        return (string) ($ctx['language']
            ?? $_SESSION['pp_lang']
            ?? $_SESSION['language']
            ?? $_SESSION['lang']
            ?? (defined('PP_LANG') ? PP_LANG : 'English'));
    }

    /** Reset the per-request user cache (tests). */
    public static function reset(): void
    {
        self::$userCache = null;
    }

    // ── safe literal ──────────────────────────────────────────────────

    private static function literal(mixed $value): string
    {
        if ($value === null) {
            return "''";                         // missing → empty string (never breaks the SQL)
        }
        if (is_bool($value)) {
            return $value ? '1' : '0';
        }
        if (is_int($value) || is_float($value)) {
            return (string) $value;
        }
        if (is_array($value)) {
            if ($value === []) {
                return "''";
            }
            return implode(',', array_map([self::class, 'literal'], array_values($value)));
        }
        $s = (string) $value;
        // pure integer string → number (so `owner_id = :userid` compares numerically)
        if ($s !== '' && ctype_digit(ltrim($s, '-')) && $s[0] !== '0' || $s === '0') {
            return $s;
        }
        return "'" . str_replace("'", "''", $s) . "'";   // escape + quote
    }
}
