const User = require("../../models").users;
const Profile = require("../../models").profiles;
const Project = require("../../models").projects;
const ProjectSubmission = require("../../models").project_submissions;
const Level = require("../../models").levels;
const StudyMode = require("../../models").study_modes;
const ProjectType = require("../../models").project_types;
const ProjectTopic = require("../../models").project_topics;
const Department = require("../../models").departments;
const Institute = require("../../models").institutes;
const Session = require("../../models").sessions;
const StudentSupervisor = require("../../models").student_supervisors;
const DocumentCategory = require("../../models").document_categories;
const CryptoJS = require("crypto-js");
var key = CryptoJS.enc.Base64.parse("4gt71TxD1e4P3433");
var iv = CryptoJS.enc.Base64.parse("4gt71TxD1e4P3433");
const ActivityLog = require("../../models").activity_log;
const LibraryAccessCode = require("../../models").library_access_codes;
const { viewResearch } = require("./researchLibrary");
const { Op, where } = require("sequelize");
const { Parser } = require('json2csv');
const sequelize = require("../../config/connection");

exports.staffDashboard = async (req, res) => {
  try {
    const activeSession = await Session.findOne({
      where: { status: true },
      attributes: ["id"],
      raw: true,
    });
    const [totalAssigned, totalPT, totalFT, totalHND, totalND] =
      await Promise.all([
        StudentSupervisor.count({
          where: {
            lecturer_user_id: req.user.id,
            session_id: activeSession.id,
          },
        }),
        StudentSupervisor.count({
          where: {
            lecturer_user_id: req.user.id,
            session_id: activeSession.id,
          },
          include: [
            {
              model: Profile,
              where: { study_mode_id: 2 },
              as: "stud",
              required: true,
            },
          ],
        }),
        StudentSupervisor.count({
          where: {
            lecturer_user_id: req.user.id,
            session_id: activeSession.id,
          },
          include: [
            {
              model: Profile,
              where: { study_mode_id: 1 },
              as: "stud",
              required: true,
            },
          ],
        }),
        StudentSupervisor.count({
          where: {
            lecturer_user_id: req.user.id,
            session_id: activeSession.id,
          },
          include: [
            {
              model: Profile,
              where: { level_id: 2 },
              as: "stud",
              required: true,
            },
          ],
        }),
        StudentSupervisor.count({
          where: {
            lecturer_user_id: req.user.id,
            session_id: activeSession.id,
          },
          include: [
            {
              model: Profile,
              where: { level_id: 1 },
              as: "stud",
              required: true,
            },
          ],
        }),
      ]);

    const departments = await Department.findAll({ where: { active: true } });
    let [projectInprogress] =
      await sequelize.query(`SELECT count(project_submissions.id) AS projectInprogress  FROM project_submissions 
        INNER JOIN project_topics ON project_submissions.project_id  = project_topics.id 
        WHERE document_category_id <> '7' AND lecturer_user_id = '${req.user.id}' AND project_submissions.session_id =  '${activeSession.id}'`);

    let [projectRejected] =
      await sequelize.query(`SELECT count(project_submissions.id) AS projectInprogress FROM project_submissions 
        INNER JOIN project_topics ON project_submissions.project_id  = project_topics.id 
        WHERE project_submissions.active = '0' AND lecturer_user_id = '${req.user.id}' AND project_submissions.session_id =  '${activeSession.id}' `);

    let [projectCompleted] =
      await sequelize.query(`SELECT count(project_submissions.id) AS projectInprogress 
        FROM project_submissions 
        INNER JOIN project_topics ON project_submissions.project_id = project_topics.id
        WHERE document_category_id = '7' AND lecturer_user_id = '${req.user.id}' AND project_submissions.session_id =  '${activeSession.id}'
        `);
    let projects = [];
    const topics = await ProjectTopic.findAll({
      where: {
        lecturer_user_id: req.user.id,
        active: true,
        is_approved: true,
        session_id: activeSession.id,
      },
      include: [
        {
          model: ProjectSubmission,
          include: [
            {
              model: DocumentCategory,
              attributes: ["name"],
            },
            {
              model: ProjectTopic,
              include: [
                {
                  model: User,
                  as: "student",
                  attributes: ["username"],
                  include: [
                    {
                      model: Profile,
                      attributes: ["firstname", "lastname", "middlename"],
                      include: [
                        {
                          model: Level,
                          attributes: ["name"],
                        },
                        {
                          model: StudyMode,
                          attributes: ["name"],
                        },
                      ],
                    },
                  ],
                },
              ],
            },
          ],
        },
      ],
    });
    for (const topic of topics) {
      projects = [...projects, ...topic.project_submissions];
    }
    return res.render("staff/staff_dashboard.ejs", {
      user: req.user,
      departments,
      projectInprogress,
      projectCompleted,
      projectRejected,
      projects: JSON.parse(JSON.stringify(projects)),
      sessions: req.sessions,
      totalAssigned,
      totalPT,
      totalFT,
      totalHND,
      totalND,
    });
  } catch (error) {
    console.log(error);
  }
};

