--------------------------------------------------
-- Mar. 01, 2022
-- Author: Tony Chen
--------------------------------------------------

-- Add a new column 'SALT' to 'users' table.
ALTER TABLE `users` 
    ADD COLUMN `SALT` VARCHAR(255) NULL
;
-- --------------------------------------------------

-- Add a new column 'RUN_NUMBER' to 'job_stats' table.
ALTER TABLE `job_stats` 
    ADD COLUMN `RUN_NUMBER` INT NOT NULL DEFAULT '1' 
;
-- --------------------------------------------------

-- Add a new column 'USER_IDENTIFIER' to 'tgusage' table.
ALTER TABLE `tgusage` 
    ADD COLUMN `USER_IDENTIFIER` VARCHAR(255) NULL
;
-- --------------------------------------------------

CREATE TABLE xsede_attr_report_hx
( 
    ID                              BIGINT AUTO_INCREMENT,
    USER_ID                         BIGINT NOT NULL,
    TASK_ID                         BIGINT NOT NULL,
    TASK_RUN_NUMBER                 INTEGER NOT NULL,
    
    XSEDE_JOB_ID                    VARCHAR(255) NULL,
    XSEDE_REPORT_DEBUG_MODE         INTEGER NULL, 
    XSEDE_REPORT_CONTENT            VARCHAR(2000) NULL, -- the content sent to XSEDE 
    XSEDE_RESRC_ENDPOINT            VARCHAR(1000) NULL,
    XSEDE_RESPONSE_CODE             INTEGER NULL,
    XSEDE_RESPONSE_MESSAGE          VARCHAR(255) NULL, 
    XSEDE_RESPONSE_CONTENT          VARCHAR(8000) NULL,
    
    REPORT_NUMBER                   INT NOT NULL,
    QUEUED_TIME                     DATETIME NOT NULL,
    SCHEDULED_TIME                  DATETIME NULL,
    REPORT_TIME                     DATETIME NULL,
    
    NOTE                            VARCHAR(2000) NULL, 
    
    CONSTRAINT pk_xsede_attr_report_hx_01 PRIMARY KEY (ID)
);
-- --------------------------------------------------


-- --------------------------------------------------
-- Tony Chen:
-- Following changes are made to update SU calculations. 
-- --------------------------------------------------

ALTER TABLE `resource_conversion` 
    ADD COLUMN `RESOURCE_FULL_NAME` VARCHAR(255) NOT NULL DEFAULT 'sdsc.edu' AFTER `RESOURCE`
;

ALTER TABLE `resource_conversion` 
    ADD COLUMN `CHARGE_FACTOR` DECIMAL(5,2) NOT NULL DEFAULT '1.00'  AFTER `RESOURCE_FULL_NAME`
;

ALTER TABLE `resource_conversion` 
    CHANGE COLUMN `CONVERSION` `CONVERSION` DECIMAL(5,2) NOT NULL DEFAULT '1.00' 
;

-- CREATE TABLE resource_conversion 
-- (
--     ID                              BIGINT AUTO_INCREMENT,
--     RESOURCE                        VARCHAR(100) NOT NULL,
--     RESOURCE_FULL_NAME              VARCHAR(255) NOT NULL,
--     CHARGE_FACTOR                   DECIMAL(5,2) NOT NULL DEFAULT '1.00',
--     CONVERSION                      DECIMAL(5,2) NOT NULL DEFAULT '1.00',
--     
--     CONSTRAINT pk_resource_conversion_01 PRIMARY KEY (ID),
--     CONSTRAINT uq_resource_conversion_01 UNIQUE (RESOURCE)
-- );


-- Fill in the newly created table, 'resource_conversion.' 
-- All resources have the conversion ratio of 1 (except comet-gpu). 
INSERT INTO resource_conversion (RESOURCE, RESOURCE_FULL_NAME) 
  SELECT JS.RESOURCE, JS.RESOURCE 
    FROM job_stats AS JS 
    GROUP BY JS.RESOURCE 
    ORDER BY JS.RESOURCE ASC 
;


-- Change all non utf8_unicode charset to be utf8_unicode. 
ALTER TABLE tgusage CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE job_stats CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
