
-- W

-- DROP VIEW v_w_stundenmittel;

-- DROP VIEW w_stundenmittel_count_v;

-- DROP VIEW v_w_tagesmittel;

-- DROP VIEW w_tagesmittel_count_v;

-- DROP VIEW v_w_stundenmittel_bund;

-- DROP VIEW w_stundenmittel_count_v_bund;

-- DROP VIEW check_15min;

TRUNCATE TABLE w;

ALTER TABLE w
  ALTER COLUMN ZEIT TYPE timestamp with time zone
  USING ZEIT::timestamp with time zone;

-- W_HAUPT

TRUNCATE TABLE w_haupt;

ALTER TABLE w_haupt
  ALTER COLUMN ZEIT TYPE timestamp with time zone
  USING ZEIT::timestamp with time zone;

DROP VIEW v_w_stundenmittel_redundanz;

DROP VIEW w_stundenmittel_redundanz_count_v;

DROP VIEW v_w_tagesmittel_redundanz;

DROP VIEW w_tagesmittel_redundanz_count_v;

TRUNCATE TABLE w_redundanzsystem;

ALTER TABLE w_redundanzsystem
  ALTER COLUMN ZEIT TYPE timestamp with time zone
  USING ZEIT::timestamp with time zone;

-- W_HAND_HAUPT

TRUNCATE TABLE w_hand_haupt;

ALTER TABLE w_hand_haupt
  ALTER COLUMN ZEIT TYPE timestamp with time zone
  USING ZEIT::timestamp with time zone;

-- W_SYSTEM_HAUPT

TRUNCATE TABLE w_system_haupt;

ALTER TABLE w_system_haupt
  ALTER COLUMN ZEIT TYPE timestamp with time zone
  USING ZEIT::timestamp with time zone;

-- W_MAX

DROP VIEW v_hvz_w_max;

DROP VIEW v_w_max_und_scheitel;

DROP VIEW check_max_1d;

TRUNCATE TABLE w_max;

ALTER TABLE w_max
  ALTER COLUMN ZEIT TYPE timestamp with time zone
  USING ZEIT::timestamp with time zone;

-- W_TEMP_REIHEN

TRUNCATE TABLE w_temp_reihen;

ALTER TABLE w_temp_reihen
  ALTER COLUMN ZEIT TYPE timestamp with time zone
  USING ZEIT::timestamp with time zone;

-- Q

DROP VIEW v_q_stundenmittel;

DROP VIEW q_stundenmittel_count_v;

DROP VIEW v_q_stundenmittel_bund;

DROP VIEW q_stundenmittel_count_v_bund;

DROP VIEW v_q_tagesmittel_bundespegel;

DROP VIEW v_q_tagesmittel;

DROP VIEW q_tagesmittel_count_v;

TRUNCATE TABLE q;

ALTER TABLE q
  ALTER COLUMN ZEIT TYPE timestamp with time zone
  USING ZEIT::timestamp with time zone;

-- Q_HAUPT

TRUNCATE TABLE q_haupt;

ALTER TABLE q_haupt
  ALTER COLUMN ZEIT TYPE timestamp with time zone
  USING ZEIT::timestamp with time zone;

-- Q_REDUNDANZ

DROP VIEW v_q_stundenmittel_redundanz;

DROP VIEW q_stundenmittel_redundanz_count_v;

DROP VIEW v_q_tagesmittel_redundanz;

DROP VIEW q_tagesmittel_redundanz_count_v;

TRUNCATE TABLE q_redundanz;

ALTER TABLE q_redundanz
  ALTER COLUMN ZEIT TYPE timestamp with time zone
  USING ZEIT::timestamp with time zone;

-- Q_HAND_HAUPT

TRUNCATE TABLE q_hand_haupt;

ALTER TABLE q_hand_haupt
  ALTER COLUMN ZEIT TYPE timestamp with time zone
  USING ZEIT::timestamp with time zone;

-- Q_SYSTEM_HAUPT

TRUNCATE TABLE q_system_haupt;

ALTER TABLE q_system_haupt
  ALTER COLUMN ZEIT TYPE timestamp with time zone
  USING ZEIT::timestamp with time zone;

-- Z_PEGELNOTIZ_HAUPT

TRUNCATE TABLE z_pegelnotiz_haupt;

ALTER TABLE z_pegelnotiz_haupt
  ALTER COLUMN ZEIT TYPE timestamp with time zone
  USING ZEIT::timestamp with time zone;

