Art-gallery/server/search-service.js

const pool = require('./db');

const VALID_TYPES = new Set(['artist', 'painting', 'movement']);

const TRANSLATION_MATCH = (entityType, entityAlias, fieldName, localeParam) => `
  EXISTS (
    SELECT 1 FROM entity_translations t
    WHERE t.entity_type = '${entityType}'
      AND t.entity_id = ${entityAlias}.id
      AND t.locale = ${localeParam}
      AND t.field_name = '${fieldName}'
      AND t.status IN ('published', 'reviewed')
      AND t.value ILIKE $1
  )`;

function parseTypes(typesParam) {
  if (!typesParam || typeof typesParam !== 'string') {
    return ['artist', 'movement', 'painting'];
  }
  const parsed = typesParam
    .split(',')
    .map((t) => t.trim().toLowerCase())
    .filter((t) => VALID_TYPES.has(t));
  return parsed.length > 0 ? parsed : ['artist', 'movement', 'painting'];
}

async function searchArtists(pattern, prefixPattern, perTypeLimit, locale = 'en') {
  const localeParam = locale !== 'en' ? '$4' : null;
  const translationClause = locale !== 'en'
    ? ` OR ${TRANSLATION_MATCH('artist', 'a', 'name', localeParam)}`
    : '';
  const params = locale !== 'en'
    ? [pattern, prefixPattern, perTypeLimit, locale]
    : [pattern, prefixPattern, perTypeLimit];

  const { rows } = await pool.query(
    `SELECT a.id, a.name, a.birth_year, a.death_year,
            a.portrait_path, a.portrait_thumb_path, a.movement_id,
            m.name AS movement_name,
            CASE WHEN a.name ILIKE $2 THEN 0 ELSE 1 END AS rank
     FROM artists a
     LEFT JOIN art_movements m ON a.movement_id = m.id
     WHERE a.name ILIKE $1
        OR a.wikipedia_title ILIKE $1
        OR m.name ILIKE $1
        ${translationClause}
     ORDER BY rank, a.name
     LIMIT $3`,
    params,
  );

  if (locale === 'en') {
    return rows.map((row) => ({
      type: 'artist',
      id: row.id,
      name: row.name,
      birth_year: row.birth_year,
      death_year: row.death_year,
      movement_name: row.movement_name,
      portrait_path: row.portrait_path,
      portrait_thumb_path: row.portrait_thumb_path,
      rank: row.rank,
    }));
  }

  const {
    loadTranslationsByType,
    PUBLIC_STATUSES,
  } = require('./translation-service');

  const artistIds = rows.map((r) => r.id);
  const movementIds = rows.map((r) => r.movement_id).filter(Boolean);
  const artistMap = await loadTranslationsByType('artist', artistIds, locale, ['name'], PUBLIC_STATUSES);
  const movementMap = movementIds.length
    ? await loadTranslationsByType('movement', movementIds, locale, ['name'], PUBLIC_STATUSES)
    : new Map();

  const localized = rows.map((row) => {
    const name = artistMap.get(`${row.id}:name`) || row.name;
    let movement_name = row.movement_name;
    if (row.movement_id && movementMap.has(`${row.movement_id}:name`)) {
      movement_name = movementMap.get(`${row.movement_id}:name`);
    }
    return {
      type: 'artist',
      id: row.id,
      name,
      birth_year: row.birth_year,
      death_year: row.death_year,
      movement_name,
      portrait_path: row.portrait_path,
      portrait_thumb_path: row.portrait_thumb_path,
      rank: row.rank,
    };
  });

  return localized;
}

async function searchMovements(pattern, prefixPattern, perTypeLimit, locale = 'en') {
  const localeParam = locale !== 'en' ? '$4' : null;
  const movementTrans = locale !== 'en'
    ? ` OR ${TRANSLATION_MATCH('movement', 'm', 'name', localeParam)}`
    : '';
  const eraTrans = locale !== 'en'
    ? ` OR ${TRANSLATION_MATCH('era', 'e', 'name', localeParam)}`
    : '';
  const params = locale !== 'en'
    ? [pattern, prefixPattern, perTypeLimit, locale]
    : [pattern, prefixPattern, perTypeLimit];

  const { rows } = await pool.query(
    `SELECT m.id, m.name, m.color, m.start_year, m.end_year, m.era_id,
            e.name AS era_name,
            CASE WHEN m.name ILIKE $2 THEN 0 ELSE 1 END AS rank
     FROM art_movements m
     LEFT JOIN historical_eras e ON m.era_id = e.id
     WHERE m.name ILIKE $1
        OR e.name ILIKE $1
        ${movementTrans}
        ${eraTrans}
     ORDER BY rank, m.name
     LIMIT $3`,
    params,
  );

  if (locale === 'en') {
    return rows.map((row) => ({
      type: 'movement',
      id: row.id,
      name: row.name,
      color: row.color,
      start_year: row.start_year,
      end_year: row.end_year,
      era_name: row.era_name,
      rank: row.rank,
    }));
  }

  const { loadTranslationsByType, PUBLIC_STATUSES } = require('./translation-service');
  const movementMap = await loadTranslationsByType(
    'movement',
    rows.map((r) => r.id),
    locale,
    ['name'],
    PUBLIC_STATUSES,
  );
  const eraMap = await loadTranslationsByType(
    'era',
    rows.map((r) => r.era_id).filter(Boolean),
    locale,
    ['name'],
    PUBLIC_STATUSES,
  );

  return rows.map((row) => ({
    type: 'movement',
    id: row.id,
    name: movementMap.get(`${row.id}:name`) || row.name,
    color: row.color,
    start_year: row.start_year,
    end_year: row.end_year,
    era_name: (row.era_id && eraMap.get(`${row.era_id}:name`)) || row.era_name,
    rank: row.rank,
  }));
}

