drop view if exists BUDGET.TARIF_PROGRESSIV_DENORM;

create or replace view BUDGET.TARIF_PROGRESSIV_DENORM as
      select K.CODE as KANTONSCODE,
             K.NAME as KANTONSNAME,
             TG.CODE as TARIFGRUPPENCODE,
             TG.BESCHREIBUNG as TARIFGRUPPENBESCHREIBUNG,
             TP.KINDER_ANZAHL,
             TP.KIRCHENSTEUER_FLAG,
             TP.EINKOMMEN_STEUERBARES_RANGE,
             lower(TP.EINKOMMEN_STEUERBARES_RANGE) as EINKOMMEN_STEUERBARES_RANGE_LOW_BOUNDARY,
             upper(TP.EINKOMMEN_STEUERBARES_RANGE) as EINKOMMEN_STEUERBARES_RANGE_HIGH_BOUNDARY,
             TP.TARIFSCHRITT,
             TP.MINDESTSTEUER,
             TP.STEUERSATZ,
             LV.LADEVORGANG_PT,
             TP.LADEVORGAENGE⠒ID,
             TP.ID
        from BUDGET.TARIF_PROGRESSIV TP
  inner join BUDGET_LOG.LADEVORGAENGE LV
          on TP.LADEVORGAENGE⠒ID = LV.ID
  inner join BUDGET_MASTER.KANTON K
          on TP.KANTON⠒ID = K.ID
  inner join BUDGET_MASTER.TARIFGRUPPE TG
          on TP.TARIFGRUPPE⠒ID = TG.ID;


comment on column BUDGET.TARIF_PROGRESSIV_DENORM.KANTONSCODE is
  'Abkürzung des Kantonsnamen';

comment on column BUDGET.TARIF_PROGRESSIV_DENORM.LADEVORGANG_PT is
  'Zeitpunkt, wann der Ladevorgang war, der den Datensatz in die Tabelle geschrieben hat. Dies ist unbesehen von Vorgängen, z. B. Konsolidierung, die den Satz noch verändern.';

comment on view BUDGET.TARIF_PROGRESSIV_DENORM is
  'Informationen progressiver Tarife angereichert mit Kantos-, Tarifgruppen- und Ladeinformationen
$Id$';


-- create or replace function BUDGET.TARIF_PROGRESSIV_DENORM⠒INS_DEL() returns trigger
  -- as
  -- $CODE$
      -- BEGIN
          -- raise warning 'Insert or deletes are not allowed on TARIF_PROGRESSIV_DENORM. For inserts use the batch programme. Deletes should be made as changes to the galus of WIEDERKEHREND.';
      -- END;
  -- $CODE$
  -- language plpgsql;

-- create or replace trigger TARIF_PROGRESSIV_DENORM⠒INS_DEL
  -- before insert or delete
  -- on BUDGET.TARIF_PROGRESSIV_DENORM
  -- for each statement
  -- execute function BUDGET.TARIF_PROGRESSIV_DENORM⠒INS_DEL();

