SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS resource_conversion CASCADE;

DROP TABLE IF EXISTS tgusage CASCADE;

DROP TABLE IF EXISTS sso CASCADE;

DROP TABLE IF EXISTS running_tasks_parameters CASCADE;

DROP TABLE IF EXISTS running_tasks CASCADE;

DROP TABLE IF EXISTS job_stats CASCADE;

DROP TABLE IF EXISTS job_events CASCADE;

DROP TABLE IF EXISTS cached_items CASCADE;

DROP TABLE IF EXISTS application_preferences CASCADE;

DROP TABLE IF EXISTS applications CASCADE;

DROP TABLE IF EXISTS task_log_messages CASCADE;

DROP TABLE IF EXISTS task_output_source_documents CASCADE;

DROP TABLE IF EXISTS task_output_parameters CASCADE;

DROP TABLE IF EXISTS task_input_source_documents CASCADE;

DROP TABLE IF EXISTS task_input_parameters CASCADE;

DROP TABLE IF EXISTS tool_parameters CASCADE;

DROP TABLE IF EXISTS task_properties CASCADE;

DROP TABLE IF EXISTS tasks CASCADE;

DROP TABLE IF EXISTS task_runs CASCADE;

DROP TABLE IF EXISTS record_fields CASCADE;

DROP TABLE IF EXISTS data_records CASCADE;

DROP TABLE IF EXISTS item_metadata CASCADE;

DROP TABLE IF EXISTS userdata CASCADE;

DROP TABLE IF EXISTS source_documents CASCADE;

DROP TABLE IF EXISTS folder_preferences CASCADE;

DROP TABLE IF EXISTS folders CASCADE;

DROP TABLE IF EXISTS user_group_lookup CASCADE;

DROP TABLE IF EXISTS groups CASCADE;

DROP TABLE IF EXISTS user_preferences CASCADE;

DROP TABLE IF EXISTS users CASCADE;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE users (
  USER_ID bigint auto_increment NOT NULL,
  DEFAULT_GROUP_ID bigint,
  COMMENT varchar(255),
  INSTITUTION varchar(255),
  AREA_CODE varchar(10),
  CITY varchar(100),
  COUNTRY varchar(50),
  EMAIL varchar(200) NOT NULL,
  FIRST_NAME varchar(100) NOT NULL,
  LAST_NAME varchar(100) NOT NULL,
  MAILCODE varchar(10),
  PASSWORD varchar(50) NOT NULL,
  PHONE_NUMBER varchar(20),
  ROLE varchar(50) NOT NULL,
  STATE varchar(50),
  STREET_ADDRESS varchar(255),
  USERNAME varchar(200) NOT NULL,
  WEBSITE_URL varchar(255),
  ZIP_CODE varchar(10),
  ACTIVE tinyint(1) DEFAULT 1 NOT NULL,
  VERSION integer DEFAULT 0 NOT NULL,
  CAN_SUBMIT tinyint(1) DEFAULT 1 NOT NULL,
  LAST_LOGIN datetime,
  UMBRELLA_APPNAME varchar(30) DEFAULT '' NOT NULL,
  ACTIVATION_CODE varchar(50),
  ACTIVATION_SENT datetime,
  DATE_CREATED datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (USER_ID),
  UNIQUE (USERNAME),
  UNIQUE (EMAIL, ROLE, UMBRELLA_APPNAME)
);

CREATE INDEX ACTIVATION ON users (ACTIVATION_SENT);

CREATE TABLE user_preferences (
  USER_ID bigint NOT NULL,
  VALUE varchar(100),
  PREFERENCE varchar(100) NOT NULL,
  PRIMARY KEY (USER_ID, PREFERENCE),
  FOREIGN KEY (USER_ID) REFERENCES users (USER_ID)
);

CREATE TABLE groups (
  GROUP_ID bigint auto_increment NOT NULL,
  ADMINISTRATOR bigint,
  COMMENT varchar(255),
  CREATION_DATE datetime NOT NULL,
  DESCRIPTION varchar(255),
  GROUPNAME varchar(255) NOT NULL,
  VERSION integer DEFAULT 0 NOT NULL,
  PRIMARY KEY (GROUP_ID),
  UNIQUE (GROUPNAME),
  FOREIGN KEY (ADMINISTRATOR) REFERENCES users (USER_ID)
);

