
-- ATTACHMENT

CREATE SEQUENCE tbl_attachment_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;
   

CREATE TABLE tbl_attachment (
    id NUMBER(10) DEFAULT tbl_attachment_id_seq.nextval NOT NULL,
    file_name character varying(255),
    file_type character varying(255),
    statement_id NUMBER(19),
    attachment_lob_id NUMBER(10),
    len NUMBER(19),
    ts timestamp
);


ALTER TABLE tbl_attachment ADD (
  CONSTRAINT tbl_attachment_pk PRIMARY KEY (id));
 
CREATE SEQUENCE tbl_attachment_lob_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;
   

CREATE TABLE tbl_attachment_lob (
    id NUMBER(10) DEFAULT tbl_attachment_lob_id_seq.nextval NOT NULL,
    value BLOB NOT NULL
);


ALTER TABLE tbl_attachment_lob ADD (
  CONSTRAINT tbl_attachment_lob_pk PRIMARY KEY (id));

-- TAG

 
CREATE TABLE tbl_tag (
    id character varying(255) NOT NULL,
    name character varying(255) NOT NULL
);

ALTER TABLE tbl_tag ADD (
  CONSTRAINT tbl_tag_pk PRIMARY KEY (ID));

 
-- ATTACHMENT_2_TAG


CREATE TABLE tbl_attachment2tag (
    tag_id character varying(255) NOT NULL,
    attachment_id NUMBER(19) NOT NULL
);

ALTER TABLE tbl_attachment2tag
    ADD CONSTRAINT tbl_attachment2tag_pkey PRIMARY KEY (attachment_id, tag_id);

-- COMMENT

CREATE SEQUENCE tbl_comment_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

CREATE TABLE tbl_comment (
    id NUMBER(10) DEFAULT tbl_comment_id_seq.nextval NOT NULL,
    disabled NUMBER(1),
    text CLOB NOT NULL,
    ts timestamp,
    user_id NUMBER(10),
    workflow_id NUMBER(10)
);

ALTER TABLE tbl_comment ADD (
  CONSTRAINT tbl_comment_pk PRIMARY KEY (ID));

-- DEPARTMENT

 CREATE SEQUENCE tbl_department_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

CREATE TABLE tbl_department (
    id NUMBER(10) DEFAULT tbl_department_id_seq.nextval NOT NULL,
    departmentgroup character varying(255),
    name character varying(255)
);

ALTER TABLE tbl_department ADD (
  CONSTRAINT tbl_department_pk PRIMARY KEY (id));

ALTER TABLE tbl_department
    ADD CONSTRAINT tbl_department_name_departmentgroup_u UNIQUE (name, departmentgroup);

-- DEPARTMENT_STRUCTURE

 CREATE SEQUENCE tbl_departmentstructure_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;


 CREATE TABLE tbl_departmentstructure (
    id NUMBER(10) DEFAULT tbl_departmentstructure_id_seq.nextval NOT NULL,
    definition CLOB NOT NULL,
    version NUMBER(10) NOT NULL
);

ALTER TABLE tbl_departmentstructure ADD (
  CONSTRAINT tbl_departmentstructure_pk PRIMARY KEY (id));
 
-- REQ_DEPARTMENT

 CREATE SEQUENCE tbl_reqdepartment_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;


CREATE TABLE tbl_reqdepartment (
    id NUMBER(10) DEFAULT tbl_reqdepartment_id_seq.nextval NOT NULL,
    contributed NUMBER(1),
    optional NUMBER(1),
    department_id NUMBER(10),
    workflowdata_id NUMBER(10)
);

ALTER TABLE tbl_reqdepartment ADD (
  CONSTRAINT tbl_reqdepartment_pk PRIMARY KEY (id));

-- STATEMENT


CREATE SEQUENCE tbl_statement_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;


