﻿
-- ---------------------------------------------
-- TABLE VERSION
-- ---------------------------------------------
-- TABLE: PUBLIC.REF_VERSION
CREATE TABLE VERSION
(
  "ID" INTEGER NOT NULL,
  "VERSION" VARCHAR2(500) NOT NULL,
  CONSTRAINT VERSION_PKEY PRIMARY KEY ("ID")
);

INSERT INTO VERSION (ID, VERSION) VALUES ( 1, '1.0-ORA' );


-- ---------------------------------------------
-- TABLE TBL_CONTACT
-- ---------------------------------------------

CREATE SEQUENCE TBL_CONTACT_ID_SEQ
  INCREMENT BY 1;

CREATE TABLE TBL_CONTACT
(
  "ID" INTEGER NOT NULL,
  "UUID" RAW(16) NOT NULL,
  "CONTACT_TYPE" VARCHAR2(3),
  "NOTE" VARCHAR2(255),
  "ANONYMIZED" NUMBER,
  CONSTRAINT TBL_CONTACT_PKEY PRIMARY KEY ("ID")
);

CREATE OR REPLACE TRIGGER "TBL_CONTACT_BIR"
BEFORE INSERT ON TBL_CONTACT
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT TBL_CONTACT_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "TBL_CONTACT_BIR" ENABLE;


INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('ae3f2ec1-ccc5-4269-a48f-dd40e37fa14e','-','')), '4CO', 'company 1 (id=1)', null );
INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('fc7f598b-0d51-46bb-9563-99851fe6a3ad','-','')), '4CO', 'company 2 (id=2)', null );
INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('556b91be-6d57-432f-93ed-65604dd6e5cd','-','')), '1CP', 'contact person 1 (id=3)', null );
INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('116380e3-25c5-4179-b40a-8abebe10fe07','-','')), '1CP', 'contact person 2 (id=4)', null );
INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('7782179b-fb79-4370-8f71-f4c71470d006','-','')), '3IP', 'internal person 1 (id=5)', null );
INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('8963aa38-d021-4dc9-bd70-d3734ccd20c4','-','')), '3IP', 'internal person 2 (id=6)', null );
INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('c862d604-5766-43d6-a7e8-a4bac2bd01e1','-','')), '2EP', 'external person 1 (id=7)', null );
INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('fa3d981b-a7d6-4965-a623-cdbc69404153','-','')), '2EP', 'external person 2 (id=8)', null );
INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('ceba21e9-e685-483b-840e-ad167860a696','-','')), '4CO', 'anonymous company A (id=9)', 1 );
INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('7889c814-9752-4e4e-a9fe-b46f36a38ccd','-','')), '4CO', 'anonymous company B (id=10)', 1 );
INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('618a01a3-f348-44cc-9ddd-c9df946b0212','-','')), '1CP', 'anoymous contact person A1 (id=11)', 1 );
INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('4ed82474-3878-457a-baef-c28b8e486f25','-','')), '1CP', 'anoymous contact persion A2 (id=12)', 1 );
INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('8fe41b90-d10c-4a70-8fde-0990286ad3c6','-','')), '3IP', 'internal person 3 (id=13)', null );
INSERT INTO "TBL_CONTACT" ("UUID", "CONTACT_TYPE", "NOTE", "ANONYMIZED") VALUES ( hextoraw(replace('ab804610-d6a4-4803-a4a1-3f6cb742b2a4','-','')), '3IP', 'internal person 4 (id=14)', null );


CREATE UNIQUE INDEX IDX_CNTCT_UUID ON TBL_CONTACT (UUID);
CREATE INDEX IDX_CNTCT_ANONYM ON TBL_CONTACT (ANONYMIZED);


-- ---------------------------------------------
-- TABLE REF_ADDRESS_TYPE
-- ---------------------------------------------
CREATE SEQUENCE REF_ADDRESS_TYPE_ID_SEQ
  INCREMENT BY 1;

CREATE TABLE REF_ADDRESS_TYPE
(
  "ID" INTEGER NOT NULL,
  "UUID" RAW(16) NOT NULL,
  "TYPE" VARCHAR2(30),
  "DESCRIPTION" VARCHAR2(255),
  CONSTRAINT REF_ADDRESS_TYPE_PKEY PRIMARY KEY ("ID")
);

