aiform_dev/docs/SQL_CLAIMSAVE_FIXED_NEW_FLOW.sql

-- ============================================================================
-- Исправленный SQL для сохранения claim (claimsave) - ПОДДЕРЖКА НОВОГО ФЛОУ
-- ============================================================================
-- Проблема: SQL не сохранял documents_required и перезаписывал status_code на 'draft'
-- Решение: Сохраняем documents_required и не перезаписываем новые статусы
-- ============================================================================

WITH partial AS (
  SELECT 
    $1::jsonb AS p, 
    $2::text AS claim_id_str
),

existing_claim AS (
  SELECT 
    id,
    payload,
    status_code,
    created_at
  FROM clpr_claims
  WHERE id = (SELECT claim_id_str::uuid FROM partial)
     OR payload->>'claim_id' = (SELECT claim_id_str FROM partial)
  ORDER BY 
    CASE WHEN id = (SELECT claim_id_str::uuid FROM partial) THEN 1 ELSE 2 END,
    updated_at DESC
  LIMIT 1
),

-- Парсим documents_required (или берём из БД)
documents_required_parsed AS (
  SELECT 
    CASE 
      WHEN partial.p->'documents_required' IS NOT NULL 
        AND jsonb_typeof(partial.p->'documents_required') = 'array'
        THEN partial.p->'documents_required'
      WHEN partial.p->'edit_fields_parsed'->'documents_required' IS NOT NULL
        AND jsonb_typeof(partial.p->'edit_fields_parsed'->'documents_required') = 'array'
        THEN partial.p->'edit_fields_parsed'->'documents_required'
      WHEN EXISTS (SELECT 1 FROM existing_claim WHERE payload->'documents_required' IS NOT NULL)
        THEN (SELECT payload->'documents_required' FROM existing_claim)
      ELSE '[]'::jsonb
    END AS documents_required
  FROM partial
),

-- Парсим documents_uploaded (или берём из БД)
documents_uploaded_parsed AS (
  SELECT 
    CASE 
      WHEN partial.p->'documents_uploaded' IS NOT NULL 
        AND jsonb_typeof(partial.p->'documents_uploaded') = 'array'
        THEN partial.p->'documents_uploaded'
      WHEN EXISTS (SELECT 1 FROM existing_claim WHERE payload->'documents_uploaded' IS NOT NULL)
        THEN (SELECT payload->'documents_uploaded' FROM existing_claim)
      ELSE '[]'::jsonb
    END AS documents_uploaded
  FROM partial
),

-- Парсим documents_skipped (или берём из БД)
documents_skipped_parsed AS (
  SELECT 
    CASE 
      WHEN partial.p->'documents_skipped' IS NOT NULL 
        AND jsonb_typeof(partial.p->'documents_skipped') = 'array'
        THEN partial.p->'documents_skipped'
      WHEN EXISTS (SELECT 1 FROM existing_claim WHERE payload->'documents_skipped' IS NOT NULL)
        THEN (SELECT payload->'documents_skipped' FROM existing_claim)
      ELSE '[]'::jsonb
    END AS documents_skipped
  FROM partial
),

-- Парсим current_doc_index (или берём из БД)
current_doc_index_parsed AS (
  SELECT 
    CASE 
      WHEN partial.p->'current_doc_index' IS NOT NULL 
        THEN (partial.p->'current_doc_index')::int
      WHEN EXISTS (SELECT 1 FROM existing_claim WHERE payload->'current_doc_index' IS NOT NULL)
        THEN (SELECT (payload->'current_doc_index')::int FROM existing_claim)
      ELSE 0
    END AS current_doc_index
  FROM partial
),

-- Парсим wizard_answers
wizard_answers_parsed AS (
  SELECT 
    CASE 
      WHEN partial.p->'edit_fields_raw'->'body'->>'wizard_answers' IS NOT NULL
        THEN (partial.p->'edit_fields_raw'->'body'->>'wizard_answers')::jsonb
      WHEN partial.p->'edit_fields_parsed'->'body'->>'wizard_answers' IS NOT NULL
        THEN (partial.p->'edit_fields_parsed'->'body'->>'wizard_answers')::jsonb
      WHEN partial.p->>'wizard_answers' IS NOT NULL 
        THEN (partial.p->>'wizard_answers')::jsonb
      WHEN partial.p->'wizard_answers' IS NOT NULL 
        AND jsonb_typeof(partial.p->'wizard_answers') = 'object'
        THEN partial.p->'wizard_answers'
      ELSE '{}'::jsonb
    END AS answers
  FROM partial
),

