-- ABS-74042/ ABS-123104

---CONTACT table---
ALTER TABLE CONTACT MODIFY CONTACTNOTES VARCHAR2(2000);
ALTER TABLE CONTACT MODIFY FOLLOWUPNOTES VARCHAR2(4000);

ALTER TABLE CONTACT ADD (TITLE VARCHAR2(240) NULL,
                         CONTACTPHONE VARCHAR2(60) NULL,
                         CONTACTEMAIL VARCHAR2(2000) NULL);

COMMENT ON COLUMN CONTACT.TITLE
  IS 'Title of the Contact Message ';
COMMENT ON COLUMN CONTACT.CONTACTPHONE
  IS 'Contact Phone for message ';
COMMENT ON COLUMN CONTACT.CONTACTEMAIL
  IS 'Contact Email for message';

ALTER TABLE CONTACT MODIFY (CONTACTPERSONNAME VARCHAR2(301));

---CONTACTFOLLOWUPNOTE table---
ALTER TABLE CONTACTFOLLOWUPNOTE MODIFY (NOTE VARCHAR2(4000));


--- TELESERVICESIMPORT TABLE ---
CREATE TABLE TELESERVICESIMPORT
(
  INCIDENT_ID             NUMBER(15) NOT NULL,
  TASK_ID                 NUMBER NOT NULL,
  NOTE_ID                 NUMBER NOT NULL,
  SR_SUMMARY              VARCHAR2(240) NOT NULL,
  SR_NOTES                VARCHAR2(2000) NOT NULL,
  SR_NUMBER               VARCHAR2(64) NOT NULL,
  STATUS_CODE             VARCHAR2(90) NOT NULL,
  TASK_STATUS             VARCHAR2(30) NOT NULL,
  SR_CREATION_CHANNEL     VARCHAR2(50) NULL,
  PROBLEM_CODE            VARCHAR2(80) NOT NULL,
  SITE_NAME               VARCHAR2(240) NULL,
  CUSTOMER_NAME           VARCHAR2(360) NULL,
  SUBJECT                 VARCHAR2(672) NULL,
  CUSTOMER_ID             VARCHAR2(150) NULL,
  MARKET_CENTER           VARCHAR2(150) NULL,
  CUSTOMER_ADDRESS        VARCHAR2(423) NULL,
  SR_CONTACT              VARCHAR2(301) NULL,
  SR_CONTACT_PHONE        VARCHAR2(51) NULL,
  SR_CONTACT_EMAIL        VARCHAR2(2000) NULL,
  SR_CREATION_DATE        VARCHAR2(20) NULL,
  SR_LAST_UPDATE_DATE     DATE NOT NULL,
  TASK_LAST_UPDATE_DATE   DATE NOT NULL,
  TASK_CREATION_DATE      DATE NOT NULL,
  TASK_NOTE_CREATION_DATE DATE NOT NULL,
  CREATION_DATE           DATE NULL,
  ORIG_ID                 NUMBER NULL,
  SR_NOTE_DETAILS1        VARCHAR2(4000) NULL,
  SR_NOTE_DETAILS2        VARCHAR2(4000) NULL,
  SR_NOTE_DETAILS1_CLEAN  VARCHAR2(4000) NULL,
  SR_NOTE_DETAILS2_CLEAN  VARCHAR2(4000) NULL,
  TASK_SCH_END_DATE	  DATE NULL,
  PROCESSED               NUMBER NOT NULL
                          CONSTRAINT CHK_TELESERVICESIMP_PROCESSED CHECK (PROCESSED IN (0, 1)),
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
)
;

COMMENT ON COLUMN TELESERVICESIMPORT.PROCESSED
  IS '0 = Not Processed
1 = Processed
';

ALTER TABLE TELESERVICESIMPORT
  ADD CONSTRAINT XPKTELESERVICESIMPORT PRIMARY KEY (NOTE_ID, TASK_LAST_UPDATE_DATE);

COMMENT ON COLUMN TELESERVICESIMPORT.SR_NOTE_DETAILS1_CLEAN
  IS 'Clean text from SR_NOTE_DETAILS1';
COMMENT ON COLUMN TELESERVICESIMPORT.SR_NOTE_DETAILS2_CLEAN
  IS 'Clean text from SR_NOTE_DETAILS2';

