-- ABS-57449/ ABS-70449

-- CUSTOMER table -- 
ALTER TABLE CUSTOMER DROP COLUMN MYUNIFORMASSISTALLOW;

-- CUSTOMEREXTRAINFO TABLE --
ALTER TABLE CUSTOMEREXTRAINFO ADD (MYUNIFORMASSISTALLOW VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_VALIDATIONFIELD446 CHECK (MYUNIFORMASSISTALLOW IN ('Y', 'N')));

COMMENT ON COLUMN CUSTOMEREXTRAINFO.MYUNIFORMASSISTALLOW
  IS 'Only applicable for WEARERSERVICE module';
  
-- MUASETTINGS table --
INSERT INTO MUASETTINGS(MUASETTINGS_ID, NAME, DISPLAYNAME, TYPE, DEFAULTVALUE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_MUASETTINGS.NEXTVAL,
       'SYSTEMUSERNAME',
       'System user name used for the ABSSolute session',
       2,
       null,
       'Automatically generated as the customer number when the customer is switch to MUA on and is used for the ABSSolute session for all wearers.',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

-- MUAWEARERPROFILE table --
ALTER TABLE MUAWEARERPROFILE
  ADD CONSTRAINT MUAWEARERPROFILE_WEARER FOREIGN KEY (WEARER_ID)
  REFERENCES WEARER (WEARER_ID) ON DELETE CASCADE;

-- EMAILSETTING table --
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 soil 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 = (Succesfully) 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';

ALTER TABLE EMAILSETTING
  DROP CONSTRAINT DOCUMENTTYPE_VALID2_1;

ALTER TABLE EMAILSETTING
  ADD CONSTRAINT DOCUMENTTYPE_VALID3
  CHECK (DOCUMENTTYPE BETWEEN 1 AND 32);

-- EMAILSETTING_DESC table --
CREATE TABLE EMAILSETTING_DESC
(
  EMAILSETTING_ID NUMBER NOT NULL,
  LANGUAGE_ID     NUMBER NOT NULL,
  SUBJECT         VARCHAR2(80) NOT NULL,
  BODY            VARCHAR2(1000) NULL,
  SYSTEMUSER_ID   NUMBER NOT NULL,
  TIMESTAMP       DATE NOT NULL
)
;

ALTER TABLE EMAILSETTING_DESC
  ADD CONSTRAINT XPKEMAILSETTING_DESC PRIMARY KEY (EMAILSETTING_ID, LANGUAGE_ID);
  
CREATE INDEX XIF1EMAILSETTING_DESC ON EMAILSETTING_DESC (EMAILSETTING_ID ASC);
CREATE INDEX XIF2EMAILSETTING_DESC ON EMAILSETTING_DESC (LANGUAGE_ID ASC);

ALTER TABLE EMAILSETTING_DESC
  ADD CONSTRAINT EMAILSETTING_DESC_ES FOREIGN KEY (EMAILSETTING_ID)
  REFERENCES EMAILSETTING (EMAILSETTING_ID) ON DELETE CASCADE;
  
ALTER TABLE EMAILSETTING_DESC
  ADD CONSTRAINT EMAILSETTING_DESC_L FOREIGN KEY (LANGUAGE_ID)
  REFERENCES LANGUAGES (LANGUAGE_ID);

-- EMAILSETTINGREPLPARAM TABLE --
CREATE SEQUENCE SEQ_EMAILSETTINGREPLPARAM
START WITH 1
INCREMENT BY 1;

CREATE TABLE EMAILSETTINGREPLPARAM
(
  EMAILSETTINGREPLPARAM_ID NUMBER NOT NULL,
  DOCUMENTTYPE             NUMBER NOT NULL,
  REPLACEABLEPARAMETER     VARCHAR2(1000) NOT NULL,
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE NOT NULL
)
;

COMMENT ON COLUMN EMAILSETTINGREPLPARAM.REPLACEABLEPARAMETER
  IS 'A description of the replaceable parameters this document type supports';

ALTER TABLE EMAILSETTINGREPLPARAM
  ADD CONSTRAINT XPKEMAILSETTINGREPLPARAM PRIMARY KEY (EMAILSETTINGREPLPARAM_ID);
  
CREATE UNIQUE INDEX XAK1EMAILSETTINGREPLPARAM ON EMAILSETTINGREPLPARAM (DOCUMENTTYPE ASC);

INSERT INTO EMAILSETTINGREPLPARAM(EMAILSETTINGREPLPARAM_ID, DOCUMENTTYPE, REPLACEABLEPARAMETER, SYSTEMUSER_ID, TIMESTAMP)
SELECT SEQ_EMAILSETTINGREPLPARAM.NEXTVAL,
       1,
       'Parameter #B is replaced by the Customer Number of the Bill to customer
        Parameter #D is replaced by the Customer Number of the Deliver to customer
        Parameter #N is replaced by the Invoice Number',
       GETMINSYSTEMUSER(),
       SYSDATE
       FROM DUAL;
COMMIT;

INSERT INTO EMAILSETTINGREPLPARAM(EMAILSETTINGREPLPARAM_ID, DOCUMENTTYPE, REPLACEABLEPARAMETER, SYSTEMUSER_ID, TIMESTAMP)
SELECT SEQ_EMAILSETTINGREPLPARAM.NEXTVAL,
       4,
       'Parameter # is replaced by the WebUsers password',
       GETMINSYSTEMUSER(),
       SYSDATE
       FROM DUAL;
COMMIT;

INSERT INTO EMAILSETTINGREPLPARAM(EMAILSETTINGREPLPARAM_ID, DOCUMENTTYPE, REPLACEABLEPARAMETER, SYSTEMUSER_ID, TIMESTAMP)
SELECT SEQ_EMAILSETTINGREPLPARAM.NEXTVAL,
       16,
       'Parameter #P is replaced by the WebUsers password',
       GETMINSYSTEMUSER(),
       SYSDATE
       FROM DUAL;
COMMIT;

INSERT INTO EMAILSETTINGREPLPARAM(EMAILSETTINGREPLPARAM_ID, DOCUMENTTYPE, REPLACEABLEPARAMETER, SYSTEMUSER_ID, TIMESTAMP)
SELECT SEQ_EMAILSETTINGREPLPARAM.NEXTVAL,
       25,
       'Parameter #C is replaced by the customer number
        Parameter #W is replaced by the department number
        Parameter #D is replaced by the consumption point number
        Parameter #P is replaced by the product code
        Parameter #S is replaced by the size code
        Parameter #F is replaced by the finishing method code
        Parameter #Q is replaced by the free-of-charge contract quantity
        Parameter #R is replaced by the total delivered quantity for the contract period
        Parameter #O is replaced by the overuse quantity
        Parameter #L is replaced by the comparison quantity
        Parameter #T is replaced by the delivery note number',
       GETMINSYSTEMUSER(),
       SYSDATE
       FROM DUAL;
COMMIT;

INSERT INTO EMAILSETTINGREPLPARAM(EMAILSETTINGREPLPARAM_ID, DOCUMENTTYPE, REPLACEABLEPARAMETER, SYSTEMUSER_ID, TIMESTAMP)
SELECT SEQ_EMAILSETTINGREPLPARAM.NEXTVAL,
       26,
       'No change, only used in PLSQL for now',
       GETMINSYSTEMUSER(),
       SYSDATE
       FROM DUAL;
COMMIT;

INSERT INTO EMAILSETTINGREPLPARAM(EMAILSETTINGREPLPARAM_ID, DOCUMENTTYPE, REPLACEABLEPARAMETER, SYSTEMUSER_ID, TIMESTAMP)
SELECT SEQ_EMAILSETTINGREPLPARAM.NEXTVAL,
       27,
       'Parameter #N is replaced by the PIN number
        Parameter #W is replaced by the wearer’s full name',
       GETMINSYSTEMUSER(),
       SYSDATE
       FROM DUAL;
COMMIT;

INSERT INTO EMAILSETTINGREPLPARAM(EMAILSETTINGREPLPARAM_ID, DOCUMENTTYPE, REPLACEABLEPARAMETER, SYSTEMUSER_ID, TIMESTAMP)
SELECT SEQ_EMAILSETTINGREPLPARAM.NEXTVAL,
       28,
       'Parameter #W is replaced by the wearer’s full name 
        Parameter #P is replaced by the wearer’s password',
       GETMINSYSTEMUSER(),
       SYSDATE
       FROM DUAL;
COMMIT;

INSERT INTO EMAILSETTINGREPLPARAM(EMAILSETTINGREPLPARAM_ID, DOCUMENTTYPE, REPLACEABLEPARAMETER, SYSTEMUSER_ID, TIMESTAMP)
SELECT SEQ_EMAILSETTINGREPLPARAM.NEXTVAL,
       30,
       'Parameter #W is replaced by the wearer’s full name',
       GETMINSYSTEMUSER(),
       SYSDATE
       FROM DUAL;
COMMIT;

INSERT INTO EMAILSETTINGREPLPARAM(EMAILSETTINGREPLPARAM_ID, DOCUMENTTYPE, REPLACEABLEPARAMETER, SYSTEMUSER_ID, TIMESTAMP)
SELECT SEQ_EMAILSETTINGREPLPARAM.NEXTVAL,
       31,
       'Parameter #W is replaced by the wearer’s full name 
        Parameter #L is replaced by the URL of the web page
        Parameter #N is replaced by the PIN number
        Parameter #P is replaced by the wearer’s password',
       GETMINSYSTEMUSER(),
       SYSDATE
       FROM DUAL;
COMMIT;

INSERT INTO EMAILSETTINGREPLPARAM(EMAILSETTINGREPLPARAM_ID, DOCUMENTTYPE, REPLACEABLEPARAMETER, SYSTEMUSER_ID, TIMESTAMP)
SELECT SEQ_EMAILSETTINGREPLPARAM.NEXTVAL,
       32,
       'Parameter #W is replaced by the wearer’s full name 
        Parameter #U is replaced by the new username',
       GETMINSYSTEMUSER(),
       SYSDATE
       FROM DUAL;
COMMIT;