/*
 *
 *  Copyright 2017 Netflix, Inc.
 *
 *     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.
 *
 */

SELECT
  CURRENT_TIMESTAMP                       AS '',
  'Inserting column data into jobs table' AS '';

INSERT INTO `jobs` (
  `created`,
  `updated`,
  `entity_version`,
  `unique_id`,
  `name`,
  `genie_user`,
  `version`,
  `description`,
  `genie_user_group`,
  `disable_log_archival`,
  `email`,
  `cpu_requested`,
  `memory_requested`,
  `timeout_requested`,
  `client_host`,
  `user_agent`,
  `num_attachments`,
  `total_size_of_attachments`,
  `std_out_size`,
  `std_err_size`,
  `command_name`,
  `cluster_name`,
  `started`,
  `finished`,
  `status`,
  `status_msg`,
  `host_name`,
  `process_id`,
  `exit_code`,
  `check_delay`,
  `timeout`,
  `memory_used`,
  `archive_location`
) SELECT
    `j`.`created`,
    `j`.`updated`,
    `j`.`entity_version`,
    `j`.`id`,
    `j`.`name`,
    `j`.`genie_user`,
    `j`.`version`,
    `j`.`description`,
    `r`.`group_name`,
    `r`.`disable_log_archival`,
    `r`.`email`,
    `r`.`cpu`,
    `r`.`memory`,
    `r`.`timeout`,
    `m`.`client_host`,
    `m`.`user_agent`,
    `m`.`num_attachments`,
    `m`.`total_size_of_attachments`,
    `m`.`std_out_size`,
    `m`.`std_err_size`,
    `j`.`command_name`,
    `j`.`cluster_name`,
    `j`.`started`,
    `j`.`finished`,
    `j`.`status`,
    `j`.`status_msg`,
    `e`.`host_name`,
    `e`.`process_id`,
    `e`.`exit_code`,
    `e`.`check_delay`,
    `e`.`timeout`,
    `e`.`memory`,
    `j`.`archive_location`
  FROM `jobs_320` `j`
    JOIN `job_requests_320` `r` ON `j`.`id` = `r`.`id`
    JOIN `job_executions_320` e ON `j`.`id` = `e`.`id`
    JOIN `job_metadata_320` `m` ON `j`.`id` = `m`.`id`;

SELECT
  CURRENT_TIMESTAMP                                AS '',
  'Finished inserting column data into jobs table' AS '';

SELECT
  CURRENT_TIMESTAMP                                            AS '',
  'Splitting fields from 3.2.0 jobs table into new jobs table' AS '';

