project.controller.js

const mongoose = require("mongoose");
const net = require("net");
const dns = require("dns").promises;
const { Project } = require("@urbackend/common");
const { Developer } = require("@urbackend/common");
const { Log } = require("@urbackend/common");
const { getStorage } = require("@urbackend/common");
const { randomUUID } = require("crypto");
const {
  createProjectSchema,
  createCollectionSchema,
  updateExternalConfigSchema,
  updateAuthProvidersSchema,
  sanitizeObjectId,
  sanitizeNonEmptyString,
} = require("@urbackend/common");
const { generateApiKey, hashApiKey } = require("@urbackend/common");
const { z } = require("zod");
const { encrypt, decrypt } = require("@urbackend/common");
const { URL } = require("url");
const path = require("path");
const axios = require("axios");
const { getConnection } = require("@urbackend/common");
const { getCompiledModel } = require("@urbackend/common");
const { QueryEngine } = require("@urbackend/common");
const { storageRegistry } = require("@urbackend/common");
const { AppError, webhookQueue, enqueueCollectionCleanup, syncCollectionCleanup } = require("@urbackend/common");
const { resolveEffectivePlan } = require("@urbackend/common");
const {
  deleteProjectByApiKeyCache,
  setProjectById,
  getProjectById,
  deleteProjectById,
} = require("@urbackend/common");
const { isProjectStorageExternal, getBucket } = require("@urbackend/common");
const { getPresignedUploadUrl } = require("@urbackend/common");
const { verifyUploadedFile } = require("@urbackend/common");
const { getPublicIp } = require("@urbackend/common");
const { clearCompiledModel } = require("@urbackend/common");
const { createUniqueIndexes, ApiAnalytics, MailLog } = require("@urbackend/common");
const { getProjectAccessQuery, getProjectRole, Invitation } = require("@urbackend/common");
const { emitEvent } = require('../utils/emitEvent');
const MAX_FILE_SIZE = 10 * 1024 * 1024;
const SAFETY_MAX_BYTES = 100 * 1024 * 1024;
const CONFIRM_UPLOAD_SIZE_TOLERANCE_BYTES = 64;

const validateUsersSchema = (schema) => {
  if (!Array.isArray(schema)) return false;

  const sanitizedSchema = sanitizeSchemaFields(schema);

  const hasEmail = sanitizedSchema.find(
    (f) =>
      normalizeFieldKey(f.key).toLowerCase() === "email" &&
      normalizeFieldType(f.type) === "string" &&
      isRequiredField(f.required),
  );

  const hasPassword = sanitizedSchema.find(
    (f) =>
      normalizeFieldKey(f.key).toLowerCase() === "password" &&
      normalizeFieldType(f.type) === "string" &&
      isRequiredField(f.required),
  );

  return !!(hasEmail && hasPassword);
};

const normalizeFieldKey = (key) =>
  String(key || "")
    .replace(/\uFEFF/g, "")
    .trim();

const normalizeFieldType = (type) =>
  String(type || "")
    .trim()
    .toLowerCase();

const isRequiredField = (required) =>
  required === true ||
  required === 1 ||
  String(required).trim().toLowerCase() === "true" ||
  String(required).trim() === "1";

const toPlainObject = (value) => {
  if (!value || typeof value !== "object") return value;
  if (typeof value.toObject === "function") {
    return value.toObject({ depopulate: true });
  }
  if (value._doc && typeof value._doc === "object") {
    return { ...value._doc };
  }
  return value;
};

const sanitizeSchemaFields = (schema = []) => {
  if (!Array.isArray(schema)) return [];
  return schema
    .map((rawField) => {
      const field = toPlainObject(rawField);
      if (!field || typeof field !== "object") return null;

      const normalizedKey = normalizeFieldKey(field.key);
      if (!normalizedKey) return null;

      const next = { ...field, key: normalizedKey };
      if (field.default !== undefined) {
        next.default = field.default;
      }

      if (Array.isArray(field.fields)) {
        next.fields = sanitizeSchemaFields(field.fields);
      }

      if (field.items && typeof field.items === "object") {
        next.items = { ...field.items };
        if (Array.isArray(field.items.fields)) {
          next.items.fields = sanitizeSchemaFields(field.items.fields);
        }
      }

      return next;
    })
    .filter(Boolean);
};

const getDefaultRlsForCollection = (collectionName, schema = []) => {
  const normalizedName = String(collectionName || "").toLowerCase();
  const keys = sanitizeSchemaFields(schema).map((f) => f.key);

  let ownerField = "userId";
  if (normalizedName === "users") {
    ownerField = "_id";
  } else if (keys.includes("userId")) {
    ownerField = "userId";
  } else if (keys.includes("ownerId")) {
    ownerField = "ownerId";
  }

  return {
    enabled: false,
    mode: "public-read",
    ownerField,
    requireAuthForWrite: true,
  };
};

const SOCIAL_PROVIDER_KEYS = ["github", "google"];

const sanitizeAuthProviders = (authProviders = {}) => {
  return SOCIAL_PROVIDER_KEYS.reduce((acc, provider) => {
    const config = authProviders?.[provider] || {};
    const cs = config.clientSecret;
    const hasClientSecret =
      cs != null &&
      typeof cs === "object" &&
      Object.keys(cs).length > 0;
    acc[provider] = {
      enabled: !!config.enabled,
      clientId: config.clientId || "",
      hasClientSecret,
    };
    return acc;
  }, {});
};

const sanitizeProjectResponse = (projectObj) => {
  delete projectObj.publishableKey;
  delete projectObj.secretKey;
  delete projectObj.jwtSecret;
  const resendConfig = projectObj.resendApiKey;
  projectObj.hasResendApiKey =
    resendConfig != null &&
    typeof resendConfig === "object" &&
    Object.keys(resendConfig).length > 0;
  delete projectObj.resendApiKey;

  projectObj.authProviders = sanitizeAuthProviders(projectObj.authProviders);

  if (projectObj.collections && Array.isArray(projectObj.collections)) {
    projectObj.collections = projectObj.collections.map((col) => {
      if (col.name === "users" && col.model) {
        return {
          ...col,
          model: col.model.filter((m) => m.key !== "password"),
          rls: col.rls || getDefaultRlsForCollection(col.name, col.model),
        };
      }

      return {
        ...col,
        rls: col.rls || getDefaultRlsForCollection(col.name, col.model),
      };
    });
  }

  return projectObj;
};

const parsePositiveSize = (size) => {
  const numericSize = Number(size);
  if (!Number.isFinite(numericSize) || numericSize <= 0) {
    return null;
  }
  return numericSize;
};

const normalizeProjectPath = (projectId, inputPath) => {
  if (typeof inputPath !== "string") {
    return null;
  }

  let decodedPath = inputPath;
  try {
    decodedPath = decodeURIComponent(inputPath);
  } catch {
    return null;
  }

  const normalizedPath = path.posix.normalize(decodedPath).replace(/^\/+/, "");
  const segments = normalizedPath.split("/").filter(Boolean);

  if (segments.length < 2) {
    return null;
  }

  if (segments[0] !== String(projectId)) {
    return null;
  }

  if (segments.some((segment) => segment === "." || segment === "..")) {
    return null;
  }

  return normalizedPath;
};

const bestEffortDeleteUploadedObject = async (project, filePath) => {
  try {
    const supabase = await getStorage(project);
    const bucket = getBucket(project);
    await supabase.storage.from(bucket).remove([filePath]);
  } catch {
    // ignore cleanup failures; the primary response should still be returned
  }
};

module.exports.createProject = async (req, res) => {
  const executeOperation = async (session) => {
    const { name, description, siteUrl } = createProjectSchema.parse(req.body);

    if (req.projectLimit !== undefined) {
      const queryOpts = session ? { session } : {};
      const currentCount = await Project.countDocuments(
        { owner: req.user._id },
        queryOpts,
      );

      if (currentCount >= req.projectLimit) {
        const error = new Error(`Project limit reached (${req.projectLimit}). Please upgrade your plan to create more projects.`);
        error.status = 403;
        throw error;
      }
    }

    const rawPublishableKey = generateApiKey("pk_live_");
    const hashedPublishableKey = hashApiKey(rawPublishableKey);

    const rawSecretKey = generateApiKey("sk_live_");
    const hashedSecretKey = hashApiKey(rawSecretKey);

    const rawJwtSecret = generateApiKey("jwt_");

    const newProject = new Project({
      name,
      description,
      owner: req.user._id,
      publishableKey: hashedPublishableKey,
      secretKey: hashedSecretKey,
      jwtSecret: rawJwtSecret,
      siteUrl: siteUrl || "",
    });
    
    const saveOpts = session ? { session } : {};
    await newProject.save(saveOpts);

    const projectObj = newProject.toObject();
    projectObj.publishableKey = rawPublishableKey;
    projectObj.secretKey = rawSecretKey;
    delete projectObj.jwtSecret;
    projectObj.authProviders = sanitizeAuthProviders(projectObj.authProviders);

    return { projectObj, newProject };
  };

  let session = null;
  try {
    session = await mongoose.startSession();
    session.startTransaction();
    
    const { projectObj, newProject } = await executeOperation(session);
    
    await session.commitTransaction();
    session.endSession();
    
    emitEvent(req.user._id, 'project_created', { projectName: projectObj.name }, newProject._id);
    return res.status(201).json(projectObj);
  } catch (err) {
    if (session) {
      try { await session.abortTransaction(); } catch (e) {}
      session.endSession();
    }
    
    if (err.message && (err.message.includes("Transaction numbers are only allowed") || err.message.includes("buffering timed out"))) {
      try {
        const { projectObj, newProject } = await executeOperation(null);
        emitEvent(req.user._id, 'project_created', { projectName: projectObj.name }, newProject._id);
        return res.status(201).json(projectObj);
      } catch (retryErr) {
        if (retryErr instanceof z.ZodError) return res.status(400).json({ error: retryErr.issues });
        return res.status(retryErr.status || 500).json({ error: retryErr.message });
      }
    }

    if (err instanceof z.ZodError) return res.status(400).json({ error: err.issues });
    return res.status(err.status || 500).json({ error: err.message });
  }
};

