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

// import controller function

// session
const getsession = async(req, res, next)=>{
    try {
        const sql = `SELECT * FROM session`;
        db.query(sql, (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
                if(result.length===0) return res.status(400).json({message:'Session data is empty!'})
            res.status(200).json({message:'session is created', data:result});
        });
    } catch (error) {
        next(error)
    }
}
const deletesession = async(req, res, next)=>{
    const id = req.params.id;
    try {
        const sql = `DELETE FROM session WHERE id = ?`;
        db.query(sql, [id], (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
            res.status(200).json({message:'session is deleted'});
        });
    } catch (error) {
        next(error)
    }
}
const updatesession = async(req, res, next)=>{
    const {id, session} = req.body;
    try {
        const sql = `UPDATE session SET session = ? WHERE id = ?`;
        db.query(sql, [session,id], (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
            res.status(200).json({message:'session is updated.'});
        });
    } catch (error) {
        next(error)
    }
}
const createsession = async(req, res, next)=>{
    const {session} = req.body;
    try{
        const check = `SELECT * FROM session WHERE session = ?`;
        db.query(check, [session], (checkerr, checkresult)=>{
            if(checkresult.length > 0){
                return res.status(402).json({message:'Session is already exits!'})
            }
            const sql = `INSERT INTO session (session) VALUES(?)`;
            db.query(sql, [session], (err, result)=>{
                if(err) return res.status(400).json({message:'Server Error! Try again later'})
                res.status(200).json({message:'session is created'});
            });
        })
    }catch(err){
        next(err)
    }
}

// classes
const getclass = async(req, res, next)=>{
    try {
        const sql = `SELECT * FROM classes`;
        db.query(sql, (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
                if(result.length===0) return res.status(400).json({message:'Class data is empty!'})
            res.status(200).json({message:'class is get.', data:result});
        });
    } catch (error) {
        next(error)
    }
}
const deleteclass = async(req, res, next)=>{
    const id = req.params.id;
    try {
        const sql = `DELETE FROM classes WHERE id = ?`;
        db.query(sql, [id], (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
            res.status(200).json({message:'class is deleted'});
        });
    } catch (error) {
        next(error)
    }
}
const updateclass = async(req, res, next)=>{
    const {id, name} = req.body;
    try {
        const sql = `UPDATE classes SET name = ? WHERE id = ?`;
        db.query(sql, [name,id], (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
            res.status(200).json({message:'class is updated.'});
        });
    } catch (error) {
        next(error)
    }
}
const createclass = async(req, res, next)=>{
    const {name} = req.body;
    try{
        const check = `SELECT * FROM classes WHERE name = ?`;
        db.query(check, [name], (checkerr, checkresult)=>{
            if(checkresult.length > 0){
                return res.status(402).json({message:'Class is already exits!'})
            }
            const sql = `INSERT INTO classes (name) VALUES(?)`;
            db.query(sql, [name], (err, result)=>{
                if(err) return res.status(400).json({message:'Server Error! Try again later'})
                res.status(200).json({message:'class is created'});
            });
        })
    }catch(err){
        next(err)
    }
}

// section
const getsection = async(req, res, next)=>{
    try {
        const sql = `SELECT * FROM sections`;
        db.query(sql, (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
                if(result.length===0) return res.status(400).json({message:'Sections data is empty!'})
            res.status(200).json({message:'section is get.', data:result});
        });
    } catch (error) {
        next(error)
    }
}
const deletesection = async(req, res, next)=>{
    const id = req.params.id;
    try {
        const sql = `DELETE FROM sections WHERE id = ?`;
        db.query(sql, [id], (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
            res.status(200).json({message:'section is deleted'});
        });
    } catch (error) {
        next(error)
    }
}
const updatesection = async(req, res, next)=>{
    const {id, name} = req.body;
    try {
        const sql = `UPDATE sections SET name = ? WHERE id = ?`;
        db.query(sql, [name,id], (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
            res.status(200).json({message:'section is updated.'});
        });
    } catch (error) {
        next(error)
    }
}
const createsection = async(req, res, next)=>{
    const {name} = req.body;
    try{
        const check = `SELECT * FROM sections WHERE name = ?`;
        db.query(check, [name], (checkerr, checkresult)=>{
            if(checkresult.length > 0){
                return res.status(402).json({message:'Section is already exits!'})
            }
            const sql = `INSERT INTO sections (name) VALUES(?)`;
            db.query(sql, [name], (err, result)=>{
                if(err) return res.status(400).json({message:'Server Error! Try again later'})
                res.status(200).json({message:'section is created'});
            });
        })
    }catch(err){
        next(err)
    }
}


