-----ABS-113802-----
                                                    
---------ContactCause---------
ALTER TABLE CONTACTCAUSE
      ADD (SHOWONWEBPORTAL VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CONTACTCAUSE_SHOWONWEBPORTAL CHECK (SHOWONWEBPORTAL IN ('Y', 'N')));

---Business Unit Activities---

ALTER TABLE BUSINESSUNITACTIVITIES 
ADD (SERVICEEMPLOYEE_ID NUMBER DEFAULT NULL);

CREATE INDEX XIF4BUSINESSUNITACTIVITIES ON BUSINESSUNITACTIVITIES (SERVICEEMPLOYEE_ID);

ALTER TABLE BUSINESSUNITACTIVITIES
ADD CONSTRAINT FK_BUACT_SERVICEEMPLOYEEID FOREIGN KEY(SERVICEEMPLOYEE_ID)
REFERENCES EMPLOYEE (EMPLOYEE_ID) ON DELETE SET NULL;

--------CustomerInternetOptions---------
ALTER TABLE CUSTOMERINTERNETOPTIONS 
      ADD (CONTACTSWEBPORTAL VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CUSTOMERINTERNETOPTIONS_CONTACTSWEBPORTAL CHECK (CONTACTSWEBPORTAL IN ('Y', 'N')),
           AUTOMAILCONTACTUPDATES VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CUSTOMERINTERNETOPTIONS_AUTOMAILCONTACTUPDATES CHECK (AUTOMAILCONTACTUPDATES IN ('Y', 'N'))) ;


----------------WEBUSER-----------------------

ALTER TABLE WEBUSER 
      ADD (ALLOWCREATECONTACTS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_WEBUSER_ALLOWCREATECONTACTS CHECK (ALLOWCREATECONTACTS IN (0,1)),
           ALLOWCLOSECONTACTS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_WEBUSER_ALLOWCLOSECONTACTS CHECK (ALLOWCLOSECONTACTS IN (0,1)) );
     
COMMENT ON COLUMN WEBUSER.ALLOWCREATECONTACTS IS 'This attribute controls whether this web user may create contacts from the web portal. This is only relevant when the CONTACTMANAGER module is active. Possible values: 0 = Not applicable/not allowed 1 = Web user may create contacts';                                               
COMMENT ON COLUMN WEBUSER.ALLOWCLOSECONTACTS IS 'This attribute controls whether this web user may close contacts from the web portal. This is only relevant when the CONTACTMANAGER module is active. Possible values: 0 = Not applicable/not allowed 1 = Web user may close contacts'; 

                                             
ALTER TABLE WEBUSERCUSTOMER
      ADD (CONTACTPERSON_ID NUMBER DEFAULT NULL);

CREATE INDEX XIF3WEBUSERCUSTOMER ON WEBUSERCUSTOMER (CONTACTPERSON_ID);

ALTER TABLE WEBUSERCUSTOMER 
      ADD CONSTRAINT FK_WEBUSERCUSTOMER_CONTACTPERSONID FOREIGN KEY (CONTACTPERSON_ID)
      REFERENCES CONTACTPERSON (CONTACTPERSON_ID);                                                  



---ContactCategory---

ALTER TABLE CONTACTCATEGORY 
      ADD (SHOWONWEBPORTAL VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CONTACTCATEGORY_SHOWONWEBPORTAL CHECK (SHOWONWEBPORTAL IN ('Y', 'N')),
           HANDLEDBYBUSINESSUNITTYPE NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_CONTACTCATEGORY_HANDLEDBYBUSINESSUNITTYPE CHECK (HANDLEDBYBUSINESSUNITTYPE IN (0,1,2)));