CREATE TABLE tbl_statement (
    id NUMBER(10) DEFAULT tbl_statement_id_seq.nextval NOT NULL,
    business_key character varying(40) NOT NULL,
    city character varying(255) NOT NULL,
    district character varying(255) NOT NULL,
    due_date date NOT NULL,
    finished NUMBER(1) NOT NULL,
    receipt_date date NOT NULL,
    title character varying(255) NOT NULL,
    contact_db_id character varying(255) NOT NULL,
    department_structure_id NUMBER(10) NOT NULL,
    type_id NUMBER(10) NOT NULL,
    source_mail_id character varying(255),
    creation_date date NOT NULL,
    customer_reference character varying(255),
    finished_date date
);

ALTER TABLE tbl_statement ADD (
  CONSTRAINT tbl_statement_pk PRIMARY KEY (id));

-- STATMENET_2_PARENT

CREATE TABLE tbl_statement2parent (
    statement_id NUMBER(10) NOT NULL,
    parent_id NUMBER(10) NOT NULL
);

ALTER TABLE tbl_statement2parent
    ADD CONSTRAINT tbl_statement2parent_pkey PRIMARY KEY (parent_id, statement_id);

-- STATEMENT_EDIT_LOG

CREATE SEQUENCE tbl_statement_edit_log_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

CREATE TABLE tbl_statement_edit_log (
    id NUMBER(10) DEFAULT tbl_statement_edit_log_id_seq.nextval NOT NULL,
    accesstype character varying(255) NOT NULL,
    ts timestamp(0) NOT NULL,
    statement_id NUMBER(10) NOT NULL,
    user_id NUMBER(10) NOT NULL
);

ALTER TABLE tbl_statement_edit_log ADD (
  CONSTRAINT tbl_statement_edit_log_pk PRIMARY KEY (id));

-- STATEMENT_TYPE
 
CREATE SEQUENCE tbl_statementtype_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

 
CREATE TABLE tbl_statementtype (
    id NUMBER(10) DEFAULT tbl_statementtype_id_seq.nextval NOT NULL,
    name character varying(255)
);


ALTER TABLE tbl_statementtype ADD (
  CONSTRAINT tbl_statementtype_pk PRIMARY KEY (id));

-- TEXTBLOCK_DEFINITION

CREATE SEQUENCE tbl_textblockdefinition_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;
 
CREATE TABLE tbl_textblockdefinition (
    id NUMBER(10) DEFAULT tbl_textblockdefinition_id_seq.nextval NOT NULL,
    definition CLOB NOT NULL,
    version NUMBER(10) NOT NULL
);

ALTER TABLE tbl_textblockdefinition ADD (
  CONSTRAINT tbl_textblockdefinition_pk PRIMARY KEY (id));
 
-- USER

CREATE SEQUENCE tbl_user_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;


CREATE TABLE tbl_user (
    id NUMBER(10) DEFAULT tbl_user_id_seq.nextval NOT NULL,
    first_name character varying(255) NOT NULL,
    last_name character varying(255) NOT NULL,
    username character varying(255) NOT NULL,
    email_address character varying(255),
    department_id NUMBER(10)
);

ALTER TABLE tbl_user ADD (
  CONSTRAINT tbl_user_pk PRIMARY KEY (id));
 
ALTER TABLE tbl_user
    ADD CONSTRAINT tbl_user_username_u UNIQUE (username);

 
-- WORKFLOW_DATA

CREATE SEQUENCE tbl_workflowdata_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

 
CREATE TABLE tbl_workflowdata (
    id NUMBER(10) DEFAULT tbl_workflowdata_id_seq.nextval NOT NULL,
    draft CLOB,
    initial_state NUMBER(1),
    pos character varying(255),
    statement_id NUMBER(10) NOT NULL,
    textblockdefinition_id NUMBER(10) NOT NULL
);

ALTER TABLE tbl_workflowdata ADD (
  CONSTRAINT tbl_workflowdata_pk PRIMARY KEY (id));
 
