const util = require('util');
const db = require('../utils/db.js')
const query = util.promisify(db.query).bind(db);


const listofPayment = async(req, res, next)=>{
    const sql = `SELECT p.*, DATE_FORMAT(p.createat, '%d %M %Y') AS created_date, c.name AS class_name, d.name AS department_name
     FROM payment p
     LEFT JOIN classes c ON p.class_ref = c.id
     LEFT JOIN departments d ON p.department_ref = d.id
     ORDER BY p.createat DESC`;
    try {
        db.query(sql, (err,result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length===0) return res.status(400).json({message:'Not found any Payment!'})
            res.status(200).json({message:'List of payment.', data:result})
        })
    } catch (error) {
        next(error)
    }
}
const reportwithPaymentId = async(req, res, next)=>{
    const {id, payment_for} = req.params
  // Mapping payment_for to table names
  const tableMap = {
    'Session Fee': 'session_fee',
    'Regular Class Fee': 'class_fee',
    'Institution Fee': 'institution_fee',
    'Event Fee': 'event_fee',
    'Exam Fee': 'exam_fee',
    'Other': 'other_fee'
  };
  const table_name = tableMap[payment_for];
  if (!table_name) {
    return res.status(400).json({ message: 'Invalid payment type' });
  }
  try {
    const payment_and__qty = `SELECT p.*, COUNT(s.id) AS total_student, SUM(c.amount) AS collected_amount,
    COUNT(c.id) AS collected_student
    FROM payment p
    LEFT JOIN students s ON p.class_ref = s.class  AND p.department_ref = s.department
    LEFT JOIN ?? c ON p.id = c.payment_id
    WHERE p.id = ?`;
    const sql = `SELECT p.*, DATE_FORMAT(p.createat, '%d %M %Y') AS payment_date, SUM(p.amount) AS present_amount, c.name AS class_name, se.name AS section_name, d.name AS department_name,
    s.roll, s.name AS student_name, s.image AS student_image, s.fname, s.mname, s.gnumber, s.randid
    FROM ?? p
    LEFT JOIN classes c ON p.class_id = c.id
    LEFT JOIN sections se ON p.section_id = se.id
    LEFT JOIN departments d ON p.department_id = d.id
    JOIN students s ON p.student_id = s.id  
    WHERE payment_id = ?
    GROUP BY p.createat DESC`;
    // count how many data are present. this data lenght.
    // sum total amount. sum total amount. 
    // = quantity and amount.
    // total student of this academic information.
    const result = await query(sql, [table_name, id]);
    const basic = await query(payment_and__qty, [table_name, id])

    console.log(basic)
    if(basic?.length===0) return res.status(400).json({message:'Records is empty!'})
    res.status(200).json({ message: 'response ok', data: result, header_data:basic?basic[0]:[]});
  } catch (error) {
    next(error); // Pass to error handler middleware
  }
} 
const selectPayment = async(req, res, next)=>{
    const sql = `SELECT id AS value, name AS label, payment_for FROM payment`;
    try {
        db.query(sql, (err,result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length===0) return res.status(400).json({message:'not found!'})
            res.status(200).json({message:'found', data:result})
        })
    } catch (error) {
        next(error)
    }
}
const initPayment = async(req, res, next)=>{
    const {name, paymentType, class_id, department_id, amount, note, otherCharges, status, created} = req.body
    const classid = class_id?.value
    const departmentid = department_id?.value
    const initsql = `INSERT INTO payment(name, class_ref, department_ref, service_charge, other_charge, payment_note, payment_for, issue_date, status)
    VALUES(?,?,?,?,?,?,?,?,?)`;
    try {
        db.query(initsql, [name, classid, departmentid, amount, otherCharges, note, paymentType, created, status], (err, result)=>{
            if(err) return res.status(400).json({message:err})
            res.status(201).json({message:'Payment is initialized.'})
        });
    } catch (error) {
        next(error)
    }
}

const updatePayment = async(req, res, next)=>{
    const {id, name, service_charge, other_charge, payment_note, payment_for, issue_date, status} = req.body
    const updatesql = `UPDATE payment SET name = ?, service_charge = ?, other_charge = ?, payment_note = ?, payment_for = ?, issue_date = ?, status = ? WHERE id = ?`;
    try {
        db.query(updatesql, [name, service_charge, other_charge, payment_note, payment_for, issue_date, status, id], (err,result)=>{
            if(err) return res.status(400).json({message:err})
            res.status(200).json({message:'Payment is Updated.'})
        })
    } catch (error) {
        next(error)
    }
}

const deletePayment = async(req, res, next)=>{
    const id = req.params.id
    const sql = `DELETE FROM payment WHERE id = ?`;
    try {
        db.query(sql, [id], (err,result)=>{
            if(err) return res.status(400).json({message:err})
            res.status(200).json({message:'Payment is deleted.'})
        })
    } catch (error) {
        next(error)
    }
}

// Reports
const paymentReports = async (req, res, next) => {
  const { payment_id, payment_for } = req.params;

  // Mapping payment_for to table names
  const tableMap = {
    'Session Fee': 'session_fee',
    'Regular Class Fee': 'class_fee',
    'Institution Fee': 'institution_fee',
    'Event Fee': 'event_fee',
    'Exam Fee': 'exam_fee',
    'Other': 'other_fee'
  };

  const table_name = tableMap[payment_for];

  if (!table_name) {
    return res.status(400).json({ message: 'Invalid payment type' });
  }
 // 1. payment id base get single payment table data and get class name and department name. 2. student id base get each student data.
 // 3. default table count all record. 4. default table sum all amount of this.
 // 5. find paid student. 6. find unpaid student . of this class base.
  try {
    const sql = `SELECT * FROM ?? WHERE payment_id = ?`;
    const result = await query(sql, [table_name, payment_id]);

    res.status(200).json({ message: 'response ok', data: result });
  } catch (error) {
    next(error); // Pass to error handler middleware
  }
};
// Cache
/* clear cache payment */


module.exports = {initPayment, selectPayment, reportwithPaymentId, listofPayment, updatePayment, deletePayment, paymentReports}