--------------------------------------------------------- СОЗДАНИЕ ТАБЛИЦ

-- таблица для хранения перечислений
CREATE TABLE enum (
    id serial PRIMARY KEY,
    name varchar(50),
    description varchar(150)
);

-- таблица для хранения значений перечислений
CREATE TABLE enum_value (
    id serial PRIMARY KEY,
    id_enum integer,
    position integer,
    name varchar(50)
);

-- таблица для хранения параметров
CREATE TABLE parameter (
    id serial PRIMARY KEY,
	name varchar(50),
	id_measure integer,
    id_enum integer,
	type varchar(50),
    position integer
);

-- таблица для хранения параметров для продуктов
CREATE TABLE product_parameter (
    id serial PRIMARY KEY,
	value double precision,
	info varchar(150),
    id_parameter integer,
	id_product integer
);

-- таблица для хранения параметров для категорий продуктов
CREATE TABLE product_class_parameter (
    id serial PRIMARY KEY,
	min_value double precision,
	max_value double precision,
    id_parameter integer,
	id_product_class integer
);

-- таблица для хранения единиц измерения
create table measure (
    id serial PRIMARY KEY,
    name varchar (50),
    short_name varchar (10)
);

-- таблица для хранения классов продукта
create table product_class (
    id serial PRIMARY KEY,
    name varchar (50),
    description varchar(150),
    measure_id integer
);

-- таблица для хранения экземпляров продуктов
create table product (
    id serial PRIMARY KEY,
    product_class_id integer,
    name varchar (50)
);

--------------------------------------------------------- ЗАПОЛНЕНИЕ ТАБЛИЦ

-- заполнение таблицы перечислений
INSERT INTO enum
	(name, description)
VALUES
	('Производитель', 'Компания или организация, которая занимается производством и изготовлением товаров или услуг.');
SELECT * FROM enum;

-- заполнение таблицы значений перечислений
INSERT INTO enum_value
	(id_enum, position, name)
VALUES
	('1', '1', 'Bosch'),
	('1', '2', 'Samsung'),
	('1', '3', 'LG'),
	('1', '4', 'Азербайджанские');
SELECT * FROM enum_value;

-- заполнение таблицы мер
INSERT INTO measure
	(name, short_name)
VALUES
	('Дней', 'Дн.'),
	('Градусы', 'Гр.'),
	('Килограммы', 'Кг.'),
	('Литры', 'Лит.'),
	('Штук', 'Шт.');
SELECT * FROM measure;

-- заполнение таблицы категорий продуктов
INSERT INTO product_class
	(name, description, measure_id)
VALUES
	('Овощи', 'Сезонные овощи, замороженные продукты, зелень.', 2),
	('Фрукты', 'Сезонные фрукты и орехи, замороженные продукты, экзотические фрукты.', 2),
	('Бытовая техники', 'Холодильники, стиральные и сушильные машины, пылесосы, кухонные плиты, духовки.', 5),
	('Напитки', 'Свежие и пакетированные соки, газировки, вода и морсы.', 4);
SELECT * FROM product_class;


-- заполнение таблицы продуктов
INSERT INTO product
	(name, product_class_id)
VALUES
	('Помидоры', 1),
	('Огурцы', 1),
	('Бананы', 2),
	('Яблоки', 2),
	('Утюги', 3),
	('Пылесос', 3),
	('Сок яблочный', 4),
	('Кока-кола', 4);
SELECT * FROM product;

-- заполнение таблицы параметров
INSERT INTO parameter
	(name, id_measure, id_enum, type, position)
VALUES
	('Срок годности', 1, null, 'Числовой', 1),
	('Производитель', null, 1, 'Список', 2),
	('Температура хранения', 2, null, 'Числовой', 3);
SELECT * FROM parameter;

-- заполнение таблицы параметров для продуктов
INSERT INTO product_parameter
	(value, id_parameter, id_product)
VALUES
	(30, 1, 1),
	(720, 1, 5),
	(2, 2, 6),
	(4, 2, 1);
SELECT * FROM product_parameter;

-- заполнение таблицы параметров для категорий продуктов
INSERT INTO product_class_parameter
	(min_value, max_value, id_parameter, id_product_class)
VALUES
	(10, 90, 1, 1),
	(-999, 999, 7, 7);
SELECT * FROM product_class_parameter;


--------------------------------------------------------- СОЗДАНИЕ ФУНКЦИЙ

-- Добавить числовой параметр
create or replace function add_val_parameter(param_id integer, id_product integer, value double precision, description varchar(150))
returns void
language plpgsql
as $$
declare
    type integer default null;
    value_param_id integer default null;
    id_class integer default null;
    min_param_value integer default null;
    max_param_value integer default null;
