// Express routing
const express = require('express');
const router = express.Router();
// Database connection
const db = require('../db');
// Param validation functions
const paramValidation = require('../param_validation');
// Error handling functions
const errorHandling = require('../error_handling');
const { DatabaseError } = require('pg');

/* GET Students */
router.get('/students', async function (req, res, next) {
    const team_id = req.query.team_id;
    const section_id = req.query.section_id;

    if (team_id) {
        let validation_tuple = await paramValidation.validate_team_id(
            team_id,
            paramValidation.ParamType.QUERY
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(404)
                .json(
                    errorHandling.return_error_404(validation_tuple.error_msg)
                );
        }
    }
    if (section_id) {
        let validation_tuple = paramValidation.build_validation_tuple(
            section_id,
            paramValidation.checkInteger(section_id),
            'Failed to convert value ' +
                section_id +
                ' for section_id to an integer.'
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_404(validation_tuple.error_msg)
                );
        }
    }

    const query = db.prepare_select_statement('students', req.query);
    // Retrieve rows from database
    try {
        const result = await db.query(query, Object.values(req.query));
        return res.status(200).json(result.rows);
    } catch (error) {
        return res
            .status(500)
            .json(errorHandling.return_error_500(error.message));
    }
});

/* GET Student by ID */
router.get('/students/:student_id', async function (req, res, next) {
    const student_id = req.params.student_id;
    if (student_id) {
        let validation_tuple = await paramValidation.validate_student_id(
            student_id,
            paramValidation.ParamType.PATH
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(404)
                .json(
                    errorHandling.return_error_404(validation_tuple.error_msg)
                );
        }
    }
    // Retrieve row from database
    try {
        const result = await db.query(
            'SELECT users.* FROM users JOIN students ON users.user_id = students.user_id WHERE students.student_id = $1',
            [student_id]
        );
        return res.status(200).json(result.rows[0]);
    } catch (error) {
        return res
            .status(500)
            .json(errorHandling.return_error_500(error.message));
    }
});

/* PATCH student by ID */
router.patch('/students/:student_id', async function (req, res, next) {
    // Create a dict to store SQL "WHERE" params
    let where = {};
    // Validate path param
    const student_id = req.params.student_id;
    if (student_id) {
        let validation_tuple = await paramValidation.validate_student_id(
            student_id,
            paramValidation.ParamType.PATH
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(404)
                .json(
                    errorHandling.return_error_404(validation_tuple.error_msg)
                );
        }

        // Update where dict
        where.student_id = student_id;
    }

    // Retrieve request body parameters
    const team_id = req.body.team_id || null; // Optional
    const section_id = req.body.section_id || null; // Optional

    // Create a dict to store "SET" params
    let set = {};
    let proceed = false;
    let validation_tuple;

    if (team_id) {
        validation_tuple = await paramValidation.validate_team_id(
            team_id,
            paramValidation.ParamType.BODY
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_400(validation_tuple.error_msg)
                );
        }

        set.team_id = team_id;
        proceed = true;
    }
    if (section_id) {
        validation_tuple = paramValidation.build_validation_tuple(
            section_id,
            paramValidation.checkInteger(section_id),
            'Failed to convert value ' +
                section_id +
                ' for section_id to an integer.'
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_400(validation_tuple.error_msg)
                );
        }

        set.section_id = section_id;
        proceed = true;
    }

    // Make sure at least one property was provided for update
    if (!proceed) {
        return res
            .status(400)
            .json(
                errorHandling.return_error_400(
                    'At least one property must be provided.'
                )
            );
    }

    // Assemble SQL query based on provided query params
    const query = db.prepare_update_statement('students', set, where);
    // Attempt to write row to database
    try {
        const result = await db.query(
            query,
            Object.values(set).concat(Object.values(where))
        );
        // Make sure row was created
        if (!result.rowCount) {
            throw new DatabaseError(
                'Database update failed: no rows affected.'
            );
        }

        return res.status(200).json(result.rows[0]);
    } catch (error) {
        return res
            .status(500)
            .json(errorHandling.return_error_500(error.message));
    }
});

/* DELETE student by ID */
router.delete('/students/:student_id', async function (req, res, next) {
    // Validate path param
    const student_id = req.params.student_id;
    if (student_id) {
        let validation_tuple = await paramValidation.validate_student_id(
            student_id,
            paramValidation.ParamType.PATH
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(404)
                .json(
                    errorHandling.return_error_404(validation_tuple.error_msg)
                );
        }
    }

    // Delete row from database
    try {
        const result = await db.query(
            'DELETE FROM students WHERE student_id = $1',
            [student_id]
        );

        if (result.rowCount === 1) {
            return res.status(204).send();
        } else {
            throw new DatabaseError(
                'Database delete failed: no rows affected.'
            );
        }
    } catch (error) {
        return res
            .status(500)
            .json(errorHandling.return_error_500(error.message));
    }
});

module.exports = router;
