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

/* GET parts */
router.get('/parts', async function (req, res, next) {
    const supplier_id = req.query.supplier_id;
    let url = req.query.url;

    if (supplier_id) {
        let validation_tuple = await paramValidation.validate_supplier_id(
            supplier_id,
            paramValidation.ParamType.QUERY
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(404)
                .json(
                    errorHandling.return_error_404(validation_tuple.error_msg)
                );
        }
    }
    if (url) {
        url = url.includes('://') ? url : 'http://' + url;
        let validation_tuple = paramValidation.build_validation_tuple(
            url,
            paramValidation.checkURL(url),
            'Failed to convert value ' + url + ' for url to a valid URL string.'
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_400(validation_tuple.error_msg)
                );
        }
        url = url.includes('https://')
            ? url.replace('https://', 'http://')
            : url;

        req.query.url = url;
    }

    // Assemble SQL query based on provided query params
    const query = db.prepare_select_statement('parts', req.query);
    // Retrieve rows from database
    try {
        const result = await db.query(query, Object.values(req.query));

        // Transform price to number
        result.rows.forEach((row) =>
            paramTransformation.transform_part_monies(row)
        );

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

/* POST parts */
router.post('/parts', async function (req, res, next) {
    // Create dict to store SQL "INSERT INTO"/"VALUES" params
    let params = {};
    // Retrieve request body properties
    let url = req.body.url || null;
    const part_num = req.body.part_num || null; // Optional parameter
    const supplier_id = req.body.supplier_id || null;
    const description = req.body.description || null;
    const price = Number(req.body.price) || null;

    let validation_tuple;

    url = url.includes('://') ? url : 'http://' + url;
    validation_tuple = paramValidation.build_validation_tuple(
        url,
        paramValidation.checkURL(url),
        'Failed to convert value ' + url + ' for url to a valid URL string.'
    );
    if (!validation_tuple.is_valid) {
        return res
            .status(400)
            .json(errorHandling.return_error_400(validation_tuple.error_msg));
    }
    url = url.includes('https://') ? url.replace('https://', 'http://') : url;
    params.url = url;

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

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

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

    validation_tuple = paramValidation.build_validation_tuple(
        price,
        paramValidation.checkNumber(price),
        'Failed to convert value ' + price + ' for price to a number.'
    );
    if (!validation_tuple.is_valid) {
        return res
            .status(400)
            .json(errorHandling.return_error_400(validation_tuple.error_msg));
    }
    params.price = price.toFixed(2);

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

        // Transform price to number
        result.rows[0] = paramTransformation.transform_part_monies(
            result.rows[0]
        );

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

/* GET part by ID */
router.get('/parts/:part_id', async function (req, res, next) {
    const part_id = req.params.part_id;
    if (part_id) {
        let validation_tuple = await paramValidation.validate_part_id(
            part_id,
            paramValidation.ParamType.QUERY
        );
        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 parts WHERE part_id = $1',
            [part_id]
        );

        // Transform price to number
        result.rows[0] = paramTransformation.transform_part_monies(
            result.rows[0]
        );

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

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

    let url = req.body.url || null;
    const part_num = req.body.part_num || null; // Optional parameter
    const description = req.body.description || null;
    const price = Number(req.body.price) || null;

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

    // Validate types of properties
    if (url) {
        url = url.includes('://') ? url : 'http://' + url;
        validation_tuple = paramValidation.build_validation_tuple(
            url,
            paramValidation.checkURL(url),
            'Failed to convert value ' + url + ' for url to a valid URL string.'
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_400(validation_tuple.error_msg)
                );
        }
        url = url.includes('https://')
            ? url.replace('https://', 'http://')
            : url;

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

        set.description = description;
        proceed = true;
    }
    if (price) {
        // does the price attribute in the PART table need a > 0 CONSTRAINT and a check here? *********************************
        validation_tuple = paramValidation.build_validation_tuple(
            price,
            paramValidation.checkNumber(price),
            'Failed to convert value ' + price + ' for price to a number.'
        );
        if (!validation_tuple.is_valid) {
            return res
                .status(400)
                .json(
                    errorHandling.return_error_400(validation_tuple.error_msg)
                );
        }

        set.price = price.toFixed(2);
        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('parts', 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.'
            );
        }

        // Transform price to number
        result.rows[0] = paramTransformation.transform_part_monies(
            result.rows[0]
        );

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

/* DELETE part by ID */
router.delete('/parts/:part_id', async function (req, res, next) {
    // Validate path param
    const part_id = req.params.part_id;
    if (part_id) {
        let validation_tuple = await paramValidation.validate_part_id(
            part_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 parts WHERE part_id = $1', [
            part_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;
