BEGIN;

CREATE TABLE osoba (
email TEXT PRIMARY KEY,
ime TEXT NOT NULL,
prezime TEXT NOT NULL,
drzava TEXT,
grad TEXT,
spol VARCHAR( 6 ) CHECK( spol = 'zensko'
 OR spol = 'musko' ) );

INSERT INTO osoba VALUES ('lbarberic@foi.hr','Luka','Barb','hrv','zg','musko');
INSERT INTO osoba VALUES ('ihribljan@foi.hr','ime2','Hrib','hrv','zg','zensko');
INSERT INTO osoba VALUES ('kilicic@foi.hr','ime3','Ilic','hrv','zg','musko');
INSERT INTO osoba VALUES ('tmitrovic@foi.hr','ime4','Mitrovic','hrv','zg','musko');
INSERT INTO osoba VALUES ('tkeskic@foi.hr','ime5','Keskic','hrv','zg','musko');

CREATE TABLE poruka (
sifra SERIAL PRIMARY KEY,
posiljatelj TEXT REFERENCES osoba( email )
ON UPDATE CASCADE ON DELETE RESTRICT,
primatelj TEXT REFERENCES osoba( email )
ON UPDATE CASCADE ON DELETE RESTRICT,
naslov TEXT,
sadrzaj TEXT,
vrijeme TIMESTAMP DEFAULT now() );

INSERT INTO poruka VALUES (DEFAULT,'lbarberic@foi.hr','ihribljan@foi.hr','naslov1','sadrzaj1',DEFAULT);
INSERT INTO poruka VALUES (DEFAULT,'ihribljan@foi.hr','kilicic@foi.hr','naslov2','sadrzaj2',DEFAULT);
INSERT INTO poruka VALUES (DEFAULT,'kilicic@foi.hr','tmitrovic@foi.hr','naslov3','sadrzaj3',DEFAULT);
INSERT INTO poruka VALUES (DEFAULT,'tmitrovic@foi.hr','tkeskic@foi.hr','naslov4','sadrzaj4',DEFAULT);
INSERT INTO poruka VALUES (DEFAULT,'tkeskic@foi.hr','lbarberic@foi.hr','naslov5','sadrzaj5',DEFAULT);

CREATE TABLE grupa (
sifra SERIAL PRIMARY KEY,
naziv VARCHAR(40) NOT NULL UNIQUE
);

INSERT INTO grupa VALUES (DEFAULT, 'naziv1');
INSERT INTO grupa VALUES (DEFAULT, 'naziv2');
INSERT INTO grupa VALUES (DEFAULT, 'naziv3');
INSERT INTO grupa VALUES (DEFAULT, 'naziv4');
INSERT INTO grupa VALUES (DEFAULT, 'naziv5');

CREATE TABLE clanstvo (
clan TEXT REFERENCES osoba( email )
ON UPDATE CASCADE ON DELETE RESTRICT,
grupa INT REFERENCES grupa( sifra ),
PRIMARY KEY( clan, grupa )
);

INSERT INTO clanstvo VALUES ('lbarberic@foi.hr', 1);
INSERT INTO clanstvo VALUES ('ihribljan@foi.hr', 2);
INSERT INTO clanstvo VALUES ('kilicic@foi.hr', 3);
INSERT INTO clanstvo VALUES ('tmitrovic@foi.hr', 4);
INSERT INTO clanstvo VALUES ('tkeskic@foi.hr', 5);

CREATE TABLE vrsta_veze (
sifra SERIAL PRIMARY KEY,
naziv VARCHAR NOT NULL UNIQUE
);

INSERT INTO vrsta_veze VALUES (DEFAULT, 'naziv1');
INSERT INTO vrsta_veze VALUES (DEFAULT, 'naziv2');
INSERT INTO vrsta_veze VALUES (DEFAULT, 'naziv3');
INSERT INTO vrsta_veze VALUES (DEFAULT, 'naziv4');
INSERT INTO vrsta_veze VALUES (DEFAULT, 'naziv5');

