<?php

namespace PublishPress\FuturePro\Modules\Workflows\Domain\Engine;

use PublishPress\FuturePro\Modules\Workflows\Interfaces\JsonLogicPreprocessorInterface;
use InvalidArgumentException;

class JsonLogicSqlPreprocessor implements JsonLogicPreprocessorInterface
{
    private array $parameters = [];
    private int $paramCounter = 0;

    public function process(array $jsonLogic): array
    {
        $this->parameters = [];
        $this->paramCounter = 0;

        $sql = $this->convertToSql($jsonLogic);

        return [
            'sql' => $sql,
            'parameters' => $this->parameters
        ];
    }

    private function convertToSql($value): string
    {
        // If it's a scalar value, return it as a parameter
        if (!is_array($value)) {
            $this->paramCounter++;
            $paramName = "param_{$this->paramCounter}";
            $this->parameters[$paramName] = $value;
            return '%s';
        }

        if (empty($value)) {
            throw new InvalidArgumentException('Empty JSON Logic expression');
        }

        $operator = array_keys($value)[0];
        $values = $value[$operator];

        if (!is_array($values)) {
            $values = [$values];
        }

        switch ($operator) {
            case 'and':
                return $this->processAndOperator($values);
            case 'or':
                return $this->processOrOperator($values);
            case '==':
                return $this->processEqualityOperator($values);
            case '!=':
                return $this->processInequalityOperator($values);
            case '>':
                return $this->processComparisonOperator($values, '>');
            case '>=':
                return $this->processComparisonOperator($values, '>=');
            case '<':
                return $this->processComparisonOperator($values, '<');
            case '<=':
                return $this->processComparisonOperator($values, '<=');
            case 'in':
                return $this->processInOperator($values);
            case 'not':
                return $this->processNotOperator($values);
            case 'var':
                return $this->processVarOperator($values[0]);
            case 'between':
                return $this->processBetweenOperator($values);
            case 'contains':
                return $this->processContainsOperator($values);
            case 'startsWith':
                return $this->processStartsWithOperator($values);
            case 'endsWith':
                return $this->processEndsWithOperator($values);
            default:
                throw new InvalidArgumentException(
                    sprintf('Unsupported operator: %s', esc_html($operator))
                );
        }
    }

    private function processAndOperator(array $conditions): string
    {
        $sqlParts = array_map(
            function ($condition) {
                return '(' . $this->convertToSql($condition) . ')';
            },
            $conditions
        );
        return implode(' AND ', $sqlParts);
    }

    private function processOrOperator(array $conditions): string
    {
        $sqlParts = array_map(
            function ($condition) {
                return '(' . $this->convertToSql($condition) . ')';
            },
            $conditions
        );
        return implode(' OR ', $sqlParts);
    }

    private function processEqualityOperator(array $values): string
    {
        $field = $this->convertToSql($values[0]);

        if (isset($values[1])) {
            if (is_array($values[1]) && isset($values[1]['var'])) {
                $value = $this->processVarOperator($values[1]['var']);
                return "{$field} = {$value}";
            }

            $this->paramCounter++;
            $paramName = "param_{$this->paramCounter}";
            $this->parameters[$paramName] = $values[1];
            return "{$field} = %s";
        }

        return "{$field} = %s";
    }

    private function processInequalityOperator(array $values): string
    {
        $field = $this->convertToSql($values[0]);

        if (isset($values[1])) {
            if (is_array($values[1]) && isset($values[1]['var'])) {
                $value = $this->processVarOperator($values[1]['var']);
                return "{$field} != {$value}";
            }

            $this->paramCounter++;
            $paramName = "param_{$this->paramCounter}";
            $this->parameters[$paramName] = $values[1];
            return "{$field} != %s";
        }

        return "{$field} != %s";
    }

    private function processComparisonOperator(array $values, string $operator): string
    {
        $field = $this->convertToSql($values[0]);

        if (isset($values[1])) {
            if (is_array($values[1]) && isset($values[1]['var'])) {
                $value = $this->processVarOperator($values[1]['var']);
                return "{$field} {$operator} {$value}";
            }

            $this->paramCounter++;
            $paramName = "param_{$this->paramCounter}";
            $this->parameters[$paramName] = $values[1];
            return "{$field} {$operator} %s";
        }

        return "{$field} {$operator} %s";
    }

