drop table x;
drop table p;
drop table e;
drop table b;
drop table k;
drop table f;
drop table s;

drop sequence s_seq;
drop sequence b_seq;
drop sequence e_seq;
drop sequence f_seq;
drop sequence k_seq;
drop sequence p_seq;
drop sequence x_seq;

create table s
(
	id integer,
	lang integer not null,
	part integer not null,
	val text not null,
	attr text,
	pron text,
	primary key (id),
	foreign key (lang) references languages (id) on delete cascade,
	foreign key (part) references parts (id) on delete cascade
);

create table f
(
	id integer,
	word integer not null,
	val text not null,
	attr text,
	pron text,
	primary key (id),
	foreign key (word) references s (id) on delete cascade
);

create table k
(
	id integer,
	word integer not null,
	val text not null,
	attr text,
	pron text,
	primary key (id),
	foreign key (word) references s (id) on delete cascade
);

create table e
(
	id integer,
	lang integer not null,
	part integer not null,
	val text not null,
	attr text,
	pron text,
	primary key (id),
	foreign key (lang) references languages (id) on delete cascade,
	foreign key (part) references parts (id) on delete cascade
);

create table b
(
	id integer,
	lang integer not null,
	part integer not null,
	val text not null,
	attr text,
	pron text,
	primary key (id),
	foreign key (lang) references languages (id) on delete cascade,
	foreign key (part) references parts (id) on delete cascade
);

create table p
(
	id integer,
	first integer not null,
	second integer,
	comment text,
	usuality integer not null,
	primary key (id),
	foreign key (first) references s (id) on delete cascade,
	foreign key (second) references s (id) on delete cascade
);

create table x
(
	id integer,
	tran integer not null,
	first text not null,
	second text,
	primary key (id),
	foreign key (tran) references p (id) on delete cascade
);

create sequence s_seq minvalue 0 start 0;
create sequence b_seq minvalue 0 start 0;
create sequence e_seq minvalue 0 start 0;
create sequence f_seq minvalue 0 start 0;
create sequence k_seq minvalue 0 start 0;
create sequence p_seq minvalue 0 start 0;
create sequence x_seq minvalue 0 start 0;

create or replace function screate
       (integer, integer, varchar, varchar, varchar, bool)
returns integer as '
declare
	kount integer;
	newid integer;
begin
	if $6 then
	        select count(id) into kount from s
		where lang = $1 and part = $2 and val = $3 
		and coalesce (attr, ''nil'') = coalesce ($4, ''nil'');
		if kount > 0 then
			return -1;
		end if;
	end if;
	select nextval (''s_seq'') into newid;
	insert into s values (newid, $1, $2, $3, $4, $5);
	return newid;
end;
' language 'plpgsql'; 

create or replace function bcreate
       (integer, integer, varchar, varchar, varchar, bool)
returns integer as '
declare
	kount integer;
	newid integer;
begin
	if $6 then
	        select count(id) into kount from b
		where lang = $1 and part = $2 and val = $3 
		and coalesce (attr, ''nil'') = coalesce ($4, ''nil'');
		if kount > 0 then
			return -1;
		end if;
	end if;
	select nextval (''b_seq'') into newid;
	insert into b values (newid, $1, $2, $3, $4, $5);
	return newid;
end;
' language 'plpgsql'; 

create or replace function ecreate
       (integer, integer, varchar, varchar, varchar, bool)
returns integer as '
declare
	kount integer;
	newid integer;
