-- ABS-57449/ ABS-70449

  
-- WEBEVENTTYPE table --
INSERT INTO WEBEVENTTYPE(WEBEVENTTYPE_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP) 
  SELECT 300, 'Login', GETMINSYSTEMUSER(), SYSDATE FROM DUAL;
COMMIT;

INSERT INTO WEBEVENTTYPE(WEBEVENTTYPE_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP) 
  SELECT 301, 'Automatic Log Out', GETMINSYSTEMUSER(), SYSDATE FROM DUAL;
COMMIT;

INSERT INTO WEBEVENTTYPE(WEBEVENTTYPE_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP) 
  SELECT 302, 'Repair Logged', GETMINSYSTEMUSER(), SYSDATE FROM DUAL;
COMMIT;

INSERT INTO WEBEVENTTYPE(WEBEVENTTYPE_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP) 
  SELECT 303, 'Lost and Found', GETMINSYSTEMUSER(), SYSDATE FROM DUAL;
COMMIT;

INSERT INTO WEBEVENTTYPE(WEBEVENTTYPE_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP) 
  SELECT 304, 'Password Reset', GETMINSYSTEMUSER(), SYSDATE FROM DUAL;
COMMIT;

INSERT INTO WEBEVENTTYPE(WEBEVENTTYPE_ID, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP) 
  SELECT 305, 'PIN Reset', GETMINSYSTEMUSER(), SYSDATE FROM DUAL;
COMMIT;

-- WEBPORTALEVENT TABLE -- 
ALTER TABLE WEBPORTALEVENT ADD (WEARER_ID NUMBER NULL);

CREATE INDEX XIF5WEBPORTALEVENT ON WEBPORTALEVENT (WEARER_ID ASC);

ALTER TABLE WEBPORTALEVENT
  ADD CONSTRAINT WEBPORTALEVENT_WEARER FOREIGN KEY (WEARER_ID)
  REFERENCES WEARER (WEARER_ID) ON DELETE SET NULL;

-- AUTOMATICFLAG table --
INSERT INTO AUTOMATICFLAG
      (AUTOMATICFLAG_ID,
       EVENT,
       SYSTEMUSER_ID,
       TIMESTAMP,
       SELECTABLE,
       DELETABLE,
       TRANSFERABLE,
       FLAGTYPE)
    VALUES
      (SEQ_AUTOMATICFLAG.NEXTVAL,
       'Lost And Found',
       GETMINSYSTEMUSER(),
       SYSDATE,
       'N',
       'N',
       'N',
       1);
COMMIT;

INSERT INTO AUTOMATICFLAG
      (AUTOMATICFLAG_ID,
       EVENT,
       SYSTEMUSER_ID,
       TIMESTAMP,
       SELECTABLE,
       DELETABLE,
       TRANSFERABLE,
       FLAGTYPE)
    VALUES
      (SEQ_AUTOMATICFLAG.NEXTVAL,
       'Repair requested by Web',
       GETMINSYSTEMUSER(),
       SYSDATE,
       'N',
       'Y',
       'Y',
       1);
COMMIT;

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

COMMENT ON COLUMN CUSTOMER.MYUNIFORMASSISTALLOW
  IS 'Only applicable for WEARERSERVICE module';

-- HELPTEXT table --
CREATE SEQUENCE SEQ_HELPTEXT
START WITH 1
INCREMENT BY 1;

CREATE TABLE HELPTEXT
(
  HELPTEXT_ID   NUMBER NOT NULL,
  CUSTOMER_ID   NUMBER NULL,
  SCREENID      NUMBER NOT NULL
                CONSTRAINT SCREEN_VALID1 CHECK (SCREENID BETWEEN 1 AND 12),
  SCREENNAME    VARCHAR2(40) NOT NULL,
  HELPTEXT      CLOB NOT NULL,
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP     DATE NOT NULL
)
;

COMMENT ON COLUMN HELPTEXT.SCREENID
  IS '1 - Kiosk login
2 - Phone login
3 - My Repairs
4 - My Profile
5 - My Profile Phone Number
6 - Lost and Found
7 - My Garments
8 - My Satisfaction
9 - My Order
10 - My Points
11 - My Notifications 
12 - QuickView
';

ALTER TABLE HELPTEXT
  ADD CONSTRAINT XPKHELPTEXT PRIMARY KEY (HELPTEXT_ID);

CREATE UNIQUE INDEX XAK1HELPTEXT ON HELPTEXT (CUSTOMER_ID ASC, SCREENID ASC);
 