module.exports.getAllProject = async (req, res) => {
  try {
    const projects = await Project.find(getProjectAccessQuery(req.user._id))
      .select("name description databaseUsed databaseLimit storageUsed storageLimit updatedAt isAuthEnabled collections owner members")
      .lean();

    const projectIds = projects.map(p => p._id);
    const recentLogs = await Log.aggregate([
  { $match: { projectId: { $in: projectIds } } },
  { $sort: { timestamp: -1 } },
  {
    $group: {
      _id: "$projectId",
      logs: { $topN: { n: 100, sortBy: { timestamp: -1 }, output: { status: "$status" } } }
    }
  },
  {
    $project: {
      errorCount: {
        $size: {
          $filter: { input: "$logs", as: "l", cond: { $gte: ["$$l.status", 400] } }
        }
      },
      successCount: {
        $size: {
          $filter: { input: "$logs", as: "l", cond: { $lt: ["$$l.status", 400] } }
        }
      }
    }
  }
]);

    const logsMap = recentLogs.reduce((acc, log) => {
      acc[log._id.toString()] = log;
      return acc;
    }, {});

    const enrichedProjects = projects.map(project => {
      const stats = logsMap[project._id.toString()];
      let health = 'healthy';
      
      if (stats) {
        const total = stats.errorCount + stats.successCount;
        const errorRate = stats.errorCount / total;
        if (errorRate > 0.2) health = 'warning';
      }

      return {
        ...project,
        health,
        collectionsCount: project.collections?.length || 0,
        metrics: {
          database: { used: project.databaseUsed, limit: project.databaseLimit },
          storage: { used: project.storageUsed, limit: project.storageLimit }
        }
      };
    });

    res.status(200).json(enrichedProjects);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};

module.exports.getSingleProject = async (req, res) => {
  try {
    let projectObj = await getProjectById(req.params.projectId);

    if (!projectObj) {
      const project = await Project.findOne({
        _id: req.params.projectId,
        ...getProjectAccessQuery(req.user._id),
      }).select(
        "-publishableKey -secretKey -jwtSecret " +
          "+authProviders.github.clientSecret.encrypted " +
          "+authProviders.github.clientSecret.iv " +
          "+authProviders.github.clientSecret.tag " +
          "+authProviders.google.clientSecret.encrypted " +
          "+authProviders.google.clientSecret.iv " +
          "+authProviders.google.clientSecret.tag " +
          "+resendApiKey.encrypted " +
          "+resendApiKey.iv " +
          "+resendApiKey.tag",
      );
      if (!project) return res.status(404).json({ success: false, data: {}, message: "Project not found." });
      projectObj = project.toObject();
      await setProjectById(req.params.projectId, projectObj);
    }

    if (!getProjectRole(projectObj, req.user._id)) {
      throw new AppError(403, "Access denied.");
    }

    res.json(sanitizeProjectResponse(projectObj));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};

module.exports.regenerateApiKey = async (req, res) => {
  try {
    const { keyType } = req.body;

    if (keyType !== "publishable" && keyType !== "secret") {
      return res
        .status(400)
        .json({ error: "Invalid keyType. Must be 'publishable' or 'secret'." });
    }

    const prefix = keyType === "publishable" ? "pk_live_" : "sk_live_";
    const newApiKey = generateApiKey(prefix);
    const hashed = hashApiKey(newApiKey);

    const oldApiProj = await Project.findOne({
      _id: req.params.projectId,
      ...getProjectAccessQuery(req.user._id),
    }).select("publishableKey secretKey");
    if (!oldApiProj)
      return res.status(404).json({ error: "Project not found." });

    await deleteProjectByApiKeyCache(oldApiProj.publishableKey);
    await deleteProjectByApiKeyCache(oldApiProj.secretKey);

    const updateField =
      keyType === "publishable"
        ? { publishableKey: hashed }
        : { secretKey: hashed };

    const project = await Project.findOneAndUpdate(
      { _id: req.params.projectId, ...getProjectAccessQuery(req.user._id) },
      { $set: updateField },
      { new: true },
    );
    if (!project) return res.status(404).json({ error: "Project not found." });

    const projectObj = project.toObject();
    delete projectObj.publishableKey;
    delete projectObj.secretKey;
    delete projectObj.jwtSecret;
    res.json({ apiKey: newApiKey, keyType, project: projectObj });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};

const isNamespaceNotFoundError = (err) => {
  return err && (err.code === 26 || /ns not found/i.test(err.message));
};

const dropCollectionIfExists = async (connection, collectionName) => {
  try {
    await connection.db.dropCollection(collectionName);
  } catch (err) {
    if (!isNamespaceNotFoundError(err)) {
      throw err;
    }
  }
};

/**
 * Convert a dotted-decimal IPv4 address string to a 32-bit unsigned integer.
 * @param {string} ip - IPv4 address in dotted-decimal notation (e.g., '192.168.1.1')
 * @returns {number} 32-bit unsigned integer representation of the IPv4 address
 */
function ipv4ToInt(ip) {
  return ip.split(".").reduce((acc, octet) => (acc << 8) + parseInt(octet, 10), 0) >>> 0;
}

/**
 * Check if an IPv4 address falls within any restricted or reserved IP range.
 * Blocks loopback (127.0.0.0/8), RFC-1918 private ranges (10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16),
 * link-local and cloud metadata (169.254.0.0/16), and unspecified (0.0.0.0/8) addresses to prevent SSRF attacks.
 * @param {string} ip - IPv4 address to validate
 * @returns {boolean} True if the IP is restricted, false otherwise
 */
function isRestrictedIPv4(ip) {
  const n = ipv4ToInt(ip);
  // Loopback 127.0.0.0/8
  if (n >= ipv4ToInt("127.0.0.0") && n <= ipv4ToInt("127.255.255.255")) return true;
  // RFC-1918: 10.0.0.0/8
  if (n >= ipv4ToInt("10.0.0.0") && n <= ipv4ToInt("10.255.255.255")) return true;
  // RFC-1918: 172.16.0.0/12
  if (n >= ipv4ToInt("172.16.0.0") && n <= ipv4ToInt("172.31.255.255")) return true;
  // RFC-1918: 192.168.0.0/16
  if (n >= ipv4ToInt("192.168.0.0") && n <= ipv4ToInt("192.168.255.255")) return true;
  // Link-local / cloud instance metadata (AWS, GCP, Azure): 169.254.0.0/16
  if (n >= ipv4ToInt("169.254.0.0") && n <= ipv4ToInt("169.254.255.255")) return true;
  // Unspecified: 0.0.0.0/8
  if (n >= ipv4ToInt("0.0.0.0") && n <= ipv4ToInt("0.255.255.255")) return true;
  return false;
}

/**
 * Check if an IPv6 address falls within any restricted or reserved range.
 * Blocks loopback (::1), unspecified (::), link-local (fe80::/10), IPv6 Unique Local Addresses (fc00::/7),
 * and IPv4-mapped IPv6 addresses that resolve to restricted IPv4 ranges to prevent SSRF attacks.
 * @param {string} ip - IPv6 address to validate
 * @returns {boolean} True if the IP is restricted, false otherwise
 */
function isRestrictedIPv6(ip) {
  const expanded = ip.replace(/^\[|\]$/g, "").toLowerCase();
  // IPv6 loopback ::1
  if (expanded === "::1" || expanded === "0:0:0:0:0:0:0:1") return true;
  // IPv6 unspecified ::
  if (expanded === "::" || expanded === "0:0:0:0:0:0:0:0") return true;
  // IPv4-mapped IPv6 addresses (::ffff:x.x.x.x)
  const mapped = expanded.match(/^::ffff:(\d+\.\d+\.\d+\.\d+)$/);
  if (mapped && isRestrictedIPv4(mapped[1])) return true;
  // IPv6 link-local fe80::/10 (fe80: through febf:)
  if (/^fe[89ab]/.test(expanded)) return true;
  // IPv6 ULA (Unique Local Address) fc00::/7
  if (expanded.startsWith("fc") || expanded.startsWith("fd")) return true;
  return false;
}

/**
 * Check if an IP address (either IPv4 or IPv6) is restricted or falls within a reserved range.
 * Delegates to isRestrictedIPv4() or isRestrictedIPv6() based on the IP version.
 * @param {string} ip - IP address to validate
 * @returns {boolean} True if the IP is restricted, false otherwise
 */
function isRestrictedIP(ip) {
  if (net.isIPv4(ip)) return isRestrictedIPv4(ip);
  if (net.isIPv6(ip)) return isRestrictedIPv6(ip);
  return false;
}

/**
 * Validate a URI to ensure it does not target restricted hosts or IP ranges (SSRF prevention).
 * Performs DNS resolution on hostnames to check both A and AAAA records against restricted ranges.
 * Blocks loopback, RFC-1918 private ranges, cloud metadata endpoints, and other reserved IP ranges.
 * @async
 * @param {string} uri - The URI to validate
 * @returns {Promise<boolean>} True if the URI is safe to connect to, false if it targets a restricted host
 */
const isSafeUri = async (uri) => {
  try {
    const parsed = new URL(uri);
    const host = parsed.hostname.replace(/^\[|\]$/g, "").toLowerCase();

    // Block well-known loopback and internal hostnames
    const blockedHostnames = ["localhost", "metadata.google.internal"];
    if (blockedHostnames.includes(host)) return false;

    // Reject mongodb+srv:// URIs as they perform hidden SRV/TXT discovery
    // We cannot safely validate all targets without resolving SRV records
    if (uri.toLowerCase().includes("mongodb+srv://")) return false;

    // If the host is a bare IPv4 or IPv6 address, check all restricted ranges
    if (net.isIPv4(host) || net.isIPv6(host)) {
      return !isRestrictedIP(host);
    }

    // For hostnames, perform DNS resolution to check both A and AAAA records
    const [ipv4Result, ipv6Result] = await Promise.allSettled([
      dns.resolve4(host),
      dns.resolve6(host),
    ]);

    const resolved = [
      ...(ipv4Result.status === "fulfilled" ? ipv4Result.value : []),
      ...(ipv6Result.status === "fulfilled" ? ipv6Result.value : []),
    ];

    // If no addresses resolved, treat as unsafe
    if (resolved.length === 0) return false;

    // Check all resolved addresses for restricted ranges
    if (resolved.some((addr) => isRestrictedIP(addr))) return false;

    return true;
  } catch (e) {
    return false;
  }
};

module.exports.updateExternalConfig = async (req, res) => {
  try {
    const { projectId } = req.params;

    const validatedData = updateExternalConfigSchema.parse(req.body);
    const { dbUri, storageUrl, storageKey, storageProvider } = validatedData;

    const updateData = {};

    if (dbUri) {
      if (!(await isSafeUri(dbUri)))
        return res.status(400).json({
          success: false,
          data: {},
          message: "DB URI is pointing to a restricted host, internal network, or unsupported URI format.",
        });

      updateData["resources.db.config"] = encrypt(JSON.stringify({ dbUri }));
      updateData["resources.db.isExternal"] = true;

      console.log("Verifying connection to:", projectId);
      try {
        const tempConn = mongoose.createConnection(dbUri, {
          serverSelectionTimeoutMS: 5000,
        });
        await tempConn.asPromise();
        await tempConn.close();
      } catch (connErr) {
        console.error("Verification Connection Failed:", connErr.message);
        let errorMsg = "Could not connect to the provided MongoDB URI.";

        if (
          connErr.message.includes("Server selection timed out") ||
          connErr.message.includes("Could not connect")
        ) {
          const serverIp = await getPublicIp();
          errorMsg = `Access Denied: Please whitelist Server IP [${serverIp}] in MongoDB Atlas.`;
        }

        return res.status(400).json({ success: false, data: {}, message: errorMsg });
      }
    }

    if (storageUrl && storageKey) {
      const storageConfig = {
        storageUrl,
        storageKey,
        storageProvider: storageProvider || "supabase",
      };
      updateData["resources.storage.config"] = encrypt(
        JSON.stringify(storageConfig),
      );
      updateData["resources.storage.isExternal"] = true;
    }

    const project = await Project.findOneAndUpdate(
      { _id: projectId, ...getProjectAccessQuery(req.user._id) },
      { $set: updateData },
      { new: true },
    );

    if (!project)
      return res.status(404).json({ success: false, data: {}, message: "Project not found or access denied." });

    res.status(200).json({ success: true, data: {}, message: "External configuration updated successfully." });
  } catch (err) {
    if (err instanceof z.ZodError) {
      return res.status(400).json({ success: false, data: {}, message: "Invalid request data." });
    }

    console.error("External Config Error:", err);
    res.status(500).json({ success: false, data: {}, message: "Failed to update external configuration." });
  }
};

module.exports.deleteExternalDbConfig = async (req, res) => {
  try {
    const parsedBody = z
      .object({
        projectId: z.string(),
      })
      .parse(req.body);
    const { projectId } = parsedBody;

    const project = await Project.findOne({
      _id: { $eq: projectId },
      ...getProjectAccessQuery(req.user._id),
    });
    if (!project)
      return res
        .status(404)
        .json({ error: "Project not found or access denied." });

    project.resources.db.isExternal = false;
    project.resources.db.config = null;
    await project.save();

    res
      .status(200)
      .json({ message: "External configuration deleted successfully." });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};

module.exports.deleteExternalStorageConfig = async (req, res) => {
  try {
    const parsedBody = z
      .object({
        projectId: z.string(),
      })
      .parse(req.body);
    const { projectId } = parsedBody;

    const project = await Project.findOne({
      _id: { $eq: projectId },
      ...getProjectAccessQuery(req.user._id),
    });
    if (!project)
      return res
        .status(404)
        .json({ error: "Project not found or access denied." });

    project.resources.storage.isExternal = false;
    project.resources.storage.config = null;

    await project.save();
    await deleteProjectById(projectId);
    await setProjectById(projectId, project.toObject());

    res
      .status(200)
      .json({ message: "External configuration deleted successfully." });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};

module.exports.createCollection = async (req, res) => {
  const executeOperation = async (session) => {
    const { projectId, collectionName, schema } = createCollectionSchema.parse(req.body);

    const projectQuery = Project.findOne({
      _id: projectId,
      ...getProjectAccessQuery(req.user._id),
    });
    if (session) projectQuery.session(session);
    
    const project = await projectQuery;
    if (!project) {
      const error = new Error("Project not found");
      error.status = 404;
      throw error;
    }

    const exists = project.collections.find((c) => c.name === collectionName);
    if (exists) {
      const error = new Error("Collection already exists");
      error.status = 400;
      throw error;
    }

    if (req.collectionLimit !== undefined) {
      if (project.collections.length >= req.collectionLimit) {
        const error = new Error(`Collection limit reached (${req.collectionLimit}). Please upgrade your plan to create more collections.`);
        error.status = 403;
        throw error;
      }
    }

    if (!project.jwtSecret) {
      project.jwtSecret = generateApiKey("jwt_");
    }

    if (collectionName === "users") {
      if (!validateUsersSchema(schema)) {
        const error = new Error("The 'users' collection must have required 'email' and 'password' string fields.");
        error.status = 422;
        throw error;
      }
    }

    const compiledCollectionName = project.resources.db.isExternal
      ? collectionName
      : `${project._id}_${collectionName}`;

    const newCollectionConfig = {
      name: collectionName,
      model: schema,
      rls: getDefaultRlsForCollection(collectionName, schema),
    };

    project.collections.push(newCollectionConfig);
    
    const saveOpts = session ? { session } : {};
    await project.save(saveOpts);

    const connection = await getConnection(projectId);

    const collectionExistedBefore = await connection.db
      .listCollections({ name: compiledCollectionName }, { nameOnly: true })
      .hasNext();

    const Model = getCompiledModel(
      connection,
      newCollectionConfig,
      projectId,
      project.resources.db.isExternal,
    );

    await createUniqueIndexes(Model, newCollectionConfig.model);

    return { project, connection, compiledCollectionName, collectionExistedBefore, projectId, collectionName };
  };

  let session = null;
  try {
    session = await mongoose.startSession();
    session.startTransaction();
    
    const { project, projectId, collectionName } = await executeOperation(session);

    await session.commitTransaction();
    session.endSession();

    await deleteProjectById(projectId);
    await setProjectById(projectId, project.toObject());
    await deleteProjectByApiKeyCache(project.publishableKey);
    await deleteProjectByApiKeyCache(project.secretKey);

    const projectObj = project.toObject();
    delete projectObj.publishableKey;
    delete projectObj.secretKey;
    delete projectObj.jwtSecret;

    emitEvent(req.user._id, 'collection_created', { collectionName, isUsersCollection: collectionName === 'users' }, projectId);

    return res.status(201).json(projectObj);
  } catch (err) {
    if (session) {
      try { await session.abortTransaction(); } catch (e) {}
      session.endSession();
    }

    if (err.message && (err.message.includes("Transaction numbers are only allowed") || err.message.includes("buffering timed out"))) {
      try {
        const { project, projectId, collectionName } = await executeOperation(null);
        await deleteProjectById(projectId);
        await setProjectById(projectId, project.toObject());
        await deleteProjectByApiKeyCache(project.publishableKey);
        await deleteProjectByApiKeyCache(project.secretKey);

        const projectObj = project.toObject();
        delete projectObj.publishableKey;
        delete projectObj.secretKey;
        delete projectObj.jwtSecret;

        emitEvent(req.user._id, 'collection_created', { collectionName, isUsersCollection: collectionName === 'users' }, projectId);

        return res.status(201).json(projectObj);
      } catch (retryErr) {
        if (retryErr instanceof z.ZodError) return res.status(400).json({ error: retryErr.issues });
        return res.status(retryErr.status || 400).json({ error: retryErr.message });
      }
    }

    if (err instanceof z.ZodError) return res.status(400).json({ error: err.issues });
    return res.status(err.status || 400).json({ error: err.message });
  }
};

// GET DOC BY ID — FIXED: added limitFields(), populate(), cursor pagination, count support, structured response
module.exports.getData = async (req, res) => {
    try {
        const { projectId, collectionName } = req.params;
        const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) });
       if (!project) return res.status(404).json({ success: false, data: {}, message: "Project not found." });



        const collectionConfig = project.collections.find(c => c.name === collectionName);
        if (!collectionConfig) {
         return res.status(404).json({ success: false, data: {}, message: `Collection ${collectionName} not found.` });
        }

        const connection = await getConnection(projectId);
        const model = getCompiledModel(
            connection,
            collectionConfig,
            projectId,
            project.resources.db.isExternal,
        );

        const baseQuery = model.find();

        // Strip password from users collection
        if (collectionName === 'users') {
            baseQuery.select('-password');
        }

        // Handle ?count=true — return document count only
        if (req.query.count === 'true') {
            const countEngine = new QueryEngine(model.find(), req.query);
            const count = await countEngine.filter().query.countDocuments();
            return res.status(200).json({
                success: true,
                data: { count },
                message: "Count fetched successfully.",
            });
        }

        const features = new QueryEngine(baseQuery, req.query)
            .filter()
            .sort()
            .limitFields()   // fixes: ?fields= and ?meta=false now work
            .populate();     // fixes: ?populate= and ?expand= now work

        // Get total before paginating
        const total = await features.count();

        // Cursor-based pagination if ?cursor= is provided, otherwise offset-based
        const useCursor = !!req.query.cursor;
        if (useCursor) {
            features.cursorPaginate();
        } else {
            features.paginate();
        }

        const data = await features.query.lean();

        // Cursor: slice to limit and generate next cursor token
        let items = data;
        let nextCursor = null;
        if (useCursor) {
            const limit = Math.min(parseInt(req.query.limit, 10) || 100, 100);
            features.generateNextCursor(data, limit);
            items = data.slice(0, limit);
            nextCursor = features.nextCursor;
        }

        const responseMeta = useCursor
            ? {
                total,
                cursor: req.query.cursor || null,
                nextCursor,
                limit: Math.max(1, Math.min(parseInt(req.query.limit, 10) || 100, 100)),
              }
            : {
                total,
                page: parseInt(req.query.page, 10) || 1,
                limit: Math.max(1, Math.min(parseInt(req.query.limit, 10) || 100, 100)),
              };

        res.json({
            success: true,
            data: {
                items,
                ...responseMeta,
            },
            message: "Data fetched successfully.",
        });
    } catch (err) {
    if (err?.statusCode === 400 || err?.name === 'QueryFilterError') {
        return res.status(400).json({ success: false, data: {}, message: err.message || "Invalid query filter." });
    }
};
};
module.exports.deleteCollection = async (req, res) => {
  try {
    const { projectId, collectionName } = req.params;

    const project = await Project.findOne({
      _id: projectId,
      ...getProjectAccessQuery(req.user._id),
    });
    if (!project) {
      return res
        .status(404)
        .json({ error: "Project not found or access denied." });
    }

    const collectionIndex = project.collections.findIndex(
      (c) => c.name === collectionName,
    );
    if (collectionIndex === -1) {
      return res.status(404).json({ error: "Collection not found." });
    }

    const isExternal = project.resources?.db?.isExternal;
    const connection = await getConnection(projectId);

    const finalCollectionName = isExternal
      ? collectionName
      : `${project._id}_${collectionName}`;

    await dropCollectionIfExists(connection, finalCollectionName);
    clearCompiledModel(connection, finalCollectionName);

    project.collections.splice(collectionIndex, 1);
    await project.save();

    await deleteProjectById(projectId);
    await setProjectById(projectId, project.toObject());
    await deleteProjectByApiKeyCache(project.publishableKey);
    await deleteProjectByApiKeyCache(project.secretKey);

    return res.json({
      message: `Collection '${collectionName}' deleted successfully.`,
    });
  } catch (err) {
    console.error("Delete Collection Error:", err);
    return res.status(500).json({ error: err.message });
  }
};

module.exports.insertData = async (req, res) => {
  try {
    console.time("insert data");
    const { projectId, collectionName } = req.params;
    const project = await Project.findOne({
      _id: projectId,
      ...getProjectAccessQuery(req.user._id),
    });
    if (!project) return res.status(404).json({ error: "Project not found." });

    if (collectionName === "users") {
      return res.status(400).json({
        error:
          "Direct inserts into 'users' collection are not allowed. Please use the Auth signup or admin endpoints.",
      });
    }

    const incomingData = req.body;

    const collectionConfig = project.collections.find(
      (c) => c.name === collectionName,
    );
    if (!collectionConfig) {
    return res.status(404).json({ success: false, data: {}, message: `Collection ${collectionName} not found.` });
}

    // Prevent manual injection of soft-delete fields
    delete incomingData.isDeleted;
    delete incomingData.deletedAt;

    let docSize = 0;
    if (!project.resources.db.isExternal) {
      docSize = Buffer.byteLength(JSON.stringify(incomingData));

      const limit = project.databaseLimit || 20 * 1024 * 1024;

      if ((project.databaseUsed || 0) + docSize > limit) {
        return res
          .status(403)
          .json({ error: "Database limit exceeded. Delete some data." });
      }
    }

    const connection = await getConnection(projectId);
    const model = getCompiledModel(
      connection,
      collectionConfig,
      projectId,
      project.resources.db.isExternal,
    );

    const result = await model.create(incomingData);

    if (!project.resources.db.isExternal) {
      project.databaseUsed = (project.databaseUsed || 0) + docSize;
    }
    await project.save();

    res.json(result);
  } catch (err) {
    if (err && err.code === 11000) {
      return res.status(409).json({
        error: "Duplicate value violates unique constraint.",
        details: err.message,
      });
    }

    res.status(500).json({ error: err.message });
  }
};

/**
 * Soft-deletes a document by setting isDeleted: true and recording the deletion time.
 * @param {import('express').Request} req - Express request
 * @param {import('express').Response} res - Express response
 */
module.exports.deleteRow = async (req, res, next) => {
  try {
    const { projectId, collectionName, id } = req.params;

    if (!mongoose.isValidObjectId(id)) {
      return next(new AppError(400, "Invalid document ID format."));
    }

    const project = await Project.findOne({
      _id: projectId,
      ...getProjectAccessQuery(req.user._id),
    });
    if (!project) return next(new AppError(404, "Project not found."));

    const collectionConfig = project.collections.find(
      (c) => c.name === collectionName,
    );
    if (!collectionConfig) {
      return next(new AppError(404, "Collection not found."));
    }

    const connection = await getConnection(projectId);
    const Model = getCompiledModel(
      connection,
      collectionConfig,
      projectId,
      project.resources.db.isExternal,
    );

    const result = await Model.findOneAndUpdate(
      { _id: id, isDeleted: { $ne: true } },
      {
        $set: {
          isDeleted: true,
          deletedAt: new Date()
        }
      },
      { new: false }
    ).lean();

    if (!result) {
      return next(new AppError(404, "Document not found."));
    }

    // We don't decrement databaseUsed here because the document still occupies space.
    // It will be decremented during hard delete in the background worker.
    try {
      await enqueueCollectionCleanup(projectId, collectionName);
    } catch (err) {
      console.error("Failed to enqueue trash cleanup job", { projectId, collectionName, err });
    }

    res.json({ success: true, data: { id: result._id }, message: "Document moved to trash" });
  } catch (err) {
    console.error("Delete Error:", err);
    next(new AppError(500, "Failed to delete document"));
  }
};
/**
 * Recovers a soft-deleted document from trash.
 * @param {import('express').Request} req - Express request
 * @param {import('express').Response} res - Express response
 * @param {import('express').NextFunction} next - Error handler
 */
module.exports.recoverRow = async (req, res, next) => {
  try {
    const { projectId, collectionName, id } = req.params;

    if (!mongoose.isValidObjectId(id)) {
      return next(new AppError(400, "Invalid document ID format."));
    }

    const project = await Project.findOne({
      _id: projectId,
      ...getProjectAccessQuery(req.user._id),
    }).lean();
    if (!project) {
      return next(new AppError(404, "Project not found."));
    }

    const collectionConfig = project.collections.find(
      (c) => c.name === collectionName,
    );
    if (!collectionConfig) {
      return next(new AppError(404, "Collection not found."));
    }

    const connection = await getConnection(projectId);
    const Model = getCompiledModel(
      connection,
      collectionConfig,
      projectId,
      project.resources.db.isExternal,
    );

    const thirtyDaysAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000);

    const result = await Model.findOneAndUpdate(
      { 
        _id: id, 
        isDeleted: true,
        deletedAt: { $gte: thirtyDaysAgo }
      },
      { 
        $set: { 
          isDeleted: false, 
          deletedAt: null 
        } 
      },
      { new: true }
    ).lean();

    if (!result) {
      return next(new AppError(404, "Document not found or recovery window expired (30 days)."));
    }

    await webhookQueue.add('trigger-webhook', {
      projectId: project._id,
      event: 'document.recovered',
      collection: collectionName,
      payload: result
    }, { removeOnComplete: true });

    try {
      await syncCollectionCleanup(projectId, collectionName);
    } catch (err) {
      console.error("Failed to sync trash cleanup job after recovery", { projectId, collectionName, err });
    }

    res.json({ success: true, data: result, message: "Document recovered from trash" });
  } catch (err) {
    if (err && err.code === 11000) {
      return next(new AppError(409, "Cannot restore document: a unique field value conflicts with an existing active document."));
    }
    return next(new AppError(500, "Failed to recover document."));
  }
};

module.exports.editRow = async (req, res) => {
  try {
    const { projectId, collectionName, id } = req.params;

    const project = await Project.findOne({
      _id: projectId,
      ...getProjectAccessQuery(req.user._id),
    });
    if (!project) return res.status(404).json({ error: "Project not found." });

    const collectionConfig = project.collections.find(
      (c) => c.name === collectionName,
    );
    if (!collectionConfig) {
      return res.status(404).json({ error: "Collection not found." });
    }

    const connection = await getConnection(projectId);
    const Model = getCompiledModel(
      connection,
      collectionConfig,
      projectId,
      project.resources.db.isExternal,
    );

    if (collectionName === "users") {
      delete req.body.password;
      Object.keys(req.body).forEach((key) => {
        if (key.toLowerCase().includes("password")) delete req.body[key];
      });
    }

    const docToEdit = await Model.findOne({ _id: id, isDeleted: { $ne: true } });
    if (!docToEdit) {
      return res.status(404).json({ error: "Document not found." });
    }

    // Prevent manual injection of soft-delete fields
    delete req.body.isDeleted;
    delete req.body.deletedAt;

    const oldSize = Buffer.byteLength(JSON.stringify(docToEdit.toObject()));

    docToEdit.set(req.body);

    const newSize = Buffer.byteLength(JSON.stringify(docToEdit.toObject()));
    const sizeDiff = newSize - oldSize;

    if (!project.resources.db.isExternal) {
      const limit = project.databaseLimit || 500 * 1024 * 1024;
      const currentUsed = project.databaseUsed || 0;

      if (currentUsed + sizeDiff > limit) {
        return res.status(403).json({ error: "Database limit exceeded." });
      }
    }

    const updatedDoc = await docToEdit.save();

    if (!project.resources.db.isExternal) {
      const currentUsed = project.databaseUsed || 0;
      project.databaseUsed = Math.max(0, currentUsed + sizeDiff);
      await project.save();
    }

    const responseData = updatedDoc.toObject();
    if (collectionName === "users") {
      delete responseData.password;
    }

    res.json({
      success: true,
      message: "Document edited successfully",
      data: responseData,
    });
  } catch (err) {
    console.error("Edit Error:", err);

    if (err && err.code === 11000) {
      return res.status(409).json({
        error: "Duplicate value violates unique constraint.",
        details: err.message,
      });
    }

    res.status(500).json({ error: err.message });
  }
};

module.exports.listFiles = async (req, res) => {
  try {
    const { projectId } = req.params;

    const project = await Project.findOne({
      _id: projectId,
      ...getProjectAccessQuery(req.user._id),
    }).select(
      "+resources.storage.config.encrypted +resources.storage.config.iv +resources.storage.config.tag resources.storage.isExternal storageUsed storageLimit",
    );
    if (!project) return res.status(404).json({ error: "Project not found" });

    const supabase = await getStorage(project);
    const bucket = getBucket(project);

    const { data, error } = await supabase.storage
      .from(bucket)
      .list(`${projectId}`, {
        limit: 100,
        sortBy: { column: "created_at", order: "desc" },
      });

    if (error) throw error;

    const files = data.map((file) => {
      const { data: url } = supabase.storage
        .from(bucket)
        .getPublicUrl(`${projectId}/${file.name}`);

      return {
        ...file,
        path: `${projectId}/${file.name}`,
        publicUrl: url.publicUrl,
      };
    });

    res.json(files);
  } catch (err) {
    console.error(err);
    res.status(500).json({
      error: "Something Went Wrong",
      try: "Try checking docs or contact support - urbackend@bitbros.in",
    });
  }
};

module.exports.deleteFile = async (req, res) => {
  try {
    const { projectId } = req.params;
    const { path } = req.body;

    if (!path) return res.status(400).json({ error: "Path required" });

    const project = await Project.findOne({
      _id: projectId,
      ...getProjectAccessQuery(req.user._id),
    }).select(
      "+resources.storage.config.encrypted +resources.storage.config.iv +resources.storage.config.tag resources.storage.isExternal storageUsed storageLimit",
    );
    if (!project) return res.status(404).json({ error: "Project not found" });

    if (!path.startsWith(`${projectId}/`)) {
      return res.status(403).json({ error: "Access denied" });
    }

    const supabase = await getStorage(project);
    const bucket = getBucket(project);
    const external = isProjectStorageExternal(project);

    let fileSize = 0;

    if (!external) {
      const { data } = await supabase.storage.from(bucket).list(projectId, {
        search: path.split("/").pop(),
      });

      if (data?.length) {
        fileSize = data[0]?.metadata?.size || 0;
      }
    }

    const { error } = await supabase.storage.from(bucket).remove([path]);
    if (error) throw error;

    if (!external && fileSize > 0) {
      project.storageUsed = Math.max(0, project.storageUsed - fileSize);
      await project.save();
    }

    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};

module.exports.deleteAllFiles = async (req, res) => {
  try {
    const { projectId } = req.params;

    const project = await Project.findOne({
      _id: projectId,
      ...getProjectAccessQuery(req.user._id),
    }).select(
      "+resources.storage.config.encrypted +resources.storage.config.iv +resources.storage.config.tag resources.storage.isExternal storageUsed storageLimit",
    );
    if (!project) return res.status(404).json({ error: "Project not found" });

    const supabase = await getStorage(project);
    const bucket = getBucket(project);

    let hasMore = true;
    let deleted = 0;

    while (hasMore) {
      const { data, error } = await supabase.storage
        .from(bucket)
        .list(projectId, { limit: 100 });

      if (error) throw error;

      if (data.length === 0) {
        hasMore = false;
      } else {
        const paths = data.map((f) => `${projectId}/${f.name}`);
        await supabase.storage.from(bucket).remove(paths);
        deleted += data.length;
      }
    }

    if (!isProjectStorageExternal(project)) {
      project.storageUsed = 0;
      await project.save();
    }

    res.json({ success: true, deleted });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};

module.exports.requestUpload = async (req, res, next) => {
  try {
    const projectId = sanitizeObjectId(req.params?.projectId);
    const { filename, contentType, size } = req.body;
    const sanitizedFilename = sanitizeNonEmptyString(filename, {
      maxLength: 255,
    });
    const sanitizedContentType = sanitizeNonEmptyString(contentType, {
      maxLength: 255,
    });
    const numericSize = parsePositiveSize(size);

    if (!projectId || !sanitizedFilename || !sanitizedContentType || numericSize === null) {
      return next(
        new AppError(400, "projectId, filename, contentType, and size are required."),
      );
    }

    if (numericSize > MAX_FILE_SIZE) {
      return next(new AppError(413, "File size exceeds limit."));
    }

    const project = await Project.findOne({
      _id: projectId,
      ...getProjectAccessQuery(req.user._id),
    }).select(
      "+resources.storage.config.encrypted +resources.storage.config.iv +resources.storage.config.tag resources.storage.isExternal storageUsed storageLimit",
    );

    if (!project) return next(new AppError(404, "Project not found"));

    const external = isProjectStorageExternal(project);

    if (!external) {
      const storageLimit =
        typeof project.storageLimit === "number"
          ? project.storageLimit
          : 20 * 1024 * 1024;
      const quotaLimit =
        storageLimit === -1 ? SAFETY_MAX_BYTES : storageLimit;

      if ((project.storageUsed || 0) + numericSize > quotaLimit) {
        return next(new AppError(403, "Internal storage limit exceeded."));
      }
    }

    const safeName = sanitizedFilename.replace(/\s+/g, "_");
    const filePath = `${projectId}/${randomUUID()}_${safeName}`;

    const { signedUrl, token } = await getPresignedUploadUrl(
      project,
      filePath,
      sanitizedContentType,
      numericSize,
    );

    return res.status(200).json({
      success: true,
      data: { signedUrl, token, filePath },
      message: "Upload URL generated successfully.",
    });
  } catch (err) {
    if (err instanceof AppError) return next(err);
    return next(new AppError(500, "Could not generate upload URL"));
  }
};

module.exports.confirmUpload = async (req, res, next) => {
  try {
    const projectId = sanitizeObjectId(req.params?.projectId);
    const { filePath, size } = req.body;
    const sanitizedFilePath = sanitizeNonEmptyString(filePath, {
      maxLength: 1024,
    });
    const declaredSize = parsePositiveSize(size);

    if (!projectId || !sanitizedFilePath || declaredSize === null) {
      return next(new AppError(400, "projectId, filePath, and size are required."));
    }

    const project = await Project.findOne({
      _id: projectId,
      ...getProjectAccessQuery(req.user._id),
    }).select(
      "+resources.storage.config.encrypted +resources.storage.config.iv +resources.storage.config.tag resources.storage.isExternal storageUsed storageLimit",
    );

    if (!project) return next(new AppError(404, "Project not found"));

    const external = isProjectStorageExternal(project);
    const normalizedPath = normalizeProjectPath(projectId, sanitizedFilePath);

    if (!normalizedPath) {
      return next(new AppError(403, "Access denied."));
    }

    let actualSize;
    try {
      actualSize = await verifyUploadedFile(project, normalizedPath);
    } catch (err) {
      if (err?.message === "File not found after upload") {
        await bestEffortDeleteUploadedObject(project, normalizedPath);
        return next(
          new AppError(
            409,
            "Uploaded file is not visible yet. Please retry confirmation.",
          ),
        );
      }
      throw err;
    }

    if (!Number.isFinite(actualSize) || actualSize <= 0) {
      await bestEffortDeleteUploadedObject(project, normalizedPath);
      return next(new AppError(500, "Uploaded file size could not be determined"));
    }

    if (Math.abs(actualSize - declaredSize) > CONFIRM_UPLOAD_SIZE_TOLERANCE_BYTES) {
      await bestEffortDeleteUploadedObject(project, normalizedPath);
      return next(
        new AppError(400, "Declared file size does not match uploaded file size."),
      );
    }

    if (!external) {
      const result = await Project.updateOne(
        {
          _id: project._id,
          $or: [
            { storageLimit: -1 },
            { $expr: { $lte: [{ $add: ["$storageUsed", actualSize] }, "$storageLimit"] } },
          ],
        },
        { $inc: { storageUsed: actualSize } },
      );

      if (result.matchedCount === 0) {
        await bestEffortDeleteUploadedObject(project, normalizedPath);
        return next(new AppError(403, "Internal storage limit exceeded."));
      }
    }

    const supabase = await getStorage(project);
    const bucket = getBucket(project);
    const { data: publicUrlData, error: apiError } = supabase.storage
      .from(bucket)
      .getPublicUrl(normalizedPath);

    const publicUrl = publicUrlData?.publicUrl;
    const provider = publicUrl ? (external ? "external" : "internal") : "external";

    const responseData = {
      message: "Upload confirmed",
      path: normalizedPath,
      provider,
      url: publicUrl ?? null,
    };

    if (!publicUrl) {
      responseData.warning =
        apiError || "Upload confirmed, but a public URL is unavailable.";
    }

    return res.status(200).json({
      success: true,
      data: responseData,
      message: "Upload confirmed.",
    });
  } catch (err) {
    if (err instanceof AppError) return next(err);
    return next(new AppError(500, "Upload confirmation failed"));
  }
};

module.exports.updateProject = async (req, res) => {
  try {
    const { name, siteUrl, resendApiKey, resendFromEmail } = req.body;
    const updateFields = {};
    if (name !== undefined) {
      if (typeof name !== "string" || !name.trim()) {
        return res.status(400).json({ error: "name must be a non-empty string." });
      }
      updateFields.name = name.trim();
    }
    if (resendFromEmail !== undefined) {
      if (typeof resendFromEmail !== "string") {
        return res.status(400).json({ error: "resendFromEmail must be a string." });
      }
      const trimmedFrom = resendFromEmail.trim();
      if (trimmedFrom !== "") {
         if (trimmedFrom.length > 255) {
            return res.status(400).json({ error: "resendFromEmail is too long." });
         }
         let addressToValidate = trimmedFrom;
         const bracketMatch = trimmedFrom.match(/<([^>]+)>$/);
         if (bracketMatch) {
            addressToValidate = bracketMatch[1].trim();
         }
         const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
         if (!emailRegex.test(addressToValidate)) {
            return res.status(400).json({ error: "resendFromEmail must be a valid format (e.g., 'me@domain.com' or 'App <me@domain.com>')." });
         }
      }
      updateFields.resendFromEmail = trimmedFrom;
    }
    if (siteUrl !== undefined) {
      if (siteUrl !== "" && typeof siteUrl !== "string") {
        return res.status(400).json({ error: "siteUrl must be a string." });
      }
      if (siteUrl) {
        try {
          const parsed = new URL(siteUrl);
          if (
            parsed.protocol !== "https:" &&
            !(
              parsed.protocol === "http:" &&
              ["localhost", "127.0.0.1", "::1"].includes(parsed.hostname)
            )
          ) {
            return res.status(400).json({
              error:
                "Site URL must use HTTPS (or http://localhost for local development).",
            });
          }
        } catch {
          return res.status(400).json({ error: "Invalid Site URL format." });
        }
      }
      updateFields.siteUrl = siteUrl || "";
    }
    if (resendApiKey !== undefined) {
      const trimmedKey = typeof resendApiKey === "string" ? resendApiKey.trim() : "";
      if (!trimmedKey) {
        return res
          .status(400)
          .json({ error: "resendApiKey must be a non-empty string." });
      }
      
      if (!/^re_[A-Za-z0-9_]+$/.test(trimmedKey)) {
        return res.status(400).json({ error: "Invalid Resend API Key format." });
      }

      updateFields.resendApiKey = encrypt(trimmedKey);
    }

    const project = await Project.findOneAndUpdate(
      { _id: req.params.projectId, ...getProjectAccessQuery(req.user._id) },
      { $set: updateFields },
      {
        new: true,
        projection:
          "+resendApiKey.encrypted +resendApiKey.iv +resendApiKey.tag",
      },
    );
    if (!project) return res.status(404).json({ error: "Project not found." });

    await deleteProjectById(project._id.toString());
    await deleteProjectByApiKeyCache(project.publishableKey);
    await deleteProjectByApiKeyCache(project.secretKey);

    res.json(sanitizeProjectResponse(project.toObject()));

  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};

// -------------------- MAIL TEMPLATES (Phase 2 feature) --------------------

const { MailTemplate } = require("@urbackend/common");

const toSlug = (value) => {
  return String(value || "")
    .trim()
    .toLowerCase()
    .replace(/\s+/g, "-")
    .replace(/[^a-z0-9_-]/g, "-")
    .replace(/-+/g, "-")
    .replace(/^-|-$/g, "");
};


module.exports.listMailTemplates = async (req, res, next) => {
  try {
    const { projectId } = req.params;

    const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) }).select("+mailTemplates");

    if (!project) return res.status(404).json({ success: false, data: {}, message: "Project not found." });

    const legacy = Array.isArray(project.mailTemplates) ? project.mailTemplates : [];

    if (legacy.length > 0) {
      const existing = await MailTemplate.find({ projectId: project._id })
        .select("keyLower nameLower")
        .lean();

      const usedKeys = new Set((existing || []).map((t) => t.keyLower).filter(Boolean));
      const usedNames = new Set((existing || []).map((t) => t.nameLower).filter(Boolean));

      const migrationOps = [];

      for (const lt of legacy) {
        const baseName = String(lt.name || "").trim() || "template";

        let name = baseName;
        let nameLower = name.toLowerCase();
        let n = 1;
        while (usedNames.has(nameLower)) {
          n += 1;
          name = `${baseName} ${n}`;
          nameLower = name.toLowerCase();
        }
        usedNames.add(nameLower);

        const baseKey = toSlug(baseName) || "";
        let key = baseKey;
        let keyLower = key.toLowerCase();
        let k = 1;
        while (keyLower && usedKeys.has(keyLower)) {
          k += 1;
          key = `${baseKey}-${k}`;
          keyLower = key.toLowerCase();
        }
        if (keyLower) usedKeys.add(keyLower);

        migrationOps.push({
          updateOne: {
            filter: keyLower
              ? { projectId: project._id, keyLower }
              : { projectId: project._id, nameLower },
            update: {
              $setOnInsert: {
                projectId: project._id,
                isSystem: false,
                key,
                keyLower,
                name,
                nameLower,
                subject: String(lt.subject || ""),
                html: String(lt.html || ""),
                text: String(lt.text || ""),
                createdAt: lt.createdAt ? new Date(lt.createdAt) : new Date(),
                updatedAt: lt.updatedAt ? new Date(lt.updatedAt) : new Date(),
              },
            },
            upsert: true,
          },
        });
      }

      if (migrationOps.length > 0) {
        let migrationSafeToFinalize = false;
        try {
          await MailTemplate.bulkWrite(migrationOps, { ordered: false });
          migrationSafeToFinalize = true;
        } catch (err) {
          const writeErrors = Array.isArray(err?.writeErrors) ? err.writeErrors : [];
          const duplicateOnly =
            err?.code === 11000 ||
            (writeErrors.length > 0 && writeErrors.every((w) => w?.code === 11000));
          if (duplicateOnly) {
            migrationSafeToFinalize = true;
          } else {
            throw err;
          }
        }

        if (migrationSafeToFinalize) {
          project.mailTemplates = [];
          await project.save();
          await deleteProjectById(project._id.toString()).catch(() => {});
        }
      }
    }

    const templates = await MailTemplate.find({ projectId: project._id, isSystem: { $ne: true } })
      .sort({ updatedAt: -1 })
      .select("_id key name subject updatedAt")
      .lean();

    return res.json({
      success: true,
      data: {
        templates: templates.map((t) => ({
          id: t._id,
          key: t.key || "",
          name: t.name,
          subject: t.subject,
          updatedAt: t.updatedAt,
        })),
      },
      message: "Mail templates fetched.",
    });
  } catch (err) {
    if (err instanceof AppError) return next(err);
    return next(new AppError(500, "Internal server error"));
  }
};

module.exports.listGlobalMailTemplates = async (req, res, next) => {
  try {
    const { projectId } = req.params;

    const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) })
      .select("_id")
      .lean();

    if (!project) return res.status(404).json({ success: false, data: {}, message: "Project not found." });

    const templates = await MailTemplate.find({ projectId: null, isSystem: true })
      .sort({ keyLower: 1 })
      .select("_id key name subject updatedAt")
      .lean();

    return res.json({
      success: true,
      data: {
        templates: templates.map((t) => ({
          id: t._id,
          key: t.key || "",
          name: t.name,
          subject: t.subject,
          updatedAt: t.updatedAt,
          scope: "global",
        })),
      },
      message: "Global mail templates fetched.",
    });
  } catch (err) {
    if (err instanceof AppError) return next(err);
    return next(new AppError(500, "Internal server error"));
  }
};

