-- Materialized Views with statistical data for data quality reports
DROP MATERIALIZED VIEW waterway.dqr_gauge_stats;

CREATE MATERIALIZED VIEW waterway.dqr_gauge_stats AS
WITH d AS ( SELECT ym::date, d::date
              FROM generate_series( '2019-10-01'::date,
                                    now() - interval '1 day',
                                    '1 month'::interval ) ym,
                   generate_series( ym,
                                    ( ym + interval '1 month'
                                         - interval '1 day'),
                                     '1 day'::interval ) d ),
     g AS ( SELECT d.ym, d.d AS day, (location).country_code AS cc,
                   min((location).hectometre) AS hm,
                   objname, array_agg(distinct(location)) AS locations
            FROM waterway.gauges,d
            GROUP BY objname,d.d,(location).country_code,d.ym ),
     measure AS (
       SELECT g.ym, g.cc, g.hm, g.objname, g.day,
              CASE WHEN count(measure_date) = 0
              THEN 1 ELSE 0 END AS missing
         FROM g
         LEFT OUTER JOIN waterway.gauge_measurements gm
           ON ARRAY[location] <@ g.locations
             AND g.day <= measure_date
             AND measure_date < (g.day + interval '1 day')
         GROUP BY g.objname,g.day,g.ym,g.cc,g.hm )
  SELECT cc, ym AS month, objname, hm, sum(missing) AS daynodata
    FROM measure
    GROUP BY cc, ym, objname, hm;

DROP MATERIALIZED VIEW waterway.dqr_bottleneck_stats;

CREATE MATERIALIZED VIEW waterway.dqr_bottleneck_stats AS
WITH d AS ( SELECT ym::date
              FROM generate_series( '2019-10-01'::date,
                                    now() - interval '1 day',
                                    '1 month'::interval ) ym),
     bn AS ( SELECT DISTINCT objnam, responsible_country AS cc,
                    min ((lower(stretch)).hectometre) AS hm
               FROM waterway.bottlenecks GROUP BY objnam,cc),
     bid AS (SELECT objnam, array_agg(distinct(bottleneck_id)) AS ids
               FROM waterway.bottlenecks GROUP BY objnam)
  SELECT bn.cc, bn.hm, d.ym AS month, bid.objnam,
         COALESCE(count(distinct(sr.date_info)),0) AS srcnt,
         COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt
    FROM bn, bid
    CROSS JOIN d
    LEFT OUTER JOIN waterway.sounding_results sr
      ON ARRAY[sr.bottleneck_id] <@ bid.ids
         AND d.ym <= sr.date_info
         AND sr.date_info < (d.ym + interval '1 month')
    LEFT OUTER JOIN waterway.fairway_availability fa
      ON ARRAY[fa.bottleneck_id] <@ bid.ids
    LEFT OUTER JOIN waterway.effective_fairway_availability efa
      ON fairway_availability_id = fa.id
         AND d.ym <= efa.measure_date
         AND efa.measure_date < (d.ym + interval '1 month')
         AND efa.measure_type = 'Measured'
    WHERE bid.objnam = bn.objnam
    GROUP BY bn.cc, bn.hm, d.ym, bid.objnam;

-- Refresh access rights!
GRANT SELECT on ALL tables in schema waterway TO waterway_user;