DELIMITER $$
CREATE PROCEDURE GENIE_SPLIT_JOBS_320()
  BEGIN
    DECLARE `done` INT DEFAULT FALSE;
    DECLARE `old_cluster_id` VARCHAR(255)
    CHARSET utf8;
    DECLARE `old_command_id` VARCHAR(255)
    CHARSET utf8;
    DECLARE `old_job_id` VARCHAR(255)
    CHARSET utf8;
    DECLARE `new_command_id` BIGINT(20);
    DECLARE `new_cluster_id` BIGINT(20);

    DECLARE `jobs_cursor` CURSOR FOR
      SELECT
        `id`,
        `cluster_id`,
        `command_id`
      FROM `jobs_320`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` = TRUE;

    OPEN `jobs_cursor`;
    READ_LOOP: LOOP
      START TRANSACTION;
      SET `done` = FALSE;

      FETCH `jobs_cursor`
      INTO `old_job_id`, `old_cluster_id`, `old_command_id`;

      IF `done`
      THEN
        COMMIT;
        LEAVE READ_LOOP;
      END IF;

      SELECT `cl`.`id`
      INTO `new_cluster_id`
      FROM `clusters` `cl`
      WHERE `cl`.`unique_id` = `old_cluster_id` COLLATE utf8_bin;

      SELECT `co`.`id`
      INTO `new_command_id`
      FROM `commands` `co`
      WHERE `co`.`unique_id` = `old_command_id` COLLATE utf8_bin;

      UPDATE `jobs` `j`
      SET `j`.`cluster_id` = `new_cluster_id`, `j`.`command_id` = `new_command_id`
      WHERE `j`.`unique_id` = `old_job_id`;

      COMMIT;
    END LOOP READ_LOOP;
  END $$

DELIMITER ;

CALL GENIE_SPLIT_JOBS_320();
DROP PROCEDURE GENIE_SPLIT_JOBS_320;

SELECT
  CURRENT_TIMESTAMP                                                     AS '',
  'Finished splitting fields from 3.2.0 jobs table into new jobs table' AS '';

SELECT
  CURRENT_TIMESTAMP                                                    AS '',
  'Splitting fields from 3.2.0 job requests table into new jobs table' AS '';

DELIMITER $$
CREATE PROCEDURE GENIE_SPLIT_JOB_REQUESTS_320()
  BEGIN
    DECLARE `done` INT DEFAULT FALSE;
    DECLARE `old_job_id` VARCHAR(255)
    CHARSET utf8;
    DECLARE `applications_json` VARCHAR(2048)
    CHARSET utf8;
    DECLARE `cluster_criterias_json` TEXT CHARSET utf8;
    DECLARE `command_criteria_json` TEXT CHARSET utf8;
    DECLARE `configs_json` TEXT CHARSET utf8;
    DECLARE `dependencies_json` TEXT CHARSET utf8;
    DECLARE `old_job_tags` VARCHAR(10000)
    CHARSET utf8;
    DECLARE `old_setup_file` VARCHAR(1024)
    CHARSET utf8;
    DECLARE `new_job_id` BIGINT(20);
    DECLARE `found_tag_id` BIGINT(20);
    DECLARE `old_command_args` VARCHAR(10000);

    DECLARE `job_request_cursor` CURSOR FOR
      SELECT
        `id`,
        `applications`,
        `cluster_criterias`,
        `command_criteria`,
        `configs`,
        `dependencies`,
        `tags`,
        `setup_file`,
        `command_args`
      FROM `job_requests_320`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` = TRUE;

    OPEN `job_request_cursor`;
    READ_LOOP: LOOP
      START TRANSACTION;
      SET `done` = FALSE;

      FETCH `job_request_cursor`
      INTO
        `old_job_id`,
        `applications_json`,
        `cluster_criterias_json`,
        `command_criteria_json`,
        `configs_json`,
        `dependencies_json`,
        `old_job_tags`,
        `old_setup_file`,
        `old_command_args`;

      IF `done`
      THEN
        COMMIT;
        LEAVE READ_LOOP;
      END IF;

      SELECT `j`.`id`
      INTO `new_job_id`
      FROM `jobs` `j`
      WHERE `j`.`unique_id` = `old_job_id` COLLATE utf8_bin;

      IF `old_setup_file` IS NOT NULL
      THEN
        INSERT IGNORE INTO `files` (`file`) VALUES (`old_setup_file`);

        SELECT `f`.`id`
        INTO @file_id
        FROM `files` `f`
        WHERE `f`.`file` = `old_setup_file`;

        UPDATE `jobs`
        SET `setup_file` = @file_id
        WHERE `id` = `new_job_id`;
      END IF;

      /*
       * COMMAND ARGUMENTS FOR A GIVEN JOB
       */

      SET @argument_order = 0;
      SET @command_args_local = `old_command_args`;
      COMMAND_ARGS_LOOP: WHILE LENGTH(@command_args_local) > 0 DO
        SET @argument = SUBSTRING_INDEX(@command_args_local, ' ', 1);
        SET @command_args_local = TRIM(LEADING @argument FROM @command_args_local);
        SET @command_args_local = TRIM(LEADING ' ' FROM @command_args_local);
        IF LENGTH(@argument) > 0
        THEN
          INSERT INTO `job_command_arguments`
          VALUES (`new_job_id`, @argument, @argument_order);
          SET @argument_order = @argument_order + 1;
        END IF;
      END WHILE COMMAND_ARGS_LOOP;

      /*
       * APPLICATIONS REQUESTED FOR A GIVEN JOB
       */

      SET @applications_requested_local = `applications_json`;
      -- Pull off the brackets
      SET @applications_requested_local = TRIM(LEADING '[' FROM @applications_requested_local);
      SET @applications_requested_local = TRIM(TRAILING ']' FROM @applications_requested_local);

      -- LOOP while nothing left
      SET @application_order = 0;
      APPLICATIONS_REQUESTED_LOOP: WHILE LENGTH(@applications_requested_local) > 0 DO
        SET @application_requested = SUBSTRING_INDEX(@applications_requested_local, '",', 1);
        SET @applications_requested_local = TRIM(LEADING @application_requested FROM @applications_requested_local);
        SET @applications_requested_local = TRIM(LEADING '"' FROM @applications_requested_local);
        SET @applications_requested_local = TRIM(LEADING ',' FROM @applications_requested_local);
        SET @application_requested = TRIM(@application_requested);
        SET @application_requested = TRIM(BOTH '"' FROM @application_requested);
        INSERT INTO `job_applications_requested`
        VALUES (`new_job_id`, @application_requested, @application_order);
        SET @application_order = @application_order + 1;
      END WHILE APPLICATIONS_REQUESTED_LOOP;

      /*
       * CLUSTER CRITERIA (desired cluster tags) FOR A GIVEN JOB
       */

      -- Rip off array brackets []
      SET @cluster_criteria = `cluster_criterias_json`;
      SET @cluster_criteria = TRIM(LEADING '[' FROM @cluster_criteria);
      SET @cluster_criteria = TRIM(TRAILING ']' FROM @cluster_criteria);
      IF LENGTH(@cluster_criteria > 0)
      THEN
        -- Loop through array (keep variable for order starting at 0)
        SET @cluster_criteria_order = 0;
        CLUSTER_CRITERIA_LOOP: WHILE LENGTH(@cluster_criteria) > 0 DO
          -- Create cluster_criterias entry (save ID)
          INSERT INTO `criteria` (`id`) VALUES (DEFAULT);
          SET @criteria_id = LAST_INSERT_ID();

          INSERT INTO `jobs_cluster_criteria` (`job_id`, `criterion_id`, `priority_order`)
          VALUES (`new_job_id`, @criteria_id, @cluster_criteria_order);

          -- Rip off JSON Object tags
          SET @cluster_criteria = TRIM(LEADING '{' FROM @cluster_criteria);
          SET @cluster_criterion = SUBSTRING_INDEX(@cluster_criteria, '}', 1);
          SET @cluster_criteria = TRIM(LEADING @cluster_criterion FROM @cluster_criteria);
          SET @cluster_criteria = TRIM(LEADING '}' FROM @cluster_criteria);
          SET @cluster_criteria = TRIM(LEADING ',' FROM @cluster_criteria);
          SET @cluster_criterion = TRIM(@cluster_criterion);
          -- Rip off "{tags:["
          SET @cluster_criterion = TRIM(LEADING '"tags":[' FROM @cluster_criterion);
          -- Rip off bracket ]
          SET @cluster_criterion = TRIM(TRAILING ']' FROM @cluster_criterion);
          -- Loop through array
          CLUSTER_CRITERION_LOOP: WHILE LENGTH(@cluster_criterion) > 0 DO
            -- Create entry in cluster_criteria_tags using saved id
            SET @criterion_tag = SUBSTRING_INDEX(@cluster_criterion, '",', 1);
            SET @cluster_criterion = TRIM(LEADING @criterion_tag FROM @cluster_criterion);
            SET @cluster_criterion = TRIM(LEADING '"' FROM @cluster_criterion);
            SET @cluster_criterion = TRIM(LEADING ',' FROM @cluster_criterion);
            SET @criterion_tag = TRIM(@criterion_tag);
            SET @criterion_tag = TRIM(BOTH '"' FROM @criterion_tag);

            INSERT IGNORE INTO `tags` (`tag`) VALUES (@criterion_tag);

            SELECT `t`.`id`
            INTO `found_tag_id`
            FROM `tags` `t`
            WHERE `t`.`tag` = @criterion_tag COLLATE utf8_bin;

            INSERT INTO `criteria_tags` (`criterion_id`, `tag_id`) VALUES (@criteria_id, `found_tag_id`);
          END WHILE CLUSTER_CRITERION_LOOP;

          -- Increment order
          SET @cluster_criteria_order = @cluster_criteria_order + 1;
        END WHILE CLUSTER_CRITERIA_LOOP;
      END IF;

      /*
       * COMMAND CRITERIA (desired command tags) FOR A GIVEN JOB
       */

      SET @command_criterion = `command_criteria_json`;
      -- Pull off the brackets
      SET @command_criterion = TRIM(LEADING '[' FROM @command_criterion);
      SET @command_criterion = TRIM(TRAILING ']' FROM @command_criterion);

      IF LENGTH(@command_criterion) > 0
      THEN
        -- Create cluster_criterias entry (save ID)
        INSERT INTO `criteria` (`id`) VALUES (DEFAULT);
        SET @criteria_id = LAST_INSERT_ID();

        UPDATE `jobs` `j`
        SET `j`.`command_criterion` = @criteria_id
        WHERE `j`.`id` = `new_job_id`;

        -- LOOP while nothing left
        COMMAND_CRITERION_LOOP: WHILE LENGTH(@command_criterion) > 0 DO
          SET @criterion_tag = SUBSTRING_INDEX(@command_criterion, '",', 1);
          SET @command_criterion = TRIM(LEADING @criterion_tag FROM @command_criterion);
          SET @command_criterion = TRIM(LEADING '"' FROM @command_criterion);
          SET @command_criterion = TRIM(LEADING ',' FROM @command_criterion);
          SET @criterion_tag = TRIM(@criterion_tag);
          SET @criterion_tag = TRIM(BOTH '"' FROM @criterion_tag);

          INSERT IGNORE INTO `tags` (`tag`) VALUES (@criterion_tag);

          SELECT `t`.`id`
          INTO `found_tag_id`
          FROM `tags` `t`
          WHERE `t`.`tag` = @criterion_tag COLLATE utf8_bin;

          -- Save tag data for later
          INSERT INTO `criteria_tags` (`criterion_id`, `tag_id`) VALUES (@criteria_id, `found_tag_id`);
        END WHILE COMMAND_CRITERION_LOOP;
      END IF;

      /*
       * CONFIG FILES FOR A GIVEN JOB
       */

      SET @configs_local = `configs_json`;
      -- Pull off the brackets
      SET @configs_local = TRIM(LEADING '[' FROM @configs_local);
      SET @configs_local = TRIM(TRAILING ']' FROM @configs_local);

      -- LOOP while nothing left
      CONFIGS_LOOP: WHILE LENGTH(@configs_local) > 0 DO
        SET @config = SUBSTRING_INDEX(@configs_local, '",', 1);
        SET @configs_local = TRIM(LEADING @config FROM @configs_local);
        SET @configs_local = TRIM(LEADING '"' FROM @configs_local);
        SET @configs_local = TRIM(LEADING ',' FROM @configs_local);
        SET @config = TRIM(@config);
        SET @config = TRIM(BOTH '"' FROM @config);

        INSERT IGNORE INTO `files` (`file`) VALUES (@config);

        SELECT `f`.`id`
        INTO @file_id
        FROM `files` `f`
        WHERE `f`.`file` = @config;

        INSERT INTO `jobs_configs` VALUES (`new_job_id`, @file_id);
      END WHILE CONFIGS_LOOP;

      /*
       * DEPENDENCY FILES FOR A GIVEN JOB
       */

      SET @dependencies_local = `dependencies_json`;
      -- Pull off the brackets
      SET @dependencies_local = TRIM(LEADING '[' FROM @dependencies_local);
      SET @dependencies_local = TRIM(TRAILING ']' FROM @dependencies_local);

      -- LOOP while nothing left
      DEPENDENCIES_LOOP: WHILE LENGTH(@dependencies_local) > 0 DO
        SET @dependency = SUBSTRING_INDEX(@dependencies_local, '",', 1);
        SET @dependencies_local = TRIM(LEADING @dependency FROM @dependencies_local);
        SET @dependencies_local = TRIM(LEADING '"' FROM @dependencies_local);
        SET @dependencies_local = TRIM(LEADING ',' FROM @dependencies_local);
        SET @dependency = TRIM(@dependency);
        SET @dependency = TRIM(BOTH '"' FROM @dependency);

        INSERT IGNORE INTO `files` (`file`) VALUES (@dependency);

        SELECT `f`.`id`
        INTO @file_id
        FROM `files` `f`
        WHERE `f`.`file` = @dependency;

        INSERT INTO `jobs_dependencies` VALUES (`new_job_id`, @file_id);
      END WHILE DEPENDENCIES_LOOP;

      /*
       * TAGS FOR A GIVEN JOB
       */

      SET @tags_local = `old_job_tags`;
      IF LENGTH(@tags_local) > 0
      THEN
        SET @new_tags_local = '';
      ELSE
        SET @new_tags_local = NULL;
      END IF;
      TAGS_LOOP: WHILE LENGTH(@tags_local) > 0 DO
        # Tear off the leading |
        SET @tags_local = TRIM(LEADING '|' FROM @tags_local);
        SET @job_tag = SUBSTRING_INDEX(@tags_local, '|', 1);
        SET @tags_local = TRIM(LEADING @job_tag FROM @tags_local);
        SET @tags_local = TRIM(LEADING '|' FROM @tags_local);

        IF @job_tag LIKE 'scheduler.job_name:%'
        THEN
          UPDATE `jobs` `j`
          SET `j`.`grouping` = SUBSTRING_INDEX(@job_tag, 'scheduler.job_name:', -1)
          WHERE `j`.`id` = `new_job_id`;
        ELSEIF @job_tag LIKE 'scheduler.run_id%'
          THEN
            UPDATE `jobs` `j`
            SET `j`.`grouping_instance` = SUBSTRING_INDEX(@job_tag, 'bdp.test.build.name:', -1)
            WHERE `j`.`id` = `new_job_id`;
        ELSEIF @job_tag LIKE 'bdp.test.build.name:%'
          THEN
            UPDATE `jobs` `j`
            SET `j`.`grouping` = SUBSTRING_INDEX(@job_tag, 'bdp.test.build.name:', -1)
            WHERE `j`.`id` = `new_job_id`;
        ELSEIF @job_tag LIKE 'bdp.test.build.number:%'
          THEN
            UPDATE `jobs` `j`
            SET `j`.`grouping_instance` = SUBSTRING_INDEX(@job_tag, 'bdp.test.build.number:', -1)
            WHERE `j`.`id` = `new_job_id`;
        ELSE
          INSERT IGNORE INTO `tags` (`tag`) VALUES (@job_tag);

          SELECT `t`.`id`
          INTO `found_tag_id`
          FROM `tags` `t`
          WHERE `t`.`tag` = @job_tag COLLATE utf8_bin;

          INSERT INTO `jobs_tags` VALUES (`new_job_id`, `found_tag_id`);

          SET @new_tags_local = CONCAT(@new_tags_local, '|', @job_tag, '|');
        END IF;
      END WHILE TAGS_LOOP;

      # For search
      UPDATE `jobs` `j`
      SET `j`.`tags` = @new_tags_local
      WHERE `j`.`id` = `new_job_id`;

      COMMIT;

    END LOOP READ_LOOP;
    CLOSE `job_request_cursor`;
  END $$

