-- c77_dbh--1.0.sql
-- PostgreSQL extension for database health monitoring

\echo Use "CREATE EXTENSION c77_dbh" to load this file. \quit

-- Schema setup (optional, using public schema by default)
-- CREATE SCHEMA IF NOT EXISTS c77_dbh;
-- SET search_path TO c77_dbh, public;

-- Vacuum Stats Table
CREATE TABLE IF NOT EXISTS public.c77_dbh_vacuum_stats (
                                                           table_name text COLLATE pg_catalog."default" PRIMARY KEY,
                                                           last_vacuum timestamp without time zone,
                                                           last_autovacuum timestamp without time zone,
                                                           vacuum_count bigint DEFAULT 0,
                                                           dead_tuples bigint DEFAULT 0,
                                                           live_tuples bigint DEFAULT 0,
                                                           table_size bigint DEFAULT 0,
                                                           bloat_estimate numeric DEFAULT 0,
                                                           last_updated timestamp without time zone DEFAULT now()
);

REVOKE ALL ON TABLE public.c77_dbh_vacuum_stats FROM PUBLIC;
GRANT SELECT ON TABLE public.c77_dbh_vacuum_stats TO PUBLIC;

-- Index Stats Table
CREATE TABLE IF NOT EXISTS public.c77_dbh_index_stats (
                                                          index_name text COLLATE pg_catalog."default" PRIMARY KEY,
                                                          table_name text,
                                                          index_size bigint DEFAULT 0,
                                                          scan_count bigint DEFAULT 0,
                                                          bloat_estimate numeric DEFAULT 0,
                                                          last_updated timestamp without time zone DEFAULT now()
);

REVOKE ALL ON TABLE public.c77_dbh_index_stats FROM PUBLIC;
GRANT SELECT ON TABLE public.c77_dbh_index_stats TO PUBLIC;

-- Materialized View Stats Table
CREATE TABLE IF NOT EXISTS public.c77_dbh_mv_stats (
                                                       mv_name text COLLATE pg_catalog."default",
                                                       create_mv timestamp without time zone,
                                                       mod_mv timestamp without time zone,
                                                       refresh_mv_last timestamp without time zone,
                                                       refresh_count integer DEFAULT 0,
                                                       refresh_mv_time_last interval,
                                                       refresh_mv_time_total interval DEFAULT '00:00:00'::interval,
                                                       refresh_mv_time_min interval,
                                                       refresh_mv_time_max interval,
                                                       reset_last timestamp without time zone
);

REVOKE ALL ON TABLE public.c77_dbh_mv_stats FROM PUBLIC;
GRANT SELECT ON TABLE public.c77_dbh_mv_stats TO PUBLIC;

-- Vacuum Update Function
CREATE OR REPLACE FUNCTION public.c77_dbh_update_vacuum_stats()
    RETURNS void AS $$
DECLARE
    v_has_pgstattuple boolean;
BEGIN
    SELECT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pgstattuple') INTO v_has_pgstattuple;

    INSERT INTO public.c77_dbh_vacuum_stats (
        table_name, last_vacuum, last_autovacuum, vacuum_count,
        dead_tuples, live_tuples, table_size, last_updated
    )
    SELECT
        (n.nspname || '.' || c.relname) AS table_name,
        s.last_vacuum,
        s.last_autovacuum,
        COALESCE(s.vacuum_count, 0) + COALESCE(s.autovacuum_count, 0) AS vacuum_count,
        s.n_dead_tup AS dead_tuples,
        s.n_live_tup AS live_tuples,
        pg_relation_size(c.oid) AS table_size,
        now() AS last_updated
    FROM pg_stat_all_tables s
             JOIN pg_class c ON c.oid = s.relid
             JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
    ON CONFLICT (table_name) DO UPDATE
        SET
            last_vacuum = EXCLUDED.last_vacuum,
            last_autovacuum = EXCLUDED.last_autovacuum,
            vacuum_count = EXCLUDED.vacuum_count,
            dead_tuples = EXCLUDED.dead_tuples,
            live_tuples = EXCLUDED.live_tuples,
            table_size = EXCLUDED.table_size,
            last_updated = EXCLUDED.last_updated;

    IF v_has_pgstattuple THEN
        UPDATE public.c77_dbh_vacuum_stats v
        SET bloat_estimate = ROUND(CAST(COALESCE(t.free_percent + t.dead_tuple_percent, 0) AS numeric), 2)
        FROM (
                 SELECT (pgstattuple(c.oid)).*
                 FROM pg_class c
                          JOIN pg_namespace n ON n.oid = c.relnamespace
                 WHERE (n.nspname || '.' || c.relname) = v.table_name
             ) t
        WHERE v.last_updated = now();
    ELSE
        UPDATE public.c77_dbh_vacuum_stats
        SET bloat_estimate = CASE
                                 WHEN live_tuples + dead_tuples = 0 THEN 0
                                 ELSE ROUND((dead_tuples::numeric / (live_tuples + dead_tuples)) * 100, 2)
            END
        WHERE last_updated = now();
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Index Update Function
CREATE OR REPLACE FUNCTION public.c77_dbh_update_index_stats()
    RETURNS void AS $$
