oracle

DROP TABLE category CASCADE CONSTRAINTS;
DROP TABLE categorytree CASCADE CONSTRAINTS;

create table category(
id NUMBER GENERATED ALWAYS AS IDENTITY,
name VARCHAR2(50),
PRIMARY KEY(ID)
);

create table categorytree(
parent_id number not null references category(id),
child_id number not null references category(id),
depth number
);



CREATE OR REPLACE PROCEDURE addcategory ( 
	name IN VARCHAR2, 
	parent in number default null, 
	parentbyname in VARCHAR2 default null) 
IS
cat_id number;
temp_parent_id number := parent;
BEGIN

	if parentbyname is not null then
		 select id into temp_parent_id
			from ( select id
					 from category 
					 where name = parentbyname 
					 order by id desc )
		   where rownum <2 ;
		   dbms_output.Put_line('parent id' || temp_parent_id );
	end if;

	Insert INTO category (name) VALUES (name) Returning id into cat_id;
	dbms_output.Put_line('New category ' || name ||' created with ID: ' || cat_id);
	INSERT INTO categorytree(parent_id, child_id, depth) VALUES(cat_id,cat_id,0);

	if temp_parent_id is not null then
		INSERT INTO categorytree(parent_id, child_id, depth) 
			SELECT parent_id, cat_id, depth+1 from categorytree WHERE child_id = temp_parent_id;
 
                
	end if;
	
END;
/

begin
	addcategory('Home',null,null);	
	addcategory('Car',null,null);	
	addcategory('Food',null,'Home');	
	addcategory('Drinks',null,'Food');	
	addcategory('Coca-Cola',null,'Drinks');	
	addcategory('Meat',null,'Food');	
	addcategory('Ham',null,'Meat');		
	addcategory('Pork',null,'Meat');
	addcategory('Mince',null,'Pork');	
	addcategory('Sauces, oils, vinegar',null,'Food');	
	addcategory('Majonese',null,'Sauces, oils, vinegar');	
	addcategory('Fast Food',null,'Food');	
	addcategory('Pizza',null,'Fast Food');	
	addcategory('Sweets',null,'Food');	
	addcategory('Chocolates',null,'Sweets');	
	addcategory('Maltesers',null,'Chocolates');	
	addcategory('Repair',null,'Car');	
	addcategory('Tyres',null,'Repair');	
	addcategory('Bills',null,'Home');	
	addcategory('Media',null,'Bills');	
	addcategory('Internet',null,'Media');	
	addcategory('Bakery',null,'Food');	
	addcategory('Rolls',null,'Bakery');	
	


end;
/

create or replace procedure del_cat_by_name (
	del_name in varchar2, 
	cat_del_id in number default null)
is
--del_name VARCHAR2 (10) := 'Food';
del_id number := cat_del_id;
temp_del_parent_id number;

cursor c1 is select con.child_id, con.parent_id from categorytree con
where con.parent_id = del_id ORDER BY con.child_id DESC ;

c2 SYS_REFCURSOR;

begin

if del_id is null then 
select id into del_id
			from ( select id
					 from category 
					 where name = del_name 
					 order by id desc )
		   where rownum <2 ;
end if;

	FOR child IN c1
	loop
	dbms_output.Put_line('child:  ' || child.child_id );
		open c2
		for 'select parent_id from categorytree where child_id=' || child.child_id ;
		loop
		FETCH c2 INTO temp_del_parent_id ;
		EXIT WHEN c2%NOTFOUND;
		dbms_output.Put_line('parent:  ' || temp_del_parent_id || ' and child:  ' || child.child_id);
		Delete from categorytree where parent_id = temp_del_parent_id and child_id= child.child_id;
		dbms_output.Put_line('parent:  ' || temp_del_parent_id || ' and child:  ' || child.child_id || '  -connection deleted!');
		
		end loop;
		
		close c2;
	
	dbms_output.Put_line('child:  ' || child.child_id || '- category deleted!' );
        Delete from category where id = child.child_id;
	end loop;
