# --- Created by Slick DDL
# To stop Slick DDL generation, remove this comment and start using Evolutions

# --- !Ups

create table "COMPANY" ("id" VARCHAR NOT NULL PRIMARY KEY,"name" VARCHAR NOT NULL,"main_contact_id" VARCHAR NOT NULL,"logo_file_id" VARCHAR,"description" VARCHAR,"context" VARCHAR,"employees_count" INTEGER NOT NULL);
create unique index "idx_company_name" on "COMPANY" ("name");
create table "COMPANY_SCOPE" ("id" VARCHAR NOT NULL PRIMARY KEY,"company_id" VARCHAR NOT NULL,"company_scope_prefix" VARCHAR NOT NULL,"name" VARCHAR NOT NULL,"green_number" VARCHAR NOT NULL,"employees_count" INTEGER NOT NULL,"remark" VARCHAR);
create unique index "idx_company_scope_name" on "COMPANY_SCOPE" ("name");
create table "COMPANY_SCOPE_COORDINATOR" ("id" VARCHAR NOT NULL PRIMARY KEY,"company_scope_id" VARCHAR NOT NULL,"psy_coordinator_id" VARCHAR NOT NULL);
create table "CONTACT" ("id" VARCHAR NOT NULL PRIMARY KEY,"company_scope_id" VARCHAR,"label" VARCHAR,"first_name" VARCHAR,"last_name" VARCHAR,"role" VARCHAR,"phone" VARCHAR,"mobile_phone" VARCHAR,"email" VARCHAR,"street" VARCHAR,"city" VARCHAR,"postal_code" VARCHAR);
create table "DBFILE" ("id" VARCHAR NOT NULL PRIMARY KEY,"file_name" VARCHAR NOT NULL,"content_type" VARCHAR NOT NULL,"content" BLOB NOT NULL);
create table "PATIENT" ("id" VARCHAR NOT NULL PRIMARY KEY,"company_scope_id" VARCHAR NOT NULL,"company_scope_index" INTEGER,"patient_validation_status" VARCHAR,"assigned_to_user_id" VARCHAR,"last_folder_event_date" TIMESTAMP,"nb_events" INTEGER,"first_name" VARCHAR,"last_name" VARCHAR,"gender" VARCHAR,"alias" VARCHAR,"street" VARCHAR,"city" VARCHAR,"postal_code" VARCHAR,"phone" VARCHAR,"mobile_phone" VARCHAR,"email" VARCHAR,"age" VARCHAR,"company_seniority" VARCHAR,"employment_contract" VARCHAR,"social_status" VARCHAR,"professional_status" VARCHAR,"job_title" VARCHAR,"psy_synthesis" VARCHAR,"coordinator_remark" VARCHAR);
create table "PATIENT_FOLDER" ("id" VARCHAR NOT NULL PRIMARY KEY,"patient_id" VARCHAR NOT NULL,"creation_date" TIMESTAMP NOT NULL,"status" VARCHAR NOT NULL,"validation_status" VARCHAR NOT NULL,"validation_date" TIMESTAMP,"survey_status" VARCHAR NOT NULL,"psy_remark" VARCHAR,"coordinator_remark" VARCHAR,"reporting_remark" VARCHAR);
create table "PATIENT_FOLDER_EVENT" ("id" VARCHAR NOT NULL PRIMARY KEY,"folder_id" VARCHAR NOT NULL,"psychologist_id" VARCHAR NOT NULL,"date" TIMESTAMP NOT NULL,"duration" BIGINT NOT NULL,"type" VARCHAR NOT NULL,"designation" VARCHAR NOT NULL,"summary" VARCHAR NOT NULL,"conclusion" VARCHAR NOT NULL,"suicidal_risk" VARCHAR NOT NULL,"emergency_service_triggered" BOOLEAN NOT NULL,"coordinator_remark" VARCHAR,"validation_status" VARCHAR NOT NULL,"validation_date" TIMESTAMP);
create table "PATIENT_FOLDER_FLAG" ("folder_id" VARCHAR NOT NULL,"flag" VARCHAR NOT NULL);
create table "PATIENT_FOLDER_SURVEY" ("id" VARCHAR NOT NULL PRIMARY KEY,"folder_id" VARCHAR NOT NULL,"date" TIMESTAMP NOT NULL,"numeric_format" BOOLEAN NOT NULL,"paper_format" BOOLEAN NOT NULL,"human_ressources" BOOLEAN NOT NULL,"chsct" BOOLEAN NOT NULL,"manager" BOOLEAN NOT NULL,"health_service" BOOLEAN NOT NULL,"colleagues" BOOLEAN NOT NULL,"other" BOOLEAN NOT NULL,"remarks" VARCHAR,"how_situation_choice" VARCHAR NOT NULL,"how_situation_evolved_remarks" VARCHAR,"estimate_help_service_choice" VARCHAR,"estimate_help_service_remarks" VARCHAR,"happy_general_service_choice" VARCHAR,"happy_general_service_remarks" VARCHAR,"happy_following_psy_choice" VARCHAR,"happy_following_psy_remarks" VARCHAR,"happy_adequation_psy_choice" VARCHAR,"happy_adequation_psy_remarks" VARCHAR,"happy_means_to_access_choice" VARCHAR,"happy_means_to_access_remarks" VARCHAR,"green_number_favorite_choice" VARCHAR,"green_number_favorite_remarks" VARCHAR,"recommand_closed_service_choice" VARCHAR,"recommand_closed_service_remarks" VARCHAR,"note" INTEGER,"note_zero_to_ten_remarks" VARCHAR,"improved_since_last_call_choice" VARCHAR NOT NULL,"improved_since_last_call_remarks" VARCHAR,"employee_new_accompaniment_choice" VARCHAR,"employee_new_accompaniment_remarks" VARCHAR,"employee_situation_choice" VARCHAR NOT NULL);
create table "PROFILE" ("user_id" VARCHAR NOT NULL,"birthDate" TIMESTAMP,"phone" VARCHAR,"mobilePhone" VARCHAR,"email" VARCHAR,"numeroSiret" VARCHAR,"numeroAdeli" VARCHAR,"hasCabinet" BOOLEAN,"street" VARCHAR,"city" VARCHAR,"postalCode" VARCHAR,"diplome" VARCHAR,"year" TIMESTAMP,"otherTraining" VARCHAR,"competence" VARCHAR);
create table "USER" ("id" VARCHAR NOT NULL PRIMARY KEY,"role" VARCHAR NOT NULL,"login" VARCHAR NOT NULL,"password" VARCHAR NOT NULL,"first_name" VARCHAR NOT NULL,"last_name" VARCHAR NOT NULL);
create unique index "idx_user_login" on "USER" ("login");
alter table "COMPANY" add constraint "LOGO_FILE_ID_FK" foreign key("logo_file_id") references "DBFILE"("id") on update RESTRICT on delete RESTRICT;
alter table "COMPANY" add constraint "MAIN_CONTACT_FK" foreign key("main_contact_id") references "CONTACT"("id") on update RESTRICT on delete RESTRICT;
alter table "COMPANY_SCOPE" add constraint "SCOPE_COMPANY_FK" foreign key("company_id") references "COMPANY"("id") on update RESTRICT on delete CASCADE;
alter table "COMPANY_SCOPE_COORDINATOR" add constraint "COORDINATOR_COMPANY_SCOPE_FK" foreign key("company_scope_id") references "COMPANY_SCOPE"("id") on update RESTRICT on delete CASCADE;
alter table "COMPANY_SCOPE_COORDINATOR" add constraint "COORDINATOR_USER_FK" foreign key("psy_coordinator_id") references "USER"("id") on update RESTRICT on delete CASCADE;
alter table "CONTACT" add constraint "CONTACT_COMPANY_SCOPE_FK" foreign key("company_scope_id") references "COMPANY_SCOPE"("id") on update RESTRICT on delete CASCADE;
alter table "PATIENT" add constraint "PATIENT_COMPANY_SCOPE_FK" foreign key("company_scope_id") references "COMPANY_SCOPE"("id") on update RESTRICT on delete CASCADE;
alter table "PATIENT" add constraint "PATIENT_USER_ASSIGNATION_FK" foreign key("assigned_to_user_id") references "USER"("id") on update RESTRICT on delete SET NULL;
alter table "PATIENT_FOLDER" add constraint "FOLDER_PATIENT_FK" foreign key("patient_id") references "PATIENT"("id") on update RESTRICT on delete CASCADE;
alter table "PATIENT_FOLDER_EVENT" add constraint "EVENT_PSY_FK" foreign key("psychologist_id") references "USER"("id") on update RESTRICT on delete CASCADE;
alter table "PATIENT_FOLDER_FLAG" add constraint "FLAG_FOLDER_FK" foreign key("folder_id") references "PATIENT_FOLDER"("id") on update RESTRICT on delete CASCADE;
alter table "PATIENT_FOLDER_SURVEY" add constraint "SURVEY_FOLDER_FK" foreign key("folder_id") references "PATIENT_FOLDER"("id") on update RESTRICT on delete CASCADE;
alter table "PROFILE" add constraint "PROFILE_USER_FK" foreign key("user_id") references "USER"("id") on update RESTRICT on delete CASCADE;