exports.researchLibrary = async (req, res) => {
  try {
    const { auth_code } = req.query;
    const data = await viewResearch(req, res);
    const departments = await Department.findAll({ where: { active: true } });
    const sessions = await Session.findAll();
    // if(!accessType){
    // }
    if (req.user.role == 1) {
      return res.render("staff/library.ejs", {
        user: req.user,
        projects: data.projects,
        departments,
        sessions,
      });
    } else {
      if (!auth_code) {
        return res.redirect("/staff-dashboard");
      }
      const user = await LibraryAccessCode.findOne({
        where: { access_code: auth_code, status: false },
      });
      if (user) {
        await LibraryAccessCode.update(
          { status: true, user_by: req.user.id },
          { where: { access_code: auth_code } }
        );
        return res.render("staff/researchLibrary.ejs", {
          user: req.user,
          error: false,
          projects: data.projects,
          departments,
          sessions,
        });
      } else {
        return res.redirect("/staff-dashboard");
      }
    }
  } catch (error) {
    console.log(error);
  }
};

exports.myAssignStuent = async (req, res) => {
  try {
    let allStudents = [];
    let pendingApproval = 0;
    let totalND = 0;
    let totalHND = 0;
    let totalFT = 0;
    let totalPT = 0;
    const session_id = req.params.session_id;
    const activeSession = await Session.findOne({
      where: { status: true },
      attributes: ["id"],
      raw: true,
    });

    const projectSubmission = await Project.findAll({
      where: {
        lecturer_user_id: req.user.id,
      },
      include: [{ model: ProjectSubmission }],
    });
    for (const project of JSON.parse(JSON.stringify(projectSubmission))) {
      if (project.project_submissions.is_approved === false) pendingApproval++;
    }
    const students = await StudentSupervisor.findAll({
      where: {
        lecturer_user_id: req.user.id,
        session_id: activeSession.id,
      },
      attributes: ["student_user_id"],
    });
    for (const student of students) {
      let user = await Profile.findOne({
        where: { user_id: student.student_user_id, session_id },
        attributes: ["firstname", "lastname", "middlename", "user_id"],
        include: [
          {
            model: User,
            attributes: ["username", "email", "phone", "id"],
          },
          {
            model: Level,
          },
          {
            model: StudyMode,
          },
        ],
      });
      if (JSON.parse(JSON.stringify(user))?.study_mode.name == "FULL TIME") {
        totalFT++;
      }
      if (JSON.parse(JSON.stringify(user))?.study_mode.name == "PART TIME") {
        totalPT++;
      }
      if (JSON.parse(JSON.stringify(user))?.level.name == "National Diploma") {
        totalND++;
      }
      if (
        JSON.parse(JSON.stringify(user))?.level.name ==
        "Higher National Diploma"
      ) {
        totalHND++;
      }
      let project = await ProjectTopic.findOne({
        where: { student_user_id: student.student_user_id },
        attributes: ["id"],
        include: [
          {
            where: { document_category_id: 7, is_approved: true },
            required: true,
            model: ProjectSubmission,
            attributes: ["id"],
          },
        ],
      });
      project = JSON.parse(JSON.stringify(project));
      if (user) {
        user = JSON.parse(JSON.stringify(user));
        allStudents.push({
          ...user,
          project_id: project?.project_submissions[0].id,
        });
      }
    }
    return res.render("staff/assign_student.ejs", {
      user: req.user,
      students: allStudents,
      subscriptions: req.subscriptions,
      error: false,
      pendingApproval,
      totalND,
      totalHND,
      totalFT,
      totalPT,
      sessions: req.sessions,
    });
  } catch (error) {
    console.log(error);
    return res.render("staff/assign_student.ejs", {
      user: req.user,
      students: [],
      subscriptions: req.subscriptions,
      error: error.message,
      totalND: 0,
      totalHND: 0,
      totalFT: 0,
      totalPT: 0,
      sessions: req.sessions,
    });
  }
};

exports.topic = async (req, res) => {
  try {
    const activeSession = await Session.findOne({
      where: { status: true },
      attributes: ["id"],
      raw: true,
    });
    const commonWhereConditions = {
      active: true,
      lecturer_user_id: req.user.id,
      session_id: activeSession.id,
    };

    const pendingApprove = await ProjectTopic.count({
      where: { ...commonWhereConditions, is_approved: false },
    });

    const approved = await ProjectTopic.count({
      where: { ...commonWhereConditions, is_approved: true },
    });

    const rejected = await ProjectTopic.count({
      where: { ...commonWhereConditions, active: false },
    });

    const topics = await ProjectTopic.findAll({
      where: commonWhereConditions,
      include: [
        {
          model: User,
          as: "student",
          attributes: ["username"],
          include: [
            {
              model: Profile,
              include: [
                {
                  model: Level,
                },
                {
                  model: StudyMode,
                },
              ],
            },
          ],
        },
        {
          model: ProjectType,
        },
      ],
    });

    return res.render("staff/topic.ejs", {
      user: req.user,
      pendingApprove,
      approved,
      rejected,
      topics,
      error: false,
      sessions: req.sessions, // Assuming you have sessions available in req
    });
  } catch (error) {
    console.log(error);
    return res.render("staff/topic.ejs", {
      user: req.user,
      pendingApprove: 0,
      approved: 0,
      rejected: 0,
      topics: [],
      error: error.message,
      sessions: req.sessions, // Assuming you have sessions available in req
    });
  }
};

