
--------------------------------------------------
-- 08/01/2019
-- Author: Tony Chen 
-- Following changes are made for validating and metadata parsing of 
-- user input BEAST/BEAST2 data files. 
--------------------------------------------------

-- Move some columns to place realted information together for better visibility. 
ALTER TABLE `userdata` 
  MODIFY COLUMN `LABEL` VARCHAR(1023) AFTER `SOURCE_DOCUMENT_ID`
;

ALTER TABLE `userdata` 
  MODIFY COLUMN `COMMENT` VARCHAR(255) AFTER `VALIDATED`
;

ALTER TABLE `userdata` 
  MODIFY COLUMN `DATATYPE` VARCHAR(255) AFTER `CREATION_DATE`
;

ALTER TABLE `userdata` 
  MODIFY COLUMN `ENTITYTYPE` VARCHAR(255) AFTER `DATATYPE`
;
-- --------------------------------------------------

-- New columns in `userdata` table. 
ALTER TABLE `userdata` 
  ADD COLUMN `USER_ASSIGNED_DATA_FORMAT` VARCHAR(255) NULL AFTER `DATAFORMAT`
;

ALTER TABLE `userdata` 
  ADD COLUMN `PARSE_STATUS` BIGINT NULL AFTER `VALIDATED`
;

ALTER TABLE `userdata` 
  ADD COLUMN `PARSED_DATE` DATETIME NULL AFTER `PARSE_STATUS`
;
-- --------------------------------------------------

-- Rename of some columns for better visibility. 
ALTER TABLE `userdata` 
  CHANGE COLUMN `DATATYPE` `DATA_TYPE` VARCHAR(255) NOT NULL 
;

ALTER TABLE `userdata` 
  CHANGE COLUMN `ENTITYTYPE` `ENTITY_TYPE` VARCHAR(255) NOT NULL 
;

ALTER TABLE `userdata` 
  CHANGE COLUMN `DATAFORMAT` `DATA_FORMAT` VARCHAR(255) NOT NULL 
;
-- --------------------------------------------------

ALTER TABLE `item_metadata` 
  MODIFY COLUMN `FIELD` VARCHAR(100) AFTER `USERDATA_ID`
;
-- --------------------------------------------------

UPDATE `userdata` SET 
    USER_ASSIGNED_DATA_FORMAT = 'UNKNOWN'
;
-- --------------------------------------------------

-- New tables. 
CREATE TABLE zl_data_parse_status 
(
    ID                              BIGINT AUTO_INCREMENT,
    NAME                            VARCHAR(255) NOT NULL,
    TITLE                           VARCHAR(255) NOT NULL,
    ABBREVIATION                    VARCHAR(255) NOT NULL,

    CONSTRAINT pk_zl_data_parse_status_01 PRIMARY KEY (ID),
    CONSTRAINT uq_zl_data_parse_status_01 UNIQUE (NAME),
    CONSTRAINT uq_zl_data_parse_status_02 UNIQUE (TITLE),
    CONSTRAINT uq_zl_data_parse_status_03 UNIQUE (ABBREVIATION)
);

INSERT INTO zl_data_parse_status (ID, NAME, TITLE, ABBREVIATION) VALUES 
  ('1', 'Queued', 'QUEUED', 'Queued'),
  ('2', 'Processing', 'PROCESSING', 'Processing'),
  ('3', 'Succeeded', 'SUCCEEDED', 'Succeeded'),
  ('4', 'Failed', 'FAILED', 'Failed'),
  ('5', 'Unknown', 'UNKNOWN', 'Unknown')
;

CREATE TABLE data_parsing_schedule 
(
    ID                              BIGINT AUTO_INCREMENT,
    USERDATA_ID                     BIGINT NOT NULL, 
    JOB_ID                          VARCHAR(255) NULL, -- UUID
    QUEUED_TIME                     DATETIME NOT NULL,
    SCHEDULED_TIME                  DATETIME NULL,  -- Scheduled time to start the job. 
    START_TIME                      DATETIME NULL,  -- Actual time the job starts. 
    END_TIME                        DATETIME NULL,  -- The time the job execution ends.     
    JOB_STATUS_L                    BIGINT NOT NULL,
    MESSAGE                         LONGTEXT NULL,
    
    CONSTRAINT pk_data_parsing_schedule_01 PRIMARY KEY (ID),

--     CONSTRAINT fk_data_parsing_schedule_01 FOREIGN KEY (USERDATA_ID) REFERENCES userdata (USERDATA_ID) 
--         ON DELETE NO ACTION     ON UPDATE NO ACTION, 
    
    CONSTRAINT fk_data_parsing_schedule_02 FOREIGN KEY (JOB_STATUS_L) REFERENCES zl_data_parse_status (ID) 
        ON DELETE NO ACTION     ON UPDATE NO ACTION 
);
-- --------------------------------------------------


ALTER TABLE `userdata` 
    ADD CONSTRAINT fk_userdata_01 FOREIGN KEY (`PARSE_STATUS`)
    REFERENCES zl_data_parse_status (ID) 
    ON DELETE NO ACTION     ON UPDATE NO ACTION 
;

UPDATE `userdata` SET PARSE_STATUS = '5';
-- --------------------------------------------------
