--1. Kreirati bazu podatka sa jednim data i jednim log fajlom. Imenovati je Vašim brojem dosijea.
--Prilikom kreiranje baze podataka za data fajl postaviti sljedeće parametre:
--Lokacija: D:\DBMS_Data
--Veličina: inicijalno 5 MB,  maksimalna veličina neograničena
--Uvećanje: 10%

--Za Log fajl postaviti sljedeće paramtre:
--Lokacija: C:\DBMS_Data\Log
--Veličina: inicijalno 2 MB,  maksimalna veličina neograničena
--Uvećanje: 5%

CREATE DATABASE IB2372 ON PRIMARY
(
    NAME = 'IB2372',
    FILENAME = 'c:\BP2\data\IB2372.mdf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
)

LOG ON
(
    NAME = 'IB2372_log',
    FILENAME = 'c:\BP2\log\IB2372_log.ldf',
    SIZE = 2MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 5%
)

--2. Koristeći SQL::DDL komande kreirati sljedeće tabele i osigurati referencijalni integritet:
--Edukatori (Ime, Prezime, Titula, Email, Telefon, CV, Slika)
--Predmeti (Naziv, Oznaka, ECTS [int])
--EdukatoriPredmeti (Edukator, Predmet, Zvanje, BrojSati)

--Također, kreirati tabelu Fakulteti (Naziv, Telefon, Fax) i povezati je sa prethodno kreiranim tabelama Edukatori i EdukatoriPredmeti.
-- U tabelu Edukatori dodati polje Adresa.
--Za polje ECTS u tabeli Predmeti izmijeniti tip podatka u DECIMAL.
USE IB2372
GO
CREATE TABLE Edukatori
(
    EdukatorID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_Edukatori PRIMARY KEY,
    Ime NVARCHAR(15) NOT NULL,
    Prezime NVARCHAR(15) NOT NULL,
    Titula NVARCHAR(5) NULL,
    Email NVARCHAR(25) NULL,
    Telefon NVARCHAR(25) NULL,
    CV NVARCHAR(MAX) NULL,
    Slika IMAGE
)

CREATE TABLE Predmeti
(
    PredmetID INT NOT NULL IDENTITY (1,1) CONSTRAINT PK_Predmeti PRIMARY KEY,
    Naziv NVARCHAR(20) NOT NULL,
    Oznaka NVARCHAR(10) NULL,
    ECTS INT NULL
)

CREATE TABLE EdukatoriPredmeti
(
    EdukatorID INT NOT NULL CONSTRAINT FK_EdukatoriPredmeti_Edukatori FOREIGN KEY REFERENCES Edukatori (EdukatorID),
    PredmetID INT NOT NULL CONSTRAINT FK_EdukatoriPredmeti_Predmeti FOREIGN KEY REFERENCES Predmeti (PredmetID),
    CONSTRAINT PK_EdukatoriPredmeti PRIMARY KEY (EdukatorID, PredmetID),
    Zvanje NVARCHAR(20),
    BrojSati INT
)

CREATE TABLE Fakulteti
(
    FakultetID INT NOT NULL IDENTITY (1,1) CONSTRAINT PK_Fakulteti PRIMARY KEY,
    Naziv NVARCHAR(25) NOT NULL,
    Telefon NVARCHAR(25),
    Fax NVARCHAR(20)
)

ALTER TABLE Edukatori
ADD FakultetID INT NOT NULL CONSTRAINT FK_Edukatori_Fakulteti FOREIGN KEY REFERENCES Fakulteti (FakultetID),
    Adresa NVARCHAR(50)

ALTER TABLE EdukatoriPredmeti
ADD FakultetID INT NOT NULL CONSTRAINT FK_EdukatoriPredmeti_Fakulteti FOREIGN KEY REFERENCES Fakulteti (FakultetID)

ALTER TABLE Predmeti
ALTER COLUMN ECTS DECIMAL(18,2)

--3. Kreirati stored proceduru za upis podataka u tabelu Edukatori.
-- Također, kreirati procedure za izmjenu i brisanje podataka u istoj tabeli.
--Koristeći procedure za upis podataka u tabelu dodati 5 novih edukatora.
USE IB2372
Go
CREATE PROCEDURE usp_edukatori_insert
(
    @Ime NVARCHAR(15),
    @Prezime NVARCHAR(15),
    @Titula NVARCHAR(5) = NULL,
    @Email NVARCHAR(25) = NULL,
    @Telefon NVARCHAR(25) = NULL,
    @CV NVARCHAR(MAX) = NULL,
    @Slika IMAGE = NULL,
    @FakultetID INT,
    @Adresa NVARCHAR(50)
)
AS
BEGIN
    INSERT INTO Edukatori
    VALUES
    (
           @Ime,
           @Prezime,
           @Titula,
           @Email,
           @Telefon,
           @CV,
           @Slika,
           @FakultetID,
           @Adresa
    )
