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

declare(strict_types = 1);

namespace Kufer\Web\Classes\Dbfilter;


class KurseSQLFilter extends \Kufer\Web\Classes\Dbfilter\BasicSQLFilter
{
  public const FILTER_OPTION_DOZENTEN = 'dozenten';
  public const FILTER_OPTION_KATEGORIEN = 'kategorien';
  public const FILTER_OPTION_BELEGUNGEN = 'belegungen';
  public const FILTER_OPTION_KURSE_FAECHER = 'kurseFaecher';
  public const FILTER_OPTION_KURSGRUPPEN = 'kursgruppen';

  protected function __construct()
  {
    parent::__construct();

    $this->registerDb('Id', 'nr', static::TYPE_STRING); 
    $this->registerDb('Kursnummer', 'nr_anzeige', static::TYPE_STRING); 
    $this->registerDb('Typ', 'typ', static::TYPE_STRING);
    $this->registerDb('Semester', 'semester', static::TYPE_STRING);
    $this->registerDb('Kurzbezeichnung', 'kurzbez', static::TYPE_STRING);
    $this->registerDb('Programmhefttitel', 'titel', static::TYPE_STRING);
    $this->registerDb('Verlauf', 'verlauf', static::TYPE_STRING);
    $this->registerDb('Aussenstelle', 'aussenst', static::TYPE_STRING);
    $this->registerDb('PlanenderMitarbeiter', 'hpm', static::TYPE_STRING);
    $this->registerDb('VerwaltenderMitarbeiter', 'verwaltung_ma', static::TYPE_STRING, null, 'kaa');
    $this->registerDb('Ausfall', 'ausfall', static::TYPE_BOOLEAN_J_N);
    $this->registerDb('Abschluss', 'abschluss', static::TYPE_BOOLEAN_J_N);
    $this->registerDb('Status', 'abschluss', static::TYPE_STRING_STRING_DEFAULT_N_OR_EMPTY);
    $this->registerDb('BeginnDatum', 'beginn_dat', static::TYPE_DATE);
    $this->registerDb('EndeDatum', 'ende_dat', static::TYPE_DATE);
    $this->registerDb('Beginn', 'beginn', static::TYPE_STRING); 
    $this->registerDb('Ende', 'ende', static::TYPE_STRING); 
    $this->registerDb('Dozentenbewerbung', 'dozentenbewerbung', static::TYPE_INTEGER);
    $this->registerDb('Ziel', 'kurs_zielg', static::TYPE_STRING);
    $this->registerDb('Ort', 'ort', static::TYPE_STRING);
    
    $this->registerDb('FachbereichKennzeichen', 'fachb', static::TYPE_STRING);
    $this->registerDb('ShownOnWeb', 'internetaktiv', static::TYPE_BOOLEAN_W_F);
    $this->registerDb('ShownOnIntranet', 'intranet_aktiv', static::TYPE_BOOLEAN_W_F, true);
    $this->registerDb('Anmeldebedingungen', 'zielgruppe', static::TYPE_STRING, null, 'kaa');
    $this->registerDb('Info', 'info', static::TYPE_STRING);
    $this->registerDb('WebInfo', 'webinfo', static::TYPE_STRING);
    $this->registerDb('Material', 'material', static::TYPE_STRING);
    $this->registerDb('Vorspann', 'vorspann', static::TYPE_STRING);
    $this->registerDb('Nachspann', 'nachspann', static::TYPE_STRING);
    $this->registerDb('WebSuchbegriffe', 'websuchbegriffe', static::TYPE_STRING);
    $this->registerDb('Voraussetzungen', 'voraussetzungen', static::TYPE_STRING, null, 'kaa');
    $this->registerDb('Zielgruppe', 'zielgruppe', static::TYPE_STRING, null, 'kaa');
    $this->registerDb('ELearningId', 'elearningkursid', static::TYPE_STRING);

    $freidefFelder = \DBWorkFreiWaehlbareEingabefelder4Kurs::getDefaultInstance()->getFreiWaehlbareFelder();
    foreach ($freidefFelder as $freidef) {
      $virtI = $freidef->id;
      if ($virtI === 10) { 
        $virtI = 0;
      }

      $this->registerDb('Freidef' . $freidef->id, 'kursauswahl' . $virtI, static::TYPE_STRING);
    }

    $fnAddToBuilder = $this->addToBuilder();
    $fnAddIntArrayToBuilder = static function(array $toAdd, \SelectQueryBuilder $result): \SelectQueryBuilder {
      $var = $result->uniqueVar();

      return $result->and(":table:.nr IN (:$var:)")
        ->paramIntArray($var, $toAdd);
    };

    $katFn = static function(\DBOperation $dbop, $value): \SelectQueryBuilder {
      if (!is_array($value)) {
        $value = array($value);
      }

      $dbwCat = new \DBWorkCategories($dbop->dbnr);
      $ids = array();
      foreach ($value as $katId) {
        array_push($ids, ...$dbwCat->getChildCategoriesIdsRecursive($katId));
      }

      $katKursBuilder = new \SelectQueryBuilder($dbop);

      return $katKursBuilder
          ->select('kk.knr')
          ->from('kurse_kategorien', 'kk')
          ->and('kk.kategorie_id IN (:katIds:)')
          ->paramIntArray('katIds', $ids);
    };

    $this->registerForeignDb(
      'Kategorie',
      $fnAddToBuilder,
      $katFn,
      $katFn
    );
  }

