db/db_dev_evolution/create_BTB_DB_0.1.1.sql

-- Database: betriebstagebuch


-- DROP DATABASE betriebstagebuch;

-- REVIEW: Why is drop commented out? A clean install is strongly encouraged  

/*
CREATE DATABASE betriebstagebuch
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'German_Germany.1252'
    LC_CTYPE = 'German_Germany.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

-- Role: btbservice

-- DROP ROLE btbservice;
CREATE ROLE btbservice LOGIN
  ENCRYPTED PASSWORD 'md5014d35e4f8632560130b265acb21413f'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
*/


/*
DROP SEQUENCE public.tbl_notification_incident_id_seq;

DROP TABLE public.tbl_notification;
DROP TABLE public."ref_version";
DROP TABLE public.ref_branch;
DROP TABLE public.ref_notification_status;
DROP TABLE public.tbl_settings;

DROP VIEW public.view_active_notification;
*/

-- Table: public."REF_VERSION" -------------------------------------------------
CREATE TABLE public."ref_version"
(
    "id" integer NOT NULL,
    "version" varchar(100) NOT NULL,
    CONSTRAINT "ref_version_pkey" PRIMARY KEY ("id")
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public."ref_version"
    OWNER to btbservice;



-- Table: public."REF_BRANCH" (Sparte) -----------------------------------------

CREATE TABLE public.ref_branch
(
    id SERIAL PRIMARY KEY,
    name character varying(50) NOT NULL,
    description character varying(255) NULL
)
WITH (
    OIDS=FALSE
);
ALTER TABLE public.ref_branch
    OWNER TO btbservice;

-- Table: public."REF_NOTIFICATION_STATUS" -----------------------------------------

CREATE TABLE public.ref_notification_status
(
    id SERIAL PRIMARY KEY,
    name character varying(50) NOT NULL
)
WITH (
    OIDS=FALSE
);
ALTER TABLE public.ref_notification_status
    OWNER TO btbservice;

-- Table: public.tbl_settings --------------------------------------------------------

CREATE TABLE public.tbl_settings
(
    parameter_name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    parameter_value character varying(100) COLLATE pg_catalog."default" NOT NULL
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.tbl_settings
    OWNER to btbservice;

-- Table: public."TBL_NOTIFICATION" ---------------------------------------------
-- DROP SEQUENCE public.tbl_notification_incident_id_seq;
CREATE SEQUENCE public.tbl_notification_incident_id_seq;

CREATE TABLE public.tbl_notification
(
  id SERIAL PRIMARY KEY,
  incident_id integer,
  version integer NOT NULL DEFAULT 0,
  fk_ref_branch integer NOT NULL,
  notification_text character varying(200) NOT NULL,
  free_text character varying(1000) NULL,
  free_text_extended character varying(1000) NULL,
  fk_ref_notification_status integer NOT NULL,
  responsibility_forwarding character varying(100),
  reminder_date timestamp without time zone NULL,
  expected_finished_date timestamp without time zone NULL,
  responsibility_control_point character varying(100),
  begin_date timestamp without time zone NOT NULL,
  finished_date timestamp without time zone NULL,
  create_user character varying(100) NOT NULL,
  create_date timestamp without time zone NOT NULL,
  mod_user character varying(100),
  mod_date timestamp without time zone
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.tbl_notification
  OWNER TO btbservice;

CREATE UNIQUE INDEX tbl_notification_incident_version_unique
   ON public.tbl_notification (incident_id ASC NULLS LAST, version ASC NULLS LAST);

ALTER TABLE public.tbl_notification
  ADD CONSTRAINT fk_notification_fk_status FOREIGN KEY (fk_ref_notification_status) REFERENCES public.ref_notification_status (id)
   ON UPDATE NO ACTION ON DELETE NO ACTION;
CREATE INDEX fki_notification_fk_status
  ON public.tbl_notification(fk_ref_notification_status);

ALTER TABLE public.tbl_notification
  ADD CONSTRAINT fk_notification_fk_branch FOREIGN KEY (fk_ref_branch) REFERENCES public.ref_branch (id)
   ON UPDATE NO ACTION ON DELETE NO ACTION;
CREATE INDEX fki_notification_fk_branch
  ON public.tbl_notification(fk_ref_branch);


--------------------------------------------------------------------------------
-- Triggers
--------------------------------------------------------------------------------

-- Trigger Function for setting the incident_id of a notification.
-- This function is called on every insert on tbl_notification.
-- Only for newly created notification we copy the id into the incident_id.

-- drop trigger tbl_notification_incident_trg on tbl_notification_test
CREATE or REPLACE FUNCTION tbl_notification_incident_trg() RETURNS trigger AS $tbl_notification_incident_trg$
    BEGIN
        IF NEW.incident_id IS NULL THEN
            NEW.incident_id := NEW.id;
        END IF;
        RETURN NEW;
    END;
$tbl_notification_incident_trg$ LANGUAGE plpgsql;
CREATE TRIGGER tbl_notification_incident_trg BEFORE INSERT ON tbl_notification
    FOR EACH ROW EXECUTE PROCEDURE tbl_notification_incident_trg();

--------------------------------------------------------------------------------
-- Views
--------------------------------------------------------------------------------

-- 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
   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,
            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;




-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- Fill DB Script
INSERT INTO public."ref_version" ("id", "version") VALUES ( 1, '0.1.1_Snapshot');

INSERT INTO public."ref_notification_status" ( id, name ) VALUES ( 1, 'offen' );
INSERT INTO public."ref_notification_status" ( id, name ) VALUES ( 2, 'in Arbeit' );
INSERT INTO public."ref_notification_status" ( id, name ) VALUES ( 3, 'erledigt' );
INSERT INTO public."ref_notification_status" ( id, name ) VALUES ( 4, 'geschlossen' );
SELECT setval('ref_notification_status_id_seq', 5, true);

INSERT INTO public."ref_branch" (id, name, description ) VALUES ( 1, 'S', 'Strom' );
INSERT INTO public."ref_branch" (id, name, description ) VALUES ( 2, 'G', 'Gas' );
INSERT INTO public."ref_branch" (id, name, description ) VALUES ( 3, 'F', 'Fernwärme' );
INSERT INTO public."ref_branch" (id, name, description ) VALUES ( 4, 'W', 'Wasser' );
SELECT setval('ref_branch_id_seq', 5, true);

INSERT INTO public."tbl_settings" (parameter_name, parameter_value ) VALUES ( 'notification_list_closed_max_age', '6');

-- REVIEW: Why are the Sprint comments necessary? 

-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- ENDE Sprint 1
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Anfang Sprint 2 30.05.2017
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
UPDATE public."ref_branch" SET name='F' WHERE id=3;

-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Anfang Sprint 3 12.06.2017
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- Drop Tables
DROP TABLE public.tbl_responsibility;
DROP TABLE public.ref_grid_territory;

-- Table: public."ref_grid_territory" (Netzgebiete) -------------------------------------------------
CREATE TABLE public."ref_grid_territory"
(
    id SERIAL PRIMARY KEY,
    name character varying(50) NOT NULL,
    description character varying(255) NULL
);

CREATE UNIQUE INDEX ref_grid_territory_description_unique
   ON public.ref_grid_territory (description ASC);

ALTER TABLE public.ref_grid_territory
   OWNER TO btbservice;

-- Table: public."tbl_responsibility" (Verantwortlichkeiten) -------------------------------------------------
CREATE TABLE public."tbl_responsibility"
(
    id SERIAL PRIMARY KEY,
    fk_ref_grid_territory integer NOT NULL,
    fk_ref_branch integer NOT NULL,
    responsible_user character varying(100) NOT NULL,
    new_responsible_user character varying(100),
    create_user character varying(100) NOT NULL,
    create_date timestamp without time zone NOT NULL,
    mod_user character varying(100),
    mod_date timestamp without time zone
);

CREATE UNIQUE INDEX tbl_responsibility_territory_branch_user_unique
   ON public.tbl_responsibility (fk_ref_grid_territory ASC, fk_ref_branch ASC);

ALTER TABLE public.tbl_responsibility
  ADD CONSTRAINT fk_grid_territory FOREIGN KEY (fk_ref_grid_territory) REFERENCES public.ref_grid_territory (id)
   ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE public.tbl_responsibility
  ADD CONSTRAINT fk_branch FOREIGN KEY (fk_ref_branch) REFERENCES public.ref_branch (id)
   ON UPDATE NO ACTION ON DELETE NO ACTION;


ALTER TABLE public.tbl_responsibility
   OWNER TO btbservice;

--REVIEW: Should be refactored to test data. We should have one script with DDL and multiple, content oriented DML scripts


-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- Fill DB Script

-- Table: public."ref_grid_territory" (Netzgebiete)
INSERT INTO public."ref_grid_territory" (id, name, description) VALUES ( 1, 'MA', 'Mannheim');
INSERT INTO public."ref_grid_territory" (id, name, description) VALUES ( 2, 'OF', 'Offenbach');


-- Table: public."tbl_responsibility" (Verantwortlichkeiten) -------------------------------------------------
INSERT INTO public."tbl_responsibility" (id, fk_ref_grid_territory, fk_ref_branch, responsible_user, create_user, create_date) VALUES ( 1, 1, 1, 'admin','admin',now());
INSERT INTO public."tbl_responsibility" (id, fk_ref_grid_territory, fk_ref_branch, responsible_user, create_user, create_date) VALUES ( 2, 1, 2, 'admin','admin',now());
INSERT INTO public."tbl_responsibility" (id, fk_ref_grid_territory, fk_ref_branch, responsible_user, create_user, create_date) VALUES ( 3, 1, 3, 'admin','admin',now());
INSERT INTO public."tbl_responsibility" (id, fk_ref_grid_territory, fk_ref_branch, responsible_user, create_user, create_date) VALUES ( 4, 2, 2, 'admin','admin',now());
INSERT INTO public."tbl_responsibility" (id, fk_ref_grid_territory, fk_ref_branch, responsible_user, create_user, create_date) VALUES ( 5, 2, 3, 'otto','admin',now());
INSERT INTO public."tbl_responsibility" (id, fk_ref_grid_territory, fk_ref_branch, responsible_user, create_user, create_date) VALUES ( 6, 1, 4, 'otto','admin',now());


-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Ende Sprint 3 21.06.2017
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>