// Import "pg" module
const { Pool } = require('pg');

// Configure database connection
const pool = new Pool({
    user: process.env.POSTGRES_USER,
    host: process.env.POSTGRES_HOST,
    database: process.env.POSTGRES_DB,
    password: process.env.POSTGRES_PASSWORD,
    port: process.env.POSTGRES_PORT,
});

// Log connection success/failure
pool.on('connect', () => {
    console.log('Connected to PostgreSQL database.');
});
pool.on('error', (error) => {
    console.error('Connection to PostgreSQL database failed: ', error);
});

// Function that assembles prepared statements for SELECT query execution
// IN: table : table name string like "orders"
//     params : dict of parameters to be inserted (possibly req.query)
// OUT: prepared statement string with inserted parameters
function prepare_select_statement(table, params) {
    let query = 'SELECT * FROM ' + table;
    // Loop through params and assemble prepared statement string
    for (let i = 0; i < Object.keys(params).length; i++) {
        if (query.includes('WHERE')) {
            query += ' AND ';
        } else {
            query += ' WHERE ';
        }
        query += Object.keys(params)[i] + ' = $' + (i + 1);
    }
    query += ' ORDER BY ' + table.substring(0, table.length - 1) + '_id';

    return query;
}

// Function that assembles prepared statements for complex SELECT query execution (i.e., with JOIN clauses)
// IN: from : string like "users JOIN students ON users.user_id = students.user_id JOIN teams ON students.team_id = teams.team_id"
//     params : dict of parameters to be inserted (in form: { table_name: [ { param_name: param_value }, ], })
// OUT: prepared statement string with inserted parameters
function prepare_complex_select_statement(from, params) {
    const primary_table = from.substring(0, from.indexOf(' '));
    let query = 'SELECT ' + primary_table + '.* FROM ' + from;
    // Loop through params and assemble prepared statement string
    for (let i = 0; i < Object.keys(params).length; i++) {
        const current_param_table = Object.keys(params)[i];
        for (let j = 0; j < params[current_param_table].length; j++) {
            const current_param_array = params[current_param_table];
            for (let k = 0; k < current_param_array.length; k++) {
                const current_param = current_param_array[k];
                if (query.includes('WHERE')) {
                    query += ' AND ';
                } else {
                    query += ' WHERE ';
                }
                query +=
                    current_param_table +
                    '.' +
                    Object.keys(current_param) +
                    ' = $' +
                    (i + 1);
            }
        }
    }
    query +=
        ' ORDER BY ' +
        primary_table +
        '.' +
        primary_table.substring(0, primary_table.length - 1) +
        '_id';

    return query;
}

// Function that assembles prepared statements for INSERT INTO query execution
// IN: table : table name string like "orders"
//     params : dict of parameters to be inserted
// OUT: prepared statement string with inserted parameters
function prepare_insert_statement(table, params) {
    let query = 'INSERT INTO ' + table + '(';
    // Add param names to statement
    for (let i = 0; i < Object.keys(params).length; i++) {
        query += Object.keys(params)[i];
        if (i + 1 !== Object.keys(params).length) {
            query += ', ';
        }
    }
    query += ') VALUES (';
    // Loop through params and assemble prepared statement string
    for (let i = 0; i < Object.keys(params).length; i++) {
        if (i + 1 === Object.keys(params).length) {
            query += '$' + (i + 1) + ')';
        } else {
            query += '$' + (i + 1) + ',';
        }
    }

    // Add "RETURNING *" clause to query so that result of insertion is returned
    query += ' RETURNING *';

    return query;
}

// Function that assembles prepared statements for UPDATE query execution
// IN: table : table name string like "orders"
//     set : dict of parameters for SQL "SET" clause
//     where : dict of parameters for SQL "WHERE" clause
// OUT: prepared statement string with inserted parameters
function prepare_update_statement(table, set, where) {
    let query = 'UPDATE ' + table + ' SET ';
    // Loop through params and add to prepared statement
    for (let i = 0; i < Object.keys(set).length; i++) {
        query += Object.keys(set)[i] + ' = $' + (i + 1);
        if (i + 1 !== Object.keys(set).length) {
            query += ', ';
        }
    }
    // Loop through params (req.query object) and assemble prepared statement string
    for (let i = 0; i < Object.keys(where).length; i++) {
        if (query.includes('WHERE')) {
            query += ' AND ';
        } else {
            query += ' WHERE ';
        }
        query +=
            Object.keys(where)[i] + ' = $' + (Object.keys(set).length + i + 1);
    }

    // Add "RETURNING *" clause to query so that result of insertion is returned
    query += ' RETURNING *';

    return query;
}

// Make connection available throughout app
module.exports = {
    query: (text, params) => pool.query(text, params), // Allows executing queries
    pool, // Export pool in case it needs to be accessed
    prepare_select_statement: (table, params) =>
        prepare_select_statement(table, params),
    prepare_complex_select_statement: (from, params) =>
        prepare_complex_select_statement(from, params),
    prepare_insert_statement: (table, params) =>
        prepare_insert_statement(table, params),
    prepare_update_statement: (table, params, where) =>
        prepare_update_statement(table, params, where),
};