-- Парсим wizard_plan (или берём из существующей записи)
wizard_plan_parsed AS (
  SELECT 
    CASE 
      WHEN partial.p->'edit_fields_parsed'->'wizard_plan_parsed' IS NOT NULL
        AND jsonb_typeof(partial.p->'edit_fields_parsed'->'wizard_plan_parsed') = 'object'
        THEN partial.p->'edit_fields_parsed'->'wizard_plan_parsed'
      WHEN partial.p->>'wizard_plan' IS NOT NULL 
        THEN (partial.p->>'wizard_plan')::jsonb
      WHEN partial.p->'wizard_plan' IS NOT NULL 
        AND jsonb_typeof(partial.p->'wizard_plan') = 'object'
        THEN partial.p->'wizard_plan'
      WHEN partial.p->'edit_fields_raw'->'body'->>'wizard_plan' IS NOT NULL
        THEN (partial.p->'edit_fields_raw'->'body'->>'wizard_plan')::jsonb
      WHEN EXISTS (SELECT 1 FROM existing_claim WHERE payload->'wizard_plan' IS NOT NULL)
        THEN (SELECT payload->'wizard_plan' FROM existing_claim)
      ELSE NULL
    END AS wizard_plan
  FROM partial
),

-- Парсим problem_description (или берём из БД)
problem_description_parsed AS (
  SELECT 
    CASE 
      WHEN partial.p->>'problem_description' IS NOT NULL 
        THEN partial.p->>'problem_description'
      WHEN EXISTS (SELECT 1 FROM existing_claim WHERE payload->>'problem_description' IS NOT NULL)
        THEN (SELECT payload->>'problem_description' FROM existing_claim)
      ELSE NULL
    END AS problem_description
  FROM partial
),

-- Определяем правильный статус
status_code_resolved AS (
  SELECT 
    CASE
      -- Если есть documents_required и документы загружаются - новый флоу
      WHEN (SELECT jsonb_array_length(documents_required) FROM documents_required_parsed) > 0
        THEN CASE
          -- Все документы загружены или пропущены
          WHEN (SELECT jsonb_array_length(documents_uploaded) FROM documents_uploaded_parsed) + 
               (SELECT jsonb_array_length(documents_skipped) FROM documents_skipped_parsed) >= 
               (SELECT jsonb_array_length(documents_required) FROM documents_required_parsed)
            THEN 'draft_docs_complete'
          -- Документы загружаются
          WHEN (SELECT jsonb_array_length(documents_uploaded) FROM documents_uploaded_parsed) > 0
            THEN 'draft_docs_progress'
          -- Только описание
          ELSE 'draft_new'
        END
      -- Старый флоу: проверяем wizard_answers
      WHEN (SELECT answers->>'docs_exist' FROM wizard_answers_parsed) = 'true'
        THEN 'in_work'
      -- Сохраняем существующий статус, если он новый
      WHEN EXISTS (SELECT 1 FROM existing_claim 
                   WHERE status_code IN ('draft_new', 'draft_docs_progress', 'draft_docs_complete', 'draft_claim_ready'))
        THEN (SELECT status_code FROM existing_claim)
      -- По умолчанию
      ELSE 'draft'
    END AS status_code
  FROM partial
),

