CREATE TYPE trigger_plan AS (
    trigger_name    TEXT,
    constraint_name TEXT,
    relation        TEXT,
    time            INTERVAL,
    calls           FLOAT
);

CREATE OR REPLACE FUNCTION parse_triggers(
    triggers  XML[]
) RETURNS SETOF trigger_plan LANGUAGE plpgsql AS $$
DECLARE
    trig xml;
BEGIN
    IF triggers IS NOT NULL THEN
        FOR trig IN SELECT unnest(triggers) LOOP
            RETURN QUERY SELECT
                (xpath('/Trigger/Trigger-Name/text()', trig))[1]::text,
                (xpath('/Trigger/Constraint-Name/text()', trig))[1]::text,
                (xpath('/Trigger/Relation/text()', trig))[1]::text,
                ((xpath('/Trigger/Time/text()', trig))[1]::text || ' ms')::interval,
                (xpath('/Trigger/Calls/text()', trig))[1]::text::float;
        END LOOP;
    END IF;
END;
$$;

CREATE OR REPLACE FUNCTION parse_node(
    node       XML,
    parent_id  TEXT           DEFAULT NULL,
    runtime    INTERVAL       DEFAULT NULL,
    trigs      trigger_plan[] DEFAULT NULL
) RETURNS TABLE(
    node_id               TEXT,
    parent_id             TEXT,
    node_type             TEXT,
    total_runtime         INTERVAL,
    strategy              TEXT,
    operation             TEXT,
    startup_cost          FLOAT,
    total_cost            FLOAT,
    plan_rows             FLOAT,
    plan_width            INTEGER,
    actual_startup_time   INTERVAL,
    actual_total_time     INTERVAL,
    actual_rows           FLOAT,
    actual_loops          FLOAT,
    parent_relationship   TEXT,
    sort_key              TEXT[],
    sort_method           TEXT[],
    sort_space_used       BIGINT,
    sort_space_type       TEXT,
    join_type             TEXT,
    join_filter           TEXT,
    hash_cond             TEXT,
    relation_name         TEXT,
    alias                 TEXT,
    scan_direction        TEXT,
    index_name            TEXT,
    index_cond            TEXT,
    recheck_cond          TEXT,
    tid_cond              TEXT,
    merge_cond            TEXT,
    subplan_name          TEXT,
    function_name         TEXT,
    function_call         TEXT,
    filter                TEXT,
    one_time_filter       TEXT,
    command               TEXT,
    shared_hit_blocks     BIGINT,
    shared_read_blocks    BIGINT,
    shared_written_blocks BIGINT,
    local_hit_blocks      BIGINT,
    local_read_blocks     BIGINT,
    local_written_blocks  BIGINT,
    temp_read_blocks      BIGINT,
    temp_written_blocks   BIGINT,
    output                TEXT[],
    hash_buckets          BIGINT,
    hash_batches          BIGINT,
    original_hash_batches BIGINT,
    peak_memory_usage     BIGINT,
    schema                TEXT,
    cte_name              TEXT,
    triggers              trigger_plan[]
) LANGUAGE plpgsql AS $$
DECLARE
    plans   xml[] := xpath('/Plan/Plans/Plan', node);
    node_id TEXT  := md5(pg_backend_pid()::text || clock_timestamp());
