payfrit-works/api/menu/getForBuilder.cfm

<cfsetting showdebugoutput="false">
<cfsetting enablecfoutputonly="true">
<cfcontent type="application/json; charset=utf-8" reset="true">
<cfheader name="Cache-Control" value="no-store">

<cfscript>
/**
 * Get Menu for Builder
 * Returns categories and items in structured format for the menu builder UI
 */

response = { "OK": false };

// Recursive function to build nested options
function buildOptionsTree(allOptions, parentId) {
    var result = [];
    for (var i = 1; i <= allOptions.recordCount; i++) {
        if (allOptions.ParentItemID[i] == parentId) {
            var children = buildOptionsTree(allOptions, allOptions.ItemID[i]);
            arrayAppend(result, {
                "id": "opt_" & allOptions.ItemID[i],
                "dbId": allOptions.ItemID[i],
                "name": allOptions.ItemName[i],
                "price": allOptions.ItemPrice[i],
                "isDefault": allOptions.IsDefault[i] == 1 ? true : false,
                "sortOrder": allOptions.ItemSortOrder[i],
                "requiresSelection": isNull(allOptions.RequiresSelection[i]) ? false : (allOptions.RequiresSelection[i] == 1),
                "maxSelections": isNull(allOptions.MaxSelections[i]) ? 0 : allOptions.MaxSelections[i],
                "options": children
            });
        }
    }
    if (arrayLen(result) > 1) {
        arraySort(result, function(a, b) {
            return a.sortOrder - b.sortOrder;
        });
    }
    return result;
}