DECLARE
    v_has_pgstattuple boolean;
BEGIN
    SELECT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pgstattuple') INTO v_has_pgstattuple;

    INSERT INTO public.c77_dbh_index_stats (
        index_name, table_name, index_size, scan_count, bloat_estimate, last_updated
    )
    SELECT
        (n.nspname || '.' || i.relname) AS index_name,
        (n.nspname || '.' || t.relname) AS table_name,
        pg_relation_size(i.oid) AS index_size,
        COALESCE(s.idx_scan, 0) AS scan_count,
        CASE WHEN v_has_pgstattuple THEN
                 ROUND(CAST(COALESCE((pgstattuple(i.oid)).free_percent + (pgstattuple(i.oid)).dead_tuple_percent, 0) AS numeric), 2)
             ELSE 0 END AS bloat_estimate,
        now() AS last_updated
    FROM pg_index ix
             JOIN pg_class i ON i.oid = ix.indexrelid
             JOIN pg_class t ON t.oid = ix.indrelid
             JOIN pg_namespace n ON n.oid = i.relnamespace
             LEFT JOIN pg_stat_all_indexes s ON s.indexrelid = i.oid
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
    ON CONFLICT (index_name) DO UPDATE
        SET
            table_name = EXCLUDED.table_name,
            index_size = EXCLUDED.index_size,
            scan_count = EXCLUDED.scan_count,
            bloat_estimate = EXCLUDED.bloat_estimate,
            last_updated = EXCLUDED.last_updated;
END;
$$ LANGUAGE plpgsql;

-- Vacuum Health Function
CREATE OR REPLACE FUNCTION public.c77_dbh_get_vacuum_health()
    RETURNS JSON AS $$
DECLARE
    v_median_bloat numeric;
    v_bloat_stddev numeric;
    v_has_pgstattuple boolean;
