CREATE TABLE objects (object_id NUMBER PRIMARY KEY,
parent_id NUMBER, object_type_id NUMBER NOT NULL, name VARCHAR2(20) UNIQUE NOT NULL,
description VARCHAR2(20), order_number NUMBER);

CREATE TABLE object_types (object_type_id NUMBER PRIMARY KEY,
parent_id NUMBER, name VARCHAR2(20) UNIQUE NOT NULL,
description VARCHAR2(20), properties VARCHAR2(255));

CREATE TABLE params(
attr_id NUMBER NOT NULL, object_id NUMBER NOT NULL,
VALUE VARCHAR2(20), date_value DATE, show_order NUMBER);

CREATE TABLE attr_types(attr_type_id NUMBER PRIMARY KEY,
name VARCHAR2(20) UNIQUE NOT NULL, properties VARCHAR2(255));

CREATE TABLE attr_groups(attr_group_id NUMBER PRIMARY KEY,
name VARCHAR2(20) UNIQUE NOT NULL, properties VARCHAR2(255));

CREATE TABLE attributes(attr_id NUMBER PRIMARY KEY,
attr_type_id NUMBER NOT NULL, attr_group_id NUMBER NOT NULL,
name VARCHAR2(20) UNIQUE NOT NULL, description VARCHAR2(20),
ismultiple NUMBER(1, 0) NOT NULL, properties VARCHAR2(255));

CREATE TABLE references(
attr_id NUMBER, object_id NUMBER, reference NUMBER, show_order NUMBER,
CONSTRAINT ref_unique UNIQUE (attr_id, object_id, reference));

CREATE TABLE attr_binds(object_type_id NUMBER NOT NULL, attr_id NUMBER NOT NULL,
options VARCHAR2(255), isrequired NUMBER(1, 0) NOT NULL, defaul_value VARCHAR2(255));



ALTER TABLE objects ADD FOREIGN KEY (parent_id) REFERENCES objects (object_id);
ALTER TABLE objects ADD FOREIGN KEY (object_type_id) REFERENCES object_types (object_type_id);

ALTER TABLE object_types ADD FOREIGN KEY (parent_id) REFERENCES object_types (object_type_id);

ALTER TABLE params add UNIQUE (attr_id, object_id, VALUE);
ALTER TABLE params ADD FOREIGN KEY (object_id) REFERENCES objects (object_id)
ON DELETE cascade;
ALTER TABLE params ADD FOREIGN KEY (attr_id) REFERENCES attributes (attr_id)
ON DELETE cascade;

ALTER TABLE attributes add UNIQUE (attr_id, name);
ALTER TABLE attributes ADD FOREIGN KEY (attr_type_id) REFERENCES attr_types (attr_type_id);
ALTER TABLE attributes ADD FOREIGN KEY (attr_group_id) REFERENCES attr_groups (attr_group_id);

ALTER TABLE references ADD FOREIGN KEY (attr_id) REFERENCES attributes (attr_id);
ALTER TABLE references ADD FOREIGN KEY (object_id) REFERENCES objects (object_id);
ALTER TABLE references ADD FOREIGN KEY (reference) REFERENCES objects (object_id);

ALTER TABLE attr_binds ADD FOREIGN KEY (object_type_id) REFERENCES object_types (object_type_id);
ALTER TABLE attr_binds ADD FOREIGN KEY (attr_id) REFERENCES attributes (attr_id);
ALTER TABLE attr_binds add constraint pk_attr_binds primary key (object_type_id, attr_id);



INSERT INTO object_types VALUES(1, NULL, 'factory', 'any factory', '...');
INSERT INTO object_types VALUES(2, 1, 'bicycle', 'vehicle', '...');
INSERT INTO object_types VALUES(3, 1, 'rim', 'part of the wheel', '...');

INSERT INTO objects VALUES(1, NULL, 1, 'mountainBikeFactory', 'bicycle factory', 1);
INSERT INTO objects VALUES(2, 1, 2, 'myVelo', 'my bicycle', 2);
INSERT INTO objects VALUES(3, 1, 2, 'friendVelo', 'my friends bicycle', 3);
INSERT INTO objects VALUES(4, 2, 3, 'reinforcedRim', 'wheel rim', 4);
INSERT INTO objects VALUES(5, 3, 3, 'singleRim', 'wheel rim', 5);

INSERT INTO attr_groups VALUES(1, 'technical', '...' );
INSERT INTO attr_groups VALUES(2, 'non-technical', '...');

INSERT INTO attr_types VALUES(1, 'name', '...');
INSERT INTO attr_types VALUES(2, 'cost', '...');
INSERT INTO attr_types VALUES(3, 'strength', '...');
INSERT INTO attr_types VALUES(4, 'weight', '...');
INSERT INTO attr_types VALUES(5, 'colour', '...');
INSERT INTO attr_types VALUES(6, 'beloning', '...');

INSERT INTO attributes VALUES(1, 1, 2, 'companyName', 'company name', 1, '...');
INSERT INTO attributes VALUES(2, 4, 1, 'constWeight', 'construction weight', 0, '...');
INSERT INTO attributes VALUES(3, 2, 2, 'price', '...', 0, '...');
INSERT INTO attributes VALUES(4, 3, 1, 'wheelStrength','...', 0, '...');
INSERT INTO attributes VALUES(5, 5, 2, 'coloring','...', 1, '...');
INSERT INTO attributes VALUES(6, 6, 2, 'owner','...', 0, '...');

