CREATE TABLE rule ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, table_name TEXT NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE rule_param ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, "key" TEXT NOT NULL, expr_id TEXT NOT NULL, expr_type TEXT NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE aggr_expr ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, expr_id TEXT NOT NULL, fn_name TEXT NOT NULL, fncall_id TEXT NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE fncall_expr ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, expr_id TEXT NOT NULL, fn_name TEXT NOT NULL, fncall_id TEXT NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE fn_val_arg ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, fncall_id TEXT NOT NULL, fncall_expr_type TEXT NOT NULL, arg_index INTEGER NOT NULL, expr_id TEXT NOT NULL, expr_type TEXT NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE fn_kv_arg ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, fncall_id TEXT NOT NULL, fncall_expr_type TEXT NOT NULL, "key" TEXT NOT NULL, expr_id TEXT NOT NULL, expr_type TEXT NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE int_expr ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, expr_id TEXT NOT NULL, value BIGINT NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE str_expr ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, expr_id TEXT NOT NULL, value TEXT NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE var_expr ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, expr_id TEXT NOT NULL, var_name TEXT NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE sql_expr ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, expr_id TEXT NOT NULL, template TEXT ARRAY NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE dict_expr ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, expr_id TEXT NOT NULL, dict_id TEXT NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE dict_entry ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, dict_id TEXT NOT NULL, key TEXT NOT NULL, expr_id TEXT NOT NULL, expr_type TEXT NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE array_expr ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, expr_id TEXT NOT NULL, array_id TEXT NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE array_entry ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, array_id TEXT NOT NULL, "index" INTEGER NOT NULL, expr_id TEXT NOT NULL, expr_type TEXT NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE body_match ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, match_id TEXT NOT NULL, left_expr_id TEXT NOT NULL, left_expr_type TEXT NOT NULL, right_expr_id TEXT NOT NULL, right_expr_type TEXT NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE canonical_fact_var_sql ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, var_name TEXT NOT NULL, sql TEXT NOT NULL, fact_index INTEGER NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE sql_expr_template_part ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, expr_id TEXT NOT NULL, part TEXT NOT NULL, "index" INTEGER NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE aggr_expr_matching_signature ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, expr_id TEXT NOT NULL, fn_name TEXT NOT NULL, fncall_id TEXT NOT NULL, sql_name TEXT NOT NULL, val_arg_count INTEGER NOT NULL, kv_arg_keys TEXT ARRAY NOT NULL ) WITH ('materialized' = 'true');
CREATE TABLE var_mentioned_in_expr ( pipeline_id TEXT NOT NULL, rule_id TEXT NOT NULL, expr_id TEXT NOT NULL, expr_type TEXT NOT NULL, var_name TEXT NOT NULL, access_prefix TEXT NOT NULL ) WITH ('materialized' = 'true');

DECLARE RECURSIVE VIEW var_bound_via_match (pipeline_id TEXT, rule_id TEXT, match_id TEXT, var_name TEXT, sql TEXT, aggregated BOOLEAN);
DECLARE RECURSIVE VIEW canonical_var_bound_sql (pipeline_id TEXT, rule_id TEXT, var_name TEXT, sql TEXT, aggregated BOOLEAN);
CREATE MATERIALIZED VIEW canonical_var_bound_sql AS
    SELECT DISTINCT
        canonical_fact_var_sql.pipeline_id,
        canonical_fact_var_sql.rule_id,
        canonical_fact_var_sql.var_name,
        canonical_fact_var_sql.sql,
        false AS aggregated
    FROM canonical_fact_var_sql

    UNION

    SELECT DISTINCT
        var_bound_via_match.pipeline_id,
        var_bound_via_match.rule_id,
        var_bound_via_match.var_name,
        MIN(var_bound_via_match.sql) AS sql,
        SOME(var_bound_via_match.aggregated) AS aggregated
    FROM var_bound_via_match
    WHERE NOT EXISTS (
        SELECT 1
        FROM canonical_fact_var_sql
        WHERE canonical_fact_var_sql.pipeline_id = var_bound_via_match.pipeline_id
        AND canonical_fact_var_sql.rule_id = var_bound_via_match.rule_id
        AND canonical_fact_var_sql.var_name = var_bound_via_match.var_name
    )
    GROUP BY var_bound_via_match.pipeline_id, var_bound_via_match.rule_id, var_bound_via_match.var_name;