create or replace rule TARIF_PROGRESSIV_DENORM⠒INS as
  on insert
  to BUDGET.TARIF_PROGRESSIV_DENORM
  do instead
    insert into TARIF_PROGRESSIV (KANTON⠒ID,
                                  TARIFGRUPPE⠒ID,
                                  KINDER_ANZAHL,
                                  KIRCHENSTEUER_FLAG,
                                  EINKOMMEN_STEUERBARES_RANGE,
                                  TARIFSCHRITT,
                                  MINDESTSTEUER,
                                  STEUERSATZ,
                                  LADEVORGAENGE⠒ID)
        values (/* KANTON⠒ID */                   case
                                                      -- a disjoint
                                                      when new.KANTONSCODE is not null and new.KANTONSNAME is null then (select ID
                                                                                                                           from BUDGET_MASTER.KANTON
                                                                                                                          where 1 = 1
                                                                                                                            and CODE = new.KANTONSCODE
                                                                                                                            and 1 = 1)
                                                      -- the other disjoint
                                                      when new.KANTONSCODE is null and new.KANTONSNAME is not null then (select ID
                                                                                                                           from BUDGET_MASTER.KANTON
                                                                                                                          where 1 = 1
                                                                                                                            and NAME = new.KANTONSNAME
                                                                                                                            and 1 = 1)
                                                      -- check for conflicts
                                                      when new.KANTONSCODE is not null and new.KANTONSNAME is not null then (select ID
                                                                                                                               from BUDGET_MASTER.KANTON
                                                                                                                              where 1 = 1
                                                                                                                                and CODE = new.KANTONSCODE
                                                                                                                                and NAME = new.KANTONSNAME
                                                                                                                                and 1 = 1)
                                                      -- fallback
                                                      else null
                                                  end,
                /* TARIFGRUPPE⠒ID */              case
                                                      -- a disjoint
                                                      when new.TARIFGRUPPENCODE is not null and new.TARIFGRUPPENBESCHREIBUNG is null then (select ID
                                                                                                                                             from BUDGET_MASTER.TARIFGRUPPE
                                                                                                                                            where 1 = 1
                                                                                                                                              and CODE = new.TARIFGRUPPENCODE
                                                                                                                                              and 1 = 1)
                                                      -- the other disjoint
                                                      when new.TARIFGRUPPENCODE is null and new.TARIFGRUPPENBESCHREIBUNG is not null then (select ID
                                                                                                                                             from BUDGET_MASTER.TARIFGRUPPE
                                                                                                                                            where 1 = 1
                                                                                                                                              and BESCHREIBUNG = new.TARIFGRUPPENBESCHREIBUNG
                                                                                                                                              and 1 = 1)
                                                      -- check for conflicts
                                                      when new.TARIFGRUPPENCODE is not null and new.TARIFGRUPPENBESCHREIBUNG is not null then (select ID
                                                                                                                                                 from BUDGET_MASTER.TARIFGRUPPE
                                                                                                                                                where 1 = 1
                                                                                                                                                  and CODE = new.TARIFGRUPPENCODE
                                                                                                                                                  and BESCHREIBUNG = new.TARIFGRUPPENBESCHREIBUNG
                                                                                                                                                  and 1 = 1)
                                                      -- fallback
                                                      else null
                                                  end,
                /* KINDER_ANZAHL */               new.KINDER_ANZAHL,
                /* KIRCHENSTEUER_FLAG */          new.KIRCHENSTEUER_FLAG,
                /* EINKOMMEN_STEUERBARES_RANGE */ case
                                                      -- most frequent and disjoint
                                                      when new.EINKOMMEN_STEUERBARES_RANGE is null
                                                       and (   new.EINKOMMEN_STEUERBARES_RANGE_LOW_BOUNDARY is not null
                                                            or new.EINKOMMEN_STEUERBARES_RANGE_HIGH_BOUNDARY is not null) then numrange(new.EINKOMMEN_STEUERBARES_RANGE_LOW_BOUNDARY, new.EINKOMMEN_STEUERBARES_RANGE_HIGH_BOUNDARY)
                                                      -- the other disjoint
                                                      when new.EINKOMMEN_STEUERBARES_RANGE is not null
                                                       and new.EINKOMMEN_STEUERBARES_RANGE_LOW_BOUNDARY is null
                                                       and new.EINKOMMEN_STEUERBARES_RANGE_HIGH_BOUNDARY is null then new.EINKOMMEN_STEUERBARES_RANGE
                                                      -- check for conflicts
                                                      when new.EINKOMMEN_STEUERBARES_RANGE is not null
                                                       and (new.EINKOMMEN_STEUERBARES_RANGE_LOW_BOUNDARY is not null or new.EINKOMMEN_STEUERBARES_RANGE_HIGH_BOUNDARY is not null)
                                                       and new.EINKOMMEN_STEUERBARES_RANGE = numrange(new.EINKOMMEN_STEUERBARES_RANGE_LOW_BOUNDARY, new.EINKOMMEN_STEUERBARES_RANGE_HIGH_BOUNDARY) then new.EINKOMMEN_STEUERBARES_RANGE
                                                      -- fallback
                                                      else null
                                                  end,
                /* TARIFSCHRITT */                new.TARIFSCHRITT,
                /* MINDESTSTEUER */               new.MINDESTSTEUER,
                /* STEUERSATZ */                  new.STEUERSATZ,
                /* LADEVORGAENGE⠒ID */            case
                                                      -- most frequent and disjoint
                                                      when new.LADEVORGAENGE⠒ID is not null
                                                       and new.LADEVORGANG_PT is null then new.LADEVORGAENGE⠒ID
                                                      -- the other disjoint
                                                      when new.LADEVORGAENGE⠒ID is null
                                                       and new.LADEVORGANG_PT is not null then (select ID
                                                                                                  from BUDGET_LOG.LADEVORGAENGE
                                                                                                 where 1 = 1
                                                                                                   and LADEVORGANG_PT = new.LADEVORGANG_PT
                                                                                                   and 1 = 1)
                                                      -- check for conflicts
                                                      when new.LADEVORGAENGE⠒ID is not null and new.LADEVORGANG_PT is not null then (select ID
                                                                                                                                       from BUDGET_LOG.LADEVORGAENGE
                                                                                                                                      where 1 = 1
                                                                                                                                        and ID = new.LADEVORGAENGE⠒ID
                                                                                                                                        and LADEVORGANG_PT = new.LADEVORGANG_PT
                                                                                                                                        and 1 = 1)
                                                      -- fallback
                                                      else null
                                                  end);