// department
const getdepartment = async(req, res, next)=>{
    try {
        const sql = `SELECT * FROM departments`;
        db.query(sql, (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
                if(result.length===0) return res.status(400).json({message:'Department data is empty!'})
            res.status(200).json({message:'department is get.', data:result});
        });
    } catch (error) {
        next(error)
    }
}
const deletedepartment = async(req, res, next)=>{
    const id = req.params.id;
    try {
        const sql = `DELETE FROM departments WHERE id = ?`;
        db.query(sql, [id], (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
            res.status(200).json({message:'department is deleted'});
        });
    } catch (error) {
        next(error)
    }
}
const updatedepartment = async(req, res, next)=>{
    const {id, name} = req.body;
    try {
        const sql = `UPDATE departments SET name = ? WHERE id = ?`;
        db.query(sql, [name,id], (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
            res.status(200).json({message:'department is updated.'});
        });
    } catch (error) {
        next(error)
    }
}
const createdepartment = async(req, res, next)=>{
    const {name} = req.body;
    try{
        const check = `SELECT * FROM departments WHERE name = ?`;
        db.query(check, [name], (checkerr, checkresult)=>{
            if(checkresult.length > 0){
                return res.status(402).json({message:'Department is already exits!'})
            }
            const sql = `INSERT INTO departments (name) VALUES(?)`;
            db.query(sql, [name], (err, result)=>{
                if(err) return res.status(400).json({message:'Server Error! Try again later'})
                res.status(200).json({message:'department is created'});
            });
        })
    }catch(err){
        next(err)
    }
}

// subjects
const getsubject = async(req, res, next)=>{
    try {
        const sql = `SELECT * FROM subjects`;
        db.query(sql, (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
                if(result.length===0) return res.status(400).json({message:'Subjects data is empty!'})
            res.status(200).json({message:'subject is get.', data:result});
        });
    } catch (error) {
        next(error)
    }
}
const getsubjectwithquery = async(req, res, next)=>{
    const {cid, did,type} = req.params;
    try {
        // res.status(200).json({data:[]})
        let sql;
        let val;
        sql = `SELECT * FROM subjects WHERE class_id = ? AND department_id = ?`;
        if(type=='all'){
            sql = `SELECT * FROM subjects WHERE class_id = ? AND department_id = ?`;
            db.query(sql, [cid, did], (err, result)=>{
                if(err) return res.status(400).json({message:'Server Error! Try again later'})
                    if(result.length===0) return res.status(400).json({message:'Subjects data is empty!'})
                res.status(200).json({message:'subject is get.', data:result});
            });
        }else{
                sql = `SELECT * FROM subjects WHERE type = ? AND class_id = ? AND department_id = ?`;
                db.query(sql, [type,cid, did], (err, result)=>{
                    if(err) return res.status(400).json({message:'Server Error! Try again later'})
                        if(result.length===0) return res.status(400).json({message:'Subjects data is empty!'})
                    res.status(200).json({message:'subject is get.', data:result});
                });
        }
        
        
    } catch (error) {
        next(error)
    }
}