exports.searchStudentProject = async (req, res) => {
  const { term } = req.query;
  const projects = await Project.findAll({
    where: {
      lecturer_user_id: req.user.id,
      topic: {
        [Op.startsWith]: `${term}`,
      },
    },
    attributes: [
      "id",
      "student_user_id",
      "topic",
      "createdAt",
      "active",
      "is_completed",
    ],
    include: [
      {
        model: ProjectType,
        attributes: ["name"],
      },
      {
        model: Profile,
        as: "student",
        attributes: [
          "department_id",
          "firstname",
          "lastname",
          "middlename",
          "photo_path",
          "study_mode_id",
          "level_id",
        ],
        include: [
          {
            model: User,
            attributes: ["username"],
            username: {
              [Op.startsWith]: `${term}`,
            },
          },
          {
            model: Department,
            attributes: ["id", "name"],
            include: [
              {
                model: Institute,
                attributes: ["name"],
              },
            ],
          },
        ],
        where: {
          [Op.or]: [
            {
              firstname: {
                [Op.startsWith]: `${term}`,
              },
              lastname: {
                [Op.startsWith]: `${term}`,
              },
              middlename: {
                [Op.startsWith]: `${term}`,
              },
            },
          ],
        },
      },
    ],
  });

  return res.status(200).send(projects);
};
exports.getProjectStudentForLecturer = async (req, res) => {
  const { term, prop } = req.params;
  let projects = [];
  if (!term)
    return res
      .status(400)
      .send({ success: false, message: "Term is required!" });
  if (prop == "level") {
    projects = await ProjectSubmission.findAll({
      include: [
        {
          model: DocumentCategory,
          attributes: ["name"],
        },
        {
          model: Project,
          where: {
            lecturer_user_id: req.user.id,
          },
          attributes: [
            "id",
            "student_user_id",
            "topic",
            "createdAt",
            "active",
            "is_completed",
          ],
          include: [
            {
              model: ProjectType,
              attributes: ["name"],
            },
            {
              model: User,
              as: "lecturer",
              include: [
                {
                  model: Profile,
                  attributes: [
                    "department_id",
                    "firstname",
                    "lastname",
                    "middlename",
                  ],
                },
              ],
            },
            {
              model: User,
              as: "student",
              include: [
                {
                  model: Profile,
                  attributes: [
                    "department_id",
                    "firstname",
                    "lastname",
                    "middlename",
                    "photo_path",
                    "study_mode_id",
                    "level_id",
                  ],
                  include: [
                    {
                      model: Department,
                      attributes: ["id", "name"],
                      include: [
                        {
                          model: Institute,
                          attributes: ["name"],
                        },
                      ],
                    },
                  ],
                  where: {
                    level_id: term,
                  },
                },
              ],
            },
          ],
        },
      ],
    });
  }
  if (prop == "study_mode") {
    projects = await ProjectSubmission.findAll({
      include: [
        {
          model: DocumentCategory,
          attributes: ["name"],
        },
        {
          model: Project,
          where: {
            lecturer_user_id: req.user.id,
          },
          attributes: [
            "id",
            "student_user_id",
            "topic",
            "createdAt",
            "active",
            "is_completed",
          ],
          include: [
            {
              model: ProjectType,
              attributes: ["name"],
            },
            {
              model: Profile,
              as: "lecturer",
              attributes: [
                "department_id",
                "firstname",
                "lastname",
                "middlename",
              ],
            },
            {
              model: Profile,
              as: "student",
              attributes: [
                "department_id",
                "firstname",
                "lastname",
                "middlename",
                "photo_path",
                "study_mode_id",
                "level_id",
              ],
              include: [
                {
                  model: User,
                  attributes: ["username"],
                },
                {
                  model: Department,
                  attributes: ["id", "name"],
                  include: [
                    {
                      model: Institute,
                      attributes: ["name"],
                    },
                  ],
                },
              ],
              where: {
                study_mode_id: term,
              },
            },
          ],
        },
      ],
    });
  }
  if (prop == "projectTye") {
    projects = await ProjectSubmission.findAll({
      include: [
        {
          model: DocumentCategory,
          attributes: ["name"],
        },
        {
          model: Project,
          where: {
            lecturer_user_id: req.user.id,
          },
          attributes: [
            "id",
            "student_user_id",
            "topic",
            "createdAt",
            "active",
            "is_completed",
          ],
          include: [
            {
              model: ProjectType,
              attributes: ["name", "id"],
              where: { id: term },
            },
            {
              model: Profile,
              as: "lecturer",
              attributes: [
                "department_id",
                "firstname",
                "lastname",
                "middlename",
              ],
            },
            {
              model: Profile,
              as: "student",
              attributes: [
                "department_id",
                "firstname",
                "lastname",
                "middlename",
                "photo_path",
                "study_mode_id",
                "level_id",
              ],
              include: [
                {
                  model: User,
                  attributes: ["username"],
                },
                {
                  model: Department,
                  attributes: ["id", "name"],
                  include: [
                    {
                      model: Institute,
                      attributes: ["name"],
                    },
                  ],
                },
              ],
            },
          ],
        },
      ],
    });
  }
  if (prop == "status") {
    projects = await ProjectSubmission.findAll({
      include: [
        {
          model: DocumentCategory,
          attributes: ["name"],
        },
        {
          model: Project,
          where: {
            lecturer_user_id: req.user.id,
            is_completed: Boolean(term),
          },
          attributes: [
            "id",
            "student_user_id",
            "topic",
            "createdAt",
            "active",
            "is_completed",
          ],
          include: [
            {
              model: ProjectType,
              attributes: ["name"],
            },
            {
              model: Profile,
              as: "lecturer",
              attributes: [
                "department_id",
                "firstname",
                "lastname",
                "middlename",
              ],
            },
            {
              model: Profile,
              as: "student",
              attributes: [
                "department_id",
                "firstname",
                "lastname",
                "middlename",
                "photo_path",
                "study_mode_id",
                "level_id",
              ],
              include: [
                {
                  model: User,
                  attributes: ["username"],
                },
                {
                  model: Department,
                  attributes: ["id", "name"],
                  include: [
                    {
                      model: Institute,
                      attributes: ["name"],
                    },
                  ],
                },
              ],
            },
          ],
        },
      ],
    });
  }
  return res.status(200).send(projects);
};
exports.studentManagemt = async (req, res) => {
  let err = false;
  let success = false;
  if (req.query.error) err = req.query.error;
  if (req.query.success) success = true;
  const department_id = req.department_id;
  const session = await Session.findOne({ where: { status: true } });
  const supervisors = await Profile.findAll({
    where: { department_id, role_id: 6 },
  });
  const departments = await Department.findAll({ where: { active: true } });
  const department = await Department.findByPk(department_id);
  const totalND = await Profile.count({
    where: { department_id, level_id: 1, session_id: session.id, role_id: 7 },
  });
  const totalHND = await Profile.count({
    where: { department_id, level_id: 2, session_id: session.id, role_id: 7 },
  });
  const totalFT = await Profile.count({
    where: { department_id, study_mode_id: 1, session_id: session.id },
    role_id: 7,
  });
  const totalPT = await Profile.count({
    where: {
      department_id,
      study_mode_id: 2,
      session_id: session.id,
      role_id: 7,
    },
  });

  const activeSession = await Session.findOne({
    where: { status: true },
    attributes: ["id"],
    raw: true,
  });
  const student = await Profile.findAll({
    where: {
      department_id,
      session_id: activeSession.id,
      role_id: 7,
    },
    order: [["createdAt", "DESC"]],
    include: [
      {
        model: User,
        attributes: ["username", "email", "phone", "id"],
      },
      {
        model: StudyMode,
      },
      {
        model: Level,
      },
    ],
  });
  return res.render("staff/student_management.ejs", {
    success,
    err,
    supervisors: JSON.parse(JSON.stringify(supervisors)),
    students: JSON.parse(JSON.stringify(student)),
    departments,
    department,
    totalND,
    totalHND,
    totalFT,
    totalPT,
    user: req.user,
    sessions: req.sessions,
  });
};

