Junction Table Help Needed: Separating Team Schedules into individual 'Shifts'

let settings = input.config({
    title: "Create junction table",
    description: `Running this script will populate a junction table that contains every possible combination of linked
    records from two tables. Both tables must contain a linked field to the junction table being populated.`,
    items: [
        input.config.table("table1", { label: "First table name" }),
        input.config.view("view1", { parentTable:"table1", label: "View to use from the first table" }),
        input.config.table("table2", { label: "Second table name" }),
        input.config.table("junctionTable", {
            label: "Junction table name",
        }),
        input.config.field("firstJunctionField", {
            parentTable: "junctionTable",
            label: "First junction field name",
            description: "Linked record to first table",
        }),
        input.config.field("secondJunctionField", {
            parentTable: "junctionTable",
            label: "Second junction field name",
            description: "Linked record to second table",
        }),
    ],
});

async function createJunction() {
    let {
        table1,
        view1,
        table2,
        junctionTable,
        firstJunctionField,
        secondJunctionField,
    } = settings;

    if (table1 === junctionTable) {
        output.text("First table can't be the same as the junction table.");
        return;
    }
    if (table2 === junctionTable) {
        output.text("Second table can't be the same as the junction table.");
        return;
    }
    if (firstJunctionField === secondJunctionField) {
        output.text("First junction field can't be the same as the second junction field.")
    }
    if (
        firstJunctionField.type !== "multipleRecordLinks" ||
        secondJunctionField.type !== "multipleRecordLinks"
    ) {
        output.text(
            "First and second junction field should be of linked record type."
        );
        return;
    }

    let existing = Object.create(null);
    let toCreate = [];
    let toDelete = [];
    // Airtable limits batch operations to 50 records or fewer.
    let maxRecordsPerCall = 50;

    // Part 1: determine the necessary operations.
    //
    // We don't modify the table contents in this Part in the interest of
    // efficiency. This script may trigger a large number of database
    // modifications, and it's much faster to request that they be done in batches.
    // When we identify a record that should be created or deleted, we add it to
    // the appropriate array so we can batch the operations in Part 2 of the
    // script.

    let query3 = await junctionTable.selectRecordsAsync({
        fields: [firstJunctionField, secondJunctionField],
    });

    for (let record3 of query3.records) {
        let records1 = record3.getCellValue(firstJunctionField);
        let records2 = record3.getCellValue(secondJunctionField);

        // Either field in the junction table may have zero records. That's not
        // expected, so junction records like that should be removed.
        if (!records1 || !records2) {
            toDelete.push(record3);
            continue;
        }

        // Either field in the junction table may reference multiple records.
        // That's not expected, either, so junction records like that should be
        // removed.
        if (records1.length > 1 || records2.length > 1) {
            toDelete.push(record3);
            continue;
        }

        let key = `${records1[0].id}${records2[0].id}`;

        // Keep track of each record in the junction table that describes a unique
        // pair of foreign records. We'll use this to determine whether new records
        // need to be created.
        if (!(key in existing)) {
            existing[key] = record3;

            // If we've already seen a record in the junction table for two foreign
            // records, then the current record is a duplicate, so we should plan
            // to remove it.
        } else {
            toDelete.push(record3);
        }
    }

    let query1 = await view1.selectRecordsAsync();
    let query2 = await table2.selectRecordsAsync();

    for (let recordId1 of query1.recordIds) {
        for (let recordId2 of query2.recordIds) {
            let key = `${recordId1}${recordId2}`;

            // If we didn't see this combination of foreign records when we
            // traversed the junction table, we should plan to create a new record.
            if (!(key in existing)) {
                toCreate.push({
                    fields: {
                        [firstJunctionField.name]: [{ id: recordId1 }],
                        [secondJunctionField.name]: [{ id: recordId2 }],
                    },
                });

                // If we *did* see this combination of foreign records, then we'll
                // remove the corresponding junction record from our data
                // structure. That way, once this loop is complete, the only
                // records that remain in the data structure will be the ones that
                // describe non-existent foreign records.
            } else {
                delete existing[key];
            }
        }
    }

    // If `existing` still has any entries, they are junction records which include
    // non-existent foreign records. We should delete those, too.
    toDelete.push(...Object.values(existing));

    // Part 2: Verify
    //
    // Inform the script's user of the changes to be made and await their
    // confirmation.
    output.markdown(
        `Identified **${toCreate.length}** records in need of creation.`
    );
    output.markdown(
        `Identified **${toDelete.length}** records in need of deletion.`
    );

    let decision = await input.buttonsAsync("Proceed?", ["Yes", "No"]);

    // Part 3: Execute the necessary operations

    if (decision === "No") {
        output.text("Operation cancelled.");
    } else {
        output.text("Applying changes...");

        while (toDelete.length > 0) {
            await junctionTable.deleteRecordsAsync(
                toDelete.slice(0, maxRecordsPerCall)
            );
            toDelete = toDelete.slice(maxRecordsPerCall);
        }

        while (toCreate.length > 0) {
            await junctionTable.createRecordsAsync(
                toCreate.slice(0, maxRecordsPerCall)
            );
            toCreate = toCreate.slice(maxRecordsPerCall);
        }

        output.text("Done");
    }
}