ALTER TABLE users ADD FOREIGN KEY (DEFAULT_GROUP_ID) REFERENCES groups (GROUP_ID);

CREATE TABLE user_group_lookup (
  USER_ID bigint NOT NULL,
  GROUP_ID bigint NOT NULL,
  PRIMARY KEY (USER_ID, GROUP_ID),
  FOREIGN KEY (USER_ID) REFERENCES users (USER_ID),
  FOREIGN KEY (GROUP_ID) REFERENCES groups (GROUP_ID)
);

CREATE TABLE folders (
  FOLDER_ID bigint auto_increment NOT NULL,
  GROUP_ID bigint NOT NULL,
  USER_ID bigint NOT NULL,
  ENCLOSING_FOLDER_ID bigint,
  COMMENT varchar(255),
  CREATION_DATE datetime NOT NULL,
  LABEL varchar(255) NOT NULL,
  GROUP_READABLE tinyint(1) DEFAULT 0 NOT NULL,
  WORLD_READABLE tinyint(1) DEFAULT 0 NOT NULL,
  UUID varchar(46) NOT NULL,
  VERSION integer DEFAULT 0 NOT NULL,
  PRIMARY KEY (FOLDER_ID),
  FOREIGN KEY (GROUP_ID) REFERENCES groups (GROUP_ID),
  FOREIGN KEY (USER_ID) REFERENCES users (USER_ID),
  FOREIGN KEY (ENCLOSING_FOLDER_ID) REFERENCES folders (FOLDER_ID),
  UNIQUE (UUID),
  UNIQUE (ENCLOSING_FOLDER_ID, LABEL)
);

CREATE TABLE folder_preferences (
  FOLDER_ID bigint NOT NULL,
  VALUE varchar(100),
  PREFERENCE varchar(100) NOT NULL,
  PRIMARY KEY (FOLDER_ID, PREFERENCE),
  FOREIGN KEY (FOLDER_ID) REFERENCES folders (FOLDER_ID)
);

CREATE TABLE source_documents (
  SOURCE_DOCUMENT_ID bigint auto_increment NOT NULL,
  FILENAME varchar(1023) NOT NULL,
  LENGTH bigint NOT NULL,
  SIGNATURE varchar(255) NOT NULL,
  PRIMARY KEY (SOURCE_DOCUMENT_ID),
  UNIQUE (SIGNATURE)
);

CREATE TABLE userdata (
  USERDATA_ID bigint auto_increment NOT NULL,
  GROUP_ID bigint NOT NULL,
  USER_ID bigint NOT NULL,
  ENCLOSING_FOLDER_ID bigint NOT NULL,
  SOURCE_DOCUMENT_ID bigint NOT NULL,
  COMMENT varchar(255),
  CREATION_DATE datetime NOT NULL,
  LABEL varchar(255) NOT NULL,
  DATAFORMAT varchar(255) NOT NULL,
  DATATYPE varchar(255) NOT NULL,
  ENTITYTYPE varchar(255) NOT NULL,
  VALIDATED tinyint(1) DEFAULT 0 NOT NULL,
  UUID varchar(46) NOT NULL,
  VERSION integer DEFAULT 0 NOT NULL,
  PRIMARY KEY (USERDATA_ID),
  FOREIGN KEY (GROUP_ID) REFERENCES groups (GROUP_ID),
  FOREIGN KEY (USER_ID) REFERENCES users (USER_ID),
  FOREIGN KEY (ENCLOSING_FOLDER_ID) REFERENCES folders (FOLDER_ID),
  FOREIGN KEY (SOURCE_DOCUMENT_ID) REFERENCES source_documents (SOURCE_DOCUMENT_ID),
  UNIQUE (UUID),
  UNIQUE (ENCLOSING_FOLDER_ID, LABEL)
);

CREATE INDEX COMMENT_INDEX ON userdata (COMMENT);

CREATE TABLE item_metadata (
  USERDATA_ID bigint NOT NULL,
  VALUE varchar(255),
  FIELD varchar(100) NOT NULL,
  PRIMARY KEY (USERDATA_ID, FIELD),
  FOREIGN KEY (USERDATA_ID) REFERENCES userdata (USERDATA_ID)
);

