-- merge naredba 

-- 1. napisati sledece sql naredbe:

--    a) kreirati tabelu predmetStudent u kojoj cuvamo koliko j estudenata polozilo neki predmet
--      idPredmeta, brStudenata idPredmeta primarni i strani kljuc na tabelu predmet

CREATE TABLE da.predmetStudent(
	idPredmeta INTEGER NOT NULL PRIMARY KEY,
	brStudenata INTEGER,
	FOREIGN KEY fkps_predmet(idPredmeta) REFERENCES da.predmet
)@

--   b) popuniti tabelu predmetStudent svi obaveznim predmetima sa smera Informatika
--      na osnovnim akademskim studijama, brStudenata svima postaviti na 5

INSERT INTO da.predmetStudent(idPredmeta, brStudenata)
SELECT pp.idpredmeta, 5
FROM da.predmetprograma pp
WHERE pp.idprograma IN (SELECT id FROM da.studijskiprogram sp
						WHERE sp.naziv = 'Informatika' AND sp.idnivoa IN (SELECT id 
																		  FROM da.nivokvalifikacije
																		  WHERE naziv = 'Osnovne akademske studije'))@
																		  
-- c) azurirati informacije u tabeli predmetStudenti(dodati sve predmete i one koji
--   su vec u tabeli azurirati ispravnim informacijama)

MERGE INTO da.predmetStudent ps
USING (
	SELECT p.id, COUNT(i.indeks) brStudenata
	FROM da.predmet p LEFT JOIN da.ispit i
			ON i.idpredmeta = p.id
			AND ocena > 5 AND status = 'o'
	GROUP BY p.id
) as real_data																	  																	  
ON ps.idPredmeta = real_data.id
WHEN MATCHED THEN UPDATE SET ps.brStudenata = real_data.brStudenata
WHEN NOT MATCHED THEN INSERT VALUES (real_data.id, real_data.brStudenata)@
  
SELECT * FROM da.predmetStudent@

DROP TABLE da.predmetStudent@
---------------------------------------------------------------------

-- 2.
--- a) napraviti tabelu studentPodaci u kojo cuvamo sledec informacije:
--     indeks, brpolpred, prosek, datum upisa

CREATE TABLE studentPodaci(
	indeks INTEGER NOT NULL PRIMARY KEY,
	brPred SMALLINT,
	prosek FLOAT,
	datUpisa DATE,
	FOREIGN KEY fksp_dosije(indeks) REFERENCES dosije)@

-- b) popuniti tabelu sledecim informacijama:
--    studente sa prosecima preko 8 ubaciti indeks, brpred, prosek
--    studenti koji su diplomirali indeks, 40, 10

INSERT INTO studentPodaci(indeks, brPred, prosek)
WITH diplomirani AS (
	SELECT indeks
	FROM dosije
	WHERE idstatusa IN (SELECT id FROM studentskistatus
						WHERE naziv = 'Diplomirao')
)
SELECT i.indeks, COUNT(i.idpredmeta), AVG(i.ocena + 0.0)
FROM ispit i
WHERE i.ocena > 5 AND i.status = 'o'
	AND i.indeks NOT IN (SELECT indeks FROM diplomirani)
GROUP BY i.indeks
HAVING AVG(i.ocena + 0.0) > 8
UNION
SELECT indeks, 40, 10.0
FROM diplomirani@

SELECT * FROM studentPodaci@


-- c) azurirati informacije iz tabelee studentPodaci
--   svima koji su diplomirali da unesemo datumupisa
--   onima koji su u tabeli i na budzetu azurirati brpredmeta i prosek
--   onima koji su u tabeli a status im je ispisan - obrisati ih
--   onima koji nisu u tabeli i nisu ispisani uneti sve osim datuma upisa
--   sve ostalo ignorisati

MERGE INTO studentPodaci sp
USING (
	SELECT d.indeks, COUNT( CASE
								WHEN i.ocena > 5 AND status = 'o' THEN i.idpredmeta
								ELSE NULL
							END) brPredmeta,
					AVG(CASE
							WHEN ocena > 5 AND status = 'o' THEN ocena + 0.0
							ELSE NULL
						END) prosek, 
						ss.naziv,
						d.datupisa
	FROM dosije d LEFT JOIN ispit i	
			ON d.indeks = i.indeks
		LEFT JOIN studentskistatus ss
			ON d.idstatusa = ss.id
	GROUP BY d.indeks, ss.naziv, d.datupisa
) AS real_data
ON sp.indeks = real_data.indeks
WHEN MATCHED AND real_data.naziv = 'Diplomirao' THEN
	UPDATE SET sp.datUpisa = real_data.datupisa
WHEN MATCHED AND real_data.naziv = 'Budzet' THEN
	UPDATE SET (sp.brPred, sp.prosek) = (real_data.brPredmeta, real_data.prosek)
WHEN MATCHED AND real_data.naziv = 'Ispisan' THEN
	DELETE
WHEN NOT MATCHED AND real_data.naziv != 'Ispisan' THEN
	INSERT (indeks, brPred, prosek)
	VALUES (real_data.indeks, real_data.brPredmeta, real_data.prosek)
ELSE IGNORE@

-- d) obrisatu tabelu..

DROP TABLE studentPodaci@

------------------------------------------------------------------------

-- okidac koji zabranjuje brisanje studenata koji su diplomirali:
CREATE OR REPLACE TRIGGER dosijeBrisanjeDipl
	BEFORE DELETE ON dosije
	REFERENCING OLD AS staro
	FOR EACH ROW
	WHEN (staro.idstatusa IN (SELECT id 
							 FROM studentskistatus 
							 WHERE naziv = 'Diplomirao'))
    SIGNAL SQLSTATE '75000' ('Student ciji je status Diplomiran ne moze biti obrisan')@


