
--------------------------------------------------
-- 10/10/2019
-- Author: Tony Chen 
-- Following changes are made for reporting job 
-- attributes to XSEDE utilizing its REST API endpoints.
--------------------------------------------------

ALTER TABLE `applications` 
    CHANGE COLUMN `AUTH_USER_ID` `AUTH_USER_ID` BIGINT(20) NULL 
;

INSERT INTO `applications` (`NAME`, `APP_ID`, `LONGNAME`, `AUTH_TYPE`, `ACTIVE`) VALUES 
    ('PORTAL2', 'cipres_portal2', 'PORTAL2', 'DIRECT', b'1')
;

-- Adminstration Tasks List 
INSERT INTO `application_preferences` (`NAME`, `PREFERENCE`, `VALUE`) VALUES 
    ('PORTAL2', 'administration.tasks.enabled', '1')
;

-- CIPRE Share (SeedMe Lab)
INSERT INTO `application_preferences` (`NAME`, `PREFERENCE`, `VALUE`) VALUES 
    ('PORTAL2', 'cipres.share.auto.login.enabled', '1'),
    ('PORTAL2', 'cipres.share.file.transfer.enabled', '1'),
    ('PORTAL2', 'cipres.share.api.key', '<api-key-assigned-by-SeedMeLab>'),

    -- Both URLs should be verified with SeedMeLab. 
    ('PORTAL2', 'cipres.share.hostname', '<hostname-given-by-SeedMeLab>'),
    ('PORTAL2', 'cipres.share.url', '<hostname-given-by-SeedMeLab>')
;

-- MetaData files parser 
INSERT INTO `application_preferences` (`NAME`, `PREFERENCE`, `VALUE`) VALUES 
    ('PORTAL2', 'data.file.parser.task.enabled', '1'), 
    ('PORTAL2', 'data.file.parser.task.manager.run.interval.sec', '30')
;

-- XSEDE Gateway Attribute reporting
INSERT INTO `application_preferences` (`NAME`, `PREFERENCE`, `VALUE`) VALUES 
    ('PORTAL2', 'xsede.attribute.reporting.api.key', '<gateway-api-key-from-xsede>'),
    ('PORTAL2', 'xsede.attribute.reporting.task.enabled', '0'),
    ('PORTAL2', 'xsede.attribute.reporting.task.manager.run.interval.sec', '10'),
    ('PORTAL2', 'xsede.attribute.reporting.fail.max.retries', '10'),
    ('PORTAL2', 'xsede.attribute.reporting.max.tasks', '35'),
    ('PORTAL2', 'xsede.attribute.reporting.debug.mode', '0')
;


ALTER TABLE `users` 
    ADD COLUMN `UUID` VARCHAR(255) NULL AFTER `USER_ID`
;

ALTER TABLE `resource_conversion` 
    ADD COLUMN `RESOURCE_FULL_NAME` VARCHAR(255) NULL AFTER `RESOURCE`
;


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)

--     CONSTRAINT fk_xsede_attr_report_hx_01 FOREIGN KEY (USER_ID) REFERENCES users (USER_ID) 
--         ON DELETE NO ACTION     ON UPDATE NO ACTION,

--     CONSTRAINT fk_xsede_attr_report_hx_02 FOREIGN KEY (XSEDE_JOB_ID) REFERENCES tgusage (JOBID) 
--         ON DELETE NO ACTION     ON UPDATE NO ACTION 
);
-- --------------------------------------------------

-- Import submitted jobs into xsede_attr_report_hx table. 
INSERT INTO xsede_attr_report_hx 
(USER_ID, TASK_ID, TASK_RUN_NUMBER, XSEDE_JOB_ID, REPORT_NUMBER, QUEUED_TIME, SCHEDULED_TIME) 
SELECT JS.USER_ID, JS.TASK_ID, JS.RUN_NUMBER, JS.REMOTE_JOB_ID, '1', NOW(), NOW()  
FROM job_stats AS JS 
WHERE 
    JS.DATE_SUBMITTED >= '2019/07/01 00:00:00.000' 
    AND JS.DATE_SUBMITTED <= '2019/11/30 23:59:59.999'
    AND JS.REMOTE_JOB_ID IS NOT NULL 
    -- AND LENGTH(JS.REMOTE_JOB_ID) > 0
    AND JS.RESOURCE LIKE 'comet%'
ORDER BY JS.DATE_SUBMITTED ASC, JS.TASK_ID ASC, JS.USER_ID ASC 
; 
-- --------------------------------------------------


