const Sequelize = require("sequelize");
const User = require("../../../models").users;
const Profile = require("../../../models").profiles;
const Level = require("../../../models").levels;
const StudyMode = require("../../../models").study_modes;
const Department = require("../../../models").departments;
const Institute = require("../../../models").institutes;
const ProjectSubmission = require("../../../models").project_submissions;
const StudentSupervisor = require("../../../models").student_supervisors ;
const CurrentSession = require("../../../models").sessions;
const ProjectTopic = require("../../../models").project_topics;
const BindingPayment = require("../../../models").binding_payments;
const appendDateFilter = require("../../helpers/appendDateFilter");
const env = process.env.NODE_ENV || "development";
// const config = require( '../../../config/config.json')[env];
const { randomString } = require("../../helpers/randomString");
const readXlsxFile = require("read-excel-file/node");
const fs = require("fs");
const sequelize = require("../../../config/connection");
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 qr = require("qrcode");
const OdfelPayment = require("../../../models").odfel_forms;
const generateReceipt = async (url) => {
  return new Promise((resolve, reject) => {
    qr.toDataURL(url, (err, src) => {
      if (err) reject("Error occurred");

      resolve(src);
    });
  });
};
function timeSince(date) {
  var seconds = Math.floor((new Date() - date) / 1000);

  var interval = seconds / 31536000;

  if (interval > 1) {
    return Math.floor(interval) + " years";
  }
  interval = seconds / 2592000;
  if (interval > 1) {
    return Math.floor(interval) + " months";
  }
  interval = seconds / 86400;
  if (interval > 1) {
    return Math.floor(interval) + " days";
  }
  interval = seconds / 3600;
  if (interval > 1) {
    return Math.floor(interval) + " hours";
  }
  interval = seconds / 60;
  if (interval > 1) {
    return Math.floor(interval) + " minutes";
  }
  return Math.floor(seconds) + " seconds";
}

exports.studentReport = async (req, res) => {
  try {
    const session_id = req.params.session_id;
    let err = false;
    let success = false;
    if (req.query.error) err = req.query.error;
    if (req.query.success) success = true;
    const {
      startDate,
      endDate,
      institute_id,
      department_id,
      study_mode_id,
      level_id,
      status,
    } = req.query;
    let students;
    let activeStudents;
    let inActiveStudents;

    const { searchTearm } = req.query;
    let querySearch = {};

    let queryString = "";
    if (startDate && endDate) {
      queryString += `AND (users.createdAt >= '${endDate}' AND users.createdAt <= '${startDate}')`;
    }
    if (institute_id) queryString += ` AND institutes.id = ${institute_id}`;
    if (department_id) queryString += ` AND departments.id = ${department_id}`;
    if (study_mode_id) queryString += ` AND study_modes.id = ${study_mode_id}`;
    if (level_id) queryString += ` AND levels.id = ${level_id}`;
    if (status) queryString += ` AND users.active = ${status}`;

    if (searchTearm) {
      queryString += ` AND users.username LIKE '${searchTearm}%'`;
    }

    const departments = await Department.findAll({});
    const institutes = await Institute.findAll({});
    const study_modes = await StudyMode.findAll({});
    const levels = await Level.findAll({});
    const supervisors = await Profile.findAll({ where: { role_id: 6 } });

    let [activeStudentsCount] =
      await sequelize.query(`SELECT count(*) FROM users
            INNER JOIN profiles ON users.id = profiles.user_id 
            INNER JOIN study_modes ON profiles.study_mode_id = study_modes.id 
            INNER JOIN levels ON profiles.level_id = levels.id 
            INNER JOIN departments ON profiles.department_id = departments.id
            INNER JOIN institutes ON departments.institute_id = institutes.id WHERE users.role = 7 AND users.session_id = '${session_id}' AND users.active = true ${queryString} `);

    let [inActiveStudentsCount] =
      await sequelize.query(`SELECT count(*)   FROM users 
            INNER JOIN profiles ON users.id = profiles.user_id 
            INNER JOIN study_modes ON profiles.study_mode_id = study_modes.id 
            INNER JOIN levels ON profiles.level_id = levels.id 
            INNER JOIN departments ON profiles.department_id = departments.id
            INNER JOIN institutes ON departments.institute_id = institutes.id WHERE users.role = 7 AND users.session_id = '${session_id}' AND  users.active = false ${queryString}`);

    let [results] = await sequelize.query(`
            SELECT user_id,username,firstname,lastname,departments.name 
            AS dept_name,study_modes.name AS study_name, users.active AS isActive,
            users.createdAt As createdAt FROM users  
            INNER JOIN profiles ON users.id = profiles.user_id 
            INNER JOIN study_modes ON profiles.study_mode_id = study_modes.id 
            INNER JOIN levels ON profiles.level_id = levels.id 
            INNER JOIN departments ON profiles.department_id = departments.id
            INNER JOIN institutes ON departments.institute_id = institutes.id WHERE users.role = 7 AND users.session_id = '${session_id}'   ${queryString} ORDER BY createdAt DESC  LIMIT 500`);
    students = results;
    activeStudents = activeStudentsCount[0]["count(*)"];
    inActiveStudents = inActiveStudentsCount[0]["count(*)"];
    return res.render("admin/students.ejs", {
      user: req.user,
      err,
      students,
      activeStudents,
      inActiveStudents,
      departments,
      institutes,
      study_modes,
      levels,
      sessions: req.sessions,
      supervisors,
      success,
      session_id,
    });
  } catch (error) {
    console.log(error);
    return res.render("admin/students.ejs", {
      user: req.user,
      err: error.message,
      success: false,
      students: [],
      activeStudents: 0,
      inActiveStudents: 0,
      departments: [],
      sessions: req.sessions,
      institutes: [],
      study_modes: [],
      levels: [],
      supervisors: [],
    });
  }
};