module.exports.getMailTemplate = async (req, res, next) => {
  try {
    const { projectId, templateId } = req.params;
    if (!mongoose.isValidObjectId(templateId)) {
      return res.status(400).json({ success: false, data: {}, message: "Invalid template id" });
    }

    const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) })
      .select("+mailTemplates")
      .lean();

    if (!project) return res.status(404).json({ success: false, data: {}, message: "Project not found." });

    let template = await MailTemplate.findOne({
      _id: templateId,
      $or: [{ projectId: project._id }, { projectId: null }],
    })
      .select("_id key name subject html text updatedAt projectId isSystem")
      .lean();

    if (!template) {
      const legacy = Array.isArray(project.mailTemplates) ? project.mailTemplates : [];
      const lt = legacy.find((x) => String(x._id) === String(templateId));
      if (lt) {
        template = {
          _id: lt._id,
          key: "",
          name: lt.name,
          subject: lt.subject,
          html: lt.html,
          text: lt.text,
          updatedAt: lt.updatedAt,
          projectId: project._id,
          isSystem: false,
        };
      }
    }

    if (!template) return res.status(404).json({ success: false, data: {}, message: "Template not found." });

    return res.json({
      success: true,
      data: {
        template: {
          id: template._id,
          key: template.key || "",
          name: template.name,
          subject: template.subject,
          html: template.html,
          text: template.text,
          updatedAt: template.updatedAt,
          scope: template.projectId ? "project" : "global",
          isSystem: !!template.isSystem,
        },
      },
      message: "Mail template fetched.",
    });
  } catch (err) {
    if (err instanceof AppError) return next(err);
    return next(new AppError(500, "Internal server error"));
  }
};