END

CREATE PROCEDURE usp_edukatori_update
(
    @EdukatorID INT,
    @Ime NVARCHAR(15),
    @Prezime NVARCHAR(15),
    @Titula NVARCHAR(5) = NULL,
    @Email NVARCHAR(25) = NULL,
    @Telefon NVARCHAR(25) = NULL,
    @CV NVARCHAR(MAX) = NULL,
    @Slika IMAGE = NULL,
    @FakultetID INT,
    @Adresa NVARCHAR(50)
)
AS
BEGIN
    UPDATE Edukatori
    SET Ime = @Ime, Prezime = @Prezime, Titula = @Titula, Email = @Email, Telefon = @Telefon, CV = @CV, Slika = @Slika, FakultetID = @FakultetID, Adresa = @Adresa
    WHERE EdukatorID = @EdukatorID
END

CREATE PROCEDURE usp_edukatori_delete
(
    @EdukatorID INT
)
AS
BEGIN
    DELETE FROM Edukatori
    WHERE EdukatorID = @EdukatorID
END

INSERT INTO Fakulteti
VALUES ('FIT', '036574612', '036574612')

SELECT * FROM Fakulteti

EXEC usp_edukatori_insert @Ime = 'Amer',
                          @Prezime = 'Stovrag',
                          @Titula = 'Mrs.',
                          @Email = 'stovragamer@gmail.com',
                          @Telefon = '061498925',
                          @CV = 'Imam CV',
                          @FakultetID = 1,
                          @Adresa = 'Semetovac bb'

                          EXEC usp_edukatori_insert @Ime = 'Amer',
                          @Prezime = 'Stovrag',
                          @Titula = 'Mrs.',
                          @Email = 'stovragamer@gmail.com',
                          @Telefon = '061498925',
                          @CV = 'Imam CV',
                          @FakultetID = 1,
                          @Adresa = 'Semetovac bb'

                          EXEC usp_edukatori_insert @Ime = 'Amer',
                          @Prezime = 'Stovrag',
                          @Titula = 'Mrs.',
                          @Email = 'stovragamer@gmail.com',
                          @Telefon = '061498925',
                          @CV = 'Imam CV',
                          @FakultetID = 1,
                          @Adresa = 'Semetovac bb'

                          EXEC usp_edukatori_insert @Ime = 'Amer',
                          @Prezime = 'Stovrag',
                          @Titula = 'Mrs.',
                          @Email = 'stovragamer@gmail.com',
                          @Telefon = '061498925',
                          @CV = 'Imam CV',
                          @FakultetID = 1,
                          @Adresa = 'Semetovac bb'

                          EXEC usp_edukatori_insert @Ime = 'Amer',
                          @Prezime = 'Stovrag',
                          @Titula = 'Mrs.',
                          @Email = 'stovragamer@gmail.com',
                          @Telefon = '061498925',
                          @CV = 'Imam CV',
                          @FakultetID = 1,
                          @Adresa = 'Semetovac bb'

SELECT * FROM Edukatori

--4. U tabele Predmeti i Fakulteti dodati po 5 zapisa. Također, u tabelu EduktoriPredmeti dodati proizvoljne podatke (ko predaje određeni predmet,
-- te na kojem fakultetu).

INSERT INTO Predmeti
VALUES ('Programiranje 2', 'PR2', 7),
       ('Baze 2', 'BP2', 7),
       ('Statistika', 'PS', 6),
       ('engleski Jezik', 'EJ', 5),
       ('Racunarske Mreze', 'PR2', 6)

INSERT INTO Fakulteti
VALUES ('Masinski', '98987192871', '78368723'),
       ('Ekonomski', '98987192871', '78368723'),
       ('Gradjevinski', '98987192871', '78368723'),
       ('Pravni', '98987192871', '78368723')

INSERT INTO EdukatoriPredmeti
VALUES (1, 1, 'Zvanje 1', 30, 1),
       (1, 3, 'Zvanje 2', 15, 2),
       (2, 4, 'Zvanje 3', 45, 5)

--5. Kreirati pogled (View) koji obuhvata sljedeće podatke: ime i prezime edukatora (spojeno), titula, oznaka predmeta,
-- naziv predmeta, broj ECTS kredita, broj sati.

