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

// import controller function.

const getallinfo = async(req, res, next)=>{
    const {examid, classid, departmentid} = req.body
    // if subtype is 0 then, optional subject name color is depp blue.
    // get last 2 query required is subject type and count general subject and optional subject about this class.
    try {
        const sql1 = `SELECT * FROM exam_assinged WHERE examid = ? AND classid = ? AND departmentid = ? `;
const sql2 = `
SELECT s1.*, s2.id AS sub_id, s2.code AS sub_code, s2.name AS sub_name,
COALESCE(
            CONCAT('[', GROUP_CONCAT(
                JSON_OBJECT(
                    'id', s3.id,
                    'examid', s3.examid,
                    'classid', s3.classid,
                    'departmentid', s3.departmentid,
                    'subid', s3.subid,
                    'subtype', s3.subtype,
                    'from_num', s3.from_num,
                    'to_num', s3.to_num,
                    'conditions', s3.conditions,
                    'result', s3.result
                )
            ), ']'), '[]'
        ) AS allgradelistofthissub
FROM subject_assigned s1
JOIN subjects s2 ON s2.id = s1.subid
LEFT JOIN subject_grading s3 ON s1.examid = s3.examid AND s1.classid = s3.classid AND s1.departmentid = s3.departmentid AND s1.subid = s3.subid
WHERE s1.examid = ? AND s1.classid = ? AND s1.departmentid = ?
GROUP BY s2.code
`;
        const sql3 = `SELECT * FROM subject_grading WHERE examid = ? AND classid = ? AND departmentid = ?`;
        const subject = `SELECT id AS value, code AS sub_code, CONCAT(code, '|', name) AS label, type AS sub_type FROM subjects WHERE class_id = ? AND department_id = ? AND status = ?`;
        db.query(sql1,[examid,classid,departmentid], (err,result)=>{
            if(err) return res.status(400).json({message:err})
            db.query(sql2,[examid,classid,departmentid], (err2,result2)=>{
            if(err2) return res.status(400).json({message:err2})
            // get all subject of following class and department. encoding thid data.
            db.query(subject, [classid, departmentid, '1'], (err4, result4)=>{
                if(err4) return res.status(400).json({message:err4})
                    const data = {
                        examdata:result[0], subjectmarkdata:result2, subjectdata:result4
                    }
                    res.status(200).json({message:'getting',data:data})
            })
            })
        });
    } catch (error) {
        next(error)
    }
}

const easubrequired = async(req, res, next)=>{
    const {examid, classid, departmentid, isgrade, updatevalue} = req.body
    const sql = `INSERT INTO exam_assinged (examid, classid, departmentid, isgrading, subrequired) VALUES(?,?,?,?,?)`; 
    const check = `SELECT * FROM exam_assinged WHERE examid = ? AND classid = ? AND departmentid = ?`;
    const usql = `UPDATE exam_assinged SET subrequired = ? WHERE id = ?`; 
    try {
        db.query(check, [examid, classid, departmentid], (checkerr, checkress)=>{
            if(checkerr) return res.status(400).json({message:err})
            if(checkress.length===0){
                db.query(sql, [examid, classid, departmentid, isgrade, updatevalue])
                return res.status(201).json({message:'saved.'})
            }else{
                const uid = checkress[0].id
                db.query(usql, [updatevalue, uid])
                res.status(200).json({message:'saved.'})
            }
        })
    } catch (error) {
        next(error)
    }
} 