await createJunction();

let config = input.config({
    title: "Expand linked records into one record each",
    description: "For each record in a source view, create one record per linked record found in up to 3 optional linked fields, while also removing invalid, duplicate, and obsolete junction records.",
    items: [
        input.config.table("sourceTable", {
            label: "Source table"
        }),
        input.config.view("sourceView", {
            parentTable: "sourceTable",
            label: "Source view"
        }),
        input.config.table("destinationTable", {
            label: "Destination table"
        }),
        input.config.field("destinationSourceField", {
            parentTable: "destinationTable",
            label: "Destination field linking back to the source record"
        }),
        input.config.field("destinationLinkedField", {
            parentTable: "destinationTable",
            label: "Destination field for the linked record found in the optional fields"
        }),
        input.config.field("destinationFieldNameField", {
            parentTable: "destinationTable",
            label: "Optional text field to store the source field name"
        }),
        input.config.field("optionalLinkedField1", {
            parentTable: "sourceTable",
            label: "Optional linked field 1"
        }),
        input.config.field("optionalLinkedField2", {
            parentTable: "sourceTable",
            label: "Optional linked field 2"
        }),
        input.config.field("optionalLinkedField3", {
            parentTable: "sourceTable",
            label: "Optional linked field 3"
        })
    ]
});

let {
    sourceView,
    destinationTable,
    destinationSourceField,
    destinationLinkedField,
    destinationFieldNameField,
    optionalLinkedField1,
    optionalLinkedField2,
    optionalLinkedField3,
} = config;

let linkedFields = [
    optionalLinkedField1,
    optionalLinkedField2,
    optionalLinkedField3
].filter(Boolean);

if (linkedFields.length === 0) {
    throw new Error("You need to select at least one linked field.");
}

let maxRecordsPerCall = 50;

// This will track all source/linked combinations that SHOULD exist.
let desiredPairs = {};
let toCreate = [];
let toDelete = [];
let existing = {};

// Pull source records from the selected view.
let sourceQuery = await sourceView.selectRecordsAsync({
    fields: linkedFields
});

// Part 1A: Determine all desired source/linked pairs from the source view.
for (let sourceRecord of sourceQuery.records) {
    for (let linkedField of linkedFields) {
        let linkedRecords = sourceRecord.getCellValue(linkedField);

        if (!linkedRecords || linkedRecords.length === 0) {
            continue;
        }

        for (let linkedRecord of linkedRecords) {
            let key = `${sourceRecord.id}___${linkedRecord.id}`;

            // If the same pair appears in multiple linked fields, keep the first one.
            if (!(key in desiredPairs)) {
                let fields = {
                    [destinationSourceField.id]: [{ id: sourceRecord.id }],
                    [destinationLinkedField.id]: [{ id: linkedRecord.id }]
                };

                if (destinationFieldNameField) {
                    fields[destinationFieldNameField.id] = linkedField.name;
                }

                desiredPairs[key] = { fields };
            }
        }
    }
}