ALTER TABLE tbl_workflowdata
    ADD CONSTRAINT tbl_workflowdata_statement_u UNIQUE (statement_id);
    
    
-- FKs

ALTER TABLE tbl_statement_edit_log
    ADD CONSTRAINT tbl_statement_edit_log_fk_1 FOREIGN KEY (statement_id) REFERENCES tbl_statement(id);
ALTER TABLE tbl_statement_edit_log
    ADD CONSTRAINT tbl_statement_edit_log_fk_2 FOREIGN KEY (user_id) REFERENCES tbl_user(id);
   

ALTER TABLE tbl_reqdepartment
    ADD CONSTRAINT tbl_reqdepartment_fk_1 FOREIGN KEY (department_id) REFERENCES tbl_department(id);
ALTER TABLE tbl_reqdepartment
    ADD CONSTRAINT tbl_reqdepartment_fk_2 FOREIGN KEY (workflowdata_id) REFERENCES tbl_workflowdata(id);


ALTER TABLE tbl_attachment2tag
    ADD CONSTRAINT tbl_attachment2tag_fk_1 FOREIGN KEY (attachment_id) REFERENCES tbl_attachment(id);
ALTER TABLE tbl_attachment2tag
    ADD CONSTRAINT tbl_attachment2tag_fk_2 FOREIGN KEY (tag_id) REFERENCES tbl_tag(id);

   
ALTER TABLE tbl_statement2parent
    ADD CONSTRAINT tbl_statement2parent_fk_1 FOREIGN KEY (parent_id) REFERENCES tbl_statement(id);
ALTER TABLE tbl_statement2parent
    ADD CONSTRAINT tbl_statement2parent_fk_2 FOREIGN KEY (statement_id) REFERENCES tbl_statement(id);


ALTER TABLE tbl_statement
    ADD CONSTRAINT tbl_statement_fk_1 FOREIGN KEY (type_id) REFERENCES tbl_statementtype(id);
ALTER TABLE tbl_statement
    ADD CONSTRAINT tbl_statement_fk_2 FOREIGN KEY (department_structure_id) REFERENCES tbl_departmentstructure(id);


ALTER TABLE tbl_comment
    ADD CONSTRAINT tbl_comment_fk_1 FOREIGN KEY (user_id) REFERENCES tbl_user(id);


ALTER TABLE tbl_workflowdata
    ADD CONSTRAINT tbl_workflowdata_fk_1 FOREIGN KEY (statement_id) REFERENCES tbl_statement(id);
ALTER TABLE tbl_workflowdata
    ADD CONSTRAINT tbl_workflowdata_fk_2 FOREIGN KEY (textblockdefinition_id) REFERENCES tbl_textblockdefinition(id);


ALTER TABLE tbl_attachment
    ADD CONSTRAINT tbl_attachment_fk_1 FOREIGN KEY (statement_id) REFERENCES tbl_statement(id);

ALTER TABLE tbl_attachment
    ADD CONSTRAINT tbl_attachment_fk_2 FOREIGN KEY (attachment_lob_id) REFERENCES tbl_attachment_lob(id);

ALTER TABLE tbl_comment
    ADD CONSTRAINT tbl_comment_fk_2 FOREIGN KEY (workflow_id) REFERENCES tbl_workflowdata(id);


-- VW_STATEMENT_POSITION_SEARCH

CREATE VIEW vw_statement_position_search AS
 SELECT s.id,
    s.due_date,
    s.finished,
    s.title,
    s.type_id,
    w.pos
   FROM tbl_statement s,
    tbl_workflowdata w
  WHERE (w.statement_id = s.id);