CREATE OR REPLACE TRIGGER "REF_ADDRESS_TYPE_BIR"
BEFORE INSERT ON REF_ADDRESS_TYPE
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT REF_ADDRESS_TYPE_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "REF_ADDRESS_TYPE_BIR" ENABLE;

CREATE UNIQUE INDEX IDX_REF_ADDRESS_TYPE_UUID ON REF_ADDRESS_TYPE ( UUID ASC );

INSERT INTO REF_ADDRESS_TYPE ("UUID", "TYPE", "DESCRIPTION") VALUES ( hextoraw(replace('3802e681-9396-434e-b19c-5fedcec40ba7','-','')), 'Geschäftsadresse', 'Adresse des Hauptfirmensitzes' );
INSERT INTO REF_ADDRESS_TYPE ("UUID", "TYPE", "DESCRIPTION") VALUES ( hextoraw(replace('f43ed6ac-9e7a-40f6-acc9-ec6b73eebf79','-','')), 'Privatadresse', 'private Anschrift' );
INSERT INTO REF_ADDRESS_TYPE ("UUID", "TYPE", "DESCRIPTION") VALUES ( hextoraw(replace('70fd0811-f674-4f3a-96a7-7ae29fc95188','-','')), 'Lieferadresse', 'Adresse für Lieferungen' );


-- ---------------------------------------------
-- TABLE REF_PERSON_TYPE
-- ---------------------------------------------
CREATE SEQUENCE REF_PERSON_TYPE_ID_SEQ
  INCREMENT BY 1;

CREATE TABLE REF_PERSON_TYPE
(
  "ID" INTEGER NOT NULL,
  "UUID" RAW(16) NOT NULL,
  "TYPE" VARCHAR2(30),
  "DESCRIPTION" VARCHAR2(255),
  CONSTRAINT REF_PERSON_TYPE_PKEY PRIMARY KEY (ID)
);

CREATE OR REPLACE TRIGGER "REF_PERSON_TYPE_BIR"
BEFORE INSERT ON REF_PERSON_TYPE
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT REF_PERSON_TYPE_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "REF_PERSON_TYPE_BIR" ENABLE;

CREATE UNIQUE INDEX IDX_REF_PERSON_TYPE_UUID ON REF_PERSON_TYPE ( UUID ASC );

INSERT INTO REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( hextoraw(replace('47ce68b7-6d44-453e-b421-19020fd791b5','-','')), 'Rechtsanwalt', '' );
INSERT INTO REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( hextoraw(replace('a7522c72-14d0-4e9d-afe3-bfcb3ffbec10','-','')), 'Geschäftsführer', '' );
INSERT INTO REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( hextoraw(replace('2eb4885e-7363-4918-90ed-b7d5d84cfd3f','-','')), 'Rechnungsempfänger', 'Person, der Rechnungen zukommen' );


-- ---------------------------------------------
-- TABLE REF_COMMUNICATION_TYPE
-- ---------------------------------------------
CREATE SEQUENCE REF_COMMUNICATION_TYPE_ID_SEQ
   INCREMENT BY 1;


CREATE TABLE REF_COMMUNICATION_TYPE
(
  "ID" INTEGER NOT NULL,
  "UUID" RAW(16) NOT NULL,
  "TYPE" VARCHAR2(30),
  "DESCRIPTION" VARCHAR2(255),
  "TYPE_EMAIL" NUMBER,
  CONSTRAINT REF_COMMUNICATION_TYPE_PKEY PRIMARY KEY (ID)
);

CREATE OR REPLACE TRIGGER "REF_COMMUNICATION_TYPE_BIR"
BEFORE INSERT ON REF_COMMUNICATION_TYPE
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT REF_COMMUNICATION_TYPE_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "REF_COMMUNICATION_TYPE_BIR" ENABLE;


CREATE UNIQUE INDEX idx_ref_comm_type_uuid ON REF_COMMUNICATION_TYPE ( UUID ASC );