begin
    select type from parameter where id = param_id and type = 'Числовой';
    select product_class_id into id_class from product where id = id_product;

    select max_value, min_value into max_param_value, min_param_value from product_class_parameter
    where id_parameter = param_id and id_product_class = id_class;

    if type is not null then
        if min_param_value is not null then
            if min_param_value <= value AND max_param_value >= value THEN
                insert into product_parameter (value, info, id_parameter, id_product)
                values (value, description, id_parameter, id_product);
            else
                raise exception 'значение добавляемого параметра не входит в диапазон допустимых параметров';
            end if;
        else
            raise exception 'требуемый параметр не существует';
        end if;
    else
        raise exception 'можно добавить только числовой параметр';
    end if;
end;
$$;

-- Добавить параметр типа "Перечисление"
create or replace function add_enum_val_parameter(parameter_id integer, id_product integer, value_id integer, description varchar(150))
returns void
language plpgsql
as $$
declare
    type integer default null;
    enum_id integer default null;
    parameter_enum_id integer default null;
    id_class integer default null;
begin
    select type from parameter where id = parameter_id and type = 'Перечисление';
    select id into enum_id from enum_value where id = value_id;
    select id_enum into parameter_enum_id from parameter where id = parameter_id;
    select product_class_id into id_class from product where id = id_product;

    if type is not null then
        if enum_id = parameter_enum_id and enum_id is not null then
            if exists(select * from product_class_parameter where id_parameter = parameter_id
                                                              and id_product_class = id_class) then
                insert into product_parameter (id_parameter, id_product, value, info)
                values(parameter_id, ""."".product_parameter.id_product, value_id, description);
            else
                raise exception 'требуемый параметр не существует';
            end if;
        else
            raise exception 'значение перечисления не принадлежит типу перечисления';
        end if;
    else
        raise exception 'можно добавить только параметр типа "Перечисление"';
    end if;
end;
$$;



-- 3
-- Вернуть таблицу параметров для изделия
CREATE OR REPLACE FUNCTION product_params(prod_id INTEGER)
	RETURNS TABLE
	(
		product VARCHAR(50),
		product_class VARCHAR(50),
		parameter VARCHAR(50),
		enum_value TEXT
	)
	LANGUAGE plpgsql
AS
$$
BEGIN
	RETURN QUERY
	(
		SELECT
			product.name as product,
			product_class.name as product_class,
			parameter.name as parameter,
			CASE
				WHEN parameter.type = 'Список'
				THEN concat(enum_value.name)
				ELSE concat(product_parameter.value, ' ', measure.short_name)
			END AS value
		FROM product
			JOIN product_class ON product_class.id = product.product_class_id
			JOIN product_parameter ON product.id = product_parameter.id_product
			JOIN parameter ON product_parameter.id_parameter = parameter.id
			LEFT JOIN enum_value ON parameter.id_enum = enum_value.id_enum AND product_parameter.value = enum_value.id
			LEFT JOIN measure ON parameter.id_measure = measure.id
		WHERE
			product.id = prod_id
	);
END;
$$;

SELECT * FROM product_parameter;
SELECT * FROM product_params(6);




-- 4
-- Скопировать параметры от класса для класса
-- From_Class_Id - идентификатор класса, от которого необходимо скопировать параметры
CREATE FUNCTION copy_class_params(id_from INTEGER, id_to INTEGER)
RETURNS VOID AS $$
BEGIN
    UPDATE
		product_class_parameter
    SET
		min_value = (
			SELECT min_value
			FROM product_class_parameter
			WHERE id = id_from
		),
		max_value = (
			SELECT max_value
			FROM product_class_parameter
			WHERE id = id_from
		)
    WHERE product_class_parameter.id = id_to;
END; $$
LANGUAGE plpgsql;

SELECT * FROM product_class_parameter;
SELECT copy_class_params(1, 2);


--5
-- Просмотреть содержимое агрегата
--Arg_Id - идентификатор агрегата
-- Возвращает аблицу со столбцами Позиция, Краткое Имя, Имя
CREATE OR REPLACE FUNCTION aregate_content(pos INTEGER)
	RETURNS TABLE
	(
		p_position int,
		p_name varchar(100),
		p_short_name varchar(45)
	)
	LANGUAGE plpgsql
AS
$$
BEGIN
	RETURN QUERY
	(
		SELECT
			parameter.position, parameter.name, parameter.type
		FROM
			parameter
		WHERE
			parameter.position = pos
	);
END;
$$

SELECT * FROM parameter;
SELECT * FROM aregate_content(2);