DROP   PROCEDURE _Base_Messaging;
DELIMITER //
CREATE PROCEDURE _Base_Messaging
(
    $MESSAGE_ID                  INT,
    $order                       TEXT,
    $limit                       INT,
    $offset                      INT
)
SQL SECURITY INVOKER
BEGIN

    CALL CheckLimitOffset( $limit, $offset );

    SELECT   *
    FROM     base_messaging
    WHERE    message_deleted = 0
    AND     (MESSAGE_ID      = $MESSAGE_ID OR $MESSAGE_ID = 0)
    ORDER BY message_created
    LIMIT    $limit
    OFFSET   $offset;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Messaging_Replace;
DELIMITER //
CREATE PROCEDURE Base_Messaging_Replace
(
    $USER                        INT,
    $MESSAGE_ID                  INT,
    $message_guid                TEXT,
    $message_type                TEXT,
    $message_from                TEXT,
    $message_to                  TEXT,
    $message_cc                  TEXT,
    $message_bcc                 TEXT,
    $message_reply_to            TEXT,
    $message_subject             TEXT,
    $message_tags                TEXT,
    $message_content_txt64       LONGTEXT,
    $message_content_htm64       LONGTEXT,
    $message_send_at             DATETIME,
    $message_sent_at             DATETIME,
    $message_response_code       TEXT,
    $message_last_send_at        DATETIME,
    $message_last_sent_at        DATETIME,
    $message_last_response_code  TEXT
)
SQL SECURITY INVOKER
BEGIN

    IF @@read_only THEN

        CALL Error( 'READ_ONLY' );

    ELSE

        IF NOT $MESSAGE_ID THEN

            REPLACE INTO base_messaging
            ( USER,  MESSAGE_ID)
            VALUES
            ($USER, $MESSAGE_ID);

            SET $MESSAGE_ID = LAST_INSERT_ID();

        END IF;

        UPDATE base_messaging
        SET
            message_guid               = $message_guid,
            message_type               = $message_type,
            message_from               = $message_from,
            message_to                 = $message_to,
            message_cc                 = $message_cc,
            message_bcc                = $message_bcc,
            message_reply_to           = $message_reply_to,
            message_subject            = $message_subject,
            message_tags               = $message_tags,
            message_content_txt64      = $message_content_txt64,
            message_content_htm64      = $message_content_htm64,
            message_send_at            = $message_send_at,
            message_sent_at            = $message_sent_at,
            message_response_code      = $message_response_code,
            message_last_send_at       = $message_last_send_at,
            message_last_sent_at       = $message_last_sent_at,
            message_last_response_code = $message_last_response_code

        WHERE message_deleted            = 0
        AND   MESSAGE_ID                 = $MESSAGE_ID;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE _Base_Messaging_Save;
DELIMITER //
CREATE PROCEDURE _Base_Messaging_Save
(
    $MESSAGE_ID                INT,
    $name                      TEXT,
    $value                     TEXT
)
SQL SECURITY INVOKER
BEGIN

    IF @@read_only THEN

        CALL Error( 'READ_ONLY' );

    ELSE

        CASE $name
        WHEN 'message_send_at'            THEN UPDATE base_messaging SET message_send_at            = $value WHERE MESSAGE_ID = MESSAGE_ID AND message_deleted = 0;
        WHEN 'message_sent_at'            THEN UPDATE base_messaging SET message_sent_at            = $value WHERE MESSAGE_ID = MESSAGE_ID AND message_deleted = 0;
        WHEN 'message_response_code'      THEN UPDATE base_messaging SET message_response_code      = $value WHERE MESSAGE_ID = MESSAGE_ID AND message_deleted = 0;
        WHEN 'message_last_send_at'       THEN UPDATE base_messaging SET message_last_send_at       = $value WHERE MESSAGE_ID = MESSAGE_ID AND message_deleted = 0;
        WHEN 'message_last_sent_at'       THEN UPDATE base_messaging SET message_last_sent_at       = $value WHERE MESSAGE_ID = MESSAGE_ID AND message_deleted = 0;
        WHEN 'message_last_response_code' THEN UPDATE base_messaging SET message_last_response_code = $value WHERE MESSAGE_ID = MESSAGE_ID AND message_deleted = 0;
        ELSE

            CALL Error( 'Base_messaging_Save: INVALID_CASE_OPTION' );

        END CASE;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE _Base_Messaging_Delete;
