-- 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.


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

CREATE TEMPORARY VIEW exp_ver AS SELECT "82" AS expected_version;

.bail ON

SELECT ('The patch only applies to schema version '
    || expected_version
    || ', but the current schema version is '
    || meta_value
    || ', skipping the rest.') AS ''
    FROM hive_meta JOIN exp_ver WHERE meta_key='hive_sql_schema_version' AND meta_value<>expected_version;

INSERT INTO hive_meta (meta_key, meta_value)
    SELECT hm.* FROM hive_meta AS hm JOIN exp_ver WHERE meta_key='hive_sql_schema_version' AND meta_value<>expected_version;

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


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

DROP INDEX beekeeper_signature_idx;
ALTER TABLE beekeeper RENAME TO beekeeper__;

CREATE TABLE beekeeper (
       beekeeper_id      INTEGER        NOT NULL PRIMARY KEY AUTOINCREMENT,
       meadow_host       VARCHAR(255)   NOT NULL,
       meadow_user       VARCHAR(255)   NOT NULL,
       process_id        INTEGER        NOT NULL,
       is_blocked        SMALLINT       NOT NULL DEFAULT 0,
       cause_of_death    VARCHAR(255)   NULL, /* enum('ANALYSIS_FAILED', 'DISAPPEARED', 'JOB_FAILED', 'LOOP_LIMIT', 'NO_WORK', 'TASK_FAILED') NOT NULL */
       sleep_minutes     REAL           NULL,
       analyses_pattern  TEXT           NULL,
       loop_limit        INTEGER        NULL,
       loop_until        VARCHAR(255)   NOT NULL, /* enum('ANALYSIS_FAILURE', 'FOREVER', 'JOB_FAILURE', 'NO_WORK') NOT NULL */
       options           TEXT           NULL,
       meadow_signatures TEXT           NULL
);

CREATE INDEX beekeeper_signature_idx ON beekeeper (meadow_host, meadow_user, process_id);

INSERT INTO beekeeper (beekeeper_id,meadow_host,meadow_user,process_id,cause_of_death,sleep_minutes,analyses_pattern,loop_limit,loop_until,options,meadow_signatures)
    SELECT * FROM beekeeper__;

DROP TABLE beekeeper__;

DROP VIEW IF EXISTS beekeeper_activity;
CREATE VIEW IF NOT EXISTS beekeeper_activity AS
    SELECT b.beekeeper_id, b.meadow_host, b.sleep_minutes, b.loop_limit, b.is_blocked,
           b.cause_of_death, COUNT(*) AS loops_executed,
           MAX(lm.when_logged) AS last_heartbeat,
           STRFTIME('%s', 'now') - STRFTIME('%s', MAX(lm.when_logged)) AS time_since_last_heartbeat,
           ((sleep_minutes * 60) -
                (STRFTIME('%s', 'now') - STRFTIME('%s', 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;

-- ----------------------------------</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 'patched_to_' || meta_value, CURRENT_TIMESTAMP FROM hive_meta WHERE meta_key = 'hive_sql_schema_version';