  protected function addToBuilder(): callable
  {
    return static function(\SelectQueryBuilder $toAdd, \SelectQueryBuilder $result): \SelectQueryBuilder {
      $var = $result->uniqueVar();

      return $result->and(":table:.nr IN (:$var:)")
        ->paramSelectQuery($var, $toAdd);
    };
  }

  public function equalsId($id): IFilter
  {
    $this->applyFilterCurrent(
      static::FILTER_OPTION_ID,
      SQLFilterUtils::filterEqualsString(':table:', 'nr', $id)
    );

    return $this;
  }

  public function getTablePraeffix(): string
  {
    return 'k';
  }

  
  public function searchImportant(string $stichwort): IFilter
  {
    $this->any(function() use ($stichwort) {
      $this->all(function() use ($stichwort) {
        (new \Kufer\Web\Classes\Stream\IterableStream(\StringOperations::explodeGenerator(' ', $stichwort)))
          ->map('\\trim')
          ->filter(\FunctionOperations::not(\FunctionOperations::args(\FunctionOperations::equalsStrict(), '')))
          ->limit(6) 
          ->each(function($word) {
            $this->any(function() use ($word) {
              $matches = array();
              if (\preg_match('&^"(.*)"$&', $word, $matches) === 1) {
                
                $wordFromMatch = $matches[1];
                $this->equalsKursnummer($wordFromMatch);
                $this->equalsKurzbezeichnung($wordFromMatch);
                $this->equalsProgrammhefttitel($wordFromMatch);
              } else {
                $this->containsKursnummer($word);
                $this->containsKurzbezeichnung($word);
                $this->containsProgrammhefttitel($word);
              }
            });
          });
      });

      
      $regex = '&[ \\t\\n\\r]+&';
      $preparedStichwort = $stichwort;
      $hasMultiple = false;
      while (\preg_match($regex, $preparedStichwort) === 1) {
        $preparedStichwort = \preg_replace($regex, '%', $preparedStichwort);
        $hasMultiple = true;
      }

      $preparedStichwort = \StringOperations::str_fast_parallel_replace($preparedStichwort, array('"' => '', '\\' => ''));

      if ($hasMultiple) {
        $this->containsKurzbezeichnung($preparedStichwort);
        $this->containsProgrammhefttitel($preparedStichwort);
      }
    });

    return $this;
  }

  public function equalsAussenstelleId(int $aussenstId): IFilter
  {
    $dbwAussenstelle = new \DBWorkHLAussenstellen();
    $aussenstelle = $dbwAussenstelle->getAussenstelle($aussenstId);
    
    if ($aussenstelle->bezeichner === '') {
      if ($this->isInverse()) { 
        return $this;
      }

      $this->applyFilterCurrent('Aussenstelle', SQLFilterUtils::filterFalse());

      return $this;
    }

    $this->equalsAussenstelle($aussenstelle->bezeichner ?? '');

    return $this;
  }

  public function equalsOrtId(int $ortId): IFilter
  {
    $dbwKursorte = new \DBWorkKursorte();
    $ort = $dbwKursorte->getKursort($ortId);

    $this->equalsOrt($ort->mktext); 

    return $this;
  }

  public function applyDozentenFilter(\Kufer\Web\Classes\Dbfilter\DozentenSQLFilter $filter): IFilter
  {
    $filter->makeImmutable();

    $dozKurseFilter = DozentenKurseSQLFilter::create();
    $dozKurseFilter->applyDozentenFilter($filter);
    $this->applyDozentenKurseFilter($dozKurseFilter);

    return $this;
  }

