Source: Router/family/controller.js

// (types imported above)
/**
 * Family Controller
 * 
 * Contains all the business logic for family operations:
 * - Creating or changing family memberships for members
 * - Updating family data (address, contact information, fee settings)
 * - Removing members from families
 * - Retrieving family information (current and historical)
 * - Bulk family data retrieval
 * - Getting family listings for groups
 * - Family name suggestions based on phonetic matching
 * 
 * Families are organizational units that group related members together for
 * fee calculation, address management, and administrative purposes.
 */

// @ts-check
/**
 * @import {ExpressRequestAuthorized, ExpressResponse} from './../../types.js'
 */



import { query, pool, UUID2hex, HEX2uuid } from '@commtool/sql-query';
import { parseTimestampToSeconds } from '../../utils/parseTimestamp.js';
import { queueAdd } from '../../tree/treeQueue/treeQueue.js';
import { addUpdateEntry } from '../../server.ws.js';
import { addFamilyUpdate, familyAddress } from '../../tree/familyAddress.js';
import { getFamilyFees } from '../../utils/getFamilyFee.js';
import { isObjectAdmin, isObjectVisible, isAdmin } from '../../utils/authChecks.js';
import paginateList from '../../utils/paginateList.js';
import { doubleMetaphone } from 'double-metaphone';
import { requestUpdateLogger, errorLoggerRead, errorLoggerUpdate } from '../../utils/requestLogger.js';
import { getConfig } from '../../utils/compileTemplates.js';
import { publishEvent } from '../../utils/events.js';
import { isValidUID } from '../../utils/UUIDs.js';

/**
 * Create or change family membership (PUT /:famMember/:member)
 * 
 * Handles the creation or assignment of a family for a member.
 * Can create new families or assign members to existing families.
 * Supports rebate mode for family fee calculations.
 * @param {ExpressRequestAuthorized} req - Express request object
 * @param {ExpressResponse} res - Express response object
 */
export const createOrChangeFamily = async (req, res) => {
    try {
        let rebate = false;
        if (req.query.rebate) {
            rebate = true;
        }

        let UIDfamMember = UUID2hex(req.params.famMember);
        const UIDperson = UUID2hex(req.params.member);
        
        if (!UIDfamMember) {
            UIDfamMember = UIDperson;
        }

        // Validate member exists
        const result = await query(`SELECT Member.Data, ObjectBase.Type, Member.Display 
            FROM Member INNER JOIN ObjectBase ON (ObjectBase.UID=Member.UID) 
            WHERE Member.UID=?`, [UIDperson]);

        if (result && result.length === 0) {
            res.json({ success: false, message: 'invalid member UID' });
            return;
        }

        // Check permissions
        if (!isObjectAdmin(req, UIDperson)) {
            res.json({ success: false, message: 'user has no admin rights for this member' });
            return;
        }
        if (!isObjectVisible(req, UIDfamMember)) {
            res.json({ success: false, message: 'user has no visibility rights for this family member' });
            return;
        }

        const personDisplay = result[0].Display;

        // Find current family
        const resultOld = await query(`SELECT Member.UID AS UIDfamily 
            FROM Links INNER JOIN Member
            ON (Member.UID=Links.UIDTarget AND Links.Type IN ('family','familyFees') )
            INNER JOIN Links AS FamLinks ON (FamLinks.UIDTarget= Member.UID AND FamLinks.Type IN ('family','familyFees'))
            WHERE Links.UID=?`, [UIDperson]);

        let oldFamily;
        if (resultOld.length > 0 && UIDfamMember.compare(UIDperson) !== 0) {
            oldFamily = resultOld[0].UIDfamily;
        } else {
            oldFamily = null;
        }

        // Find or create new family
        const resultNew = await query(`SELECT Member.UID AS UIDfamily, Member.Data
            FROM Links INNER JOIN Member
            ON (Member.UID=Links.UIDTarget AND Links.Type IN ('family','familyFees'))
            WHERE Links.UID=? OR Links.UIDTarget=?  `, [UIDfamMember, UIDfamMember]);

        let newFamily;

        if (resultNew.length > 0) {
            newFamily = resultNew[0].UIDfamily;
        } else {
            // Create new family for this user
            [{ UID: newFamily }] = await query(`SELECT UIDV1() AS UID`,[]);
            await query(
                `INSERT INTO Member  (UID,Display,SortName,FullTextIndex,Data) VALUES(?,?,'','',?)`,
                [newFamily, 'family: ' + personDisplay, JSON.stringify({ feeAddress: { UID: HEX2uuid(UIDperson), Display: personDisplay, type: 'person' } })]
            );
            await query(`INSERT IGNORE INTO Links (UID,Type,UIDTarget) VALUES(?,?,?)`, [UIDperson, rebate ? 'familyFees' : 'family', newFamily]);
        }

        // Handle family link migration
        if (oldFamily === null) {
            // Add family link if not migrated by tree queue
            await query(`INSERT IGNORE INTO Links (UID,Type,UIDTarget) VALUES(?,?,?)`, [UIDperson, rebate ? 'familyFees' : 'family', newFamily]);
        }

        // Queue family fee recalculation
        queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), rebate ? 'familyB' : 'family', UIDperson, UIDperson, oldFamily, newFamily);
        const newData = await addFamilyUpdate(UIDperson, newFamily, req.session.root);

        addUpdateEntry(req.params.member, { data: newData });
        res.json({ success: true, data: newData });
    } catch (e) {
        errorLoggerUpdate(e);
        res.status(500).json({ success: false, message: 'Internal server error' });
    }
};