exports.studentUpload = async (req, res) => {
  let session_id = "";
  const session = await CurrentSession.findOne({ where: { status: true } });

  if (session) {
    session_id = session.id;
  }
  try {
    const { department_id, level_id, study_mode_id, actionType } = req.body;
    if (actionType === "upload") {
      if (req.file == undefined) {
        return res.redirect(
          `/admin/students?error=${"Please Upload Valid file"}`
        );
      }

      const filePath = "./libaries/" + req.file.filename;
      const students = await readXlsxFile(filePath);
      students.shift(); // Skip header

      const allStudents = [];
      for (const student of students) {
        if (!student[0]) continue;

        const username = student[0]?.toUpperCase();
        const lastname = student[1];
        const firstname = student[2];
        const middlename = student[3];
        const supervisor = student[1];
        const password = CryptoJS.AES.encrypt("12345678", key, {
          iv: iv,
        }).toString();

        const lecturer = await User.findOne({
          where: { username: supervisor },
        });
        const session = await CurrentSession.findOne({
          where: { status: true },
        });

        const lecturer_id = lecturer ? lecturer.id : "INVALID";
        const session_id = session ? session.id : "NONE";

        const checkUser = await User.findOne({
          where: { username },
          raw: true,
          attributes: ["id"],
        });

        if (checkUser) continue;

        const user = await User.create({
          username,
          slug: randomString(20),
          password,
          role: 7,
          session_id,
        });
        allStudents.push(user);

        if (user) {
          await Profile.create({
            user_id: user.id,
            department_id,
            level_id,
            study_mode_id,
            // firstname,
            // lastname,
            // middlename,
            role_id: 7,
            session_id,
          });

          await StudentSupervisor.create({
            project_type_id: 1,
            student_user_id: user.id,
            lecturer_user_id: lecturer_id,
            session_id,
          });
        }
      }

      fs.unlink(filePath, (err) => {
        if (err) console.log(err);
        else {
          console.log("\nDeleted file: " + req.file.originalname);
        }
      });

      return res.redirect(`/admin/students/${session_id}?success=true`);
    } else if (actionType === "assign") {
      if (req.file == undefined) {
        return res.redirect(
          `/admin/students/${session_id}?error=${"Please Upload Valid file"}`
        );
      }

      const filePath = "./libaries/" + req.file.filename;
      const students = await readXlsxFile(filePath);
      students.shift(); // Skip header

      for (const student of students) {
        if (!student[0]) continue;

        const username = student[0]?.toUpperCase();
        const supervisor = student[1]?.toUpperCase();
        const studentDetail = await User.findOne({
          where: { username },
          raw: true,
        });
        const lecturer = await User.findOne({
          where: { username: supervisor },
          raw: true,
        });

        if (studentDetail && lecturer) {
          const checkStudentSuperVisor = await StudentSupervisor.findOne({
            where: { student_user_id: studentDetail.id },
            raw: true,
          });
          if (checkStudentSuperVisor) {
            await StudentSupervisor.update(
              { lecturer_user_id: lecturer.id },
              { where: { student_user_id: studentDetail.id } }
            );
          } else {
            await StudentSupervisor.create({
              student_user_id: studentDetail.id,
              lecturer_user_id: lecturer.id,
              project_type_id: 1,
              session_id,
            });
          }
        }
      }

      fs.unlink(filePath, (err) => {
        if (err) console.log(err);
        else {
          console.log("\nDeleted file: " + req.file.originalname);
        }
      });

      return res.redirect(`/admin/students/${session_id}?success=true`);
    } else if (actionType === "ppo") {
      if (req.file == undefined) {
        return res.redirect(
          `/admin/students/${session_id}?error=${"Please Upload Valid file"}`
        );
      }

      const filePath = "./libaries/" + req.file.filename;
      const students = await readXlsxFile(filePath);
      students.shift(); // Skip header

      for (const student of students) {
        if (!student[0]) continue;
        const username = student[0]?.toUpperCase();
        const supervisor = student[1]?.toUpperCase();
        const studentDetail = await User.findOne({
          where: { username },
          raw: true,
        });
        const lecturer = await User.findOne({
          where: { username: supervisor },
          raw: true,
        });

        
        if (studentDetail && lecturer) {
          await User.update({ppo_id: lecturer.id},{where: {id: studentDetail.id}})
          const topic = await ProjectTopic.findOne({
            where: { student_user_id: studentDetail.id, is_approved:true, active:true },
            raw: true,
          });

          if(!topic) continue;
          
          await ProjectSubmission.update({ppo_id: lecturer.id},{where: {project_id: topic.id, document_category_id: 8}})
          
        }
      }
      fs.unlink(filePath, (err) => {
        if (err) console.log(err);
        else {
          console.log("\nDeleted file: " + req.file.originalname);
        }
      });

      return res.redirect(`/admin/students/${session_id}?success=true`);
    } else if (actionType === "binding") {
      if (req.file == undefined) {
        return res.redirect(
          `/admin/students/${session_id}?error=${"Please Upload Valid file"}`
        );
      }

      const filePath = "./libaries/" + req.file.filename;
      const students = await readXlsxFile(filePath);
      students.shift(); // Skip header

      for (const student of students) {
        if (!student[0]) continue;

        const username = student[0]?.toUpperCase();

        const studentDetail = await User.findOne({
          where: { username },
          raw: true,
        });
        if (!studentDetail) continue;

        const checkUser = await BindingPayment.findOne({
          where: { id: studentDetail.id },
          raw: true,
        });
        if (checkUser) continue;

        await BindingPayment.create({
          user_id: studentDetail.id,
          amount: 0,
          txn_ref: "mannualupload",
          session_id,
          txn_status: "success",
        });
      }

      fs.unlink(filePath, (err) => {
        if (err) console.log(err);
        else {
          console.log("\nDeleted file: " + req.file.originalname);
        }
      });

      return res.redirect(`/admin/students/${session_id}?success=true`);
    }
  } catch (error) {
    // console.log(error)
    // return
    return res.redirect(
      `/admin/students/${session_id}?error=${
        error.message + " " + req.file.originalname
      }`
    );
  }
};

