const finalQuery = `SELECT 
    w.warehouse_name, 
     (SELECT
	id as area_id
	FROM
	area
	WHERE id = 1) as existing_area_id,
    (SELECT
	id as branch_id
	FROM
	branch
	WHERE id = 1) AS existing_branch_id
FROM 
    warehouse w
RIGHT JOIN
    area a ON w.warehouse_area_id = a.id
    AND a.id = 1
    AND w.warehouse_name = 'test warehouse'
RIGHT JOIN 
    branch b ON w.branch_id = b.id
    AND b.id = 1
    AND w.warehouse_name = 'test warehouse'`

function buildGetResourceByParentIds({tableParams, childValue, parentValues, childUpdateId}) {
  const { parentSelects, parentSelectParams, parentJoins, parentJoinParams } = buildParentClauses({tableParams, childValue, parentValues, childUpdateId});
  const query = `SELECT ${tableParams.childTable}.${tableParams.childUniqueColumn} ${parentSelects} 
    FROM ${tableParams.childTable} ${parentJoins}`;
  const params = [...parentSelectParams, ...parentJoinParams];
  
  return { query, params }
}

function buildParentClauses({tableParams: {childTable, childUniqueColumn, childKey, parentMapping}, childValue, parentValues, childUpdateId}) {
  parentSelects = '';
  parentJoins = '';
  parentSelectParams = [];
  parentJoinParams = [];
  
  for ({parentKey, parentTable, childForeignKeyColumn} of parentMapping) {
    parentSelects += `, ( SELECT ${parentKey} 
    FROM ${parentTable} 
    WHERE ${parentKey} = ? ) AS ${childForeignKeyColumn}`
    parentSelectParams.push(parentValues[childForeignKeyColumn]);
    
    parentJoins += 
    ` RIGHT JOIN ${parentTable} 
    ON ${childTable}.${childForeignKeyColumn} = ${parentTable}.${parentKey} 
    AND ${parentTable}.${parentKey} = ?`
    parentJoinParams.push(parentValues[childForeignKeyColumn]);
    if (childValue) {
    parentJoins += ` AND ${childTable}.${childUniqueColumn} = ?`
    parentJoinParams.push(childValue);
    }
    
    if (childUpdateId) {
    parentJoins += ` AND ${childTable}.${childKey} <> ?`;
    parentJoinParams.push(childUpdateId);
    }
  }
  
  return { parentSelects, parentSelectParams, parentJoins, parentJoinParams }
}

const warehouseParams = {
  childTable : 'warehouse',
  childUniqueColumn : 'warehouse_name',
  childKey: 'id', //Optional
  parentMapping: [
    {
      parentTable : 'area',
      childForeignKeyColumn : 'warehouse_area_id',
      parentKey : 'id' // Optional
    },
    {
      parentTable : 'branch',
      childForeignKeyColumn : 'branch_id',
      parentKey : 'id' // Optional
    }
  ]
}

const warehouse2Params = { ...warehouseParams };
warehouse2Params.childUniqueColumn = 'id'

const childValue = null;
const parentValues = {
  branch_id: '1',
  warehouse_area_id: '2'
}

const queryResult = {
  warehouse_name : 'test 2',
  branch_id: null,
  warehouse_area_id: '2'
  
}

const parentKeys = Object.keys(parentValues);

for (parentKey of parentKeys) {
  if (!queryResult[parentKey]) {
    console.log(`Id does not exist for ${parentKey}`);
  }
}
if (!childValue) {
  if (queryResult[warehouseParams.childUniqueColumn]) {
    console.log(`${warehouseParams.childUniqueColumn} already exists`);
  }
} else if (childValue === queryResult[warehouseParams.childUniqueColumn]) {
  console.log(`Duplicate ${warehouseParams.childUniqueColumn}`);
}

childUpdateId = '1'

//Scenario 1: Has a unique column, has a child value that needs to be compared, and is an update transaction
const scenario1 = buildGetResourceByParentIds({tableParams: warehouseParams, parentValues, childValue, childUpdateId});


//Scenario 2: Has a unique column, has a child value that needs to be compared, and is a create transaction
const scenario2 = buildGetResourceByParentIds({tableParams: warehouseParams, parentValues, childValue});

//Scenario 3: Has a unique column, is a 1:1 relationship so the unique column is just the id, and is an update transaction
const scenario3 = buildGetResourceByParentIds({tableParams: warehouse2Params, parentValues, childUpdateId});

//Scenario 4: Has a unique column, is a 1:1 relationship so the unique column is just the id, and is a create transaction
const scenario4 = buildGetResourceByParentIds({tableParams: warehouse2Params, parentValues});

console.log(`Scenario 1`);
console.log(`Query: ${scenario1.query}`);
console.log(`Params: ${scenario1.params} \n`);

console.log(`Scenario 2`);
console.log(`Query: ${scenario2.query}`);
console.log(`Params: ${scenario2.params} \n`);

console.log(`Scenario 3`);
console.log(`Query: ${scenario3.query}`);
console.log(`Params: ${scenario3.params} \n`);

console.log(`Scenario 4`);
console.log(`Query: ${scenario4.query}`);
console.log(`Params: ${scenario4.params} \n`);