Kitty_Fall_Mytel/FIle/ddl_game_kitty_fall.sql

--------------------------------------------------------
-- DDL phan game Kitty Fall
-- Database: Oracle
-- Created: 2026-07-01
--
-- Ghi chu:
-- 1. Script nay chi tao bang/sequence/index/constraint/comment cho module game.
-- 2. Khong drop bang cu, khong sua cac bang ACCOUNT_USER, REG_INFO, LUCKY_SPIN.
-- 3. Cac comment dung tieng Viet khong dau de tranh loi encoding khi chay SQL.
--------------------------------------------------------

--------------------------------------------------------
-- SEQUENCES
--------------------------------------------------------

CREATE SEQUENCE SEQ_GAME_MODE START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_LEVEL_CONFIG START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_LEVEL_REWARD_OPTION START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_PLAY_SESSION START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_LEVEL_ATTEMPT START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_RESCUE_LOG START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_SCORE_LEDGER START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_REWARD_LIMIT_CONFIG START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_REWARD_LIMIT_USAGE START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_REWARD_LOG START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_MONTHLY_RANKING START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_MONTHLY_TICKET_RULE START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_MONTHLY_PRIZE_CONFIG START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_MONTHLY_DRAW START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_MONTHLY_DRAW_TICKET START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE SEQUENCE SEQ_GAME_MONTHLY_WINNER START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

--------------------------------------------------------
-- GAME_MODE
--------------------------------------------------------

CREATE TABLE GAME_MODE
(
    MODE_ID             NUMBER(10,0) NOT NULL,
    MODE_CODE           VARCHAR2(20 BYTE) NOT NULL,
    MODE_NAME           NVARCHAR2(100) NOT NULL,
    TIME_LIMIT_SECONDS  NUMBER(5,0) NOT NULL,
    DISPLAY_ORDER       NUMBER(5,0) DEFAULT 0 NOT NULL,
    STATUS              NUMBER(1,0) DEFAULT 1 NOT NULL,
    CREATED_TIME        DATE DEFAULT SYSDATE NOT NULL,
    UPDATED_TIME        DATE,
    CONSTRAINT PK_GAME_MODE PRIMARY KEY (MODE_ID),
    CONSTRAINT UK_GAME_MODE_CODE UNIQUE (MODE_CODE),
    CONSTRAINT CK_GAME_MODE_STATUS CHECK (STATUS IN (0, 1)),
    CONSTRAINT CK_GAME_MODE_CODE CHECK (MODE_CODE IN ('EASY', 'NORMAL', 'HARD'))
);

COMMENT ON TABLE GAME_MODE IS 'Danh muc che do choi Kitty Fall';
COMMENT ON COLUMN GAME_MODE.MODE_ID IS 'Khoa chinh che do choi';
COMMENT ON COLUMN GAME_MODE.MODE_CODE IS 'Ma che do choi: EASY, NORMAL, HARD';
COMMENT ON COLUMN GAME_MODE.MODE_NAME IS 'Ten hien thi cua che do choi';
COMMENT ON COLUMN GAME_MODE.TIME_LIMIT_SECONDS IS 'Thoi gian choi mac dinh moi level tinh bang giay';
COMMENT ON COLUMN GAME_MODE.DISPLAY_ORDER IS 'Thu tu hien thi tren UI';
COMMENT ON COLUMN GAME_MODE.STATUS IS 'Trang thai: 1 hieu luc, 0 khong hieu luc';
COMMENT ON COLUMN GAME_MODE.CREATED_TIME IS 'Thoi diem tao ban ghi';
COMMENT ON COLUMN GAME_MODE.UPDATED_TIME IS 'Thoi diem cap nhat gan nhat';

--------------------------------------------------------
-- GAME_LEVEL_CONFIG
--------------------------------------------------------

CREATE TABLE GAME_LEVEL_CONFIG
(
    LEVEL_CONFIG_ID     NUMBER(12,0) NOT NULL,
    MODE_ID             NUMBER(10,0) NOT NULL,
    LEVEL_NO            NUMBER(3,0) NOT NULL,
    BASE_SCORE          NUMBER(10,0) DEFAULT 0 NOT NULL,
    TIME_LIMIT_SECONDS  NUMBER(5,0) NOT NULL,
    IS_MILESTONE        NUMBER(1,0) DEFAULT 0 NOT NULL,
    STATUS              NUMBER(1,0) DEFAULT 1 NOT NULL,
    CREATED_TIME        DATE DEFAULT SYSDATE NOT NULL,
    UPDATED_TIME        DATE,
    CONSTRAINT PK_GAME_LEVEL_CONFIG PRIMARY KEY (LEVEL_CONFIG_ID),
    CONSTRAINT UK_GAME_LEVEL_CONFIG UNIQUE (MODE_ID, LEVEL_NO),
    CONSTRAINT FK_GLC_MODE FOREIGN KEY (MODE_ID) REFERENCES GAME_MODE (MODE_ID),
    CONSTRAINT CK_GLC_LEVEL_NO CHECK (LEVEL_NO BETWEEN 1 AND 50),
    CONSTRAINT CK_GLC_BASE_SCORE CHECK (BASE_SCORE >= 0),
    CONSTRAINT CK_GLC_TIME_LIMIT CHECK (TIME_LIMIT_SECONDS > 0),
    CONSTRAINT CK_GLC_IS_MILESTONE CHECK (IS_MILESTONE IN (0, 1)),
    CONSTRAINT CK_GLC_STATUS CHECK (STATUS IN (0, 1))
);

CREATE INDEX IDX_GLC_MODE_STATUS ON GAME_LEVEL_CONFIG (MODE_ID, STATUS);
CREATE INDEX IDX_GLC_MILESTONE ON GAME_LEVEL_CONFIG (MODE_ID, IS_MILESTONE, LEVEL_NO);

COMMENT ON TABLE GAME_LEVEL_CONFIG IS 'Cau hinh diem va thoi gian cho tung level cua tung mode';
COMMENT ON COLUMN GAME_LEVEL_CONFIG.LEVEL_CONFIG_ID IS 'Khoa chinh cau hinh level';
COMMENT ON COLUMN GAME_LEVEL_CONFIG.MODE_ID IS 'Che do choi, tham chieu GAME_MODE';
COMMENT ON COLUMN GAME_LEVEL_CONFIG.LEVEL_NO IS 'So level tu 1 den 50';
COMMENT ON COLUMN GAME_LEVEL_CONFIG.BASE_SCORE IS 'Diem base cong khi qua level hop le';
COMMENT ON COLUMN GAME_LEVEL_CONFIG.TIME_LIMIT_SECONDS IS 'Thoi gian toi da cua level tinh bang giay';
COMMENT ON COLUMN GAME_LEVEL_CONFIG.IS_MILESTONE IS 'Danh dau level moc co thuong: 1 co thuong, 0 level thuong';
COMMENT ON COLUMN GAME_LEVEL_CONFIG.STATUS IS 'Trang thai cau hinh: 1 hieu luc, 0 khong hieu luc';
COMMENT ON COLUMN GAME_LEVEL_CONFIG.CREATED_TIME IS 'Thoi diem tao ban ghi';
COMMENT ON COLUMN GAME_LEVEL_CONFIG.UPDATED_TIME IS 'Thoi diem cap nhat gan nhat';

--------------------------------------------------------
-- GAME_LEVEL_REWARD_OPTION
--------------------------------------------------------

CREATE TABLE GAME_LEVEL_REWARD_OPTION
(
    REWARD_OPTION_ID    NUMBER(12,0) NOT NULL,
    MODE_ID             NUMBER(10,0) NOT NULL,
    LEVEL_NO            NUMBER(3,0) NOT NULL,
    REWARD_TYPE         VARCHAR2(30 BYTE) NOT NULL,
    REWARD_VALUE        NUMBER(12,2) NOT NULL,
    REWARD_UNIT         VARCHAR2(20 BYTE) NOT NULL,
    WEIGHT              NUMBER(10,0) DEFAULT 1 NOT NULL,
    IS_DEFAULT          NUMBER(1,0) DEFAULT 0 NOT NULL,
    REWARD_DESC         NVARCHAR2(500),
    STATUS              NUMBER(1,0) DEFAULT 1 NOT NULL,
    CREATED_TIME        DATE DEFAULT SYSDATE NOT NULL,
    UPDATED_TIME        DATE,
    CONSTRAINT PK_GAME_LEVEL_REWARD_OPTION PRIMARY KEY (REWARD_OPTION_ID),
    CONSTRAINT UK_GLRO_OPTION UNIQUE (MODE_ID, LEVEL_NO, REWARD_TYPE, REWARD_VALUE, REWARD_UNIT),
    CONSTRAINT FK_GLRO_MODE FOREIGN KEY (MODE_ID) REFERENCES GAME_MODE (MODE_ID),
    CONSTRAINT CK_GLRO_LEVEL_NO CHECK (LEVEL_NO BETWEEN 1 AND 50),
    CONSTRAINT CK_GLRO_REWARD_TYPE CHECK (REWARD_TYPE IN ('DATA', 'LOYALTY', 'SCORE', 'MONEY')),
    CONSTRAINT CK_GLRO_REWARD_VALUE CHECK (REWARD_VALUE > 0),
    CONSTRAINT CK_GLRO_WEIGHT CHECK (WEIGHT > 0),
    CONSTRAINT CK_GLRO_IS_DEFAULT CHECK (IS_DEFAULT IN (0, 1)),
    CONSTRAINT CK_GLRO_STATUS CHECK (STATUS IN (0, 1))
);

CREATE INDEX IDX_GLRO_MODE_LEVEL ON GAME_LEVEL_REWARD_OPTION (MODE_ID, LEVEL_NO, STATUS);
CREATE INDEX IDX_GLRO_TYPE ON GAME_LEVEL_REWARD_OPTION (REWARD_TYPE, STATUS);

COMMENT ON TABLE GAME_LEVEL_REWARD_OPTION IS 'Danh sach option thuong cua cac level moc';
COMMENT ON COLUMN GAME_LEVEL_REWARD_OPTION.REWARD_OPTION_ID IS 'Khoa chinh option thuong';
COMMENT ON COLUMN GAME_LEVEL_REWARD_OPTION.MODE_ID IS 'Che do choi, tham chieu GAME_MODE';
COMMENT ON COLUMN GAME_LEVEL_REWARD_OPTION.LEVEL_NO IS 'Level moc ap dung option thuong';
COMMENT ON COLUMN GAME_LEVEL_REWARD_OPTION.REWARD_TYPE IS 'Loai thuong: DATA, LOYALTY, SCORE, MONEY';
COMMENT ON COLUMN GAME_LEVEL_REWARD_OPTION.REWARD_VALUE IS 'Gia tri thuong';
COMMENT ON COLUMN GAME_LEVEL_REWARD_OPTION.REWARD_UNIT IS 'Don vi thuong: MB, POINT, MMK hoac don vi khac';
COMMENT ON COLUMN GAME_LEVEL_REWARD_OPTION.WEIGHT IS 'Trong so random option thuong, so cang lon ti le cang cao';
COMMENT ON COLUMN GAME_LEVEL_REWARD_OPTION.IS_DEFAULT IS 'Danh dau option mac dinh neu khong random duoc';
COMMENT ON COLUMN GAME_LEVEL_REWARD_OPTION.REWARD_DESC IS 'Mo ta option thuong hien thi/audit';
COMMENT ON COLUMN GAME_LEVEL_REWARD_OPTION.STATUS IS 'Trang thai option: 1 hieu luc, 0 khong hieu luc';
COMMENT ON COLUMN GAME_LEVEL_REWARD_OPTION.CREATED_TIME IS 'Thoi diem tao ban ghi';
COMMENT ON COLUMN GAME_LEVEL_REWARD_OPTION.UPDATED_TIME IS 'Thoi diem cap nhat gan nhat';

--------------------------------------------------------
-- GAME_PLAY_SESSION
--------------------------------------------------------

