<?php

use KupShop\I18nBundle\Translations\ParametersListTranslation;
use Query\QueryBuilder;
use Query\Translation;

#[AllowDynamicProperties]
class ParameterBase implements ArrayAccess
{
    use DatabaseCommunication;

    public $id;
    public $name;
    public $name_frontend;
    public $value_type = 'float';
    protected $list_values;
    public $unit;

    public function __construct($id = -1)
    {
        $this->id = $id;
    }

    public function createFromDB($id)
    {
        $fields = '*';

        $this->id = $id;

        return $this->fetchFields($fields);
    }

    public function createFromArray($data)
    {
        foreach ($data as $key => $value) {
            $this->{$key} = $value;
        }

        if ($this->unit) {
            $this->unit = explode('|', $this->unit);
        }

        return true;
    }

    public function fetchFields($fields)
    {
        $SQL = sqlQuery("SELECT {$fields}
	         FROM ".getTableName('parameters')."
	               WHERE id='{$this->id}'
	               LIMIT 1");

        if (sqlNumRows($SQL) == 1) {
            $data = sqlFetchAssoc($SQL);
            sqlFreeResult($SQL);

            return $this->createFromArray($data);
        } else {
            return false;
        }
    }

    public function fetchListValues($force_fetch = false)
    {
        if (!is_null($this->list_values) && !$force_fetch) {
            return $this->list_values;
        }
        $qb = sqlQueryBuilder()->select('pl.*, COUNT(pp.id_product) AS count')
            ->from('parameters_list', 'pl')
            ->leftJoin('pl', 'parameters_products', 'pp', 'pp.value_list=pl.id')
            ->leftJoin('pl', 'parameters', 'pa', 'pl.id_parameter=pa.id')
            ->where(\Query\Operator::equals(['pl.id_parameter' => $this->id]))

            ->andWhere(Translation::joinTranslatedFields(ParametersListTranslation::class, function (QueryBuilder $qb, $columnName, $translatedField) {
                if ($columnName == 'value') {
                    $field = $translatedField ? "COALESCE({$translatedField},pl.value)" : 'pl.value';
                    $qb->addSelect("CASE pa.value_type
                     WHEN 'char' THEN pp.value_char
                     WHEN 'list' THEN {$field}
                     ELSE pp.value_float
                  END as value");

                    return false;
                }
            }, ['value', 'description']))

            ->groupBy('pl.id')
            ->orderBy('pl.position, pl.id');

        $this->list_values = array_map(function ($val) {
            $val['data'] = json_decode($val['data'] ?? '[]', true);

            return $val;
        }, sqlFetchAll($qb->execute(), 'id'));

        return $this->list_values;
    }

    public function setValue($values, $id = null)
    {
        if (!empty($values['delete'])) {
            return $this->deleteSQL('parameters_products', ['id' => $id]);
        }

        $value = $values['value'];
        unset($values['value']);
        $values["value_{$this->value_type}"] = $value;

        if (isset($values['unit'])) {
            $this->prepareNull($values['unit']);
        }

        if ($id > 0) {
            return $this->updateSQL('parameters_products', $values, ['id' => $id]);
        } else {
            return $this->insertSQL('parameters_products', $values);
        }
    }

    public function getValueTypeField()
    {
        switch ($this->value_type) {
            case 'float':
            case 'int':
                return 'value_float';
            case 'list':
                return 'value_list';
            case 'char':
                return 'value_char';
        }
        throw new Exception("Undefined parameter type: {$this->value_type}");
    }

    /**
     * @param int|null   $id_product    ID of product-belonging parameters
     * @param int[]|null $id_parameters List of parameter IDs to get
     *
     * @return Parameter[]
     */
    public static function get($id_product = null, $id_parameters = null)
    {
        $where = ' 1 ';
        $from = '';

        if ($id_product) {
            $where .= ' AND pp.id_product=:id ';
            $from = 'JOIN parameters_products AS pp ON pp.id_parameter=p.id';
        }

        if ($id_parameters) {
            $where .= ' AND p.id IN (:parameters) ';
        }

        $SQL = sqlQuery("SELECT p.*
            FROM parameters AS p
            {$from}
            WHERE {$where}
            GROUP BY p.id
            ORDER BY p.position",
            ['id' => $id_product, 'parameters' => $id_parameters],
            ['parameters' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]);

        $parameters = [];
        foreach ($SQL as $row) {
            $parameter = new self();
            $parameter->createFromArray($row);
            $parameters[$row['id']] = $parameter;
        }

        return $parameters;
    }

    public static function getValues(&$parameters, $id_product)
    {
        $fields = static::getValuesFields();

        if (findModule('products_parameters', 'configurations')) {
            $fields .= 'configuration_price, ';
        }

        $SQL = sqlQuery("SELECT {$fields}
            CASE p.value_type
                WHEN 'char' THEN value_char
                WHEN 'list' THEN value_list
                ELSE value_float
            END as value
            FROM parameters AS p
            LEFT JOIN parameters_products AS pp ON pp.id_parameter=p.id
            WHERE pp.id_product=:id
            ORDER BY p.position ASC, pp.weight DESC", ['id' => $id_product]);

        foreach ($SQL as $row) {
            $parameters[$row['id_parameter']]->values[$row['id']] = $row;
        }

        return $parameters;
    }

    protected static function getValuesFields()
    {
        return 'p.id as id_parameter, pp.id, pp.unit, pp.weight, ';
    }

    /**
     * Implements ArrayAccess interface.
     */
    public function offsetSet($offset, $value): void
    {
        $this->{$offset} = $value;
    }

    public function offsetExists(mixed $offset): bool
    {
        return isset($this->{$offset});
    }

    public function offsetUnset($offset): void
    {
        unset($this->{$offset});
    }

    public function offsetGet($offset): mixed
    {
        return $this->{$offset} ?? null;
    }
}

if (empty($subclass)) {
    class Parameter extends ParameterBase
    {
    }
}