const db = require('../utils/db.js');
const bcrypt = require('bcryptjs')
const Jwt = require('jsonwebtoken');
const path = require('path')
const fs = require("fs");

// import controller function.

const authontication = async(req, res, next)=>{
    console.log(req.body)
    const {phone, password, otp} = req.body;
    const check = `SELECT * FROM admin WHERE phone = ? AND role = ?`;
    try {
        db.query(check, [phone, 'attandance'], async(err, result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length < 1){
                return res.status(401).json({message:'Invalid phone number'})
            }
            // compare password.
            const isPasswordValid = await bcrypt.compare(password, result[0].password);
            if (!isPasswordValid) {
              return res.status(401).json({ message: 'Invalid phone number or password!'});
            }
                // credential matched.
                const token = Jwt.sign(
                    {id:result[0].id, name:result[0].name, phone:result[0].phone},
                    process.env.JWT_SECRET_USER,
                    {expiresIn: process.env.JWT_EXPIRES_USER}
                ); // assigned userId, userPhone and userRole.
                res.status(200).json({message:'credential matched.', token:token})
        })
    } catch (error) {
        next(error)
    }
}

// details
const details = async(req, res, next)=>{
    try {
        const adminid = req.userid; //req.userId;
        const sql = `SELECT * FROM admin WHERE id = ?`;
        const session = `SELECT session AS value, session AS label FROM session`;
        const classes = `SELECT id AS value, name AS label FROM classes`;
        const sections = `SELECT id AS value, name AS label FROM sections`;
        const departments = `SELECT id AS value, name AS label FROM departments`;
        const site = `SELECT * FROM site WHERE id = 1`;

        db.query(sql, [adminid], (err, result)=>{
            if(err) return res.status(400).json({message:err})
            db.query(session, (err1, result1)=>{
                if(err1) return res.status(400).json({message:err1})
            db.query(classes, (err2, result2)=>{
                if(err2) return res.status(400).json({message:err2})
            db.query(sections, (err3, result3)=>{
                if(err3) return res.status(400).json({message:err3})
            db.query(departments, (err4, result4)=>{
                if(err4) return res.status(400).json({message:err4})
             db.query(site, (err6, result6)=>{
            if(err6) return res.status(400).json({message:err6})
                const data = {
                    site:result6[0],
                    user:result[0],
                    sessions:result1,
                    classes:result2,
                    sections:result3,
                    departments:result4
                }
                res.status(200).json({message:'Login success',  data:data})
            });
            });
        });
    });
    });
        });
    } catch (error) {
        next(error)
    }
}
// this
const homescreen = async(req, res, next)=>{
    // const {classn, section, department} = req.params;
    try {
        // const summary = `SELECT COUNT()`;
        const sql = `SELECT id, image FROM gallery WHERE type = ?`;
        db.query(sql, ['2'], (err, result)=>{
            if(result.length===0) return res.status(400).json({message:'not found!'})
                if(err) return res.status(400).json({message:err})
            res.status(200).json({message:'found!', data:result})
        })
    } catch (error) {
        next(error)
    }
}
// get attdata. requirement class,section and department. condition: if inserted then not select.
const attandancedata = async(req, res, next)=>{
    const {classn, section, department} = req.params;
    const date = `${new Date().getDate()}-${new Date().getMonth()+1}-${new Date().getFullYear()}`;
    
    try {
        const sql = `
        SELECT s.*, d.datetime
        FROM students s
        LEFT JOIN datetime d ON s.id = d.uniqueid && d.datetime = ?
        WHERE s.class = ? AND s.section = ? AND s.department = ?
        GROUP BY s.roll
        `; 
        // add where condition.
        // if complete attandance, then not load all data, just slice the particular data into data state.
        // skip, govt. live.
        db.query(sql, [date, classn, section, department], (err,result)=>{
            if(err) return res.status(400).json({message:err})
        const newdata = []
        result.forEach(element => {
            if(element.datetime==null){
                newdata.push(element)
            }
        });
            res.status(200).json({message:'success', data:newdata})
        })
    } catch (error) {
        next(error)
    }
}
const studentdata = async(req, res, next)=>{
    const {classn, section, department} = req.params;
    try {
        const sql = `
        SELECT s.*, c.name AS class_name, se.name AS section_name, d.name AS department_name
        FROM students s
        JOIN classes c ON s.class = c.id
        JOIN sections se ON s.section = se.id
        JOIN departments d ON s.department = d.id
        WHERE s.class = ? AND s.section = ? AND s.department = ?
        GROUP BY s.roll`;
        db.query(sql, [classn, section, department], (err, result)=>{
            if(err) return res.status(400).json({message:err})
            res.status(200).json({message:'getting', data:result})
        })
    } catch (error) {
        next(error)
    }
}
// preview total student length
// set specify attandance time 