CREATE TABLE GAME_PLAY_SESSION
(
    SESSION_ID          NUMBER(18,0) NOT NULL,
    ROOT_TRANS_ID       VARCHAR2(100 BYTE) NOT NULL,
    MSISDN              VARCHAR2(20 BYTE) NOT NULL,
    USER_ID             NUMBER(18,0),
    REGISTER_ID         NUMBER(18,0),
    MODE_ID             NUMBER(10,0) NOT NULL,
    START_LEVEL         NUMBER(3,0) DEFAULT 1 NOT NULL,
    CURRENT_LEVEL       NUMBER(3,0) DEFAULT 1 NOT NULL,
    MAX_LEVEL_REACHED   NUMBER(3,0) DEFAULT 0 NOT NULL,
    TOTAL_SCORE         NUMBER(12,0) DEFAULT 0 NOT NULL,
    TOTAL_REWARD_SCORE  NUMBER(12,0) DEFAULT 0 NOT NULL,
    TOTAL_DATA_MB       NUMBER(12,2) DEFAULT 0 NOT NULL,
    TOTAL_LOYALTY       NUMBER(12,2) DEFAULT 0 NOT NULL,
    TURN_DEDUCTED       NUMBER(1,0) DEFAULT 0 NOT NULL,
    TURN_SOURCE         VARCHAR2(30 BYTE),
    TURN_REF_ID         VARCHAR2(100 BYTE),
    STATUS              NUMBER(2,0) DEFAULT 1 NOT NULL,
    START_TIME          DATE DEFAULT SYSDATE NOT NULL,
    END_TIME            DATE,
    LAST_ACTION_TIME    DATE DEFAULT SYSDATE NOT NULL,
    CHANNEL             VARCHAR2(20 BYTE),
    CLIENT_IP           VARCHAR2(100 BYTE),
    USER_AGENT          VARCHAR2(500 BYTE),
    FAIL_REASON         VARCHAR2(200 BYTE),
    CREATED_TIME        DATE DEFAULT SYSDATE NOT NULL,
    UPDATED_TIME        DATE,
    CONSTRAINT PK_GAME_PLAY_SESSION PRIMARY KEY (SESSION_ID),
    CONSTRAINT UK_GPS_ROOT_TRANS UNIQUE (ROOT_TRANS_ID),
    CONSTRAINT FK_GPS_MODE FOREIGN KEY (MODE_ID) REFERENCES GAME_MODE (MODE_ID),
    CONSTRAINT CK_GPS_LEVEL_RANGE CHECK (START_LEVEL BETWEEN 1 AND 50 AND CURRENT_LEVEL BETWEEN 1 AND 50 AND MAX_LEVEL_REACHED BETWEEN 0 AND 50),
    CONSTRAINT CK_GPS_TOTAL_SCORE CHECK (TOTAL_SCORE >= 0 AND TOTAL_REWARD_SCORE >= 0 AND TOTAL_DATA_MB >= 0 AND TOTAL_LOYALTY >= 0),
    CONSTRAINT CK_GPS_TURN_DEDUCTED CHECK (TURN_DEDUCTED IN (0, 1)),
    CONSTRAINT CK_GPS_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4, 5, 6)),
    CONSTRAINT CK_GPS_CHANNEL CHECK (CHANNEL IS NULL OR CHANNEL IN ('WEB', 'APP', 'USSD', 'SMS', 'SYSTEM')),
    CONSTRAINT CK_GPS_TURN_SOURCE CHECK (TURN_SOURCE IS NULL OR TURN_SOURCE IN ('SUB', 'BUY', 'FREE', 'GIFT', 'ADMIN'))
);

CREATE INDEX IDX_GPS_MSISDN_TIME ON GAME_PLAY_SESSION (MSISDN, START_TIME);
CREATE INDEX IDX_GPS_STATUS_TIME ON GAME_PLAY_SESSION (STATUS, START_TIME);
CREATE INDEX IDX_GPS_MODE_TIME ON GAME_PLAY_SESSION (MODE_ID, START_TIME);
CREATE INDEX IDX_GPS_USER_ID ON GAME_PLAY_SESSION (USER_ID);
CREATE INDEX IDX_GPS_REGISTER_ID ON GAME_PLAY_SESSION (REGISTER_ID);

COMMENT ON TABLE GAME_PLAY_SESSION IS 'Mot lan bam Play va tieu hao turn cua user';
COMMENT ON COLUMN GAME_PLAY_SESSION.SESSION_ID IS 'Khoa chinh session choi';
COMMENT ON COLUMN GAME_PLAY_SESSION.ROOT_TRANS_ID IS 'Ma giao dich goc cua mot lan choi, dung chung cho cac level trong session';
COMMENT ON COLUMN GAME_PLAY_SESSION.MSISDN IS 'So thue bao choi game';
COMMENT ON COLUMN GAME_PLAY_SESSION.USER_ID IS 'ID user tu bang ACCOUNT_USER neu co';
COMMENT ON COLUMN GAME_PLAY_SESSION.REGISTER_ID IS 'ID dang ky goi tu bang REG_INFO neu co';
COMMENT ON COLUMN GAME_PLAY_SESSION.MODE_ID IS 'Che do choi cua session';
COMMENT ON COLUMN GAME_PLAY_SESSION.START_LEVEL IS 'Level bat dau cua session';
COMMENT ON COLUMN GAME_PLAY_SESSION.CURRENT_LEVEL IS 'Level hien tai dang choi';
COMMENT ON COLUMN GAME_PLAY_SESSION.MAX_LEVEL_REACHED IS 'Level cao nhat da qua trong session';
COMMENT ON COLUMN GAME_PLAY_SESSION.TOTAL_SCORE IS 'Tong diem thuc te cua session gom base score va score reward';
COMMENT ON COLUMN GAME_PLAY_SESSION.TOTAL_REWARD_SCORE IS 'Tong diem nhan tu reward type SCORE trong session';
COMMENT ON COLUMN GAME_PLAY_SESSION.TOTAL_DATA_MB IS 'Tong dung luong data MB da trung trong session';
COMMENT ON COLUMN GAME_PLAY_SESSION.TOTAL_LOYALTY IS 'Tong loyalty point da trung trong session';
COMMENT ON COLUMN GAME_PLAY_SESSION.TURN_DEDUCTED IS 'Da tru turn hay chua: 1 da tru, 0 chua tru';
COMMENT ON COLUMN GAME_PLAY_SESSION.TURN_SOURCE IS 'Nguon turn: SUB, BUY, FREE, GIFT, ADMIN';
COMMENT ON COLUMN GAME_PLAY_SESSION.TURN_REF_ID IS 'Ma tham chieu giao dich/ban ghi tru turn';
COMMENT ON COLUMN GAME_PLAY_SESSION.STATUS IS 'Trang thai session: 0 INIT, 1 PLAYING, 2 COMPLETED, 3 FAILED, 4 TIMEOUT, 5 CANCELLED, 6 EXPIRED';
COMMENT ON COLUMN GAME_PLAY_SESSION.START_TIME IS 'Thoi diem bat dau session';
COMMENT ON COLUMN GAME_PLAY_SESSION.END_TIME IS 'Thoi diem ket thuc session';
COMMENT ON COLUMN GAME_PLAY_SESSION.LAST_ACTION_TIME IS 'Thoi diem thao tac gan nhat cua session';
COMMENT ON COLUMN GAME_PLAY_SESSION.CHANNEL IS 'Kenh phat sinh session: WEB, APP, USSD, SMS, SYSTEM';
COMMENT ON COLUMN GAME_PLAY_SESSION.CLIENT_IP IS 'IP client khi bat dau choi';
COMMENT ON COLUMN GAME_PLAY_SESSION.USER_AGENT IS 'Thong tin trinh duyet/thiet bi client';
COMMENT ON COLUMN GAME_PLAY_SESSION.FAIL_REASON IS 'Ly do fail/cancel/timeout neu co';
COMMENT ON COLUMN GAME_PLAY_SESSION.CREATED_TIME IS 'Thoi diem tao ban ghi';
COMMENT ON COLUMN GAME_PLAY_SESSION.UPDATED_TIME IS 'Thoi diem cap nhat gan nhat';

--------------------------------------------------------
-- GAME_LEVEL_ATTEMPT
--------------------------------------------------------

CREATE TABLE GAME_LEVEL_ATTEMPT
(
    ATTEMPT_ID              NUMBER(18,0) NOT NULL,
    SESSION_ID              NUMBER(18,0) NOT NULL,
    ROOT_TRANS_ID           VARCHAR2(100 BYTE) NOT NULL,
    CHILD_TRANS_ID          VARCHAR2(100 BYTE) NOT NULL,
    PARENT_CHILD_TRANS_ID   VARCHAR2(100 BYTE),
    MSISDN                  VARCHAR2(20 BYTE) NOT NULL,
    MODE_ID                 NUMBER(10,0) NOT NULL,
    LEVEL_NO                NUMBER(3,0) NOT NULL,
    ATTEMPT_NO              NUMBER(5,0) DEFAULT 1 NOT NULL,
    EXPECTED_SCORE          NUMBER(10,0) DEFAULT 0 NOT NULL,
    CLIENT_SCORE            NUMBER(10,0),
    AWARDED_SCORE           NUMBER(10,0) DEFAULT 0 NOT NULL,
    REWARD_OPTION_ID        NUMBER(12,0),
    REWARD_TYPE             VARCHAR2(30 BYTE),
    REWARD_VALUE            NUMBER(12,2),
    REWARD_UNIT             VARCHAR2(20 BYTE),
    STATUS                  NUMBER(2,0) DEFAULT 1 NOT NULL,
    START_TIME              DATE DEFAULT SYSDATE NOT NULL,
    SUBMIT_TIME             DATE,
    SERVER_DURATION_SECONDS NUMBER(10,0),
    CLIENT_DURATION_SECONDS NUMBER(10,0),
    IS_VALID                NUMBER(1,0),
    VALIDATE_CODE           VARCHAR2(50 BYTE),
    VALIDATE_MESSAGE        VARCHAR2(500 BYTE),
    REQUEST_HASH            VARCHAR2(128 BYTE),
    NONCE                   VARCHAR2(100 BYTE),
    RAW_REQUEST             CLOB,
    RAW_RESPONSE            CLOB,
    CREATED_TIME            DATE DEFAULT SYSDATE NOT NULL,
    UPDATED_TIME            DATE,
    CONSTRAINT PK_GAME_LEVEL_ATTEMPT PRIMARY KEY (ATTEMPT_ID),
    CONSTRAINT UK_GLA_CHILD_TRANS UNIQUE (CHILD_TRANS_ID),
    CONSTRAINT UK_GLA_SESSION_LEVEL_ATTEMPT UNIQUE (SESSION_ID, LEVEL_NO, ATTEMPT_NO),
    CONSTRAINT FK_GLA_SESSION FOREIGN KEY (SESSION_ID) REFERENCES GAME_PLAY_SESSION (SESSION_ID),
    CONSTRAINT FK_GLA_MODE FOREIGN KEY (MODE_ID) REFERENCES GAME_MODE (MODE_ID),
    CONSTRAINT FK_GLA_REWARD_OPTION FOREIGN KEY (REWARD_OPTION_ID) REFERENCES GAME_LEVEL_REWARD_OPTION (REWARD_OPTION_ID),
    CONSTRAINT CK_GLA_LEVEL_NO CHECK (LEVEL_NO BETWEEN 1 AND 50),
    CONSTRAINT CK_GLA_ATTEMPT_NO CHECK (ATTEMPT_NO > 0),
    CONSTRAINT CK_GLA_SCORE CHECK (EXPECTED_SCORE >= 0 AND AWARDED_SCORE >= 0),
    CONSTRAINT CK_GLA_REWARD_TYPE CHECK (REWARD_TYPE IS NULL OR REWARD_TYPE IN ('DATA', 'LOYALTY', 'SCORE', 'MONEY')),
    CONSTRAINT CK_GLA_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4, 5, 6)),
    CONSTRAINT CK_GLA_IS_VALID CHECK (IS_VALID IS NULL OR IS_VALID IN (0, 1))
);

CREATE INDEX IDX_GLA_ROOT_TRANS ON GAME_LEVEL_ATTEMPT (ROOT_TRANS_ID);
CREATE INDEX IDX_GLA_SESSION_STATUS ON GAME_LEVEL_ATTEMPT (SESSION_ID, STATUS);
CREATE INDEX IDX_GLA_MSISDN_TIME ON GAME_LEVEL_ATTEMPT (MSISDN, START_TIME);
CREATE INDEX IDX_GLA_MODE_LEVEL ON GAME_LEVEL_ATTEMPT (MODE_ID, LEVEL_NO);
CREATE INDEX IDX_GLA_PARENT_CHILD ON GAME_LEVEL_ATTEMPT (PARENT_CHILD_TRANS_ID);
CREATE INDEX IDX_GLA_REQUEST_HASH ON GAME_LEVEL_ATTEMPT (REQUEST_HASH);

COMMENT ON TABLE GAME_LEVEL_ATTEMPT IS 'Lich su choi tung level trong mot session';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.ATTEMPT_ID IS 'Khoa chinh lan choi level';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.SESSION_ID IS 'Session cha cua level attempt';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.ROOT_TRANS_ID IS 'Ma giao dich goc cua session';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.CHILD_TRANS_ID IS 'Ma giao dich con cua rieng level attempt, dung de submit mot lan';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.PARENT_CHILD_TRANS_ID IS 'Child trans cha neu attempt duoc tao do retry/rescue';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.MSISDN IS 'So thue bao choi level';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.MODE_ID IS 'Che do choi cua level';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.LEVEL_NO IS 'So level dang choi';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.ATTEMPT_NO IS 'Lan attempt cua level trong session, dung cho retry/rescue';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.EXPECTED_SCORE IS 'Diem server du kien cong theo cau hinh level';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.CLIENT_SCORE IS 'Diem client submit, chi dung de doi chieu va audit';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.AWARDED_SCORE IS 'Diem base server thuc su cong cho level';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.REWARD_OPTION_ID IS 'Option thuong duoc chon neu level la milestone';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.REWARD_TYPE IS 'Loai thuong thuc te: DATA, LOYALTY, SCORE, MONEY';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.REWARD_VALUE IS 'Gia tri thuong thuc te';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.REWARD_UNIT IS 'Don vi thuong thuc te';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.STATUS IS 'Trang thai attempt: 0 CREATED, 1 PLAYING, 2 COMPLETED, 3 FAILED, 4 TIMEOUT, 5 INVALID, 6 REPLACED';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.START_TIME IS 'Thoi diem server tao attempt/bat dau level';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.SUBMIT_TIME IS 'Thoi diem server nhan submit level';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.SERVER_DURATION_SECONDS IS 'Thoi gian choi tinh theo server';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.CLIENT_DURATION_SECONDS IS 'Thoi gian choi client gui len';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.IS_VALID IS 'Ket qua validate submit: 1 hop le, 0 khong hop le';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.VALIDATE_CODE IS 'Ma ket qua validate';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.VALIDATE_MESSAGE IS 'Mo ta ket qua validate';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.REQUEST_HASH IS 'Hash request submit de phat hien replay/trung lap';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.NONCE IS 'Nonce phat cho client de chong replay';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.RAW_REQUEST IS 'Noi dung request goc neu can audit';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.RAW_RESPONSE IS 'Noi dung response goc neu can audit';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.CREATED_TIME IS 'Thoi diem tao ban ghi';
COMMENT ON COLUMN GAME_LEVEL_ATTEMPT.UPDATED_TIME IS 'Thoi diem cap nhat gan nhat';