INSERT INTO REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( hextoraw(replace('4757ca3a-72c2-4f13-a2f6-ce092e3eadf4','-','')), 'E-Mail', 'E-Mail Adresse', 1 );
INSERT INTO REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( hextoraw(replace('77028572-ff57-4c1d-999a-78fa3fcbc1cd','-','')), 'Festnetz', '', 0 );
INSERT INTO REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( hextoraw(replace('f7d5b343-00c2-4d7f-8e03-009aad3d90f7','-','')), 'Mobil', '', 0 );
INSERT INTO REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( hextoraw(replace('2bfe40f9-c4eb-4d2e-855f-6b0883912846','-','')), 'Fax', '', 0 );
INSERT INTO REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( hextoraw(replace('d00d1a61-c8e7-43b2-959f-66e986731441','-','')), 'WhatsApp', '', 0 );

-- ---------------------------------------------
-- TABLE REF_SALUTATION
-- ---------------------------------------------
CREATE SEQUENCE REF_SALUTATION_ID_SEQ
  INCREMENT BY 1;

CREATE TABLE REF_SALUTATION
(
  "ID" INTEGER NOT NULL,
  "UUID" RAW(16) NOT NULL,
  "TYPE" VARCHAR2(30),
  "DESCRIPTION" VARCHAR2(255),
  CONSTRAINT REF_SALUTATION_PKEY PRIMARY KEY (ID)
);

CREATE OR REPLACE TRIGGER "REF_SALUTATION_BIR"
BEFORE INSERT ON REF_SALUTATION
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT REF_SALUTATION_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "REF_SALUTATION_BIR" ENABLE;


CREATE UNIQUE INDEX idx_ref_salutation_uuid ON REF_SALUTATION ( UUID ASC );

INSERT INTO REF_SALUTATION (UUID, TYPE, DESCRIPTION) VALUES ( hextoraw(replace('90119f18-5562-425d-9a36-3dd58ea125e5','-','')), 'Herr', 'Anrede männlich' );
INSERT INTO REF_SALUTATION (UUID, TYPE, DESCRIPTION) VALUES ( hextoraw(replace('4e873baa-e4f5-4585-8b16-2db8fac66538','-','')), 'Frau', 'Anrede weiblich' );


-- ---------------------------------------------
-- TABLE TBL_ADDRESS
-- ---------------------------------------------
CREATE SEQUENCE TBL_ADDRESS_ID_SEQ
  INCREMENT BY 1;

CREATE TABLE TBL_ADDRESS
(
  "ID" INTEGER NOT NULL,
  "UUID" RAW(16) NOT NULL,
  "FK_CONTACT_ID" INTEGER NOT NULL,
  "FK_ADDRESS_TYPE" INTEGER,
  "IS_MAIN_ADDRESS" NUMBER,
  "POSTCODE" VARCHAR2(30),
  "COMMUNITY" VARCHAR2(255),
  "COMMUNITY_SUFFIX" VARCHAR2(255),
  "STREET" VARCHAR2(255),
  "HOUSENUMBER" VARCHAR2(30),
  "WGS_84_ZONE" VARCHAR2(255),
  "LATITUDE" VARCHAR2(255),
  "LONGITUDE" VARCHAR2(255),
  "URL_MAP" VARCHAR2(255),
  "NOTE" VARCHAR2(255),

  CONSTRAINT TBL_ADDRESS_PKEY PRIMARY KEY (ID),
  CONSTRAINT TBL_ADDRESS__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
      REFERENCES TBL_CONTACT(ID),
  CONSTRAINT TBL_ADDRESS_ADDR_TYPE_FKEY FOREIGN KEY (FK_ADDRESS_TYPE)
       REFERENCES REF_ADDRESS_TYPE (ID)
);

CREATE OR REPLACE TRIGGER "TBL_ADDRESS_BIR"
BEFORE INSERT ON TBL_ADDRESS
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT TBL_ADDRESS_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "TBL_ADDRESS_BIR" ENABLE;


CREATE UNIQUE INDEX idx_tbl_address_uuid ON TBL_ADDRESS ( UUID ASC );

