-- DDL (Data Definition Language)
-- Deo SQL-a koji se koristi za definisanje i upravljenje strukturom baze podataka
-- Kroz DDL se kreiraju, menjaju i brisu objekti baze podataka kao sto su tabele, sheme, indeksi, pogledi i ogranicenja

-- Glavne komande:

-- 1. CREATE (kreiranje objekata baze podataka)

-- -- Kreiranje sheme:
CREATE SCHEMA IF NOT EXISTS fakultet;
-- objekti baze podataka se mogu smestati unutar kreirane sheme

-- -- Kreiranje tabele:
-- -- CREATE TABLE naziv_tabele (
-- --         naziv_atributa_1 tip_1 [ogranicenje],
-- --         ...
-- --         naziv_atributa_n tip_n [ogranicenje]),
--            [lista ogranicenja] );
-- --
-- -- Svaki atribut moze imati odredjeno ogranicenje:
-- --
-- -- NOT NULL - vrednost atributa ne sme biti nedostajuca
-- -- CHECK (uslov) - vrednost atributa mora ispunjavati zadati uslov
-- -- UNIQUE - vrednost atributa mora biti jedinstvena na nivou citave kolone u tabeli
-- -- PRIMARY KEY - uslov primarnog kljuca
-- -- FOREIGN KEY references naziv_tabele(naziv_atributa) - uslov stranog kljuca

-- -- Ogranicenja se mogu zadati u zasebnom redu prilikom definisanja tabela, u tom slucaju moguce je definisati ogranicenja koja u obzir uzimaju vise atributa istovremeno. Na primer:
-- -- PRIMARY KEY (atribut1,atribut2) - slozeni primarni kljuc
-- -- FOREIGN KEY (atribut1,atribut2) references naziv_tabele(kolona1,kolona2) - slozeni strani kljuc
-- -- UNIQUE(atribut1,atribut2) - vrednost para atributa atribut1 i atribut2 je jedinstvena
-- -- CHECK (atribut1 + atribut2 < 100) - provera da li je zbir vrednosti atributa manji od 100

-- -- Ispred definicije svakog ogranicenja moguce je dodati i naziv tog ogranicenja:
-- -- CONSTRAINT naziv_ogranicenja [ogranicenje]

-- -- Na primer:
-- -- CONSTRAINT jedinstveni_par UNIQUE(atribut1,atribut2)

CREATE TABLE Student(
    ID INT PRIMARY KEY,
    ime VARCHAR(50),
    prezime VARCHAR(50),
    datum_rodjenja DATE
);


-- -- Unos podataka u tabelu
INSERT INTO Student VALUES (1,'Pera','Peric',null);
INSERT INTO Student (ID,ime,prezime) VALUES (2,'Ana','Anic');

-- -- Ispis podataka u tabeli
select * from Student;

-- 2. DROP (Brisanje stuktura baze podataka)

-- Brisanje tabele ukoliko ona vec postoji

CREATE TABLE fakultet.Predmet(
    ID SERIAL PRIMARY KEY,
    naziv VARCHAR(40) NOT NULL,
    ESPB INT CHECK(ESPB>0 AND ESPB<=30) --dodavanje ogranicenja
);

insert into fakultet.Predmet values(1,'NAZIV1',8);