COMMENT ON COLUMN CONTACTCATEGORY.SHOWONWEBPORTAL IS 'Y/N field to indicate whether the contact category may be used on the web portal';
COMMENT ON COLUMN CONTACTCATEGORY.HANDLEDBYBUSINESSUNITTYPE IS 'Attribute to indicate which type of business unit will be used to link the contact category to. Possible values: 0 = Default business unit of customer 1 = Invoicing business unit of customer 2 = Determined via business line entered via web portal';        
                                                              
---------------Contact----------
ALTER TABLE CONTACT 
ADD (ORIGIN NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_CONTACT_ORIGIN CHECK (ORIGIN IN (0,1,2,3)),
     ACTIVITY_ID NUMBER NULL,
     CREATEDBYWEBUSER_ID NUMBER NULL,
     CLOSEDBYWEBUSER_ID NUMBER NULL);
   
COMMENT ON COLUMN CONTACT.CREATEDBYUSERNAME IS 'Will be filled with SystemUser.UserName or WebUser.LoginName depending on origin';
COMMENT ON COLUMN CONTACT.CLOSEDBYUSERNAME IS 'Will be filled with SystemUser.UserName or WebUser.LoginName depending on origin';
COMMENT ON COLUMN CONTACT.ORIGIN IS '0 = Not applicable/relevant 1 = Created by client application 2 = Created by route assistant 3 = Created by web portal';

CREATE INDEX XIF17CONTACT ON CONTACT (ACTIVITY_ID);
CREATE INDEX XIF18CONTACT ON CONTACT (CREATEDBYWEBUSER_ID);
CREATE INDEX XIF19CONTACT ON CONTACT (CLOSEDBYWEBUSER_ID);

ALTER TABLE CONTACT 
      ADD CONSTRAINT FK_CONTACT_ACTIVITYID FOREIGN KEY (ACTIVITY_ID) 
      REFERENCES ACTIVITY (ACTIVITY_ID) ON DELETE SET NULL;

ALTER TABLE CONTACT 
      ADD CONSTRAINT FK_CONTACT_CREATEDBYWEBUSERID FOREIGN KEY (CREATEDBYWEBUSER_ID) 
      REFERENCES WEBUSER (WEBUSER_ID) ON DELETE SET NULL;

ALTER TABLE CONTACT 
      ADD CONSTRAINT FK_CONTACT_CLOSEDBYWEBUSERID FOREIGN KEY (CLOSEDBYWEBUSER_ID) 
      REFERENCES WEBUSER (WEBUSER_ID) ON DELETE SET NULL;     

------------CONTACTFOLLOWUPNOTE------------
ALTER TABLE CONTACTFOLLOWUPNOTE 
      ADD (INTERNALUSEONLY NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_CONTACTFOLLOWUPNOTE_INTERNALUSEONLY CHECK (INTERNALUSEONLY IN (0,1)));
    
COMMENT ON COLUMN CONTACTFOLLOWUPNOTE.INTERNALUSEONLY IS '0 = Can be shared with customer 1 = For internal use only';

------CONTACTATTACHMENT-----
CREATE SEQUENCE SEQ_CONTACTATTACHMENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTACTATTACHMENT
( CONTACTATTACHMENT_ID NUMBER NOT NULL,  
  CONTACT_ID NUMBER NOT NULL, 
  ATTACHMENTNUMBER NUMBER NOT NULL,
  ATTACHMENTNAME VARCHAR2(260) NOT NULL,
  ATTACHMENT BLOB NOT NULL,
  CREATEDBYWEBUSER_ID NUMBER NULL,
  CREATEDBYUSER_ID NUMBER NULL,
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP DATE NOT NULL);

ALTER TABLE CONTACTATTACHMENT 
      ADD CONSTRAINT XPKCONTACTATTACHMENT PRIMARY KEY (CONTACTATTACHMENT_ID);
      