const summarydata = async(req, res, next)=>{
    const date = `${new Date().getDate()}-${new Date().getMonth()+1}-${new Date().getFullYear()}`;
    const {classn, section, department} = req.params;
    const sql = `SELECT COUNT(s.id) AS total_student, COUNT(d.id) AS total_attandance
    FROM students s
    LEFT JOIN datetime d ON d.datetime = ?
    WHERE s.class = ? AND section = ? AND department = ?`
    try {
        db.query(sql, [date, classn, section, department], (err, result)=>{
            if(err) return res.status(400).json({message:err})
            res.status(200).json({message:'get', data:result[0]})
        })
    } catch (error) {
        next(error)
    }
}

const save = async(req, res, next)=>{
    const {status, id, classid, sectionid, departmentid} = req.body;
    const date = `${new Date().getDate()}-${new Date().getMonth()+1}-${new Date().getFullYear()}`;
    try {
        const sql = `INSERT INTO datetime (uniqueid, classid, sectionid, departmentid, datetime, status) VALUES(?,?,?,?,?,?)`;
        // check the record already exits or not in current day.
        const check = `SELECT id FROM datetime WHERE uniqueid = ? AND classid = ? AND departmentid = ? AND datetime = ? `;
        db.query(check,[id, classid, sectionid, departmentid, date], (checkerr, checkresult)=>{
            if(checkerr) return res.status(400).json({message:checkerr})
            if(checkresult.length > 0){
                return res.status(200).json({message:'completed.'})
            }else{
                db.query(sql, [id, classid, sectionid, departmentid, date, status], (err, result)=>{
                    if(err) return res.status(400).json({message:err})
                    res.status(200).json({message:'completed.'})
                })
            }
        })
    } catch (error) {
        next(error)
    }
}

// search report.
const searchreport = async (req, res, next) => {
    const { classn, section, department, from, to } = req.params;
    
    try {
        const formatDate = (dateStr) => {
            const [year, month, day] = dateStr.split('-');
            return `${day}-${month}-${year}`;
        };
        // Step 1: Fetch Students
        const studentQuery = `
            SELECT id AS value, CONCAT(roll, ' | ', name, ' (', randid, ')') AS label
            FROM students
            WHERE class = ? AND section = ? AND department = ?
            GROUP BY roll
        `;
        // later pad.
        const headerinfo = `SELECT c.name AS class_name, s.name AS section_name, d.name AS department_name
        FROM classes c
        JOIN sections s ON s.id = ?
        JOIN departments d ON d.id = ?
        WHERE c.id = ?`;
        db.query(headerinfo, [section, department, classn], (he, hresult)=>{
        if(he) return res.status(400).json({message:he})
        if(hresult.length===0) return res.status(400).json({message:'Somthing went wrong!'})
        const header = hresult[0];
         // summary report query.
         const summaryReportQuery = `SELECT COUNT(d.id) AS days, SUM(d.status = 1) AS total_present, SUM(d.status = 0) AS total_absent, SUM(d.status = 2) AS total_holiday
         FROM datetime d
         WHERE d.classid = ? AND d.sectionid = ? AND departmentid = ? AND STR_TO_DATE(d.datetime, '%d-%m-%Y') BETWEEN STR_TO_DATE(?, '%d-%m-%Y') AND STR_TO_DATE(?, '%d-%m-%Y')`;
        db.query(summaryReportQuery, [classn, section, department, formatDate(from), formatDate(to)], (ser, sresult)=>{
            if(ser) return res.status(400).json({message:ser})
            if(sresult.length===0) return res.status(400).json({message:'Cannot find this date range report.'})
            const reportysummary = sresult[0];
        const headerdata = {
            header:header,
            summary:reportysummary,
            from:from,
            to:to
        }
        db.query(studentQuery, [classn, section, department], async (err, result) => {
            if (err) return res.status(400).json({ message: err });
            if (result.length === 0) return res.status(400).json({ message: 'Empty record!' });

            // Step 2: Fetch Attendance Reports for Each Student
            let fullreport = [];

            const reportPromises = result.map(student => {
                return new Promise((resolve, reject) => {
                    const userid = student.value;
                    // this bottom query extends: roll, name, class, section, department, total present, total_absent. total day. 
                    const findReportQuery = `SELECT COUNT(d.id) AS days, SUM(d.status = 1) AS total_present, 
                    SUM(d.status = 0) AS total_absent, SUM(d.status = 2) AS total_holiday, d.*, s.roll student_roll, s.name student_name, s.gname gardian_name, s.gnumber gardian_number
                    FROM datetime d
                    JOIN students s ON d.uniqueid = s.id
                    WHERE d.uniqueid = ? AND STR_TO_DATE(d.datetime, '%d-%m-%Y') BETWEEN STR_TO_DATE(?, '%d-%m-%Y') AND STR_TO_DATE(?, '%d-%m-%Y')`;

                    db.query(findReportQuery, [userid, formatDate(from), formatDate(to)], (ser, selectResult) => {
                        if (ser) reject(ser);
                        fullreport.push(selectResult[0]); // Store the first result (if exists)
                        resolve();
                    });
                });
            });

            try {
                await Promise.all(reportPromises); // Wait for all DB queries to complete
                return res.status(200).json({ message: 'Found', student_data: result, attendance: fullreport, headerdata:headerdata });
            } catch (queryError) {
                return res.status(400).json({ message: queryError });
            }
        });
    });
    })
    } catch (error) {
        next(error);
    }
};