exports.singleStudentUplod = async (req, res) => {
  let session_id;
  const session = await CurrentSession.findOne({ where: { status: true } });

  if (session) {
    session_id = session.id;
  }
  try {
    const {
      username,
      lastname,
      firstname,
      middlename,
      study_mode_id,
      level_id,
      supervisor,
      department_id,
    } = req.body;

    const password = CryptoJS.AES.encrypt(lastname?.toUpperCase(), key, {
      iv: iv,
    }).toString();

    const checkUser = await User.findOne({
      where: { username },
      raw: true,
      attributes: ["id"],
    });

    if (checkUser) {
      return res.redirect(
        `/admin/students/${session_id}?error=${"Matric Number already exists"}`
      );
    }

    const user = await User.create({
      username,
      slug: randomString(20),
      password,
      role: 7,
      session_id,
    });

    if (user) {
      await Profile.create({
        user_id: user.id,
        department_id,
        level_id,
        study_mode_id,
        firstname,
        lastname,
        middlename,
        role_id: 7,
        session_id,
      });

      await StudentSupervisor.create({
        project_type_id: 1,
        student_user_id: user.id,
        lecturer_user_id: supervisor,
        session_id,
      });

      return res.redirect(`/admin/students/${session_id}?success=true`);
    } else {
      return res.redirect(
        `/admin/students/${session_id}?error=${"Failed to create user"}`
      );
    }
  } catch (error) {
    return res.redirect(
      `/admin/students/${session_id}?error=${"Error: " + error.message}`
    );
  }
};