--------------------------------------------------------
-- GAME_RESCUE_LOG
--------------------------------------------------------

CREATE TABLE GAME_RESCUE_LOG
(
    RESCUE_ID          NUMBER(18,0) NOT NULL,
    SESSION_ID         NUMBER(18,0) NOT NULL,
    ROOT_TRANS_ID      VARCHAR2(100 BYTE) NOT NULL,
    CHILD_TRANS_ID     VARCHAR2(100 BYTE) NOT NULL,
    MSISDN             VARCHAR2(20 BYTE) NOT NULL,
    MODE_ID            NUMBER(10,0) NOT NULL,
    LEVEL_NO           NUMBER(3,0) NOT NULL,
    FEE                NUMBER(12,2) DEFAULT 79 NOT NULL,
    CHARGE_TRANS_ID    VARCHAR2(100 BYTE),
    CHARGE_STATUS      NUMBER(2,0),
    STATUS             NUMBER(2,0) DEFAULT 0 NOT NULL,
    REQUEST_TIME       DATE DEFAULT SYSDATE NOT NULL,
    RESPONSE_TIME      DATE,
    RESPONSE_CODE      VARCHAR2(50 BYTE),
    RESPONSE_MESSAGE   VARCHAR2(500 BYTE),
    CREATED_TIME       DATE DEFAULT SYSDATE NOT NULL,
    UPDATED_TIME       DATE,
    CONSTRAINT PK_GAME_RESCUE_LOG PRIMARY KEY (RESCUE_ID),
    CONSTRAINT UK_GRL_ONE_SESSION UNIQUE (SESSION_ID),
    CONSTRAINT FK_GRL_SESSION FOREIGN KEY (SESSION_ID) REFERENCES GAME_PLAY_SESSION (SESSION_ID),
    CONSTRAINT FK_GRL_MODE FOREIGN KEY (MODE_ID) REFERENCES GAME_MODE (MODE_ID),
    CONSTRAINT CK_GRL_LEVEL_NO CHECK (LEVEL_NO BETWEEN 1 AND 50),
    CONSTRAINT CK_GRL_FEE CHECK (FEE >= 0),
    CONSTRAINT CK_GRL_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4))
);

CREATE INDEX IDX_GRL_MSISDN_TIME ON GAME_RESCUE_LOG (MSISDN, REQUEST_TIME);
CREATE INDEX IDX_GRL_ROOT_TRANS ON GAME_RESCUE_LOG (ROOT_TRANS_ID);
CREATE INDEX IDX_GRL_CHARGE_TRANS ON GAME_RESCUE_LOG (CHARGE_TRANS_ID);

COMMENT ON TABLE GAME_RESCUE_LOG IS 'Lich su mua/su dung rescue, toi da 1 lan moi session';
COMMENT ON COLUMN GAME_RESCUE_LOG.RESCUE_ID IS 'Khoa chinh rescue log';
COMMENT ON COLUMN GAME_RESCUE_LOG.SESSION_ID IS 'Session su dung rescue';
COMMENT ON COLUMN GAME_RESCUE_LOG.ROOT_TRANS_ID IS 'Ma giao dich goc cua session';
COMMENT ON COLUMN GAME_RESCUE_LOG.CHILD_TRANS_ID IS 'Child trans cua level dang rescue';
COMMENT ON COLUMN GAME_RESCUE_LOG.MSISDN IS 'So thue bao mua rescue';
COMMENT ON COLUMN GAME_RESCUE_LOG.MODE_ID IS 'Che do choi khi mua rescue';
COMMENT ON COLUMN GAME_RESCUE_LOG.LEVEL_NO IS 'Level su dung rescue';
COMMENT ON COLUMN GAME_RESCUE_LOG.FEE IS 'Phi rescue';
COMMENT ON COLUMN GAME_RESCUE_LOG.CHARGE_TRANS_ID IS 'Ma giao dich charge phi rescue';
COMMENT ON COLUMN GAME_RESCUE_LOG.CHARGE_STATUS IS 'Trang thai charge tu he thong thanh toan';
COMMENT ON COLUMN GAME_RESCUE_LOG.STATUS IS 'Trang thai rescue: 0 NEW, 1 CHARGING, 2 SUCCESS, 3 FAILED, 4 CANCELLED';
COMMENT ON COLUMN GAME_RESCUE_LOG.REQUEST_TIME IS 'Thoi diem gui yeu cau rescue';
COMMENT ON COLUMN GAME_RESCUE_LOG.RESPONSE_TIME IS 'Thoi diem nhan ket qua rescue/charge';
COMMENT ON COLUMN GAME_RESCUE_LOG.RESPONSE_CODE IS 'Ma ket qua tu he thong charge';
COMMENT ON COLUMN GAME_RESCUE_LOG.RESPONSE_MESSAGE IS 'Mo ta ket qua tu he thong charge';
COMMENT ON COLUMN GAME_RESCUE_LOG.CREATED_TIME IS 'Thoi diem tao ban ghi';
COMMENT ON COLUMN GAME_RESCUE_LOG.UPDATED_TIME IS 'Thoi diem cap nhat gan nhat';

--------------------------------------------------------
-- GAME_SCORE_LEDGER
--------------------------------------------------------

CREATE TABLE GAME_SCORE_LEDGER
(
    LEDGER_ID          NUMBER(18,0) NOT NULL,
    MSISDN             VARCHAR2(20 BYTE) NOT NULL,
    USER_ID            NUMBER(18,0),
    SESSION_ID         NUMBER(18,0) NOT NULL,
    ATTEMPT_ID         NUMBER(18,0),
    ROOT_TRANS_ID      VARCHAR2(100 BYTE) NOT NULL,
    CHILD_TRANS_ID     VARCHAR2(100 BYTE),
    SCORE_TYPE         VARCHAR2(30 BYTE) NOT NULL,
    SCORE_DELTA        NUMBER(12,0) NOT NULL,
    BALANCE_AFTER      NUMBER(12,0),
    DESCRIPTION        NVARCHAR2(500),
    CREATED_TIME       DATE DEFAULT SYSDATE NOT NULL,
    CREATED_DATE_KEY   NUMBER(8,0) NOT NULL,
    CREATED_MONTH_KEY  NUMBER(6,0) NOT NULL,
    CONSTRAINT PK_GAME_SCORE_LEDGER PRIMARY KEY (LEDGER_ID),
    CONSTRAINT UK_GSL_ATTEMPT_TYPE UNIQUE (ATTEMPT_ID, SCORE_TYPE),
    CONSTRAINT FK_GSL_SESSION FOREIGN KEY (SESSION_ID) REFERENCES GAME_PLAY_SESSION (SESSION_ID),
    CONSTRAINT FK_GSL_ATTEMPT FOREIGN KEY (ATTEMPT_ID) REFERENCES GAME_LEVEL_ATTEMPT (ATTEMPT_ID),
    CONSTRAINT CK_GSL_SCORE_TYPE CHECK (SCORE_TYPE IN ('LEVEL', 'REWARD', 'ADJUST', 'CANCEL')),
    CONSTRAINT CK_GSL_DATE_KEY CHECK (CREATED_DATE_KEY BETWEEN 20000101 AND 29991231),
    CONSTRAINT CK_GSL_MONTH_KEY CHECK (CREATED_MONTH_KEY BETWEEN 200001 AND 299912)
);

CREATE INDEX IDX_GSL_MSISDN_MONTH ON GAME_SCORE_LEDGER (MSISDN, CREATED_MONTH_KEY);
CREATE INDEX IDX_GSL_MONTH_TIME ON GAME_SCORE_LEDGER (CREATED_MONTH_KEY, CREATED_TIME);
CREATE INDEX IDX_GSL_ROOT_TRANS ON GAME_SCORE_LEDGER (ROOT_TRANS_ID);
CREATE INDEX IDX_GSL_SESSION ON GAME_SCORE_LEDGER (SESSION_ID);
CREATE INDEX IDX_GSL_CHILD_TRANS ON GAME_SCORE_LEDGER (CHILD_TRANS_ID);

COMMENT ON TABLE GAME_SCORE_LEDGER IS 'So cai diem game, dung de audit va tinh ranking';
COMMENT ON COLUMN GAME_SCORE_LEDGER.LEDGER_ID IS 'Khoa chinh dong ledger diem';
COMMENT ON COLUMN GAME_SCORE_LEDGER.MSISDN IS 'So thue bao duoc cong/tru diem';
COMMENT ON COLUMN GAME_SCORE_LEDGER.USER_ID IS 'ID user tu ACCOUNT_USER neu co';
COMMENT ON COLUMN GAME_SCORE_LEDGER.SESSION_ID IS 'Session phat sinh diem';
COMMENT ON COLUMN GAME_SCORE_LEDGER.ATTEMPT_ID IS 'Level attempt phat sinh diem neu co';
COMMENT ON COLUMN GAME_SCORE_LEDGER.ROOT_TRANS_ID IS 'Ma giao dich goc cua session';
COMMENT ON COLUMN GAME_SCORE_LEDGER.CHILD_TRANS_ID IS 'Ma giao dich con cua level phat sinh diem';
COMMENT ON COLUMN GAME_SCORE_LEDGER.SCORE_TYPE IS 'Loai diem: LEVEL, REWARD, ADJUST, CANCEL';
COMMENT ON COLUMN GAME_SCORE_LEDGER.SCORE_DELTA IS 'So diem thay doi, duong la cong diem, am la tru diem';
COMMENT ON COLUMN GAME_SCORE_LEDGER.BALANCE_AFTER IS 'Tong diem sau khi xu ly neu he thong co tinh balance';
COMMENT ON COLUMN GAME_SCORE_LEDGER.DESCRIPTION IS 'Mo ta ly do cong/tru diem';
COMMENT ON COLUMN GAME_SCORE_LEDGER.CREATED_TIME IS 'Thoi diem phat sinh diem';
COMMENT ON COLUMN GAME_SCORE_LEDGER.CREATED_DATE_KEY IS 'Ngay phat sinh dang YYYYMMDD de query nhanh';
COMMENT ON COLUMN GAME_SCORE_LEDGER.CREATED_MONTH_KEY IS 'Thang phat sinh dang YYYYMM de tinh ranking thang';

--------------------------------------------------------
-- GAME_REWARD_LIMIT_CONFIG
--------------------------------------------------------

