/* --------------------------------------------------------- * server.js — Giftwrap Multi-Tenant E-Commerce API Server * Merged Version: Correct Boot Order + All Features * --------------------------------------------------------- */ "use strict"; const express = require("express"); const path = require("path"); const cors = require("cors"); const session = require("express-session"); const fs = require("fs"); const fsp = require("fs/promises"); const crypto = require("crypto"); const http = require("http"); const bodyParser = require("body-parser"); const cookieParser = require("cookie-parser"); require("dotenv").config(); const config = require("./config"); const pool = require("./database"); // Gate (from corrected version) const { createGateRouter, requireGate } = require("./server/gateRoutes"); const { createStsRouter } = require("./routes/stsRouter"); const { createJwksRouter } = require("./routes/jwksRouter"); const { createOidcRouter } = require("./routes/oidcRouter"); const { createDomainRouter } = require("./routes/domainRouter"); const { requestId: requestIdMw } = require("./middleware/requestId"); const { requireAuth } = require("./middleware/requireAuth"); const { entitlementsMiddleware } = require("./middleware/entitlements"); const { createAdminRbacRouter } = require("./routes/adminRbacRouter"); const { requirePermission } = require("./middleware/requirePermission"); // Journey (optional; if you have it) let createJourneyRouter = null; try { ({ createJourneyRouter } = require("./routes/journeyRouter")); } catch { } /* --------------------------------------------------------- * Utils, Services, Middleware, Controllers * --------------------------------------------------------- */ const { pickIncomingHost, normaliseHost } = require("./utils/host"); const { resolveTenantByDomain } = require("./services/tenantService"); const { mintAppToken } = require("./services/authTokenService"); const { applyThemeAndSeo } = require("./services/seoService"); const { tenantResolver } = require("./middleware/tenantResolver"); const { requireAppToken } = require("./middleware/appToken"); const appTokenGuard = requireAppToken(pool); // Refactored Utilities (Act I & II) const { getProto, buildOrigin, toAbsoluteUrl, resolveProductImageUrl } = require("./utils/url"); const { calculatePriceWithMarkup, calculateVatInclusive, toNum } = require("./utils/price"); const { shapeProductRow, safeJsonParse } = require("./utils/productFormatter"); const { createQuoteRequestTable, createCollectionsTable } = require("./models/legacy/QuoteRequestTable"); const { resolveLegacyTarget, normalizePath: normalizeLegacyPath, loadLegacyRedirects, SKIP_PREFIXES: LEGACY_SKIP_PREFIXES, } = require("./utils/legacyRedirects"); const { createDiscoveryRouter, createSearchRouter, createContentRouter, createSitemapRouter, createMediaRouter, createProductPageRouter, brandingController, protectedImageController, authController, productsController, apiController, } = require("./controllers"); const roleController = require("./controllers/roleController"); const createAdminRouter = require("./controllers/adminController"); const createEmailController = require("./controllers/emailController"); // FROM YOUR VERSION - All these services const { createLeadRouter } = require("./controllers/leadsController"); const { createQuoteDispatchRoutes } = require("./routes/quoteDispatchRoutes"); const AILoggingService = require('./services/aiAssistanceService'); const EnhancedAIAssistant = require('./services/enhancedAIAssistant'); const RealTimeMonitor = require('./services/realTimeMonitor'); const aiAnalyticsController = require('./controllers/aiAnalyticsController'); const { createWhatsAppService } = require("./services/whatsappService"); const createPagesRouter = require("./routes/pages"); const createBlogRouter = require("./routes/blog"); const { productManagementController } = require('./controllers/productManagementController'); // Migration controller returns an express router with initWebSocketServer attached const migrationController = require('./controllers/migration.controller')(pool); const createBillingRoutes = require('./routes/billingRoutes'); const { createCollectionRouter: createRealCollectionRouter } = require('./controllers/collectionController'); // const { createMegaMenuControllerV2 } = require("./controllers/megaMenuControllerV2"); // ... /* --------------------------------------------------------- * Optional (guarded) deps * --------------------------------------------------------- */ let helmet = null, compression = null, morgan = null, rateLimit = null, requestId = null; try { helmet = require("helmet"); } catch { } try { compression = require("compression"); } catch { } try { morgan = require("morgan"); } catch { } try { rateLimit = require("express-rate-limit"); } catch { } try { requestId = require("express-request-id")(); } catch { } /* --------------------------------------------------------- * Config * --------------------------------------------------------- */ const PORT = process.env.PORT || 3000; const NODE_ENV = process.env.NODE_ENV || "development"; const IS_PROD = NODE_ENV === "production"; const LEGACY_REDIRECTS = loadLegacyRedirects(path.join(__dirname, "data", "legacy-urls.csv")); const APP_ORIGIN = process.env.APP_ORIGIN || `http://localhost:${PORT}`; const sessionSecret = process.env.SESSION_SECRET; if (IS_PROD && !sessionSecret) { throw new Error("SESSION_SECRET environment variable is required in production"); } const finalSessionSecret = sessionSecret || "dev_session_secret_change_me"; // PRODUCT_IMAGE_BASE moved to src/utils/url.js /* --------------------------------------------------------- * App & Trust Proxy * --------------------------------------------------------- */ const app = express(); app.locals.pool = pool; // Request ID early app.use(requestIdMw()); app.use(session({ name: "gw.sid", secret: process.env.SESSION_SECRET, resave: false, saveUninitialized: false, proxy: true, cookie: { httpOnly: true, sameSite: "lax", secure: process.env.NODE_ENV === "production", // prod only path: "/", }, })) app.set("trust proxy", 1); if (requestId) app.use(requestId); app.use((req, res, next) => { if (req.url.includes('/api/v3')) { console.log(`[TOP-DEBUG] Request: ${req.method} ${req.url}`); } next(); }); /* --------------------------------------------------------- * Cookie Parser and Body Parsers (ONCE HERE - Correct Order) * --------------------------------------------------------- */ app.use(cookieParser(process.env.COOKIE_SECRET || "cookie_dev")); app.use(express.json({ limit: "5mb" })); app.use(express.urlencoded({ extended: true, limit: "5mb" })); /* --------------------------------------------------------- * Security headers (lightweight) * --------------------------------------------------------- */ app.use((req, res, next) => { // HSTS (only meaningful over HTTPS; harmless otherwise) res.setHeader("Strict-Transport-Security", "max-age=31536000; includeSubDomains; preload"); next(); }); /* --------------------------------------------------------- * CORS * --------------------------------------------------------- */ const allowedOrigins = [ "http://localhost:5172", "http://127.0.0.1:5172", "http://localhost:5173", "http://127.0.0.1:5173", "http://localhost:3000", "http://127.0.0.1:3000", "http://localhost:8080", "http://127.0.0.1:8080", "https://giftwrap.co.za", "https://www.giftwrap.co.za", "https://admin.giftwrap.co.za", "https://mobi.giftwrap.co.za" ]; app.use( cors({ origin: (origin, cb) => { if (!origin) return cb(null, true); if (allowedOrigins.includes(origin)) return cb(null, true); console.warn("[CORS] Blocked origin:", origin); return cb(new Error("Not allowed by CORS")); }, credentials: true, }) ); /* --------------------------------------------------------- * Security + Compression + Logging * --------------------------------------------------------- */ if (compression) app.use(compression()); if (morgan) app.use(morgan(IS_PROD ? "combined" : "dev")); if (helmet) { app.use( helmet({ contentSecurityPolicy: { directives: { defaultSrc: ["'self'", "https:", "http:", "data:", "blob:", "'unsafe-inline'", "'unsafe-eval'"], scriptSrc: ["'self'", "'unsafe-inline'", "'unsafe-eval'", "https:", "http:"], styleSrc: ["'self'", "'unsafe-inline'", "https:", "http:"], imgSrc: ["'self'", "data:", "https:", "http:", "blob:"], fontSrc: ["'self'", "https:", "http:", "data:"], connectSrc: ["'self'", "https:", "http:", "ws:", "wss:"], frameSrc: ["'self'", "https:", "http:"], objectSrc: ["'none'"], baseUri: ["'self'"], }, }, crossOriginEmbedderPolicy: false, referrerPolicy: { policy: "no-referrer-when-downgrade" }, }) ); } /* --------------------------------------------------------- * Sessions (BEFORE any routes that need sessions) * --------------------------------------------------------- */ app.use( session({ secret: finalSessionSecret, resave: false, saveUninitialized: false, name: "gw.sid", cookie: { httpOnly: true, secure: IS_PROD, sameSite: IS_PROD ? "none" : "lax", maxAge: 1000 * 60 * 60, }, }) ); /* --------------------------------------------------------- * Rate Limit (optional) * --------------------------------------------------------- */ if (rateLimit) { app.use( "/api/", rateLimit({ windowMs: 15 * 60 * 1000, max: 1500, standardHeaders: true, legacyHeaders: false, }) ); } /* --------------------------------------------------------- * API Request Logger * --------------------------------------------------------- */ app.use((req, res, next) => { const start = Date.now(); res.on("finish", () => { if (!req.path.startsWith("/api/")) return; const ms = Date.now() - start; console.log("[API]", { id: req.id || null, status: res.statusCode, ms, method: req.method, path: req.originalUrl, origin: req.headers.origin || null, host: req.headers.host || null, tenantId: req.tenant?.id || null, authenticated: !!req.session?.authenticated, }); }); next(); }); /* --------------------------------------------------------- * FROM YOUR VERSION: User Behavior Tracking Middleware * --------------------------------------------------------- */ app.use((req, res, next) => { // Don't track API health checks or static files if (req.path === '/healthz' || req.path === '/readyz' || req.path.startsWith('/public/') || req.path.startsWith('/node_modules/')) { return next(); } const startTime = Date.now(); // Log request completion res.on('finish', async () => { try { if (!req.tenant?.id) return; const sessionId = req.headers['x-session-id'] || req.sessionID; const userId = req.session?.user?.id || null; const userAgent = req.get('User-Agent'); const ipAddress = req.ip || req.connection.remoteAddress; // Determine event type based on route let eventType = 'page_view'; let eventSubtype = null; if (req.path.startsWith('/api/')) { eventType = 'api_call'; eventSubtype = req.path.split('/')[2]; // Extract resource type } else if (req.path.includes('/products/')) { eventType = 'product_view'; } else if (req.path.includes('/categories/')) { eventType = 'category_view'; } else if (req.path.includes('/cart')) { eventType = 'cart_interaction'; } // Log user behavior await AILoggingService.logUserBehavior({ tenantId: req.tenant.id, sessionId, userId, eventType, eventSubtype, pageUrl: `${req.protocol}://${req.get('host')}${req.originalUrl}`, referrerUrl: req.get('referer'), userAgent, ipAddress, eventData: { method: req.method, statusCode: res.statusCode, responseTime: Date.now() - startTime, path: req.path } }); // Log performance for API endpoints if (req.path.startsWith('/api/')) { await AILoggingService.logPerformanceMetric({ tenantId: req.tenant.id, endpoint: req.path, method: req.method, responseTime: Date.now() - startTime, statusCode: res.statusCode, userId, success: res.statusCode < 400, errorType: res.statusCode >= 400 ? `http_${res.statusCode}` : null, requestSize: req.headers['content-length'] ? parseInt(req.headers['content-length']) : null, responseSize: res.get('Content-Length') ? parseInt(res.get('Content-Length')) : null }); } } catch (error) { console.error('Error logging user behavior:', error.message); } }); next(); }); /* --------------------------------------------------------- * Static Files * --------------------------------------------------------- */ app.use( express.static(path.join(__dirname, "public"), { setHeaders: (res, filePath) => { res.setHeader("X-Content-Type-Options", "nosniff"); if (IS_PROD && filePath.includes(`${path.sep}assets${path.sep}`)) { res.setHeader("Cache-Control", "public, max-age=31536000, immutable"); } else { res.setHeader("Cache-Control", IS_PROD ? "public, max-age=600" : "no-cache"); } }, }) ); // Serve the SPA dist folder for assets app.use(express.static(path.join(__dirname, "public", "bdls-mts", "dist"))); if (!IS_PROD) { app.use("/node_modules", express.static(path.join(__dirname, "node_modules"))); // Add static route for frontend source files app.use("/src", express.static(path.join(__dirname, "public", "bdls-mts", "frontend", "src"))); } /* --------------------------------------------------------- * Favicon (stop 404 spam) * --------------------------------------------------------- */ function serveIfExistsOr204(relativePath) { return (_req, res) => { const abs = path.join(__dirname, "public", relativePath); if (fs.existsSync(abs)) return res.sendFile(abs); return res.status(204).end(); }; } app.get("/favicon.ico", serveIfExistsOr204("favicon.ico")); app.get("/favicon-16.png", serveIfExistsOr204("favicon-16.png")); app.get("/favicon-32.png", serveIfExistsOr204("favicon-32.png")); app.get("/apple-touch-icon.png", serveIfExistsOr204("apple-touch-icon.png")); /* --------------------------------------------------------- * Health / Ready * --------------------------------------------------------- */ function safeAsync(fn) { return (req, res, next) => Promise.resolve(fn(req, res, next)).catch(next); } app.get("/healthz", (_req, res) => res.type("text").send("ok")); app.get( "/readyz", safeAsync(async (_req, res) => { try { await pool.query("SELECT 1"); res.type("text").send("ready"); } catch { res.status(500).type("text").send("not-ready"); } }) ); /* --------------------------------------------------------- * Tenant Resolution Middleware (MUST be before /api gate checks) * --------------------------------------------------------- */ app.use(tenantResolver(pool)); // app.use("/api/v2/mega-menu", createMegaMenuControllerV2(pool)); // const { createMegaMenuController } = require("./controllers/megaMenuController"); // const megaMenuRouter = createMegaMenuController(pool, appTokenGuard); // server.js (example) const { storefrontV2Controller } = require("./controllers/storefrontV2Controller"); // app.use(storefrontV2Controller(pool, requireAppToken)); app.use("/api/v2", storefrontV2Controller(pool, { appTokenGuard })); // In your main Express app (same as storefront): // Middleware flow is identical: // 1. Tenant resolution (from storefront middleware) // 2. Permission checking // 3. Configuration loading // 4. Route handling /* --------------------------------------------------------- * FROM YOUR VERSION: WhatsApp Service Initialization * --------------------------------------------------------- */ const wa = createWhatsAppService({ endpoint: process.env.WATI_API_ENDPOINT, token: process.env.WATI_TOKEN, defaultCountry: "ZA", }); function devGuard(req, res, next) { // if (process.env.NODE_ENV === "production" && req.headers["x-dev-key"] !== process.env.DEV_TEST_KEY) { // return res.status(401).json({ success: false, error: "unauthorized" }); // } next(); } /* --------------------------------------------------------- * Gate (CORRECT MOUNT ORDER - AFTER sessions + tenant) * --------------------------------------------------------- */ // Protect everything else under /api, but allow bootstrap endpoints const gateMw = requireGate(pool); const entMw = entitlementsMiddleware(pool); app.use("/api/gate", createGateRouter(pool)); // STS + JWKS (public, but rate-limited inside router later) app.use("/.well-known", createJwksRouter()); app.use("/sts", createStsRouter(pool)); // OIDC auth app.use("/auth", createOidcRouter()); // Domain management (protect with session auth + entitlements headers) app.use("/api/admin", requireAuth, entMw, requirePermission("admin.domains"), createDomainRouter(pool)); // RBAC admin (roles/permissions/invites) app.use("/api/admin", requireAuth, entMw, requirePermission("admin.rbac"), createAdminRbacRouter(pool)); app.use('/api/admin/migration', migrationController); // migrationController.initWebSocketServer(server); // Protect everything else under /api, but allow bootstrap endpoints app.get("/api/v3/ping", (req, res) => res.json({ success: true, message: "v3 ping from server.js" })); // MOVED BEFORE GATE: /api/categories (public browsing) // This matches what Categories.vue expects app.get("/api/categories", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1; const categories = await getCategoryHierarchy(req, pool, tenantId); res.json({ success: true, categories }); })); app.use("/api", (req, res, next) => { const fullPath = req.originalUrl || req.path; let pathWithoutApi = fullPath.replace(/^\/api/, "") || "/"; // Allow public / read-only endpoints (public browsing resilience) const PUBLIC_GET_PREFIXES = [ "/v2/", // storefront v2 read endpoints (products, categories, featured, search) "/v3/", // storefront v3 catalog read endpoints "/catalog/products", // legacy catalog listing "/pages", // CMS page content "/blog", // blog/article reads "/store/categories", // storefront category tree/detail reads "/store/products", // popular/featured "/store/collections", // storefront collection reads "/collections", // collection listings "/discovery/", // trending/recommended "/products", // generic product reads (v1/v3 aliases) "/product", // legacy product pages "/categories", // category listings "/category", // legacy category pages "/sitemap", // sitemap endpoints "/search", // search endpoints "/tenants/resolve", "/auth/", "/auth/me", "/gate", "/branding/", ]; const PUBLIC_GET_EXACT = new Set([ "/whatsapp/branding-request", "/whatsapp/webhook", "/dev/test-whatsapp", "/ai/shop-assistant", "/store/quote", ]); if (req.method === "GET") { if (PUBLIC_GET_EXACT.has(pathWithoutApi)) return next(); if (PUBLIC_GET_PREFIXES.some((p) => pathWithoutApi.startsWith(p))) return next(); } // Non-GET exceptions (webhooks etc.) if (pathWithoutApi === "/whatsapp/webhook") return next(); if (pathWithoutApi.startsWith("/gate")) return next(); // Require OIDC auth if (!req.session?.authenticated || !req.session?.user) { req.session.postAuthRedirect = req.originalUrl; return res.status(401).json({ success: false, error: "unauthorized" }); } // Enforce bot gate (recaptcha/session) on all protected API calls, even when authenticated, // then inject entitlements/headers. return gateMw(req, res, (err) => { if (err) return next(err); return entMw(req, res, next); }); }); /* --------------------------------------------------------- * Optional Journey router (behind gate because it's under /api) * --------------------------------------------------------- */ if (typeof createJourneyRouter === "function") { app.use( "/api/journey", createJourneyRouter(pool, { requireGate: (_req, _res, next) => next(), getTenantId: (req) => req.tenant?.id ?? 1, getUserId: (req) => req.user?.id ?? req.session?.user?.id ?? null, }) ); } /* --------------------------------------------------------- * FROM YOUR VERSION: Mount Lead Router and Pages Router * --------------------------------------------------------- */ app.use(createLeadRouter(pool, appTokenGuard)); app.use("/api", createPagesRouter(pool)); app.use("/api", createBlogRouter(pool)); /* --------------------------------------------------------- * Helpers * --------------------------------------------------------- */ function clampInt(v, min, max, fallback) { const n = Number(v); if (!Number.isFinite(n)) return fallback; return Math.max(min, Math.min(max, Math.trunc(n))); } // Helpers moved to src/utils/ function normStr(v) { return String(v || "").trim(); } async function safeApplyThemeAndSeo(pool, html, opts) { try { if (typeof applyThemeAndSeo === "function") return await applyThemeAndSeo(pool, html, opts); } catch (e) { console.warn("[SEO] applyThemeAndSeo failed - falling back:", e?.message || e); } return html; } /* --------------------------------------------------------- * DB schema feature detection * --------------------------------------------------------- */ let PRICE_HAS_COST_MARKUP = false; async function detectSchema() { try { const [c1] = await pool.query("SHOW COLUMNS FROM catalog_product_prices LIKE 'cost_price'"); const [c2] = await pool.query("SHOW COLUMNS FROM catalog_product_prices LIKE 'markup_pct'"); PRICE_HAS_COST_MARKUP = (c1?.length || 0) > 0 && (c2?.length || 0) > 0; console.log("[Schema] PRICE_HAS_COST_MARKUP =", PRICE_HAS_COST_MARKUP); } catch (e) { PRICE_HAS_COST_MARKUP = false; console.warn("[Schema] detectSchema failed:", e?.message || e); } } detectSchema(); /* --------------------------------------------------------- * FROM YOUR VERSION: Create Tables * --------------------------------------------------------- */ /* --------------------------------------------------------- * FROM YOUR VERSION: Create Tables (Refactored) * --------------------------------------------------------- */ // async function createQuoteRequestTable... moved to src/models/legacy/QuoteRequestTable.js // Call the functions (passing pool) createQuoteRequestTable(pool); createCollectionsTable(pool); /* --------------------------------------------------------- * Public API: Tenant Resolve (allowed by requireGate allowlist) * --------------------------------------------------------- */ app.get( "/api/tenants/resolve", safeAsync(async (req, res) => { const requested = String(req.query.domain || req.query.host || "").trim(); const host = requested ? normaliseHost(requested) : normaliseHost(pickIncomingHost(req)); const t = req.tenant || (await resolveTenantByDomain(pool, host, req)); if (!t) return res.status(404).json({ success: false }); res.json({ success: true, tenant: t }); }) ); /* --------------------------------------------------------- * Compatibility: /api/auth/me (always 200) * --------------------------------------------------------- */ app.get("/api/auth/me", (req, res) => { const authenticated = !!req.session?.authenticated; const user = req.session?.user || null; res.json({ success: true, authenticated, user, tenant: req.tenant || null, data: { authenticated, user }, }); }); /* --------------------------------------------------------- * Shapes a DB row into a rich product object. * --------------------------------------------------------- */ // shapeProductRow moved to src/utils/productFormatter.js /* --------------------------------------------------------- * Helper to get cost/markup select fields * --------------------------------------------------------- */ function getCostMarkupSelect(tableAlias = 'p') { if (!PRICE_HAS_COST_MARKUP) return ''; return `, ( SELECT cost_price FROM catalog_product_prices WHERE product_id = ${tableAlias}.id AND is_active = 1 AND min_qty >= 1 AND (effective_from IS NULL OR effective_from <= CURRENT_DATE()) AND (effective_to IS NULL OR effective_to >= CURRENT_DATE()) ORDER BY (min_qty <= 1) DESC, ABS(min_qty - 1) ASC, COALESCE(effective_from, '1000-01-01') DESC LIMIT 1 ) AS cost_price, ( SELECT markup_pct FROM catalog_product_prices WHERE product_id = ${tableAlias}.id AND is_active = 1 AND min_qty >= 1 AND (effective_from IS NULL OR effective_from <= CURRENT_DATE()) AND (effective_to IS NULL OR effective_to >= CURRENT_DATE()) ORDER BY (min_qty <= 1) DESC, ABS(min_qty - 1) ASC, COALESCE(effective_from, '1000-01-01') DESC LIMIT 1 ) AS markup_pct`; } function shapeProducts(req, rows) { return (rows || []).map((r) => shapeProductRow(req, r)); } // Safely serialise responses that might contain accidental circular refs function safeJsonResponse(res, payload) { const seen = new WeakSet(); const replacer = (_key, value) => { if (typeof value === "object" && value !== null) { if (seen.has(value)) return undefined; // drop circular seen.add(value); } return value; }; try { res.json(payload); } catch (err) { try { res.type("application/json").send(JSON.stringify(payload, replacer)); } catch { // Last resort: send minimal error res.status(500).json({ success: false, error: "Serialization failed" }); } } } /* --------------------------------------------------------- * Mount core controllers (ALL under /api are already gated) * --------------------------------------------------------- */ app.use("/api/ai-analytics", aiAnalyticsController); app.use("/api/auth", authController); app.use("/api/billing", createBillingRoutes(authController)); app.use("/api/products", productsController); app.use("/api/branding", brandingController); app.use("/protected-image", protectedImageController); app.use("/api/admin/roles", roleController); app.use("/api/admin", createAdminRouter(pool)); // Mount product management routes BEFORE the general /api/admin routes to avoid conflict const productMgmtRouter = productManagementController(pool); app.use( '/api/admin/product-management', requireAuth, entMw, requirePermission("admin.products"), productMgmtRouter ); app.use( '/api/admin/products', requireAuth, entMw, requirePermission("admin.products"), productMgmtRouter ); const emailController = createEmailController(pool); app.use("/api", emailController); /* --------------------------------------------------------- * FROM YOUR VERSION: Quote Dispatch Routes * --------------------------------------------------------- */ app.use(createQuoteDispatchRoutes({ safeAsync })); /* --------------------------------------------------------- * CATEGORY SYSTEM - Robust hierarchy builder * --------------------------------------------------------- */ const CATEGORY_CACHE_TTL_MS = clampInt(process.env.CATEGORY_CACHE_TTL_MS, 10_000, 3_600_000, 300_000); const categoryCache = new Map(); async function buildCategoryIndex(req, tenantId) { const [cats] = await pool.query( ` SELECT id, tenant_id, name, slug, description, image_path, icon_path, sort_order, is_featured, page_title, meta_description, parent_id, created_at, updated_at FROM catalog_categories WHERE tenant_id = ? AND COALESCE(is_active,1) = 1 ORDER BY sort_order, name `, [tenantId] ); const [cntRows] = await pool.query( ` SELECT pc.category_id, COUNT(DISTINCT p.id) AS product_count_direct FROM catalog_product_categories pc JOIN catalog_categories c ON c.id = pc.category_id JOIN catalog_products p ON p.id = pc.product_id WHERE c.tenant_id = ? AND COALESCE(c.is_active,1) = 1 AND p.tenant_id = ? AND p.status = 'active' AND COALESCE(p.is_active,1) = 1 GROUP BY pc.category_id `, [tenantId, tenantId] ); const directCount = new Map(cntRows.map((r) => [Number(r.category_id), Number(r.product_count_direct || 0)])); const byId = new Map(); const bySlug = new Map(); for (const r of cats) { const id = Number(r.id); const parentId = r.parent_id == null || Number(r.parent_id) === 0 ? null : Number(r.parent_id); const direct = directCount.get(id) || 0; const node = { id, uniqueId: `${tenantId}-${id}`, tenant_id: Number(tenantId), name: r.name, slug: r.slug, description: r.description || null, parent_id: parentId, sort_order: Number(r.sort_order || 0), is_featured: Number(r.is_featured || 0), image_path: r.image_path || null, icon_path: r.icon_path || null, image_url: r.image_path ? toAbsoluteUrl(req, r.image_path) : null, icon_url: r.icon_path ? toAbsoluteUrl(req, r.icon_path) : null, page_title: r.page_title || null, meta_description: r.meta_description || null, product_count_direct: direct, productCountDirect: direct, product_count: direct, productCount: direct, depth: 0, path: String(id), children: [], subcategories: [], created_at: r.created_at || null, updated_at: r.updated_at || null, }; byId.set(id, node); if (node.slug) bySlug.set(String(node.slug).toLowerCase(), node); } const roots = []; for (const node of byId.values()) { if (node.parent_id && byId.has(node.parent_id)) { byId.get(node.parent_id).children.push(node); } else { roots.push(node); } } const sortKids = (n) => { n.children.sort((a, b) => a.sort_order - b.sort_order || a.name.localeCompare(b.name)); for (const c of n.children) sortKids(c); }; roots.sort((a, b) => a.sort_order - b.sort_order || a.name.localeCompare(b.name)); for (const r of roots) sortKids(r); const dfs = (n, depth, pathArr) => { n.depth = depth; const nextPath = [...pathArr, n.id]; n.path = nextPath.join(","); let total = Number(n.product_count_direct || 0); for (const c of n.children) total += dfs(c, depth + 1, nextPath); n.product_count = total; n.productCount = total; n.subcategories = n.children; return total; }; for (const r of roots) dfs(r, 0, []); return { tenantId, builtAt: Date.now(), roots, byId, bySlug }; } async function getCategoryIndex(req, tenantId) { const key = Number(tenantId || 1); const cached = categoryCache.get(key); if (cached && Date.now() - cached.builtAt < CATEGORY_CACHE_TTL_MS) return cached; const fresh = await buildCategoryIndex(req, key); categoryCache.set(key, fresh); return fresh; } async function getCategoryHierarchy(req, _pool, tenantId, rootId = null) { const idx = await getCategoryIndex(req, tenantId); if (rootId) { const node = idx.byId.get(Number(rootId)); return node ? [node] : []; } return idx.roots; } async function getCategoryBreadcrumb(req, _pool, tenantId, categoryId) { const idx = await getCategoryIndex(req, tenantId); let cur = idx.byId.get(Number(categoryId)); if (!cur) return []; const out = []; while (cur) { out.push({ id: cur.id, name: cur.name, slug: cur.slug }); cur = cur.parent_id ? idx.byId.get(cur.parent_id) : null; } out.reverse(); return out.map((x, i) => ({ ...x, level: i })); } async function getCategoryDescendants(req, _pool, tenantId, categoryId) { const idx = await getCategoryIndex(req, tenantId); const start = idx.byId.get(Number(categoryId)); if (!start) return []; const ids = []; const walk = (n) => { ids.push(n.id); for (const c of n.children) walk(c); }; walk(start); return ids; } async function getCategoryBySlug(req, tenantId, slug) { const idx = await getCategoryIndex(req, tenantId); return idx.bySlug.get(String(slug || "").toLowerCase()) || null; } /* --------------------------------------------------------- * NEW: /api/categories/grouped (from corrected version) * --------------------------------------------------------- */ app.get( "/api/categories/grouped", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1; const includeEmpty = String(req.query.include_empty || "false").toLowerCase() === "true"; const maxDepth = clampInt(req.query.max_depth, 1, 10, 3); try { const hierarchy = await getCategoryHierarchy(req, pool, tenantId); const grouped = { root: [], byParent: {} }; const processNode = (node, depth = 0) => { if (depth > maxDepth) return; const categoryData = { id: Number(node.id), name: node.name, slug: node.slug, description: node.description || null, parent_id: node.parent_id, sort_order: node.sort_order, is_featured: node.is_featured, icon_url: node.icon_url, image_url: node.image_url, product_count: node.product_count, productCount: node.productCount, has_children: (node.children || []).length > 0, depth, }; if (!includeEmpty && node.product_count === 0 && (node.children || []).length === 0) return; if (node.parent_id === null) grouped.root.push(categoryData); else { if (!grouped.byParent[node.parent_id]) grouped.byParent[node.parent_id] = []; grouped.byParent[node.parent_id].push(categoryData); } for (const child of node.children || []) processNode(child, depth + 1); }; for (const root of hierarchy) processNode(root, 0); grouped.root.sort((a, b) => a.sort_order - b.sort_order || a.name.localeCompare(b.name)); for (const parentId of Object.keys(grouped.byParent)) { grouped.byParent[parentId].sort((a, b) => a.sort_order - b.sort_order || a.name.localeCompare(b.name)); } return res.json({ success: true, grouped, total_root: grouped.root.length, total_groups: Object.keys(grouped.byParent).length + 1, data: { grouped }, }); } catch (error) { console.error("[Categories Grouped Error]:", error); return res.status(500).json({ success: false, error: "Failed to fetch grouped categories", message: error.message, }); } }) ); /* --------------------------------------------------------- * /api/categories (admin) * --------------------------------------------------------- */ app.get( "/api/categories", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1; const format = String(req.query.format || "hierarchical").toLowerCase(); const limit = Math.min(Number(req.query.limit) || 500, 1000); const page = req.query.page != null ? clampInt(req.query.page, 1, 1000000, 1) : null; const offset = req.query.offset != null ? Math.max(Number(req.query.offset) || 0, 0) : page != null ? (page - 1) * limit : 0; const search = String(req.query.search || req.query.q || "").trim(); try { if (format === "hierarchical") { let categories = await getCategoryHierarchy(req, pool, tenantId); if (search) { const q = search.toLowerCase(); const filterKeepParents = (nodes) => (nodes || []) .map((n) => { const kids = filterKeepParents(n.children || []); const match = (n.name || "").toLowerCase().includes(q) || (n.description || "").toLowerCase().includes(q); return match || kids.length ? { ...n, children: kids, subcategories: kids } : null; }) .filter(Boolean); categories = filterKeepParents(categories); } return res.json({ success: true, categories, format: "hierarchical", total: Array.isArray(categories) ? categories.length : 0, data: { categories }, }); } if (format === "flat") { const where = [`tenant_id=?`, `COALESCE(is_active,1)=1`]; const params = [tenantId]; if (search) { where.push(`(name LIKE ? OR slug LIKE ? OR description LIKE ?)`); params.push(`%${search}%`, `%${search}%`, `%${search}%`); } const [rows] = await pool.query( `SELECT SQL_CALC_FOUND_ROWS id, name, slug, description, parent_id, sort_order, is_featured, icon_path FROM catalog_categories WHERE ${where.join(" AND ")} ORDER BY sort_order, name LIMIT ? OFFSET ?`, [...params, limit, offset] ); const [[found]] = await pool.query("SELECT FOUND_ROWS() AS total"); const total = Number(found?.total || 0); const categoryIds = rows.map((r) => r.id); const productCounts = {}; if (categoryIds.length > 0) { const [countRows] = await pool.query( ` SELECT pc.category_id, COUNT(DISTINCT p.id) as product_count FROM catalog_product_categories pc JOIN catalog_products p ON p.id = pc.product_id WHERE pc.category_id IN (?) AND p.tenant_id = ? AND p.status = 'active' AND COALESCE(p.is_active,1) = 1 GROUP BY pc.category_id `, [categoryIds, tenantId] ); countRows.forEach((row) => { productCounts[row.category_id] = Number(row.product_count); }); } const categories = rows.map((cat) => ({ id: Number(cat.id), name: cat.name, slug: cat.slug, description: cat.description || null, parent_id: cat.parent_id == null || Number(cat.parent_id) === 0 ? null : Number(cat.parent_id), sort_order: Number(cat.sort_order || 0), is_featured: Number(cat.is_featured || 0), icon_url: cat.icon_path ? toAbsoluteUrl(req, cat.icon_path) : null, meta: {}, product_count: productCounts[cat.id] || 0, productCount: productCounts[cat.id] || 0, })); const pages = Math.max(1, Math.ceil(total / limit) || 1); const currentPage = page || Math.floor(offset / limit) + 1; const has_more = offset + categories.length < total; return res.json({ success: true, categories, format: "flat", pagination: { limit, offset, page: currentPage, pages, total, has_more }, data: { categories, pagination: { limit, offset, page: currentPage, pages, total, has_more } }, }); } const categories = await getCategoryHierarchy(req, pool, tenantId); return res.json({ success: true, categories, format: "hierarchical", data: { categories } }); } catch (error) { console.error("[Categories API Error]:", error); return res.status(500).json({ success: false, error: "Failed to fetch categories", message: error.message }); } }) ); /* --------------------------------------------------------- * Storefront categories list * --------------------------------------------------------- */ app.get( "/api/store/categories", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1; const format = String(req.query.format || "hierarchical").toLowerCase(); const search = String(req.query.search || req.query.q || "").trim(); const featured = String(req.query.featured || "").toLowerCase() === "true"; const include = String(req.query.include || "children,product_count") .split(",") .map((s) => s.trim()) .filter(Boolean); const limit = Math.min(Number(req.query.limit) || 100, 250); try { let categories = []; if (format === "hierarchical") { let hierarchy = await getCategoryHierarchy(req, pool, tenantId); const filterKeepParents = (nodes, q) => (nodes || []) .map((n) => { const kids = filterKeepParents(n.children || [], q); const match = (n.name || "").toLowerCase().includes(q) || (n.description || "").toLowerCase().includes(q); return match || kids.length ? { ...n, children: kids, subcategories: kids } : null; }) .filter(Boolean); if (search) hierarchy = filterKeepParents(hierarchy, search.toLowerCase()); if (featured) { const flattenFeatured = (nodes) => { let out = []; for (const n of nodes || []) { if (Number(n.is_featured || 0) === 1) out.push(n); if (n.children?.length) out = out.concat(flattenFeatured(n.children)); } return out; }; categories = flattenFeatured(hierarchy).slice(0, limit); } else { categories = hierarchy; } return res.json({ success: true, categories, format: "hierarchical", total: categories.length, data: { categories, total: categories.length }, }); } // flat mode const where = [`tenant_id=?`, `COALESCE(is_active,1)=1`]; const params = [tenantId]; if (search) { where.push(`(name LIKE ? OR slug LIKE ? OR description LIKE ?)`); params.push(`%${search}%`, `%${search}%`, `%${search}%`); } if (featured) where.push(`is_featured = 1`); const [rows] = await pool.query( `SELECT id, name, slug, description, parent_id, sort_order, is_featured, icon_path FROM catalog_categories WHERE ${where.join(" AND ")} ORDER BY sort_order, name LIMIT ?`, [...params, limit] ); const categoryIds = rows.map((r) => r.id); const productCounts = {}; if (categoryIds.length > 0) { const [countRows] = await pool.query( ` SELECT pc.category_id, COUNT(DISTINCT p.id) as product_count FROM catalog_product_categories pc JOIN catalog_products p ON p.id = pc.product_id WHERE pc.category_id IN (?) AND p.tenant_id = ? AND p.status = 'active' AND COALESCE(p.is_active,1) = 1 GROUP BY pc.category_id `, [categoryIds, tenantId] ); countRows.forEach((row) => { productCounts[row.category_id] = Number(row.product_count); }); } categories = rows.map((cat) => ({ id: Number(cat.id), name: cat.name, slug: cat.slug, description: cat.description || null, parent_id: cat.parent_id == null || Number(cat.parent_id) === 0 ? null : Number(cat.parent_id), sort_order: Number(cat.sort_order || 0), is_featured: Number(cat.is_featured || 0), icon_url: cat.icon_path ? toAbsoluteUrl(req, cat.icon_path) : null, meta: {}, product_count: productCounts[cat.id] || 0, productCount: productCounts[cat.id] || 0, })); if (include.includes("children")) { const parentIds = categories.map((c) => c.id); if (parentIds.length) { const [childRows] = await pool.query( `SELECT id, name, slug, description, parent_id, sort_order, is_featured, icon_path FROM catalog_categories WHERE tenant_id=? AND COALESCE(is_active,1)=1 AND parent_id IN (?) ORDER BY parent_id, sort_order, name`, [tenantId, parentIds] ); const childIds = childRows.map((c) => c.id); const childProductCounts = {}; if (childIds.length) { const [childCountRows] = await pool.query( ` SELECT pc.category_id, COUNT(DISTINCT p.id) as product_count FROM catalog_product_categories pc JOIN catalog_products p ON p.id = pc.product_id WHERE pc.category_id IN (?) AND p.tenant_id = ? AND p.status = 'active' AND COALESCE(p.is_active,1) = 1 GROUP BY pc.category_id `, [childIds, tenantId] ); childCountRows.forEach((row) => { childProductCounts[row.category_id] = Number(row.product_count); }); } const childrenByParent = childRows.reduce((acc, child) => { const pid = Number(child.parent_id || 0) || null; if (!acc[pid]) acc[pid] = []; acc[pid].push({ id: Number(child.id), name: child.name, slug: child.slug, description: child.description || null, parent_id: pid, sort_order: Number(child.sort_order || 0), is_featured: Number(child.is_featured || 0), icon_url: child.icon_path ? toAbsoluteUrl(req, child.icon_path) : null, product_count: childProductCounts[child.id] || 0, productCount: childProductCounts[child.id] || 0, }); return acc; }, {}); categories.forEach((cat) => { cat.children = childrenByParent[cat.id] || []; cat.subcategories = cat.children; cat.subcategory_count = cat.children.length; }); } } return res.json({ success: true, categories, format: "flat", total: categories.length, data: { categories, total: categories.length }, }); } catch (error) { console.error("[Store Categories Error]:", error); return res.status(500).json({ success: false, error: "Failed to fetch categories", message: error.message }); } }) ); /* --------------------------------------------------------- * Storefront category detail (slug) + SERVER FILTERS * --------------------------------------------------------- */ const STOCK_EXPR = `CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.stock_qty')),'0') AS UNSIGNED)`; app.get( "/api/store/categories/:slug", safeAsync(async (req, res) => { const slug = normStr(req.params.slug); const tenantId = req.tenant?.id; const include = normStr(req.query.include || "parent,children,products,breadcrumb") .split(",") .map((s) => s.trim()) .filter(Boolean); const productSearch = normStr(req.query.product_search || req.query.search || req.query.q); const stock = normStr(req.query.stock || "all").toLowerCase(); const pMinRaw = req.query.price_min ?? req.query.product_price_min; const pMaxRaw = req.query.price_max ?? req.query.product_price_max; const priceMin = toNum(pMinRaw, null); const priceMax = toNum(pMaxRaw, null); const limit = Math.min(Number(req.query.product_limit) || 12, 100); const page = Math.max(Number(req.query.product_page) || 1, 1); const offsetFromQuery = Math.max(Number(req.query.product_offset) || 0, 0); const offset = offsetFromQuery > 0 ? offsetFromQuery : (page - 1) * limit; const sort = normStr(req.query.product_sort || "name_asc").toLowerCase(); try { const node = await getCategoryBySlug(req, tenantId, slug); if (!node) return res.status(404).json({ success: false, error: "Category not found" }); const response = { id: Number(node.id), name: node.name, slug: node.slug, description: node.description || null, parent_id: node.parent_id, sort_order: node.sort_order, is_featured: node.is_featured, icon_url: node.icon_path ? toAbsoluteUrl(req, node.icon_path) : node.icon_url || null, image_url: node.image_path ? toAbsoluteUrl(req, node.image_path) : node.image_url || null, meta: {}, }; if (include.includes("breadcrumb")) { response.breadcrumb = (await getCategoryBreadcrumb(req, pool, tenantId, node.id)).map((row) => ({ id: row.id, name: row.name, slug: row.slug, level: row.level, })); } if (include.includes("parent") && node.parent_id) { const idx = await getCategoryIndex(req, tenantId); const parent = idx.byId.get(Number(node.parent_id)); response.parent = parent ? { id: Number(parent.id), name: parent.name, slug: parent.slug, description: parent.description || null } : null; } if (include.includes("children") || include.includes("subcategories")) { response.children = (node.children || []).map((child) => ({ id: Number(child.id), name: child.name, slug: child.slug, description: child.description || null, parent_id: child.parent_id, sort_order: child.sort_order, is_featured: child.is_featured, icon_url: child.icon_path ? toAbsoluteUrl(req, child.icon_path) : child.icon_url || null, image_url: child.image_path ? toAbsoluteUrl(req, child.image_path) : child.image_url || null, product_count: Number(child.product_count || 0), productCount: Number(child.product_count || 0), })); response.subcategories = response.children; response.subcategory_count = response.children.length; } const descendantIds = await getCategoryDescendants(req, pool, tenantId, node.id); const nodeIdNum = Number(node.id); if (!descendantIds.includes(nodeIdNum)) descendantIds.push(nodeIdNum); if (descendantIds.length > 0) { const [[countResult]] = await pool.query( ` SELECT COUNT(DISTINCT p.id) AS product_count FROM catalog_product_categories pc JOIN catalog_products p ON p.id = pc.product_id WHERE pc.category_id IN (?) AND p.tenant_id = ? AND p.status = 'active' AND COALESCE(p.is_active,1) = 1 `, [descendantIds, tenantId] ); response.product_count = Number(countResult?.product_count || 0); } else { response.product_count = 0; } response.productCount = response.product_count; if (include.includes("products") && response.product_count > 0) { const baseCurrency = (req.tenant?.base_currency || "ZAR").toUpperCase(); const costMarkupSelect = getCostMarkupSelect('p'); // Build WHERE conditions dynamically const whereConditions = [ "pc.category_id IN (?)", "p.tenant_id = ?", "p.status = 'active'", "COALESCE(p.is_active,1) = 1" ]; const params = [descendantIds, tenantId]; // Add search filter if (productSearch) { whereConditions.push("(p.name LIKE ? OR p.sku LIKE ? OR p.short_description LIKE ? OR p.description LIKE ?)"); const like = `%${productSearch}%`; params.push(like, like, like, like); } // Add stock filter if (stock === "in_stock") { whereConditions.push(`${STOCK_EXPR} > 10`); } else if (stock === "low_stock") { whereConditions.push(`${STOCK_EXPR} BETWEEN 1 AND 10`); } // Build ORDER BY based on sort parameter let orderByClause; switch (sort) { case "name_asc": orderByClause = "p.name ASC"; break; case "name_desc": orderByClause = "p.name DESC"; break; case "price_asc": orderByClause = "price_ex_vat ASC, p.name ASC"; break; case "price_desc": orderByClause = "price_ex_vat DESC, p.name ASC"; break; case "newest": case "created_desc": orderByClause = "p.created_at DESC, p.name ASC"; break; case "created_asc": orderByClause = "p.created_at ASC, p.name ASC"; break; default: orderByClause = "p.name ASC"; } // Price filter conditions let priceWhereConditions = []; if (priceMin != null || priceMax != null) { if (priceMin != null) { priceWhereConditions.push("COALESCE(CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN (COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)) ELSE NULL END, pp.price_ex_vat, pp_fb.price_ex_vat, 0) >= ?"); params.push(priceMin); } if (priceMax != null) { priceWhereConditions.push("COALESCE(CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN (COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)) ELSE NULL END, pp.price_ex_vat, pp_fb.price_ex_vat, 0) <= ?"); params.push(priceMax); } } // Combine all WHERE conditions const allWhereConditions = [...whereConditions]; if (priceWhereConditions.length > 0) { allWhereConditions.push(`(${priceWhereConditions.join(" AND ")})`); } // Build the final SQL query - make sure all ORDER BY columns are in SELECT list const sql = ` SELECT SQL_CALC_FOUND_ROWS DISTINCT p.id, p.sku, p.name, p.slug, p.short_description, p.description AS long_description, p.page_title, p.meta_description, p.supplier_code, p.status, p.base_price, p.selling_price, p.image_url, p.attributes_json, p.brand AS brand, JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.brand')) AS brand_json, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.rating')),'0') AS DECIMAL(3,1)) AS rating, ${STOCK_EXPR} AS stock_qty, p.created_at, -- Added for sorting by created_at COALESCE(CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN (COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)) ELSE NULL END, pp.price_ex_vat, pp_fb.price_ex_vat, 0) AS price_ex_vat, COALESCE(pp.currency_code, pp_fb.currency_code) AS currency_code, COALESCE(pp.min_qty, pp_fb.min_qty, 1) AS min_qty, ( SELECT mm.path FROM ( SELECT i.path, 0 AS is_primary, i.position AS sort_order FROM catalog_product_images i WHERE i.product_id = p.id UNION ALL SELECT m.path, m.is_primary, m.sort_order FROM catalog_product_media m WHERE m.product_id = p.id ) mm ORDER BY mm.is_primary DESC, mm.sort_order ASC LIMIT 1 ) AS primary_image_path, COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.image_url')), '') AS image_url_fallback ${costMarkupSelect} FROM catalog_products p JOIN catalog_product_categories pc ON pc.product_id = p.id LEFT JOIN catalog_product_prices pp ON pp.id = ( SELECT pp2.id FROM catalog_product_prices pp2 WHERE pp2.product_id = p.id AND pp2.is_active = 1 AND (pp2.effective_from IS NULL OR pp2.effective_from <= CURRENT_DATE()) AND (pp2.effective_to IS NULL OR pp2.effective_to >= CURRENT_DATE()) AND pp2.min_qty >= 1 AND pp2.currency_code = ? ORDER BY (pp2.min_qty <= 1) DESC, ABS(pp2.min_qty - 1) ASC, COALESCE(pp2.effective_from,'1000-01-01') DESC LIMIT 1 ) LEFT JOIN catalog_product_prices pp_fb ON pp.id IS NULL AND pp_fb.id = ( SELECT pp3.id FROM catalog_product_prices pp3 WHERE pp3.product_id = p.id AND pp3.is_active = 1 AND (pp3.effective_from IS NULL OR pp3.effective_from <= CURRENT_DATE()) AND (pp3.effective_to IS NULL OR pp3.effective_to >= CURRENT_DATE()) AND pp3.min_qty >= 1 ORDER BY (pp3.min_qty <= 1) DESC, ABS(pp3.min_qty - 1) ASC, COALESCE(pp3.effective_from,'1000-01-01') DESC LIMIT 1 ) WHERE ${allWhereConditions.join(" AND ")} ORDER BY ${orderByClause} LIMIT ? OFFSET ? `; // Add baseCurrency, limit, and offset to params const finalParams = [baseCurrency, ...params, limit, offset]; // Execute the query const [rows] = await pool.query(sql, finalParams); const [[found]] = await pool.query("SELECT FOUND_ROWS() AS total"); const total = Number(found?.total || 0); const products = shapeProducts(req, rows); const pages = Math.max(1, Math.ceil(total / limit) || 1); const currentPage = Math.floor(offset / limit) + 1; const has_more = offset + products.length < total; response.products = products; response.products_pagination = { limit, offset, page: currentPage, pages, total, has_more }; response.filters_applied = { product_search: productSearch || "", stock, price_min: priceMin, price_max: priceMax, product_sort: sort, }; } response.view_mode = response.product_count > 0 ? "products" : response.children?.length > 0 ? "categories" : "empty"; return res.json({ success: true, category: response, data: { category: response } }); } catch (error) { console.error("[Category Detail Error]:", error); console.error("SQL Error Details:", error.sql); console.error("Parameters:", error.params || "No params logged"); return res.status(500).json({ success: false, error: "Failed to fetch category details", message: error.message, }); } }) ); /* --------------------------------------------------------- * Siblings endpoint (recursive CTE) * --------------------------------------------------------- */ app.get( "/api/store/categories/:id/siblings", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1; const categoryId = Number(req.params.id); if (!categoryId) return res.status(400).json({ success: false, error: "invalid category id" }); const limit = clampInt(req.query.limit, 1, 50, 8); const includeSelf = String(req.query.include_self || "").toLowerCase() === "true" || String(req.query.include_self) === "1"; const [[cat]] = await pool.query( `SELECT id, parent_id FROM catalog_categories WHERE tenant_id=? AND id=? AND COALESCE(is_active,1)=1 LIMIT 1`, [tenantId, categoryId] ); if (!cat) return res.status(404).json({ success: false, error: "Category not found" }); const parentIdRaw = cat.parent_id; const parentId = parentIdRaw == null || Number(parentIdRaw) === 0 ? null : Number(parentIdRaw); const parentWhere = parentId ? "c.parent_id = ?" : "(c.parent_id IS NULL OR c.parent_id=0)"; const params = [tenantId]; if (parentId) params.push(parentId); if (!includeSelf) params.push(categoryId); params.push(limit, tenantId, tenantId); const sql = ` WITH RECURSIVE sibs AS ( SELECT c.id, c.name, c.slug, c.description, c.parent_id, c.sort_order, c.is_featured, c.icon_path FROM catalog_categories c WHERE c.tenant_id = ? AND COALESCE(c.is_active,1)=1 AND ${parentWhere} ${includeSelf ? "" : "AND c.id <> ?"} ORDER BY c.sort_order, c.name LIMIT ? ), descs AS ( SELECT s.id AS sibling_id, s.id AS category_id FROM sibs s UNION ALL SELECT d.sibling_id, c2.id FROM catalog_categories c2 JOIN descs d ON c2.parent_id = d.category_id WHERE c2.tenant_id = ? AND COALESCE(c2.is_active,1)=1 ), prod AS ( SELECT d.sibling_id, COUNT(DISTINCT p.id) AS product_count FROM descs d JOIN catalog_product_categories pc ON pc.category_id = d.category_id JOIN catalog_products p ON p.id = pc.product_id WHERE p.tenant_id = ? AND p.status='active' AND COALESCE(p.is_active,1)=1 GROUP BY d.sibling_id ) SELECT s.*, COALESCE(prod.product_count,0) AS product_count FROM sibs s LEFT JOIN prod ON prod.sibling_id = s.id ORDER BY s.sort_order, s.name `; const [rows] = await pool.query(sql, params); const siblings = (rows || []).map((r) => ({ id: Number(r.id), name: r.name, slug: r.slug, description: r.description || null, parent_id: r.parent_id == null || Number(r.parent_id) === 0 ? null : Number(r.parent_id), sort_order: Number(r.sort_order || 0), is_featured: Number(r.is_featured || 0), icon_url: r.icon_path ? toAbsoluteUrl(req, r.icon_path) : null, meta: {}, product_count: Number(r.product_count || 0), productCount: Number(r.product_count || 0), is_current: Number(r.id) === categoryId, })); return res.json({ success: true, category_id: categoryId, parent_id: parentId, siblings, total: siblings.length, data: { siblings, total: siblings.length }, }); }) ); /* --------------------------------------------------------- * FROM YOUR VERSION: WhatsApp Helper Functions * --------------------------------------------------------- */ function parseDataUrl(dataUrl) { const s = String(dataUrl || "").trim(); if (!s) return null; const m = s.match(/^data:([^;]+);base64,(.+)$/i); if (!m) return null; const mimetype = m[1]; const b64 = m[2]; // buffer const buf = Buffer.from(b64, "base64"); return { mimetype, buffer: buf }; } // very light abuse guard (in-memory) const waRate = new Map(); // ip -> { count, resetAt } function rateLimitWa(req, res, next) { const ip = (req.headers["x-forwarded-for"] || req.socket.remoteAddress || "").toString().split(",")[0].trim(); const now = Date.now(); const windowMs = 60_000; // 1 min const max = 12; // 12 req/min per IP const rec = waRate.get(ip) || { count: 0, resetAt: now + windowMs }; if (now > rec.resetAt) { rec.count = 0; rec.resetAt = now + windowMs; } rec.count += 1; waRate.set(ip, rec); if (rec.count > max) { return res.status(429).json({ success: false, error: "Too many requests. Please try again shortly." }); } next(); } // IMPORTANT: adjust if your project root differs const PROTECTED_ROOT = process.env.PROTECTED_ROOT || "/home/bordales/public_html/v1/protected"; const BRANDING_ROOT = path.join(PROTECTED_ROOT, "whatsapp", "branding_requests"); function safeFilename(name, fallback = "file") { const s = String(name || "").trim() || fallback; return s.replace(/[^\w.\-]/g, "_").slice(0, 120); } function extFromMimetype(m) { const mt = String(m || "").toLowerCase(); if (mt === "image/png") return ".png"; if (mt === "image/jpeg") return ".jpg"; if (mt === "image/webp") return ".webp"; if (mt === "image/svg+xml") return ".svg"; if (mt === "application/pdf") return ".pdf"; return ""; } async function ensureDir(p) { await fsp.mkdir(p, { recursive: true, mode: 0o750 }); } function makeReqId() { return `req_${Date.now()}_${crypto.randomBytes(3).toString("hex")}`; } function ymdParts(d) { return { yyyy: String(d.getFullYear()), mm: String(d.getMonth() + 1).padStart(2, "0"), dd: String(d.getDate()).padStart(2, "0"), }; } async function createBrandingRequestFolder(toDigits, now = new Date()) { const { yyyy, mm, dd } = ymdParts(now); const reqId = makeReqId(); const dir = path.join(BRANDING_ROOT, toDigits, yyyy, mm, dd, reqId); await ensureDir(dir); return { dir, reqId, createdAt: now.toISOString() }; } async function writeJson(filePath, obj) { await fsp.writeFile(filePath, JSON.stringify(obj, null, 2), "utf8"); } // Remove any big base64 fields from request.json (keeps it readable) function stripLargeFields(input) { if (!input || typeof input !== "object") return input; if (Array.isArray(input)) return input.map(stripLargeFields); const out = {}; for (const [k, v] of Object.entries(input)) { if (k === "dataUrl" || k === "logoDataUrl") continue; // strip base64 out[k] = stripLargeFields(v); } return out; } /* --------------------------------------------------------- * FROM YOUR VERSION: WhatsApp Branding Request Endpoint * --------------------------------------------------------- */ app.post( "/api/whatsapp/branding-request", // ✅ raise body limit for this route (base64 attachments) express.json({ limit: process.env.WA_BRANDING_BODY_LIMIT || "30mb" }), rateLimitWa, safeAsync(async (req, res) => { const body = req.body || {}; const reqObj = body.request || {}; const attachments = Array.isArray(body.attachments) ? body.attachments : []; // Accept multiple shapes for "to" const to = reqObj.to || reqObj.phone || body.to || body.phone || ""; // Prefer request.name from your payload const contactName = reqObj.name || body.contactName || body.name || "Customer"; // Accept message OR summaryText (your payload uses summaryText) const message = String( body.message || body.summaryText || reqObj.notes || // fallback (optional) "" ).trim(); if (!to) return res.status(400).json({ success: false, error: "Missing 'to' phone number" }); if (!message) return res.status(400).json({ success: false, error: "Missing 'message' or 'summaryText'" }); // Normalise (your wa service does ZA rules) const toDigits = wa.toE164Digits(to); if (!toDigits) return res.status(400).json({ success: false, error: "Invalid phone number" }); // ✅ Create storage folder per-number/per-day/per-request await ensureDir(BRANDING_ROOT); const { dir, reqId, createdAt } = await createBrandingRequestFolder(toDigits, new Date()); // ✅ Save message await fsp.writeFile(path.join(dir, "message.txt"), message, "utf8"); // ✅ Save request payload (without any huge base64 fields) const bodySansLarge = stripLargeFields(body); await writeJson(path.join(dir, "request.json"), { reqId, createdAt, to: toDigits, contactName, email: reqObj.email || body.email || null, channel: body.channel || "whatsapp", body: bodySansLarge, }); // Ensure contact once await wa.ensureContact(toDigits, contactName); // Send text (no ensureContact again) const sentText = await wa.sendText(toDigits, message, { ensureContact: false, contactName, }); // ✅ Handle logoDataUrl (legacy) + attachments[].dataUrl (your payload) const allowed = new Set([ "image/png", "image/jpeg", "image/svg+xml", "image/webp", "application/pdf", ]); const savedFiles = []; const sentFiles = []; // 1) optional legacy logoDataUrl const maybeLogo = body.logoDataUrl ? [{ id: "logo", filename: body.logoFilename, mimetype: null, dataUrl: body.logoDataUrl, caption: body.logoCaption }] : []; // 2) attachments array (your payload) const allUploads = [...maybeLogo, ...attachments]; // guardrails const MAX_ATTACHMENTS = Number(process.env.WA_BRANDING_MAX_ATTACHMENTS || 8); const MAX_BYTES = Number(process.env.WA_BRANDING_MAX_BYTES || 15 * 1024 * 1024); // 15MB per file (WhatsApp-ish) for (const [idx, att] of allUploads.slice(0, MAX_ATTACHMENTS).entries()) { const dataUrl = String(att?.dataUrl || "").trim(); if (!dataUrl) continue; const parsed = parseDataUrl(dataUrl); if (!parsed) continue; if (!allowed.has(parsed.mimetype)) { return res.status(400).json({ success: false, error: `Unsupported attachment type: ${parsed.mimetype}`, }); } if (parsed.buffer.length > MAX_BYTES) { return res.status(400).json({ success: false, error: `Attachment too large (${parsed.buffer.length} bytes). Max is ${MAX_BYTES} bytes.`, }); } const base = safeFilename(att?.filename || att?.id || `attachment_${idx + 1}`, `attachment_${idx + 1}`); const ext = extFromMimetype(parsed.mimetype); const fileNameWithExt = base.endsWith(ext) ? base : `${base}${ext || ""}`; const filePath = path.join(dir, fileNameWithExt || `attachment_${idx + 1}.bin`); await fsp.writeFile(filePath, parsed.buffer); const savedMeta = { id: att?.id || null, filename: path.basename(filePath), mimetype: parsed.mimetype, bytes: parsed.buffer.length, }; savedFiles.push(savedMeta); const caption = String(att?.caption || body.logoCaption || "Branding request attachment").slice(0, 2000); const sent = await wa.sendFileBuffer( toDigits, parsed.buffer, { filename: savedMeta.filename, mimetype: parsed.mimetype, caption, }, { ensureContact: false, contactName } ); sentFiles.push({ id: savedMeta.id, result: sent }); } // ✅ Save meta (wati results) await writeJson(path.join(dir, "meta.json"), { reqId, createdAt, to: toDigits, contactName, savedFiles, wati: { sentText, sentFiles, }, }); return res.json({ success: true, reqId, to: toDigits, sentText, sentFiles, savedFiles, }); }) ); /* --------------------------------------------------------- * WhatsApp AI Chatbot Webhook * WATI sends all incoming messages here. * Configure in WATI Dashboard → Settings → Webhook URL: * https://yourdomain.com/api/whatsapp/webhook * --------------------------------------------------------- */ const whatsappBot = require("./services/whatsappBotService"); app.post( "/api/whatsapp/webhook", express.json({ limit: "1mb" }), safeAsync(async (req, res) => { // Always respond 200 immediately so WATI doesn't retry res.status(200).json({ ok: true }); try { const tenantId = req.tenant?.id || 1; await whatsappBot.handleWebhook(req, tenantId); } catch (err) { console.error("[WaBot] Webhook handler error:", err.message); } }) ); // GET handler for WATI webhook verification (some providers send a GET to verify) app.get("/api/whatsapp/webhook", (req, res) => { res.status(200).send("Giftwrap WhatsApp Bot Active"); }); /* --------------------------------------------------------- * FROM YOUR VERSION: WhatsApp Test Endpoint * --------------------------------------------------------- */ app.all( "/api/dev/test-whatsapp", devGuard, safeAsync(async (req, res) => { // ✅ hard defaults so it "just works" even on GET with no params const to = (req.body && req.body.to) || (req.query && req.query.to) || process.env.WATI_TEST_TO || "0822175681"; const message = (req.body && req.body.message) || (req.query && req.query.message) || "Hi Mandiso, this is a WhatsApp DEV test (message + attachment)."; const contactName = (req.body && req.body.contactName) || (req.query && req.query.contactName) || "Giftwrap Test Contact"; // ✅ your server file (attachment) const filePath = "/home/bordales/public_html/v1/protected/images/img.png"; // Ensure file exists if (!fs.existsSync(filePath)) { return res.status(404).json({ success: false, error: "Attachment file not found on server", filePath, }); } // ✅ ALWAYS send message + ALWAYS send file const result = await wa.sendTextAndFileFromPath(to, message, filePath, { contactName }); await wa.sendTextAndFileFromPath( "0822175681", "Hi Mandiso, here is the image + message.", "/home/bordales/public_html/v1/protected/images/img.png", { contactName: "Mandiso Ngwenya" } ); return res.json({ success: true, to, to_normalised: wa.toE164Digits(to), filePath, sentText: result.sentText, sentFile: result.sentFile, }); }) ); /* --------------------------------------------------------- * FROM YOUR VERSION: AI Shop Assistant Endpoint * --------------------------------------------------------- */ app.post( "/api/ai/shop-assistant", safeAsync(async (req, res) => { const startTime = Date.now(); try { const { message, context } = req.body; const tenantId = req.tenant.id; // Get session ID let sessionId = req.headers['x-session-id'] || req.sessionID; if (!sessionId) { sessionId = AILoggingService.generateSessionId(); } const userId = req.session?.user?.id || null; console.log('AI Shop Assistant called'); // Use enhanced AI assistant const result = await EnhancedAIAssistant.processUserQuery( tenantId, sessionId, userId, message, context || {} ); // Set session ID in response headers res.set('X-Session-ID', sessionId); res.json({ success: true, ...result, sessionId }); } catch (error) { console.error("[AI Assistant Error]:", error); // Log error if (req.tenant?.id) { await AILoggingService.logAIInteraction({ tenantId: req.tenant.id, sessionId: req.headers['x-session-id'] || req.sessionID, userId: req.session?.user?.id, interactionType: 'shop_assistant', userMessage: req.body.message || '', aiResponse: 'Error processing request', responseTime: Date.now() - startTime, success: false, errorMessage: error.message }); } res.status(500).json({ success: false, reply: "I'm having trouble processing your request right now. Please try again or use the filters directly." }); } }) ); /* --------------------------------------------------------- * FROM YOUR VERSION: Enhanced AI Assistant Test * --------------------------------------------------------- */ // Initialize real-time monitor let realTimeMonitor; if (process.env.REALTIME_MONITOR_ENABLED === 'true') { // realTimeMonitor = new RealTimeMonitor(server); } // Add cleanup job (run daily at midnight) const cron = require('node-cron'); cron.schedule('0 0 * * *', async () => { console.log('Running daily log cleanup...'); try { const result = await AILoggingService.cleanupOldLogs(90); // Keep 90 days of logs console.log('Log cleanup completed:', result); } catch (error) { console.error('Log cleanup failed:', error); } }); // Add periodic monitoring (every 5 minutes) cron.schedule('*/5 * * * *', async () => { console.log('Running periodic AI performance monitoring...'); // Get all tenants try { const [tenants] = await pool.query('SELECT id FROM tenants WHERE is_active =1'); for (const tenant of tenants) { try { await EnhancedAIAssistant.monitorAIPerformance(tenant.id); } catch (error) { console.error(`Failed to monitor tenant ${tenant.id}:`, error.message); } } } catch (error) { console.error('Failed to fetch tenants for monitoring:', error); } }); /* --------------------------------------------------------- * COLLECTIONS API ENDPOINTS (admin + store) * --------------------------------------------------------- */ app.get( "/api/collections", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1; const limit = Math.min(Number(req.query.limit) || 500, 1000); const offset = Math.max(Number(req.query.offset) || 0, 0); const search = String(req.query.search || req.query.q || "").trim(); const includeProducts = String(req.query.include_products || "").toLowerCase() === "true"; const status = String(req.query.status || "active").toLowerCase(); try { const [tables] = await pool.query("SHOW TABLES LIKE 'collections'"); if (tables.length === 0) { return res.json({ success: true, collections: [], message: "Collections table does not exist", data: { collections: [] } }); } const where = ["tenant_id = ?"]; const params = [tenantId]; if (status !== "all") { where.push("status = ?"); params.push(status); } else { where.push("status IN ('active', 'draft', 'archived')"); } if (search) { where.push("(title LIKE ? OR slug LIKE ? OR description LIKE ?)"); params.push(`%${search}%`, `%${search}%`, `%${search}%`); } const [rows] = await pool.query( `SELECT SQL_CALC_FOUND_ROWS id, title, slug, description, image_path, status, sort_order, page_title, meta_description, created_at, updated_at FROM collections WHERE ${where.join(" AND ")} ORDER BY sort_order ASC, title ASC LIMIT ? OFFSET ?`, [...params, limit, offset] ); const [[found]] = await pool.query("SELECT FOUND_ROWS() AS total"); const total = Number(found?.total || 0); const collections = rows.map((collection) => ({ id: Number(collection.id), title: collection.title, slug: collection.slug, description: collection.description || null, image_url: collection.image_path ? toAbsoluteUrl(req, collection.image_path) : null, status: collection.status || "active", sort_order: Number(collection.sort_order || 0), page_title: collection.page_title || null, meta_description: collection.meta_description || null, created_at: collection.created_at || null, updated_at: collection.updated_at || null, })); if (includeProducts && collections.length > 0) { const collectionIds = collections.map((c) => c.id); const [collectionProducts] = await pool.query( `SELECT cp.collection_id, cp.product_id, cp.sort_order, p.sku, p.name, p.slug, p.status, ( SELECT mm.path FROM ( SELECT i.path, 0 AS is_primary, i.position AS sort_order FROM catalog_product_images i WHERE i.product_id = p.id UNION ALL SELECT m.path, m.is_primary, m.sort_order FROM catalog_product_media m WHERE m.product_id = p.id ) mm ORDER BY mm.is_primary DESC, mm.sort_order ASC LIMIT 1 ) AS primary_image_path FROM collection_products cp JOIN catalog_products p ON p.id = cp.product_id WHERE cp.collection_id IN (?) AND p.tenant_id = ? AND p.status = 'active' ORDER BY cp.sort_order ASC, p.name ASC`, [collectionIds, tenantId] ); const productsByCollection = collectionProducts.reduce((acc, row) => { const collectionId = row.collection_id; if (!acc[collectionId]) acc[collectionId] = []; acc[collectionId].push({ id: Number(row.product_id), sku: row.sku, name: row.name, slug: row.slug, status: row.status, image_url: row.primary_image_path ? resolveProductImageUrl(req, row.primary_image_path) : null, }); return acc; }, {}); collections.forEach((collection) => { collection.products = productsByCollection[collection.id] || []; collection.product_count = collection.products.length; }); } const pages = Math.max(1, Math.ceil(total / limit) || 1); const currentPage = Math.floor(offset / limit) + 1; const has_more = offset + collections.length < total; return res.json({ success: true, collections, pagination: { limit, offset, page: currentPage, pages, total, has_more }, data: { collections, pagination: { limit, offset, page: currentPage, pages, total, has_more } }, }); } catch (error) { console.error("[Collections API Error]:", error); return res.json({ success: true, collections: [], message: "No collections found", data: { collections: [] } }); } }) ); app.get( "/api/store/collections", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1; const limit = Math.min(Number(req.query.limit) || 100, 250); const offset = Math.max(Number(req.query.offset) || 0, 0); const featured = String(req.query.featured || "").toLowerCase() === "true"; const include = String(req.query.include || "products,product_count") .split(",") .map((s) => s.trim()) .filter(Boolean); try { const [tables] = await pool.query("SHOW TABLES LIKE 'collections'"); if (tables.length === 0) return res.json({ success: true, collections: [], data: { collections: [] } }); const where = ["tenant_id = ?", "status = 'active'"]; const params = [tenantId]; if (featured) where.push("is_featured = 1"); const [rows] = await pool.query( `SELECT SQL_CALC_FOUND_ROWS id, title, slug, description, image_path, is_featured, sort_order, page_title, meta_description, created_at, updated_at FROM collections WHERE ${where.join(" AND ")} ORDER BY sort_order ASC, title ASC LIMIT ? OFFSET ?`, [...params, limit, offset] ); const [[found]] = await pool.query("SELECT FOUND_ROWS() AS total"); const total = Number(found?.total || 0); const collections = rows.map((collection) => ({ id: Number(collection.id), title: collection.title, slug: collection.slug, description: collection.description || null, image_url: collection.image_path ? toAbsoluteUrl(req, collection.image_path) : null, is_featured: Number(collection.is_featured || 0), sort_order: Number(collection.sort_order || 0), page_title: collection.page_title || null, meta_description: collection.meta_description || null, created_at: collection.created_at || null, updated_at: collection.updated_at || null, })); if (include.includes("product_count") && collections.length > 0) { const collectionIds = collections.map((c) => c.id); const [countRows] = await pool.query( `SELECT cp.collection_id, COUNT(DISTINCT cp.product_id) as product_count FROM collection_products cp JOIN catalog_products p ON p.id = cp.product_id WHERE cp.collection_id IN (?) AND p.tenant_id = ? AND p.status = 'active' AND COALESCE(p.is_active,1) = 1 GROUP BY cp.collection_id`, [collectionIds, tenantId] ); const countMap = countRows.reduce((acc, row) => { acc[row.collection_id] = Number(row.product_count || 0); return acc; }, {}); collections.forEach((collection) => { collection.product_count = countMap[collection.id] || 0; collection.productCount = collection.product_count; }); } const pages = Math.max(1, Math.ceil(total / limit) || 1); const currentPage = Math.floor(offset / limit) + 1; const has_more = offset + collections.length < total; return res.json({ success: true, collections, pagination: { limit, offset, page: currentPage, pages, total, has_more }, data: { collections, pagination: { limit, offset, page: currentPage, pages, total, has_more } }, }); } catch (error) { console.error("[Store Collections Error]:", error); return res.json({ success: true, collections: [], data: { collections: [] } }); } }) ); app.get( "/api/catalog/collections", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1 const limit = Math.min(Number(req.query.limit) || 500, 1000) try { // Check if collections table exists const [tables] = await pool.query("SHOW TABLES LIKE 'collections'") if (tables.length === 0) { return res.json({ success: true, collections: [], message: "Collections feature not available", data: { collections: [] } }) } const [rows] = await pool.query( `SELECT id, title, slug, description, image_path, status, sort_order, page_title, meta_description FROM collections WHERE tenant_id = ? AND status = 'active' ORDER BY sort_order ASC, title ASC LIMIT ?`, [tenantId, limit] ) const collections = rows.map(collection => ({ id: Number(collection.id), title: collection.title, slug: collection.slug, description: collection.description || null, image_url: collection.image_path ? toAbsoluteUrl(req, collection.image_path) : null, status: collection.status, sort_order: Number(collection.sort_order || 0), page_title: collection.page_title || null, meta_description: collection.meta_description || null })) return res.json({ success: true, collections, total: collections.length, data: { collections, total: collections.length } }) } catch (error) { console.error("[Catalog Collections Error]:", error) return res.json({ success: true, collections: [], message: "Error fetching collections", data: { collections: [] } }) } }) ) // 4. GET /api/store/collections/:slug - Single collection detail app.get( "/api/store/collections/:slug", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1 const slug = String(req.params.slug || "").trim() const include = String(req.query.include || "products,product_count") .split(",") .map(s => s.trim()) .filter(Boolean) try { // Check if collections table exists const [tables] = await pool.query("SHOW TABLES LIKE 'collections'") if (tables.length === 0) { return res.status(404).json({ success: false, error: "Collections feature not available" }) } const [[collection]] = await pool.query( `SELECT id, title, slug, description, image_path, is_featured, sort_order, page_title, meta_description, created_at, updated_at FROM collections WHERE tenant_id = ? AND slug = ? AND status = 'active' LIMIT 1`, [tenantId, slug] ) if (!collection) { return res.status(404).json({ success: false, error: "Collection not found" }) } const response = { id: Number(collection.id), title: collection.title, slug: collection.slug, description: collection.description || null, image_url: collection.image_path ? toAbsoluteUrl(req, collection.image_path) : null, is_featured: Number(collection.is_featured || 0), sort_order: Number(collection.sort_order || 0), page_title: collection.page_title || null, meta_description: collection.meta_description || null, created_at: collection.created_at || null, updated_at: collection.updated_at || null } // Include products if requested if (include.includes("products")) { const baseCurrency = (req.tenant?.base_currency || "ZAR").toUpperCase() const product_limit = Math.min(Number(req.query.limit) || 100, 200) const product_offset = Math.max(Number(req.query.offset) || 0, 0) // In the /api/store/collections/:slug endpoint, update the products query: // In the /api/store/collections/:slug endpoint, find the products query and update it: const costMarkupSelect = getCostMarkupSelect('p'); const [productsRows] = await pool.query( ` SELECT SQL_CALC_FOUND_ROWS DISTINCT p.id, p.sku, p.name, p.slug, p.short_description, p.description AS long_description, p.page_title, p.meta_description, p.supplier_code, p.status, p.brand AS brand, JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.brand')) AS brand_json, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.rating')),'0') AS DECIMAL(3,1)) AS rating, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.stock_qty')),'0') AS UNSIGNED) AS stock_qty, COALESCE(CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN (COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)) ELSE NULL END, pp.price_ex_vat, pp_fb.price_ex_vat) AS price_ex_vat, COALESCE(pp.currency_code, pp_fb.currency_code) AS currency_code, COALESCE(pp.min_qty, pp_fb.min_qty, 1) AS min_qty, ( SELECT mm.path FROM ( SELECT i.path, 0 AS is_primary, i.position AS sort_order FROM catalog_product_images i WHERE i.product_id = p.id UNION ALL SELECT m.path, m.is_primary, m.sort_order FROM catalog_product_media m WHERE m.product_id = p.id ) mm ORDER BY mm.is_primary DESC, mm.sort_order ASC LIMIT 1 ) AS primary_image_path, COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.image_url')), '') AS image_url_fallback ${costMarkupSelect} FROM collection_products cp JOIN catalog_products p ON p.id = cp.product_id LEFT JOIN catalog_product_prices pp ON pp.id = ( SELECT pp2.id FROM catalog_product_prices pp2 WHERE pp2.product_id = p.id AND pp2.is_active=1 AND (pp2.effective_from IS NULL OR pp2.effective_from <= CURRENT_DATE()) AND (pp2.effective_to IS NULL OR pp2.effective_to >= CURRENT_DATE()) AND pp2.min_qty >= 1 AND pp2.currency_code = ? ORDER BY (pp2.min_qty <= 1) DESC, ABS(pp2.min_qty - 1) ASC, COALESCE(pp2.effective_from,'1000-01-01') DESC LIMIT 1 ) LEFT JOIN catalog_product_prices pp_fb ON pp.id IS NULL AND pp_fb.id = ( SELECT pp3.id FROM catalog_product_prices pp3 WHERE pp3.product_id = p.id AND pp3.is_active=1 AND (pp3.effective_from IS NULL OR pp3.effective_from <= CURRENT_DATE()) AND (pp3.effective_to IS NULL OR pp3.effective_to >= CURRENT_DATE()) AND pp3.min_qty >= 1 ORDER BY (pp3.min_qty <= 1) DESC, ABS(pp3.min_qty - 1) ASC, COALESCE(pp3.effective_from,'1000-01-01') DESC LIMIT 1 ) WHERE cp.collection_id = ? AND p.tenant_id = ? AND p.status = 'active' AND COALESCE(p.is_active,1) = 1 ORDER BY cp.sort_order ASC, p.name ASC LIMIT ? OFFSET ? `, [baseCurrency, collection.id, tenantId, product_limit, product_offset] ); const [[found]] = await pool.query("SELECT FOUND_ROWS() AS total") const total = Number(found?.total || 0) const products = shapeProducts(req, productsRows) const pages = Math.max(1, Math.ceil(total / product_limit) || 1) const currentPage = Math.floor(product_offset / product_limit) + 1 const has_more = product_offset + products.length < total response.products = products response.products_pagination = { limit: product_limit, offset: product_offset, page: currentPage, pages, total, has_more } } // Include product count if requested if (include.includes("product_count")) { const [[countResult]] = await pool.query( `SELECT COUNT(DISTINCT cp.product_id) as product_count FROM collection_products cp JOIN catalog_products p ON p.id = cp.product_id WHERE cp.collection_id = ? AND p.tenant_id = ? AND p.status = 'active' AND COALESCE(p.is_active,1) = 1`, [collection.id, tenantId] ) response.product_count = Number(countResult?.product_count || 0) response.productCount = response.product_count } return res.json({ success: true, collection: response, data: { collection: response } }) } catch (error) { console.error("[Collection Detail Error]:", error) return res.status(500).json({ success: false, error: "Failed to fetch collection details", message: error.message }) } }) ) /* --------------------------------------------------------- * ✅ COMPLETE CART API (Fully working with frontend) * --------------------------------------------------------- */ // Helper to get tenant cart function getTenantCart(req) { const tenantId = req.tenant?.id || 1; req.session.carts = req.session.carts || {}; req.session.carts[tenantId] = req.session.carts[tenantId] || { tenant_id: tenantId, cart_id: `cart_${tenantId}_${Date.now()}`, currency: req.tenant?.base_currency || 'ZAR', items: [], shipping: { method: null, cost: 0, address: null }, discount: { code: null, type: 'percentage', value: 0, amount: 0 }, tax: 0, updated_at: new Date().toISOString(), created_at: new Date().toISOString() } return req.session.carts[tenantId]; } // Helper to normalize cart item function normalizeCartItem(item) { return { product_id: Number(item.product_id || item.id || 0), qty: Math.max(1, Number(item.qty || item.quantity || 1)), meta: item.meta || { sku: item.sku || '', name: item.name || '', slug: item.slug || '', image_url: item.image_url || null }, price_ex_vat: Number(item.price_ex_vat || item.price || 0), price_inc_vat: Number(item.price_inc_vat || (item.price_ex_vat || item.price || 0) * 1.15), currency_code: item.currency_code || 'ZAR', options: item.options || {}, added_at: item.added_at || new Date().toISOString(), updated_at: new Date().toISOString() } } // 1. GET /api/cart - Get full cart details app.get('/api/cart', (req, res) => { try { const cart = getTenantCart(req) const formattedCart = { id: cart.cart_id, items: cart.items.map(item => ({ id: item.product_id, product_id: item.product_id, sku: item.meta?.sku || '', name: item.meta?.name || '', slug: item.meta?.slug || '', quantity: item.qty, price_ex_vat: item.price_ex_vat || 0, price_inc_vat: item.price_inc_vat || 0, currency_code: item.currency_code || 'ZAR', image_url: item.meta?.image_url || null, options: item.options || {}, added_at: item.added_at, updated_at: item.updated_at })), tenant_id: cart.tenant_id, currency: cart.currency, shipping: cart.shipping, discount: cart.discount, tax: cart.tax, updated_at: cart.updated_at, created_at: cart.created_at } res.json({ success: true, cart: formattedCart, data: { cart: formattedCart } }) } catch (error) { console.error('Cart fetch error:', error) res.status(500).json({ success: false, error: 'Failed to fetch cart' }) } }) // 2. POST /api/cart - Replace cart (sync) app.post('/api/cart', (req, res) => { try { const cart = getTenantCart(req) const incoming = req.body?.cart || req.body || {} // Accept items array directly or from cart object const items = Array.isArray(incoming.items) ? incoming.items : Array.isArray(req.body?.items) ? req.body.items : [] if (items.length === 0) { return res.status(400).json({ success: false, error: 'Invalid payload. Expected {items:[...]} or {cart:{items:[...]}}' }) } // Clear and replace items cart.items = items.map(normalizeCartItem).filter(item => item.product_id > 0) cart.updated_at = new Date().toISOString() // Save session req.session.save(() => { res.json({ success: true, cart: { id: cart.cart_id, items: cart.items, item_count: cart.items.reduce((sum, item) => sum + item.qty, 0), updated_at: cart.updated_at }, data: { cart: { id: cart.cart_id, items: cart.items, item_count: cart.items.reduce((sum, item) => sum + item.qty, 0), updated_at: cart.updated_at } } }) }) } catch (error) { console.error('Cart replace error:', error) res.status(500).json({ success: false, error: 'Failed to update cart' }) } }) // 3. POST /api/cart/sync - Sync cart (for logged-in users) app.post('/api/cart/sync', async (req, res) => { try { const cart = getTenantCart(req) const { items, shipping, discount, tax } = req.body // Update items if provided if (Array.isArray(items)) { cart.items = items.map(item => normalizeCartItem({ id: item.id, product_id: item.id, quantity: item.quantity, sku: item.sku, name: item.name, slug: item.slug, price_ex_vat: item.price_ex_vat, price_inc_vat: item.price_inc_vat, image_url: item.image_url, currency_code: item.currency_code })).filter(item => item.product_id > 0) } // Update other fields if (shipping) cart.shipping = shipping if (discount) cart.discount = discount if (tax !== undefined) cart.tax = tax cart.updated_at = new Date().toISOString() req.session.save(() => { res.json({ success: true, cart_id: cart.cart_id, updated_at: cart.updated_at, message: 'Cart synced successfully' }) }) } catch (error) { console.error('Cart sync error:', error) res.status(500).json({ success: false, error: 'Failed to sync cart' }) } }) // 4. POST /api/cart/add - Add item to cart // Update the cart add endpoint to fetch product with markup app.post('/api/cart/add', async (req, res) => { try { const cart = getTenantCart(req); const { product_id, sku, quantity = 1, options = {} } = req.body; let productId = product_id; // If SKU provided, find product ID if (!productId && sku) { const [[product]] = await pool.query( `SELECT id FROM catalog_products WHERE sku = ? AND tenant_id = ? AND status='active' AND COALESCE(is_active,1)=1 LIMIT 1`, [sku, req.tenant?.id || 1] ); if (product) { productId = product.id; } } if (!productId || productId <= 0) { return res.status(400).json({ success: false, error: 'Valid product_id or sku is required' }); } // Fetch product with markup calculation const costMarkupSelect = getCostMarkupSelect('p'); const [[product]] = await pool.query( ` SELECT p.id, p.sku, p.name, p.slug, COALESCE(CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN (COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)) ELSE NULL END, pp.price_ex_vat, pp_fb.price_ex_vat) AS price_ex_vat, COALESCE(pp.currency_code, pp_fb.currency_code) AS currency_code, p.image_url ${costMarkupSelect} FROM catalog_products p LEFT JOIN catalog_product_prices pp ON pp.id = ( SELECT pp2.id FROM catalog_product_prices pp2 WHERE pp2.product_id = p.id AND pp2.is_active=1 AND (pp2.effective_from IS NULL OR pp2.effective_from <= CURRENT_DATE()) AND (pp2.effective_to IS NULL OR pp2.effective_to >= CURRENT_DATE()) AND pp2.min_qty >= 1 AND pp2.currency_code = ? ORDER BY (pp2.min_qty <= 1) DESC, ABS(pp2.min_qty - 1) ASC, COALESCE(pp2.effective_from,'1000-01-01') DESC LIMIT 1 ) LEFT JOIN catalog_product_prices pp_fb ON pp.id IS NULL AND pp_fb.id = ( SELECT pp3.id FROM catalog_product_prices pp3 WHERE pp3.product_id = p.id AND pp3.is_active=1 AND (pp3.effective_from IS NULL OR pp3.effective_from <= CURRENT_DATE()) AND (pp3.effective_to IS NULL OR pp3.effective_to >= CURRENT_DATE()) AND pp3.min_qty >= 1 ORDER BY (pp3.min_qty <= 1) DESC, ABS(pp3.min_qty - 1) ASC, COALESCE(pp3.effective_from,'1000-01-01') DESC LIMIT 1 ) WHERE p.id = ? AND p.tenant_id = ? AND p.status='active' AND COALESCE(p.is_active,1)=1 LIMIT 1 `, [req.tenant?.base_currency || 'ZAR', productId, req.tenant?.id || 1] ); if (!product) { return res.status(404).json({ success: false, error: 'Product not found' }); } // Calculate price with markup let priceEx = product.price_ex_vat != null ? Number.parseFloat(product.price_ex_vat) : null; if (product.cost_price != null || product.markup_pct != null) { const costPrice = Number(product.cost_price || 0); const markupPct = Number(product.markup_pct || 0); priceEx = calculatePriceWithMarkup(costPrice, markupPct); } if (priceEx == null || Number.isNaN(priceEx)) priceEx = 0; const priceInc = calculateVatInclusive(priceEx); // Check if item already exists in cart const existingItemIndex = cart.items.findIndex(item => item.product_id === productId && JSON.stringify(item.options) === JSON.stringify(options) ); if (existingItemIndex > -1) { // Update quantity cart.items[existingItemIndex].qty += Math.max(1, quantity); cart.items[existingItemIndex].updated_at = new Date().toISOString(); } else { // Add new item cart.items.push({ product_id: product.id, qty: Math.max(1, quantity), price_ex_vat: priceEx, price_inc_vat: priceInc, currency_code: product.currency_code || req.tenant?.base_currency || 'ZAR', meta: { sku: product.sku || '', name: product.name || '', slug: product.slug || '', image_url: product.image_url || null }, options: options, added_at: new Date().toISOString(), updated_at: new Date().toISOString() }); } cart.updated_at = new Date().toISOString(); req.session.save(() => { res.json({ success: true, cart: { items: cart.items, item_count: cart.items.reduce((sum, item) => sum + item.qty, 0), updated_at: cart.updated_at }, message: 'Item added to cart successfully' }); }); } catch (error) { console.error('Add to cart error:', error); res.status(500).json({ success: false, error: 'Failed to add item to cart' }); } }); // 5. POST /api/cart/remove - Remove item from cart app.post('/api/cart/remove', (req, res) => { try { const cart = getTenantCart(req) const { product_id } = req.body if (!product_id) { return res.status(400).json({ success: false, error: 'product_id is required' }) } const initialLength = cart.items.length cart.items = cart.items.filter(item => item.product_id !== product_id) if (cart.items.length !== initialLength) { cart.updated_at = new Date().toISOString() req.session.save(() => { res.json({ success: true, cart: { items: cart.items, item_count: cart.items.reduce((sum, item) => sum + item.qty, 0), updated_at: cart.updated_at }, message: 'Item removed from cart' }) }) } else { res.status(404).json({ success: false, error: 'Item not found in cart' }) } } catch (error) { console.error('Remove from cart error:', error) res.status(500).json({ success: false, error: 'Failed to remove item from cart' }) } }) // 6. POST /api/cart/update - Update item quantity app.post('/api/cart/update', (req, res) => { try { const cart = getTenantCart(req) const { product_id, quantity } = req.body if (!product_id || quantity === undefined) { return res.status(400).json({ success: false, error: 'product_id and quantity are required' }) } const item = cart.items.find(item => item.product_id === product_id) if (item) { if (quantity <= 0) { // Remove item if quantity is 0 or less cart.items = cart.items.filter(item => item.product_id !== product_id) } else { item.qty = Math.max(1, quantity) item.updated_at = new Date().toISOString() } cart.updated_at = new Date().toISOString() req.session.save(() => { res.json({ success: true, cart: { items: cart.items, item_count: cart.items.reduce((sum, item) => sum + item.qty, 0), updated_at: cart.updated_at }, message: 'Cart updated successfully' }) }) } else { res.status(404).json({ success: false, error: 'Item not found in cart' }) } } catch (error) { console.error('Update cart error:', error) res.status(500).json({ success: false, error: 'Failed to update cart' }) } }) // 7. DELETE /api/cart - Clear cart app.delete('/api/cart', (req, res) => { try { const tenantId = req.tenant?.id || 1 req.session.carts = req.session.carts || {} req.session.carts[tenantId] = { tenant_id: tenantId, cart_id: `cart_${tenantId}_${Date.now()}`, currency: req.tenant?.base_currency || 'ZAR', items: [], shipping: { method: null, cost: 0, address: null }, discount: { code: null, type: 'percentage', value: 0, amount: 0 }, tax: 0, updated_at: new Date().toISOString(), created_at: new Date().toISOString() } req.session.save(() => { res.json({ success: true, message: 'Cart cleared successfully', data: { cleared: true } }) }) } catch (error) { console.error('Clear cart error:', error) res.status(500).json({ success: false, error: 'Failed to clear cart' }) } }) // 8. POST /api/cart/apply-discount - Apply discount code app.post('/api/cart/apply-discount', async (req, res) => { try { const cart = getTenantCart(req) const { code, cart_id, subtotal } = req.body // Basic discount validation const validDiscounts = { 'WELCOME10': { type: 'percentage', value: 10, min_order: 0 }, 'SAVE20': { type: 'percentage', value: 20, min_order: 500 }, 'FREESHIP': { type: 'fixed', value: 0, min_order: 1000 } } const discount = validDiscounts[code?.toUpperCase()] if (!discount) { return res.status(400).json({ success: false, message: 'Invalid discount code' }) } // Calculate subtotal if not provided const cartSubtotal = subtotal || cart.items.reduce((sum, item) => sum + (item.price_ex_vat * item.qty), 0 ) // Check minimum order if (cartSubtotal < discount.min_order) { return res.status(400).json({ success: false, message: `Minimum order of ${discount.min_order} required` }) } // Calculate discount amount let amount = 0 if (discount.type === 'percentage') { amount = cartSubtotal * (discount.value / 100) } else if (discount.type === 'fixed') { amount = discount.value } // Update cart discount cart.discount = { code: code.toUpperCase(), type: discount.type, value: discount.value, amount: amount } cart.updated_at = new Date().toISOString() req.session.save(() => { res.json({ success: true, discount: cart.discount, message: 'Discount applied successfully' }) }) } catch (error) { console.error('Discount error:', error) res.status(500).json({ success: false, error: 'Failed to apply discount' }) } }) // 9. POST /api/cart/remove-discount - Remove discount app.post('/api/cart/remove-discount', (req, res) => { try { const cart = getTenantCart(req) cart.discount = { code: null, type: 'percentage', value: 0, amount: 0 } cart.updated_at = new Date().toISOString() req.session.save(() => { res.json({ success: true, message: 'Discount removed successfully' }) }) } catch (error) { console.error('Remove discount error:', error) res.status(500).json({ success: false, error: 'Failed to remove discount' }) } }) // 10. GET /api/cart/summary - Get cart summary app.get('/api/cart/summary', (req, res) => { try { const cart = getTenantCart(req) // Calculate totals const subtotal = cart.items.reduce((sum, item) => sum + (item.price_ex_vat * item.qty), 0 ) const item_count = cart.items.reduce((sum, item) => sum + item.qty, 0) const total = subtotal + (cart.shipping?.cost || 0) + (cart.tax || 0) - (cart.discount?.amount || 0) const summary = { items: cart.items.map(item => ({ id: item.product_id, product_id: item.product_id, sku: item.meta?.sku || '', name: item.meta?.name || '', quantity: item.qty, price_ex_vat: item.price_ex_vat, price_inc_vat: item.price_inc_vat, currency_code: item.currency_code, image_url: item.meta?.image_url })), item_count, subtotal, shipping: cart.shipping, discount: cart.discount, tax: cart.tax, total: total, currency: cart.currency } res.json({ success: true, summary: summary, data: { summary } }) } catch (error) { console.error('Cart summary error:', error) res.status(500).json({ success: false, error: 'Failed to get cart summary' }) } }) // Calculate shipping app.post('/api/cart/calculate-shipping', async (req, res) => { try { const { address, items, weight, dimensions, subtotal } = req.body // Basic shipping calculation let shippingCost = 0 let method = 'standard' if (address?.country === 'ZA') { // South Africa shipping if (subtotal > 1000) { shippingCost = 0 // Free shipping over 1000 method = 'free' } else if (weight > 5) { shippingCost = 150 method = 'courier' } else { shippingCost = 75 method = 'standard' } } else { // International shipping shippingCost = 250 method = 'international' } res.json({ success: true, shipping: { method: method, cost: shippingCost, address: address, estimated_delivery: '3-5 business days' } }) } catch (error) { console.error('Shipping calculation error:', error) res.status(500).json({ success: false, error: 'Failed to calculate shipping' }) } }) // Calculate tax app.post('/api/cart/calculate-tax', async (req, res) => { try { const { address, items, subtotal, shipping } = req.body // Basic VAT calculation for South Africa const vatRate = 0.15 // 15% VAT let taxableAmount = subtotal + (shipping || 0) // Check if international (no VAT) if (address?.country && address.country !== 'ZA') { taxableAmount = 0 } const taxAmount = taxableAmount * vatRate res.json({ success: true, tax: { rate: vatRate, amount: taxAmount, taxable_amount: taxableAmount } }) } catch (error) { console.error('Tax calculation error:', error) res.status(500).json({ success: false, error: 'Failed to calculate tax' }) } }) // Get cart with proper structure app.get('/api/cart/details', (req, res) => { const cart = getTenantCart(req) const formattedCart = { id: cart.cart_id || `session_${req.tenant?.id || 1}_${Date.now()}`, items: cart.items.map(item => ({ id: item.product_id, product_id: item.product_id, quantity: item.qty, sku: item.meta?.sku || '', name: item.meta?.name || '', slug: item.meta?.slug || '', price_ex_vat: item.price_ex_vat || 0, price_inc_vat: item.price_inc_vat || 0, currency_code: item.currency_code || req.tenant?.base_currency || 'ZAR', image_url: item.image_url || null })), tenant_id: req.tenant?.id || 1, updated_at: cart.updated_at, currency: cart.currency } res.json({ success: true, cart: formattedCart, data: { cart: formattedCart } }) }) // GET /api/store/cart app.get('/api/store/cart', (req, res) => { const cart = getTenantCart(req) res.json({ success: true, cart: { items: cart.items, item_count: cart.items.reduce((sum, item) => sum + item.qty, 0), updated_at: cart.updated_at }, data: { cart: { items: cart.items, item_count: cart.items.reduce((sum, item) => sum + item.qty, 0), updated_at: cart.updated_at } } }) }) // POST /api/store/cart/items - Add to cart (frontend alias) app.post('/api/store/cart/items', async (req, res) => { try { const cart = getTenantCart(req) const { product_id, sku, qty = 1 } = req.body let productId = product_id // If SKU provided, find product ID if (!productId && sku) { const [[product]] = await pool.query( `SELECT id, sku, name, slug FROM catalog_products WHERE sku = ? AND tenant_id = ? AND status='active' AND COALESCE(is_active,1)=1 LIMIT 1`, [sku, req.tenant?.id || 1] ) if (product) { productId = product.id } } if (!productId) { return res.status(400).json({ success: false, error: 'product_id or sku is required' }) } // Fetch product const [[product]] = await pool.query( `SELECT id, sku, name, slug FROM catalog_products WHERE id = ? AND tenant_id = ? AND status='active' AND COALESCE(is_active,1)=1 LIMIT 1`, [productId, req.tenant?.id || 1] ) if (!product) { return res.status(404).json({ success: false, error: 'Product not found' }) } const existing = cart.items.find(item => item.product_id === productId) if (existing) { existing.qty += Math.max(1, qty) existing.updated_at = new Date().toISOString() } else { cart.items.push({ product_id: product.id, qty: Math.max(1, qty), meta: { sku: product.sku || '', name: product.name || '', slug: product.slug || '' }, added_at: new Date().toISOString(), updated_at: new Date().toISOString() }) } cart.updated_at = new Date().toISOString() req.session.save(() => { res.json({ success: true, cart: { items: cart.items, item_count: cart.items.reduce((sum, item) => sum + item.qty, 0), updated_at: cart.updated_at }, data: { cart: { items: cart.items, item_count: cart.items.reduce((sum, item) => sum + item.qty, 0), updated_at: cart.updated_at } } }) }) } catch (error) { console.error('Store cart add error:', error) res.status(500).json({ success: false, error: 'Failed to add item to cart' }) } }) // POST /api/store/cart/add - Add to cart (another alias) app.post('/api/store/cart/add', async (req, res) => { try { const cart = getTenantCart(req) const { product_id, sku, qty = 1 } = req.body let productId = product_id if (!productId && sku) { const [[product]] = await pool.query( `SELECT id, sku, name, slug FROM catalog_products WHERE sku = ? AND tenant_id = ? AND status='active' AND COALESCE(is_active,1)=1 LIMIT 1`, [sku, req.tenant?.id || 1] ) if (product) { productId = product.id } } if (!productId) { return res.status(400).json({ success: false, error: 'product_id or sku is required' }) } const [[product]] = await pool.query( `SELECT id, sku, name, slug FROM catalog_products WHERE id = ? AND tenant_id = ? AND status='active' AND COALESCE(is_active,1)=1 LIMIT 1`, [productId, req.tenant?.id || 1] ) if (!product) { return res.status(404).json({ success: false, error: 'Product not found' }) } const existing = cart.items.find(item => item.product_id === productId) if (existing) { existing.qty += Math.max(1, qty) existing.updated_at = new Date().toISOString() } else { cart.items.push({ product_id: product.id, qty: Math.max(1, qty), meta: { sku: product.sku || '', name: product.name || '', slug: product.slug || '' }, added_at: new Date().toISOString(), updated_at: new Date().toISOString() }) } cart.updated_at = new Date().toISOString() req.session.save(() => { res.json({ success: true, cart: { items: cart.items, item_count: cart.items.reduce((sum, item) => sum + item.qty, 0), updated_at: cart.updated_at }, data: { cart: { items: cart.items, item_count: cart.items.reduce((sum, item) => sum + item.qty, 0), updated_at: cart.updated_at } } }) }) } catch (error) { console.error('Store cart add error:', error) res.status(500).json({ success: false, error: 'Failed to add item to cart' }) } }) /* --------------------------------------------------------- * FROM YOUR VERSION: Quote Endpoints * --------------------------------------------------------- */ app.post('/api/store/quote/items', (req, res) => { const tenantId = req.tenant?.id || 1 req.session.quotes = req.session.quotes || {} req.session.quotes[tenantId] = req.session.quotes[tenantId] || { tenant_id: tenantId, items: [], updated_at: new Date().toISOString() } const quoteCart = req.session.quotes[tenantId] const { product_id, sku, qty } = req.body const existingItem = quoteCart.items.find(item => item.product_id === product_id || item.sku === sku ) if (existingItem) { existingItem.qty += Math.max(1, qty || 1) } else { quoteCart.items.push({ product_id, sku, qty: Math.max(1, qty || 1), added_at: new Date().toISOString() }) } quoteCart.updated_at = new Date().toISOString() res.json({ success: true, quote: quoteCart, data: { quote: quoteCart } }) }) app.get('/api/store/quote', (req, res) => { const tenantId = req.tenant?.id || 1 req.session.quotes = req.session.quotes || {} const quoteCart = req.session.quotes[tenantId] || { tenant_id: tenantId, items: [], updated_at: new Date().toISOString() } res.json({ success: true, quote: quoteCart, data: { quote: quoteCart } }) }) // Request quote app.post('/api/store/quotes/request', async (req, res) => { try { const { items, category_id, category_name, request_type } = req.body // Generate quote ID const quoteId = `QUOTE_${Date.now()}_${Math.random().toString(36).substr(2, 9)}` // Here you would typically save to database and send email // For now, return success with quote ID res.json({ success: true, quote_id: quoteId, message: 'Quote request submitted successfully', next_steps: 'Our team will contact you within 24 hours' }) } catch (error) { console.error('Quote request error:', error) res.status(500).json({ success: false, error: 'Failed to submit quote request' }) } }) /* --------------------------------------------------------- * Price calculation utility * --------------------------------------------------------- */ /* --------------------------------------------------------- * Price calculation utility * --------------------------------------------------------- */ function calculateProductPrice(costPrice, markupPct) { const cost = Number.parseFloat(costPrice) || 0; const markup = Number.parseFloat(markupPct) || 0; return cost * (1 + markup / 100); } function calculateProductPriceFromRow(row) { // Prefer cost + markup whenever markup inputs are present. const markupRaw = row.markup != null ? row.markup : row.markup_pct; if (row.cost_price != null || markupRaw != null) { const costPrice = Number(row.cost_price || 0); const markupPct = Number(markupRaw || 0); return calculatePriceWithMarkup(costPrice, markupPct); } // Fallback to explicit stored price. if (row.price_ex_vat != null && !Number.isNaN(Number(row.price_ex_vat))) { return Number(row.price_ex_vat); } return 0; } /* --------------------------------------------------------- * Catalog Router Factory * --------------------------------------------------------- */ function createCatalogRouter(pool) { const router = express.Router() // GET /api/catalog/product?slug=... router.get( "/product", safeAsync(async (req, res) => { const tenantId = req.tenant.id const baseCurrency = (req.tenant.base_currency || "ZAR").toUpperCase() const slug = String(req.query.slug || "").trim() if (!slug) return res.status(400).json({ success: false, error: "slug required" }) const costMarkupSelect = PRICE_HAS_COST_MARKUP ? `, (SELECT cost_price FROM catalog_product_prices WHERE product_id=p.id AND is_active=1 AND min_qty>=1 ORDER BY (min_qty <= 1) DESC, ABS(min_qty - 1) ASC, COALESCE(effective_from,'1000-01-01') DESC LIMIT 1) AS cost_price, (SELECT markup_pct FROM catalog_product_prices WHERE product_id=p.id AND is_active=1 AND min_qty>=1 ORDER BY (min_qty <= 1) DESC, ABS(min_qty - 1) ASC, COALESCE(effective_from,'1000-01-01') DESC LIMIT 1) AS markup_pct ` : `` const [rows] = await pool.query( ` WITH base_price AS ( SELECT pr.product_id, pr.currency_code, pr.min_qty, pr.price_ex_vat, ROW_NUMBER() OVER (PARTITION BY pr.product_id, pr.currency_code ORDER BY pr.min_qty ASC) AS rn FROM catalog_product_prices pr WHERE pr.is_active = 1 ) SELECT p.id, p.sku, p.name, p.page_title, p.meta_description, p.slug, p.short_description, p.long_description, p.supplier_code, p.status, p.brand AS brand, JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.brand')) AS brand_json, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.rating')),'0') AS DECIMAL(3,1)) AS rating, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.stock_qty')),'0') AS UNSIGNED) AS stock_qty, pcat.category_id AS category_id, c.slug AS category_slug, c.name AS category_name, COALESCE(CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN (COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)) ELSE NULL END, pp.price_ex_vat, pp_fb.price_ex_vat) AS price_ex_vat, COALESCE(pp.currency_code, pp_fb.currency_code) AS currency_code, COALESCE(pp.min_qty, pp_fb.min_qty, 1) AS min_qty, ( SELECT mm.path FROM ( SELECT i.path, 0 AS is_primary, i.position AS sort_order FROM catalog_product_images i WHERE i.product_id = p.id UNION ALL SELECT m.path, m.is_primary, m.sort_order FROM catalog_product_media m WHERE m.product_id = p.id ) mm ORDER BY mm.is_primary DESC, mm.sort_order ASC LIMIT 1 ) AS primary_image_path, COALESCE( JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.image_url')), ( SELECT mm2.path FROM ( SELECT i.path, 0 AS is_primary, i.position AS sort_order FROM catalog_product_images i WHERE i.product_id = p.id UNION ALL SELECT m.path, m.is_primary, m.sort_order FROM catalog_product_media m WHERE m.product_id = p.id ) mm2 ORDER BY mm2.is_primary DESC, mm2.sort_order ASC LIMIT 1 ) ) AS image_url, ( SELECT JSON_ARRAYAGG(name) FROM ( SELECT DISTINCT c2.name AS name FROM catalog_product_categories pc2 JOIN catalog_categories c2 ON c2.id = pc2.category_id WHERE pc2.product_id = p.id ) x ) AS categories, ( SELECT JSON_ARRAYAGG(title) FROM ( SELECT DISTINCT col.title AS title FROM collection_products cp JOIN collections col ON col.id = cp.collection_id WHERE cp.product_id = p.id ) x ) AS collections, ( SELECT JSON_ARRAYAGG(tag) FROM ( SELECT DISTINCT t.tag AS tag FROM catalog_product_tags t WHERE t.product_id = p.id ) x ) AS tags, ( SELECT JSON_ARRAYAGG( JSON_OBJECT('kind', m.kind,'variant', m.variant,'path', m.path,'sort_order', m.sort_order,'is_primary', m.is_primary) ) FROM ( SELECT 'image' AS kind, NULL AS variant, i.path, i.position AS sort_order, 0 AS is_primary FROM catalog_product_images i WHERE i.product_id = p.id UNION ALL SELECT m.kind, m.variant, m.path, m.sort_order, m.is_primary FROM catalog_product_media m WHERE m.product_id = p.id ) m ) AS media, ( SELECT JSON_ARRAYAGG( JSON_OBJECT('currency', pr.currency_code,'min_qty', pr.min_qty,'price_ex_vat', pr.price_ex_vat) ) FROM catalog_product_prices pr WHERE pr.product_id = p.id AND pr.is_active = 1 ) AS prices, (SELECT bp.price_ex_vat FROM base_price bp WHERE bp.product_id = p.id AND rn = 1) AS base_price ${costMarkupSelect} FROM catalog_products p LEFT JOIN ( SELECT pc.product_id, MIN(pc.category_id) AS category_id FROM catalog_product_categories pc GROUP BY pc.product_id ) pcat ON pcat.product_id = p.id LEFT JOIN catalog_categories c ON c.id = pcat.category_id LEFT JOIN catalog_product_prices pp ON pp.id = ( SELECT pp2.id FROM catalog_product_prices pp2 WHERE pp2.product_id = p.id AND pp2.is_active = 1 AND (pp2.effective_from IS NULL OR pp2.effective_from <= CURRENT_DATE()) AND (pp2.effective_to IS NULL OR pp2.effective_to >= CURRENT_DATE()) AND pp2.min_qty >= 1 AND pp2.currency_code = ? ORDER BY (pp2.min_qty <= 1) DESC, ABS(pp2.min_qty - 1) ASC, COALESCE(pp2.effective_from,'1000-01-01') DESC LIMIT 1 ) LEFT JOIN catalog_product_prices pp_fb ON pp.id IS NULL AND pp_fb.id = ( SELECT pp3.id FROM catalog_product_prices pp3 WHERE pp3.product_id = p.id AND pp3.is_active = 1 AND (pp3.effective_from IS NULL OR pp3.effective_from <= CURRENT_DATE()) AND (pp3.effective_to IS NULL OR pp3.effective_to >= CURRENT_DATE()) AND pp3.min_qty >= 1 ORDER BY (pp3.min_qty <= 1) DESC, ABS(pp3.min_qty - 1) ASC, COALESCE(pp3.effective_from,'1000-01-01') DESC LIMIT 1 ) WHERE p.tenant_id = ? AND p.slug = ? AND p.status ='active' LIMIT 1 `, [baseCurrency, tenantId, slug], ) const raw = rows?.[0] || null const product = raw ? shapeProductRow(req, raw) : null return res.json({ success: !!product, product }) }), ) router.get( "/product/by-category", safeAsync(async (req, res) => { const tenantId = req.tenant.id const { category_slug, product_slug } = req.query if (!category_slug || !product_slug) { return res.status(400).json({ success: false, error: "category_slug and product_slug required" }) } const { fetchProductByCategoryPair } = require("./services/productService") const product = await fetchProductByCategoryPair( pool, tenantId, String(category_slug).trim(), String(product_slug).trim(), ) return res.json({ success: !!product, product }) }), ) router.get( "/product/by-tag", safeAsync(async (req, res) => { const tenantId = req.tenant.id const { tag_slug, product_slug } = req.query if (!tag_slug || !product_slug) { return res.status(400).json({ success: false, error: "tag_slug and product_slug required" }) } const { fetchProductByTagPair } = require("./services/productService") const product = await fetchProductByTagPair(pool, tenantId, String(tag_slug).trim(), String(product_slug).trim()) return res.json({ success: !!product, product }) }), ) router.get( "/facets", safeAsync(async (req, res) => { const tenantId = req.tenant.id const [cats] = await pool.query( `SELECT id, name, slug FROM catalog_categories WHERE tenant_id=? AND COALESCE(is_active,1)=1 ORDER BY name ASC LIMIT 500`, [tenantId], ) const [brows] = await pool.query( ` SELECT brand, COUNT(*) AS count FROM ( SELECT NULLIF(TRIM(brand), '') AS brand FROM catalog_products WHERE tenant_id=? AND COALESCE(is_active,1)=1 UNION ALL SELECT NULLIF(TRIM(JSON_UNQUOTE(JSON_EXTRACT(attributes_json, '$.brand'))), '') AS brand FROM catalog_products WHERE tenant_id=? AND p.status ='active' ) x WHERE brand IS NOT NULL AND brand <> '' GROUP BY brand ORDER BY count DESC, brand ASC LIMIT 250 `, [tenantId, tenantId], ) const brands = (brows || []).map((r) => ({ name: r.brand, count: Number(r.count || 0) })) res.json({ success: true, categories: cats || [], brands, facets: { categories: cats || [], brands }, data: { categories: cats || [], brands }, }) }), ) // In createCatalogRouter, update the products query: router.get( "/products", safeAsync(async (req, res) => { const tenantId = req.tenant.id; const baseCurrency = (req.tenant.base_currency || "ZAR").toUpperCase(); const q = String(req.query.search || req.query.q || "").trim(); const category = String(req.query.category || "").trim(); const minP = Number(req.query.min_price || 0); const maxP = Number(req.query.max_price || 0); const brands = String(req.query.brands || "") .split(",") .map((s) => s.trim()) .filter(Boolean); const minRating = Number(req.query.min_rating || 0); const inStock = String(req.query.in_stock || "") === "1"; const shipsFast = String(req.query.ships_fast || "") === "1"; const limit = Math.min(Number(req.query.limit) || 24, 100); const offset = Math.max(Number(req.query.offset) || 0, 0); const sortCol = String(req.query.sortColumn || "created_at"); const sortDir = String(req.query.sortOrder || "DESC").toUpperCase() === "ASC" ? "ASC" : "DESC"; const where = [`p.tenant_id = ?`, `COALESCE(p.is_active,1)=1`]; const args = [tenantId]; if (q) { where.push(`(p.name LIKE ? OR p.sku LIKE ? OR p.short_description LIKE ? OR p.long_description LIKE ?)`); args.push(`%${q}%`, `%${q}%`, `%${q}%`, `%${q}%`); } if (category) { where.push(`EXISTS ( SELECT 1 FROM catalog_product_categories pcx JOIN catalog_categories cat ON cat.id = pcx.category_id WHERE pcx.product_id = p.id AND cat.tenant_id = ? AND (cat.slug = ? OR cat.id = ?) )`); args.push(tenantId, category, category); } if (brands.length) { where.push(`( p.brand IN (${brands.map(() => "?").join(",")}) OR JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.brand')) IN (${brands.map(() => "?").join(",")}) )`); args.push(...brands, ...brands); } if (minRating) { where.push(`CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.rating')),'0') AS DECIMAL(3,1)) >= ?`); args.push(minRating); } if (inStock) { where.push(`CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.stock_qty')),'0') AS UNSIGNED) > 0`); } if (shipsFast) { where.push( `CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.lead_time_days')),'7') AS UNSIGNED) <= 3`, ); } const orderable = new Set(["created_at", "price", "name", "rating"]); const ob = orderable.has(sortCol) ? sortCol : "created_at"; const dir = sortDir; const catalogListPriceExpr = ` COALESCE( CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN NULLIF((COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)), 0) ELSE NULL END, NULLIF(pp.price_ex_vat, 0), NULLIF(pp_fb.price_ex_vat, 0), NULLIF(p.selling_price, 0), NULLIF(p.base_price, 0), NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.price_ex_vat')) AS DECIMAL(12,2)), 0), NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.price')) AS DECIMAL(12,2)), 0), 0 ) `; // Get cost/markup select fields const costMarkupSelect = getCostMarkupSelect('p'); const sql = ` SELECT SQL_CALC_FOUND_ROWS p.id, p.sku, p.name, p.slug, p.short_description, p.long_description, p.page_title, p.meta_description, p.supplier_code, p.status, p.base_price, p.selling_price, p.image_url, p.attributes_json, p.brand AS brand, JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.brand')) AS brand_json, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json, '$.stock_qty')), '0') AS UNSIGNED) AS stock_qty, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.rating')),'0') AS DECIMAL(3,1)) AS rating, pcat.category_id AS category_id, c.slug AS category_slug, c.name AS category_name, ${catalogListPriceExpr} AS price_ex_vat, COALESCE(pp.currency_code, pp_fb.currency_code) AS currency_code, COALESCE(pp.min_qty, pp_fb.min_qty, 1) AS min_qty, -- IMAGE FIX ONLY: Get all possible image sources ( -- First try to get a full URL from catalog_product_images SELECT image_url FROM catalog_product_images WHERE product_id = p.id AND image_url IS NOT NULL AND image_url LIKE 'http%' ORDER BY is_primary DESC, sort_order ASC, position ASC LIMIT 1 ) AS img_image_url, ( -- Try to get path from catalog_product_images SELECT path FROM catalog_product_images WHERE product_id = p.id AND path IS NOT NULL ORDER BY is_primary DESC, sort_order ASC, position ASC LIMIT 1 ) AS img_path, ( -- Try to get local_path from catalog_product_images SELECT local_path FROM catalog_product_images WHERE product_id = p.id AND local_path IS NOT NULL ORDER BY is_primary DESC, sort_order ASC, position ASC LIMIT 1 ) AS img_local_path, ( -- Try to get path from catalog_product_media SELECT path FROM catalog_product_media WHERE product_id = p.id AND path IS NOT NULL ORDER BY is_primary DESC, sort_order ASC LIMIT 1 ) AS media_path, -- Original image_url field from attributes (keep for compatibility) JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.image_url')) AS attr_image_url ${costMarkupSelect} FROM catalog_products p LEFT JOIN ( SELECT pc.product_id, MIN(pc.category_id) AS category_id FROM catalog_product_categories pc GROUP BY pc.product_id ) pcat ON pcat.product_id = p.id LEFT JOIN catalog_categories c ON c.id = pcat.category_id LEFT JOIN catalog_product_prices pp ON pp.id = ( SELECT pp2.id FROM catalog_product_prices pp2 WHERE pp2.product_id = p.id AND pp2.is_active = 1 AND (pp2.effective_from IS NULL OR pp2.effective_from <= CURRENT_DATE()) AND (pp2.effective_to IS NULL OR pp2.effective_to >= CURRENT_DATE()) AND pp2.min_qty >= 1 AND pp2.currency_code = ? ORDER BY (pp2.min_qty <= 1) DESC, ABS(pp2.min_qty - 1) ASC, COALESCE(pp2.effective_from,'1000-01-01') DESC LIMIT 1 ) LEFT JOIN catalog_product_prices pp_fb ON pp.id IS NULL AND pp_fb.id = ( SELECT pp3.id FROM catalog_product_prices pp3 WHERE pp3.product_id = p.id AND pp3.is_active = 1 AND (pp3.effective_from IS NULL OR pp3.effective_from <= CURRENT_DATE()) AND (pp3.effective_to IS NULL OR pp3.effective_to >= CURRENT_DATE()) AND pp3.min_qty >= 1 ORDER BY (pp3.min_qty <= 1) DESC, ABS(pp3.min_qty - 1) ASC, COALESCE(pp3.effective_from,'1000-01-01') DESC LIMIT 1 ) WHERE ${where.join(" AND ")} ${minP ? `AND ${catalogListPriceExpr} >= ?` : ""} ${maxP ? `AND ${catalogListPriceExpr} <= ?` : ""} AND p.status ='active' ORDER BY ${ob === "price" ? catalogListPriceExpr : ob === "name" ? "p.name" : ob === "rating" ? "rating" : "p.created_at" } ${dir} LIMIT ? OFFSET ? `; const params = [baseCurrency, ...args]; if (minP) params.push(minP); if (maxP) params.push(maxP); params.push(limit, offset); const [rows] = await pool.query(sql, params); const [[found]] = await pool.query("SELECT FOUND_ROWS() AS total"); const total = Number(found?.total || 0); // Add image formatting helper (only if not already present) const originalShapeProducts = shapeProducts; // Assuming shapeProducts is defined elsewhere // Create wrapper that adds image formatting function shapeProductsWithImageFix(req, rows) { const products = originalShapeProducts(req, rows); // Add image formatting to each product return products.map((product, index) => { const row = rows[index]; // Format the image URL from all possible sources let finalImageUrl = ''; // Check in priority order: // 1. Full URL from img_image_url if (row.img_image_url && row.img_image_url.trim() && row.img_image_url.toLowerCase().startsWith('http')) { finalImageUrl = row.img_image_url.trim(); } // 2. Full URL from attr_image_url else if (row.attr_image_url && row.attr_image_url.trim() && row.attr_image_url.toLowerCase().startsWith('http')) { finalImageUrl = row.attr_image_url.trim(); } // 3. Path from img_path or media_path else if (row.img_path || row.media_path) { const path = (row.img_path || row.media_path).trim(); if (path.toLowerCase().startsWith('http')) { finalImageUrl = path; } else if (path.startsWith('/')) { finalImageUrl = `https://www.giftwrap.co.za${path}`; } else if (path.includes('amrcdn.amrod.co.za')) { finalImageUrl = `https://${path}`; } else if (path.startsWith('protected/images/')) { finalImageUrl = `https://www.giftwrap.co.za/${path.replace('protected/', '')}`; } else { finalImageUrl = `https://www.giftwrap.co.za/images/products/large/${path}`; } } // 4. Local path from img_local_path else if (row.img_local_path) { const localPath = row.img_local_path.trim(); if (localPath.toLowerCase().startsWith('http')) { finalImageUrl = localPath; } else if (localPath.startsWith('/')) { finalImageUrl = `https://www.giftwrap.co.za${localPath}`; } else if (localPath.includes('amrcdn.amrod.co.za')) { finalImageUrl = `https://${localPath}`; } else if (localPath.startsWith('protected/images/')) { finalImageUrl = `https://www.giftwrap.co.za/${localPath.replace('protected/', '')}`; } else { finalImageUrl = `https://www.giftwrap.co.za/images/products/${localPath}`; } } // Only update if we found an image if (finalImageUrl) { return { ...product, image_url: finalImageUrl, image: finalImageUrl }; } return product; }); } // Use the wrapper instead of original const products = shapeProductsWithImageFix(req, rows); safeJsonResponse(res, { success: true, products, items: products, pagination: { limit, offset, total }, total, data: { products, pagination: { limit, offset, total }, total }, }); }), ); /* --------------------------------------------------------- * Product by Slug Endpoint * --------------------------------------------------------- */ app.get( "/api/products/slug/:slug", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1 const slug = String(req.params.slug || "").trim() const baseCurrency = (req.tenant?.base_currency || "ZAR").toUpperCase() if (!slug) { return res.status(400).json({ success: false, error: "Product slug is required" }) } // Get cost/markup select fields const costMarkupSelect = getCostMarkupSelect('p') const [rows] = await pool.query( ` SELECT p.id, p.sku, p.name, p.slug, p.short_description, p.description AS long_description, p.page_title, p.meta_description, p.supplier_code, p.status, p.brand AS brand, JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.brand')) AS brand_json, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.rating')),'0') AS DECIMAL(3,1)) AS rating, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.stock_qty')),'0') AS UNSIGNED) AS stock_qty, COALESCE(CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN (COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)) ELSE NULL END, pp.price_ex_vat, pp_fb.price_ex_vat) AS price_ex_vat, COALESCE(pp.currency_code, pp_fb.currency_code) AS currency_code, COALESCE(pp.min_qty, pp_fb.min_qty, 1) AS min_qty, ( SELECT mm.path FROM ( SELECT i.path, 0 AS is_primary, i.position AS sort_order FROM catalog_product_images i WHERE i.product_id = p.id UNION ALL SELECT m.path, m.is_primary, m.sort_order FROM catalog_product_media m WHERE m.product_id = p.id ) mm ORDER BY mm.is_primary DESC, mm.sort_order ASC LIMIT 1 ) AS primary_image_path, COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.image_url')), '') AS image_url_fallback ${costMarkupSelect} FROM catalog_products p LEFT JOIN catalog_product_prices pp ON pp.id = ( SELECT pp2.id FROM catalog_product_prices pp2 WHERE pp2.product_id = p.id AND pp2.is_active=1 AND (pp2.effective_from IS NULL OR pp2.effective_from <= CURRENT_DATE()) AND (pp2.effective_to IS NULL OR pp2.effective_to >= CURRENT_DATE()) AND pp2.min_qty >= 1 AND pp2.currency_code = ? ORDER BY (pp2.min_qty <= 1) DESC, ABS(pp2.min_qty - 1) ASC, COALESCE(pp2.effective_from,'1000-01-01') DESC LIMIT 1 ) LEFT JOIN catalog_product_prices pp_fb ON pp.id IS NULL AND pp_fb.id = ( SELECT pp3.id FROM catalog_product_prices pp3 WHERE pp3.product_id = p.id AND pp3.is_active=1 AND (pp3.effective_from IS NULL OR pp3.effective_from <= CURRENT_DATE()) AND (pp3.effective_to IS NULL OR pp3.effective_to >= CURRENT_DATE()) AND pp3.min_qty >= 1 ORDER BY (pp3.min_qty <= 1) DESC, ABS(pp3.min_qty - 1) ASC, COALESCE(pp3.effective_from,'1000-01-01') DESC LIMIT 1 ) WHERE p.tenant_id = ? AND ( LOWER(p.slug) = LOWER(?) OR LOWER(p.sku) = LOWER(?) ) AND (p.status IS NULL OR p.status = '' OR p.status IN ('active','published','live','enabled')) AND COALESCE(p.is_active, 1) = 1 LIMIT 1 `, [baseCurrency, tenantId, slug, slug] ) const raw = rows?.[0] || null if (!raw) { return res.status(404).json({ success: false, error: "Product not found" }) } const product = shapeProductRow(req, raw) return res.json({ success: true, product, data: { product } }) }), ) /* --------------------------------------------------------- * Legacy product lookup by legacy ID (old SEO URLs) * --------------------------------------------------------- */ app.get( "/api/products/legacy/:legacyId", safeAsync(async (req, res) => { const legacyId = Number(req.params.legacyId || 0) if (!legacyId || legacyId <= 0) { return res.status(400).json({ success: false, error: "Valid legacy product id required" }) } const [[row]] = await pool.query( `SELECT * FROM _product WHERE id = ? AND active = 1 LIMIT 1`, [legacyId] ) if (!row) { return res.status(404).json({ success: false, error: "Legacy product not found" }) } const slugify = (s = "") => String(s || "") .trim() .toLowerCase() .replace(/[\s_]+/g, "-") .replace(/[^a-z0-9-]+/g, "") .replace(/-+/g, "-") .replace(/^-|-$/g, "") // basic price calc using mark_up_1 if present const cost = Number(row.cost_price_1 || row.cost_price || 0) const markup = Number(row.mark_up_1 || row.mark_up || row.markup || 0) const price_ex_vat = cost > 0 ? cost * (1 + markup / 100) : Number(row.price_ex_vat || 0) const price_inc_vat = price_ex_vat > 0 ? price_ex_vat * 1.15 : null const product = { id: row.id, legacy_id: row.id, sku: row.product_code || row.sku || `LEG-${row.id}`, name: row.name || row.product_name || "Legacy product", slug: slugify(row.slug || row.name || `legacy-${row.id}`), short_description: row.short_description || row.meta_description || null, description: row.description || null, meta_description: row.meta_description || null, price_ex_vat, price_inc_vat, cost_price: cost || null, markup_pct: markup || null, image_url: row.image || row.image_url || null, attributes_json: row.attributes_json || null, category_id: row.category_id || null, category_name: row.category_name || null, stock_qty: row.stock || row.stock_qty || null, is_active: true, } return res.json({ success: true, product, data: { product } }) }) ) /* --------------------------------------------------------- * Product Pricing Endpoint * --------------------------------------------------------- */ app.get( "/api/products/:id/pricing", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1 const productId = Number(req.params.id || 0) const baseCurrency = (req.tenant?.base_currency || "ZAR").toUpperCase() const quantity = Math.max(1, Number(req.query.quantity || 1)) if (!productId || productId <= 0) { return res.status(400).json({ success: false, error: "Valid product ID is required" }) } // First check if product exists and belongs to tenant const [[product]] = await pool.query( `SELECT id, name, slug, sku FROM catalog_products WHERE id = ? AND tenant_id = ? AND status = 'active'`, [productId, tenantId] ) if (!product) { return res.status(404).json({ success: false, error: "Product not found" }) } // Get cost/markup select fields const costMarkupSelect = PRICE_HAS_COST_MARKUP ? `, (SELECT cost_price FROM catalog_product_prices WHERE product_id = ? AND is_active = 1 AND min_qty <= ? ORDER BY min_qty DESC LIMIT 1) AS cost_price, (SELECT markup_pct FROM catalog_product_prices WHERE product_id = ? AND is_active = 1 AND min_qty <= ? ORDER BY min_qty DESC LIMIT 1) AS markup_pct ` : `` // Get pricing information const [pricingRows] = await pool.query( ` SELECT pp.id, pp.price_ex_vat, pp.currency_code, pp.min_qty, pp.effective_from, pp.effective_to, pp.is_active ${costMarkupSelect} FROM catalog_product_prices pp WHERE pp.product_id = ? AND pp.is_active = 1 AND (pp.effective_from IS NULL OR pp.effective_from <= CURRENT_DATE()) AND (pp.effective_to IS NULL OR pp.effective_to >= CURRENT_DATE()) AND pp.min_qty <= ? ORDER BY pp.min_qty DESC, pp.currency_code ASC LIMIT 10 `, PRICE_HAS_COST_MARKUP ? [productId, quantity, productId, quantity, productId, quantity] : [productId, quantity] ) // Get the best matching price for the requested quantity const [bestPriceRows] = await pool.query( ` SELECT pp.price_ex_vat, pp.currency_code, pp.min_qty, ( SELECT cost_price FROM catalog_product_prices WHERE product_id = ? AND is_active = 1 AND min_qty <= ? ORDER BY min_qty DESC LIMIT 1 ) AS cost_price, ( SELECT markup_pct FROM catalog_product_prices WHERE product_id = ? AND is_active = 1 AND min_qty <= ? ORDER BY min_qty DESC LIMIT 1 ) AS markup_pct FROM catalog_product_prices pp WHERE pp.product_id = ? AND pp.is_active = 1 AND (pp.effective_from IS NULL OR pp.effective_from <= CURRENT_DATE()) AND (pp.effective_to IS NULL OR pp.effective_to >= CURRENT_DATE()) AND pp.min_qty <= ? AND pp.currency_code = ? ORDER BY pp.min_qty DESC LIMIT 1 `, [productId, quantity, productId, quantity, productId, quantity, baseCurrency] ) let bestPrice = null let calculatedPrice = 0 let priceExVat = 0 if (bestPriceRows.length > 0) { bestPrice = bestPriceRows[0] priceExVat = Number(bestPrice.price_ex_vat || 0) // Calculate price with markup if available const bestMarkup = bestPrice.markup != null ? bestPrice.markup : bestPrice.markup_pct if (bestPrice.cost_price != null || bestMarkup != null) { const costPrice = Number(bestPrice.cost_price || 0) const markupPct = Number(bestMarkup || 0) calculatedPrice = calculatePriceWithMarkup(costPrice, markupPct) } else { calculatedPrice = priceExVat } } const priceIncVat = calculateVatInclusive(calculatedPrice) // Format all pricing tiers const pricingTiers = pricingRows.map(row => { const costPrice = Number(row.cost_price || 0) const markupPct = Number((row.markup != null ? row.markup : row.markup_pct) || 0) let tierPriceExVat = Number(row.price_ex_vat || 0) // Calculate price with markup if available if (costPrice > 0 || markupPct > 0) { tierPriceExVat = calculatePriceWithMarkup(costPrice, markupPct) } const tierPriceIncVat = calculateVatInclusive(tierPriceExVat) return { min_qty: Number(row.min_qty || 1), price_ex_vat: tierPriceExVat, price_inc_vat: tierPriceIncVat, currency_code: row.currency_code || baseCurrency, effective_from: row.effective_from || null, effective_to: row.effective_to || null, is_active: Boolean(row.is_active) } }) // Group pricing by currency const pricingByCurrency = pricingTiers.reduce((acc, tier) => { const currency = tier.currency_code || baseCurrency if (!acc[currency]) acc[currency] = [] acc[currency].push(tier) return acc }, {}) const response = { product_id: productId, product_name: product.name, product_slug: product.slug, product_sku: product.sku, requested_quantity: quantity, base_currency: baseCurrency, best_price: bestPrice ? { price_ex_vat: calculatedPrice, price_inc_vat: priceIncVat, currency_code: bestPrice.currency_code || baseCurrency, min_qty: Number(bestPrice.min_qty || 1), cost_price: bestPrice.cost_price != null ? Number(bestPrice.cost_price) : null, markup_pct: bestPrice.markup_pct != null ? Number(bestPrice.markup_pct) : null } : null, pricing_tiers: pricingTiers, pricing_by_currency: pricingByCurrency, vat_rate: 0.15, // South Africa VAT rate vat_amount: priceIncVat - calculatedPrice } return res.json({ success: true, ...response, data: response }) }), ) return router } /* ---------------------------------------`------------------ * Product by ID Endpoint * --------------------------------------------------------- */ app.get( "/api/products/:id", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1 const productId = Number(req.params.id || 0) const baseCurrency = (req.tenant?.base_currency || "ZAR").toUpperCase() if (!productId || productId <= 0) { return res.status(400).json({ success: false, error: "Valid product ID is required" }) } // Get cost/markup select fields const costMarkupSelect = getCostMarkupSelect('p') const [rows] = await pool.query( ` SELECT p.id, p.sku, p.name, p.slug, p.short_description, p.description AS long_description, p.page_title, p.meta_description, p.supplier_code, p.status, p.brand AS brand, JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.brand')) AS brand_json, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.rating')),'0') AS DECIMAL(3,1)) AS rating, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.stock_qty')),'0') AS UNSIGNED) AS stock_qty, COALESCE(CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN (COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)) ELSE NULL END, pp.price_ex_vat, pp_fb.price_ex_vat) AS price_ex_vat, COALESCE(pp.currency_code, pp_fb.currency_code) AS currency_code, COALESCE(pp.min_qty, pp_fb.min_qty, 1) AS min_qty, -- IMAGE FIX: Get all possible image columns ( SELECT image_url FROM catalog_product_images WHERE product_id = p.id AND (is_primary = 1 OR is_primary IS NULL) ORDER BY sort_order ASC, position ASC LIMIT 1 ) AS img_image_url, ( SELECT path FROM catalog_product_images WHERE product_id = p.id AND (is_primary = 1 OR is_primary IS NULL) ORDER BY sort_order ASC, position ASC LIMIT 1 ) AS img_path, ( SELECT local_path FROM catalog_product_images WHERE product_id = p.id AND (is_primary = 1 OR is_primary IS NULL) ORDER BY sort_order ASC, position ASC LIMIT 1 ) AS img_local_path, ( SELECT path FROM catalog_product_media WHERE product_id = p.id AND (is_primary = 1 OR is_primary IS NULL) ORDER BY sort_order ASC LIMIT 1 ) AS media_path, -- Keep original for compatibility COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.image_url')), '') AS image_url_fallback ${costMarkupSelect} FROM catalog_products p LEFT JOIN catalog_product_prices pp ON pp.id = ( SELECT pp2.id FROM catalog_product_prices pp2 WHERE pp2.product_id = p.id AND pp2.is_active=1 AND (pp2.effective_from IS NULL OR pp2.effective_from <= CURRENT_DATE()) AND (pp2.effective_to IS NULL OR pp2.effective_to >= CURRENT_DATE()) AND pp2.min_qty >= 1 AND pp2.currency_code = ? ORDER BY (pp2.min_qty <= 1) DESC, ABS(pp2.min_qty - 1) ASC, COALESCE(pp2.effective_from,'1000-01-01') DESC LIMIT 1 ) LEFT JOIN catalog_product_prices pp_fb ON pp.id IS NULL AND pp_fb.id = ( SELECT pp3.id FROM catalog_product_prices pp3 WHERE pp3.product_id = p.id AND pp3.is_active=1 AND (pp3.effective_from IS NULL OR pp3.effective_from <= CURRENT_DATE()) AND (pp3.effective_to IS NULL OR pp3.effective_to >= CURRENT_DATE()) AND pp3.min_qty >= 1 ORDER BY (pp3.min_qty <= 1) DESC, ABS(pp3.min_qty - 1) ASC, COALESCE(pp3.effective_from,'1000-01-01') DESC LIMIT 1 ) WHERE p.tenant_id = ? AND p.id = ? AND p.status = 'active' LIMIT 1 `, [baseCurrency, tenantId, productId] ) const raw = rows?.[0] || null if (!raw) { return res.status(404).json({ success: false, error: "Product not found" }) } // Create a wrapper for shapeProductRow that adds image formatting const productWithImageFix = (req, row) => { const product = shapeProductRow(req, row); // Format the image URL from all possible sources let finalImageUrl = ''; // Check in priority order: // 1. Full URL from img_image_url if (row.img_image_url && row.img_image_url.trim() && row.img_image_url.toLowerCase().startsWith('http')) { finalImageUrl = row.img_image_url.trim(); } // 2. Full URL from image_url_fallback (attributes) else if (row.image_url_fallback && row.image_url_fallback.trim() && row.image_url_fallback.toLowerCase().startsWith('http')) { finalImageUrl = row.image_url_fallback.trim(); } // 3. Path from img_path or media_path else if (row.img_path || row.media_path) { const path = (row.img_path || row.media_path).trim(); if (path.toLowerCase().startsWith('http')) { finalImageUrl = path; } else if (path.startsWith('/')) { finalImageUrl = `https://www.giftwrap.co.za${path}`; } else if (path.includes('amrcdn.amrod.co.za')) { finalImageUrl = `https://${path}`; } else if (path.startsWith('protected/images/')) { finalImageUrl = `https://www.giftwrap.co.za/${path.replace('protected/', '')}`; } else { finalImageUrl = `https://www.giftwrap.co.za/images/products/large/${path}`; } } // 4. Local path from img_local_path else if (row.img_local_path) { const localPath = row.img_local_path.trim(); if (localPath.toLowerCase().startsWith('http')) { finalImageUrl = localPath; } else if (localPath.startsWith('/')) { finalImageUrl = `https://www.giftwrap.co.za${localPath}`; } else if (localPath.includes('amrcdn.amrod.co.za')) { finalImageUrl = `https://${localPath}`; } else if (localPath.startsWith('protected/images/')) { finalImageUrl = `https://www.giftwrap.co.za/${localPath.replace('protected/', '')}`; } else { finalImageUrl = `https://www.giftwrap.co.za/images/products/${localPath}`; } } // Only update if we found an image if (finalImageUrl) { product.image_url = finalImageUrl; product.image = finalImageUrl; } return product; }; const product = productWithImageFix(req, raw); return res.json({ success: true, product, data: { product } }) }), ) /* --------------------------------------------------------- * POPULAR PRODUCTS ENDPOINT * --------------------------------------------------------- */ app.get( "/api/store/products/popular", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1; const baseCurrency = (req.tenant?.base_currency || "ZAR").toUpperCase(); const limit = Math.min(Number(req.query.limit) || 5, 50); // Get cost/markup select fields const costMarkupSelect = getCostMarkupSelect('p'); const [rows] = await pool.query( ` SELECT p.id, p.sku, p.name, p.slug, p.short_description, p.description AS long_description, p.page_title, p.meta_description, p.supplier_code, p.status, p.brand AS brand, JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.brand')) AS brand_json, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.stock_qty')),'0') AS UNSIGNED) AS stock_qty, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.rating')),'0') AS DECIMAL(3,1)) AS rating, COALESCE(CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN (COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)) ELSE NULL END, pp.price_ex_vat, pp_fb.price_ex_vat) AS price_ex_vat, COALESCE(pp.currency_code, pp_fb.currency_code) AS currency_code, COALESCE(pp.min_qty, pp_fb.min_qty, 1) AS min_qty, -- IMAGE FIX: Get all possible image columns ( SELECT image_url FROM catalog_product_images WHERE product_id = p.id AND (is_primary = 1 OR is_primary IS NULL) ORDER BY sort_order ASC, position ASC LIMIT 1 ) AS img_image_url, ( SELECT path FROM catalog_product_images WHERE product_id = p.id AND (is_primary = 1 OR is_primary IS NULL) ORDER BY sort_order ASC, position ASC LIMIT 1 ) AS img_path, ( SELECT local_path FROM catalog_product_images WHERE product_id = p.id AND (is_primary = 1 OR is_primary IS NULL) ORDER BY sort_order ASC, position ASC LIMIT 1 ) AS img_local_path, ( SELECT path FROM catalog_product_media WHERE product_id = p.id AND (is_primary = 1 OR is_primary IS NULL) ORDER BY sort_order ASC LIMIT 1 ) AS media_path ${costMarkupSelect} FROM catalog_products p LEFT JOIN catalog_product_prices pp ON pp.id = ( SELECT pp2.id FROM catalog_product_prices pp2 WHERE pp2.product_id = p.id AND pp2.is_active=1 AND (pp2.effective_from IS NULL OR pp2.effective_from <= CURRENT_DATE()) AND (pp2.effective_to IS NULL OR pp2.effective_to >= CURRENT_DATE()) AND pp2.min_qty >= 1 AND pp2.currency_code = ? ORDER BY (pp2.min_qty <= 1) DESC, ABS(pp2.min_qty - 1) ASC, COALESCE(pp2.effective_from,'1000-01-01') DESC LIMIT 1 ) LEFT JOIN catalog_product_prices pp_fb ON pp.id IS NULL AND pp_fb.id = ( SELECT pp3.id FROM catalog_product_prices pp3 WHERE pp3.product_id = p.id AND pp3.is_active=1 AND (pp3.effective_from IS NULL OR pp3.effective_from <= CURRENT_DATE()) AND (pp3.effective_to IS NULL OR pp3.effective_to >= CURRENT_DATE()) AND pp3.min_qty >= 1 ORDER BY (pp3.min_qty <= 1) DESC, ABS(pp3.min_qty - 1) ASC, COALESCE(pp3.effective_from,'1000-01-01') DESC LIMIT 1 ) WHERE p.tenant_id=? AND p.status='active' ORDER BY CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json, '$.popularity')), '0') AS UNSIGNED) DESC, p.id DESC LIMIT ? `, [baseCurrency, tenantId, limit], ); // Create a wrapper for shapeProducts that adds image formatting const shapeProductsWithImageFix = (req, rows) => { const products = shapeProducts(req, rows); // Add image formatting to each product return products.map((product, index) => { const row = rows[index]; // Format the image URL from all possible sources let finalImageUrl = ''; // Check in priority order: // 1. Full URL from img_image_url if (row.img_image_url && row.img_image_url.trim() && row.img_image_url.toLowerCase().startsWith('http')) { finalImageUrl = row.img_image_url.trim(); } // 2. Path from img_path or media_path else if (row.img_path || row.media_path) { const path = (row.img_path || row.media_path).trim(); if (path.toLowerCase().startsWith('http')) { finalImageUrl = path; } else if (path.startsWith('/')) { finalImageUrl = `https://www.giftwrap.co.za${path}`; } else if (path.includes('amrcdn.amrod.co.za')) { finalImageUrl = `https://${path}`; } else if (path.startsWith('protected/images/')) { finalImageUrl = `https://www.giftwrap.co.za/${path.replace('protected/', '')}`; } else { finalImageUrl = `https://www.giftwrap.co.za/images/products/large/${path}`; } } // 3. Local path from img_local_path else if (row.img_local_path) { const localPath = row.img_local_path.trim(); if (localPath.toLowerCase().startsWith('http')) { finalImageUrl = localPath; } else if (localPath.startsWith('/')) { finalImageUrl = `https://www.giftwrap.co.za${localPath}`; } else if (localPath.includes('amrcdn.amrod.co.za')) { finalImageUrl = `https://${localPath}`; } else if (localPath.startsWith('protected/images/')) { finalImageUrl = `https://www.giftwrap.co.za/${localPath.replace('protected/', '')}`; } else { finalImageUrl = `https://www.giftwrap.co.za/images/products/${localPath}`; } } // Only update if we found an image if (finalImageUrl) { return { ...product, image_url: finalImageUrl, image: finalImageUrl }; } return product; }); }; const products = shapeProductsWithImageFix(req, rows); safeJsonResponse(res, { success: true, products, items: products, data: { products } }); }), ); app.get( "/api/store/products", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1; const baseCurrency = (req.tenant?.base_currency || "ZAR").toUpperCase(); const limit = Math.min(Number(req.query.limit) || 5, 50); // Get cost/markup select fields const costMarkupSelect = getCostMarkupSelect('p'); const [rows] = await pool.query( ` SELECT p.id, p.sku, p.name, p.slug, p.short_description, p.description AS long_description, p.page_title, p.meta_description, p.supplier_code, p.status, p.brand AS brand, JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.brand')) AS brand_json, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.stock_qty')),'0') AS UNSIGNED) AS stock_qty, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.rating')),'0') AS DECIMAL(3,1)) AS rating, COALESCE(CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN (COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)) ELSE NULL END, pp.price_ex_vat, pp_fb.price_ex_vat) AS price_ex_vat, COALESCE(pp.currency_code, pp_fb.currency_code) AS currency_code, COALESCE(pp.min_qty, pp_fb.min_qty, 1) AS min_qty, -- IMAGE FIX: Get all possible image columns ( SELECT image_url FROM catalog_product_images WHERE product_id = p.id AND (is_primary = 1 OR is_primary IS NULL) ORDER BY sort_order ASC, position ASC LIMIT 1 ) AS img_image_url, ( SELECT path FROM catalog_product_images WHERE product_id = p.id AND (is_primary = 1 OR is_primary IS NULL) ORDER BY sort_order ASC, position ASC LIMIT 1 ) AS img_path, ( SELECT local_path FROM catalog_product_images WHERE product_id = p.id AND (is_primary = 1 OR is_primary IS NULL) ORDER BY sort_order ASC, position ASC LIMIT 1 ) AS img_local_path, ( SELECT path FROM catalog_product_media WHERE product_id = p.id AND (is_primary = 1 OR is_primary IS NULL) ORDER BY sort_order ASC LIMIT 1 ) AS media_path ${costMarkupSelect} FROM catalog_products p LEFT JOIN catalog_product_prices pp ON pp.id = ( SELECT pp2.id FROM catalog_product_prices pp2 WHERE pp2.product_id = p.id AND pp2.is_active=1 AND (pp2.effective_from IS NULL OR pp2.effective_from <= CURRENT_DATE()) AND (pp2.effective_to IS NULL OR pp2.effective_to >= CURRENT_DATE()) AND pp2.min_qty >= 1 AND pp2.currency_code = ? ORDER BY (pp2.min_qty <= 1) DESC, ABS(pp2.min_qty - 1) ASC, COALESCE(pp2.effective_from,'1000-01-01') DESC LIMIT 1 ) LEFT JOIN catalog_product_prices pp_fb ON pp.id IS NULL AND pp_fb.id = ( SELECT pp3.id FROM catalog_product_prices pp3 WHERE pp3.product_id = p.id AND pp3.is_active=1 AND (pp3.effective_from IS NULL OR pp3.effective_from <= CURRENT_DATE()) AND (pp3.effective_to IS NULL OR pp3.effective_to >= CURRENT_DATE()) AND pp3.min_qty >= 1 ORDER BY (pp3.min_qty <= 1) DESC, ABS(pp3.min_qty - 1) ASC, COALESCE(pp3.effective_from,'1000-01-01') DESC LIMIT 1 ) WHERE p.tenant_id=? AND p.status='active' ORDER BY p.created_at DESC, p.id DESC LIMIT ? `, [baseCurrency, tenantId, limit], ); // Use the same image fix wrapper as above const shapeProductsWithImageFix = (req, rows) => { const products = shapeProducts(req, rows); return products.map((product, index) => { const row = rows[index]; let finalImageUrl = ''; if (row.img_image_url && row.img_image_url.trim() && row.img_image_url.toLowerCase().startsWith('http')) { finalImageUrl = row.img_image_url.trim(); } else if (row.img_path || row.media_path) { const path = (row.img_path || row.media_path).trim(); if (path.toLowerCase().startsWith('http')) { finalImageUrl = path; } else if (path.startsWith('/')) { finalImageUrl = `https://www.giftwrap.co.za${path}`; } else if (path.includes('amrcdn.amrod.co.za')) { finalImageUrl = `https://${path}`; } else if (path.startsWith('protected/images/')) { finalImageUrl = `https://www.giftwrap.co.za/${path.replace('protected/', '')}`; } else { finalImageUrl = `https://www.giftwrap.co.za/images/products/large/${path}`; } } else if (row.img_local_path) { const localPath = row.img_local_path.trim(); if (localPath.toLowerCase().startsWith('http')) { finalImageUrl = localPath; } else if (localPath.startsWith('/')) { finalImageUrl = `https://www.giftwrap.co.za${localPath}`; } else if (localPath.includes('amrcdn.amrod.co.za')) { finalImageUrl = `https://${localPath}`; } else if (localPath.startsWith('protected/images/')) { finalImageUrl = `https://www.giftwrap.co.za/${localPath.replace('protected/', '')}`; } else { finalImageUrl = `https://www.giftwrap.co.za/images/products/${localPath}`; } } if (finalImageUrl) { return { ...product, image_url: finalImageUrl, image: finalImageUrl }; } return product; }); }; const products = shapeProductsWithImageFix(req, rows); safeJsonResponse(res, { success: true, products, items: products, data: { products } }); }), ); /* --------------------------------------------------------- * PAGES/CATEGORIES ENDPOINT * --------------------------------------------------------- */ app.get( "/api/pages/categories", safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1 const categories = await getCategoryHierarchy(req, pool, tenantId) const page = { slug: "categories", title: "Categories", description: "Browse product categories.", seo: { title: `${req.tenant?.name || "GiftWrap"} | Categories`, description: "Browse product categories and discover popular branded items.", robots: "index,follow", }, blocks: [ { type: "hero", heading: "Categories", subheading: "Browse our catalogue by category." }, { type: "categories_grid", count: categories.length }, ], } res.json({ success: true, page, categories, total: categories.length, pagination: { limit: 100, offset: 0, page: 1, pages: 1, total: categories.length, has_more: false }, data: { page, categories, total: categories.length, pagination: { limit: 100, offset: 0, page: 1, pages: 1, total: categories.length, has_more: false }, }, }) }), ) /* --------------------------------------------------------- * FROM YOUR VERSION: Create Collection Router Function * --------------------------------------------------------- */ function createCollectionRouter(pool, appTokenGuard) { const router = express.Router() // Note: This router needs to be mounted at "/api/collections" not "/" // We'll mount it properly below // List collections router.get( "/", appTokenGuard, safeAsync(async (req, res) => { const tenantId = req.tenant?.id || 1 const limit = Math.min(Number(req.query.limit) || 200, 500) const offset = Math.max(Number(req.query.offset) || 0, 0) try { const [rows] = await pool.query( ` SELECT id, name, slug, description, image_url, sort_order, is_featured, COALESCE(product_count, 0) AS product_count FROM catalog_collections WHERE tenant_id = ? ORDER BY COALESCE(sort_order, 9999) ASC, name ASC LIMIT ? OFFSET ? `, [tenantId, limit, offset] ) return safeJsonResponse(res, { success: true, collections: rows, data: { collections: rows, total: rows.length, pagination: { limit, offset, total: rows.length } }, }) } catch (err) { console.error("[collections] list error", err.message) return safeJsonResponse(res, { success: true, collections: [], data: { collections: [], total: 0 } }) } }) ) router.get( "/:collectionId/products", appTokenGuard, safeAsync(async (req, res) => { const tenantId = req.tenant.id const collectionId = Number(req.params.collectionId) if (!collectionId) return res.status(400).json({ success: false, error: "invalid collectionId" }) // ... rest of your collection router logic // This is just the stub - add your full collection router logic here res.json({ success: true, message: "Collection products endpoint", collectionId }) }), ) router.get( "/api/collections/:collectionId/products", appTokenGuard, safeAsync(async (req, res) => { const tenantId = req.tenant.id const collectionId = Number(req.params.collectionId) if (!collectionId) return res.status(400).json({ success: false, error: "invalid collectionId" }) const limit = Math.min(Number(req.query.limit) || 60, 200) const offset = Math.max(Number(req.query.offset) || 0, 0) const qty = Math.max(Number(req.query.qty) || 1, 1) const currency = String(req.query.currency || req.tenant.base_currency || "ZAR").toUpperCase() const search = String(req.query.search || "").trim() const sort = ["name", "price", "stock", "created"].includes(String(req.query.sort)) ? String(req.query.sort) : "name" const dir = String(req.query.dir).toLowerCase() === "desc" ? "desc" : "asc" const where = ["cp.collection_id = ?", "p.tenant_id = ?", "COALESCE(p.is_active,1) = 1", "p.status = 'active'"] const params = [collectionId, tenantId] if (search) { where.push("(p.name LIKE ? OR p.sku LIKE ? OR p.slug LIKE ?)") params.push(`%${search}%`, `%${search}%`, `%${search}%`) } const costMarkupSelect = PRICE_HAS_COST_MARKUP ? `, (SELECT cost_price FROM catalog_product_prices WHERE product_id=p.id AND is_active=1 AND min_qty>=1 ORDER BY (min_qty <= 1) DESC, ABS(min_qty - 1) ASC, COALESCE(effective_from,'1000-01-01') DESC LIMIT 1) AS cost_price, (SELECT markup_pct FROM catalog_product_prices WHERE product_id=p.id AND is_active=1 AND min_qty>=1 ORDER BY (min_qty <= 1) DESC, ABS(min_qty - 1) ASC, COALESCE(effective_from,'1000-01-01') DESC LIMIT 1) AS markup_pct ` : `` const sql = ` SELECT SQL_CALC_FOUND_ROWS p.id, p.sku, p.name, p.slug, p.short_description, p.description AS long_description, p.page_title, p.meta_description, p.supplier_code, p.status, p.brand AS brand, JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.brand')) AS brand_json, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.rating')),'0') AS DECIMAL(3,1)) AS rating, COALESCE(inv.stock_qty, 0) AS stock_qty, COALESCE(CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN (COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)) ELSE NULL END, pp.price_ex_vat, pp_fb.price_ex_vat) AS price_ex_vat, COALESCE(pp.currency_code, pp_fb.currency_code) AS currency_code, COALESCE(pp.min_qty, pp_fb.min_qty, 1) AS min_qty, ( SELECT mm.path FROM ( SELECT i.path, 0 AS is_primary, i.position AS sort_order FROM catalog_product_images i WHERE i.product_id = p.id UNION ALL SELECT m.path, m.is_primary, m.sort_order FROM catalog_product_media m WHERE m.product_id = p.id ) mm ORDER BY mm.is_primary DESC, mm.sort_order ASC LIMIT 1 ) AS primary_image_path, COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.image_url')), '') AS image_url_fallback, ( SELECT JSON_ARRAYAGG(name) FROM ( SELECT DISTINCT c2.name AS name FROM catalog_product_categories pc2 JOIN catalog_categories c2 ON c2.id = pc2.category_id WHERE pc2.product_id = p.id ) x ) AS categories, ( SELECT JSON_ARRAYAGG(title) FROM ( SELECT DISTINCT col.title AS title FROM collection_products cp2 JOIN collections col ON col.id = cp2.collection_id WHERE cp2.product_id = p.id ) x ) AS collections, ( SELECT JSON_ARRAYAGG(tag) FROM ( SELECT DISTINCT t.tag AS tag FROM catalog_product_tags t WHERE t.product_id = p.id ) x ) AS tags, ( SELECT JSON_ARRAYAGG( JSON_OBJECT( 'kind', m.kind, 'variant', m.variant, 'path', m.path, 'sort_order', m.sort_order, 'is_primary', m.is_primary ) ) FROM ( SELECT 'image' AS kind, NULL AS variant, i.path, i.position AS sort_order, 0 AS is_primary FROM catalog_product_images i WHERE i.product_id = p.id UNION ALL SELECT m.kind, m.variant, m.path, m.sort_order, m.is_primary FROM catalog_product_media m WHERE m.product_id = p.id ) m ) AS media, ( SELECT JSON_ARRAYAGG( JSON_OBJECT('currency', pr.currency_code, 'min_qty', pr.min_qty, 'price_ex_vat', pr.price_ex_vat) ) FROM catalog_product_prices pr WHERE pr.product_id=p.id AND pr.is_active=1 ) AS prices ${costMarkupSelect} FROM collection_products cp JOIN catalog_products p ON p.id = cp.product_id LEFT JOIN ( SELECT v.product_id, SUM(COALESCE(i.on_hand,0) - COALESCE(i.reserved,0)) AS stock_qty FROM catalog_product_variants v LEFT JOIN catalog_variant_inventory i ON i.variant_id = v.id GROUP BY v.product_id ) inv ON inv.product_id = p.id LEFT JOIN catalog_product_prices pp ON pp.id = ( SELECT pp2.id FROM catalog_product_prices pp2 WHERE pp2.product_id = p.id AND pp2.is_active=1 AND (pp2.effective_from IS NULL OR pp2.effective_from <= CURRENT_DATE()) AND (pp2.effective_to IS NULL OR pp2.effective_to >= CURRENT_DATE()) AND pp2.min_qty <= ? AND pp2.currency_code = ? ORDER BY pp2.min_qty DESC, COALESCE(pp2.effective_from,'1000-01-01') DESC LIMIT 1 ) LEFT JOIN catalog_product_prices pp_fb ON pp.id IS NULL AND pp_fb.id = ( SELECT pp3.id FROM catalog_product_prices pp3 WHERE pp3.product_id = p.id AND pp3.is_active=1 AND (pp3.effective_from IS NULL OR pp3.effective_from <= CURRENT_DATE()) AND (pp3.effective_to IS NULL OR pp3.effective_to >= CURRENT_DATE()) AND pp3.min_qty <= ? ORDER BY pp3.min_qty DESC, COALESCE(pp3.effective_from,'1000-01-01') DESC LIMIT 1 ) WHERE ${where.join(" AND ")} ORDER BY CASE WHEN ?='name' AND ?='asc' THEN p.name END ASC, CASE WHEN ?='name' AND ?='desc' THEN p.name END DESC, CASE WHEN ?='created' AND ?='asc' THEN p.created_at END ASC, CASE WHEN ?='created' AND ?='desc' THEN p.created_at END DESC, CASE WHEN ?='stock' AND ?='asc' THEN inv.stock_qty END ASC, CASE WHEN ?='stock' AND ?='desc' THEN inv.stock_qty END DESC, CASE WHEN ?='price' AND ?='asc' THEN COALESCE(CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN (COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)) ELSE NULL END, pp.price_ex_vat, pp_fb.price_ex_vat) END ASC, CASE WHEN ?='price' AND ?='desc' THEN COALESCE(CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN (COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)) ELSE NULL END, pp.price_ex_vat, pp_fb.price_ex_vat) END DESC, p.name ASC LIMIT ? OFFSET ? ` const sqlParams = [ ...params, qty, currency, qty, sort, dir, sort, dir, sort, dir, sort, dir, sort, dir, sort, dir, limit, offset, ] const [rows] = await pool.query(sql, sqlParams) const [[found]] = await pool.query("SELECT FOUND_ROWS() AS total") const total = Number(found?.total || 0) const products = shapeProducts(req, rows) res.json({ success: true, products, items: products, pagination: { limit, offset, total }, total, data: { products, pagination: { limit, offset, total }, total }, }) }), ) return router } // ============================================================================ // V3 CATALOG API (BENJE AI ENABLED) // ============================================================================ const { createV3CatalogController } = require("./controllers/v3CatalogController"); const v3CatalogRouter = createV3CatalogController(pool, { enable_cache: true, enable_validation: true, enable_images: true, enable_ai: true, enable_performance_monitoring: true }); // app.use('/api/v3/catalog', v3CatalogRouter); // ← alternative sub-path (disabled) // ✅ FIX: /collections/index alias — must be registered BEFORE app.use('/api/v3', ...) // Without this, the v3 router's /:id wildcard treats 'index' as a collection ID → 500 error. app.get('/api/v3/collections/index', (req, res, next) => { req.url = '/collections'; return v3CatalogRouter(req, res, next); }); app.use('/api/v3', v3CatalogRouter); // ✅ FIXED: mount v3 router so /api/v3/collections works /* --------------------------------------------------------- * Mount remaining routers * --------------------------------------------------------- */ app.use("/api/catalog", createCatalogRouter(pool)); app.use("/", createSitemapRouter(pool)); app.use("/", createMediaRouter(__dirname)); if (typeof createDiscoveryRouter === "function") app.use("/api/discovery", createDiscoveryRouter(pool)); if (typeof createSearchRouter === "function") app.use("/api/search", createSearchRouter(pool)); if (typeof createContentRouter === "function") app.use("/api", createContentRouter(pool)); if (typeof createProductPageRouter === "function") app.use("/", createProductPageRouter(pool)); // Mount the real collection router (handles /api/collections/*, /collections, /collections/:slug, etc.) app.use("/", createRealCollectionRouter(pool, appTokenGuard)); /* --------------------------------------------------------- * ✅ Categories SSR routes * --------------------------------------------------------- */ app.get( "/categories", safeAsync(async (req, res, next) => { if (req.path.startsWith("/api/")) return next() const tenantId = req.tenant?.id || 1 const categories = await getCategoryHierarchy(req, pool, tenantId) const tenant = req.tenant const origin = tenant?.domain ? `https://${tenant.domain}` : APP_ORIGIN const canonical = `${origin}/categories` const preload = { page: "categories-index", view_mode: "categories", categories, total: categories.length } const flatten = (cats) => { let flat = [] for (const c of cats || []) { flat.push(c) if (c.children?.length) flat = flat.concat(flatten(c.children)) } return flat } const noscriptHtml = buildCategoriesNoscript(req, flatten(categories)) return buildSpa(req, res, { entityType: "categories-index", entityId: null, fallback: { title: `${tenant?.name || "Giftwrap"} | Categories`, description: `Browse categories from ${tenant?.name || "Giftwrap"}.`, canonical, robots: "index,follow", }, schemaJsonLd: { "@context": "https://schema.org", "@type": "CollectionPage", name: "Categories", url: canonical }, preload, noscriptHtml, }) }), ) app.get( "/categories/:slug", safeAsync(async (req, res, next) => { if (req.path.startsWith("/api/")) return next() const slug = String(req.params.slug || "").trim() const tenantId = req.tenant?.id || 1 const node = await getCategoryBySlug(req, tenantId, slug) if (!node) return buildSpa(req, res) const subcats = node.children || [] const descendantIds = await getCategoryDescendants(req, pool, tenantId, node.id) const baseCurrency = (req.tenant?.base_currency || "ZAR").toUpperCase() const product_limit = Math.min(Number(req.query.limit) || 24, 100) const product_offset = req.query.offset != null ? Math.max(Number(req.query.offset) || 0, 0) : 0 let products = [] let products_total = 0 if (descendantIds.length > 0) { const [prodRows] = await pool.query( ` SELECT SQL_CALC_FOUND_ROWS DISTINCT p.id, p.sku, p.name, p.slug, p.short_description, p.description AS long_description, p.page_title, p.meta_description, p.supplier_code, p.status, p.brand AS brand, JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.brand')) AS brand_json, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.rating')),'0') AS DECIMAL(3,1)) AS rating, CAST(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.stock_qty')),'0') AS UNSIGNED) AS stock_qty, COALESCE(CASE WHEN COALESCE(pp.cost_price, pp_fb.cost_price) IS NOT NULL OR COALESCE(pp.markup_pct, pp_fb.markup_pct) IS NOT NULL THEN (COALESCE(pp.cost_price, pp_fb.cost_price, 0) * (1 + COALESCE(pp.markup_pct, pp_fb.markup_pct, 0) / 100)) ELSE NULL END, pp.price_ex_vat, pp_fb.price_ex_vat) AS price_ex_vat, COALESCE(pp.currency_code, pp_fb.currency_code) AS currency_code, COALESCE(pp.min_qty, pp_fb.min_qty, 1) AS min_qty, ( SELECT mm.path FROM ( SELECT i.path, 0 AS is_primary, i.position AS sort_order FROM catalog_product_images i WHERE i.product_id = p.id UNION ALL SELECT m.path, m.is_primary, m.sort_order FROM catalog_product_media m WHERE m.product_id = p.id ) mm ORDER BY mm.is_primary DESC, mm.sort_order ASC LIMIT 1 ) AS primary_image_path, COALESCE(JSON_UNQUOTE(JSON_EXTRACT(p.attributes_json,'$.image_url')), '') AS image_url_fallback FROM catalog_products p JOIN catalog_product_categories pc ON pc.product_id = p.id LEFT JOIN catalog_product_prices pp ON pp.id = ( SELECT pp2.id FROM catalog_product_prices pp2 WHERE pp2.product_id = p.id AND pp2.is_active=1 AND (pp2.effective_from IS NULL OR pp2.effective_from <= CURRENT_DATE()) AND (pp2.effective_to IS NULL OR pp2.effective_to >= CURRENT_DATE()) AND pp2.min_qty >= 1 AND pp2.currency_code = ? ORDER BY (pp2.min_qty <= 1) DESC, ABS(pp2.min_qty - 1) ASC, COALESCE(pp2.effective_from,'1000-01-01') DESC LIMIT 1 ) LEFT JOIN catalog_product_prices pp_fb ON pp.id IS NULL AND pp_fb.id = ( SELECT pp3.id FROM catalog_product_prices pp3 WHERE pp3.product_id = p.id AND pp3.is_active=1 AND (pp3.effective_from IS NULL OR pp3.effective_from <= CURRENT_DATE()) AND (pp3.effective_to IS NULL OR pp3.effective_to >= CURRENT_DATE()) AND pp3.min_qty >= 1 ORDER BY (pp3.min_qty <= 1) DESC, ABS(pp3.min_qty - 1) ASC, COALESCE(pp3.effective_from,'1000-01-01') DESC LIMIT 1 ) WHERE p.tenant_id=? AND pc.category_id IN (?) AND p.status='active' AND COALESCE(p.is_active,1)=1 ORDER BY p.name ASC LIMIT ? OFFSET ? `, [baseCurrency, tenantId, descendantIds, product_limit, product_offset], ) const [[found]] = await pool.query("SELECT FOUND_ROWS() AS total") products_total = Number(found?.total || 0) products = shapeProducts(req, prodRows) } const view_mode = products_total > 0 ? "products" : subcats.length > 0 ? "categories" : "empty" const tenant = req.tenant const origin = tenant?.domain ? `https://${tenant.domain}` : APP_ORIGIN const canonical = `${origin}/categories/${node.slug}` const preload = { page: "category", category: { id: Number(node.id), name: node.name, slug: node.slug, description: node.description || null, parent_id: node.parent_id, }, view_mode, subcategories: subcats, products, products_pagination: { limit: product_limit, offset: product_offset, total: products_total, pages: Math.max(1, Math.ceil(products_total / product_limit) || 1), has_more: product_offset + products.length < products_total, }, } const noscriptHtml = view_mode === "products" ? buildProductsNoscript(req, products) : buildCategoriesNoscript(req, subcats) return buildSpa(req, res, { entityType: "category", entityId: Number(node.id), fallback: { title: `${node.name} | ${tenant?.name || "Giftwrap"}`, description: node.description || `Browse ${node.name} from ${tenant?.name || "Giftwrap"}.`, canonical, robots: "index,follow", }, schemaJsonLd: { "@context": "https://schema.org", "@type": "CollectionPage", name: node.name, url: canonical }, preload, noscriptHtml, }) }), ) app.use("/api", apiController); /* --------------------------------------------------------- * Root route "/" (serves SPA if built, else JSON) * --------------------------------------------------------- */ app.get( "/", safeAsync(async (req, res) => { console.log("[DEBUG] [ROOT] Handler reached for host:", req.headers.host); const tenant = req.tenant; if (!tenant) { return res.status(200).json({ success: true, message: "Giftwrap API Server", version: "1.0.0", endpoints: { health: "/healthz", ready: "/readyz", categories: "/api/categories", products: "/api/catalog/products", collections: "/api/collections", }, }); } const spaIndex = findSpaIndex(); if (spaIndex) return buildSpa(req, res); return res.json({ success: true, message: `Welcome to ${tenant.name || "Giftwrap"}`, tenant: { id: tenant.id, name: tenant.name, domain: tenant.domain }, }); }) ); /* --------------------------------------------------------- * SPA Shell Builder * --------------------------------------------------------- */ function findSpaIndex() { const candidates = [ path.join(__dirname, "public", "bdls-mts", "dist", "index.html"), path.join(__dirname, "public", "bdls-mts", "frontend", "index.html"), path.join(__dirname, "public", "dist", "index.html"), path.join(__dirname, "dist", "index.html"), path.join(__dirname, "public", "index.html"), ]; for (const p of candidates) if (fs.existsSync(p)) return p; return null; } function buildCategoriesNoscript(req, categories) { const cards = (categories || []) .map((c) => { const href = `/categories/${encodeURIComponent(c.slug || c.id)}` return `
${escapeHtml(c.name)}
` }) .join("\n") return `