Delete from category where id = del_id ;
dbms_output.Put_line('Category:  ' || del_id  || '- SUCCESFULL DELETED!' );
end;
/



create or replace procedure update_cat (del_name in varchar2, cat_del_id in number default null, new_parent_id number default null)
is
--del_name VARCHAR2 (10) := 'Food';
del_id number := cat_del_id;
cat_id number := del_id;
temp_del_parent_id number;
previous number;

cursor c1 is select con.child_id, con.parent_id from categorytree con
where con.parent_id = del_id ORDER BY con.child_id asc ;

c2 SYS_REFCURSOR;

begin


if del_id is null then 
select id into del_id
			from ( select id
					 from category 
					 where name = del_name )
		   where rownum <2;
end if;

	FOR child IN c1
	loop
	dbms_output.Put_line('kategoria id w kursorze:  ' || child.child_id );
		open c2
		for 'select parent_id from categorytree where child_id=' || child.child_id || 'order by child_id asc';
		loop
		FETCH c2 INTO temp_del_parent_id ;
		EXIT WHEN c2%NOTFOUND;
		
		Delete from categorytree where parent_id = temp_del_parent_id and child_id= child.child_id;
		dbms_output.Put_line('parent:  ' || temp_del_parent_id || ' and child:  ' || child.child_id || '  -connection deleted!');
		end loop;
		close c2;
		
		INSERT INTO categorytree(parent_id, child_id, depth) VALUES(child.child_id,child.child_id,0);
	dbms_output.Put_line('Kategoria id :  ' || child.child_id || ' sama jako rodzic.  ' );   
	
	--if new_parent_id is not null then
		INSERT INTO categorytree(parent_id, child_id, depth) 
			SELECT parent_id, child.child_id, depth+1 from categorytree WHERE child_id = previous;
             dbms_output.Put_line('nowy zestaw powiazań dla id :  ' || child.child_id || ' Done.  ' );   
	--end if;
		child.child_id := previous;

	
	end loop;
	
	
	

end;
/

begin 
update_cat(null, 10, 1);  
end;
/


create or replace function path_cat(id number) return varchar2
is
choosen_category number := id;
temprec category%rowtype;
reply Varchar2 (100);
BEGIN
reply := '/';
FOR temprec IN (SELECT k.name FROM category k 
				join categorytree p on k.id = p.parent_id 
				WHERE p.child_id = choosen_category  
				ORDER BY p.depth desc )
LOOP
reply := reply || temprec.name || '/';
END LOOP;
dbms_output.Put_line(reply);
RETURN reply;
END;

/

DROP TABLE unit CASCADE CONSTRAINTS;
DROP TABLE shop CASCADE CONSTRAINTS;
DROP TABLE currency CASCADE CONSTRAINTS;
DROP TABLE product CASCADE CONSTRAINTS;
DROP TABLE price CASCADE CONSTRAINTS;
DROP TABLE promotion CASCADE CONSTRAINTS;

create table unit(
id number generated always as identity,
name varchar2(20),
name_short varchar2(10),
unit_parent_id number references unit(id),
primary key (id)
);

create table shop(
id number generated always as identity,
name varchar2(20),
primary key (id)
);

create table currency(
id number generated always as identity,
name varchar2(3),
primary key(id)
);


create table product (
id number generated always as identity,
barcode varchar2 (20),
name varchar2(50),
quality number,
out_of_stock date,
category_id number references category(id),
unit_id number references unit(id),
primary key(id)
);

create table price(
id number generated always as identity,
shop_id number references shop(id),
product_id number references product(id),
price_of_product number,
amout_of_product number,
currency_id number references currency(id),
create_date date,
primary key (id)
);


create table promotion(
id number generated always as identity,
start_date date,
expire_date date,
price_of_product number,
amout_of_product number,
price_id number references price(id),
primary key(id)
);