db/create_BTB_DB_0.1.3_Sprint6_OK210_notifications_fks_nullable.sql

/* ********************************************************************************** */
 -- Task: [OK-210] In table tbl_notification, make these foreign-keys nullable:      
 -- * fk_ref_branch                                                                  
 -- * fk_ref_grid_territory.                                                         
 -- Add an admin flag to distinguish instructions from pure notifications            
 -- Adapt view.                                                                      
 /* ********************************************************************************** */


/* *** 1. Make foreign keys nullable *** */

ALTER TABLE public.tbl_notification 
  ALTER COLUMN fk_ref_branch DROP NOT NULL;
  
ALTER TABLE public.tbl_notification 
  ALTER COLUMN fk_ref_grid_territory DROP NOT NULL;
 
/* *** 2. Add admin flag *** */

ALTER TABLE public.tbl_notification
  ADD COLUMN admin_flag BOOLEAN NOT NULL DEFAULT FALSE; 
 

/* *** 3. Adapt view according to the new admin flag *** */

-- View: public.view_active_notification
-- DROP VIEW public.view_active_notification;

-- Erstellt eine View, die die jeweils höchste Version einer Notification aus tbl_notification enthält.
CREATE OR REPLACE VIEW public.view_active_notification AS
 SELECT s.id,
    s.incident_id,
    s.version,
    s.fk_ref_branch,
    s.notification_text,
    s.free_text,
    s.free_text_extended,
    s.fk_ref_notification_status,
    s.responsibility_forwarding,
    s.reminder_date,
    s.expected_finished_date,
    s.responsibility_control_point,
    s.begin_date,
    s.finished_date,
    s.create_user,
    s.create_date,
    s.mod_user,
    s.mod_date,
    s.fk_ref_grid_territory,
    s.admin_flag
   FROM ( SELECT tbl_notification.id,
            tbl_notification.incident_id,
            tbl_notification.version,
            tbl_notification.fk_ref_branch,
            tbl_notification.notification_text,
            tbl_notification.free_text,
            tbl_notification.free_text_extended,
            tbl_notification.fk_ref_notification_status,
            tbl_notification.responsibility_forwarding,
            tbl_notification.reminder_date,
            tbl_notification.expected_finished_date,
            tbl_notification.responsibility_control_point,
            tbl_notification.begin_date,
            tbl_notification.finished_date,
            tbl_notification.create_user,
            tbl_notification.create_date,
            tbl_notification.mod_user,
            tbl_notification.mod_date,
            tbl_notification.fk_ref_grid_territory,
            tbl_notification.admin_flag,
            rank() OVER (PARTITION BY tbl_notification.incident_id ORDER BY tbl_notification.version DESC) AS rank
           FROM tbl_notification) s
  WHERE s.rank = 1;

ALTER TABLE public.view_active_notification
    OWNER TO btbservice;