export interface ParameterizedPieces {
    condition: string;
    inputs: Record<string, any>;
}

export interface InsertPieces {
    columns: string;
    values: string;
    inputs: Record<string, any>;
}

/**
 * Converts an object into a parameterized WHERE/SET condition string.
 * Generates `@param` placeholders instead of inline values to prevent SQL injection.
 *
 * @param data - The key/value pairs to parameterize
 * @param prefix - Prefix for parameter names (default: 'w' for WHERE)
 * @returns Parameterized condition and input values for request.input()
 */
export const getConditionPieces = (data: Record<string, any> = {}, prefix: string = 'w'): ParameterizedPieces => {
    const keys = Object.keys(data).filter(key => isValidIdentifier(key));
    const condition = keys.map((key, i) => `${key} = @${prefix}_${i}`).join(' AND ');
    const inputs: Record<string, any> = {};
    keys.forEach((key, i) => {
        inputs[`${prefix}_${i}`] = sanitizeValue(data[key]);
    });
    return { condition, inputs };
};

/**
 * Converts an object into a parameterized INSERT fragment.
 *
 * @param data - The key/value pairs to insert
 * @returns Column names, parameter placeholders, and input values for request.input()
 */
export const getInsertPieces = (data: Record<string, any> = {}): InsertPieces => {
    const keys = Object.keys(data).filter(key => isValidIdentifier(key));
    const paramNames = keys.map((_, i) => `@i_${i}`);
    const columns = keys.join(', ');
    const values = paramNames.join(', ');
    const inputs: Record<string, any> = {};
    keys.forEach((key, i) => {
        inputs[`i_${i}`] = sanitizeValue(data[key]);
    });
    return { columns, values, inputs };
};

/**
 * Validates that a SQL identifier (table/column name) contains only safe characters.
 */
export const isValidIdentifier = (name: string): boolean => {
    return /^[a-zA-Z_][a-zA-Z0-9_]*$/.test(name);
};

/**
 * Sanitizes a value for use as a SQL parameter.
 * Converts undefined/NaN to null, booleans to 0/1, bigint to string.
 */
function sanitizeValue(value: any): any {
    if (value === undefined || value === null || (typeof value === 'number' && isNaN(value))) {
        return null;
    }
    if (typeof value === 'boolean') {
        return value ? 1 : 0;
    }
    if (typeof value === 'bigint') {
        return value.toString();
    }
    return value;
}