Source: config/migrations/20260713-swap-member-geo-coordinates.js

/**
 * Migration: Swap lat/lng in Member.Geo for all rows
 *
 * Der Code hat bisher POINT(lat lng) geschrieben, MariaDB/MySQL erwartet aber
 * POINT(lng lat). Alle bestehenden Geo-Einträge sind daher falsch herum.
 *
 * Diese Migration:
 *   1. Liest jedes Member.Geo (POINT)
 *   2. Schreibt es mit getauschten Koordinaten zurück: POINT(ST_Y, ST_X)
 *   3. Member ist nicht system-versioned, daher kein SET HISTORY nötig
 */

export const id = '20260713-swap-member-geo-coordinates';

export const name = 'Swap lat/lng coordinates in Member.Geo (POINT format)';

/**
 * @param {{ query: Function }} api
 */
export const migrate = async ({ query }) => {
    // Alle Member-Zeilen mit Geo-Wert holen
    const rows = await query(`
        SELECT UID, ST_X(Geo) AS curX, ST_Y(Geo) AS curY
        FROM Member
        WHERE Geo IS NOT NULL
    `, []);

    let updated = 0;
    for (const row of rows) {
        await query(`
            UPDATE Member
            SET Geo = ST_GeomFromText(CONCAT('POINT(', ?, ' ', ?, ')'))
            WHERE UID = ?
        `, [row.curY, row.curX, row.UID]);
        updated++;
    }

    console.log(`  Swapped coordinates for ${updated} Member rows`);
};