// 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');
const { sendEmail } = require('../services/emailService');

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

    let query;
    // This is a special case
    // team_id and section_id filters can only be used when type is 'student'
    // This exists so that we can get the parent type records for the subtype filters
    if (type) {
        let validation_tuple = paramValidation.validate_user_type(type);
        if (!validation_tuple.is_valid) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_400(validation_tuple.error_msg)
                );
        }

        if (type === 'student') {
            let fun;
            let from = 'users';
            let params = {
                users: [
                    {
                        type: type,
                    },
                ],
            };

            if (team_id || section_id) {
                from += ' JOIN students ON users.user_id = students.user_id';
            }

            if (team_id) {
                // This requires joining the teams table
                from += ' JOIN teams ON students.team_id = teams.team_id';
                params.teams = [
                    {
                        team_id: team_id,
                    },
                ];
            }
            if (section_id) {
                params.students = [
                    {
                        section_id: section_id,
                    },
                ];
            }

            if (team_id || section_id) {
                query = db.prepare_complex_select_statement(from, params);
            } else {
                query = db.prepare_select_statement('users', req.query);
            }
        }
    }

    if (!type || type !== 'student') {
        // Guard against usage of team_id and section_id filters when type is not 'student'
        if (team_id || section_id) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_400(
                        'Student subtype filters can only be used when the type filter is student.'
                    )
                );
        }

        query = db.prepare_select_statement('users', 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));
    }
});

/* POST users */
router.post('/users', async function (req, res, next) {
    // Create dict to store SQL "INSERT INTO"/"VALUES" params
    let params = {};
    // Retrieve request body properties
    const first_name = req.body.first_name || null;
    const last_name = req.body.last_name || null;
    const email_address = req.body.email_address
        ? req.body.email_address.toLowerCase()
        : null;
    const type = req.body.type || null;
    const team_id = req.body.team_id || null; // For type === 'student'
    const section_id = req.body.section_id || null; // For type === 'student'
    const receive_emails =
        req.body.receive_emails === undefined ||
        req.body.receive_emails === null
            ? 'on_custom'
            : req.body.receive_emails;
    const is_admin = req.body.is_admin || 'false'; // For type === 'instructor'

    let validation_tuple;
    // Validate types of other properties
    validation_tuple = paramValidation.build_validation_tuple(
        first_name,
        paramValidation.checkString(first_name),
        'Failed to convert value ' + first_name + ' for first_name to a string.'
    );
    if (!validation_tuple.is_valid) {
        return res
            .status(400)
            .json(errorHandling.return_error_400(validation_tuple.error_msg));
    }
    params.first_name = first_name;

    validation_tuple = paramValidation.build_validation_tuple(
        last_name,
        paramValidation.checkString(last_name),
        'Failed to convert value ' + last_name + ' for last_name to a string.'
    );
    if (!validation_tuple.is_valid) {
        return res
            .status(400)
            .json(errorHandling.return_error_400(validation_tuple.error_msg));
    }
    params.last_name = last_name;

    validation_tuple = paramValidation.build_validation_tuple(
        email_address,
        paramValidation.checkEmailAddress(email_address),
        'The email ' +
            email_address +
            ' for email_address is not a valid Clemson email.'
    );
    if (!validation_tuple.is_valid) {
        return res
            .status(400)
            .json(errorHandling.return_error_400(validation_tuple.error_msg));
    }
    params.email_address = email_address;

    // enum for type? then need to insert in order (user, then subtype)
    validation_tuple = paramValidation.validate_user_type(type);
    if (!validation_tuple.is_valid) {
        return res
            .status(400)
            .json(errorHandling.return_error_400(validation_tuple.error_msg));
    }
    params.type = type;

    if (receive_emails !== null) {
        validation_tuple = paramValidation.build_validation_tuple(
            receive_emails,
            paramValidation.checkString(receive_emails),
            'Failed to convert value ' +
                receive_emails +
                ' for first_name to a string.'
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_400(validation_tuple.error_msg)
                );
        }
    }
    params.receive_emails = receive_emails;

    if (team_id && type === 'student') {
        validation_tuple = paramValidation.build_validation_tuple(
            team_id,
            paramValidation.checkInteger(team_id),
            'Failed to convert value ' + team_id + ' for team_id to an integer.'
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_400(validation_tuple.error_msg)
                );
        }
    }
    if (section_id && type === 'student') {
        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)
                );
        }
    }

    if (is_admin && type === 'instructor') {
        validation_tuple =
            paramValidation.validate_instructor_is_admin(is_admin);
        if (!validation_tuple.is_valid) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_400(validation_tuple.error_msg)
                );
        }
    }

    // Assemble SQL query based on provided query params
    const query = db.prepare_insert_statement('users', params);
    // Attempt to write row to database
    let user_result = null;
    try {
        user_result = await db.query(query, Object.values(params));
        // Make sure row was created
        if (!user_result.rowCount) {
            throw new DatabaseError(
                'Database insertion failed: no rows affected.'
            );
        }
    } catch (error) {
        return res
            .status(500)
            .json(errorHandling.return_error_500(error.message));
    }

    if (type === 'instructor') {
        let instructor_params = {};
        instructor_params.user_id = user_result.rows[0].user_id;
        instructor_params.is_admin = is_admin;

        const query = db.prepare_insert_statement(
            'instructors',
            instructor_params
        );
        try {
            const result = await db.query(
                query,
                Object.values(instructor_params)
            );
            // Make sure row was created
            if (!result.rowCount) {
                throw new DatabaseError(
                    'Database insertion failed: no rows affected.'
                );
            }
        } catch (error) {
            return res
                .status(500)
                .json(errorHandling.return_error_500(error.message));
        }
    } else if (type === 'student') {
        let student_params = {};
        student_params.user_id = user_result.rows[0].user_id;
        student_params.team_id = team_id;
        student_params.section_id = section_id;

        const query = db.prepare_insert_statement('students', student_params);
        try {
            const result = await db.query(query, Object.values(student_params));
            // Make sure row was created
            if (!result.rowCount) {
                throw new DatabaseError(
                    'Database insertion failed: no rows affected.'
                );
            }
        } catch (error) {
            return res
                .status(500)
                .json(errorHandling.return_error_500(error.message));
        }
    } else {
        try {
            const result = await db.query(
                'INSERT INTO tas(user_id) VALUES ($1)',
                [user_result.rows[0].user_id]
            );
            // Make sure row was created
            if (!result.rowCount) {
                throw new DatabaseError(
                    'Database insertion failed: no rows affected.'
                );
            }
        } catch (error) {
            return res
                .status(500)
                .json(errorHandling.return_error_500(error.message));
        }
    }

    if (type === 'student') {
        sendEmail({
            to: email_address,
            subject: 'You have been added as a student in ShipME',
            html: `
            <div style="font-size: 1rem; color: black; margin-bottom: 0.5rem;">
                Hi ${first_name} ${last_name},
            </div>

            <div style="font-size: 1rem; color: black;">
                You have been added as a student in ShipME by your instructor, assigned to <strong>Section ${section_id}</strong>.
                ShipME is an application for use alongside ME 4020 to view and place requests for parts.
                <a href="https://shipmeapp.com/">Click here to visit ShipME.</a>
            </div>
            `,
        });
    }

    return res.status(201).json(user_result.rows[0]);
});

