const { where } = require("sequelize");
const Sequelize = require("sequelize");
const User = require("../../../models").users;
const Group = require("../../../models").groups;
const Profile = require("../../../models").profiles;
const ProjectType = require("../../../models").project_types;
const OdfelPayment = require("../../../models").odfel_forms;
const { Op } = require("sequelize");
const Department = require("../../../models").departments;
const Institute = require("../../../models").institutes;
const Packages = require("../../../models").packages;
const Subcriptions = require("../../../models").subscriptions;
const PaystackDetails = require("../../../models").paystack_details;
const paymentHistory = require("../../../models").payment_history;
const BindingPayment = require("../../../models").binding_payments;
const appendDateFilter = require("../../helpers/appendDateFilter");
const env = process.env.NODE_ENV || "development";
const readXlsxFile = require("read-excel-file/node");
const createCsvWriter = require("csv-writer").createObjectCsvWriter;
const fs = require("fs");
const path = require("path");

const sequelize = require("../../../models").sequelize;

// const sequelize = require("../../../config/connection");

exports.paymentReport = async (req, res) => {
  try {
    const {
      startDate,
      endDate,
      institute,
      department_id,
      projectTypeId,
      status,
      paymentRef
    } = req.query;
    const session_id = req.params.session_id;
    let query = {};
    let results;
    let getPayemtHistory;
    let transactionSum;
    let transactionCount;
    if (projectTypeId) query.package_id = projectTypeId;
    if (status) query.txn_status = status;
    if (paymentRef) query.txn_ref = paymentRef;

    let queryDate = {};
    if (startDate && endDate) {
      queryDate = {
        ...appendDateFilter(startDate, endDate, sequelize, "payment_history"),
      };
    }

    const departments = await Department.findAll({});
    const institutes = await Institute.findAll({});
    const projectType = await ProjectType.findAll({});
    transactionCount = await paymentHistory.count({
      where: { txn_status: "success", ...query, ...queryDate, session_id },
    });
    transactionSum = await paymentHistory.findAll({
      where: { txn_status: "success", ...query, ...queryDate, session_id },
      attributes: [
        [Sequelize.fn("sum", Sequelize.col("amount")), "totalAmount"],
      ],
    });
    transactionSum = JSON.parse(JSON.stringify(transactionSum));

    getPayemtHistory = await paymentHistory.findAll({
      where: { ...query, ...queryDate, session_id },
      include: [
        { model: Packages },
        {
          model: Profile,
          include: [
            {
              model: Department,
              include: [
                {
                  model: Institute,
                },
              ],
            },
          ],
        },
      ],
      order: [["createdAt", "DESC"]],
      limit: 2000,
    });
    transactionSum = transactionSum[0].totalAmount;
    if (institute) {
      [transactionCount, metadata] =
        await sequelize.query(`SELECT count(*) FROM payment_history 
            INNER JOIN users  ON payment_history.username = users.username 
            INNER JOIN profiles ON users.id = profiles.user_id 
            INNER JOIN departments ON profiles.department_id = departments.id
            INNER JOIN institutes ON departments.institute_id = institutes.id WHERE institute_id = ${institute} AND txn_status = "success" AND users.session_id = "${session_id}"`);

      [transactionSum, metadata] =
        await sequelize.query(`SELECT sum(amount) FROM payment_history 
            INNER JOIN users  ON payment_history.username = users.username 
            INNER JOIN profiles ON users.id = profiles.user_id 
            INNER JOIN departments ON profiles.department_id = departments.id
            INNER JOIN institutes ON departments.institute_id = institutes.id WHERE institute_id = ${institute} AND txn_status = "success" AND users.session_id = "${session_id}"`);

      [results, metadata] =
        await sequelize.query(`SELECT * FROM payment_history 
            INNER JOIN users  ON payment_history.username = users.username 
            INNER JOIN profiles ON users.id = profiles.user_id 
            INNER JOIN departments ON profiles.department_id = departments.id
            INNER JOIN institutes ON departments.institute_id = institutes.id WHERE institute_id = ${institute} AND users.session_id = "${session_id}"`);
      getPayemtHistory = results;
      transactionCount = transactionCount[0]["count(*)"];
      transactionSum = transactionSum[0]["sum(amount)"];
    }

    if (department_id) {
      [transactionCount, metadata] =
        await sequelize.query(`SELECT count(*) FROM payment_history 
            INNER JOIN users  ON payment_history.username = users.username 
            INNER JOIN profiles ON users.id = profiles.user_id 
            INNER JOIN departments ON profiles.department_id = departments.id
            INNER JOIN institutes ON departments.institute_id = institutes.id WHERE department_id = ${department_id} AND txn_status = "success" AND users.session_id = "${session_id}"`);

      [transactionSum, metadata] =
        await sequelize.query(`SELECT sum(amount) FROM payment_history 
            INNER JOIN users  ON payment_history.username = users.username 
            INNER JOIN profiles ON users.id = profiles.user_id 
            INNER JOIN departments ON profiles.department_id = departments.id
            INNER JOIN institutes ON departments.institute_id = institutes.id WHERE department_id = ${department_id} AND txn_status = "success" AND users.session_id = "${session_id}"`);

      [results, metadata] =
        await sequelize.query(`SELECT * FROM payment_history 
            INNER JOIN users  ON payment_history.username = users.username 
            INNER JOIN profiles ON users.id = profiles.user_id 
            INNER JOIN departments ON profiles.department_id = departments.id
            INNER JOIN institutes ON departments.institute_id = institutes.id WHERE department_id = ${department_id} AND users.session_id = "${session_id}"`);
      getPayemtHistory = results;
      transactionCount = transactionCount[0]["count(*)"];
      transactionSum = transactionSum[0]["sum(amount)"];
    }
    // console.log(getPayemtHistory)
    return res.render("admin/payments.ejs", {
      error: false,
      getPayemtHistory,
      transactionCount,
      transactionSum,
      departments,
      projectType,
      institutes,
      sessions: req.sessions,
      session_id,
      user: req.user,
    });
  } catch (error) {
    return res.render("admin/payments.ejs", {
      user: req.user,
      error: error.message,
      getPayemtHistory: [],
      transactionCount: 0,
      transactionSum: 0,
      departments: [],
      projectType: [],
      sessions: req.sessions,
      institutes: [],
    });
  }
};
exports.downloadPaymentReport = async (req, res) => {
  try {
    const {
      startDate,
      endDate,
      institute,
      department_id,
      projectTypeId,
      status,
    } = req.query;

    const session_id = req.params.session_id;
    let query = {};
    let getPayemtHistory;

    if (projectTypeId) query.package_id = projectTypeId;
    if (status) query.txn_status = status;

    const queryConditions = {
      ...query,
      session_id,
    };

    const queryInclude = [
      {
        model: User,
        include: [
          {
            model: Profile,
            include: [
              {
                model: Department,
                required: department_id ? true : false,
                where: department_id ? { id: department_id } : {},
                include: [
                  {
                    model: Institute,
                    required: institute ? true : false,
                    where: institute ? { id: institute } : {},
                  },
                ],
              },
            ],
          },
        ],
      },
    ];
    getPayemtHistory = await paymentHistory.findAll({
      where: {
        ...queryConditions,
        //  ...appendDateFilter(startDate, endDate, sequelize, "payment_history"),
      },
      include: queryInclude,
      order: [["createdAt", "DESC"]],
      limit: 2,
    });
    console.log(getPayemtHistory.length);
    const csvWriter = createCsvWriter({
      path: "payment_history.csv",
      header: [
        { id: "username", title: "Matric Number" },
        { id: "txn_ref", title: "Payment Reference" },
        { id: "amount", title: "Amount" },
        { id: "createdAt", title: "Date" },
        { id: "txn_status", title: "Status" },
      ],
    });

    await csvWriter.writeRecords(JSON.parse(JSON.stringify(getPayemtHistory)));

    res.setHeader(
      "Content-Disposition",
      "attachment; filename=payment_history.csv"
    );
    res.setHeader("Content-Type", "text/csv");

    const csvFilePath = path.resolve("payment_history.csv");

    const fileStream = fs.createReadStream(csvFilePath);
    fileStream.pipe(res);
  } catch (error) {
    console.log(error);
  }
};