CREATE TABLE data_records (
  RECORD_ID bigint auto_increment NOT NULL,
  USERDATA_ID bigint NOT NULL,
  RECORD_TYPE varchar(100) NOT NULL,
  PRIMARY KEY (RECORD_ID),
  FOREIGN KEY (USERDATA_ID) REFERENCES userdata (USERDATA_ID)
);

CREATE TABLE record_fields (
  RECORD_ID bigint NOT NULL,
  RECORD_FIELD varchar(100) NOT NULL,
  VALUE varchar(1023),
  PRIMARY KEY (RECORD_ID, RECORD_FIELD),
  FOREIGN KEY (RECORD_ID) REFERENCES data_records (RECORD_ID)
);

CREATE TABLE tasks (
  TASK_ID bigint auto_increment NOT NULL,
  GROUP_ID bigint NOT NULL,
  USER_ID bigint NOT NULL,
  ENCLOSING_FOLDER_ID bigint NOT NULL,
  COMMENT varchar(255),
  CREATION_DATE datetime NOT NULL,
  LABEL varchar(255) NOT NULL,
  JOBHANDLE varchar(255),
  TOOL_ID varchar(100),
  APPNAME varchar(30) DEFAULT '' NOT NULL,
  VERSION integer DEFAULT 0 NOT NULL,
  PRIMARY KEY (TASK_ID),
  UNIQUE (JOBHANDLE),
  UNIQUE (ENCLOSING_FOLDER_ID, LABEL),
  FOREIGN KEY (GROUP_ID) REFERENCES groups (GROUP_ID),
  FOREIGN KEY (USER_ID) REFERENCES users (USER_ID),
  FOREIGN KEY (ENCLOSING_FOLDER_ID) REFERENCES folders (FOLDER_ID)
);

CREATE TABLE task_runs (
  TASK_ID bigint NOT NULL,
  RUN_NUMBER integer NOT NULL,
  CREATION_DATE datetime NOT NULL,
  OK tinyint(1) DEFAULT 1 NOT NULL,
  STAGE varchar(20) NOT NULL,
  IS_TERMINAL tinyint(1) DEFAULT 0 NOT NULL,
  IS_RESTART tinyint(1) DEFAULT 0 NOT NULL,
  PRIMARY KEY (TASK_ID, RUN_NUMBER),
  FOREIGN KEY (TASK_ID) REFERENCES tasks (TASK_ID)
);

CREATE TABLE task_properties (
  TASK_ID bigint NOT NULL,
  VALUE varchar(4095),
  PROPERTY varchar(100) NOT NULL,
  PRIMARY KEY (TASK_ID, PROPERTY),
  FOREIGN KEY (TASK_ID) REFERENCES tasks (TASK_ID)
);

CREATE TABLE tool_parameters (
  TASK_ID bigint NOT NULL,
  VALUE varchar(1024),
  PARAMETER varchar(100) NOT NULL,
  PRIMARY KEY (TASK_ID, PARAMETER),
  FOREIGN KEY (TASK_ID) REFERENCES tasks (TASK_ID)
);

CREATE TABLE task_input_parameters (
  INPUT_ID bigint auto_increment NOT NULL,
  TASK_ID bigint NOT NULL,
  RUN_NUMBER integer NOT NULL,
  PARAMETER varchar(255) NOT NULL,
  PRIMARY KEY (INPUT_ID),
  FOREIGN KEY (TASK_ID, RUN_NUMBER) REFERENCES task_runs (TASK_ID, RUN_NUMBER)
);

CREATE TABLE task_input_source_documents (
  INPUT_DOCUMENT_ID bigint auto_increment NOT NULL,
  INPUT_ID bigint NOT NULL,
  SOURCE_DOCUMENT_ID bigint NOT NULL,
  DATAFORMAT varchar(255) NOT NULL,
  DATATYPE varchar(255) NOT NULL,
  ENTITYTYPE varchar(255) NOT NULL,
  NAME varchar(255),
  VALIDATED tinyint(1) DEFAULT 0 NOT NULL,
  PRIMARY KEY (INPUT_DOCUMENT_ID),
  FOREIGN KEY (INPUT_ID) REFERENCES task_input_parameters (INPUT_ID),
  FOREIGN KEY (SOURCE_DOCUMENT_ID) REFERENCES source_documents (SOURCE_DOCUMENT_ID)
);