exports.studentManagemtFilter = async (req, res) => {
  let { term, prop } = req.params;
  const department_id = req.department_id;
  const session = await Session.findOne({ where: { status: true } });
  let student = [];
  if (prop == "study_mode") {
    student = await Profile.findAll({
      where: {
        department_id,
        session_id: session.id,
        study_mode_id: term,
      },
      include: [
        {
          model: User,
          attributes: ["username", "email", "phone"],
        },
        {
          model: StudyMode,
        },
        {
          model: Level,
        },
      ],
    });
  } else {
    student = await Profile.findAll({
      where: {
        department_id,
        session_id: session.id,
        level_id: term,
      },
      include: [
        {
          model: User,
          attributes: ["username", "email", "phone"],
        },
        {
          model: StudyMode,
        },
        {
          model: Level,
        },
      ],
    });
  }

  return res.send(JSON.parse(JSON.stringify(student)));
};

exports.studentManagemtSearchFilter = async (req, res) => {
  const { term } = req.params;
  const department_id = req.department_id;
  const session = await Session.findOne({ where: { status: true } });

  const student = await Profile.findAll({
    where: {
      department_id,
      session_id: session.id,
    },
    where: {
      [Op.or]: [
        {
          firstname: {
            [Op.startsWith]: `${term}`,
          },
          lastname: {
            [Op.startsWith]: `${term}`,
          },
          middlename: {
            [Op.startsWith]: `${term}`,
          },
        },
      ],
    },
    include: [
      {
        model: User,
        attributes: ["username", "email", "phone"],
        where: {
          [Op.or]: [
            {
              username: {
                [Op.startsWith]: `${term}`,
              },
              email: {
                [Op.startsWith]: `${term}`,
              },
              phone: {
                [Op.startsWith]: `${term}`,
              },
            },
          ],
        },
      },
      {
        model: StudyMode,
      },
      {
        model: Level,
      },
    ],
  });
  return res.send(JSON.parse(JSON.stringify(student)));
};

exports.approveTopic = async (req, res) => {
  try {
    const {
      id,
      message,
      student_id,
      actionType,
      project_type_id,
      topicUpdate,
    } = req.body;
    let updateTopic;
    if (actionType === "accept") {
      await ProjectTopic.update(
        { is_approved: false, active: false },
        {
          where: {
            student_user_id: student_id,
            lecturer_user_id: req.user.id,
            project_type_id,
          },
        }
      );
      updateTopic = await ProjectTopic.update(
        { is_approved: true, active: true, approval_comment: message },
        { where: { id } }
      );
      return res
        .status(200)
        .send({ success: true, message: "Topic successfully approved" });
    } else if (actionType === "reject") {
      updateTopic = await ProjectTopic.update(
        { active: false, approval_comment: message },
        { where: { id } }
      );
      return res
        .status(200)
        .send({ success: true, message: "Topic successfully rejected" });
    } else if (actionType === "edit") {
      await ProjectTopic.update(
        { is_approved: false, active: false },
        {
          where: {
            student_user_id: student_id,
            lecturer_user_id: req.user.id,
            project_type_id,
          },
        }
      );
      updateTopic = await ProjectTopic.update(
        {
          is_approved: true,
          active: true,
          approval_comment: message,
          topic: topicUpdate,
        },
        { where: { id } }
      );
      return res.status(200).send({
        success: true,
        sessions: req.sessions,
        message: "Topic successfully updated and approved",
      });
    }
  } catch (error) {
    return res.status(500).send({ success: false, message: error.message });
  }
};