CREATE TABLE GAME_REWARD_LIMIT_CONFIG
(
    LIMIT_CONFIG_ID     NUMBER(12,0) NOT NULL,
    LIMIT_CODE          VARCHAR2(50 BYTE) NOT NULL,
    LIMIT_NAME          NVARCHAR2(200) NOT NULL,
    PERIOD_TYPE         VARCHAR2(20 BYTE) DEFAULT 'DAILY' NOT NULL,
    REWARD_TYPE         VARCHAR2(30 BYTE) NOT NULL,
    REWARD_UNIT         VARCHAR2(20 BYTE) NOT NULL,
    MODE_ID             NUMBER(10,0),
    LEVEL_NO            NUMBER(3,0),
    MAX_TOTAL_VALUE     NUMBER(18,2),
    MAX_TOTAL_COUNT     NUMBER(12,0),
    PRIORITY            NUMBER(5,0) DEFAULT 0 NOT NULL,
    EFFECTIVE_FROM      DATE,
    EFFECTIVE_TO        DATE,
    EXCEED_ACTION       VARCHAR2(30 BYTE) DEFAULT 'BLOCK' NOT NULL,
    FALLBACK_REWARD_TYPE VARCHAR2(30 BYTE),
    FALLBACK_REWARD_VALUE NUMBER(12,2),
    FALLBACK_REWARD_UNIT VARCHAR2(20 BYTE),
    STATUS              NUMBER(1,0) DEFAULT 1 NOT NULL,
    CREATED_TIME        DATE DEFAULT SYSDATE NOT NULL,
    UPDATED_TIME        DATE,
    CONSTRAINT PK_GAME_REWARD_LIMIT_CONFIG PRIMARY KEY (LIMIT_CONFIG_ID),
    CONSTRAINT UK_GRLC_CODE UNIQUE (LIMIT_CODE),
    CONSTRAINT FK_GRLC_MODE FOREIGN KEY (MODE_ID) REFERENCES GAME_MODE (MODE_ID),
    CONSTRAINT CK_GRLC_PERIOD_TYPE CHECK (PERIOD_TYPE IN ('DAILY', 'MONTHLY')),
    CONSTRAINT CK_GRLC_REWARD_TYPE CHECK (REWARD_TYPE IN ('DATA', 'LOYALTY', 'SCORE', 'MONEY')),
    CONSTRAINT CK_GRLC_FALLBACK_TYPE CHECK (FALLBACK_REWARD_TYPE IS NULL OR FALLBACK_REWARD_TYPE IN ('DATA', 'LOYALTY', 'SCORE', 'MONEY')),
    CONSTRAINT CK_GRLC_LEVEL_NO CHECK (LEVEL_NO IS NULL OR LEVEL_NO BETWEEN 1 AND 50),
    CONSTRAINT CK_GRLC_MAX_VALUE CHECK (MAX_TOTAL_VALUE IS NULL OR MAX_TOTAL_VALUE >= 0),
    CONSTRAINT CK_GRLC_MAX_COUNT CHECK (MAX_TOTAL_COUNT IS NULL OR MAX_TOTAL_COUNT >= 0),
    CONSTRAINT CK_GRLC_LIMIT_NOT_NULL CHECK (MAX_TOTAL_VALUE IS NOT NULL OR MAX_TOTAL_COUNT IS NOT NULL),
    CONSTRAINT CK_GRLC_EXCEED_ACTION CHECK (EXCEED_ACTION IN ('BLOCK', 'FALLBACK', 'QUEUE')),
    CONSTRAINT CK_GRLC_FALLBACK_VALUE CHECK (FALLBACK_REWARD_VALUE IS NULL OR FALLBACK_REWARD_VALUE > 0),
    CONSTRAINT CK_GRLC_STATUS CHECK (STATUS IN (0, 1))
);

CREATE INDEX IDX_GRLC_LOOKUP ON GAME_REWARD_LIMIT_CONFIG (STATUS, PERIOD_TYPE, REWARD_TYPE, REWARD_UNIT, MODE_ID, LEVEL_NO);
CREATE INDEX IDX_GRLC_EFFECTIVE ON GAME_REWARD_LIMIT_CONFIG (STATUS, EFFECTIVE_FROM, EFFECTIVE_TO);
CREATE INDEX IDX_GRLC_PRIORITY ON GAME_REWARD_LIMIT_CONFIG (STATUS, PRIORITY);

COMMENT ON TABLE GAME_REWARD_LIMIT_CONFIG IS 'Cau hinh han muc tra thuong de kiem soat chi phi theo ngay/thang';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.LIMIT_CONFIG_ID IS 'Khoa chinh cau hinh han muc reward';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.LIMIT_CODE IS 'Ma cau hinh han muc, dung de nhan dien duy nhat';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.LIMIT_NAME IS 'Ten mo ta cau hinh han muc';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.PERIOD_TYPE IS 'Ky ap dung han muc: DAILY theo ngay, MONTHLY theo thang';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.REWARD_TYPE IS 'Loai reward bi gioi han: DATA, LOYALTY, SCORE, MONEY';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.REWARD_UNIT IS 'Don vi reward bi gioi han: MB, POINT, MMK';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.MODE_ID IS 'Mode ap dung han muc, null la ap dung tat ca mode';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.LEVEL_NO IS 'Level ap dung han muc, null la ap dung tat ca level';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.MAX_TOTAL_VALUE IS 'Tong gia tri reward toi da duoc tra trong ky';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.MAX_TOTAL_COUNT IS 'Tong so lan reward toi da duoc tra trong ky';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.PRIORITY IS 'Do uu tien khi co nhieu cau hinh match, so lon uu tien hon';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.EFFECTIVE_FROM IS 'Ngay bat dau hieu luc cau hinh';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.EFFECTIVE_TO IS 'Ngay ket thuc hieu luc cau hinh';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.EXCEED_ACTION IS 'Hanh dong khi vuot han muc: BLOCK chan, FALLBACK doi thuong, QUEUE dua vao cho xu ly sau';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.FALLBACK_REWARD_TYPE IS 'Loai reward thay the neu EXCEED_ACTION la FALLBACK';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.FALLBACK_REWARD_VALUE IS 'Gia tri reward thay the neu EXCEED_ACTION la FALLBACK';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.FALLBACK_REWARD_UNIT IS 'Don vi reward thay the neu EXCEED_ACTION la FALLBACK';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.STATUS IS 'Trang thai cau hinh: 1 hieu luc, 0 khong hieu luc';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.CREATED_TIME IS 'Thoi diem tao ban ghi';
COMMENT ON COLUMN GAME_REWARD_LIMIT_CONFIG.UPDATED_TIME IS 'Thoi diem cap nhat gan nhat';

--------------------------------------------------------
-- GAME_REWARD_LIMIT_USAGE
--------------------------------------------------------

CREATE TABLE GAME_REWARD_LIMIT_USAGE
(
    LIMIT_USAGE_ID      NUMBER(18,0) NOT NULL,
    LIMIT_CONFIG_ID     NUMBER(12,0) NOT NULL,
    PERIOD_TYPE         VARCHAR2(20 BYTE) NOT NULL,
    PERIOD_KEY          NUMBER(8,0) NOT NULL,
    REWARD_TYPE         VARCHAR2(30 BYTE) NOT NULL,
    REWARD_UNIT         VARCHAR2(20 BYTE) NOT NULL,
    USED_TOTAL_VALUE    NUMBER(18,2) DEFAULT 0 NOT NULL,
    USED_TOTAL_COUNT    NUMBER(12,0) DEFAULT 0 NOT NULL,
    LAST_REWARD_LOG_ID  NUMBER(18,0),
    LAST_UPDATE_TIME    DATE DEFAULT SYSDATE NOT NULL,
    CREATED_TIME        DATE DEFAULT SYSDATE NOT NULL,
    UPDATED_TIME        DATE,
    CONSTRAINT PK_GAME_REWARD_LIMIT_USAGE PRIMARY KEY (LIMIT_USAGE_ID),
    CONSTRAINT UK_GRLU_LIMIT_PERIOD UNIQUE (LIMIT_CONFIG_ID, PERIOD_KEY),
    CONSTRAINT FK_GRLU_CONFIG FOREIGN KEY (LIMIT_CONFIG_ID) REFERENCES GAME_REWARD_LIMIT_CONFIG (LIMIT_CONFIG_ID),
    CONSTRAINT CK_GRLU_PERIOD_TYPE CHECK (PERIOD_TYPE IN ('DAILY', 'MONTHLY')),
    CONSTRAINT CK_GRLU_PERIOD_KEY CHECK (PERIOD_KEY BETWEEN 200001 AND 29991231),
    CONSTRAINT CK_GRLU_REWARD_TYPE CHECK (REWARD_TYPE IN ('DATA', 'LOYALTY', 'SCORE', 'MONEY')),
    CONSTRAINT CK_GRLU_USED_VALUE CHECK (USED_TOTAL_VALUE >= 0),
    CONSTRAINT CK_GRLU_USED_COUNT CHECK (USED_TOTAL_COUNT >= 0)
);

CREATE INDEX IDX_GRLU_PERIOD_TYPE_KEY ON GAME_REWARD_LIMIT_USAGE (PERIOD_TYPE, PERIOD_KEY);
CREATE INDEX IDX_GRLU_REWARD_TYPE ON GAME_REWARD_LIMIT_USAGE (REWARD_TYPE, REWARD_UNIT, PERIOD_KEY);

COMMENT ON TABLE GAME_REWARD_LIMIT_USAGE IS 'So lieu da su dung cua han muc reward theo tung ky';
COMMENT ON COLUMN GAME_REWARD_LIMIT_USAGE.LIMIT_USAGE_ID IS 'Khoa chinh usage han muc reward';
COMMENT ON COLUMN GAME_REWARD_LIMIT_USAGE.LIMIT_CONFIG_ID IS 'Cau hinh han muc dang duoc theo doi';
COMMENT ON COLUMN GAME_REWARD_LIMIT_USAGE.PERIOD_TYPE IS 'Ky usage: DAILY hoac MONTHLY';
COMMENT ON COLUMN GAME_REWARD_LIMIT_USAGE.PERIOD_KEY IS 'Khoa ky: DAILY dung YYYYMMDD, MONTHLY dung YYYYMM';
COMMENT ON COLUMN GAME_REWARD_LIMIT_USAGE.REWARD_TYPE IS 'Loai reward cua usage: DATA, LOYALTY, SCORE, MONEY';
COMMENT ON COLUMN GAME_REWARD_LIMIT_USAGE.REWARD_UNIT IS 'Don vi reward cua usage';
COMMENT ON COLUMN GAME_REWARD_LIMIT_USAGE.USED_TOTAL_VALUE IS 'Tong gia tri reward da tra trong ky';
COMMENT ON COLUMN GAME_REWARD_LIMIT_USAGE.USED_TOTAL_COUNT IS 'Tong so lan reward da tra trong ky';
COMMENT ON COLUMN GAME_REWARD_LIMIT_USAGE.LAST_REWARD_LOG_ID IS 'Reward log gan nhat lam thay doi usage';
COMMENT ON COLUMN GAME_REWARD_LIMIT_USAGE.LAST_UPDATE_TIME IS 'Thoi diem cap nhat usage gan nhat';
COMMENT ON COLUMN GAME_REWARD_LIMIT_USAGE.CREATED_TIME IS 'Thoi diem tao ban ghi';
COMMENT ON COLUMN GAME_REWARD_LIMIT_USAGE.UPDATED_TIME IS 'Thoi diem cap nhat gan nhat';

--------------------------------------------------------
-- GAME_REWARD_LOG
--------------------------------------------------------

CREATE TABLE GAME_REWARD_LOG
(
    REWARD_LOG_ID      NUMBER(18,0) NOT NULL,
    MSISDN             VARCHAR2(20 BYTE) NOT NULL,
    USER_ID            NUMBER(18,0),
    SESSION_ID         NUMBER(18,0) NOT NULL,
    ATTEMPT_ID         NUMBER(18,0) NOT NULL,
    ROOT_TRANS_ID      VARCHAR2(100 BYTE) NOT NULL,
    CHILD_TRANS_ID     VARCHAR2(100 BYTE) NOT NULL,
    MODE_ID            NUMBER(10,0) NOT NULL,
    LEVEL_NO           NUMBER(3,0) NOT NULL,
    REWARD_OPTION_ID   NUMBER(12,0),
    REWARD_TYPE        VARCHAR2(30 BYTE) NOT NULL,
    REWARD_VALUE       NUMBER(12,2) NOT NULL,
    REWARD_UNIT        VARCHAR2(20 BYTE) NOT NULL,
    LIMIT_CONFIG_ID    NUMBER(12,0),
    STATUS             NUMBER(2,0) DEFAULT 0 NOT NULL,
    EXTERNAL_TRANS_ID  VARCHAR2(100 BYTE),
    REQUEST_TIME       DATE,
    RESPONSE_TIME      DATE,
    RESPONSE_CODE      VARCHAR2(50 BYTE),
    RESPONSE_MESSAGE   VARCHAR2(500 BYTE),
    RETRY_COUNT        NUMBER(5,0) DEFAULT 0 NOT NULL,
    CREATED_TIME       DATE DEFAULT SYSDATE NOT NULL,
    UPDATED_TIME       DATE,
    CONSTRAINT PK_GAME_REWARD_LOG PRIMARY KEY (REWARD_LOG_ID),
    CONSTRAINT UK_GRWL_ATTEMPT UNIQUE (ATTEMPT_ID),
    CONSTRAINT FK_GRWL_SESSION FOREIGN KEY (SESSION_ID) REFERENCES GAME_PLAY_SESSION (SESSION_ID),
    CONSTRAINT FK_GRWL_ATTEMPT FOREIGN KEY (ATTEMPT_ID) REFERENCES GAME_LEVEL_ATTEMPT (ATTEMPT_ID),
    CONSTRAINT FK_GRWL_MODE FOREIGN KEY (MODE_ID) REFERENCES GAME_MODE (MODE_ID),
    CONSTRAINT FK_GRWL_OPTION FOREIGN KEY (REWARD_OPTION_ID) REFERENCES GAME_LEVEL_REWARD_OPTION (REWARD_OPTION_ID),
    CONSTRAINT FK_GRWL_LIMIT_CONFIG FOREIGN KEY (LIMIT_CONFIG_ID) REFERENCES GAME_REWARD_LIMIT_CONFIG (LIMIT_CONFIG_ID),
    CONSTRAINT CK_GRWL_LEVEL_NO CHECK (LEVEL_NO BETWEEN 1 AND 50),
    CONSTRAINT CK_GRWL_REWARD_TYPE CHECK (REWARD_TYPE IN ('DATA', 'LOYALTY', 'SCORE', 'MONEY')),
    CONSTRAINT CK_GRWL_REWARD_VALUE CHECK (REWARD_VALUE > 0),
    CONSTRAINT CK_GRWL_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4)),
    CONSTRAINT CK_GRWL_RETRY_COUNT CHECK (RETRY_COUNT >= 0)
);