/**
 * Update family data (POST /:UID)
 * 
 * Updates family information including address, contact details, accounts, and fee address.
 * Handles both family-level updates and member-specific updates.
 */
export const updateFamily = async (req, res) => {
    try {
        if (req.params.UID === 'families') {
            // This will be handled by the bulk families endpoint
            return;
        }

        const UID = UUID2hex(req.params.UID);
        const result = await query(`SELECT Member.UID, Member.Data FROM Member
            INNER JOIN Links ON (Links.UIDTarget =Member.UID AND Links.Type IN('family','familyFees'))
            WHERE Links.UID=? OR Links.UIDTarget=?
            `, [UID, UID], { cast: ['json'] });

        if (result.length === 0) {
            res.json({ success: false, message: 'no family found for this UID' });
            return;
        }

        const UIDfamily = result[0].UID;
        const Data = {};
        let addressChanged = false;

        // Handle address updates
        if (req.body.address) {
            Data.address = { type: 'family', ...req.body.address };
            addressChanged = true;
        }

        // Handle email updates
        if (req.body.email) {
            Data.email = { type: 'family', email: req.body.email };
            addressChanged = true;
        }

        // Handle phone updates
        if (req.body.phone) {
            Data.phone = { type: 'family', number: req.body.phone };
            addressChanged = true;
        }

        // Handle account updates
        if (req.body.accounts) {
            Data.accounts = req.body.accounts;
            addressChanged = true;
        }

        // Handle fee address updates
        if (req.body.feeAddress) {
            const famData = result[0].Data;
            famData.feeAddress = req.body.feeAddress;
            await query(`
                Update Member SET Member.Data=? WHERE Member.UID=?
                `, [JSON.stringify(famData), UID]);
            publishEvent(`/change/family/${HEX2uuid(UIDfamily)}`, {
                organization: req.session.root,
                data: { feeAddress: req.body.feeAddress }
            });
        }

        // Process address changes
        if (addressChanged) {
            familyAddress(/** @type {import('../../tree/familyAddress.js').FamilyMemberObject} */ ({ UID: UID, Type: 'family', Data: Data }), req.session.root);
        }

        res.json({ success: true });
    } catch (e) {
        errorLoggerUpdate(e);
        res.status(500).json({ success: false, message: 'Internal server error' });
    }
};

