<?php

namespace WPML\StringTranslation\Infrastructure\StringPackage\Query\ManyLanguagesStrategy;

use WPML\StringTranslation\Application\Setting\Repository\SettingsRepositoryInterface as SettingsRepository;
use WPML\StringTranslation\Application\StringPackage\Query\Criteria\StringPackageCriteria;
use WPML\StringTranslation\Application\StringPackage\Query\FindStringPackagesQueryBuilderInterface;
use WPML\StringTranslation\Infrastructure\StringPackage\Query\QueryBuilderTrait;

class FindStringPackagesQueryBuilder implements FindStringPackagesQueryBuilderInterface {
	use QueryBuilderTrait;
	use TranslationStatusQueryBuilderTrait;

	const SP_COLUMNS = '
        sp.ID,
        sp.name,
        sp.kind_slug,
        sp.title,
        sp.word_count,
        sp.translator_note
    ';

	private $sitepress;

	private $settingsRepository;

	private $wpdb;

	public function __construct(
		$wpdb,
		$sitepress,
		SettingsRepository $settingsRepository
	) {
		$this->wpdb               = $wpdb;
		$this->sitepress          = $sitepress;
		$this->settingsRepository = $settingsRepository;
	}

	public function build( StringPackageCriteria $criteria ): string {
		$targetLanguageCodes = $this->getTargetLanguageCodes( $criteria );
		$sourceLanguage = $this->getSourceLanguageCode( $criteria );
		$fields = $this->getFields();
		$stringPackageId = $criteria->getType();

		$sql = "
      SELECT
          {$fields}
      FROM {$this->wpdb->prefix}icl_string_packages sp
      INNER JOIN {$this->wpdb->prefix}icl_translations source_t
          ON source_t.element_id = sp.ID
          AND source_t.element_type = CONCAT('package_', sp.kind_slug)
          AND source_t.language_code = %s
      LEFT JOIN {$this->wpdb->prefix}icl_translations target_t
          ON target_t.trid = source_t.trid
          AND target_t.language_code IN (" . wpml_prepare_in( $targetLanguageCodes ) . ")
      LEFT JOIN {$this->wpdb->prefix}icl_translation_status target_ts
          ON target_ts.translation_id = target_t.translation_id
      WHERE sp.kind_slug = %s
          {$this->buildPostTitleCondition( $criteria )}
          {$this->buildTranslationStatusConditionWrapper( $criteria, $targetLanguageCodes )}
      GROUP BY sp.ID
      {$this->buildSortingQueryPart( $criteria )}
      {$this->buildPagination( $criteria )}
    ";

		return $this->wpdb->prepare( $sql, $sourceLanguage, $stringPackageId );
	}

	private function getFields(): string {
		$spColumns = self::SP_COLUMNS;

		return "
            {$spColumns}
		";
	}

	private function buildTranslationStatusConditionWrapper(
		StringPackageCriteria $criteria,
		array $targetLanguageCodes
	) : string {
		return 'AND ' . $this->buildTranslationStatusCondition( $criteria, $targetLanguageCodes );
	}
}