LOAD 'age';

DROP FUNCTION IF EXISTS ag_catalog.bench_message_replies(bigint);

CREATE OR REPLACE FUNCTION ag_catalog.bench_message_replies(
    p_mid bigint
) RETURNS TABLE(
    commentId agtype,
    commentContent agtype,
    commentCreationDate agtype,
    replyAuthorId agtype,
    replyAuthorFirstName agtype,
    replyAuthorLastName agtype,
    replyAuthorKnowsOriginalMessageAuthor agtype
) AS $$
DECLARE
    v_json_params text;
BEGIN
    v_json_params := format('{"mid": %s}', p_mid);

    RETURN QUERY EXECUTE $exec$
        SELECT *
        FROM ag_catalog.cypher('snb_graph', $cypher$
            MATCH (m:Post {id: $mid})<-[:REPLY_OF]-(c:Comment)-[:HAS_CREATOR]->(p:Person)
            OPTIONAL MATCH (m)-[:HAS_CREATOR]->(a:Person)-[r:KNOWS]-(p)
            RETURN c.id,
                   c.content,
                   c.creationDate,
                   p.id,
                   p.firstName,
                   p.lastName,
                   CASE WHEN r IS NULL THEN false ELSE true END
            ORDER BY c.creationDate DESC, p.id

            UNION ALL

            MATCH (m:Comment {id: $mid})<-[:REPLY_OF]-(c:Comment)-[:HAS_CREATOR]->(p:Person)
            OPTIONAL MATCH (m)-[:HAS_CREATOR]->(a:Person)-[r:KNOWS]-(p)
            RETURN c.id,
                   c.content,
                   c.creationDate,
                   p.id,
                   p.firstName,
                   p.lastName,
                   CASE WHEN r IS NULL THEN false ELSE true END
            ORDER BY c.creationDate DESC, p.id
        $cypher$, $1) AS (
            commentId agtype,
            commentContent agtype,
            commentCreationDate agtype,
            replyAuthorId agtype,
            replyAuthorFirstName agtype,
            replyAuthorLastName agtype,
            replyAuthorKnowsOriginalMessageAuthor agtype
        );
$exec$ USING v_json_params::agtype;
END;
$$ LANGUAGE plpgsql;