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

// import controller function.

const authdata = async(req, res, next)=>{
    const id = req.userid
    // here, class, section and department.
    const sql = `SELECT s.*, c.name AS class_name, se.name AS section_name, d.name AS department_name
    FROM students s
    JOIN classes c ON s.class = c.id
    JOIN sections se ON s.section = se.id
    Join departments d ON s.department = d.id
    WHERE s.id = ?`;
    try {
        db.query(sql, [id], (err, ress)=>{
            if(err) return res.status(400).json({message:err})
            res.status(200).json({message:'.', data:ress[0]})
        })
    } catch (error) {
        next(error)
    }
}

const login = async(req, res, next)=>{
    const {phone, studentid} = req.body
    const sql = `SELECT * FROM students WHERE gnumber = ? AND randid = ?`;
    try {
        db.query(sql, [phone, studentid], (err, ress)=>{
            if(err) return res.status(400).json({message:err})
            if(ress.length===0) return res.status(400).json({message:'Invalid Login Credentials!'})
            const result = ress[0]
            // assigned token.
            const token =  Jwt.sign(
                {studentid:result.id, name:result.name, phone:result.gnumber},
                process.env.JWT_SECRET_STUDENT,
                {expiresIn:process.env.JWT_EXPIRES_STUDENT}
            );
            // back response.
            res.status(200).json({message:'authonticate', token:token})
        })
    } catch (error) {
        next(error)
    }
}

// update profile.

// summary data

const summarydata = async(req, res, next)=>{
    const {userid, classid, sectionid, departmentid} = req
    // query for notice
    const notices = `SELECT * FROM notices WHERE classn = ? AND section = ? AND department = ? AND status = ?`
    const attandance = `SELECT COUNT(*) AS total_records, 
        SUM(status = 1) AS total_present, 
        SUM(status = 0) AS total_absent,
        SUM(status = 2) AS total_holiday
        FROM datetime
        WHERE uniqueid = ?`;
    // exam to completed and his assinged, and he id exit on result table.
    const result = `SELECT r.id AS result_id, e.id AS exam_id, e.name AS exam_name
    FROM results r
    JOIN exam e ON r.examid = e.id AND e.status = 2
    WHERE r.classid = ? AND r.sectionid = ? AND r.departmentid = ? AND r.studentid = ?
    GROUP BY r.id
    ORDER BY r.createat DESC`;
    try {
        db.query(notices, [classid, sectionid, departmentid, '2'], (err1, result1)=>{
            if(err1) return res.status(400).json({message:err1})
        db.query(attandance, [userid], (err2, result2)=>{
            if(err2) return res.status(400).json({message:err2})
        db.query(result, [classid, sectionid, departmentid, userid], (err3, result3)=>{
            if(err3) return res.status(400).json({message:err3})
            
            res.status(200).json({message:'found!', notices:result1, attandance:result2[0], results:result3})
        })
        })    
        })
    } catch (error) {
        next(error)
    }
}

// attandance section

const returnyear = async(req, res, next)=>{
    const {userid, classid, sectionid, departmentid} = req;
    const {year} = req.params;
    const sql = `
    SELECT
    DATE_FORMAT(MIN(STR_TO_DATE(datetime, '%d-%m-%Y')), '%Y-%m-%d') AS min_date,
    DATE_FORMAT(MAX(STR_TO_DATE(datetime, '%d-%m-%Y')), '%Y-%m-%d') AS max_date 
    FROM datetime 
    WHERE uniqueid = ? AND classid = ? AND sectionid = ? AND departmentid = ? AND YEAR(STR_TO_DATE(datetime, '%d-%m-%Y')) = ?`;

    try {
        db.query(sql, [userid, classid, sectionid, departmentid, year], (err, result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length===0) return res.status(400).json({message:'empty records!'})
            const data = {
                from:result[0].min_date,
                to:result[0].max_date
            }
            res.status(200).json({message:'found!', data:data})
        })
    } catch (error) {
        next(error)
    }
}

const returnmonth = async(req, res, next)=>{
    // from 01 to 31 inside all data i need to get.
    const {userid, classid, sectionid, departmentid} = req;
    const {year} = req.params; // instead of date.

    // just change date in dynamicly.

    const query = `
    SELECT 
        datetime AS date, status
    FROM datetime
    WHERE uniqueid = ? AND classid = ? AND sectionid = ? AND departmentid = ? AND STR_TO_DATE(datetime, '%d-%m-%Y') 
          BETWEEN STR_TO_DATE(CONCAT(SUBSTRING('${year}', 1, 4), '-', SUBSTRING('${year}', 6, 2), '-01'), '%Y-%m-%d')
          AND LAST_DAY(STR_TO_DATE(CONCAT(SUBSTRING('${year}', 1, 4), '-', SUBSTRING('${year}', 6, 2), '-01'), '%Y-%m-%d'))
    GROUP BY datetime
    ORDER BY STR_TO_DATE(datetime, '%d-%m-%Y')
`;
try {    
        db.query(query, [userid, classid, sectionid, departmentid], (err, result)=>{
        if(err) return res.status(400).json({message:err})
        if(result.length===0) return res.status(400).json({message:'Empty records of this month!'})
        // render this
    // { sl:4, date: "4-1-2024", status: "Present", takenby: "Md. Sakibul" },
        const newdata = []
        let sl = 1;
        result.forEach(element => {
            const formatDate = (dateString) => {
                const [day, month, year] = dateString.split('-');
                const date = new Date(`${year}-${month.padStart(2, '0')}-${day.padStart(2, '0')}`);
                return date.toLocaleDateString('en-US', {
                    weekday: 'long',
                    day: '2-digit',
                    month: 'long'
                });
            };
            const status = element.status=='1' ? 'Present' : element.status=='0' ? 'Absent' : 'Holiday' 
            const cdata = {
                sl:sl++,
                date:formatDate(element.date),
                status:status,
                takenby:'Application'

            }
            newdata.push(cdata)
        });
        // back response
        res.status(200).json({message:'found!', data:newdata,})
        })
} catch (error) {
    next(error)
}
}

module.exports = {authdata, login, summarydata, returnyear, returnmonth}