try {
    requestBody = toString(getHttpRequestData().content);
    requestData = {};
    if (len(requestBody)) {
        requestData = deserializeJSON(requestBody);
    }

    businessID = 0;
    if (structKeyExists(requestData, "BusinessID")) {
        businessID = val(requestData.BusinessID);
    }

    if (businessID == 0) {
        response["ERROR"] = "missing_business_id";
        response["MESSAGE"] = "BusinessID is required";
        writeOutput(serializeJSON(response));
        abort;
    }

    // Check for MenuID filter (optional - if provided, only return categories for that menu)
    menuID = structKeyExists(requestData, "MenuID") ? val(requestData.MenuID) : 0;

    // Get all menus for this business
    allMenus = [];
    try {
        qMenus = queryExecute("
            SELECT MenuID, MenuName, MenuDescription, MenuDaysActive,
                   MenuStartTime, MenuEndTime, MenuSortOrder
            FROM Menus
            WHERE MenuBusinessID = :businessID AND MenuIsActive = 1
            ORDER BY MenuSortOrder, MenuName
        ", { businessID: businessID }, { datasource: "payfrit" });

        for (m = 1; m <= qMenus.recordCount; m++) {
            arrayAppend(allMenus, {
                "MenuID": qMenus.MenuID[m],
                "MenuName": qMenus.MenuName[m],
                "MenuDescription": isNull(qMenus.MenuDescription[m]) ? "" : qMenus.MenuDescription[m],
                "MenuDaysActive": qMenus.MenuDaysActive[m],
                "MenuStartTime": isNull(qMenus.MenuStartTime[m]) ? "" : timeFormat(qMenus.MenuStartTime[m], "HH:mm"),
                "MenuEndTime": isNull(qMenus.MenuEndTime[m]) ? "" : timeFormat(qMenus.MenuEndTime[m], "HH:mm"),
                "MenuSortOrder": qMenus.MenuSortOrder[m]
            });
        }
    } catch (any e) {
        // Menus table might not exist yet
    }

    // Check if Categories table has data for this business
    hasCategoriesData = false;
    try {
        qCatCheck = queryExecute("
            SELECT 1 FROM Categories WHERE CategoryBusinessID = :businessID LIMIT 1
        ", { businessID: businessID }, { datasource: "payfrit" });
        hasCategoriesData = (qCatCheck.recordCount > 0);
    } catch (any e) {
        hasCategoriesData = false;
    }

    if (hasCategoriesData) {
        // OLD SCHEMA: Use Categories table for categories
        // Build menu filter clause
        menuFilter = "";
        menuParams = { businessID: businessID };
        if (menuID > 0) {
            menuFilter = " AND CategoryMenuID = :menuID";
            menuParams["menuID"] = menuID;
        }

        qCategories = queryExecute("
            SELECT
                CategoryID,
                CategoryName,
                CategorySortOrder as ItemSortOrder,
                CategoryMenuID
            FROM Categories
            WHERE CategoryBusinessID = :businessID #menuFilter#
            ORDER BY CategorySortOrder, CategoryName
        ", menuParams, { datasource: "payfrit" });

        // Get menu items - items that belong to categories (not modifiers)
        qItems = queryExecute("
            SELECT
                i.ItemID,
                i.ItemCategoryID as CategoryItemID,
                i.ItemName,
                i.ItemDescription,
                i.ItemPrice,
                i.ItemSortOrder,
                i.ItemIsActive
            FROM Items i
            WHERE i.ItemBusinessID = :businessID
              AND i.ItemIsActive = 1
              AND i.ItemCategoryID > 0
            ORDER BY i.ItemSortOrder, i.ItemName
        ", { businessID: businessID }, { datasource: "payfrit" });

        // Get direct modifiers (items with ParentItemID pointing to menu items, not categories)
        qDirectModifiers = queryExecute("
            SELECT
                m.ItemID,
                m.ItemParentItemID as ParentItemID,
                m.ItemName,
                m.ItemPrice,
                m.ItemIsCheckedByDefault as IsDefault,
                m.ItemSortOrder,
                m.ItemRequiresChildSelection as RequiresSelection,
                m.ItemMaxNumSelectionReq as MaxSelections
            FROM Items m
            WHERE m.ItemBusinessID = :businessID
              AND m.ItemIsActive = 1
              AND m.ItemParentItemID > 0
              AND (m.ItemCategoryID = 0 OR m.ItemCategoryID IS NULL)
            ORDER BY m.ItemSortOrder, m.ItemName
        ", { businessID: businessID }, { datasource: "payfrit" });

    } else {
        // NEW UNIFIED SCHEMA: Categories are Items at ParentID=0 with children
        qCategories = queryExecute("
            SELECT DISTINCT
                p.ItemID as CategoryID,
                p.ItemName as CategoryName,
                p.ItemSortOrder
            FROM Items p
            INNER JOIN Items c ON c.ItemParentItemID = p.ItemID
            WHERE p.ItemBusinessID = :businessID
              AND p.ItemParentItemID = 0
              AND p.ItemIsActive = 1
              AND NOT EXISTS (
                  SELECT 1 FROM ItemTemplateLinks tl WHERE tl.TemplateItemID = p.ItemID
              )
            ORDER BY p.ItemSortOrder, p.ItemName
        ", { businessID: businessID }, { datasource: "payfrit" });

        qItems = queryExecute("
            SELECT
                i.ItemID,
                i.ItemParentItemID as CategoryItemID,
                i.ItemName,
                i.ItemDescription,
                i.ItemPrice,
                i.ItemSortOrder,
                i.ItemIsActive
            FROM Items i
            INNER JOIN Items cat ON cat.ItemID = i.ItemParentItemID
            WHERE i.ItemBusinessID = :businessID
              AND i.ItemIsActive = 1
              AND cat.ItemParentItemID = 0
              AND NOT EXISTS (
                  SELECT 1 FROM ItemTemplateLinks tl WHERE tl.TemplateItemID = cat.ItemID
              )
            ORDER BY i.ItemSortOrder, i.ItemName
        ", { businessID: businessID }, { datasource: "payfrit" });

        qDirectModifiers = queryExecute("
            SELECT
                m.ItemID,
                m.ItemParentItemID as ParentItemID,
                m.ItemName,
                m.ItemPrice,
                m.ItemIsCheckedByDefault as IsDefault,
                m.ItemSortOrder,
                m.ItemRequiresChildSelection as RequiresSelection,
                m.ItemMaxNumSelectionReq as MaxSelections
            FROM Items m
            WHERE m.ItemBusinessID = :businessID
              AND m.ItemIsActive = 1
              AND m.ItemParentItemID > 0
            ORDER BY m.ItemSortOrder, m.ItemName
        ", { businessID: businessID }, { datasource: "payfrit" });
    }

    // Collect menu item IDs for filtering template links
    menuItemIds = [];
    for (i = 1; i <= qItems.recordCount; i++) {
        arrayAppend(menuItemIds, qItems.ItemID[i]);
    }

    // Get template links ONLY for this business's menu items
    qTemplateLinks = queryNew("ParentItemID,TemplateItemID,SortOrder");
    if (arrayLen(menuItemIds) > 0) {
        qTemplateLinks = queryExecute("
            SELECT
                tl.ItemID as ParentItemID,
                tl.TemplateItemID,
                tl.SortOrder
            FROM ItemTemplateLinks tl
            WHERE tl.ItemID IN (:itemIds)
            ORDER BY tl.ItemID, tl.SortOrder
        ", { itemIds: { value: arrayToList(menuItemIds), cfsqltype: "cf_sql_varchar", list: true } }, { datasource: "payfrit" });
    }

    // Get templates for this business only
    qTemplates = queryExecute("
        SELECT DISTINCT
            t.ItemID,
            t.ItemName,
            t.ItemPrice,
            t.ItemIsCheckedByDefault as IsDefault,
            t.ItemSortOrder,
            t.ItemRequiresChildSelection as RequiresSelection,
            t.ItemMaxNumSelectionReq as MaxSelections
        FROM Items t
        WHERE t.ItemBusinessID = :businessID
          AND (t.ItemCategoryID = 0 OR t.ItemCategoryID IS NULL)
          AND t.ItemParentItemID = 0
          AND t.ItemIsActive = 1
        ORDER BY t.ItemSortOrder, t.ItemName
    ", { businessID: businessID }, { datasource: "payfrit" });

    // Get template children (options within templates)
    templateIds = [];
    for (i = 1; i <= qTemplates.recordCount; i++) {
        arrayAppend(templateIds, qTemplates.ItemID[i]);
    }

    qTemplateChildren = queryNew("ItemID,ParentItemID,ItemName,ItemPrice,IsDefault,ItemSortOrder,RequiresSelection,MaxSelections");
    if (arrayLen(templateIds) > 0) {
        qTemplateChildren = queryExecute("
            SELECT
                c.ItemID,
                c.ItemParentItemID as ParentItemID,
                c.ItemName,
                c.ItemPrice,
                c.ItemIsCheckedByDefault as IsDefault,
                c.ItemSortOrder,
                c.ItemRequiresChildSelection as RequiresSelection,
                c.ItemMaxNumSelectionReq as MaxSelections
            FROM Items c
            WHERE c.ItemParentItemID IN (:templateIds)
              AND c.ItemIsActive = 1
            ORDER BY c.ItemSortOrder, c.ItemName
        ", { templateIds: { value: arrayToList(templateIds), cfsqltype: "cf_sql_varchar", list: true } }, { datasource: "payfrit" });
    }

    // Build templates lookup with their options
    templatesById = {};
    for (i = 1; i <= qTemplates.recordCount; i++) {
        templateID = qTemplates.ItemID[i];
        options = buildOptionsTree(qTemplateChildren, templateID);
        templatesById[templateID] = {
            "id": "mod_" & qTemplates.ItemID[i],
            "dbId": qTemplates.ItemID[i],
            "name": qTemplates.ItemName[i],
            "price": qTemplates.ItemPrice[i],
            "isDefault": qTemplates.IsDefault[i] == 1 ? true : false,
            "sortOrder": qTemplates.ItemSortOrder[i],
            "isTemplate": true,
            "requiresSelection": isNull(qTemplates.RequiresSelection[i]) ? false : (qTemplates.RequiresSelection[i] == 1),
            "maxSelections": isNull(qTemplates.MaxSelections[i]) ? 0 : qTemplates.MaxSelections[i],
            "options": options
        };
    }

    // Build template links lookup by parent ItemID
    templateLinksByItem = {};
    for (i = 1; i <= qTemplateLinks.recordCount; i++) {
        parentID = qTemplateLinks.ParentItemID[i];
        templateID = qTemplateLinks.TemplateItemID[i];

        if (!structKeyExists(templateLinksByItem, parentID)) {
            templateLinksByItem[parentID] = [];
        }

        if (structKeyExists(templatesById, templateID)) {
            tmpl = duplicate(templatesById[templateID]);
            tmpl["sortOrder"] = qTemplateLinks.SortOrder[i];
            arrayAppend(templateLinksByItem[parentID], tmpl);
        }
    }

    // Build nested direct modifiers for each menu item
    directModsByItem = {};
    for (itemId in menuItemIds) {
        options = buildOptionsTree(qDirectModifiers, itemId);
        if (arrayLen(options) > 0) {
            directModsByItem[itemId] = options;
        }
    }

    // Build items lookup by CategoryID
    itemsByCategory = {};
    for (i = 1; i <= qItems.recordCount; i++) {
        catID = qItems.CategoryItemID[i];
        if (!structKeyExists(itemsByCategory, catID)) {
            itemsByCategory[catID] = [];
        }

        itemID = qItems.ItemID[i];

        // Get template-linked modifiers
        itemModifiers = structKeyExists(templateLinksByItem, itemID) ? duplicate(templateLinksByItem[itemID]) : [];

        // Add direct modifiers
        if (structKeyExists(directModsByItem, itemID)) {
            directMods = directModsByItem[itemID];
            for (j = 1; j <= arrayLen(directMods); j++) {
                arrayAppend(itemModifiers, directMods[j]);
            }
        }

        // Sort modifiers by sortOrder
        if (arrayLen(itemModifiers) > 1) {
            arraySort(itemModifiers, function(a, b) {
                return a.sortOrder - b.sortOrder;
            });
        }

        arrayAppend(itemsByCategory[catID], {
            "id": "item_" & qItems.ItemID[i],
            "dbId": qItems.ItemID[i],
            "name": qItems.ItemName[i],
            "description": isNull(qItems.ItemDescription[i]) ? "" : qItems.ItemDescription[i],
            "price": qItems.ItemPrice[i],
            "imageUrl": javaCast("null", ""),
            "photoTaskId": javaCast("null", ""),
            "modifiers": itemModifiers,
            "sortOrder": qItems.ItemSortOrder[i]
        });
    }

    // Build categories array
    categories = [];
    catIndex = 0;
    for (i = 1; i <= qCategories.recordCount; i++) {
        catID = qCategories.CategoryID[i];
        catItems = structKeyExists(itemsByCategory, catID) ? itemsByCategory[catID] : [];

        catStruct = {
            "id": "cat_" & qCategories.CategoryID[i],
            "dbId": qCategories.CategoryID[i],
            "name": qCategories.CategoryName[i],
            "description": "",
            "sortOrder": catIndex,
            "items": catItems
        };

        // Include MenuID if available (legacy schema with Categories table)
        if (hasCategoriesData) {
            try {
                catStruct["menuId"] = isNull(qCategories.CategoryMenuID[i]) ? 0 : val(qCategories.CategoryMenuID[i]);
            } catch (any e) {
                catStruct["menuId"] = 0;
            }
        }

        arrayAppend(categories, catStruct);
        catIndex++;
    }

    // Build template library array
    templateLibrary = [];
    for (templateID in templatesById) {
        arrayAppend(templateLibrary, templatesById[templateID]);
    }

    // Get business brand color
    brandColor = "";
    try {
        qBrand = queryExecute("
            SELECT BusinessBrandColor FROM Businesses WHERE BusinessID = :bizId
        ", { bizId: businessID }, { datasource: "payfrit" });
        if (qBrand.recordCount > 0 && len(trim(qBrand.BusinessBrandColor))) {
            brandColor = qBrand.BusinessBrandColor;
        }
    } catch (any e) {
        // Column may not exist yet, ignore
    }

    response["OK"] = true;
    response["MENU"] = { "categories": categories };
    response["MENUS"] = allMenus;
    response["SELECTED_MENU_ID"] = menuID;
    response["TEMPLATES"] = templateLibrary;
    response["BRANDCOLOR"] = brandColor;
    response["CATEGORY_COUNT"] = arrayLen(categories);
    response["TEMPLATE_COUNT"] = arrayLen(templateLibrary);
    response["MENU_COUNT"] = arrayLen(allMenus);
    response["SCHEMA"] = hasCategoriesData ? "legacy" : "unified";

    totalItems = 0;
    for (cat in categories) {
        totalItems += arrayLen(cat.items);
    }
    response["ITEM_COUNT"] = totalItems;

} catch (any e) {
    response["ERROR"] = "server_error";
    response["MESSAGE"] = e.message;
    response["DETAIL"] = e.detail ?: "";
}

writeOutput(serializeJSON(response));
</cfscript>