createSearchHistoryRecord.ts

import { executeQuery } from '../executeQuery'

const USER_SEARCH_HISTORY_LIMIT = 30

/**
 * returns '$[0], $[1], $[2], ..., $[limit - 1]'
 */
function generateRange(limit: number) {
  return new Array(limit - 1)
    .fill(0)
    .map((value, index) => `$[${index}]`)
    .join(',')
}

function getFirst30Records(sql: string) {
  return `JSON_EXTRACT(${sql}, ${generateRange(USER_SEARCH_HISTORY_LIMIT)})`
}

function insertRecordAndReturnNewArray(sql: string) {
  return `JSON_ARRAY_INSERT(${sql}, '$[0]', ?)`
}

function removeRecordAndReturnNewArray() {
  return `IFNULL(IFNULL(JSON_REMOVE(search_history, JSON_UNQUOTE(JSON_SEARCH(search_history, 'one', ?))), search_history), '[]')`
}

function updateSearchHistory(sql: string) {
  return `UPDATE users SET search_history = ${sql} WHERE user_id = ?`
}

export function createSearchHistoryRecord({ userId, record }: { userId: string; record: string }) {
  /* eslint-disable prettier/prettier */
  const sql =
    updateSearchHistory(
      getFirst30Records(
        insertRecordAndReturnNewArray(
          removeRecordAndReturnNewArray()
        )
      ),
    )
  /* eslint-enable prettier/prettier */

  return executeQuery(sql, [record, userId, record])
}