// single report.
const studentreport = async(req, res, next)=>{
    const {id, from, to} = req.params
    try {
        // student infor. // count absent, present, holiday.
        const studentsql = `
        SELECT s.roll, s.name, s.image, s.gname, s.gnumber, s.randid, c.name AS class_name, se.name AS section_name, d.name AS department_name
        FROM students s
        JOIN classes c ON s.class = c.id
        JOIN sections se ON s.section = se.id
        JOIN departments d ON s.department = d.id
        WHERE s.id = ?
        GROUP BY s.roll`; // find class, section & department with id.
        db.query(studentsql, [id], async(err1, result1)=>{
        if(err1) return res.status(400).json({message:err1})
        if(result1.length===0) return res.status(400).json({message:'not found student academic record!'})
                    // encoding
                    const withImages = await Promise.all(
                        result1.map(async (record) => {
                          try {
                            if (!record.image) {
                              return { ...record, imageBase64: null };
                            }
                          const imagePath = path.join(__dirname, '../uploads/student', record.image); // <- adjust this path
                          const imageBuffer = fs.readFileSync(imagePath);
                          const base64 = imageBuffer.toString('base64');
                            return {
                              ...record,
                              imageBase64: `data:image/jpeg;base64,${base64}`,
                            };
                          } catch (err) {
                            return { ...record, imageBase64: null };
                          }
                        })
                      );
                    // encoding
            // Convert 'YYYY-MM-DD' -> 'DD-MM-YYYY'
        const formatDate = (dateStr) => {
            const [year, month, day] = dateStr.split('-');
            return `${day}-${month}-${year}`;
        };
        const singlereport = `SELECT * FROM datetime
         WHERE uniqueid = ? AND STR_TO_DATE(datetime, '%d-%m-%Y') 
    BETWEEN STR_TO_DATE(?, '%d-%m-%Y') 
    AND STR_TO_DATE(?, '%d-%m-%Y')
    ORDER BY createat DESC`;
    db.query(singlereport, [id, formatDate(from), formatDate(to)], (err2, result2)=>{
        if(err2) return res.status(400).json({message:err2})
        if(result2.length===0) return res.status(400).json({message:'Not found any report of this date range!'})
        // count, absent, present & holiday.
        var present = 0
        var absent = 0
        var holiday = 0
        result2.forEach(element => {
            if(element.status=='1'){
                present = present+1
            }else if(element.status=='0'){
                absent= absent+1
            }else{
                holiday = holiday+1
            }
        });
        const data = {
            student: withImages[0],
            summaryreport:{day:result2.length, present:present, absent:absent, holiday:holiday},
            attandanceinformation:result2,
            range:{from:from, to:to}
        }
        res.status(200).json({message:"found", data})
    })
    })
    } catch (error) {
        next(error)
    }
}
// single report.
// search report.

// report section.

const reportstudents = async(req, res, next)=>{
    const {classn, section, department, from, to} = req.params;
    const date = `${new Date().getFullYear()}-${new Date().getMonth()+1}-${new Date().getDay()}`;
    const sql = `
    SELECT da.*,
    s.roll, s.name, s.gname, s.gnumber,
    c.name AS class_name,
    se.name AS section_name,
    d.name AS department_name
    FROM datetime da
    JOIN students s ON s.id = da.uniqueid
    JOIN classes c ON s.class = c.id
    JOIN sections se ON s.section = se.id
    JOIN departments d ON s.department = d.id
    WHERE da.classid = ? AND da.sectionid = ? AND da.departmentid = ? AND STR_TO_DATE(datetime, '%d-%c-%Y') 
        BETWEEN STR_TO_DATE(?, '%d-%c-%Y') 
        AND STR_TO_DATE(?, '%d-%c-%Y')
        GROUP BY s.roll
    `;
    try {
        db.query(sql, [classn, section, department, from, to], (err, result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length===0) return res.status(400).json({message:'Not found this daterange records!'})
            res.status(200).json({message:'found records!', data:result})
        })
    } catch (error) {
        next(error)
    }
}