/**
 * Remove member from family (DELETE /:UIDmember)
 * 
 * Removes a member from their current family and creates a new family for them.
 * Supports rebate mode for fee calculations.
 * @param {ExpressRequestAuthorized} req - Express request object
 * @param {ExpressResponse} res - Express response object       
 */
export const removeMemberFromFamily = async (req, res) => {
    try {
        let rebate = false;
        if (req.query.rebate) {
            rebate = true;
        }

        const UIDmember = UUID2hex(req.params.UIDmember);
        
        // Find current family
        const result = await query(`SELECT Member.UID FROM Member
            INNER JOIN Links ON (Links.UIDTarget =Member.UID AND Links.Type IN ('family','familyFees'))
            WHERE  Links.UID=? 
            `, [UIDmember]);

        const oldFamily = result[0];
        if (result.length === 0) {
            res.json({ success: false, message: 'no family found for this UID' });
            return;
        }

        // Validate member exists
        const resultP = await query(`SELECT Member.UID,Member.Data, ObjectBase.Type, Member.Display FROM Member 
                INNER JOIN ObjectBase ON (ObjectBase.UID=Member.UID) WHERE Member.UID=?`, [UIDmember]);
        
        if (resultP.length === 0) {
            res.json({ success: false, message: 'invalid member UID' });
            return;
        }

        const person = resultP[0];

        // Remove user from old family
        queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), rebate ? 'familyB' : 'family', UIDmember, UIDmember, oldFamily.UID, null);

        // Create new family for this user
        const [{ UID: UIDfamily }] = await query(`SELECT UIDV1() AS UID`, []);
        await query(`INSERT INTO Member  (UID,Display,SortName,FullTextIndex,Data) VALUES(?,?,'','',?)`,
            [UIDfamily, 'family: ' + person.Display, JSON.stringify({ feeAddress: { UID: HEX2uuid(person.UID), Display: person.Display, type: 'person' } })]
        );
        await query(`INSERT IGNORE INTO Links (UID,Type,UIDTarget) VALUES(?,?,?)`, [person.UID, rebate ? 'familyFees' : 'family', UIDfamily]);
        queueAdd(UUID2hex(req.session.root), UUID2hex(req.session.user), 'family', person.UID, person.UID, null, UIDfamily);

        res.json({ success: true });
    } catch (e) {
        errorLoggerUpdate(e);
        res.status(500).json({ success: false, message: 'Internal server error' });
    }
};

/**
 * Get family information (GET /:UID)
 * 
 * Retrieves family data including current information or historical data for a specific year.
 * Supports fee calculation and historical data retrieval.
 * @param {ExpressRequestAuthorized} req - Express request object
 * @param {ExpressResponse} res - Express response object
 */
