-- Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
-- Copyright [2016-2021] EMBL-European Bioinformatics Institute
-- 
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
-- 
--      http://www.apache.org/licenses/LICENSE-2.0
-- 
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.


-- ---------------------------------------------------------------------------------------------------

SET @expected_version = 81;

    -- make MySQL stop immediately after it encounters division by zero:
SET SESSION sql_mode='TRADITIONAL';

    -- warn that we detected the schema version mismatch:
SELECT CONCAT(  'The patch only applies to schema version ',
                @expected_version,
                ', but the current schema version is ',
                meta_value,
                ', so skipping the rest.') AS ''
    FROM hive_meta WHERE meta_key='hive_sql_schema_version' AND meta_value<>@expected_version;

    -- cause division by zero only if current version differs from the expected one:
INSERT INTO hive_meta (meta_key, meta_value)
    SELECT 'this_should_never_be_inserted', 1 FROM hive_meta WHERE NOT 1/(meta_key<>'hive_sql_schema_version' OR meta_value=@expected_version);

SELECT CONCAT(  'The patch seems to be compatible with schema version ',
                @expected_version,
                ', applying the patch...') AS '';

    -- Now undo the change so that we could patch potentially non-TRADITIONAL schema:
SET SESSION sql_mode='';

-- ----------------------------------<actual_patch> -------------------------------------------------

ALTER TABLE beekeeper MODIFY COLUMN status ENUM('ALIVE', 'ANALYSIS_FAILED', 'DISAPPEARED', 'JOB_FAILED', 'LOOP_LIMIT', 'NO_WORK', 'TASK_FAILED');
UPDATE beekeeper SET status = NULL WHERE status = 'ALIVE';
ALTER TABLE beekeeper CHANGE COLUMN status cause_of_death ENUM('ANALYSIS_FAILED', 'DISAPPEARED', 'JOB_FAILED', 'LOOP_LIMIT', 'NO_WORK', 'TASK_FAILED');

CREATE OR REPLACE VIEW beekeeper_activity AS
    SELECT b.beekeeper_id, b.meadow_host, b.sleep_minutes, b.loop_limit,
           b.cause_of_death, COUNT(*) AS loops_executed,
           MAX(lm.when_logged) AS last_heartbeat,
           TIMEDIFF(now(), max(lm.when_logged)) AS time_since_last_heartbeat,
           (TIMEDIFF(SEC_TO_TIME(sleep_minutes * 60), TIMEDIFF(now(), max(lm.when_logged)))) < 0 AS is_overdue
    FROM beekeeper b
    LEFT JOIN log_message lm
    ON b.beekeeper_id = lm.beekeeper_id
    GROUP BY b.beekeeper_id;

DROP PROCEDURE IF EXISTS drop_hive_tables;
DELIMITER //
CREATE PROCEDURE drop_hive_tables()
MODIFIES SQL DATA
BEGIN
    DROP VIEW IF EXISTS msg, progress, resource_usage_stats, live_roles, beekeeper_activity;
    DROP TABLE IF EXISTS pipeline_wide_parameters, analysis_stats_monitor, worker_resource_usage, resource_description, analysis_data, job_file, dataflow_target, dataflow_rule, analysis_ctrl_rule, analysis_stats, log_message, accu, job, role, worker, beekeeper, analysis_base, resource_class, hive_meta;
END; //
DELIMITER ;

-- ----------------------------------</actual_patch> -------------------------------------------------


    -- increase the schema version by one:
UPDATE hive_meta SET meta_value=meta_value+1 WHERE meta_key='hive_sql_schema_version';
INSERT INTO hive_meta (meta_key, meta_value) SELECT CONCAT("patched_to_", meta_value), CURRENT_TIMESTAMP FROM hive_meta WHERE meta_key = "hive_sql_schema_version";