import pymysql
import xml.etree.ElementTree as ET
from xml.dom import minidom

def format_xml(elem):
    rough_string = ET.tostring(elem, 'utf-8')
    reparsed = minidom.parseString(rough_string)
    return reparsed.toprettyxml(indent="  ")

conn = pymysql.connect(host='85.215.144.168', port=3306, user='asesoft', password='Pantera1', database='pastelerias', cursorclass=pymysql.cursors.DictCursor)

def test_generate_xml(alb_id):
    with conn.cursor() as cur:
        # Cabecera
        cur.execute("""
            SELECT a.id, a.serie, a.numero, a.fecha, a.total, a.empresa_id, a.cliente_id, a.clientedestino_id, a.id_pedido,
                   a.observaciones1, a.edi_referencia_pedido_cliente, a.edi_numero_sscc,
                   c.nif AS cliente_nif, c.nombre_fiscal AS cliente_nombre, c.ean AS cliente_gln,
                   d.ean_receptor AS destino_ean_receptor, d.ean_cliente AS destino_ean_cliente,
                   d.ean_emisor AS destino_ean_emisor, d.ean_facturacion AS destino_ean_facturacion,
                   d.departamento AS destino_departamento, d.centro AS destino_centro,
                   COALESCE(p1.numero_pedido, p2.numero_pedido, '') AS numero_pedido
            FROM ge_albaranes a
            LEFT JOIN ge_clientes c ON a.cliente_id = c.id
            LEFT JOIN ge_clientes_destinos d ON a.clientedestino_id = d.id
            LEFT JOIN ge_pedidos p1 ON a.id_pedido = p1.id
            LEFT JOIN ge_envios_recogidas r ON r.albaran_id = a.id AND r.estado_codigo <> 5
            LEFT JOIN ge_pedidos p2 ON r.pedido_id = p2.id
            WHERE a.id = %s
        """, (alb_id,))
        cab = cur.fetchone()

        # Lineas
        cur.execute("""
            SELECT l.id, l.posicion, l.descripcion, l.cantidad, l.bultos, l.precio, l.lotes, l.fecha_caducidad,
                   l.articulo_id, a.id AS articulo_codigo, a.ean AS articulo_ean
            FROM ge_albaranes_lineas l
            LEFT JOIN ge_articulos a ON l.articulo_id = a.id
            WHERE l.albaran_id = %s
            ORDER BY l.posicion, l.id
        """, (alb_id,))
        det = cur.fetchall()

        # Bultos
        cur.execute("""
            SELECT id, numero_bulto, bulto_padre_id, nivel, tipo_embalaje, sscc
            FROM ge_albaranes_bultos
            WHERE albaran_id = %s AND (nivel = 3 OR (nivel = 2 AND id NOT IN (SELECT DISTINCT bulto_padre_id FROM ge_albaranes_bultos WHERE albaran_id = %s AND bulto_padre_id IS NOT NULL)))
            ORDER BY numero_bulto ASC, id ASC
        """, (alb_id, alb_id))
        bultos = cur.fetchall()

        print(f"=== ALBARAN {cab['serie']}-{cab['numero']} (ID {alb_id}) ===")
        print(f"Total lineas: {len(det)}, Total bultos en ge_albaranes_bultos: {len(bultos)}")
        for b in bultos[:3]:
            cur.execute("""
                SELECT lb.id, lb.cantidad, COALESCE(lb.lote, '') AS lote, lb.fecha_caducidad,
                       al.articulo_id, COALESCE(al.descripcion, 'ARTICULO') AS articulo_desc,
                       COALESCE(art.ean, al.edi_ean_enviado, '') AS ean
                FROM ge_albaranes_lineas_bultos lb
                INNER JOIN ge_albaranes_lineas al ON lb.linea_albaran_id = al.id
                LEFT JOIN ge_articulos art ON al.articulo_id = art.id
                WHERE lb.bulto_id = %s
                ORDER BY lb.id ASC
            """, (b['id'],))
            contents = cur.fetchall()
            print(f"  Bulto id {b['id']} (num {b['numero_bulto']}): {contents}")

for aid in [7175, 7176, 7178]:
    test_generate_xml(aid)