-- VW_STATEMENT_REQDEPARTMENT_USERS

 
CREATE VIEW vw_statement_reqdepartment_users AS
 SELECT row_number() OVER (ORDER BY u.id) AS id,
    u.id AS user_id,
    u.first_name,
    u.last_name,
    u.username AS user_name,
    u.email_address,
    wd.statement_id,
    wd.id AS workflow_id,
    u.department_id,
    d.name AS department_name,
    d.departmentgroup AS department_group
   FROM (((tbl_user u
     JOIN tbl_department d ON ((u.department_id = d.id)))
     JOIN tbl_reqdepartment rd ON ((rd.department_id = d.id)))
     JOIN tbl_workflowdata wd ON ((wd.id = rd.workflowdata_id)));

-- VW_STATEMENT_SEARCH

CREATE VIEW vw_statement_search AS
 SELECT s.id,
    s.business_key,
    s.city,
    s.district,
    s.due_date,
    s.finished,
    s.receipt_date,
    s.creation_date,
    s.customer_reference,
    s.title,
    s.type_id,
    s.contact_db_id,
    s.source_mail_id,
    upper(s.id || '|@|' || COALESCE(t.name, '') ||  '|@|' || COALESCE (s.title, '') || '|@|' || COALESCE (s.city, '')  || '|@|' || COALESCE (s.district, '') || '|@|' || COALESCE (s.customer_reference, '') || '|@|' ) AS searchfield
   FROM tbl_statement s,
    tbl_statementtype t
  WHERE (s.type_id = t.id);


-- VW_USER_STATEMENT_SEARCH

CREATE VIEW vw_user_statement_search AS
 SELECT DISTINCT s.id,
    s.business_key,
    s.city,
    s.district,
    s.due_date,
    s.finished,
    s.receipt_date,
    s.creation_date,
    s.customer_reference,
    s.title,
    s.type_id,
    s.contact_db_id,
    e.user_id,
    s.source_mail_id,
    upper(s.id || '|@|' || COALESCE(t.name, '') ||  '|@|' || COALESCE (s.title, '') || '|@|' || COALESCE (s.city, '')  || '|@|' || COALESCE (s.district, '') || '|@|' || COALESCE (s.customer_reference, '') || '|@|' ) AS searchfield
   FROM tbl_statement s,
    tbl_statementtype t,
    tbl_statement_edit_log e
  WHERE ((s.type_id = t.id) AND (e.statement_id = s.id));
 
 
-- INSERT STATEMENT_TYPES

 
INSERT INTO tbl_statementtype VALUES (1, 'Bauleitplanung');
INSERT INTO tbl_statementtype VALUES (2, 'Mitverlegungsanfrage');
INSERT INTO tbl_statementtype VALUES (3, 'Bauvoranfrage');
INSERT INTO tbl_statementtype VALUES (4, 'Planfeststellungsverfahren');
INSERT INTO tbl_statementtype VALUES (5, 'Formlose Anfrage');


-- INSERT TAGS

INSERT INTO tbl_tag VALUES ('email-text', 'Emailanfrage');
INSERT INTO tbl_tag VALUES ('email', 'Email');
INSERT INTO tbl_tag VALUES ('outbox', 'Ausgang');
INSERT INTO tbl_tag VALUES ('consideration', 'Abwägungsergebnis');
INSERT INTO tbl_tag VALUES ('statement', 'Stellungnahme');
INSERT INTO tbl_tag VALUES ('cover-letter', 'Anschreiben');
INSERT INTO tbl_tag VALUES ('overview', 'Übersicht');
INSERT INTO tbl_tag VALUES ('expertise', 'Gutachten');
INSERT INTO tbl_tag VALUES ('plan', 'Plan');
INSERT INTO tbl_tag VALUES ('explanatory-report', 'Erläuterungsbericht');

-- INSERT DEPARTMENTSTRUCTURE

INSERT INTO TBL_DEPARTMENTSTRUCTURE (DEFINITION, VERSION) VALUES ('{}', 1);

-- INSERT TEXTBLOCKDEFINITION

INSERT INTO TBL_TEXTBLOCKDEFINITION (DEFINITION, VERSION) VALUES('{"groups":[],"negativeGroups":[],"selects":{}}', 1);