const  reportstudent = async(req, res, next)=>{
    const {studentid, from, to} = req.params;
    const sql = `
    SELECT da.*,
    s.roll, s.name, s.gname, s.gnumber,
    c.name AS class_name,
    se.name AS section_name,
    d.name AS department_name
    FROM datetime da
    JOIN students s ON s.id = da.uniqueid
    JOIN classes c ON s.class = c.id
    JOIN sections se ON s.section = se.id
    JOIN departments d ON s.department = d.id
    WHERE da.uniqueid = ? AND STR_TO_DATE(datetime, '%d-%c-%Y') 
        BETWEEN STR_TO_DATE(?, '%d-%c-%Y') 
        AND STR_TO_DATE(?, '%d-%c-%Y')
    GROUP BY da.datetime
    `;
    try {
        db.query(sql, [studentid, from, to], (err, result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length===0) return res.status(400).json({message:'Not found this daterange records!'})
            res.status(200).json({message:'found records!', data:result})
        })
    } catch (error) {
        next(error)
    }
}

// showing this data into attandance page.
const currentmonth = async(req, res, next)=>{
    // from 01 to 31 inside all data i need to get.
    const from = `${new Date().getFullYear()}-${new Date().getMonth+1}-1`
    const to = `${new Date().getFullYear()}-${new Date().getMonth+1}-31`

    // just change date in dynamicly.

    var day = 0;
    const reports = []
    const sql = `SELECT * FROM datetime WHERE datetime = ?`;
    const query = `
    SELECT 
        datetime AS date, 
        COUNT(*) AS total_records, 
        SUM(status = 1) AS total_present, 
        SUM(status = 0) AS total_absent,
        SUM(status = 2) AS total_holiday
    FROM datetime
    WHERE STR_TO_DATE(datetime, '%d-%m-%Y') 
          BETWEEN STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-01'), '%Y-%m-%d')
          AND LAST_DAY(CURDATE())
    GROUP BY datetime
    ORDER BY STR_TO_DATE(datetime, '%d-%m-%Y')
`;
const summary = `
            SELECT 
                COUNT(*) AS total_records, 
                SUM(status = 1) AS total_present, 
                SUM(status = 0) AS total_absent, 
                SUM(status = 2) AS total_holiday
            FROM datetime
            WHERE STR_TO_DATE(datetime, '%d-%m-%Y') 
                  BETWEEN STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-01'), '%Y-%m-%d')
                  AND LAST_DAY(CURDATE())
        `;

try {
    db.query(summary, (summaryer, summaryres)=>{
        if(summaryer) return res.status(400).json({message:err})
        if(summaryres.length===0) return res.status(400).json({message:'Empty records of this month!'})        
        db.query(query, (err, result)=>{
        if(err) return res.status(400).json({message:err})
        if(result.length===0) return res.status(400).json({message:'Empty records of this month!'})
        res.status(200).json({message:'found!', data:result, summary:summaryres[0]})
    })
})
} catch (error) {
    next(error)
}
}
// same, student progress of each month. check first, then this base run loop
// year, month, need input
const findreports = async(req, res, next)=>{
    // recive data.
    const {from, to, classn, section, department} = req.body;
    try {
        // find sql.
    } catch (error) {
        next(error)
    }
}

const returnyear = async(req, res, next)=>{
    const {classid, sectionid, departmentid, year} = req.params;
    // classid, sectionid, departmentid
    const sql = `
    SELECT 
    DATE_FORMAT(MIN(STR_TO_DATE(datetime, '%d-%m-%Y')), '%Y-%m-%d') AS min_date,
    DATE_FORMAT(MAX(STR_TO_DATE(datetime, '%d-%m-%Y')), '%Y-%m-%d') AS max_date 
    FROM datetime 
    WHERE classid = ? AND sectionid = ? AND departmentid = ? AND YEAR(STR_TO_DATE(datetime, '%d-%m-%Y')) = ?`;
    try {
        db.query(sql, [classid, sectionid, departmentid, year], (err, result)=>{
            if(err) return res.status(400).json({message:err})
            if(result.length===0) return res.status(400).json({message:'empty records!'})
            const data = {
                from:result[0].min_date,
                to:result[0].max_date
            }
            res.status(200).json({message:'found!', data:data})
        })
    } catch (error) {
        next(error)
    }
}