exports.studentProject = async (req, res) => {
  try {
    const departments = await Department.findAll({ where: { active: true } });
    const activeSession = await Session.findOne({
      where: { status: true },
      raw: true,
    });
    let [projectInprogress] =
      await sequelize.query(`SELECT count(project_submissions.id) AS projectInprogress FROM project_submissions 
        INNER JOIN project_topics ON project_submissions.project_id  = project_topics.id 
        WHERE document_category_id <> '7' AND lecturer_user_id = '${req.user.id}' AND project_submissions.session_id =  '${activeSession.id}' `);

    let [projectRejected] =
      await sequelize.query(`SELECT count(project_submissions.id) AS projectInprogress FROM project_submissions 
        INNER JOIN project_topics ON project_submissions.project_id  = project_topics.id 
        WHERE project_submissions.active = '0' AND lecturer_user_id = '${req.user.id}' AND project_submissions.session_id =  '${activeSession.id}' `);

    let [projectCompleted] =
      await sequelize.query(`SELECT count(project_submissions.id) AS projectInprogress 
        FROM project_submissions 
        INNER JOIN project_topics ON project_submissions.project_id = project_topics.id
        WHERE document_category_id = '7' AND lecturer_user_id = '${req.user.id}' AND project_submissions.session_id =  '${activeSession.id}'
        `);
    let projects = [];
    const topics = await ProjectTopic.findAll({
      where: {
        lecturer_user_id: req.user.id,
        active: true,
        is_approved: true,
        session_id: activeSession.id,
      },
      include: [
        {
          model: ProjectSubmission,
          include: [
            {
              model: DocumentCategory,
              attributes: ["name"],
            },
            {
              model: ProjectTopic,
              include: [
                {
                  model: User,
                  as: "student",
                  attributes: ["username"],
                  include: [
                    {
                      model: Profile,
                      attributes: ["firstname", "lastname", "middlename"],
                      include: [
                        {
                          model: Level,
                          attributes: ["name"],
                        },
                        {
                          model: StudyMode,
                          attributes: ["name"],
                        },
                      ],
                    },
                  ],
                },
              ],
            },
          ],
        },
      ],
    });
    for (const topic of topics) {
      projects = [...projects, ...topic.project_submissions];
    }
    return res.render("staff/project.ejs", {
      error: false,
      user: req.user,
      departments,
      projectInprogress,
      projectCompleted,
      projectRejected,
      projects: JSON.parse(JSON.stringify(projects)),
      sessions: req.sessions,
    });
  } catch (error) {
    console.log(error);
    return res.render("staff/project.ejs", {
      user: req.user,
      error: error.message,
      departments: [],
      projectInprogress: 0,
      projectCompleted: 0,
      projectRejected: 0,
      projects: [],
      sessions: req.sessions,
    });
  }
};
exports.ppoDashboard = async (req, res) => {
  try {
    const { matricNumber, status, page = 1 } = req.query;
    const perPage = 10; // Number of items per page
    const offset = (page - 1) * perPage;

    const departments = await Department.findAll({ where: { active: true } });
    const activeSession = await Session.findOne({
      where: { status: true },
      raw: true,
    });
    console.log(activeSession)
    // Default filters
    const baseWhere = { ppo_id: req.user.id, session_id: activeSession.id };
    const projectWhere = {
      session_id: activeSession.id,
      document_category_id: 7,
      active: true,
      is_approved: true,
    };

    // Apply status filter
    if (status === "pending") {
      projectWhere.is_approved = false;
      projectWhere.active = true;
    } else if (status === "rejected") {
      projectWhere.is_approved = false;
      projectWhere.active = false;
    } else if (status === "printed") {
      projectWhere.is_approved = true;
      projectWhere.active = true;
    }

    // Build the user query
    const userWhere = { ...baseWhere };
    if (matricNumber) {
      userWhere.username = { [Op.like]: `%${matricNumber}%` };
    }

    // Get counts
    const [
      totalDocument,
      pendingDocuments,
      rejectedDocument,
      completedDocument,
      totalFilteredCount,
    ] = await Promise.all([
      User.count({ where: { ...baseWhere } }),
      ProjectSubmission.count({
        where: { ...baseWhere, is_approved: false, active: true },
      }),
      ProjectSubmission.count({
        where: { ...baseWhere, is_approved: false, active: false },
      }),
      User.count({
        where: { ...baseWhere, is_printed: true },
      }),
      User.count({ where: userWhere }),
    ]);

    // Calculate pagination values
    const totalPages = Math.ceil(totalFilteredCount / perPage);
    const currentPage = Math.min(Math.max(parseInt(page, 10)) || 1, totalPages);
    
    // Build query string for pagination links (excluding page parameter)
    const queryParams = new URLSearchParams();
    if (matricNumber) queryParams.append('matricNumber', matricNumber);
    if (status) queryParams.append('status', status);
    const queryString = queryParams.toString() ? `&${queryParams.toString()}` : '';

    // Fetch filtered projects with pagination
    const users = await User.findAll({
      where: userWhere,
      attributes: ["id", "username", "email", "phone", "is_printed"],
      include: [
        {
          model: Profile,
          attributes: ["firstname", "lastname", "middlename"],
          include: [
            { model: Level, attributes: ["name"] },
            { model: StudyMode, attributes: ["name"] },
          ],
        },
        {
          model: ProjectTopic,
          where: {
            active: true,
            is_approved: true,
            session_id: activeSession.id,
          },
          attributes: ["id", "topic", "createdAt"],
          include: [
            {
              model: ProjectSubmission,
              required: false,
              where: { ...projectWhere },
            },
          ],
        },
      ],
      limit: perPage,
      offset: offset,
      order: [['username', 'ASC']],
    });
 
    return res.render("staff/ppo-dashboard.ejs", {
      error: false,
      user: req.user,
      departments,
      pendingDocuments: totalDocument - completedDocument,
      rejectedDocument,
      completedDocument,
      totalDocument,
      projects: JSON.parse(JSON.stringify(users)),
      sessions: req.sessions,
      matricNumber,
      status,
      // Pagination variables
      currentPage,
      totalPages,
      queryString,
    });
  } catch (error) {
    console.log(error);
    return res.render("staff/ppo-dashboard.ejs", {
      user: req.user,
      error: error.message,
      departments: [],
      pendingDocuments: 0,
      rejectedDocument: 0,
      completedDocument: 0,
      totalDocument: 0,
      projects: [],
      sessions: req.sessions,
      status: req.query.status || "all",
      // Default pagination values on error
      currentPage: 1,
      totalPages: 1,
      queryString: '',
    });
  }
};
exports.exportPpoDashboardCSV = async (req, res) => {
  try {
    const { matricNumber, status } = req.query;

    const activeSession = await Session.findOne({
      where: { status: true },
      raw: true,
    });

    const baseWhere = { ppo_id: req.user.id, session_id: activeSession.id };
    const projectWhere = {
      session_id: activeSession.id,
      document_category_id: 7,
      active: true,
      is_approved: true,
    };

    if (status === "pending") {
      projectWhere.is_approved = false;
      projectWhere.active = true;
    } else if (status === "rejected") {
      projectWhere.is_approved = false;
      projectWhere.active = false;
    } else if (status === "printed") {
      projectWhere.is_approved = true;
      projectWhere.active = true;
    }

    const userWhere = { ...baseWhere };
    if (matricNumber) {
      userWhere.username = { [Op.like]: `%${matricNumber}%` };
    }

    const users = await User.findAll({
      where: userWhere,
      attributes: ["username", "email", "phone", "is_printed"],
      include: [
        {
          model: Profile,
          attributes: ["firstname", "lastname", "middlename"],
          include: [
            { model: Level, attributes: ["name"] },
            { model: StudyMode, attributes: ["name"] },
          ],
        },
        {
          model: ProjectTopic,
          where: {
            active: true,
            is_approved: true,
            session_id: activeSession.id,
          },
          attributes: ["topic", "createdAt"],
          include: [
            {
              model: ProjectSubmission,
              required: false,
              where: { ...projectWhere },
            },
          ],
        },
      ],
      order: [['username', 'ASC']],
    });

    // Transform data for CSV
    const records = users.map(user => ({
      MatricNumber: user.username,
      FullName: `${user.Profile?.lastname || ''} ${user.Profile?.firstname || ''} ${user.Profile?.middlename || ''}`.trim(),
      Email: user.email,
      Phone: user.phone,
      Level: user.Profile?.Level?.name || '',
      StudyMode: user.Profile?.StudyMode?.name || '',
      ProjectTopic: user.ProjectTopic?.topic || '',
      Printed: user.is_printed ? "Yes" : "No",
      CreatedAt: user.ProjectTopic?.createdAt?.toISOString().split('T')[0] || '',
    }));

    const json2csv = new Parser();
    const csv = json2csv.parse(records);

    res.header("Content-Type", "text/csv");
    res.attachment("ppo-dashboard-export.csv");
    return res.send(csv);
  } catch (error) {
    console.error(error);
    return res.status(500).send("Failed to export CSV.");
  }
};
// Controller
exports.updatePrintStatus = async (req, res) => {
  const { id, status } = req.body;

  if (!id || !["printed", "rejected", "pending"].includes(status)) {
    return res
      .status(400)
      .json({ success: false, message: "Invalid request." });
  }

  let updateFields = {};

  switch (status) {
    case "printed":
      updateFields = { is_printed: true};
      break;
    case "rejected":
      updateFields = { is_approved: false, active: false };
      break;
    case "pending":
      updateFields = { is_approved: false, active: true };
      break;
  }

  try {
    const result = await User.update(updateFields, {
      where: { id },
    });
    if (result[0] === 0) {
      return res
        .status(404)
        .json({ success: false, message: "Project not found." });
    }
    return res.json({ success: true, message: "Status updated." });
  } catch (error) {
    console.error(error);
    return res.status(500).json({ success: false, message: "Server error." });
  }
};