-- TELESERVICESEXPORT TABLE --
CREATE SEQUENCE SEQ_TELESERVICESEXPORT
START WITH 1
INCREMENT BY 1;

CREATE TABLE TELESERVICESEXPORT
(
  TELESERVICESEXPORT_ID  NUMBER NOT NULL,
  PLANT_ID		 NUMBER NOT NULL,
  CONTACT_ID             NUMBER NOT NULL,
  CONTACTFOLLOWUPNOTE_ID NUMBER NULL,
  ERD_DATE               DATE NULL,
  NOTES                  VARCHAR2(500) NULL,
  RESOLVED               VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_TELESERVEXP_RESOLV
                         CHECK (RESOLVED IN ('Y', 'N')),
  CREATEDTIMESTAMP       DATE NOT NULL,
  EMAILSENTTIMESTAMP     DATE NULL,
  PROCESSEDTIMESTAMP     DATE NULL,
  STATUS                 NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_TELESERVICESEXP_STATUS
                         CHECK (STATUS IN (0, 1, 2, 3)),
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
)
;
 
COMMENT ON COLUMN TELESERVICESEXPORT.ERD_DATE
  IS 'New estimated resolution date';
COMMENT ON COLUMN TELESERVICESEXPORT.NOTES
  IS 'Driver notes';
COMMENT ON COLUMN TELESERVICESEXPORT.STATUS
  IS '0 – pending send (default)
1 – email sent
2 – confirmation of email processed
3 - resent
';

ALTER TABLE TELESERVICESEXPORT
  ADD CONSTRAINT XPKTELESERVICESEXPORT PRIMARY KEY (TELESERVICESEXPORT_ID);

CREATE INDEX XIF1TELESERVICESEXPORT ON TELESERVICESEXPORT (CONTACT_ID ASC);
CREATE INDEX XIF2TELESERVICESEXPORT ON TELESERVICESEXPORT (CONTACTFOLLOWUPNOTE_ID ASC);
CREATE INDEX XIF3TELESERVICESEXPORT ON TELESERVICESEXPORT (PLANT_ID ASC);

ALTER TABLE TELESERVICESEXPORT
  ADD CONSTRAINT FK_TELESERVICESEXP_CONTACT FOREIGN KEY (CONTACT_ID)
  REFERENCES CONTACT (CONTACT_ID);
  
ALTER TABLE TELESERVICESEXPORT
  ADD CONSTRAINT FK_TELESERVICESEXP_CONTACTFN FOREIGN KEY (CONTACTFOLLOWUPNOTE_ID)
  REFERENCES CONTACTFOLLOWUPNOTE (CONTACTFOLLOWUPNOTE_ID);

ALTER TABLE TELESERVICESEXPORT
  ADD CONSTRAINT FK_TELESERVICESEXP_PLANT FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;

-- TELESERVICESDATACLEAN TABLE --
CREATE SEQUENCE SEQ_TELESERVICESDATACLEAN
START WITH 1
INCREMENT BY 1;

CREATE TABLE TELESERVICESDATACLEAN
(
  TELESERVICESDATACLEAN_ID NUMBER NOT NULL,
  TEXTTOREMOVE             VARCHAR2(2000) NOT NULL,
  TYPEOFREMOVAL            NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_TELESERVDATACLEAN_TOR
                           CHECK (TYPEOFREMOVAL IN (0, 1, 2)),
  ACTIVE                   VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_TELESERVDATACLEAN_ACTIVE
                           CHECK (ACTIVE IN ('Y', 'N')),
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE NOT NULL
)
;

COMMENT ON COLUMN TELESERVICESDATACLEAN.TYPEOFREMOVAL
  IS '0 = remove line when line contains (default)
1 = remove line when line starts with
2 = remove line when line is exactly
';

ALTER TABLE TELESERVICESDATACLEAN
  ADD CONSTRAINT XPKTELESERVICESDATACLEAN PRIMARY KEY (TELESERVICESDATACLEAN_ID);


--- CONTACTTELESERVICES TABLE ---
CREATE TABLE CONTACTTELESERVICES
(
  CONTACT_ID    NUMBER NOT NULL,
  SR_NUMBER     VARCHAR2(64) NOT NULL,
  SUBJECT_EMAIL VARCHAR2(700),
  SYSTEMUSER_ID NUMBER NOT NULL,
  TASK_ID 	NUMBER NOT NULL,
  TIMESTAMP     DATE NOT NULL
)
;

