CREATE DATABASE Integralni2509 ON PRIMARY
(
    NAME = 'Integralni2509',
    FILENAME = 'C:\BP2\Integralni2509.mdf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 25%
)
LOG ON
(
    NAME = 'Integralni2509_LOG',
    FILENAME = 'C:\BP2\Integralni2509.ldf'
)

USE Integralni2509;
GO


--1
CREATE TABLE Klijenti (
    KlijentID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    Ime NVARCHAR(50) NOT NULL,
    Prezime NVARCHAR(50) NOT NULL,
    Drzava NVARCHAR(50) NOT NULL,
    Grad NVARCHAR(50) NOT NULL,
    Email NVARCHAR(50) NOT NULL,
    Telefon NVARCHAR(50) NOT NULL
);

CREATE TABLE Izleti (
    IzletID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    Sifra NVARCHAR(10) NOT NULL,
    Naziv NVARCHAR(100) NOT NULL,
    DatumPolaska DATE NOT NULL,
    DatumPovratka DATE NOT NULL,
    Cijena DECIMAL(7, 2) NOT NULL,
    Opis NTEXT
);

CREATE TABLE Prijave (
    IzletID INT NOT NULL FOREIGN KEY (IzletID) REFERENCES Izleti(IzletID),
    KlijentID INT NOT NULL FOREIGN KEY (KlijentID) REFERENCES Klijenti(KlijentID),
    Datum DATETIME NOT NULL,
    BrojOdraslih INT NOT NULL,
    BrojDjece INT NOT NULL,
    CONSTRAINT FUCK PRIMARY KEY(IzletID, KlijentID)
);

--2
/*
iii.    CountryRegion (Name) -> Drzava
iv. Addresss (City) -> Grad
v.  EmailAddress (EmailAddress)  -> Email (Između imena i prezime staviti tačku)
vi. PersonPhone (PhoneNumber) -> Telefon
*/
USE Integralni2509;
GO

INSERT INTO Klijenti
SELECT DISTINCT P.FirstName, P.LastName, SP.Name, A.City, EA.EmailAddress, PP.PhoneNumber
FROM AdventureWorks2017.Person.CountryRegion AS CR JOIN AdventureWorks2017.Person.StateProvince AS SP
    ON CR.CountryRegionCode = SP.CountryRegionCode JOIN AdventureWorks2017.Person.Address AS A
    ON SP.StateProvinceID = A.AddressID JOIN AdventureWorks2017.Sales.SalesOrderHeader AS SOH
    ON A.AddressID = SOH.ShipToAddressID JOIN AdventureWorks2017.Sales.CreditCard AS CC
    ON SOH.CreditCardID = CC.CreditCardID JOIN AdventureWorks2017.Sales.PersonCreditCard AS PCC
    ON CC.CreditCardID = PCC.CreditCardID JOIN AdventureWorks2017.Person.Person AS P
    ON PCC.BusinessEntityID = P.BusinessEntityID JOIN AdventureWorks2017.Person.EmailAddress AS EA
    ON P.BusinessEntityID = EA.BusinessEntityID JOIN AdventureWorks2017.Person.PersonPhone AS PP
    ON P.BusinessEntityID = PP.BusinessEntityID


INSERT INTO Izleti(Sifra, Naziv, DatumPolaska, DatumPovratka, Cijena)
VALUES
    ('AOIF', 'Izlet 1', '19970624', '20080604', 25.2),
    ('AOIH', 'Izlet 2', '19980624', '20090406', 2235.2),
    ('AOIG', 'Izlet 3', '19990624', '20190406', 252.2);

GO
CREATE PROCEDURE UnosPrijave
    @IzletID INT,
    @KlijentID INT,
    @Odrasli INT,
    @Djeca INT
AS
INSERT INTO Prijave
VALUES (@IzletID, @KlijentID, GETDATE(), @Odrasli, @Djeca)

SELECT *
FROM Izleti

SELECT *
FROM Klijenti

SELECT *
FROM Prijave

EXEC UnosPrijave @IzletID = 4, @KlijentID = 1, @Odrasli = 2, @Djeca = 50

CREATE UNIQUE INDEX UNQ_Email
ON Klijenti(Email)

UPDATE Izleti
SET Cijena = Cijena * 0.9
WHERE IzletID IN (
    SELECT I.IzletID
    FROM Izleti AS I JOIN Prijave AS P
        ON I.IzletID = P.IzletID
    GROUP BY I.IzletID
    HAVING COUNT(I.IzletID) < 3
);

/*Kreirati view (pogled) koji prikazuje podatke o izletu: šifra, naziv, datum polaska, datum
povratka i cijena, te ukupan broj prijava na izletu, ukupan broj putnika, ukupan broj odraslih
i ukupan broj djece. Obavezno prilagoditi format datuma (dd.mm.yyyy).*/
GO
CREATE VIEW [VIEW]
AS
SELECT I.Sifra, I.Naziv, I.DatumPolaska, I.DatumPovratka, I.Cijena, COUNT(P.IzletID) AS Total, SUM(P.BrojOdraslih) AS TOT
FROM Izleti AS I JOIN Prijave AS P
    ON I.IzletID = P.IzletID
GROUP BY I.Sifra, I.Naziv, I.DatumPolaska, I.DatumPovratka, I.Cijena

SELECT *
FROM [VIEW];

/*7.    Kreirati uskladištenu proceduru koja će na osnovu unesene šifre izleta prikazivati zaradu od izleta
i to sljedeće kolone: naziv izleta, zarada od odraslih, zarada od djece, ukupna zarada. Popust za djecu
se obračunava 50% na ukupnu cijenu za djecu. Obavezno testirati ispravnost kreirane procedure.*/
GO
CREATE PROCEDURE Sifre
    @Sifra nvarchar(50)
AS
    SELECT I.Naziv, SUM(P.BrojOdraslih) * I.Cijena
    FROM Izleti AS I JOIN Prijave AS P ON I.IzletID = P.IzletID
    WHERE I.Sifra = @Sifra
    GROUP BY I.Naziv, I.Cijena

GO
EXECUTE Sifre 'AOIF'

SELECT *
FROM Prijave
FROM Izleti