CREATE INDEX XIF1HELPTEXT ON HELPTEXT (CUSTOMER_ID ASC);

ALTER TABLE HELPTEXT
  ADD CONSTRAINT HELPTEXT_CUSTOMER FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;

INSERT INTO HELPTEXT(HELPTEXT_ID, SCREENID, SCREENNAME, HELPTEXT, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_HELPTEXT.NEXTVAL,
       1,
       'Kiosk Login',
       'Kiosk Login',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO HELPTEXT(HELPTEXT_ID, SCREENID, SCREENNAME, HELPTEXT, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_HELPTEXT.NEXTVAL,
       2,
       'Phone Login',
       'Phone Login',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO HELPTEXT(HELPTEXT_ID, SCREENID, SCREENNAME, HELPTEXT, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_HELPTEXT.NEXTVAL,
       3,
       'My Repairs',
       'My Repairs',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO HELPTEXT(HELPTEXT_ID, SCREENID, SCREENNAME, HELPTEXT, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_HELPTEXT.NEXTVAL,
       4,
       'My Profile',
       'My Profile',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO HELPTEXT(HELPTEXT_ID, SCREENID, SCREENNAME, HELPTEXT, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_HELPTEXT.NEXTVAL,
       5,
       'My Profile Phone Number',
       'This number is optional and will not be shared with any 3rd parties.  It will only be used by the laundry to communicate with you if you log some lost items.',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO HELPTEXT(HELPTEXT_ID, SCREENID, SCREENNAME, HELPTEXT, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_HELPTEXT.NEXTVAL,
       6,
       'Lost and Found',
       'Lost and Found',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO HELPTEXT(HELPTEXT_ID, SCREENID, SCREENNAME, HELPTEXT, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_HELPTEXT.NEXTVAL,
       7,
       'My Garments',
       'My Garments',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO HELPTEXT(HELPTEXT_ID, SCREENID, SCREENNAME, HELPTEXT, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_HELPTEXT.NEXTVAL,
       8,
       'My Satisfaction',
       'My Satisfaction',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO HELPTEXT(HELPTEXT_ID, SCREENID, SCREENNAME, HELPTEXT, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_HELPTEXT.NEXTVAL,
       9,
       'My Orders',
       'My Orders',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO HELPTEXT(HELPTEXT_ID, SCREENID, SCREENNAME, HELPTEXT, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_HELPTEXT.NEXTVAL,
       10,
       'My Points',
       'My Points',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO HELPTEXT(HELPTEXT_ID, SCREENID, SCREENNAME, HELPTEXT, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_HELPTEXT.NEXTVAL,
       11,
       'My Notifications',
       'My Notifications',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO HELPTEXT(HELPTEXT_ID, SCREENID, SCREENNAME, HELPTEXT, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_HELPTEXT.NEXTVAL,
       12,
       'Quick View',
       'Quick View',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

-- HELPTEXT_DESC table --
CREATE TABLE HELPTEXT_DESC
(
  HELPTEXT_ID   NUMBER NOT NULL,
  LANGUAGE_ID   NUMBER NOT NULL,
  HELPTEXT      CLOB NOT NULL,
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP     DATE NOT NULL
)
;

ALTER TABLE HELPTEXT_DESC
  ADD CONSTRAINT XPKHELPTEXT_DESC PRIMARY KEY (HELPTEXT_ID, LANGUAGE_ID);
  
CREATE INDEX XIF1HELPTEXT_DESC ON HELPTEXT_DESC (HELPTEXT_ID ASC);
CREATE INDEX XIF2HELPTEXT_DESC ON HELPTEXT_DESC (LANGUAGE_ID ASC);

ALTER TABLE HELPTEXT_DESC
  ADD CONSTRAINT HELPTEXT_DESC_HELPTEXT FOREIGN KEY (HELPTEXT_ID)
  REFERENCES HELPTEXT (HELPTEXT_ID) ON DELETE CASCADE;

ALTER TABLE HELPTEXT_DESC
  ADD CONSTRAINT HELPTEXT_DESC_LANGUAGE FOREIGN KEY (LANGUAGE_ID)
  REFERENCES LANGUAGES (LANGUAGE_ID);

INSERT INTO HELPTEXT_DESC (HELPTEXT_ID, LANGUAGE_ID, HELPTEXT, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       H.HELPTEXT_ID,
       L.LANGUAGE_ID,
       H.HELPTEXT,
       GETMINSYSTEMUSER(),
       SYSDATE
FROM HELPTEXT H, LANGUAGES L;
COMMIT;

-- MUASETTINGS table --
CREATE SEQUENCE SEQ_MUASETTINGS
START WITH 1
INCREMENT BY 1;

CREATE TABLE MUASETTINGS
(
  MUASETTINGS_ID NUMBER NOT NULL,
  NAME           VARCHAR2(25) NOT NULL,
  DISPLAYNAME    VARCHAR2(255) NOT NULL,
  TYPE           NUMBER NOT NULL
                 CONSTRAINT TYPE_VALID1 CHECK (TYPE BETWEEN 0 AND 3),
  DEFAULTVALUE   VARCHAR2(100) NULL,
  DESCRIPTION    VARCHAR2(4000) NULL,
  SYSTEMUSER_ID  NUMBER NOT NULL,
  TIMESTAMP      DATE NOT NULL
)
;

COMMENT ON COLUMN MUASETTINGS.TYPE
  IS '0 = Boolean
1 = Integer
2 = String
3 = Float
';

ALTER TABLE MUASETTINGS
  ADD CONSTRAINT XPKMUASETTINGS PRIMARY KEY (MUASETTINGS_ID);
  
CREATE UNIQUE INDEX XAK1MUASETTINGS ON MUASETTINGS (NAME ASC);

INSERT INTO MUASETTINGS(MUASETTINGS_ID, NAME, DISPLAYNAME, TYPE, DEFAULTVALUE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_MUASETTINGS.NEXTVAL,
       'TWOFACTORAUTHEN',
       'Two Factor Login Authentication Needed',
       0,
       'Y',
       'Y = PIN number requested after scanning / entering garment ID',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO MUASETTINGS(MUASETTINGS_ID, NAME, DISPLAYNAME, TYPE, DEFAULTVALUE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_MUASETTINGS.NEXTVAL,
       'WELCOMEMETHOD',
       'Wearer Welcome Communication Method',
       1,
       '0',
       '0 = none
        1 = email
        2 = csv',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO MUASETTINGS(MUASETTINGS_ID, NAME, DISPLAYNAME, TYPE, DEFAULTVALUE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_MUASETTINGS.NEXTVAL,
       'PASSWORDCOMPLEXITY',
       'Password Complexity',
       1,
       '0',
       '0 = no complexity
        1 = complexity check
        >4 = minimum length, no other complexity check',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO MUASETTINGS(MUASETTINGS_ID, NAME, DISPLAYNAME, TYPE, DEFAULTVALUE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_MUASETTINGS.NEXTVAL,
       'SECONDAYUNIQUEIDUSED',
       'Secondary ID used',
       0,
       'N',
       'Y = check for unique id will be on primary and secondary',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO MUASETTINGS(MUASETTINGS_ID, NAME, DISPLAYNAME, TYPE, DEFAULTVALUE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_MUASETTINGS.NEXTVAL,
       'TIMEOUT',
       'System Timeout',
       1,
       '10',
       'Timeout in minutes that a wearer is logged out for no activity',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO MUASETTINGS(MUASETTINGS_ID, NAME, DISPLAYNAME, TYPE, DEFAULTVALUE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_MUASETTINGS.NEXTVAL,
       'GARMENTTILE',
       'Garment Tile',
       0,
       'Y',
       'If My Garment tile is shown',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO MUASETTINGS(MUASETTINGS_ID, NAME, DISPLAYNAME, TYPE, DEFAULTVALUE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_MUASETTINGS.NEXTVAL,
       'PROFILETILE',
       'Profile Tile',
       0,
       'Y',
       'If My Profile tile is shown',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO MUASETTINGS(MUASETTINGS_ID, NAME, DISPLAYNAME, TYPE, DEFAULTVALUE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_MUASETTINGS.NEXTVAL,
       'REPAIRSTILE',
       'Repairs Tile',
       0,
       'Y',
       'If My Repairs tile is shown',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO MUASETTINGS(MUASETTINGS_ID, NAME, DISPLAYNAME, TYPE, DEFAULTVALUE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_MUASETTINGS.NEXTVAL,
       'LOSTANDFOUNDTILE',
       'Lost and Found Tile',
       0,
       'Y',
       'If My Lost and Found tile is shown',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO MUASETTINGS(MUASETTINGS_ID, NAME, DISPLAYNAME, TYPE, DEFAULTVALUE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_MUASETTINGS.NEXTVAL,
       'PHONECONTACT',
       'Lost and Found Phone contact compulsory',
       0,
       'N',
       'If phone number is compulsory in Lost and Found',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO MUASETTINGS(MUASETTINGS_ID, NAME, DISPLAYNAME, TYPE, DEFAULTVALUE, DESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
SELECT
       SEQ_MUASETTINGS.NEXTVAL,
       'NUMBEROFLASTDIGITS',
       'Number of last digits of ID code for uniqueness',
       1,
       '5',
       'This is the number of least significant digits (last digits) of a garment ID code that need to be entered.  This can vary due to the spread of codes the customer has.',
       GETMINSYSTEMUSER(),
       SYSDATE
FROM DUAL;
COMMIT;

-- MUACUSTOMERSETTINGS TABLE --
CREATE SEQUENCE SEQ_MUACUSTOMERSETTINGS
START WITH 1
INCREMENT BY 1;

CREATE TABLE MUACUSTOMERSETTINGS
(
  MUACUSTOMERSETTINGS_ID NUMBER NOT NULL,
  MUASETTINGS_ID         NUMBER NOT NULL,
  CUSTOMER_ID            NUMBER NOT NULL,
  SETTINGVALUE           VARCHAR2(100) NOT NULL,
  SYSTEMUSER_ID          NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL
)
;

ALTER TABLE MUACUSTOMERSETTINGS
  ADD CONSTRAINT XPKMUACUSTOMERSETTINGS PRIMARY KEY (MUACUSTOMERSETTINGS_ID);

CREATE UNIQUE INDEX XAK1MUACUSTOMERSETTINGS ON MUACUSTOMERSETTINGS (MUASETTINGS_ID ASC, CUSTOMER_ID ASC);
CREATE INDEX XIF1MUACUSTOMERSETTINGS ON MUACUSTOMERSETTINGS (MUASETTINGS_ID ASC);
CREATE INDEX XIF2MUACUSTOMERSETTINGS ON MUACUSTOMERSETTINGS (CUSTOMER_ID ASC);

ALTER TABLE MUACUSTOMERSETTINGS
  ADD CONSTRAINT MUACUSTOMERSETTINGS_MUASETT FOREIGN KEY (MUASETTINGS_ID)
  REFERENCES MUASETTINGS (MUASETTINGS_ID) ON DELETE CASCADE;
  
ALTER TABLE MUACUSTOMERSETTINGS
  ADD CONSTRAINT MUACUSTOMERSETTINGS_CUST FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;



-- WEARER table --
COMMENT ON COLUMN WEARER.WEBWEARER
  IS 'Indicates if wearer may have access to wearer web portal. Also now used for My Garment Assistant.';

-- MUAWEARERPROFILE TABLE --
CREATE TABLE MUAWEARERPROFILE
(
  WEARER_ID        NUMBER NOT NULL,
  INACTIVEDATE     DATE NULL,
  PIN              VARCHAR2(4) NULL,
  STARTLOCKTIME    DATE NULL,
  FAILEDLOGINCOUNT NUMBER DEFAULT 0 NOT NULL,
  WELCOMESTATUS    NUMBER DEFAULT 0 NOT NULL
                   CONSTRAINT WELCOMESTATUS_VALID1 CHECK (WELCOMESTATUS BETWEEN 0 AND 2),
  PHONENUMBER      VARCHAR2(15) NULL,
  SYSTEMUSER_ID    NUMBER NOT NULL,
  TIMESTAMP        DATE NOT NULL
)
;

COMMENT ON COLUMN MUAWEARERPROFILE.WELCOMESTATUS
  IS '0 = not sent
1 = sent but not used
2 = Active
';

ALTER TABLE MUAWEARERPROFILE
  ADD CONSTRAINT XPKMUAWEARERPROFILE PRIMARY KEY (WEARER_ID);

-- CUSTOMERIMAGE TABLE --
CREATE TABLE CUSTOMERIMAGE
(
  CUSTOMER_ID   NUMBER NOT NULL,
  IMAGE         BLOB NOT NULL,
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP     DATE NOT NULL
)
;

ALTER TABLE CUSTOMERIMAGE
  ADD CONSTRAINT XPKCUSTOMERIMAGE PRIMARY KEY (CUSTOMER_ID);
  
ALTER TABLE CUSTOMERIMAGE
  ADD CONSTRAINT CUSTOMERIMAGE_CU FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;
