const db = require('../utils/db')
// import controller function
const multer = require('multer')
const path = require('path')
const fs = require("fs");

// Set up Multer storage (optional, saves file with original name)
const storage = multer.diskStorage({
    destination: (req, file, cb) => {
      cb(null, "uploads/student/"); // ✅ Save files in 'uploads/notice' folder
    },
    filename: (req, file, cb) => {
      cb(null, Date.now() + path.extname(file.originalname)); // Unique file name
    },
  });
  
  const upload = multer({ storage: storage });

const admission = async(req, res, next)=>{
    try{
        const {session, classn, section, department, roll, name, dob, nationality, gendar, relagion, bgroup, fname, mname, gname, gnumber, address, randid} = req.body;
        const image = req.file ? req.file.filename : null;
        const check = `SELECT * FROM students WHERE session = ? AND class = ? AND section = ? AND department = ? AND roll = ?`;
        const check2 = `SELECT * FROM students WHERE randid = ?`;
        const create = `INSERT INTO students (session, class, section, department, roll, name, image, dob, nationality, gendar, relagion, bgroup, fname, mname, gname, gnumber, address, randid)
        VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
        db.query(check, [session, classn, section, department, roll], (checkerr, checkress)=>{
            if(checkerr) return res.status(400).json({message:checkerr})
            if(checkress.length > 0) return res.status(400).json({message:'Student roll number is already exits!'})
        db.query(check2, [randid], (checkerr2, checkeress2)=>{
            if(checkerr2) return res.status(400).json({message:checkerr2})
            if(checkeress2.length > 0){
                return res.status(400).json({message:'Please again Re-Generate StudentID!'})
            }
        db.query(create, [session, classn, section, department, roll, name, image, dob, nationality, gendar, relagion, bgroup, fname, mname, gname, gnumber, address, randid], (err,result)=>{
            if(err){
                return res.status(400).json({message:err})
            }
         res.status(200).json({message:'Save record.'})
        });
    });     
        })
    }catch(err){
        next(err)
    }
}

const allstudents = async (req, res, next) => {
    try {
      const get = `
        SELECT st.*,
          c.id AS class_id, c.name AS class_name,
          s.id AS section_id, s.name AS section_name,
          d.id AS department_id, d.name AS department_name
        FROM students st
        LEFT JOIN classes c ON st.class = c.id
        LEFT JOIN sections s ON st.section = s.id
        LEFT JOIN departments d ON st.department = d.id
        ORDER BY st.created_time DESC LIMIT 100
      `;
  
      db.query(get, async (err, result) => {
        if (err) return res.status(400).json({ message: 'Get Request failed!' });
        if (result.length === 0) return res.status(401).json({ message: 'Students data is empty!' });
  
        // Fetch and attach base64 images
        const withImages = await Promise.all(
          result.map(async (student) => {
            try {
              if (!student.image) {
                return { ...student, imageBase64: null };
              }
            const imagePath = path.join(__dirname, '../uploads/student', student.image); // <- adjust this path
            const imageBuffer = fs.readFileSync(imagePath);
            const base64 = imageBuffer.toString('base64');
              return {
                ...student,
                imageBase64: `data:image/jpeg;base64,${base64}`,
              };
            } catch (err) {
              console.error(`Failed to load image for student ID ${student.id}:`, err.message);
              return { ...student, imageBase64: null };
            }
          })
        );
  
        res.status(200).json({ message: 'Found records.', data: withImages });
      });
    } catch (err) {
      next(err);
    }
  };

// Query -> Where -> session, class, section, department
const studentsquery = async(req, res, next)=>{
    const {session, classn, section, department} = req.body;
    try{
        const get = `SELECT st.*, c.id AS class_id, c.name AS class_name, s.id AS section_id, s.name AS section_name, d.id AS department_id, d.name AS department_name
           FROM students st
           LEFT JOIN
           classes c ON st.class = c.id
           LEFT JOIN
           sections s ON st.section = s.id
           LEFT JOIN
           departments d ON st.department = d.id
           WHERE st.session = ? AND st.class = ? AND st.section = ? AND st.department = ?
           ORDER BY st.created_time DESC`;
        db.query(get, [session, classn, section, department], async (err, result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length===0) return res.status(401).json({message:'Students data is empty!'})
            // Fetch and attach base64 images
        const withImages = await Promise.all(
            result.map(async (student) => {
              try {
                if (!student.image) {
                  return { ...student, imageBase64: null };
                }
              const imagePath = path.join(__dirname, '../uploads/student', student.image); // <- adjust this path
              const imageBuffer = fs.readFileSync(imagePath);
              const base64 = imageBuffer.toString('base64');
                return {
                  ...student,
                  imageBase64: `data:image/jpeg;base64,${base64}`,
                };
              } catch (err) {
                console.error(`Failed to load image for student ID ${student.id}:`, err.message);
                return { ...student, imageBase64: null };
              }
            })
          );
            res.status(200).json({message:'Found records.', data:withImages})
        })
    }catch(err){
        next(err)
    }
}
// Query -> Where -> id.
const wherestudent = async(req, res, next)=>{
    const studentid = req.userid;
    try{
        const get = `SELECT st.*, c.id AS class_id, c.name AS class_name, s.id AS section_id, s.name AS section_name, d.id AS department_id, d.name AS department_name
           FROM students st
           LEFT JOIN
           classes c ON st.class = c.id
           LEFT JOIN
           sections s ON st.section = s.id
           LEFT JOIN
           departments d ON st.department = d.id
           GROUP BY
           st.id
           WHERE st.id = ?`;
        db.query(get, [studentid],(err, result)=>{
            if(err) return res.status(400).json({message:'Get Request faild!'})
            if(result.length===0) return res.status(401).json({message:'Student data is empty!'})
            res.status(200).json({message:'Found record.', data:result})
        })
    }catch(err){
        next(err)
    }
}
// Delete users.
const deleteadmission = async(req, res, next)=>{
    const {uid} = req.params;
    try{
        // student
        const selectimage = `SELECT image FROM students WHERE id = ?`;
        const deletereq = `DELETE FROM students WHERE id =?`;
        // delete here result, unlink image if found.
        const results = `SELECT id FROM results WHERE studentid = ?`;
        const deleteitems = `DELETE FROM result_items WHERE result_id = ?`;
        const deleteresults = `DELETE FROM results WHERE studentid = ?`;
        // step 1
        db.query(results, [uid], (err1, result1)=>{
            if(err1) return res.status(400).json({message:err1})
            if(result1.length > 0){
                result1.forEach(element => {
                // delete items.
                const rid = element.id
                db.query(deleteitems, [rid])
                });
            }
        })
        // ste 2
        db.query(deleteresults, [uid])
        // step 3 . unlink image.
        db.query(selectimage, [uid], (err2, result2)=>{
            if(err2) return res.status(400).json({message:err2})
            const img = result2[0].image ? result2[0].image : null
            if(!img || img==null || img==''){
                db.query(deletereq, [uid],(err, result)=>{
                    if(err) return res.status(400).json({message:'Delete Request faild!'})
                    res.status(200).json({message:'Deleted admission record..'})
                })
            }else{
                 // unlink here.
            const filePath = path.join(__dirname, "../uploads/student/", img);
            fs.unlink(filePath, (err) => {
                if (err) {
                  console.error("Error deleting file:", err);
                } else {
                // complete step 4.
                db.query(deletereq, [uid],(err, result)=>{
                if(err) return res.status(400).json({message:'Delete Request faild!'})
                return res.status(200).json({message:'Deleted admission record..'})
                })
                }
              });
            }
        })
    }catch(err){
        next(err)
    }
}
// Update student.
const updateadmission = async(req, res, next)=>{
    const {uid, session, classn, section, department, roll, name, oldimage, dob, nationality, gendar, relagion, bgroup, fname, mname, gname, gnumber, address, status} = req.body;
    try{
        const image = req.file ? req.file.filename : oldimage=='null' || oldimage==null ? null : oldimage;
        console.log(` name: ${image}`)
        // if req.file is true, then unlink old image. using try catch inside, beacuse image!!
        if(req.file && oldimage){
            // unlink here.
            try{
                const filePath = path.join(__dirname, "../uploads/student/", oldimage);
                fs.unlink(filePath, (err) => {
                    if (err) {
                      console.error(" ");
                    }
                  });
            }catch(err){ console.log('unspected error!') }
        }
        const updated = `UPDATE students SET session =?, class = ?, section = ?, department = ?, roll = ?, name = ?, image = ?, dob = ?, nationality = ?, gendar = ?, relagion = ?, bgroup = ?, fname = ?, mname = ?, gname = ?, gnumber = ?, address = ?, status = ? WHERE id = ?`;
        db.query(updated, [session, classn, section, department, roll, name, image, dob, nationality, gendar, relagion, bgroup, fname, mname, gname, gnumber, address, status, uid],(err, result)=>{
            if(err) return res.status(400).json({message:'Update Request faild!'})
            res.status(200).json({message:'Updated admission record..'})
        })
    }catch(err){
        next(err)
    }
}

// Datasheet
const countinfo = async(req, res, next)=>{
    try {
        // all student. male student. female student. other student. relagion student. other relagion student
        const gendarcount = `SELECT COUNT(id) AS allstudent FROM students WHERE gendar = ?`;
        const relagioncount = `SELECT COUNT(id) AS allstudent FROM students WHERE relagion = ?`;
        db.query(gendarcount, ['Male'], (err, result1)=>{
            db.query(gendarcount, ['Female'], (err, result2)=>{
                db.query(gendarcount, ['Other'], (err, result3)=>{
                    db.query(relagioncount, ['Islam'], (err, result4)=>{
                        const maledata = result1[0].allstudent;
                        const femaledata = result2[0].allstudent;
                        const othergendardata = result3[0].allstudent;
                        const allgendarstudent = parseInt(maledata)+parseInt(femaledata)+parseInt(othergendardata)
                        const islamstudent = result4[0].allstudent;
                        const otherrelagionstudent = allgendarstudent - parseInt(islamstudent);
                        const data = {
                            allstudent:allgendarstudent,
                            malestudent: maledata,
                            femalestudent: femaledata,
                            otherstudent: othergendardata,
                            rislamstudent: islamstudent,
                            rotherstudent: otherrelagionstudent
                        }
                        res.status(200).json({message:'Found records!', data:data})
                    });
                });
            });
        });
    } catch (error) {
        next(error)
    }
}

const datasheet = async(req, res, next)=>{
    const {session, classn, section, department} = req.body;
    try {
        // all student. male student. female student. other student. relagion student. other relagion student
        const gendarcount = `SELECT * FROM students WHERE session = ? AND class = ? AND section = ? AND department = ? AND gendar = ?`;
        const relagioncount = `SELECT * FROM students WHERE session = ? AND class = ? AND section = ? AND department = ? AND relagion = ?`;
        
        db.query(gendarcount, [session, classn, section, department, 'Male'], async(err, result1)=>{
        // # encoding 01
            // Fetch and attach base64 images
            const withImages1 = await Promise.all(
                result1.map(async (student1) => {
                try {
                    if (!student1.image) {
                    return { ...student1, imageBase64: null };
                    }
                const imagePath = path.join(__dirname, '../uploads/student', student1.image); // <- adjust this path
                const imageBuffer = fs.readFileSync(imagePath);
                const base64 = imageBuffer.toString('base64');
                    return {
                    ...student1,
                    imageBase64: `data:image/jpeg;base64,${base64}`,
                    };
                } catch (err) {
                    console.error(`Failed to load image for student ID ${student1.id}:`, err.message);
                    return { ...student1, imageBase64: null };
                }
                })
            );
        // # encoding 01
            db.query(gendarcount, [session, classn, section, department, 'Female'], async(err, result2)=>{
        // # encoding 02
            const withImages2 = await Promise.all(
                result2.map(async (student2) => {
                try {
                    if (!student2.image) {
                    return { ...student2, imageBase64: null };
                    }
                const imagePath = path.join(__dirname, '../uploads/student', student2.image); // <- adjust this path
                const imageBuffer = fs.readFileSync(imagePath);
                const base64 = imageBuffer.toString('base64');
                    return {
                    ...student2,
                    imageBase64: `data:image/jpeg;base64,${base64}`,
                    };
                } catch (err) {
                    console.error(`Failed to load image for student ID ${student2.id}:`, err.message);
                    return { ...student2, imageBase64: null };
                }
                })
            );
                db.query(gendarcount, [session, classn, section, department, 'Other'], async(err, result3)=>{
                // conding 3
                    const withImages3 = await Promise.all(
                        result3.map(async (student3) => {
                        try {
                            if (!student3.image) {
                            return { ...student3, imageBase64: null };
                            }
                        const imagePath = path.join(__dirname, '../uploads/student', student3.image); // <- adjust this path
                        const imageBuffer = fs.readFileSync(imagePath);
                        const base64 = imageBuffer.toString('base64');
                            return {
                            ...student3,
                            imageBase64: `data:image/jpeg;base64,${base64}`,
                            };
                        } catch (err) {
                            console.error(`Failed to load image for student ID ${student3.id}:`, err.message);
                            return { ...student3, imageBase64: null };
                        }
                        })
                    );
                
                    db.query(relagioncount, [session, classn, section, department, 'Islam'], (err, result4)=>{
                        // end encoding.
                        const maledata = result1.length;
                        const femaledata = result2.length;
                        !result1 ? result1 = [] : result1 = withImages1
                        !result2 ? result2 = [] : result2 = withImages2
                        !result3 ? result3 = [] : result3 = withImages3
                        const allsdata = [...result1, ...result2, ...result3];

                        const othergendardata = result3.length;
                        const allgendarstudent = parseInt(maledata)+parseInt(femaledata)+parseInt(othergendardata)

                        const islamstudent = result4[0] ? result4[0].allstudent : 0;
                        const otherrelagionstudent = allgendarstudent - parseInt(islamstudent);
                        const data = {
                            allsdata:allsdata?allsdata:[],
                            allstudent:allgendarstudent?allgendarstudent:0,
                            malesdata:result1,
                            malestudent: maledata,
                            femalesdata:result2,
                            femalestudent: femaledata,
                            otherstudent: othergendardata,
                            rislamstudent: islamstudent,
                            rotherstudent: otherrelagionstudent,
                            academicinfo: {session,classn,section,department}
                        }
                        res.status(200).json({message:'Found records!', data:data})
                    });
                });
            });
        });
    } catch (error) {
        next(error)
    }
}

module.exports = {upload, admission, allstudents, studentsquery, wherestudent, deleteadmission, updateadmission, countinfo, datasheet}