BEGIN
    SELECT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pgstattuple') INTO v_has_pgstattuple;

    WITH toast_bloat AS (
        SELECT
            (n.nspname || '.' || c.relname) AS table_name,
            CASE WHEN v_has_pgstattuple THEN
                     COALESCE((pgstattuple(c.oid)).free_percent + (pgstattuple(c.oid)).dead_tuple_percent, 0)
                 ELSE
                     CASE WHEN s.n_live_tup + s.n_dead_tup = 0 THEN 0
                          ELSE (s.n_dead_tup::numeric / (s.n_live_tup + s.n_dead_tup)) * 100 END
                END AS bloat_estimate
        FROM pg_stat_all_tables s
                 JOIN pg_class c ON c.oid = s.relid
                 JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
    )
    SELECT
                percentile_cont(0.5) WITHIN GROUP (ORDER BY bloat_estimate),
                stddev(bloat_estimate)
    INTO v_median_bloat, v_bloat_stddev
    FROM toast_bloat;

    RETURN (
        SELECT json_agg(
                       json_build_object(
                               'metric', 'Table Vacuum Health',
                               'table_name', v.table_name,
                               'status', CASE
                                             WHEN (v.bloat_estimate + COALESCE(t.toast_bloat, 0)) > v_median_bloat + 2 * v_bloat_stddev THEN 'Red'
                                             WHEN (v.bloat_estimate + COALESCE(t.toast_bloat, 0)) > v_median_bloat + v_bloat_stddev THEN 'Yellow'
                                             ELSE 'Green'
                                   END,
                               'severity', CASE
                                               WHEN (v.bloat_estimate + COALESCE(t.toast_bloat, 0)) > v_median_bloat + 2 * v_bloat_stddev THEN 5
                                               WHEN (v.bloat_estimate + COALESCE(t.toast_bloat, 0)) > v_median_bloat + v_bloat_stddev THEN 3
                                               ELSE 1
                                   END,
                               'insight', CASE
                                              WHEN (v.bloat_estimate + COALESCE(t.toast_bloat, 0)) > v_median_bloat + 2 * v_bloat_stddev THEN
                                                  'Table bloat is critically high' ||
                                                  (CASE WHEN t.toast_bloat > v.bloat_estimate THEN '; TOAST overhead is significant.' ELSE '.' END)
                                              WHEN (v.bloat_estimate + COALESCE(t.toast_bloat, 0)) > v_median_bloat + v_bloat_stddev THEN
                                                  'Table bloat is above average' ||
                                                  (CASE WHEN t.toast_bloat > v.bloat_estimate THEN '; TOAST overhead notable.' ELSE '.' END)
                                              ELSE 'Table bloat is within normal range.'
                                   END,
                               'action', CASE
                                             WHEN (v.bloat_estimate + COALESCE(t.toast_bloat, 0)) > v_median_bloat + 2 * v_bloat_stddev THEN
                                                 'Run VACUUM FULL or CLUSTER' ||
                                                 (CASE WHEN t.toast_bloat > v.bloat_estimate THEN '; review large fields for TOAST impact.' ELSE '.' END)
                                             WHEN (v.bloat_estimate + COALESCE(t.toast_bloat, 0)) > v_median_bloat + v_bloat_stddev THEN
                                                 'Run VACUUM and ANALYZE' ||
                                                 (CASE WHEN t.toast_bloat > v.bloat_estimate THEN '; check TOAST usage.' ELSE '.' END)
                                             ELSE 'No immediate action required.'
                                   END,
                               'last_vacuum', v.last_vacuum::text,
                               'last_autovacuum', v.last_autovacuum::text,
                               'vacuum_count', v.vacuum_count,
                               'dead_tuples', v.dead_tuples,
                               'live_tuples', v.live_tuples,
                               'table_size', pg_size_pretty(v.table_size),
                               'bloat_estimate', v.bloat_estimate::text || '%' ||
                                                 (CASE WHEN v_has_pgstattuple THEN ' (precise)' ELSE ' (estimated)' END),
                               'details', json_build_object(
                                       'median_bloat', round(v_median_bloat)::text || '%',
                                       'bloat_stddev', round(v_bloat_stddev)::text || '%',
                                       'toast_bloat', round(COALESCE(t.toast_bloat, 0))::text || '%',
                                       'explanation', 'Includes TOAST bloat in total health assessment.'
                                          )
                       )
               )
        FROM public.c77_dbh_vacuum_stats v
                 LEFT JOIN (
            SELECT
                (n.nspname || '.' || c.relname) AS table_name,
                CASE WHEN v_has_pgstattuple THEN
                         COALESCE((pgstattuple(tst.oid)).free_percent + (pgstattuple(tst.oid)).dead_tuple_percent, 0)
                     ELSE 0 END AS toast_bloat
            FROM pg_class c
                     JOIN pg_namespace n ON n.oid = c.relnamespace
                     LEFT JOIN pg_class tst ON tst.oid = c.reltoastrelid
            WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND c.reltoastrelid != 0
        ) t ON v.table_name = t.table_name
    );
END;
$$ LANGUAGE plpgsql;

-- Index Health Function
CREATE OR REPLACE FUNCTION public.c77_dbh_get_index_health()
    RETURNS JSON AS $$
DECLARE
    v_median_bloat numeric;
    v_median_usage numeric;