DELIMITER ;

CALL GENIE_SPLIT_JOB_REQUESTS_320();
DROP PROCEDURE GENIE_SPLIT_JOB_REQUESTS_320;

SELECT
  CURRENT_TIMESTAMP                                                             AS '',
  'Finished splitting fields from 3.2.0 job requests table into new jobs table' AS '';

SELECT
  CURRENT_TIMESTAMP                           AS '',
  'Loading data into jobs_applications table' AS '';

DELIMITER $$
CREATE PROCEDURE GENIE_LOAD_JOBS_APPLICATIONS_320()
  BEGIN
    DECLARE `done` INT DEFAULT FALSE;
    DECLARE `old_application_id` VARCHAR(255)
    CHARSET utf8;
    DECLARE `old_job_id` VARCHAR(255)
    CHARSET utf8;
    DECLARE `new_application_id` BIGINT(20);
    DECLARE `new_job_id` BIGINT(20);
    DECLARE `old_application_order` INT(11);

    DECLARE `jobs_applications_cursor` CURSOR FOR
      SELECT
        `job_id`,
        `application_id`,
        `application_order`
      FROM `jobs_applications_320`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` = TRUE;

    OPEN `jobs_applications_cursor`;
    READ_LOOP: LOOP
      START TRANSACTION;
      SET `done` = FALSE;

      FETCH `jobs_applications_cursor`
      INTO `old_job_id`, `old_application_id`, `old_application_order`;

      IF `done`
      THEN
        COMMIT;
        LEAVE READ_LOOP;
      END IF;

      SELECT `j`.`id`
      INTO `new_job_id`
      FROM `jobs` `j`
      WHERE `j`.`unique_id` = `old_job_id` COLLATE utf8_bin;

      SELECT `a`.`id`
      INTO `new_application_id`
      FROM `applications` `a`
      WHERE `a`.`unique_id` = `old_application_id` COLLATE utf8_bin;

      INSERT INTO `jobs_applications` VALUES (`new_job_id`, `new_application_id`, `old_application_order`);

      COMMIT;
    END LOOP READ_LOOP;
  END $$

DELIMITER ;

CALL GENIE_LOAD_JOBS_APPLICATIONS_320();
DROP PROCEDURE GENIE_LOAD_JOBS_APPLICATIONS_320;

SELECT
  CURRENT_TIMESTAMP                                    AS '',
  'Finished loading data into jobs_applications table' AS '';

SELECT
  CURRENT_TIMESTAMP                                                     AS '',
  'Finished loading data from old 3.2.0 jobs tables to 3.3.0 job table' AS '';