<?php

declare(strict_types=1);

namespace RetailPricing;

use yii\db\Query;

final class ProductFilterQuery
{
    /**
     * SQL variant.
     *
     * Expected schema:
     * - product(id, manufacturer_id, name, ...)
     * - product_category(product_id, category_id)
     * - product_field_value(product_id, field_id, value_id)
     * - field(id, code)
     *
     * Multiple values inside one filter are OR: IN (...).
     * Different filter groups are AND: every selected group adds a separate condition.
     */
    public static function sql(): string
    {
        return <<<'SQL'
SELECT DISTINCT p.*
FROM product p
WHERE (:manufacturer_ids_empty = 1 OR p.manufacturer_id IN (:manufacturer_ids))
  AND (:category_ids_empty = 1 OR EXISTS (
      SELECT 1
      FROM product_category pc
      WHERE pc.product_id = p.id
        AND pc.category_id IN (:category_ids)
  ))
  AND (:scale_ids_empty = 1 OR EXISTS (
      SELECT 1
      FROM product_field_value pfv
      INNER JOIN field f ON f.id = pfv.field_id
      WHERE pfv.product_id = p.id
        AND f.code = 'scale'
        AND pfv.value_id IN (:scale_ids)
  ))
ORDER BY p.id DESC;
SQL;
    }

    /**
     * Yii Query Builder variant. Empty arrays mean "filter is not selected".
     *
     * @param int[] $manufacturerIds
     * @param int[] $categoryIds
     * @param int[] $scaleIds
     */
    public static function buildYiiQuery(array $manufacturerIds, array $categoryIds, array $scaleIds): Query
    {
        $query = (new Query())
            ->select('p.*')
            ->distinct()
            ->from(['p' => 'product'])
            ->orderBy(['p.id' => SORT_DESC]);

        if ($manufacturerIds !== []) {
            $query->andWhere(['p.manufacturer_id' => $manufacturerIds]);
        }

        if ($categoryIds !== []) {
            $query->andWhere([
                'exists',
                (new Query())
                    ->select('1')
                    ->from(['pc' => 'product_category'])
                    ->where('pc.product_id = p.id')
                    ->andWhere(['pc.category_id' => $categoryIds]),
            ]);
        }

        if ($scaleIds !== []) {
            $query->andWhere([
                'exists',
                (new Query())
                    ->select('1')
                    ->from(['pfv' => 'product_field_value'])
                    ->innerJoin(['f' => 'field'], 'f.id = pfv.field_id')
                    ->where('pfv.product_id = p.id')
                    ->andWhere(['f.code' => 'scale'])
                    ->andWhere(['pfv.value_id' => $scaleIds]),
            ]);
        }

        return $query;
    }
}