BEGIN
    RETURN QUERY SELECT
        node_id,
        parent_id,
        (xpath('/Plan/Node-Type/text()', node))[1]::text,
        runtime,
        (xpath('/Plan/Strategy/text()', node))[1]::text,
        (xpath('/Plan/Operation/text()', node))[1]::text,
        (xpath('/Plan/Startup-Cost/text()', node))[1]::text::FLOAT,
        (xpath('/Plan/Total-Cost/text()', node))[1]::text::FLOAT,
        (xpath('/Plan/Plan-Rows/text()', node))[1]::text::FLOAT,
        (xpath('/Plan/Plan-Width/text()', node))[1]::text::INTEGER,
        ((xpath('/Plan/Actual-Startup-Time/text()', node))[1]::text || ' ms')::interval,
        ((xpath('/Plan/Actual-Total-Time/text()', node))[1]::text || ' ms')::interval,
        (xpath('/Plan/Actual-Rows/text()', node))[1]::text::FLOAT,
        (xpath('/Plan/Actual-Loops/text()', node))[1]::text::FLOAT,
        (xpath('/Plan/Parent-Relationship/text()', node))[1]::text,
        xpath('/Plan/Sort-Key/Item/text()', node)::text[],
        xpath('/Plan/Sort-Method/Item/text()', node)::text[],
        (xpath('/Plan/Sort-Space-Used/text()', node))[1]::text::bigint,
        (xpath('/Plan/Sort-Space-Type/text()', node))[1]::text,
        (xpath('/Plan/Join-Type/text()', node))[1]::text,
        (xpath('/Plan/Join-Filter/text()', node))[1]::text,
        (xpath('/Plan/Hash-Cond/text()', node))[1]::text,
        (xpath('/Plan/Relation-Name/text()', node))[1]::text,
        (xpath('/Plan/Alias/text()', node))[1]::text,
        (xpath('/Plan/Scan-Direction/text()', node))[1]::text,
        (xpath('/Plan/Index-Name/text()', node))[1]::text,
        (xpath('/Plan/Index-Cond/text()', node))[1]::text,
        (xpath('/Plan/Recheck-Cond/text()', node))[1]::text,
        (xpath('/Plan/TID-Cond/text()', node))[1]::text,
        (xpath('/Plan/Merge-Cond/text()', node))[1]::text,
        (xpath('/Plan/Subplan-Name/text()', node))[1]::text,
        (xpath('/Plan/Function-Name/text()', node))[1]::text,
        (xpath('/Plan/Function-Call/text()', node))[1]::text,
        (xpath('/Plan/Filter/text()', node))[1]::text,
        (xpath('/Plan/One-Time-Filter/text()', node))[1]::text,
        (xpath('/Plan/Command/text()', node))[1]::text,
        (xpath('/Plan/Shared-Hit-Blocks/text()', node))[1]::text::bigint,
        (xpath('/Plan/Shared-Read-Blocks/text()', node))[1]::text::bigint,
        (xpath('/Plan/Shared-Written-Blocks/text()', node))[1]::text::bigint,
        (xpath('/Plan/Local-Hit-Blocks/text()', node))[1]::text::bigint,
        (xpath('/Plan/Local-Read-Blocks/text()', node))[1]::text::bigint,
        (xpath('/Plan/Local-Written-Blocks/text()', node))[1]::text::bigint,
        (xpath('/Plan/Temp-Read-Blocks/text()', node))[1]::text::bigint,
        (xpath('/Plan/Temp-Written-Blocks/text()', node))[1]::text::bigint,
        xpath('/Plan/Output/Item/text()', node)::text[],
        (xpath('/Plan/Hash-Buckets/text()', node))[1]::text::bigint,
        (xpath('/Plan/Hash-Batches/text()', node))[1]::text::bigint,
        (xpath('/Plan/Original-Hash-Batches/text()', node))[1]::text::bigint,
        (xpath('/Plan/Peak-Memory-Usage/text()', node))[1]::text::bigint,
        (xpath('/Plan/Schema/text()', node))[1]::text,
        (xpath('/Plan/CTE-Name/text()', node))[1]::text,
        trigs
    ;

    -- Recurse.
    IF plans IS NOT NULL THEN
        FOR node IN SELECT unnest(plans) LOOP
            RETURN QUERY SELECT * FROM parse_node(node, node_id, runtime);
        END LOOP;
    END IF;
END;
$$;

CREATE OR REPLACE FUNCTION parse_node(
    cols       TEXT[],
    node       XML,
    parent_id  TEXT DEFAULT NULL,
    runtime    INTERVAL DEFAULT NULL,
    trigs      trigger_plan[] DEFAULT NULL
) RETURNS TABLE(
    node_id               TEXT,
    parent_id             TEXT,
    node_type             TEXT,
    total_runtime         INTERVAL,
    strategy              TEXT,
    operation             TEXT,
    startup_cost          FLOAT,
    total_cost            FLOAT,
    plan_rows             FLOAT,
    plan_width            INTEGER,
    actual_startup_time   INTERVAL,
    actual_total_time     INTERVAL,
    actual_rows           FLOAT,
    actual_loops          FLOAT,
    parent_relationship   TEXT,
    sort_key              TEXT[],
    sort_method           TEXT[],
    sort_space_used       BIGINT,
    sort_space_type       TEXT,
    join_type             TEXT,
    join_filter           TEXT,
    hash_cond             TEXT,
    relation_name         TEXT,
    alias                 TEXT,
    scan_direction        TEXT,
    index_name            TEXT,
    index_cond            TEXT,
    recheck_cond          TEXT,
    tid_cond              TEXT,
    merge_cond            TEXT,
    subplan_name          TEXT,
    function_name         TEXT,
    function_call         TEXT,
    filter                TEXT,
    one_time_filter       TEXT,
    command               TEXT,
    shared_hit_blocks     BIGINT,
    shared_read_blocks    BIGINT,
    shared_written_blocks BIGINT,
    local_hit_blocks      BIGINT,
    local_read_blocks     BIGINT,
    local_written_blocks  BIGINT,
    temp_read_blocks      BIGINT,
    temp_written_blocks   BIGINT,
    output                TEXT[],
    hash_buckets          BIGINT,
    hash_batches          BIGINT,
    original_hash_batches BIGINT,
    peak_memory_usage     BIGINT,
    schema                TEXT,
    cte_name              TEXT,       
    triggers              trigger_plan[]
) LANGUAGE plpgsql AS $$
DECLARE
    plans   xml[] := xpath('/Plan/Plans/Plan', node);
    node_id TEXT  := md5(pg_backend_pid()::text || clock_timestamp());
