connect 'jdbc:derby:tmpdb;create=true';
set schema APP;
create table SchemaVersion ( version INTEGER NOT NULL  );
insert into SchemaVersion (version) values (0);
create table BinaryData ( 
  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
  CRC32 BIGINT NOT NULL , 
  data BLOB NOT NULL , 
  CONSTRAINT BinaryData_id_pk PRIMARY KEY(id) 
);
create table MailServers ( 
  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
  port INTEGER NOT NULL , 
  username varchar(80) NOT NULL , 
  protocol varchar(80) NOT NULL , 
  SSLProtocol varchar(10), 
  emailAddress varchar(80) NOT NULL , 
  server varchar(80) NOT NULL , 
  password varchar(80) NOT NULL , 
  CONSTRAINT MailServers_id_pk PRIMARY KEY(id) 
);
create table Mailboxes ( 
  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
  port INTEGER NOT NULL , 
  folder varchar(80) NOT NULL , 
  username varchar(80) NOT NULL , 
  SSLProtocol varchar(10), 
  hostname varchar(80) NOT NULL , 
  storeType varchar(80) NOT NULL , 
  password varchar(80) NOT NULL , 
  timeout INTEGER NOT NULL , 
  MailServerID INTEGER NOT NULL , 
  CONSTRAINT Mailboxes_id_pk PRIMARY KEY(id) 
);
create table MESSAGES ( 
  Message_From varchar(1000), 
  Message_Cc varchar(1000), 
  Message_Subject varchar(1000), 
  Message_ID varchar(256) NOT NULL , 
  Message_Bcc varchar(1000), 
  Message_Date TIMESTAMP, 
  Content_Type varchar(256), 
  MailboxID INTEGER NOT NULL , 
  Search_Text CLOB NOT NULL , 
  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
  Message_To varchar(1000), 
  Display_Text CLOB NOT NULL , 
  Message_Data_ID INTEGER NOT NULL , 
  CONSTRAINT MESSAGES_id_pk PRIMARY KEY(id) 
);
create table ATTACHMENTS ( 
  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
  Inline INTEGER, 
  CRC32 BIGINT NOT NULL , 
  Attachment_Name varchar(256) NOT NULL , 
  Attachment_File varchar(512) NOT NULL , 
  Message_ID INTEGER NOT NULL , 
  Content_Type varchar(256) NOT NULL , 
  CONSTRAINT ATTACHMENTS_id_pk PRIMARY KEY(id) 
);
alter table ATTACHMENTS ADD CONSTRAINT ATTACHMENTS_Message_ID_MESSAGES_ID FOREIGN KEY ( Message_ID ) REFERENCES MESSAGES ( ID );
alter table MESSAGES ADD CONSTRAINT MESSAGES_MailboxID_Mailboxes_ID FOREIGN KEY ( MailboxID ) REFERENCES Mailboxes ( ID );
alter table MESSAGES ADD CONSTRAINT MESSAGES_Message_Data_ID_BinaryData_ID FOREIGN KEY ( Message_Data_ID ) REFERENCES BinaryData ( ID );
alter table Mailboxes ADD CONSTRAINT Mailboxes_MailServerID_MailServers_ID FOREIGN KEY ( MailServerID ) REFERENCES MailServers ( ID );

update SchemaVersion set version=1;

alter table MESSAGES alter Message_To SET DATA TYPE varchar(10000);
alter table MESSAGES alter Message_From SET DATA TYPE varchar(10000);
alter table MESSAGES alter Message_Cc SET DATA TYPE varchar(10000);
alter table MESSAGES alter Message_Bcc SET DATA TYPE varchar(10000);

update SchemaVersion set version=2;

table MailStatistics ( 
  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
  ProcessedCount INTEGER DEFAULT 0 NOT NULL , 
  HourOfDay INTEGER NOT NULL , 
  LastModified TIMESTAMP NOT NULL , 
  RejectedMailCount INTEGER DEFAULT 0 NOT NULL , 
  DayOfWeek INTEGER NOT NULL , 
  CONSTRAINT MailStatistics_id_pk PRIMARY KEY(id) 
);

create table MailStatistics ( 
  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
  ProcessedCount INTEGER DEFAULT 0 NOT NULL , 
  HourOfDay INTEGER NOT NULL , 
  LastModified TIMESTAMP NOT NULL , 
  RejectedMailCount INTEGER DEFAULT 0 NOT NULL , 
  DayOfWeek INTEGER NOT NULL , 
  CONSTRAINT MailStatistics_id_pk PRIMARY KEY(id) 
);
CREATE INDEX MailStatistics_HourOfDay_idx ON MailStatistics(HourOfDay);
CREATE INDEX MailStatistics_DayOfWeek_idx ON MailStatistics(DayOfWeek);
alter table MESSAGES alter CONTENT_TYPE SET DATA TYPE varchar(256);

update SchemaVersion set version=3;

alter table app.messages alter column Message_ID NULL;

CREATE INDEX MESSAGES_Message_ID_idx ON MESSAGES(Message_ID);

update SchemaVersion set version=4;

alter table MESSAGES add filename varchar(256);
alter table MESSAGES add CRC32 BIGINT;
select id,crc32,data from BinaryData;

alter table messages alter column filename NOT NULL;
alter table messages alter column crc32 NOT NULL;
alter table messages alter column mailboxid NULL;
ALTER TABLE MESSAGES DROP CONSTRAINT MESSAGES_message_data_id_BinaryData_id;
alter table messages drop column message_data_id;
drop table BinaryData;

update SchemaVersion set version=6;

create table EmailAddresses ( 
  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
  address varchar(256) NOT NULL , 
  CONSTRAINT EmailAddresses_id_pk PRIMARY KEY(id), 
  CONSTRAINT EmailAddresses_address_uq UNIQUE(address) 
);

CREATE UNIQUE INDEX EmailAddresses_address_idx ON EmailAddresses(address);

create table EmailAddressesToMessages ( 
  MessageID INTEGER NOT NULL , 
  EmailAddressID INTEGER NOT NULL  
);
alter table EmailAddressesToMessages ADD CONSTRAINT EmailAddressesToMessages_MessageID_Messages_ID FOREIGN KEY ( MessageID ) REFERENCES Messages ( ID );
alter table EmailAddressesToMessages ADD CONSTRAINT EmailAddressesToMessages_EmailAddressID_EmailAddresses_ID FOREIGN KEY ( EmailAddressID ) REFERENCES EmailAddresses ( ID );
create table AuthenticationServers ( 
  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
  port INTEGER NOT NULL , 
  protocol varchar(20) NOT NULL , 
  hostname varchar(40) NOT NULL , 
  CONSTRAINT AuthenticationServers_id_pk PRIMARY KEY(id) 
);
alter table Mailboxes add AuthenticationServerID INTEGER;
select id,filename from messages;

alter table MESSAGES drop column message_to;
alter table MESSAGES drop column message_cc;
alter table MESSAGES drop column message_from;


update SchemaVersion set version=7;
