-- sequence for translation id
drop sequence g_sqtranslate;
create sequence g_sqtranslate;

-- translate table (table with identifiers in different languages)
drop table g_translate cascade;
create table g_translate
(
        -- unique generated id
	id	bigint	not null primary key,
        -- english translation for this symbol
	name_en	text	null,
        -- swedish translation for this symbol
        name_sv text	null,
        -- romanian translation for this symbol
        name_ro text	null
        -- here more translation can be added needed.
);

-- init with default values
insert into g_translate(id, name_en, name_sv, name_ro) values(0, 'No translation available', 'No translation available', 'No translation available');


-- usage id=g_in_t('English','Swedish');
drop FUNCTION g_in_t(text,text);
CREATE FUNCTION g_in_t(text,text) RETURNS bigint AS
   'DECLARE
	id bigint;
    BEGIN
	select nextval(''g_sqtranslate'') into id;
	insert into g_translate values(id, $1, $2, $2);
	RETURN id;
    END;'
   language 'plpgsql';



-- usage: translatedstring = g_out_t('en',id);
drop FUNCTION g_out_t(text, bigint);
CREATE FUNCTION g_out_t(text, bigint) RETURNS text AS
   'DECLARE
	t text;
	key text;
        b_en boolean;
        b_sv boolean;
        b_ro boolean;
    BEGIN
        select ($1 = ''en'') into b_en;
        select ($1 = ''sv'') into b_sv;
        select ($1 = ''ro'') into b_ro;
        if (b_en) then
	SELECT name_en from g_translate where id=$2 INTO t;
        end if;
        if (b_sv) then
	SELECT name_sv from g_translate where id=$2 INTO t;
        end if;
        if (b_ro) then
	SELECT name_ro from g_translate where id=$2 INTO t;
        end if;
	RETURN t;
    END;'
   language 'plpgsql';


-- type_code, en, sv
drop FUNCTION p_in_title(text,text,text);
CREATE FUNCTION p_in_title(text,text,text) RETURNS bigint AS
   'DECLARE
	zid bigint;
    BEGIN
	select nextval(''p_sqtitle'') into zid;
	insert into p_title values(zid, $1, g_in_t($2, $3));
	RETURN zid;
    END;'
   language 'plpgsql';




-- person table is the root table for person database
drop table p_person cascade;
create table p_person
(
        -- unique code for a person (personalno, or some other code if it does not have any login)
	code            varchar(200)    not null primary key,
        -- name
	firstname	varchar(80)     not null,
	lastname	varchar(80)     not null,
        -- personal no
	personalno	varchar(15)     null,

        -- the code a person has in the schedule (schema)
        schedule_code   varchar(200)    null,
        -- the key for the doors
        doorkey         varchar(500)    null
);

-- indexes defined on person table
create index p_ndxperson0 on p_person
(
	firstname
);

create index p_ndxperson1 on p_person
(
	lastname
);

create index p_ndxperson2 on p_person
(
	personalno
);


-- titletype (table for types of titles)
-- looks like this
--   code    | name_id |     g_out_t
-------------+---------+-----------------
--   AT      |     177 | Academic title
--   ET      |     178 | Education title
drop table p_titletype cascade;
create table p_titletype
(
        -- code for this type of title
	code		varchar(500)	not null primary key,
        -- translation for that
	name_id      bigint not null default 0,
	constraint p_c_titletype_fk_name_id foreign key(name_id) references g_translate(id)
		match full
		on update cascade
		on delete set default
);


-- init with default values 
insert into p_titletype(code, name_id) values('--', g_in_t('None', 'sv None'));
insert into p_titletype(code, name_id) values('AT', g_in_t('Academic title', 'sv AT'));
insert into p_titletype(code, name_id) values('ET', g_in_t('Education title', 'sv AT'));
-- after this insert the g_sqtranslate will go up to 3

-- sequence for table title
drop sequence p_sqtitle;
create sequence p_sqtitle;

-- title table (table that tell us the titles available for a person)
drop table p_title cascade;
create table p_title
(
        -- unique id generated from sequence
	id			bigint	not null primary key default nextval('p_sqtitle'),
        -- what kind of title it is (type)
	type_code		varchar(200)	not null default '--',
        -- translation for this title.
	name_id			bigint	not null default 0,
	constraint p_c_title_fk_name_id foreign key(name_id) references g_translate(id)
		match full
		on update cascade
		on delete set default,
	constraint p_c_title_fk_type_code foreign key(type_code) references p_titletype(code)
		match full
		on update cascade
		on delete set default
);

-- init with default values
insert into p_title(id, type_code, name_id) values(0, '--', g_in_t('None', 'sv None'));
-- after this insert the g_sqtranslate will go up to 4

-- person to title (defines relation between a person and several titles)
drop table p_p2title cascade;
create table p_p2title
(

	person_code		varchar(200)	not null,

	title_id		bigint	not null default 0,


	constraint p_c_p2title_pk primary key(person_code, title_id),

	constraint p_c_p2title_fk_person_code foreign key(person_code) references p_person(code)
		match full
		on update cascade
		on delete cascade,

	constraint p_c_p2title_fk_title_id foreign key(title_id) references p_title(id)
		match full
		on update cascade
		on delete set default
);


------------------------------------------------------------------------------------------------------
-- now the crash stuff:
------------------------------------------------------------------------------------------------------
-- put a person in 
insert into p_person values('99999999-9999', 'Adrian', 'Pop', '999999-9999', 'ADPOP', 'XXX');

-- now add a title in the p_title and relate the newly inserted person to it in the p_p2title table
insert into p_p2title values ('99999999-9999', p_in_title('AT','PhD Student','Doktorand'));
-- after this insert the g_sqtranslate will go up to 5 and p_sqtitle up to 1


-- now let's delete what we put in
start transaction;
delete from p_p2title where person_code='99999999-9999' and title_id=1;
delete from g_translate where id=5;
commit transaction;