Categories

${cards || `
No categories found.
`}
` } function buildProductsNoscript(req, products) { const cards = (products || []) .map((p) => { const href = `/products/${encodeURIComponent(p.slug || p.id)}` const img = p.image_url ? `${escapeHtml(p.name)}` : "" return `${img}
${escapeHtml(p.name)}
` }) .join("\n") return `

Products

${cards || `
No products found.
`}
` } function escapeHtml(s) { return String(s || "") .replaceAll("&", "&") .replaceAll("<", "<") .replaceAll(">", ">") .replaceAll('"', """) .replaceAll("'", "'") } async function buildSpa(req, res, extra = null) { console.log("[DEBUG] [SPA] buildSpa called for path:", req.originalUrl || "/"); try { const tenant = req.tenant || (await resolveTenantByDomain(pool, normaliseHost(pickIncomingHost(req)), req)); if (!tenant) { const html404 = path.join(__dirname, "public", "404.html"); if (fs.existsSync(html404)) return res.status(404).sendFile(html404); return res.status(404).type("text").send("Tenant not found"); } const spaIndex = findSpaIndex(); if (!spaIndex) { return res.json({ success: true, message: `Welcome to ${tenant.name || "Giftwrap"}`, tenant: { id: tenant.id, name: tenant.name, domain: tenant.domain }, }); } let html = fs.readFileSync(spaIndex, "utf8"); const origin = tenant.domain ? `https://${tenant.domain}` : APP_ORIGIN; const routePath = (req.originalUrl || "").split("?")[0] || "/"; console.log(`[SPA] Building for path: ${routePath}`); const url = `${origin}${routePath}`; html = await applyThemeAndSeo(pool, html, { req, tenant, routePath, entityType: extra?.entityType || "static", entityId: extra?.entityId || null, fallback: extra?.fallback || { title: `${tenant.name || "Giftwrap"} – Branded Gifts & Clothing`, description: `Browse thousands of branded promotional gifts, clothing and corporate items from ${tenant.name || "Giftwrap" }.`, canonical: url, robots: "index,follow", }, schemaJsonLd: extra?.schemaJsonLd || { "@context": "https://schema.org", "@type": "Organization", name: tenant.name || "Giftwrap", url, logo: tenant.logo_path ? `${origin}${tenant.logo_path}` : `${origin}/protected-image/Giftwrap%20New%20Logo@4x-8`, }, APP_ORIGIN, }); const bootstrap = { tenant: { id: tenant.id, slug: tenant.slug, name: tenant.name, brand_name: tenant.brand_name || tenant.name, logo_path: tenant.logo_path, domain: tenant.domain, base_currency: tenant.currency_code || "ZAR", }, session: { authenticated: !!req.session?.authenticated, user: req.session?.user || null, }, }; const scripts = []; scripts.push(``); if (extra?.preload) scripts.push(``); const inject = scripts.join("\n"); html = html.includes("") ? html.replace("", `${inject}\n`) : html.replace("", `${inject}\n`); res.send(html); } catch (err) { console.error("Error in buildSpa", err); res.status(500).send("Internal server error"); } } /* --------------------------------------------------------- * Legacy URL resolver (keeps old SEO links alive) * --------------------------------------------------------- */ app.use((req, res, next) => { const method = (req.method || "GET").toUpperCase(); if (method !== "GET" && method !== "HEAD") return next(); const pathname = req.path || "/"; // Skip obvious static/api prefixes if (LEGACY_SKIP_PREFIXES.some((p) => pathname.startsWith(p))) return next(); // Skip if already on canonical product/category paths if (pathname.startsWith("/product/") || pathname.startsWith("/products/") || pathname.startsWith("/category/") || pathname.startsWith("/categories/")) { return next(); } const normalized = normalizeLegacyPath(pathname); const explicit = LEGACY_REDIRECTS.explicitMap?.[normalized]; const target = explicit || resolveLegacyTarget(pathname); if (!target) return next(); // Preserve incoming querystring const query = req.originalUrl.includes("?") ? req.originalUrl.slice(req.originalUrl.indexOf("?")) : ""; let finalTarget = target; if (query) { finalTarget = target.includes("?") ? `${target}&${query.slice(1)}` : `${target}${query}`; } // Avoid redirect loops if (normalizeLegacyPath(finalTarget) === normalized) return next(); return res.redirect(301, finalTarget); }); /* --------------------------------------------------------- * SPA catch-all for client-side routing * --------------------------------------------------------- */ // SPA catch-all for client-side routing app.get(/^(?!\/(api|protected-image|images|src|assets)(?:\/|$)).*/, (req, res, next) => { if (path.extname(req.path)) return next(); const spaIndex = findSpaIndex(); if (spaIndex) return buildSpa(req, res); next(); }); // Serve static assets from the frontend src directory app.use( "/src", express.static(path.join(__dirname, "public", "bdls-mts", "frontend", "src"), { setHeaders: (res, filePath) => { res.setHeader("X-Content-Type-Options", "nosniff"); if (IS_PROD && filePath.includes(`${path.sep}assets${path.sep}`)) { res.setHeader("Cache-Control", "public, max-age=31536000, immutable"); } else { res.setHeader("Cache-Control", IS_PROD ? "public, max-age=600" : "no-cache"); } }, }) ); // Serve SPA dist at root (/) so https://mobi.giftwrap.co.za/ works directly app.use( express.static(path.join(__dirname, "public", "bdls-mts", "dist"), { setHeaders: (res, filePath) => { res.setHeader("X-Content-Type-Options", "nosniff"); if (IS_PROD && filePath.includes(`${path.sep}assets${path.sep}`)) { res.setHeader("Cache-Control", "public, max-age=31536000, immutable"); } else { res.setHeader("Cache-Control", IS_PROD ? "public, max-age=600" : "no-cache"); } }, }) ); // Keep legacy path for backwards compatibility app.use( "/bdls-mts/dist", express.static(path.join(__dirname, "public", "bdls-mts", "dist"), { setHeaders: (res, filePath) => { res.setHeader("X-Content-Type-Options", "nosniff"); if (IS_PROD && filePath.includes(`${path.sep}assets${path.sep}`)) { res.setHeader("Cache-Control", "public, max-age=31536000, immutable"); } else { res.setHeader("Cache-Control", IS_PROD ? "public, max-age=600" : "no-cache"); } }, }) ); // Change this line in your server.js: // const { V3CatalogControllerMerged } = require("./controllers/v3CatalogController.merged"); // To this (based on the file you shared): /* --------------------------------------------------------- * 404 Handler for API routes * --------------------------------------------------------- */ // SPA catch-all for client-side routing // Serve generic static assets (placeholders, etc) app.use( "/images", express.static(path.join(__dirname, "public", "images"), { setHeaders: (res) => { res.setHeader("X-Content-Type-Options", "nosniff"); res.setHeader("Cache-Control", "public, max-age=86400"); }, }) ); // ALIASES for Storefront SPA (matches Categories.vue and quote.js store calls) app.get("/api/store/categories", (req, res) => res.redirect(307, "/api/categories?format=hierarchical")); app.get("/api/store/categories/:slug", (req, res) => res.redirect(307, `/api/v2/categories/${req.params.slug}`)); // Quote Cart Aliases app.get("/api/store/quote-cart", (req, res) => res.redirect(307, "/api/store/quote")); app.post("/api/store/quote-cart/items", (req, res) => res.redirect(307, "/api/store/quote/items")); app.delete("/api/store/quote-cart/items/:id", (req, res) => res.redirect(307, `/api/store/quote/items/${req.params.id}`)); app.patch("/api/store/quote-cart/items/:id", (req, res) => res.redirect(307, `/api/store/quote/items/${req.params.id}`)); app.post("/api/store/quote-cart/submit", (req, res) => res.redirect(307, "/api/store/quotes/request")); // Page Content app.get("/api/pages/about", (req, res) => { res.json({ success: true, page: {} }); }); app.use("/api", (req, res) => { res.status(404).json({ success: false, error: "Endpoint not found", path: req.originalUrl, }); }); /* --------------------------------------------------------- * SPA catch-all (Serve index.html for any other route) * --------------------------------------------------------- */ app.use((req, res, next) => { // Only handle GET requests for navigation if (req.method !== 'GET') return next(); const pathParts = req.path.split('/').filter(Boolean); const topLevel = pathParts[0]; // Skip SPA handling for API, static files, and known backend top-level routes const SKIP_PREFIXES = ['api', 'branding', 'categories', 'products', 'uploads', 'protected-image', 'sts', 'auth', 'gate', 'healthz', 'readyz']; if (SKIP_PREFIXES.includes(topLevel) || req.path.includes(".")) { return next(); } // Serve the frontend index.html const indexPath = path.join(__dirname, "public", "bdls-mts", "dist", "index.html"); if (fs.existsSync(indexPath)) { return res.sendFile(indexPath); } // Fallback to a development-friendly message if not built if (!IS_PROD) { return res.status(200).send(`