export const getFamily = async (req, res) => {
    try {
        const UID = UUID2hex(req.params.UID);
        if (!UID || !Buffer.isBuffer(UID)) {
            res.json({ success: false, message: 'invalid UID' });
            return;
        }

        if (!req.query.year) {
            // Current data - use data stored in family member object
            const result = await query(`SELECT Member.UID, Member.Data
                FROM Member
                LEFT JOIN ObjectBase ON (ObjectBase.UID=Member.UID)
                INNER JOIN Links ON (Links.UIDTarget =Member.UID AND Links.Type IN ('family','familyFees'))
                WHERE (Links.UID=? OR Links.UIDTarget=?) AND ObjectBase.UID IS NULL
                `, [UID, UID]);

            if (result.length === 0) {
                res.json({ success: false, message: 'no family found for this UID' });
                return;
            }

            const UIDfamily = result[0].UID;
            const data = JSON.parse(result[0].Data);
            res.json({ success: true, result: { UID: HEX2uuid(UIDfamily), ...data } });
        } else {
            // Historical data for specific year
            const config = await getConfig('db', req);
            const timestamp = config.fees.enterTreshold ?
                new Date(`${req.query.year}-${config.fees.enterTreshold}`).valueOf() / 1000
                : new Date(`${req.query.year}-01-01`).valueOf() / 1000;

            const asOf = req.query.timestamp ? `FOR SYSTEM_TIME AS OF FROM_UNIXTIME(${timestamp})` : '';

            const result = await query(`SELECT Member.UID, IF(JSON_VALID(Member.Data),JSON_QUERY(Member.Data,'$.feeAddress'),NULL) AS feeAddress
                FROM Member
                LEFT JOIN ObjectBase ${asOf} ON (ObjectBase.UID=Member.UID)
                INNER JOIN Links ON (Links.UIDTarget =Member.UID AND Links.Type IN ('family','familyFees'))
                WHERE (Links.UID=? OR Links.UIDTarget=?) AND ObjectBase.UID IS NULL
            `, [UID, UID], { log: false, cast: ['json'] });

            if (result.length === 0) {
                res.json({ success: false, message: 'no family found for this UID' });
                return;
            }

            const familyUID = result[0].UID;
            const fees = await getFamilyFees(familyUID, req.query.year, config.fees);
            res.json({ success: true, result: { UID: HEX2uuid(familyUID), fees: fees, feeAddress: result[0].feeAddress } });
        }
    } catch (e) {
        errorLoggerRead(e);
        res.status(500).json({ success: false, message: 'Internal server error' });
    }
};

/**
 * Bulk family retrieval (POST /families)
 * 
 * Retrieves family information for multiple UIDs at once.
 * Supports both current and historical data retrieval.
 */
export const getBulkFamilies = async (req, res) => {
    try {
        let UIDs = Array.isArray(req.body) ? req.body.filter(UID => isValidUID(UID)).map(UID => UUID2hex(UID)) : [];

        if (!UIDs || UIDs.length === 0) {
            res.json({ success: false, message: 'invalid UIDs' });
            return;
        }

        let visibleSql = '';
        if (await isAdmin(req.session)) {
            const user = await query(`SELECT Links.UID AS UID FROM Links
                INNER JOIN ObjectBase ON (ObjectBase.UID=Links.UID)
                WHERE UIDTarget=? AND Links.Type='memberSys' AND ObjectBase.Type='extern'`,
                [UUID2hex(req.session.root)], {  log: false, cast: ['UUID'] });
            visibleSql = ` AND Visible.UIDUser=U_UUID2BIN('${user[0].UID}')`;
        } else {
            visibleSql = ` AND Visible.UIDUser=U_UUID2BIN('${req.session.user}') AND Visible.Type='changeable' `;
        }

        if (!req.query.year) {
            // Current data
            const result = await query(`SELECT Member.UID, Member.Data
                FROM Member
                LEFT JOIN ObjectBase ON (ObjectBase.UID=Member.UID)
                INNER JOIN Links ON (Links.UIDTarget =Member.UID AND Links.Type IN ('family','familyFees'))
                INNER JOIN  Links AS FamLinks ON (FamLinks.UIDTarget=Member.UID)
                INNER JOIN Visible ON (Visible.UID= FamLinks.UID ${visibleSql})
                WHERE (Links.UID IN(?) OR Links.UIDTarget IN(?)) AND ObjectBase.UID IS NULL
                GROUP BY Member.UID
                `, [UIDs, UIDs], { cast: ['UUID', 'json'], log: true });

            if (result.length === 0) {
                res.json({ success: false, message: 'no families found for these UIDs' });
                return;
            }
            res.json({ success: true, result });
        } else {
            // Historical data
            const config = await getConfig('db', req);
            const timestamp = config.fees.enterTreshold ?
                new Date(`${req.query.year}-${config.fees.enterTreshold}`).valueOf() / 1000
                : new Date(`${req.query.year}-01-01`).valueOf() / 1000;

            const asOf = req.query.timestamp ? `FOR SYSTEM_TIME AS OF FROM_UNIXTIME(${timestamp})` : '';

            const result = await query(`SELECT Member.UID,  IF(JSON_VALID(Member.Data),JSON_QUERY(Member.Data,'$.feeAddress'),NULL) AS feeAddress
                FROM Member
                LEFT JOIN ObjectBase ${asOf} ON (ObjectBase.UID=Member.UID)
                INNER JOIN Links  ON (Links.UIDTarget =Member.UID AND Links.Type IN ('family','familyFees'))
                INNER JOIN  Links AS FamLinks ON (FamLinks.UIDTarget=Member.UID)
                INNER JOIN Visible ON (Visible.UID= FamLinks.UID ${visibleSql})
                WHERE (Links.UID IN (?) OR Links.UIDTarget IN (?)) AND ObjectBase.UID IS dc logs -fNULL
                GROUP BY Member.UID
            `, [UIDs, UIDs], { log: true });

            if (result.length === 0) {
                res.json({ success: false, message: 'no family found for this UIDs' });
                return;
            }

            const fees = await Promise.all(result.map(f => (
                new Promise((resolve, error) => {
                    getFamilyFees(f.UID, req.query.year, config.fees)
                        .then((fees) => {
                            resolve({ UID: HEX2uuid(f.UID), Data: { fees: fees, feeAddress: f.feeAddress } });
                        });
                })
            )));
            res.json({ success: true, result: fees });
        }
    } catch (e) {
        errorLoggerRead(e);
        res.status(500).json({ success: false, message: 'Internal server error' });
    }
};

