/**
 *
 * @param sql SQL string with named placeholders
 * @param params Object of placeholder keys with values at the correct locations
 * @returns return an object ```{sql: sql string with mysql placeholders, parameters: array of values at the correct locations}```
 */
export const toNamedPlaceholders = (sql: string, params: {[key: string]: any}): {sql: string, parameters: any[]} => {
  const values: any[] = [];
  const paramMap = new Map<string, number>(); // Track parameter positions

  // Enhanced regex with word boundaries and case sensitivity
  const processedSql = sql.replace(/:([a-zA-Z_][a-zA-Z0-9_]*)\b/g, (_match, paramName) => {
    if (params.hasOwnProperty(paramName)) {
      let paramIndex = paramMap.get(paramName);
      if (paramIndex === undefined) {
        // First occurrence of this parameter - add to values array
        paramIndex = values.length;
        paramMap.set(paramName, paramIndex);
        values.push(params[paramName]);
      }
      // For subsequent occurrences, push the same value again
      else {
        values.push(params[paramName]);
      }
      return '?';
    } else {
      throw new Error(`Missing parameter: ${paramName} in SQL query`);
    }
  });

  return {sql: processedSql, parameters: values};
}