CREATE TABLE "CURRENT_USER_ID"(
USER_ID         VARCHAR2(20 CHAR)
);

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from ANNOTATION M;
Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from ANNOTATION M;

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.USER_ISID from "AUDIT" M;

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from CONTAINER M;
Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from CONTAINER M;

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from JOB M;
Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from JOB M;

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from JOB_PARAMETER M;
Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from JOB_PARAMETER M;

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from MODEL M;
Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from MODEL M;

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from MODEL_ITEM M;
Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from MODEL_ITEM M;

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from MODEL_PARAMETER M;
Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from MODEL_PARAMETER M;

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from MODEL_STEP M;
Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from MODEL_STEP M;

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from MODEL_STEP_PARAMETER M;
Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from MODEL_STEP_PARAMETER M;

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.USER_ISID from PERMISSION_AUDIT M;
Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.TARGET_USER_ISID from PERMISSION_AUDIT M;

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.USERNAME from SUBSCRIPTION M;

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.USERNAME from SUBSCRIPTION_MESSAGE M;

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from SVN_ENTRY M;
Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from SVN_ENTRY M;

Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.MODIFIEDBY from USER_PERMISSION M;
Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.CREATEDBY from USER_PERMISSION M;
Insert into "CURRENT_USER_ID" (USER_ID) Select distinct M.USER_ID from USER_PERMISSION M;

CREATE TABLE "UNIQUE_USER_ID"(
USER_ID         VARCHAR2(20 CHAR)
);

Insert into "UNIQUE_USER_ID" (USER_ID) Select distinct M.USER_ID from "CURRENT_USER_ID" M;

DROP TABLE "CURRENT_USER_ID";

MERGE into "USER" U using "UNIQUE_USER_ID" ALL_U on (U.ID = ALL_U.USER_ID)
WHEN MATCHED THEN UPDATE SET U.NAME = U.NAME
WHEN NOT MATCHED THEN
INSERT (ID, NAME, ACTIVE, CREATEDON, MODIFIEDON, CREATEDBY, MODIFIEDBY) VALUES (ALL_U.USER_ID, ALL_U.USER_ID, 1, CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP, 'system', 'system');

DROP TABLE "UNIQUE_USER_ID";