CREATE INDEX IDX_GRWL_MSISDN_TIME ON GAME_REWARD_LOG (MSISDN, CREATED_TIME);
CREATE INDEX IDX_GRWL_STATUS ON GAME_REWARD_LOG (STATUS, CREATED_TIME);
CREATE INDEX IDX_GRWL_ROOT_TRANS ON GAME_REWARD_LOG (ROOT_TRANS_ID);
CREATE INDEX IDX_GRWL_EXTERNAL_TRANS ON GAME_REWARD_LOG (EXTERNAL_TRANS_ID);
CREATE INDEX IDX_GRWL_LIMIT_CONFIG ON GAME_REWARD_LOG (LIMIT_CONFIG_ID, CREATED_TIME);

COMMENT ON TABLE GAME_REWARD_LOG IS 'Lich su reward thuc te user nhan tai level moc';
COMMENT ON COLUMN GAME_REWARD_LOG.REWARD_LOG_ID IS 'Khoa chinh reward log';
COMMENT ON COLUMN GAME_REWARD_LOG.MSISDN IS 'So thue bao nhan reward';
COMMENT ON COLUMN GAME_REWARD_LOG.USER_ID IS 'ID user tu ACCOUNT_USER neu co';
COMMENT ON COLUMN GAME_REWARD_LOG.SESSION_ID IS 'Session phat sinh reward';
COMMENT ON COLUMN GAME_REWARD_LOG.ATTEMPT_ID IS 'Level attempt phat sinh reward';
COMMENT ON COLUMN GAME_REWARD_LOG.ROOT_TRANS_ID IS 'Ma giao dich goc cua session';
COMMENT ON COLUMN GAME_REWARD_LOG.CHILD_TRANS_ID IS 'Ma giao dich con cua level';
COMMENT ON COLUMN GAME_REWARD_LOG.MODE_ID IS 'Che do choi phat sinh reward';
COMMENT ON COLUMN GAME_REWARD_LOG.LEVEL_NO IS 'Level moc phat sinh reward';
COMMENT ON COLUMN GAME_REWARD_LOG.REWARD_OPTION_ID IS 'Option reward da duoc chon';
COMMENT ON COLUMN GAME_REWARD_LOG.REWARD_TYPE IS 'Loai reward thuc te: DATA, LOYALTY, SCORE, MONEY';
COMMENT ON COLUMN GAME_REWARD_LOG.REWARD_VALUE IS 'Gia tri reward thuc te';
COMMENT ON COLUMN GAME_REWARD_LOG.REWARD_UNIT IS 'Don vi reward thuc te';
COMMENT ON COLUMN GAME_REWARD_LOG.LIMIT_CONFIG_ID IS 'Han muc reward da duoc ap dung khi tra thuong, null neu khong ap dung han muc';
COMMENT ON COLUMN GAME_REWARD_LOG.STATUS IS 'Trang thai reward: 0 NEW, 1 PROCESSING, 2 SUCCESS, 3 FAILED, 4 CANCELLED';
COMMENT ON COLUMN GAME_REWARD_LOG.EXTERNAL_TRANS_ID IS 'Ma giao dich he thong ngoai khi tra reward';
COMMENT ON COLUMN GAME_REWARD_LOG.REQUEST_TIME IS 'Thoi diem bat dau goi tra reward';
COMMENT ON COLUMN GAME_REWARD_LOG.RESPONSE_TIME IS 'Thoi diem nhan ket qua tra reward';
COMMENT ON COLUMN GAME_REWARD_LOG.RESPONSE_CODE IS 'Ma ket qua tra reward';
COMMENT ON COLUMN GAME_REWARD_LOG.RESPONSE_MESSAGE IS 'Noi dung ket qua tra reward';
COMMENT ON COLUMN GAME_REWARD_LOG.RETRY_COUNT IS 'So lan retry tra reward';
COMMENT ON COLUMN GAME_REWARD_LOG.CREATED_TIME IS 'Thoi diem tao ban ghi';
COMMENT ON COLUMN GAME_REWARD_LOG.UPDATED_TIME IS 'Thoi diem cap nhat gan nhat';

--------------------------------------------------------
-- GAME_MONTHLY_RANKING
--------------------------------------------------------

CREATE TABLE GAME_MONTHLY_RANKING
(
    RANKING_ID          NUMBER(18,0) NOT NULL,
    MONTH_KEY           NUMBER(6,0) NOT NULL,
    MSISDN              VARCHAR2(20 BYTE) NOT NULL,
    USER_ID             NUMBER(18,0),
    TOTAL_SCORE         NUMBER(12,0) DEFAULT 0 NOT NULL,
    TOTAL_PLAY          NUMBER(12,0) DEFAULT 0 NOT NULL,
    MAX_LEVEL_REACHED   NUMBER(3,0) DEFAULT 0 NOT NULL,
    BEST_MODE_ID        NUMBER(10,0),
    RANK_NO             NUMBER(12,0),
    TICKET_COUNT        NUMBER(10,0) DEFAULT 0 NOT NULL,
    IS_ELIGIBLE         NUMBER(1,0) DEFAULT 0 NOT NULL,
    LAST_CALC_TIME      DATE,
    CREATED_TIME        DATE DEFAULT SYSDATE NOT NULL,
    UPDATED_TIME        DATE,
    CONSTRAINT PK_GAME_MONTHLY_RANKING PRIMARY KEY (RANKING_ID),
    CONSTRAINT UK_GMR_MONTH_MSISDN UNIQUE (MONTH_KEY, MSISDN),
    CONSTRAINT FK_GMR_BEST_MODE FOREIGN KEY (BEST_MODE_ID) REFERENCES GAME_MODE (MODE_ID),
    CONSTRAINT CK_GMR_MONTH_KEY CHECK (MONTH_KEY BETWEEN 200001 AND 299912),
    CONSTRAINT CK_GMR_TOTALS CHECK (TOTAL_SCORE >= 0 AND TOTAL_PLAY >= 0 AND MAX_LEVEL_REACHED BETWEEN 0 AND 50 AND TICKET_COUNT >= 0),
    CONSTRAINT CK_GMR_IS_ELIGIBLE CHECK (IS_ELIGIBLE IN (0, 1))
);

CREATE INDEX IDX_GMR_MONTH_RANK ON GAME_MONTHLY_RANKING (MONTH_KEY, RANK_NO);
CREATE INDEX IDX_GMR_MONTH_SCORE ON GAME_MONTHLY_RANKING (MONTH_KEY, TOTAL_SCORE);
CREATE INDEX IDX_GMR_ELIGIBLE ON GAME_MONTHLY_RANKING (MONTH_KEY, IS_ELIGIBLE, TICKET_COUNT);

COMMENT ON TABLE GAME_MONTHLY_RANKING IS 'Snapshot xep hang va dieu kien quay thuong theo thang';
COMMENT ON COLUMN GAME_MONTHLY_RANKING.RANKING_ID IS 'Khoa chinh ranking thang';
COMMENT ON COLUMN GAME_MONTHLY_RANKING.MONTH_KEY IS 'Thang ranking dang YYYYMM';
COMMENT ON COLUMN GAME_MONTHLY_RANKING.MSISDN IS 'So thue bao duoc xep hang';
COMMENT ON COLUMN GAME_MONTHLY_RANKING.USER_ID IS 'ID user tu ACCOUNT_USER neu co';
COMMENT ON COLUMN GAME_MONTHLY_RANKING.TOTAL_SCORE IS 'Tong score trong thang';
COMMENT ON COLUMN GAME_MONTHLY_RANKING.TOTAL_PLAY IS 'Tong so session choi trong thang';
COMMENT ON COLUMN GAME_MONTHLY_RANKING.MAX_LEVEL_REACHED IS 'Level cao nhat dat duoc trong thang';
COMMENT ON COLUMN GAME_MONTHLY_RANKING.BEST_MODE_ID IS 'Mode tot nhat cua user trong thang';
COMMENT ON COLUMN GAME_MONTHLY_RANKING.RANK_NO IS 'Thu hang trong thang';
COMMENT ON COLUMN GAME_MONTHLY_RANKING.TICKET_COUNT IS 'So ticket quay thuong theo tong score';
COMMENT ON COLUMN GAME_MONTHLY_RANKING.IS_ELIGIBLE IS 'Du dieu kien quay thuong thang hay khong';
COMMENT ON COLUMN GAME_MONTHLY_RANKING.LAST_CALC_TIME IS 'Thoi diem tinh ranking gan nhat';
COMMENT ON COLUMN GAME_MONTHLY_RANKING.CREATED_TIME IS 'Thoi diem tao ban ghi';
COMMENT ON COLUMN GAME_MONTHLY_RANKING.UPDATED_TIME IS 'Thoi diem cap nhat gan nhat';

--------------------------------------------------------
-- GAME_MONTHLY_TICKET_RULE
--------------------------------------------------------

CREATE TABLE GAME_MONTHLY_TICKET_RULE
(
    TICKET_RULE_ID  NUMBER(10,0) NOT NULL,
    MIN_SCORE       NUMBER(12,0) NOT NULL,
    MAX_SCORE       NUMBER(12,0),
    TICKET_COUNT    NUMBER(10,0) NOT NULL,
    STATUS          NUMBER(1,0) DEFAULT 1 NOT NULL,
    CREATED_TIME    DATE DEFAULT SYSDATE NOT NULL,
    UPDATED_TIME    DATE,
    CONSTRAINT PK_GAME_MONTHLY_TICKET_RULE PRIMARY KEY (TICKET_RULE_ID),
    CONSTRAINT CK_GMTR_SCORE CHECK (MIN_SCORE >= 0 AND (MAX_SCORE IS NULL OR MAX_SCORE >= MIN_SCORE)),
    CONSTRAINT CK_GMTR_TICKET CHECK (TICKET_COUNT >= 0),
    CONSTRAINT CK_GMTR_STATUS CHECK (STATUS IN (0, 1))
);

CREATE INDEX IDX_GMTR_SCORE_RANGE ON GAME_MONTHLY_TICKET_RULE (MIN_SCORE, MAX_SCORE, STATUS);

COMMENT ON TABLE GAME_MONTHLY_TICKET_RULE IS 'Cau hinh so ticket theo tong score thang';
COMMENT ON COLUMN GAME_MONTHLY_TICKET_RULE.TICKET_RULE_ID IS 'Khoa chinh rule ticket';
COMMENT ON COLUMN GAME_MONTHLY_TICKET_RULE.MIN_SCORE IS 'Diem toi thieu de ap dung rule';
COMMENT ON COLUMN GAME_MONTHLY_TICKET_RULE.MAX_SCORE IS 'Diem toi da de ap dung rule, null la khong gioi han tren';
COMMENT ON COLUMN GAME_MONTHLY_TICKET_RULE.TICKET_COUNT IS 'So ticket duoc cap';
COMMENT ON COLUMN GAME_MONTHLY_TICKET_RULE.STATUS IS 'Trang thai rule: 1 hieu luc, 0 khong hieu luc';
COMMENT ON COLUMN GAME_MONTHLY_TICKET_RULE.CREATED_TIME IS 'Thoi diem tao ban ghi';
COMMENT ON COLUMN GAME_MONTHLY_TICKET_RULE.UPDATED_TIME IS 'Thoi diem cap nhat gan nhat';

--------------------------------------------------------
-- GAME_MONTHLY_PRIZE_CONFIG
--------------------------------------------------------

CREATE TABLE GAME_MONTHLY_PRIZE_CONFIG
(
    PRIZE_CONFIG_ID NUMBER(10,0) NOT NULL,
    PRIZE_CODE      VARCHAR2(30 BYTE) NOT NULL,
    PRIZE_NAME      NVARCHAR2(200) NOT NULL,
    PRIZE_VALUE     NUMBER(18,2) NOT NULL,
    PRIZE_UNIT      VARCHAR2(20 BYTE) DEFAULT 'MMK' NOT NULL,
    QUANTITY        NUMBER(10,0) NOT NULL,
    DISPLAY_ORDER   NUMBER(5,0) DEFAULT 0 NOT NULL,
    STATUS          NUMBER(1,0) DEFAULT 1 NOT NULL,
    CREATED_TIME    DATE DEFAULT SYSDATE NOT NULL,
    UPDATED_TIME    DATE,
    CONSTRAINT PK_GAME_MONTHLY_PRIZE_CONFIG PRIMARY KEY (PRIZE_CONFIG_ID),
    CONSTRAINT UK_GMPC_PRIZE_CODE UNIQUE (PRIZE_CODE),
    CONSTRAINT CK_GMPC_VALUE CHECK (PRIZE_VALUE >= 0),
    CONSTRAINT CK_GMPC_QUANTITY CHECK (QUANTITY > 0),
    CONSTRAINT CK_GMPC_STATUS CHECK (STATUS IN (0, 1))
);