    private function processInOperator(array $values): string
    {
        $field = $this->convertToSql($values[0]);

        if (!isset($values[1])) {
            return "{$field} IN (%s)";
        }

        if (is_array($values[1])) {
            if (isset($values[1]['var'])) {
                $value = $this->processVarOperator($values[1]['var']);
                return "{$field} IN ({$value})";
            }

            $placeholders = [];
            foreach ($values[1] as $item) {
                $this->paramCounter++;
                $paramName = "param_{$this->paramCounter}";
                $this->parameters[$paramName] = $item;
                $placeholders[] = '%s';
            }
            return "{$field} IN (" . implode(', ', $placeholders) . ")";
        }

        $this->paramCounter++;
        $paramName = "param_{$this->paramCounter}";
        $this->parameters[$paramName] = $values[1];
        return "{$field} IN (%s)";
    }

    private function processNotOperator(array $values): string
    {
        return 'NOT (' . $this->convertToSql($values[0]) . ')';
    }

    private function processVarOperator(string $field): string
    {
        // Sanitize the field name to prevent SQL injection
        $field = preg_replace('/[^a-zA-Z0-9_.]/', '', $field);

        // If the field contains a dot, it's likely a table.column reference
        if (strpos($field, '.') !== false) {
            list($table, $column) = explode('.', $field);
            return "{$table}.{$column}";
        }

        return "p.{$field}";
    }

    private function processBetweenOperator(array $values): string
    {
        $field = $this->convertToSql($values[0]);

        if (isset($values[1]) && isset($values[2])) {
            if (is_array($values[1]) && isset($values[1]['var'])) {
                $value1 = $this->processVarOperator($values[1]['var']);
                $value2 = isset($values[2]['var']) ?
                    $this->processVarOperator($values[2]['var']) :
                    $this->convertToSql($values[2]);
                return "{$field} BETWEEN {$value1} AND {$value2}";
            }

            $this->paramCounter++;
            $paramName1 = "param_{$this->paramCounter}";
            $this->parameters[$paramName1] = $values[1];

            $this->paramCounter++;
            $paramName2 = "param_{$this->paramCounter}";
            $this->parameters[$paramName2] = $values[2];

            return "{$field} BETWEEN %s AND %s";
        }

        return "{$field} BETWEEN %s AND %s";
    }

    private function processContainsOperator(array $values): string
    {
        $field = $this->convertToSql($values[0]);

        if (isset($values[1])) {
            if (is_array($values[1]) && isset($values[1]['var'])) {
                $value = $this->processVarOperator($values[1]['var']);
                return "LOWER({$field}) LIKE CONCAT('%%', LOWER({$value}), '%%')";
            }

            $this->paramCounter++;
            $paramName = "param_{$this->paramCounter}";
            $this->parameters[$paramName] = '%' . $values[1] . '%';
            return "LOWER({$field}) LIKE LOWER(%s)";
        }

        return "LOWER({$field}) LIKE LOWER(%s)";
    }

    private function processStartsWithOperator(array $values): string
    {
        $field = $this->convertToSql($values[0]);

        if (isset($values[1])) {
            if (is_array($values[1]) && isset($values[1]['var'])) {
                $value = $this->processVarOperator($values[1]['var']);
                return "LOWER({$field}) LIKE CONCAT(LOWER({$value}), '%%')";
            }

            $this->paramCounter++;
            $paramName = "param_{$this->paramCounter}";
            $this->parameters[$paramName] = $values[1] . '%';
            return "LOWER({$field}) LIKE LOWER(%s)";
        }

        return "LOWER({$field}) LIKE LOWER(%s)";
    }

    private function processEndsWithOperator(array $values): string
    {
        $field = $this->convertToSql($values[0]);

        if (isset($values[1])) {
            if (is_array($values[1]) && isset($values[1]['var'])) {
                $value = $this->processVarOperator($values[1]['var']);
                return "LOWER({$field}) LIKE CONCAT('%%', LOWER({$value}))";
            }

            $this->paramCounter++;
            $paramName = "param_{$this->paramCounter}";
            $this->parameters[$paramName] = '%' . $values[1];
            return "LOWER({$field}) LIKE LOWER(%s)";
        }

        return "LOWER({$field}) LIKE LOWER(%s)";
    }
}
