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

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

def calcular_digito_control_sscc(sscc17: str) -> int:
    # GS1 modulo 10
    total = 0
    for i, ch in enumerate(sscc17):
        n = int(ch)
        if i % 2 == 0:
            total += n * 3
        else:
            total += n * 1
    mod = total % 10
    return 0 if mod == 0 else 10 - mod

def generar_sscc18(prefix: str, correlativo: int, digito_extension: int = 3) -> str:
    # Total digits before check digit: 1 (ext) + prefix + seq = 17
    # e.g. ext 3 + prefix (9 digits) + seq (7 digits)
    prefix_clean = prefix.strip()
    digits_needed = 17 - 1 - len(prefix_clean)
    seq_str = f"{correlativo:0{digits_needed}d}"
    sscc17 = f"{digito_extension}{prefix_clean}{seq_str}"
    dc = calcular_digito_control_sscc(sscc17)
    return f"{sscc17}{dc}"

def get_next_sscc(cur, prefix):
    cur.execute("SELECT sscc FROM ge_contadores LIMIT 1")
    row = cur.fetchone()
    if not row:
        next_val = 1
        cur.execute("INSERT INTO ge_contadores (id, sscc) VALUES (1, 2)")
    else:
        next_val = row['sscc']
        cur.execute("UPDATE ge_contadores SET sscc = sscc + 1")
    return generar_sscc18(prefix, next_val, 3)