// Pull existing junction records from the destination table.
let destinationFields = [destinationSourceField, destinationLinkedField];
if (destinationFieldNameField) {
    destinationFields.push(destinationFieldNameField);
}

let junctionQuery = await destinationTable.selectRecordsAsync({
    fields: destinationFields
});

// Part 1B: Inspect existing junction records and identify invalid/duplicate ones.
for (let junctionRecord of junctionQuery.records) {
    let sourceLinks = junctionRecord.getCellValue(destinationSourceField);
    let linkedLinks = junctionRecord.getCellValue(destinationLinkedField);

    // Missing either side: invalid
    if (!sourceLinks || !linkedLinks || sourceLinks.length === 0 || linkedLinks.length === 0) {
        toDelete.push(junctionRecord);
        continue;
    }

    // Multiple links in either junction field: invalid
    if (sourceLinks.length > 1 || linkedLinks.length > 1) {
        toDelete.push(junctionRecord);
        continue;
    }

    let key = `${sourceLinks[0].id}___${linkedLinks[0].id}`;

    // Keep first valid record for each pair, delete duplicates
    if (!(key in existing)) {
        existing[key] = junctionRecord;
    } else {
        toDelete.push(junctionRecord);
    }
}

// Part 1C: Compare desired pairs to existing pairs.
for (let key of Object.keys(desiredPairs)) {
    if (!(key in existing)) {
        toCreate.push(desiredPairs[key]);
    } else {
        // Pair already exists, so remove from existing.
        // Any leftovers in existing after this are obsolete and should be deleted.
        delete existing[key];
    }
}

// Any remaining existing records represent obsolete pairs.
toDelete.push(...Object.values(existing));

// Part 2: Verify
output.markdown(`Identified **${toCreate.length}** records to create.`);
output.markdown(`Identified **${toDelete.length}** records to delete.`);

let previewLimit = 20;

if (toDelete.length > 0) {
    let preview = toDelete.slice(0, previewLimit).map(record => {
        let sourceLinks = record.getCellValue(destinationSourceField);
        let linkedLinks = record.getCellValue(destinationLinkedField);

        let source = sourceLinks?.[0];
        let linked = linkedLinks?.[0];

        let sourceDisplay = source?.name || source?.id || "null";
        let linkedDisplay = linked?.name || linked?.id || "null";

        let fieldName = destinationFieldNameField
            ? record.getCellValue(destinationFieldNameField) || ""
            : "";

        return `- ${sourceDisplay} → ${linkedDisplay}${fieldName ? ` (${fieldName})` : ""}`;
    });

    output.markdown(`### records to delete (showing ${Math.min(previewLimit, toDelete.length)} of ${toDelete.length})`);
    output.markdown(preview.join("\n"));
} else {
}


let decision = await input.buttonsAsync("Proceed?", ["Yes", "No"]);

// Part 3: Execute
if (decision === "No") {
    output.text("Operation cancelled.");
} else {
    output.text("Applying changes...");

    while (toDelete.length > 0) {
        await destinationTable.deleteRecordsAsync(
            toDelete.slice(0, maxRecordsPerCall).map(record => record.id)
        );
        toDelete = toDelete.slice(maxRecordsPerCall);
    }

    while (toCreate.length > 0) {
        await destinationTable.createRecordsAsync(
            toCreate.slice(0, maxRecordsPerCall)
        );
        toCreate = toCreate.slice(maxRecordsPerCall);
    }

    output.text("Done.");
}