exports.getSupervisorByDepartment = async (req, res) => {
  const { department_id } = req.params;
  const supervisors = await Profile.findAll({
    where: { role_id: 6, department_id },
  });
  return res.status(200).send(supervisors);
};
exports.profile = async (req, res) => {
  let type = req.query.type;
  try {
    let user = {};
    let supervisoDetails = {};
    let ppoDetails = {};
    const user_id = req.query.user_id;
    const findUser = await User.findByPk(user_id);
    const activeSession = await CurrentSession.findOne({
      where: { status: true },
      attributes: ["id"],
      raw: true,
    });
    if (type == "student") {
      user = await Profile.findOne({
        where: { user_id },
        attributes: [
          "user_id",
          "department_id",
          "firstname",
          "lastname",
          "middlename",
          "photo_path",
          "session_id",
        ],
        include: [
          {
            model: User,
            attributes: ["username", "email", "phone", "slug", "ppo_id"],
          },
          {
            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 departments = await Department.findAll({});
    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,
        },
      });
    }
    if(user.user.ppo_id){
      ppoDetails = await User.findOne({where:{id:user.user.ppo_id}, raw:true})
    }
    const supervisors = await Profile.findAll({
      where: { role_id: 6, department_id: user.department_id },
    });
    const ppos = await User.findAll({
      where: { second_role: 15},
    });
    const department = await Department.findOne({
      where: { id: user.department_id },
      raw: true,
    });
    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("admin/profile.ejs", {
      user: req.user,
      study_modes,
      password: decrypted,
      departments,
      department,
      sessions: req.sessions,
      levels,
      userType: type,
      user: findUser,
      supervisoDetails,
      ppoDetails,
      ppos,
      profile: user,
      supervisors,
      activityLog: logs,
      activeSession: activeSession.id === user.session_id,
      error: false,
    });
  } catch (error) {
    return res.render("admin/profile.ejs", {
      user: req.user,
      profile: {},
      subscriptions: req.subscriptions,
      error: error.message,
      activityLog: {},
      supervisors: [],
      levels: [],
      sessions: req.sessions,
      study_modes: [],
      supervisoDetails: {},
      userType: type,
      activityLog: "",
      departments: [],
    });
  }
};
exports.profile2 = async (req, res) => {
  let type = req.query.type;
  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 departments = await Department.findAll({});
    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 },
    });
    const department = await Department.findOne({
      where: { id: user.department_id },
      raw: true,
    });

    return res.render("admin/change_password.ejs", {
      user: req.user,
      userType: type,
      password: decrypted,
      supervisoDetails,
      user: findUser,
      profile: user,
      supervisors,
      study_modes,
      department,
      sessions: req.sessions,
      levels,
      departments,
    });
  } catch (error) {
    return res.render("admin/profile.ejs", {
      user: req.user,
      profile: {},
      subscriptions: req.subscriptions,
      error: error.message,
      activityLog: {},
      supervisors: [],
      supervisors: [],
      sessions: req.sessions,
      study_modes: [],
      levels: [],
      supervisoDetails: {},
      userType: type,
      activityLog: [],
      departments: [],
    });
  }
};
exports.studentCover = async (req, res) => {
  const institutes = [
    { id: 1, color: "blue" },
    { id: 2, color: "green" },
    { id: 3, color: "purple" },
    { id: 4, color: "black" },
    { id: 5, color: "red" },
  ];
  try {
    const { user_id } = req.params;
    let user = await Profile.findOne({
      where: { user_id },
      include: [
        {
          model: User,
          attributes: ["username"],
        },
        {
          model: Department,
          attributes: ["id", "name", "projet_color"],
          include: [
            {
              model: Institute,
              attributes: ["name", "id"],
            },
          ],
        },
      ],
    });
    user = JSON.parse(JSON.stringify(user));
    function getColorById(id) {
      const institute = institutes.find((institute) => institute.id == id);
      return institute ? institute.color : null;
    }
    const bodyColor = getColorById(user.department.institute.id);
    const topic = await ProjectTopic.findOne({
      where: {
        student_user_id: user_id,
        is_approved: true,
        active: true,
        project_type_id: "1",
      },
    });
    const url =
      req.headers.host + "/acknownledge-slip" + `?username=${user.user_id}`;
    const src = await generateReceipt(url);
    return res.render("admin/project-cover.ejs", {
      topic,
      user,
      bodyColor,
      src,
      sessions: req.sessions,
      groupMembers: [],
    });
  } catch (error) {
    return res.render("admin/project-cover.ejs", {
      topic,
      user: {},
      bodyColor: "",
      sessions: req.sessions,
      src: "",
    });
  }
};
exports.groupCover = async (req, res) => {
  const institutes = [
    { id: 1, color: "blue" },
    { id: 2, color: "green" },
    { id: 3, color: "purple" },
    { id: 4, color: "black" },
    { id: 5, color: "red" },
  ];
  try {
    const { user_id } = req.params;
    let user = await Profile.findOne({
      where: { user_id },
      include: [
        {
          model: User,
          attributes: ["username", "group_id"],
        },
        {
          model: Department,
          attributes: ["id", "name", "projet_color"],
          include: [
            {
              model: Institute,
              attributes: ["name", "id"],
            },
          ],
        },
      ],
    });
    user = JSON.parse(JSON.stringify(user));
    const groupMembers = await User.findAll({
      where: { group_id: user.user.group_id },
      raw: true,
      attributes: ["username"],
    });
    function getColorById(id) {
      const institute = institutes.find((institute) => institute.id == id);
      return institute ? institute.color : null;
    }
    const bodyColor = getColorById(user.department.institute.id);
    const topic = await ProjectTopic.findOne({
      where: {
        student_user_id: user_id,
        is_approved: true,
        active: true,
        project_type_id: "1",
      },
    });
    const url =
      req.headers.host + "/all-receipt" + `?username=${user.user.username}`;
    const src = await generateReceipt(url);
    return res.render("admin/project-cover.ejs", {
      topic,
      user,
      bodyColor,
      src,
      sessions: req.sessions,
      groupMembers,
    });
  } catch (error) {
    return res.render("admin/project-cover.ejs", {
      topic,
      user: {},
      bodyColor: "",
      src: "",
    });
  }
};
exports.studentOdfelCover = async (req, res) => {
  const institutes = [
    { id: "Institute of Applied Science", color: "blue" },
    { id: "Institute of Environmental Studies", color: "green" },
    { id: "Institute of Finance and Management Studies", color: "purple" },
    {
      id: "Institute of Information and Communication Technology",
      color: "black",
    },
    { id: "Institute of Technology", color: "red" },
  ];
  try {
    const { user_id } = req.params;
    let user = await OdfelPayment.findOne({
      where: { id: user_id },
      raw: true,
    });
    function getColorById(id) {
      const institute = institutes.find((institute) => institute.id == id);
      return institute ? institute.color : null;
    }
    const bodyColor = getColorById(user.institute);
    const url = req.headers.host + "/exit-form/" + `${user.id}`;
    const src = await generateReceipt(url);
    const department = await Department.findOne({
      where: { name: user.department },
      raw: true,
    });

    return res.render("admin/odfel-cover.ejs", {
      user,
      bodyColor,
      src,
      sessions: req.sessions,
      groupMembers: [],
      department,
    });
  } catch (error) {
    return res.render("admin/odfel-cover.ejs", {
      topic,
      user: {},
      bodyColor: "",
      sessions: req.sessions,
      src: "",
      department: {},
    });
  }
};