CREATE TABLE veza (
poslao TEXT REFERENCES osoba( email )
ON UPDATE CASCADE ON DELETE RESTRICT,
prihvatio TEXT REFERENCES osoba( email )
ON UPDATE CASCADE ON DELETE RESTRICT,
vrsta INT REFERENCES vrsta_veze( sifra ),
status VARCHAR( 10 ) CHECK( status =
'poslano' OR status = 'prihvaceno' ),
PRIMARY KEY( poslao, prihvatio, vrsta )
 );

INSERT INTO veza VALUES ('lbarberic@foi.hr', 'ihribljan@foi.hr', 1, 'poslano');
INSERT INTO veza VALUES ('ihribljan@foi.hr', 'kilicic@foi.hr', 2, 'poslano');
INSERT INTO veza VALUES ('kilicic@foi.hr', 'tmitrovic@foi.hr', 3, 'poslano');
INSERT INTO veza VALUES ('tmitrovic@foi.hr', 'tkeskic@foi.hr', 4, 'prihvaceno');
INSERT INTO veza VALUES ('tkeskic@foi.hr', 'lbarberic@foi.hr', 5, 'prihvaceno'); 

CREATE OR REPLACE FUNCTION upisi_u_grupu(email TEXT, naziv_grupe TEXT)
RETURNS VOID
AS 'INSERT INTO clanstvo SELECT $1, sifra FROM grupa WHERE naziv = $2'
LANGUAGE SQL;

CREATE OR REPLACE FUNCTION ispisi_iz_grupe(email TEXT, naziv_grupe TEXT)
RETURNS VOID 
AS 'DELETE FROM clanstvo WHERE clan = $1 AND grupa = (SELECT sifra FROM grupa WHERE naziv = $2)'
LANGUAGE SQL;

CREATE OR REPLACE FUNCTION posalji_poruku(email_posiljatelja TEXT, email_primatelja TEXT, naslov TEXT, sadrzaj TEXT)
RETURNS VOID
AS 'INSERT INTO poruka values (default,$1,$2,$3,$4,default)'
LANGUAGE SQL;

CREATE OR REPLACE FUNCTION moje_grupe(email TEXT)
RETURNS SETOF TEXT
AS 'SELECT naziv FROM grupa WHERE sifra IN (SELECT grupa FROM clanstvo WHERE clan = $1)'
LANGUAGE SQL;

CREATE OR REPLACE FUNCTION moje_grupe_2(email TEXT)
RETURNS SETOF grupa
AS 'SELECT * FROM grupa WHERE sifra IN (SELECT grupa FROM clanstvo WHERE clan = $1)'
LANGUAGE SQL;

CREATE TYPE inbox_popis AS (ime TEXT, prezime TEXT, sifra INT, naslov TEXT, vrijeme TIMESTAMP);

CREATE OR REPLACE FUNCTION inbox( email TEXT )
RETURNS  SETOF inbox_popis
AS 'SELECT o.ime, o.prezime, p.sifra, p.naslov, p.vrijeme 
FROM osoba o, poruka p
WHERE o.email = p.posiljatelj
AND p.primatelj = $1
ORDER BY p.vrijeme'
LANGUAGE SQL;

CREATE OR REPLACE FUNCTION zamolba( posiljatelj TEXT, primatelj TEXT, naziv_vrste TEXT )
RETURNS VOID
AS $$
DECLARE
 postoji BOOLEAN;
DECLARE
 sifra_vrste INT;
