db/postgreSQL/02_create_DB_1.0.0.sql

CREATE SEQUENCE public.HTBL_RESPONSIBILITY_ID_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 354
  CACHE 1;
ALTER TABLE public.HTBL_RESPONSIBILITY_ID_SEQ
  OWNER TO btbservice;


CREATE SEQUENCE public.REF_BRANCH_ID_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 5
  CACHE 1;
ALTER TABLE public.REF_BRANCH_ID_SEQ
  OWNER TO btbservice;

CREATE SEQUENCE public.REF_GRID_TERRITORY_ID_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 2
  CACHE 1;
ALTER TABLE public.REF_GRID_TERRITORY_ID_SEQ
  OWNER TO btbservice;

CREATE SEQUENCE public.REF_NOTIFICATION_STATUS_ID_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 5
  CACHE 1;
ALTER TABLE public.REF_NOTIFICATION_STATUS_ID_SEQ
  OWNER TO btbservice;

CREATE SEQUENCE public.TBL_NOTIFICATION_ID_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1395
  CACHE 1;
ALTER TABLE public.TBL_NOTIFICATION_ID_SEQ
  OWNER TO btbservice;

CREATE SEQUENCE public.TBL_NOTIFICATION_INCIDENT_ID_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 81
  CACHE 1;
ALTER TABLE public.TBL_NOTIFICATION_INCIDENT_ID_SEQ
  OWNER TO btbservice;

CREATE SEQUENCE public.TBL_RESPONSIBILITY_ID_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 21
  CACHE 1;
ALTER TABLE public.TBL_RESPONSIBILITY_ID_SEQ
  OWNER TO btbservice;