CREATE INDEX IDX_GMPC_STATUS_ORDER ON GAME_MONTHLY_PRIZE_CONFIG (STATUS, DISPLAY_ORDER);

COMMENT ON TABLE GAME_MONTHLY_PRIZE_CONFIG IS 'Cau hinh giai thuong quay thang';
COMMENT ON COLUMN GAME_MONTHLY_PRIZE_CONFIG.PRIZE_CONFIG_ID IS 'Khoa chinh cau hinh giai';
COMMENT ON COLUMN GAME_MONTHLY_PRIZE_CONFIG.PRIZE_CODE IS 'Ma giai: FIRST, SECOND, THIRD';
COMMENT ON COLUMN GAME_MONTHLY_PRIZE_CONFIG.PRIZE_NAME IS 'Ten giai hien thi';
COMMENT ON COLUMN GAME_MONTHLY_PRIZE_CONFIG.PRIZE_VALUE IS 'Gia tri giai thuong';
COMMENT ON COLUMN GAME_MONTHLY_PRIZE_CONFIG.PRIZE_UNIT IS 'Don vi giai thuong, vi du MMK';
COMMENT ON COLUMN GAME_MONTHLY_PRIZE_CONFIG.QUANTITY IS 'So luong nguoi trung giai nay moi thang';
COMMENT ON COLUMN GAME_MONTHLY_PRIZE_CONFIG.DISPLAY_ORDER IS 'Thu tu xu ly/hien thi giai';
COMMENT ON COLUMN GAME_MONTHLY_PRIZE_CONFIG.STATUS IS 'Trang thai cau hinh: 1 hieu luc, 0 khong hieu luc';
COMMENT ON COLUMN GAME_MONTHLY_PRIZE_CONFIG.CREATED_TIME IS 'Thoi diem tao ban ghi';
COMMENT ON COLUMN GAME_MONTHLY_PRIZE_CONFIG.UPDATED_TIME IS 'Thoi diem cap nhat gan nhat';

--------------------------------------------------------
-- GAME_MONTHLY_DRAW
--------------------------------------------------------

CREATE TABLE GAME_MONTHLY_DRAW
(
    DRAW_ID          NUMBER(18,0) NOT NULL,
    MONTH_KEY        NUMBER(6,0) NOT NULL,
    DRAW_TIME        DATE,
    STATUS           NUMBER(2,0) DEFAULT 0 NOT NULL,
    TOTAL_ELIGIBLE   NUMBER(12,0) DEFAULT 0 NOT NULL,
    TOTAL_TICKET     NUMBER(12,0) DEFAULT 0 NOT NULL,
    CREATED_TIME     DATE DEFAULT SYSDATE NOT NULL,
    UPDATED_TIME     DATE,
    CONSTRAINT PK_GAME_MONTHLY_DRAW PRIMARY KEY (DRAW_ID),
    CONSTRAINT UK_GMD_MONTH UNIQUE (MONTH_KEY),
    CONSTRAINT CK_GMD_MONTH_KEY CHECK (MONTH_KEY BETWEEN 200001 AND 299912),
    CONSTRAINT CK_GMD_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4)),
    CONSTRAINT CK_GMD_TOTAL CHECK (TOTAL_ELIGIBLE >= 0 AND TOTAL_TICKET >= 0)
);

CREATE INDEX IDX_GMD_STATUS ON GAME_MONTHLY_DRAW (STATUS, MONTH_KEY);

COMMENT ON TABLE GAME_MONTHLY_DRAW IS 'Dot quay thuong thang';
COMMENT ON COLUMN GAME_MONTHLY_DRAW.DRAW_ID IS 'Khoa chinh dot quay';
COMMENT ON COLUMN GAME_MONTHLY_DRAW.MONTH_KEY IS 'Thang duoc quay thuong dang YYYYMM';
COMMENT ON COLUMN GAME_MONTHLY_DRAW.DRAW_TIME IS 'Thoi diem thuc hien quay';
COMMENT ON COLUMN GAME_MONTHLY_DRAW.STATUS IS 'Trang thai draw: 0 NEW, 1 LOCKED, 2 DRAWING, 3 COMPLETED, 4 CANCELLED';
COMMENT ON COLUMN GAME_MONTHLY_DRAW.TOTAL_ELIGIBLE IS 'Tong so user du dieu kien quay';
COMMENT ON COLUMN GAME_MONTHLY_DRAW.TOTAL_TICKET IS 'Tong so ticket trong dot quay';
COMMENT ON COLUMN GAME_MONTHLY_DRAW.CREATED_TIME IS 'Thoi diem tao ban ghi';
COMMENT ON COLUMN GAME_MONTHLY_DRAW.UPDATED_TIME IS 'Thoi diem cap nhat gan nhat';

--------------------------------------------------------
-- GAME_MONTHLY_DRAW_TICKET
--------------------------------------------------------

CREATE TABLE GAME_MONTHLY_DRAW_TICKET
(
    TICKET_ID       NUMBER(18,0) NOT NULL,
    DRAW_ID         NUMBER(18,0) NOT NULL,
    MONTH_KEY       NUMBER(6,0) NOT NULL,
    MSISDN          VARCHAR2(20 BYTE) NOT NULL,
    TICKET_NO       NUMBER(12,0) NOT NULL,
    TOTAL_SCORE     NUMBER(12,0) NOT NULL,
    CREATED_TIME    DATE DEFAULT SYSDATE NOT NULL,
    CONSTRAINT PK_GAME_MONTHLY_DRAW_TICKET PRIMARY KEY (TICKET_ID),
    CONSTRAINT UK_GMDT_TICKET UNIQUE (DRAW_ID, TICKET_NO),
    CONSTRAINT FK_GMDT_DRAW FOREIGN KEY (DRAW_ID) REFERENCES GAME_MONTHLY_DRAW (DRAW_ID),
    CONSTRAINT CK_GMDT_MONTH_KEY CHECK (MONTH_KEY BETWEEN 200001 AND 299912),
    CONSTRAINT CK_GMDT_TICKET_NO CHECK (TICKET_NO > 0),
    CONSTRAINT CK_GMDT_TOTAL_SCORE CHECK (TOTAL_SCORE >= 0)
);

CREATE INDEX IDX_GMDT_MONTH_MSISDN ON GAME_MONTHLY_DRAW_TICKET (MONTH_KEY, MSISDN);
CREATE INDEX IDX_GMDT_DRAW_MSISDN ON GAME_MONTHLY_DRAW_TICKET (DRAW_ID, MSISDN);

COMMENT ON TABLE GAME_MONTHLY_DRAW_TICKET IS 'Ticket quay thuong thang cua user';
COMMENT ON COLUMN GAME_MONTHLY_DRAW_TICKET.TICKET_ID IS 'Khoa chinh ticket';
COMMENT ON COLUMN GAME_MONTHLY_DRAW_TICKET.DRAW_ID IS 'Dot quay thuong chua ticket';
COMMENT ON COLUMN GAME_MONTHLY_DRAW_TICKET.MONTH_KEY IS 'Thang cua ticket dang YYYYMM';
COMMENT ON COLUMN GAME_MONTHLY_DRAW_TICKET.MSISDN IS 'So thue bao so huu ticket';
COMMENT ON COLUMN GAME_MONTHLY_DRAW_TICKET.TICKET_NO IS 'So thu tu ticket trong dot quay';
COMMENT ON COLUMN GAME_MONTHLY_DRAW_TICKET.TOTAL_SCORE IS 'Tong score thang tai thoi diem sinh ticket';
COMMENT ON COLUMN GAME_MONTHLY_DRAW_TICKET.CREATED_TIME IS 'Thoi diem tao ticket';

--------------------------------------------------------
-- GAME_MONTHLY_WINNER
--------------------------------------------------------

CREATE TABLE GAME_MONTHLY_WINNER
(
    WINNER_ID       NUMBER(18,0) NOT NULL,
    DRAW_ID         NUMBER(18,0) NOT NULL,
    MONTH_KEY       NUMBER(6,0) NOT NULL,
    MSISDN          VARCHAR2(20 BYTE) NOT NULL,
    TICKET_ID       NUMBER(18,0),
    PRIZE_CODE      VARCHAR2(30 BYTE) NOT NULL,
    PRIZE_NAME      NVARCHAR2(200) NOT NULL,
    PRIZE_VALUE     NUMBER(18,2) NOT NULL,
    PRIZE_UNIT      VARCHAR2(20 BYTE) DEFAULT 'MMK' NOT NULL,
    STATUS          NUMBER(2,0) DEFAULT 0 NOT NULL,
    ANNOUNCE_TIME   DATE,
    PAID_TIME       DATE,
    RESPONSE_CODE   VARCHAR2(50 BYTE),
    RESPONSE_MESSAGE VARCHAR2(500 BYTE),
    CREATED_TIME    DATE DEFAULT SYSDATE NOT NULL,
    UPDATED_TIME    DATE,
    CONSTRAINT PK_GAME_MONTHLY_WINNER PRIMARY KEY (WINNER_ID),
    CONSTRAINT UK_GMW_DRAW_PRIZE_MSISDN UNIQUE (DRAW_ID, PRIZE_CODE, MSISDN),
    CONSTRAINT UK_GMW_DRAW_MSISDN UNIQUE (DRAW_ID, MSISDN),
    CONSTRAINT FK_GMW_DRAW FOREIGN KEY (DRAW_ID) REFERENCES GAME_MONTHLY_DRAW (DRAW_ID),
    CONSTRAINT FK_GMW_TICKET FOREIGN KEY (TICKET_ID) REFERENCES GAME_MONTHLY_DRAW_TICKET (TICKET_ID),
    CONSTRAINT CK_GMW_MONTH_KEY CHECK (MONTH_KEY BETWEEN 200001 AND 299912),
    CONSTRAINT CK_GMW_PRIZE_VALUE CHECK (PRIZE_VALUE >= 0),
    CONSTRAINT CK_GMW_STATUS CHECK (STATUS IN (0, 1, 2, 3, 4))
);

CREATE INDEX IDX_GMW_MSISDN_MONTH ON GAME_MONTHLY_WINNER (MSISDN, MONTH_KEY);
CREATE INDEX IDX_GMW_DRAW_STATUS ON GAME_MONTHLY_WINNER (DRAW_ID, STATUS);
CREATE INDEX IDX_GMW_PRIZE_CODE ON GAME_MONTHLY_WINNER (PRIZE_CODE);

COMMENT ON TABLE GAME_MONTHLY_WINNER IS 'Danh sach user trung thuong thang';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.WINNER_ID IS 'Khoa chinh winner';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.DRAW_ID IS 'Dot quay sinh winner';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.MONTH_KEY IS 'Thang trung thuong dang YYYYMM';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.MSISDN IS 'So thue bao trung thuong';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.TICKET_ID IS 'Ticket trung thuong';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.PRIZE_CODE IS 'Ma giai trung thuong';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.PRIZE_NAME IS 'Ten giai trung thuong';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.PRIZE_VALUE IS 'Gia tri giai trung thuong';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.PRIZE_UNIT IS 'Don vi giai trung thuong';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.STATUS IS 'Trang thai winner: 0 NEW, 1 ANNOUNCED, 2 PAID, 3 FAILED, 4 CANCELLED';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.ANNOUNCE_TIME IS 'Thoi diem cong bo winner';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.PAID_TIME IS 'Thoi diem tra giai';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.RESPONSE_CODE IS 'Ma ket qua tra giai';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.RESPONSE_MESSAGE IS 'Mo ta ket qua tra giai';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.CREATED_TIME IS 'Thoi diem tao ban ghi';
COMMENT ON COLUMN GAME_MONTHLY_WINNER.UPDATED_TIME IS 'Thoi diem cap nhat gan nhat';

--------------------------------------------------------
-- SEED DATA CO BAN
--------------------------------------------------------

INSERT INTO GAME_MODE (MODE_ID, MODE_CODE, MODE_NAME, TIME_LIMIT_SECONDS, DISPLAY_ORDER, STATUS)
VALUES (SEQ_GAME_MODE.NEXTVAL, 'EASY', N'Easy', 150, 1, 1);

INSERT INTO GAME_MODE (MODE_ID, MODE_CODE, MODE_NAME, TIME_LIMIT_SECONDS, DISPLAY_ORDER, STATUS)
VALUES (SEQ_GAME_MODE.NEXTVAL, 'NORMAL', N'Normal', 100, 2, 1);

INSERT INTO GAME_MODE (MODE_ID, MODE_CODE, MODE_NAME, TIME_LIMIT_SECONDS, DISPLAY_ORDER, STATUS)
VALUES (SEQ_GAME_MODE.NEXTVAL, 'HARD', N'Hard', 60, 3, 1);

--------------------------------------------------------
-- SEED LEVEL CONFIG VA REWARD OPTION THEO KICH BAN
--------------------------------------------------------