exports.paymentAudit = async (req, res) => {
  try {
    const { startDate, endDate, username } = req.query;
    const session_id = req.params.session_id;
    let query = {};
    let results;
    let getPayemtHistory;
    let transactionSum;
    let transactionCount;
    if (username) query.username = { [Op.startsWith]: username };
    let queryDate = {};
    if (startDate && endDate) {
      queryDate = {
        ...appendDateFilter(startDate, endDate, sequelize, "payment_history"),
      };
    }

    const departments = await Department.findAll({});
    const institutes = await Institute.findAll({});
    const projectType = await ProjectType.findAll({});

    getPayemtHistory = await paymentHistory.findAll({
      where: {
        ...query,
        ...queryDate,
        txn_status: "success",
        package_id: 1,
        session_id,
      },
      include: [
        { model: Packages },
        {
          model: Profile,
          include: [
            {
              model: Department,
              include: [
                {
                  model: Institute,
                },
              ],
            },
          ],
        },
      ],
      order: [["createdAt", "DESC"]],
      limit: 2000,
    });

    return res.render("admin/payment-audit.ejs", {
      error: false,
      getPayemtHistory,
      transactionCount,
      transactionSum,
      departments,
      projectType,
      institutes,
      sessions: req.sessions,
      user: req.user,
      session_id,
    });
  } catch (error) {
    console.log(error);
    return res.render("admin/payment-audit.ejs", {
      user: req.user,
      error: error.message,
      getPayemtHistory: [],
      transactionCount: 0,
      transactionSum: 0,
      departments: [],
      sessions: req.sessions,
      projectType: [],
      institutes: [],
    });
  }
};
exports.verifyPayment = async (req, res) => {
  try {
    const paystackKey = await PaystackDetails.findAll();
    const paystack = require("paystack")(paystackKey[0].LIVE_SECRET_KEY);
    // const paystack = require("paystack")(paystackKey[0].TEST_SECRET_KEY);
    const paystackReference = req.body.reference;

    if (!paystackReference) {
      return res.status(200).send({
        statusCode: "401",
        success: false,
        message: "Error",
        error: "Payment reference is required",
      });
    }

    var paystackCallbackRes;
    const verifyPay = await paystack.transaction
      .verify(paystackReference)
      .then((transaction) => {
        paystackCallbackRes = transaction.data;
      });

    if (paystackCallbackRes?.status != undefined) {
      var findSUb = await Subcriptions.findOne({
        where: {
          payment_id: paystackCallbackRes.reference,
        },
      });
      if (paystackCallbackRes.status === "success" && findSUb === null) {
        const findPaymentHistory = await paymentHistory.findOne({
          where: {
            txn_ref: paystackCallbackRes.reference,
            username: paystackCallbackRes.metadata.matricNumber,
          },
        });

        const updateUser = {
          payment_status: "PAID",
        };
        const updatePaid = await User.update(updateUser, {
          where: {
            username: paystackCallbackRes.metadata.matricNumber,
          },
        });

        var findUser = await User.findOne({
          where: {
            username: paystackCallbackRes.metadata.matricNumber,
          },
        });

        const createSub = await Subcriptions.create({
          user_id: findUser.id,
          payment_id: paystackCallbackRes.reference,
          pakage_id: findPaymentHistory.package_id,
        });

        const updatePayment = {
          txn_desc: paystackCallbackRes.gateway_response,
          txn_status: paystackCallbackRes.status,
          txn_channel: paystackCallbackRes.channel,
        };

        const updateHistory = await paymentHistory.update(updatePayment, {
          where: {
            txn_ref: paystackCallbackRes.reference,
            username: paystackCallbackRes.metadata.matricNumber,
          },
        });

        if (updateHistory && createSub && updatePaid) {
          return res.status(200).send({
            statusCode: "200",
            success: true,
            message: paystackCallbackRes.gateway_response,
          });
        }
      } else {
        const updatePayment = {
          txn_desc: paystackCallbackRes.gateway_response,
          txn_status: paystackCallbackRes.status,
          txn_channel: paystackCallbackRes.channel,
        };

        const updateHistory = await paymentHistory.update(updatePayment, {
          where: {
            txn_ref: paystackCallbackRes.reference,
            username: paystackCallbackRes.metadata.matricNumber,
          },
        });

        if (updateHistory) {
          return res.status(200).send({
            statusCode: "200",
            success: true,
            message: paystackCallbackRes.gateway_response,
          });
        }
      }
    }
  } catch (e) {
    res.status(500).send({
      statusCode: "500",
      success: false,
      message: "Error",
      error: e.message,
    });
  }
};
exports.paymentRequery = async (req, res) => {
  try {
    return res.render("admin/payment_requery.ejs", {
      user: req.user,
      sessions: req.sessions,
      error: false,
    });
  } catch (e) {
    return res.render("admin/payment_requery.ejs", {
      user: req.user,
      sessions: req.sessions,
      error: false,
    });
  }
};