INSERT INTO TBL_ADDRESS (UUID, FK_CONTACT_ID, FK_ADDRESS_TYPE, IS_MAIN_ADDRESS, POSTCODE, COMMUNITY, COMMUNITY_SUFFIX, STREET, HOUSENUMBER, WGS_84_ZONE, LATITUDE, LONGITUDE, URL_MAP, NOTE) VALUES ( hextoraw(replace('37e800fe-64f0-4834-8b83-8453cbb936a5','-','')), 2, 1, 1, '12345', 'Heringsdorf','', 'Flunderweg', '5', '', '53 NL', '3 WB','www.xyz', 'nur über Seeweg erreichbar');
INSERT INTO TBL_ADDRESS (UUID, FK_CONTACT_ID, FK_ADDRESS_TYPE, IS_MAIN_ADDRESS, POSTCODE, COMMUNITY, COMMUNITY_SUFFIX, STREET, HOUSENUMBER, WGS_84_ZONE, LATITUDE, LONGITUDE, URL_MAP, NOTE) VALUES ( hextoraw(replace('8a1202ae-2532-474e-8367-a1f0e13e9fbd','-','')), 1, 2, 0, '67890', 'Stralsund','', 'Schollendamm', '18', '', '53 N', '2 WB','www.xyz', 'Hochwassergefahr');


-- ---------------------------------------------
-- TABLE TBL_COMMUNICATION
-- ---------------------------------------------
CREATE SEQUENCE TBL_COMMUNICATION_ID_SEQ
  INCREMENT BY 1;

CREATE TABLE TBL_COMMUNICATION
(
  "ID" INTEGER NOT NULL,
  "UUID" RAW(16) NOT NULL,
  "FK_CONTACT_ID" INTEGER NOT NULL,
  "FK_COMMUNICATION_TYPE" INTEGER,
  "COMMUNICATION_DATA" VARCHAR2(1024),
  "NOTE" VARCHAR2(255),

  CONSTRAINT TBL_COMMUNICATION_PKEY PRIMARY KEY (ID),
  CONSTRAINT TBL_COMM__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
      REFERENCES TBL_CONTACT(ID),
  CONSTRAINT TBL_COMM__COMM_TYPE_ID_FKEY FOREIGN KEY (FK_COMMUNICATION_TYPE)
       REFERENCES REF_COMMUNICATION_TYPE (ID)
);

CREATE OR REPLACE TRIGGER "TBL_COMMUNICATION_BIR"
BEFORE INSERT ON TBL_COMMUNICATION
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT TBL_COMMUNICATION_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "TBL_COMMUNICATION_BIR" ENABLE;


CREATE UNIQUE INDEX idx_tbl_communication_uuid ON TBL_COMMUNICATION ( UUID ASC );

INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('25f6d7cc-b168-4dd5-a36d-6f14b2f956e9','-','')), 2, 2, 'bitte melden Sie sich bei uns', 'Info...');
INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('a5fa380e-8f33-4ea7-9416-e03d11b91cae','-','')), 1, 3, 'bitte melden zwecks Terminabstimmung', 'Info...');
INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('c0dcef80-ca07-48b7-a3ed-2c99c4388928','-','')), 1, 1, 'info@bigbang.com', 'Info...');
INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('d19d9e10-d2dd-4383-84ec-2fe96421c0a3','-','')), 2, 1, 'info@pharmapeekltd.com', 'Info...');
INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('234b63e4-d8db-48ab-899f-0320903c01af','-','')), 3, 1, 'reinbold.tab@pharmapeek.com', 'Info...');
INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('11e25c02-de00-430d-b6cd-f02f7c60e026','-','')), 5, 1, 'PaulineF@gmx.net', 'Info...');
INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('9ca29c3b-e189-4ce9-9401-15001c769627','-','')), 6, 1, 'mo@gmail.com', 'Info...');
INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('05505b1a-61df-45c0-b006-64165cbadfa2','-','')), 7, 1, 'MGruebelSport@fogger.mil', 'Info...');
INSERT INTO TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( hextoraw(replace('b52dabaf-d156-4fd0-a07c-510673112a15','-','')), 8, 1, 'Mini.osterbrink@yahoo.ie', 'Info...');