BEGIN
    SELECT
                percentile_cont(0.5) WITHIN GROUP (ORDER BY bloat_estimate),
                percentile_cont(0.5) WITHIN GROUP (ORDER BY scan_count::numeric / NULLIF(index_size::numeric / 1024^2, 0))
    INTO v_median_bloat, v_median_usage
    FROM public.c77_dbh_index_stats
    WHERE index_name NOT LIKE 'pg_toast.%';

    RETURN (
        SELECT json_agg(
                       json_build_object(
                               'metric', 'Index Health',
                               'index_name', index_name,
                               'table_name', table_name,
                               'status', CASE
                                             WHEN bloat_estimate > v_median_bloat * 2 OR scan_count = 0 THEN 'Red'
                                             WHEN bloat_estimate > v_median_bloat * 1.2 OR (scan_count::numeric / NULLIF(index_size::numeric / 1024^2, 0)) < v_median_usage * 0.5 THEN 'Yellow'
                                             ELSE 'Green'
                                   END,
                               'severity', CASE
                                               WHEN bloat_estimate > v_median_bloat * 2 OR scan_count = 0 THEN 5
                                               WHEN bloat_estimate > v_median_bloat * 1.2 OR (scan_count::numeric / NULLIF(index_size::numeric / 1024^2, 0)) < v_median_usage * 0.5 THEN 3
                                               ELSE 1
                                   END,
                               'insight', CASE
                                              WHEN scan_count = 0 THEN 'Index is unused; consider dropping.'
                                              WHEN bloat_estimate > v_median_bloat * 2 THEN 'Index bloat is critically high.'
                                              WHEN (scan_count::numeric / NULLIF(index_size::numeric / 1024^2, 0)) < v_median_usage * 0.5 THEN 'Index usage is low relative to size.'
                                              ELSE 'Index is healthy and well-utilized.'
                                   END,
                               'action', CASE
                                             WHEN scan_count = 0 THEN 'Drop the index if not needed.'
                                             WHEN bloat_estimate > v_median_bloat * 2 THEN 'Run REINDEX; check autovacuum.'
                                             WHEN (scan_count::numeric / NULLIF(index_size::numeric / 1024^2, 0)) < v_median_usage * 0.5 THEN 'Review query patterns or consider reindexing.'
                                             ELSE 'No action needed.'
                                   END,
                               'index_size', pg_size_pretty(index_size),
                               'scan_count', scan_count,
                               'bloat_estimate', bloat_estimate::text || '%',
                               'details', json_build_object(
                                       'usage_rate', round((scan_count::numeric / NULLIF(index_size::numeric / 1024^2, 0))::numeric, 2)::text || ' scans/MB',
                                       'median_bloat', round(v_median_bloat)::text || '%',
                                       'median_usage', round(v_median_usage, 2)::text || ' scans/MB'
                                          )
                       )
               )
        FROM public.c77_dbh_index_stats
        WHERE index_name NOT LIKE 'pg_toast.%'
    );
END;
$$ LANGUAGE plpgsql;

-- TOAST Health Function
CREATE OR REPLACE FUNCTION public.c77_dbh_get_toast_health()
    RETURNS JSON AS $$
DECLARE
    v_median_bloat numeric;
    v_median_usage numeric;
    v_has_pgstattuple boolean;
