/// <reference path="../pb_data/types.d.ts" />

// "Complétez votre look" — cart-based cross-sell recommendations.
//
// Different question from the product-page engine (main.pb.js): not "what's
// similar to this product?" but "given everything in the cart, what else
// would go well with it?" Built from the same precomputed tables that
// refresh.pb.js rebuilds nightly, so this request does only cheap indexed
// reads + in-memory aggregation, no live scoring.
//
// For every candidate product not already in the cart, aggregated across
// all cart items:
//   score = 0.50 * cart_association      (avg co_purchase_rate vs each cart item)
//         + 0.20 * purchase_frequency    (overall popularity, catalog-wide)
//         + 0.15 * product_similarity    (avg shared_tags vs each cart item)
//         + 0.10 * category_complementarity (avg cross-category affinity)
//         + 0.05 * price_compatibility   (avg price_similarity vs each cart item)
//
// cart_association/product_similarity/price_compatibility come straight out
// of product_recommendations (it's a full cross-join of all published pairs,
// so every cart item has a row for every other published product). This is
// what makes it "association-rule-like": a candidate that individually
// pairs well with several cart items accumulates signal from each of them,
// which is the multi-item aggregation the spec asked for, without needing
// exact-combination (A+B+C) statistics that 350 orders can't support.
//
// POST /recommendations/cart  body: { "productIds": ["id1", "id2", ...] }
routerAdd("POST", "/recommendations/cart", (e) => {
  const data = new DynamicModel({ productIds: [] })
  e.bindBody(data)

  const rawIds = Array.isArray(data.productIds) ? data.productIds : []
  const cartIds = [...new Set(rawIds.filter((id) => typeof id === "string" && id.length > 0))]

  if (cartIds.length === 0) {
    return e.error(400, "productIds must be a non-empty array of product ids", null)
  }

  const cartProducts = $app.findRecordsByIds("products", cartIds)
  const validCartIds = cartProducts.map((p) => p.id)
  const cartCategories = [...new Set(cartProducts.map((p) => p.get("category")).filter((c) => c))]

  if (validCartIds.length === 0) {
    return e.error(400, "no matching products found for the given productIds", null)
  }

  const filterParts = validCartIds.map((_, i) => `product = {:c${i}}`)
  const excludeParts = validCartIds.map((_, i) => `recommended_product != {:c${i}}`)
  const filter = `(${filterParts.join(" || ")}) && (${excludeParts.join(" && ")})`
  const params = {}
  validCartIds.forEach((id, i) => { params["c" + i] = id })

  const pairRows = $app.findRecordsByFilter("product_recommendations", filter, "", 1000, 0, params)

  const byCandidate = {}
  pairRows.forEach((row) => {
    const candidateId = row.get("recommended_product")
    if (!byCandidate[candidateId]) {
      byCandidate[candidateId] = { coPurchaseRates: [], sharedTags: [], priceSimilarities: [] }
    }
    byCandidate[candidateId].coPurchaseRates.push(row.get("co_purchase_rate"))
    byCandidate[candidateId].sharedTags.push(row.get("shared_tags"))
    byCandidate[candidateId].priceSimilarities.push(row.get("price_similarity"))
  })

  const candidateIds = Object.keys(byCandidate)
  if (candidateIds.length === 0) {
    return e.json(200, { cart: validCartIds, recommendations: [] })
  }

  const popularityRows = $app.findRecordsByFilter("product_popularity", "", "", 1000, 0, {})
  const popularityById = {}
  popularityRows.forEach((row) => { popularityById[row.get("product")] = row.get("popularity") })

  const affinityRows = $app.findRecordsByFilter("category_affinity", "", "", 1000, 0, {})
  const affinityByPair = {}
  affinityRows.forEach((row) => {
    affinityByPair[row.get("category_a") + "|" + row.get("category_b")] = row.get("confidence")
  })

  const candidateProducts = $app.findRecordsByIds("products", candidateIds)
  const candidateById = {}
  candidateProducts.forEach((p) => { candidateById[p.id] = p })

  const avg = (arr) => arr.length === 0 ? 0 : arr.reduce((a, b) => a + b, 0) / arr.length

  const scored = candidateIds.map((candidateId) => {
    const agg = byCandidate[candidateId]
    const product = candidateById[candidateId]

    const cartAssociation = avg(agg.coPurchaseRates)
    const productSimilarity = avg(agg.sharedTags)
    const priceCompatibility = avg(agg.priceSimilarities)
    const purchaseFrequency = popularityById[candidateId] || 0

    const candidateCategory = product ? product.get("category") : ""
    const categoryComplementarity = cartCategories.length === 0 ? 0 : avg(
      cartCategories.map((cat) => affinityByPair[cat + "|" + candidateCategory] || 0)
    )

    const score =
      0.50 * cartAssociation +
      0.20 * purchaseFrequency +
      0.15 * productSimilarity +
      0.10 * categoryComplementarity +
      0.05 * priceCompatibility

    return {
      id: candidateId,
      name: product ? product.get("name") : null,
      price: product ? product.get("price") : null,
      salePrice: product ? product.get("salePrice") : null,
      images: product ? product.get("images") : [],
      category: candidateCategory,
      score: score,
      cart_association: cartAssociation,
      purchase_frequency: purchaseFrequency,
      product_similarity: productSimilarity,
      category_complementarity: categoryComplementarity,
      price_compatibility: priceCompatibility,
    }
  })

  scored.sort((a, b) => b.score - a.score)

  return e.json(200, { cart: validCartIds, recommendations: scored.slice(0, 4) })
})

