-- Kad je napravljena baza AdventureWorksOBP?

SELECT d.name        AS database_name,
       d.create_date    AS created_at
FROM sys.databases AS d
WHERE d.name = 'AdventureWorksOBP';


-- Ispišite naziv stupca koji se najčešće pojavljuje u 
-- bazi AdventureWorksOBP te ispišite i koliko je to 
-- puta.

SELECT 
       c.name               AS column_name,
       COUNT(*)             AS occurrences
FROM sys.columns AS c
JOIN sys.tables  AS t ON t.object_id = c.object_id
GROUP BY c.name
ORDER BY COUNT(*) DESC, c.name;


-- Situacija: neki korisnik nema pravo koristiti tablicu Proizvod, a treba određene podatke. 
--Pomognite mu tako što ćete napraviti pogled koji će dohvaćati sve proizvode.
USE AdventureWorksOBP
go

CREATE VIEW vwProizvodi
AS
SELECT
    p.IDProizvod,
    p.Naziv,
    p.Boja,
    p.PotkategorijaID
FROM Proizvod AS p;
GO
--2. Iskoristite pogled za dohvaćanje svih zapisa
SELECT * FROM dbo.vwProizvodi;

-- a.Iskoristite pogled za dohvaćanje zapisa u potkategoriji 13.
SELECT *
FROM dbo.vwProizvodi
WHERE PotkategorijaID = 13;

--Iskoristite pogled za ispis boja proizvoda i broja proizvoda u
-- svakoj boji, padajuće prema broju proizvoda
SELECT
    Boja,
    COUNT(*) AS BrojProizvoda
FROM dbo.vwProizvodi
GROUP BY Boja
ORDER BY COUNT(*) DESC, Boja;
-- Iskoristite pogled i pokraj naziva proizvoda ispišite i naziv potkategorije
SELECT
    p.Naziv AS NazivProizvoda,
    pk.Naziv AS NazivPotkategorije
FROM dbo.vwProizvodi AS p
LEFT JOIN dbo.Potkategorija AS pk
    ON pk.IDPotkategorija = p.PotkategorijaID;
--3. Promijenite pogled tako da preimenujete stupac Naziv u 
-- NazivProizvoda. Dohvatite podatke kroz pogled.
ALTER VIEW vwProizvodi
AS
SELECT
    p.IDProizvod,
    p.Naziv AS NazivProizvoda,   
    p.Boja,
    p.PotkategorijaID
FROM dbo.Proizvod AS p;
GO

-- Dohvat nakon izmjene
SELECT * FROM dbo.vwProizvodi;
 -- 4. Uklonite pogled

DROP VIEW IF EXISTS dbo.vwProizvodi;



-- Vašeg šefa (koji voli Management Studio) zanimaju podaci: 
-- ime i prezime komercijalista i koliko je ukupno prodao proizvoda. 
-- Rezultate želi sortirane opadajuće prema broju prodanih proizvoda. 
-- Objasnite šefu kako da napiše upit  
use AdventureWorksOBP
go
SELECT  k.Ime,
        k.Prezime,
        SUM(s.Kolicina) AS UkupnoProdanihProizvoda
FROM Komercijalist AS k
JOIN Racun        AS r  ON r.KomercijalistID = k.IDKomercijalist
JOIN Stavka AS s ON s.RacunID        = r.IDRacun
GROUP BY k.Ime, k.Prezime
ORDER BY UkupnoProdanihProizvoda DESC;
go

-- Napravite pogled i objasnite šefu kako da ga koristi ☺ 
CREATE VIEW ProdajaPoKomercijalistu AS
SELECT  k.Ime,
        k.Prezime,
        SUM(s.Kolicina) AS UkupnoProdanihProizvoda
FROM Komercijalist AS k
JOIN Racun        AS r  ON r.KomercijalistID = k.IDKomercijalist
JOIN Stavka AS s ON s.RacunID        = r.IDRacun
GROUP BY k.Ime, k.Prezime
go

SELECT * 
FROM ProdajaPoKomercijalistu
ORDER BY UkupnoProdanihProizvoda DESC;

-- 6. Obrišite pogled. 

DROP VIEW IF EXISTS ProdajaPoKomercijalistu;
GO
-- 7. Gospođa iz prodaje treba pristup podacima o kreditnim karticama,
-- ali samo za kartice tipa Diners. Napravite pogled. 
CREATE VIEW KreditneKartice_Diners
AS
SELECT  kk.IDKreditnaKartica,
        kk.Tip,
        kk.Broj,
        kk.IstekMjesec,
        kk.IstekGodina