module.exports.createMailTemplate = async (req, res, next) => {
  try {
    const { projectId } = req.params;

    const schema = z
      .object({
        key: z.string().max(100).optional(),
        name: z.string().trim().min(1).max(100),
        subject: z.string().trim().min(1).max(200),
        html: z.string().trim().optional(),
        text: z.string().trim().optional(),
      })
      .refine(
        (d) =>
          (typeof d.html === "string" && d.html.trim().length > 0) ||
          (typeof d.text === "string" && d.text.trim().length > 0),
        { message: "Provide at least one of html or text." },
      );

    const payload = schema.parse(req.body || {});

    const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) })
      .select("_id")
      .lean();

    if (!project) return res.status(404).json({ success: false, data: {}, message: "Project not found." });

    const key = (payload.key !== undefined && payload.key.trim() !== "") ? toSlug(payload.key) : toSlug(payload.name);

    const created = await MailTemplate.create({
      projectId: project._id,
      isSystem: false,
      key,
      name: payload.name.trim(),
      subject: payload.subject.trim(),
      html: typeof payload.html === "string" ? payload.html : "",
      text: typeof payload.text === "string" ? payload.text : "",
    });

    return res.status(201).json({
      success: true,
      data: {
        template: {
          id: created._id,
          key: created.key || "",
          name: created.name,
          subject: created.subject,
          updatedAt: created.updatedAt,
        },
      },
      message: "Mail template created.",
    });
  } catch (err) {
    if (err instanceof z.ZodError) {
      return res.status(400).json({ success: false, data: {}, message: err.issues?.[0]?.message || "Invalid payload." });
    }

    if (err && err.code === 11000) {
      return res.status(409).json({ success: false, data: {}, message: "Template name/key already exists." });
    }

    return next(new AppError(500, "Internal server error"));
  }
};

