<?php
declare(strict_types=1);

/**
 * DevExtreme filter 表达式 -> SQL 转换器
 * 支持: = <> > >= < <= startswith endswith contains notcontains
 *      between anyof noneof isblank isnotblank
 *      "!" 取反、and/or 组合（可混用、可嵌套）、两元素简写（默认 "="）、null 值（IS NULL/IS NOT NULL）
 */
final class DxFilter
{
    private const OPERATORS = [
        '=', '<>', '>', '>=', '<', '<=',
        'startswith', 'endswith', 'contains', 'notcontains',
        'between', 'anyof', 'noneof', 'isblank', 'isnotblank',
    ];

    /**
     * @param mixed $filter DevExtreme loadOptions.filter，可能为空/数组
     * @return array{0:string,1:array} [SQL 片段（不含 WHERE 关键字，为空时不需要拼 WHERE）, 绑定参数]
     */
    public static function toSql(mixed $filter): array
    {
        if (empty($filter) || !is_array($filter)) {
            return ['', []];
        }

        return self::parse($filter);
    }

    /**
     * 字段名加反引号，支持 "别名.字段" 形式
     */
    public static function quoteColumn(string $field): string
    {
        return implode('.', array_map(
            static fn(string $part): string => '`' . str_replace('`', '', $part) . '`',
            explode('.', $field)
        ));
    }

    private static function parse(array $expr): array
    {
        $count = count($expr);

        // ["!", 子表达式]
        if ($count === 2 && $expr[0] === '!') {
            [$sql, $params] = self::parse($expr[1]);
            return ["NOT ({$sql})", $params];
        }

        // [字段, 操作符, 值]
        if ($count === 3 && is_string($expr[1]) && in_array($expr[1], self::OPERATORS, true)) {
            return self::leaf((string) $expr[0], $expr[1], $expr[2]);
        }

        // [字段, 值] 简写，等价于 [字段, "=", 值]
        if ($count === 2) {
            return self::leaf((string) $expr[0], '=', $expr[1]);
        }

        // [表达式1, "and"|"or", 表达式2, "and"|"or", 表达式3, ...]
        return self::combination($expr);
    }

    private static function combination(array $expr): array
    {
        [$sql, $params] = self::parse($expr[0]);
        $sql = "({$sql})";

        for ($i = 1, $count = count($expr); $i < $count; $i += 2) {
            $logic = strtoupper((string) $expr[$i]);
            [$nextSql, $nextParams] = self::parse($expr[$i + 1]);
            $sql = "({$sql} {$logic} ({$nextSql}))";
            $params = [...$params, ...$nextParams];
        }

        return [$sql, $params];
    }

    private static function leaf(string $field, string $operator, mixed $value): array
    {
        $column = self::quoteColumn($field);

        return match ($operator) {
            '=' => $value === null ? ["{$column} IS NULL", []] : ["{$column} = ?", [$value]],
            '<>' => $value === null ? ["{$column} IS NOT NULL", []] : ["{$column} <> ?", [$value]],
            '>' => ["{$column} > ?", [$value]],
            '>=' => ["{$column} >= ?", [$value]],
            '<' => ["{$column} < ?", [$value]],
            '<=' => ["{$column} <= ?", [$value]],
            'startswith' => ["{$column} LIKE ?", [self::escapeLike((string) $value) . '%']],
            'endswith' => ["{$column} LIKE ?", ['%' . self::escapeLike((string) $value)]],
            'contains' => ["{$column} LIKE ?", ['%' . self::escapeLike((string) $value) . '%']],
            'notcontains' => ["{$column} NOT LIKE ?", ['%' . self::escapeLike((string) $value) . '%']],
            'between' => ["{$column} BETWEEN ? AND ?", [$value[0], $value[1]]],
            'anyof' => self::inList($column, (array) $value, false),
            'noneof' => self::inList($column, (array) $value, true),
            'isblank' => ["({$column} IS NULL OR {$column} = '')", []],
            'isnotblank' => ["({$column} IS NOT NULL AND {$column} <> '')", []],
            default => throw new MvcException("不支持的过滤操作符: {$operator}"),
        };
    }

    private static function inList(string $column, array $values, bool $negate): array
    {
        if (empty($values)) {
            // anyof 空列表匹配不到任何记录；noneof 空列表不排除任何记录
            return [$negate ? '1=1' : '1=0', []];
        }

        $placeholders = implode(',', array_fill(0, count($values), '?'));

        if (!$negate) {
            return ["{$column} IN ({$placeholders})", $values];
        }

        // NULL 安全的 NOT IN：避免该列为 NULL 时 NOT IN 判定为 UNKNOWN 而被意外排除
        return ["({$column} NOT IN ({$placeholders}) OR {$column} IS NULL)", $values];
    }

    private static function escapeLike(string $value): string
    {
        return str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $value);
    }
}