crm.clientright.ru/ticket_form/fix_claim_documents_field_names.py

#!/usr/bin/env python3
"""
Исправление field_name в таблице clpr_claim_documents
Пересоздаёт записи с правильными field_name на основе documents_uploaded и documents_required
"""
import asyncio
import asyncpg
import json
from datetime import datetime

POSTGRES_HOST = "147.45.189.234"
POSTGRES_PORT = 5432
POSTGRES_DB = "default_db"
POSTGRES_USER = "gen_user"
POSTGRES_PASSWORD = "2~~9_^kVsU?2\\S"

CLAIM_ID = "bddb6815-8e17-4d54-a721-5e94382942c7"

async def fix_field_names():
    conn = await asyncpg.connect(
        host=POSTGRES_HOST,
        port=POSTGRES_PORT,
        database=POSTGRES_DB,
        user=POSTGRES_USER,
        password=POSTGRES_PASSWORD
    )
    
    try:
        # Получаем данные черновика
        row = await conn.fetchrow("""
            SELECT id, payload
            FROM clpr_claims
            WHERE id::text = $1 OR payload->>'claim_id' = $1
            ORDER BY updated_at DESC
            LIMIT 1
        """, CLAIM_ID)
        
        if not row:
            print(f"❌ Черновик {CLAIM_ID} не найден!")
            return
        
        claim_uuid = row['id']
        payload = row['payload'] if isinstance(row['payload'], dict) else json.loads(row['payload'])
        
        documents_required = payload.get('documents_required', [])
        documents_uploaded = payload.get('documents_uploaded', [])
        
        print(f"📋 documents_required: {len(documents_required)} документов")
        print(f"📋 documents_uploaded: {len(documents_uploaded)} документов")
        
        # Создаём мапу: doc_id -> group_index
        doc_id_to_index = {}
        for idx, doc_req in enumerate(documents_required):
            doc_id = doc_req.get('id')
            if doc_id:
                doc_id_to_index[doc_id] = idx
        
        print(f"\n📋 Маппинг документов:")
        for doc_id, idx in doc_id_to_index.items():
            print(f"   {doc_id} -> group_index {idx}")
        
        # Удаляем старые записи
        deleted_count = await conn.execute("""
            DELETE FROM clpr_claim_documents
            WHERE claim_id = $1
        """, str(claim_uuid))
        
        print(f"\n🗑️ Удалено старых записей: {deleted_count.split()[-1]}")
        
        # Вставляем новые записи с правильными field_name
        inserted_count = 0
        for doc_up in documents_uploaded:
            doc_type = doc_up.get('type') or doc_up.get('id')
            file_id = doc_up.get('file_id')
            
            if not doc_type or not file_id:
                print(f"   ⚠️ Пропущен документ без type/id или file_id: {doc_up}")
                continue
            
            group_index = doc_id_to_index.get(doc_type)
            if group_index is None:
                print(f"   ⚠️ Не найден group_index для типа {doc_type}")
                continue
            
            field_name = f"uploads[{group_index}][0]"
            
            # Парсим uploaded_at
            uploaded_at_str = doc_up.get('uploaded_at')
            uploaded_at = None
            if uploaded_at_str:
                try:
                    # Пробуем разные форматы даты
                    if isinstance(uploaded_at_str, str):
                        if 'T' in uploaded_at_str:
                            uploaded_at = datetime.fromisoformat(uploaded_at_str.replace('Z', '+00:00'))
                        else:
                            uploaded_at = datetime.fromisoformat(uploaded_at_str)
                    elif isinstance(uploaded_at_str, datetime):
                        uploaded_at = uploaded_at_str
                except Exception as e:
                    print(f"   ⚠️ Ошибка парсинга даты {uploaded_at_str}: {e}")
                    uploaded_at = None
            
            await conn.execute("""
                INSERT INTO clpr_claim_documents (
                    claim_id,
                    field_name,
                    file_id,
                    file_name,
                    original_file_name,
                    uploaded_at
                )
                VALUES ($1, $2, $3, $4, $5, $6)
                ON CONFLICT (claim_id, field_name) DO UPDATE SET
                    file_id = EXCLUDED.file_id,
                    file_name = EXCLUDED.file_name,
                    original_file_name = EXCLUDED.original_file_name,
                    uploaded_at = EXCLUDED.uploaded_at
            """,
                str(claim_uuid),
                field_name,
                file_id,
                doc_up.get('file_name', ''),
                doc_up.get('original_file_name', ''),
                uploaded_at
            )
            
            inserted_count += 1
            print(f"   ✅ Вставлен: {field_name} -> {doc_type} ({file_id[:50]}...)")
        
        print(f"\n✅ Вставлено новых записей: {inserted_count}")
        
        # Проверяем результат
        result_rows = await conn.fetch("""
            SELECT 
                field_name,
                file_id,
                file_name,
                original_file_name
            FROM clpr_claim_documents
            WHERE claim_id = $1
            ORDER BY field_name
        """, str(claim_uuid))
        
        print(f"\n📊 Результат в таблице ({len(result_rows)} записей):")
        for row in result_rows:
            print(f"   {row['field_name']}: {row['file_name']} ({row['file_id'][:50]}...)")
        
    finally:
        await conn.close()

if __name__ == "__main__":
    asyncio.run(fix_field_names())