BEGIN
    SELECT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pgstattuple') INTO v_has_pgstattuple;

    SELECT
                percentile_cont(0.5) WITHIN GROUP (ORDER BY bloat_estimate),
                percentile_cont(0.5) WITHIN GROUP (ORDER BY scan_count::numeric / NULLIF(index_size::numeric / 1024^2, 0))
    INTO v_median_bloat, v_median_usage
    FROM public.c77_dbh_index_stats
    WHERE index_name LIKE 'pg_toast.%';

    RETURN (
        SELECT json_agg(
                       json_build_object(
                               'metric', 'TOAST Index Health',
                               'index_name', index_name,
                               'parent_table', table_name,
                               'status', CASE
                                             WHEN bloat_estimate > v_median_bloat * 2 OR scan_count = 0 THEN 'Red'
                                             WHEN bloat_estimate > v_median_bloat * 1.2 OR (scan_count::numeric / NULLIF(index_size::numeric / 1024^2, 0)) < v_median_usage * 0.5 THEN 'Yellow'
                                             ELSE 'Green'
                                   END,
                               'severity', CASE
                                               WHEN bloat_estimate > v_median_bloat * 2 OR scan_count = 0 THEN 5
                                               WHEN bloat_estimate > v_median_bloat * 1.2 OR (scan_count::numeric / NULLIF(index_size::numeric / 1024^2, 0)) < v_median_usage * 0.5 THEN 3
                                               ELSE 1
                                   END,
                               'insight', CASE
                                              WHEN scan_count = 0 THEN 'TOAST index is unused; parent table may not need toasted columns indexed.'
                                              WHEN bloat_estimate > v_median_bloat * 2 THEN 'TOAST index bloat is critically high.'
                                              WHEN (scan_count::numeric / NULLIF(index_size::numeric / 1024^2, 0)) < v_median_usage * 0.5 THEN 'TOAST index usage is low relative to size.'
                                              ELSE 'TOAST index is healthy and well-utilized.'
                                   END,
                               'action', CASE
                                             WHEN scan_count = 0 THEN 'Review parent table queries; consider dropping TOAST index.'
                                             WHEN bloat_estimate > v_median_bloat * 2 THEN 'Run REINDEX on TOAST table; optimize large fields in parent.'
                                             WHEN (scan_count::numeric / NULLIF(index_size::numeric / 1024^2, 0)) < v_median_usage * 0.5 THEN 'Check parent table usage patterns.'
                                             ELSE 'No action needed.'
                                   END,
                               'index_size', pg_size_pretty(index_size),
                               'scan_count', scan_count,
                               'bloat_estimate', bloat_estimate::text || '%' ||
                                                 (CASE WHEN v_has_pgstattuple THEN ' (precise)' ELSE ' (estimated)' END),
                               'details', json_build_object(
                                       'usage_rate', round((scan_count::numeric / NULLIF(index_size::numeric / 1024^2, 0))::numeric, 2)::text || ' scans/MB',
                                       'median_bloat', round(v_median_bloat)::text || '%',
                                       'median_usage', round(v_median_usage, 2)::text || ' scans/MB',
                                       'parent_columns', (
                                           SELECT string_agg(attname, ', ')
                                           FROM pg_attribute a
                                                    JOIN pg_class c ON c.oid = (SELECT oid FROM pg_class WHERE relname = split_part(table_name, '.', 2) AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = split_part(table_name, '.', 1)))
                                           WHERE a.attrelid = c.oid AND a.attlen = -1
                                       )
                                          )
                       )
               )
        FROM public.c77_dbh_index_stats
        WHERE index_name LIKE 'pg_toast.%'
    );
END;
$$ LANGUAGE plpgsql;

-- MV Health Function
CREATE OR REPLACE FUNCTION public.c77_dbh_get_mv_health()
    RETURNS JSON AS $$
DECLARE
    v_has_pg_stat_statements boolean;
    v_stats_reset timestamp;
    v_base_io_load numeric;
    v_base_mem_hit_ratio numeric;
    v_avg_refresh_time interval;