/* GET user by ID */
router.get('/users/:user_id', async function (req, res, next) {
    const user_id = req.params.user_id;
    if (user_id) {
        let validation_tuple = await paramValidation.validate_user_id(
            user_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 * FROM users WHERE user_id = $1',
            [user_id]
        );
        return res.status(200).json(result.rows[0]);
    } catch (error) {
        return res
            .status(500)
            .json(errorHandling.return_error_500(error.message));
    }
});

/* PATCH user by ID */
router.patch('/users/:user_id', async function (req, res, next) {
    // Create a dict to store SQL "WHERE" params
    let where = {};
    // Validate path param
    const user_id = req.params.user_id;
    if (user_id) {
        let validation_tuple = await paramValidation.validate_user_id(
            user_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.user_id = user_id;
    }

    // Retrieve request body parameters
    const first_name = req.body.first_name || null; // Optional
    const last_name = req.body.last_name || null; // Optional
    const email_address = req.body.email_address || null; // Optional
    const receive_emails =
        req.body.receive_emails === undefined ||
        req.body.receive_emails === null
            ? null
            : req.body.receive_emails;

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

    // Validate types of properties
    if (first_name) {
        validation_tuple = paramValidation.build_validation_tuple(
            first_name,
            paramValidation.checkString(first_name),
            'Failed to convert value ' +
                first_name +
                ' for first_name to a string.'
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_400(validation_tuple.error_msg)
                );
        }

        set.first_name = first_name;
        proceed = true;
    }
    if (last_name) {
        validation_tuple = paramValidation.build_validation_tuple(
            last_name,
            paramValidation.checkString(last_name),
            'Failed to convert value ' +
                last_name +
                ' for last_name to a string.'
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_400(validation_tuple.error_msg)
                );
        }

        set.last_name = last_name;
        proceed = true;
    }
    if (email_address) {
        validation_tuple = paramValidation.build_validation_tuple(
            email_address,
            paramValidation.checkEmailAddress(email_address),
            'Failed to verify ' +
                email_address +
                ' for email_address as valid Clemson credentials'
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_400(validation_tuple.error_msg)
                );
        }

        set.email_address = email_address.toLowerCase();
        proceed = true;
    }
    if (receive_emails !== null) {
        validation_tuple = paramValidation.build_validation_tuple(
            receive_emails,
            paramValidation.checkString(receive_emails),
            'Failed to covert value ' +
                receive_emails +
                ' for receive_emails to a string.'
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_400(validation_tuple.error_msg)
                );
        }

        set.receive_emails = receive_emails;
        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('users', 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 User by ID */
router.delete('/users/:user_id', async function (req, res, next) {
    // Validate path param
    const user_id = req.params.user_id;
    if (user_id) {
        let validation_tuple = await paramValidation.validate_user_id(
            user_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 users WHERE user_id = $1', [
            user_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;