COMMENT ON COLUMN CONTACTTELESERVICES.SR_NUMBER
  IS 'Service request number in Orcale Teleservices';
COMMENT ON COLUMN CONTACTTELESERVICES.SUBJECT_EMAIL
  IS 'Subject of the structured email sent back to Oracle Teleservices';
COMMENT ON COLUMN CONTACTTELESERVICES.TASK_ID
  IS 'Task ID from TeleservicesImport';

ALTER TABLE CONTACTTELESERVICES
  ADD CONSTRAINT XPKCONTACTTELESERVICES PRIMARY KEY (CONTACT_ID);
  
CREATE UNIQUE INDEX XAK1CONTACTTELESERVICES ON CONTACTTELESERVICES (SR_NUMBER);
  
ALTER TABLE CONTACTTELESERVICES
  ADD CONSTRAINT FK_CONTACTTELESERVICES_CONTACT FOREIGN KEY (CONTACT_ID)
  REFERENCES CONTACT (CONTACT_ID) ON DELETE CASCADE;

-- CONTACTRASYNC TABLE --
CREATE TABLE CONTACTRASYNC
(
  CONTACT_ID      NUMBER NOT NULL,
  ROUTECONTROL_ID NUMBER NOT NULL,
  MOBILEDEVICE_ID NUMBER NOT NULL,
  SYSTEMUSER_ID   NUMBER NOT NULL,
  TIMESTAMP	  DATE NOT NULL
)
;

ALTER TABLE CONTACTRASYNC
  ADD CONSTRAINT XPKCONTACTRASYNC PRIMARY KEY (CONTACT_ID, ROUTECONTROL_ID, MOBILEDEVICE_ID);
  
CREATE INDEX XIF1CONTACTRASYNC ON CONTACTRASYNC (CONTACT_ID);
CREATE INDEX XIF2CONTACTRASYNC ON CONTACTRASYNC (ROUTECONTROL_ID);
CREATE INDEX XIF3CONTACTRASYNC ON CONTACTRASYNC (MOBILEDEVICE_ID);
  
ALTER TABLE CONTACTRASYNC
  ADD CONSTRAINT FK_CTRASYNC_CT FOREIGN KEY (CONTACT_ID)
  REFERENCES CONTACT (CONTACT_ID) ON DELETE CASCADE;
  
ALTER TABLE CONTACTRASYNC
  ADD CONSTRAINT FK_CTRASYNC_RC FOREIGN KEY (ROUTECONTROL_ID)
  REFERENCES ROUTECONTROL (ROUTECONTROL_ID) ON DELETE CASCADE;
  
ALTER TABLE CONTACTRASYNC
  ADD CONSTRAINT FK_CTRASYNC_MD FOREIGN KEY (MOBILEDEVICE_ID)
  REFERENCES MOBILEDEVICE (MOBILEDEVICE_ID) ON DELETE CASCADE;

-- CONTACTFOLLOWUPNOTERASYNC TABLE --
CREATE TABLE CONTACTFOLLOWUPNOTERASYNC
(
  CONTACTFOLLOWUPNOTE_ID      NUMBER NOT NULL,
  ROUTECONTROL_ID             NUMBER NOT NULL,
  MOBILEDEVICE_ID             NUMBER NOT NULL,
  SYSTEMUSER_ID		      NUMBER NOT NULL,
  TIMESTAMP		      DATE NOT NULL
)
;

ALTER TABLE CONTACTFOLLOWUPNOTERASYNC
  ADD CONSTRAINT XPKCONTACTFOLLOWUPNOTERASYNC PRIMARY KEY (CONTACTFOLLOWUPNOTE_ID, ROUTECONTROL_ID, MOBILEDEVICE_ID);
  
CREATE INDEX XIF1CONTACTFOLLOWUPNOTERASYNC ON CONTACTFOLLOWUPNOTERASYNC (CONTACTFOLLOWUPNOTE_ID);
CREATE INDEX XIF2CONTACTFOLLOWUPNOTERASYNC ON CONTACTFOLLOWUPNOTERASYNC (ROUTECONTROL_ID);
CREATE INDEX XIF3CONTACTFOLLOWUPNOTERASYNC ON CONTACTFOLLOWUPNOTERASYNC (MOBILEDEVICE_ID);
   