DECLARE RECURSIVE VIEW sql_expr_template_part_with_substitution (pipeline_id TEXT, rule_id TEXT, expr_id TEXT, part TEXT, "index" INTEGER);
CREATE MATERIALIZED VIEW sql_expr_template_part_with_substitution AS
    SELECT DISTINCT
        sql_expr_template_part.pipeline_id,
        sql_expr_template_part.rule_id,
        sql_expr_template_part.expr_id,
        canonical_var_bound_sql.sql AS part,
        sql_expr_template_part."index"
    FROM sql_expr_template_part
    JOIN canonical_var_bound_sql
        ON sql_expr_template_part.pipeline_id = canonical_var_bound_sql.pipeline_id
        AND sql_expr_template_part.rule_id = canonical_var_bound_sql.rule_id
        AND SUBSTRING(sql_expr_template_part.part FROM 3 FOR (CHAR_LENGTH(sql_expr_template_part.part)-4)) = canonical_var_bound_sql.var_name
    WHERE sql_expr_template_part.part RLIKE '^\{\{[a-zA-Z_][A-Za-z0-9_:]*\}\}$'

    UNION

    SELECT DISTINCT
        sql_expr_template_part.pipeline_id,
        sql_expr_template_part.rule_id,
        sql_expr_template_part.expr_id,
        sql_expr_template_part.part,
        sql_expr_template_part."index"
    FROM sql_expr_template_part
    WHERE NOT (sql_expr_template_part.part RLIKE '^\{\{[a-zA-Z_][A-Za-z0-9_:]*\}\}$');

DECLARE RECURSIVE VIEW sql_expr_substitution_status (pipeline_id TEXT, rule_id TEXT, expr_id TEXT, count BIGINT);
CREATE MATERIALIZED VIEW sql_expr_substitution_status AS
    SELECT DISTINCT
        t.pipeline_id,
        t.rule_id,
        t.expr_id,
        COUNT(*) AS count
    FROM sql_expr_template_part_with_substitution AS t
    GROUP BY t.pipeline_id, t.rule_id, t.expr_id;

DECLARE RECURSIVE VIEW sql_expr_all_vars_are_bound (pipeline_id TEXT, rule_id TEXT, expr_id TEXT);
CREATE MATERIALIZED VIEW sql_expr_all_vars_are_bound AS
    SELECT DISTINCT
        sql_expr.pipeline_id,
        sql_expr.rule_id,
        sql_expr.expr_id
    FROM sql_expr
    JOIN sql_expr_substitution_status
        ON sql_expr.pipeline_id = sql_expr_substitution_status.pipeline_id
        AND sql_expr.rule_id = sql_expr_substitution_status.rule_id
        AND sql_expr.expr_id = sql_expr_substitution_status.expr_id
    WHERE sql_expr_substitution_status.count = ARRAY_LENGTH(sql_expr.template);

DECLARE RECURSIVE VIEW substituted_sql_expr (pipeline_id TEXT, rule_id TEXT, expr_id TEXT, sql TEXT);
CREATE MATERIALIZED VIEW substituted_sql_expr AS
    SELECT DISTINCT
        a.pipeline_id,
        a.rule_id,
        a.expr_id,
        ARRAY_TO_STRING(ARRAY_AGG(b.part ORDER BY b."index"), '') AS sql
    FROM sql_expr_all_vars_are_bound AS a
    JOIN sql_expr_template_part_with_substitution AS b
        ON a.pipeline_id = b.pipeline_id
        AND a.rule_id = b.rule_id
        AND a.expr_id = b.expr_id
    GROUP BY a.pipeline_id, a.rule_id, a.expr_id;

DECLARE RECURSIVE VIEW substituted_expr (pipeline_id TEXT, rule_id TEXT, expr_id TEXT, expr_type TEXT, sql TEXT, aggregated BOOLEAN);

DECLARE RECURSIVE VIEW substituted_val_arg (pipeline_id TEXT, rule_id TEXT, fncall_id TEXT, fncall_expr_type TEXT, arg_index INTEGER, sql TEXT);
CREATE MATERIALIZED VIEW substituted_val_arg AS
    SELECT DISTINCT
        a.pipeline_id,
        a.rule_id,
        a.fncall_id,
        a.fncall_expr_type,
        a.arg_index,
        b.sql
    FROM fn_val_arg AS a
    JOIN substituted_expr AS b
        ON a.pipeline_id = b.pipeline_id
        AND a.rule_id = b.rule_id
        AND a.expr_id = b.expr_id
        AND a.expr_type = b.expr_type;

