<?php
/**
 * @copyright (c) Kufer Software GmbH, Oderstraße 7, D-84453 Mühldorf
 */

declare(strict_types = 1);

namespace Kufer\Web\Classes\Dbfilter;

use Kufer\Web\Classes\Addfunctions\Guid;
use Kufer\Web\Classes\Identifier\Identifier;


class SQLFilterUtils
{
  private function __construct()
  {
  }

  public static function filterFalse(): callable
  {
    return static function(\SelectQueryBuilder $builder): \SelectQueryBuilder {
      return $builder->and('1 = 0');
    };
  }

  public static function filterEqualsString(string $praeffix, string $column, string $value): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value): \SelectQueryBuilder {
      $var = $builder->uniqueVar();

      return $builder
        ->and("$praeffix.$column = :$var:")
        ->paramString($var, $value);
    };
  }

  public static function filterEqualsStringDefault(string $praeffix, string $column, string $value, string $defaultValue): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $defaultValue): \SelectQueryBuilder {
      $var = $builder->uniqueVar();

      
      
      if ($builder->getDBOperation()->dbparser->servertyp === 'ORACLE') {
        
        if ($defaultValue === '' && $value === '') {
          return $builder->and("$praeffix.$column IS NULL");
        } elseif ($defaultValue === $value) {
          return $builder
            ->and("$praeffix.$column IS NOT NULL)")
            ->and("$praeffix.$column = :$var:")
            ->paramString($var, $value);
        } elseif ($defaultValue === '') {
          return $builder
            ->and("$praeffix.$column IS NOT NULL")
            ->and("$praeffix.$column = :$var:")
            ->paramString($var, $value);
        }
      }

      $defaultvar = $builder->uniqueVar();

      return $builder
        ->and(":ifnull($praeffix.$column, :$defaultvar:): = :$var:")
        ->paramString($defaultvar, $defaultValue)
        ->paramString($var, $value);
    };
  }

  public static function filterEqualsDateDefault(string $praeffix, string $column, string $value, string $defaultValue): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $defaultValue): \SelectQueryBuilder {
      
      $value = \DateTimeOperations::getDateFromDatetimeStr($value);
      $defaultValue = \DateTimeOperations::getDateFromDatetimeStr($defaultValue);

      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();
      $builder = $builder
        ->and(":ifnull($praeffix.$column, :$defaultvar:): = :$var:")
        ->paramDate($defaultvar, $defaultValue)
        ->paramDate($var, $value);

      $defaultvar = $builder->uniqueVar();
      $var = $builder->uniqueVar();

      return $builder
        ->or(":ifnull($praeffix.$column, :$defaultvar:): = :$var:")
        ->paramDateTime($defaultvar, $defaultValue . ' ' . DEFAULTTIME_WITH_SECONDS)
        ->paramDateTime($var, $value . ' ' . DEFAULTTIME_WITH_SECONDS);
    };
  }

  public static function filterEqualsDateTimeDefault(string $praeffix, string $column, string $value, string $defaultValue): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $defaultValue): \SelectQueryBuilder {
      $value = \DateTimeOperations::getValidDateTimeString($value);
      $defaultValue = \DateTimeOperations::getValidDateTimeString($defaultValue);

      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      return $builder
        ->and(":ifnull($praeffix.$column, :$defaultvar:): = :$var:")
        ->paramDateTime($defaultvar, $defaultValue)
        ->paramDateTime($var, $value);
    };
  }

  public static function filterEqualsDateNull(string $praeffix, string $column): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column): \SelectQueryBuilder {
      return $builder->andExpr(function(\SelectQueryBuilder $builder) use ($praeffix, $column): \SelectQueryBuilder {
        return $builder
          ->or("$praeffix.$column IS NULL")
          ->or("$praeffix.$column = :defaultdate:");
      });
    };
  }

  public static function filterEqualsDateTimeNull(string $praeffix, string $column): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column): \SelectQueryBuilder {
      return $builder->andExpr(function(\SelectQueryBuilder $builder) use ($praeffix, $column): \SelectQueryBuilder {
        $varDateEmpty = $builder->uniqueVar();

        return $builder
          ->or("$praeffix.$column IS NULL")
          ->or("$praeffix.$column = :$varDateEmpty:")
          ->paramDateTime($varDateEmpty, DEFAULTDATE . ' ' . DEFAULTTIME_WITH_SECONDS);
      });
    };
  }

  public static function filterEqualsNull(string $praeffix, string $column): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column): \SelectQueryBuilder {
      return $builder
        ->and("$praeffix.$column IS NULL");
    };
  }

  public static function filterEqualsStringNull(string $praeffix, string $column): callable
  {
    
    
    return static::filterEqualsNull($praeffix, $column);
  }

  
  public static function filterInStringDefault(string $praeffix, string $column, array $value, string $defaultValue): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $defaultValue): \SelectQueryBuilder {
      if (empty($value)) {
        return $builder
          ->and('1 = 0');
      }

      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      return $builder
        ->and(":ifnull($praeffix.$column, :$defaultvar:): IN (:$var:)")
        ->paramString($defaultvar, $defaultValue)
        ->paramStringArray($var, $value);
    };
  }

  public static function filterEqualsNotW(string $praeffix, string $column, string $defaultValue = 'F'): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $defaultValue): \SelectQueryBuilder {
      return $builder->notExpr(SQLFilterUtils::filterEqualsW($praeffix, $column, $defaultValue));
    };
  }

  public static function filterEqualsNot0Dot5(string $praeffix, string $column, string $defaultValue = '1.0'): callable
  {
    assert($defaultValue !== '');

    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $defaultValue): \SelectQueryBuilder {
      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      
      if ($builder->getDBOperation()->dbparser->servertyp !== 'ORACLE') {
        if ($defaultValue === 'W') { 
          return $builder
            ->and("(:ifnull($praeffix.$column, :$defaultvar:): != :$var:)")
            ->or("$praeffix.$column = ''")
            ->paramString($defaultvar, $defaultValue)
            ->paramString($var, '0.5');
        }
      }

      return $builder
        ->and("(:ifnull($praeffix.$column, :$defaultvar:): != :$var:)")
        ->paramString($defaultvar, $defaultValue)
        ->paramString($var, '0.5');
    };
  }

  public static function filterEquals0Dot5(string $praeffix, string $column, string $defaultValue = '1'): callable
  {
    assert($defaultValue !== '');

    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $defaultValue): \SelectQueryBuilder {
      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      
      if ($builder->getDBOperation()->dbparser->servertyp !== 'ORACLE') {
        if ($defaultValue === '1') { 
          return $builder
            ->and("(:ifnull($praeffix.$column, :$defaultvar:): = :$var:)")
            ->or("$praeffix.$column = ''")
            ->paramString($defaultvar, $defaultValue)
            ->paramString($var, '0.5');
        }
      }

      return $builder
        ->and("(:ifnull($praeffix.$column, :$defaultvar:): = :$var:)")
        ->paramString($defaultvar, $defaultValue)
        ->paramString($var, '0.5');
    };
  }

  public static function filterEqualsW(string $praeffix, string $column, string $defaultValue = 'F'): callable
  {
    assert($defaultValue !== '');

    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $defaultValue): \SelectQueryBuilder {
      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      
      if ($builder->getDBOperation()->dbparser->servertyp !== 'ORACLE') {
        if ($defaultValue === 'W') { 
          return $builder
            ->and("(:ifnull($praeffix.$column, :$defaultvar:): = :$var:)")
            ->or("$praeffix.$column = ''")
            ->paramString($defaultvar, $defaultValue)
            ->paramString($var, 'W');
        }
      }

      return $builder
        ->and("(:ifnull($praeffix.$column, :$defaultvar:): = :$var:)")
        ->paramString($defaultvar, $defaultValue)
        ->paramString($var, 'W');
    };
  }

  public static function filterEqualsNot1(string $praeffix, string $column, int $defaultValue = -1): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $defaultValue): \SelectQueryBuilder {
      return $builder->notExpr(SQLFilterUtils::filterEquals1($praeffix, $column, $defaultValue));
    };
  }

  public static function filterEquals1(string $praeffix, string $column, int $defaultValue = 1): callable
  {
    assert($defaultValue !== '');

    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $defaultValue): \SelectQueryBuilder {
      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      
      if ($builder->getDBOperation()->dbparser->servertyp !== 'ORACLE') {
        if ($defaultValue === 1) { 
          return $builder
            ->and("(:ifnull($praeffix.$column, :$defaultvar:): = :$var:)")
            ->or("$praeffix.$column = ''")
            ->paramInt($defaultvar, $defaultValue)
            ->paramInt($var, 1);
        }
      }

      return $builder
        ->and("(:ifnull($praeffix.$column, :$defaultvar:): = :$var:)")
        ->paramInt($defaultvar, $defaultValue)
        ->paramInt($var, 1);
    };
  }

  public static function filterEqualsNotJ(string $praeffix, string $column, string $defaultValue = 'F'): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $defaultValue): \SelectQueryBuilder {
      return $builder->notExpr(SQLFilterUtils::filterEqualsJ($praeffix, $column, $defaultValue));
    };
  }

  public static function filterEqualsJ(string $praeffix, string $column, string $defaultValue = 'N'): callable
  {
    assert($defaultValue !== '');

    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $defaultValue): \SelectQueryBuilder {
      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      
      if ($builder->getDBOperation()->dbparser->servertyp !== 'ORACLE') {
        if ($defaultValue === 'J') { 
          return $builder
            ->and("(:ifnull($praeffix.$column, :$defaultvar:): = :$var:)")
            ->or("$praeffix.$column = ''")
            ->paramString($defaultvar, $defaultValue)
            ->paramString($var, 'J');
        }
      }

      return $builder
        ->and("(:ifnull($praeffix.$column, :$defaultvar:): = :$var:)")
        ->paramString($defaultvar, $defaultValue)
        ->paramString($var, 'J');
    };
  }

  public static function filterLessStringDefault(string $praeffix, string $column, string $value, string $default): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $default): \SelectQueryBuilder {
      if ($value === '') { 
        return $builder->and('1 = 0');
      }

      $var = $builder->uniqueVar();

      
      
      if ($builder->getDBOperation()->dbparser->servertyp === 'ORACLE') {
        if ($default === '') {
          
          return $builder
            ->and("$praeffix.$column IS NULL")
            ->or("$praeffix.$column < :$var:")
            ->paramString($var, $value);
        }
      }

      $defaultvar = $builder->uniqueVar();

      return $builder
        ->and(":ifnull($praeffix.$column, :$defaultvar:): < :$var:")
        ->paramString($var, $value)
        ->paramString($defaultvar, $default);
    };
  }

  public static function filterLessDateDefault(string $praeffix, string $column, string $value, string $default): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $default): \SelectQueryBuilder {
      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      return $builder
        ->and(":ifnull($praeffix.$column, :$defaultvar:): < :$var:")
        ->paramDate($var, $value)
        ->paramDate($defaultvar, $default);
    };
  }

  public static function filterLessDateTimeDefault(string $praeffix, string $column, string $value, string $default): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $default): \SelectQueryBuilder {
      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      return $builder
        ->and(":ifnull($praeffix.$column, :$defaultvar:): < :$var:")
        ->paramDateTime($var, $value)
        ->paramDateTime($defaultvar, $default);
    };
  }

  public static function filterGreaterStringDefault(string $praeffix, string $column, string $value, string $default): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $default): \SelectQueryBuilder {
      $var = $builder->uniqueVar();

      
      
      if ($builder->getDBOperation()->dbparser->servertyp === 'ORACLE') {
        if ($default === '') {
          if ($value !== '') {
            
            return $builder
              ->and("$praeffix.$column IS NOT NULL")
              ->and("$praeffix.$column > :$var:")
              ->paramString($var, $value);
          } else {
            
            return $builder
              ->and("$praeffix.$column IS NOT NULL");
          }
        } elseif ($value === '') {
          
          return $builder
            ->and('1 = 0');
        }
      }

      $defaultvar = $builder->uniqueVar();

      return $builder
        ->and(":ifnull($praeffix.$column, :$defaultvar:): > :$var:")
        ->paramString($var, $value)
        ->paramString($defaultvar, $default);
    };
  }

  public static function filterGreaterDateDefault(string $praeffix, string $column, string $value, string $default): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $default): \SelectQueryBuilder {
      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      return $builder
        ->and(":ifnull($praeffix.$column, :$defaultvar:): > :$var:")
        ->paramDate($var, $value)
        ->paramDate($defaultvar, $default);
    };
  }

  public static function filterGreaterDateTimeDefault(string $praeffix, string $column, string $value, string $default): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $default): \SelectQueryBuilder {
      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      return $builder
        ->and(":ifnull($praeffix.$column, :$defaultvar:): > :$var:")
        ->paramDateTime($var, $value)
        ->paramDateTime($defaultvar, $default);
    };
  }

  public static function filterEqualsInt(string $praeffix, string $column, int $value): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value): \SelectQueryBuilder {
      $var = $builder->uniqueVar();

      return $builder
        ->and("$praeffix.$column = :$var:")
        ->paramInt($var, $value);
    };
  }

  public static function filterEqualsIntDefault(string $praeffix, string $column, int $value, int $default): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $default): \SelectQueryBuilder {
      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      return $builder
        ->and(":ifnull($praeffix.$column, :$defaultvar:): = :$var:")
        ->paramInt($var, $value)
        ->paramInt($defaultvar, $default);
    };
  }

  public static function filterLessIntDefault(string $praeffix, string $column, int $value, int $default): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $default): \SelectQueryBuilder {
      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      return $builder
        ->and(":ifnull($praeffix.$column, :$defaultvar:): < :$var:")
        ->paramInt($var, $value)
        ->paramInt($defaultvar, $default);
    };
  }

  public static function filterGreaterIntDefault(string $praeffix, string $column, int $value, int $default): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $default): \SelectQueryBuilder {
      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      return $builder
        ->and(":ifnull($praeffix.$column, :$defaultvar:): > :$var:")
        ->paramInt($var, $value)
        ->paramInt($defaultvar, $default);
    };
  }

  public static function filterInIntDefault(string $praeffix, string $column, array $value, int $default): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $default): \SelectQueryBuilder {
      if (empty($value)) {
        return $builder
          ->and('1 = 0');
      }

      $var = $builder->uniqueVar();
      $defaultvar = $builder->uniqueVar();

      return $builder
        ->and(":ifnull($praeffix.$column, :$defaultvar:): IN (:$var:)")
        ->paramIntArray($var, $value)
        ->paramInt($defaultvar, $default);
    };
  }

  public static function filterLessInt(string $praeffix, string $column, int $value): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value): \SelectQueryBuilder {
      $var = $builder->uniqueVar();

      return $builder
        ->and("$praeffix.$column < :$var:")
        ->paramInt($var, $value);
    };
  }

  public static function filterGreaterInt(string $praeffix, string $column, int $value): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value): \SelectQueryBuilder {
      $var = $builder->uniqueVar();

      return $builder
        ->and("$praeffix.$column > :$var:")
        ->paramInt($var, $value);
    };
  }

  public static function filterEqualsDate(string $praeffix, string $column, $value): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value): \SelectQueryBuilder {
      $var = $builder->uniqueVar();

      return $builder
        ->and("$praeffix.$column = :$var:")
        ->paramDate($var, $value);
    };
  }

  public static function filterLessDate(string $praeffix, string $column, $value): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value): \SelectQueryBuilder {
      $var = $builder->uniqueVar();

      return $builder
        ->and("$praeffix.$column < :$var:")
        ->paramDate($var, $value);
    };
  }

  public static function filterGreaterDate(string $praeffix, string $column, $value): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value): \SelectQueryBuilder {
      $var = $builder->uniqueVar();

      return $builder
        ->and("$praeffix.$column > :$var:")
        ->paramDate($var, $value);
    };
  }

  public static function filterCaseInsensitiveLike(string $praeffix, string $column, string $value): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value): \SelectQueryBuilder {
      $var = $builder->uniqueVar();

      return $builder->and($builder->getDBOperation()->dbparser->caseInsensitiveLike(
        $praeffix . '.',
        $column,
        ":$var:"
      ))
        ->paramStringNoQuotes($var, $value);
    };
  }

  
  public static function filterEqualsIdInt(string $idColumn, $value): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($idColumn, $value): \SelectQueryBuilder {
      $var = $builder->uniqueVar();

      return $builder->and(":table:.$idColumn = :$var:")
        ->paramInt($var, $value);
    };
  }

  
  public static function filterInIdInt(string $idColumn, $value): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($idColumn, $value): \SelectQueryBuilder {
      if (!\is_array($value)) {
        $value = (new \Kufer\Web\Classes\Stream\IterableStream($value))->toArray();
      }

      if (empty($value)) {
        return $builder
          ->and('1 = 0');
      }

      $var = $builder->uniqueVar();

      return $builder->and(":table:.$idColumn IN (:$var:)")
        ->paramIntArray($var, $value);
    };
  }

  public static function filterEqualsIdentifier(string $praeffix, Identifier $value, string $idColumn = 'id', string $guidColumn = 'guid'): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $value, $idColumn, $guidColumn): \SelectQueryBuilder {
      $var = $builder->uniqueVar();

      return $builder
        ->and(":$var:")
        ->paramId($var, $value, "$praeffix.$idColumn", "$praeffix.$guidColumn");
    };
  }

  
  public static function filterInIdentifier(string $praeffix, iterable $value, string $idColumn = 'id', string $guidColumn = 'guid'): callable
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $value, $idColumn, $guidColumn): \SelectQueryBuilder {
      if (empty($value)) {
        return $builder
          ->and('1 = 0');
      }

      $var = $builder->uniqueVar();

      return $builder
        ->and(":$var:")
        ->paramIdArray($var, $value, "$praeffix.$idColumn", "$praeffix.$guidColumn");
    };
  }

  public static function filterGuid(string $praeffix, string $column, ?Guid $value, ?Guid $defaultValue)
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $defaultValue): \SelectQueryBuilder {
      if (is_null($value)) {
        if (is_null($defaultValue)) {
          return $builder->and("$praeffix.$column IS NULL");
        } else {
          return $builder->and('1 = 0');
        }
      }

      $var = $builder->uniqueVar();
      if (is_null($defaultValue)) {
        return $builder
          ->and("$praeffix.$column IS NOT NULL")
          ->and("$praeffix.$column = :$var:")
          ->paramGuid($var, $value);
      } else {
        $defaultVar = $builder->uniqueVar();

        return $builder
          ->and(":ifnull($praeffix.$column, :$defaultVar:): = :$var:")
          ->paramGuid($var, $value)
          ->paramGuid($defaultVar, $defaultValue);
      }
    };
  }

  
  public static function filterInGuid(string $praeffix, string $column, iterable $value, ?Guid $defaultValue)
  {
    return static function(\SelectQueryBuilder $builder) use ($praeffix, $column, $value, $defaultValue): \SelectQueryBuilder {
      if (!\is_array($value)) {
        $value = (new \Kufer\Web\Classes\Stream\IterableStream($value))->toArray();
      }

      if (empty($value)) {
        return $builder->and('1 = 0');
      }

      $var = $builder->uniqueVar();

      if (is_null($defaultValue)) {
        return $builder
          ->and("$praeffix.$column IS NOT NULL")
          ->and("$praeffix.$column IN (:$var:)")
          ->paramGuidArray($var, $value);
      } else {
        $defaultVar = $builder->uniqueVar();

        return $builder
          ->and(":ifnull($praeffix.$column, :$defaultVar:): IN (:$var:)")
          ->paramGuidArray($var, $value)
          ->paramGuid($defaultVar, $defaultValue);
      }
    };
  }
}