Prepare a federation member export

// Hosted Widget server. Provider-neutral CSV; adapt the contract to the recipient's specification.
const MANDATE_ID = 'YOUR_MANDATE_ID';
const CLUB_NUMBER = 'YOUR_CLUB_NUMBER';
const TEST_CONTACT_IDS: string[] = [];
const PAGE_SIZE = 100;
const MAX_MEMBERS = 5000;
const DELIMITER = ';';
const COLUMNS = [
    { key: 'clubNumber', title: 'club_number' },
    { key: 'memberNumber', title: 'member_number' },
    { key: 'firstName', title: 'first_name' },
    { key: 'lastName', title: 'last_name' },
    { key: 'entryDate', title: 'entry_date' },
];
type Row = {
    clubNumber: string;
    memberNumber: string;
    firstName: string;
    lastName: string;
    entryDate: string;
    birthdate?: string;
};

function id(value: unknown) {
    const result =
        value && typeof value === 'object' && '$oid' in value ? String(value.$oid) : String(value);
    if (!/^[a-f0-9]{24}$/i.test(result))
        throw new Error('Configure valid full mandate and test contact IDs.');
    return result;
}
function day(value: unknown) {
    if (
        typeof value !== 'string' ||
        !/^\d{4}-\d{2}-\d{2}$/.test(value) ||
        !Number.isFinite(Date.parse(`${value}T00:00:00Z`)) ||
        new Date(`${value}T00:00:00Z`).toISOString().slice(0, 10) !== value
    )
        throw new Error('Choose a real YYYY-MM-DD reporting date.');
    return value;
}
function storedDay(value: unknown) {
    if (value === null) return '';
    const iso = value instanceof Date ? value.toISOString() : value;
    if (typeof iso !== 'string' || !/^\d{4}-\d{2}-\d{2}T00:00:00\.000Z$/.test(iso))
        throw new Error(
            'A required date field is unavailable or has an unexpected representation. Review field permissions.',
        );
    return day(iso.slice(0, 10));
}
function text(value: unknown) {
    if (typeof value !== 'string')
        throw new Error('A required text field is unavailable. Review member field permissions.');
    return value;
}
function unsafeCell(value: string) {
    return /^[\s]*[=+@-]|^[\t\r\n]/.test(value);
}
function csvCell(value: string) {
    return `"${value.replace(/"/g, '""')}"`;
}

