import mongoose from "mongoose";
import "dotenv/config";

const MONGODB_URI =
  process.env.MONGODB_URI || "mongodb://localhost:27017/office-crm";

const userSchema = new mongoose.Schema({
  fullName: String,
  email: String,
  role: String,
  category: String,
});

const projectSchema = new mongoose.Schema({
  name: String,
  category: String,
  members: [{ type: mongoose.Schema.Types.ObjectId, ref: "User" }],
});

const User = mongoose.model("User", userSchema);
const Project = mongoose.model("Project", projectSchema);

async function testProjectQuery() {
  try {
    console.log("Connecting to MongoDB...");
    await mongoose.connect(MONGODB_URI);
    console.log("Connected!\n");

    // Get a non-admin user
    const employee = await User.findOne({ role: { $ne: "admin" } });

    if (!employee) {
      console.log("❌ No employee found in database!");
      return;
    }

    console.log("Testing as Employee:");
    console.log(`  Name: ${employee.fullName}`);
    console.log(`  Email: ${employee.email}`);
    console.log(`  Role: ${employee.role}`);
    console.log(`  Category: ${employee.category || "NO CATEGORY"}\n`);

    // Simulate the backend query
    const userCategory = employee.category || "general";
    const query = {
      $or: [{ category: userCategory }, { members: employee._id }],
    };

    console.log("MongoDB Query:");
    console.log(JSON.stringify(query, null, 2));
    console.log("");

    // Execute query
    const projects = await Project.find(query)
      .populate("members", "fullName category")
      .lean();

    console.log(`✅ Query returned ${projects.length} project(s)\n`);

    if (projects.length > 0) {
      console.log("Projects Employee Can See:");
      console.log("=".repeat(60));
      projects.forEach((p) => {
        const reason = p.category === userCategory ? "SAME CATEGORY" : "MEMBER";
        console.log(`📁 ${p.name}`);
        console.log(`   Category: ${p.category}`);
        console.log(`   Reason: ${reason}`);
        if (p.members.length > 0) {
          console.log(
            `   Members: ${p.members.map((m) => m.fullName).join(", ")}`
          );
        }
        console.log("");
      });
    } else {
      console.log("⚠️  NO PROJECTS FOUND FOR THIS EMPLOYEE\n");
      console.log("Possible reasons:");
      console.log(`  1. No projects with category = "${userCategory}"`);
      console.log(`  2. Employee not added as member to any projects\n`);

      // Show all projects
      const allProjects = await Project.find({});
      console.log(`Total projects in database: ${allProjects.length}`);
      if (allProjects.length > 0) {
        console.log("\nAll Projects:");
        allProjects.forEach((p) => {
          console.log(
            `  - ${p.name} (category: ${p.category || "NO CATEGORY"})`
          );
        });
      }
    }

    // Show what categories exist
    console.log("\n" + "=".repeat(60));
    console.log("SOLUTION:");
    console.log("=".repeat(60));
    console.log(`To make projects visible to ${employee.fullName}:`);
    console.log(`  1. Create project with category = "${userCategory}"`);
    console.log(
      `  2. OR assign ${employee.fullName} as member to existing project\n`
    );
  } catch (error) {
    console.error("❌ Error:", error);
  } finally {
    await mongoose.connection.close();
    console.log("Connection closed");
  }
}

testProjectQuery();
