Source: config/migrations/20260420-searchindex.js

/**
 * Migration: Replace EmailIndex column with a generic SearchIndex table
 *
 * The previous approach stored a space-separated string of all emails in a
 * VARCHAR(512) column on Member and queried it with LIKE '%email%'.  That
 * cannot use a B-tree index (leading wildcard) and cannot be extended to
 * other object types or index categories without adding more columns.
 *
 * This migration:
 *   1. Drops the EmailIndex column if it was added by the dev-only migration
 *      20260419-member-emailindex (safe no-op in production where it never ran).
 *   2. Creates a generic SearchIndex table with one row per indexed value,
 *      scoped by object type and index category.
 *   3. Backfills all person/extern email entries from Member.Data.
 *
 * Table design:
 *   SearchIndex (UID, ObjType, IndexType, Value)
 *
 *   UID       – Member.UID (= ObjectBase.UID for person/extern)
 *   ObjType   – object type: 'person', 'extern', 'group', …
 *   IndexType – index category: 'email', 'phone', …
 *   Value     – lowercased, trimmed indexed value
 *
 * The composite index idx_lookup (IndexType, ObjType, Value) covers the
 * primary search pattern:
 *   WHERE IndexType = ? AND ObjType IN (…) AND Value = ?
 * which is a point lookup — no LIKE required.
 */

export const id = '20260420-member-searchindex';

export const name = 'Replace EmailIndex column with generic SearchIndex table';

/**
 * @param {{ query: Function }} api
 */
export const migrate = async ({ query }) => {
    // 1. Remove the old denormalized column (dev-only, never reached production)
    await query(`ALTER TABLE Member DROP COLUMN IF EXISTS EmailIndex`, []);

    // 2. Create the generic search index table
    await query(`
        CREATE TABLE IF NOT EXISTS SearchIndex (
            UID        BINARY(16)   NOT NULL,
            ObjType    VARCHAR(64)  NOT NULL,
            IndexType  VARCHAR(64)  NOT NULL,
            Value      VARCHAR(254) NOT NULL,
            PRIMARY KEY (UID, IndexType, Value(100)),
            INDEX idx_lookup (IndexType, ObjType, Value(100))
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `, []);

    // 3. Backfill email entries for all person/extern rows
    //    Member.UID = ObjectBase.UIDBelongsTo = ObjectBase.UID for person/extern
    const rows = await query(
        `SELECT M.UID, OBase.Type, JSON_EXTRACT(M.Data, '$.email[*].email') AS emails
         FROM Member AS M
             INNER JOIN ObjectBase AS OBase ON OBase.UIDBelongsTo = M.UID
         WHERE OBase.Type IN ('person', 'extern')
           AND JSON_EXTRACT(M.Data, '$.email[*].email') IS NOT NULL`,
        []
    );

    let inserted = 0;
    for (const row of rows) {
        try {
            const parsed = typeof row.emails === 'string' ? JSON.parse(row.emails) : row.emails;
            if (!Array.isArray(parsed) || parsed.length === 0) continue;

            for (const email of parsed) {
                const value = typeof email === 'string' ? email.toLowerCase().trim() : null;
                if (!value) continue;
                await query(
                    `INSERT IGNORE INTO SearchIndex (UID, ObjType, IndexType, Value) VALUES (?, ?, 'email', ?)`,
                    [row.UID, row.Type, value]
                );
                inserted++;
            }
        } catch {
            // Skip rows with malformed JSON — non-critical
        }
    }

    console.log(`[migration] SearchIndex backfilled with ${inserted} email entries from ${rows.length} objects`);
};