CREATE UNIQUE INDEX XAK1CONTACTATTACHMENT ON CONTACTATTACHMENT (CONTACT_ID, ATTACHMENTNUMBER);
CREATE INDEX XIF1CONTACTATTACHMENT ON CONTACTATTACHMENT (CONTACT_ID);
CREATE INDEX XIF2CONTACTATTACHMENT ON CONTACTATTACHMENT (CREATEDBYWEBUSER_ID);
CREATE INDEX XIF3CONTACTATTACHMENT ON CONTACTATTACHMENT (CREATEDBYUSER_ID);

ALTER TABLE CONTACTATTACHMENT 
      ADD CONSTRAINT FK_CONTACTATTACHMENT_CONTACTID FOREIGN KEY (CONTACT_ID) 
      REFERENCES CONTACT (CONTACT_ID) ON DELETE CASCADE;
      
ALTER TABLE CONTACTATTACHMENT 
      ADD CONSTRAINT FK_CONTACTATTACHMENT_CREATEDBYWEBUSERID FOREIGN KEY (CREATEDBYWEBUSER_ID) 
      REFERENCES WEBUSER (WEBUSER_ID) ON DELETE SET NULL;
      
ALTER TABLE CONTACTATTACHMENT 
      ADD CONSTRAINT FK_CONTACTATTACHMENT_CREATEDBYUSER_ID FOREIGN KEY (CREATEDBYUSER_ID) 
      REFERENCES SYSTEMUSER (SYSTEMUSER_ID) ON DELETE SET NULL;

-----EmailSetting-----
ALTER TABLE EMAILSETTING
DROP CONSTRAINT CHK_EMAILSETTING_DOCUMENTTYPE;

ALTER TABLE EMAILSETTING
      ADD CONSTRAINT CHK_EMAILSETTING_DOCUMENTTYPE CHECK (DOCUMENTTYPE BETWEEN 1 AND 38);
      
ALTER TABLE EMAILSETTING MODIFY FROMACCOUNT NULL;
ALTER TABLE EMAILSETTING
      ADD CONSTRAINT CHK_EMAILSETTING_FROMACCOUNT_DOCTYPE CHECK ( (FROMACCOUNT IS NOT NULL) OR ( (FROMACCOUNT IS NULL ) AND (DOCUMENTTYPE IN (37)) ) );      

ALTER TABLE EMAILSETTING 
DROP CONSTRAINT CHK_EMAILSETTING_BU_DOCTYPE;

ALTER TABLE EMAILSETTING
  ADD CONSTRAINT CHK_EMAILSETTING_BU_DOCTYPE
  CHECK ((BUSINESSUNIT_ID IS NULL) OR ( (BUSINESSUNIT_ID IS NOT NULL) AND (DOCUMENTTYPE IN (1, 9, 37, 38) ) ));

COMMENT ON COLUMN EMAILSETTING.DOCUMENTTYPE IS 
'1 = Invoice
2 = Statement
3 = Purchase order
4 = E-service password reminder
5 = Work order via web
6 = Invoice job checklist
7 = Customer
8 = Log Message
9 = Delivery Note / Consignment Note
10 = Stock Count / Order
11 = Wash List
12 = POD (Proof of delivery)
13 = On site scan list
14 = Shipment List
15 = Commission Report
16 = Wearer Web Login Credentials
17 = Recalculation Norm Stock Levels
18 = Recalculation Standard Deliveries
19 = Incidental Inventory
20 = Container Usage Report
21 = (Successfully) Processed OR Packs
22 = Unattended UHF Reader Status Changed
23 = Future planned deliveries
24 = Client Invoice
25 = Overuse notification
26 = Stock reservation quantity check
27 = MyUniformAssistant new PIN
28 = MyUniformAssistant Password change
29 = MyUniformAssistant test email
30 = MyUniformAssistant Password reset warning
31 = MyUniformAssistant Welcome email
32 = MyUniformAssistant username change
33 = Import payment notification
34 = Customer purchase order exception
35 = Diagnostics
36 = Credit card payment confirmation
37 = Contact notification (web)
38 = Contact notification (internal)';