exports.viewStudentProject = async (req, res) => {
  try {
    const { docId } = req.params;
    const doc = await ProjectSubmission.findOne({
      where: {
        id: docId,
      },
      raw: true,
    });
    res.render("staffViewTemplate.ejs", {
      doc,
      user: req.user,
      error: false,
      sessions: req.sessions,
    });
  } catch (error) {
    console.log(error);
    return res.render("staff/view_project.ejs", {
      user: req.user,
      error: error.message,
      content: "",
    });
  }
};
exports.viewStudentProjectPo = async (req, res) => {
  try {
    const { docId } = req.params;
    const doc = await ProjectSubmission.findOne({
      where: {
        id: docId,
      },
      raw: true,
    });
    res.render("staff/ppo-view-project.ejs", {
      doc,
      user: req.user,
      error: false,
      sessions: req.sessions,
    });
  } catch (error) {
    console.log(error);
    return res.render("staff/view_project.ejs", {
      user: req.user,
      error: error.message,
      content: "",
    });
  }
};
exports.approveDocument = async (req, res) => {
  try {
    const { docId, message, actionType } = req.body;
    let updateTopic;
    const projectSubmission = await ProjectSubmission.findOne({
      where: {
        id: docId,
      },
      raw: true,
    });
    if (actionType === "accept") {
      updateTopic = await ProjectSubmission.update(
        { is_approved: true, active: true, approval_comment: message },
        { where: { id: docId } }
      );
      if (projectSubmission.document_category_id == 7) {
        await Project.update(
          { is_completed: true, active: true },
          { where: { id: projectSubmission.project_id } }
        );
      }
      return res
        .status(200)
        .send({ success: true, message: "Document successfully approved" });
    } else {
      updateTopic = await ProjectSubmission.update(
        { active: false, is_approved: false, approval_comment: message },
        { where: { id: docId } }
      );
      return res
        .status(200)
        .send({ success: true, message: "Document successfully rejected" });
    }
  } catch (error) {
    return res.status(500).send({ success: false, message: error.message });
  }
};
exports.profile = async (req, res) => {
  let type = req.query.type;
  const sessions = await Session.findAll();
  try {
    let user = {};
    let supervisoDetails = {};
    const user_id = req.query.user_id;
    const findUser = await User.findByPk(user_id);
    if (type == "student") {
      user = await Profile.findOne({
        where: { user_id },
        attributes: [
          "user_id",
          "department_id",
          "firstname",
          "lastname",
          "middlename",
          "photo_path",
        ],
        include: [
          {
            model: User,
            attributes: ["username", "email", "phone", "slug"],
          },
          {
            model: StudyMode,
          },
          {
            model: Level,
          },
          {
            model: Department,
            attributes: ["id", "name"],
            include: [
              {
                model: Institute,
                attributes: ["name"],
              },
            ],
          },
        ],
      });
    } else {
      user = await Profile.findOne({
        where: { user_id },
        attributes: [
          "user_id",
          "department_id",
          "firstname",
          "lastname",
          "middlename",
          "photo_path",
        ],
        include: [
          {
            model: User,
            attributes: ["username", "email", "phone", "slug"],
          },
          {
            model: Department,
            attributes: ["id", "name"],
            include: [
              {
                model: Institute,
                attributes: ["name"],
              },
            ],
          },
        ],
      });
    }
    const study_modes = await StudyMode.findAll({});
    const levels = await Level.findAll({});
    var decrypted = CryptoJS.AES.decrypt(findUser.password, key, {
      iv: iv,
    }).toString(CryptoJS.enc.Utf8);
    const supervisoDetailsStudentSupervisor = await StudentSupervisor.findOne({
      where: {
        student_user_id: user_id,
      },
    });
    if (supervisoDetailsStudentSupervisor) {
      supervisoDetails = await Profile.findOne({
        where: {
          user_id: JSON.parse(JSON.stringify(supervisoDetailsStudentSupervisor))
            .lecturer_user_id,
        },
      });
    }
    const supervisors = await Profile.findAll({
      where: { role_id: 6, department_id: user.department_id },
    });
    const logs = [];
    const activityLog = await ActivityLog.findAll({
      where: { causer_id: findUser.id },
      limit: 7,
      order: [["createdAt", "DESC"]],
      attributes: ["createdAt", "description"],
    });
    for (const act of activityLog) {
      const timeAgo = timeSince(act.createdAt);
      logs.push({ description: act.description, createdAt: timeAgo });
    }
    return res.render("staff/profile.ejs", {
      user: req.user,
      subscriptions: req.subscriptions,
      study_modes,
      password: decrypted,
      levels,
      userType: "staff",
      user: findUser,
      supervisoDetails,
      profile: user,
      supervisors,
      activityLog: logs,
      error: false,
      sessions: sessions,
    });
  } catch (error) {
    console.log(error);
    return res.render("staff/profile.ejs", {
      user: req.user,
      subscriptions: req.subscriptions,
      profile: {},
      subscriptions: req.subscriptions,
      error: error.message,
      activityLog: {},
      supervisors: [],
      levels: [],
      study_modes: [],
      supervisoDetails: {},
      userType: "staff",
      activityLog: "",
    });
  }
};
exports.profile2 = async (req, res) => {
  let type = req.query.type;
  const sessions = await Session.findAll();
  try {
    let supervisoDetails = {};
    const user_id = req.query.user_id;
    const findUser = await User.findByPk(user_id);
    const user = await Profile.findOne({
      where: { user_id },
      attributes: [
        "user_id",
        "department_id",
        "firstname",
        "lastname",
        "middlename",
        "photo_path",
      ],
      include: [
        {
          model: User,
          attributes: ["username", "email", "phone", "slug"],
        },
        {
          model: Department,
          attributes: ["id", "name"],
          include: [
            {
              model: Institute,
              attributes: ["name"],
            },
          ],
        },
        {
          model: StudyMode,
        },
        {
          model: Level,
        },
      ],
    });

    const study_modes = await StudyMode.findAll({});
    const levels = await Level.findAll({});
    const supervisoDetailsStudentSupervisor = await StudentSupervisor.findOne({
      where: {
        student_user_id: user_id,
      },
    });
    if (supervisoDetailsStudentSupervisor) {
      supervisoDetails = await Profile.findOne({
        where: {
          user_id: JSON.parse(JSON.stringify(supervisoDetailsStudentSupervisor))
            .lecturer_user_id,
        },
      });
    }
    var decrypted = CryptoJS.AES.decrypt(findUser.password, key, {
      iv: iv,
    }).toString(CryptoJS.enc.Utf8);
    const supervisors = await Profile.findAll({
      where: { role_id: 6, department_id: user.department_id },
    });
    return res.render("staff/change_password.ejs", {
      user: req.user,
      userType: "staff",
      password: decrypted,
      subscriptions: req.subscriptions,
      supervisoDetails,
      user: findUser,
      profile: user,
      supervisors,
      study_modes,
      levels,
      sessions: sessions,
    });
  } catch (error) {
    console.log(error);
    return res.render("staff/profile.ejs", {
      user: req.user,
      profile: {},
      subscriptions: req.subscriptions,
      error: error.message,
      activityLog: {},
      supervisors: [],
      supervisors: [],
      study_modes: [],
      levels: [],
      supervisoDetails: {},
      userType: "staff",
      activityLog: [],
      sessions: sessions,
    });
  }
};
exports.accessManagement = async (req, res) => {
  try {
    const otps = await LibraryAccessCode.findAll({
      order: [["createdAt", "DESC"]],
      include: [
        {
          model: User,
          as: "usedBy",
        },
        {
          model: User,
          as: "createdBy",
        },
      ],
    });
    const totalActiveOTP = await LibraryAccessCode.count({
      where: { status: false },
    });
    const totalInActiveOTP = await LibraryAccessCode.count({
      where: { status: true },
    });
    return res.render("admin/library_access.ejs", {
      error: false,
      success: false,
      subscriptions: req.subscriptions,
      user: req.user,
      otps,
      totalActiveOTP,
      totalInActiveOTP,
      sessions: req.sessions,
    });
  } catch (error) {
    console.log(error);
    return res.render("admin/library_access.ejs", {
      error: error.message,
      subscriptions: req.subscriptions,
      user: req.user,
      otps: [],
      totalActiveOTP: 0,
      totalInActiveOTP: 0,
      sessions: req.sessions,
    });
  }
};
exports.getTopicById = async (req, res) => {
  const { id } = req.params;
  try {
    const topic = await ProjectTopic.findOne({ where: { id }, raw: true });
    return res.status(200).send({ success: true, topic });
  } catch (error) {
    return res.status(500).send({ success: false, message: error.message });
  }
};

