CREATE TABLE queue (
    queue_id serial not null primary key,
    ts timestamptz not null default now(),
    cat int not null,
    status text not null default 'new',
    current_step int not null default 1
);

CREATE TABLE queue_steps (
    queue_id int not null,
    step int not null,
    status text not null default 'pending',
    done timestamptz,
    payload text
);

INSERT INTO queue ( ts, cat, current_step )
SELECT now() - interval '15 seconds' + ( i * interval '1 second' ),
    (random()*3)::int,
    1
FROM generate_series(1,12) as gs(i);

INSERT INTO queue_steps ( queue_id, step, status, payload )
SELECT queue_id, gs.i, 'pending',
    md5(random()::text)
FROM queue,
    generate_series(1,3) as gs(i);

CREATE INDEX queue_ts ON queue(ts DESC);
CREATE INDEX queue_step_pk ON queue_steps(queue_id, step);
ALTER TABLE queue_steps ADD CONSTRAINT queue_fk FOREIGN KEY ( queue_id ) REFERENCES queue(queue_id);

CREATE FUNCTION add_queue_item ( ncat int )
RETURNS INT
LANGUAGE plpgsql
AS $f$
DECLARE qid INT;
BEGIN
    INSERT INTO queue ( ts, cat, status, current_step )
    VALUES ( now(), ncat, 'new', 1 )
    RETURNING queue_id
    INTO qid;

    INSERT INTO queue_steps ( queue_id, step, status, payload )
    SELECT qid, gs.i, 'pending',
        md5(random()::text)
    FROM generate_series(1,3) as gs(i);

    RETURN qid;
END;
$f$;

CREATE OR REPLACE FUNCTION queue_step ( ncat int )
RETURNS INT
LANGUAGE plpgsql
AS $f$
DECLARE qid INT;
    stnum INT;
BEGIN
    SELECT queue_id
    INTO qid
    FROM queue
    WHERE status = 'working'
        AND cat = ncat
    ORDER BY ts LIMIT 1
    FOR UPDATE;

    IF qid IS NULL THEN
        SELECT queue_id
        INTO qid
        FROM queue
        WHERE status = 'new'
            AND cat = ncat
        ORDER BY ts LIMIT 1
        FOR UPDATE;
    END IF;

    SELECT step FROM queue_steps
    INTO stnum
    WHERE queue_id = qid
    AND status = 'pending'
    ORDER BY step LIMIT 1;

    IF stnum = 1 THEN
        UPDATE queue
        SET status = 'working'
        WHERE queue_id = qid;
    END IF;

    UPDATE queue_steps
    SET status = 'working'
    WHERE queue_id = qid
        AND step = stnum;

    PERFORM pg_sleep(0.002);

    UPDATE queue_steps
    SET status = 'done',
        done = now()
    WHERE queue_id = qid
        AND step = stnum;

    IF stnum = 3 THEN
        UPDATE queue
        SET status = 'done'
        WHERE queue_id = qid;
    ELSE
        UPDATE queue
        SET current_step = stnum + 1
        WHERE queue_id = qid;
    END IF;

    RETURN qid;

END; $f$;

ANALYZE;
