Source: config/migrations/20260419-member-emailindex.js

/**
 * Migration: Add EmailIndex column to Member table
 *
 * Adds a dedicated VARCHAR(512) column for email lookups, populated with all
 * lowercased emails from Member.Data.email[].email, space-separated.
 * Same pattern as FullTextIndex / PhonetikIndex — a denormalized search column
 * maintained on person save.
 *
 * Backfills existing rows from the JSON Data field.
 */

export const id = '20260419-member-emailindex';

export const name = 'Add EmailIndex column to Member and backfill from Data.email';

// Superseded by 20260420-searchindex before reaching production — do not apply.
export const disabled = true;

/**
 * @param {{ query: Function }} api
 */
export const migrate = async ({ query }) => {
    // 1. Add column
    await query(`
        ALTER TABLE Member ADD COLUMN IF NOT EXISTS EmailIndex VARCHAR(512) DEFAULT NULL
    `, []);

    // 2. Backfill from JSON Data — extract all email[].email values
    const rows = await query(
        `SELECT UID, JSON_EXTRACT(Data, '$.email[*].email') AS emails FROM Member
         WHERE JSON_EXTRACT(Data, '$.email[*].email') IS NOT NULL`,
        []
    );

    let updated = 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;

            const emailIndex = parsed
                .filter(e => typeof e === 'string' && e.length > 0)
                .map(e => e.toLowerCase().trim())
                .join(' ');

            if (emailIndex) {
                await query(`UPDATE Member SET EmailIndex = ? WHERE UID = ?`, [emailIndex, row.UID]);
                updated++;
            }
        } catch {
            // Skip rows with malformed JSON — non-critical
        }
    }

    console.log(`[migration] EmailIndex backfilled for ${updated} of ${rows.length} rows`);
};