BEGIN
    RETURN QUERY SELECT
        node_id,
        parent_id,
        CASE WHEN 'node_type'             = ANY(cols) THEN (xpath('/Plan/Node-Type/text()', node))[1]::text                                ELSE NULL END,
        runtime,
        CASE WHEN 'strategy'              = ANY(cols) THEN (xpath('/Plan/Strategy/text()', node))[1]::text                                 ELSE NULL END,
        CASE WHEN 'operation'             = ANY(cols) THEN (xpath('/Plan/Operation/text()', node))[1]::text                                ELSE NULL END,
        CASE WHEN 'startup_cost'          = ANY(cols) THEN (xpath('/Plan/Startup-Cost/text()', node))[1]::text::FLOAT                      ELSE NULL END,
        CASE WHEN 'total_cost'            = ANY(cols) THEN (xpath('/Plan/Total-Cost/text()', node))[1]::text::FLOAT                        ELSE NULL END,
        CASE WHEN 'plan_rows'             = ANY(cols) THEN (xpath('/Plan/Plan-Rows/text()', node))[1]::text::FLOAT                         ELSE NULL END,
        CASE WHEN 'plan_width'            = ANY(cols) THEN (xpath('/Plan/Plan-Width/text()', node))[1]::text::INTEGER                      ELSE NULL END,
        CASE WHEN 'actual_startup_time'   = ANY(cols) THEN ((xpath('/Plan/Actual-Startup-Time/text()', node))[1]::text || ' ms')::interval ELSE NULL END,
        CASE WHEN 'actual_total_time'     = ANY(cols) THEN ((xpath('/Plan/Actual-Total-Time/text()', node))[1]::text || ' ms')::interval   ELSE NULL END,
        CASE WHEN 'actual_rows'           = ANY(cols) THEN (xpath('/Plan/Actual-Rows/text()', node))[1]::text::FLOAT                       ELSE NULL END,
        CASE WHEN 'actual_loops'          = ANY(cols) THEN (xpath('/Plan/Actual-Loops/text()', node))[1]::text::FLOAT                      ELSE NULL END,
        CASE WHEN 'parent_relationship'   = ANY(cols) THEN (xpath('/Plan/Parent-Relationship/text()', node))[1]::text                      ELSE NULL END,
        CASE WHEN 'sort_key'              = ANY(cols) THEN xpath('/Plan/Sort-Key/Item/text()', node)::text[]                               ELSE NULL END,
        CASE WHEN 'sort_method'           = ANY(cols) THEN xpath('/Plan/Sort-Method/Item/text()', node)::text[]                            ELSE NULL END,
        CASE WHEN 'sort_space_used'       = ANY(cols) THEN (xpath('/Plan/Sort-Space-Used/text()', node))[1]::text::bigint                  ELSE NULL END,
        CASE WHEN 'sort_space_type'       = ANY(cols) THEN (xpath('/Plan/Sort-Space-Type/text()', node))[1]::text                          ELSE NULL END,
        CASE WHEN 'join_type'             = ANY(cols) THEN (xpath('/Plan/Join-Type/text()', node))[1]::text                                ELSE NULL END,
        CASE WHEN 'join_filter'           = ANY(cols) THEN (xpath('/Plan/Join-Filter/text()', node))[1]::text                              ELSE NULL END,
        CASE WHEN 'hash_cond'             = ANY(cols) THEN (xpath('/Plan/Hash-Cond/text()', node))[1]::text                                ELSE NULL END,
        CASE WHEN 'relation_name'         = ANY(cols) THEN (xpath('/Plan/Relation-Name/text()', node))[1]::text                            ELSE NULL END,
        CASE WHEN 'alias'                 = ANY(cols) THEN (xpath('/Plan/Alias/text()', node))[1]::text                                    ELSE NULL END,
        CASE WHEN 'scan_direction'        = ANY(cols) THEN (xpath('/Plan/Scan-Direction/text()', node))[1]::text                           ELSE NULL END,
        CASE WHEN 'index_name'            = ANY(cols) THEN (xpath('/Plan/Index-Name/text()', node))[1]::text                               ELSE NULL END,
        CASE WHEN 'index_cond'            = ANY(cols) THEN (xpath('/Plan/Index-Cond/text()', node))[1]::text                               ELSE NULL END,
        CASE WHEN 'recheck_cond'          = ANY(cols) THEN (xpath('/Plan/Recheck-Cond/text()', node))[1]::text                             ELSE NULL END,
        CASE WHEN 'tid_cond'              = ANY(cols) THEN (xpath('/Plan/TID-Cond/text()', node))[1]::text                                 ELSE NULL END,
        CASE WHEN 'merge_cond'            = ANY(cols) THEN (xpath('/Plan/Merge-Cond/text()', node))[1]::text                               ELSE NULL END,
        CASE WHEN 'subplan_name'          = ANY(cols) THEN (xpath('/Plan/Subplan-Name/text()', node))[1]::text                             ELSE NULL END,
        CASE WHEN 'function_name'         = ANY(cols) THEN (xpath('/Plan/Function-Name/text()', node))[1]::text                            ELSE NULL END,
        CASE WHEN 'function_call'         = ANY(cols) THEN (xpath('/Plan/Function-Call/text()', node))[1]::text                            ELSE NULL END,
        CASE WHEN 'filter'                = ANY(cols) THEN (xpath('/Plan/Filter/text()', node))[1]::text                                   ELSE NULL END,
        CASE WHEN 'one_time_filter'       = ANY(cols) THEN (xpath('/Plan/One-Time-Filter/text()', node))[1]::text                          ELSE NULL END,
        CASE WHEN 'command'               = ANY(cols) THEN (xpath('/Plan/Command/text()', node))[1]::text                                  ELSE NULL END,
        CASE WHEN 'shared_hit_blocks'     = ANY(cols) THEN (xpath('/Plan/Shared-Hit-Blocks/text()', node))[1]::text::bigint                ELSE NULL END,
        CASE WHEN 'shared_read_blocks'    = ANY(cols) THEN (xpath('/Plan/Shared-Read-Blocks/text()', node))[1]::text::bigint               ELSE NULL END,
        CASE WHEN 'shared_written_blocks' = ANY(cols) THEN (xpath('/Plan/Shared-Written-Blocks/text()', node))[1]::text::bigint            ELSE NULL END,
        CASE WHEN 'local_hit_blocks'      = ANY(cols) THEN (xpath('/Plan/Local-Hit-Blocks/text()', node))[1]::text::bigint                 ELSE NULL END,
        CASE WHEN 'local_read_blocks'     = ANY(cols) THEN (xpath('/Plan/Local-Read-Blocks/text()', node))[1]::text::bigint                ELSE NULL END,
        CASE WHEN 'local_written_blocks'  = ANY(cols) THEN (xpath('/Plan/Local-Written-Blocks/text()', node))[1]::text::bigint             ELSE NULL END,
        CASE WHEN 'temp_read_blocks'      = ANY(cols) THEN (xpath('/Plan/Temp-Read-Blocks/text()', node))[1]::text::bigint                 ELSE NULL END,
        CASE WHEN 'temp_written_blocks'   = ANY(cols) THEN (xpath('/Plan/Temp-Written-Blocks/text()', node))[1]::text::bigint              ELSE NULL END,
        CASE WHEN 'output'                = ANY(cols) THEN xpath('/Plan/Output/Item/text()', node)::text[]                                 ELSE NULL END,
        CASE WHEN 'hash_buckets'          = ANY(cols) THEN (xpath('/Plan/Hash-Buckets/text()', node))[1]::text::bigint                     ELSE NULL END,
        CASE WHEN 'hash_batches'          = ANY(cols) THEN (xpath('/Plan/Hash-Batches/text()', node))[1]::text::bigint                     ELSE NULL END,
        CASE WHEN 'original_hash_batches' = ANY(cols) THEN (xpath('/Plan/Original-Hash-Batches/text()', node))[1]::text::bigint            ELSE NULL END,
        CASE WHEN 'peak_memory_usage'     = ANY(cols) THEN (xpath('/Plan/Peak-Memory-Usage/text()', node))[1]::text::bigint                ELSE NULL END,
        CASE WHEN 'schema'                = ANY(cols) THEN (xpath('/Plan/Schema/text()', node))[1]::text                                   ELSE NULL END,
        CASE WHEN 'cte_name'              = ANY(cols) THEN (xpath('/Plan/CTE-Name/text()', node))[1]::text                                 ELSE NULL END,
        trigs
    ;

    -- Recurse.
    IF plans IS NOT NULL THEN
        FOR node IN SELECT unnest(plans) LOOP
            RETURN QUERY SELECT * FROM parse_node(cols, node, node_id, runtime);
        END LOOP;
    END IF;
