-- ABS-56309/ ABS-58327

--- PREFERENCE table ---
CREATE SEQUENCE SEQ_PREFERENCE
START WITH 1
INCREMENT BY 1;

CREATE TABLE PREFERENCE
(
  PREFERENCE_ID          NUMBER NOT NULL,
  DESCRIPTION            VARCHAR2(50) NOT NULL,
  DEFAULTVALUE           VARCHAR2(100) NULL,
  UPDATEDBYSYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL
);

ALTER TABLE PREFERENCE
  ADD CONSTRAINT XPKPREFERENCE PRIMARY KEY (PREFERENCE_ID);

CREATE UNIQUE INDEX XAK1PREFERENCE ON PREFERENCE (DESCRIPTION ASC);

insert into preference p
select seq_preference.nextval,
       'FONT_SIZE',
       'Compact',
       getminsystemuser(),
       sysdate
from dual;
commit;

insert into preference p
select seq_preference.nextval,
       'MENU_TYPE',
       'Static',
       getminsystemuser(),
       sysdate
from dual;
commit;

insert into preference p
select seq_preference.nextval,
       'PROFILE_TYPE',
       'Top',
       getminsystemuser(),
       sysdate
from dual;
commit;

insert into preference p
select seq_preference.nextval,
       'CURRENT_BU',
       null,
       getminsystemuser(),
       sysdate
from dual;
commit;

insert into preference p
select seq_preference.nextval,
       'CURRENT_WORKSPOT',
       null,
       getminsystemuser(),
       sysdate
from dual;
commit;

--- SYSTEMUSERPREFERENCE table ---
CREATE SEQUENCE SEQ_SYSTEMUSERPREFERENCE
START WITH 1
INCREMENT BY 1;

CREATE TABLE SYSTEMUSERPREFERENCE
(
  SYSTEMUSERPREFERENCE_ID NUMBER NOT NULL,
  SYSTEMUSER_ID           NUMBER NOT NULL,
  PREFERENCE_ID           NUMBER NOT NULL,
  VALUE                   VARCHAR2(100) NOT NULL,
  UPDATEDBYSYSTEMUSER_ID  NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
);

ALTER TABLE SYSTEMUSERPREFERENCE
  ADD CONSTRAINT XPKSYSTEMUSERPREFERENCE PRIMARY KEY (SYSTEMUSERPREFERENCE_ID);

CREATE INDEX XAK1SYSTEMUSERPREFERENCE ON SYSTEMUSERPREFERENCE (SYSTEMUSER_ID ASC, PREFERENCE_ID ASC);

CREATE INDEX XIF1SYSTEMUSERPREFERENCE ON SYSTEMUSERPREFERENCE (SYSTEMUSER_ID ASC);
CREATE INDEX XIF2SYSTEMUSERPREFERENCE ON SYSTEMUSERPREFERENCE (PREFERENCE_ID ASC);

ALTER TABLE SYSTEMUSERPREFERENCE
  ADD CONSTRAINT SYSTEMUSERPREFERENCE_SU FOREIGN KEY (SYSTEMUSER_ID)
  REFERENCES SYSTEMUSER (SYSTEMUSER_ID) ON DELETE CASCADE;
ALTER TABLE SYSTEMUSERPREFERENCE
  ADD CONSTRAINT SYSTEMUSERPREFERENCE_P FOREIGN KEY (PREFERENCE_ID)
  REFERENCES PREFERENCE (PREFERENCE_ID) ON DELETE CASCADE;
