js.html

<script>
    /**
     * Global State
     */
    let PRODUCT_CATALOG = [];
    let CURRENT_CLIENT = null;
    let IS_ADMIN = false;
    let ADMIN_KEY = ""; // Fetched from server via ADMIN_LOGIN named range
    const CLIENT_ID = window.initialClientId; // Defined in index.html


    /**
     * Global Failure Handler for google.script.run calls
     */
    function onFailure(error) {
        console.error("[onFailure] Server error:", error);
        showToast("Server Error: " + (error.message || error), 'error');
    }

    /**
     * Show Toast/Snackbar Notification (replaces alert())
     * @param {string} message - The message to display
     * @param {string} type - 'success', 'error', or '' for default
     * @param {number} duration - How long to show (ms), default 4000
     */
    function showToast(message, type = '', duration = 4000) {
        const snackbar = document.getElementById('snackbar');
        if (!snackbar) {
            // Fallback to alert if snackbar element not found
            alert(message);
            return;
        }

        snackbar.textContent = message;
        snackbar.className = 'md3-snackbar';
        if (type) snackbar.classList.add(type);

        // Show
        setTimeout(() => snackbar.classList.add('show'), 10);

        // Hide after duration
        setTimeout(() => {
            snackbar.classList.remove('show');
        }, duration);
    }

    /**
     * Helper: Normalize category names to match server-side logic
     * Must match superNormalize in Config.gs
     */
    function superNormalize(s) {
        return String(s || "").toLowerCase().replace(/[^a-z0-9]/g, '').replace(/s$/, '');
    }

    /**
     * Helper: Convert string to safe CSS Class
     */
    function toCssClass(str) {
        if (!str) return 'cat-unknown';
        // USE superNormalize to ensure plural versions (Darts) match singular keys (Dart)
        const norm = superNormalize(str);
        return 'cat-' + norm;
    }

    /**
     * Helper: Escape Regex Characters
     */
    function escapeRegex(string) {
        return string.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
    }

    /**
     * Helper: Calculate Contrast Color (Black or White) from Hex
     */
    function getContrastYIQ(hexcolor) {
        hexcolor = hexcolor.replace("#", "");
        var r = parseInt(hexcolor.substr(0, 2), 16);
        var g = parseInt(hexcolor.substr(2, 2), 16);
        var b = parseInt(hexcolor.substr(4, 2), 16);
        var yiq = ((r * 299) + (g * 587) + (b * 114)) / 1000;
        // Reverted to 190 per user request to prefer White text
        return (yiq >= 190) ? 'black' : 'white';
    }



    /**
     * Initialize
     */
    window.onload = function () {

        // Apply branding immediately (Login screen needs it too)
        applyCategoryColorsVisuals();

        if (!CLIENT_ID) {
            // No ID in URL -> Show Login View
            document.getElementById('loading').classList.add('hidden');
            document.getElementById('login-view').classList.remove('hidden');
            return;
        }

        // DEBUG: Alert to confirm we are starting
        // alert("App Loaded. Client ID: " + CLIENT_ID); 

        document.getElementById('header-total').classList.remove('hidden'); // Show Subtotal

        // Scroll Shadow for App Bar
        window.addEventListener('scroll', () => {
            const appBar = document.querySelector('.md3-top-app-bar');
            if (window.scrollY > 0) {
                appBar.classList.add('scrolled-header');
            } else {
                appBar.classList.remove('scrolled-header');
            }
        }, { passive: true });

        // 2. Fetch Client Data
        fetchClientData(CLIENT_ID);
    };

    function handleManualLogin() {
        const input = document.getElementById('manual-client-id');
        const id = input.value.trim();

        if (!id) {
            showLoginError("Please enter a Client ID.");
            return;
        }

        // Check for Admin Key (from ADMIN_LOGIN named range via appConfig)
        const adminKey = (window.appConfig && window.appConfig.ADMIN_KEY) || "ADMIN123";
        if (id.toUpperCase() === adminKey.toUpperCase()) {
            IS_ADMIN = true;
            document.getElementById('login-view').classList.add('hidden');
            document.getElementById('loading').classList.remove('hidden');

            // Admins don't have a specific client record initially
            onAdminLoginSuccess();
            return;
        }


        // Hide Login, Show Loading
        document.getElementById('login-view').classList.add('hidden');
        document.getElementById('loading').classList.remove('hidden');

        // Update Global and Fetch
        fetchClientData(id);
    }

    function onAdminLoginSuccess() {
        IS_ADMIN = true;
        document.getElementById('nav-tabs').classList.remove('hidden');
        // Show Admin Tab
        document.getElementById('admin-tab').classList.remove('hidden');

        document.getElementById('loading').classList.add('hidden');
        showView('history-view'); // Default to history for admins

        // 1. Fetch Products for Admin (to populate "New Order" tab)
        google.script.run
            .withSuccessHandler(onProductsSuccess)
            .withFailureHandler(onFailure)
            .getProductCatalog();

        // 2. Fetch all orders for admin
        google.script.run
            .withSuccessHandler(renderOrderHistory)
            .withFailureHandler(onFailure)
            .getOrdersByClient('');

        // 3. Init Admin Form (Lazy load or eager?)
        if (typeof initAdminModule === 'function') {
            initAdminModule();
        }
    }

    function showLoginError(msg) {
        const el = document.getElementById('login-error-msg');
        el.textContent = msg;
        el.classList.remove('hidden');
        document.getElementById('manual-client-id').classList.add('error-state'); // Optional: shake or red border
    }

    function clearLoginError() {
        document.getElementById('login-error-msg').classList.add('hidden');
    }

    function fetchClientData(id) {
        google.script.run
            .withSuccessHandler(onClientDataSuccess)
            .withFailureHandler(onLoginFailure)
            .getClientById(id);
    }

    function onLoginFailure(error) {
        // If specific login fail
        showError("Invalid Client ID or System Error.");

        // DEBUG: Fetch and show debug info to help user
        const input = document.getElementById('manual-client-id');
        const id = input ? input.value : (CLIENT_ID || "Unknown");

        google.script.run
            .withSuccessHandler(info => {
                const msg = `DEBUG RAW DATA:\nSheet: ${info.sheetName}\nTotal Rows: ${info.totalSheetRows}\nRaw Headers (Row 1): ${info.rawHeaders}\nFirst Data (Row 2): ${info.rawFirstRowData}\nParsed Keys: ${info.parsedObjectKeys}`;
                alert(msg);
            })
            .debugClientLookup(id);

        document.getElementById('error').innerHTML += `<br><button class="md3-btn-filled" onclick="location.reload()">Try Again</button>`;
    }

    function onClientDataSuccess(client) {
        // alert("Client Data Success: " + (client ? client.Name : "null"));
        if (!client) {
            showError("Client not found in database.");
            return;
        }


        CURRENT_CLIENT = client;
        document.getElementById('client-name-input').value = client['Name'] || "";
        document.getElementById('client-address-input').value = client['Address'] || "";

        // Client View: No Tabs, just the form
        document.getElementById('nav-tabs').classList.add('hidden');
        IS_ADMIN = false;

        // 2. Fetch Products
        // alert("Fetching Products...");
        google.script.run
            .withSuccessHandler(onProductsSuccess)
            .withFailureHandler(onFailure)
            .getProductCatalog();
    }

    /**
     * View Management
     */
    function showView(viewId) {
        // Toggle Active Views
        document.querySelectorAll('.state-view, form').forEach(el => {
            if (el.id === 'loading' || el.id === 'error' || el.id === 'bubble-overlay' || el.id === 'details-bubble' || el.id === 'success') return;
            el.classList.add('hidden');
        });

        const target = document.getElementById(viewId);
        if (target) target.classList.remove('hidden');

        // Toggle Tab States
        document.querySelectorAll('.md3-tab').forEach(tab => {
            const onClick = tab.getAttribute('onclick');
            if (onClick && onClick.includes(viewId)) {
                tab.classList.add('active');
            } else {
                tab.classList.remove('active');
            }
        });

        // Specific View Logic
        if (viewId === 'order-form') {
            document.getElementById('collapse-fab').classList.remove('hidden');
            document.getElementById('header-total').classList.remove('hidden');
        } else {
            document.getElementById('collapse-fab').classList.add('hidden');
            document.getElementById('header-total').classList.add('hidden');
        }

        // Recalculate sticky header offsets whenever the view/tabs change
        if (typeof updateStickyOffsets === 'function') updateStickyOffsets();
    }

    /**
     * History Logic
     */
    function fetchOrderHistory(clientName) {
        if (!clientName) return;
        google.script.run
            .withSuccessHandler(renderOrderHistory)
            .getOrdersByClient(clientName);
    }

    function renderOrderHistory(orders) {

        let selector = document.getElementById('order-selector');
        let status = document.getElementById('history-status');

        // Retry once if selector not found (timing issue)
        if (!selector) {
            console.warn("[renderOrderHistory] Selector not found, retrying in 500ms...");
            setTimeout(() => {
                selector = document.getElementById('order-selector');
                status = document.getElementById('history-status');
                if (selector) {
                    populateOrderDropdown(selector, status, orders);
                } else {
                    console.error("[renderOrderHistory] Retry failed. #order-selector does not exist.");
                    alert("Error: Order selector element not found. Please refresh.");
                }
            }, 500);
            return;
        }

        populateOrderDropdown(selector, status, orders);
    }

    function populateOrderDropdown(selector, status, orders) {
        // Reset
        selector.innerHTML = '<option value="">-- Choose an Order --</option>';

        if (!orders || orders.length === 0) {
            if (status) status.textContent = "No previous orders found.";
            return;
        }

        if (status) status.textContent = `Found ${orders.length} orders.`;

        orders.forEach(order => {
            const dateStr = new Date(order.timestamp).toLocaleDateString(undefined, {
                year: 'numeric', month: 'short', day: 'numeric'
            });
            const clientInfo = IS_ADMIN ? `(${order.clientName}) ` : '';
            const option = document.createElement('option');
            option.value = order.id;
            const stateLabel = order.state ? ` [${order.state.toUpperCase()}]` : '';
            option.textContent = `${clientInfo}${order.id}${stateLabel} - ${dateStr} - $${Number(order.total || 0).toFixed(2)}`;
            selector.appendChild(option);
        });
    }

    function handleOrderSelection(orderId) {
        if (!orderId) return;
        editHistoricalOrder(orderId);
    }

    function editHistoricalOrder(orderId) {
        // Show Loading
        const loader = document.getElementById('loading');
        const loadText = document.getElementById('loading-text');
        if (loadText) loadText.textContent = "Loading Order Details...";
        loader.classList.remove('hidden');

        google.script.run
            .withSuccessHandler(data => {
                console.log("[editHistoricalOrder] Received data:", data);
                if (!data) {
                    alert("Order data not found or row is empty. (v1.8.51)");
                    loader.classList.add('hidden');
                    return;
                }

                window.prefillData = data;
                window.editOrderId = data.id;

                const existingInputs = document.querySelectorAll('input.qty-input');
                if (existingInputs.length > 0) {
                    // Products already rendered, prefill now
                    prefillOrderForm(data);
                } else {
                    // Products not yet rendered, onProductsSuccess will handle prefill
                }

                showView('order-form');
                loader.classList.add('hidden');
            })
            .withFailureHandler(err => {
                alert("Error loading order: " + err.message);
                loader.classList.add('hidden');
            })
            .getOrderById(orderId);
    }

    function onProductsSuccess(products) {
        PRODUCT_CATALOG = products;

        // Fetch Admin Key from config if available (Prioritize appConfig over categorySettings)
        window.ADMIN_KEY = (window.appConfig && window.appConfig.ADMIN_KEY) ||
            (window.categorySettings?.main?.adminKey) ||
            "ADMIN123";

        // Filter products based on client section permissions (non-admins only)
        let filteredProducts = products;
        if (!IS_ADMIN && CURRENT_CLIENT && CURRENT_CLIENT.allowedSections) {
            const allowedSections = CURRENT_CLIENT.allowedSections;
            console.log('[ProductFilter] Client allowed sections:', allowedSections);

            filteredProducts = products.filter(product => {
                const productCategory = superNormalize(product.category || '');
                const catSettings = window.categorySettings ? window.categorySettings[productCategory] : null;
                const productSection = catSettings ? (catSettings.section || '').toUpperCase() : '';
                if (!productSection) return true;
                return allowedSections.includes(productSection);
            });
        }

        try {
            renderProducts(filteredProducts);
            if (window.prefillData) {
                prefillOrderForm(window.prefillData);
            }
        } catch (e) {
            console.error("Render Error:", e);
        }

        document.getElementById('loading').classList.add('hidden');

        // v1.8.31 FIX: Only show the form if we aren't an admin (direct entry) 
        // OR if we are explicitly on the form view.
        const currentView = document.querySelector('.md3-tab.active')?.getAttribute('onclick');
        const isOnForm = currentView ? currentView.includes('order-form') : true;

        if (!IS_ADMIN || isOnForm) {
            document.getElementById('order-form').classList.remove('hidden');
            document.getElementById('collapse-fab').classList.remove('hidden');
        }
    }
    /**
     * Prefill Order Form from Data
     */
    function prefillOrderForm(data) {
        if (!data || !data.items) {
            console.warn("[prefillOrderForm] No items to prefill.");
            return;
        }

        // 2. Populate Quantities
        // 1. Populate Client Info
        if (data.clientName) document.getElementById('client-name-input').value = data.clientName;
        if (data.clientComments) document.getElementById('client-comments-input').value = data.clientComments;

        // 2. Populate Quantities
        Object.keys(data.items).forEach(sku => {
            const item = data.items[sku];
            // FIX v1.8.45: Strip @ prefix, input IDs are just the SKU (no qty- prefix)
            const normalizedSku = sku.replace(/^@/, '');
            const input = document.getElementById(normalizedSku);
            if (input) {
                input.value = item.qty;
                // Dispatch input event to trigger any listeners
                input.dispatchEvent(new Event('input', { bubbles: true }));
            }
        });

        // 3. Recalculate Totals
        if (typeof calculateTotal === 'function') {
            calculateTotal();
        }
    }

    /**
     * Collapse All Categories
     */
    function collapseAllCats() {
        const details = document.querySelectorAll('details');
        details.forEach(d => d.open = false);
    }

    /**
     * Helper: Apply Category Colors and Visuals based on settings
     */
    function applyCategoryColorsVisuals() {
        if (!window.categorySettings) return;

        const styleId = 'dynamic-cat-syles';
        let styleBlock = document.getElementById(styleId);
        if (!styleBlock) {
            styleBlock = document.createElement('style');
            styleBlock.id = styleId;
            document.head.appendChild(styleBlock);
        }

        let cssRules = "";

        // App Theme Override from APP_STYLES named ranges
        const styles = window.appStyles || {};
        if (styles.primaryColor) {

            // Core theme variables
            cssRules += `:root { \n`;
            cssRules += `  --md-sys-color-primary: ${styles.primaryColor} !important; \n`;
            cssRules += `  --md-sys-color-on-primary: ${styles.primaryTextColor || '#ffffff'} !important; \n`;
            cssRules += `  --md-sys-color-secondary: ${styles.secondaryColor || '#625b71'} !important; \n`;
            cssRules += `  --md-sys-color-on-secondary: ${styles.secondaryTextColor || '#ffffff'} !important; \n`;
            cssRules += `} \n`;

            // Forced styles for headers and primary buttons
            cssRules += `.app-header, .md3-top-app-bar, .md3-title-large, .total-amount { color: var(--md-sys-color-on-primary) !important; } \n`;
            cssRules += `.primary-btn { background-color: var(--md-sys-color-primary) !important; color: var(--md-sys-color-on-primary) !important; } \n`;

            // Forced styles for FAB button (secondary color)
            cssRules += `.md3-fab-fixed { background-color: ${styles.secondaryColor || '#625b71'} !important; color: ${styles.secondaryTextColor || '#ffffff'} !important; } \n`;
            cssRules += `.md3-fab-fixed .material-symbols-outlined { color: ${styles.secondaryTextColor || '#ffffff'} !important; } \n`;

            // Header Layout Spacing Fix
            cssRules += `.app-header-content { display: flex; align-items: center; justify-content: space-between; padding: 0 16px; } \n`;
            cssRules += `#header-total { margin-left: auto; margin-right: 32px; font-variant-numeric: tabular-nums; } \n`;
        }

        Object.keys(window.categorySettings).forEach(normalizedKey => {
            const setting = window.categorySettings[normalizedKey];
            const color = (typeof setting === 'object' && setting.color) ? setting.color : (typeof setting === 'string' ? setting : null);
            const cleanClass = 'cat-' + normalizedKey;

            if (cleanClass && color) {
                const textColor = (typeof setting === 'object' && setting.textColor) ? setting.textColor : getContrastYIQ(color);

                // Color ONLY in OPEN state
                cssRules += `.${cleanClass} [open] > .category-summary { background-color: ${color} !important; color: ${textColor} !important; } \n`;

                // Border/Radius logic for Open State
                cssRules += `.${cleanClass} [open] > .category-summary { border-top-left-radius: 12px; border-top-right-radius: 12px; border-bottom-left-radius: 0; border-bottom-right-radius: 0; } \n`;
                cssRules += `.${cleanClass}:not([open]) > .category-summary { border-radius: 12px; transition: border-radius 0.2s ease; } \n`;

                // Content elements
                cssRules += `.${cleanClass} .matrix-header-row { background-color: ${color} !important; color: ${textColor} !important; } \n`;
                cssRules += `.${cleanClass} { --category-color: ${color}; } \n`;
            }
        });

        if (styleBlock) styleBlock.textContent = cssRules;
    }

    /**
     * Render Product List grouped by Category
     * Supports:
     * 1. Matrix Groups (e.g. SODA SQ)
     * 2. Compact Tables (Standard items)
     */
    function renderProducts(products) {
        // DEBUG: Check if product exists in raw list
        console.log(`[DEBUG] renderProducts received ${products.length} items`);
        const debugProd = products.find(p => String(p.name).toLowerCase().includes("pulse x") || String(p.variation).toLowerCase().includes("pulse x"));
        console.log("[DEBUG] Searching for 'Pulse X' in raw list:", debugProd);

        applyCategoryColorsVisuals(); // Apply styles first

        const container = document.getElementById('product-list');
        container.innerHTML = "";

        // 0. Normalize Data
        const normalizedProducts = products.map(p => {
            let name = String(p.name).trim();
            let varName = String(p.variation || "").trim();
            const nameIsSize = /^\d+(\.\d+)?\s*([a-zA-Z%]+)?$/.test(name);
            const varIsName = varName && /[a-zA-Z]/.test(varName) && varName.length > 2;

            if (nameIsSize && varIsName) {
                return { ...p, name: varName, variation: name };
            }
            return p;
        });

        // 1. Group by Category
        const groupedByCat = {};
        normalizedProducts.forEach(p => {
            const cat = p.category || "Uncategorized";
            if (!groupedByCat[cat]) groupedByCat[cat] = [];
            groupedByCat[cat].push(p);
        });

        // 2. Filter and Sort Categories
        let categories = Object.keys(groupedByCat).filter(cat => {
            const lowCat = cat.toLowerCase();
            return lowCat !== "main" && lowCat !== "category" && lowCat !== "category name";
        });

        categories.sort((a, b) => {
            // Normalize category names to match keys in categorySettings
            const normalizedA = superNormalize(a);
            const normalizedB = superNormalize(b);

            const setA = window.categorySettings && window.categorySettings[normalizedA];
            const setB = window.categorySettings && window.categorySettings[normalizedB];

            // Default to 999 if no order set
            const orderA = (setA && typeof setA.order === 'number') ? setA.order : 999;
            const orderB = (setB && typeof setB.order === 'number') ? setB.order : 999;

            if (orderA !== orderB) {
                return orderA - orderB;
            }

            // Tie-break: Alphabetical
            return a.localeCompare(b);
        });

        categories.forEach(cat => {
            const catDetails = document.createElement('details');
            catDetails.open = false; // Default closed
            // Add dynamic class using normalization to ensure it matches Settings
            const catClass = toCssClass(cat);
            catDetails.className = `category-details ${catClass}`;

            const catSummary = document.createElement('summary');
            catSummary.className = "md3-headline-medium category-summary";
            const catHasSale = (groupedByCat[cat] || []).some(p => p.onSale);
            catSummary.innerHTML = `
                <div style="flex: 1; display: flex; align-items: center;">
                    <!-- Left placeholder for balance -->
                    <span class="cat-subtotal-left" style="font-size:0.8em; opacity:0; visibility:hidden;">$0.00</span>
                </div>
                
                <span style="flex: 0 1 auto; text-align: center; white-space: nowrap;">
                    ${cat}
                </span>

                <div style="flex: 1; display: flex; justify-content: flex-end; align-items: center; gap: 12px;">
                    ${catHasSale ? '<span class="sale-badge">SALE</span>' : ''}
                    <span class="cat-subtotal" style="font-size:0.8em; opacity:0.9; font-weight:bold; display:none;">$0.00</span>
                </div>
            `;
            // Ensure Flex display for spacing
            catSummary.style.display = "flex";
            catSummary.style.justifyContent = "space-between";
            catSummary.style.alignItems = "center";
            catSummary.style.textAlign = "center";
            catDetails.appendChild(catSummary);

            const catContent = document.createElement('div');
            catContent.className = "category-content";

            const groups = groupProductsDynamically(groupedByCat[cat]);
            const groupNames = Object.keys(groups); // NO SORT to keep sheet order

            groupNames.forEach(baseName => {
                const group = groups[baseName];
                const isMatrix = checkMatrixEligibility(group, baseName);

                // --- BUILD DYNAMIC HEADER COLUMNS ---
                let headerColsHtml = "";
                let colDefs = [];

                if (isMatrix) {
                    const hasVar2 = group.some(p => p.variation2);

                    if (hasVar2) {
                        // Logic for Variation 2 Columns - NO SORT to keep sheet order
                        const uniqueVar2 = [...new Set(group.map(p => p.variation2).filter(v => v))];
                        colDefs = uniqueVar2.map(v2Name => {
                            return { label: v2Name || "Std", price: "", name: v2Name, isVar2: true };
                        });
                    } else {
                        // Classic Logic (Name-based Columns) - NO SORT to keep sheet order
                        const allNames = [...new Set(group.map(p => p.name))];
                        colDefs = allNames.map(name => {
                            const regex = new RegExp("^" + escapeRegex(baseName), "i");
                            let label = name.replace(regex, "").trim();
                            label = label.replace(/^[\s\-\&]+/, "").trim(); // Clean prefix
                            if (!label) label = "Std";
                            const p = group.find(prod => prod.name === name);
                            const price = p ? Number(p.price).toFixed(2) : "0.00";
                            return { label: label, price: price, name: name, isVar2: false };
                        });
                    }
                    // POPULATE STICKY HEADERS FOR MATRIX
                    headerColsHtml = colDefs.map(col => `
                <div style="width:48px !important; max-width:48px !important; min-width:48px !important;
                     flex:0 0 48px !important; overflow:hidden; text-align:center;">
                    <span class="qty-header-label" style="font-size:10px; font-weight:bold; white-space:nowrap; overflow:hidden; text-overflow:ellipsis; display:block;">${col.label}</span>
                </div>
                `).join("");
                } else {
                    // Standard / Compact Logic
                    const caseProducts = group.filter(p => p.hasCase);
                    const showCase = caseProducts.length > 0;

                    // Identify common spec (e.g. "50mg") to display in header
                    const uniqueSpecs = [...new Set(group.map(p => String(p.variation2 || "").trim()).filter(v => v && v.toLowerCase() !== "undefined"))];
                    const commonSpec = uniqueSpecs.length === 1 ? uniqueSpecs[0] : "";
                    if (commonSpec) {
                        headerColsHtml += `<div class="col-data" style="margin-right:auto; padding-left:12px; color:inherit; font-size:12px; font-weight:900; opacity:0.9;">${commonSpec}</div>`;
                    }

                    let caseHeaderLabel = "Case";
                    // Check if all cases have same size
                    if (showCase) {
                        const firstSize = parseInt(caseProducts[0].unitsPerCase) || 1;
                        const allSame = caseProducts.every(p => (parseInt(p.unitsPerCase) || 1) === firstSize);
                        if (allSame) {
                            caseHeaderLabel += `<br><span style="font-size:10px; opacity:0.8;">(Box ${firstSize})</span>`;
                        }
                    }

                    // NEW: Prices Aligned Above Columns
                    const leaderP = group.find(p => Number(p.price) > 0) || group[0];
                    const pSingleLeader = group.find(p => !p.hasCase && Number(p.price) > 0) || group.find(p => !p.hasCase) || leaderP;

                    const buildPriceHeader = (p, subLabel) => {
                        const isSaleOn = !!p.onSale;
                        const hasDisc = isSaleOn && (Number(p.salePrice) > 0 && Number(p.salePrice) < Number(p.price));
                        const reg = Number(p.price).toFixed(2);
                        const sale = Number(p.salePrice).toFixed(2);

                        let cell = `<div class="col-data">`;
                        if (hasDisc) {
                            cell += `<span class="price-header-label strikethrough" style="text-decoration:line-through; font-size:9px; opacity:0.7;">$${reg}</span>`;
                            cell += `<span class="price-header-label sale-price-highlight" style="font-weight:900;">$${sale}</span>`;
                        } else {
                            const displayP = isSaleOn ? p.salePrice : p.price;
                            cell += `<span class="price-header-label">$${Number(displayP).toFixed(2)}</span>`;
                        }
                        if (subLabel) {
                            cell += `<div style="font-size:9px; opacity:0.6; line-height:1; margin-top:2px;">${subLabel}</div>`;
                        }
                        cell += `</div>`;
                        return cell;
                    };

                    // Only show Single header when the group actually has non-case products.
                    // If ALL products are cases (e.g. Master Case only), skip the empty Single column.
                    const showSingle = group.some(p => !p.hasCase);
                    if (showSingle) {
                        headerColsHtml += buildPriceHeader(pSingleLeader, "Single");
                    }

                    // 2. Case Price Column Header (if applicable)
                    if (showCase) {
                        const pCaseLeader = group.find(p => p.hasCase && Number(p.price) > 0) || group.find(p => p.hasCase) || leaderP;
                        const caseSubLabel = showSingle ? caseHeaderLabel : "";
                        headerColsHtml += buildPriceHeader(pCaseLeader, caseSubLabel);
                    }
                }

                // Determine Sticky
                const isSticky = group.length > 2;

                const prodDetails = document.createElement('details');
                prodDetails.open = false; // Default closed

                // ASSIGN DYNAMIC CATEGORY CLASS
                const catClass = toCssClass(cat);
                prodDetails.className = `product-group-details mb-3 ${catClass}`;

                const prodSummary = document.createElement('summary');
                prodSummary.className = "product-group-header";
                if (!isSticky) prodSummary.classList.add('no-sticky');

                // MAIN FLEX HEADER
                // Determine Header Color (Directive v1.8.17: Comprehensive White Filtering)
                const leader = group[0];
                const gColor = String(leader.groupColor || "").trim().toLowerCase();
                const bColor = String(leader.backgroundColor || "").trim().toLowerCase();

                const isWhite = (c) => (c === "#ffffff" || c === "#fff" || c === "white" || c === "transparent");

                let headerColor = "";
                if (gColor && !isWhite(gColor)) headerColor = leader.groupColor;
                else if (bColor && !isWhite(bColor)) headerColor = leader.backgroundColor;

                const headerTextColor = String(leader.groupTextColor || leader.textColor || "").trim();

                // Final Header Color: 1. Group Override, 2. Category Color, 3. Neutral Default
                const catSet = window.categorySettings ? window.categorySettings[superNormalize(cat)] : null;
                const finalHeaderColor = headerColor || (catSet ? catSet.color.trim() : "#f8f9fa");

                const groupClass = 'prod-' + escapeRegex(baseName).replace(/[^a-zA-Z0-9-]/g, '-').toLowerCase();
                prodDetails.classList.add(groupClass);

                const dynamicStyle = document.getElementById('dynamic-cat-syles');
                if (dynamicStyle) {
                    const textColor = (headerTextColor && headerTextColor !== '#000000') ? headerTextColor : getContrastYIQ(finalHeaderColor);

                    // Color ONLY when the group is open (expanded). Closed = white/default.
                    const openRule = `.${groupClass}[open] > summary { background-color: ${finalHeaderColor} !important; color: ${textColor} !important; box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2) !important; }`;
                    dynamicStyle.textContent += openRule + '\n';
                    dynamicStyle.textContent += `.${groupClass}[open] > summary.header-content { color: ${textColor} !important; } \n`;
                    dynamicStyle.textContent += `.${groupClass}[open] > summary.header-content a { color: ${textColor} !important; } \n`;
                }

                // --- DATA INTEGRITY CHECK ---
                const varCounts = group.map(p => [p.variation, p.variation2, p.variation3, p.variation4].filter(v => v && String(v).trim() !== "").length);
                const isConsistent = varCounts.every(v => v === varCounts[0]);
                let integrityWarning = "";
                if (!isConsistent) {
                    integrityWarning = `<span class="material-symbols-outlined" style="color:#D32F2F; font-size:16px; vertical-align:middle; cursor:help;" title="Data Inconsistency Detected: Variations are not uniform across this group. Contact Admin.">warning</span>`;
                }

                prodSummary.innerHTML = `
                <div class="header-content grid-header-row">
                        <span class="prod-title col-name" style="font-size: 1.1em; font-weight: 700; font-family: var(--md-ref-typeface-brand), sans-serif;">
                            ${integrityWarning}
                            ${baseName}
                            ${group.some(p => p.onSale) ? '<span class="sale-badge">SALE</span>' : ''}
                            ${(group[0].image || group[0].description) ?
                        `<a href="#" onclick="showDetails(event, '${group[0].name}', '${(group[0].description || "").replace(/'/g, "\\'")}', '${(group[0].image || "").replace(/'/g, "\\'")}', '${Number(group[0].salePrice || 0)}', '${Number(group[0].price || 0)}')" 
                                   style="font-size:11px; margin-left:8px; color:inherit; text-decoration:underline; opacity:0.9;">Details</a>`
                        : ""}
                        </span>
                        
                        <!--Subtotal Span-->
                        <span class="prod-subtotal" style="font-size:0.9em; font-weight:bold; margin-right: 12px; display:none;">$0.00</span>
                        
                        <!--Header Cols for Alignment when open-->
                <div class="header-expanded-content" style="display:flex; flex:1; justify-content: flex-end;">
                    ${headerColsHtml}
                </div>
                </div>`;

                prodDetails.appendChild(prodSummary);

                const prodContent = document.createElement('div');
                prodContent.className = "product-content details-animation-wrapper"; // Added wrapper class

                if (isMatrix) {
                    // Pass dynamic colors from the group representative
                    const sample = group[0];
                    const matBg = sample.backgroundColor || "var(--md-sys-color-primary)";
                    const matTx = sample.textColor || "var(--md-sys-color-on-primary)";
                    prodContent.innerHTML = renderMatrixGroupFlex(baseName, group, colDefs, matBg, matTx);
                } else {
                    prodContent.innerHTML = renderCompactTableFlex(baseName, group);
                }

                prodDetails.appendChild(prodContent);
                catContent.appendChild(prodDetails);
            });

            // Wrap category content as well for animation
            const catWrapper = document.createElement('div');
            catWrapper.className = "details-animation-wrapper";
            catWrapper.appendChild(catContent);

            catDetails.appendChild(catWrapper);
            container.appendChild(catDetails);
        });

        attachInputListeners();
        setupStickyObservers();

        // Initialize Accordion Animation Logic
        initAccordionAnimation();

        // Set sticky top offsets based on real measured heights
        updateStickyOffsets();
    }

    // --- STICKY OFFSET CALCULATOR ---
    // Measures the bottom edge of the whole sticky app-bar block in one shot
    // so we don't need to track tabs separately (on mobile the tabs live inside
    // the app bar, so a single getBoundingClientRect().bottom already includes them).
    function updateStickyOffsets() {
        const appBar = document.querySelector('.md3-top-app-bar');
        if (!appBar) return;

        // Force a synchronous layout flush so we read post-resize values
        appBar.getBoundingClientRect(); // trigger reflow

        // bottom of app bar relative to the document (includes any OS banner above)
        const appBarBottom = Math.round(appBar.getBoundingClientRect().bottom + window.scrollY);

        // On mobile, tabs are inside the app bar, so appBarBottom already
        // accounts for them. On desktop, nav-tabs live outside the bar:
        // add their height only when they are NOT inside the bar.
        const tabs = document.getElementById('nav-tabs');
        let extraTabsH = 0;
        if (tabs && !tabs.classList.contains('hidden') && !appBar.contains(tabs)) {
            extraTabsH = tabs.offsetHeight;
        }

        const catTop = appBarBottom + extraTabsH;
        const catSummaryH = Math.round(
            (document.querySelector('.category-details>summary') || { offsetHeight: 52 }).offsetHeight
        );
        const prodTop = catTop + catSummaryH;

        document.documentElement.style.setProperty('--cat-sticky-top', catTop + 'px');
        document.documentElement.style.setProperty('--prod-sticky-top', prodTop + 'px');
    }

    // Watch for layout shifts (e.g. Google security banner dismissed) and
    // recalculate sticky offsets. Only set up once.
    (function initStickyObserver() {
        if (window._stickyObserverReady) return;
        window._stickyObserverReady = true;

        // ResizeObserver fires whenever the body size changes (banner dismissal
        // makes the iframe taller, shifting everything up).
        if (window.ResizeObserver) {
            new ResizeObserver(() => {
                if (typeof updateStickyOffsets === 'function') updateStickyOffsets();
            }).observe(document.body);
        }

        // Fallback: also recalculate on window resize
        window.addEventListener('resize', () => {
            if (typeof updateStickyOffsets === 'function') updateStickyOffsets();
        }, { passive: true });
    })();

    // --- ACCORDION ANIMATION HELPER ---
    function initAccordionAnimation() {
        if (window._accordionInitialized) return;
        window._accordionInitialized = true;

        // Exclusive accordion — only one item open at each level at a time.
        // Category level: opening a category closes every other category.
        // Product level: opening a product group closes every other product
        //                group WITHIN the same parent category.
        //
        // NO forced scroll on click — CSS position:sticky handles pinning
        // the header to the top of the screen naturally as the user scrolls.
        // Forcing a scroll when clicking caused the header to jump away from
        // its natural position in the list.
        document.addEventListener('toggle', function (e) {
            const details = e.target;
            if (!details || details.tagName !== 'DETAILS' || !details.open) return;

            if (details.classList.contains('category-details')) {
                // Close all OTHER category accordions
                document.querySelectorAll('.category-details').forEach(d => {
                    if (d !== details && d.open) d.open = false;
                });
            } else if (details.classList.contains('product-group-details')) {
                // Close all OTHER product groups inside the same category
                const parentCat = details.closest('.category-details');
                if (parentCat) {
                    parentCat.querySelectorAll('.product-group-details').forEach(d => {
                        if (d !== details && d.open) d.open = false;
                    });
                }
            }

        }, true); // capture phase — fires before child toggle events bubble
    }

    // --- FLEX RENDER FUNCTIONS ---

    // --- DETAILS BUBBLE LOGIC ---
    // --- DETAILS BUBBLE LOGIC ---
    function showDetails(event, name, desc, img, salePrice, regPrice) {
        if (event) event.stopPropagation();

        const overlay = document.getElementById('bubble-overlay');
        const bubble = document.getElementById('details-bubble');
        const titleEl = document.getElementById('bubble-title');
        const descEl = document.getElementById('bubble-desc');
        const imgContainer = document.getElementById('bubble-img-container');

        titleEl.textContent = name;

        // Price Logic in Bubble
        let priceHtml = "";
        const sPrice = Number(salePrice);
        const rPrice = Number(regPrice);

        if (sPrice > 0) {
            priceHtml = `<div style="margin-bottom:8px;">
                            <span style="text-decoration:line-through; color:#999; margin-right:8px;">$${rPrice.toFixed(2)}</span>
                            <span style="color:inherit; font-weight:900; font-size:1.1em;">$${sPrice.toFixed(2)}</span>
                         </div>`;
        } else {
            priceHtml = `<div style="margin-bottom:8px; font-weight:bold;">$${rPrice.toFixed(2)}</div>`;
        }

        descEl.innerHTML = priceHtml + (desc || "No description available.");

        imgContainer.innerHTML = "";
        if (img) {
            const image = document.createElement('img');
            image.src = img;
            image.className = "details-img";
            image.onerror = function () { this.style.display = 'none'; imgContainer.innerHTML = '<span style="color:#aaa">Image failed to load</span>'; };
            imgContainer.appendChild(image);
            imgContainer.style.display = 'flex';
        } else {
            imgContainer.style.display = 'none';
        }

        overlay.style.display = 'block';
        bubble.style.display = 'block';
    }

    function closeDetails() {
        document.getElementById('bubble-overlay').style.display = 'none';
        document.getElementById('details-bubble').style.display = 'none';
    }

    // --- FLEX RENDER FUNCTIONS ---

    function renderMatrixGroupFlex(title, products, colDefs, bgColor, txtColor) {
        // rowLabels based on first appearance (Sheet Order)
        const rowLabels = [...new Set(products.map(p => p.variation || "Standard"))];

        // Default colors if missing
        const headerBg = bgColor || "var(--md-sys-color-primary)";
        const headerTx = txtColor || "var(--md-sys-color-on-primary)";

        // Add 'matrix-scroll-view' class for CSS targeting
        let html = `<div class="table-container matrix-scroll-view">`;

        // 2. Data Rows
        rowLabels.forEach(rowLabel => {
            const rowProducts = products.filter(p => (p.variation || "Standard") === rowLabel);
            const rowIsSale = rowProducts.some(p => p.onSale);
            const rowAvailable = rowProducts.some(p => p.isAvailable);



            // Matrix body row — flex-start so columns pack tight at declared widths,
            // not spread across the full row by space-between.
            html += `<div style="display:flex; flex-direction:row; flex-wrap:nowrap; align-items:center;
                         justify-content:flex-start; gap:4px; padding:8px 12px;
                         border-bottom:1px solid rgba(0,0,0,0.05); width:100%; box-sizing:border-box;">`;

            // Sticky name column — fixed minimum width
            html += `<div style="position:sticky; left:0; background:var(--md-sys-color-surface); z-index:1;
                          border-right:1px solid rgba(0,0,0,0.1); flex:1; min-width:120px; padding-right:8px;
                          display:flex; align-items:center; font-family:var(--md-ref-typeface-brand),sans-serif;">
                <span>${rowLabel}</span>
                ${rowIsSale ? '<span class="sale-badge" style="transform:scale(0.8);margin-left:4px;">SALE</span>' : ''}
                ${!rowAvailable ? '<span class="sale-badge" style="background:#999;transform:scale(0.8);margin-left:4px;">N/A</span>' : ''}
             </div>`;

            colDefs.forEach(col => {
                let prod;
                if (col.isVar2) {
                    prod = products.find(p => (p.variation || "Standard") === rowLabel && String(p.variation2) === String(col.name));
                } else {
                    prod = products.find(p => (p.variation || "Standard") === rowLabel && p.name === col.name);
                }

                html += `<div style="width:48px !important; max-width:48px !important; min-width:48px !important;
                    flex:0 0 48px !important; overflow:hidden;
                    display:flex; flex-direction:column; justify-content:center; align-items:center; padding:2px;">`;

                if (prod) {
                    const isSale = !!prod.onSale;
                    const hasDiscountValue = isSale && (Number(prod.salePrice) > 0 && Number(prod.salePrice) < Number(prod.price));
                    const displayPrice = hasDiscountValue ? prod.salePrice : prod.price;
                    const priceClass = isSale ? "sale-price-highlight" : "";
                    const priceStr = Number(displayPrice).toFixed(2);

                    const isAvail = prod.isAvailable;
                    const disabledAttr = isAvail ? "" : "disabled";
                    const bgStyle = isAvail ? "" : "background: #f0f0f0; color: #aaa;";

                    // ID is now JUST the SKU
                    html += `<input type="number" class="qty-input matrix-input" id="${prod.sku}" value="0" min="0"
            data-sku="${prod.sku}" data-price="${displayPrice}"
            data-name="${prod.name}${prod.variation ? ' - ' + prod.variation : ''}${prod.variation2 ? ' (' + prod.variation2 + ')' : ''}"
            data-units="${prod.unitsPerCase}"
            onchange="calculateTotal()"
            ${disabledAttr}
            style="width:44px !important; max-width:44px !important; text-align:center; border:1px solid #ccc; border-radius:4px; padding:3px 1px; font-size:13px; box-sizing:border-box; ${bgStyle}">`;

                    html += `<div class="price-sub ${priceClass}" style="font-size:10px; margin-top:2px; overflow:hidden; max-width:48px; text-overflow:ellipsis; white-space:nowrap;">$${priceStr}</div>`;
                    if (hasDiscountValue) {
                        html += `<div class="price-sub original-price-strikethrough" style="font-size:9px;">$${Number(prod.price).toFixed(2)}</div>`;
                    }
                } else {
                    html += `<div style="background:#f9f9f9; width:100%; height:100%;"></div>`;
                }

                html += `</div>`;
            });
            html += `</div>`; // End row container
        });
        html += `</div>`; // End container
        return html;
    }

    function renderCompactTableFlex(title, products) {
        // Identify common spec to avoid redundancy in row labels (v1.8.20)
        const uniqueSpecs = [...new Set(products.map(p => String(p.variation2 || "").trim()).filter(v => v && v.toLowerCase() !== "undefined"))];
        const commonSpec = uniqueSpecs.length === 1 ? uniqueSpecs[0] : "";

        // 1. Group by Variation to merge Single + Case
        const merged = {};
        const orderedKeys = []; // Keep track of insertion order for Sheet Sort

        products.forEach(p => {
            let label = p.variation;

            // Skip if variation is a header/placeholder value AND product looks invalid
            const isHeaderValue = /^(type|variation|size|option|var|n\/a|none|-|standard)$/i.test(String(label).trim());
            if (isHeaderValue) {
                // Check if this looks like a real product (has price and SKU)
                const hasValidPrice = p.price && Number(p.price) > 0;
                const hasValidSKU = p.sku && String(p.sku).trim().length > 0;

                if (!hasValidPrice || !hasValidSKU) {
                    // Skip this product entirely - it's likely a header/template row
                    return;
                }
                // If it has valid data, treat as no variation and continue
                label = "";
            }

            if (!label || label === "Standard") {
                // Regex: Match Base Name at START of string only
                const regex = new RegExp("^" + escapeRegex(title), "i");
                if (title && p.name) {
                    label = p.name.replace(regex, "").trim();
                    // Remove leading special chars that might remain (like " - " or " & ")
                    label = label.replace(/^[\s\-&]+/, "").trim();
                }
            }
            if (!label) label = "Standard"; // Fallback
            label = String(label).trim();

            // Key by Var1-derived label — products with same Var1 (e.g. same flavor but
            // Single + Case formats) merge into one row, sorted into Single/Case slots below.
            const key = label;

            if (!merged[key]) {
                // Append Var2 as a suffix label only when it differs across the group
                // (i.e. not a common spec shared by every product).
                const v2 = String(p.variation2 || "").trim();
                const displaySpec = (v2 && v2 !== commonSpec) ? ` (${v2})` : "";
                const fullLabel = label + displaySpec;

                merged[key] = { label: fullLabel, single: null, case: null, variations: [] };
                orderedKeys.push(key);
            }

            // Collect all variants for this key to sort them into slots correctly later
            merged[key].variations.push(p);
        });

        // 2. Map Collected Variations to Single/Case Slots
        orderedKeys.forEach(key => {
            const entry = merged[key];
            const vars = entry.variations;

            // Sort logic: 
            // 1. Identify Master Case (Highest Tier)
            // 2. Identify Carton (Middle Tier)
            // 3. Identify Single (Lowest Tier)

            const masterCase = vars.find(v => {
                const combined = (String(v.variation || "") + " " + String(v.variation2 || "") + " " + String(v.variation3 || "")).toLowerCase();
                return (combined.includes("master") || combined.includes("multi")) && v.hasCase;
            });

            const carton = vars.find(v => {
                if (v === masterCase) return false;
                const combined = (String(v.variation || "") + " " + String(v.variation2 || "") + " " + String(v.variation3 || "")).toLowerCase();
                return (combined.includes("carton") || combined.includes("box") || v.hasCase);
            });

            const explicitSingle = vars.find(v => {
                if (v === masterCase || v === carton) return false;
                const combined = (String(v.variation || "") + " " + String(v.variation2 || "") + " " + String(v.variation3 || "")).toLowerCase();
                return combined.includes("single") || !v.hasCase;
            });

            // SCENARIO 1: Master Case + Carton
            if (masterCase && carton) {
                entry.case = masterCase;
                entry.single = carton;
            }
            // SCENARIO 2: Master Case + Single
            else if (masterCase && explicitSingle) {
                entry.case = masterCase;
                entry.single = explicitSingle;
            }
            // SCENARIO 3: Carton + Single
            else if (carton && explicitSingle) {
                entry.case = carton;
                entry.single = explicitSingle;
            }
            // SCENARIO 4: Two variations, one is a Case
            else if (vars.length === 2 && vars.some(v => v.hasCase) && vars.some(v => !v.hasCase)) {
                entry.case = vars.find(v => v.hasCase);
                entry.single = vars.find(v => !v.hasCase);
            }
            // SCENARIO 5: Just Case (Master or Carton)
            else if (masterCase || carton) {
                entry.case = masterCase || carton;
            }
            // FALLBACK: Sort by hasCase property (Directive v1.8.19)
            else {
                vars.forEach(v => {
                    if (v.hasCase) {
                        entry.case = v;
                    } else {
                        entry.single = v;
                    }
                });
            }
        });

        // Use VALIDATED SHEET ORDER (No alphabetic sort)
        const rows = orderedKeys.map(k => merged[k]);

        // Determine if we need the "Case" column at all for this group
        const hasAnyCase = rows.some(r => r.case !== null);
        const hasAnySingle = rows.some(r => r.single !== null);

        let html = `<div class="table-container">`;

        rows.forEach(r => {
            const pSingle = r.single;
            const pCase = r.case;
            // Use single as leader for badges/info, fallback to case
            const leader = pSingle || pCase;

            // Sales Logic (Leader)
            const isSale = leader.onSale; // Show badge if onSale is true

            // Info Icon
            let infoHtml = "";
            if (leader.description || leader.image) {
                const cleanName = (leader.name + " " + (leader.variation || "")).replace(/"/g, "&quot;");
                const cleanDesc = (leader.description || "").replace(/"/g, "&quot;");
                const cleanImg = (leader.image || "").replace(/"/g, "&quot;");
                const sPrice = Number(leader.salePrice) || 0;
                const rPrice = Number(leader.price) || 0;
                infoHtml = `<span class="info-icon" onclick="showDetails(event, '${cleanName}', '${cleanDesc}', '${cleanImg}', '${sPrice}', '${rPrice}')">i</span>`;
            }

            // Check Availability

            const isSingleAvail = pSingle && pSingle.isAvailable;
            const isCaseAvail = pCase && pCase.isAvailable;
            const isRowAvailable = isSingleAvail || isCaseAvail;

            html += `<div class="grid-data-row">`;
            html += `<div class="col-name">
                ${r.label} 
                ${isSale ? '<span class="sale-badge">SALE</span>' : ''}
                ${!isRowAvailable ? '<span class="sale-badge" style="background:#999;">Not Available</span>' : ''}
                ${infoHtml}
                </div>`;

            // --- SINGLE COLUMN (only rendered when the group has at least one single product) ---
            if (hasAnySingle) {
                html += `<div class="col-data">`;
                if (pSingle) {
                    const isSingleSale = !!pSingle.onSale;
                    const hasDiscount = isSingleSale && (Number(pSingle.salePrice) > 0 && Number(pSingle.salePrice) < Number(pSingle.price));
                    const displayPrice = hasDiscount ? pSingle.salePrice : pSingle.price;
                    const priceClass = isSingleSale ? "sale-price-highlight" : "";

                    const disabledAttr = pSingle.isAvailable ? "" : "disabled";
                    const bgStyle = pSingle.isAvailable ? "" : "background: #f0f0f0; color: #aaa;";

                    html += `<input type="number" inputmode="numeric" pattern="[0-9]*" min="0" 
            id="${pSingle.sku}"
            data-sku="${pSingle.sku}" data-type="unit"
            data-category="${leader.category}" data-group="${escapeRegex(title)}"
            class="table-input qty-input" placeholder="0"
            ${disabledAttr} style="${bgStyle}">`;

                    // ALWAYS Show Price
                    html += `<div class="price-sub ${priceClass}">$${Number(displayPrice).toFixed(2)}</div>`;
                    if (hasDiscount) {
                        html += `<div class="price-sub original-price-strikethrough" style="font-size:9px;">$${Number(pSingle.price).toFixed(2)}</div>`;
                    }
                }
                html += `</div>`;
            } // end hasAnySingle

            // --- CASE COLUMN ---
            if (hasAnyCase) {
                html += `<div class="col-data">`;
                if (pCase) {
                    const units = parseInt(pCase.unitsPerCase);
                    const isCaseSale = !!pCase.onSale;
                    const hasCaseDiscount = isCaseSale && (Number(pCase.salePrice) > 0 && Number(pCase.salePrice) < Number(pCase.price));
                    const casePrice = hasCaseDiscount ? pCase.salePrice : pCase.price;
                    const cPriceClass = isCaseSale ? "sale-price-highlight" : "";

                    const disabledAttrCase = pCase.isAvailable ? "" : "disabled";
                    const bgStyleCase = pCase.isAvailable ? "" : "background: #f0f0f0; color: #aaa;";

                    html += `<input type="number" inputmode="numeric" pattern="[0-9]*" min="0" 
            id="${pCase.sku}"
            data-sku="${pCase.sku}" data-type="case" data-per-case="${units}"
            data-category="${leader.category}" data-group="${escapeRegex(title)}"
            class="table-input qty-input" placeholder="0"
            ${disabledAttrCase} style="${bgStyleCase}">`;

                    // ALWAYS Show Price
                    html += `<div class="price-sub ${cPriceClass}">$${Number(casePrice).toFixed(2)}</div>`;
                    if (hasCaseDiscount) {
                        html += `<div class="price-sub original-price-strikethrough" style="font-size:9px;">$${Number(pCase.price).toFixed(2)}</div>`;
                    }
                } else {
                    html += `<span style="color:#ccc;">-</span>`;
                }
                html += `</div>`;
            }

            html += `</div>`;
        });

        html += `</div>`;
        return html;
    }

    function attachInputListeners() {
        const inputs = document.querySelectorAll('.qty-input');
        inputs.forEach(input => {
            input.removeEventListener('input', calculateTotal); // Safety
            input.addEventListener('input', calculateTotal);
        });
    }

    /**
     * Calculate Totals
     */
    function calculateTotal() {
        let totalPieces = 0;
        let grandTotal = 0;
        let totalCommission = 0;

        // Maps to store element totals
        const catTotals = new Map();
        const prodTotals = new Map();

        const inputs = document.querySelectorAll('.qty-input');

        // Reset Spans first (Visual reset)
        // We will only show them if > 0 later
        document.querySelectorAll('.cat-subtotal, .prod-subtotal').forEach(el => {
            el.style.display = 'none';
        });

        inputs.forEach(input => {
            const val = parseInt(input.value) || 0;
            if (val > 0) {
                // Find product in catalog
                const sku = input.dataset.sku;
                const product = PRODUCT_CATALOG.find(p => p.sku === sku);
                if (!product) return;

                const isOnSale = !!product.onSale;
                const price = isOnSale ? (parseFloat(product.salePrice) || 0) : (parseFloat(product.price) || 0);

                // Bulletproof units calculation
                const units = parseInt(product.unitsPerCase) || parseInt(product.unitsMultiplier) || 1;

                // Bulletproof commission with fallback defaults
                const commRate = isOnSale
                    ? (parseFloat(product.saleCommission) || 1.0)   // Default $1.00 on sale
                    : (parseFloat(product.commissionRate) || 1.5);  // Default $1.50 regular

                const lineTotal = price * val;
                const pieces = val * units;
                const lineComm = commRate * val * units;  // Commission = rate × quantity × units

                totalPieces += pieces;
                grandTotal += lineTotal;
                totalCommission += lineComm;

                // Aggregate for Headers
                const pDetails = input.closest('.product-group-details');
                const cDetails = input.closest('.category-details');

                if (pDetails) {
                    prodTotals.set(pDetails, (prodTotals.get(pDetails) || 0) + lineTotal);
                }
                if (cDetails) {
                    catTotals.set(cDetails, (catTotals.get(cDetails) || 0) + lineTotal);
                }
            }
        });

        // Update UI elements (with null checks for hidden admin elements)
        const piecesEl = document.getElementById('total-pieces');
        const commEl = document.getElementById('total-commission');
        if (piecesEl) piecesEl.textContent = totalPieces;
        if (commEl) commEl.textContent = `$${totalCommission.toFixed(2)} `;
        document.getElementById('header-total').textContent = `Total: $${grandTotal.toFixed(2)} `;

        // Store values for later use in order submission
        window._calculatedTotalPieces = totalPieces;
        window._calculatedTotalCommission = totalCommission;

        // Update UI Spans
        for (const [el, total] of catTotals.entries()) {
            const span = el.querySelector('.category-summary .cat-subtotal');
            if (span && total > 0) {
                span.textContent = `$${total.toFixed(2)} `;
                span.style.display = 'block';
            }
        }

        for (const [el, total] of prodTotals.entries()) {
            // prodSummary is the first child summary
            const summary = el.querySelector('summary');
            if (summary) {
                const span = summary.querySelector('.prod-subtotal');
                if (span && total > 0) {
                    span.textContent = `$${total.toFixed(2)} `;
                    span.style.display = 'block';
                }
            }
        }
    }

    /**
     * Submit Order
     */
    /**
     * REVIEW & SUBMIT LOGIC
     */
    let lastItems = []; // Store for Summary and Submission

    function gatherItems() {
        const inputs = document.querySelectorAll('.qty-input');
        const itemsMap = {}; // Map sku to total qty

        // Aggregate quantities by SKU
        // Classic FOR loop for maximum compatibility
        for (let i = 0; i < inputs.length; i++) {
            const input = inputs[i];
            const val = parseInt(input.value) || 0;
            if (val > 0) {
                const sku = input.getAttribute('data-sku');
                const type = input.getAttribute('data-type');

                if (sku) {
                    if (!itemsMap[sku]) itemsMap[sku] = 0;
                    itemsMap[sku] += val;
                }
            }
        }

        const items = Object.keys(itemsMap).map(sku => ({
            sku: sku,
            quantity: itemsMap[sku] // Total units
        }));

        return items;
    }

    function reviewOrder() {
        let stage = "Start";
        try {
            stage = "Gathering Items";
            const items = gatherItems();

            if (items.length === 0) {
                alert("Please add at least one item.");
                return;
            }

            stage = "Saving Global";
            lastItems = items; // Save global state

            // Generate HTML
            stage = "Generating Summary";
            const tableHtml = generateOrderSummaryHtml(items);

            stage = "Rendering Summary";
            const summaryContainer = document.getElementById('review-summary-container');
            if (summaryContainer) summaryContainer.innerHTML = tableHtml;

            // Populate Review Client Info
            stage = "Getting Client Info";
            const nameInput = document.getElementById('client-name-input');
            const addrInput = document.getElementById('client-address-input');
            const cName = nameInput ? nameInput.value : "";
            const cAddr = addrInput ? addrInput.value : "";

            stage = "Rendering Client Info";
            const revInfo = document.getElementById('review-client-info');
            if (revInfo) {
                const comments = document.getElementById('client-comments-input').value;
                revInfo.innerHTML = `
                <div style="margin-bottom:8px;"><strong>Bill To:</strong> ${cName}</div>
                    <div style="margin-bottom:8px;"><strong>Address:</strong> ${cAddr}</div>
                ${comments ? `<div><strong>Comments:</strong> ${comments}</div>` : ""}
            `;
            }

            // Switch Views
            stage = "Switching Views";
            const formEl = document.getElementById('order-form');
            const reviewEl = document.getElementById('review-view');
            if (formEl) formEl.classList.add('hidden');
            if (reviewEl) reviewEl.classList.remove('hidden');

            if (window.scrollTo) window.scrollTo(0, 0);

        } catch (e) {
            alert(`Error in Review(${stage}): ` + e.message);
            console.error(e);
        }
    }

    function backToEdit() {
        document.getElementById('review-view').classList.add('hidden');
        document.getElementById('order-form').classList.remove('hidden');
        window.scrollTo(0, 0);
    }

    function confirmOrder() {
        // Disable UI
        const btn = document.getElementById('confirm-btn');
        btn.disabled = true;
        btn.textContent = "Submitting...";

        // Show Loading Overlay
        const loader = document.getElementById('loading');
        const loadText = document.getElementById('loading-text');
        if (loadText) loadText.textContent = "Processing Order...";
        loader.classList.remove('hidden');

        const summaryToggle = document.getElementById('use-summary-pdf-toggle');
        const payload = {
            id: window.editOrderId,
            editOrderId: window.editOrderId, // For revision tracking
            clientId: CLIENT_ID,
            clientName: document.getElementById('client-name-input').value,
            clientAddress: document.getElementById('client-address-input').value,
            clientComments: document.getElementById('client-comments-input').value,
            items: lastItems,
            totalPieces: window._calculatedTotalPieces || 0,
            useSummaryPdf: summaryToggle ? summaryToggle.checked : false
        };

        google.script.run
            .withSuccessHandler(onOrderSuccess)
            .withFailureHandler(onFailure)
            .processOrder(payload);
    }

    function generateOrderSummaryHtml(items) {
        // Group items by their orderFormNumber so each form gets its own section.
        const formGroups = {}; // { formNum: [enrichedItems] }
        const formOrder = []; // maintain insertion order

        items.forEach(item => {
            const product = PRODUCT_CATALOG.find(p => p.sku === item.sku);
            const formNum = (product && product.orderFormNumber) ? String(product.orderFormNumber).trim() : '1';

            if (!formGroups[formNum]) {
                formGroups[formNum] = [];
                formOrder.push(formNum);
            }

            if (product) {
                const v1 = (product.variation && product.variation !== "Standard") ? product.variation : "";
                const v2 = String(product.variation2 || "").trim();
                let name = product.name;
                if (v1) name += ` ${v1}`;
                if (v2 && v2 !== v1) name += ` (${v2})`;

                const isSale = !!product.onSale;
                const finalPrice = isSale ? (Number(product.salePrice) || 0) : (Number(product.price) || 0);
                const lineTotal = finalPrice * item.quantity;

                formGroups[formNum].push({ name, quantity: item.quantity, lineTotal, found: true });
            } else {
                formGroups[formNum].push({ name: item.sku, quantity: item.quantity, lineTotal: 0, found: false });
            }
        });

        const multiForm = formOrder.length > 1;
        let html = '';
        let overallTotal = 0;

        formOrder.forEach(formNum => {
            const groupItems = formGroups[formNum];
            let formTotal = 0;
            groupItems.forEach(i => formTotal += i.lineTotal);
            overallTotal += formTotal;

            if (multiForm) {
                html += `<div style="margin-bottom:16px;">
                    <div style="font-weight:700; font-size:12px; padding:6px 8px; background:var(--md-sys-color-primary,#e67700); color:#fff; border-radius:4px 4px 0 0; margin-bottom:0;">
                        Form ${formNum}
                    </div>`;
            }

            html += `<table style="width:100%; border-collapse:collapse; font-size:12px; ${multiForm ? 'border-radius:0 0 4px 4px;' : ''}">
                <thead style="background:#f0f0f0; font-weight:bold;">
                    <tr>
                        <th style="padding:8px; border:1px solid #ddd; text-align:left;">Product</th>
                        <th style="padding:8px; border:1px solid #ddd;">Qty</th>
                        <th style="padding:8px; border:1px solid #ddd; text-align:right;">Line Total</th>
                    </tr>
                </thead><tbody>`;

            groupItems.forEach(i => {
                html += `<tr>
                    <td style="padding:8px; border:1px solid #ddd;">${i.name}</td>
                    <td style="padding:8px; border:1px solid #ddd; text-align:center;">${i.quantity}</td>
                    <td style="padding:8px; border:1px solid #ddd; text-align:right;">${i.found ? '$' + i.lineTotal.toFixed(2) : '-'}</td>
                </tr>`;
            });

            // Per-form subtotal
            html += `<tr style="font-weight:bold; background:#fafafa;">
                    <td colspan="2" style="padding:8px; border:1px solid #ddd; text-align:right;">${multiForm ? 'Form ' + formNum + ' Subtotal:' : 'Total:'}</td>
                    <td style="padding:8px; border:1px solid #ddd; text-align:right;">$${formTotal.toFixed(2)}</td>
                </tr>`;

            html += `</tbody></table>`;
            if (multiForm) html += `</div>`;
        });

        // Grand total row when there are multiple forms
        if (multiForm) {
            html += `<table style="width:100%; border-collapse:collapse; font-size:12px; margin-top:4px;">
                <tr style="font-weight:900; background:#e8f5e9;">
                    <td colspan="2" style="padding:8px; border:2px solid #4caf50; text-align:right; color:#1b5e20;">Grand Total:</td>
                    <td style="padding:8px; border:2px solid #4caf50; text-align:right; color:#1b5e20; width:100px;">$${overallTotal.toFixed(2)}</td>
                </tr>
            </table>`;
        }

        return html;
    }

    function onOrderSuccess(response) {
        document.getElementById('review-view').classList.add('hidden'); // Hide Review
        document.getElementById('order-form').classList.add('hidden'); // Ensure Form Hidden
        document.getElementById('success').classList.remove('hidden');
        document.getElementById('header-total').classList.add('hidden'); // Hide Subtotal
        document.getElementById('loading').classList.add('hidden'); // Hide Spinner

        // Use the same summary generation for success page or just clone it
        const summaryDiv = document.createElement('div');
        summaryDiv.style.marginTop = "20px";
        summaryDiv.style.textAlign = "left";

        summaryDiv.innerHTML = `<h3 class="md3-title-medium" style="margin-bottom:10px;">Order Summary</h3>` + generateOrderSummaryHtml(lastItems);

        const successEl = document.getElementById('success');
        successEl.appendChild(summaryDiv);
    }

    function onFailure(error) {
        showError("Error: " + error.message);
        const btn = document.getElementById('confirm-btn');
        if (btn) {
            btn.disabled = false;
            btn.textContent = "Confirm & Submit";
        }
        // Restore Review visibility
        document.getElementById('loading').classList.add('hidden');
    }

    function showError(msg) {
        document.getElementById('loading').classList.add('hidden');
        document.getElementById('error').classList.remove('hidden');
        document.getElementById('error-msg').textContent = msg;
    }
</script>

<script>
    /**
     * Setup IntersectionObserver to detect when Product Headers stick
     */
    function setupStickyObservers() {
        // Sticky logic depends on scrolling, fallback to classic loops
        window.removeEventListener('scroll', checkStickyHeaders);
        window.addEventListener('scroll', checkStickyHeaders);
        checkStickyHeaders(); // Initial check
    }

    function checkStickyHeaders() {
        const summaries = document.querySelectorAll('.product-group-details > summary:not(.no-sticky)');
        const STICKY_TOP = 60; // Top offset

        for (let i = 0; i < summaries.length; i++) {
            const summary = summaries[i];
            const rect = summary.getBoundingClientRect();
            if (rect.top <= STICKY_TOP + 1) {
                summary.classList.add('is-pinned');
            } else {
                summary.classList.remove('is-pinned');
            }
        }
    }

    /**
     * Group products by a "Base Name"
     */
    function groupProductsDynamically(products) {
        const groups = {};
        products.forEach(p => {
            if (p.isParent) return;

            // Grouping: Prefer clean groupName (Directive v1.8.16: Strict Trim)
            const gName = String(p.groupName || "").trim();
            const pName = String(p.name || "").trim();
            const pSku = String(p.sku || "").trim();

            let base = (gName && !gName.toLowerCase().includes("unnamed")) ? gName : (pName || pSku || "Unknown Product");

            // Cleanup: Strip trailing attributes if header is dynamic
            if (!gName || gName.toLowerCase().includes("unnamed")) {
                base = base.replace(/\s*(\d+(\.\d+)?\s*(mg|g|ml|oz|lb|kg|pcs|pack|pk)|#\d+|\d+)$/i, "").trim();
            }

            // Final safety filter for "empty" strings or system placeholders
            if (!base || base.toLowerCase() === "undefined" || base === "Uncategorized Item" || base === "Unnamed Product") {
                base = "Uncategorized Item";
            }

            if (!groups[base]) groups[base] = [];
            groups[base].push(p);
        });
        return groups;
    }

    /**
     * Check if a group should be displayed as a 2D Matrix
     */
    function checkMatrixEligibility(products, groupName) {
        // Matrix mode when Variation 2 has multiple unique values.
        //   - Var1 values (Flavor) → rows  (empty Var1 = one "Standard" row)
        //   - Var2 values (Strength/Format) → columns
        // Works for both "Var1 rows × Var2 columns" and "Standard row × Var2 columns".
        const uniqueVar2 = [...new Set(products.map(p => String(p.variation2 || "").trim()).filter(v => v && v.toLowerCase() !== "undefined"))];

        if (uniqueVar2.length > 1) return true;

        // Single or no Var2 value → stay in Compact mode (Single/Case column logic).
        return false;
    }

    /**
     * Update My Information Modal Functions
     */
    function showUpdateInfoModal() {
        // Pre-populate with current values
        document.getElementById('update-client-id').value = CLIENT_ID || '';
        document.getElementById('update-client-name').value = document.getElementById('client-name-input').value || '';

        // Parse existing address into components
        const fullAddress = document.getElementById('client-address-input').value || '';
        const addrParts = fullAddress.split(',').map(s => s.trim());

        document.getElementById('update-addr-street').value = addrParts[0] || '';
        document.getElementById('update-addr-city').value = addrParts[1] || '';

        // Province and Postal may be combined as "ON N0P 1A0"
        if (addrParts[2]) {
            const provPostal = addrParts[2].trim();
            // Try to split province from postal code (e.g. "ON N0P 1A0")
            const postalMatch = provPostal.match(/^([A-Za-z]{2})\s+(.+)$/);
            if (postalMatch) {
                document.getElementById('update-addr-province').value = postalMatch[1];
                document.getElementById('update-addr-postal').value = postalMatch[2];
            } else {
                document.getElementById('update-addr-province').value = provPostal;
                document.getElementById('update-addr-postal').value = '';
            }
        } else {
            document.getElementById('update-addr-province').value = '';
            document.getElementById('update-addr-postal').value = '';
        }

        document.getElementById('update-info-modal').classList.remove('hidden');
    }

    function closeUpdateInfoModal(event) {
        // Close if clicking overlay background or explicitly called
        if (!event || event.target === document.getElementById('update-info-modal')) {
            document.getElementById('update-info-modal').classList.add('hidden');
        }
    }

    function submitInfoUpdate() {
        // Compose address from multi-field inputs
        const street = document.getElementById('update-addr-street').value.trim();
        const city = document.getElementById('update-addr-city').value.trim();
        const province = document.getElementById('update-addr-province').value.trim();
        const postal = document.getElementById('update-addr-postal').value.trim();

        let addressParts = [];
        if (street) addressParts.push(street);
        if (city) addressParts.push(city);
        let provPostal = '';
        if (province && postal) provPostal = province + ' ' + postal;
        else if (province) provPostal = province;
        else if (postal) provPostal = postal;
        if (provPostal) addressParts.push(provPostal);
        const composedAddress = addressParts.join(', ');

        const updateData = {
            originalClientId: CLIENT_ID,
            newClientId: document.getElementById('update-client-id').value.trim(),
            newClientName: document.getElementById('update-client-name').value.trim(),
            newAddress: composedAddress
        };

        if (!updateData.newClientId && !updateData.newClientName && !updateData.newAddress) {
            showToast('Please enter at least one field to update.', 'error');
            return;
        }

        // Show loading state
        const submitBtn = document.querySelector('#update-info-modal .md3-btn-filled');
        const originalText = submitBtn.textContent;
        submitBtn.textContent = 'Submitting...';
        submitBtn.disabled = true;

        google.script.run
            .withSuccessHandler(function (result) {
                submitBtn.textContent = originalText;
                submitBtn.disabled = false;

                if (result && result.success) {
                    showToast('Update request submitted! Changes pending approval.', 'success');
                    closeUpdateInfoModal();
                } else {
                    showToast('Error: ' + (result.message || 'Could not submit update.'), 'error');
                }
            })
            .withFailureHandler(function (error) {
                submitBtn.textContent = originalText;
                submitBtn.disabled = false;
                showToast('Error submitting update: ' + error.message, 'error');
            })
            .saveClientInfoUpdate(updateData);
    }
</script>