// step 2 : just change month, previous just year, but it's time need year+month max or min data.
// month base day showing. not year. -> how many day are found in this year and month. with minimum or maximum.
/*
    - each month showing -> present, absent, or holiday count.
    - if click date/ day name, then showing this date attandance status.
    1. find date range from and to and this base showing date of specify month.
    2. this date range base, find reports and count it.
*/
// skip 1.
const returnmonth = async(req, res, next)=>{
    // from 01 to 31 inside all data i need to get.
    const {classid, sectionid, departmentid, date} = req.params;  
    const from = `${new Date().getFullYear()}-${new Date().getMonth+1}-1`
    const to = `${new Date().getFullYear()}-${new Date().getMonth+1}-31`

    // just change date in dynamicly.

    var day = 0;
    const reports = []
    const sql = `SELECT * FROM datetime WHERE datetime = ?`;
    const summary = `
            SELECT 
                COUNT(*) AS total_records, 
                SUM(status = 1) AS total_present, 
                SUM(status = 0) AS total_absent, 
                SUM(status = 2) AS total_holiday
            FROM datetime
          WHERE classid = ? AND sectionid = ? AND departmentid = ? AND STR_TO_DATE(datetime, '%d-%m-%Y') 
          BETWEEN STR_TO_DATE(CONCAT(SUBSTRING('${date}', 1, 4), '-', SUBSTRING('${date}', 6, 2), '-01'), '%Y-%m-%d')
          AND LAST_DAY(STR_TO_DATE(CONCAT(SUBSTRING('${date}', 1, 4), '-', SUBSTRING('${date}', 6, 2), '-01'), '%Y-%m-%d'))
        `;
    const query = `
    SELECT 
        datetime AS date, 
        COUNT(*) AS total_records, 
        SUM(status = 1) AS total_present, 
        SUM(status = 0) AS total_absent,
        SUM(status = 2) AS total_holiday
    FROM datetime
    WHERE classid = ? AND sectionid = ? AND departmentid = ? AND STR_TO_DATE(datetime, '%d-%m-%Y') 
          BETWEEN STR_TO_DATE(CONCAT(SUBSTRING('${date}', 1, 4), '-', SUBSTRING('${date}', 6, 2), '-01'), '%Y-%m-%d')
          AND LAST_DAY(STR_TO_DATE(CONCAT(SUBSTRING('${date}', 1, 4), '-', SUBSTRING('${date}', 6, 2), '-01'), '%Y-%m-%d'))
    GROUP BY datetime
    ORDER BY STR_TO_DATE(datetime, '%d-%m-%Y')
`;
try {    
        db.query(summary, [classid, sectionid, departmentid], (serr, sresult)=>{
        if(serr) return res.status(400).json({message:serr})
        if(sresult.length===0) return res.status(400).json({message:'Empty records of this month!'})
        db.query(query, [classid, sectionid, departmentid], (err, result)=>{
        if(err) return res.status(400).json({message:err})
        if(result.length===0) return res.status(400).json({message:'Empty records of this month!'})
        res.status(200).json({message:'found!', data:result, summary:sresult[0]})
        })
    })
} catch (error) {
    next(error)
}
}
const returndate = async(req, res, next)=>{
    // from 01 to 31 inside all data i need to get.
    const {classid, sectionid, departmentid, date} = req.params;  
    // just change date in dynamicly.
    const query = `
    SELECT d.*, s.roll, s.name AS sname, s.image, s.gendar, s.randid
    FROM datetime d
    JOIN students s ON d.uniqueid = s.id
    WHERE d.classid = ? AND d.sectionid = ? AND d.departmentid = ? AND d.datetime = ?
    GROUP BY d.id
    ORDER BY s.roll
`;
try {    
        db.query(query, [classid, sectionid, departmentid, date], (err, result)=>{
        if(err) return res.status(400).json({message:err})
        if(result.length===0) return res.status(400).json({message:'Empty records of this month!'})
        res.status(200).json({message:'found!', data:result})
        })
} catch (error) {
    next(error)
}
}
// Step 3 single day report, whic user present, absent or holiday. simple query in datetime table with join student name, roll and image.

module.exports = {authontication, searchreport, studentreport, details, homescreen, reportstudents, reportstudent, currentmonth, attandancedata, studentdata, summarydata,save, returnyear, returnmonth, returndate, findreports}