  public function applyDozentenKurseFilter(\Kufer\Web\Classes\Dbfilter\DozentenKurseSQLFilter $filter): IFilter
  {
    $filter->makeImmutable();

    if ($filter->isEmpty()) {
      return $this;
    }

    $this->applyFilterCurrent(static::FILTER_OPTION_DOZENTEN, function(\SelectQueryBuilder $builder) use ($filter): \SelectQueryBuilder {
      $dbwDozKurs = \DBWorkDozKurs::getDefaultInstance($builder->getDBOperation()->dbnr);
      $dozKursBuilder = $dbwDozKurs->findByFilterQuery($filter)
        ->select('kurs');

      return \call_user_func($this->addToBuilder(), $dozKursBuilder, $builder);
    });

    return $this;
  }

  public function applyHauptdozentenFilter(\Kufer\Web\Classes\Dbfilter\DozentenSQLFilter $filter): IFilter
  {
    $filter->makeImmutable();

    if ($filter->isEmpty()) { 
      return $this;
    }

    $dozKurseFilter = DozentenKurseSQLFilter::create();
    $dozKurseFilter->applyDozentenFilter($filter);
    $dozKurseFilter->isHauptdozent();
    $this->applyDozentenKurseFilter($dozKurseFilter);

    return $this;
  }

  public function applyKategorienFilter(KategorienSQLFilter $filter): IFilter
  {
    $filter->makeImmutable();

    if ($filter->isEmpty()) {
      return $this;
    }

    $this->applyFilterCurrent(static::FILTER_OPTION_KATEGORIEN, function(\SelectQueryBuilder $builder) use ($filter): \SelectQueryBuilder {
      $dbwCat = new \DBWorkCategories($builder->getDBOperation()->dbnr);
      $katBuilder = $dbwCat->findByFilterQuery($filter)
        ->select('id');

      $katKursBuilder = new \SelectQueryBuilder($builder->getDBOperation());
      $katKursBuilder = $katKursBuilder
        ->select('kk.knr')
        ->from('kurse_kategorien', 'kk')
        ->and('kk.kategorie_id IN (:katIds:)')
        ->paramSelectQuery('katIds', $katBuilder);

      
      
      

      return \call_user_func($this->addToBuilder(), $katKursBuilder, $builder);
    });

    return $this;
  }

  public function applyBelegungenFilter(BelegungenSQLFilter $filter): IFilter
  {
    $filter->makeImmutable();

    if ($filter->isEmpty()) {
      return $this;
    }

    $this->applyFilterCurrent(static::FILTER_OPTION_BELEGUNGEN, function(\SelectQueryBuilder $builder) use ($filter): \SelectQueryBuilder {
      $dbwBelegungen = new \DBWorkBelegung($builder->getDBOperation()->dbnr);
      $belegBuilder = $dbwBelegungen->findByFilterQuery($filter)
        ->select('b.knr');

      return \call_user_func($this->addToBuilder(), $belegBuilder, $builder);
    });

    return $this;
  }

  public function applyTeilnehmerFilter(TeilnehmerSQLFilter $filter): IFilter
  {
    $filter->makeImmutable();

    if ($filter->isEmpty()) {
      return $this;
    }

    $belegFilter = \Kufer\Web\Classes\Dbfilter\BelegungenSQLFilter::create();
    $belegFilter->applyTeilnehmerFilter($filter);
    $this->applyBelegungenFilter($belegFilter);

    return $this;
  }

  public function applyKurseFaecherFilter(KurseFaecherSQLFilter $filter): IFilter
  {
    $filter->makeImmutable();

    if ($filter->isEmpty()) {
      return $this;
    }

    $this->applyFilterCurrent(static::FILTER_OPTION_KURSE_FAECHER, function(\SelectQueryBuilder $builder) use ($filter): \SelectQueryBuilder {
      $builderKurseFaecher = $filter->createBuilder($builder->getDBOperation())
        ->select('knr')
        ->from('kurs_fach');

      return \call_user_func($this->addToBuilder(), $builderKurseFaecher, $builder);
    });

    return $this;
  }

  public function applyKursgruppenHauptkurseFilter(KursgruppenSQLFilter $filter, bool $ignoreEmpty = true): KurseSQLFilter
  {
    $filter->makeImmutable();

    if ($ignoreEmpty && $filter->isEmpty()) {
      return $this;
    }

    $this->applyFilterCurrent(static::FILTER_OPTION_KURSGRUPPEN, function(\SelectQueryBuilder $builder) use ($filter): \SelectQueryBuilder {
      $builderKursgruppen = $filter->createBuilder($builder->getDBOperation())
        ->select('hauptkurse')
        ->from('kursgruppen');

      return \call_user_func($this->addToBuilder(), $builderKursgruppen, $builder);
    });

    return $this;
  }
}