END;
$$;

CREATE OR REPLACE FUNCTION explanation(
    query    TEXT,
    analyzed BOOLEAN DEFAULT FALSE,
    columns  TEXT[]  DEFAULT NULL
) RETURNS TABLE(
    planned_at            TIMESTAMPTZ,
    node_id               TEXT,
    parent_id             TEXT,
    node_type             TEXT,
    total_runtime         INTERVAL,
    strategy              TEXT,
    operation             TEXT,
    startup_cost          FLOAT,
    total_cost            FLOAT,
    plan_rows             FLOAT,
    plan_width            INTEGER,
    actual_startup_time   INTERVAL,
    actual_total_time     INTERVAL,
    actual_rows           FLOAT,
    actual_loops          FLOAT,
    parent_relationship   TEXT,
    sort_key              TEXT[],
    sort_method           TEXT[],
    sort_space_used       BIGINT,
    sort_space_type       TEXT,
    join_type             TEXT,
    join_filter           TEXT,
    hash_cond             TEXT,
    relation_name         TEXT,
    alias                 TEXT,
    scan_direction        TEXT,
    index_name            TEXT,
    index_cond            TEXT,
    recheck_cond          TEXT,
    tid_cond              TEXT,
    merge_cond            TEXT,
    subplan_name          TEXT,
    function_name         TEXT,
    function_call         TEXT,
    filter                TEXT,
    one_time_filter       TEXT,
    command               TEXT,
    shared_hit_blocks     BIGINT,
    shared_read_blocks    BIGINT,
    shared_written_blocks BIGINT,
    local_hit_blocks      BIGINT,
    local_read_blocks     BIGINT,
    local_written_blocks  BIGINT,
    temp_read_blocks      BIGINT,
    temp_written_blocks   BIGINT,
    output                TEXT[],
    hash_buckets          BIGINT,
    hash_batches          BIGINT,
    original_hash_batches BIGINT,
    peak_memory_usage     BIGINT,
    schema                TEXT,
    cte_name              TEXT,
    triggers              trigger_plan[]
) LANGUAGE plpgsql AS $$
DECLARE
    plan  xml;
    node  xml;
    xmlns text[] := ARRAY[ARRAY['e', 'http://www.postgresql.org/2009/explain']];
