---

create database medcenter;
\c medcenter

create table doctors (id serial primary key, passport varchar unique, firstname varchar, lastname varchar, surname varchar, address varchar, phone varchar);

create table contracts (id serial primary key, number varchar unique, sign_date date, years_active int, terminated bool, termination_date date, doctor_id int references doctors(id) not null);

create table services (id serial primary key, name varchar, duration_minutes int, self_cost decimal(10, 2), price decimal(10, 2), doctor_cut decimal(10, 2));

create table patients (id serial primary key, firstname varchar, lastname varchar, surname varchar, address varchar, phone varchar, insurance varchar);

create table appointments (id serial primary key, number varchar unique, creation_date date, appointment_datetime timestamp, completion_status bool, patient_id int references patients(id) not null, service_id int references services(id) not null);

create table doctor_appointment (id serial primary key, doctor_id int references doctors(id), appointment_id int references appointments(id));