FROM dbo.KreditnaKartica AS kk
WHERE kk.Tip = N'Diners';
GO
-- Pomoću pogleda dohvatite sve podatke o karticama tipa Diners, 
-- a zatim o karticama tipa Visa. 

SELECT * FROM KreditneKartice_Diners;

SELECT * 
FROM KreditneKartice_Diners
WHERE Tip = N'Visa'; 
-- 8. Obrišite pogled
DROP VIEW IF EXISTS KreditneKartice_Diners;


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

-- WITH CHECK OPTION 
-- ograničenje (constraint) koje se može dodati na kraj definicije pogleda
-- Njegova svrha je osigurati da se svi INSERT i UPDATE \
-- upiti koji se izvršavaju preko pogleda pridržavaju uvjeta 
-- (filtera) definiranog u tom pogledu.

 --Napravite pogled koji će dohvaćati sve iz tablice Kupac. Možete li napraviti INSERT, UPDATE i 
-- DELETE nekog kupca?

USE AdventureWorksOBP;
GO

-- 1.1 Napravi view
CREATE VIEW dbo.vwKupac_All
AS
SELECT *
FROM dbo.Kupac;
GO

-- 1.2 INSERT preko view-a (radi, ako ispoštuješ NOT NULL stupce)
INSERT INTO dbo.vwKupac_All (Ime, Prezime, GradID)
VALUES (N'Iva', N'Ivić', 13);

-- 1.3 UPDATE preko view-a (radi)
UPDATE dbo.vwKupac_All
SET Ime = N'Ivana'
WHERE IDKupac = SCOPE_IDENTITY();  -- ili konkretan ID

-- 1.4 DELETE preko view-a (radi)
DELETE FROM dbo.vwKupac_All
WHERE IDKupac = SCOPE_IDENTITY();  -- ili konkretan ID

 -- 13. Promijenite prethodni pogled tako da dohvaća sve stupce osim Prezime. 
 -- Možete li napraviti INSERT, UPDATE i DELETE nekog kupca? Obrišite pogled.
ALTER VIEW dbo.vwKupac_All
AS
SELECT IDKupac, Ime, GradID   -- Prezime izostavljeno
FROM dbo.Kupac;
GO

INSERT INTO dbo.vwKupac_All (Ime, GradID)
VALUES (N'Marko', 13);

 -- 14. Napravite pogled koji će dohvaćati ime i prezime kupca te
 -- sve podatke o gradu. a.
CREATE VIEW dbo.vwKupac_Grad13
AS
SELECT IDKupac, Ime, Prezime, GradID
FROM dbo.Kupac
WHERE GradID = 13
WITH CHECK OPTION;    -- ključna stvar
GO
 --Možete li napraviti INSERT grada kroz pogled. Vidi li se kroz pogled?
 INSERT INTO dbo.vwKupac_Grad13 (Ime, Prezime, GradID)
VALUES (N'Lucija', N'Lucić', 13);
INSERT INTO dbo.vwKupac_Grad13 (Ime, Prezime, GradID)
VALUES (N'Ivo', N'Ivić', 99);

 -- WITH CHECK OPTION blokira sve izmjene koje bi rezultirale
-- redom nevidljivim kroz view.

 --c. Možete li napraviti UPDATE ili DELETE grada kroz pogled?
 UPDATE dbo.vwKupac_Grad13
SET GradID = 99
WHERE IDKupac = 2;
 -- Obrišite pogled

 -----------------------SCHEMA BINDING---------------------------------
 --Kada napraviš pogled (VIEW) u T-SQL-u, on po defaultu
 -- nije vezan uz strukturu tablica koje koristi.
--To znači da ako netko kasnije promijeni tablicu 
-- (npr. obriše stupac, preimenuje ga, promijeni tip podataka),
--tvoj view može prestati raditi — ali SQL Server to ne sprječava unaprijed.

-- WITH SCHEMABINDING kaže SQL Serveru:

-- “Ovaj pogled ima čvrstu vezu sa svim tablicama i stupcima koje koristi —
-- ne dopuštaj nikakve promjene u tim tablicama koje bi mogle pokvariti pogled.”
-- NO SCHEMABINDING	Tablice se mogu mijenjati view može postati neispravan.
-- WITH SCHEMABINDING	SQL Server blokira promjene u tablicama koje bi 
-- “slomile” view.

CREATE VIEW dbo.vwKupacSB
WITH SCHEMABINDING
AS
SELECT IDKupac, Ime, Prezime
FROM dbo.Kupac;
GO

ALTER TABLE dbo.Kupac DROP COLUMN Prezime;


EXECUTE sp_helptext vwKupac_All

ALTER VIEW dbo.vwKupac_All
WITH ENCRYPTION
AS
SELECT *
FROM dbo.Kupac;
GO