BEGIN
    -- Get the plan.
    EXECUTE 'EXPLAIN (format xml, verbose true'
         || CASE WHEN analyzed THEN ', analyze true, buffers true' ELSE '' END
         || ') ' || query INTO plan;

    IF columns IS NULL THEN
        -- Generate all columns.
        RETURN QUERY SELECT NOW(), * FROM parse_node(
            (xpath('/e:explain/e:Query/e:Plan', plan, xmlns))[1],
            NULL,
            ((xpath('/e:explain/e:Query/e:Total-Runtime/text()', plan, xmlns))[1]::text || ' ms')::interval,
            ARRAY(SELECT p FROM parse_triggers(xpath('/e:explain/e:Query/e:Triggers/e:Trigger', plan, xmlns)) AS p)
        );
    ELSE
        -- Generate only the specified columns.
        RETURN QUERY SELECT CASE WHEN 'planned_at' = ANY(columns) THEN NOW() ELSE NULL END, * FROM parse_node(
            columns,
            (xpath('/e:explain/e:Query/e:Plan', plan, xmlns))[1],
            NULL,
            CASE WHEN 'total_runtime' = ANY(columns) THEN ((xpath('/e:explain/e:Query/e:Total-Runtime/text()', plan, xmlns))[1]::text || ' ms')::interval ELSE NULL END,
            CASE WHEN 'triggers' = ANY(columns) THEN ARRAY(SELECT p FROM parse_triggers(xpath('/e:explain/e:Query/e:Triggers/e:Trigger', plan, xmlns)) AS p) ELSE NULL END
        );
    END IF;
END;
$$;
