const db = require('../utils/db.js')
const path = require('path')
const fs = require("fs");

// import controller function

const result = async(req, res, next)=>{
    const {session, exam, classn, section, department, studentid} = req.params
    try {
        const findrandid = `
        SELECT s.*,
        c.id AS classid, c.name AS classname,
        se.id AS sectionid, se.name AS sectionname,
        d.id AS departmentid, d.name AS departmentname
        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.randid = ?`;
        const resultsql = `
        SELECT r.id As result_id, r.session AS result_session, r.examid AS exam_id, r.studentid AS result_studentid,
        e.*, ea.*, COUNT(sa.id) AS total_assigned_subject
        FROM results r
        JOIN exam e ON r.examid = e.id
        JOIN exam_assinged ea ON e.id = ea.examid
        JOIN subject_assigned sa ON r.examid = sa.examid AND r.classid = sa.classid AND r.departmentid = sa.departmentid AND sa.subtype = 1
        WHERE r.session = ? AND r.examid = ? AND r.classid = ? AND r.sectionid = ? AND r.departmentid = ? AND r.studentid = ?`;
    //     var ritemsql = `
    //     SELECT i.*,
    //     sa.subtype AS sa_subtype, sa.marktype AS sa_marktype, sa.fullmark AS sa_fullmark, sa.subjective AS sa_subjective, sa.subj_pass AS sa_subj_pass, sa.objective AS sa_objective, sa.obj_pass AS sa_obj_pass, sa.practicle AS sa_practicle, sa.pra_pass AS sa_pra_pass,
    //     COALESCE(
    //     CONCAT('[', GROUP_CONCAT(
    //         JSON_OBJECT(
    //             'subid', sg.subid,
    //             'subtype', sg.subtype,
    //             'from_num', sg.from_num,
    //             'to_num', sg.to_num,
    //             'conditions', sg.conditions,
    //             'result', sg.result
    //         )
    //     ), ']'), '[]'
    // ) AS grading_items,
    //     su.code AS sub_code, su.name AS sub_name
    //     FROM result_items i
    //     JOIN subject_assigned sa ON i.assigned_id = sa.id
    //     JOIN subject_grading sg ON sa.examid = sg.examid AND sa.classid = sg.classid AND sa.departmentid = sg.departmentid AND sa.subid = sg.subid AND sa.subtype = sg.subtype
    //     JOIN subjects su ON sa.subid = su.id
    //     WHERE i.result_id = ?
    //     GROUP BY su.code
    //     `;
    var ritemsql = `
SELECT 
    i.*,
    sa.subtype AS sa_subtype, 
    sa.marktype AS sa_marktype, 
    sa.fullmark AS sa_fullmark, 
    sa.subjective AS sa_subjective, 
    sa.subj_pass AS sa_subj_pass, 
    sa.objective AS sa_objective, 
    sa.obj_pass AS sa_obj_pass, 
    sa.practicle AS sa_practicle, 
    sa.pra_pass AS sa_pra_pass,

    SUM(sg.result LIKE '%A+%') AS count_a_plus,
    SUM(sg.result LIKE '%A%') AS count_a,
    SUM(sg.result LIKE '%A-%') AS count_minus,
    SUM(sg.result LIKE '%B%') AS count_b,
    SUM(sg.result LIKE '%C%') AS count_c,
    SUM(sg.result LIKE '%D%') AS count_d,

    COALESCE(
        CONCAT('[', GROUP_CONCAT(
            JSON_OBJECT(
                'subid', sg.subid,
                'subtype', sg.subtype,
                'from_num', sg.from_num,
                'to_num', sg.to_num,
                'conditions', sg.conditions,
                'result', sg.result
            )
        ), ']'), '[]'
    ) AS grading_items,

    su.code AS sub_code, 
    su.name AS sub_name

FROM result_items i
JOIN subject_assigned sa 
    ON i.assigned_id = sa.id
JOIN subject_grading sg 
    ON sa.examid = sg.examid 
    AND sa.classid = sg.classid 
    AND sa.departmentid = sg.departmentid 
    AND sa.subid = sg.subid 
    AND sa.subtype = sg.subtype
JOIN subjects su 
    ON sa.subid = su.id

WHERE i.result_id = ?

GROUP BY su.code
`;
    
    db.query(findrandid, [studentid], (errfind, studentress)=>{
            if(errfind) return res.status(400).json({message:'Server error! please try later.'})
            if(studentress.length===0) return res.status(400).json({message:'Invalid student ID!'})
            // check result field. extends exam_assigned and exam table.
            // then get restul items and sub_assign and sub grading. to result id throw.
            const uid = studentress[0].id
            db.query(resultsql, [session, exam, classn, section, department, uid], (errsql, result)=>{
                if(errsql) return res.status(400).json({message:'Cannot find your result!'})
            if(result.length==0 || result[0].exam_id==null) return res.status(400).json({
                message:'Cannot find your result!'
            })
              const rid = result[0].result_id; // result id.
                const examid = result[0].exam_id; // result id.
                const esql = `SELECT isgrading FROM exam_assinged WHERE examid = ?`;
                console.log(`exam id: ${examid}`)
                // check this
                db.query(esql, [examid], (thiserr, thisress)=>{
                    const find = thisress[0].isgrading;
                    if(find=='0'){
                        // number
                        ritemsql = `
        SELECT i.*,
        sa.subtype AS sa_subtype, sa.marktype AS sa_marktype, sa.fullmark AS sa_fullmark, sa.subjective AS sa_subjective, sa.subj_pass AS sa_subj_pass, sa.objective AS sa_objective, sa.obj_pass AS sa_obj_pass, sa.practicle AS sa_practicle, sa.pra_pass AS sa_pra_pass,
        su.code AS sub_code, su.name AS sub_name
        FROM result_items i
        JOIN subject_assigned sa ON i.assigned_id = sa.id
        JOIN subjects su ON sa.subid = su.id
        WHERE i.result_id = ?
        GROUP BY su.code
        `;
                    }
                    // here all code.
                    // items, sub assigned and sub grading.
                        db.query(ritemsql, [rid], (eritem, items)=>{
                        if(eritem) return res.status(400).json({message:eritem})
                        // * find result_item length, 2. subject assigned leave optional subject count.
                        if(items.length===0) return res.status(400).json({message:'Result item is empty!'})
                        // check exam subject required default genarel sub. assgined into result_items > =
                        
                        if(items.length >= Number(result[0].total_assigned_subject)){
                            // sub requirment is true, just leave optional subject requirment.
                            res.status(200).json({message:'record found!', studentinfo:studentress[0], examinfo:result[0], resultinfo:items,})
                        }else{
                            // false
                            return res.status(400).json({message:'You have not attend in all exam!',})
                        }
                        // *** Menual exam sub Requriement ****/
                        // const checkreq = result[0].subrequired;
                        // // check menual exam subject requirement.
                        // if(Number(checkreq) == items.length){
                        // res.status(200).json({message:'record found!', studentinfo:studentress[0], examinfo:result[0], resultinfo:items,})
                        // }else{
                        //     return res.status(400).json({message:'You have not attend in all exam!',})
                        // }
                        // end sub requirement.
                    })
                })

                // check this
         
            })
        })
        
    } catch (error) {
        next(error)
    }
}

