Kitty_Fall_Natcom/Kitty_Fall/Kitty_Fall/sql/sync_game_sequences.sql

-- Dong bo sequence voi ID lon nhat dang co trong cac bang game.
-- Script chi tien sequence ve phia truoc, khong sua/xoa du lieu nghiep vu.
-- Nen chay bang schema KITTY_FALL truoc khi deploy API/Worker moi.

SET SERVEROUTPUT ON;

DECLARE
    PROCEDURE sync_sequence(
        p_sequence_name IN VARCHAR2,
        p_table_name    IN VARCHAR2,
        p_id_column     IN VARCHAR2
    ) IS
        v_sequence_name VARCHAR2(128);
        v_table_name    VARCHAR2(128);
        v_id_column     VARCHAR2(128);
        v_max_id        NUMBER;
        v_current_id    NUMBER;
        v_increment     NUMBER;
    BEGIN
        v_sequence_name := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(p_sequence_name));
        v_table_name := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(p_table_name));
        v_id_column := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(p_id_column));

        EXECUTE IMMEDIATE
            'SELECT NVL(MAX(' || v_id_column || '), 0) FROM ' || v_table_name
            INTO v_max_id;

        EXECUTE IMMEDIATE
            'SELECT ' || v_sequence_name || '.NEXTVAL FROM DUAL'
            INTO v_current_id;

        IF v_current_id < v_max_id THEN
            v_increment := v_max_id - v_current_id;
            EXECUTE IMMEDIATE
                'ALTER SEQUENCE ' || v_sequence_name || ' INCREMENT BY ' || TO_CHAR(v_increment);
            EXECUTE IMMEDIATE
                'SELECT ' || v_sequence_name || '.NEXTVAL FROM DUAL'
                INTO v_current_id;
            EXECUTE IMMEDIATE
                'ALTER SEQUENCE ' || v_sequence_name || ' INCREMENT BY 1';
        END IF;

        -- Khong dung DBMS_OUTPUT: mot so schema production khong duoc cap
        -- EXECUTE tren package nay. Ket qua duoc xac nhan bang cac SELECT ben duoi.
    EXCEPTION
        WHEN OTHERS THEN
            BEGIN
                EXECUTE IMMEDIATE
                    'ALTER SEQUENCE ' || v_sequence_name || ' INCREMENT BY 1';
            EXCEPTION
                WHEN OTHERS THEN NULL;
            END;
            RAISE;
    END;
BEGIN
    sync_sequence('SEQ_GAME_PLAY_SESSION',        'GAME_PLAY_SESSION',        'SESSION_ID');
    sync_sequence('SEQ_GAME_LEVEL_ATTEMPT',       'GAME_LEVEL_ATTEMPT',       'ATTEMPT_ID');
    sync_sequence('SEQ_GAME_REWARD_LOG',          'GAME_REWARD_LOG',          'REWARD_LOG_ID');
    sync_sequence('SEQ_GAME_SCORE_LEDGER',        'GAME_SCORE_LEDGER',        'LEDGER_ID');
    sync_sequence('SEQ_GAME_MONTHLY_RANKING',     'GAME_MONTHLY_RANKING',     'RANKING_ID');
    sync_sequence('SEQ_GAME_REWARD_LIMIT_USAGE',  'GAME_REWARD_LIMIT_USAGE',  'LIMIT_USAGE_ID');
    sync_sequence('SEQ_GAME_MONTHLY_DRAW',        'GAME_MONTHLY_DRAW',        'DRAW_ID');
    sync_sequence('SEQ_GAME_MONTHLY_DRAW_TICKET', 'GAME_MONTHLY_DRAW_TICKET', 'TICKET_ID');
    sync_sequence('SEQ_GAME_MONTHLY_WINNER',      'GAME_MONTHLY_WINNER',      'WINNER_ID');
END;
/

-- Xac nhan rieng loi PK_GAME_LEVEL_ATTEMPT vua gap:
SELECT MAX(ATTEMPT_ID) AS MAX_ATTEMPT_ID FROM GAME_LEVEL_ATTEMPT;
SELECT SEQUENCE_NAME, LAST_NUMBER, INCREMENT_BY, CYCLE_FLAG
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'SEQ_GAME_LEVEL_ATTEMPT';