import pyodbc
import mysql.connector

# Connect to Contasol
db_path = r'c:\fuentes\proyectos delphi\RAUL_ASENCIO\GESTION\documentacion raul asencio\datos\contabilidad\0042026.accdb'
conn_str = f'DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={db_path};'
acc_conn = pyodbc.connect(conn_str)
acc_cur = acc_conn.cursor()

# Connect to MySQL
my_conn = mysql.connector.connect(
    host='85.215.144.168',
    port=3306,
    user='asesoft',
    password='Pantera1',
    database='pastelerias'
)
my_cur = my_conn.cursor()

# Get all clients from ge_clientes
my_cur.execute("SELECT id, UPPER(TRIM(REPLACE(REPLACE(nif, '-', ''), ' ', ''))), nombre_fiscal FROM ge_clientes WHERE nif IS NOT NULL AND nif <> ''")
my_clients_by_nif = {}
for cid, nif, nom in my_cur.fetchall():
    if nif:
        my_clients_by_nif[nif] = (cid, nom)

print(f"MySQL clients with NIF: {len(my_clients_by_nif)}")

# Get all suppliers from ge_proveedores
my_cur.execute("SELECT id, UPPER(TRIM(REPLACE(REPLACE(nif, '-', ''), ' ', ''))), nombre FROM ge_proveedores WHERE nif IS NOT NULL AND nif <> ''")
my_suppliers_by_nif = {}
for pid, nif, nom in my_cur.fetchall():
    if nif:
        my_suppliers_by_nif[nif] = (pid, nom)

print(f"MySQL suppliers with NIF: {len(my_suppliers_by_nif)}")

# Cross match F_CLI
acc_cur.execute("SELECT CODCLI, CIFCLI, NOMCLI FROM F_CLI")
f_cli = acc_cur.fetchall()
matched_cli = 0
unmatched_cli = []
for cod, cif, nom in f_cli:
    clean_cif = (cif or '').strip().replace('-', '').replace(' ', '').upper()
    if clean_cif in my_clients_by_nif:
        matched_cli += 1
    else:
        unmatched_cli.append((cod, cif, nom))

print(f"F_CLI matched with ge_clientes by NIF: {matched_cli} of {len(f_cli)}")
if unmatched_cli:
    print(f"Sample unmatched F_CLI ({len(unmatched_cli)}):")
    for u in unmatched_cli[:5]:
        print(" ", u)

# Cross match F_PRO
acc_cur.execute("SELECT CODPRO, CIFPRO, NOMPRO FROM F_PRO")
f_pro = acc_cur.fetchall()
matched_pro = 0
unmatched_pro = []
for cod, cif, nom in f_pro:
    clean_cif = (cif or '').strip().replace('-', '').replace(' ', '').upper()
    if clean_cif in my_suppliers_by_nif:
        matched_pro += 1
    else:
        unmatched_pro.append((cod, cif, nom))

print(f"F_PRO matched with ge_proveedores by NIF: {matched_pro} of {len(f_pro)}")
if unmatched_pro:
    print(f"Sample unmatched F_PRO ({len(unmatched_pro)}):")
    for u in unmatched_pro[:5]:
        print(" ", u)

# What about F_MAE? Can we match F_MAE by name?
acc_cur.execute("SELECT CODMAE, NOMMAE FROM F_MAE WHERE CODMAE LIKE '43%' AND LEN(CODMAE) >= 8")
f_mae_cli = acc_cur.fetchall()
print(f"Total F_MAE 43%: {len(f_mae_cli)}")

acc_cur.execute("SELECT CODMAE, NOMMAE FROM F_MAE WHERE (CODMAE LIKE '40%' OR CODMAE LIKE '41%') AND LEN(CODMAE) >= 8")
f_mae_pro = acc_cur.fetchall()
print(f"Total F_MAE 40/41%: {len(f_mae_pro)}")

# Check ge_proveedores matching F_MAE by name
my_cur.execute("SELECT id, UPPER(TRIM(nombre)), nif FROM ge_proveedores")
pro_names = {row[1]: (row[0], row[2]) for row in my_cur.fetchall()}
matched_pro_name = 0
for cod, nom in f_mae_pro:
    clean_nom = (nom or '').strip().upper()
    if clean_nom in pro_names:
        matched_pro_name += 1

print(f"F_MAE proveedores matching ge_proveedores by exact name: {matched_pro_name} of {len(f_mae_pro)}")
