drop table if exists people cascade;
drop table if exists accused cascade;
drop table if exists witness cascade;
drop table if exists victim cascade;
drop table if exists policeman cascade;
drop table if exists officer cascade;
drop table if exists sector_of_interal_affairs cascade;
drop table if exists police_station cascade;
drop table if exists crime_case cascade;
drop table if exists statements cascade;
drop table if exists type_of_crime cascade;
drop table if exists solved_case cascade;
drop table if exists evidence cascade;
drop table if exists mentions_evidence cascade;
drop table if exists accused_for cascade;
drop table if exists case_belongs_to_type_of_crime cascade;

drop schema if exists project cascade;

create schema project;

create table People (
	pe_id bigint primary key,
	first_name varchar(255) NOT NULL,
	last_name varchar(255) NOT NULL,
	gender varchar(10) NOT NULL,
	address varchar(4000) NOT NULL,
	picture varchar(4000) NOT NULL,
	contact varchar(30) NOT NULL,
	embg char(13) unique,
	date_of_birth date NOT NULL,
	country varchar(255) NOT NULL,
	nationality varchar(255) NOT NULL
);
create table Sector_of_interal_affairs(
	s_id bigint primary key,
	city varchar(255) NOT NULL
);
create table Accused(
	pe_id bigint primary key references people(pe_id)
);
create table Witness(
	pe_id bigint primary key references people(pe_id)
);
create table Victim(
	pe_id bigint primary key references people(pe_id)
);

create table Officer(
	pe_id bigint primary key references people(pe_id),
	o_date_of_employment date NOT NULL,
	o_badge_no bigint NOT NULL,
	o_password varchar(255) NOT NULL
);

create table Police_station (
	p_id bigint primary key,
	p_address varchar(4000) NOT NULL,
	s_id bigint NOT NULL references Sector_of_interal_affairs(s_id),
	pe_id bigint NOT NULL references Officer(pe_id)
);
create table Policeman(
	pe_id bigint primary key references people(pe_id),
	badge_no bigint NOT NULL,
	p_date_of_employment date NOT NULL,
	rank smallint NOT NULL,
	p_id bigint NOT NULL references police_station(p_id),
	p_password varchar(255) NOT NULL
);
create table Crime_case (
	c_id bigint primary key,
	c_name varchar(255) NOT NULL,
	opening_date date NOT NULL,
	c_status varchar(2) NOT NULL,
	p_id bigint NOT NULL references Police_station(p_id)
);
create table Statements (
	s_id bigint primary key,
	statement_date date NOT NULL,
	description varchar(10000) NOT NULL,
	incident_timestamp timestamp NOT NULL,
	incident_place varchar(4000) NOT NULL,
	c_id bigint NOT NULL references Crime_case(c_id),
	pe_id bigint NOT NULL references Policeman(pe_id),
	victim_pe_id bigint NOT  NULL references Victim(pe_id),
	witness_pe_id bigint  NOT NULL references Witness(pe_id)
);
create table Type_of_crime (
	t_id bigint primary key,
	t_name varchar(255) NOT NULL
);
create table Solved_case (
	sc_description varchar(10000) NOT NULL,
	final_document varchar(40000) NOT NULL, -- added as a picture or a scan
	c_id bigint NOT NULL references Crime_case(c_id),
	date_of_closing date NOT NULL,
	constraint solved_case_pkey primary key (c_id,date_of_closing)
);
create table Evidence (
	e_id bigint primary key,
	e_name varchar(255) NOT NULL,
	e_type varchar(255) NOT NULL,
	is_found boolean NOT NULL,
	e_picture varchar(100000) NOT NULL
);
create table Mentions_evidence (
	s_id bigint references Statements(s_id),
	e_id bigint references Evidence(e_id),
	constraint Mentions_evidence_pkey primary key(s_id,e_id)
);
create table Accused_for (
	accused_pe_id bigint references Accused(pe_id),
	c_id bigint references Crime_case(c_id),
	constraint Accused_For_pkey primary key (accused_pe_id, c_id)
);
create table Case_belongs_to_type_of_crime (
	c_id bigint references Crime_case(c_id),
	t_id bigint references Type_of_crime(t_id),
	constraint Case_belongs_to_type_of_crime_pkey primary key (c_id, t_id)
);