DROP TABLE ppsnepl.epl_drug_drug_interaction;

CREATE TABLE ppsnepl.epl_drug_drug_interaction
(
   epl_drug_drug_interaction_id   NUMBER (38) NOT NULL
  ,interaction_name               VARCHAR2 (200) NOT NULL
  ,drug_ingredient_1              NUMBER (30, 0) NOT NULL
  ,drug_ingredient_2              NUMBER (30, 0) NOT NULL
  ,severity                       NUMBER (30, 0) NOT NULL
  ,nationally_entered             NUMBER (1, 0)
  ,total_indexes                  NUMBER (38, 0)
  ,locally_edited                 NUMBER (1, 0)
  ,vista_field_number             VARCHAR2 (10) NOT NULL
  ,vista_ien                      VARCHAR2 (50) NOT NULL
  ,created_by                     VARCHAR2 (50) NOT NULL
  ,created_dtm                    TIMESTAMP (6) NOT NULL
  ,inactivation_date              TIMESTAMP (6)
  ,last_modified_by               VARCHAR2 (50)
  ,last_modified_dtm              TIMESTAMP (6)
);
--TABLESPACE ppsnepl_data;

ALTER TABLE ppsnepl.epl_drug_drug_interaction ADD CONSTRAINT uk_epl_ddi_1 UNIQUE (drug_ingredient_1, drug_ingredient_2, severity);

CREATE UNIQUE INDEX ppsnepl.epldrugdruginteractionid_pk
   ON ppsnepl.epl_drug_drug_interaction (epl_drug_drug_interaction_id);
--   TABLESPACE ppsnepl_data;

CREATE INDEX ppsnepl.drug_ingredient_1_idx
   ON ppsnepl.epl_drug_drug_interaction (drug_ingredient_1);
--   TABLESPACE ppsnepl_data;

CREATE INDEX ppsnepl.drug_ingredient_2_idx
   ON ppsnepl.epl_drug_drug_interaction (drug_ingredient_2);
--   TABLESPACE ppsnepl_data;

CREATE INDEX ppsnepl.severity_idx
   ON ppsnepl.epl_drug_drug_interaction (severity);
--   TABLESPACE ppsnepl_data;

ALTER TABLE ppsnepl.epl_drug_drug_interaction ADD (
  CONSTRAINT epldrugdruginteractionid_pk
  PRIMARY KEY
  (epl_drug_drug_interaction_id)
  USING INDEX ppsnepl.epldrugdruginteractionid_pk
  ENABLE VALIDATE);

ALTER TABLE ppsnepl.epl_drug_drug_interaction
  ADD CONSTRAINT fk_epl_ddi_epl_ingredients_1
  FOREIGN KEY (drug_ingredient_1)
  REFERENCES ppsnepl.epl_ingredients (epl_id);

ALTER TABLE ppsnepl.epl_drug_drug_interaction
  ADD CONSTRAINT fk_epl_ddi_epl_ingredients_2
  FOREIGN KEY (drug_ingredient_2)
  REFERENCES ppsnepl.epl_ingredients (epl_id);

ALTER TABLE ppsnepl.epl_drug_drug_interaction
  ADD CONSTRAINT fk_epl_ddi_epl_severity
  FOREIGN KEY (severity)
  REFERENCES ppsnepl.epl_ddi_severity (epl_id);

GRANT SELECT ON ppsnepl.epl_drug_drug_interaction TO ppsnepl_read_only_role;

--sprint 10
ALTER TABLE ppsnepl.epl_drug_drug_interaction drop column vista_field_number;

ALTER TABLE ppsnepl.epl_drug_drug_interaction modify vista_ien NUMBER(30 , 0);

ALTER TABLE ppsnepl.epl_drug_drug_interaction drop CONSTRAINT uk_epl_ddi_1;
ALTER TABLE ppsnepl.epl_drug_drug_interaction ADD CONSTRAINT uk_epl_ddi_1 UNIQUE (drug_ingredient_1, drug_ingredient_2);
