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

-- таблица для хранения перечислений
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;


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

-- 3
-- Вернуть таблицу параметров для изделия
-- Prod_Id - идентификатор изделия
-- Возвращает таблицу со столбцами Имя Изделия, Группа Изделия, Параметр, Значение
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;
$$;

--DROP FUNCTION product_params(integer)
SELECT * FROM product;
SELECT * FROM product_params(1);




-- 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;

DROP FUNCTION copy_min_value(INTEGER, INTEGER);

SELECT * FROM product_class_parameter;
SELECT copy_min_value(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(1);