-- database_script.sql

USE `{{DB_NAME}}`;

-- Drop and create database if it does not exist
DROP DATABASE IF EXISTS globaltempDB;
CREATE DATABASE IF NOT EXISTS globaltempDB;

USE `{{DB_NAME}}`;

DROP PROCEDURE IF EXISTS nextsequence;
DELIMITER //

CREATE PROCEDURE nextsequence(
    IN sequencenames VARCHAR(255),
    IN node BINARY(16),
    IN cluster BINARY(16)
)
BEGIN
    DECLARE _next TEXT DEFAULT NULL;
    DECLARE _nextlen INT DEFAULT NULL;
    DECLARE _value TEXT DEFAULT NULL;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SHOW ERRORS;
        ROLLBACK;
    END;

    START TRANSACTION;

    IF (sequencenames IS null OR TRIM(sequencenames) = '') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Either a table name or a sequence name is required.';
    END IF;

    SET @database = (SELECT DATABASE());

    iterator:
    LOOP
        IF CHAR_LENGTH(TRIM(sequencenames)) = 0 OR sequencenames IS NULL THEN
            LEAVE iterator;
        END IF;

        SET _next = SUBSTRING_INDEX(sequencenames, ',', 1);
        SET _nextlen = CHAR_LENGTH(_next);
        SET @sequencename = TRIM(_next);
        SET sequencenames = INSERT(sequencenames, 1, _nextlen + 1, '');

        SET @SQL = CONCAT('SELECT EXISTS(SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE ''', @sequencename, ''' AND table_schema = ''globaltempdb'') INTO @e');
        PREPARE stmt FROM @SQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        IF @e = 0 THEN
            SET @SQL = CONCAT('CREATE TABLE globaltempDB.', @sequencename, ' ( ', @sequencename, 'iId bigint not null, ', @sequencename, 'CiId bigint null, seq_db VARCHAR(50) not null )');
            PREPARE stmt FROM @SQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            SET @SQL = CONCAT('INSERT globaltempDB.', @sequencename, ' (', @sequencename, 'iId, ', @sequencename, 'CiId, seq_db) VALUES(0, 0, ''', @database , ''')');
            PREPARE stmt FROM @SQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;

        SET @SQL = CONCAT('UPDATE globaltempDB.', @sequencename, ' SET ', @sequencename, 'iId = LAST_INSERT_ID(', @sequencename, 'iId + 1)');
        PREPARE stmt FROM @SQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET @SQL = CONCAT('SELECT LAST_INSERT_ID() AS ', @sequencename, 'iId, null AS ', @sequencename, 'CiId');
        PREPARE stmt FROM @SQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    COMMIT;
END //

DELIMITER ;

-- Repeat for the second stored procedure 'receivablerequest'

USE `{{DB_NAME}}`;
DROP PROCEDURE IF EXISTS receivablerequest;
DELIMITER //

CREATE PROCEDURE receivablerequest(
    IN `PERIOD` INT
)
BEGIN
    SET @PERIOD = 1;
    SET @BOD = (SELECT CONVERT_TZ(UTC_DATE(), @@session.time_zone, '+00:00'));
    SET @PBEGIN = (SELECT DATE_SUB(@BOD, INTERVAL DAYOFMONTH(@BOD) DAY));
    SET @PEND = (SELECT DATE_ADD(DATE_SUB(@BOD, INTERVAL DAYOFMONTH(@BOD) DAY), INTERVAL 1 MONTH));

    SELECT `receivablerequest`.`receivablerequestId`,
           `receivablerequest`.`comments`,
           `receivablerequest`.`from`,
           `receivablerequest`.`to`,
           `receivablerequest`.`quantity`,
           `receivablerequest`.`v1receivablerequestId`,
           `receivablerequest`.`receivablerequestCiId`,
           `receivablerequest`.`receivablerequestns`,
           `receivablerequest`.`receivablerequestiId`,
           `receivablerequest`.`datecreated`,
           `receivablerequest`.`timestamp`,
           `receivablerequest`.`migrationstate`,
           `receivablerequest`.`datemodified`,
           `receivablerequest`.`requestedbyId`,
           `receivablerequest`.`departmentId`,
           `receivablerequest`.`itemId`,
           `receivablerequest`.`receivablerequestnumber`,
           `statechange`.`state`
    FROM `receivablerequest`
    INNER JOIN statechange ON statechange.statechangeforId = `receivablerequest`.`receivablerequestId`
    WHERE `receivablerequest`.from >= @PBEGIN AND `receivablerequest`.from < @PEND

    UNION ALL

    SELECT uuid() AS `receivablerequestId`,
           'system request for period' AS `comments`,
           CONVERT(@PBEGIN, DATETIME) AS `from`,
           CONVERT(@PEND , DATETIME) AS `to`,
           CASE WHEN consumption.qty IS NULL THEN item.max ELSE consumption.qty END AS `quantity`,
           NULL AS `v1receivablerequestId`,
           NULL AS `receivablerequestCiId`,
           'sys_auto' AS `receivablerequestns`,
           NULL AS `receivablerequestiId`,
           UTC_TIMESTAMP() AS `datecreated`,
           UTC_TIMESTAMP() AS `timestamp`,
           8 AS `migrationstate`,
           UTC_TIMESTAMP() AS `datemodified`,
           NULL AS `requestedbyId`,
           NULL AS `departmentId`,
           `item`.`itemId`,
           NULL AS `receivablerequestnumber`,
           'yet to order' AS `state`
    FROM item
    LEFT JOIN (
        SELECT AVG(saleitem.quantity) AS qty, receiveditem.itemId, department.departmentId
        FROM saleitem
        INNER JOIN receiveditem ON saleitem.billableId = receiveditem.receiveditemId
        INNER JOIN encounter ON encounter.encounterId = saleitem.encounterId
        INNER JOIN department ON encounter.departmentId = department.departmentId
        GROUP BY departmentId
    ) consumption ON item.itemId = `consumption`.`itemId`
    LEFT JOIN (
        SELECT itemId FROM receivablerequest
        INNER JOIN statechange ON statechange.statechangeforId = receivablerequest.receivablerequestId
        WHERE receivablerequest.datecreated >= @PBEGIN AND receivablerequest.datecreated < @PEND
    ) requested ON requested.itemId = item.itemId
    WHERE requested.itemId IS NULL;

END //

DELIMITER ;