INSERT INTO dosije
VALUES (11111111, 101, 'Test', 'Test', 'm', 'Beograd', -2, '2015-07-06', '2019-09-12')@

INSERT INTO dosije
VALUES (11111112, 101, 'Test', 'Test', 'm', 'Beograd', 1, '2015-07-06', '2019-09-12')@

SELECT * FROM dosije WHERE ime = 'Test'@

DELETE FROM dosije WHERE ime = 'Test'@ -- ova naredba ispaljuje gresku sa SQLSTATE 75000(koji smo naveli)

DROP TRIGGER dosijeBrisanjeDipl@

DELETE FROM dosije WHERE ime = 'Test'@ -- kako smo okidac obrisali ovo sada prolazi

---------------------------------------------------------------------------

-- primer pracenja aktivnosti na bazi

CREATE TABLE imena(
	ime VARCHAR(20) NOT NULL PRIMARY KEY
)@

-- u tabeli pamtimo ime korisnika, timestamp pristupa i tip menjanja podataka na bazi
CREATE TABLE log_imena(
	ime_korisnika VARCHAR(100) NOT NULL,
	vreme_pristupa TIMESTAMP  NOT NULL,
	tip_pristupa CHAR  NOT NULL
)@

CREATE OR REPLACE TRIGGER menjanje_imena
AFTER INSERT OR DELETE OR UPDATE OF ime ON imena
FOR EACH ROW
BEGIN
	IF INSERTING THEN INSERT INTO log_imena VALUES(user, current_timestamp, 'i');
  	ELSEIF DELETING THEN INSERT INTO log_imena VALUES(user, current_timestamp, 'd');
  	ELSEIF UPDATING THEN INSERT INTO log_imena VALUES(user, current_timestamp, 'u');

	END IF;
END@

INSERT INTO imena VALUES ('neko ime')@

UPDATE imena SET ime='neko ime2' WHERE ime = 'neko ime'@

DELETE FROM imena@

SELECT * FROM log_imena@ -- vidimo 3 pristupa bazi

INSERT INTO imena
SELECT DISTINCT ime FROM dosije@

SELECT * FROM log_imena@ -- vidimo 453 pristupa bazi (3starta + 450 nova za insert)
                         -- ukoliko okidac umesto sa FOR EACH ROW opcijom kreiramo sa
                         -- FOR EACH STATEMENT mozemo videti da ce nakon ovog reda biti
                         -- samo 4 unosa (ceo insert se racuna kao jedan pristup)

DROP TRIGGER menjanje_imena@
DROP TABLE log_imena@
DROP TABLE imena@

----------------------------------------------------------

-- okidac koji forsira da se espb menja za njavise 1 bod

CREATE TRIGGER promenaESPB
BEFORE UPDATE OF espb ON predmet
REFERENCING OLD AS staro NEW AS novo
FOR EACH ROW
SET novo.espb = CASE
					WHEN staro.espb < novo.espb THEN staro.espb + 1
					WHEN staro.espb < novo.espb THEN staro.espb - 1
					ELSE staro.espb
				END
@

INSERT INTO predmet VALUES(1601, 'test', 'test', 4)@

SELECT * FROM predmet WHERE naziv = 'test'@

UPDATE predmet SET espb = 40 WHERE naziv = 'test'@

DROP TRIGGER promenaESPB@

----------------------------------------------------------------------

-- primer azuriranja vise tabela kroz okidac

CREATE TABLE broj_predmeta(
	broj INTEGER NOT NULL PRIMARY KEY
)@
	
CREATE TABLE broj_po_espb(
	espb INTEGER NOT NULL PRIMARY KEY,
	broj INTEGER
)@

INSERT INTO broj_predmeta
SELECT COUNT(*) FROM predmet@

INSERT INTO broj_po_espb
SELECT espb, COUNT(*)
FROM predmet
GROUP BY espb@

-- bitno je da obe tabele azuriramo atomicno
-- za to koristimo ATOMIC blok

CREATE TRIGGER predmetDEL
AFTER DELETE ON predmet
REFERENCING OLD AS stari
FOR EACH ROW
BEGIN ATOMIC
	UPDATE broj_predmeta SET broj = broj - 1;
	IF 0 = (SELECT COUNT(*) FROM predmet WHERE espb = stari.espb)
			 THEN DELETE FROM broj_po_espb WHERE espb = stari.espb;
	ELSE UPDATE broj_po_espb SET broj = broj - 1 WHERE espb = stari.espb;
	END IF;
END
@


CREATE TRIGGER predmetINS
AFTER INSERT ON predmet
REFERENCING NEW AS novi
FOR EACH ROW
BEGIN ATOMIC
	UPDATE broj_predmeta SET broj = broj + 1;
	IF 1 = (SELECT COUNT(*) FROM predmet WHERE espb = novi.espb)
			 THEN INSERT INTO broj_po_espb VALUES(novi.espb, 1);
	ELSE UPDATE broj_po_espb SET broj = broj + 1 WHERE espb = novi.espb;
	END IF;
END
@

SELECT * FROM broj_predmeta@

SELECT * FROM broj_po_espb@
SELECT * FROM broj_predmeta@

INSERT INTO predmet VALUES(1601, 'test', 'test', 11)@

DELETE FROM predmet WHERE naziv = 'test'@

DROP TRIGGER predmetDEL@
DROP TRIGGER predmetINS@