Source: config/migrations/20260615-add-transaction-tuids.js

export const id = '20260615-add-transaction-tuids';
export const name = 'Add TUID virtual columns to Transactions and TransactionLines';

/**
 * Adds TUID (human-readable UUID) virtual columns to both transaction tables,
 * matching the same pattern used in ObjectBase, Member, Links, AIEmbeddings, and Visible.
 *
 * TUID is generated as: bytes[5-8]-bytes[3-4]-bytes[1-2]-bytes[9-10]-bytes[11-16]
 * This mirrors the byte ordering of UUID v1 hex display, which is the format
 * the CommTool @commtool/sql-query library (HEX2uuid/UUID2hex) expects.
 *
 * TransactionLines.UIDTransaction gets TUIDTransaction as a virtual FK display column.
 */
export const migrate = async ({ query }) => {
    await query('SET @@system_versioning_alter_history = 1', []);

    // Transactions: TUID virtual column on UID
    await query(`ALTER TABLE Transactions
        ADD COLUMN IF NOT EXISTS \`TUID\` varchar(48)
        GENERATED ALWAYS AS (
            lcase(concat_ws('-',
                hex(substr(\`UID\`, 5, 4)),
                hex(substr(\`UID\`, 3, 2)),
                hex(substr(\`UID\`, 1, 2)),
                hex(substr(\`UID\`, 9, 2)),
                hex(substr(\`UID\`, 11))
            ))
        ) VIRTUAL`, []);

    // TransactionLines: TUID virtual column on UID
    await query(`ALTER TABLE TransactionLines
        ADD COLUMN IF NOT EXISTS \`TUID\` varchar(48)
        GENERATED ALWAYS AS (
            lcase(concat_ws('-',
                hex(substr(\`UID\`, 5, 4)),
                hex(substr(\`UID\`, 3, 2)),
                hex(substr(\`UID\`, 1, 2)),
                hex(substr(\`UID\`, 9, 2)),
                hex(substr(\`UID\`, 11))
            ))
        ) VIRTUAL`, []);

    // TransactionLines: TUIDTransaction virtual column on UIDTransaction (FK display)
    await query(`ALTER TABLE TransactionLines
        ADD COLUMN IF NOT EXISTS \`TUIDTransaction\` varchar(48)
        GENERATED ALWAYS AS (
            lcase(concat_ws('-',
                hex(substr(\`UIDTransaction\`, 5, 4)),
                hex(substr(\`UIDTransaction\`, 3, 2)),
                hex(substr(\`UIDTransaction\`, 1, 2)),
                hex(substr(\`UIDTransaction\`, 9, 2)),
                hex(substr(\`UIDTransaction\`, 11))
            ))
        ) VIRTUAL`, []);

    await query('SET @@system_versioning_alter_history = 0', []);
};