-- ---------------------------------------------
-- TABLE TBL_COMPANY
-- ---------------------------------------------
CREATE SEQUENCE TBL_COMPANY_ID_SEQ
  INCREMENT BY 1;


CREATE TABLE TBL_COMPANY
(
  "ID" INTEGER NOT NULL,
  "COMPANY_NAME" VARCHAR2(255),
  "COMPANY_TYPE" VARCHAR2(30),
  "HR_NUMBER" VARCHAR2(255),
  "FK_CONTACT_ID" INTEGER NOT NULL,
  CONSTRAINT TBL_COMPANY_PKEY PRIMARY KEY (id),
  CONSTRAINT TBL_COMPANY__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
      REFERENCES TBL_CONTACT(ID)
);

CREATE OR REPLACE TRIGGER "TBL_COMPANY_BIR"
BEFORE INSERT ON TBL_COMPANY
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT TBL_COMPANY_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "TBL_COMPANY_BIR" ENABLE;


INSERT INTO TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( 'BigBang Logistic', 'Logistik', '123', 1 );
INSERT INTO TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( 'Pharma Peek', 'Pharma', '345', 2 );
INSERT INTO TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( '***', '***', null, 9 );
INSERT INTO TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( '***', '***', null, 10 );


-- ---------------------------------------------
-- TABLE TBL_CONTACT_PERSON
-- ---------------------------------------------
CREATE SEQUENCE TBL_CONTACT_PERSON_ID_SEQ
  INCREMENT BY 1;


CREATE TABLE TBL_CONTACT_PERSON
(
  "ID" INTEGER NOT NULL,
  "FIRST_NAME" VARCHAR2(255),
  "LAST_NAME" VARCHAR2(255),
  "TITLE" VARCHAR2(255),
  "FK_SALUTATION_ID" INTEGER,
  "FK_REF_PERSON_TYPE_ID" INTEGER NULL,
  "FK_CONTACT_ID" INTEGER NOT NULL,
  "FK_COMPANY_ID" INTEGER NOT NULL,
  CONSTRAINT TBL_CONTACT_PERSON_PKEY PRIMARY KEY (ID),
  CONSTRAINT TBL_CP__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
      REFERENCES TBL_CONTACT (ID),
  CONSTRAINT TBL_CP__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
      REFERENCES REF_PERSON_TYPE (ID),
  CONSTRAINT TBL_CP__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
           REFERENCES REF_SALUTATION (ID),
  CONSTRAINT TBL_CP__COMPANY_ID_FKEY FOREIGN KEY (FK_COMPANY_ID)
       REFERENCES TBL_COMPANY (ID)
);

CREATE OR REPLACE TRIGGER "TBL_CONTACT_PERSON_BIR"
BEFORE INSERT ON TBL_CONTACT_PERSON
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT TBL_CONTACT_PERSON_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "TBL_CONTACT_PERSON_BIR" ENABLE;


INSERT INTO TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( 'Tabea', 'Reinebold', 'Dr.', 2, 1, 3, 2);
INSERT INTO TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( 'Jan', 'Wacker', '', 1, 1, 4, 2);
INSERT INTO TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( '***', '***', null, null, null, 11, 3);
INSERT INTO TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( '***', '***', null, null, null, 12, 2);

-- ---------------------------------------------
-- TABLE TBL_EXTERNAL_PERSON
-- ---------------------------------------------
CREATE SEQUENCE TBL_EXTERNAL_PERSON_ID_SEQ
  INCREMENT BY 1;


CREATE TABLE TBL_EXTERNAL_PERSON
(
  "ID" INTEGER NOT NULL,
  "FIRST_NAME" VARCHAR2(255),
  "LAST_NAME" VARCHAR2(255),
  "TITLE" VARCHAR2(255),
  "FK_SALUTATION_ID" INTEGER,
  "FK_REF_PERSON_TYPE_ID" INTEGER,
  "FK_CONTACT_ID" INTEGER NOT NULL,
  CONSTRAINT TBL_EXTERNAL_PERSON_PKEY PRIMARY KEY (id),
  CONSTRAINT TBL_EP__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
       REFERENCES TBL_CONTACT (ID),
  CONSTRAINT TBL_EP__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
         REFERENCES REF_SALUTATION (ID),
  CONSTRAINT TBL_EP__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
           REFERENCES REF_PERSON_TYPE (ID)
);