-- 'SERIAL' kao tip podataka nam omogucuje da ne moramo navoditi ID eksplicitno, vec se moze dodeljivati vrednost ID-a na automatizovan nacin:
-- -- GRESKA (za ID se ne sme postavljati NULL vrednost:
-- -- insert into fakultet.Predmet values(NULL,'NAZIV 1',30);
INSERT INTO fakultet.predmet(naziv,espb) values ('naziv1',30);

select * from fakultet.Predmet;

-- Naredba za brisanje SVIH podataka u tabeli -> treba je razlikovati sa naredbom DROP
DELETE FROM fakultet.predmet;

-- Naredba za brisanje svih podataka u tabeli koji ispunjavaju odredjeni uslov
DELETE FROM Student
WHERE datum_rodjenja IS NULL;

-- Primer: Kreirati tabelu Ispit, koja cuva informacije o id predmeta koji je polagao student sa odredjenim indeksom, kao i datum polaganja i ocenu koju je dobio

CREATE TABLE Ispit(
    ID_STUDENTA INT NOT NULL,
    ID_PREDMETA INT NOT NULL,
    ocena INT check(ocena between 5 and 10),
    datum_polaganja DATE not null,
    PRIMARY KEY (ID_STUDENTA,ID_PREDMETA),
    CONSTRAINT fk_student FOREIGN KEY (ID_STUDENTA) references Student(ID),
    CONSTRAINT fk_idpredmeta FOREIGN KEY (ID_PREDMETA) references fakultet.Predmet(ID)
);
-- dodavanjem stranog kljuca obezbedili smo da id_studenta i id_predmeta ne mogu uzeti vrednosti koje se vec ne nalaze u tabelama Student i Predmet, tj. student koji nije u bazi ne moze polagati ispit iz predmeta koji nije u bazi
INSERT INTO Student VALUES (1,'Toma','Tomic',NULL);
SELECT * FROM Student;
SELECT * FROM fakultet.predmet;

INSERT INTO Ispit values(1,2,6,DATE('5.1.2000') );
SELECT * FROM Ispit;

-- Sta bi se desilo sa tabelom ispit, ukoliko bismo obrisali studenta sa indeksom 1 iz tabele Student?
DELETE FROM Student
WHERE id=1;
-- [23503] ERROR: update or delete on table "student" violates foreign key constraint "fk_student" on table "ispit" Detail: Key (id)=(1) is still referenced from table "ispit".
-- U PostgreSQL, ukoliko nije drugacije navedeno, referencijalni integritet se cuva, jer strani kljuc po default-u radi po principu NO ACTION.


-- Principi ponasanja stragon kljuca:
-- NO ACTION: Ne dozvoljava brisanje ili ažuriranje reda u parent tabeli ako postoji povezani red u child tabeli. (provera intergriteta se vrsi na kraju transakcija -> privremeno moze biti narusen intergitet, dok se ne dodje do kraja transakcije)
-- RESTRICT: Slicno kao i NO ACTION, samo se provera integriteta vrsi na pocetku transakcije, tako da ni u jednom trenutku nece doci do narusavanja integriteta.
-- CASCADE: Ako se red u parent tabeli obriše ili izmeni (primarni ključ), odgovarajući redovi u child tabeli se automatski obrišu ili ažuriraju.
-- NOT NULL: Kada se obriše ili izmeni red u parent tabeli, odgovarajuca kolona u child tabeli se postavlja na NULL

-- FOREIGN KEY (naziv_kolone1) references naziv_tabele(naziv_kolone2)
                                        -- ON DELETE [MODE]
                                        -- ON UPDATE [MODE]
-- Na primer, ukoliko zelimo da obrisemo ili izmenimo red u child tabeli, kada se obrise ili izmeni odgovarajuci red u parent tabeli, strani kljuc bismo definisali na sledeci nacin:
-- FOREIGN KEY (naziv_child_kolone) references naziv_parent_tabele(naziv_odgovarajuce_kolone)
                                    -- on delete cascade
                                    -- on update cascade

-- 3. ALTER TABLE - IZMENA STRUKTURE POSTOJECE TABELE
--
-- omogucava naknadno dodavanje, brisanje ili menjanje kolona i ogranicenja
--
-- sintaksa:
--
-- ALTER [IGNORE] TABLE tbl_name
-- alter_specification [, alter_specification] ...
--
-- alter_specification:
-- ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
-- | ADD [COLUMN] (column_definition,...)
-- | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
-- | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
-- | ADD [CONSTRAINT [symbol]]
-- UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
-- | ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (index_col_name,...)
-- | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)
-- [reference_definition]
-- | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
-- | CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]
-- | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
-- | DROP [COLUMN] col_name
-- | DROP PRIMARY KEY
-- | DROP {INDEX|KEY} index_name
-- | DROP FOREIGN KEY fk_symbol

-- ZADATAK:

