usenet-indexer/app/services/NameService.ts

import db from '@adonisjs/lucid/services/db'
import { looksRandom } from '#services/ArchiveService'

/**
 * A release that is nothing more than one randomly named file (its own
 * filename stem). Such a file moves into the par2 set that names it exactly;
 * real groupings (par2, RAR, NZB, spot, subject, post id) are left alone.
 */
export const WEAK_RELEASE_SQL = (column: string) =>
  `EXISTS (SELECT 1 FROM releases wr WHERE wr.id = ${column} AND wr.source IN ('stem', 'weak-stem')
     AND wr.file_count <= 1 AND (wr.name !~ '[._ -]' OR NOT is_readable_name(wr.name)))`

/**
 * Whether `candidate` (e.g. from a par2 listing) should replace `current`:
 * the current name is random, or a single token without separators
 * (mOzyyxCyYUj, F8lv3w5agMDI3; too short for looksRandom), while the
 * candidate reads like a release name.
 */
export function isBetterName(current: string, candidate: string): boolean {
  if (looksRandom(candidate) || !/[._ -]/.test(candidate)) return false
  return looksRandom(current) || !/[._ -]/.test(current)
}

/** Smaller files (nfo, sfv) collide too easily on size alone. */
const MIN_SIZE_ONLY = 64 * 1024

/**
 * Gives binaries (and their files) the real filename that a par2 set lists
 * for them. See CONCEPT.md (D21).
 * - Exact: size and MD5 of the first 16 KiB both match a par2 entry. This
 *   also puts the binary in that par2 set's release.
 * - Size only, when the fingerprint is unknown: within the binary's own
 *   release, only when exactly one listed file has that size.
 */
class NameService {
  async resolve(binaryIds: number[]): Promise<number> {
    if (binaryIds.length === 0) return 0
    const exact = await db.rawQuery(
      `UPDATE binaries b SET real_name = m.name,
         release_id = CASE WHEN b.release_id IS NULL OR ${WEAK_RELEASE_SQL('b.release_id')} THEN m.release_id ELSE b.release_id END
       FROM (
         SELECT DISTINCT ON (b2.id) b2.id, rf.name, rf.release_id
         FROM binaries b2
         JOIN release_files rf ON rf.size = b2.size AND rf.md5_16k = b2.md5_16k
         WHERE b2.id = ANY(?)
         ORDER BY b2.id, rf.release_id DESC
       ) m
       WHERE b.id = m.id AND (b.real_name IS DISTINCT FROM m.name OR b.release_id IS NULL
         OR (b.release_id <> m.release_id AND ${WEAK_RELEASE_SQL('b.release_id')}))`,
      [binaryIds]
    )
    const bySize = await db.rawQuery(
      `UPDATE binaries b SET real_name = m.name
       FROM (
         SELECT b2.id, min(rf.name) AS name
         FROM binaries b2
         JOIN release_files rf ON rf.release_id = b2.release_id AND rf.size = b2.size
         WHERE b2.id = ANY(?) AND b2.real_name IS NULL AND b2.md5_16k IS NULL AND b2.size >= ?
         GROUP BY b2.id
         HAVING count(DISTINCT rf.name) = 1
       ) m
       WHERE b.id = m.id`,
      [binaryIds, MIN_SIZE_ONLY]
    )
    await db.rawQuery(
      `UPDATE files f SET real_name = b.real_name,
         release_id = CASE WHEN f.release_id IS NULL OR ${WEAK_RELEASE_SQL('f.release_id')} THEN coalesce(b.release_id, f.release_id) ELSE f.release_id END
       FROM binaries b
       WHERE b.id = ANY(?) AND b.file_id = f.id
         AND (f.real_name IS DISTINCT FROM b.real_name OR (f.release_id IS NULL AND b.release_id IS NOT NULL)
           OR (f.release_id <> b.release_id AND ${WEAK_RELEASE_SQL('f.release_id')}))`,
      [binaryIds]
    )
    return (exact.rowCount ?? 0) + (bySize.rowCount ?? 0)
  }

  /**
   * When a par2 set arrives: the binaries it can name, i.e. those matching
   * one of its entries exactly (size + first-16-KiB MD5) and those in its
   * release. Selecting every binary of the listed sizes instead picked up
   * hundreds of thousands of equal-sized volumes per set (LOG.md 2026-09-28 (18)).
   */
  async resolveForRelease(releaseId: number): Promise<number> {
    const { rows } = await db.rawQuery(
      `SELECT b.id FROM release_files rf
       JOIN binaries b ON b.md5_16k = rf.md5_16k AND b.size = rf.size
       WHERE rf.release_id = ?
       UNION
       SELECT id FROM binaries WHERE release_id = ? AND size IS NOT NULL`,
      [releaseId, releaseId]
    )
    return this.resolve(rows.map((r: { id: string }) => Number(r.id)))
  }
}

/**
 * Repair for data from before files moved into the par2 set that names them
 * (used by `release:rename`): regroups exact matches out of random-named
 * single-file releases, then renames par2 releases that still carry a
 * random name after their listing. No downloads.
 */
export async function regroupAndRenameFromPar2(): Promise<{ regrouped: number; renamed: number }> {
  const { rows } = await db.rawQuery(
    `SELECT DISTINCT b.id FROM binaries b
     JOIN release_files rf ON rf.md5_16k = b.md5_16k AND rf.size = b.size
     WHERE b.release_id IS DISTINCT FROM rf.release_id
       AND (b.release_id IS NULL OR ${WEAK_RELEASE_SQL('b.release_id')})`
  )
  const ids = rows.map((r: { id: string }) => Number(r.id))
  for (let i = 0; i < ids.length; i += 1000) await nameService.resolve(ids.slice(i, i + 1000))

  const { par2ReleaseName } = await import('#lib/release/index')
  const listings = await db.rawQuery(
    `SELECT r.id, r.name, array_agg(rf.name) AS names FROM releases r
     JOIN release_files rf ON rf.release_id = r.id WHERE r.source <> 'spot' GROUP BY r.id, r.name`
  )
  let renamed = 0
  for (const release of listings.rows) {
    const better = par2ReleaseName(release.names)
    if (!better || better === release.name || !isBetterName(release.name, better)) continue
    await db.from('releases').where('id', release.id).update({ name: better })
    renamed++
  }
  return { regrouped: ids.length, renamed }
}

const nameService = new NameService()
export default nameService