BEGIN
sifra_vrste := (
SELECT sifra
FROM vrsta_veze
WHERE naziv = 
naziv_vrste);
postoji := EXISTS(
SELECT veza
FROM veza
WHERE poslao = posiljatelj
AND prihvatio = primatelj
AND vrsta = sifra_vrste);
IF NOT postoji 
THEN
INSERT INTO veza VALUES ( posiljatelj, primatelj, sifra_vrste, 'poslano' );
ELSE
RAISE EXCEPTION '%','Vec postoji takva veza';
END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION 
nova_grupa( kreator TEXT, naziv_grupe TEXT )
RETURNS VOID
AS $$
DECLARE postoji BOOLEAN;
BEGIN
postoji := EXISTS(SELECT naziv FROM grupa WHERE naziv = naziv_grupe);
IF NOT postoji THEN
INSERT INTO grupa( naziv ) VALUES ( naziv_grupe );
PERFORM upisi_u_grupu(kreator, naziv_grupe );
ELSE
RAISE EXCEPTION '%', 'Vec postoji grupa pod nazivom ' || naziv_grupe;
END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION registracija(email_novi TEXT, ime TEXT, prezime TEXT,grad TEXT,spol TEXT)
RETURNS VOID AS $$
DECLARE Postoji BOOLEAN;
BEGIN
Postoji:= EXISTS (Select email as broj from osoba where email=email_novi);
IF NOT Postoji then
insert into osoba values(email_novi,ime,prezime,grad,spol);
ELSE
RAISE EXCEPTION '%', 'Vec postoji korisnik sa emailom ' || email1;
END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION provjeri_email()
RETURNS TRIGGER
AS $$
BEGIN
IF NEW.email LIKE '%@%' THEN
RETURN NEW;
ELSE
RAISE EXCEPTION '%', 'E-mail adresa ne sadrzi znak ''@''';
END IF;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER email_provjera
 BEFORE INSERT OR UPDATE
 ON osoba
 FOR EACH ROW
 EXECUTE PROCEDURE provjeri_email();

CREATE OR REPLACE FUNCTION nova_zamolba()
RETURNS TRIGGER
AS $$
 DECLARE molitelj osoba;
 DECLARE sadrzaj_poruke TEXT;
 BEGIN
 IF NEW.status = 'poslano' THEN
 molitelj := (
 SELECT osoba
 FROM osoba
 WHERE email = NEW.poslao
 );