exports.paymentSwap = async (req, res) => {
  try {
    var requery = req.query.requery;
    if (!requery) {
      return res.render("admin/payment_swap.ejs", {
        user: req.user,
        error: false,
        sessions: req.sessions,
        userSwap: [],
      });
    }
    const findUser = await User.findOne({
      where: {
        username: requery,
      },
      raw: true,
    });
    var fecthPackage = await Packages.findAll({});

    var fecthSubcription = await Subcriptions.findAll({
      where: {
        user_id: findUser.id,
      },
      raw: true,
    });

    var eachPakages = [];

    for (const fecthSubcriptions of fecthSubcription) {
      const GetPackages = await Packages.findOne({
        where: {
          id: fecthSubcriptions.pakage_id,
        },
      });

      eachPakages.push({
        ...fecthSubcriptions,
        package_name: GetPackages?.name,
        package: JSON.parse(JSON.stringify(fecthPackage)),
      });
    }
    return res.render("admin/payment_swap.ejs", {
      user: req.user,
      error: false,
      userSwap: eachPakages,
      sessions: req.sessions,
      fecthPackage,
    });
  } catch (e) {
    console.log(e);
    return res.render("admin/payment_swap.ejs", {
      user: req.user,
      error: false,
      sessions: req.sessions,
      userSwap: [],
    });
  }
};

