import * as constants from './constants.js'

//Returns query results from database for basic search based on parameters passed in GET request
export async function search_items_get(request, result) {
    const selectColumns = constants.requiredColumns + ',' +
    'm.mttitle AS "Matter Title",' +
    'm.mtname AS "Matter Name",' +
    'm.mtcontract AS "Contract Num",' +
    'u.udisplay AS "Assignee",' +
    'm.mtsubtype AS "Matter Type",' +
    'm.mtdeadline AS "Deadline",' +
    'last_editted.udisplay AS "Last Editor"'
    const sqlQuery = {
      text: 'SELECT ' + selectColumns + ' FROM matter AS m JOIN (SELECT udisplay, ukey FROM users) AS u ON m.mtuser = u.ukey ' +
      'JOIN (SELECT udisplay, ukey FROM users) AS last_editted ON last_editted.ukey = m.mtlastuser ' +
      'WHERE UPPER(m.mtname) LIKE CONCAT (\'%\', UPPER((SELECT $1::text)), \'%\')',
      values: [request.query.q]
    }

    request.app.locals.pool.query(sqlQuery)
      .then((queryResults) => {
				result.status(200).json({
          results_matters:queryResults.rows
        });
      })
      .catch( (err) => {
        console.log('SQL Error: ' + err.stack);
        result.status(400).json({
          results_matters:[]
        })
      })
};

//Returns query results from database for advanced  search based on parameters passed in GET request
export async function search_advanced_items_get(request, result) {
    const selectColumns = constants.requiredColumns + ',' +
    'm.mttitle AS "Matter Title",' +
    'm.mtname AS "Matter Name",' +
    'm.mtcontract AS "Contract Num",' +
    'u.udisplay AS "Assignee",' +
    'm.mtsubtype AS "Matter Type",' +
    'm.mtdeadline AS "Deadline",' +
    'last_editted.udisplay AS "Last Editor"'

    //Improvement: Omit clauses when the respective query parameter is blank.
    //For example, do not include a where-clause in the query for title when request.query.q_title is blank
    const clauses = {
      joinAssignee:'JOIN (SELECT ulname, ufname, udisplay, ukey FROM users) AS u ON m.mtuser = u.ukey',
      joinLastEditor:'JOIN (SELECT udisplay, ulname, ufname, ukey FROM users) AS last_editted ON last_editted.ukey = m.mtlastuser',
      whereAssignee:'UPPER(u.udisplay) LIKE CONCAT (\'%\', UPPER((SELECT $1::text)), \'%\')' +
      'OR UPPER(u.ulname) LIKE CONCAT (\'%\', UPPER((SELECT $1::text)), \'%\')' +
      'OR UPPER(u.ufname) LIKE CONCAT (\'%\', UPPER((SELECT $1::text)), \'%\')',
      whereTitle:'UPPER(m.mttitle) LIKE CONCAT (\'%\', UPPER((SELECT $2::text)), \'%\')',
      whereDeadline:'CAST(m.mtdeadline AS text) LIKE CONCAT (\'%\', (SELECT $3::text), \'%\')',
      whereContract:'UPPER(m.mtcontract) LIKE CONCAT (\'%\', UPPER((SELECT $4::text)), \'%\')',
      whereSubtype:'UPPER(m.mtsubtype) LIKE CONCAT (\'%\', UPPER((SELECT $5::text)), \'%\')',
      whereLastEditor:'UPPER(last_editted.udisplay) LIKE CONCAT (\'%\', UPPER((SELECT $6::text)), \'%\')' +
      'OR UPPER(last_editted.ulname) LIKE CONCAT (\'%\', UPPER((SELECT $6::text)), \'%\')' +
      'OR UPPER(last_editted.ufname) LIKE CONCAT (\'%\', UPPER((SELECT $6::text)), \'%\')'
    }

    const sqlQuery_text = 'SELECT ' + selectColumns + ' FROM matter AS m ' + clauses.joinAssignee + ' ' + clauses.joinLastEditor +
     ` WHERE (${clauses.whereAssignee}) AND (${clauses.whereTitle}) AND (${clauses.whereDeadline})` +
     ` AND (${clauses.whereContract}) AND (${clauses.whereSubtype}) AND (${clauses.whereLastEditor})`;

    const sqlQuery_values = [request.query.q_assignee, request.query.q_title, request.query.q_deadline,
      request.query.q_contract, request.query.q_subtype, request.query.q_last_editor];

    const sqlQuery = {
      text: sqlQuery_text,
      values: sqlQuery_values
    }

    request.app.locals.pool.query(sqlQuery)
      .then((queryResults) => {
				result.status(200).json({
          results_matters:queryResults.rows
        });
      })
      .catch( (err) => {
        console.log('SQL Error: 2' + err.stack);
        result.status(400).json({
          results_matters:[]
        });
      })
};
