-------------- arm-core-utility ----------------

-- AP_DOM
CREATE TABLE IF NOT EXISTs AP_DOM (
    COD_CAMPO varchar(30) NOT NULL,
    VAL_CAMPO varchar(100) NOT NULL,
    DES_DOM varchar(4000) NULL,
    COD_ML varchar(100) NULL,
    PROD varchar(1) NULL,
    TRASC_VAL varchar(4000) NULL,
    ORD_VIS numeric(3,0) NULL,
    TRASC_VAL_1 varchar(4000) NULL,
    STATUS varchar(1) NULL,
    INFO varchar(4000) NULL,
    DATA_AGG TIMESTAMP NULL,
    HIDE numeric(1,0) DEFAULT 0 NULL,
    TRASC_VAL_2 varchar(250) NULL,
    USERID_UPD varchar(30) NULL,
    CONSTRAINT PK_AP_DOM PRIMARY KEY (COD_CAMPO, VAL_CAMPO)
);

-- AP_LANG
CREATE TABLE IF NOT EXISTS AP_LANG (
    CODE varchar(200) NULL,
    LANG varchar(2) NOT NULL,
    DESCR varchar(4000) NULL,
    DATA_AGG TIMESTAMP NULL,
    ORIGIN varchar(10) NULL,
    DESCR_MCHAR CLOB NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS PK_AP_LANG ON AP_LANG (CODE ASC, LANG ASC);

-- AP_OPZIONI
CREATE TABLE IF NOT EXISTS AP_OPZIONI (
    USERID varchar(30) NOT NULL,
    SEZIONE varchar(15) NOT NULL,
    OPZIONE varchar(50) NOT NULL,
    VALORE CLOB NULL,
    DESCRIZIONE varchar(255) NULL,
    TIPO varchar(1) DEFAULT 'S' NULL,
    OVERRIDE varchar(1) DEFAULT 'N' NULL,
    DATA_AGG TIMESTAMP NULL,
    USERID_UPD varchar(30) NULL,
    CONSTRAINT AP_OPZIONI_PK PRIMARY KEY (USERID, SEZIONE, OPZIONE)
);

-------------- arm-core-users ----------------
--SE_UO
CREATE TABLE IF NOT EXISTS SE_UO (
    ID_UO BIGINT NOT NULL,
    COD_UO varchar(30) NULL,
    DESC_UO varchar(255) NULL,
    ID_USER_MANAGER varchar(20) NULL,
    ID_UO_FATHER BIGINT NULL,
    NOTE varchar(500) NULL,
    COD_ML_NOTE varchar(65) NULL,
    DATE_UPDATE TIMESTAMP NULL,
    ID_USER_UPD varchar(30) NULL,
    DELETED TINYINT NULL,
    COD_ML_DESC varchar(65) NULL,
    GOAL_CONTRIBUTOR TINYINT NULL,
    CENTRAL_OFFICE TINYINT NULL,
    PROSPECT_MANAGER TINYINT NULL,
    COD_FIL varchar(10) NULL,
    EMAIL_UO varchar(256) NULL,
    CONSTRAINT SE_UO_PK PRIMARY KEY (ID_UO)
);

--SE_USERS
CREATE TABLE IF NOT EXISTS SE_USERS (
    USERID varchar(50) NOT NULL,
    NAME varchar(50) NULL,
    LASTNAME varchar(70) NULL,
    TITLE varchar(10) NULL,
    EMAIL varchar(290) NULL,
    PHONE varchar(50) NULL,
    PASSWORD varchar(255) NULL,
    FIRSTNAME varchar(255) NULL,
    DTPASSWORD TIMESTAMP NULL,
    LASTPASSWORD varchar(255) NULL,
    COD_TYPEENT varchar(10) NULL,
    USER_ACR varchar(10) NULL,
    COD_LANG varchar(10) NULL,
    COD_FIL varchar(10) NULL,
    STATUS varchar(1) NULL,
    EXT_KEY varchar(20) NULL,
    COD_BANK varchar(15) NULL,
    ID_USER_UPD varchar(20) NULL,
    DATE_UPD TIMESTAMP NULL,
    IS_FAKE TINYINT NULL,
    DATE_DELETED TIMESTAMP NULL,
    DATE_LAST_LOGIN TIMESTAMP NULL,
    TIPO_ACCOUNT varchar(20) NULL,
    COD_COMPAGNIA varchar(30) NULL,
    ID_PERSON BIGINT NULL,
    COD_PORT varchar(16) NULL,
    IP_ADDRESS varchar(100) NULL,
    PASS_TEMP varchar(255) NULL,
    DATE_CRE TIMESTAMP NULL,
    DATE_PREVIOUS_LOGIN TIMESTAMP NULL,
    CLIENT_VERSION varchar(50) NULL,
    DELETED TINYINT NULL,
    CONSECUTIVE_FAILURE BIGINT NULL,
    LOGIN_FAILURE BIGINT NULL,
    MASKED_MODE TINYINT NULL,
    LOGIN_SUCCESS BIGINT NULL,
    CODICE_INTERNO BIGINT NULL,
    IS_ENABLED TINYINT NULL,
    DATE_VERIFIED_EMAIL TIMESTAMP NULL,
    DATE_VERIFIED_PHONE TIMESTAMP NULL,
    EMAIL_TO_VERIFY varchar(50) NULL,
    PHONE_TO_VERIFY varchar(50) NULL,
    TOKEN_VERIFY_EMAIL varchar(10) NULL,
    TOKEN_VERIFY_PHONE varchar(10) NULL,
    DATE_EXPIRE_TOKEN_EMAIL TIMESTAMP NULL,
    DATE_EXPIRE_TOKEN_PHONE TIMESTAMP NULL,
    DATE_DISABLED TIMESTAMP NULL,
    DATE_BLOCK TIMESTAMP NULL,
    USERID_BO varchar(50) NULL,
    COD_FISCALE varchar(30) NULL,
    N_ISCRIZIONE_RUI varchar(30) NULL,
    DT_ISCRIZIONE_RUI TIMESTAMP NULL,
    DT_CANCELLAZIONE_RUI TIMESTAMP NULL,
    COD_CDR varchar(30) NULL,
    COD_REG varchar(10) NULL,
    LICENSE TINYINT NULL,
    ACTIVE_JSESSIONID varchar(255) NULL,
    CONSTRAINT SE_USERS_PK PRIMARY KEY (USERID)
);

--SE_USERS_ACCESS
CREATE TABLE IF NOT EXISTS SE_USERS_ACCESS (
    ID BIGINT AUTO_INCREMENT NOT NULL,
    DATA_LOGIN TIMESTAMP NULL,
    DATA_LOGOUT TIMESTAMP NULL,
    USER_ID varchar(20) NULL,
    IP_ADDRESS varchar(100) NULL,
    USER_AGENT varchar(200) NULL,
    SESSION_ID varchar(40) NULL,
    RESULT_MESSAGE varchar(150) NULL,
    RESULT_STATUS TINYINT NULL,
    FROM_AVATAR TINYINT NULL,
    CLIENT_VERSION varchar(50) NULL,
    APPLICATION_NAME varchar(255) NULL,
    CONSTRAINT SE_USERS_ACCESS_PK PRIMARY KEY (ID)
);

--SE_USERS_ROLES
CREATE TABLE IF NOT EXISTS SE_USERS_ROLES (
    USERID varchar(20) NOT NULL,
    ROLEID varchar(30) NOT NULL,
    DTROLE TIMESTAMP NULL,
    ENABLED TINYINT NULL,
    ID_USER_UPD varchar(20) NULL,
    DATE_UPD TIMESTAMP NULL,
    CONSTRAINT SE_USERS_ROLES_PK PRIMARY KEY (USERID, ROLEID)
);

--SE_USERS_UO
CREATE TABLE IF NOT EXISTS SE_USERS_UO (
    ID_USER varchar(20) NOT NULL,
    ID_UO BIGINT NOT NULL,
    MAIN TINYINT NULL,
    DATE_ACT TIMESTAMP NULL,
    CONSTRAINT SE_USERS_UO_PK PRIMARY KEY (ID_USER, ID_UO)
);

-- SE_AUTHORIZATION
CREATE TABLE IF NOT EXISTS SE_AUTHORIZATION (
    AUTHID varchar(50) NOT NULL,
    AUTHDESCR varchar(255) NULL,
    APPLICATION varchar(3) NULL,
    CONSTRAINT SE_AUTHORIZATION_PK PRIMARY KEY (AUTHID)
);

-- SE_HISTORY_USERS_UO
CREATE TABLE IF NOT EXISTS SE_HISTORY_USERS_UO (
    ID_USER varchar(20) NULL,
    ID_UO BIGINT NULL,
    DATE_FROM TIMESTAMP NULL,
    DATE_TO TIMESTAMP NULL,
    DATE_ACT TIMESTAMP NULL
);

-- SE_ROLES
CREATE TABLE IF NOT EXISTS SE_ROLES (
    ROLEID varchar(30) NOT NULL,
    ROLEDESC varchar(255) NULL,
    ID_USER_UPD varchar(20) NULL,
    DATE_UPD TIMESTAMP NULL,
    ID_USER_CRE varchar(20) NULL,
    DATE_CRE TIMESTAMP NULL,
    APPLICATION varchar(3) NULL,
    CONSTRAINT SE_ROLES_PK PRIMARY KEY (ROLEID)
);

-- SE_ROLES_AUTH
CREATE TABLE IF NOT EXISTS SE_ROLES_AUTH (
    ROLEID varchar(30) NOT NULL,
    AUTHID varchar(50) NOT NULL,
    DTAUTH TIMESTAMP NULL,
    ID_USER_UPD varchar(20) NULL,
    DATE_UPD TIMESTAMP NULL,
    CONSTRAINT SE_ROLES_AUTH_PK PRIMARY KEY (ROLEID, AUTHID)
);