exports.SwapPayment = async (req, res) => {
  const matricNumber = req.body.matricNumber;

  if (!matricNumber) {
    return res.status(200).send({
      statusCode: "200",
      success: false,
      message: "Error",
      error: "Matric number is required",
    });
  }
  try {
    return res.status(200).send({
      statusCode: "200",
      success: true,
      message: "Success",
      sessions: req.sessions,
      url: "/admin/paymentswap?requery=" + matricNumber,
    });
  } catch (error) {
    console.log(error);
    return res.render("admin/payment_swap.ejs", {
      user: req.user,
      error: error.message,
      sessions: req.sessions,
      userSwap: [],
    });
  }
};

exports.SwapPaymentSubcription = async (req, res) => {
  try {
    const user_id = req.body.user_id;
    const package_id = req.body.package_id;
    const sub_id = req.body.sub_id;

    var user = await User.findOne({
      where: {
        id: user_id,
      },
      attributes: ["username"],
      raw: true,
    });
    if (!user.username) {
      return res.status(200).send({
        statusCode: "200",
        success: false,
        message: "Invalid User",
        error: "Invalid User",
      });
    }
    var fecthSubcription = await Subcriptions.findOne({
      where: {
        user_id,
        id: sub_id,
      },
      attributes: ["pakage_id"],
    });

    const updateSub = {
      pakage_id: package_id,
    };
    const updatedSub = await Subcriptions.update(updateSub, {
      where: {
        id: sub_id,
      },
    });
    const updatedPay = await paymentHistory.update(
      { package_id: package_id },
      {
        where: {
          username: user.username,
          package_id: fecthSubcription.pakage_id,
          txn_status: "success",
        },
      }
    );

    if (updatedSub) {
      return res.status(200).send({
        statusCode: "200",
        success: true,
        message: "Payment successfully swap",
      });
    } else {
      return res.status(200).send({
        statusCode: "200",
        success: false,
        message: "Error",
        sessions: req.sessions,
        error: "Payment not updated",
      });
    }
  } catch (e) {
    console.log(e);
    return res.status(200).send({
      statusCode: "200",
      success: false,
      message: e.message,
      sessions: req.sessions,
      error: "An error ocurred",
    });
    return res.render("admin/payment_swap.ejs", {
      user: req.user,
      error: false,
      sessions: req.sessions,
      userSwap: [],
    });
  }
};

