const express = require('express');
const router = express.Router();
const db = require('../database/db');


router.get('/getpuni', (req, res) => {

    const query = `SELECT count(*) AS privateInstituteCount FROM institute WHERE type="Private"`;

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            // Access the count value from the result
            const count = result[0].privateInstituteCount;
            res.json({ count });
        }
    });

});


router.get('/getguni', (req, res) => {

    const query = `SELECT count(*) AS governmentInstituteCount FROM institute WHERE type="Government"`;

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            // Access the count value from the result
            const count = result[0].governmentInstituteCount;
            res.json({ count });
        }
    });

});


router.get('/getgcou', (req, res) => {

    const query = `SELECT count(*) AS generalCourseCount FROM courses WHERE universityType="Government"`;

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            // Access the count value from the result
            const count = result[0].generalCourseCount;
            res.json({ count });
        }
    });

});

router.get('/getpcou', (req, res) => {

    const query = `SELECT count(*) AS privateCourseCount FROM courses WHERE universityType="Private"`;

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            // Access the count value from the result
            const count = result[0].privateCourseCount;
            res.json({ count });
        }
    });

});


router.get('/getfjob', (req, res) => {

    const query = 'SELECT count(*) AS jobs FROM `fjob`';

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            // Access the count value from the result
            const count = result[0].jobs;
            res.json({ count });
        }
    });
})

router.get('/getgjob', (req, res) => {

    const query = 'SELECT count(*) AS jobs FROM `ljob`';

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            // Access the count value from the result
            const count = result[0].jobs;
            res.json({ count });
        }
    });
})

router.get('/getEvent', (req, res) => {

    const query = 'SELECT count(*) AS events FROM `events`';

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            // Access the count value from the result
            const count = result[0].events;
            res.json({ count });
        }
    });

})

router.get('/getpevent', (req, res) => {

    const query = 'SELECT count(*) AS events FROM `events` WHERE approved = 0';

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            // Access the count value from the result
            const count = result[0].events;
            res.json({ count });
        }
    });
})

//get all studyabroad
router.get('/astudyabroad', (req, res) => {
    const query = 'SELECT count(*) AS events FROM `studyabroad`';

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            // Access the count value from the result
            const count = result[0].events;
            res.json({ count });
        }
    });
})


router.get('/pstudyabroad', (req, res) => {
    const query = 'SELECT COUNT(*) AS events FROM `studyabroad` WHERE approved=0';

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            // Access the count value from the result
            const count = result[0].events;
            res.json({ count });
        }
    });
});


router.get('/allexter', (req, res) => {

    const query = 'SELECT COUNT(*) AS events FROM `external_degrees`'

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            // Access the count value from the result
            const count = result[0].events;
            res.json({ count });
        }
    });
})

router.get('/pexter', (req, res) => {

    const query = 'SELECT COUNT(*) AS events FROM `external_degrees` WHERE approved=0'

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            // Access the count value from the result
            const count = result[0].events;
            res.json({ count });
        }
    });

})

router.get('/getschool', (req, res) => {

    const query = 'SELECT count(*) AS events FROM `scholarships`'

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            // Access the count value from the result
            const count = result[0].events;
            res.json({ count });
        }
    });
})

router.get('/getpschool', (req, res) => {

    const query = 'SELECT count(*) AS events FROM `scholarships` WHERE approved=0'

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            // Access the count value from the result
            const count = result[0].events;
            res.json({ count });
        }
    });
})

router.get('/tuta', (req, res) => {

    const query = 'SELECT count(*) AS events FROM `tuition`'

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            const count = result[0].events;
            res.json({ count });
        }
    });
})

router.get('/getptute', (req, res) => {
    const query = 'SELECT count(*) AS events FROM `tuition` WHERE approved=0'

    db.query(query, (err, result) => {
        if (err) {
            res.status(500).send({ message: err.message });
        } else {
            const count = result[0].events;
            res.json({ count });
        }
    });
})



module.exports = router;