sadrzaj_poruke := molitelj.ime || ' ' ||
 molitelj.prezime || ' ti je poslao
 zamolbu za prijateljstvom!';
 INSERT INTO poruka(
 posiljatelj, primatelj, naslov, sadrzaj )
 VALUES( NEW.poslao, NEW.prihvatio, 'Nova
 zamolba', sadrzaj_poruke );
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER poruka_uz_zamolbu
BEFORE INSERT
ON veza
FOR EACH ROW
 EXECUTE PROCEDURE nova_zamolba();

CREATE OR REPLACE FUNCTION veza_prihvacena()
RETURNS TRIGGER
AS $$
DECLARE postoji BOOLEAN;
BEGIN
IF NEW.status = 'prihvaceno' THEN
postoji := EXISTS(
SELECT veza
FROM veza
WHERE poslao = NEW.prihvatio
AND prihvatio = NEW.poslao
AND vrsta = NEW.vrsta
);
IF NOT postoji THEN
INSERT INTO veza
VALUES ( NEW.prihvatio, NEW.poslao,
 NEW.vrsta, 'prihvaceno' );
END IF;
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER prihvat_veze
AFTER INSERT OR UPDATE
ON veza
FOR EACH ROW
 EXECUTE PROCEDURE veza_prihvacena();

CREATE OR REPLACE FUNCTION jel_veza_prihvacena() 
RETURNS TRIGGER
AS $$
DECLARE Postojiveza BOOLEAN;
BEGIN
Postojiveza:= EXISTS (SELECT * FROM veza WHERE new.posiljatelj = poslao AND new.primatelj = prihvatio AND status = 'prihvaceno'); 
IF NOT Postojiveza then 
INSERT INTO poruka VALUES (default, 'admin@mail.com', new.posiljatelj, 'Obavijest', 'Ne mozes poslati poruku korisniku ' || new.primatelj || ' jer nisi s njime povezan!!', default);
RETURN OLD;
ELSE
RETURN NEW;
END IF;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER provjera_veze
BEFORE INSERT OR UPDATE
ON poruka 
FOR EACH ROW
EXECUTE PROCEDURE jel_veza_prihvacena();

CREATE OR REPLACE FUNCTION novo_clanstvo()
RETURNS TRIGGER
AS $$
DECLARE prijatelj TEXT;
DECLARE naziv_grupe TEXT;
DECLARE sadrzaj_poruke TEXT;
DECLARE novi_clan osoba;
BEGIN
naziv_grupe := (
SELECT naziv
FROM grupa
WHERE sifra = NEW.grupa
);
novi_clan := (
SELECT osoba
FROM osoba
WHERE email = NEW.clan
);
sadrzaj_poruke := novi_clan.ime || ' '
 || novi_clan.prezime || ' je postao clan grupe '
 || naziv_grupe || '!';
FOR prijatelj IN SELECT DISTINCT prihvatio FROM
 veza WHERE poslao = NEW.clan LOOP
PERFORM posalji_poruku( NEW.clan,
prijatelj,
 'Novo clanstvo', sadrzaj_poruke );
END LOOP;
RETURN NEW;
END;
 $$ LANGUAGE plpgsql;

CREATE TRIGGER novi_clan_grupe
AFTER INSERT
ON clanstvo
FOR EACH ROW
 EXECUTE PROCEDURE novo_clanstvo();

CREATE OR REPLACE FUNCTION provjera_teme()
RETURNS TRIGGER AS $$
DECLARE Postojiporuka BOOLEAN;
BEGIN
Postojiporuka:= EXISTS (SELECT * FROM poruka WHERE (new.posiljatelj = posiljatelj AND new.primatelj = primatelj AND new.naslov = naslov) OR (new.posiljatelj = primatelj AND new.primatelj = posiljatelj AND new.naslov = naslov));
IF Postojiporuka THEN
INSERT INTO poruka VALUES (default, new.posiljatelj, new.primatelj, 'Re: ' || new.naslov, new.sadrzaj, default);
RETURN OLD;
ELSE
RETURN NEW;
END IF;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER provjera_teme_okidac
BEFORE INSERT OR UPDATE
ON poruka 
FOR EACH ROW
EXECUTE PROCEDURE provjera_teme();

CREATE TABLE fotoalbum(
sifra SERIAL PRIMARY KEY,
opis TEXT,
tip VARCHAR(3),
slika OID,
kljucne_rijeci VARCHAR(50)[5]
);

CREATE TEMP TABLE
evidencija_nastave(
student VARCHAR( 20 ),
dolasci BOOLEAN[]
);

CREATE OR REPLACE FUNCTION ima_kljucnu_rijec(kr VARCHAR)
RETURNS SETOF OID
AS
'SELECT slika FROM fotoalbum WHERE $1 = ANY(kljucne_rijeci)'
LANGUAGE SQL;

CREATE TYPE status AS ENUM (
'na vezi',
'nije na vezi',
'nepoznat'
);

CREATE TEMP TABLE korisnik(
kor_ime VARCHAR( 20 ),
stanje STATUS DEFAULT 'nepoznat'
);

INSERT INTO korisnik
VALUES ( 'Ivan', 'na vezi' );
INSERT INTO korisnik
VALUES ( 'Luka', 'nije na vezi' );
INSERT INTO korisnik
VALUES ( 'Luka' );


CREATE TYPE tip_adresa AS (
ulica TEXT,
broj INTEGER,
post_broj INTEGER,
grad TEXT,
drzava TEXT
);

CREATE TEMP TABLE kupac(
sifra SERIAL PRIMARY KEY,
ime TEXT,
prezime TEXT,
adresa tip_adresa
);

CREATE TABLE admin(
tel_br VARCHAR(10)
) INHERITS (osoba);

INSERT INTO admin( email, ime, prezime,
tel_br ) VALUES ( 'zkovac@foi.hr',
'Zovko', 'Kovac', '0984345561' );

CREATE TABLE moderator(
stanje STATUS DEFAULT 'nepoznat'
) INHERITS (osoba);

ALTER TABLE clanstvo
ADD COLUMN vrijedece_vrijeme TSRANGE
 DEFAULT tsrange( NOW()::TIMESTAMP, 'infinity'::TIMESTAMP );

ALTER TABLE clanstvo
DROP CONSTRAINT clanstvo_pkey;

ALTER TABLE clanstvo
ADD CONSTRAINT clanstvo_pkey
 PRIMARY KEY( clan, grupa, vrijedece_vrijeme );

CREATE OR REPLACE FUNCTION promjena_clanstva()
RETURNS TRIGGER
AS $$
 BEGIN
 UPDATE clanstvo
 SET vrijedece_vrijeme = tsrange(
 LOWER( vrijedece_vrijeme )::TIMESTAMP,
 NOW()::TIMESTAMP )
 WHERE OLD.clan = clan
 AND OLD.grupa = grupa
 AND UPPER( vrijedece_vrijeme ) = 'infinity'::TIMESTAMP;
 RETURN NULL;
 END;
 $$
 LANGUAGE plpgsql;

CREATE TRIGGER temp_clanstvo
BEFORE DELETE
ON clanstvo
FOR EACH ROW
EXECUTE PROCEDURE promjena_clanstva();

ALTER TABLE poruka
ADD COLUMN vrijedece_vrijeme TSRANGE
 DEFAULT tsrange( NOW()::TIMESTAMP, 'infinity'::TIMESTAMP );

ALTER TABLE poruka
DROP CONSTRAINT poruka_pkey;

ALTER TABLE poruka
ADD CONSTRAINT poruka_pkey
 PRIMARY KEY( sifra, vrijedece_vrijeme );

CREATE OR REPLACE FUNCTION promjena_poruke()
RETURNS TRIGGER
AS $$
 BEGIN
 UPDATE poruka
 SET naslov = old.naslov || ' (obrisana poruka)',
 vrijedece_vrijeme = tsrange(
 LOWER( vrijedece_vrijeme )::TIMESTAMP,
 NOW()::TIMESTAMP )
 WHERE OLD.sifra = sifra
 AND UPPER( vrijedece_vrijeme ) = 'infinity'::TIMESTAMP;
 RETURN NULL;
 END;
 $$
 LANGUAGE plpgsql;

CREATE TRIGGER temp_poruka
BEFORE DELETE
ON poruka
FOR EACH ROW
EXECUTE PROCEDURE promjena_poruke();

ALTER TABLE veza
ADD COLUMN vrijedece_vrijeme TSRANGE
 DEFAULT tsrange( NOW()::TIMESTAMP, 'infinity'::TIMESTAMP );

ALTER TABLE veza
DROP CONSTRAINT veza_pkey;

ALTER TABLE veza
ADD CONSTRAINT veza_pkey
 PRIMARY KEY( poslao, prihvatio, vrsta, vrijedece_vrijeme );

CREATE OR REPLACE FUNCTION promjena_veze()
RETURNS TRIGGER
AS $$
 BEGIN
 UPDATE veza
 SET vrijedece_vrijeme = tsrange(
 LOWER( vrijedece_vrijeme )::TIMESTAMP,
 NOW()::TIMESTAMP )
 WHERE OLD.poslao = poslao
 AND OLD.prihvatio = prihvatio
 AND OLD.vrsta = vrsta
 AND UPPER( vrijedece_vrijeme ) = 'infinity'::TIMESTAMP;
 RETURN NULL;
 END;
 $$
 LANGUAGE plpgsql;

CREATE TRIGGER temp_veza
BEFORE DELETE
ON veza
FOR EACH ROW
EXECUTE PROCEDURE promjena_veze();

COMMIT;