BEGIN
    SELECT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements') INTO v_has_pg_stat_statements;

    SELECT
        d.stats_reset,
        (d.blks_read + d.blks_hit) / EXTRACT(EPOCH FROM (NOW() - d.stats_reset)),
        CASE WHEN b.buffers_clean + b.buffers_backend = 0 THEN 1.0
             ELSE b.buffers_clean::numeric / (b.buffers_clean + b.buffers_backend) END
    INTO v_stats_reset, v_base_io_load, v_base_mem_hit_ratio
    FROM pg_stat_database d
             CROSS JOIN pg_stat_bgwriter b
    WHERE d.datname = current_database();

    SELECT AVG(refresh_mv_time_total / GREATEST(refresh_count, 1))
    INTO v_avg_refresh_time
    FROM public.c77_dbh_mv_stats
    WHERE refresh_count > 0;

    RETURN (
        SELECT json_agg(
                       json_build_object(
                               'metric', 'Materialized View Refresh',
                               'mv_name', m.mv_name,
                               'status', CASE
                                             WHEN m.refresh_mv_time_last > v_avg_refresh_time * 2 THEN 'Red'
                                             WHEN m.refresh_mv_time_last > v_avg_refresh_time * 1.5 THEN 'Yellow'
                                             ELSE 'Green'
                                   END,
                               'severity', CASE
                                               WHEN m.refresh_mv_time_last > v_avg_refresh_time * 2 THEN 5
                                               WHEN m.refresh_mv_time_last > v_avg_refresh_time * 1.5 THEN 3
                                               ELSE 1
                                   END,
                               'insight', CASE
                                              WHEN v_io_load > 100 THEN 'Refreshes are slow due to high I/O load.'
                                              WHEN v_cpu_time > (CASE WHEN v_has_pg_stat_statements THEN 1000 ELSE 0.5 END) THEN 'Refreshes are slow due to CPU usage.'
                                              WHEN v_mem_hit_ratio < 0.9 THEN 'Refreshes are slow due to memory pressure.'
                                              ELSE 'Refresh performance is within normal range.'
                                   END,
                               'action', CASE
                                             WHEN v_io_load > 100 THEN 'Optimize I/O or schedule during off-peak hours.'
                                             WHEN v_cpu_time > (CASE WHEN v_has_pg_stat_statements THEN 1000 ELSE 0.5 END) THEN 'Increase CPU capacity or optimize queries.'
                                             WHEN v_mem_hit_ratio < 0.9 THEN 'Adjust memory settings (e.g., work_mem).'
                                             ELSE 'No action needed.'
                                   END,
                               'last_refresh_time', m.refresh_mv_time_last::text,
                               'refresh_count', m.refresh_count,
                               'io_load', round(v_io_load)::text || ' blocks/sec',
                               'cpu_time', (CASE WHEN v_has_pg_stat_statements THEN round(v_cpu_time)::text || ' ms/call'
                                                 ELSE (v_cpu_time * 100)::text || '% waiting' END),
                               'buffer_hit_ratio', round(v_mem_hit_ratio * 100)::text || '%',
                               'bottleneck', CASE
                                                 WHEN v_io_load > 100 THEN 'I/O'
                                                 WHEN v_cpu_time > (CASE WHEN v_has_pg_stat_statements THEN 1000 ELSE 0.5 END) THEN 'CPU'
                                                 WHEN v_mem_hit_ratio < 0.9 THEN 'RAM'
                                                 ELSE 'None'
                                   END,
                               'details', json_build_object(
                                       'avg_refresh_time', v_avg_refresh_time::text,
                                       'explanation', 'Thresholds based on average refresh time (' || v_avg_refresh_time::text || ').'
                                          )
                       )
               )
        FROM public.c77_dbh_mv_stats m
                 CROSS JOIN LATERAL (
            SELECT
                CASE WHEN v_has_pg_stat_statements THEN
                         COALESCE(SUM(s.shared_blks_read) / EXTRACT(EPOCH FROM (NOW() - v_stats_reset)), 0)
                     ELSE v_base_io_load END AS v_io_load,
                CASE WHEN v_has_pg_stat_statements THEN
                         COALESCE(SUM(s.total_time) / GREATEST(SUM(s.calls), 1), 0)
                     ELSE CASE WHEN EXISTS (
                         SELECT 1 FROM pg_stat_activity
                         WHERE query ILIKE '%REFRESH MATERIALIZED VIEW ' || m.mv_name || '%'
                           AND wait_event_type = 'CPU' AND state = 'active'
                     ) THEN 1 ELSE 0 END END AS v_cpu_time,
                CASE WHEN v_has_pg_stat_statements THEN
                         CASE WHEN SUM(s.shared_blks_hit + s.shared_blks_read) = 0 THEN 1.0
                              ELSE SUM(s.shared_blks_hit)::numeric / (SUM(s.shared_blks_hit) + SUM(s.shared_blks_read)) END
                     ELSE v_base_mem_hit_ratio END AS v_mem_hit_ratio
            FROM (SELECT 1 AS dummy) AS dummy
                     LEFT JOIN pg_stat_statements s ON v_has_pg_stat_statements
                AND s.query ILIKE '%REFRESH MATERIALIZED VIEW ' || m.mv_name || '%'
                AND s.dbid = (SELECT datid FROM pg_stat_database WHERE datname = current_database())
            ) AS stats
    );
END;
$$ LANGUAGE plpgsql;

