SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS Objekt;
DROP TABLE IF EXISTS Vo_Izgradba;
DROP TABLE IF EXISTS Gotov;
DROP TABLE IF EXISTS Nadzor;
DROP TABLE IF EXISTS Podizveduvac;
DROP TABLE IF EXISTS Rabotnik;
DROP TABLE IF EXISTS Dobavuvac;
DROP TABLE IF EXISTS Nabavka;
DROP TABLE IF EXISTS Korisnik;
DROP TABLE IF EXISTS Sopstvenik;
DROP TABLE IF EXISTS Raboti_Na;
DROP TABLE IF EXISTS Raboti_Za;
DROP TABLE IF EXISTS Nadgleduva;
DROP TABLE IF EXISTS Pobaruva;
DROP TABLE IF EXISTS Nameneta_Za;
DROP TABLE IF EXISTS Odobruva;
DROP TABLE IF EXISTS Oddrzuva;
DROP TABLE IF EXISTS Ispolnuva;



CREATE TABLE Objekt (
	Objekt_ID INT AUTO_INCREMENT UNIQUE NOT NULL,
	Tip VARCHAR(100),
	Adresa VARCHAR(100),
	CONSTRAINT Objekt PRIMARY KEY (Objekt_ID)
);

CREATE TABLE Vo_Izgradba (
	Objekt_ID INT  NOT NULL UNIQUE,
	Budzet FLOAT,
	CONSTRAINT Vo_Izgradba FOREIGN KEY (Objekt_ID) REFERENCES Objekt (Objekt_ID)
);

CREATE TABLE Gotov (
	Objekt_ID INT AUTO_INCREMENT NOT NULL UNIQUE,
	CONSTRAINT Gotov FOREIGN KEY (Objekt_ID) REFERENCES Objekt (Objekt_ID)
);

CREATE TABLE Nadzor (
    UID INT NOT NULL UNIQUE,
	Nadzor_Ime VARCHAR(50) NOT NULL,
	Plata INT,
	Kvalifikacii VARCHAR(50),
	Firma VARCHAR(50),
    CONSTRAINT Nadzor PRIMARY KEY (UID),
	CONSTRAINT Nadzor FOREIGN KEY (UID) REFERENCES Korisnik (UID)
);

CREATE TABLE Podizveduvac (
	Podizveduvac_Ime VARCHAR(50) NOT NULL UNIQUE,
	Struka VARCHAR(50) NOT NULL,
	Br_vraboteni INT NOT NULL,
	Saatnica FLOAT NOT NULL,
	UID INT NOT NULL UNIQUE,
	CONSTRAINT Podizveduvac PRIMARY KEY (UID),
	CONSTRAINT Podizveduvac FOREIGN KEY (UID) REFERENCES Korisnik (UID)
);

CREATE TABLE Rabotnik (
	Maticen_br VARCHAR(13) NOT NULL UNIQUE,
	Ime VARCHAR(50) NOT NULL,
	Seniornost VARCHAR(50) NOT NULL,
	CONSTRAINT Rabotnik PRIMARY KEY (Maticen_br)
);

CREATE TABLE Dobavuvac (
	Dobavuvac_Ime VARCHAR(50) NOT NULL UNIQUE,
	Klasa VARCHAR(50) NOT NULL,
	Tip VARCHAR(50) NOT NULL,
	Poteklo VARCHAR(50) NOT NULL,
	CONSTRAINT Dobavuvac PRIMARY KEY (Dobavuvac_Ime)
);

CREATE TABLE Nabavka (
	Nabavka_ID INT AUTO_INCREMENT NOT NULL UNIQUE,
	Datum DATE NOT NULL,
	Cena FLOAT NOT NULL,
	Kolicina FLOAT NOT NULL,
	Predmet VARCHAR(100) NOT NULL,
	CONSTRAINT Nabavka PRIMARY KEY (Nabavka_ID)
);