DELIMITER //
CREATE PROCEDURE _Base_Messaging_Delete
(
    $MESSAGE_ID  INT
)
SQL SECURITY INVOKER
BEGIN

    IF @@read_only THEN

        CALL Error( 'READ_ONLY' );

    ELSE

        UPDATE base_messaging
        SET
            message_deleted = NOW()

        WHERE message_deleted = 0
        AND   MESSAGE_ID      = $MESSAGE_ID;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE _Base_Messaging_Create;
DELIMITER //
CREATE PROCEDURE _Base_Messaging_Create
(
    $USER                        INT,
    $message_type                TEXT,
    $message_from                TEXT,
    $message_to                  TEXT,
    $message_cc                  TEXT,
    $message_bcc                 TEXT,
    $message_reply_to            TEXT,
    $message_subject             TEXT,
    $message_tags                TEXT,
    $message_content_txt64       LONGTEXT,
    $message_content_htm64       LONGTEXT,
    $message_send_at             TEXT
)
SQL SECURITY INVOKER
BEGIN

    DECLARE $MESSAGE_ID    INT   DEFAULT  0;
    DECLARE $message_guid  TEXT  DEFAULT '';

    IF 'NOW' = $message_send_at THEN
        SET $message_send_at = NOW();
    END IF;

    IF @@read_only THEN

        CALL Error( 'READ_ONLY' );

    ELSE

        REPLACE INTO base_messaging
        ( USER,  MESSAGE_ID)
        VALUES
        ($USER, $MESSAGE_ID);
        SET $MESSAGE_ID = LAST_INSERT_ID();

        CALL Base_Guids_Create( 'message', $MESSAGE_ID, $message_guid );

        UPDATE base_messaging
        SET
            message_guid               = $message_guid,
            message_type               = $message_type,
            message_from               = $message_from,
            message_to                 = $message_to,
            message_cc                 = $message_cc,
            message_bcc                = $message_bcc,
            message_reply_to           = $message_reply_to,
            message_subject            = $message_subject,
            message_tags               = $message_tags,
            message_content_txt64      = $message_content_txt64,
            message_content_htm64      = $message_content_htm64,
            message_send_at            = $message_send_at

        WHERE message_deleted          = 0
        AND   MESSAGE_ID               = $MESSAGE_ID;

        DO LAST_INSERT_ID( $MESSAGE_ID );

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Messaging_Sent;
DELIMITER //
CREATE PROCEDURE Base_Messaging_Sent
(
    $apikey         TEXT,
    $MESSAGE_ID     INT,
    $response_code  TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN

    CALL _Base_Users_Authorise_APIKey_Type( 'MAILBOT', $apikey, @email, @idtype, @apikey_type, @USER, @ORG_ID, @PROJECT_ID );

    IF @@read_only THEN

        CALL Error( 'READ_ONLY' );

    ELSEIF NOT( "ADMIN" = @idtype ) THEN

        CALL Notice( 'Base_Messaging_Sent: INVALID_AUTHORISATION' );

    ELSEIF EXISTS( SELECT * FROM base_messaging WHERE MESSAGE_ID=$MESSAGE_ID AND message_sent_at = 0 ) THEN

        UPDATE base_messaging
        SET
            message_sent_at       = NOW(),
            message_response_code = $response_code

        WHERE MESSAGE_ID = $MESSAGE_ID;

    ELSEIF EXISTS( SELECT * FROM base_messaging WHERE MESSAGE_ID=$MESSAGE_ID AND message_last_sent_at = 0 ) THEN

        UPDATE base_messaging
        SET
            message_last_sent_at       = NOW(),
            message_last_response_code = $response_code

        WHERE MESSAGE_ID = $MESSAGE_ID;

    END IF;

END
//
DELIMITER ;

DROP   PROCEDURE Base_Messaging_Unsent;
DELIMITER //
CREATE PROCEDURE Base_Messaging_Unsent
(
    $apikey        TEXT,
    $message_type  TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN

    DECLARE $MESSAGE_ID    INT   DEFAULT  0;
    DECLARE $message_guid  TEXT  DEFAULT '';

    CALL Base_Users_Authorise_APIKey_Type( 'MAILBOT', $apikey, @email, @idtype, @apikey_type, @USER, @ORG_ID, @PROJECT_ID );

    IF NOT "ADMIN" = @idtype THEN

        CALL Notice( 'Base_Messaging_Unsent: INVALID_AUTHORISATION' );

    ELSE

        SELECT *
        FROM   base_messaging
        WHERE  message_deleted = 0
        AND    message_type    = $message_type
        AND
        (
            (NOT 0 = message_send_at      AND message_send_at      < NOW() AND 0 = message_sent_at)
            OR
            (NOT 0 = message_last_send_at AND message_last_send_at < NOW() AND 0 = message_last_sent_at)
        );

    END IF;

END
//
DELIMITER ;