export async function prepareExport(raw: unknown) {
    if (!raw || typeof raw !== 'object') throw new Error('Choose the reporting date and fields.');
    const input = raw as Record<string, unknown>;
    const asOf = day(input.asOf);
    if (typeof input.includeBirthdate !== 'boolean')
        throw new Error('Choose whether birthdates are required.');
    const includeBirthdate = input.includeBirthdate;
    if (!CLUB_NUMBER.trim() || CLUB_NUMBER.trim() === 'YOUR_CLUB_NUMBER' || unsafeCell(CLUB_NUMBER))
        throw new Error('Configure your federation club number before exporting.');
    const mandateId = id(MANDATE_ID);
    const fixtureIds = [...new Set(TEST_CONTACT_IDS.map(id))];
    if (fixtureIds.length > 1000) throw new Error('Use at most 1,000 test contact IDs.');
    const contacts: any[] = [];
    const seen = new Set<string>();
    const append = (contact: any) => {
        const contactId = id(contact?._id);
        if (fixtureIds.length && !fixtureIds.includes(contactId))
            throw new Error('A returned member is outside the test selection. No export was returned.');
        if (seen.has(contactId))
            throw new Error('The list changed while scanning. No partial export was prepared.');
        if (!Array.isArray(contact.types) || !contact.types.includes('member'))
            throw new Error('A returned contact is not a member.');
        seen.add(contactId);
        contacts.push(contact);
        if (contacts.length > MAX_MEMBERS)
            throw new Error('Split this export before scanning more than 5,000 records.');
    };
    let complete = false;
    for (let offset = 0; offset <= MAX_MEMBERS; offset += PAGE_SIZE) {
        const page = await api.crm.contacts.listContacts({
            mandateId,
            types: ['member'],
            sort: { _id: 'asc' },
            offset,
            limit: PAGE_SIZE,
            ...(fixtureIds.length ? { selection: { ids: fixtureIds } } : {}),
        });
        if (!Array.isArray(page.contacts) || page.contacts.length > PAGE_SIZE)
            throw new Error('Unexpected member-list response.');
        page.contacts.forEach(append);
        if (page.contacts.length < PAGE_SIZE) {
            complete = true;
            break;
        }
    }
    if (!complete) throw new Error('The scan did not finish. No partial export was prepared.');
    if (fixtureIds.length && seen.size !== fixtureIds.length)
        throw new Error('Some test contacts were not returned. Check the IDs, mandate, and member type.');
    const rows: Row[] = [];
    const issues: { memberNumber: string; name: string; problems: string[] }[] = [];
    let outsideDate = 0,
        institutions = 0;
    for (const contact of contacts) {
        if (!contact.member || !Object.prototype.hasOwnProperty.call(contact, 'person'))
            throw new Error('Membership or person fields are unavailable.');
        const entryDate = storedDay(contact.member.entryAt);
        const exitDate = storedDay(contact.member.exitAt);
        // Missing starts and reversed intervals cannot establish whether someone belongs in this export.
        const intervalProblem = !entryDate
            ? 'Membership start is missing'
            : exitDate && exitDate < entryDate
              ? 'Membership exit precedes entry'
              : '';
        if (!intervalProblem && (entryDate > asOf || (exitDate && exitDate < asOf))) {
            outsideDate++;
            continue;
        }
        if (contact.person === null) {
            institutions++;
            continue;
        }
        const row: Row = {
            clubNumber: CLUB_NUMBER,
            memberNumber: text(contact.contactNumbers?.member),
            firstName: text(contact.person.firstName),
            lastName: text(contact.person.lastName),
            entryDate,
        };
        if (includeBirthdate) row.birthdate = storedDay(contact.person.birthdate);
        const problems = intervalProblem ? [intervalProblem] : [];
        for (const [field, value] of Object.entries(row)) {
            if (!value.trim()) problems.push(`${field} is empty`);
            if (unsafeCell(value)) problems.push(`${field} starts like a spreadsheet formula`);
            if (/[\r\n\t]/.test(value)) problems.push(`${field} contains a line break or tab`);
        }
        if (includeBirthdate && row.birthdate && row.birthdate > asOf)
            problems.push('Birthdate is after the reporting date');
        if (problems.length)
            issues.push({
                memberNumber: row.memberNumber,
                name: [row.firstName, row.lastName].filter(Boolean).join(' '),
                problems,
            });
        else rows.push(row);
    }
    const counts = new Map<string, number>();
    for (const row of rows) counts.set(row.memberNumber, (counts.get(row.memberNumber) || 0) + 1);
    for (const row of rows)
        if (counts.get(row.memberNumber)! > 1)
            issues.push({
                memberNumber: row.memberNumber,
                name: `${row.firstName} ${row.lastName}`,
                problems: ['Member number appears more than once'],
            });
    const ready = rows.filter((row) => counts.get(row.memberNumber) === 1);
    const columns = includeBirthdate ? [...COLUMNS, { key: 'birthdate', title: 'birthdate' }] : COLUMNS;
    ready.sort((a, b) => a.memberNumber.localeCompare(b.memberNumber));
    // Refuse a partial file. The issue list and exclusions remain visible for review.
    const csv = issues.length
        ? null
        : [
              columns.map((column) => csvCell(column.title)).join(DELIMITER),
              ...ready.map((row) =>
                  columns.map((column) => csvCell(row[column.key as keyof Row] ?? '')).join(DELIMITER),
              ),
          ].join('\r\n') + '\r\n';
    return {
        asOf,
        includeBirthdate,
        clubNumber: CLUB_NUMBER,
        scanned: contacts.length,
        eligible: ready.length,
        outsideDate,
        institutions,
        issues,
        columns,
        rows: ready.slice(0, 20),
        csv,
        filename: `member-export-${asOf}.csv`,
        checkedAt: new Date().toISOString(),
        fixtureOnly: TEST_CONTACT_IDS.length > 0,
    };
}