exports.bindingPaymentReport = async (req, res) => {
  try {
    const {
      startDate,
      endDate,
      institute,
      department_id,
      projectTypeId,
      status,
    } = req.query;
    let query = {};
    let results;
    let getPayemtHistory;
    let transactionSum;
    let transactionCount;
    if (projectTypeId) query.package_id = projectTypeId;
    if (status) query.txn_status = status;
    const session_id = req.params.session_id;

    let queryDate = {};
    if (startDate && endDate) {
      queryDate = {
        ...appendDateFilter(startDate, endDate, sequelize, "binding_payments"),
      };
    }

    const departments = await Department.findAll({});
    const institutes = await Institute.findAll({});
    const projectType = await ProjectType.findAll({});
    transactionCount = await BindingPayment.count({
      where: { txn_status: "success", ...query, ...queryDate, session_id },
    });
    transactionSum = await BindingPayment.findAll({
      where: { txn_status: "success", ...query, ...queryDate, session_id },
      attributes: [
        [Sequelize.fn("sum", Sequelize.col("amount")), "totalAmount"],
      ],
    });
    transactionSum = JSON.parse(JSON.stringify(transactionSum));

    [getPayemtHistory, metadata] =
      await sequelize.query(`SELECT * FROM binding_payments 
                INNER JOIN users  ON binding_payments.user_id = users.id 
                INNER JOIN profiles ON users.id = profiles.user_id 
                INNER JOIN departments ON profiles.department_id = departments.id
                INNER JOIN institutes ON departments.institute_id = institutes.id WHERE  users.session_id = "${session_id}"  LIMIT 500`);
    transactionSum = transactionSum[0].totalAmount;
    if (institute) {
      [transactionCount, metadata] =
        await sequelize.query(`SELECT count(*) FROM binding_payments 
                    INNER JOIN users  ON binding_payments.user_id = users.id 
                    INNER JOIN profiles ON users.id = profiles.user_id 
                    INNER JOIN departments ON profiles.department_id = departments.id
                    INNER JOIN institutes ON departments.institute_id = institutes.id WHERE institute_id = ${institute} AND txn_status = "success" AND users.session_id = "${session_id}" `);

      [transactionSum, metadata] =
        await sequelize.query(`SELECT sum(amount) FROM binding_payments 
                    INNER JOIN users  ON binding_payments.user_id = users.id 
                    INNER JOIN profiles ON users.id = profiles.user_id 
                    INNER JOIN departments ON profiles.department_id = departments.id
                    INNER JOIN institutes ON departments.institute_id = institutes.id WHERE institute_id = ${institute} AND txn_status = "success" AND users.session_id = "${session_id}" `);

      [results, metadata] =
        await sequelize.query(`SELECT * FROM binding_payments 
                    INNER JOIN users  ON binding_payments.user_id = users.id 
                    INNER JOIN profiles ON users.id = profiles.user_id 
                    INNER JOIN departments ON profiles.department_id = departments.id
                    INNER JOIN institutes ON departments.institute_id = institutes.id WHERE institute_id = ${institute} AND users.session_id = "${session_id}"  `);
      getPayemtHistory = results;
      transactionCount = transactionCount[0]["count(*)"];
      transactionSum = transactionSum[0]["sum(amount)"];
    }

    if (department_id) {
      [transactionCount, metadata] =
        await sequelize.query(`SELECT count(*) FROM binding_payments 
                    INNER JOIN users  ON binding_payments.user_id = users.id 
                    INNER JOIN profiles ON users.id = profiles.user_id 
                    INNER JOIN departments ON profiles.department_id = departments.id
                    INNER JOIN institutes ON departments.institute_id = institutes.id WHERE department_id = ${department_id} AND txn_status = "success" AND users.session_id = "${session_id}" `);

      [transactionSum, metadata] =
        await sequelize.query(`SELECT sum(amount) FROM binding_payments 
                    INNER JOIN users  ON binding_payments.user_id = users.id 
                    INNER JOIN profiles ON users.id = profiles.user_id 
                    INNER JOIN departments ON profiles.department_id = departments.id
                    INNER JOIN institutes ON departments.institute_id = institutes.id WHERE department_id = ${department_id} AND txn_status = "success" AND users.session_id = "${session_id}" `);

      [results, metadata] =
        await sequelize.query(`SELECT * FROM binding_payments 
                    INNER JOIN users  ON binding_payments.user_id = users.id 
                    INNER JOIN profiles ON users.id = profiles.user_id 
                    INNER JOIN departments ON profiles.department_id = departments.id
                    INNER JOIN institutes ON departments.institute_id = institutes.id WHERE department_id = ${department_id} AND users.session_id = "${session_id}" `);
      getPayemtHistory = results;
      transactionCount = transactionCount[0]["count(*)"];
      transactionSum = transactionSum[0]["sum(amount)"];
    }
    return res.render("admin/binding-payment.ejs", {
      error: false,
      getPayemtHistory,
      transactionCount,
      transactionSum,
      departments,
      projectType,
      institutes,
      sessions: req.sessions,
      user: req.user,
      session_id,
    });
  } catch (error) {
    return res.render("admin/binding-payment.ejs", {
      user: req.user,
      error: error.message,
      getPayemtHistory: [],
      transactionCount: 0,
      transactionSum: 0,
      departments: [],
      projectType: [],
      sessions: req.sessions,
      institutes: [],
    });
  }
};
exports.bindingRecord = async (req, res) => {
  try {
    const { searchTearm, institute, department_id } = req.query;
    const session_id = req.params.session_id;
    let getPayemtHistory;
    let query = {};
    let sqlQuery = "";

    if (searchTearm != undefined || searchTearm != null) {
      query.username = searchTearm;
      sqlQuery = `AND username = "${searchTearm}" `;
    }
    [getPayemtHistory, metadata] =
      await sequelize.query(`SELECT username, users.id As id, binding_payments.createdAt As createdAt,status,lastname, firstname FROM binding_payments 
                    INNER JOIN users  ON binding_payments.user_id = users.id 
                    INNER JOIN profiles ON users.id = profiles.user_id 
                    INNER JOIN departments ON profiles.department_id = departments.id
                    INNER JOIN institutes ON departments.institute_id = institutes.id WHERE txn_status = "success" AND users.session_id = "${session_id}" ${sqlQuery} LIMIT 500 `);
    const departments = await Department.findAll({});
    const institutes = await Institute.findAll({});
    if (institute) {
      [transactionCount, metadata] =
        await sequelize.query(`SELECT count(*) FROM binding_payments 
                    INNER JOIN users  ON binding_payments.user_id = users.id 
                    INNER JOIN profiles ON users.id = profiles.user_id 
                    INNER JOIN departments ON profiles.department_id = departments.id
                    INNER JOIN institutes ON departments.institute_id = institutes.id WHERE institute_id = ${institute} AND txn_status = "success" AND users.session_id = "${session_id}"`);

      [transactionSum, metadata] =
        await sequelize.query(`SELECT sum(amount) FROM binding_payments 
                    INNER JOIN users  ON binding_payments.user_id = users.id 
                    INNER JOIN profiles ON users.id = profiles.user_id 
                    INNER JOIN departments ON profiles.department_id = departments.id
                    INNER JOIN institutes ON departments.institute_id = institutes.id WHERE institute_id = ${institute} AND txn_status = "success" AND users.session_id = "${session_id}" `);

      [results, metadata] =
        await sequelize.query(`SELECT username, users.id As id, binding_payments.createdAt As createdAt,status,lastname, firstname FROM binding_payments 
                    INNER JOIN users  ON binding_payments.user_id = users.id 
                    INNER JOIN profiles ON users.id = profiles.user_id 
                    INNER JOIN departments ON profiles.department_id = departments.id
                    INNER JOIN institutes ON departments.institute_id = institutes.id WHERE institute_id = ${institute} AND txn_status = "success" AND users.session_id = "${session_id}" ${sqlQuery}`);
      getPayemtHistory = results;
      transactionCount = transactionCount[0]["count(*)"];
      transactionSum = transactionSum[0]["sum(amount)"];
    }

    if (department_id) {
      [transactionCount, metadata] =
        await sequelize.query(`SELECT count(*) FROM binding_payments 
                    INNER JOIN users  ON binding_payments.user_id = users.id 
                    INNER JOIN profiles ON users.id = profiles.user_id 
                    INNER JOIN departments ON profiles.department_id = departments.id
                    INNER JOIN institutes ON departments.institute_id = institutes.id WHERE department_id = ${department_id} AND txn_status = "success" AND users.session_id = "${session_id}" `);

      [transactionSum, metadata] =
        await sequelize.query(`SELECT sum(amount) FROM binding_payments 
                    INNER JOIN users  ON binding_payments.user_id = users.id 
                    INNER JOIN profiles ON users.id = profiles.user_id 
                    INNER JOIN departments ON profiles.department_id = departments.id
                    INNER JOIN institutes ON departments.institute_id = institutes.id WHERE department_id = ${department_id} AND txn_status = "success" AND users.session_id = "${session_id}" `);

      [results, metadata] =
        await sequelize.query(`SELECT username, users.id As id, binding_payments.createdAt As createdAt,status,lastname, firstname FROM binding_payments 
                    INNER JOIN users  ON binding_payments.user_id = users.id 
                    INNER JOIN profiles ON users.id = profiles.user_id 
                    INNER JOIN departments ON profiles.department_id = departments.id
                    INNER JOIN institutes ON departments.institute_id = institutes.id WHERE department_id = ${department_id} AND users.session_id = "${session_id}" `);
      getPayemtHistory = results;
      transactionCount = transactionCount[0]["count(*)"];
      transactionSum = transactionSum[0]["sum(amount)"];
    }
    const totalPaid = await BindingPayment.count({ where: { status: "PAID" } });
    const totalSubmitted = await BindingPayment.count({
      where: { status: "SUBMITTED", session_id },
    });
    const totalReady = await BindingPayment.count({
      where: { status: "READY", session_id },
    });
    return res.render("admin/binding-record.ejs", {
      error: false,
      getPayemtHistory,
      user: req.user,
      totalPaid,
      totalSubmitted,
      totalReady,
      departments,
      sessions: req.sessions,
      institutes,
      session_id,
    });
  } catch (error) {
    console.log(error);
    return res.render("admin/binding-record.ejs", {
      user: req.user,
      error: error.message,
      getPayemtHistory: [],
      totalPai: 0,
      totalSubmitted: 0,
      totalReady: 0,
      departments: [],
      sessions: req.sessions,
      institutes: [],
    });
  }
};
exports.bindingRecordGroup = async (req, res) => {
  let alert = "";
  let alertError = "";
  try {
    const {
      searchTearm,
      department_id,
      removestudent,
      addStudentMatric,
      leaderMatric,
    } = req.query;
    let getPayemtHistory;
    let sqlQuery = "";
    if (addStudentMatric != undefined || addStudentMatric != null) {
      const leader = await User.findOne({
        where: { username: leaderMatric },
        raw: true,
      });
      if (!leader) {
        alertError = "Invalid Matric Number for leader!";
      }
      const member = await User.findOne({
        where: { username: addStudentMatric },
        raw: true,
      });

      if (!member) {
        alertError = "Invalid Matric Number for Member!";
      }
      if (!leader?.group_id) {
        alertError = "Leader does not belong to any group!";
      }
      if (leader && member && leader.group_id) {
        await User.update(
          { has_group: true, group_id: leader.group_id },
          { where: { username: addStudentMatric } }
        );
        alert = "Student added to Group Successfully!";
      }
    }
    if (removestudent != undefined || removestudent != null) {
      await User.update(
        { has_group: false, group_id: "" },
        { where: { username: removestudent } }
      );
      alert = "Student Removed Successfully!";
    }
    if (searchTearm != undefined || searchTearm != null) {
      const user = await User.findOne({
        where: { username: searchTearm },
        raw: true,
      });
      sqlQuery = `WHERE groups.leader_id = "${user?.id}" `;
    }
    [getPayemtHistory, metadata] =
      await sequelize.query(`SELECT username, departments.name As dept,  users.id As id, groups.createdAt As createdAt,lastname, firstname FROM groups 
                      INNER JOIN users  ON groups.leader_id = users.id 
                      INNER JOIN profiles ON users.id = profiles.user_id 
                      INNER JOIN departments ON profiles.department_id = departments.id ${sqlQuery} LIMIT 500`);
    const departments = await Department.findAll({});
    const institutes = await Institute.findAll({});
    if (department_id) {
      [results, metadata] =
        await sequelize.query(`SELECT username, departments.name As dept,  users.id As id, groups.createdAt As createdAt,lastname, firstname FROM groups 
                        INNER JOIN users  ON groups.leader_id = users.id 
                        INNER JOIN profiles ON users.id = profiles.user_id 
                      INNER JOIN departments ON profiles.department_id = departments.id  WHERE groups.department_id= ${department_id} `);
      getPayemtHistory = results;
    }
    const totalPaid = await BindingPayment.count({ where: { status: "PAID" } });
    const totalSubmitted = await BindingPayment.count({
      where: { status: "SUBMITTED" },
    });
    const totalReady = await BindingPayment.count({
      where: { status: "READY" },
    });
    return res.render("admin/binding-group.ejs", {
      error: false,
      getPayemtHistory,
      user: req.user,
      totalPaid,
      totalSubmitted,
      totalReady,
      departments,
      institutes,
      alert,
      alertError,
      sessions: req.sessions,
    });
  } catch (error) {
    console.log(error);
    return res.render("admin/binding-group.ejs", {
      user: req.user,
      error: error.message,
      getPayemtHistory: [],
      totalPai: 0,
      totalSubmitted: 0,
      totalReady: 0,
      departments: [],
      institutes: [],
      alert,
      sessions: req.sessions,
      alertError,
    });
  }
};
exports.upateBindingStatus = async (req, res) => {
  try {
    const { user_id, status } = req.body;
    await BindingPayment.update({ status }, { where: { id: user_id } });
    return res.status(200).send({ success: true, message: "Status Updated!" });
  } catch (error) {
    return res.status(500).send({ success: false, message: error.message });
  }
};

