import openpyxl
from datetime import datetime

wb = openpyxl.load_workbook('C:/Users/nezma/Downloads/bd_grupo_ois_clientes.xlsx', data_only=True)
ws = wb['5) BD SEGUROS OIS']

def clean(val, upper=False):
    if val is None:
        return 'NULL'
    s = str(val).strip()
    if s.upper() in ('N/A', 'N.A.', '00/00/0000', '', ' '):
        return 'NULL'
    if upper:
        s = s.upper()
    s = s.replace("'", "''")   # escape SQL con doble comilla simple
    return "'" + s + "'"

def clean_num(val):
    if val is None:
        return 'NULL'
    s = str(val).strip()
    if s.upper() in ('N/A', '', ' '):
        return 'NULL'
    try:
        return str(float(s))
    except:
        return 'NULL'

def clean_date(val):
    if val is None:
        return 'NULL'
    if isinstance(val, datetime):
        return "'" + val.strftime('%Y-%m-%d') + "'"
    s = str(val).strip()
    if s.upper() in ('N/A', '00/00/0000', '', ' '):
        return 'NULL'
    s2 = s.replace('//', '/')
    for fmt in ('%d/%m/%Y', '%Y-%m-%d', '%d/%m/%y', '%m/%d/%Y'):
        try:
            d = datetime.strptime(s2, fmt)
            return "'" + d.strftime('%Y-%m-%d') + "'"
        except:
            pass
    return 'NULL'

rows = []
for row in ws.iter_rows(min_row=6, values_only=True):
    r = list(row)
    if not any(v is not None and str(v).strip() not in ('', 'N/A') for v in r[:5]):
        continue

    rows.append((
        clean_date(r[0]),   # fecha
        clean(r[1]),        # aseguradora
        clean(r[2]),        # tipo
        clean(r[3], True),  # poliza
        clean(r[4]),        # cobertura
        clean(r[5], True),  # poliza_anterior
        clean(r[7]),        # ticket
        clean(r[8]),        # solicitud
        clean(r[9]),        # endoso
        clean(r[10]),       # tipo_endoso
        clean_date(r[11]),  # fecha_renovacion
        clean(r[12]),       # contratante_titular
        clean(r[13]),       # asegurado
        clean(r[14]),       # beneficiarios
        clean(r[15]),       # correo
        clean(r[16]),       # telefono1
        clean(r[17]),       # telefono2
        clean(r[18]),       # clave_agente
        clean_date(r[19]),  # vigencia_ini
        clean_date(r[20]),  # vigencia_fin
        clean_num(r[21]),   # aportacion
        clean(r[22]),       # periodicidad
        clean(r[23]),       # plazo_comprometido
        clean(r[24]),       # conducto_cobro
        clean(r[25]),       # estatus_poliza
        clean(r[26]),       # sub
        clean_date(r[27]),  # proximo_pago
        clean(r[28]),       # plazo
        clean(r[29], True), # rfc
        clean_date(r[30]),  # cumpleanos
        clean(r[31]),       # direccion
        clean(r[32], True), # serie
        clean(r[33], True), # motor
        clean(r[34], True), # placa
        clean(r[36]),       # modelo
        clean(r[37]),       # servicio
        clean(r[38]),       # uso
        clean_num(r[39]),   # pago_inicial
        clean_num(r[40]),   # pago_subsecuente
        clean_num(r[41]),   # pago_total
        clean(r[42]),       # nueva_renovacion
        clean_date(r[43]),  # cancelacion_fecha
        clean(r[44]),       # cancelacion_motivo
    ))

print(f'Filas a insertar: {len(rows)}')

cols = ("fecha,aseguradora,tipo,poliza,cobertura,poliza_anterior,"
        "ticket,solicitud,endoso,tipo_endoso,fecha_renovacion,"
        "contratante_titular,asegurado,beneficiarios,correo,telefono1,telefono2,"
        "clave_agente,vigencia_ini,vigencia_fin,aportacion,periodicidad,"
        "plazo_comprometido,conducto_cobro,estatus_poliza,sub,proximo_pago,plazo,"
        "rfc,cumpleanos,direccion,serie,motor,placa,modelo,"
        "servicio,uso,pago_inicial,pago_subsecuente,pago_total,"
        "nueva_renovacion,cancelacion_fecha,cancelacion_motivo")

chunk_size = 50
out_path = 'C:/wamp64/www/grupoois.site/database/seeders/polizas_import.sql'
with open(out_path, 'w', encoding='utf-8') as f:
    f.write("-- Importacion automatica de polizas desde BD SEGUROS OIS\n")
    f.write("-- Generado: 2026-04-11\n\n")
    f.write("SET NAMES utf8mb4;\n\n")
    for i in range(0, len(rows), chunk_size):
        chunk = rows[i:i+chunk_size]
        vals = ["(" + ",".join(row_t) + ")" for row_t in chunk]
        f.write("INSERT INTO `polizas` (" + cols + ") VALUES\n")
        f.write(",\n".join(vals))
        f.write(";\n\n")

print('Archivo SQL generado en: ' + out_path)