def export_desadv(alb_id, output_path):
    conn = pymysql.connect(host='85.215.144.168', port=3306, user='asesoft', password='Pantera1', database='pastelerias', cursorclass=pymysql.cursors.DictCursor)
    with conn.cursor() as cur:
        # 1. 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()

        # Empresa
        cur.execute("""
            SELECT e.cif_nif, e.nombre, e.gln AS emp_gln, ed.gln_empresa AS edicom_gln
            FROM ge_empresas e
            LEFT JOIN ge_edicom_config ed ON ed.id_empresa = e.id AND ed.activo = 1
            WHERE e.id = %s
        """, (cab['empresa_id'],))
        emp = 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()

        # Format albaran number (7 digits)
        serie = str(cab['serie']).strip()
        numero = str(cab['numero']).strip()
        combined = "".join(ch for ch in (serie + numero) if ch.isdigit())
        alb_num = f"{int(combined):07d}" if combined else f"{int(numero):07d}"

        fecha_str = cab['fecha'].strftime('%Y-%m-%dT%H:%M:%S')

        num_pedido = (cab['edi_referencia_pedido_cliente'] or '').strip()
        if not num_pedido:
            num_pedido = (cab['numero_pedido'] or '').strip()
        if not num_pedido:
            num_pedido = alb_num

        gln_emisor = (emp['edicom_gln'] or emp['emp_gln'] or emp['cif_nif'] or '').strip()
        prefix_gln = gln_emisor[:9] if len(gln_emisor) >= 7 else '843701918'

        # Receptor GLN ECI Gran Consumo: 8422416000504
        gln_receptor = '8422416000504'

        # BY - Comprador
        gln_buyer = (cab['destino_ean_receptor'] or cab['destino_ean_emisor'] or cab['destino_ean_cliente'] or cab['cliente_gln'] or '').strip()
        if '-' in gln_buyer:
            gln_buyer = gln_buyer.split('-')[0].strip()

        # DP - Punto de entrega
        gln_dp = (cab['destino_ean_receptor'] or gln_buyer).strip()
        if '-' in gln_dp:
            gln_dp = gln_dp.split('-')[0].strip()

        # Build XML
        root = ET.Element('HEADER')
        ET.SubElement(root, 'Despatch_advice_number').text = alb_num
        ET.SubElement(root, 'Project').text = 'ES_ECI_GC_OUT'
        ET.SubElement(root, 'Type').text = '398'
        ET.SubElement(root, 'Message_function').text = '9'
        ET.SubElement(root, 'Date').text = fecha_str
        ET.SubElement(root, 'Delivery_date').text = fecha_str

        # MS
        p_ms = ET.SubElement(root, 'PARTIES')
        ET.SubElement(p_ms, 'Qualifier').text = 'MS'
        ET.SubElement(p_ms, 'Identifier_type').text = 'GLN' if len(gln_emisor) == 13 and gln_emisor.isdigit() else 'VAT'
        ET.SubElement(p_ms, 'Identifier').text = gln_emisor

        # MR
        p_mr = ET.SubElement(root, 'PARTIES')
        ET.SubElement(p_mr, 'Qualifier').text = 'MR'
        ET.SubElement(p_mr, 'Identifier_type').text = 'GLN'
        ET.SubElement(p_mr, 'Identifier').text = gln_receptor

        # BY
        p_by = ET.SubElement(root, 'PARTIES')
        ET.SubElement(p_by, 'Qualifier').text = 'BY'
        ET.SubElement(p_by, 'Identifier_type').text = 'GLN' if len(gln_buyer) == 13 and gln_buyer.isdigit() else 'VAT'
        ET.SubElement(p_by, 'Identifier').text = gln_buyer

        depto = (cab['destino_departamento'] or '').strip()
        while len(depto) > 3 and depto.startswith('0'):
            depto = depto[1:]
        if len(depto) > 3:
            depto = depto[:3]
        if depto:
            add_gp = ET.SubElement(p_by, 'ADDITIONAL_GROUP_PARTIES')
            ET.SubElement(add_gp, 'Name').text = 'GENERAL'
            add_gpp = ET.SubElement(add_gp, 'ADDITIONAL_GROUP_PROPERTY_PARTIES')
            ET.SubElement(add_gpp, 'Name').text = 'Buyer_department_id'
            ET.SubElement(add_gpp, 'Value').text = depto

        # SU
        p_su = ET.SubElement(root, 'PARTIES')
        ET.SubElement(p_su, 'Qualifier').text = 'SU'
        ET.SubElement(p_su, 'Identifier_type').text = 'GLN' if len(gln_emisor) == 13 and gln_emisor.isdigit() else 'VAT'
        ET.SubElement(p_su, 'Identifier').text = gln_emisor

        # DP
        p_dp = ET.SubElement(root, 'PARTIES')
        ET.SubElement(p_dp, 'Qualifier').text = 'DP'
        ET.SubElement(p_dp, 'Identifier_type').text = 'GLN' if len(gln_dp) == 13 and gln_dp.isdigit() else 'VAT'
        ET.SubElement(p_dp, 'Identifier').text = gln_dp

        # Supporting docs
        doc220 = ET.SubElement(root, 'SUPPORTING_DOCS')
        ET.SubElement(doc220, 'Type').text = '220'
        ET.SubElement(doc220, 'Identifier').text = num_pedido

        doc351 = ET.SubElement(root, 'SUPPORTING_DOCS')
        ET.SubElement(doc351, 'Type').text = '351'
        ET.SubElement(doc351, 'Identifier').text = alb_num

        # Remarks
        obs = (cab['observaciones1'] or '').strip()
        if obs:
            agh_rem = ET.SubElement(root, 'ADDITIONAL_GROUP_HEADER')
            ET.SubElement(agh_rem, 'Name').text = 'GENERAL'
            agph = ET.SubElement(agh_rem, 'ADDITIONAL_GROUP_PROPERTY_HEADER')
            ET.SubElement(agph, 'Name').text = 'Remarks'
            ET.SubElement(agph, 'Value').text = obs

        # Total_lines
        agh_tot = ET.SubElement(root, 'ADDITIONAL_GROUP_HEADER')
        ET.SubElement(agh_tot, 'Name').text = 'GENERAL'
        agph_tot = ET.SubElement(agh_tot, 'ADDITIONAL_GROUP_PROPERTY_HEADER')
        ET.SubElement(agph_tot, 'Name').text = 'Total_lines'
        ET.SubElement(agph_tot, 'Value').text = str(len(det))

        # Root package 1
        pkg_root = ET.SubElement(root, 'PACKAGES')
        ET.SubElement(pkg_root, 'Package_ID').text = '1'
        ET.SubElement(pkg_root, 'Type').text = '09'
        ET.SubElement(pkg_root, 'Quantity').text = '1'

        pkg_seq = 1
        first_sscc = None

        if bultos:
            for b in bultos:
                pkg_seq += 1
                b_sscc = (b['sscc'] or '').strip()
                if len(b_sscc) != 18 or not b_sscc.isdigit():
                    b_sscc = get_next_sscc(cur, prefix_gln)
                    cur.execute("UPDATE ge_albaranes_bultos SET sscc = %s WHERE id = %s", (b_sscc, b['id']))
                if not first_sscc:
                    first_sscc = b_sscc

                pkg_elem = ET.SubElement(root, 'PACKAGES')
                ET.SubElement(pkg_elem, 'Package_ID').text = str(pkg_seq)
                ET.SubElement(pkg_elem, 'Package_superior_level_ID').text = '1'
                ET.SubElement(pkg_elem, 'SSCC').text = b_sscc
                ET.SubElement(pkg_elem, 'Type').text = 'CT'

                # Lines inside this bulto
                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,
                           al.articulo_codigo
                    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'],))
                lb_rows = cur.fetchall()

                line_in_pkg = 0
                for lb in lb_rows:
                    line_in_pkg += 1
                    lp = ET.SubElement(pkg_elem, 'LINES_PACKAGES')
                    ET.SubElement(lp, 'Line_ID').text = str(line_in_pkg)
                    ean_val = (lb['ean'] or lb['articulo_codigo'] or '').strip()
                    ET.SubElement(lp, 'Identifier_type').text = 'UPC' if len(ean_val) == 12 else 'EAN'
                    ET.SubElement(lp, 'Identifier').text = ean_val
                    ET.SubElement(lp, 'Description').text = lb['articulo_desc']
                    ET.SubElement(lp, 'Quantity').text = str(int(lb['cantidad']))
                    ET.SubElement(lp, 'Packages_quantity').text = '1'

                    # Dun14 / packaging unit identifier
                    cur.execute("""
                        SELECT COALESCE(NULLIF(TRIM(dun14), ''), '') AS dun14_val,
                               COALESCE(NULLIF(TRIM(ean13), ''), '') AS ean13_val
                        FROM ge_clientes_articulos
                        WHERE id_cliente = %s AND id_articulo = %s AND activo = 1 LIMIT 1
                    """, (cab['cliente_id'], lb['articulo_id']))
                    cli_art = cur.fetchone()
                    pkg_id_val = ean_val
                    if cli_art:
                        if cli_art['dun14_val']: pkg_id_val = cli_art['dun14_val']
                        elif cli_art['ean13_val']: pkg_id_val = cli_art['ean13_val']
                    if pkg_id_val:
                        add_id = ET.SubElement(lp, 'ADDITIONAL_IDENTIFIER_ITEM_LINES_PACKAGES')
                        ET.SubElement(add_id, 'Identifier_type').text = 'Packaging_unit_identifier'
                        ET.SubElement(add_id, 'Identifier').text = pkg_id_val

                    lote = lb['lote'].strip()
                    if lote:
                        batch = ET.SubElement(lp, 'BATCH_LINES_PACKAGES')
                        ET.SubElement(batch, 'Batch_number').text = lote
                        if lb['fecha_caducidad']:
                            ET.SubElement(batch, 'Expiry_date').text = lb['fecha_caducidad'].strftime('%Y-%m-%dT00:00:00')
        else:
            # Fallback
            for d in det:
                b_count = int(d['bultos']) if d['bultos'] and int(d['bultos']) > 0 else 1
                cant_total = int(d['cantidad'])
                base_cant = cant_total // b_count
                resto = cant_total % b_count
                for idx_b in range(b_count):
                    pkg_seq += 1
                    pkg_sscc = get_next_sscc(cur, prefix_gln)
                    if not first_sscc:
                        first_sscc = pkg_sscc

                    pkg_elem = ET.SubElement(root, 'PACKAGES')
                    ET.SubElement(pkg_elem, 'Package_ID').text = str(pkg_seq)
                    ET.SubElement(pkg_elem, 'Package_superior_level_ID').text = '1'
                    ET.SubElement(pkg_elem, 'SSCC').text = pkg_sscc
                    ET.SubElement(pkg_elem, 'Type').text = 'CT'

                    lp = ET.SubElement(pkg_elem, 'LINES_PACKAGES')
                    ET.SubElement(lp, 'Line_ID').text = '1'
                    ean_val = (d['articulo_ean'] or d['articulo_codigo'] or '').strip()
                    ET.SubElement(lp, 'Identifier_type').text = 'UPC' if len(ean_val) == 12 else 'EAN'
                    ET.SubElement(lp, 'Identifier').text = ean_val
                    ET.SubElement(lp, 'Description').text = d['descripcion']
                    c_caja = base_cant + 1 if idx_b < resto else base_cant
                    if c_caja == 0 and cant_total > 0: c_caja = cant_total
                    ET.SubElement(lp, 'Quantity').text = str(c_caja)
                    ET.SubElement(lp, 'Packages_quantity').text = '1'

                    cur.execute("""
                        SELECT COALESCE(NULLIF(TRIM(dun14), ''), '') AS dun14_val,
                               COALESCE(NULLIF(TRIM(ean13), ''), '') AS ean13_val
                        FROM ge_clientes_articulos
                        WHERE id_cliente = %s AND id_articulo = %s AND activo = 1 LIMIT 1
                    """, (cab['cliente_id'], d['articulo_id']))
                    cli_art = cur.fetchone()
                    pkg_id_val = ean_val
                    if cli_art:
                        if cli_art['dun14_val']: pkg_id_val = cli_art['dun14_val']
                        elif cli_art['ean13_val']: pkg_id_val = cli_art['ean13_val']
                    if pkg_id_val:
                        add_id = ET.SubElement(lp, 'ADDITIONAL_IDENTIFIER_ITEM_LINES_PACKAGES')
                        ET.SubElement(add_id, 'Identifier_type').text = 'Packaging_unit_identifier'
                        ET.SubElement(add_id, 'Identifier').text = pkg_id_val

                    lote = (d['lotes'] or '').strip()
                    if lote:
                        batch = ET.SubElement(lp, 'BATCH_LINES_PACKAGES')
                        ET.SubElement(batch, 'Batch_number').text = lote
                        if d['fecha_caducidad']:
                            ET.SubElement(batch, 'Expiry_date').text = d['fecha_caducidad'].strftime('%Y-%m-%dT00:00:00')

        if first_sscc and not (cab['edi_numero_sscc'] or '').strip():
            cur.execute("UPDATE ge_albaranes SET edi_numero_sscc = %s WHERE id = %s", (first_sscc, alb_id))

        conn.commit()

        # Format and save
        xml_str = format_xml(root)
        # remove empty lines from minidom pretty print
        cleaned_lines = [l for l in xml_str.splitlines() if l.strip()]
        final_xml = "\n".join(cleaned_lines) + "\n"
        with open(output_path, 'w', encoding='utf-8') as f:
            f.write(final_xml)
        print(f"Exported {output_path} successfully. Total packages: {pkg_seq}")

if __name__ == '__main__':
    export_desadv(7175, 'DESADV_26_436.xml')
    export_desadv(7176, 'DESADV_26_437.xml')
    export_desadv(7178, 'DESADV_26_438.xml')