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 bottleneck_id ,max(objnam) as objnam, responsible_country AS cc,
                    min ((lower(stretch)).hectometre) AS hm
               FROM waterway.bottlenecks
               WHERE validity @> tstzrange(now() - interval '1 hour', now() + interval '1 hour', '[)')
               GROUP BY bottleneck_id,cc
               )
  SELECT bn.cc, bn.hm, d.ym AS month, bn.objnam, bn.bottleneck_id,
         COALESCE(count(distinct(sr.date_info)),0) AS srcnt,
         COALESCE(count(distinct(efa.measure_date)),0) AS fwacnt
    FROM bn
    CROSS JOIN d
    LEFT OUTER JOIN waterway.sounding_results sr
      ON sr.bottleneck_id = bn.bottleneck_id
         AND d.ym <= sr.date_info
         AND sr.date_info < (d.ym + interval '1 month')
    LEFT OUTER JOIN waterway.fairway_availability fa
      ON fa.bottleneck_id = bn.bottleneck_id
    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'
    GROUP BY bn.cc, bn.hm, d.ym, bn.bottleneck_id, bn.objnam;