-- a) Kreirati shemu Prodavnica koja ce skladistiti sledece relacije u vidu tabela:
--    Dostava(ID,SIFRA_KUPCA,JMBG_DOSTAVLJACA,NAPOMENA,ADRESA,STATUS,DATUM_IZMENE,BROJ_POKUSAJA)
--    Dostavljac(JMBG,BROJ_VOZACKE,KATEGORIJA,IME,PREZIME)
--    Kupac(SIFRA,IME,PREZIME,ADRESA,TELEFON,MEJL)
--    Ukoliko shema sa zadatim imenom vec postoji, obrisati je pre ponovnog kreiranja.

DROP SCHEMA IF EXISTS Prodavnica;
CREATE SCHEMA Prodavnica;

-- b) Prilikom kreiranja tabela koje imaju strani kljuc, integritet stranog kljuca u slucaju azuriranja obezbediti kaskadnim izvrsavanjem.
-- c) Prodavnica mora imati poznate informacije o imenu, prezimenu, adresi i telefonu kupca, dok imejl adreas ne mora biti poznata.
--    Obezbediti da dva kupca ne mogu imati isti telefon niti da dva dostavljaca mogu imati isti broj vozacke dozvole.
-- d) Prilikom evidentiranja dostave, njen status moze biti - kreirana, spakovana, otkazana ili izvrsena. Obezbediti da status po formiranju podrazumevano bude kreirana.
--    Da bi dostavu bilo moguce obaviti, potrebno je da bude poznata adresa kao i datum izmene statusa. Takodje, broj pokusaja je nenegativan ceo broj koji je podrazumevano 0 i ne moze biti veci od 2.
CREATE TABLE Prodavnica.Kupac(
	sifra INT PRIMARY KEY,
	ime VARCHAR(50) NOT NULL,
	prezime VARCHAR(50) NOT NULL,
	adresa VARCHAR(50) NOT NULL,
	telefon VARCHAR(10) NOT NULL,
	email VARCHAR(50),
	UNIQUE(telefon)
);

CREATE TABLE Prodavnica.Dostavljac(
    jmbg INT PRIMARY KEY,
    broj_vozacke INT NOT NULL,
    kategorija VARCHAR(2),
	ime VARCHAR(50) NOT NULL,
	prezime VARCHAR(50) NOT NULL,
	UNIQUE(broj_vozacke)
);

CREATE TABLE Prodavnica.Dostava(
	id SERIAL PRIMARY KEY,
	sifra_kupca INT NOT NULL,
	jmbg_dostavljaca INT NOT NULL,
	napomena VARCHAR(50),
	adresa VARCHAR(50) NOT NULL,
	status VARCHAR(10) DEFAULT 'kreirana' CHECK(status in ('kreirana','spakovana','otkazana', 'zavrsena')),
	datum_izmene DATE NOT NULL,
	broj_pokusaja INT DEFAULT 0 CHECK(broj_pokusaja >= 0 and broj_pokusaja <= 2),
	CONSTRAINT kupac_fk FOREIGN KEY(sifra_kupca) REFERENCES Prodavnica.Kupac(sifra) ON UPDATE CASCADE,
	CONSTRAINT dostavljac_fk FOREIGN KEY(jmbg_dostavljaca) REFERENCES Prodavnica.Dostavljac(jmbg) ON UPDATE CASCADE
);

-- e) Napisati naredbu nezavisnu od naredbe za kreiranje tabele Kupac, kojom se toj tabeli dodaje uslv da imejl adresa mora biti poznata. Napisati naredbu kojom se brise kolona kategorija vec kreirane tabele Dostavljac.
ALTER TABLE Prodavnica.Kupac
ADD CHECK(email is not null);

ALTER TABLE Prodavnica.Dostavljac
DROP COLUMN kategorija;

-- DOMACI: Uneti podatke u tabele i istestirati ponasanje u razlicitim situacijama:
--         1) kada uslov stranog kljuca nije ispunjen
--         2) kada uslov nad atributom nije ispunjen
--         3) kada se obrise ili izmeni red u parent tabeli, kako se to odrazava na child tabelu

-- NOTE: u okviru DataGrip-a, moguce je podesiti podrazumevanu shemu, tako da se u naredbama ne mora navoditi u kojoj je shemi odredjena tabela