-------------------------------------------------------- -- 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 --------------------------------------------------------