-- Z_PEGELNOTIZ_REDUNDANZ

TRUNCATE TABLE z_pegelnotiz_redundanz;

ALTER TABLE z_pegelnotiz_redundanz
  ALTER COLUMN ZEIT TYPE timestamp with time zone
  USING ZEIT::timestamp with time zone;

--- Views wieder erstellen ---

CREATE OR REPLACE VIEW W_STUNDENMITTEL_COUNT_V (MESSTELLEN_NR, ZEIT, W, W_COUNT, TOTAL_COUNT) AS SELECT messtellen_nr,
         TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24') AS zeit,
         AVG (w) AS w,
         COUNT (w) AS w_count,
         COUNT (*) AS total_count
    FROM w
    WHERE zeit >= TO_DATE('2016-05-01', 'YYYY-MM-DD')
GROUP BY messtellen_nr,
         TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24');

CREATE OR REPLACE VIEW V_W_STUNDENMITTEL (MESSTELLEN_NR, ZEIT, W) AS (SELECT messtellen_nr, zeit, W
   FROM W
  WHERE zeit < TO_DATE ('2016-05-01', 'YYYY-MM-DD'))
UNION
(SELECT messtellen_nr,
        zeit,
        CASE WHEN w_count = total_count THEN w ELSE NULL END AS w
   FROM W_STUNDENMITTEL_COUNT_V
  WHERE zeit >= TO_DATE ('2016-05-01', 'YYYY-MM-DD'));

CREATE OR REPLACE VIEW W_TAGESMITTEL_COUNT_V (MESSTELLEN_NR, ZEIT, W, W_COUNT, TOTAL_COUNT) AS SELECT messtellen_nr,
         TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD'), 'YYYY-MM-DD') AS zeit,
         AVG (w) AS w,
         COUNT (w) AS w_count,
         COUNT (*) AS total_count
    FROM w
GROUP BY messtellen_nr, TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD'), 'YYYY-MM-DD');

CREATE OR REPLACE VIEW V_W_TAGESMITTEL (MESSTELLEN_NR, ZEIT, W) AS SELECT messtellen_nr,
           zeit,
           CASE WHEN total_count - w_count <= 1 THEN w ELSE NULL END     AS w
      FROM w_tagesmittel_count_v
where zeit < to_date(to_char(current_date,'DD.MM.YYYY'), 'DD.MM.YYYY');

CREATE OR REPLACE VIEW W_STUNDENMITTEL_COUNT_V_BUND (MESSTELLEN_NR, ZEIT, W, W_COUNT, TOTAL_COUNT) AS SELECT messtellen_nr,
             TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24')
                 AS zeit,
             AVG (w)
                 AS w,
             COUNT (w)
                 AS w_count,
             COUNT (*)
                 AS total_count
        FROM w
           GROUP BY messtellen_nr,
             TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24');

CREATE OR REPLACE VIEW V_W_STUNDENMITTEL_BUND (MESSTELLEN_NR, ZEIT, W) AS SELECT messtellen_nr,
           zeit,
           CASE WHEN w_count = total_count THEN w ELSE NULL END     AS w
      FROM W_STUNDENMITTEL_COUNT_V_BUND;

CREATE OR REPLACE VIEW CHECK_15MIN (MESSTELLEN_NR, FEHLT, ANZAHL) AS (
   select
   messtellen_nr, TO_CHAR(zeit, 'YYYY-MM-DD HH24') as fehlt, count(*) as anzahl
   from w w1
   where zeit between TO_DATE('2016-05-01', 'YYYY-MM-DD')
   and
   (
      select
      TO_DATE
      (
         TO_CHAR(max(w2.zeit) - INTERVAL '1 day', 'YYYY-MM-DD') || '23:59', 'YYYY-MM-DD HH24:MI'
      )
      from w w2
      where w2.messtellen_nr=w1.messtellen_nr
   )
   group by messtellen_nr, TO_CHAR(zeit, 'YYYY-MM-DD HH24') having count(*) < 4
);