# --- !Downs

alter table "PROFILE" drop constraint "PROFILE_USER_FK";
alter table "PATIENT_FOLDER_SURVEY" drop constraint "SURVEY_FOLDER_FK";
alter table "PATIENT_FOLDER_FLAG" drop constraint "FLAG_FOLDER_FK";
alter table "PATIENT_FOLDER_EVENT" drop constraint "EVENT_PSY_FK";
alter table "PATIENT_FOLDER" drop constraint "FOLDER_PATIENT_FK";
alter table "PATIENT" drop constraint "PATIENT_COMPANY_SCOPE_FK";
alter table "PATIENT" drop constraint "PATIENT_USER_ASSIGNATION_FK";
alter table "CONTACT" drop constraint "CONTACT_COMPANY_SCOPE_FK";
alter table "COMPANY_SCOPE_COORDINATOR" drop constraint "COORDINATOR_COMPANY_SCOPE_FK";
alter table "COMPANY_SCOPE_COORDINATOR" drop constraint "COORDINATOR_USER_FK";
alter table "COMPANY_SCOPE" drop constraint "SCOPE_COMPANY_FK";
alter table "COMPANY" drop constraint "LOGO_FILE_ID_FK";
alter table "COMPANY" drop constraint "MAIN_CONTACT_FK";
drop table "USER";
drop table "PROFILE";
drop table "PATIENT_FOLDER_SURVEY";
drop table "PATIENT_FOLDER_FLAG";
drop table "PATIENT_FOLDER_EVENT";
drop table "PATIENT_FOLDER";
drop table "PATIENT";
drop table "DBFILE";
drop table "CONTACT";
drop table "COMPANY_SCOPE_COORDINATOR";
drop table "COMPANY_SCOPE";
drop table "COMPANY";