DECLARE RECURSIVE VIEW substituted_kv_arg (pipeline_id TEXT, rule_id TEXT, fncall_id TEXT, fncall_expr_type TEXT, key TEXT, sql TEXT);
CREATE MATERIALIZED VIEW substituted_kv_arg AS
    SELECT DISTINCT
        a.pipeline_id,
        a.rule_id,
        a.fncall_id,
        a.fncall_expr_type,
        a.key,
        b.sql
    FROM fn_kv_arg AS a
    JOIN substituted_expr AS b
        ON a.pipeline_id = b.pipeline_id
        AND a.rule_id = b.rule_id
        AND a.expr_id = b.expr_id
        AND a.expr_type = b.expr_type;

DECLARE RECURSIVE VIEW substituted_aggr_expr (pipeline_id TEXT, rule_id TEXT, expr_id TEXT, sql TEXT);
CREATE MATERIALIZED VIEW substituted_aggr_expr AS
    SELECT DISTINCT
        a.pipeline_id,
        a.rule_id,
        a.expr_id,
        'COUNT(*)' AS sql
    FROM aggr_expr_matching_signature AS a
    WHERE a.fn_name = 'count'
    AND a.val_arg_count = 0
    AND ARRAY_SIZE(a.kv_arg_keys) = 0
    
    UNION

    SELECT DISTINCT
        a.pipeline_id,
        a.rule_id,
        a.expr_id,
        (a.sql_name || '(' || c.sql || ')') AS sql
    FROM aggr_expr_matching_signature AS a
    JOIN substituted_val_arg AS c
        ON a.pipeline_id = c.pipeline_id
        AND a.rule_id = c.rule_id
        AND a.fncall_id = c.fncall_id
        AND c.fncall_expr_type = 'aggr_expr'
        AND c.arg_index = 0
    
    UNION

    SELECT DISTINCT
        a.pipeline_id,
        a.rule_id,
        a.expr_id,
        (a.sql_name || '(' || c.sql || ', ' || d.sql || ')') AS sql
    FROM aggr_expr_matching_signature AS a
    JOIN substituted_val_arg AS c
        ON a.pipeline_id = c.pipeline_id
        AND a.rule_id = c.rule_id
        AND a.fncall_id = c.fncall_id
        AND c.fncall_expr_type = 'aggr_expr'
        AND c.arg_index = 0
    JOIN substituted_kv_arg AS d
        ON a.pipeline_id = d.pipeline_id
        AND a.rule_id = d.rule_id
        AND a.fncall_id = d.fncall_id
        AND d.fncall_expr_type = 'aggr_expr'
        AND d.key = 'by'
    WHERE a.fn_name in ('argmin', 'argmax');

DECLARE RECURSIVE VIEW substituted_array_expr (pipeline_id TEXT, rule_id TEXT, expr_id TEXT, sql TEXT, aggregated BOOLEAN);
CREATE MATERIALIZED VIEW substituted_array_expr AS
    SELECT DISTINCT
        a.pipeline_id,
        a.rule_id,
        a.expr_id,
        ('ARRAY[' || ARRAY_TO_STRING(ARRAY_AGG(c.sql ORDER BY b."index"), ', ') || ']') AS sql,
        SOME(c.aggregated) AS aggregated
    FROM array_expr AS a
    JOIN array_entry AS b
        ON a.pipeline_id = b.pipeline_id
        AND a.rule_id = b.rule_id
        AND a.array_id = b.array_id
    JOIN substituted_expr AS c
        ON b.pipeline_id = c.pipeline_id
        AND b.rule_id = c.rule_id
        AND b.expr_id = c.expr_id
        AND b.expr_type = c.expr_type
    GROUP BY a.pipeline_id, a.rule_id, a.expr_id;