module.exports.updateMailTemplate = async (req, res, next) => {
  try {
    const { projectId, templateId } = req.params;
    if (!mongoose.isValidObjectId(templateId)) {
      return res.status(400).json({ success: false, data: {}, message: "Invalid template id" });
    }

    const schema = z
      .object({
        key: z.string().max(100).optional(),
        name: z.string().trim().min(1).max(100).optional(),
        subject: z.string().trim().min(1).max(200).optional(),
        html: z.string().trim().optional(),
        text: z.string().trim().optional(),
      })
      .refine((d) => Object.keys(d).length > 0, { message: "At least one field must be provided." });

    const payload = schema.parse(req.body || {});

    const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) })
      .select("_id")
      .lean();

    if (!project) return res.status(404).json({ success: false, data: {}, message: "Project not found." });

    const update = {};
    if (payload.key !== undefined) {
      update.key = toSlug(payload.key);
      update.keyLower = String(update.key || "").toLowerCase();
    }
    if (payload.name !== undefined) {
      update.name = payload.name.trim();
      update.nameLower = String(update.name || "").toLowerCase();
    }
    if (payload.subject !== undefined) update.subject = payload.subject.trim();
    if (payload.html !== undefined) update.html = payload.html;
    if (payload.text !== undefined) update.text = payload.text;

    const templateFilter = { _id: templateId, projectId: project._id, isSystem: { $ne: true } };
    const existing = await MailTemplate.findOne(templateFilter).lean();

    if (!existing) return res.status(404).json({ success: false, data: {}, message: "Template not found." });

    const nextHtml = payload.html !== undefined ? update.html : existing.html;
    const nextText = payload.text !== undefined ? update.text : existing.text;
    const hasBody =
      (typeof nextHtml === "string" && nextHtml.trim().length > 0) ||
      (typeof nextText === "string" && nextText.trim().length > 0);
    if (!hasBody) {
      return res.status(400).json({ success: false, data: {}, message: "Template must contain at least one of html or text." });
    }

    const updated = await MailTemplate.findOneAndUpdate(
      templateFilter,
      { $set: update },
      { new: true },
    ).lean();

    if (!updated) return res.status(404).json({ success: false, data: {}, message: "Template not found." });

    return res.json({
      success: true,
      data: {
        template: {
          id: updated._id,
          key: updated.key || "",
          name: updated.name,
          subject: updated.subject,
          updatedAt: updated.updatedAt,
        },
      },
      message: "Mail template updated.",
    });
  } catch (err) {
    if (err instanceof z.ZodError) {
      return res.status(400).json({ success: false, data: {}, message: err.issues?.[0]?.message || "Invalid payload." });
    }

    if (err && err.code === 11000) {
      return res.status(409).json({ success: false, data: {}, message: "Template name/key already exists." });
    }

    return next(new AppError(500, "Internal server error"));
  }
};