CREATE OR REPLACE TRIGGER "TBL_EXTERNAL_PERSON_BIR"
BEFORE INSERT ON TBL_EXTERNAL_PERSON
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT TBL_EXTERNAL_PERSON_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "TBL_EXTERNAL_PERSON_BIR" ENABLE;


INSERT INTO TBL_EXTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID) VALUES ( 'Monica', 'Grübel', 'Dipl.-Sportlehrerin', 2, 1, 7);
INSERT INTO TBL_EXTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID) VALUES ( 'Maurice', 'Fürstenberg', 'B.A.', 2, 2, 8);


-- ---------------------------------------------
-- TABLE TBL_INTERNAL_PERSON
-- ---------------------------------------------
CREATE SEQUENCE TBL_INTERNAL_PERSON_ID_SEQ
  INCREMENT BY 1;

CREATE TABLE TBL_INTERNAL_PERSON
(
   "ID" INTEGER NOT NULL,
   "FIRST_NAME" VARCHAR2(255),
   "LAST_NAME" VARCHAR2(255),
   "TITLE" VARCHAR2(255),
   "FK_SALUTATION_ID" INTEGER,
   "FK_REF_PERSON_TYPE_ID" INTEGER,
   "DEPARTMENT" VARCHAR2(255),
   "SYNC_NOTE" VARCHAR2(1024),
   "IS_SYNC_ERROR" NUMBER,
   "UID_IDENT" VARCHAR2(255),
   "USER_REF" VARCHAR2(255),
   "FK_CONTACT_ID" INTEGER NOT NULL,
   CONSTRAINT TBL_INTERNAL_PERSON_PKEY PRIMARY KEY (ID),
   CONSTRAINT TBL_IP__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID)
      REFERENCES TBL_CONTACT (ID),
   CONSTRAINT TBL_IP__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID)
         REFERENCES REF_SALUTATION (ID),
   CONSTRAINT TBL_IP__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID)
         REFERENCES REF_PERSON_TYPE (ID)
);

CREATE OR REPLACE TRIGGER "TBL_INTERNAL_PERSON_BIR"
BEFORE INSERT ON TBL_INTERNAL_PERSON
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT TBL_INTERNAL_PERSON_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "TBL_INTERNAL_PERSON_BIR" ENABLE;


CREATE UNIQUE INDEX idx_tbl_internal_person_uid ON TBL_INTERNAL_PERSON ( "UID_IDENT" ASC );
CREATE UNIQUE INDEX idx_tbl_ip_user_ref ON TBL_INTERNAL_PERSON ( "USER_REF" ASC );

INSERT INTO TBL_INTERNAL_PERSON ("FIRST_NAME", "LAST_NAME", "TITLE", "FK_SALUTATION_ID", "FK_REF_PERSON_TYPE_ID", "DEPARTMENT", "UID_IDENT", "USER_REF", "FK_CONTACT_ID") VALUES ( 'Pauline', 'Freudenberg', 'B.Sc.', 1, 1,'Abteilung Rechnungsstellung', '66cd78c3-6716-4ab3-b834-a199fc796b88', 'PFREUD',  5);
INSERT INTO TBL_INTERNAL_PERSON ("FIRST_NAME", "LAST_NAME", "TITLE", "FK_SALUTATION_ID", "FK_REF_PERSON_TYPE_ID", "DEPARTMENT", "UID_IDENT", "USER_REF", "FK_CONTACT_ID") VALUES ( 'Bernhardt', 'Iffland', '', 2, 2,'Kreativ', '4124e4e7-3488-4492-bf39-75e6a23a1c1a', 'BIFFL', 6);
INSERT INTO TBL_INTERNAL_PERSON ("FIRST_NAME", "LAST_NAME", "TITLE", "FK_SALUTATION_ID", "FK_REF_PERSON_TYPE_ID", "DEPARTMENT", "UID_IDENT", "USER_REF", "FK_CONTACT_ID") VALUES ( 'Horatio', 'Hornblower', 'Capt.', 1, 2,'Royal Navy', 'hhornblo', 'HORNB', 13);
INSERT INTO TBL_INTERNAL_PERSON ("FIRST_NAME", "LAST_NAME", "TITLE", "FK_SALUTATION_ID", "FK_REF_PERSON_TYPE_ID", "DEPARTMENT", "UID_IDENT", "USER_REF", "FK_CONTACT_ID") VALUES ( 'Cornelius', 'Buckley', '', 1, 2,'Royal Navy', null, 'BUCKC', 14);
-- ---------------------------------------------
-- TABLE TBL_ASSIGNMENT_MODUL_CONTACT
-- ---------------------------------------------
CREATE SEQUENCE TBL_ASSIGN_MODUL_CNTCT_ID_SEQ
  INCREMENT BY 1;


