const db = require('../utils/db.js')
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/routine/"); // ✅ 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 });
  

// import controller function.

const routine = async(req, res, next)=>{
    const sql = `SELECT n.*,
    c.name AS class_name,
    s.name AS section_name,
    d.name AS department_name
    FROM routine n
    LEFT JOIN classes c ON n.classn = c.id
    LEFT JOIN sections s ON n.section = s.id
    LEFT JOIN departments d ON n.department = d.id
    ORDER BY n.createat DESC`;
    try {
        db.query(sql, (err,result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length==0) return res.status(400).json({message:'empty notices!'})
            res.status(200).json({message:'notice found!', data:result})
        })
    } catch (error) {
        next(error)
    }
}

// active notice
const hsc_routine = async(req, res, next)=>{
    const sql = `SELECT n.*, DATE_FORMAT(n.createat, '%d %M %Y') AS create_date,
    c.name AS class_name,
    s.name AS section_name,
    d.name AS department_name
    FROM routine n
    LEFT JOIN classes c ON n.classn = c.id
    LEFT JOIN sections s ON n.section = s.id
    LEFT JOIN departments d ON n.department = d.id
    WHERE n.type = 1 AND n.status = 1
    ORDER BY n.createat DESC`;
    try {
        db.query(sql, (err,result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length==0) return res.status(400).json({message:'empty routine!'})
            res.status(200).json({message:'routine found!', data:result})
        })
    } catch (error) {
        next(error)
    }
}

const hons_routine = async(req, res, next)=>{
    const sql = `SELECT n.*,
    c.name AS class_name,
    s.name AS section_name,
    d.name AS department_name
    FROM routine n
    LEFT JOIN classes c ON n.classn = c.id
    LEFT JOIN sections s ON n.section = s.id
    LEFT JOIN departments d ON n.department = d.id
    WHERE n.type = 2 AND n.status = 1
    ORDER BY n.createat DESC`;
    try {
        db.query(sql, (err,result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length==0) return res.status(400).json({message:'empty routine!'})
            res.status(200).json({message:'routine found!', data:result})
        })
    } catch (error) {
        next(error)
    }
}

const degree_routine = async(req, res, next)=>{
    const sql = `SELECT n.*,
    c.name AS class_name,
    s.name AS section_name,
    d.name AS department_name
    FROM routine n
    LEFT JOIN classes c ON n.classn = c.id
    LEFT JOIN sections s ON n.section = s.id
    LEFT JOIN departments d ON n.department = d.id
    WHERE n.type = 3 AND n.status = 1
    ORDER BY n.createat DESC`;
    try {
        db.query(sql, (err,result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length==0) return res.status(400).json({message:'empty routine!'})
            res.status(200).json({message:'routine found!', data:result})
        })
    } catch (error) {
        next(error)
    }
}

const create = async(req, res)=>{
    const sql = `INSERT INTO routine (classn, section, department, attachment, type, status)
    VALUES(?,?,?,?,?,?) `;
    try {
          const {classn, section, department, type, status} = req.body
            // prepare the data.
            const document = req.file ? req.file.filename : null;
            db.query(sql, [classn, section, department, document, type, status], (err,result)=>{
                if(err) return res.status(400).json({message:err})
                res.status(200).json({ message: "Routine is created.", id:result.insertId});  
            })
    } catch (error) {
        console.error(error)
    }

}

const update = async(req, res)=>{
    const {id, classn, section, department, oldfilename, type, status} = req.body
    const sql = `UPDATE routine SET classn = ?, section = ?, department = ?, attachment = ?, type = ?, status = ? WHERE id = ? `;
    try {
            // prepare the data.
            if(req.file){
                console.log('New File come here..')
                const filePath = path.join(__dirname, "../uploads/routine/", oldfilename);
                fs.unlink(filePath, (err) => {
                    if (err) {
                      console.error("Error deleting file:", err);
                    } else {
                      console.log("File deleted successfully:", filePath);
                    }
                  });
            }
            const document = req.file ? req.file.filename : oldfilename;
            db.query(sql, [classn, section, department, document, type, status, id], (err, result)=>{
                if(err) return res.status(400).json({message:err})
                res.status(200).json({ message: "Routine is successfully updated." });
            })
    } catch (error) {
        console.error(error)
    }

}


const deleted = async(req, res, next)=>{
    const id = req.params.id;
    const sql = `DELETE FROM routine WHERE id = ?`;
    const find = `SELECT attachment FROM routine WHERE id = ?`;
    try {
        db.query(find, [id], (err, result)=>{
            if(err) return res.status(400).json({message:err})
            const oldfile = result[0].attachment;
            // unlink here.
            const filePath = path.join(__dirname, "../uploads/routine/", oldfile);
            fs.unlink(filePath, (err) => {
                if (err) {
                  console.error("Error deleting file:", err);
                } else {
                    // all right deleted record.
                db.query(sql, [id], (err2, result2)=>{
                if(err2) return res.status(400).json({message:err2})
                 res.status(203).json({message:'Routine is successfully removed.'})
                })
                }
              });
        })
    } catch (error) {
        next(error)
    }
}

module.exports = {upload, routine, create, update, deleted, hsc_routine, hons_routine, degree_routine}