DROP SCHEMA IF EXISTS project CASCADE;

DROP TABLE IF EXISTS project.Bank CASCADE;
DROP TABLE IF EXISTS project.DDV CASCADE;
DROP TABLE IF EXISTS project.Grad CASCADE;
DROP TABLE IF EXISTS project.Klient CASCADE;
DROP TABLE IF EXISTS project.TipDok CASCADE;
DROP TABLE IF EXISTS project.Vraboteni CASCADE;
DROP TABLE IF EXISTS project.Artikal CASCADE;
DROP TABLE IF EXISTS project.ZiroSmetki CASCADE;
DROP TABLE IF EXISTS project.Stavka CASCADE;
DROP TABLE IF EXISTS project.LagerList CASCADE;

CREATE SCHEMA project;

CREATE TABLE project.Bank (
	BankID SERIAL PRIMARY KEY,
  	BankIme VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE project.DDV (
	ddvID SERIAL PRIMARY KEY,
  	ddvVal SMALLINT NOT NULL UNIQUE
);

CREATE TABLE project.Grad (
	GradID SERIAL PRIMARY KEY,
  	GradIme VARCHAR(50) NOT NULL UNIQUE,
  	PostBroj SMALLINT UNIQUE
);

CREATE TABLE project.Klient (
	KlientID SERIAL PRIMARY KEY,
  	KlientIme VARCHAR(100) NOT NULL,
  	Adresa VARCHAR(100),
  	GradID SMALLINT REFERENCES project.Grad ON DELETE SET NULL,
  	Phone VARCHAR(20),
  	EMail VARCHAR(100),
  	EDB CHAR(13) NOT NULL UNIQUE
);

CREATE TABLE project.TipDok (
	TipID SMALLINT PRIMARY KEY,
  	TipIme VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE project.Vraboteni (
	UserID SERIAL PRIMARY KEY,
  	Username VARCHAR(100) NOT NULL UNIQUE,
  	Password VARCHAR(100) NOT NULL,
  	Email VARCHAR(100) NOT NULL,
  	DisplayName VARCHAR(100),
  	isAdmin BOOLEAN
);

CREATE TABLE project.Artikal (
	Artikal_ID SERIAL PRIMARY KEY,
  	Sifra VARCHAR(10) NOT NULL,
  	Artikal_Ime VARCHAR(100) NOT NULL,
  	Cena NUMERIC(10,2) NOT NULL,
  	CenaSoDDV NUMERIC(10,2) NOT NULL,
  	ddvID SMALLINT REFERENCES project.DDV
);

CREATE TABLE project.ZiroSmetki (
	ZiroSmetkiID SERIAL PRIMARY KEY,
  	KlientID SMALLINT REFERENCES project.Klient ON DELETE CASCADE,
  	BankID SMALLINT REFERENCES project.Bank ON DELETE CASCADE,
  	Broj CHAR(15) NOT NULL UNIQUE
);

CREATE TABLE project.Stavka (
	StavkaID SERIAL PRIMARY KEY,
  	TipID SMALLINT REFERENCES project.TipDok,
  	Created_By SMALLINT REFERENCES project.Vraboteni ON DELETE SET NULL,
  	Datum DATE NOT NULL,
  	Broj INTEGER NOT NULL,
  	KlientID SMALLINT REFERENCES project.Klient ON DELETE RESTRICT,
  	Cena_Osnova NUMERIC(10,2) NOT NULL,
  	Cena_DDV NUMERIC(10,2) NOT NULL,
  	Cena_Vkupno NUMERIC(10,2) NOT NULL
);

CREATE TABLE project.LagerList (
	LagerList_ID SERIAL PRIMARY KEY,
  	StavkaID SMALLINT REFERENCES project.Stavka ON DELETE CASCADE,
  	Artikal_ID SMALLINT REFERENCES project.Artikal ON DELETE CASCADE,
  	Kolicina SMALLINT NOT NULL,
  	Avg_Cena NUMERIC(10,2) NOT NULL
);