--ABS-119980 Email refactoring
create sequence seq_message_credentials
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;

--main setup table for credentials
create table message_credentials
(
 message_credentials_id number not null,
 businessunit_id    number not null,
 url_email          varchar2(1000) null,
 url_whatsapp       varchar2(1000) null,
 url_sms            varchar2(1000) null,
 url_email_status   varchar2(1000) null,
 url_whatsapp_status varchar2(1000) null,
 url_sms_status     varchar2(1000) null, 
 key_email          varchar2(1000) null,
 key_whatsapp       varchar2(1000) null,
 key_sms            varchar2(1000) null,
 senderemailaddress varchar2(300) null,
 wallet_path        varchar2(300) null,
 wallet_pwd         varchar2(300) null,
 systemuser_id      number default 1 not null,
 timestamp          date default sysdate not null 
);
alter table message_credentials add constraint uk_message_credentials primary key (message_credentials_id);
alter table message_credentials add constraint pk_message_credentials unique (businessunit_id);
alter table message_credentials add constraint fk_message_credentials_bu foreign key (businessunit_id)
 references businessunit (businessunit_id) on delete cascade;

create sequence seq_message
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;

--main table to process and show all message and the status
create table message
(
 message_id        number not null,
 businessunit_id   number not null,
 message_type      number not null,
 message_source    varchar2(100) not null,
 status            number default 0 not null,
 tobesent_datetime date default sysdate not null,
 creationuser_id   number default 1 not null,
 creationtimestamp date default sysdate not null, 
 systemuser_id     number default 1 not null,
 timestamp         date default sysdate not null
);
alter table message add constraint pk_message primary key (message_id);
alter table message add constraint pk2_message unique (message_id, message_type);
create index xif01_message_bu on message (businessunit_id);
create index xind01_message on message (status, tobesent_datetime);
alter table message add constraint fk_message_businessunit_id foreign key (businessunit_id)
  references businessunit(businessunit_id) on delete cascade;
comment on column message.message_type is '1=mail, 2=sms, 3=whats app';
comment on column message.status is '0=New (default),
1=Sent,
2=Processed,
3=Cancelled,
4=Error';
alter table message add constraint chk_message_01 check (message_type in (1,2,3));

--message log historical message per timestamp--
create table message_log
(
 message_id       number not null,
 message_type     number not null, 
 status           number default 0 not null,
 status_text      varchar2(1000) null,
 systemuser_id    number default 1 not null,
 timestamp        date default sysdate not null
);
alter table message_log add constraint pk_message_log primary key (message_id, timestamp);
alter table message_log add constraint fk_message_log foreign key (message_id, message_type)
  references message(message_id, message_type) on delete cascade;
comment on column message_log.message_type is '1=mail, 2=sms, 3=whats app';
comment on column message_log.status is '0=New (default),
1=Sent,
2=Processed,
3=Cancelled,
4=Error';

--message attachment--
create table message_attachment
(
 message_id        number not null,
 message_type      number not null,
 attachement_seq   number default 1 not null,
 attachementname   varchar2(100) not null,
 attachement_clob  clob null,
 attachement_blob  blob null,
 systemuser_id     number default 1 not null,
 timestamp         date default sysdate not null
);
alter table message_attachment add constraint pk_message_attachment primary key (message_id, attachement_seq);
alter table message_attachment add constraint fk_message_attachment foreign key (message_id, message_type)
  references message(message_id, message_type) on delete cascade;
alter table message_attachment add constraint chk_message_type13 check (message_type in (1,3)); --sms cannot have attachment
alter table message_attachment add constraint chk_message_attachment1 check (attachement_clob is not null or attachement_blob is not null);
comment on column message_attachment.message_type is '1=mail, 3=whats app';

--email message--
create table message_email
(
  message_id     number not null,
  message_type   number default 1 not null, --cannot be changed
  senddate       date default sysdate not null,
  sender         varchar2(300) not null,
  to_            varchar2(1000) not null,
  cc_            varchar2(1000) null,
  bcc            varchar2(1000) null,
  subject        varchar2(1000) not null,
  message        clob not null,
  importance     number default 1 not null, --2=high, 1=Normal ( default ), 0=low
  systemuser_id  number default 1 not null,
  timestamp      date default sysdate not null  
);
alter table message_email add constraint pk_message_email primary key (message_id);
alter table message_email add constraint fk_message_email foreign key (message_id, message_type)
  references message(message_id, message_type) on delete cascade;
alter table message_email add constraint chk_message_email_type1 check (message_type=1);
alter table message_email add constraint chk_message_email_importance2 check (importance in (0,1,2));
comment on column message_email.message_type is '1=mail';
comment on column message_email.importance is '2=high, 1=Normal ( default ), 0=low';

--sms message--
create table message_sms
(
  message_id    number not null,
  message_type  number default 2 not null,  --cannot be changed
  senddate      date default sysdate not null,
  sender        varchar2(20) not null,
  receiver      varchar2(20) not null,
  message       varchar2(1000) not null,
  systemuser_id number default 1 not null,
  timestamp     date default sysdate not null  
);
alter table message_sms add constraint pk_message_sms primary key (message_id);
alter table message_sms add constraint fk_message_sms foreign key (message_id, message_type)
  references message (message_id, message_type) on delete cascade;
alter table message_sms add constraint chk_message_type2 check (message_type=2);
comment on column message_sms.message_type is '2=sms';

--whats app message--
create table message_whatsapp
(
  message_id    number not null,
  message_type  number default 3 not null,  --cannot be changed
  senddate      date default sysdate not null,
  sender        varchar2(20) not null,
  receiver      varchar2(20) not null,
  message       varchar2(1000) not null,
  systemuser_id number default 1 not null,
  timestamp     date default sysdate not null  
);
alter table message_whatsapp add constraint pk_message_whatsapp primary key (message_id);
alter table message_whatsapp add constraint fk_message_whatsapp foreign key (message_id, message_type)
  references message (message_id, message_type) on delete cascade;
alter table message_whatsapp add constraint chk_message_type3 check (message_type=3);
comment on column message_whatsapp.message_type is '3=whats app';

---"optional" table to link a message to a customer---
create table message_customer
(
  message_id    number not null,
  message_type  number not null,
  customer_id   number not null,
  systemuser_id number default 1 not null,
  timestamp     date default sysdate not null
);
alter table message_customer add constraint pk_message_customer primary key (message_id);
alter table message_customer add constraint fk_message_customer_m foreign key (message_id, message_type)
  references message (message_id, message_type) on delete cascade;
create index xif01_message_customer on message_customer (customer_id);  
alter table message_customer add constraint fk_message_customer_c foreign key (customer_id)
  references customer(customer_id) on delete cascade;
comment on column message_customer.message_type is '1=mail, 2=sms, 3=whats app';
