`db/migrations/012_pd_film_image_match_productions.sql`

34 lines
-- One-shot fuzzy match: link pd_film_image rows to film_production via title similarity.
-- Threshold 0.45 trigram similarity = "very likely the same title", err on conservative side.
-- Re-runnable: only updates rows whose production_id is currently NULL.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

WITH candidates AS (
  SELECT
    img.id   AS img_id,
    fp.id    AS production_id,
    similarity(lower(img.title), lower(fp.title)) AS sim,
    row_number() OVER (
      PARTITION BY img.id
      ORDER BY similarity(lower(img.title), lower(fp.title)) DESC, fp.year DESC NULLS LAST
    ) AS rnk
  FROM pd_film_image img
  CROSS JOIN film_production fp
  WHERE img.production_id IS NULL
    AND length(img.title) > 4
    AND length(fp.title)  > 3
    AND lower(img.title) % lower(fp.title)   -- pg_trgm fast pre-filter (uses GIN if indexed)
    AND similarity(lower(img.title), lower(fp.title)) >= 0.45
)
UPDATE pd_film_image img
SET production_id = c.production_id
FROM candidates c
WHERE c.img_id = img.id AND c.rnk = 1;

SELECT
  'matched' AS status,
  count(*) AS n
FROM pd_film_image
WHERE production_id IS NOT NULL;