/**
 * Get all families (GET /)
 * 
 * Administrative endpoint to retrieve all families.
 * Requires admin privileges.
 */
export const getAllFamilies = async (req, res) => {
    try {
        req.params = {};
        const result = await getFamilyListing(req);
        res.json({ success: true, result: result });
    } catch (e) {
        errorLoggerRead(e);
        res.status(500).json({ success: false, message: 'Internal server error' });
    }
};

/**
 * Get family listing for a group (GET /list/:group)
 * 
 * Retrieves families associated with a specific group.
 * Supports pagination and various filtering options.
 */
export const getFamilyListForGroup = async (req, res) => {
    try {
        const result = await getFamilyListing(req);
        res.json({ success: true, result: result });
    } catch (e) {
        errorLoggerRead(e);
        res.status(500).json({ success: false, message: 'Internal server error' });
    }
};

/**
 * Get paginated family listing (GET /list/:group with __page parameter)
 * 
 * Returns paginated family data for a group.
 */
export const getPaginatedFamilyList = async (req, res) => {
    try {
        res.json(await paginateList(req, getFamilyListing));
    } catch (e) {
        errorLoggerRead(e);
        res.status(500).json({ success: false, message: 'Internal server error' });
    }
};

/**
 * Helper function to get family listing
 * 
 * Core function that builds family listings with various filtering and data options.
 * Supports historical data, person data inclusion, fee information, and visibility filtering.
 */