const resultwithid = async(req, res, next)=>{
    const resultid = req.params.withresultid;
    const studentid = req.params.withstudentid;
    // required studentid and resultid.
    const findstudent = `
    SELECT s.*,
    c.id AS classid, c.name AS classname,
    se.id AS sectionid, se.name AS sectionname,
    d.id AS departmentid, d.name AS departmentname
    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 = ?`;
    const resultsql = `
    SELECT r.id As result_id, r.session AS result_session, r.examid AS exam_id, r.studentid AS result_studentid,
    e.*, ea.*, COUNT(sa.id) AS total_assigned_subject
    FROM results r
    JOIN exam e ON r.examid = e.id
    JOIN exam_assinged ea ON e.id = ea.examid
    JOIN subject_assigned sa ON r.examid = sa.examid AND r.classid = sa.classid AND r.departmentid = sa.departmentid AND sa.subtype = 1
    WHERE r.id = ?`;
    // check exam type
    var ritemsql = `
    SELECT i.*,
    sa.subtype AS sa_subtype, sa.marktype AS sa_marktype, sa.fullmark AS sa_fullmark, sa.subjective AS sa_subjective, sa.subj_pass AS sa_subj_pass, sa.objective AS sa_objective, sa.obj_pass AS sa_obj_pass, sa.practicle AS sa_practicle, sa.pra_pass AS sa_pra_pass,
    COALESCE(
    CONCAT('[', GROUP_CONCAT(
        JSON_OBJECT(
            'subid', sg.subid,
            'subtype', sg.subtype,
            'from_num', sg.from_num,
            'to_num', sg.to_num,
            'conditions', sg.conditions,
            'result', sg.result
        )
    ), ']'), '[]'
) AS grading_items,
    su.code AS sub_code, su.name AS sub_name
    FROM result_items i
    JOIN subject_assigned sa ON i.assigned_id = sa.id
    JOIN subject_grading sg ON sa.examid = sg.examid AND sa.classid = sg.classid AND sa.departmentid = sg.departmentid AND sa.subid = sg.subid AND sa.subtype = sg.subtype
    JOIN subjects su ON sa.subid = su.id
    WHERE i.result_id = ?
    GROUP BY su.code
    `;
    try {
        // get request with resultid.
        db.query(findstudent, [studentid], (er,re)=>{
            if(er) return res.status(400).json({message:'Server error!'})
            if(re.length===0) return res.status(400).json({message:'Invalid student ID!'})

        db.query(resultsql, [resultid], (err, result)=>{
            if(err) return res.status(400).json({message:err})
        if(result.length===0 || result[0].exam_id==null) return res.status(400).json({message:'Cannot find student result!'})
          const examid = result[0].exam_id; // result id.
          const esql = `SELECT isgrading FROM exam_assinged WHERE examid = ?`;
          db.query(esql, [examid], (thiserr, thisress)=>{
            const find = thisress[0].isgrading;
            if(find=='0'){
                // number
                ritemsql = `
    SELECT i.*,
    sa.subtype AS sa_subtype, sa.marktype AS sa_marktype, sa.fullmark AS sa_fullmark, sa.subjective AS sa_subjective, sa.subj_pass AS sa_subj_pass, sa.objective AS sa_objective, sa.obj_pass AS sa_obj_pass, sa.practicle AS sa_practicle, sa.pra_pass AS sa_pra_pass,
    su.code AS sub_code, su.name AS sub_name
    FROM result_items i
    JOIN subject_assigned sa ON i.assigned_id = sa.id
    JOIN subjects su ON sa.subid = su.id
    WHERE i.result_id = ?
    GROUP BY su.code
    `;
            }
            // here is a code.
             // items, sub assigned and sub grading.
            db.query(ritemsql, [resultid], (eritem, items)=>{
            if(eritem) return res.status(400).json({message:eritem})
        
            if(items.length===0) return res.status(400).json({message:'Result item is empty!'})
                if(items.length >= Number(result[0].total_assigned_subject)){
                    // sub requirment is true, just leave optional subject requirment.
                    res.status(200).json({message:'record found!', studentinfo:re[0], examinfo:result[0], resultinfo:items,})
                }else{
                    // false
                    return res.status(400).json({message:'Student not attend in all exam!',})
                }
            // const checkreq = result[0].subrequired;
            // // check exam subject requirement. qr code.
            // if(Number(checkreq) == items.length){
            // res.status(200).json({message:'record found!', studentinfo:re[0], examinfo:result[0], resultinfo:items,})
            // }else{
            //     return res.status(400).json({message:'Student not attend in all exam!',})
            // }
        })
            // here is a code.
          });
       
     
        })
        })
    } catch (error) {
        next(error)
    }
}