-- EASY level config: 50 level, milestone tai 5/10/15/.../50.
INSERT INTO GAME_LEVEL_CONFIG
(
    LEVEL_CONFIG_ID, MODE_ID, LEVEL_NO, BASE_SCORE, TIME_LIMIT_SECONDS,
    IS_MILESTONE, STATUS, CREATED_TIME, UPDATED_TIME
)
SELECT
    SEQ_GAME_LEVEL_CONFIG.NEXTVAL, m.MODE_ID, cfg.LEVEL_NO, cfg.BASE_SCORE, 150,
    cfg.IS_MILESTONE, 1, SYSDATE, NULL
FROM GAME_MODE m
JOIN
(
    SELECT 1 LEVEL_NO, 10 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 2 LEVEL_NO, 10 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 3 LEVEL_NO, 10 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 4 LEVEL_NO, 10 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 5 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 6 LEVEL_NO, 15 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 7 LEVEL_NO, 15 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 8 LEVEL_NO, 15 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 9 LEVEL_NO, 15 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 10 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 11 LEVEL_NO, 20 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 12 LEVEL_NO, 20 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 13 LEVEL_NO, 20 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 14 LEVEL_NO, 20 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 15 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 16 LEVEL_NO, 25 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 17 LEVEL_NO, 25 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 18 LEVEL_NO, 25 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 19 LEVEL_NO, 25 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 20 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 21 LEVEL_NO, 35 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 22 LEVEL_NO, 35 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 23 LEVEL_NO, 35 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 24 LEVEL_NO, 35 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 25 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 26 LEVEL_NO, 45 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 27 LEVEL_NO, 45 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 28 LEVEL_NO, 45 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 29 LEVEL_NO, 45 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 30 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 31 LEVEL_NO, 55 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 32 LEVEL_NO, 55 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 33 LEVEL_NO, 55 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 34 LEVEL_NO, 55 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 35 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 36 LEVEL_NO, 65 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 37 LEVEL_NO, 65 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 38 LEVEL_NO, 65 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 39 LEVEL_NO, 65 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 40 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 41 LEVEL_NO, 80 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 42 LEVEL_NO, 80 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 43 LEVEL_NO, 80 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 44 LEVEL_NO, 80 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 45 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 46 LEVEL_NO, 100 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 47 LEVEL_NO, 100 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 48 LEVEL_NO, 100 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 49 LEVEL_NO, 100 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 50 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
) cfg ON 1 = 1
WHERE m.MODE_CODE = 'EASY';
-- EASY reward options theo cac level moc trong kich ban.
INSERT INTO GAME_LEVEL_REWARD_OPTION
(
    REWARD_OPTION_ID, MODE_ID, LEVEL_NO, REWARD_TYPE, REWARD_VALUE,
    REWARD_UNIT, WEIGHT, IS_DEFAULT, REWARD_DESC, STATUS, CREATED_TIME, UPDATED_TIME
)
SELECT
    SEQ_GAME_LEVEL_REWARD_OPTION.NEXTVAL, m.MODE_ID, cfg.LEVEL_NO, cfg.REWARD_TYPE, cfg.REWARD_VALUE,
    cfg.REWARD_UNIT, 1, 0, cfg.REWARD_DESC, 1, SYSDATE, NULL
FROM GAME_MODE m
JOIN
(
    SELECT 5 LEVEL_NO, 'DATA' REWARD_TYPE, 5 REWARD_VALUE, 'MB' REWARD_UNIT, N'Easy level 5 reward data 5MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 5 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 3 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 5 reward loyalty 3 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 5 LEVEL_NO, 'SCORE' REWARD_TYPE, 20 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 5 reward score 20 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 10 LEVEL_NO, 'DATA' REWARD_TYPE, 10 REWARD_VALUE, 'MB' REWARD_UNIT, N'Easy level 10 reward data 10MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 10 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 5 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 10 reward loyalty 5 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 10 LEVEL_NO, 'SCORE' REWARD_TYPE, 40 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 10 reward score 40 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 15 LEVEL_NO, 'DATA' REWARD_TYPE, 20 REWARD_VALUE, 'MB' REWARD_UNIT, N'Easy level 15 reward data 20MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 15 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 10 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 15 reward loyalty 10 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 15 LEVEL_NO, 'SCORE' REWARD_TYPE, 70 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 15 reward score 70 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 20 LEVEL_NO, 'DATA' REWARD_TYPE, 40 REWARD_VALUE, 'MB' REWARD_UNIT, N'Easy level 20 reward data 40MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 20 LEVEL_NO, 'SCORE' REWARD_TYPE, 100 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 20 reward score 100 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 25 LEVEL_NO, 'DATA' REWARD_TYPE, 70 REWARD_VALUE, 'MB' REWARD_UNIT, N'Easy level 25 reward data 70MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 25 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 20 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 25 reward loyalty 20 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 25 LEVEL_NO, 'SCORE' REWARD_TYPE, 140 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 25 reward score 140 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 30 LEVEL_NO, 'DATA' REWARD_TYPE, 100 REWARD_VALUE, 'MB' REWARD_UNIT, N'Easy level 30 reward data 100MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 30 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 30 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 30 reward loyalty 30 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 30 LEVEL_NO, 'SCORE' REWARD_TYPE, 180 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 30 reward score 180 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 35 LEVEL_NO, 'DATA' REWARD_TYPE, 120 REWARD_VALUE, 'MB' REWARD_UNIT, N'Easy level 35 reward data 120MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 35 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 40 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 35 reward loyalty 40 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 35 LEVEL_NO, 'SCORE' REWARD_TYPE, 230 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 35 reward score 230 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 40 LEVEL_NO, 'DATA' REWARD_TYPE, 150 REWARD_VALUE, 'MB' REWARD_UNIT, N'Easy level 40 reward data 150MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 40 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 50 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 40 reward loyalty 50 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 40 LEVEL_NO, 'SCORE' REWARD_TYPE, 300 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 40 reward score 300 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 45 LEVEL_NO, 'DATA' REWARD_TYPE, 180 REWARD_VALUE, 'MB' REWARD_UNIT, N'Easy level 45 reward data 180MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 45 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 60 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 45 reward loyalty 60 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 45 LEVEL_NO, 'SCORE' REWARD_TYPE, 380 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 45 reward score 380 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 50 LEVEL_NO, 'DATA' REWARD_TYPE, 250 REWARD_VALUE, 'MB' REWARD_UNIT, N'Easy level 50 reward data 250MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 50 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 80 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 50 reward loyalty 80 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 50 LEVEL_NO, 'SCORE' REWARD_TYPE, 500 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Easy level 50 reward score 500 point' REWARD_DESC FROM DUAL
) cfg ON 1 = 1
WHERE m.MODE_CODE = 'EASY';
-- NORMAL level config: 50 level, milestone tai 5/10/15/.../50.
INSERT INTO GAME_LEVEL_CONFIG
(
    LEVEL_CONFIG_ID, MODE_ID, LEVEL_NO, BASE_SCORE, TIME_LIMIT_SECONDS,
    IS_MILESTONE, STATUS, CREATED_TIME, UPDATED_TIME
)
SELECT
    SEQ_GAME_LEVEL_CONFIG.NEXTVAL, m.MODE_ID, cfg.LEVEL_NO, cfg.BASE_SCORE, 100,
    cfg.IS_MILESTONE, 1, SYSDATE, NULL
FROM GAME_MODE m
JOIN
(
    SELECT 1 LEVEL_NO, 20 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 2 LEVEL_NO, 20 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 3 LEVEL_NO, 20 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 4 LEVEL_NO, 20 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 5 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 6 LEVEL_NO, 25 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 7 LEVEL_NO, 25 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 8 LEVEL_NO, 25 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 9 LEVEL_NO, 25 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 10 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 11 LEVEL_NO, 30 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 12 LEVEL_NO, 30 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 13 LEVEL_NO, 30 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 14 LEVEL_NO, 30 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 15 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 16 LEVEL_NO, 35 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 17 LEVEL_NO, 35 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 18 LEVEL_NO, 35 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 19 LEVEL_NO, 35 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 20 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 21 LEVEL_NO, 40 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 22 LEVEL_NO, 40 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 23 LEVEL_NO, 40 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 24 LEVEL_NO, 40 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 25 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 26 LEVEL_NO, 50 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 27 LEVEL_NO, 50 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 28 LEVEL_NO, 50 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 29 LEVEL_NO, 50 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 30 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 31 LEVEL_NO, 60 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 32 LEVEL_NO, 60 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 33 LEVEL_NO, 60 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 34 LEVEL_NO, 60 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 35 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 36 LEVEL_NO, 70 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 37 LEVEL_NO, 70 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 38 LEVEL_NO, 70 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 39 LEVEL_NO, 70 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 40 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 41 LEVEL_NO, 80 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 42 LEVEL_NO, 80 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 43 LEVEL_NO, 80 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 44 LEVEL_NO, 80 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 45 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 46 LEVEL_NO, 100 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 47 LEVEL_NO, 100 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 48 LEVEL_NO, 100 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 49 LEVEL_NO, 100 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 50 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
) cfg ON 1 = 1
WHERE m.MODE_CODE = 'NORMAL';
-- NORMAL reward options theo cac level moc trong kich ban.
INSERT INTO GAME_LEVEL_REWARD_OPTION
(
    REWARD_OPTION_ID, MODE_ID, LEVEL_NO, REWARD_TYPE, REWARD_VALUE,
    REWARD_UNIT, WEIGHT, IS_DEFAULT, REWARD_DESC, STATUS, CREATED_TIME, UPDATED_TIME
)
SELECT
    SEQ_GAME_LEVEL_REWARD_OPTION.NEXTVAL, m.MODE_ID, cfg.LEVEL_NO, cfg.REWARD_TYPE, cfg.REWARD_VALUE,
    cfg.REWARD_UNIT, 1, 0, cfg.REWARD_DESC, 1, SYSDATE, NULL
FROM GAME_MODE m
JOIN
(
    SELECT 5 LEVEL_NO, 'DATA' REWARD_TYPE, 20 REWARD_VALUE, 'MB' REWARD_UNIT, N'Normal level 5 reward data 20MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 5 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 6 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 5 reward loyalty 6 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 5 LEVEL_NO, 'SCORE' REWARD_TYPE, 70 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 5 reward score 70 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 10 LEVEL_NO, 'DATA' REWARD_TYPE, 40 REWARD_VALUE, 'MB' REWARD_UNIT, N'Normal level 10 reward data 40MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 10 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 12 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 10 reward loyalty 12 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 10 LEVEL_NO, 'SCORE' REWARD_TYPE, 130 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 10 reward score 130 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 15 LEVEL_NO, 'DATA' REWARD_TYPE, 80 REWARD_VALUE, 'MB' REWARD_UNIT, N'Normal level 15 reward data 80MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 15 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 18 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 15 reward loyalty 18 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 15 LEVEL_NO, 'SCORE' REWARD_TYPE, 190 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 15 reward score 190 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 20 LEVEL_NO, 'DATA' REWARD_TYPE, 140 REWARD_VALUE, 'MB' REWARD_UNIT, N'Normal level 20 reward data 140MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 20 LEVEL_NO, 'SCORE' REWARD_TYPE, 260 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 20 reward score 260 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 25 LEVEL_NO, 'DATA' REWARD_TYPE, 180 REWARD_VALUE, 'MB' REWARD_UNIT, N'Normal level 25 reward data 180MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 25 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 30 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 25 reward loyalty 30 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 25 LEVEL_NO, 'SCORE' REWARD_TYPE, 320 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 25 reward score 320 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 30 LEVEL_NO, 'DATA' REWARD_TYPE, 220 REWARD_VALUE, 'MB' REWARD_UNIT, N'Normal level 30 reward data 220MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 30 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 40 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 30 reward loyalty 40 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 30 LEVEL_NO, 'SCORE' REWARD_TYPE, 380 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 30 reward score 380 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 35 LEVEL_NO, 'DATA' REWARD_TYPE, 260 REWARD_VALUE, 'MB' REWARD_UNIT, N'Normal level 35 reward data 260MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 35 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 60 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 35 reward loyalty 60 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 35 LEVEL_NO, 'SCORE' REWARD_TYPE, 450 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 35 reward score 450 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 40 LEVEL_NO, 'DATA' REWARD_TYPE, 300 REWARD_VALUE, 'MB' REWARD_UNIT, N'Normal level 40 reward data 300MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 40 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 70 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 40 reward loyalty 70 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 40 LEVEL_NO, 'SCORE' REWARD_TYPE, 520 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 40 reward score 520 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 45 LEVEL_NO, 'DATA' REWARD_TYPE, 350 REWARD_VALUE, 'MB' REWARD_UNIT, N'Normal level 45 reward data 350MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 45 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 80 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 45 reward loyalty 80 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 45 LEVEL_NO, 'SCORE' REWARD_TYPE, 600 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 45 reward score 600 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 50 LEVEL_NO, 'DATA' REWARD_TYPE, 450 REWARD_VALUE, 'MB' REWARD_UNIT, N'Normal level 50 reward data 450MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 50 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 100 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 50 reward loyalty 100 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 50 LEVEL_NO, 'SCORE' REWARD_TYPE, 700 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Normal level 50 reward score 700 point' REWARD_DESC FROM DUAL
) cfg ON 1 = 1
WHERE m.MODE_CODE = 'NORMAL';
-- HARD level config: 50 level, milestone tai 5/10/15/.../50.
INSERT INTO GAME_LEVEL_CONFIG
(
    LEVEL_CONFIG_ID, MODE_ID, LEVEL_NO, BASE_SCORE, TIME_LIMIT_SECONDS,
    IS_MILESTONE, STATUS, CREATED_TIME, UPDATED_TIME
)
SELECT
    SEQ_GAME_LEVEL_CONFIG.NEXTVAL, m.MODE_ID, cfg.LEVEL_NO, cfg.BASE_SCORE, 60,
    cfg.IS_MILESTONE, 1, SYSDATE, NULL
