CREATE TABLE city(
	cityname varchar(50) primary key
);

CREATE TABLE faculty(
	facultyid serial primary key,
	facultyname varchar(255) not null,
	cityname varchar(255) references city(cityname)
);

CREATE TABLE building(
	buildingid serial primary key,
	cityname varchar(255) references city(cityname),
	building_adress varchar(255)
);


CREATE TABLE appuser(
	userid serial unique not null,
	prezime varchar(13) not null,
	ime varchar(200) not null,
	pass varchar(200) not null,
	phone_number varchar(30) not null,
	city_of_residency varchar(50),
	datumragj date
);

CREATE TABLE administrator(
	adminid integer primary key references appuser(userid),
	adminrole varchar(255) not null
);

CREATE TABLE apartment_owner(
	ownerid integer primary key references appuser(userid)
);

CREATE TABLE student(
	studentid integer not null primary key references appuser(userid),
	facultyid integer not null references faculty(facultyid),
	age integer,
	gender varchar(255)
);

CREATE TABLE apartment(
	apartmentid serial primary key,
	buildingid integer references building(buildingid) not null,
	ownerid integer references apartment_owner(ownerid) not null,
	apartment_adress varchar(255) not null,
	apartment_size varchar(255) not null
	
);




CREATE TABLE apartment_agreement(
	apartment_agreementid serial primary key,
	apartmentid integer not null references apartment(apartmentid),
	ownerid integer not null references apartment_owner(ownerid),
	date_signed date not null,
	date_expires date not null,
	apartment_rent integer not null
);




CREATE TABLE post(
	postid serial primary key,
	cityname varchar(255) references city(cityname),
	number_of_roommates integer not null,
	price integer not null,
	date_posted date not null,
	description varchar(255)
);




CREATE TABLE student_post(
	student_postid integer primary key references post(postid),
	studentid integer not null references student(studentid)
);

CREATE TABLE owner_post(
	owner_postid integer not null primary key references post(postid),
	apartmentid integer not null references apartment(apartmentid),
	ownerid integer not null references apartment_owner(ownerid)
);

CREATE TABLE apartment_photos(
	postid integer references owner_post(owner_postid) primary key,
	photosurl varchar(1000)
);

CREATE TABLE moderates_posts_for(
	cityname varchar(255) not null references city(cityname),
	adminid integer not null references administrator(adminid),
	primary key(cityname,adminid)
);

CREATE TABLE student_signs(
	studentid integer not null references student(studentid),
	agreementid integer not null references apartment_agreement(apartment_agreementid),
	primary key(studentid,agreementid)
);