create or replace rule TARIF_PROGRESSIV_DENORM⠒UPD as
  on update
  to BUDGET.TARIF_PROGRESSIV_DENORM
  do instead
    update TARIF_PROGRESSIV
       set KANTON⠒ID = case
                           -- a disjoint
                           when new.KANTONSCODE is not null and new.KANTONSNAME is null then (select ID
                                                                                                from BUDGET_MASTER.KANTON
                                                                                               where 1 = 1
                                                                                                 and CODE = new.KANTONSCODE
                                                                                                 and 1 = 1)
                           -- the other disjoint
                           when new.KANTONSCODE is null and new.KANTONSNAME is not null then (select ID
                                                                                                from BUDGET_MASTER.KANTON
                                                                                               where 1 = 1
                                                                                                 and NAME = new.KANTONSNAME
                                                                                                 and 1 = 1)
                           -- check for conflicts
                           when new.KANTONSCODE is not null and new.KANTONSNAME is not null then (select ID
                                                                                                    from BUDGET_MASTER.KANTON
                                                                                                   where 1 = 1
                                                                                                     and CODE = new.KANTONSCODE
                                                                                                     and NAME = new.KANTONSNAME
                                                                                                     and 1 = 1)
                           -- fallback
                           else null
                       end,
           TARIFGRUPPE⠒ID = case
                                -- a disjoint
                                when new.TARIFGRUPPENCODE is not null and new.TARIFGRUPPENBESCHREIBUNG is null then (select ID
                                                                                                                       from BUDGET_MASTER.TARIFGRUPPE
                                                                                                                      where 1 = 1
                                                                                                                        and CODE = new.TARIFGRUPPENCODE
                                                                                                                        and 1 = 1)
                                -- the other disjoint
                                when new.TARIFGRUPPENCODE is null and new.TARIFGRUPPENBESCHREIBUNG is not null then (select ID
                                                                                                                       from BUDGET_MASTER.TARIFGRUPPE
                                                                                                                      where 1 = 1
                                                                                                                        and BESCHREIBUNG = new.TARIFGRUPPENBESCHREIBUNG
                                                                                                                        and 1 = 1)
                                -- check for conflicts
                                when new.TARIFGRUPPENCODE is not null and new.TARIFGRUPPENBESCHREIBUNG is not null then (select ID
                                                                                                                           from BUDGET_MASTER.TARIFGRUPPE
                                                                                                                          where 1 = 1
                                                                                                                            and CODE = new.TARIFGRUPPENCODE
                                                                                                                            and BESCHREIBUNG = new.TARIFGRUPPENBESCHREIBUNG
                                                                                                                            and 1 = 1)
                                -- fallback
                                else null
                            end,
           KINDER_ANZAHL = coalesce(new.KINDER_ANZAHL, old.KINDER_ANZAHL),
           KIRCHENSTEUER_FLAG = coalesce(new.KIRCHENSTEUER_FLAG, old.KIRCHENSTEUER_FLAG),
           EINKOMMEN_STEUERBARES_RANGE = coalesce(new.EINKOMMEN_STEUERBARES_RANGE, old.EINKOMMEN_STEUERBARES_RANGE),
           TARIFSCHRITT = coalesce(new.TARIFSCHRITT, old.TARIFSCHRITT),
           MINDESTSTEUER = coalesce(new.MINDESTSTEUER, old.MINDESTSTEUER),
           STEUERSATZ = coalesce(new.STEUERSATZ, old.STEUERSATZ)
     where id = old.ID;

create or replace rule TARIF_PROGRESSIV_DENORM⠒DEL as
  on delete
  to BUDGET.TARIF_PROGRESSIV_DENORM
  do instead
    delete
      from TARIF_PROGRESSIV
     where id = old.id;


commit;