module.exports.deleteMailTemplate = async (req, res, next) => {
  try {
    const { projectId, templateId } = req.params;
    if (!mongoose.isValidObjectId(templateId)) {
      return res.status(400).json({ success: false, data: {}, message: "Invalid template id" });
    }

    const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) })
      .select("_id")
      .lean();

    if (!project) return res.status(404).json({ success: false, data: {}, message: "Project not found." });

    const deleted = await MailTemplate.findOneAndDelete({
      _id: templateId,
      projectId: project._id,
      isSystem: { $ne: true },
    }).lean();

    if (!deleted) return res.status(404).json({ success: false, data: {}, message: "Template not found." });

    return res.json({ success: true, data: {}, message: "Mail template deleted." });
  } catch (err) {
    if (err instanceof AppError) return next(err);
    return next(new AppError(500, "Internal server error"));
  }
};

// -------------------------------------------------------------------------

module.exports.updateAllowedDomains = async (req, res) => {
  try {
    const { domains } = req.body;
    if (
      !Array.isArray(domains) ||
      !domains.every((d) => typeof d === "string")
    ) {
      return res
        .status(400)
        .json({ error: "domains must be an array of strings." });
    }

    const cleanedDomains = domains
      .map((d) => d.trim())
      .filter((d) => d.length > 0);

    const project = await Project.findOneAndUpdate(
      { _id: req.params.projectId, ...getProjectAccessQuery(req.user._id) },
      { $set: { allowedDomains: cleanedDomains } },
      { new: true },
    );

    if (!project)
      return res
        .status(404)
        .json({ error: "Project not found or access denied." });
    await deleteProjectById(project._id.toString());
    await setProjectById(project._id.toString(), project.toObject());
    await deleteProjectByApiKeyCache(project.publishableKey);
    await deleteProjectByApiKeyCache(project.secretKey);

    res.json({
      message: "Allowed domains updated",
      allowedDomains: project.allowedDomains,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};

module.exports.deleteProject = async (req, res) => {
  try {
    const projectId = req.params.projectId;

    const project = await Project.findOne({
      _id: projectId,
      owner: req.user._id,
    }).select(
      "+resources.storage.config.encrypted " +
        "+resources.storage.config.iv " +
        "+resources.storage.config.tag " +
        "publishableKey secretKey",
    );

    if (!project) {
      return res
        .status(404)
        .json({ error: "Project not found or access denied." });
    }

    if (!project.resources.db.isExternal) {
      for (const col of project.collections) {
        const collectionName = `${project._id}_${col.name}`;
        try {
          await mongoose.connection.db.dropCollection(collectionName);
        } catch (e) {}
      }

      try {
        await mongoose.connection.db.dropCollection(`${project._id}_users`);
      } catch (e) {}
    }

    if (!isProjectStorageExternal(project)) {
      const supabase = await getStorage(project);
      const bucket = getBucket(project);

      let hasMoreFiles = true;

      while (hasMoreFiles) {
        const { data: files, error } = await supabase.storage
          .from(bucket)
          .list(projectId, { limit: 100 });

        if (error) throw error;

        if (files && files.length > 0) {
          const paths = files.map((f) => `${projectId}/${f.name}`);
          await supabase.storage.from(bucket).remove(paths);
        } else {
          hasMoreFiles = false;
        }
      }
    }

    await MailTemplate.deleteMany({ projectId: project._id });
    await Project.deleteOne({ _id: projectId });

    await deleteProjectByApiKeyCache(project.publishableKey);
    await deleteProjectByApiKeyCache(project.secretKey);
    await deleteProjectById(project._id.toString());

    storageRegistry.delete(projectId.toString());

    res.json({
      message: "Project and all associated resources deleted successfully",
    });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: err.message });
  }
};

module.exports.analytics = async (req, res, next) => {
  try {
    const { projectId } = req.params;
    const { range = 'last24h' } = req.query;

    const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) });
    if (!project) {
      return res.status(404).json({
        success: false,
        data: {},
        message: "Project not found or access denied.",
      });
    }

    const VALID_RANGES = new Set(['last1h', 'last24h', 'last7d', 'last30d', 'allTime']);
    if (!VALID_RANGES.has(range)) {
      return res.status(400).json({
        success: false,
        data: {},
        message: `Invalid range. Allowed values: ${[...VALID_RANGES].join(', ')}.`,
      });
    }

    let startDate = new Date();
    let format = "%Y-%m-%d";
    let groupStep = "day";

    switch (range) {
      case 'last1h': 
        startDate.setHours(startDate.getHours() - 1); 
        format = "%H:%M";
        groupStep = "minute";
        break;
      case 'last24h': 
        startDate.setDate(startDate.getDate() - 1); 
        format = "%H:00";
        groupStep = "hour";
        break;
      case 'last7d': 
        startDate.setDate(startDate.getDate() - 7); 
        break;
      case 'last30d': 
        startDate.setDate(startDate.getDate() - 30); 
        break;
      case 'allTime': 
        startDate = new Date(0); 
        break;
    }

    const match = {
      projectId: new mongoose.Types.ObjectId(projectId),
      timestamp: { $gte: startDate },
    };

    const timeSeriesData = await ApiAnalytics.aggregate([
      { $match: match },
      {
        $group: {
          _id: { $dateToString: { format: format, date: "$timestamp" } },
          success: { $sum: { $cond: [{ $lt: ["$statusCode", 400] }, 1, 0] } },
          errors: { $sum: { $cond: [{ $gte: ["$statusCode", 400] }, 1, 0] } },
          avgLatency: { $avg: "$responseTimeMs" }
        }
      },
      { $sort: { _id: 1 } }
    ]);

    const [breakdownStats, topEndpoints] = await Promise.all([
      ApiAnalytics.aggregate([
        { $match: match },
        {
          $facet: {
            statusCodes: [
              { $group: { _id: { $concat: [{ $substr: ["$statusCode", 0, 1] }, "xx"] }, count: { $sum: 1 } } }
            ],
            methods: [
              { $group: { _id: "$method", count: { $sum: 1 } } }
            ],
            global: [
              {
                $group: {
                  _id: null,
                  avgResponseTimeMs: { $avg: "$responseTimeMs" },
                  totalRequests: { $sum: 1 },
                  errors: { $sum: { $cond: [{ $gte: ["$statusCode", 400] }, 1, 0] } }
                }
              }
            ]
          }
        }
      ]),
      ApiAnalytics.aggregate([
        { $match: match },
        {
          $group: {
            _id: { endpoint: "$endpoint", method: "$method" },
            count: { $sum: 1 },
            avgLatency: { $avg: "$responseTimeMs" },
            errors: { $sum: { $cond: [{ $gte: ["$statusCode", 400] }, 1, 0] } }
          }
        },
        { $sort: { count: -1 } },
        { $limit: 10 }
      ])
    ]);

    const stats = breakdownStats[0].global[0] || { avgResponseTimeMs: 0, totalRequests: 0, errors: 0 };
    const errorRate = stats.totalRequests > 0 ? (stats.errors / stats.totalRequests) * 100 : 0;

    let p95 = 0;
    if (stats.totalRequests > 0) {
      const p95Results = await ApiAnalytics.find(match)
        .sort({ responseTimeMs: 1 })
        .skip(Math.max(0, Math.floor(stats.totalRequests * 0.95) - 1))
        .limit(1)
        .select('responseTimeMs')
        .lean();
      p95 = p95Results[0]?.responseTimeMs || 0;
    }

    const rawLogs = await ApiAnalytics.find(match).sort({ timestamp: -1 }).limit(50).lean();
    const logs = rawLogs.map(l => ({ ...l, path: l.endpoint, status: l.statusCode }));

    const allTimeRequests = await Log.countDocuments({ projectId });

    return res.json({
      success: true,
      data: {
        storage: { used: project.storageUsed, limit: project.storageLimit },
        database: { used: project.databaseUsed, limit: project.databaseLimit },
        totalRequests: allTimeRequests,
        rangeStats: {
          totalRequests: stats.totalRequests,
          avgResponseTimeMs: stats.avgResponseTimeMs,
          p95ResponseTimeMs: p95,
          errorRate: errorRate
        },
        timeSeries: timeSeriesData,
        topEndpoints: topEndpoints.map(e => ({
          path: e._id.endpoint,
          method: e._id.method,
          count: e.count,
          avgLatency: e.avgLatency,
          errorRate: (e.errors / e.count) * 100
        })),
        distributions: {
          statusCodes: breakdownStats[0].statusCodes,
          methods: breakdownStats[0].methods
        },
        logs,
        range
      },
      message: 'Analytics fetched successfully.',
    });
  } catch (err) {
    console.error('Analytics error:', err);
    return next(new AppError(500, 'Failed to fetch analytics.'));
  }
};