begin
	if $6 then
	        select count(id) into kount from e
	        where lang = $1 and part = $2 and val = $3 
		and coalesce (attr, ''nil'') = coalesce ($4, ''nil'');
		if kount > 0 then
			return -1;
		end if;
	end if;
	select nextval (''e_seq'') into newid;
	insert into e values (newid, $1, $2, coalesce ($3, ''''), $4, $5);
	return newid;
end;
' language 'plpgsql';

create or replace function fcreate
       (integer, integer, varchar, varchar, varchar, bool)
returns integer as '
declare
	kount integer;
	newid integer;
begin
	if $6 then
	        select count(id) into kount from f
	        where word = $2 and val = $3 
		and coalesce (attr, ''nil'') = coalesce ($4, ''nil'');
		if kount > 0 then
			return -1;
		end if;
	end if;
	select nextval (''f_seq'') into newid;
	insert into f values (newid, $2, $3, $4, $5);
	return newid;
end;
' language 'plpgsql'; 

create or replace function kcreate
       (integer, integer, varchar, varchar, varchar, bool)
returns integer as '
declare
	kount integer;
	newid integer;
begin
	if $6 then
	        select count(id) into kount from k
	        where word = $2 and val = $3 
		and coalesce (attr, ''nil'') = coalesce ($4, ''nil'');
		if kount > 0 then
			return -1;
		end if;
	end if;
	select nextval (''k_seq'') into newid;
	insert into k values (newid, $2, $3, $4, $5);
	return newid;
end;
' language 'plpgsql'; 

create or replace function pcreate(integer, integer, varchar, integer)
returns integer as '
declare
	kount integer;
	newid integer;
begin
	if $2 > 10 then
		select count(id) into kount from p where first = $1 and second = $2;
		if kount > 0 then
			return -1;
		end if;
	end if;
	select nextval (''p_seq'') into newid;
	insert into p values (newid, $1, $2, $3, $4);
	return newid;
end;
' language 'plpgsql'; 

create or replace function xcreate(integer, varchar, varchar)
returns integer as '
declare
	newid integer;
begin
	select nextval (''x_seq'') into newid;
	insert into x values (newid, $1, $2, $3);
	return newid;
end;
' language 'plpgsql'; 

create index p_first on p (first);
create index p_second on p (second);
create index p_fs on p (first, second);
create index p_sf on p (second, first);
create index s_val on s (val);
create index f_word on f (word);
create index f_val on f (val);
create index b_lp on b (lang, part);
create index e_lp on e (lang, part);
create index x_tran on x (tran);
create index k_val on k (val);
create index e_val on e (val);

create table px
(
	pid integer,
	count integer not null,
	primary key (pid),
	foreign key (pid) references p (id) on delete cascade
);

insert into px select p.id, count(x.id) from p left join x on p.id=x.tran group by p.id;

create table py
(
	pid integer,
	dip integer,
	primary key (pid),
	foreign key (pid) references p (id) on delete cascade,
	foreign key (dip) references p (id) on delete set null
);

insert into py select p.id, pp.id from p left join pp on p.first=pp.second and p.second=pp.first;

create or replace function xif()
returns trigger as '
declare
	kount integer;
begin
	select count into kount from px where pid=new.tran;
	update px set count=kount+1 where pid=new.tran;
	return null;
end;
' language 'plpgsql';

create or replace function xuf()
returns trigger as '
declare
	kount integer;
begin
	select count into kount from px where pid=old.tran;
	update px set count=kount-1 where pid=old.tran;
	select count into kount from px where pid=new.tran;
	update px set count=kount+1 where pid=new.tran;
	return null;
end;
' language 'plpgsql'; 

create or replace function xdf()
returns trigger as '
declare
	kount integer;
begin
	select count into kount from px where pid=old.tran;
	update px set count=kount-1 where pid=old.tran;
	return null;
end;
' language 'plpgsql'; 

create trigger xit after insert on x for each row execute procedure xif();
create trigger xut after update on x for each row execute procedure xuf();
create trigger xdt after delete on x for each row execute procedure xdf();

create or replace function pif()
returns trigger as '
declare
	yd integer;
begin
	insert into px values (new.id, 0);
	select id into yd from p where first=new.second and second=new.first;
	insert into py values (new.id, yd);
	update py set dip=new.id where pid=yd; 
	return null;
end;
' language 'plpgsql';

create or replace function puf()
returns trigger as '
declare
	yd1 integer;
	yd2 integer;
begin
	select id into yd1 from p where first=old.second and second=old.first;
	select id into yd2 from p where first=new.second and second=new.first;
	update py set dip=null where pid=yd1;
	update py set dip=new.id where pid=yd2;
	update py set dip=yd2 where pid=new.id; 
	return null;
end;
' language 'plpgsql';

create trigger pit after insert on p for each row execute procedure pif();
create trigger put after update on p for each row execute procedure puf();