/// <reference path="../pb_data/types.d.ts" />

routerAdd("GET", "/api/products/datafeed.xlsx", (c) => {
    try {
        // Fetch all published products
        const products = $app.findRecordsByFilter(
            "products",
            "",
            "-created", // Order by created date descending
            500 // Limit to 500 products max
        );

        // Get the base URL from the request
        const baseUrl = "https://hotmix.ma";
        const storageUrl = "https://hotmix-files.s3.eu-north-1.amazonaws.com";

        // Helper function to strip HTML tags and convert to plain text
        const stripHtml = (html) => {
            if (!html) return "";
            // Remove HTML tags
            let text = html.replace(/<[^>]*>/g, " ");
            // Decode common HTML entities
            text = text.replace(/&nbsp;/g, " ");
            text = text.replace(/&amp;/g, "&");
            text = text.replace(/&lt;/g, "<");
            text = text.replace(/&gt;/g, ">");
            text = text.replace(/&quot;/g, '"');
            text = text.replace(/&#39;/g, "'");
            // Remove extra whitespace
            text = text.replace(/\s+/g, " ").trim();
            return text;
        };

        // Create CSV content (Excel can open CSV files)
        // Header row
        let csvContent = "id\ttitle\tdescription\tavailability\tcondition\tprice\tlink\timage_link\tbrand\n";

        // Data rows
        products.forEach((product) => {
            const id = product.getString("id") || "";
            const title = product.getString("name") || "";
            const descriptionHtml = product.getString("description") || "";
            const description = stripHtml(descriptionHtml);

            // Determine availability based on status
            const status = product.getString("status") || "";
            const availability = status === "published" ? "in_stock" : "out_of_stock";

            // Condition is typically "new" for most products
            const condition = "new";

            // Use salePrice if available, otherwise use regular price
            const salePrice = product.getFloat("salePrice");
            const regularPrice = product.getFloat("price") || 0;
            const price = salePrice > 0 ? salePrice : regularPrice;

            // Format price with currency
            const priceFormatted = `${price.toFixed(2)} MAD`;

            // Product link
            const productSlug = product.getString("slug") || id;
            const link = `${baseUrl}/products/${productSlug}`;

            // Image link - S3 format: bucket_url/collection_id/product_id/filename
            const images = product.get("images");
            let imageLink = "";
            if (images && Array.isArray(images) && images.length > 0) {
                const firstImage = images[0];
                const collectionId = product.collection().id;
                imageLink = `${storageUrl}/${collectionId}/${id}/${firstImage}`;
            }

            // Brand
            const brand = "hotmix";

            // Escape and clean values for CSV
            const cleanValue = (value) => {
                if (!value) return "";
                // Replace tabs and newlines with spaces
                let cleaned = String(value).replace(/[\t\n\r]/g, " ");
                // Trim whitespace
                cleaned = cleaned.trim();
                // If contains comma, quote, or special chars, wrap in quotes
                if (cleaned.includes(",") || cleaned.includes('"') || cleaned.includes("\t")) {
                    cleaned = '"' + cleaned.replace(/"/g, '""') + '"';
                }
                return cleaned;
            };

            // Add row
            csvContent += `${cleanValue(id)}\t${cleanValue(title)}\t${cleanValue(description)}\t${cleanValue(availability)}\t${cleanValue(condition)}\t${cleanValue(priceFormatted)}\t${cleanValue(link)}\t${cleanValue(imageLink)}\t${cleanValue(brand)}\n`;
        });

        // Set response headers for Excel file download
        c.response.header().set("Content-Type", "application/vnd.ms-excel");
        c.response.header().set("Content-Disposition", 'attachment; filename="datafeed.xlsx"');

        // Return the CSV content
        return c.string(200, csvContent);

    } catch (err) {
        console.error("Error generating datafeed:", err);
        return c.json(500, {
            error: "Failed to generate datafeed",
            details: err.message
        });
    }
});

/// <reference path="..\pb_data\types.d.ts" />

onRecordAfterCreateSuccess((e) => {
  const order = e.record;

  const message = new MailerMessage({
    from: {
      address: e.app.settings().meta.senderAddress,
      name:    e.app.settings().meta.senderName,
    },
    to: [{ address: "hotmix.ma@gmail.com" }],
    subject: "New Order Placed #" + order.id,
    html: `
      <h2>A new order has been placed!</h2>
      <p><strong>Order ID:</strong> ${order.id}</p>
      <p><strong>Full Name:</strong> ${order.get("full_name")}</p>
      <p><strong>Total:</strong> ${order.get("total_price")}</p>
      <p><strong>Created:</strong> ${order.get("created")}</p>
      <hr/>
      <p>Log in to your admin panel to view full details.</p>
    `,
  });

  e.app.newMailClient().send(message);

}, "orders");

/// <reference path="../pb_data/types.d.ts" />

function validateAndSetOrderItem(e) {
    const record = e.record;
    const productId = record.get("product");
    const colorId = record.get("color");
    const sizeId = record.get("size");

    if (productId) {
        let productRecord;
        try {
            productRecord = $app.findRecordById("products", productId);
        } catch (err) {
            throw new BadRequestError(`Product with ID "${productId}" not found.`, {
                product: new ValidationError("validation_invalid_product", "Selected product does not exist.")
            });
        }

        if (productRecord) {
            const errors = {};

            // Validate product status (must be published)
            const productStatus = productRecord.get("status");
            if (productStatus !== "published") {
                errors["product"] = new ValidationError(
                    "validation_product_not_published",
                    "The selected product is not published and cannot be ordered."
                );
            }

            // Validate color if provided
            if (colorId) {
                const allowedColors = productRecord.get("colors");
                const colorList = Array.isArray(allowedColors) ? allowedColors : [];
                if (!colorList.includes(colorId)) {
                    errors["color"] = new ValidationError(
                        "validation_invalid_color",
                        "The selected color is not available for this product."
                    );
                }
            }

            // Validate size if provided
            if (sizeId) {
                const allowedSizes = productRecord.get("sizes");
                const sizeList = Array.isArray(allowedSizes) ? allowedSizes : [];
                if (!sizeList.includes(sizeId)) {
                    errors["size"] = new ValidationError(
                        "validation_invalid_size",
                        "The selected size is not available for this product."
                    );
                }
            }

            // If any validation errors exist, throw BadRequestError with field-keyed errors so they render under each field in the UI
            if (Object.keys(errors).length > 0) {
                throw new BadRequestError("Failed to save record.", errors);
            }

            // Set item price based on product sale price or regular price
            const salePrice = parseFloat(productRecord.get("salePrice")) > 0
                ? parseFloat(productRecord.get("salePrice"))
                : (parseFloat(productRecord.get("price")) || 0);
            record.set("price", salePrice);
        }
    }

    e.next();
}

onRecordCreate(validateAndSetOrderItem, "order_items");
onRecordUpdate(validateAndSetOrderItem, "order_items");

// Keep the parent order's total_price in sync whenever an order item is
// created, updated (e.g. quantity change) or deleted. This is needed because
// updating an order_item record directly does not trigger the orders
// collection's request hooks, so its total_price would otherwise go stale.
//
// The order_items.order relation (see the
// 1787000000_add_order_relation_to_order_items.js migration) gives a direct,
// indexed link back to the parent order, so this looks up exactly one order
// by id and sums exactly that order's items (via the indexed `order` field)
// instead of scanning every order in the database.
//
// NOTE: each hook callback is executed by PocketBase's JSVM in isolation, so
// it cannot call helper functions declared elsewhere in this file - all the
// logic must live inside the handler itself (same reason validateAndSetOrderItem
// above is self-contained).
function syncParentOrderTotalPrice(e) {
    const orderId = e.record.get("order");
    if (!orderId) {
        e.next();
        return;
    }

    let orderRecord;
    try {
        orderRecord = $app.findRecordById("orders", orderId);
    } catch (err) {
        console.log("Error finding order for order item:", err);
        e.next();
        return;
    }

    let items;
    try {
        items = $app.findRecordsByFilter("order_items", "order = {:orderId}", "", 0, 0, { orderId: orderId });
    } catch (err) {
        console.log("Error finding items for order:", err);
        e.next();
        return;
    }

    let totalAmount = 0;
    items.forEach((itemRecord) => {
        const price = parseFloat(itemRecord.get("price")) || 0;
        const quantity = parseFloat(itemRecord.get("quantity")) || 0;
        totalAmount += (price * quantity);
    });

    orderRecord.set("total_price", totalAmount);

    try {
        $app.save(orderRecord);
    } catch (err) {
        console.log("Error saving order total_price:", err);
    }

    e.next();
}

onRecordAfterCreateSuccess(syncParentOrderTotalPrice, "order_items");
onRecordAfterUpdateSuccess(syncParentOrderTotalPrice, "order_items");
onRecordAfterDeleteSuccess(syncParentOrderTotalPrice, "order_items");

/// <reference path="../pb_data/types.d.ts" />

routerAdd("POST", "/api/orders/create-full", (c) => {
    try {
        const raw = readerToString(c.request.body);
        if (raw) {
            const data = JSON.parse(raw);
            try {
                const orderItemsCollection = $app.findCollectionByNameOrId("order_items");
                const createdItemIds = [];
                let totalAmount = 0;

                if (data.items && Array.isArray(data.items)) {
                    for (const itemData of data.items) {
                        const itemRecord = new Record(orderItemsCollection);

                        // Fetch the product to get the salePrice
                        const productRecord = $app.findRecordById("products", itemData.product);
                        const rawSalePrice = parseFloat(productRecord.get("salePrice"));
                        const rawPrice = parseFloat(productRecord.get("price"));
                        const salePrice = (rawSalePrice > 0) ? rawSalePrice : (rawPrice || 0);
                        const quantity = itemData.quantity;

                        itemRecord.set("product", itemData.product);
                        itemRecord.set("color", itemData.color);
                        itemRecord.set("size", itemData.size);
                        itemRecord.set("quantity", quantity);
                        itemRecord.set("price", salePrice); // Set price from product's salePrice

                        $app.save(itemRecord);
                        createdItemIds.push(itemRecord.id);

                        totalAmount += (salePrice * quantity);
                    }
                }

                const paymentCollection = $app.findCollectionByNameOrId("payments");
                const paymentRecord = new Record(paymentCollection);

                const paymentData = data.payment;

                // Set fields based on the provided JSON structure
                paymentRecord.set("method", paymentData.method);
                paymentRecord.set("status", "pending");
                paymentRecord.set("amount", totalAmount);

                $app.save(paymentRecord);

                const ordersCollection = $app.findCollectionByNameOrId("orders");
                const orderRecord = new Record(ordersCollection);

                orderRecord.set("full_name", data.full_name);
                orderRecord.set("status", "placed");
                orderRecord.set("shipping_address", data.shipping_address);
                orderRecord.set("city", data.city);
                orderRecord.set("phone_number", data.phone_number);
                orderRecord.set("gift", data.gift);
                orderRecord.set("gift_message", data.gift_message);
                orderRecord.set("payment", paymentRecord.id);
                orderRecord.set("items", createdItemIds);
                orderRecord.set("total_price", totalAmount);

                // Assuming user is authenticated and we want to link the order to them
                // If not, you might need to handle guest orders differently or pass user_id in body
                const authRecord = c.get("authRecord");
                if (authRecord) {
                    orderRecord.set("user", authRecord.id);
                }

                $app.save(orderRecord);

                // Link each item back to its order so total_price can later be
                // recalculated by looking up this one order directly, instead of
                // scanning every order to find which one owns a given item.
                createdItemIds.forEach((itemId) => {
                    try {
                        const itemRecord = $app.findRecordById("order_items", itemId);
                        itemRecord.set("order", orderRecord.id);
                        $app.save(itemRecord);
                    } catch (err) {
                        console.log("Error linking order item to order:", err);
                    }
                });

                return c.json(200, {
                    message: "Order created successfully",
                    order_id: orderRecord.id,
                    payment_id: paymentRecord.id,
                    order_item_ids: createdItemIds
                });
            } catch (dbErr) {
                return c.json(500, { error: "Failed to create payment record", details: dbErr.message });
            }
        } else {
            return c.json(400, { error: "Empty request body" });
        }
    } catch (err) {
        console.log("Error parsing data:", err);
    }
});

// change the total price when order items are created or updated
function calculateOrderTotalPrice(e) {
    const record = e.record;
    const items = record.get("items");
    let totalAmount = 0;

    if (items && Array.isArray(items)) {
        items.forEach((item) => {
            try {
                const itemRecord = $app.findRecordById("order_items", item);
                if (itemRecord) {
                    const price = parseFloat(itemRecord.get("price")) || 0;
                    const quantity = parseFloat(itemRecord.get("quantity")) || 0;
                    totalAmount += (price * quantity);
                }
            } catch (err) {
                console.log("Error finding order item:", err);
            }
        });
    }

    record.set("total_price", totalAmount);
    e.next();
}

// Same as calculateOrderTotalPrice, but for order creation it also links each
// item back to the newly created order (order_items.order), so a later direct
// edit of that item (e.g. quantity change) can recalculate this order's
// total_price via the indexed order_items.order lookup instead of scanning
// every order.
//
// The link can only happen *after* e.next(), because that's what actually
// persists the order - before that the order id doesn't exist as a row yet,
// so saving an order_item with a relation to it would fail validation. The
// total_price read has to happen *before* e.next() so the create response
// reflects the correct value. Each item is only fetched once (into
// itemRecords) and reused for both steps instead of being looked up twice.
function calculateOrderTotalPriceAndLinkItems(e) {
    const record = e.record;
    const items = record.get("items");
    const itemRecords = [];
    let totalAmount = 0;

    if (items && Array.isArray(items)) {
        items.forEach((item) => {
            try {
                const itemRecord = $app.findRecordById("order_items", item);
                if (itemRecord) {
                    itemRecords.push(itemRecord);
                    const price = parseFloat(itemRecord.get("price")) || 0;
                    const quantity = parseFloat(itemRecord.get("quantity")) || 0;
                    totalAmount += (price * quantity);
                }
            } catch (err) {
                console.log("Error finding order item:", err);
            }
        });
    }

    record.set("total_price", totalAmount);
    e.next();

    itemRecords.forEach((itemRecord) => {
        try {
            itemRecord.set("order", record.id);
            $app.save(itemRecord);
        } catch (err) {
            console.log("Error linking order item to order:", err);
        }
    });
}

onRecordCreateRequest(calculateOrderTotalPriceAndLinkItems, "orders");
onRecordUpdateRequest(calculateOrderTotalPrice, "orders");

/// <reference path="../pb_data/types.d.ts" />

// "Vous aimerez aussi" — product page recommendations.
//
// Reads from the `product_recommendations` table (rebuilt daily, see
// refresh.pb.js), which scores every published product pair as:
//   score = 0.60 * co_purchase_rate   (orders with both / orders with this product)
//         + 0.20 * same_category
//         + 0.15 * shared_tags (jaccard)
//         + 0.05 * price_similarity
//
// GET /recommendations/{productId} -> top 4 recommended products.
routerAdd("GET", "/recommendations/{productId}", (e) => {
  const productId = e.request.pathValue("productId")
  if (!productId) {
    return e.error(400, "productId is required", null)
  }

  let rows
  try {
    rows = $app.findRecordsByFilter(
      "product_recommendations",
      "product = {:productId}",
      "-score",
      6,
      0,
      { productId: productId }
    )
  } catch (err) {
    return e.error(404, "product not found or has no recommendations", null)
  }

  $app.expandRecords(rows, ["recommended_product"], null)

  const recommendations = rows.map((row) => {
    const product = row.expandedOne("recommended_product")
    return {
      id: product ? product.id : row.get("recommended_product"),
      name: product ? product.get("name") : null,
      price: product ? product.get("price") : null,
      salePrice: product ? product.get("salePrice") : null,
      images: product ? product.get("images") : [],
      category: product ? product.get("category") : null,
      score: row.get("score"),
      orders_together: row.get("orders_together"),
      co_purchase_rate: row.get("co_purchase_rate"),
      same_category: row.get("same_category"),
      shared_tags: row.get("shared_tags"),
      price_similarity: row.get("price_similarity"),
    }
  })

  return e.json(200, { product: productId, recommendations: recommendations })
})

/// <reference path="../pb_data/types.d.ts" />

onRecordCreate((e) => {
    const record = e.record;

    // Auto-generate product ID with prefix 669557 + 9-digit incrementing number (total 15 chars)
    const prefix = "669557";

    try {
        // Find all products with this prefix and get the highest ID
        const products = $app.findRecordsByFilter(
            "products",
            `id ~ '${prefix}'`,
            "-id", // Order by id descending
            100 // Limit to last 100 products with this prefix
        );

        let maxNumber = 0;

        if (products && products.length > 0) {
            // Find the maximum numeric part from all IDs
            products.forEach((product) => {
                const productId = product.getString("id");
                if (productId && productId.startsWith(prefix)) {
                    const numericPart = parseInt(productId.substring(prefix.length));
                    if (!isNaN(numericPart) && numericPart > maxNumber) {
                        maxNumber = numericPart;
                    }
                }
            });
        }

        // Increment and create new ID
        const nextNumber = maxNumber + 1;
        const newId = prefix + String(nextNumber).padStart(9, '0');
        record.set("id", newId);
        // check if status is empty then set it to draft
        if (!record.get("status")) {
            record.set("status", "draft");
        }
    } catch (error) {
        // If query fails, try to generate a random-ish ID to avoid conflicts
        const timestamp = Date.now() % 1000000000; // Last 9 digits of timestamp
        const newId = prefix + String(timestamp).padStart(9, '0');
        record.set("id", newId);
    }

    const price = parseFloat(record.get("price")) || 0;
    const salePrice = parseFloat(record.get("salePrice")) || 0;

    // Check if price is smaller than sale price
    if (salePrice > 0 && price < salePrice) {
        throw new Error("Price cannot be smaller than Sale Price.");
    }

    // Calculate discount as percentage and round to nearest integer
    if (price > 0 && salePrice > 0 && price > salePrice) {
        const discountPercentage = ((price - salePrice) / price) * 100;
        record.set("discount", Math.round(discountPercentage));
    } else {
        record.set("discount", 0);
    }

    e.next();
}, "products");

onRecordUpdate((e) => {
    const record = e.record;
    const price = parseFloat(record.get("price")) || 0;
    const salePrice = parseFloat(record.get("salePrice")) || 0;

    // Check if price is smaller than sale price
    if (salePrice > 0 && price < salePrice) {
        throw new Error("Price cannot be smaller than Sale Price.");
    }

    // Calculate discount as percentage and round to nearest integer
    if (price > 0 && salePrice > 0 && price > salePrice) {
        const discountPercentage = ((price - salePrice) / price) * 100;
        record.set("discount", Math.round(discountPercentage));
    } else {
        record.set("discount", 0);
    }

    e.next();
}, "products");

/// <reference path="../pb_data/types.d.ts" />

// Nightly (4am) rebuild of the three precomputed recommendation tables:
//   - product_recommendations: pairwise product->product association/similarity
//   - product_popularity:      how often each product is bought, normalized 0..1
//   - category_affinity:       how often two DIFFERENT categories are bought
//                               together in the same order (cross-sell signal)
//
// All three are plain DELETE+INSERT rebuilds — cheap at this order volume,
// and simplest to reason about (no incremental-update bugs).
//
// NOTE: callbacks passed to routerAdd/cronAdd run standalone in this
// PocketBase JSVM and can't see sibling top-level const/function
// declarations (confirmed by testing), so the refresh SQL is duplicated
// between the cron job and the manual-trigger route below rather than
// shared via a helper.

// Rebuilds all three tables daily at 4am.
cronAdd("refreshRecommendationTables", "0 4 * * *", () => {
  $app.db().newQuery("DELETE FROM product_recommendations").execute()
  $app.db().newQuery(`
    WITH order_product_totals AS (
        SELECT oi.product AS product, COUNT(DISTINCT o.id) AS total_orders
        FROM order_items oi
        JOIN orders o
            ON EXISTS (SELECT 1 FROM json_each(o.items) WHERE value = oi.id)
        WHERE o.status IN ('confirmed', 'shipped', 'delivered')
          AND oi.product IS NOT NULL AND oi.product != ''
        GROUP BY oi.product
    ),
    pair_counts AS (
        SELECT
            oi1.product AS product,
            oi2.product AS recommended_product,
            COUNT(DISTINCT o.id) AS orders_together
        FROM orders o
        JOIN order_items oi1
            ON EXISTS (SELECT 1 FROM json_each(o.items) WHERE value = oi1.id)
        JOIN order_items oi2
            ON EXISTS (SELECT 1 FROM json_each(o.items) WHERE value = oi2.id)
        WHERE o.status IN ('confirmed', 'shipped', 'delivered')
          AND oi1.product IS NOT NULL AND oi1.product != ''
          AND oi2.product IS NOT NULL AND oi2.product != ''
          AND oi1.product != oi2.product
        GROUP BY oi1.product, oi2.product
    ),
    tag_overlap AS (
        SELECT
            p1.id AS product,
            p2.id AS recommended_product,
            COUNT(DISTINCT t.value) AS shared_count
        FROM products p1
        JOIN products p2 ON p1.id != p2.id
        JOIN json_each(p1.tags) t
            ON EXISTS (SELECT 1 FROM json_each(p2.tags) t2 WHERE t2.value = t.value)
        GROUP BY p1.id, p2.id
    )
    INSERT INTO product_recommendations (
        product, recommended_product, orders_together,
        co_purchase_rate, same_category, shared_tags, price_similarity, score
    )
    SELECT
        p1.id AS product,
        p2.id AS recommended_product,
        COALESCE(pc.orders_together, 0) AS orders_together,
        COALESCE(COALESCE(pc.orders_together, 0) * 1.0 / NULLIF(opt.total_orders, 0), 0) AS co_purchase_rate,
        (CASE WHEN p1.category = p2.category AND p1.category != '' THEN 1 ELSE 0 END) AS same_category,
        COALESCE(
            CASE
                WHEN (json_array_length(p1.tags) + json_array_length(p2.tags) - COALESCE(tago.shared_count, 0)) <= 0 THEN 0
                ELSE COALESCE(tago.shared_count, 0) * 1.0
                     / (json_array_length(p1.tags) + json_array_length(p2.tags) - COALESCE(tago.shared_count, 0))
            END,
            0
        ) AS shared_tags,
        COALESCE(1 - ABS(p1.price - p2.price) / NULLIF(MAX(p1.price, p2.price), 0), 0) AS price_similarity,
        (
            0.60 * COALESCE(COALESCE(pc.orders_together, 0) * 1.0 / NULLIF(opt.total_orders, 0), 0)
            + 0.20 * (CASE WHEN p1.category = p2.category AND p1.category != '' THEN 1 ELSE 0 END)
            + 0.15 * COALESCE(
                CASE
                    WHEN (json_array_length(p1.tags) + json_array_length(p2.tags) - COALESCE(tago.shared_count, 0)) <= 0 THEN 0
                    ELSE COALESCE(tago.shared_count, 0) * 1.0
                         / (json_array_length(p1.tags) + json_array_length(p2.tags) - COALESCE(tago.shared_count, 0))
                END,
                0
              )
            + 0.05 * COALESCE(1 - ABS(p1.price - p2.price) / NULLIF(MAX(p1.price, p2.price), 0), 0)
        ) AS score
    FROM products p1
    JOIN products p2 ON p1.id != p2.id
    LEFT JOIN pair_counts pc ON pc.product = p1.id AND pc.recommended_product = p2.id
    LEFT JOIN order_product_totals opt ON opt.product = p1.id
    LEFT JOIN tag_overlap tago ON tago.product = p1.id AND tago.recommended_product = p2.id
    WHERE p1.status = 'published' AND p2.status = 'published';
  `).execute()

  $app.db().newQuery("DELETE FROM product_popularity").execute()
  $app.db().newQuery(`
    WITH totals AS (
        SELECT oi.product AS product, COUNT(DISTINCT o.id) AS total_orders
        FROM order_items oi
        JOIN orders o
            ON EXISTS (SELECT 1 FROM json_each(o.items) WHERE value = oi.id)
        WHERE o.status IN ('confirmed', 'shipped', 'delivered')
          AND oi.product IS NOT NULL AND oi.product != ''
        GROUP BY oi.product
    ),
    maxtotal AS (
        SELECT MAX(total_orders) AS m FROM totals
    )
    INSERT INTO product_popularity (product, orders_count, popularity)
    SELECT
        p.id AS product,
        COALESCE(t.total_orders, 0) AS orders_count,
        COALESCE(COALESCE(t.total_orders, 0) * 1.0 / NULLIF((SELECT m FROM maxtotal), 0), 0) AS popularity
    FROM products p
    LEFT JOIN totals t ON t.product = p.id
    WHERE p.status = 'published';
  `).execute()

  $app.db().newQuery("DELETE FROM category_affinity").execute()
  $app.db().newQuery(`
    WITH category_orders AS (
        SELECT DISTINCT o.id AS order_id, p.category AS category
        FROM orders o
        JOIN order_items oi
            ON EXISTS (SELECT 1 FROM json_each(o.items) WHERE value = oi.id)
        JOIN products p ON p.id = oi.product
        WHERE o.status IN ('confirmed', 'shipped', 'delivered')
          AND p.category IS NOT NULL AND p.category != ''
    ),
    category_totals AS (
        SELECT category, COUNT(DISTINCT order_id) AS total_orders
        FROM category_orders
        GROUP BY category
    ),
    category_pairs AS (
        SELECT
            co1.category AS category_a,
            co2.category AS category_b,
            COUNT(DISTINCT co1.order_id) AS orders_together
        FROM category_orders co1
        JOIN category_orders co2
            ON co1.order_id = co2.order_id AND co1.category != co2.category
        GROUP BY co1.category, co2.category
    )
    INSERT INTO category_affinity (category_a, category_b, orders_together, confidence)
    SELECT
        cp.category_a,
        cp.category_b,
        cp.orders_together,
        COALESCE(cp.orders_together * 1.0 / NULLIF(ct.total_orders, 0), 0) AS confidence
    FROM category_pairs cp
    JOIN category_totals ct ON ct.category = cp.category_a;
  `).execute()

  $app.logger().info("recommendation tables refreshed (product_recommendations, product_popularity, category_affinity)")
})

// Manual trigger for testing/ops, e.g. `curl -X POST .../recommendations/refresh`
// with a superuser Authorization token. Identical body to the cron job above.
routerAdd("POST", "/recommendations/refresh", (e) => {
  $app.db().newQuery("DELETE FROM product_recommendations").execute()
  $app.db().newQuery(`
    WITH order_product_totals AS (
        SELECT oi.product AS product, COUNT(DISTINCT o.id) AS total_orders
        FROM order_items oi
        JOIN orders o
            ON EXISTS (SELECT 1 FROM json_each(o.items) WHERE value = oi.id)
        WHERE o.status IN ('confirmed', 'shipped', 'delivered')
          AND oi.product IS NOT NULL AND oi.product != ''
        GROUP BY oi.product
    ),
    pair_counts AS (
        SELECT
            oi1.product AS product,
            oi2.product AS recommended_product,
            COUNT(DISTINCT o.id) AS orders_together
        FROM orders o
        JOIN order_items oi1
            ON EXISTS (SELECT 1 FROM json_each(o.items) WHERE value = oi1.id)
        JOIN order_items oi2
            ON EXISTS (SELECT 1 FROM json_each(o.items) WHERE value = oi2.id)
        WHERE o.status IN ('confirmed', 'shipped', 'delivered')
          AND oi1.product IS NOT NULL AND oi1.product != ''
          AND oi2.product IS NOT NULL AND oi2.product != ''
          AND oi1.product != oi2.product
        GROUP BY oi1.product, oi2.product
    ),
    tag_overlap AS (
        SELECT
            p1.id AS product,
            p2.id AS recommended_product,
            COUNT(DISTINCT t.value) AS shared_count
        FROM products p1
        JOIN products p2 ON p1.id != p2.id
        JOIN json_each(p1.tags) t
            ON EXISTS (SELECT 1 FROM json_each(p2.tags) t2 WHERE t2.value = t.value)
        GROUP BY p1.id, p2.id
    )
    INSERT INTO product_recommendations (
        product, recommended_product, orders_together,
        co_purchase_rate, same_category, shared_tags, price_similarity, score
    )
    SELECT
        p1.id AS product,
        p2.id AS recommended_product,
        COALESCE(pc.orders_together, 0) AS orders_together,
        COALESCE(COALESCE(pc.orders_together, 0) * 1.0 / NULLIF(opt.total_orders, 0), 0) AS co_purchase_rate,
        (CASE WHEN p1.category = p2.category AND p1.category != '' THEN 1 ELSE 0 END) AS same_category,
        COALESCE(
            CASE
                WHEN (json_array_length(p1.tags) + json_array_length(p2.tags) - COALESCE(tago.shared_count, 0)) <= 0 THEN 0
                ELSE COALESCE(tago.shared_count, 0) * 1.0
                     / (json_array_length(p1.tags) + json_array_length(p2.tags) - COALESCE(tago.shared_count, 0))
            END,
            0
        ) AS shared_tags,
        COALESCE(1 - ABS(p1.price - p2.price) / NULLIF(MAX(p1.price, p2.price), 0), 0) AS price_similarity,
        (
            0.60 * COALESCE(COALESCE(pc.orders_together, 0) * 1.0 / NULLIF(opt.total_orders, 0), 0)
            + 0.20 * (CASE WHEN p1.category = p2.category AND p1.category != '' THEN 1 ELSE 0 END)
            + 0.15 * COALESCE(
                CASE
                    WHEN (json_array_length(p1.tags) + json_array_length(p2.tags) - COALESCE(tago.shared_count, 0)) <= 0 THEN 0
                    ELSE COALESCE(tago.shared_count, 0) * 1.0
                         / (json_array_length(p1.tags) + json_array_length(p2.tags) - COALESCE(tago.shared_count, 0))
                END,
                0
              )
            + 0.05 * COALESCE(1 - ABS(p1.price - p2.price) / NULLIF(MAX(p1.price, p2.price), 0), 0)
        ) AS score
    FROM products p1
    JOIN products p2 ON p1.id != p2.id
    LEFT JOIN pair_counts pc ON pc.product = p1.id AND pc.recommended_product = p2.id
    LEFT JOIN order_product_totals opt ON opt.product = p1.id
    LEFT JOIN tag_overlap tago ON tago.product = p1.id AND tago.recommended_product = p2.id
    WHERE p1.status = 'published' AND p2.status = 'published';
  `).execute()

  $app.db().newQuery("DELETE FROM product_popularity").execute()
  $app.db().newQuery(`
    WITH totals AS (
        SELECT oi.product AS product, COUNT(DISTINCT o.id) AS total_orders
        FROM order_items oi
        JOIN orders o
            ON EXISTS (SELECT 1 FROM json_each(o.items) WHERE value = oi.id)
        WHERE o.status IN ('confirmed', 'shipped', 'delivered')
          AND oi.product IS NOT NULL AND oi.product != ''
        GROUP BY oi.product
    ),
    maxtotal AS (
        SELECT MAX(total_orders) AS m FROM totals
    )
    INSERT INTO product_popularity (product, orders_count, popularity)
    SELECT
        p.id AS product,
        COALESCE(t.total_orders, 0) AS orders_count,
        COALESCE(COALESCE(t.total_orders, 0) * 1.0 / NULLIF((SELECT m FROM maxtotal), 0), 0) AS popularity
    FROM products p
    LEFT JOIN totals t ON t.product = p.id
    WHERE p.status = 'published';
  `).execute()

  $app.db().newQuery("DELETE FROM category_affinity").execute()
  $app.db().newQuery(`
    WITH category_orders AS (
        SELECT DISTINCT o.id AS order_id, p.category AS category
        FROM orders o
        JOIN order_items oi
            ON EXISTS (SELECT 1 FROM json_each(o.items) WHERE value = oi.id)
        JOIN products p ON p.id = oi.product
        WHERE o.status IN ('confirmed', 'shipped', 'delivered')
          AND p.category IS NOT NULL AND p.category != ''
    ),
    category_totals AS (
        SELECT category, COUNT(DISTINCT order_id) AS total_orders
        FROM category_orders
        GROUP BY category
    ),
    category_pairs AS (
        SELECT
            co1.category AS category_a,
            co2.category AS category_b,
            COUNT(DISTINCT co1.order_id) AS orders_together
        FROM category_orders co1
        JOIN category_orders co2
            ON co1.order_id = co2.order_id AND co1.category != co2.category
        GROUP BY co1.category, co2.category
    )
    INSERT INTO category_affinity (category_a, category_b, orders_together, confidence)
    SELECT
        cp.category_a,
        cp.category_b,
        cp.orders_together,
        COALESCE(cp.orders_together * 1.0 / NULLIF(ct.total_orders, 0), 0) AS confidence
    FROM category_pairs cp
    JOIN category_totals ct ON ct.category = cp.category_a;
  `).execute()

  $app.logger().info("recommendation tables refreshed (product_recommendations, product_popularity, category_affinity)")
  return e.json(200, { status: "ok" })
}, $apis.requireSuperuserAuth())