create database Oglasnik;
use Oglasnik;

create table Luge(
	lid int primary key IDENTITY(1,1),
	firstName nvarchar(200),
	lastName nvarchar(200),
	dataRaganje nvarchar(50),
	phone nvarchar(50),
	email nvarchar(200),
	pass nvarchar(100),
	mestoRaganje nvarchar (200),
	profile_pic nvarchar(200) unique,
);

create table Korisnik(
	kid int primary key identity(1,1),
	lid int not null,
	obrazovanie nvarchar(300),
	webPage nvarchar(200),
	opis nvarchar(MAX),
	activate int,

	constraint k_lid foreign key (lid) references Luge(lid)
);

create table Tip(
	tip_id int primary key identity(1,1),
	name nvarchar(500)unique
);

create table Pripaga(
	pid int primary key identity (1,1),
	kid int not null,
	tip_id int not null,
	
	constraint pr_kid Foreign key (kid) references Korisnik(kid),
	constraint pr_tip foreign key (tip_id) references Tip(tip_id)
);

create table Kompanija(
	kompanija_id int primary key identity(1,1),
	name nvarchar(200),
	email nvarchar(300),
	phone nvarchar (50),
	picture varchar(500);
);

create table Oglas(
	oglas_id int primary key identity (1,1),
	kompanija_id int not null,
	naslov nvarchar(100),
	sodrzina nvarchar(MAX),
	dataObjava nvarchar(100),

	constraint og_ko foreign key (kompanija_id) references Kompanija(kompanija_id)
);

create table TipOglas(
	to_id int primary key identity (1,1),
	oglas_id int not null,
	tip_id int not null,

	constraint to_og foreign key (oglas_id) references Oglas(oglas_id),
	constraint to_tip foreign key (tip_id) references Tip(tip_id)
);

create table Administrator(
	aid int primary key identity,
	lid int not null,
	
	constraint admin_luge foreign key (lid) references Luge(lid)
);

create table Problem(
	pid int primary key identity(1,1),
	lid int foreign key references Luge(lid),
	naslov nvarchar(300),
	data nvarchar(150),
	opis nvarchar(MAX),
	isDel int
);

create table Komentari(
	ID int primary key IDENTITY(1,1),
	UID int not null,
	OID int not null,
	sodrzina varchar(500) not null,
	constraint fk_UID Foreign key (UID) references Luge(lid),
	constraint fk_OID Foreign key (OID) references Oglas(oglas_id)
);

create table Poraka(
	ID int primary key IDENTITY(1,1),
	UID1 int not null,
	UID2 int not null,
	sodrzina varchar(500) not null,
	constraint fk_UID1 Foreign key (UID1) references Luge(lid),
	constraint fk_UID2 Foreign key (UID2) references Luge(lid)
);