-- UPSERT claim
claim_upsert AS (
  INSERT INTO clpr_claims (
    id,
    session_token,
    unified_id,
    contact_id,
    phone,
    channel,
    type_code,
    status_code,
    payload,
    created_at,
    updated_at,
    expires_at
  )
  SELECT 
    COALESCE((SELECT id FROM existing_claim), partial.claim_id_str::uuid),
    COALESCE(
      partial.p->>'session_id',
      partial.p->'edit_fields_parsed'->'body'->>'session_id',
      partial.p->'edit_fields_raw'->'body'->>'session_id',
      'sess-unknown'
    ),
    COALESCE(
      partial.p->>'unified_id',
      partial.p->'edit_fields_parsed'->'body'->>'unified_id',
      partial.p->'edit_fields_raw'->'body'->>'unified_id'
    ),
    COALESCE(
      partial.p->>'contact_id',
      partial.p->'edit_fields_parsed'->'body'->>'contact_id',
      partial.p->'edit_fields_raw'->'body'->>'contact_id'
    ),
    COALESCE(
      partial.p->>'phone',
      partial.p->'edit_fields_parsed'->'body'->>'phone',
      partial.p->'edit_fields_raw'->'body'->>'phone'
    ),
    'web_form',
    COALESCE(partial.p->>'type_code', 'consumer'),
    (SELECT status_code FROM status_code_resolved),
    jsonb_build_object(
      'claim_id', partial.claim_id_str,
      'problem_description', (SELECT problem_description FROM problem_description_parsed),
      'answers', (SELECT answers FROM wizard_answers_parsed),
      -- ✅ ОБЪЕДИНЯЕМ documents_meta с существующими (не перезаписываем!)
      'documents_meta', COALESCE(
        (SELECT p->'documents_meta' FROM partial WHERE partial.p->'documents_meta' IS NOT NULL),
        '[]'::jsonb
      ) || COALESCE(
        (SELECT payload->'documents_meta' FROM existing_claim),
        '[]'::jsonb
      ),
      -- ✅ НОВЫЙ ФЛОУ: Сохраняем documents_required и связанные поля
      'documents_required', (SELECT documents_required FROM documents_required_parsed),
      'documents_uploaded', (SELECT documents_uploaded FROM documents_uploaded_parsed),
      'documents_skipped', (SELECT documents_skipped FROM documents_skipped_parsed),
      'current_doc_index', (SELECT current_doc_index FROM current_doc_index_parsed),
      'wizard_plan', (SELECT wizard_plan FROM wizard_plan_parsed),
      'phone', COALESCE(partial.p->>'phone', (SELECT payload->>'phone' FROM existing_claim)),
      'email', COALESCE(partial.p->>'email', (SELECT payload->>'email' FROM existing_claim))
    ),
    COALESCE((SELECT created_at FROM existing_claim), now()),
    now(),
    now() + interval '14 days'
  FROM partial
  ON CONFLICT (id) DO UPDATE SET
    session_token = EXCLUDED.session_token,
    unified_id = COALESCE(EXCLUDED.unified_id, clpr_claims.unified_id),
    contact_id = COALESCE(EXCLUDED.contact_id, clpr_claims.contact_id),
    phone = COALESCE(EXCLUDED.phone, clpr_claims.phone),
    -- ✅ НЕ перезаписываем статус, если он новый (сохраняем существующий)
    status_code = CASE
      WHEN clpr_claims.status_code IN ('draft_new', 'draft_docs_progress', 'draft_docs_complete', 'draft_claim_ready')
        THEN clpr_claims.status_code  -- Сохраняем существующий новый статус
      ELSE EXCLUDED.status_code       -- Используем новый статус
    END,
    -- ✅ Объединяем payload правильно: аккуратно объединяем критичные поля
    payload = jsonb_set(
      jsonb_set(
        jsonb_set(
          jsonb_set(
            jsonb_set(
              -- Сначала берём существующий payload и объединяем с новым (без критичных полей)
              COALESCE(clpr_claims.payload, '{}'::jsonb) || 
              (EXCLUDED.payload - 'documents_meta' - 'documents_required' - 'documents_uploaded' - 'documents_skipped' - 'current_doc_index'),
              '{documents_meta}',
              -- ✅ ОБЪЕДИНЯЕМ documents_meta (не перезаписываем!)
              COALESCE(
                EXCLUDED.payload->'documents_meta',
                '[]'::jsonb
              ) || COALESCE(
                clpr_claims.payload->'documents_meta',
                '[]'::jsonb
              ),
              true
            ),
            '{documents_required}',
            COALESCE(
              EXCLUDED.payload->'documents_required',
              clpr_claims.payload->'documents_required',
              '[]'::jsonb
            ),
            true
          ),
          '{documents_uploaded}',
          COALESCE(
            EXCLUDED.payload->'documents_uploaded',
            clpr_claims.payload->'documents_uploaded',
            '[]'::jsonb
          ),
          true
        ),
        '{documents_skipped}',
        COALESCE(
          EXCLUDED.payload->'documents_skipped',
          clpr_claims.payload->'documents_skipped',
          '[]'::jsonb
        ),
        true
      ),
      '{current_doc_index}',
      COALESCE(
        EXCLUDED.payload->'current_doc_index',
        clpr_claims.payload->'current_doc_index',
        to_jsonb(0)
      ),
      true
    ),
    updated_at = now(),
    expires_at = now() + interval '14 days'
  RETURNING id, status_code, payload, unified_id, contact_id, phone, session_token
),

-- UPSERT documents (если есть)
docs_upsert AS (
  INSERT INTO clpr_claim_documents (
    claim_id, 
    field_name, 
    file_id, 
    uploaded_at, 
    file_name, 
    original_file_name
  )
  SELECT
    partial.claim_id_str AS claim_id,
    doc.field_name,
    doc.file_id,
    COALESCE((doc.uploaded_at)::timestamptz, now()),
    doc.file_name,
    doc.original_file_name
  FROM partial
  CROSS JOIN LATERAL jsonb_to_recordset(
    COALESCE(partial.p->'documents_meta', '[]'::jsonb)
  ) AS doc(
    field_name text, 
    file_id text, 
    file_name text, 
    original_file_name text, 
    uploaded_at text
  )
  WHERE partial.p->'documents_meta' IS NOT NULL
    AND jsonb_array_length(partial.p->'documents_meta') > 0
  ON CONFLICT (claim_id, field_name) DO UPDATE SET
    file_id = EXCLUDED.file_id,
    uploaded_at = EXCLUDED.uploaded_at,
    file_name = EXCLUDED.file_name,
    original_file_name = EXCLUDED.original_file_name
  RETURNING id, claim_id, field_name, file_id, file_name, original_file_name
)

-- Возвращаем результат
SELECT
  (SELECT jsonb_build_object(
    'claim_id', cu.id::text,
    'claim_id_str', (cu.payload->>'claim_id'),
    'status_code', cu.status_code,
    'unified_id', cu.unified_id,
    'contact_id', cu.contact_id,
    'phone', cu.phone,
    'session_token', cu.session_token,
    'payload', cu.payload
  ) FROM claim_upsert cu) AS claim,
  
  (SELECT jsonb_agg(jsonb_build_object(
    'id', id, 
    'field_name', field_name, 
    'file_id', file_id,
    'file_name', file_name,
    'original_file_name', original_file_name
  )) FROM docs_upsert) AS documents;