CREATE TABLE task_output_parameters (
  OUTPUT_ID bigint auto_increment NOT NULL,
  TASK_ID bigint NOT NULL,
  RUN_NUMBER integer NOT NULL,
  PARAMETER varchar(255) NOT NULL,
  INTERMEDIATE tinyint(1) DEFAULT 0 NOT NULL,
  PRIMARY KEY (OUTPUT_ID),
  FOREIGN KEY (TASK_ID, RUN_NUMBER) REFERENCES task_runs (TASK_ID, RUN_NUMBER)
);

CREATE TABLE task_output_source_documents (
  OUTPUT_DOCUMENT_ID bigint auto_increment NOT NULL,
  OUTPUT_ID bigint NOT NULL,
  SOURCE_DOCUMENT_ID bigint NOT NULL,
  DATAFORMAT varchar(255) NOT NULL,
  DATATYPE varchar(255) NOT NULL,
  ENTITYTYPE varchar(255) NOT NULL,
  NAME varchar(255),
  VALIDATED tinyint(1) DEFAULT 0 NOT NULL,
  PRIMARY KEY (OUTPUT_DOCUMENT_ID),
  FOREIGN KEY (OUTPUT_ID) REFERENCES task_output_parameters (OUTPUT_ID),
  FOREIGN KEY (SOURCE_DOCUMENT_ID) REFERENCES source_documents (SOURCE_DOCUMENT_ID)
);

CREATE TABLE task_log_messages (
  TASK_ID bigint NOT NULL,
  RUN_NUMBER integer NOT NULL,
  MESSAGE_INDEX integer NOT NULL,
  CREATION_DATE datetime NOT NULL,
  STAGE varchar(20) NOT NULL,
  ERROR tinyint(1) DEFAULT 0 NOT NULL,
  MESSAGE longtext,
  PRIMARY KEY (TASK_ID, RUN_NUMBER, MESSAGE_INDEX),
  FOREIGN KEY (TASK_ID, RUN_NUMBER) REFERENCES task_runs (TASK_ID, RUN_NUMBER)
);

CREATE TABLE applications (
  NAME varchar(30) NOT NULL,
  APP_ID varchar(255) NOT NULL,
  LONGNAME varchar(100) NOT NULL,
  AUTH_TYPE varchar(30) NOT NULL,
  AUTH_USER_ID bigint NOT NULL,
  WEBSITE_URL varchar(255),
  COMMENT varchar(255),
  ACTIVE tinyint(1) NOT NULL,
  DATE_CREATED datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (NAME),
  UNIQUE (APP_ID)
);

CREATE TABLE application_preferences (
  NAME varchar(30) NOT NULL,
  VALUE varchar(100),
  PREFERENCE varchar(100) NOT NULL,
  PRIMARY KEY (NAME, PREFERENCE),
  FOREIGN KEY (NAME) REFERENCES applications (NAME)
);

CREATE TABLE cached_items (
  CACHED_ITEM_ID bigint auto_increment NOT NULL,
  SESSION_ID varchar(255) NOT NULL,
  ATTRIBUTE varchar(255) NOT NULL,
  COMMENT varchar(255),
  VERSION integer DEFAULT 0 NOT NULL,
  VALUE longblob NOT NULL,
  PRIMARY KEY (CACHED_ITEM_ID),
  UNIQUE (SESSION_ID, ATTRIBUTE)
);

CREATE TABLE job_events (
  SE_ID bigint auto_increment NOT NULL,
  RUN_NUMBER integer NOT NULL,
  JOBHANDLE varchar(255) NOT NULL,
  EVENT_DATE datetime NOT NULL,
  TASK_STAGE varchar(20),
  NAME varchar(100) NOT NULL,
  VALUE longtext,
  PRIMARY KEY (SE_ID)
);

CREATE INDEX JOBHANDLE_RUN_NUMBER ON job_events (JOBHANDLE, RUN_NUMBER);