const results = async(req, res, next)=>{
    
    try {
    const result = `
    SELECT r.id AS resultid, r.session AS result_session, r.studentid AS student_id,
    s.roll AS student_roll, s.name AS student_name, s.image AS s_image , s.gendar AS gendar, s.gnumber AS gnumber, s.address AS address, s.randid AS random_id,
    c.id AS class_id, c.name AS class_name,
    se.id AS section_id, se.name AS section_name,
    d.id AS department_id, d.name AS department_name,
    COUNT(i.id) AS total_subjects,
    e.id AS exam_id, e.name AS exam_name
    FROM results  r
    LEFT JOIN result_items i ON r.id = i.result_id
    JOIN students s ON r.studentid = s.id
    JOIN classes c ON s.class = c.id
    JOIN sections se ON s.section = se.id
    JOIN departments d ON s.department = d.id
    JOIN exam e ON r.examid = e.id
    GROUP BY resultid
    ORDER BY r.createat DESC`;
    db.query(result, (rerror,rdata)=>{
        if(rerror) return res.status(400).json({message:rerror})
        res.status(200).json({message:'founds data', data:rdata})
    })
    } catch (error) {
        next(error)
    }
}

// entire info.
const entireinfo = async(req, res, next)=>{
    const {exam, classn, section, department} = req.params
    try {
        // subject, student, result. 3 term
        // following class and department subject.
        const subsql = `
        SELECT a.*, s.id AS value, s.code AS sub_code, s.name As label
        FROM subject_assigned a
        JOIN subjects s ON s.id = a.subid
        WHERE a.examid = ? AND a.classid = ? AND  a.departmentid
        ORDER BY s.code
        `;
        db.query(subsql, [exam, classn, department], (suberr, subress)=>{
        if(suberr) return res.status(400).json({message:suberr})
        // following class, section and department student.
        const student = `SELECT id AS value, roll AS roll_no, concat(roll, ' | ', name) AS label FROM students WHERE class = ? AND section = ? AND department = ?`;
        db.query(student, [classn, section, department], (serr, sress)=>{
        if(serr) return res.status(400).json({message:serr})
        // following exam, class, section and department results. Left Join to get student info.
        // this query return all subject of result not maintained exam
    //     const encoderesult = `
    //     SELECT r.id AS id, r.studentid AS studentid, r.avaragemark AS avaragemark,
    //     COALESCE(
    //         CONCAT('[', GROUP_CONCAT(
    //             JSON_OBJECT(
    //                 'id', s2.id,
    //                 'subid', s2.subject_id,
    //                 'assignedid', s2.assigned_id,
    //                 'code', s3.code,
    //                 'name', s3.name,
    //                 'subtype', s3.type,
    //                 'marktype', s4.marktype,
    //                 'fullmark', s4.fullmark,
    //                 'sub', s2.subjective,
    //                 'obj', s2.objective,
    //                 'pra', s2.practical
    //             )
    //         ), ']'), '[]'
    //     ) AS result_items,
    //      s3.code AS sub_code, s3.name AS sub_name, s3.type AS sub_type, 
    //      s4.marktype AS mark_type, s4.fullmark AS full_mark
    //     FROM results r 
    //     LEFT JOIN result_items s2 ON r.id = s2.result_id
    //     LEFT JOIN subjects s3 ON s2.subject_id = s3.id
    //     LEFT JOIN subject_assigned s4 ON s3.id = s4.subid
    //     WHERE r.examid = ? AND r.classid = ? AND r.sectionid = ? AND r.departmentid = ?
    //     GROUP BY r.id`; // find out the student id. this data. then set id.
    // // it required only 
    const encoderesult = `SELECT 
    r.id AS id, 
    r.studentid AS studentid, 
    r.avaragemark AS avaragemark,
    COALESCE(
        (
            SELECT 
                CONCAT('[', GROUP_CONCAT(DISTINCT 
                    JSON_OBJECT(
                        'id', ri.id,
                        'subid', ri.subject_id,
                        'assignedid', ri.assigned_id,
                        'code', s.code,
                        'name', s.name,
                        'subtype', s.type,
                        'marktype', sa.marktype,
                        'fullmark', sa.fullmark,
                        'sub', ri.subjective,
                        'obj', ri.objective,
                        'pra', ri.practical
                    )
                ), ']')
            FROM result_items ri
            LEFT JOIN subjects s ON ri.subject_id = s.id
            LEFT JOIN subject_assigned sa ON sa.subid = s.id AND sa.id = ri.assigned_id
            WHERE ri.result_id = r.id
        ), '[]'
    ) AS result_items
FROM results r
WHERE r.examid = ? AND r.classid = ? AND r.sectionid = ? AND r.departmentid = ?
`;
    const result = `
    SELECT r.id,
    s.roll AS student_roll, s.name AS student_name, s.randid AS random_id,
    COUNT(i.id) AS total_subjects  
    FROM results  r
    LEFT JOIN result_items i ON r.id = i.result_id
    LEFT JOIN students s ON r.studentid = s.id
    WHERE r.examid = ? AND r.classid = ? AND r.sectionid = ? AND r.departmentid = ?
    GROUP BY r.id
    ORDER BY student_roll ASC`;
        db.query(encoderesult, [exam, classn, section, department], (encodeerr, encoderess)=>{
        if(encodeerr) return res.status(400).json({message:encodeerr})
        db.query(result, [exam, classn, section, department], (rerror,rdata)=>{
            if(rerror) return res.status(400).json({message:e})
                const data = {
                    subjectdata:subress.length===0 ? [] : subress,
                    studentdata:sress.length===0 ? [] : sress,
                    encoders:encoderess.length===0 ? [] : encoderess,
                    resultdata: rdata
                }
                res.status(200).json({message:'founds data', data:data})
        })

        });
        });
        });

    } catch (error) {
        next(error)
    }
}

