CREATE TABLE Korisnik (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Ime NVARCHAR(30) NOT NULL,
    Prezime NVARCHAR(30) NOT NULL,
    Email NVARCHAR(30) NOT NULL,
    Telefon NVARCHAR(15) NOT NULL,
    Rodjendan DATETIME NOT NULL,
    Spol NVARCHAR(1) NOT NULL,
    GradId INT NOT NULL,
    PoslovnicaId INT NOT NULL
    CONSTRAINT GradKorisnikFK FOREIGN KEY(GradId) REFERENCES Grad(Id),
    CONSTRAINT PoslovnicaKorisnikFK FOREIGN KEY(PoslovnicaId) REFERENCES Poslovnica(Id)
)

CREATE TABLE Trener (
    TrenerId INT PRIMARY KEY,
    PocetakUgovor DATETIME NOT NULL,
    KrajUgovor DATETIME NOT NULL,
    CONSTRAINT KorisnikTrenerFK FOREIGN KEY (TrenerId) REFERENCES Korisnik(Id)
)

CREATE TABLE Klient (
    KlientId INT PRIMARY KEY,
    Opis NVARCHAR(30) NOT NULL,
    Kilaza INT NULL,
    CONSTRAINT KorisnikKlientFK FOREIGN KEY (KlientId) REFERENCES Korisnik(Id)
)

CREATE TABLE Prodavac (
    ProdavacId INT PRIMARY KEY,
    PocetakUgovor DATETIME NOT NULL,
    KrajUgovor DATETIME NOT NULL,
    CONSTRAINT KorisnikProdavacFK FOREIGN KEY (ProdavacId) REFERENCES Korisnik(Id)
)

CREATE TABLE Grad (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Naziv NVARCHAR(30) NOT NULL,
    DrzavaId INT NOT NULL,
    CONSTRAINT Drzava_FK FOREIGN KEY (DrzavaId) REFERENCES Drzava (Id)
)

CREATE TABLE Drzava (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Naziv NVARCHAR(30) NOT NULL
)

CREATE TABLE Poslovnica (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Adresa NVARCHAR(50) NOT NULL,
    GradId INT NOT NULL,
    CONSTRAINT GradPoslovnica_FK FOREIGN KEY (GradId) REFERENCES Grad (Id)
)

CREATE TABLE Program (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Naziv NVARCHAR(50) NOT NULL,
    Cijena INT NOT NULL,
    TrajanjeMjeseci INT NOT NULL,
    TipId INT NOT NULL,
    JelovnikId INT NOT NULL,
    CONSTRAINT ProgramTip_FK FOREIGN KEY (TipId) REFERENCES TipProgram (Id),
    CONSTRAINT ProgramJelovnik_FK FOREIGN KEY (JelovnikId) REFERENCES Jelovnik (Id)
)

CREATE TABLE TipProgram (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Naziv NVARCHAR(30) NOT NULL
)

CREATE TABLE Jelovnik (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Naziv NVARCHAR(30) NOT NULL,
    Opis NVARCHAR(100) NOT NULL,
    Cijena FLOAT NOT NULL
)

CREATE TABLE KlientProgram (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    KlientId INT NOT NULL,
    TrenerId INT NOT NULL,
    ProgramId INT NOT NULL,
    Pocetak DATETIME NOT NULL,
    CONSTRAINT KlientProgramKlientFK FOREIGN KEY(KlientId) REFERENCES dbo.Klient(KlientId),
    CONSTRAINT KlientProgramTrenerFK FOREIGN KEY(TrenerId) REFERENCES dbo.Trener(TrenerId),
    CONSTRAINT KlientProgramProgramFK FOREIGN KEY(ProgramId) REFERENCES dbo.Program(Id)
)

CREATE TABLE ZavrsenProgram (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    KlientProgramId INT NOT NULL,
    Datum DATETIME NOT NULL,
    Ocjena INT NOT NULL,
    CONSTRAINT KlientProgramZavrsenFK FOREIGN KEY(KlientProgramId) REFERENCES dbo.KlientProgram(Id)
)

CREATE TABLE Popust (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    KlientId INT NOT NULL,
    Razlog NVARCHAR(50) NULL,
    Kolicina INT NOT NULL
    CONSTRAINT KlientPopustFK FOREIGN KEY(KlientId) REFERENCES dbo.Klient(KlientId)
)

CREATE TABLE Clanarina (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    KlientId INT NOT NULL,
    Pocetak DATETIME NOT NULL,
    TipCLanarinaId INT NOT NULL,
    CONSTRAINT ClanarinaTipFk FOREIGN KEY(TipCLanarinaId) REFERENCES dbo.TipClanarina(Id)
)

CREATE TABLE TipClanarina (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Naziv NVARCHAR(30) NOT NULL,
    Cijena FLOAT NOT NULL,
    BrojMjeseci INT NOT NULL
)

CREATE TABLE Kategorija (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Naziv NVARCHAR(30) NOT NULL
)

CREATE TABLE Suplement (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Naziv NVARCHAR(30) NOT NULL,
    Cijena FLOAT NOT NULL,
    KategorijaId INT NOT NULL,
    CONSTRAINT SuplementKategorijaID FOREIGN KEY(KategorijaId) REFERENCES dbo.Kategorija(Id)
)

CREATE TABLE Dobavljac (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Naziv NVARCHAR(30) NOT NULL,
    GradId INT NOT NULL,
    CONSTRAINT DObavljacGradId FOREIGN KEY(GradId) REFERENCES dbo.Grad(Id)
)

CREATE TABLE DobavljacSuplement (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    SuplementId INT NOT NULL,
    DobavljacId INT NOT NULL,
    Kolicina INT NOT NULL,
    CONSTRAINT SuplementDobavljacSuplementFk FOREIGN KEY(SuplementId) REFERENCES dbo.Suplement(Id),
    CONSTRAINT SuplementDobavljacDObavljacFk FOREIGN KEY(DobavljacId) REFERENCES dbo.Dobavljac(Id)
)

CREATE TABLE ProdajaSuplemenata (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    SuplementId INT NOT NULL,
    ProdavacId INT NOT NULL,
    KlientId INT NOT NULL,
    Kolicina INT NOT NULL,
    Datum DATETIME NOT NULL,
    CONSTRAINT ProdajaSuplemenataSuplementFK FOREIGN KEY(SuplementId) REFERENCES dbo.Suplement(Id),
    CONSTRAINT ProdajaSuplemenataProdavacFk FOREIGN KEY(ProdavacId) REFERENCES dbo.Prodavac(ProdavacId),
    CONSTRAINT ProdajaSuplemenataKlientFk FOREIGN KEY(KlientId) REFERENCES dbo.Klient(KlientId)
)