const db = require('../utils/db.js')


const verifywithrandid = async(req, res, next)=>{
    const {session, classn, department, randid} = req.params
    const verify = `SELECT * FROM students WHERE session = ? AND class = ? AND department = ? AND randid = ?`;
    try {
        db.query(verify, [session, classn, department, randid], (err,result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length ===0) return res.status(400).json({message:'Invalid Student Credentials!'})
            res.status(200).json({message:'found.', data:result[0]})
        })
    } catch (error) {
        next(error)
    }
}

// leter
const letter = async(req, res, next)=>{
    const sql = `
    SELECT l.*, DATE_FORMAT(l.createat, '%d %M %Y') AS create_date, 
           s.name AS student_name, s.fname, s.mname, s.gname, s.gnumber
    FROM leter l
    JOIN students s ON s.randid = l.student_id
    ORDER BY l.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 leter data!'})
            res.status(200).json({message:'found records.', data:result})
        })
    } catch (error) {
        next(error)
    }
}
const insertleter = async(req, res, next)=>{
    const {student_id, getway, amount, number, transaction, trx_time, status} = req.body
    const insertsql = `INSERT INTO leter (student_id, getway, amount, number, transaction, trx_time, status)`;
    try {
    db.query(insertsql, [student_id, getway, amount, number, transaction, trx_time, status], (err,result)=>{
        if(err) return res.status(400).json({message:err})
        res.status(200).json({message:'Saved record.'})
    })
    } catch (error) {
        next(error)
    }
}

module.exports = {verifywithrandid, insertleter, letter}