CREATE OR REPLACE VIEW W_STUNDENMITTEL_REDUNDANZ_COUNT_V (MESSTELLEN_NR, ZEIT, W, W_COUNT, TOTAL_COUNT) AS select messtellen_nr, TO_DATE(TO_CHAR(zeit, 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24') as zeit, avg(w) as w, count(w) as w_count, count(*) as total_count from w_redundanzsystem group by messtellen_nr, TO_DATE(TO_CHAR(zeit, 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24');

CREATE OR REPLACE VIEW V_W_STUNDENMITTEL_REDUNDANZ (MESSTELLEN_NR, ZEIT, W) AS (SELECT messtellen_nr,
        zeit,
        CASE WHEN w_count = total_count THEN w ELSE NULL END AS w
   FROM w_stundenmittel_redundanz_count_v);

CREATE OR REPLACE VIEW W_TAGESMITTEL_REDUNDANZ_COUNT_V (MESSTELLEN_NR, ZEIT, W, W_COUNT, TOTAL_COUNT, STATUS) AS select messtellen_nr, TO_DATE(TO_CHAR(zeit, 'YYYY-MM-DD'), 'YYYY-MM-DD') as zeit, avg(w) as w, count(w) as w_count, count(*) as total_count, max(status) as status from w_redundanzsystem group by messtellen_nr, TO_DATE(TO_CHAR(zeit, 'YYYY-MM-DD'), 'YYYY-MM-DD');

CREATE OR REPLACE VIEW V_W_TAGESMITTEL_REDUNDANZ (MESSTELLEN_NR, ZEIT, W, STATUS) AS select messtellen_nr, zeit, case when w_count>=(case when zeit<TO_DATE('2016-05-01', 'YYYY-MM-DD') then 23 else 96 end) then w else null end as w, status from w_tagesmittel_redundanz_count_v;

CREATE OR REPLACE VIEW V_HVZ_W_MAX (MESSTELLEN_NR, W, ZEIT) AS (SELECT messtellen_nr, w, zeit FROM w_max);

CREATE OR REPLACE VIEW V_HVZ_W_MAX (MESSTELLEN_NR, W, ZEIT) AS (SELECT messtellen_nr, w, zeit FROM w_max);

CREATE OR REPLACE VIEW V_W_MAX_UND_SCHEITEL (MESSTELLEN_NR, W, ZEIT) AS (select messtellen_nr, w, zeit from w_max union select messtellen_nr, w, zeit from w_scheitel);

CREATE OR REPLACE VIEW CHECK_MAX_1D (MESSTELLEN_NR, TAG, ERSTER, LETZTER, ANZAHL) AS (select messtellen_nr, to_char(zeit, 'YYYY-MM-DD') as tag, min(zeit) as erster, max(zeit) as letzter, count(*) as anzahl from w_max group by messtellen_nr, to_char(zeit, 'YYYY-MM-DD') having count(*) > 1);

CREATE OR REPLACE VIEW Q_STUNDENMITTEL_COUNT_V (MESSTELLEN_NR, ZEIT, Q, Q_COUNT, TOTAL_COUNT) AS SELECT messtellen_nr,
         TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24')
             AS zeit,
         AVG (q)
             AS q,
         COUNT (q)
             AS q_count,
         COUNT (*)
             AS total_count
    FROM q
   WHERE zeit <= TO_DATE ('2016-05-01', 'YYYY-MM-DD')
GROUP BY messtellen_nr,
         TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24');

CREATE OR REPLACE VIEW V_Q_STUNDENMITTEL (MESSTELLEN_NR, ZEIT, Q) AS (SELECT messtellen_nr, zeit, Q
   FROM Q
  WHERE zeit < TO_DATE ('2016-05-01', 'YYYY-MM-DD'))
UNION
(SELECT messtellen_nr,
        zeit,
        CASE WHEN q_count = total_count THEN q ELSE NULL END AS q
   FROM Q_STUNDENMITTEL_COUNT_V
  WHERE zeit >= TO_DATE ('2016-05-01', 'YYYY-MM-DD'));

CREATE OR REPLACE VIEW Q_STUNDENMITTEL_COUNT_V_BUND (MESSTELLEN_NR, ZEIT, Q, Q_COUNT, TOTAL_COUNT) AS SELECT messtellen_nr,
             TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24')
                 AS zeit,
             AVG (q)
                 AS q,
             COUNT (q)
                 AS q_count,
             COUNT (*)
                 AS total_count
        FROM q
    GROUP BY messtellen_nr,
             TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24');

CREATE OR REPLACE VIEW V_Q_STUNDENMITTEL_BUND (MESSTELLEN_NR, ZEIT, Q) AS SELECT messtellen_nr,
           zeit,
           CASE WHEN q_count = total_count THEN q ELSE NULL END     AS q
      FROM q_stundenmittel_count_v_bund;

CREATE OR REPLACE VIEW Q_TAGESMITTEL_COUNT_V (MESSTELLEN_NR, ZEIT, Q, Q_COUNT, TOTAL_COUNT) AS SELECT messtellen_nr,
         TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD'), 'YYYY-MM-DD')    AS zeit,
         AVG (q)                                                 AS q,
         COUNT (q)                                               AS q_count,
         COUNT (*)                                               AS total_count
    FROM q
GROUP BY messtellen_nr, TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD'), 'YYYY-MM-DD');

CREATE OR REPLACE VIEW V_Q_TAGESMITTEL (MESSTELLEN_NR, ZEIT, Q) AS SELECT messtellen_nr,
           zeit,
           CASE WHEN q_count = total_count THEN q ELSE NULL END     AS q
      FROM q_tagesmittel_count_v
      where zeit < to_date(to_char(current_date,'DD.MM.YYYY'),'DD.MM.YYYY');

CREATE OR REPLACE VIEW V_Q_TAGESMITTEL_BUNDESPEGEL (MESSTELLEN_NR, ZEIT, Q) AS (
select d.messtellen_nr, zeit, q from q_temp_reihen_definition d left outer join q_temp_reihen r on r.reihen_nr=d.id left outer join v_pegeldaten p on p.messtellen_nr=d.messtellen_nr where name='Geprüfte Tagesmittel' and geloescht=0 and messnetz_nr=7
union select t.messtellen_nr, zeit, q from v_q_tagesmittel t left outer join v_pegeldaten p on p.messtellen_nr=t.messtellen_nr where messnetz_nr=7 and t.messtellen_nr in (select messtellen_nr from q_temp_reihen_definition where name='Geprüfte Tagesmittel' and geloescht=0) and zeit>(select max(zeit) from q_temp_reihen where reihen_nr=(select id from q_temp_reihen_definition where messtellen_nr=t.messtellen_nr and name='Geprüfte Tagesmittel' and geloescht=0))
union select t.messtellen_nr, zeit, q from v_q_tagesmittel t left outer join v_pegeldaten p on p.messtellen_nr=t.messtellen_nr where messnetz_nr=7 and t.messtellen_nr not in (select messtellen_nr from q_temp_reihen_definition where name='Geprüfte Tagesmittel' and geloescht=0));

CREATE OR REPLACE VIEW Q_STUNDENMITTEL_REDUNDANZ_COUNT_V (MESSTELLEN_NR, ZEIT, Q, Q_COUNT, TOTAL_COUNT) AS SELECT messtellen_nr,
         TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24')
             AS zeit,
         AVG (q)
             AS q,
         COUNT (q)
             AS q_count,
         COUNT (*)
             AS total_count
    FROM Q_REDUNDANZ
GROUP BY messtellen_nr,
         TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24');

CREATE OR REPLACE VIEW V_Q_STUNDENMITTEL_REDUNDANZ (MESSTELLEN_NR, ZEIT, Q) AS SELECT messtellen_nr,
           zeit,
           CASE WHEN q_count = total_count THEN q ELSE NULL END     AS q
      FROM q_stundenmittel_redundanz_count_v;

CREATE OR REPLACE VIEW Q_TAGESMITTEL_REDUNDANZ_COUNT_V (MESSTELLEN_NR, ZEIT, Q, Q_COUNT, TOTAL_COUNT, STATUS) AS SELECT messtellen_nr,
         TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD'), 'YYYY-MM-DD')
             AS zeit,
         AVG (q)
             AS q,
         COUNT (q)
             AS q_count,
         COUNT (*)
             AS total_count,
         MAX (status)
             AS status
    FROM q_redundanz
GROUP BY messtellen_nr, TO_DATE (TO_CHAR (zeit, 'YYYY-MM-DD'), 'YYYY-MM-DD');

CREATE OR REPLACE VIEW V_Q_TAGESMITTEL_REDUNDANZ (MESSTELLEN_NR, ZEIT, Q, STATUS) AS SELECT messtellen_nr,
           zeit,
           CASE
               WHEN q_count >=
                    (CASE
                         WHEN zeit < TO_DATE ('2016-05-01', 'YYYY-MM-DD')
                         THEN
                             23
                         ELSE
                             96
                     END)
               THEN
                   q
               ELSE
                   NULL
           END    AS q,
           status
      FROM q_tagesmittel_redundanz_count_v;
