
--------------------------------------------------
-- 07/02/2020
-- Author: Tony Chen 
-- Following changes are made to integrade with 
-- PHOENIX system.
--------------------------------------------------

-- New tables. 
CREATE TABLE `zl_country` 
(
    `ID`                              BIGINT AUTO_INCREMENT,
    `NAME`                            VARCHAR(255) NOT NULL,
    `TITLE`                           VARCHAR(255) NOT NULL,
    `ABBREVIATION`                    VARCHAR(255) NOT NULL,

    CONSTRAINT pk_zl_country_01 PRIMARY KEY (`ID`),
    CONSTRAINT uq_zl_country_01 UNIQUE (`NAME`),
    CONSTRAINT uq_zl_country_02 UNIQUE (`TITLE`),
    CONSTRAINT uq_zl_country_03 UNIQUE (`ABBREVIATION`)
);

CREATE TABLE `zl_state` 
(
    `ID`                              BIGINT AUTO_INCREMENT,
    `NAME`                            VARCHAR(255) NOT NULL,
    `TITLE`                           VARCHAR(255) NOT NULL,
    `ABBREVIATION`                    VARCHAR(255) NOT NULL,

    CONSTRAINT pk_zl_state_01 PRIMARY KEY (`ID`),
    CONSTRAINT uq_zl_state_01 UNIQUE (`NAME`),
    CONSTRAINT uq_zl_state_02 UNIQUE (`TITLE`),
    CONSTRAINT uq_zl_state_03 UNIQUE (`ABBREVIATION`)
);

CREATE TABLE `zl_su_transaction_type` 
(
    `ID`                              BIGINT AUTO_INCREMENT,
    `NAME`                            VARCHAR(255) NOT NULL,
    `TITLE`                           VARCHAR(255) NOT NULL,
    `ABBREVIATION`                    VARCHAR(255) NOT NULL,

    CONSTRAINT pk_zl_su_transaction_type_01 PRIMARY KEY (`ID`),
    CONSTRAINT uq_zl_su_transaction_type_01 UNIQUE (`NAME`),
    CONSTRAINT uq_zl_su_transaction_type_02 UNIQUE (`TITLE`),
    CONSTRAINT uq_zl_su_transaction_type_03 UNIQUE (`ABBREVIATION`)
);

INSERT INTO `zl_su_transaction_type` (`ID`, `NAME`, `TITLE`, `ABBREVIATION`) VALUES 
  ('1', 'Adjustment', 'ADJUSTMENT', 'ADJ'),
  ('2', 'Award', 'AWARD', 'AWD'),
  ('3', 'Purchased', 'PURCHASED', 'PRCH'),
  ('4', 'Usage', 'USAGE', 'USG')
;
-- --------------------------------------------------

CREATE TABLE `user_su_transaction` 
(
    `ID`                            BIGINT AUTO_INCREMENT,
    `USER_ID`                       BIGINT NOT NULL, 
    `SU_AMOUNT`                     INT NOT NULL, 
    `TRANSACTION_NUMBER`            VARCHAR(255) NOT NULL, 
    `TRANSACTION_TIME`              DATETIME NOT NULL,
    `SU_TRANSACTION_TYPE_L`         BIGINT NOT NULL, 
    `NOTE`                          VARCHAR(2000) NULL, 
    `CREATED_TIME`                  DATETIME NOT NULL, 
    `CREATED_BY`                    BIGINT NOT NULL, 
    
    CONSTRAINT pk_user_su_transaction_01 PRIMARY KEY (`ID`), 
    CONSTRAINT uq_user_su_transaction_01 UNIQUE (`TRANSACTION_NUMBER`), 

    CONSTRAINT fk_user_su_transaction_01 FOREIGN KEY (`USER_ID`) 
        REFERENCES `users` (`USER_ID`) ON DELETE NO ACTION     ON UPDATE NO ACTION,
    
    CONSTRAINT fk_user_su_transaction_02 FOREIGN KEY (`CREATED_BY`) 
        REFERENCES `users` (`USER_ID`) ON DELETE NO ACTION     ON UPDATE NO ACTION,

    CONSTRAINT fk_user_su_transaction_03 FOREIGN KEY (`SU_TRANSACTION_TYPE_L`) 
        REFERENCES `zl_su_transaction_type` (`ID`) ON DELETE NO ACTION     ON UPDATE NO ACTION
);
-- --------------------------------------------------

CREATE TABLE `user_su_allocation` 
(
    `ID`                            BIGINT AUTO_INCREMENT,
    `USER_ID`                       BIGINT NOT NULL, 
    `START_TIME`                    DATETIME NOT NULL, 
    `SU_MAX`                        INT NOT NULL,
    `SU_EXPIRE_TIME`                DATETIME NOT NULL, 
    `SU_VALID_DAYS`                 INT NOT NULL, 
    `NOTE`                          VARCHAR(2000) NULL, 
    `CREATED_TIME`                  DATETIME NOT NULL, 
    `CREATED_BY`                    BIGINT NOT NULL, 
    `LAST_MODIFIED_TIME`            DATETIME NOT NULL, 
    `LAST_MODIFIED_BY`              BIGINT NOT NULL, 

    CONSTRAINT pk_user_su_allocation_01 PRIMARY KEY (`ID`), 

    CONSTRAINT fk_user_su_allocation_01 FOREIGN KEY (`USER_ID`) 
        REFERENCES `users` (`USER_ID`) ON DELETE NO ACTION     ON UPDATE NO ACTION,

    CONSTRAINT fk_user_su_allocation_02 FOREIGN KEY (`CREATED_BY`) 
        REFERENCES `users` (`USER_ID`) ON DELETE NO ACTION     ON UPDATE NO ACTION,

    CONSTRAINT fk_user_su_allocation_03 FOREIGN KEY (`LAST_MODIFIED_BY`) 
        REFERENCES `users` (`USER_ID`) ON DELETE NO ACTION     ON UPDATE NO ACTION
);
-- --------------------------------------------------

INSERT INTO `version` (`VERSION`, `LAST_MODIFIED_TIME`) VALUES 
    ('2020-07-02', NOW())
;
-- --------------------------------------------------