CREATE TABLE TBL_ASSIGNMENT_MODUL_CONTACT
(
  "ID" INTEGER NOT NULL,
  "UUID" RAW(16) NOT NULL,
  "FK_CONTACT_ID" INTEGER NOT NULL,
  "MODUL_NAME" VARCHAR2(255),
  "ASSIGNMENT_DATE" timestamp,
  "EXPIRING_DATE" timestamp,
  "DELETION_LOCK_UNTIL" timestamp,
  "ASSIGNMENT_NOTE" VARCHAR2(2048),
  CONSTRAINT TBL_ASS_MOD_CONTACT_PKEY PRIMARY KEY (id),
  CONSTRAINT TBL_ASS_MOD_CONTACT_ID_FKEY FOREIGN KEY (fk_contact_id)
      REFERENCES TBL_CONTACT (ID)
);

CREATE OR REPLACE TRIGGER "TBL_ASSIGN_MODUL_CNTCT_BIR"
BEFORE INSERT ON TBL_ASSIGNMENT_MODUL_CONTACT
FOR EACH ROW
 WHEN (NEW."ID" IS NULL) BEGIN
  SELECT TBL_ASSIGN_MODUL_CNTCT_ID_SEQ.NEXTVAL
  INTO   :NEW."ID"
  FROM   DUAL;
END;
/
ALTER TRIGGER "TBL_ASSIGN_MODUL_CNTCT_BIR" ENABLE;


INSERT INTO tbl_assignment_modul_contact(uuid, fk_contact_id, modul_name, assignment_date, expiring_date, deletion_lock_until, assignment_note) VALUES ( hextoraw(replace('dfc2979c-40e3-11ea-b77f-2e728ce88125','-','')), 1, 'Betriebstagebuch', CURRENT_TIMESTAMP, null, null, 'seit Januar zugeordnet');
INSERT INTO tbl_assignment_modul_contact(uuid, fk_contact_id, modul_name, assignment_date, expiring_date, deletion_lock_until, assignment_note) VALUES ( hextoraw(replace('4009bce2-40e5-11ea-b77f-2e728ce88125','-','')), 1, 'Störinfos', CURRENT_TIMESTAMP, null, null, 'seit Februar zugeordnet');

-- -------------------------------------
-- VIEWS -------------------------------
-- -------------------------------------
CREATE VIEW VW_GENERAL_CONTACT
AS
SELECT g.id,
	c.uuid,
	g.name,
	c.contact_type,
	g.fk_contact_id,
	g.company_name,
	g.company_type,
	g.company_id,
	g.fk_salutation_id,
	g.fk_ref_person_type_id,
	g.title,
	g.first_name,
	g.last_name,
	g.department,
	c.note,
	c.anonymized