async function searchPaintings(pattern, prefixPattern, perTypeLimit, locale = 'en') {
  const localeParam = locale !== 'en' ? '$4' : null;
  const paintingTrans = locale !== 'en'
    ? ` OR ${TRANSLATION_MATCH('painting', 'p', 'title', localeParam)}`
    : '';
  const artistTrans = locale !== 'en'
    ? ` OR ${TRANSLATION_MATCH('artist', 'a', 'name', localeParam)}`
    : '';
  const params = locale !== 'en'
    ? [pattern, prefixPattern, perTypeLimit, locale]
    : [pattern, prefixPattern, perTypeLimit];

  const { rows } = await pool.query(
    `SELECT p.id, p.title, p.year, p.thumbnail_path, p.image_path,
            a.id AS artist_id, a.name AS artist_name,
            m.name AS movement_name,
            CASE WHEN p.title ILIKE $2 THEN 0 ELSE 1 END AS rank
     FROM paintings p
     JOIN artists a ON p.artist_id = a.id
     LEFT JOIN art_movements m ON a.movement_id = m.id
     WHERE p.title ILIKE $1
        OR p.wikipedia_title ILIKE $1
        OR a.name ILIKE $1
        OR m.name ILIKE $1
        OR CAST(p.year AS TEXT) ILIKE $1
        ${paintingTrans}
        ${artistTrans}
     ORDER BY rank, p.year NULLS LAST, p.title
     LIMIT $3`,
    params,
  );

  if (locale === 'en') {
    return rows.map((row) => ({
      type: 'painting',
      id: row.id,
      title: row.title,
      year: row.year,
      artist_id: row.artist_id,
      artist_name: row.artist_name,
      movement_name: row.movement_name,
      thumbnail_path: row.thumbnail_path,
      image_path: row.image_path,
      rank: row.rank,
    }));
  }

  const { loadTranslationsByType, PUBLIC_STATUSES } = require('./translation-service');
  const paintingMap = await loadTranslationsByType(
    'painting',
    rows.map((r) => r.id),
    locale,
    ['title'],
    PUBLIC_STATUSES,
  );
  const artistMap = await loadTranslationsByType(
    'artist',
    rows.map((r) => r.artist_id),
    locale,
    ['name'],
    PUBLIC_STATUSES,
  );

  return rows.map((row) => ({
    type: 'painting',
    id: row.id,
    title: paintingMap.get(`${row.id}:title`) || row.title,
    year: row.year,
    artist_id: row.artist_id,
    artist_name: artistMap.get(`${row.artist_id}:name`) || row.artist_name,
    movement_name: row.movement_name,
    thumbnail_path: row.thumbnail_path,
    image_path: row.image_path,
    rank: row.rank,
  }));
}

async function searchCatalog(query, options = {}) {
  const q = String(query || '').trim();
  if (q.length < 2) {
    return { q, locale: options.locale || 'en', results: [] };
  }

  const locale = options.locale || 'en';
  const limit = Math.min(50, Math.max(1, Number(options.limit) || 20));
  const types = parseTypes(options.types);
  const perTypeLimit = Math.max(1, Math.ceil(limit / types.length));
  const pattern = `%${q}%`;
  const prefixPattern = `${q}%`;

  const tasks = [];
  if (types.includes('artist')) tasks.push(searchArtists(pattern, prefixPattern, perTypeLimit, locale));
  if (types.includes('movement')) tasks.push(searchMovements(pattern, prefixPattern, perTypeLimit, locale));
  if (types.includes('painting')) tasks.push(searchPaintings(pattern, prefixPattern, perTypeLimit, locale));

  const groups = await Promise.all(tasks);
  const merged = groups
    .flat()
    .sort((a, b) => {
      if (a.rank !== b.rank) return a.rank - b.rank;
      const typeOrder = { artist: 0, movement: 1, painting: 2 };
      if (typeOrder[a.type] !== typeOrder[b.type]) return typeOrder[a.type] - typeOrder[b.type];
      const labelA = a.type === 'painting' ? a.title : a.name;
      const labelB = b.type === 'painting' ? b.title : b.name;
      return String(labelA).localeCompare(String(labelB));
    })
    .slice(0, limit)
    .map(({ rank: _rank, ...rest }) => rest);

  return { q, locale, results: merged };
}

module.exports = { searchCatalog };