exports.verifyPaymentBinding = async (req, res) => {
  try {
    const paystackKey = await PaystackDetails.findAll();
    const paystack = require("paystack")(LIVE_SECRET_KEY);

    const paystackReference = req.body.reference;
    let paystackCallbackRes; // Use let instead of var for better scoping
    const verifyPay = await paystack.transaction
      .verify(paystackReference)
      .then((transaction) => {
        paystackCallbackRes = transaction.data;
      });

    if (paystackCallbackRes?.status !== undefined) {
      if (paystackCallbackRes.status === "success") {
        const updatePayment = {
          txn_desc: paystackCallbackRes.gateway_response,
          txn_status: paystackCallbackRes.status,
          txn_channel: paystackCallbackRes.channel,
        };

        // Update the payment details
        await BindingPayment.update(updatePayment, {
          where: {
            txn_ref: paystackCallbackRes.reference,
            user_id: paystackCallbackRes.metadata.user_id,
          },
        });

        // Update the user status
        const updateUser = {
          status: "PAID",
        };
        await BindingPayment.update(updateUser, {
          where: {
            txn_ref: paystackCallbackRes.reference,
            user_id: paystackCallbackRes.metadata.user_id,
          },
        });

        return res.status(200).send({
          statusCode: "200",
          success: true,
          message: paystackCallbackRes.gateway_response,
        });
      } else {
        return res.status(200).send({
          statusCode: "200",
          success: true,
          message: paystackCallbackRes.gateway_response,
        });
      }
    }
  } catch (e) {
    return res.status(500).send({
      statusCode: "500",
      success: false,
      message: "Error",
      error: e.message,
    });
  }
};
exports.createGroup = async (req, res) => {
  try {
    const { groupMembers } = req.body;

    const existingUsers = await User.findAll({
      attributes: ["username", "has_group"],
      where: {
        username: groupMembers,
        has_group: false,
      },
      raw: true,
    });
    for (const groupMember of groupMembers) {
      const existingUsers = await User.findAll({
        attributes: ["username", "has_group"],
        where: {
          username: groupMember,
          has_group: false,
        },
        raw: true,
      });
      if (!existingUsers)
        return res.status(200).json({
          success: false,
          message: `${groupMember} already belong to a group or does not exist`,
        });
    }

    const leader = await User.findOne({
      where: { username: groupMembers[0] },
      include: [{ model: Profile }],
    });

    const transaction = await sequelize.transaction();

    try {
      const group = await Group.create(
        { leader_id: leader.id, department_id: leader.profile.department_id },
        { transaction }
      );

      for (const user of existingUsers) {
        await User.update(
          {
            has_group: true,
            group_id: group.id,
          },
          {
            where: { username: user.username },
            transaction,
          }
        );
      }

      await transaction.commit();
    } catch (error) {
      await transaction.rollback();
      throw error;
    }

    return res.json({
      success: true,
      message: "Group created successfully",
    });
  } catch (error) {
    console.error(error);
    return res.status(200).json({
      success: false,
      message: "An error occurred, please try again later",
    });
  }
};
exports.removeGroup = async (req, res) => {
  try {
    // const
  } catch (error) {
    console.error(error);
    return res.status(200).json({
      success: false,
      message: "An error occurred, please try again later",
    });
  }
};