module.exports.toggleAuth = async (req, res) => {
  try {
    const { projectId } = req.params;
    const { enable } = req.body;

    const project = await Project.findOne({
      _id: projectId,
      ...getProjectAccessQuery(req.user._id),
    }).select(
      "+authProviders.github.clientSecret.encrypted " +
      "+authProviders.github.clientSecret.iv " +
      "+authProviders.github.clientSecret.tag " +
      "+authProviders.google.clientSecret.encrypted " +
      "+authProviders.google.clientSecret.iv " +
      "+authProviders.google.clientSecret.tag"
    );
    if (!project) return res.status(404).json({ error: "Project not found" });

    if (enable) {
      const usersCol = project.collections.find((c) => c.name === "users");
      if (!usersCol) {
        return res.status(422).json({
          error: "Users Collection Missing",
          message:
            "The 'users' collection must be created and configured with required 'email' and 'password' fields before enabling Authentication.",
        });
      }

      if (!validateUsersSchema(usersCol.model)) {
        return res.status(422).json({
          error: "Invalid Users Schema",
          message:
            "The 'users' collection must have required 'email' and 'password' string fields. Please fix the schema before enabling Auth.",
        });
      }
    }

    project.isAuthEnabled = !!enable;
    await project.save();

    await deleteProjectById(projectId);
    await deleteProjectByApiKeyCache(project.publishableKey);
    await deleteProjectByApiKeyCache(project.secretKey);

    const projectObj = sanitizeProjectResponse(project.toObject());

    res.json({
      message: `Authentication ${project.isAuthEnabled ? "enabled" : "disabled"} successfully`,
      isAuthEnabled: project.isAuthEnabled,
      project: projectObj,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};

module.exports.updateAuthProviders = async (req, res) => {
  try {
    const { projectId } = req.params;
    const parsed = updateAuthProvidersSchema.parse(req.body || {});

    const project = await Project.findOne({
      _id: projectId,
      ...getProjectAccessQuery(req.user._id),
    }).select(
      "+authProviders.github.clientSecret.encrypted " +
        "+authProviders.github.clientSecret.iv " +
        "+authProviders.github.clientSecret.tag " +
        "+authProviders.google.clientSecret.encrypted " +
        "+authProviders.google.clientSecret.iv " +
        "+authProviders.google.clientSecret.tag",
    );

    if (!project) return res.status(404).json({ error: "Project not found" });

    project.authProviders = project.authProviders || {};

    for (const provider of SOCIAL_PROVIDER_KEYS) {
      const incoming = parsed[provider];
      if (!incoming) continue;

      const current = project.authProviders?.[provider] || {};
      const nextEnabled =
        typeof incoming.enabled === "boolean" ? incoming.enabled : !!current.enabled;
      const nextClientId =
        incoming.clientId !== undefined ? incoming.clientId : (current.clientId || "");
      const nextClientSecret =
        incoming.clientSecret !== undefined
          ? encrypt(incoming.clientSecret)
          : (current.clientSecret || null);

      if (nextEnabled && (!nextClientId || !nextClientSecret)) {
        return res.status(422).json({
          error: "Incomplete provider config",
          message: `${provider} requires clientId and clientSecret before it can be enabled.`,
        });
      }

      if (nextEnabled && !project.siteUrl?.trim()) {
        return res.status(422).json({
          error: "siteUrl required",
          message: `You must configure a Site URL in Project Settings before enabling ${provider} OAuth. The Site URL is used to redirect users after authentication.`,
        });
      }

      project.authProviders[provider] = {
        enabled: nextEnabled,
        clientId: nextClientId,
        clientSecret: nextClientSecret,
        redirectUri: current.redirectUri || "",
      };
    }

    await project.save();

    await deleteProjectById(projectId);
    await deleteProjectByApiKeyCache(project.publishableKey);
    await deleteProjectByApiKeyCache(project.secretKey);

    return res.json({
      message: "Auth providers updated",
      authProviders: sanitizeAuthProviders(project.toObject().authProviders),
    });
  } catch (err) {
    if (err instanceof z.ZodError) {
      return res.status(400).json({ error: err.issues });
    }
    return res.status(500).json({ error: err.message });
  }
};

module.exports.updateCollectionRls = async (req, res) => {
    try {
        const { projectId, collectionName } = req.params;
        const { enabled, mode, ownerField, requireAuthForWrite } = req.body || {};

        const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) });
        if (!project) return res.status(404).json({ error: "Project not found" });

        const collection = project.collections.find(c => c.name === collectionName);
        if (!collection) return res.status(404).json({ error: "Collection not found" });

        const validMode = mode || collection?.rls?.mode || 'public-read';
        const allowedModes = new Set(['public-read', 'private', 'owner-write-only']);
        if (!allowedModes.has(validMode)) {
            return res.status(400).json({ error: "Unsupported RLS mode. Allowed: public-read, private, owner-write-only (legacy)." });
        }

        const modelKeys = (collection.model || [])
            .map(f => String(f?.key || '').trim())
            .filter(Boolean);
        const modelKeySet = new Set(modelKeys);
        const modelKeyLowerMap = new Map(modelKeys.map(k => [k.toLowerCase(), k]));

        const requestedOwnerRaw = String(ownerField ?? collection?.rls?.ownerField ?? 'userId').trim();
        const requestedOwnerLower = requestedOwnerRaw.toLowerCase();
        const canonicalOwnerField = modelKeySet.has(requestedOwnerRaw)
            ? requestedOwnerRaw
            : modelKeyLowerMap.get(requestedOwnerLower);
        const nextOwnerField = requestedOwnerRaw === '_id' ? '_id' : (canonicalOwnerField || requestedOwnerRaw);

        if (nextOwnerField !== '_id' && !modelKeySet.has(nextOwnerField)) {
            return res.status(400).json({
                error: "Invalid owner field",
                message: `ownerField '${nextOwnerField}' not found in collection schema`
            });
        }

        if (nextOwnerField === '_id' && collection.name !== 'users') {
            return res.status(400).json({
                error: "Invalid owner field",
                message: "ownerField '_id' is only allowed for the 'users' collection"
            });
        }

        collection.rls = {
            enabled: typeof enabled === 'boolean' ? enabled : !!collection?.rls?.enabled,
            mode: validMode,
            ownerField: nextOwnerField,
            requireAuthForWrite: typeof requireAuthForWrite === 'boolean'
                ? requireAuthForWrite
                : (collection?.rls?.requireAuthForWrite ?? true)
        };

        await project.save();

        await deleteProjectById(projectId);
        await deleteProjectByApiKeyCache(project.publishableKey);
        await deleteProjectByApiKeyCache(project.secretKey);

        res.json({
            message: "Collection RLS updated",
            collection: {
                name: collection.name,
                rls: collection.rls
            }
        });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
};

// -------------------- EXPANDED MAIL API PLATFORM PROXIES --------------------

const getResolvedResendKey = (project) => {
    if (project?.resendApiKey?.encrypted) {
        try {
            const key = decrypt(project.resendApiKey);
            if (key) return { key, isByok: true };
        } catch (e) {
            console.error("Failed to decrypt project resend key", e);
        }
    }
    return { key: process.env.RESEND_API_KEY_2 || process.env.RESEND_API_KEY, isByok: false };
};

module.exports.getMailLogs = async (req, res) => {
    try {
        const { projectId } = req.params;
        const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) });
        if (!project) return res.status(404).json({ success: false, message: "Project not found" });

        const logs = await MailLog.find({ projectId: project._id })
            .sort({ sentAt: -1 })
            .limit(50)
            .lean();

        return res.json({ success: true, data: { logs } });
    } catch (err) {
        return res.status(500).json({ success: false, message: err.message });
    }
};

module.exports.getResendLiveStatus = async (req, res) => {
    try {
        const { projectId, resendId } = req.params;
        if (!/^[A-Za-z0-9_-]{1,128}$/.test(resendId)) {
            return res.status(400).json({ success: false, message: "Invalid resendId format." });
        }

        const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) }).select("+resendApiKey.encrypted +resendApiKey.iv +resendApiKey.tag");
        if (!project) return res.status(404).json({ success: false, message: "Project not found" });

        const logEntry = await MailLog.findOne({ resendEmailId: resendId, projectId: project._id }).lean();
        if (!logEntry) {
            return res.status(404).json({ success: false, message: "Mail log entry not found for this project." });
        }

        const { key } = getResolvedResendKey(project);
        if (!key) return res.status(400).json({ success: false, message: "Resend API Key is missing." });

        const safeResendId = encodeURIComponent(resendId);
        const response = await axios.get(`https://api.resend.com/emails/${safeResendId}`, {
            headers: { Authorization: `Bearer ${key}` }
        });

        return res.json({ success: true, data: response.data });
    } catch (err) {
        const { resendId } = req.params;
        if (err.response?.status === 404) {
            return res.status(404).json({
                success: false,
                data: {
                    id: resendId,
                    last_event: "unknown",
                    providerStatus: "not_found",
                },
                message: "Email status not found on Resend for this id."
            });
        }
        const errorMsg = err.response?.data?.message || err.message;
        return res.status(err.response?.status || 500).json({ success: false, message: errorMsg });
    }
};

module.exports.manageAudiences = async (req, res) => {
    try {
        const { projectId } = req.params;
        const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) }).select("+resendApiKey.encrypted +resendApiKey.iv +resendApiKey.tag");
        if (!project) return res.status(404).json({ success: false, message: "Project not found" });

        const { key, isByok } = getResolvedResendKey(project);
        if (!isByok || !key) {
            return res.status(403).json({ success: false, message: "Audiences require a custom Resend API Key (BYOK) configured in Project Settings." });
        }

        if (req.method === "GET") {
            const response = await axios.get("https://api.resend.com/audiences", {
                headers: { Authorization: `Bearer ${key}` }
            });
            return res.json({ success: true, data: response.data });
        }

        if (req.method === "POST") {
            const { name } = req.body;
            if (!name) return res.status(400).json({ success: false, message: "Audience name required" });

            const response = await axios.post("https://api.resend.com/audiences", { name }, {
                headers: { Authorization: `Bearer ${key}` }
            });
            return res.json({ success: true, data: response.data });
        }

        return res.status(405).json({ success: false, message: "Method not allowed" });
    } catch (err) {
        const errorMsg = err.response?.data?.message || err.message;
        return res.status(err.response?.status || 500).json({ success: false, message: errorMsg });
    }
};