export const getFamilyListing = async (req) => {
    try {
        let visibleSql = '';
        let dataFields = '';
        const timestamp = parseTimestampToSeconds(req.query.timestamp ? String(req.query.timestamp) : undefined);
        let groupBy = req.query.distinct ? 'GROUP BY Member.UID' : '';

        let asOf = timestamp ? `FOR SYSTEM_TIME AS OF FROM_UNIXTIME(${timestamp})` : '';
        let sinceWhere = '';

        // Set up visibility restrictions
        if (await isAdmin(req.session)) {
            const user = await query(`SELECT Links.UID AS UID FROM Links
                INNER JOIN ObjectBase ON (ObjectBase.UID=Links.UID)
                WHERE UIDTarget=? AND Links.Type='memberSys' AND ObjectBase.Type='extern'`,
                [UUID2hex(req.session.root)], { log: false, cast: ['UUID'] });
            visibleSql = ` AND Visible.UIDUser=U_UUID2BIN('${user[0].UID}')`;
        } else {
            visibleSql = ` AND Visible.UIDUser=U_UUID2BIN('${req.session.user}') AND Visible.Type='changeable' `;
        }

        // Handle person data fields
        if (req.query.personData) {
            if (req.query.personData === 'all') {
                dataFields = `,person.Data AS DataPerson`;
            } else {
                let fields = [];
                try {
                    fields = req.query.personData ? JSON.parse(String(req.query.personData)) : null;
                } catch (e) {
                    fields[0] = [req.query.personData];
                }
                for (const field of fields) {
                    if (!field.query) {
                        dataFields += `,JSON_VALUE(person.Data,${pool.escape(field.path)}) AS ${pool.escape(field.alias)}`;
                    } else {
                        dataFields += `,JSON_QUERY(person.Data,${pool.escape(field.path)}) AS ${pool.escape(field.alias)}`;
                    }
                }
            }
        }

        if (req.query.fees && !timestamp) {
            dataFields += ',Member.Data';
        }

        // Handle family link types
        let type = ['familyFees', 'family'];
        if (req.query.type) {
            try {
                type = req.query.type ? JSON.parse(String(req.query.type)) : type;
            } catch (e) {
                type = [req.query.type];
            }
        }

        // Handle time-based filtering
        if (req.query.since) {
            const since = parseTimestampToSeconds(req.query.since);
            sinceWhere = ` AND pLinks.validFrom > FROM_UNIXTIME(${since})  AND pLinks.validUntil > NOW()`;
            asOf = `FOR SYSTEM_TIME BETWEEN  FROM_UNIXTIME(${Math.max(since, 1672527600)}) AND NOW()`;
            groupBy = 'GROUP BY Member.UID';
        }

        // Security: filter allowed types
        type = type.filter(el => (['familyFees', 'family'].includes(el)));

        // Execute main query
        const result = await query(`
        SELECT 
            Member.UID AS UIDfamily,JSON_VALUE(Member.Data,'$.fees[1].Display') AS DisplayFamily, 
            JSON_VALUE(Member.Data,'$.fees[1].Title') AS Title, IF(JSON_VALID(Member.Data),JSON_QUERY(Member.Data,'$.feeAddress'),NULL) AS feeAddress,
            person.Display AS DisplayPerson,person.UID AS UIDperson,
            ObjectBase.Title AS TitlePerson, ObjectBase.dindex, aLinks.UIDTarget AS UIDgroup, Visible.Type AS visibility ${dataFields}
        
        FROM  Member
        INNER JOIN Links ${asOf}  AS pLinks ON (pLinks.UIDTarget=Member.UID AND pLinks.Type IN(?)) 
        INNER JOIN Member AS person ON (pLinks.UID=person.UID)
        INNER JOIN ObjectBase ON (ObjectBase.UID=person.UID AND ObjectBase.Type='person')
        INNER JOIN Visible ON (Visible.UID=ObjectBase.UID)
        INNER JOIN Links ${asOf}  AS mLinks ON (mLinks.UID=pLinks.UID AND mLinks.Type IN ('member','memberA')) 
        INNER JOIN Links ${asOf}  AS aLinks ON (aLinks.UID=ObjectBase.UID AND aLinks.Type ='memberA') 
        ${visibleSql}
        WHERE mLinks.UIDTarget=? ${sinceWhere}
        ${groupBy}
        ORDER BY person.SortName
        `,
            [type, UUID2hex(req.params.group)],
            {
                log: true,
                cast: ['json', 'UUID']
            });

        if (req.query.fees) {
            return result.map(r => ({ ...r, Data: undefined, fees: r.Data.fees }));
        } else {
            return result.map(r => ({ ...r, Data: undefined }));
        }
    } catch (e) {
        errorLoggerRead(e);
        throw e;
    }
};

/**
 * Suggest families by last name (GET /suggest/:lastName)
 * 
 * Provides family suggestions based on phonetic matching of last names.
 * Uses double metaphone algorithm for fuzzy matching and scoring.
 * @param {ExpressRequestAuthorized} req - Express request object
 * @param {ExpressResponse} res - Express response object
 */