// save
const saveresult = async(req, res, next)=>{
    const {ressdata, subjects, d} = req.body;
    const {resultid, session, examid, classid, sectionid, departmentid, studentid, avaragemark} = ressdata
    try {
        const resultsql = `INSERT INTO results (session, examid, classid, sectionid, departmentid, studentid, avaragemark)
        VALUES(?,?,?,?,?,?,?)`;
        const ritemssql = `INSERT INTO result_items (result_id, subject_id, assigned_id, subjective, objective, practical)
        VALUES(?,?,?,?,?,?)`;
        const chekrssql = `SELECT * FROM results WHERE session = ? AND examid = ? AND classid = ? AND sectionid = ? AND departmentid = ? AND studentid = ?`;
        
        // check
        db.query(chekrssql, [session, examid, classid, sectionid, departmentid, studentid], (checkrserr, checkrsress)=>{
            if(checkrserr) return res.status(400).json({message:checkrserr})
            if(checkrsress.length > 0 ){
                // already exits just update this.
                // # avoid results table, because it already exits.
                // now update result item, of this result id based.
                const result_id = checkrsress[0].id;
                // already set in this id. if id set then create new item of encoding data.
                // RUN
                const updateritem = `UPDATE result_items SET assigned_id = ?, subjective = ?, objective = ?, practical = ? WHERE id = ?`;
                subjects.forEach(element => {
                    if(element.id!=''){
                        // update this.
                        db.query(updateritem, [element.assignedid, element.sub, element.obj, element.pra, element.id])
                    }else{
                        // insert new.
                        db.query(ritemssql, [result_id,element.subid, element.assignedid, element.sub, element.obj, element.pra])
                    }
                });
                // RUN
                // back response.
              return res.status(200).json({message:'result is saved.'})
            }else{
                // insert a new record.
                db.query(resultsql, [session, examid, classid, sectionid, departmentid, studentid, avaragemark], (err2, rsress2)=>{
                    if(err2) return res.status(400).json({message:err2})
                    const resultInsertid = rsress2.insertId;
                    // insert result items.
                    subjects.forEach(element => {
                        // check if exits id, then change to update this record into this id.
                        db.query(ritemssql, [resultInsertid, element.subid, element.assignedid, element.sub, element.obj, element.pra])
                        console.log(element.assignedid)
                    });
                    // back response.
                    res.status(200).json({message:'saved new result.'})
                })
                
            }
        });
    } catch (error) {
        next(error)
    }
}

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

