1/*
2ZADATAK 1 (tekst iz slike):
3 
4   Napisati proceduru usp_PrikaziRezervisaneFilmovePoKategoriji kojoj je
5   neophodno proslediti ID kategorije (kategorija_id). Ukoliko procedura vec
6   postoji, prvo je obrisati.
7 
8   1) Proveriti da li postoji kategorija sa tim ID-em, te ako ne postoji
9      izbaciti gresku "Ne postoji kategorija ciji je ID <kategorija_id>".
10   2) Ako kategorija postoji, izlistati podatke o filmovima koji pripadaju
11      toj kategoriji i koji imaju rezervisane kopije filmova (status kopije
12      je REZERVISAN), pod odredjenim uslovima.
13      A. U zaglavlju ispisa treba da stoji:
14         Za kategoriju sa oznakom <kategorija_id> - <naziv kategorije>,
15         rezervisani su sledeci filmovi:
16      B. Zatim sledi lista filmova sortirana po datumu izdavanja tako da
17         prvi u listi bude film sa poslednjim (najskorijim) datumom
18         izdavanja. Ispisati:
19         <redni broj>. <naziv_filma>, <trajanje filma (ako postoji, inace "0")>,
20         <datum izdavanja>, <naziv reziera koji mu je dodeljen>,
21         <broj rezervisanih kopija tog filma>.
22      C. Ukupno filmova: <broj rezervisanih kopija filmova sa tom kategorijom>
23   3) Ako kategorija postoji, a nema rezervisanih kopija filmova sa tom
24      odgovarajucom oznakom:
25      Za kategoriju sa oznakom <kategorija_id> nema rezervisanih filmova.
26 
27   NAPOMENA: Prilozeni i kod za poziv i proveru svakog use case-a procedure.
28*/
29
30
31
32CREATE PROCEDURE usp_PrikaziRezervisaneFilmovePoKategoriji
33    @kategorija_id INT
34AS
35BEGIN
36    SET NOCOUNT ON;
37 
38    DECLARE @naziv_kategorije VARCHAR(50);
39    DECLARE @broj_rezervisanih INT;
40    DECLARE @poruka NVARCHAR(200);
41 
42    -- 1) provera da li kategorija postoji
43    SELECT @naziv_kategorije = naziv
44    FROM KATEGORIJA
45    WHERE kategorija_id = @kategorija_id;
46 
47    IF @naziv_kategorije IS NULL
48    BEGIN
49        SET @poruka = 'Ne postoji kategorija ciji je ID ' + CAST(@kategorija_id AS VARCHAR(10));
50        RAISERROR(@poruka, 16, 1);
51        RETURN;
52    END
53 
54    -- broj rezervisanih kopija filmova te kategorije
55    SELECT @broj_rezervisanih = COUNT(*)
56    FROM KOPIJA K
57    JOIN FILM F ON F.film_id = K.film_id
58    WHERE F.kategorija_id = @kategorija_id
59      AND K.status = 'REZERVISAN';
60 
61    -- 3) kategorija postoji, ali nema rezervisanih filmova
62    IF @broj_rezervisanih = 0
63    BEGIN
64        PRINT 'Za kategoriju sa oznakom ' + CAST(@kategorija_id AS VARCHAR(10)) + ' nema rezervisanih filmova.';
65        RETURN;
66    END
67 
68    -- 2A) zaglavlje
69    PRINT 'Za kategoriju sa oznakom ' + CAST(@kategorija_id AS VARCHAR(10)) + ' - ' + @naziv_kategorije
70        + ', rezervisani su sledeci filmovi:';
71 
72    -- 2B) lista filmova, sortirana po datumu izdavanja opadajuce (najskoriji prvi)
73    SELECT
74        ROW_NUMBER() OVER (ORDER BY F.datum_izdavanja DESC) AS redni_broj,
75        F.naziv                                              AS naziv_filma,
76        COALESCE(CAST(F.trajanje AS VARCHAR(10)), '0')       AS trajanje,
77        F.datum_izdavanja,
78        COALESCE(R.ime + ' ' + R.prezime, 'N/A')              AS reziser,
79        COUNT(K.kopija_id)                                    AS broj_rezervisanih_kopija
80    FROM FILM F
81    JOIN KOPIJA K   ON K.film_id = F.film_id AND K.status = 'REZERVISAN'
82    LEFT JOIN REZISER R ON R.reziser_id = F.reziser_id
83    WHERE F.kategorija_id = @kategorija_id
84    GROUP BY F.film_id, F.naziv, F.trajanje, F.datum_izdavanja, R.ime, R.prezime
85    ORDER BY F.datum_izdavanja DESC;
86 
87    -- 2C) ukupan broj rezervisanih kopija
88    PRINT 'Ukupno filmova: ' + CAST(@broj_rezervisanih AS VARCHAR(10));
89END
90GO
91
92
93
94/*
95Napisati triger trg_ProveriVracanje koji se aktivira nakon azuriranja
96   tabele IZNAJMLJIVANJE, konkretno kada clan vrati film (tj. kada se upise
97   vrednost u kolonu datum_vracanja). Triger treba da implementira sledeca
98   poslovna pravila:
99 
100   1. Kada clan vrati kopiju filma, automatski promeniti status te kopije
101      u 'DOSTUPAN'.
102   2. Proveriti da li postoje rezervacije nekih drugih clanova za taj film.
103      Ukoliko postoje, odmah promeniti status kopije u 'REZERVISAN' umesto
104      u 'DOSTUPAN', kako bi rezervacije bile ispostovane.
105   3. Izracunati da li postoji kasnjenje - ako je datum_vracanja veci od
106      rok_vracanja, upisati iznos penala u kolonu iznos_kasnjenja
107      (broj dana kasnjenja x fiksna dnevna cena penala od 100 dinara).
108   4. Ukoliko se pokusa azuriranje na nacin koji nije povratak filma
109      (tj. datum_vracanja nije promenjen), ne raditi nista.
110 
111   NAPOMENA: Prilozeni i kod za proveru trigera koji pokriva bar jedan
112   slucaj (npr. vracanje filma sa kasnjenjem, ili vracanje filma kada
113   postoji rezervacija).
114*/
115
116IF OBJECT_ID('trg_ProveriVracanje', 'TR') IS NOT NULL
117    DROP TRIGGER trg_ProveriVracanje;
118GO
119
120
121CREATE TRIGGER trg_ProveriVracanje
122ON IZNAJMLJIVANJE
123AFTER UPDATE
124AS
125BEGIN
126    SET NOCOUNT ON;
127 
128    -- 4) triger radi samo ako je kolona datum_vracanja uopste izmenjena
129    IF NOT UPDATE(datum_vracanja)
130        RETURN;
131 
132    -- dalje posmatramo samo redove kod kojih je datum_vracanja upravo
133    -- postavljen (bio je NULL, sada nije) = film je stvarno vracen
134    IF NOT EXISTS (
135        SELECT 1
136        FROM inserted i
137        JOIN deleted  d ON d.iznajmljivanje_id = i.iznajmljivanje_id
138        WHERE d.datum_vracanja IS NULL
139          AND i.datum_vracanja IS NOT NULL
140    )
141        RETURN;
142 
143    -- 1) status vracene kopije -> DOSTUPAN
144    UPDATE K
145    SET K.status = 'DOSTUPAN'
146    FROM KOPIJA K
147    JOIN inserted i ON i.kopija_id = K.kopija_id
148    JOIN deleted  d ON d.iznajmljivanje_id = i.iznajmljivanje_id
149    WHERE d.datum_vracanja IS NULL
150      AND i.datum_vracanja IS NOT NULL;
151 
152    -- 2) ako postoji AKTIVNA rezervacija drugog clana za taj film -> REZERVISAN
153    UPDATE K
154    SET K.status = 'REZERVISAN'
155    FROM KOPIJA K
156    JOIN inserted i ON i.kopija_id = K.kopija_id
157    JOIN deleted  d ON d.iznajmljivanje_id = i.iznajmljivanje_id
158    JOIN FILM F     ON F.film_id = K.film_id
159    WHERE d.datum_vracanja IS NULL
160      AND i.datum_vracanja IS NOT NULL
161      AND EXISTS (
162            SELECT 1
163            FROM REZERVACIJA R
164            WHERE R.film_id = F.film_id
165              AND R.clan_id <> i.clan_id
166              AND R.status = 'AKTIVNA'
167      );
168 
169    -- 3) obracun penala za kasnjenje (100 din. po danu kasnjenja)
170    UPDATE IZ
171    SET IZ.iznos_kasnjenja = DATEDIFF(DAY, i.rok_vracanja, i.datum_vracanja) * 100
172    FROM IZNAJMLJIVANJE IZ
173    JOIN inserted i ON i.iznajmljivanje_id = IZ.iznajmljivanje_id
174    JOIN deleted  d ON d.iznajmljivanje_id = i.iznajmljivanje_id
175    WHERE d.datum_vracanja IS NULL
176      AND i.datum_vracanja IS NOT NULL
177      AND i.datum_vracanja > i.rok_vracanja;
178END
179GO
180
181
182
183/*
184ZADATAK 1. TEST 2
185Napisati proceduru usp_PrikaziSlobodneFilmovePoRejtingu kojoj je neophodno proslediti ID rejtinga (rejting_id). 
186Ukoliko procedura već postoji, prvo je obrisati. 
187Proveriti da li postoji rejting sa tim ID-em, te ako ne postoji izbaciti grešku "Ne postoji rejting cija je ID <rejting_id>".
188Ako rejting postoji, izlistati podatke o filmovima sa tim rejtingom koji imaju slobodne kopije filmova (status kopije je DOSTUPAN) pod određenim uslovima.
189A. U zaglavlju ispisa treba da stoji: Za rejting sa oznakom <rejting_id> - <znacenje rejtinga>, dostupni su sledeci filmovi:
190B. Zatim sledi lista filmova, sortirano po datumu izdavanja tako da prvi u listi bude datum poslednjeg izdavanja. 
191Ispisati: <redni broj>. <naziv_filma>, <prevod (ako postoji), inače "-">, <datum izdavanja>, <naziv kategorije kojoj pripada>, <broj slobodnih kopija tog filma>.
192C. Ukupno filmova: <broj kopija filmova sa tim rejtingom>.
193Ako rejting postoji, a nema slobodnih kopija filmova sa tim rejtingom, ispisati odgovarajuću poruku: Za rejting sa oznakom <rejting_id> - <znacenje rejtinga>, nema slobodnih filmova.
194NAPOMENA: Priložiti i kod za poziv i proveru svakog use case-a procedure. Ukoliko neki use case vraća grešku, adekvatno je obraditi poruku o grešci.
195*/
196
197
198IF OBJECT_ID('dbo.usp_PrikaziSlobodneFilmovePoRejtingu', 'P') IS NOT NULL
199    DROP PROCEDURE dbo.usp_PrikaziSlobodneFilmovePoRejtingu;
200GO
201
202CREATE PROCEDURE dbo.usp_PrikaziSlobodneFilmovePoRejtingu
203    @rejting_id INT
204AS
205BEGIN
206    SET NOCOUNT ON;
207
208    IF NOT EXISTS (SELECT 1 FROM dbo.REJTING WHERE rejting_id = @rejting_id)
209    BEGIN
210        DECLARE @poruka NVARCHAR(400) = FORMATMESSAGE('Ne postoji rejting cija je ID %d', @rejting_id);
211        RAISERROR('%s', 16, 1, @poruka);
212        RETURN;
213    END
214
215    DECLARE @znacenje NVARCHAR(200);
216    SELECT @znacenje = znacenje FROM dbo.REJTING WHERE rejting_id = @rejting_id;
217
218    DECLARE @brojFilmova INT;
219    SELECT @brojFilmova = COUNT(DISTINCT f.film_id)
220    FROM dbo.FILM f
221    JOIN dbo.KOPIJA k ON k.film_id = f.film_id AND k.status = 'DOSTUPAN'
222    WHERE f.rejting_id = @rejting_id;
223
224    IF @brojFilmova = 0
225    BEGIN
226        PRINT N'Za rejting sa oznakom ' + CAST(@rejting_id AS NVARCHAR(10)) + N' - ' + @znacenje + N', nema slobodnih filmova.';
227        RETURN;
228    END
229
230    PRINT N'Za rejting sa oznakom ' + CAST(@rejting_id AS NVARCHAR(10)) + N' - ' + @znacenje + N', dostupni su sledeci filmovi:';
231
232    SELECT
233        ROW_NUMBER() OVER (ORDER BY f.datum_izdavanja DESC) AS [Redni broj],
234        f.naziv AS [Naziv filma],
235        ISNULL(f.prevod, N'-') AS [Prevod],
236        f.datum_izdavanja AS [Datum izdavanja],
237        kat.naziv AS [Kategorija],
238        COUNT(k.copy_id) AS [Broj slobodnih kopija]
239    FROM dbo.FILM f
240    JOIN dbo.KOPIJA k ON k.film_id = f.film_id AND k.status = 'DOSTUPAN'
241    LEFT JOIN dbo.KATEGORIJA kat ON kat.kategorija_id = f.kategorija_id
242    WHERE f.rejting_id = @rejting_id
243    GROUP BY f.film_id, f.naziv, f.prevod, f.datum_izdavanja, kat.naziv
244    ORDER BY f.datum_izdavanja DESC;
245
246    PRINT N'Ukupno filmova: ' + CAST(@brojFilmova AS NVARCHAR(10));
247END
248GO
249
250-- ============ USE CASE-OVI ============
251PRINT N'--- Use case 1: nepostojeci rejting ---';
252BEGIN TRY
253    EXEC dbo.usp_PrikaziSlobodneFilmovePoRejtingu @rejting_id = 999;
254END TRY
255BEGIN CATCH
256    PRINT N'Uhvacena greska: ' + ERROR_MESSAGE();
257END CATCH
258GO
259
260PRINT N'--- Use case 2: rejting sa slobodnim filmovima ---';
261EXEC dbo.usp_PrikaziSlobodneFilmovePoRejtingu @rejting_id = 1;
262GO
263
264PRINT N'--- Use case 3: rejting bez slobodnih filmova ---';
265EXEC dbo.usp_PrikaziSlobodneFilmovePoRejtingu @rejting_id = 2;
266GO
267
268
269
270/*
271ZADATAK 2.
272Napisati triger trg_IzvrsiIznajmljivanje koji će se aktivirati pri pokušaju upisa nove stavke iznajmljivanja, tako da se implementiraju sledeća poslovna pravila:
273Proveriti da li postoji rezervacija za to iznajmljivanje. Voditi računa o tome koji član pokušava da iznajmi koji film.
274
275Ukoliko rezervacija postoji, izvršiti iznajmljivanje tako što treba: promeniti status kopije tog filma iz "REZERVISAN" u "IZDAT", obrisati tu rezervaciju iz tabele rezervacija, i uneti stavku iznajmljivanja.
276Ukoliko ne postoji rezervacija tog člana za taj film, proveriti da li postoji dostupna kopija tog filma (status kopije "DOSTUPAN"). Ukoliko postoji, izvršiti iznajmljivanje i promeniti status kopije u "IZDAT".
277U suprotnom, onemogućiti iznajmljivanje i obavestiti da ne postoje dostupne kopije filma.
278
279NAPOMENA: Priložiti i kod za proveru trigera, ali dovoljno je pokriti bar jedan use case.
280Napomena: tačka C u ZADATKU 1 doslovno kaže "broj kopija filmova" pod naslovom "Ukupno filmova" — malo je nejasno da li se misli na broj filmova ili broj kopija; implementirao sam broj filmova (u skladu sa nazivom "Ukupno filmova"), proveri protiv originala.
281*/
282
283IF OBJECT_ID('dbo.trg_IzvrsiIznajmljivanje', 'TR') IS NOT NULL
284    DROP TRIGGER dbo.trg_IzvrsiIznajmljivanje;
285GO
286
287CREATE TRIGGER IzvrsiIznajmljivanje
288on Iznajmljivanje
289INSTEAD OF INSERT
290AS
291BEGIN
292	SET NOCOUNT ON;
293
294	DECLARE @film_id INT,
295	@clan_id INT,
296	@datum_iznajmljivanja DATE,
297	@rok DATE;
298	DECLARE @rezervacija_id INT, @copy_id INT;
299
300	DECLARE novi_kursor CURSOR FAST_FORWARD FOR
301		SELECT film_id, clan_id, datum_iznajmljivanja, rok FROM inserted;
302
303		OPEN novi_kursor;
304		FETCH NEXT FROM novi_kursor INTO @film_id @clan_id @datum_iznajmljivanja @rok
305
306		WHILE @@FETCH_STATUS = 0
307		BEGIN
308			set @rezervacija_id = null;
309			set @copy_id = null;
310
311			SELECT TOP 1 @rezervacija_id = rezervacija_id
312			from dbo.Rezervacija
313			WHERE film_id = @film_id and clan_id = @clan_id and aktivna = 1
314
315
316
317CREATE TRIGGER dbo.trg_IzvrsiIznajmljivanje
318ON dbo.IZNAJMLJIVANJE
319INSTEAD OF INSERT
320AS
321BEGIN
322    SET NOCOUNT ON;
323
324    DECLARE @film_id INT, @clan_id INT, @datumIznajmljivanja DATE, @rok DATE;
325    DECLARE @rezervacija_id INT, @copy_id INT;
326
327    DECLARE novi_cursor CURSOR LOCAL FAST_FORWARD FOR
328        SELECT film_id, clan_id, datum_iznajmljivanja, rok_za_vracanje FROM inserted;
329
330    OPEN novi_cursor;
331    FETCH NEXT FROM novi_cursor INTO @film_id, @clan_id, @datumIznajmljivanja, @rok;
332
333    WHILE @@FETCH_STATUS = 0
334    BEGIN
335        SET @rezervacija_id = NULL;
336        SET @copy_id = NULL;
337
338        -- da li ovaj clan ima aktivnu rezervaciju bas za ovaj film
339        SELECT TOP 1 @rezervacija_id = rezervacija_id
340        FROM dbo.REZERVACIJA
341        WHERE film_id = @film_id AND clan_id = @clan_id AND aktivna = 1;
342
343        IF @rezervacija_id IS NOT NULL
344        BEGIN
345            -- 1) rezervacija postoji -> nadji rezervisanu kopiju tog filma
346            SELECT TOP 1 @copy_id = copy_id
347            FROM dbo.KOPIJA
348            WHERE film_id = @film_id AND status = 'REZERVISAN';
349
350            IF @copy_id IS NOT NULL
351            BEGIN
352                UPDATE dbo.KOPIJA SET status = 'IZDAT' WHERE copy_id = @copy_id;
353                DELETE FROM dbo.REZERVACIJA WHERE rezervacija_id = @rezervacija_id;
354
355                INSERT INTO dbo.IZNAJMLJIVANJE (copy_id, film_id, clan_id, datum_iznajmljivanja, rok_za_vracanje)
356                VALUES (@copy_id, @film_id, @clan_id, @datumIznajmljivanja, @rok);
357
358                PRINT N'Iznajmljivanje izvrseno na osnovu rezervacije (kopija ' + CAST(@copy_id AS NVARCHAR(10)) + N').';
359            END
360        END
361        ELSE
362        BEGIN
363            -- 2) nema rezervacije - trazi dostupnu kopiju
364            SELECT TOP 1 @copy_id = copy_id
365            FROM dbo.KOPIJA
366            WHERE film_id = @film_id AND status = 'DOSTUPAN';
367
368            IF @copy_id IS NOT NULL
369            BEGIN
370                UPDATE dbo.KOPIJA SET status = 'IZDAT' WHERE copy_id = @copy_id;
371
372                INSERT INTO dbo.IZNAJMLJIVANJE (copy_id, film_id, clan_id, datum_iznajmljivanja, rok_za_vracanje)
373                VALUES (@copy_id, @film_id, @clan_id, @datumIznajmljivanja, @rok);
374
375                PRINT N'Iznajmljivanje izvrseno (dostupna kopija ' + CAST(@copy_id AS NVARCHAR(10)) + N').';
376            END
377            ELSE
378            BEGIN
379                -- 3) ni rezervacija ni dostupna kopija - blokiraj (nema INSERT-a)
380                PRINT N'Iznajmljivanje NIJE moguce - ne postoje dostupne kopije za film ID ' + CAST(@film_id AS NVARCHAR(10)) + N'.';
381            END
382        END
383
384        FETCH NEXT FROM novi_cursor INTO @film_id, @clan_id, @datumIznajmljivanja, @rok;
385    END
386
387    CLOSE novi_cursor;
388    DEALLOCATE novi_cursor;
389END
390GO
391
392-- ============ TEST (svez film, cisto testiranje sva 3 use case-a) ============
393INSERT INTO dbo.FILM (naziv, prevod, datum_izdavanja) VALUES (N'Test Film Trigger2', NULL, '2022-01-01');
394DECLARE @noviFilmId INT = SCOPE_IDENTITY();
395
396INSERT INTO dbo.KOPIJA (film_id, status) VALUES (@noviFilmId, 'DOSTUPAN');    -- kopija A
397INSERT INTO dbo.KOPIJA (film_id, status) VALUES (@noviFilmId, 'REZERVISAN'); -- kopija B
398
399-- Use case 1: clan_id=1 ima aktivnu rezervaciju za ovaj film
400INSERT INTO dbo.REZERVACIJA (film_id, clan_id, aktivna) VALUES (@noviFilmId, 1, 1);
401
402INSERT INTO dbo.IZNAJMLJIVANJE (film_id, clan_id, datum_iznajmljivanja, rok_za_vracanje)
403VALUES (@noviFilmId, 1, '2026-07-13', '2026-07-20');
404
405SELECT * FROM dbo.IZNAJMLJIVANJE WHERE film_id = @noviFilmId;
406SELECT copy_id, status FROM dbo.KOPIJA WHERE film_id = @noviFilmId;
407SELECT * FROM dbo.REZERVACIJA WHERE film_id = @noviFilmId;   -- ocekuje se prazno
408
409-- Use case 2: clan_id=2, bez rezervacije, koristi se dostupna kopija A
410INSERT INTO dbo.IZNAJMLJIVANJE (film_id, clan_id, datum_iznajmljivanja, rok_za_vracanje)
411VALUES (@noviFilmId, 2, '2026-07-13', '2026-07-20');
412
413SELECT * FROM dbo.IZNAJMLJIVANJE WHERE film_id = @noviFilmId;   -- sad 2 reda
414SELECT copy_id, status FROM dbo.KOPIJA WHERE film_id = @noviFilmId;   -- obe IZDAT
415
416-- Use case 3: clan_id=1 pokusava opet - nema rezervacije, nema dostupne kopije -> blokirano
417INSERT INTO dbo.IZNAJMLJIVANJE (film_id, clan_id, datum_iznajmljivanja, rok_za_vracanje)
418VALUES (@noviFilmId, 1, '2026-07-14', '2026-07-21');
419
420SELECT * FROM dbo.IZNAJMLJIVANJE WHERE film_id = @noviFilmId;   -- i dalje samo 2 reda
421GO