module.exports.deleteAudience = async (req, res) => {
    try {
        const { projectId, audienceId } = req.params;
        if (!/^[A-Za-z0-9_-]+$/.test(audienceId)) {
            return res.status(400).json({ success: false, message: "Invalid audienceId format" });
        }
        const safeAudienceId = encodeURIComponent(audienceId);
        const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) }).select("+resendApiKey.encrypted +resendApiKey.iv +resendApiKey.tag");
        if (!project) return res.status(404).json({ success: false, message: "Project not found" });

        const { key, isByok } = getResolvedResendKey(project);
        if (!isByok || !key) {
            return res.status(403).json({ success: false, message: "Audiences require a custom Resend API Key (BYOK)." });
        }

        await axios.delete(`https://api.resend.com/audiences/${safeAudienceId}`, {
            headers: { Authorization: `Bearer ${key}` }
        });

        return res.json({ success: true, message: "Audience deleted successfully" });
    } catch (err) {
        const errorMsg = err.response?.data?.message || err.message;
        return res.status(err.response?.status || 500).json({ success: false, message: errorMsg });
    }
};

module.exports.manageContacts = async (req, res) => {
    try {
        const { projectId, audienceId } = req.params;
        if (!/^[A-Za-z0-9_-]+$/.test(audienceId)) {
            return res.status(400).json({ success: false, message: "Invalid audienceId format" });
        }
        const safeAudienceId = encodeURIComponent(audienceId);
        const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) }).select("+resendApiKey.encrypted +resendApiKey.iv +resendApiKey.tag");
        if (!project) return res.status(404).json({ success: false, message: "Project not found" });

        const { key, isByok } = getResolvedResendKey(project);
        if (!isByok || !key) {
            return res.status(403).json({ success: false, message: "Contacts require a custom Resend API Key (BYOK)." });
        }

        if (req.method === "GET") {
            const response = await axios.get(`https://api.resend.com/audiences/${safeAudienceId}/contacts`, {
                headers: { Authorization: `Bearer ${key}` }
            });
            return res.json({ success: true, data: response.data });
        }

        if (req.method === "POST") {
            const { email, firstName, lastName, unsubscribed } = req.body;
            if (!email) return res.status(400).json({ success: false, message: "Contact email required" });

            const payload = { email, first_name: firstName, last_name: lastName, unsubscribed };
            const response = await axios.post(`https://api.resend.com/audiences/${safeAudienceId}/contacts`, payload, {
                headers: { Authorization: `Bearer ${key}` }
            });
            return res.json({ success: true, data: response.data });
        }

        return res.status(405).json({ success: false, message: "Method not allowed" });
    } catch (err) {
        const errorMsg = err.response?.data?.message || err.message;
        return res.status(err.response?.status || 500).json({ success: false, message: errorMsg });
    }
};

module.exports.deleteContact = async (req, res) => {
    try {
        const { projectId, audienceId, contactId } = req.params;
        const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) }).select("+resendApiKey.encrypted +resendApiKey.iv +resendApiKey.tag");
        if (!project) return res.status(404).json({ success: false, message: "Project not found" });

        const { key, isByok } = getResolvedResendKey(project);
        if (!isByok || !key) {
            return res.status(403).json({ success: false, message: "Contacts require a custom Resend API Key (BYOK)." });
        }

        const resendIdPattern = /^[A-Za-z0-9_-]+$/;
        if (!resendIdPattern.test(audienceId) || !resendIdPattern.test(contactId)) {
            return res.status(400).json({ success: false, message: "Invalid audienceId or contactId format." });
        }

        const safeAudienceId = encodeURIComponent(audienceId);
        const safeContactId = encodeURIComponent(contactId);

        await axios.delete(`https://api.resend.com/audiences/${safeAudienceId}/contacts/${safeContactId}`, {
            headers: { Authorization: `Bearer ${key}` }
        });

        return res.json({ success: true, message: "Contact removed successfully" });
    } catch (err) {
        const errorMsg = err.response?.data?.message || err.message;
        return res.status(err.response?.status || 500).json({ success: false, message: errorMsg });
    }
};

module.exports.sendMarketingBroadcast = async (req, res) => {
    try {
        const { projectId } = req.params;
        const { audienceId, subject, html, from } = req.body;

        const project = await Project.findOne({ _id: projectId, ...getProjectAccessQuery(req.user._id) }).select("+resendApiKey.encrypted +resendApiKey.iv +resendApiKey.tag");
        if (!project) return res.status(404).json({ success: false, message: "Project not found" });

        const { key, isByok } = getResolvedResendKey(project);
        if (!isByok || !key) {
            return res.status(403).json({ success: false, message: "Marketing Broadcasts require a custom Resend API Key (BYOK)." });
        }

        const dev = await Developer.findById(req.user._id);
        const effectivePlan = resolveEffectivePlan(dev);
        if (effectivePlan !== "pro") {
            return res.status(403).json({ success: false, message: "Marketing Broadcasts are a premium feature requiring the Pro tier." });
        }

        if (!audienceId || !subject || !html) {
            return res.status(400).json({ success: false, message: "Audience ID, subject, and html content are required." });
        }

        const payload = {
            audience_id: audienceId,
            subject,
            html,
            from: from || project.resendFromEmail || "onboarding@resend.dev"
        };

        const response = await axios.post("https://api.resend.com/broadcasts", payload, {
            headers: { Authorization: `Bearer ${key}` }
        });

        return res.json({ success: true, data: response.data, message: "Broadcast dispatched successfully!" });
    } catch (err) {
        const errorMsg = err.response?.data?.message || err.message;
        return res.status(err.response?.status || 500).json({ success: false, message: errorMsg });
    }
};

// ─────────────────────────────────────────────────────────────────────────────
// TEAM MEMBER MANAGEMENT
// ─────────────────────────────────────────────────────────────────────────────

/**
 * GET /:projectId/members
 * Returns the member list populated with email and name.
 * Accessible by owner and all members.
 */
module.exports.getMembers = async (req, res, next) => {
  try {
    const { projectId } = req.params;
    const project = await Project.findOne({
      _id: projectId,
      ...getProjectAccessQuery(req.user._id),
    })
      .select("owner members")
      .populate("owner", "email name")
      .populate("members.user", "email name")
      .lean();

    if (!project) return next(new AppError(404, "Project not found or access denied"));

    const ownerEntry = {
      _id: project.owner._id,
      email: project.owner.email,
      name: project.owner.name,
      role: "owner",
    };

    const memberEntries = (project.members || []).map((m) => ({
      _id: m.user._id,
      email: m.user.email,
      name: m.user.name,
      role: m.role,
      addedAt: m.addedAt,
    }));

    return res.json({
      success: true,
      data: { members: [ownerEntry, ...memberEntries] },
      message: "",
    });
  } catch (err) {
    next(err);
  }
};

/**
 * POST /:projectId/members/invite
 * Owner-only. Invites a developer by email (must already have an account).
 * Creates a pending Invitation and sends an invite email.
 */
module.exports.inviteMember = async (req, res, next) => {
  try {
    const { projectId } = req.params;
    const { email, role = "admin" } = req.body;

    if (!email || typeof email !== "string") {
      return next(new AppError(400, "A valid email address is required"));
    }
    if (!["admin", "viewer"].includes(role)) {
      return next(new AppError(400, "Role must be 'admin' or 'viewer'"));
    }

    // Only owner can invite
    const project = await Project.findOne({
      _id: projectId,
      owner: req.user._id,
    })
      .select("owner members name")
      .lean();

    if (!project) return next(new AppError(404, "Project not found or access denied"));

    const role_ = getProjectRole(project, req.user._id);
    if (role_ !== "owner") {
      return next(new AppError(403, "Only the project owner can invite members"));
    }

    const normalizedEmail = email.trim().toLowerCase();

    // Check invitee has an account
    const invitee = await Developer.findOne({ email: normalizedEmail }).select("_id email name").lean();
    if (!invitee) {
      return next(new AppError(404, "No urBackend account found with this email address"));
    }

    // Cannot invite yourself
    if (invitee._id.toString() === req.user._id.toString()) {
      return next(new AppError(400, "You cannot invite yourself"));
    }

    // Cannot invite someone already a member
    const alreadyMember = project.members?.some(
      (m) => m.user.toString() === invitee._id.toString()
    );
    if (alreadyMember) {
      return next(new AppError(409, "This developer is already a member of this project"));
    }

    // Cannot invite if there's already a pending invite for them
    const existingInvite = await Invitation.findOne({
      project: projectId,
      invitee: invitee._id,
      status: "pending",
      expiresAt: { $gt: new Date() },
    }).lean();
    if (existingInvite) {
      return next(new AppError(409, "A pending invitation already exists for this developer"));
    }

    // Create the invitation (7-day expiry)
    const expiresAt = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000);
    await Invitation.create({
      project: projectId,
      inviter: req.user._id,
      invitee: invitee._id,
      role,
      status: "pending",
      expiresAt,
    });

    // TODO: Send invite notification email here once email template is ready
    // await sendInviteEmail({ to: invitee.email, projectName: project.name, role, inviterEmail: req.user.email });

    return res.status(201).json({
      success: true,
      data: { email: invitee.email, role, expiresAt },
      message: `Invitation sent to ${invitee.email}`,
    });
  } catch (err) {
    next(err);
  }
};

/**
 * PATCH /:projectId/members/:memberId/role
 * Owner-only. Changes a member's role.
 */
module.exports.updateMemberRole = async (req, res, next) => {
  try {
    const { projectId, memberId } = req.params;
    const { role } = req.body;

    if (!["admin", "viewer"].includes(role)) {
      return next(new AppError(400, "Role must be 'admin' or 'viewer'"));
    }

    if (req.user._id.toString() === memberId) {
      return next(new AppError(403, "You cannot modify your own role"));
    }

    const project = await Project.findOne({
      _id: projectId,
      owner: req.user._id,
      "members.user": memberId,
    });

    if (!project) return next(new AppError(404, "Project or member not found"));

    const member = project.members.find((m) => m.user.toString() === memberId);
    if (!member) return next(new AppError(404, "Member not found"));

    member.role = role;
    await project.save();
    await deleteProjectById(projectId);

    return res.json({
      success: true,
      data: { memberId, role },
      message: "Member role updated",
    });
  } catch (err) {
    next(err);
  }
};

/**
 * DELETE /:projectId/members/:memberId
 * Admin/Owner-only. Removes a member from the project.
 */
module.exports.removeMember = async (req, res, next) => {
  try {
    const { projectId, memberId } = req.params;

    if (req.user._id.toString() === memberId) {
      return next(new AppError(403, "You cannot remove yourself"));
    }

    // Verify member exists before attempting removal
    const projectCheck = await Project.findOne({ _id: projectId, owner: req.user._id }).lean();
    if (!projectCheck) return next(new AppError(404, "Project not found or access denied"));

    const memberExists = projectCheck.members?.some(m => m.user.toString() === memberId);
    if (!memberExists) {
      return next(new AppError(404, "Member not found in this project"));
    }

    const project = await Project.findOneAndUpdate(
      { _id: projectId, owner: req.user._id },
      { $pull: { members: { user: memberId } } },
      { new: true },
    );

    if (!project) return next(new AppError(404, "Project not found or access denied"));

    // Also clean up any pending invitations for this member
    await Invitation.deleteMany({ project: projectId, invitee: memberId });

    // Invalidate project cache
    await deleteProjectById(projectId);

    return res.json({
      success: true,
      data: {},
      message: "Member removed from project",
    });
  } catch (err) {
    next(err);
  }
};