-- ============================================================================
-- SQL для очистки дубликатов в documents_meta
-- ============================================================================
-- Удаляет дубликаты, оставляя только самую новую запись для каждого field_name
-- ============================================================================

-- $1 = claim_id (UUID)

UPDATE clpr_claims
SET payload = jsonb_set(
  payload,
  '{documents_meta}',
  (
    SELECT COALESCE(jsonb_agg(doc ORDER BY (doc->>'uploaded_at') DESC NULLS LAST), '[]'::jsonb)
    FROM (
      SELECT DISTINCT ON (doc->>'field_name') doc
      FROM jsonb_array_elements(COALESCE(payload->'documents_meta', '[]'::jsonb)) AS doc
      ORDER BY 
        doc->>'field_name',
        -- Приоритет: записи с file_url важнее, потом по дате
        CASE WHEN doc->>'file_url' IS NOT NULL AND doc->>'file_url' <> '' THEN 0 ELSE 1 END,
        (doc->>'uploaded_at') DESC NULLS LAST
    ) unique_docs
  ),
  true
),
updated_at = now()
WHERE id = $1
RETURNING 
  id,
  jsonb_array_length(payload->'documents_meta') AS documents_meta_count,
  (
    SELECT jsonb_agg(doc->>'field_name')
    FROM jsonb_array_elements(payload->'documents_meta') AS doc
  ) AS field_names;