const examassigned = async(req, res, next)=>{
    // edit id is found then, update this.
    try {
        // here is code.
        const {exam, mark, grading} = req.body
        // check already inserted or not.
        const sql = `INSERT INTO exam_assinged (examid, classid, departmentid, isgrading) VALUES(?,?,?,?)`; 
        const sql2 = `INSERT INTO subject_assigned (examid, classid, departmentid, subid, subtype, marktype, fullmark, subjective, subj_pass, objective, obj_pass, practicle, pra_pass)
         VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)`;
        const sql3 = `INSERT INTO subject_grading (examid, classid, departmentid, subid, subtype, from_num, to_num, conditions, result)
         VALUES(?,?,?,?,?,?,?,?,?)`;
        // decode data 1.
        const {examid, classid, departmentid, isgrade} = exam
        // decode data 2.
        const {subid, subtype, marktype, fullmark, subjective, subj_pass, objective, obj_pass, practicle, pra_pass} = mark
        // check
        const check1 = `SELECT * FROM exam_assinged WHERE examid = ? AND classid = ? AND departmentid = ?`;
        const check2 = `SELECT * FROM subject_assigned WHERE examid = ? AND classid = ? AND departmentid = ? AND subid = ?`;
        const check3 = `SELECT * FROM subject_grading WHERE examid = ? AND classid = ? AND departmentid = ? AND subid = ?`; // can i get this id.
        // check
        // decode data 3. parent_id, subid, subtype, from_num, to_num, conditions, result
        db.query(check1, [examid, classid, departmentid], (err, checkres)=>{
            if(checkres.length > 0){
                // edit mode.
                // ignore exam_assigned update.
                db.query(check2, [examid, classid, departmentid, subid], (err, checksub)=>{
                    if(checksub.length===0){
                        // new subject
                        db.query(sql2, [examid,classid,departmentid, subid, subtype, marktype, fullmark, subjective, subj_pass, objective, obj_pass, practicle, pra_pass])
                        // new grading.
                        var c = 0;
                        // rendering this.
                        if(grading.length===0){
                            return res.status(201).json({message:'Subject is successfully assigned.'})
                        }else{
                            grading.forEach(element => {
                                c++
                            db.query(sql3, [examid,classid,departmentid, element.subid, element.subtype, element.from_num, element.to_num, element.conditions, element.result]);
                            });
                            var len = grading.length;
                                res.status(200).json({message:'Subject is not successfully assigned!'})
                            
                        }
                    }else{
                        // update subject.
                         // update subject_assigned.
                        const updatesql2 = `UPDATE subject_assigned SET subtype = ?, marktype = ?, fullmark = ?, subjective = ?, subj_pass = ?, objective = ?, obj_pass = ?, practicle = ?, pra_pass = ?
                        WHERE examid = ? AND classid = ? AND departmentid = ? AND subid = ?`; 
                        db.query(updatesql2, [subtype, marktype, fullmark, subjective, subj_pass, objective, obj_pass, practicle, pra_pass, examid, classid, departmentid, subid]) 
                        // update all grading.
                     var c = 0;
                    // rendering this.
                    if(grading.length===0){
                        return res.status(200).json({message:'Subject is not successfully assigned!'})
                    }else{
                        grading.forEach(element => {
                            // if id is available, then update it.
                            if(!element.id || element.id == ''){
                                // new grading.
                                db.query(sql3, [examid,classid,departmentid, element.subid, element.subtype, element.from_num, element.to_num, element.conditions, element.result]);
                            }else{
                                // old grading.
                                // check again.
                                const updatesql3 = `UPDATE subject_grading SET subtype = ?, from_num = ?, to_num = ?, conditions = ?, result = ? WHERE id = ?`;
                                db.query(updatesql3, [subtype, element.from_num, element.to_num, element.conditions, element.result, element.id]);
                            }
                            });
        
                            res.status(200).json({message:'Subject is successfully assigned!'})
                    }

                        
                    }
                });
               
            }else{
                // insert new record.
                db.query(sql, [examid, classid, departmentid, isgrade], (err, result)=>{
                    if(err) return res.status(401).json({message:err})
                    const pid = result.insertId;
                db.query(sql2, [examid,classid,departmentid, subid, subtype, marktype, fullmark, subjective, subj_pass, objective, obj_pass, practicle, pra_pass], (err2, result2)=>{
                    if(err2) return res.status(401).json({message:err2})
                    var c = 0;
                    // rendering this. 
                    if(grading.length===0){
                        return res.status(200).json({message:'Subject is not successfully assigned!'})
                    }else{
                        grading.forEach(element => {
                            c++
                        db.query(sql3, [examid,classid,departmentid, element.subid, element.subtype, element.from_num, element.to_num, element.conditions, element.result]);
                        });
                        var len = grading.length;
                        if(c === len){
                            return res.status(200).json({message:'Subject is successfully assigned in exam.'})
                        }else{
                            res.status(200).json({message:'Subject is not successfully assigned!'})
                        }
                    }
                   
                })
                })
            }
        })
        
    } catch (error) {
        console.log(error)
    }
}

// remove single grade.
const removesinglegrade = async(req, res, next)=>{
    try {
        const id = req.params.id
        const sql = `DELETE FROM subject_grading WHERE id = ?`;
        db.query(sql, [id], (err, result)=>{
            if(err) return res.status(400).json({message:err})
            res.status(200).json({message:'removed.'})
        })
    } catch (error) {
        next(error)
    }
}
// remove listing subject.
const removeListing = async(req, res, next)=>{
    const {assignedid,examid,classid,departmentid,subid,subtype} = req.params;
    try {
        const d1 = `DELETE FROM subject_assigned WHERE id = ? `;
        const selectd2 = `SELECT id FROM subject_grading WHERE examid = ? AND classid = ? AND departmentid = ? AND subid = ? AND subtype = ?`;
        const d2 = `DELETE FROM subject_grading WHERE examid = ? AND classid = ? AND departmentid = ? AND subid = ? AND subtype = ?`;
        const selectd3 = `SELECT id FROM result_items WHERE assigned_id = ?`;
        const d3 = `DELETE FROM result_items WHERE assigned_id = ?`;
        // step 1
        db.query(d1, [assignedid]);
        // step 2
        db.query(selectd2, [examid, classid, departmentid, subid, subtype], (err1, result1)=>{
            if(err1) return res.status(400).json({message:err1})
            if(result1.length > 0){
                db.query(d2, [examid, classid, departmentid, subid, subtype])
            }
        })
        // next step delete this subejct result_items. 
        // step 3
        db.query(selectd3, [assignedid], (err2, result2)=>{
        if(err2) return res.status(400).json({message:err2})
        if(result2.length > 0){
            db.query(d3, [assignedid])
        }
        });
        // back response.
        res.status(200).json({message:'listing is removed.'})
    } catch (error) {
        next(error)
    }
}

module.exports = {easubrequired, examassigned, getallinfo, removesinglegrade, removeListing}