Source: config/migrations/20260809-eventlog-orga-index.js

export const id = '20260809-eventlog-orga-index';
export const name = 'Add generated UIDorga column + (UIDorga, seq) index to eventLog for replay';

/**
 * The replay endpoint (GET /api/kpe20/events) filters the eventLog by the
 * organization, which previously required a full scan of millions of rows via
 * JSON_VALUE(Data, '$.UIDorga'). A generated virtual column plus an index on
 * (UIDorga, seq) makes org-scoped replay and high-watermark lookups fast.
 */
export const migrate = async ({ query }) => {
    const [col] = await query(`
        SELECT COLUMN_NAME FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'eventLog' AND COLUMN_NAME = 'UIDorga'
    `, []);
    if (!col) {
        await query(`
            ALTER TABLE eventLog
            ADD COLUMN UIDorga varchar(64) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_VALUE(Data, '$.UIDorga'))) VIRTUAL
        `, []);
    } else {
        await query(`
            ALTER TABLE eventLog
            MODIFY COLUMN UIDorga varchar(64) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_VALUE(Data, '$.UIDorga'))) VIRTUAL
        `, []);
    }
    await query(`
        ALTER TABLE eventLog
        ADD INDEX IF NOT EXISTS idx_eventlog_orga_seq (UIDorga, seq)
    `, []);
};