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

// import controller function

const gets = async(req, res, next)=>{
    try {
        // with assigned*`
        // const exam = `SELECT * FROM exam`;
        const exam = `
        SELECT e.*, COUNT(a.id) AS is_define
        FROM exam e
        LEFT JOIN 
        exam_assinged a ON a.examid = e.id
        GROUP BY e.id
        `;
        db.query(exam, (err, result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length===0) return res.status(401).json({message:'not found exam!'})
            res.status(200).json({message:'found', data:result})
        })
    } catch (error) {
        next(error)
    }
}

const getactive = async(req, res, next)=>{
    try {
        // with assigned*`
        // const exam = `SELECT * FROM exam`;
        const exam = `
        SELECT e.id AS value, e.name AS label, e.type AS etype, COUNT(a.id) AS is_define
        FROM exam e
        LEFT JOIN 
        exam_assinged a ON a.examid = e.id
        WHERE (e.status = 1 OR e.status = 2)
        GROUP BY e.id
        `;
        db.query(exam, (err, result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length===0) return res.status(401).json({message:'not found exam!'})
            res.status(200).json({message:'found', data:result})
        })
    } catch (error) {
        next(error)
    }
}

const getactivemakeres = async(req, res, next)=>{
    try {
        // with assigned*`
        const exam = `
        SELECT e.id AS value, e.session AS e_session, e.name AS label, e.type AS etype, COUNT(a.id) AS is_define
        FROM exam e
        JOIN 
        exam_assinged a ON a.examid = e.id
        WHERE (e.status = 1 OR e.status = 2)
        GROUP BY e.id
        `;
        db.query(exam, (err, result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length===0) return res.status(401).json({message:'not found exam!'})
            res.status(200).json({message:'found', data:result})
        })
    } catch (error) {
        next(error)
    }
}


const create = async(req, res, next)=>{
    const sql = `INSERT INTO exam (session, name, type, status) VALUES(?,?,?,?)`;
    try {
        const {session, name, type, status} = req.body
        db.query(sql, [session, name, type, status], (err, result)=>{
            if(err) return res.status(400).json({message:err})
            res.status(201).json({message:'exam is created.'})
        })
    } catch (error) {
        next(error)
    }
}

const deleted = async(req, res, next)=>{
    const id = req.params.id
    try {
        // with assigned
        const exam = `DELETE FROM exam WHERE id = ?`;
        const d1 = `DELETE FROM exam_assinged WHERE examid = ?`;
        const d2 = `DELETE FROM subject_assigned WHERE examid = ?`;
        const d3 = `DELETE FROM subject_grading WHERE examid = ?`;
        const d4 = `DELETE FROM results WHERE examid = ?`;
        const items = `DELETE FROM result_items WHERE result_id = ?`;
        const results = `SELECT * FROM results WHERE examid = ?`;

       try {
         // step 1
         db.query(d1, [id]);
         db.query(d2, [id]);
         db.query(d3, [id]);
       } catch (error) {
            console.log('some record missing..')
       }
        // step 2
        db.query(results, [id], (e,r)=>{
            if(e) return res.status(400).json({message:e})
            r.forEach(element => {
                db.query(items, [element.id])
            });
        // step 3
        db.query(d4, [id]);
        // step 4
        db.query(exam, [id], (err, result)=>{
            if(err) return res.status(400).json({message:err})
            res.status(200).json({message:'exam is deleted.'})
        })
    })
    } catch (error) {
        next(error)
    }
}
const reset = async(req, res, next)=>{
    const id = req.params.id
    try {
        // with assigned
        const d1select = `SELECT id FROM exam_assinged WHERE examid = ?`;
        const d2select = `SELECT id FROM subject_assigned WHERE examid = ?`;
        const d3select = `SELECT id FROM subject_grading WHERE examid = ?`;
        const d1 = `DELETE FROM exam_assinged WHERE examid = ?`;
        const d2 = `DELETE FROM subject_assigned WHERE examid = ?`;
        const d3 = `DELETE FROM subject_grading WHERE examid = ?`;
        // results
        const resultwithexam = `DELETE FROM results WHERE examid = ?`;
        const items = `DELETE FROM result_items WHERE result_id = ?`;
        const results = `SELECT * FROM results WHERE examid = ?`;
        try {
            // step 1
        db.query(d1select, [id], (err1, result1)=>{
            if(err1) return res.status(400).json({message:err1})
            if(result1.length > 0){
                db.query(d1, [id]);
            }
        })
        // step 2
        db.query(d2select, [id], (err2, result2)=>{
            if(err2) return res.status(400).json({message:err2})
            if(result2.length > 0){
                db.query(d2, [id]);
            }
        })
        // step 3
        db.query(d3select, [id], (err3, result3)=>{
            if(err3) return res.status(400).json({message:err3})
            if(result3.length > 0){
                db.query(d3, [id]);
            }
        })
        // step 4 delete result and here items.
        db.query(results, [id], (e,r)=>{
            if(e) return res.status(400).json({message:e})
            r.forEach(element => {
                db.query(items, [element.id])
            });
        // empty result with this exam.
        db.query(resultwithexam, [id]);
        })
        } catch (error) {
            console.log('some data is missing..')
        }
        // back response.
        res.status(200).json({message:'reset success.'})
        
    } catch (error) {
        next(error)
    }
}

const updated = async(req, res, next)=>{
    const {id, session, name, type, status} = req.body
    try {
        // with assigned
        const exam = `UPDATE exam SET session = ?, name = ?, type = ?, status = ? WHERE id = ?`;
        db.query(exam, [session, name, type, status, id], (err, result)=>{
            if(err) return res.status(400).json({message:err})
            res.status(200).json({message:'exam is updated.'})
        })
    } catch (error) {
        next(error)
    }
}


module.exports = {gets, getactive, getactivemakeres, create, deleted, updated, reset}