drop table if exists workers cascade;
drop table if exists customers cascade;
drop table if exists salaries cascade;
drop table if exists films cascade;
drop table if exists events cascade;
drop table if exists users cascade;
drop table if exists cinemas cascade;
drop table if exists categories cascade;
drop table if exists discounts cascade;
drop table if exists seats cascade;
drop table if exists projections cascade;
drop table if exists projection_rooms cascade;
drop table if exists tickets cascade;
drop table if exists customer_rates_film cascade;
drop table if exists cinema_plays_film cascade;
drop table if exists cinema_organizes_event cascade;
drop table if exists projection_is_played_in_room cascade;
drop table if exists customer_is_interested_in_event cascade;
drop table if exists work_hours_weekly cascade;
drop schema if exists project cascade;

create schema project;


create table users(
	ID_user serial primary key,
	password varchar(200) not NULL,
	first_name varchar(200) not null,
	last_name varchar(200) not null,
	address  varchar(20000) not null,
	contact_number  varchar(20000) not null,
	date_created date not null
); 
create table cinemas(
	ID_cinema serial primary key,
	name varchar(200) not null,
	location varchar(200) not null
);
create table workers(
	ID_worker serial primary key references users(ID_user),
	position varchar(200) not NULL,
	work_hours_from varchar(200) not null,
	work_hours_to varchar(200) not null,
	ID_cinema integer references cinemas(ID_cinema) not null
); 

create table customers(
	ID_customer serial primary key references users(ID_User),
	points integer
);

create table salaries(
	ID_salary serial primary key,
	sum integer not null,
	date_from date not null,
	date_to date,
	ID_worker integer references workers(ID_worker) not null
);
create table films(
	ID_film serial primary key,
	name varchar(200) not null,
	duration integer not null,
	actors varchar(20000) not null,
	genre varchar(2000) not null,
	age_category varchar(2000) not null,
	director varchar(2000) not null,
	description varchar(20000),
	start_date date not null,
	end_date date not null
);
create table events(
	ID_event serial primary key,
	theme varchar(2000),
	duration varchar(2000) not null,
	repeating varchar(200),
	start_date date not null
);
create table discounts(
	ID_discount serial primary key,
	validity date not null,
	code varchar(2000) not null,
	type varchar(2000),
	percent integer not null
);

create table projections(
	ID_projection serial primary key,
	date_time_start date not null,
	type_of_technology varchar(200) not null,
	date_time_end date not null,
	ID_film integer references films(ID_film) not null,
	ID_discount integer references discounts(ID_discount),
	ID_event integer references events(ID_event)
	);
create table projection_rooms(
	ID_room serial primary key,
	number_of_seats integer not null,
	projection_room_number integer not null,
	ID_cinema integer references cinemas(ID_cinema) not null
);
create table categories(
	ID_category serial primary key,
	name varchar(2000) not null,
	extra_amount integer
);

create table seats(
	ID_seat serial primary key,
	seat_number integer not null,
	ID_room integer references projection_rooms(ID_room) not null,
	ID_category integer references categories(ID_category)
);
create table tickets(
	ID_ticket serial primary key,
	price bigint not null,
	ID_customer integer references customers(ID_customer) not null,
	date_reserved date not null,
	ID_projection integer references projections(ID_projection) not null,
	ID_discount integer references discounts(ID_discount),
	ID_seat integer references seats(ID_seat) not null
);

create table customer_rates_film(
ID_customer integer references customers(ID_customer) not null,
ID_film integer references films(ID_film) not null,
rating float not null,
CONSTRAINT pk_customer_film PRIMARY KEY (ID_customer, ID_film)
);

create table cinema_plays_film(
ID_cinema integer references cinemas(ID_cinema),
ID_film integer references films(ID_film),
CONSTRAINT pk_cinema_film PRIMARY KEY (ID_cinema, ID_film)
);

create table cinema_organizes_event(
ID_cinema integer references cinemas(ID_cinema),
ID_event integer references events(ID_event),
CONSTRAINT pk_cinema_event PRIMARY KEY (ID_cinema,ID_event)
);

create table projection_is_played_in_room(
ID_projection integer references projections(ID_projection),
ID_room integer references projection_rooms(ID_room),
CONSTRAINT pk_projection_room PRIMARY KEY (ID_projection,ID_room)
);
create table customer_is_interested_in_event(
id_customer integer references customers(ID_customer),
id_event integer references events(id_event),
constraint ce_customer_event PRIMARY KEY (id_customer,id_event)
);
create table work_hours_weekly(
id_work_hours serial primary key,
id_worker integer references workers(id_worker) not null,
week_number integer not null,
year_num varchar(200) not null,
hours_from timestamp not null,
hours_to timestamp not null,
check_in bool
);