-- MV Triggers
CREATE OR REPLACE FUNCTION public.c77_dbh_fn_trg_mv()
    RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    r RECORD;
    flag boolean;
    t_refresh_total interval;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
            IF tg_tag = 'CREATE MATERIALIZED VIEW' THEN
                INSERT INTO public.c77_dbh_mv_stats (mv_name, create_mv) VALUES (r.object_identity, now());
            END IF;
            IF tg_tag = 'ALTER MATERIALIZED VIEW' THEN
                SELECT TRUE INTO flag FROM public.c77_dbh_mv_stats WHERE mv_name = r.object_identity;
                IF NOT FOUND THEN
                    INSERT INTO public.c77_dbh_mv_stats (mv_name, create_mv) VALUES (r.object_identity, now());
                    DELETE FROM public.c77_dbh_mv_stats WHERE mv_name NOT IN (SELECT schemaname || '.' || matviewname FROM pg_catalog.pg_matviews);
                ELSE
                    UPDATE public.c77_dbh_mv_stats SET mod_mv = now() WHERE mv_name = r.object_identity;
                END IF;
            END IF;
            IF tg_tag = 'REFRESH MATERIALIZED VIEW' THEN
                t_refresh_total := clock_timestamp() - (SELECT current_setting('mv_stats.start')::timestamp);
                SET mv_stats.start TO DEFAULT;
                UPDATE public.c77_dbh_mv_stats
                SET refresh_mv_last = now(),
                    refresh_count = refresh_count + 1,
                    refresh_mv_time_last = t_refresh_total,
                    refresh_mv_time_total = refresh_mv_time_total + t_refresh_total,
                    refresh_mv_time_min = CASE
                                              WHEN refresh_mv_time_min IS NULL THEN t_refresh_total
                                              WHEN refresh_mv_time_min > t_refresh_total THEN t_refresh_total
                                              ELSE refresh_mv_time_min
                        END,
                    refresh_mv_time_max = CASE
                                              WHEN refresh_mv_time_max IS NULL THEN t_refresh_total
                                              WHEN refresh_mv_time_max < t_refresh_total THEN t_refresh_total
                                              ELSE refresh_mv_time_max
                        END
                WHERE mv_name = r.object_identity;
            END IF;
        END LOOP;
END;
$$;

CREATE OR REPLACE FUNCTION public.c77_dbh_fn_trg_mv_drop()
    RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
            DELETE FROM public.c77_dbh_mv_stats WHERE mv_name = r.object_identity;
        END LOOP;
END;
$$;

CREATE OR REPLACE FUNCTION public.c77_dbh_fn_trg_mv_start()
    RETURNS event_trigger LANGUAGE plpgsql AS $$
BEGIN
    PERFORM set_config('mv_stats.start', clock_timestamp()::text, true);
END;
$$;

DO $$
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM pg_event_trigger WHERE evtname = 'c77_dbh_trg_mv_info') THEN
            CREATE EVENT TRIGGER c77_dbh_trg_mv_info
                ON DDL_COMMAND_END
                WHEN TAG IN ('CREATE MATERIALIZED VIEW', 'ALTER MATERIALIZED VIEW', 'REFRESH MATERIALIZED VIEW')
            EXECUTE PROCEDURE public.c77_dbh_fn_trg_mv();
        END IF;

        IF NOT EXISTS (SELECT 1 FROM pg_event_trigger WHERE evtname = 'c77_dbh_trg_mv_info_drop') THEN
            CREATE EVENT TRIGGER c77_dbh_trg_mv_info_drop
                ON SQL_DROP
                WHEN TAG IN ('DROP MATERIALIZED VIEW')
            EXECUTE PROCEDURE public.c77_dbh_fn_trg_mv_drop();
        END IF;

        IF NOT EXISTS (SELECT 1 FROM pg_event_trigger WHERE evtname = 'c77_dbh_trg_mv_info_start') THEN
            CREATE EVENT TRIGGER c77_dbh_trg_mv_info_start
                ON DDL_COMMAND_START
                WHEN TAG IN ('REFRESH MATERIALIZED VIEW')
            EXECUTE PROCEDURE public.c77_dbh_fn_trg_mv_start();
        END IF;
    END;
$$;

-- Materialized View Stats View
CREATE OR REPLACE VIEW public.c77_dbh_matv_stats AS
SELECT mv_name, create_mv, mod_mv, refresh_mv_last, refresh_count,
       refresh_mv_time_last, refresh_mv_time_total, refresh_mv_time_min,
       refresh_mv_time_max, reset_last
FROM public.c77_dbh_mv_stats;

GRANT SELECT ON TABLE public.c77_dbh_matv_stats TO PUBLIC;