CREATE VIEW view_Edukatori_Predmeti AS
    SELECT E.Ime + ' ' + E.Prezime AS Edukator, E.Titula, P.Oznaka, P.Naziv, P.ECTS, EP.BrojSati
    FROM Edukatori AS E INNER JOIN EdukatoriPredmeti AS EP
    ON E.EdukatorID = EP.EdukatorID INNER JOIN Predmeti AS P
    ON EP.PredmetID = P.PredmetID

--6. Kreirati stored proceduru koja će na osnovu proslijeđenog parametra NazivPredmeta prikazti nastavno osoblje angažovano na predmetu.
--Iskoristiti prethodno kreirani view.

CREATE PROCEDURE usp_NazivPredmeta
(
    @Naziv NVARCHAR(20)
)
AS
BEGIN
    SELECT EP.[Edukator]
    FROM view_Edukatori_Predmeti AS EP
    WHERE Naziv = @Naziv
END

EXEC usp_NazivPredmeta @Naziv = 'Programiranje 2'

--Izmijeniti prethodno kreiranu stored proceduru tako da prima dva parametra: NazivPredmeta i ImePrezime edukatora. U zavisnosti od proslijeđenog/ih parametra,
--procedura treba da prikaže podatke o predmetima i nastavnom osoblju.

ALTER PROCEDURE usp_NazivPredmeta
@NazivPredmeta NVARCHAR(20),
@ImePrezime NVARCHAR(30)
AS
BEGIN
    SELECT *
    FROM view_Edukatori_Predmeti AS EP
    WHERE Naziv = @NazivPredmeta AND Edukator = @ImePrezime
END

EXEC usp_NazivPredmeta @NazivPredmeta = 'Programiranje 2', @ImePrezime = 'Amer Stovrag'

--7. Koristeći proceduru za brisanje zapisa u tabeli Edukatori (Zadatak 3) obrisati 2 edukatora.
--Ukoliko edukatori imaju dodijeljene predmete,
--modifikovati proceduru tako da prethodno obriše sve edukatoru dodijeljene predmete,
--a zatim obriše zapis iz tabele Edukatori.

ALTER PROCEDURE usp_edukatori_delete
(
    @EdukatorID INT
)
AS
BEGIN
    DELETE FROM EdukatoriPredmeti
    WHERE EdukatorID = @EdukatorID
    DELETE FROM Edukatori
    WHERE EdukatorID = @EdukatorID
END

EXEC usp_edukatori_delete @EdukatorID= 1
EXEC usp_edukatori_delete @EdukatorID=2

SELECT *  FROM EdukatoriPredmeti

--8. Kreirati trigger koji će u tabelu EdukatoriAudit zapisati detalje izmjene podataka nad tabelom Edukatori.
USE IB2372
GO
CREATE TABLE EdukatorAudit
(
    AuditID INT NOT NULL IDENTITY (1,1) CONSTRAINT PK_EdukatorAudit PRIMARY KEY,
    EdukatorID INT,
    Ime NVARCHAR(15) NOT NULL,
    Prezime NVARCHAR(15) NOT NULL,
    Titula NVARCHAR(5) NULL,
    Email NVARCHAR(25) NULL,
    Telefon NVARCHAR(25) NULL,
    CV NVARCHAR(MAX) NULL,
    Slika IMAGE,
    Komanda NVARCHAR(15),
    Korisnik NVARCHAR(20),
    Datum DATE
)

CREATE TRIGGER trg_EdukatoriAudit ON Edukatori
AFTER UPDATE AS
    INSERT INTO EdukatorAudit
    (EdukatorID, Ime, Prezime, Titula, Email, Telefon, CV, Komanda, Korisnik, Datum)
    SELECT
        d.EdukatorID,
        d.Ime,
        d.Prezime,
        d.Titula,
        d.Email,
        d.Telefon,
        d.CV,
        'UPDATE',
        SYSTEM_USER,
        GETDATE()
    FROM deleted AS d

DROP TRIGGER trg_EdukatoriAudit

UPDATE Edukatori
SET Ime = 'Rema'
WHERE EdukatorID = 3

INSERT INTO Edukatori (Ime, Prezime, FakultetID)
VALUES ('Azra', 'Turkic', 1)

SELECT * FROM Edukatori
SELECT * FROM EdukatorAudit

--9. Koristeći proceduru za izmjenu zapisa u tabeli Edukatori (Zadatak 3) izmijeniti mail i telefon
--određenom edukatoru.

EXEC usp_edukatori_update @Ime = 'Rema', @Prezime = 'Stovrag', @Email = 'stovragamer@yahoo.com',@Telefon = '7673676123', @FakultetID = 1, @EdukatorID = 4, @Adresa = 'Semetovac'