const deletesubject = async(req, res, next)=>{
    const id = req.params.id;
    try {
        const sql = `DELETE FROM subjects WHERE id = ?`;
        db.query(sql, [id], (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
            // delete sub assigned and sub grading if found, this subject id.
            const result_item = `SELECT id FROM result_items WHERE subject_id = ?`;
            const exits_assigned = `SELECT id FROM subject_assigned WHERE subid = ?`;
            const exits_grading = `SELECT id FROM subject_grading WHERE subid = ?`;
            const delete_assigned = `DELETE FROM subject_assigned WHERE subid = ?`;
            const delete_grading = `DELETE FROM subject_grading WHERE subid = ?`;
            const delete_ritem = `DELETE FROM result_items WHERE subject_id = ?`;
            
            // i don't know there info. exits or not.
            try{
            db.query(exits_assigned, [id], (err1, check1)=>{
                if(err1) return res.status(400).json({message:err1})
                if(check1.length>0){
                    db.query(delete_assigned,[id])
                }
            })
            db.query(exits_grading, [id], (err2, check2)=>{
                if(err2) return res.status(400).json({message:err2})
                if(check2.length>0){
                    db.query(delete_grading, [id])
                }
            })
            db.query(result_item, [id], (err3, check3)=>{
                if(err3) return res.status(400).json({message:err3})
                if(check3.length>0){
                    db.query(delete_ritem, [id])
                }
            })
            }catch(err){console.log('not found that info!')}
            // finally
            res.status(200).json({message:'subject is deleted'});
        });
    } catch (error) {
        next(error)
    }
}
const updatesubject = async(req, res, next)=>{
    const {id, code, name, type, class_id, department_id, status} = req.body;
    try {
        const sql = `UPDATE subjects SET code = ?, name = ?, type = ?, class_id = ?, department_id = ?, status = ?  WHERE id = ?`;
        db.query(sql, [code,name,type,class_id,department_id,status,id], (err, result)=>{
            if(err) return res.status(400).json({message:'Server Error! Try again later'})
            // update sub_assgined and sub_grading if found this subid.
            const exits_assigned = `SELECT id FROM subject_assigned WHERE subid = ?`;
            const exits_grading = `SELECT id FROM subject_grading WHERE subid = ?`;
            const update_assigned = `UPDATE subject_assigned SET classid = ?, departmentid = ?, subtype= ? WHERE subid = ?`;
            const update_grading = `UPDATE subject_grading SET classid = ?, departmentid = ?, subtype=? WHERE subid = ?`;
            // i don't know there info. exits or not.
            try{
            db.query(exits_assigned, [id], (err, check1)=>{
                if(err) return res.status(400).json({message:err})
                if(check1.length>0){
                    db.query(update_assigned,[class_id, department_id, type, id])
                }
            })
            db.query(exits_grading, [id], (err2, check2)=>{
                if(err2) return res.status(400).json({message:err2})
                if(check2.length>0){
                    db.query(update_grading, [class_id, department_id, type, id])
                }
            })
            }catch(err){console.log('not found that info!')}
            // finally
            res.status(200).json({message:'subject is updated.'});
        });
    } catch (error) {
        next(error)
    }
}
const createsubject = async(req, res, next)=>{
    const {code, name, type, class_id, department_id,status} = req.body;
    try{
        const check = `SELECT * FROM subjects WHERE code = ? AND class_id = ? AND department_id = ?`;
        db.query(check, [code,class_id,department_id], (checkerr, checkresult)=>{
            if(checkresult.length > 0){
                return res.status(402).json({message:'Subject is already exits!'})
            }
            const sql = `INSERT INTO subjects (code, name, type, class_id, department_id, status) VALUES(?,?,?,?,?,?)`;
            db.query(sql, [code, name, type, class_id, department_id, status], (err, result)=>{
                if(err) return res.status(400).json({message:'Server Error! Try again later'})
                res.status(200).json({message:'subject is created'});
            });
        })
    }catch(err){
        next(err)
    }
}
module.exports = {getsession, createsession, updatesession, deletesession, getclass, createclass, updateclass, deleteclass, getsection, createsection, updatesection, deletesection, getdepartment, createdepartment, updatedepartment, deletedepartment, getsubject, getsubjectwithquery, createsubject,updatesubject,deletesubject}