// delete result
const deleteresult = async(req, res, next)=>{
    const resultid = req.params.id
    try {
        const sql = `DELETE FROM result_items WHERE result_id = ?`;
        const self = `DELETE FROM results WHERE id = ?`;
        db.query(sql, [resultid], (err, result)=>{
            if(err) return res.status(400).json({message:'Server error! please try later.'})
            db.query(self, [resultid])
            res.status(200).json({message:'result is successfully deleted.'})
        })
    } catch (error) {
        next(error)
    }
}

// REPORT, duplicate report come here in exam based. as like as resultAssigned. fix this easyly.
const reports = async(req, res, next)=>{
    const {session, exam, classn, section, department} = req.params;
    try {
        const leavesql = `
        SELECT s.id, s.roll AS student_roll, s.name AS student_name, s.image AS simg, s.gendar, s.gname, s.gnumber, s.randid AS student_rand_id, r.studentid
        FROM students s
        LEFT JOIN results r ON s.id = r.studentid AND r.session = ? AND r.examid = ? AND r.classid = ? AND r.sectionid = ? AND r.departmentid = ?
        WHERE s.class = ? AND s.section = ? AND s.department = ?
        GROUP BY s.id
        ORDER BY s.roll
        `;

        // const encoderesult = `
        // SELECT r.id AS id, r.studentid AS studentid, r.avaragemark AS avaragemark,
        // COALESCE(
        //     CONCAT('[', GROUP_CONCAT(
        //         JSON_OBJECT(
        //             'id', s2.id,
        //             'subid', s2.subject_id,
        //             'assignedid', s2.assigned_id,
        //             'code', s3.code,
        //             'name', s3.name,
        //             'subtype', s3.type,
        //             'marktype', s4.marktype,
        //             'fullmark', s4.fullmark,
        //             'subpass', s4.subj_pass,
        //             'objpass', s4.obj_pass,
        //             'prapass', s4.pra_pass,
        //             'sub', s2.subjective,
        //             'obj', s2.objective,
        //             'pra', s2.practical
        //         )
        //     ), ']'), '[]'
        // ) AS result_items,
        //  s3.code AS sub_code, s3.name AS sub_name, s3.type AS sub_type, 
        //  s4.marktype AS mark_type, s4.fullmark AS full_mark,
        //  s.roll AS student_roll, s.name AS student_name, s.image AS simg, s.randid AS rand_id
        // FROM results r 
        // LEFT JOIN result_items s2 ON r.id = s2.result_id
        // LEFT JOIN subjects s3 ON s2.subject_id = s3.id
        // LEFT JOIN subject_assigned s4 ON s3.id = s4.subid
        // JOIN students s ON studentid = s.id
        // WHERE r.session = ? AND r.examid = ? AND r.classid = ? AND r.sectionid = ? AND r.departmentid = ?
        // GROUP BY r.id`; // find out the student id. this data. then set id.
       
        const encoderesult = `
SELECT 
  r.id AS id, 
  r.studentid AS studentid, 
  r.avaragemark AS avaragemark,

  COALESCE(
    (
      SELECT CONCAT('[', GROUP_CONCAT(
        JSON_OBJECT(
          'id', s2.id,
          'subid', s2.subject_id,
          'assignedid', s2.assigned_id,
          'code', s3.code,
          'name', s3.name,
          'subtype', s3.type,
          'marktype', s4.marktype,
          'fullmark', s4.fullmark,
          'subpass', s4.subj_pass,
          'objpass', s4.obj_pass,
          'prapass', s4.pra_pass,
          'sub', s2.subjective,
          'obj', s2.objective,
          'pra', s2.practical
        )
      ), ']')
      FROM result_items s2
      LEFT JOIN subjects s3 ON s2.subject_id = s3.id
      LEFT JOIN subject_assigned s4 ON s3.id = s4.subid AND s4.id = s2.assigned_id
      WHERE s2.result_id = r.id
    ), '[]'
  ) AS result_items,

  s3.code AS sub_code, 
  s3.name AS sub_name, 
  s3.type AS sub_type, 
  s4.marktype AS mark_type, 
  s4.fullmark AS full_mark,
  s.roll AS student_roll, 
  s.name AS student_name, 
  s.image AS simg, 
  s.randid AS rand_id

FROM results r 
LEFT JOIN result_items s2 ON r.id = s2.result_id
LEFT JOIN subjects s3 ON s2.subject_id = s3.id
LEFT JOIN subject_assigned s4 ON s3.id = s4.subid AND s4.id = s2.assigned_id
JOIN students s ON r.studentid = s.id

WHERE 
  r.session = ? AND 
  r.examid = ? AND 
  r.classid = ? AND 
  r.sectionid = ? AND 
  r.departmentid = ?

GROUP BY r.id
`;

        const examinfosql = `
        SELECT e.session AS session, e.name AS exam_name,
        c.name AS class_name,
        s.name AS section_name,
        d.name AS department_name
        FROM exam e
        JOIN classes c ON c.id = ?
        JOIN sections s ON s.id = ?
        JOIN departments d ON d.id = ? 
        WHERE e.id = ?
        `;
        // const serialresult = `
        // SELECT r.id, r.studentid,
        // s.roll AS student_roll, s.name AS student_name, s.image AS simg, s.gendar AS gendar, s.fname AS f_name, s.mname AS m_name, s.gname AS gardian_name, gnumber AS gardian_number, s.randid AS student_rand_id, 
        //  (SUM(COALESCE(i.subjective, 0)) + SUM(COALESCE(i.objective, 0)) + SUM(COALESCE(i.practical, 0))) AS total_marks, COUNT(i.id) AS count_item,
        //  SUM(sa.fullmark) AS full_mark, COUNT(sa.id) AS count_sub
        // FROM results  r
        // LEFT JOIN result_items i ON r.id = i.result_id
        // LEFT JOIN students s ON r.studentid = s.id
        // LEFT JOIN subject_assigned sa ON sa.subid = i.subject_id
        // WHERE r.session = ? AND r.examid = ? AND r.classid = ? AND r.sectionid = ? AND r.departmentid = ?
        // GROUP BY r.id
        // ORDER BY total_marks DESC`;
        const serialresult = `
SELECT 
  r.id, 
  r.studentid,
  s.roll AS student_roll, 
  s.name AS student_name, 
  s.image AS simg, 
  s.gendar AS gendar, 
  s.fname AS f_name, 
  s.mname AS m_name, 
  s.gname AS gardian_name, 
  gnumber AS gardian_number, 
  s.randid AS student_rand_id, 

  (SUM(COALESCE(i.subjective, 0)) + SUM(COALESCE(i.objective, 0)) + SUM(COALESCE(i.practical, 0))) AS total_marks, 
  COUNT(i.id) AS count_item,
  SUM(sa.fullmark) AS full_mark, 
  COUNT(sa.id) AS count_sub

FROM results r
LEFT JOIN result_items i ON r.id = i.result_id
LEFT JOIN students s ON r.studentid = s.id
LEFT JOIN subject_assigned sa ON sa.subid = i.subject_id AND sa.id = i.assigned_id

WHERE 
  r.session = ? AND 
  r.examid = ? AND 
  r.classid = ? AND 
  r.sectionid = ? AND 
  r.departmentid = ?

GROUP BY r.id
ORDER BY total_marks DESC
`;

        const subassignsql = `SELECT COUNT(r.id) AS assignsub_count, SUM(r.fullmark) AS total_assignsub FROM subject_assigned r WHERE r.examid = ? AND r.classid = ? AND r.departmentid = ?`;
        // unatt. 
        // how many sub are found this exam -> sub assigned table
        // and how many item are inserted in -> result_items table    of student.
        // result item have a result id student id. assigned id.
        // full report. gpa and fail sub. final step.
        /*
        - result table -> student id and students table id is not match of following class,section and department.. then it's show.
        :::Reprot 5
        */
        db.query(examinfosql, [classn, section, department, exam], (step1err, step1res)=>{
            if(step1err) return res.status(400).json({message:step1err})
                db.query(serialresult, [session, exam, classn, section, department], async(err, result)=>{
                    if(err) return res.status(400).json({message:err})
                    // encode result
                     const withImages = await Promise.all(
                                result.map(async (rs) => {
                                  try {
                                    if (!rs.simg) {
                                      return { ...rs, imageBase64: null };
                                    }
                                  const imagePath = path.join(__dirname, '../uploads/student', rs.simg); // <- adjust this path
                                  const imageBuffer = fs.readFileSync(imagePath);
                                  const base64 = imageBuffer.toString('base64');
                                    return {
                                      ...rs,
                                      imageBase64: `data:image/jpeg;base64,${base64}`,
                                    };
                                  } catch (err) {
                                    return { ...rs, imageBase64: null };
                                  }
                                })
                              );
                    // encode result
                    db.query(subassignsql, [exam, classn, department], (err2, result2)=>{
                    if(err2) return res.status(400).json({message:err2})
                    db.query(encoderesult, [session, exam, classn, section, department], (encoderr, encoderes)=>{
                    if(encoderr) return res.status(400).json({message:encoderr})
                    db.query(leavesql, [session, exam, classn, section, department, classn, section, department], async(errl, leaveress)=>{
                    if(errl) return res.status(400).json({message:errl})
                    // encoding
                    const withImages2 = await Promise.all(
                        leaveress.map(async (record) => {
                          try {
                            if (!record.simg || record.studentid) {
                              return { ...record, imageBase64: null };
                            }
                          const imagePath = path.join(__dirname, '../uploads/student', record.simg); // <- adjust this path
                          const imageBuffer = fs.readFileSync(imagePath);
                          const base64 = imageBuffer.toString('base64');
                            return {
                              ...record,
                              imageBase64: `data:image/jpeg;base64,${base64}`,
                            };
                          } catch (err) {
                            return { ...record, imageBase64: null };
                          }
                        })
                      );
                    // encoding
                        const data = {
                            examinfo: step1res[0],
                            resultdata:withImages,
                            subassign:result2[0],
                            encoders:encoderes,
                            leave:withImages2
                            }
                            res.status(200).json({message:'success', data})                
                    });

                
                    })
                })
                })
        })


    } catch (error) {
        next(error)
    }
}

module.exports = {result, resultwithid, results, entireinfo, saveresult, itemDelete, deleteresult, reports}