CREATE TABLE Korisnik(
	UID INT AUTO_INCREMENT NOT NULL UNIQUE,
	Username VARCHAR(50) NOT NULL UNIQUE,
	Password VARCHAR(72) NOT NULL,
	Privilegii INT NOT NULL,
	CONSTRAINT Korisnik PRIMARY KEY (UID)
);

CREATE TABLE Sopstvenik(
	UID INT AUTO_INCREMENT NOT NULL UNIQUE,
	CONSTRAINT Sopstvenik FOREIGN KEY (UID) REFERENCES Korisnik (UID)
);

CREATE TABLE Raboti_Na (
    Objekt_ID INT,
    UID_p INT NOT NULL,
    Od_vreme DATE,
    Do_vreme DATE,
    PRIMARY KEY (Objekt_ID, UID_p),
    FOREIGN KEY (Objekt_ID) REFERENCES Objekt(Objekt_ID),
    FOREIGN KEY (UID_p) REFERENCES Podizveduvac(UID)
);

CREATE TABLE Raboti_Za (
    Maticen_br VARCHAR(13),
    UID_p INT NOT NULL,
    PRIMARY KEY (Maticen_br, UID_p),
    FOREIGN KEY (Maticen_br) REFERENCES Rabotnik(Maticen_br),
    FOREIGN KEY (UID_p) REFERENCES Podizveduvac(UID)
);

CREATE TABLE Nadgleduva (
    Objekt_ID INT,
    UID_n INT NOT NULL,
    PRIMARY KEY (Objekt_ID, UID_n),
    FOREIGN KEY (Objekt_ID) REFERENCES Objekt(Objekt_ID),
    FOREIGN KEY (UID_n) REFERENCES Nadzor(UID)
);

CREATE TABLE Pobaruva (
    Nabavka_ID INT,
    UID_p INT NOT NULL,
    PRIMARY KEY (Nabavka_ID, UID_p),
    FOREIGN KEY (Nabavka_ID) REFERENCES Nabavka(Nabavka_ID),
    FOREIGN KEY (UID_p) REFERENCES Podizveduvac(UID)
);

CREATE TABLE Odobruva (
    Nabavka_ID INT,
    UID_n INT NOT NULL,
    Datum_Odobruvanje DATE,
    PRIMARY KEY (Nabavka_ID, UID_n),
    FOREIGN KEY (Nabavka_ID) REFERENCES Nabavka(Nabavka_ID),
    FOREIGN KEY (UID_n) REFERENCES Nadzor(UID)
);

CREATE TABLE Nameneta_Za (
    Nabavka_ID INT,
    Objekt_ID INT,
    PRIMARY KEY (Nabavka_ID, Objekt_ID),
    FOREIGN KEY (Nabavka_ID) REFERENCES Nabavka(Nabavka_ID),
    FOREIGN KEY (Objekt_ID) REFERENCES Objekt(Objekt_ID)
);

CREATE TABLE Ispolnuva (
    Nabavka_ID INT,
    Dobavuvac_Ime VARCHAR(50),
    PRIMARY KEY (Nabavka_ID, Dobavuvac_Ime),
    FOREIGN KEY (Nabavka_ID) REFERENCES Nabavka(Nabavka_ID),
    FOREIGN KEY (Dobavuvac_Ime) REFERENCES Dobavuvac(Dobavuvac_Ime)
);

CREATE TABLE Oddrzuva (
    Objekt_ID INT,
    UID_p INT NOT NULL,
    Od_vreme DATE,
    Do_vreme DATE,
    PRIMARY KEY (Objekt_ID, UID_p, Od_vreme),
    FOREIGN KEY (Objekt_ID) REFERENCES Objekt(Objekt_ID),
    FOREIGN KEY (UID_p) REFERENCES Podizveduvac(UID)
);

SET FOREIGN_KEY_CHECKS = 1;