--FOREIGN KEY
ALTER TABLE ANNOTATION ADD (
  CONSTRAINT ANNOTATION_FK_1
  FOREIGN KEY (CREATEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE ANNOTATION ADD (
  CONSTRAINT ANNOTATION_FK_2
  FOREIGN KEY (MODIFIEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE "AUDIT" ADD (
  CONSTRAINT AUDIT_FK_1
  FOREIGN KEY (USER_ISID)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE CONTAINER ADD (
  CONSTRAINT CONTAINER_FK_1
  FOREIGN KEY (CREATEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE CONTAINER ADD (
  CONSTRAINT CONTAINER_FK_2
  FOREIGN KEY (MODIFIEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE JOB ADD (
  CONSTRAINT JOB_FK_1
  FOREIGN KEY (CREATEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE JOB ADD (
  CONSTRAINT JOB_FK_2
  FOREIGN KEY (MODIFIEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE JOB_PARAMETER ADD (
  CONSTRAINT JOB_PARAMETER_FK_1
  FOREIGN KEY (CREATEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE JOB_PARAMETER ADD (
  CONSTRAINT JOB_PARAMETER_FK_2
  FOREIGN KEY (MODIFIEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE MODEL ADD (
  CONSTRAINT MODEL_FK_1
  FOREIGN KEY (CREATEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE MODEL ADD (
  CONSTRAINT MODEL_FK_2
  FOREIGN KEY (MODIFIEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE MODEL_ITEM ADD (
  CONSTRAINT MODEL_ITEM_FK_1
  FOREIGN KEY (CREATEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE MODEL_ITEM ADD (
  CONSTRAINT MODEL_ITEM_FK_2
  FOREIGN KEY (MODIFIEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE MODEL_PARAMETER ADD (
  CONSTRAINT MODEL_PARAMETER_FK_1
  FOREIGN KEY (CREATEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE MODEL_PARAMETER ADD (
  CONSTRAINT MODEL_PARAMETER_FK_2
  FOREIGN KEY (MODIFIEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE MODEL_STEP ADD (
  CONSTRAINT MODEL_STEP_FK_1
  FOREIGN KEY (CREATEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE MODEL_STEP ADD (
  CONSTRAINT MODEL_STEP_FK_2
  FOREIGN KEY (MODIFIEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE MODEL_STEP_PARAMETER ADD (
  CONSTRAINT MODEL_STEP_PARAMETER_FK_1
  FOREIGN KEY (CREATEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE MODEL_STEP_PARAMETER ADD (
  CONSTRAINT MODEL_STEP_PARAMETER_FK_2
  FOREIGN KEY (MODIFIEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE PERMISSION_AUDIT ADD (
  CONSTRAINT PERMISSION_AUDIT_FK_1
  FOREIGN KEY (USER_ISID)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE PERMISSION_AUDIT ADD (
  CONSTRAINT PERMISSION_AUDIT_FK_2
  FOREIGN KEY (TARGET_USER_ISID)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE SUBSCRIPTION ADD (
  CONSTRAINT SUBSCRIPTION_FK_1
  FOREIGN KEY (USERNAME)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE SUBSCRIPTION_MESSAGE ADD (
  CONSTRAINT SUBSCRIPTION_MESSAGE_FK_1
  FOREIGN KEY (USERNAME)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE SVN_ENTRY ADD (
  CONSTRAINT SVN_ENTRY_FK_CR_1
  FOREIGN KEY (CREATEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE SVN_ENTRY ADD (
  CONSTRAINT SVN_ENTRY_FK_CR_2
  FOREIGN KEY (MODIFIEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE USER_PERMISSION ADD (
  CONSTRAINT USER_PERMISSION_FK_1
  FOREIGN KEY (CREATEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE USER_PERMISSION ADD (
  CONSTRAINT USER_PERMISSION_FK_2
  FOREIGN KEY (MODIFIEDBY)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

ALTER TABLE USER_PERMISSION ADD (
  CONSTRAINT USER_PERMISSION_FK_3
  FOREIGN KEY (USER_ID)
  REFERENCES "USER" (ID)
  ON DELETE CASCADE
  ENABLE VALIDATE);

--INDEX

CREATE INDEX IDX_ANNOTATION_CREATEDBY ON ANNOTATION (CREATEDBY);
CREATE INDEX IDX_ANNOTATION_MODIFIEDBY ON ANNOTATION (MODIFIEDBY);

CREATE INDEX IDX_CONTAINER_CREATEDBY ON CONTAINER (CREATEDBY);
CREATE INDEX IDX_CONTAINER_MODIFIEDBY ON CONTAINER (MODIFIEDBY);

CREATE INDEX IDX_JOB_CREATEDBY ON JOB (CREATEDBY);
CREATE INDEX IDX_JOB_MODIFIEDBY ON JOB (MODIFIEDBY);

CREATE INDEX IDX_JOB_PARAMETER_CREATEDBY ON JOB_PARAMETER (CREATEDBY);
CREATE INDEX IDX_JOB_PARAMETER_MODIFIEDBY ON JOB_PARAMETER (MODIFIEDBY);

CREATE INDEX IDX_MODEL_CREATEDBY ON MODEL (CREATEDBY);
CREATE INDEX IDX_MODEL_MODIFIEDBY ON MODEL (MODIFIEDBY);

CREATE INDEX IDX_MODEL_ITEM_CREATEDBY ON MODEL_ITEM (CREATEDBY);
CREATE INDEX IDX_MODEL_ITEM_MODIFIEDBY ON MODEL_ITEM (MODIFIEDBY);

CREATE INDEX IDX_MODEL_PARAMETER_CREATEDBY ON MODEL_PARAMETER (CREATEDBY);
CREATE INDEX IDX_MODEL_PARAMETER_MODIFIEDBY ON MODEL_PARAMETER (MODIFIEDBY);

CREATE INDEX IDX_MODEL_STEP_CREATEDBY ON MODEL_STEP (CREATEDBY);
CREATE INDEX IDX_MODEL_STEP_MODIFIEDBY ON MODEL_STEP (MODIFIEDBY);

CREATE INDEX IDX_M_S_P_CREATEDBY ON MODEL_STEP_PARAMETER (CREATEDBY);
CREATE INDEX IDX_M_S_P_MODIFIEDBY ON MODEL_STEP_PARAMETER (MODIFIEDBY);

CREATE INDEX IDX_P_A_TARGET_ISID ON PERMISSION_AUDIT (TARGET_USER_ISID);
CREATE INDEX IDX_P_A_ISID ON PERMISSION_AUDIT (USER_ISID);

CREATE INDEX IDX_SUBSCRIPTION_USERNAME ON SUBSCRIPTION (USERNAME);

CREATE INDEX IDX_SUBS_MESSAGE_USERNAME ON SUBSCRIPTION_MESSAGE (USERNAME);

CREATE INDEX IDX_SVN_ENTRY_CREATEDBY ON SVN_ENTRY (CREATEDBY);
CREATE INDEX IDX_SVN_ENTRY_MODIFIEDBY ON SVN_ENTRY (MODIFIEDBY);

CREATE INDEX IDX_U_P_CREATEDBY ON USER_PERMISSION (CREATEDBY);
CREATE INDEX IDX_U_P_MODIFIEDBY ON USER_PERMISSION (MODIFIEDBY);
CREATE INDEX IDX_U_P_USER_ID ON USER_PERMISSION (USER_ID);