-- ============================================================================
-- SQL запрос для получения документа по claim_document_id
-- ============================================================================
-- Параметры:
--   $1 :: uuid  -- claim_id (ID жалобы)
--   $2 :: uuid  -- claim_document_id (ID документа из таблицы clpr_claim_documents)
-- ============================================================================

WITH c AS (
  SELECT id, id::text AS claim_id_text, payload
  FROM clpr_claims
  WHERE id = $1
)

SELECT
  cd.id                     AS claim_document_id,
  cd.claim_id::text         AS claim_id,
  cd.field_name,
  cd.file_id,
  cd.uploaded_at,
  cd.file_name,
  cd.original_file_name,
  cd.file_hash,              -- ✅ SHA-256 хеш файла (для дедупликации)
  m.file_url,
  m.file_name               AS meta_file_name,
  m.original_file_name      AS meta_original_file_name,
  -- ✅ Название документа: сначала из field_label в documents_meta, потом из documents_uploaded, потом из documents_required
  COALESCE(
    NULLIF(m.field_label, ''),
    NULLIF(du_name.document_name_from_uploaded, ''),
    NULLIF(dr_name.document_name_from_required, ''),
    cd.field_name,
    'Документ'
  ) AS document_name,
  COALESCE(
    NULLIF(m.original_file_name, ''),
    NULLIF(m.file_name, ''),
    NULLIF(cd.original_file_name, ''),
    NULLIF(cd.file_name, ''),
    NULLIF(regexp_replace(cd.file_id, '^.*/', ''), ''),
    'document.pdf'
  ) AS filename_for_upload,
  /* описание: сначала из массива edit_fields_parsed.uploads_descriptions[i],
     потом — из payload.body['uploads_descriptions[i]'],
     потом — из payload['uploads_descriptions[i]'] */
  NULLIF(
    COALESCE(ud_arr.upload_description, ud_body.upload_description, ud_root.upload_description),
    ''
  ) AS upload_description

FROM clpr_claim_documents cd
JOIN c ON c.claim_id_text = cd.claim_id::text

-- достаём i из uploads[i][j]
JOIN LATERAL (
  SELECT NULLIF((regexp_match(cd.field_name, 'uploads\[(\d+)\]'))[1], '')::int AS i1
) idx ON TRUE

-- мета по файлу (валидный URL) + название документа (field_label)
LEFT JOIN LATERAL (
  SELECT
    x.file_url::text,
    x.file_name::text,
    x.original_file_name::text,
    x.field_label::text
  FROM jsonb_to_recordset(COALESCE(c.payload->'documents_meta','[]'::jsonb))
       AS x(field_name text, file_id text, file_url text, file_name text, original_file_name text, field_label text)
  WHERE x.field_name = cd.field_name
    AND x.file_id    = cd.file_id
    AND x.file_url   ~* '^https?://'
    AND x.file_url   <> ''
  LIMIT 1
) m ON TRUE

-- название документа из documents_uploaded (fallback, если нет field_label в documents_meta)
LEFT JOIN LATERAL (
  SELECT du.name::text AS document_name_from_uploaded
  FROM jsonb_to_recordset(COALESCE(c.payload->'documents_uploaded','[]'::jsonb))
       AS du(id text, name text, field_name text, file_id text, type text)
  WHERE du.field_name = cd.field_name
    AND (du.file_id = cd.file_id OR du.file_id IS NULL)
  LIMIT 1
) du_name ON TRUE

-- название документа из documents_required (fallback через тип документа из documents_uploaded)
LEFT JOIN LATERAL (
  SELECT dr.name::text AS document_name_from_required
  FROM jsonb_to_recordset(COALESCE(c.payload->'documents_required','[]'::jsonb))
       AS dr(id text, name text, required boolean, priority int)
  -- Находим тип документа через documents_uploaded по field_name
  WHERE EXISTS (
    SELECT 1
    FROM jsonb_to_recordset(COALESCE(c.payload->'documents_uploaded','[]'::jsonb))
         AS du(id text, field_name text)
    WHERE du.field_name = cd.field_name
      AND du.id = dr.id
    LIMIT 1
  )
  LIMIT 1
) dr_name ON TRUE

-- 1) массив: payload.edit_fields_parsed.uploads_descriptions[i]
LEFT JOIN LATERAL (
  SELECT (c.payload->'edit_fields_parsed'->'uploads_descriptions')->>idx.i1 AS upload_description
) ud_arr ON TRUE

-- 2) плоские ключи в payload.body: 'uploads_descriptions[i]'
LEFT JOIN LATERAL (
  SELECT b.v AS upload_description
  FROM jsonb_each_text(COALESCE(c.payload->'body','{}'::jsonb)) AS b(k, v)
  WHERE b.k = 'uploads_descriptions[' || idx.i1::text || ']'
  LIMIT 1
) ud_body ON TRUE

-- 3) плоские ключи на корне payload: 'uploads_descriptions[i]'
LEFT JOIN LATERAL (
  SELECT r.v AS upload_description
  FROM jsonb_each_text(COALESCE(c.payload,'{}'::jsonb)) AS r(k, v)
  WHERE r.k = 'uploads_descriptions[' || idx.i1::text || ']'
  LIMIT 1
) ud_root ON TRUE

-- ✅ ФИЛЬТР: ищем конкретный документ по claim_document_id (после всех JOIN)
WHERE cd.id = $2

LIMIT 1;  -- ✅ Возвращаем только один документ