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

// Promisify DB query
function query(sql, params) {
    return new Promise((resolve, reject) => {
        db.query(sql, params, (err, results) => {
            if (err) return reject(err);
            resolve(results);
        });
    });
}

// Home Page Controller
const homePage = async (req, res, next) => {
    try {
        const queries = [
            query(`SELECT title, image FROM gallery WHERE type = 1 ORDER BY createat DESC LIMIT 6`), // gallery
            query(`SELECT * FROM notices WHERE type != 4 AND status = 1 ORDER BY createat DESC LIMIT 6`), // notice
            query(`SELECT * FROM notices WHERE type = 4 AND status = 1 ORDER BY createat DESC LIMIT 6`), // scroll
            query(`SELECT * FROM site WHERE id = ?`, [1]), // site
            query(`SELECT * FROM instructors`), // instructor
            query(`SELECT * FROM theme WHERE id = ?`, [1]), // theme
            query(`SELECT * FROM videos`) // video
        ];

        const [
            gallery,
            notice,
            scroll,
            site,
            instructor,
            theme,
            video
        ] = await Promise.all(queries);

        // Transform video URLs to only include the YouTube video ID
        const updatedVideos = video.map(v => {
            const url = new URL(v.url);
            const videoId = url.searchParams.get("v"); // Extract "v" parameter
            return {
                ...v,
                videoId // add videoId or replace `url` if needed
            };
        });

        res.status(200).json({
            message: 'Homepage data loaded successfully.',
            data: {
                gallery,
                notice,
                scroll,
                site: site?.[0] || null,
                instructor,
                theme: theme?.[0] || null,
                video: updatedVideos
            }
        });

    } catch (error) {
        next(error);
    }
};


// Get all active notices
const getNotice = async (req, res, next) => {
    try {
        const sql = `SELECT * FROM notices WHERE status = ? ORDER BY createat DESC`;
        const result = await query(sql, [1]);
        res.status(200).json({ message: 'Notices fetched successfully', data: result || [] });
    } catch (error) {
        next(error);
    }
};

// Get all active teachers
const getTeacher = async (req, res, next) => {
    try {
        const sql = `SELECT * FROM teachers WHERE status = ? ORDER BY name ASC`;
        const result = await query(sql, [1]);
        res.status(200).json({ message: 'Teachers fetched successfully', data: result || [] });
    } catch (error) {
        next(error);
    }
};

// Get Commititee
const getCommie = async (req, res, next) => {
    try {
        const sql = `SELECT * FROM committee`;
        const result = await query(sql);
        res.status(200).json({ message: 'Committe fetched successfully', data: result || [] });
    } catch (error) {
        next(error);
    }
};
// Class Routine

// Exam Routine

// Get photo gallery
const getPhotoGallery = async (req, res, next) => {
    try {
        const sql = `SELECT id, title, image AS src FROM gallery WHERE type = ? ORDER BY createat DESC`;
        const result = await query(sql, ['0']);
        res.status(200).json({ message: 'Gallery fetched successfully', data: result || [] });
    } catch (error) {
        next(error);
    }
};

// Get all committee members
const getCommitte = async (req, res, next) => {
    try {
        const sql = `SELECT * FROM committee`;
        const result = await query(sql);
        res.status(200).json({ message: 'Committee fetched successfully', data: result || [] });
    } catch (error) {
        next(error);
    }
};

// Get all instructors
const getInstructor = async (req, res, next) => {
    try {
        const sql = `SELECT * FROM instructors ORDER BY name ASC`;
        const result = await query(sql);
        res.status(200).json({ message: 'Instructors fetched successfully', data: result || [] });
    } catch (error) {
        next(error);
    }
};

// Get class routine with joins
const getClassRoutine = async (req, res, next) => {
    try {
        const sql = `
            SELECT 
                r.*, 
                c.name AS class_name, 
                d.name AS department_name
            FROM routine r
            LEFT JOIN classes c ON r.classn = c.id
            LEFT JOIN departments d ON r.department = d.id
            ORDER BY r.id DESC`;
        const result = await query(sql);
        res.status(200).json({ message: 'Class routine fetched successfully', data: result || [] });
    } catch (error) {
        next(error);
    }
};

// Get exam routine with joins
const getExamRoutine = async (req, res, next) => {
    try {
        const sql = `
            SELECT 
                r.*, 
                c.name AS class_name, 
                d.name AS department_name
            FROM exam_routine r
            LEFT JOIN classes c ON r.classn = c.id
            LEFT JOIN departments d ON r.department = d.id
            ORDER BY r.id DESC`;
        const result = await query(sql);
        res.status(200).json({ message: 'Exam routine fetched successfully', data: result || [] });
    } catch (error) {
        next(error);
    }
};

module.exports = {
    homePage,
    getNotice,
    getTeacher,
    getCommitte,
    getInstructor,
    getClassRoutine,
    getExamRoutine,
    getPhotoGallery
};