CREATE TABLE job_stats (
  JOBHANDLE varchar(255) NOT NULL,
  RUN_NUMBER integer NOT NULL,
  RESOURCE varchar(255) NOT NULL,
  TASK_ID bigint NOT NULL,
  USER_ID bigint NOT NULL,
  TOOL_ID varchar(100) NOT NULL,
  EMAIL varchar(200) NOT NULL,
  SUBMITTER varchar(1023) DEFAULT 'UNKNOWN' NOT NULL,
  REMOTE_JOB_ID varchar(1023),
  TG_CHARGE_NUMBER varchar(100),
  DATE_ENTERED datetime NOT NULL,
  DATE_SUBMITTED datetime,
  DATE_TERMINATED datetime,
  SU_PREDICTED bigint,
  SU_COMPUTED bigint,
  SU_CHARGED bigint,
  SU_OVERRIDE integer,
  APPNAME varchar(30) DEFAULT '' NOT NULL,
  SPROPS varchar(8000),
  RESRC_CONVRTN_ID bigint,
  PRIMARY KEY (JOBHANDLE, RUN_NUMBER)
);

CREATE INDEX USER_CHARGE_DATE ON job_stats (USER_ID, TG_CHARGE_NUMBER, DATE_ENTERED);

CREATE INDEX RESOURCE_REMOTE_JOB_USER ON job_stats (RESOURCE, REMOTE_JOB_ID, USER_ID);

CREATE INDEX JOB_STATS_RESOURCE ON job_stats (RESOURCE);

CREATE INDEX JOB_STATS_REMOTE_JOB ON job_stats (REMOTE_JOB_ID);

CREATE TABLE running_tasks (
  JOBHANDLE varchar(255) NOT NULL,
  RUN_NUMBER integer NOT NULL,
  RESOURCE varchar(255) NOT NULL,
  TOOL_ID varchar(100) NOT NULL,
  USER_ID bigint NOT NULL,
  TASK_ID bigint NOT NULL,
  SUBMITTER varchar(1023) NOT NULL,
  REMOTE_JOB_ID varchar(1023),
  DATE_ENTERED datetime NOT NULL,
  LOCKED datetime,
  HOSTNAME varchar(200),
  PID bigint,
  STATUS varchar(100) NOT NULL,
  ATTEMPTS integer DEFAULT 0 NOT NULL,
  OUTPUT_DESC varchar(5000),
  COMMANDLINE varchar(5000),
  SPROPS varchar(500),
  PRIMARY KEY (JOBHANDLE, RUN_NUMBER)
);

CREATE TABLE running_tasks_parameters (
  JOBHANDLE varchar(255) NOT NULL,
  RUN_NUMBER integer NOT NULL,
  NAME varchar(100) NOT NULL,
  VALUE varchar(1023),
  PRIMARY KEY (JOBHANDLE, RUN_NUMBER, NAME),
  FOREIGN KEY (JOBHANDLE, RUN_NUMBER) REFERENCES running_tasks (JOBHANDLE, RUN_NUMBER)
);

CREATE TABLE sso (
  SSO_ID bigint auto_increment NOT NULL,
  USER_ID bigint NOT NULL,
  SSO_USERNAME varchar(255) NOT NULL,
  PRIMARY KEY (SSO_ID),
  UNIQUE (SSO_USERNAME),
  FOREIGN KEY (USER_ID) REFERENCES users (USER_ID)
);

CREATE TABLE tgusage (
  TGUSAGE_ID bigint auto_increment NOT NULL,
  RESOURCE varchar(100) NOT NULL,
  JOBID varchar(100) NOT NULL,
  FULL_JOBID varchar(255) NOT NULL,
  FULL_RESOURCE varchar(255) NOT NULL,
  CHARGE integer NOT NULL,
  START_TIME datetime NOT NULL,
  END_TIME datetime NOT NULL,
  SUBMIT_TIME datetime NOT NULL,
  CHARGE_NUMBER varchar(100) NOT NULL,
  WALL_HRS varchar(10) NOT NULL,
  SU integer NOT NULL,
  NODECOUNT integer NOT NULL,
  PROCESSORS integer NOT NULL,
  QUEUE varchar(100) NOT NULL,
  PRIMARY KEY (TGUSAGE_ID),
  UNIQUE (RESOURCE, JOBID, SUBMIT_TIME)
);

CREATE INDEX TGUSAGE_RESOURCE ON tgusage (RESOURCE);

CREATE INDEX TGUSAGE_JOBID ON tgusage (JOBID);

CREATE TABLE resource_conversion (
  ID bigint auto_increment NOT NULL,
  RESOURCE varchar(100) NOT NULL,
  CONVERSION integer DEFAULT 1 NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE (RESOURCE)
);

