create.sql

create table tbl_appointment (appointment_type_id integer not null, area_id integer not null, date TIMESTAMP WITH TIME ZONE not null, end_time TIME WITH TIME ZONE not null, start_time TIME WITH TIME ZONE not null, client_id bigint not null, id bigint generated by default as identity, professional_id bigint not null, status varchar(10) not null check (status in ('PRESENT','ABSENT','CANCEL','OPEN')), comments varchar(1024), primary key (id));
create table tbl_appointment_type (id integer generated by default as identity, type varchar(255), primary key (id));
create table tbl_area (id integer generated by default as identity, name varchar(255), primary key (id));
create table tbl_area_professional (area_id integer not null, professional_id bigint not null, primary key (area_id, professional_id));
create table tbl_client (date_of_birth date, person_id bigint not null, comments varchar(255), primary key (person_id));
create table tbl_person (id bigint generated by default as identity, phone varchar(20), name varchar(255), primary key (id));
create table tbl_professional (active boolean not null, person_id bigint not null, primary key (person_id));
create table tbl_role (id integer generated by default as identity, role varchar(255) check (role in ('ROLE_ADMIN','ROLE_OPERATOR')), primary key (id));
create table tbl_user (person_id bigint not null, email varchar(255) not null unique, password varchar(255), primary key (person_id));
create table tbl_user_role (role_id integer not null, user_id bigint not null, primary key (role_id, user_id));
create table tbl_work_schedule_item (day_of_week integer, end_time TIME WITH TIME ZONE, slot_size integer, slots integer, start_time TIME WITH TIME ZONE, id bigint generated by default as identity, professional_id bigint, primary key (id));
alter table if exists tbl_appointment add constraint FKehutpu22rmfam8t4raurkwpis foreign key (appointment_type_id) references tbl_appointment_type;
alter table if exists tbl_appointment add constraint FKhjyakw183cxebiqaw9lwu60ht foreign key (area_id) references tbl_area;
alter table if exists tbl_appointment add constraint FKcxcaw6r3vas3hpphlwrstkg3n foreign key (client_id) references tbl_client;
alter table if exists tbl_appointment add constraint FKd2qv82ltxt451xuweu0cjvdl5 foreign key (professional_id) references tbl_professional;
alter table if exists tbl_area_professional add constraint FKkh6k14rrg3xa3rvv9aymn52me foreign key (area_id) references tbl_area;
alter table if exists tbl_area_professional add constraint FKbducqqoevuafwynqky5reyuro foreign key (professional_id) references tbl_professional;
alter table if exists tbl_client add constraint FK3r3obc5kd90k4kc87dgg9e74j foreign key (person_id) references tbl_person;
alter table if exists tbl_professional add constraint FKbpqdpcg7cfkrko1vbmgyvbtx foreign key (person_id) references tbl_person;
alter table if exists tbl_user add constraint FKbi8yrqu0dd3dfce2ej9uadqoc foreign key (person_id) references tbl_person;
alter table if exists tbl_user_role add constraint FK6phlytlf1w3h9vutsu019xor5 foreign key (role_id) references tbl_role;
alter table if exists tbl_user_role add constraint FKggc6wjqokl2vlw89y22a1j2oh foreign key (user_id) references tbl_user;
alter table if exists tbl_work_schedule_item add constraint FKnesd3mshtwm1bytki3iuw2xs foreign key (professional_id) references tbl_professional;