i want to return as part of the groupedProject a projectReporter boolean. in the projectmembership table if a userid is false on projectid that means they are project reporters. i want a boolean true is projectlead is false and false if they are not part of the project. this is the column for the table "1	9	6	true" for id, userid, projectid, projectlead. "router.get(
  "/charity-projects/:ukcharitynumber",
  authMiddleware,
  async (req, res) => {
    try {
      console.log("Fetching transactions and project updates for charity...");

      const { fetchByCharityId, fetchByTransactionId } = req.query;
      const userId = getRequestingUserId(req.user);
      const ukcharitynumber = req.params.ukcharitynumber;

      // Define allowed charity roles
      const allowedRoles = [
        "Charity Head",
        "Charity Admin",
        "Operations Head",
        "Operations Admin",
        "Fundraising Head",
        "Fundraising Admin",
      ];

      // Step 1: Fetch projects the user has access to (either by project membership or charity role)
      const userProjectIds = knex("projectmembership")
        .where("userid", userId)
        .select("projectid");

      const authorizedCharityProjects = knex("project as proj")
        .innerJoin(
          "charitymembership as chm",
          "proj.charityid",
          "chm.charityid"
        )
        .where("chm.userid", userId)
        .whereRaw("chm.roles && ?", [allowedRoles])
        .select("proj.id");


      const accessibleProjectIdsQuery = knex
        .from(function () {
          this.union([
            userProjectIds,
            authorizedCharityProjects,
          ])
            .as("accessibleProjects");
        });

      const projectQuery = knex("project as proj")
        .distinct("proj.id as projectid")
        .select(
          "proj.title as projecttitle",
          "proj.currentamount",
          "proj.isactive",
          "cha.charityname",
          "proj.currency"
        )
        .innerJoin("charity as cha", "cha.ukcharitynumber", "proj.charityid")
        .where("cha.ukcharitynumber", ukcharitynumber)
        .whereIn("proj.id", accessibleProjectIdsQuery);

      const projects = await projectQuery;

      if (!projects.length) {
        return res.status(404).json({
          message: "No projects found for this charity that you can view.",
        });
      }

      // Step 2: Fetch transactions for the retrieved projects
      const projectIds = projects.map((p) => p.projectid);

      let transactionQuery = knex("transaction as tra")
        .select(
          "tra.id",
          "tra.category as category",
          "tra.reason as whatfor",
          "tra.title as whatbrought",
          "tra.amount",
          "tra.currency",
          "tra.proof",
          "tra.isTransactionActive",
          "tra.datetime",
          "tra.projectid",
          "tra.reviewerid",
          "tra.approved",
          "tra.reviewernote as reviewerNote",
          "usr.firstname as firstName",
          "usr.lastname as lastName",
          "reviewer.firstname as reviewerFirstName",
          "reviewer.lastname as reviewerLastName"
        )
        .innerJoin("user as usr", "usr.id", "tra.userid")
        .leftJoin("user as reviewer", "reviewer.id", "tra.reviewerid")
        .orderBy("tra.datetime", "desc");

      if (fetchByTransactionId) {
        transactionQuery.andWhere("tra.id", fetchByTransactionId);
      } else {
        transactionQuery.whereIn("tra.projectid", projectIds);
      }

      const transactions = await transactionQuery;

      // Step 3: Fetch project updates
      const projectUpdates = await knex("projectupdate")
        .whereIn("projectid", projectIds)
        .select(
          "id",
          "text",
          "projectid",
          "userid",
          "datetime",
          "author",
          "approver",
          "status",
          "title",
          "subtitle",
          "body",
          "mainimage",
          "weblinks",
          "category",
          "tags",
          "priority",
          "likes",
          "viewcount",
          "videolink",
          "approvaldate",
          "createddate",
          "updateddate"
        );

      // Step 4: Group transactions and updates by project
      const groupedProjects = projects
        .map((project) => {
          const projectTransactions = transactions.filter(
            (t) => t.projectid === project.projectid
          );

          const updates = projectUpdates.filter(
            (u) => u.projectid === project.projectid
          );

          if (fetchByTransactionId && projectTransactions.length === 0)
            return null;

          return {
            projectid: project.projectid,
            name: project.projecttitle,
            budget: project.currentamount,
            currency: project.currency,
            isactive: project.isactive,
            charityname: project.charityname,
            projectlead: project.projectlead,
            transactions: projectTransactions.map((t) => ({
              id: t.id,
              category: t.category,
              user: `${t.firstName} ${t.lastName}`,
              reviewer:
                t.reviewerFirstName && t.reviewerLastName
                  ? {
                      firstName: t.reviewerFirstName,
                      lastName: t.reviewerLastName,
                    }
                  : null,
              reviewerNote: t.reviewerNote,
              amount: t.amount,
              whatfor: t.whatfor,
              whatbrought: t.whatbrought,
              currency: t.currency,
              proof: t.proof,
              status:
                t.approved === true
                  ? "Accepted"
                  : t.approved === false
                  ? "Rejected"
                  : "Pending",
              transaction_details: t.whatfor,
              isTransactionActive: t.isTransactionActive,
              datetime: t.datetime,
              time: new Date(t.datetime).toLocaleTimeString("en-GB"),
            })),
            projectUpdates: updates,
          };
        })
        .filter(Boolean);

      // Step 5: Final response
      res.status(200).json({
        charityname: projects[0]?.charityname || "Unknown Charity",
        projects: groupedProjects,
      });
    } catch (error) {
      console.error(
        "Failed to fetch transactions and updates for charity:",
        error
      );
      res.status(500).json({
        error: "Failed to fetch transactions and updates for charity",
      });
    }
  }
); "