interface QueryResult {
  sql: string;
  values: unknown[];
}

const SAFE_IDENTIFIER = /^[a-zA-Z_][a-zA-Z0-9_-]*$/;

export function validateIdentifier(name: string, context: string = 'identifier'): string {
  if (!name || typeof name !== 'string' || !SAFE_IDENTIFIER.test(name)) {
    throw new Error(`Invalid SQL ${context}: "${name}". Identifiers must match ${SAFE_IDENTIFIER}`);
  }

  return name;
}

export function buildInsert(table: string, data: Record<string, unknown>): QueryResult {
  validateIdentifier(table, 'table name');

  const keys = Object.keys(data);
  keys.forEach(k => validateIdentifier(k, 'column name'));

  const placeholders = keys.map(() => '?');
  const values = keys.map(k => data[k]);

  const sql = `INSERT INTO \`${table}\` (${keys.map(k => `\`${k}\``).join(', ')}) VALUES (${placeholders.join(', ')})`;

  return { sql, values };
}

export function buildUpdate(table: string, id: unknown, data: Record<string, unknown>): QueryResult {
  validateIdentifier(table, 'table name');

  const keys = Object.keys(data);
  keys.forEach(k => validateIdentifier(k, 'column name'));

  const setClauses = keys.map(k => `\`${k}\` = ?`);
  const values = [...keys.map(k => data[k]), id];

  const sql = `UPDATE \`${table}\` SET ${setClauses.join(', ')} WHERE \`id\` = ?`;

  return { sql, values };
}

export function buildDelete(table: string, id: unknown): QueryResult {
  validateIdentifier(table, 'table name');

  return {
    sql: `DELETE FROM \`${table}\` WHERE \`id\` = ?`,
    values: [id],
  };
}

export function buildSelect(table: string, conditions?: Record<string, unknown>): QueryResult {
  validateIdentifier(table, 'table name');

  if (!conditions || Object.keys(conditions).length === 0) {
    return { sql: `SELECT * FROM \`${table}\``, values: [] };
  }

  const keys = Object.keys(conditions);
  keys.forEach(k => validateIdentifier(k, 'column name'));

  const whereClauses = keys.map(k => `\`${k}\` = ?`);
  const values = keys.map(k => conditions[k]);

  const sql = `SELECT * FROM \`${table}\` WHERE ${whereClauses.join(' AND ')}`;

  return { sql, values };
}