GiftWrap Developer Server

Backend is running on port 3000.

Frontend (Vite) is running on port 5173.

Go to About Us Page (Vite)

Note: To serve the frontend on port 3000, run npm run build:spa.

`); } next(); }); /* --------------------------------------------------------- * Error Handler * --------------------------------------------------------- */ app.use((err, req, res, _next) => { console.error("[Error]", err); res.status(err.status || 500).json({ success: false, error: IS_PROD ? "Internal server error" : err.message, ...(IS_PROD ? {} : { stack: err.stack }), }); }); /* --------------------------------------------------------- * Start Server * --------------------------------------------------------- */ const server = http.createServer(app); migrationController.initWebSocketServer(server); // ✅ Initialize real-time monitor after server is created but before listening if (process.env.REALTIME_MONITOR_ENABLED === 'true') { const RealTimeMonitor = require('./services/realTimeMonitor'); realTimeMonitor = new RealTimeMonitor(server); console.log(`[Server] ✅ Real-Time Monitor: Initialized`); } const { sequelize } = require('./models'); // const PORT = process.env.PORT || 3000;? sequelize.sync({ alter: process.env.NODE_ENV === 'development' }) .then(() => { console.log('✅ Database synced – tables are ready'); server.listen(PORT, () => { console.log(`[Server] Running on port ${PORT} in ${NODE_ENV} mode`); console.log(`[Server] Health check: http://localhost:${PORT}/healthz`); console.log(`[Server] Ready check: http://localhost:${PORT}/readyz`); console.log(`[Server] ✅ WhatsApp Service: ${wa ? "Loaded" : "Not available"}`); console.log(`[Server] ✅ AI Assistant: ${EnhancedAIAssistant ? "Loaded" : "Not available"}`); console.log(`[Server] ✅ Gate System: ${createGateRouter ? "Loaded" : "Not available"}`); }); }) .catch(err => { console.error('❌ Database sync failed:', err.message); // Still start server – but tables are missing, so API will fail server.listen(PORT, () => { console.log(`[Server] Running WITHOUT database sync – tables may be missing`); }); }); module.exports = { app, server };