FROM GAME_MODE m
JOIN
(
    SELECT 1 LEVEL_NO, 40 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 2 LEVEL_NO, 40 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 3 LEVEL_NO, 40 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 4 LEVEL_NO, 40 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 5 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 6 LEVEL_NO, 50 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 7 LEVEL_NO, 50 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 8 LEVEL_NO, 50 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 9 LEVEL_NO, 50 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 10 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 11 LEVEL_NO, 60 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 12 LEVEL_NO, 60 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 13 LEVEL_NO, 60 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 14 LEVEL_NO, 60 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 15 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 16 LEVEL_NO, 70 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 17 LEVEL_NO, 70 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 18 LEVEL_NO, 70 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 19 LEVEL_NO, 70 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 20 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 21 LEVEL_NO, 85 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 22 LEVEL_NO, 85 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 23 LEVEL_NO, 85 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 24 LEVEL_NO, 85 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 25 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 26 LEVEL_NO, 100 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 27 LEVEL_NO, 100 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 28 LEVEL_NO, 100 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 29 LEVEL_NO, 100 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 30 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 31 LEVEL_NO, 120 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 32 LEVEL_NO, 120 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 33 LEVEL_NO, 120 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 34 LEVEL_NO, 120 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 35 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 36 LEVEL_NO, 140 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 37 LEVEL_NO, 140 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 38 LEVEL_NO, 140 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 39 LEVEL_NO, 140 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 40 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 41 LEVEL_NO, 160 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 42 LEVEL_NO, 160 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 43 LEVEL_NO, 160 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 44 LEVEL_NO, 160 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 45 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 46 LEVEL_NO, 180 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 47 LEVEL_NO, 180 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 48 LEVEL_NO, 180 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 49 LEVEL_NO, 180 BASE_SCORE, 0 IS_MILESTONE FROM DUAL
    UNION ALL
    SELECT 50 LEVEL_NO, 0 BASE_SCORE, 1 IS_MILESTONE FROM DUAL
) cfg ON 1 = 1
WHERE m.MODE_CODE = 'HARD';
-- HARD reward options theo cac level moc trong kich ban.
INSERT INTO GAME_LEVEL_REWARD_OPTION
(
    REWARD_OPTION_ID, MODE_ID, LEVEL_NO, REWARD_TYPE, REWARD_VALUE,
    REWARD_UNIT, WEIGHT, IS_DEFAULT, REWARD_DESC, STATUS, CREATED_TIME, UPDATED_TIME
)
SELECT
    SEQ_GAME_LEVEL_REWARD_OPTION.NEXTVAL, m.MODE_ID, cfg.LEVEL_NO, cfg.REWARD_TYPE, cfg.REWARD_VALUE,
    cfg.REWARD_UNIT, 1, 0, cfg.REWARD_DESC, 1, SYSDATE, NULL
FROM GAME_MODE m
JOIN
(
    SELECT 5 LEVEL_NO, 'DATA' REWARD_TYPE, 40 REWARD_VALUE, 'MB' REWARD_UNIT, N'Hard level 5 reward data 40MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 5 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 10 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 5 reward loyalty 10 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 5 LEVEL_NO, 'SCORE' REWARD_TYPE, 100 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 5 reward score 100 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 10 LEVEL_NO, 'DATA' REWARD_TYPE, 80 REWARD_VALUE, 'MB' REWARD_UNIT, N'Hard level 10 reward data 80MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 10 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 20 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 10 reward loyalty 20 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 10 LEVEL_NO, 'SCORE' REWARD_TYPE, 200 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 10 reward score 200 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 15 LEVEL_NO, 'DATA' REWARD_TYPE, 140 REWARD_VALUE, 'MB' REWARD_UNIT, N'Hard level 15 reward data 140MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 15 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 30 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 15 reward loyalty 30 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 15 LEVEL_NO, 'SCORE' REWARD_TYPE, 300 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 15 reward score 300 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 20 LEVEL_NO, 'DATA' REWARD_TYPE, 220 REWARD_VALUE, 'MB' REWARD_UNIT, N'Hard level 20 reward data 220MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 20 LEVEL_NO, 'SCORE' REWARD_TYPE, 400 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 20 reward score 400 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 25 LEVEL_NO, 'DATA' REWARD_TYPE, 300 REWARD_VALUE, 'MB' REWARD_UNIT, N'Hard level 25 reward data 300MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 25 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 50 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 25 reward loyalty 50 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 25 LEVEL_NO, 'SCORE' REWARD_TYPE, 500 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 25 reward score 500 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 30 LEVEL_NO, 'DATA' REWARD_TYPE, 380 REWARD_VALUE, 'MB' REWARD_UNIT, N'Hard level 30 reward data 380MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 30 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 70 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 30 reward loyalty 70 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 30 LEVEL_NO, 'SCORE' REWARD_TYPE, 600 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 30 reward score 600 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 35 LEVEL_NO, 'DATA' REWARD_TYPE, 450 REWARD_VALUE, 'MB' REWARD_UNIT, N'Hard level 35 reward data 450MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 35 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 90 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 35 reward loyalty 90 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 35 LEVEL_NO, 'SCORE' REWARD_TYPE, 700 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 35 reward score 700 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 40 LEVEL_NO, 'DATA' REWARD_TYPE, 520 REWARD_VALUE, 'MB' REWARD_UNIT, N'Hard level 40 reward data 520MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 40 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 110 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 40 reward loyalty 110 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 40 LEVEL_NO, 'SCORE' REWARD_TYPE, 800 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 40 reward score 800 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 45 LEVEL_NO, 'DATA' REWARD_TYPE, 600 REWARD_VALUE, 'MB' REWARD_UNIT, N'Hard level 45 reward data 600MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 45 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 130 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 45 reward loyalty 130 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 45 LEVEL_NO, 'SCORE' REWARD_TYPE, 900 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 45 reward score 900 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 50 LEVEL_NO, 'DATA' REWARD_TYPE, 750 REWARD_VALUE, 'MB' REWARD_UNIT, N'Hard level 50 reward data 750MB' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 50 LEVEL_NO, 'LOYALTY' REWARD_TYPE, 150 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 50 reward loyalty 150 point' REWARD_DESC FROM DUAL
    UNION ALL
    SELECT 50 LEVEL_NO, 'SCORE' REWARD_TYPE, 1000 REWARD_VALUE, 'POINT' REWARD_UNIT, N'Hard level 50 reward score 1000 point' REWARD_DESC FROM DUAL
) cfg ON 1 = 1
WHERE m.MODE_CODE = 'HARD';

INSERT INTO GAME_MONTHLY_TICKET_RULE (TICKET_RULE_ID, MIN_SCORE, MAX_SCORE, TICKET_COUNT, STATUS)
VALUES (SEQ_GAME_MONTHLY_TICKET_RULE.NEXTVAL, 5000, 5999, 1, 1);

INSERT INTO GAME_MONTHLY_TICKET_RULE (TICKET_RULE_ID, MIN_SCORE, MAX_SCORE, TICKET_COUNT, STATUS)
VALUES (SEQ_GAME_MONTHLY_TICKET_RULE.NEXTVAL, 6000, 6999, 2, 1);

INSERT INTO GAME_MONTHLY_TICKET_RULE (TICKET_RULE_ID, MIN_SCORE, MAX_SCORE, TICKET_COUNT, STATUS)
VALUES (SEQ_GAME_MONTHLY_TICKET_RULE.NEXTVAL, 7000, 7999, 3, 1);

INSERT INTO GAME_MONTHLY_TICKET_RULE (TICKET_RULE_ID, MIN_SCORE, MAX_SCORE, TICKET_COUNT, STATUS)
VALUES (SEQ_GAME_MONTHLY_TICKET_RULE.NEXTVAL, 8000, 8999, 5, 1);

INSERT INTO GAME_MONTHLY_TICKET_RULE (TICKET_RULE_ID, MIN_SCORE, MAX_SCORE, TICKET_COUNT, STATUS)
VALUES (SEQ_GAME_MONTHLY_TICKET_RULE.NEXTVAL, 9000, 9999, 7, 1);

INSERT INTO GAME_MONTHLY_TICKET_RULE (TICKET_RULE_ID, MIN_SCORE, MAX_SCORE, TICKET_COUNT, STATUS)
VALUES (SEQ_GAME_MONTHLY_TICKET_RULE.NEXTVAL, 10000, NULL, 10, 1);

INSERT INTO GAME_MONTHLY_PRIZE_CONFIG (PRIZE_CONFIG_ID, PRIZE_CODE, PRIZE_NAME, PRIZE_VALUE, PRIZE_UNIT, QUANTITY, DISPLAY_ORDER, STATUS)
VALUES (SEQ_GAME_MONTHLY_PRIZE_CONFIG.NEXTVAL, 'FIRST', N'First Prize', 2000000, 'MMK', 1, 1, 1);

INSERT INTO GAME_MONTHLY_PRIZE_CONFIG (PRIZE_CONFIG_ID, PRIZE_CODE, PRIZE_NAME, PRIZE_VALUE, PRIZE_UNIT, QUANTITY, DISPLAY_ORDER, STATUS)
VALUES (SEQ_GAME_MONTHLY_PRIZE_CONFIG.NEXTVAL, 'SECOND', N'Second Prize', 500000, 'MMK', 1, 2, 1);

INSERT INTO GAME_MONTHLY_PRIZE_CONFIG (PRIZE_CONFIG_ID, PRIZE_CODE, PRIZE_NAME, PRIZE_VALUE, PRIZE_UNIT, QUANTITY, DISPLAY_ORDER, STATUS)
VALUES (SEQ_GAME_MONTHLY_PRIZE_CONFIG.NEXTVAL, 'THIRD', N'Third Prize', 200000, 'MMK', 2, 3, 1);

--------------------------------------------------------
-- SAMPLE REWARD LIMIT CONFIG
-- Bo comment va thay gia tri han muc theo doanh thu/ngan sach thuc te.
--------------------------------------------------------

-- Gioi han tong data tra thuong trong ngay, ap dung moi mode/moi level.
-- INSERT INTO GAME_REWARD_LIMIT_CONFIG
-- (
--     LIMIT_CONFIG_ID, LIMIT_CODE, LIMIT_NAME, PERIOD_TYPE, REWARD_TYPE, REWARD_UNIT,
--     MODE_ID, LEVEL_NO, MAX_TOTAL_VALUE, MAX_TOTAL_COUNT, PRIORITY, EXCEED_ACTION, STATUS
-- )
-- VALUES
-- (
--     SEQ_GAME_REWARD_LIMIT_CONFIG.NEXTVAL, 'DAILY_DATA_MB_ALL', N'Daily data MB limit all game',
--     'DAILY', 'DATA', 'MB', NULL, NULL, 100000, NULL, 10, 'BLOCK', 1
-- );

-- Gioi han tong money tra thuong trong ngay, ap dung moi mode/moi level.
-- INSERT INTO GAME_REWARD_LIMIT_CONFIG
-- (
--     LIMIT_CONFIG_ID, LIMIT_CODE, LIMIT_NAME, PERIOD_TYPE, REWARD_TYPE, REWARD_UNIT,
--     MODE_ID, LEVEL_NO, MAX_TOTAL_VALUE, MAX_TOTAL_COUNT, PRIORITY, EXCEED_ACTION, STATUS
-- )
-- VALUES
-- (
--     SEQ_GAME_REWARD_LIMIT_CONFIG.NEXTVAL, 'DAILY_MONEY_MMK_ALL', N'Daily money MMK limit all game',
--     'DAILY', 'MONEY', 'MMK', NULL, NULL, 5000000, NULL, 10, 'BLOCK', 1
-- );

-- Gioi han so lan trung MONEY trong ngay, khong gioi han tong gia tri.
-- INSERT INTO GAME_REWARD_LIMIT_CONFIG
-- (
--     LIMIT_CONFIG_ID, LIMIT_CODE, LIMIT_NAME, PERIOD_TYPE, REWARD_TYPE, REWARD_UNIT,
--     MODE_ID, LEVEL_NO, MAX_TOTAL_VALUE, MAX_TOTAL_COUNT, PRIORITY, EXCEED_ACTION, STATUS
-- )
-- VALUES
-- (
--     SEQ_GAME_REWARD_LIMIT_CONFIG.NEXTVAL, 'DAILY_MONEY_COUNT_ALL', N'Daily money reward count limit',
--     'DAILY', 'MONEY', 'MMK', NULL, NULL, NULL, 100, 5, 'BLOCK', 1
-- );

COMMIT;

--------------------------------------------------------
-- END OF FILE
--------------------------------------------------------