ALTER TABLE CONTACTFOLLOWUPNOTERASYNC
  ADD CONSTRAINT FK_CTFUPNOTERASYNC_CT FOREIGN KEY (CONTACTFOLLOWUPNOTE_ID)
  REFERENCES CONTACTFOLLOWUPNOTE (CONTACTFOLLOWUPNOTE_ID) ON DELETE CASCADE;
  
ALTER TABLE CONTACTFOLLOWUPNOTERASYNC
  ADD CONSTRAINT FK_CTFUPNOTERASYNC_RC FOREIGN KEY (ROUTECONTROL_ID)
  REFERENCES ROUTECONTROL (ROUTECONTROL_ID) ON DELETE CASCADE;
  
ALTER TABLE CONTACTFOLLOWUPNOTERASYNC
  ADD CONSTRAINT FK_CTFUPNOTERASYNC_MD FOREIGN KEY (MOBILEDEVICE_ID)
  REFERENCES MOBILEDEVICE (MOBILEDEVICE_ID) ON DELETE CASCADE;

-- CONTACTFOLLOWUPTELESERVICE TABLE --
CREATE TABLE CONTACTFOLLOWUPTELESERVICE
(
  CONTACTFOLLOWUPNOTE_ID NUMBER NOT NULL,
  NOTE_ID                NUMBER NOT NULL,
  SYSTEMUSER_ID          NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL
)
;

COMMENT ON COLUMN CONTACTFOLLOWUPTELESERVICE.NOTE_ID
  IS 'Note number in Oracle Teleservices';

ALTER TABLE CONTACTFOLLOWUPTELESERVICE
  ADD CONSTRAINT XPKCONTACTFOLLOWUPTELESERVICE PRIMARY KEY (CONTACTFOLLOWUPNOTE_ID);
  
CREATE UNIQUE INDEX XAK1CONTACTFOLLOWUPTELESERVICE ON CONTACTFOLLOWUPTELESERVICE (NOTE_ID);

ALTER TABLE CONTACTFOLLOWUPTELESERVICE
  ADD CONSTRAINT FK_CONTACTFPTELESERV_CTCFPNOTE FOREIGN KEY (CONTACTFOLLOWUPNOTE_ID)
  REFERENCES CONTACTFOLLOWUPNOTE (CONTACTFOLLOWUPNOTE_ID) ON DELETE CASCADE;


--- ROUTEASSISTANTSYNCREQUEST TABLE ---
CREATE SEQUENCE SEQ_ROUTEASSISTANTSYNCREQUEST
START WITH 1
INCREMENT BY 1;

CREATE TABLE ROUTEASSISTANTSYNCREQUEST
(
  ROUTEASSISTANTSYNCREQUEST_ID NUMBER NOT NULL,
  ROUTECONTROL_ID              NUMBER NOT NULL,
  CREATIONTIMESTAMP            DATE NOT NULL,
  SENTTIMESTAMP                DATE NULL,
  SYNCTIMESTAMP                DATE NULL,
  SYSTEMUSER_ID                NUMBER NOT NULL,
  TIMESTAMP                    DATE NOT NULL
)
;

COMMENT ON COLUMN ROUTEASSISTANTSYNCREQUEST.SENTTIMESTAMP
  IS 'Timestamp when the sync request was sent via firebase';

ALTER TABLE ROUTEASSISTANTSYNCREQUEST
  ADD CONSTRAINT XPKROUTEASSISTANTSYNCREQUEST PRIMARY KEY (ROUTEASSISTANTSYNCREQUEST_ID);
  
CREATE INDEX XIF1ROUTEASSISTANTSYNCREQUEST ON ROUTEASSISTANTSYNCREQUEST (ROUTECONTROL_ID);
  
ALTER TABLE ROUTEASSISTANTSYNCREQUEST
  ADD CONSTRAINT FK_ROUTEASSISTSYNCREQ_RC FOREIGN KEY (ROUTECONTROL_ID)
  REFERENCES ROUTECONTROL (ROUTECONTROL_ID) ON DELETE CASCADE;

--- ROUTECONTROLEXTRAINFO TABLE ---
ALTER TABLE ROUTECONTROLEXTRAINFO ADD (TOKEN VARCHAR2(300) NULL);

COMMENT ON COLUMN ROUTECONTROLEXTRAINFO.TOKEN
  IS 'Used only for module ROUTEASSISTANTFIREBASE';