import mysql.connector
from datetime import datetime
from db_utils import get_db, normalizephone

def safe_fetch_one(cursor, query, params=None):
    """Función segura para obtener un solo resultado"""
    try:
        cursor.execute(query, params or ())
        results = cursor.fetchall()
        return results[0] if results else None
    except Exception as e:
        print(f"Error en consulta: {e}")
        return None

def importar_whatsapp_logs():
    print("---------------------------------------------")
    print("Importando logs de WhatsApp - Todos los registros")
    print("---------------------------------------------")
    db = get_db()
    cursor = db.cursor(dictionary=True)

    cursor.execute("""
        SELECT *, replace(normalizartelefono(replace(target, '@c.us', '')), '+', '') as target_normalizado, replace(normalizartelefono(replace(source, '@c.us', '')), '+', '') as source_normalizado FROM nslog
        WHERE accion IN ('Contesto WP', 'WP message')
        AND detalles != '' and mail not like '%@g.us' and mail not like '%@broadcast'
        AND detalles IS NOT NULL
        and (status is null or status != 1)
        ORDER BY id ASC
    """)
    logs = cursor.fetchall()

    for log in logs:
        try:
            # Obtener target y source del log
            target = log["target_normalizado"]
            source = log["source_normalizado"]
            
            # Limpiar números (quitar @c.us)
            if target and '@c.us' in target:
                target = target.replace('@c.us', '')
            if source and '@c.us' in source:
                source = source.replace('@c.us', '')
            
            #target = normalizephone(target) if target else None
            #source = normalizephone(source) if source else None
            
            print(f"🔍 Analizando log {log['id']}: target={target}, source={source}")
            
            # Buscar si source está en cmtelwp
            user_phone = None
            contact_phone = None
            direction = None
            
            # Verificar source primero
            if source:
                print(f"🔍 Buscando source en cmtelwp: {source}")
                source_result = safe_fetch_one(
                    cursor, 
                    "SELECT numeronormalizado FROM cmtelwp WHERE numeronormalizado = %s LIMIT 1", 
                    (source,)
                )
                
                if source_result:                    
                    # Source está en cmtelwp, es un mensaje saliente
                    direction = "outgoing"
                    user_phone = source
                    contact_phone = target
                    print(f"✅ Source encontrado en cmtelwp: {source} -> OUTGOING")
                else:
                    # Source no está en cmtelwp, verificar si target está
                    if target:
                        print(f"🔍 Buscando target en cmtelwp: {target}")
                        target_result = safe_fetch_one(
                            cursor, 
                            "SELECT numeronormalizado FROM cmtelwp WHERE numeronormalizado = %s LIMIT 1", 
                            (target,)
                        )
                        
                        if target_result:
                            # Target está en cmtelwp, es un mensaje entrante
                            direction = "incoming"
                            user_phone = target
                            contact_phone = source
                            print(f"✅ Target encontrado en cmtelwp: {target} -> INCOMING")
                        else:
                            print(f"⚠️ Ni source ({source}) ni target ({target}) están en cmtelwp")
                            continue
                    else:
                        print(f"⚠️ Source no está en cmtelwp y no hay target válido")
                        continue
            else:
                # No hay source, verificar solo target
                if target:
                    print(f"🔍 Buscando target en cmtelwp: {target}")
                    target_result = safe_fetch_one(
                        cursor, 
                        "SELECT numeronormalizado FROM cmtelwp WHERE numeronormalizado = %s LIMIT 1", 
                        (target,)
                    )
                    
                    if target_result:
                        direction = "incoming"
                        user_phone = target
                        contact_phone = None  # No hay source para determinar contacto
                        print(f"✅ Target encontrado en cmtelwp: {target} -> INCOMING (sin source)")
                    else:
                        print(f"⚠️ Target no está en cmtelwp: {target}")
                        continue
                else:
                    print(f"⚠️ No hay source ni target válidos")
                    continue

            if not contact_phone:
                print(f"⚠️ No se puede determinar el contacto para el mensaje")
                continue

            print(f"🔍 Direccion: {direction} {contact_phone} ({user_phone})")
            content = log["detalles"]
            timestamp = log["fecha"] if "fecha" in log and log["fecha"] else datetime.now()
            print(f"📱 {timestamp} {direction} - {contact_phone} ({user_phone}) : {content}")

            # Buscar usuario en cmtelwp
            row = safe_fetch_one(
                cursor, 
                "SELECT usuario FROM cmtelwp WHERE numeronormalizado = %s LIMIT 1", 
                (user_phone,)
            )
            
            if not row:
                print(f"⚠️ Usuario no encontrado en cmtelwp: {user_phone}")
                continue
            user_id = row["usuario"]

            # Buscar contacto por phone
            row = safe_fetch_one(
                cursor, 
                "SELECT id FROM contact WHERE replace(normalizartelefono(phone), '+', '') = %s LIMIT 1", 
                (contact_phone,)
            )
            
            if not row:
                print(f"⚠️ Contacto no encontrado: {contact_phone}")
                continue
            
            contact_id = row["id"]

            # Evitar duplicados por contenido y timestamp
            duplicate_check = safe_fetch_one(
                cursor,
                """
                SELECT id FROM message
                WHERE contact_id = %s AND user_id = %s AND content = %s AND timestamp = %s
                LIMIT 1
                """, 
                (contact_id, user_id, content, timestamp)
            )
            
            if duplicate_check:
                print(f"📌 Mensaje duplicado: contacto {contact_phone} a las {timestamp}")
                continue

            # Insertar mensaje
            cursor.execute("""
                INSERT INTO message (
                    user_id, contact_id, direction, content, timestamp,
                    channel, subject, address, addressname, useraddress, username
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, (
                user_id, contact_id, direction, content, timestamp,
                'WhatsApp', 'Mensaje de WhatsApp', contact_phone, contact_phone, '', ''
            ))
            db.commit()

            cursor.execute("update nslog set status = 1 where id = %s", (log["id"],))
            db.commit()

            print(f"✅ Mensaje guardado: {direction} {contact_phone} ({user_phone})")
            
        except Exception as e:
            print(f"❌ Error procesando log {log['id']}: {str(e)}")
            continue

if __name__ == "__main__":
    importar_whatsapp_logs()