INSERT INTO params VALUES(1, 2, 'GT', NULL, 1);
INSERT INTO params VALUES(1, 3, 'cannondale', NULL, 2);
INSERT INTO params VALUES(2, 2, '12', NULL, 3);
INSERT INTO params VALUES(2, 3, '11', NULL, 4);
INSERT INTO params VALUES(3, 4, '100', NULL, 5);
INSERT INTO params VALUES(3, 5, '50', NULL, 6);
INSERT INTO params VALUES(4, 4, 'strong', NULL, 7);
INSERT INTO params VALUES(4, 5, 'low', NULL, 8);
INSERT INTO params VALUES(5, 1, 'matt', NULL, 9);
INSERT INTO params VALUES(6, 2, 'I', NULL, 10);

INSERT INTO attr_binds VALUES(2, 1, 'VeloFirm', 1, 'GT');
INSERT INTO attr_binds VALUES(2, 2, 'VeloWeight', 0, NULL);
INSERT INTO attr_binds VALUES(2, 3, 'VeloPrice', 1, '500');
INSERT INTO attr_binds VALUES(2, 5, 'VeloColoring', 1, 'blue');
INSERT INTO attr_binds VALUES(3, 4, 'rimStrength', 0, NULL);

INSERT INTO references VALUES(6, 1, 2, 1);
INSERT INTO references VALUES(6, 1, 3, 2);


/* 1. Получение информации обо всех атрибутах (учитывая только атрибутную группу и атрибутные типы)
(attr_id, attr_name, attr_group_id, attr_group_name, attr_type_id, attr_type_name) */
SELECT attr.attr_id, attr.name, attr.attr_group_id, gr.name, tp.attr_type_id, tp.name
FROM attributes attr
join attr_groups gr  ON attr.attr_group_id = gr.attr_group_id
join attr_types tp ON attr.attr_type_id = tp.attr_type_id;

/* 2. Получение всех атрибутов для заданного объектного типа,
без учета наследования(attr_id, attr_name )*/
SELECT attr.attr_id, attr.name FROM attributes attr
join attr_binds bind ON attr.attr_id = bind.attr_id
join object_types TYPE ON TYPE.object_type_id = bind.object_type_id
WHERE TYPE.object_type_id = : id;

/* 3. Получение иерархии ОТ(объектных типов)  для заданного объектного типа
(нужно получить иерархию наследования) (ot_id, ot_name, level)*/
SELECT object_type_id, name, LEVEL
FROM object_types
START WITH object_type_id = (SELECT object_type_id FROM object_types
WHERE object_type_id =: obj_id)
CONNECT BY PRIOR object_type_id = parent_id;

/* 4. Получение вложенности объектов для заданного объекта
(нужно получить иерархию вложенности)(obj_id, obj_name, level)*/
SELECT object_id, name,  LEVEL
FROM objects
START WITH object_id = (SELECT object_id FROM objects
WHERE object_id =: obj_id)
CONNECT BY PRIOR object_id = parent_id;

/*5. Получение объектов заданного объектного типа
(учитывая только наследование ОТ)(ot_id, ot_name, obj_id, obj_name)*/
SELECT obj.object_type_id, ot.name, obj.object_id, obj.name
FROM objects obj
join object_types ot ON obj.object_type_id = ot.object_type_id
WHERE obj.object_type_id IN
(SELECT object_type_id
FROM object_types
START WITH object_type_id = (SELECT object_type_id FROM object_types
WHERE object_type_id =: obj_id)
CONNECT BY PRIOR object_type_id = parent_id);

/* 6. Получение значений всех атрибутов(всех возможных типов)
для заданного объекта(без учета наследования ОТ)(attr_id, attr_name, value)*/
SELECT attr.attr_id, attr.name, par.VALUE VALUE
FROM attributes attr
join params par ON par.attr_id = attr.attr_id
WHERE par.object_id = :id
UNION
SELECT attr.attr_id, attr.name, obj.name VALUE
FROM attributes attr
join references REF ON attr.attr_id = REF.attr_id
join objects obj ON obj.object_id = REF.reference
WHERE REF.object_id = :id;


/* 7. Получение ссылок на заданный объект
(все объекты, которые ссылаются на текущий)(ref_id, ref_name)*/
SELECT ob.object_id, ob.name
FROM objects ob
WHERE object_id =
(SELECT object_id FROM references REF
WHERE REF.reference = :id);


/* 8. Получение значений всех атрибутов
(всех возможных типов, без повторяющихся атрибутов) для заданного объекта
(с учетом наследования ОТ) Вывести в виде см. п.6*/
DELETE FROM
(SELECT attr.attr_id, attr.name, par.VALUE VALUE
FROM attributes attr
join params par ON par.attr_id = attr.attr_id
WHERE par.object_id IN
(SELECT object_id
FROM objects
START WITH object_id = (SELECT object_id FROM objects
WHERE object_id = :id)
CONNECT BY PRIOR parent_id = object_id)
UNION
SELECT attr.attr_id, attr.name, obj.name VALUE
FROM attributes attr
join references REF ON attr.attr_id = REF.attr_id
join objects obj ON obj.object_id = REF.reference
WHERE REF.object_id = :id) res
WHERE res.id IN (
    SELECT res.id
    FROM (SELECT res.id,
            ROW_NUMBER() over (PARTITION BY res.attr_id ORDER BY NULL) rw
                FROM res)
            WHERE rw > 1);



DELETE params;
DELETE references;
DELETE objects;
DELETE attr_binds;
DELETE attributes;
DELETE attr_groups;
DELETE attr_types;
DELETE object_types;