CREATE TABLE public.REF_BRANCH
(
  id integer NOT NULL DEFAULT nextval('ref_branch_id_seq'::regclass),
  name character varying(50) NOT NULL,
  description character varying(255),
  CONSTRAINT REF_BRANCH_PKEY PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.REF_BRANCH
  OWNER TO btbservice;
GRANT ALL ON TABLE public.REF_BRANCH TO btbservice;


CREATE TABLE public.REF_GRID_TERRITORY
(
  id integer NOT NULL DEFAULT nextval('ref_grid_territory_id_seq'::regclass),
  name character varying(50) NOT NULL,
  description character varying(255),
  fk_ref_master integer NOT NULL,
  CONSTRAINT REF_GRID_TERRITORY_PKEY PRIMARY KEY (id),
  CONSTRAINT FK_REF_GRID_TERRITORY_SELF FOREIGN KEY (fk_ref_master)
      REFERENCES public.REF_GRID_TERRITORY (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.REF_GRID_TERRITORY
  OWNER TO btbservice;
GRANT ALL ON TABLE public.REF_GRID_TERRITORY TO btbservice;

-- Index: public.fki_fk_grid_territory_self

-- DROP INDEX public.fki_fk_grid_territory_self;

CREATE INDEX FKI_FK_GRID_TERRITORY_SELF
  ON public.REF_GRID_TERRITORY
  USING btree
  (fk_ref_master);

-- Index: public.ref_grid_territory_description_unique

-- DROP INDEX public.ref_grid_territory_description_unique;

CREATE UNIQUE INDEX REF_GRID_TERRITORY_DESCRIPTION_UNIQUE
  ON public.REF_GRID_TERRITORY
  USING btree
  (description COLLATE pg_catalog."default");


CREATE TABLE public.REF_NOTIFICATION_STATUS
(
  id integer NOT NULL DEFAULT nextval('ref_notification_status_id_seq'::regclass),
  name character varying(50) NOT NULL,
  CONSTRAINT REF_NOTIFICATION_STATUS_PKEY PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.REF_NOTIFICATION_STATUS
  OWNER TO btbservice;
GRANT ALL ON TABLE public.REF_NOTIFICATION_STATUS TO btbservice;


CREATE TABLE public.REF_VERSION
(
  id integer NOT NULL,
  version character varying(100) NOT NULL,
  CONSTRAINT ref_version_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.REF_VERSION
  OWNER TO btbservice;
GRANT ALL ON TABLE public.REF_VERSION TO btbservice;


CREATE TABLE public.TBL_NOTIFICATION
(
  id integer NOT NULL DEFAULT nextval('tbl_notification_id_seq'::regclass),
  incident_id integer DEFAULT nextval('tbl_notification_incident_id_seq'::regclass),
  version integer NOT NULL DEFAULT 0,
  fk_ref_branch integer,
  notification_text character varying(200) NOT NULL,
  free_text character varying(1000),
  free_text_extended character varying(1000),
  fk_ref_notification_status integer NOT NULL,
  responsibility_forwarding character varying(100),
  reminder_date timestamp without time zone,
  expected_finished_date timestamp without time zone,
  responsibility_control_point character varying(100),
  begin_date timestamp without time zone NOT NULL,
  finished_date timestamp without time zone,
  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,
  fk_ref_grid_territory integer,
  admin_flag boolean NOT NULL DEFAULT false,
  CONSTRAINT TBL_NOTIFICATION_PKEY PRIMARY KEY (id),
  CONSTRAINT FK_NOTIFICATION_FK_BRANCH FOREIGN KEY (fk_ref_branch)
      REFERENCES public.REF_BRANCH (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT FK_NOTIFICATION_FK_GRID_TERRITORY FOREIGN KEY (fk_ref_grid_territory)
      REFERENCES public.REF_GRID_TERRITORY (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT FK_NOTIFICATION_FK_STATUS FOREIGN KEY (fk_ref_notification_status)
      REFERENCES public.REF_NOTIFICATION_STATUS (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.TBL_NOTIFICATION
  OWNER TO btbservice;
GRANT ALL ON TABLE public.TBL_NOTIFICATION TO btbservice;

-- Index: public.create_date_idx

-- DROP INDEX public.create_date_idx;

CREATE INDEX CREATE_DATE_IDX
  ON public.TBL_NOTIFICATION
  USING btree
  (create_date);

-- Index: public.fki_notification_fk_branch

-- DROP INDEX public.fki_notification_fk_branch;

CREATE INDEX FKI_NOTIFICATION_FK_BRANCH
  ON public.TBL_NOTIFICATION
  USING btree
  (fk_ref_branch);

-- Index: public.fki_notification_fk_grid_territory

-- DROP INDEX public.fki_notification_fk_grid_territory;

CREATE INDEX FKI_NOTIFICATION_FK_GRID_TERRITORY
  ON public.TBL_NOTIFICATION
  USING btree
  (fk_ref_grid_territory);

-- Index: public.fki_notification_fk_status

-- DROP INDEX public.fki_notification_fk_status;

CREATE INDEX FKI_NOTIFICATION_FK_STATUS
  ON public.TBL_NOTIFICATION
  USING btree
  (fk_ref_notification_status);

-- Index: public.mod_date_idx

-- DROP INDEX public.mod_date_idx;

CREATE INDEX MOD_DATE_IDX
  ON public.TBL_NOTIFICATION
  USING btree
  (mod_date);

-- Index: public.responsibility_forwarding_idx

-- DROP INDEX public.responsibility_forwarding_idx;

CREATE INDEX RESPONSIBILITY_FORWARDING_IDX
  ON public.TBL_NOTIFICATION
  USING btree
  (responsibility_forwarding COLLATE pg_catalog."default");

-- Index: public.tbl_notification_incident_version_unique

-- DROP INDEX public.tbl_notification_incident_version_unique;

CREATE UNIQUE INDEX TBL_NOTIFICATION_INCIDENT_VERSION_UNIQUE
  ON public.tbl_notification
  USING btree
  (incident_id, version);

  
CREATE OR REPLACE FUNCTION public.TBL_NOTIFICATION_INCIDENT_TRG()
  RETURNS trigger AS
$BODY$
    BEGIN

        IF NEW.incident_id IS NULL THEN
            NEW.incident_id := NEW.id;
        END IF;

        RETURN NEW;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.TBL_NOTIFICATION_INCIDENT_TRG()
  OWNER TO btbservice;
  

-- Trigger: tbl_notification_incident_trg on public.tbl_notification

-- DROP TRIGGER tbl_notification_incident_trg ON public.tbl_notification;

CREATE TRIGGER TBL_NOTIFICATION_INCIDENT_TRG
  BEFORE INSERT
  ON public.tbl_notification
  FOR EACH ROW
  EXECUTE PROCEDURE public.TBL_NOTIFICATION_INCIDENT_TRG();


CREATE TABLE public.TBL_RESPONSIBILITY
(
  id integer NOT NULL DEFAULT nextval('tbl_responsibility_id_seq'::regclass),
  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,
  CONSTRAINT TBL_RESPONSIBILITY_PKEY PRIMARY KEY (id),
  CONSTRAINT FK_BRANCH FOREIGN KEY (fk_ref_branch)
      REFERENCES public.REF_BRANCH (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT FK_GRID_TERRITORY FOREIGN KEY (fk_ref_grid_territory)
      REFERENCES public.REF_GRID_TERRITORY (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.TBL_RESPONSIBILITY
  OWNER TO btbservice;
GRANT ALL ON TABLE public.TBL_RESPONSIBILITY TO btbservice;

-- Index: public.tbl_responsibility_territory_branch_user_unique

-- DROP INDEX public.tbl_responsibility_territory_branch_user_unique;

CREATE UNIQUE INDEX TBL_RESPONSIBILITY_TERRITORY_BRANCH_USER_UNIQUE
  ON public.TBL_RESPONSIBILITY
  USING btree
  (fk_ref_grid_territory, fk_ref_branch);


CREATE TABLE public.HTBL_RESPONSIBILITY
(
  id integer NOT NULL DEFAULT nextval('htbl_responsibility_id_seq'::regclass),
  fk_ref_grid_territory integer NOT NULL,
  fk_ref_branch integer NOT NULL,
  transfer_date timestamp without time zone NOT NULL,
  transaction_id integer NOT NULL,
  responsible_user character varying(100) NOT NULL,
  former_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,
  CONSTRAINT HTBL_RESPONSIBILITY_PKEY PRIMARY KEY (id),
  CONSTRAINT fk_branch FOREIGN KEY (fk_ref_branch)
      REFERENCES public.REF_BRANCH (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT FK_GRID_TERRITORY FOREIGN KEY (fk_ref_grid_territory)
      REFERENCES public.REF_GRID_TERRITORY (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.HTBL_RESPONSIBILITY
  OWNER TO btbservice;

-- Index: public.htbl_responsibility_territory_branch_user_transaction_id_unique

-- DROP INDEX public.htbl_responsibility_territory_branch_user_transaction_id_unique;

CREATE UNIQUE INDEX HTBL_RESPONSIBILITY_TERRITORY_BRANCH_USER_TRANSACTION_ID_UNIQUE
  ON public.HTBL_RESPONSIBILITY
  USING btree
  (fk_ref_grid_territory, fk_ref_branch, transaction_id);


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;
GRANT ALL ON TABLE public.VIEW_ACTIVE_NOTIFICATION TO btbservice;