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 StudentSupervisor = require("../../../models").student_supervisors;
const CurrentSession = require("../../../models").sessions;
const ProjectTopic = require("../../../models").project_topics;
const Session = require("../../../models").sessions;
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 { name } = require("ejs");

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 {
    let err = false;
    let success = false;
    if (req.query.error) err = req.query.error;
    if (req.query.success) success = true;
    const session_id = req.params.session_id;
    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 departments ON users.department_id = departments.id
            INNER JOIN institutes ON departments.institute_id = institutes.id WHERE users.role = 7 AND users.active = true ${queryString} AND session_id = '${session_id}' `);

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

    let [results] = await sequelize.query(`
            SELECT username,firstname,lastname,departments.name 
            AS dept_name,users.active AS isActive,
            institutes.name AS school_name, users.id AS id,
            users.createdAt As createdAt FROM users  
            INNER JOIN departments ON users.department_id = departments.id
            INNER JOIN institutes ON departments.institute_id = institutes.id WHERE users.role = 7 ${queryString} AND session_id = '${session_id}'  ORDER BY createdAt DESC  LIMIT 500`);
    students = results;
    activeStudents = activeStudentsCount[0]["count(*)"];
    inActiveStudents = inActiveStudentsCount[0]["count(*)"];
    const sessions = await Session.findAll({ raw: true });
    return res.render("admin/students.ejs", {
      user: req.user,
      err,
      students,
      activeStudents,
      inActiveStudents,
      departments,
      institutes,
      study_modes,
      levels,
      supervisors,
      success,
      sessions,
      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: [],
      institutes: [],
      study_modes: [],
      levels: [],
      supervisors: [],
    });
  }
};
exports.studentUpload = async (req, res) => {
  try {
    const { actionType } = req.body;
    const session = await CurrentSession.findOne({
      where: { status: true },
    });
    if (actionType === "upload") {
      let allStaudent = [];
      if (req.file == undefined) {
        return res.redirect(
          `/admin/students?error=${"Please Upload Valid file"}`
        );
      }
      let path = "./libaries/" + req.file.filename;
      readXlsxFile(path).then(async (students) => {
        students.shift(); // skip header
        for (const student of students) {
          let lecturer_id = "INVALID";
          let session_id = "NONE";
          if (!student[0]) continue;
          const username = student[0]?.toUpperCase();
          const lastname = student[1];
          const firstname = student[2];
          const middlename = student[3];
          const department_name = student[4];
          const institute_name = student[5];

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

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

          
          let department = {}
          department = await Department.findOne({
            where: { name: department_name },
            raw: true,
          });
          if(!department) continue;
          let institute_id = "";
          if(institute_name){
                      const institute = await Institute.findOne({
            where: { name: institute_name },
            raw: true,
          });
          institute_id = institute?.id
          if(!institute){
            const createdInstitute = await Institute.create({name:institute_name, code:institute_name})
            institute_id = createdInstitute.id
          }
          }
          if(!department){
             department = await Department.create({
              name:department_name,
              institute_id,
              code:"#000000",
              projet_color:"#000000",
              active:true

            })
          }
          const department_id = department ? department.id : "";

          if (session) session_id = session.id;

          const user = await User.create({
            username,
            slug: randomString(20),
            password,
            role: 7,
            firstname,
            lastname,
            middlename,
            role_id: 7,
            second_role: 7,
            session_id,
            department_id,
          });
          await BindingPayment.create({
            user_id: user.id,
            amount: 9000,
            status: "PAID",
            txn_ref: "Auto",
            txn_status: "success",
            txn_channel: "School Portal",
            session_id,
          });
          allStaudent.push(user);
          if (user) {
            await StudentSupervisor.create({
              project_type_id: 1,
              student_user_id: user.id,
            });
          }
        }
        fs.unlink(path, (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 (req.file == undefined) {
        return res.redirect(
          `/admin/students?error=${"Please Upload Valid file"}`
        );
      }
      let path = "./libaries/" + req.file.filename;
      readXlsxFile(path).then(async (students) => {
        students.shift(); // skip header
        for (const student of students) {
          let lecturer_id = "INVALID";
          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 (student && lecturer) {
            await StudentSupervisor.update(
              {
                lecturer_user_id: lecturer.id,
              },
              {
                where: {
                  student_user_id: studentDetail.id,
                },
              }
            );
          }
        }
        fs.unlink(path, (err) => {
          if (err) console.log(err);
          else {
            console.log("\nDeleted file: " + req.file.originalname);
          }
        });
        return res.redirect(`/admin/students?success=true`);
      });
    }
  } catch (error) {
    return res.redirect(
      `/admin/students?error=${
        "File not in correct format: " + req.file.originalname
      }`
    );
  }
};
exports.singleStudentUplod = async (req, res) => {
  try {
    const {
      username,
      lastname,
      firstname,
      middlename,
      supervisor,
      department_id,
    } = req.body;
    let session_id;
    const password = CryptoJS.AES.encrypt(lastname?.toUpperCase(), key, {
      iv: iv,
    }).toString();
    const session = await CurrentSession.findOne({ where: { status: true } });
    if (session) {
      session_id = session.id;
    }
    const user = await User.create({
      username,
      slug: randomString(20),
      password,
      role: 7,
      department_id,
      firstname,
      lastname,
      middlename,
      second_role: 7,
      session_id,
    });

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

      await BindingPayment.create({
        user_id: user.id,
        amount: 9000,
        status: "PAID",
        txn_ref: "Auto",
        txn_status: "success",
        txn_channel: "School Portal",
        session_id,
      });
    }
    return res.redirect(`/admin/students?success=true`);
  } catch (error) {
    return res.redirect(`/admin/students?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 = {};
    const user_id = req.query.user_id;
    const findUser = await User.findByPk(user_id);
    if (type == "student") {
      user = await User.findOne({
        where: { id: user_id },
        attributes: [
          "id",
          "department_id",
          "firstname",
          "lastname",
          "middlename",
          "photo_path",
          "password",
          "username",
        ],
        include: [
          {
            model: Department,
            attributes: ["id", "name"],
            include: [
              {
                model: Institute,
                attributes: ["name"],
              },
            ],
          },
        ],
      });
      user = JSON.parse(JSON.stringify(user));
    } else {
      user = await User.findOne({
        where: { id: user_id },
        attributes: [
          "id",
          "department_id",
          "firstname",
          "lastname",
          "middlename",
          "photo_path",
          "password",
          "username",
        ],
        include: [
          {
            model: Department,
            attributes: ["id", "name"],
            include: [
              {
                model: Institute,
                attributes: ["name"],
              },
            ],
          },
        ],
      });
      user = JSON.parse(JSON.stringify(user));
    }
    const departments = await Department.findAll();
    var decrypted = CryptoJS.AES.decrypt(user.password, key, {
      iv: iv,
    }).toString(CryptoJS.enc.Utf8);
    supervisoDetails = await StudentSupervisor.findOne({
      where: {
        student_user_id: user_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 });
    }

    const sessions = await Session.findAll({ raw: true });
    return res.render("admin/profile.ejs", {
      user: req.user,
      password: decrypted,
      userType: type,
      supervisoDetails,
      profile: user,
      activityLog: logs,
      error: false,
      departments,
      sessions,
    });
  } catch (error) {
    console.log(error);
    return res.render("admin/profile.ejs", {
      user: req.user,
      profile: {},
      subscriptions: req.subscriptions,
      error: error.message,
      activityLog: {},
      supervisors: [],
      levels: [],
      study_modes: [],
      supervisoDetails: {},
      userType: type,
      activityLog: "",
    });
  }
};
exports.profile2 = async (req, res) => {
  let type = req.query.type;
  try {
    let supervisoDetails = {};
    let user = {};
    const user_id = req.query.user_id;
    const findUser = await User.findByPk(user_id);
    user = await User.findOne({
      where: { id: user_id },
      attributes: [
        "id",
        "department_id",
        "firstname",
        "lastname",
        "middlename",
        "photo_path",
        "password",
        "username",
      ],
      include: [
        {
          model: Department,
          attributes: ["id", "name"],
          include: [
            {
              model: Institute,
              attributes: ["name"],
            },
          ],
        },
      ],
    });
    user = JSON.parse(JSON.stringify(user));
    supervisoDetails = await StudentSupervisor.findOne({
      where: {
        student_user_id: user_id,
      },
    });
    var decrypted = CryptoJS.AES.decrypt(findUser.password, key, {
      iv: iv,
    }).toString(CryptoJS.enc.Utf8);
    const departments = await Department.findAll();
    const sessions = await Session.findAll({ raw: true });
    return res.render("admin/change_password.ejs", {
      user: req.user,
      userType: type,
      password: decrypted,
      supervisoDetails,
      user: findUser,
      profile: user,
      departments,
      sessions,
    });
  } catch (error) {
    return res.render("admin/profile.ejs", {
      user: req.user,
      profile: {},
      subscriptions: req.subscriptions,
      error: error.message,
      activityLog: {},
      supervisors: [],
      supervisors: [],
      study_modes: [],
      levels: [],
      supervisoDetails: {},
      userType: type,
      activityLog: [],
    });
  }
};
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 User.findOne({
      where: { id: user_id },
      include: [
        {
          model: Department,
          attributes: ["id", "name", "projet_color"],
          include: [
            {
              model: Institute,
              attributes: ["name", "id"],
            },
          ],
        },
      ],
    });

    user = JSON.parse(JSON.stringify(user));

    // 🔥 Add isUni (username starts with EKSu)
    user.isUni = user.username?.startsWith("EKSU");

    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_id}`;

    const src = await generateReceipt(url);
    // console.log(user)
    return res.render("admin/project-cover.ejs", {
      topic,
      user,
      bodyColor,
      src,
      groupMembers: [],
    });

  } catch (error) {
    console.log(error);

    return res.render("admin/project-cover.ejs", {
      topic: {},
      user: {},
      bodyColor: "",
      src: "",
    });
  }
};
exports.studentUploadGeneral = async (req, res) => {
  try {
    const { department_id, level_id, study_mode_id, actionType } = req.body;
    if (actionType === "upload") {
      let allStaudent = [];
      if (req.file == undefined) {
        return res.redirect(
          `/admin/students?error=${"Please Upload Valid file"}`
        );
      }
      let path = "./libaries/" + req.file.filename;
      readXlsxFile(path).then(async (students) => {
        students.shift(); // skip header
        for (const student of students) {
          let session_id = "NONE";
          if (!student[1]) continue;
          const username = student[1]?.toUpperCase();
          const lastname = student[2]?.toUpperCase();
          const firstname = student[3]?.toUpperCase();
          const middlename = student[4]?.toUpperCase();
          const department = student[5]?.toUpperCase();
          const password = CryptoJS.AES.encrypt(lastname?.toUpperCase(), key, {
            iv: iv,
          }).toString();

          const checkUser = await User.findOne({
            where: { username },
            raw: true,
          });

          if (checkUser) continue;

          const session = await CurrentSession.findOne({
            where: { status: true },
          });
          const departmentInfo = await Department.findOne({
            where: { name: department },
            raw: true,
          });

          if (session) session_id = session.id;

          const user = await User.create({
            username,
            password,
            role: 7,
            firstname,
            lastname,
            middlename,
            department_id: departmentInfo.id,
            session_id,
          });

          await BindingPayment.create({
            user_id: user.id,
            amount: 9000,
            status: "PAID",
            txn_ref: "Auto",
            txn_status: "success",
            txn_channel: "School Portal",
            session_id,
          });
          allStaudent.push(user);
        }
        fs.unlink(path, (err) => {
          if (err) console.log(err);
          else {
            console.log("\nDeleted file: " + req.file.originalname);
          }
        });
        return res.redirect(`/admin/students?success=true`);
      });
    } else {
      if (req.file == undefined) {
        return res.redirect(
          `/admin/students?error=${"Please Upload Valid file"}`
        );
      }
      let path = "./libaries/" + req.file.filename;
      readXlsxFile(path).then(async (students) => {
        students.shift(); // skip header
        for (const student of students) {
          let lecturer_id = "INVALID";
          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 (student && lecturer) {
            await StudentSupervisor.update(
              {
                lecturer_user_id: lecturer.id,
              },
              {
                where: {
                  student_user_id: studentDetail.id,
                },
              }
            );
          }
        }
        fs.unlink(path, (err) => {
          if (err) console.log(err);
          else {
            console.log("\nDeleted file: " + req.file.originalname);
          }
        });
        return res.redirect(`/admin/students?success=true`);
      });
    }
  } catch (error) {
    return res.redirect(
      `/admin/students?error=${
        "File not in correct format: " + req.file.originalname
      }`
    );
  }
};
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 User.findOne({
      where: { id: user_id },
      include: [
        {
          model: Department,
          attributes: ["id", "name", "projet_color"],
          include: [
            {
              model: Institute,
              attributes: ["name", "id"],
            },
          ],
        },
      ],
    });

    user = JSON.parse(JSON.stringify(user));

    // 🔥 NEW PROPERTY: isUni (username starts with EKSu)
    user.isUni = user.username?.startsWith("EKSU");

    const groupMembers = await User.findAll({
      where: { group_id: 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.username}`;

    const src = await generateReceipt(url);

    return res.render("admin/project-cover.ejs", {
      topic,
      user,
      bodyColor,
      src,
      sessions: req.sessions,
      groupMembers,
    });

  } catch (error) {
    console.log(error);

    return res.render("admin/project-cover.ejs", {
      topic: null,
      user: {},
      bodyColor: "",
      src: "",
    });
  }
};