DECLARE RECURSIVE VIEW substituted_dict_expr (pipeline_id TEXT, rule_id TEXT, expr_id TEXT, sql TEXT, aggregated BOOLEAN);
CREATE MATERIALIZED VIEW substituted_dict_expr AS
    SELECT DISTINCT
        a.pipeline_id,
        a.rule_id,
        a.expr_id,
        ('MAP[' || ARRAY_TO_STRING(ARRAY_AGG('''' || b.key || '''' || ', ' || c.sql), ', ') || ']') AS sql,
        SOME(c.aggregated) AS aggregated
    FROM dict_expr AS a
    JOIN dict_entry AS b
        ON a.pipeline_id = b.pipeline_id
        AND a.rule_id = b.rule_id
        AND a.dict_id = b.dict_id
    JOIN substituted_expr AS c
        ON b.pipeline_id = c.pipeline_id
        AND b.rule_id = c.rule_id
        AND b.expr_id = c.expr_id
        AND b.expr_type = c.expr_type
    GROUP BY a.pipeline_id, a.rule_id, a.expr_id;

CREATE MATERIALIZED VIEW substituted_expr AS
    SELECT a.pipeline_id, a.rule_id, a.expr_id, CAST('sql_expr' AS TEXT) AS expr_type, a.sql, false AS aggregated
    FROM substituted_sql_expr AS a
    
    UNION
    
    SELECT b.pipeline_id, b.rule_id, b.expr_id, 'int_expr' AS expr_type, b.value AS sql, false AS aggregated
    FROM int_expr AS b

    UNION

    SELECT c.pipeline_id, c.rule_id, c.expr_id, 'str_expr' AS expr_type, ('''' || c.value || '''') AS sql, false AS aggregated
    FROM str_expr AS c

    UNION

    SELECT d.pipeline_id, d.rule_id, d.expr_id, 'var_expr' AS expr_type, canonical_var_bound_sql.sql, canonical_var_bound_sql.aggregated
    FROM var_expr AS d
    JOIN canonical_var_bound_sql
        ON d.pipeline_id = canonical_var_bound_sql.pipeline_id
        AND d.rule_id = canonical_var_bound_sql.rule_id
        AND d.var_name = canonical_var_bound_sql.var_name

    UNION

    SELECT e.pipeline_id, e.rule_id, e.expr_id, 'aggr_expr' AS expr_type, e.sql, true AS aggregated
    FROM substituted_aggr_expr AS e

    UNION

    SELECT f.pipeline_id, f.rule_id, f.expr_id, 'array_expr' AS expr_type, f.sql, f.aggregated
    FROM substituted_array_expr AS f

    UNION

    SELECT g.pipeline_id, g.rule_id, g.expr_id, 'dict_expr' AS expr_type, g.sql, g.aggregated
    FROM substituted_dict_expr AS g;

DECLARE RECURSIVE VIEW match_right_expr_sql (pipeline_id TEXT, rule_id TEXT, match_id TEXT, sql TEXT, aggregated BOOLEAN);
CREATE MATERIALIZED VIEW match_right_expr_sql AS
    SELECT DISTINCT a.pipeline_id, a.rule_id, a.match_id, b.sql, b.aggregated
    FROM body_match AS a
    JOIN substituted_expr AS b
        ON a.pipeline_id = b.pipeline_id
        AND a.rule_id = b.rule_id
        AND a.right_expr_id = b.expr_id
        AND a.right_expr_type = b.expr_type;

CREATE MATERIALIZED VIEW var_bound_via_match AS
    SELECT DISTINCT
        body_match.pipeline_id,
        body_match.rule_id,
        body_match.match_id,
        var_mentioned_in_expr.var_name,
        (match_right_expr_sql.sql || var_mentioned_in_expr.access_prefix) AS sql,
        match_right_expr_sql.aggregated
    FROM body_match
    JOIN var_mentioned_in_expr
        ON body_match.pipeline_id = var_mentioned_in_expr.pipeline_id
        AND body_match.rule_id = var_mentioned_in_expr.rule_id
        AND body_match.left_expr_id = var_mentioned_in_expr.expr_id
        AND body_match.left_expr_type = var_mentioned_in_expr.expr_type
    JOIN match_right_expr_sql
        ON body_match.pipeline_id = match_right_expr_sql.pipeline_id
        AND body_match.rule_id = match_right_expr_sql.rule_id
        AND body_match.match_id = match_right_expr_sql.match_id
    
    UNION
    
    SELECT DISTINCT
        rule_param.pipeline_id,
        rule_param.rule_id,
        NULL AS match_id,
        rule_param.key AS var_name,
        substituted_expr.sql AS sql,
        substituted_expr.aggregated
    FROM rule_param
    JOIN substituted_expr
        ON rule_param.pipeline_id = substituted_expr.pipeline_id
        AND rule_param.rule_id = substituted_expr.rule_id
        AND rule_param.expr_id = substituted_expr.expr_id
        AND rule_param.expr_type = substituted_expr.expr_type;