clear runaway conferences

create_runaway_conference_cleaning.sql

DELIMITER $$


DROP PROCEDURE IF EXISTS clear_runaway_conferences$$
CREATE PROCEDURE clear_runaway_conferences()
    MODIFIES SQL DATA
BEGIN

    DROP TABLE IF EXISTS _in_process;
    CREATE TEMPORARY TABLE _in_process (`state` VARCHAR(32));
    INSERT INTO _in_process VALUES ('created'), ('seeker_answered'), ('advisor_answered'), ('billing_started');

    DROP TABLE IF EXISTS _runaway_conferences;
    CREATE TEMPORARY TABLE _runaway_conferences AS
        SELECT lc.id, c.id AS cid FROM live_conferences lc
        INNER JOIN conferences c ON (
            c.id = lc.conference_id
        )
        LEFT JOIN conference_events ce ON (
            ce.conference_id = lc.id
            AND ce.`type` = 'HeartbeatConferenceEvent'
        )
        WHERE
            (c.aasm_state NOT IN (SELECT `state` FROM _in_process) OR lc.created_at < (NOW() - INTERVAL 36000 SECOND) )
            AND ce.`type` IS NULL;

    SELECT cid AS conference_id FROM _runaway_conferences;
    DELETE FROM live_conferences WHERE id IN (SELECT id FROM _runaway_conferences);

END$$


DELIMITER ;