exports.groupBulkUpload = async (req, res) => {
  const transaction = await sequelize.transaction();
  try {
    if (req.file == undefined) {
      return res.redirect(
        `/admin/students?error=${"Please Upload a Valid file"}`
      );
    }
    const path = "./libaries/" + req.file.filename;

    const students = await readXlsxFile(path);

    students.shift(); // skip header

    for (const student of students) {
      const leader = student[0];
      const members = student.slice(0, 21).filter(Boolean); // Filter out empty cells
      let hasError = false;
      const existingUsers = await User.findAll({
        attributes: ["username", "has_group"],
        where: {
          username: members,
          has_group: false,
        },
        raw: true,
      });

      for (const member of members) {
        const userExists = existingUsers.find(
          (existingUser) => existingUser.username === member
        );
        if (!userExists) {
          hasError = true;
        }
      }
      //? skip groups with issuess
      if (hasError) continue;

      const leaderUser = await User.findOne({
        where: { username: leader },
        include: [{ model: Profile }],
      });

      const group = await Group.create(
        {
          leader_id: leaderUser.id,
          department_id: leaderUser.profile.department_id,
        },
        { transaction }
      );

      for (const user of existingUsers) {
        await User.update(
          {
            has_group: true,
            group_id: group.id,
          },
          {
            where: { username: user.username },
            transaction,
          }
        );
      }
    }

    // Commit the transaction only if there are no errors
    await transaction.commit();
    return res.status(200).json({
      success: true,
      message: `Group(s) Created Successfully!`,
    });
    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) {
    console.log(error);
    await transaction.rollback();
    return res.redirect(
      `/admin/students?error=${"An error occurred while processing the file"}`
    );
  }
};
exports.bindingOdfel = async (req, res) => {
  let alert = "";
  let alertError = "";
  try {
    const { searchTearm, department_id } = req.query;
    let getPayemtHistory;

    const departments = await Department.findAll({});
    const institutes = await Institute.findAll({});
    getPayemtHistory = await OdfelPayment.findAll({
      where: { txn_status: "success" },
      raw: true,
    });
    if (department_id) {
      getPayemtHistory = await OdfelPayment.findAll({
        where: { department: department_id, txn_status: "success" },
        raw: true,
      });
    }
    if (searchTearm) {
      getPayemtHistory = await OdfelPayment.findAll({
        where: { username: searchTearm },
        raw: true,
      });
    }
    return res.render("admin/binding-odfel.ejs", {
      error: false,
      getPayemtHistory,
      user: req.user,
      departments,
      institutes,
      alert,
      alertError,
      sessions: req.sessions,
    });
  } catch (error) {
    console.log(error);
    return res.render("admin/binding-odfel.ejs", {
      user: req.user,
      error: error.message,
      getPayemtHistory: [],
      departments: [],
      institutes: [],
      alert,
      sessions: req.sessions,
      alertError,
    });
  }
};
