Kugelsicheres
Cloud
Backbone.

// lib/mongodb.ts
import { MongoClient, type MongoClientOptions } from "mongodb";

if (!process.env.MONGODB_URI) {
  throw new Error("MONGODB_URI nicht gesetzt (.env.local pruefen)");
}

const uri = process.env.MONGODB_URI; // mongodb+srv://...
const options: MongoClientOptions = {
  retryWrites: true,
  w: "majority",
  appName: "palmer-digital-backbone",
  maxPoolSize: 10,           // pro Serverless-Instanz
  minPoolSize: 1,
  serverSelectionTimeoutMS: 5000,
};

// Global cachen — verhindert "Zombie Connections"
// bei Hot Reload (dev) und Lambda-Reuse (prod).
declare global {
  // eslint-disable-next-line no-var
  var _mongoClientPromise: Promise<MongoClient> | undefined;
}

const clientPromise: Promise<MongoClient> =
  global._mongoClientPromise ??
  (global._mongoClientPromise = new MongoClient(uri, options).connect());

export default clientPromise;

// Verwendung in Route Handler:
// const client = await clientPromise;
// const db = client.db("shop");

// Monatsumsatz je Kategorie inkl. Avg. Warenkorb
db.orders.aggregate([
  // 1. Nur bezahlte Bestellungen im letzten Monat
  { $match: {
      status: "paid",
      createdAt: {
        $gte: ISODate("2026-03-01T00:00:00Z"),
        $lt:  ISODate("2026-04-01T00:00:00Z"),
      },
  }},

  // 2. Line-Items entfalten
  { $unwind: "$items" },

  // 3. Produkt-Kategorie joinen
  { $lookup: {
      from: "products",
      localField: "items.productId",
      foreignField: "_id",
      as: "product",
  }},
  { $unwind: "$product" },

  // 4. Gruppierung nach Kategorie
  { $group: {
      _id: "$product.category",
      revenue: { $sum: { $multiply: ["$items.qty", "$items.price"] } },
      orders:  { $addToSet: "$_id" },
      avgBasket: { $avg: "$total" },
  }},

  // 5. Projektion fuer Dashboard
  { $project: {
      _id: 0,
      category:  "$_id",
      revenue:   { $round: ["$revenue", 2] },
      orderCnt:  { $size: "$orders" },
      avgBasket: { $round: ["$avgBasket", 2] },
  }},

  // 6. Top-Kategorien zuerst
  { $sort: { revenue: -1 } },
], { allowDiskUse: true });

// collection "orders" mit Schema-Guardrails
db.runCommand({
  collMod: "orders",
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["customerEmail", "total", "status", "createdAt"],
      additionalProperties: true,
      properties: {
        customerEmail: {
          bsonType: "string",
          pattern: "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$",
          description: "RFC 5322-konforme E-Mail (DSGVO-Pflichtfeld)",
        },
        total: {
          bsonType: "decimal",
          minimum: 0,
          description: "Bestellwert in EUR, nie negativ",
        },
        status: {
          enum: ["pending", "paid", "shipped", "cancelled", "refunded"],
          description: "Nur diese Status sind zulaessig",
        },
        items: {
          bsonType: "array",
          minItems: 1,
          items: {
            bsonType: "object",
            required: ["productId", "qty", "price"],
            properties: {
              productId: { bsonType: "objectId" },
              qty:       { bsonType: "int", minimum: 1 },
              price:     { bsonType: "decimal", minimum: 0 },
            },
          },
        },
        createdAt: { bsonType: "date" },
      },
    },
  },
  validationLevel:  "strict",
  validationAction: "error", // REJECT invalid writes
});