exports.allProjects = async (req, res) => {
  try {
    const { username } = req.query;
    let query = {};
    const departments = await Department.findAll({ where: { active: true } });
    let [projectInprogress] =
      await sequelize.query(`SELECT count(project_submissions.id) AS projectInprogress FROM project_submissions 
      INNER JOIN project_topics ON project_submissions.project_id  = project_topics.id 
      WHERE document_category_id <> '7' `);

    let [projectRejected] =
      await sequelize.query(`SELECT count(project_submissions.id) AS projectInprogress FROM project_submissions 
      INNER JOIN project_topics ON project_submissions.project_id  = project_topics.id 
      WHERE project_submissions.active = '0'`);

    let [projectCompleted] =
      await sequelize.query(`SELECT count(project_submissions.id) AS projectInprogress 
      FROM project_submissions 
      INNER JOIN project_topics ON project_submissions.project_id = project_topics.id
      WHERE document_category_id = '7'`);
    let projects = [];

    if (username) {
      const user = await User.findOne({ where: { username }, raw: true });
      query.student_user_id = user.id;
    }
    const activeSession = await Session.findOne({
      where: { status: true },
      attributes: ["id"],
      raw: true,
    });
    const topics = await ProjectTopic.findAll({
      where: {
        active: true,
        is_approved: true,
        ...query,
      },
      limit: 500,
      include: [
        {
          model: ProjectSubmission,
          require: true,
          where: { document_category_id: 7 },
          include: [
            {
              model: DocumentCategory,
              attributes: ["name"],
            },
            {
              model: ProjectTopic,
              include: [
                {
                  model: User,
                  as: "student",
                  attributes: ["username"],
                  include: [
                    {
                      model: Profile,
                      attributes: ["firstname", "lastname", "middlename"],
                    },
                  ],
                },
              ],
            },
          ],
        },
      ],
    });
    for (const topic of topics) {
      projects = [...projects, ...topic.project_submissions];
    }
    return res.render("staff/qa-project.ejs", {
      error: false,
      user: req.user,
      departments,
      projectInprogress,
      projectCompleted,
      projectRejected,
      projects: JSON.parse(JSON.stringify(projects)),
      sessions: req.sessions,
    });
  } catch (error) {
    console.log(error);
    return res.render("admin/projects.ejs", {
      user: req.user,
      error: error.message,
      departments: [],
      projectInprogress: 0,
      projectCompleted: 0,
      projectRejected: 0,
      projects: [],
      sessions: req.sessions,
    });
  }
};