FROM tbl_contact c
JOIN (

SELECT company.id,
	company_name as name,
	fk_contact_id,
	company_name,
	company_type,
	cntct.uuid as company_id,
	null as fk_salutation_id,
	null as fk_ref_person_type_id,
	null as title,
	null as first_name,
	null as last_name,
	null as department
FROM tbl_company company
JOIN tbl_contact cntct ON company.fk_contact_id = cntct.id

UNION

SELECT p.id,
	COALESCE(p.last_name, '')
	|| CASE WHEN LENGTH(COALESCE(p.last_name, '')) > 0 AND LENGTH(COALESCE(p.first_name, '')) > 0 THEN ', ' ELSE '' END
	|| COALESCE(p.first_name,'') || ' [' || COALESCE(c.company_name, '') || ']' as name,
	p.fk_contact_id,
	c.company_name,
	c.company_type,
	company_contact.uuid as company_id,
	p.fk_salutation_id,
	p.fk_ref_person_type_id,
	p.title,
	p.first_name,
	p.last_name,
	null as department
FROM tbl_contact_person p
JOIN tbl_company c ON c.id = p.fk_company_id
JOIN tbl_contact company_contact ON c.fk_contact_id = company_contact.id

UNION

SELECT id,
	COALESCE(last_name, '')
	|| CASE WHEN LENGTH(COALESCE(last_name, '')) > 0 AND LENGTH(COALESCE(first_name, '')) > 0 THEN ', ' ELSE '' END
	|| COALESCE(first_name, '') as name,
	fk_contact_id,
	null as company_name,
	null as company_type,
	null as company_id,
	fk_salutation_id,
	fk_ref_person_type_id,
	title,
	first_name,
	last_name,
	department
FROM tbl_internal_person

UNION

SELECT id,
	COALESCE(last_name, '')
	|| CASE WHEN LENGTH(COALESCE(last_name, '')) > 0 AND LENGTH(COALESCE(first_name, '')) > 0 THEN ', ' ELSE '' END
	|| COALESCE(first_name, '') as name,
	fk_contact_id,
	null as company_name,
	null as company_type,
	null as company_id,
	fk_salutation_id,
	fk_ref_person_type_id,
	title,
	first_name,
	last_name,
	null as department
FROM tbl_external_person
	) g
ON g.fk_contact_id = c.ID;


CREATE VIEW VW_DETAILED_CONTACT
AS
SELECT c.id,
    c.uuid,
	c.name,
	c.contact_type,
	c.fk_contact_id,
	c.company_name,
	c.company_type,
	c.company_id,
	c.anonymized,
	s.uuid as salutation_uuid,
	t.uuid as person_type_uuid,
	c.title,
	c.first_name,
	c.last_name,
	c.department,
	c.note,
	s.type as salutation_type,
	t.type as person_type,
	COALESCE(a.community, '') ||
	        CASE WHEN LENGTH(COALESCE(a.street, '') || COALESCE(a.housenumber, '')) = 0 THEN '' ELSE ', ' END ||
	        COALESCE(a.street, '') ||
	        CASE WHEN LENGTH(COALESCE(a.housenumber, '')) = 0 THEN '' ELSE ' ' END ||
	        COALESCE(a.housenumber, '') as main_address,
	com.communication_data as email,

	UPPER(
        COALESCE(c.name, '') || '|@|'
        || COALESCE(company_name, '') || '|@|'
        || COALESCE(c.company_type, '') || '|@|'
        || COALESCE(c.title, '') || '|@|'
        || COALESCE(c.first_name, '') || '|@|'
        || COALESCE(c.last_name, '') || '|@|'
        || COALESCE(c.department, '') || '|@|'
        || COALESCE(c.note, '') || '|@|'
        || COALESCE(s.type, '') || '|@|'
        || COALESCE(t.type, '') || '|@|'
        || COALESCE(a.street, '') || '|@|'
        || COALESCE(a.housenumber, '') || '|@|'
        || COALESCE(a.community, '') || '|@|'
        || COALESCE(com.communication_data, '')
    )as searchfield
FROM VW_GENERAL_CONTACT c
LEFT OUTER JOIN ref_salutation s ON c.fk_salutation_id = s.id
LEFT OUTER JOIN tbl_address a ON a.fk_contact_id = c.fk_contact_id and is_main_address = 1
LEFT OUTER JOIN ref_person_type t ON c.fk_ref_person_type_id = t.id
LEFT OUTER JOIN tbl_communication com ON (com.fk_contact_id = c.fk_contact_id )
LEFT OUTER JOIN ref_communication_type rct ON (rct.id = com.fk_communication_type)
WHERE COALESCE( rct.type_email, 1) = 1;