export const suggestFamiliesByLastName = async (req, res) => {
    try {
        const [firstLast] = req.params.lastName.split(/\s/);
        const [phonLast0, phonLast1] = doubleMetaphone(firstLast.replace('ß', 'ss'));
        let visibleSql;
        const config = await getConfig('db', req);

        // Set up visibility restrictions
        if (await isAdmin(req.session)) {
            const user = await query(`SELECT Links.UID AS UID FROM Links
                INNER JOIN ObjectBase ON (ObjectBase.UID=Links.UID)
                WHERE UIDTarget=? AND Links.Type='memberSys' AND ObjectBase.Type='extern'`,
                [UUID2hex(req.session.root)], { log: false, cast: ['UUID'] });
            visibleSql = ` AND Visible.UIDUser=U_UUID2BIN('${user[0].UID}')`;
        } else {
            visibleSql = ` AND Visible.UIDUser=U_UUID2BIN('${req.session.user}') `;
        }

        // Get potential candidates using phonetic search
        const result = await query(`SELECT Member.UID,Member.Display,ObjectBase.Title, Member.PhonetikIndex,Family.Display AS Family,Family.UID AS UIDfamily,
            JSON_VALUE(Member.Data,'$.lastName') AS lastName,JSON_VALUE(Member.Data,'$.died') AS died, 
            JSON_QUERY(Member.Data,'$.address') AS address, COUNT(Member.UID) AS score 
            FROM Member
            INNER JOIN ObjectBase ON (ObjectBase.UIDBelongsTo=Member.UID)
            INNER JOIN Visible ON (Visible.UID=ObjectBase.UID)
            INNER JOIN (Links AS linkF INNER JOIN Member AS Family ON (Family.UID=linkF.UIDTarget) AND linkF.Type IN ('family','familyFees')) 
            ON (linkF.UID=Member.UID )

            ${visibleSql}
            WHERE  (Member.PhonetikIndex REGEXP ? OR Member.PhonetikIndex REGEXP ?)
            AND ObjectBase.Type IN ('extern','person','job','guest')
            GROUP BY Family.UID
            `,
            [`\\b${phonLast0}\\b`, `\\b${phonLast1}\\b`],
            { cast: ['UUID', 'json'], log: true });

        // Score and filter results
        const result2 = result.filter(p => {
            // Process address to prioritize family address
            if (p.address && p.address.length > 0) {
                let myAddress = p.address.find(a => a.Type === 'family');
                if (!myAddress) {
                    myAddress = p.address[0];
                }
                p.address = { ...myAddress };
            }

            // Filter invalid entries
            if (p.died) {
                return false;
            }

            // Direct last name match
            if (p.lastName.localeCompare(req.params.lastName, config.CountryCode.toLowerCase(), { sensitivity: "base" }) === 0) {
                p.score += 100;
                return true;
            }

            // Partial last name match (for combined last names)
            if (p.lastName.match(new RegExp(`\\W?${req.params.lastName}\\W?`)) || req.params.lastName.match(new RegExp(`\\W?${p.lastName}\\W?`))) {
                p.score += 80;
                return true;
            }

            // Phonetic matching
            const phonems = p.PhonetikIndex.split(' ').filter((p, i) => (i > 1));
            for (let i = 0; i < phonems.length; i = i + 2) {
                let myScore = p.score;
                if (phonems[i] === phonLast0) {
                    myScore += 2;
                }
                if (phonems[i + 1] === phonLast1) {
                    myScore += 2;
                }
                p.score += myScore;
                if (myScore > 0) {
                    return true;
                }
            }
            return false;
        });

        const sorted = result2.sort((p1, p2) => p1.score > p2.score ? -1 : p1.score === p2.score ? 0 : 1);


        res.json({ success: true, result: sorted });
    } catch (e) {
        errorLoggerRead(e);
        res.status(500).json({ success: false, message: 'Internal server error' });
    }
};