import openpyxl

print("Membuat SQL dump dengan batch size 100...")

wb = openpyxl.load_workbook('public/DataPelanggan.xlsx', read_only=True)
sheet = wb.active

sql_file = 'import_pelanggan.sql'
with open(sql_file, 'w', encoding='utf-8') as f:
    f.write("SET FOREIGN_KEY_CHECKS=0;\n")
    f.write("TRUNCATE TABLE pelanggan;\n")
    
    count = 0
    batch = []
    
    for i, row in enumerate(sheet.iter_rows(values_only=True)):
        if i < 3: # Skip title and headers
            continue
        
        no_pelanggan = str(row[3]).strip() if row[3] is not None else ""
        if not no_pelanggan or no_pelanggan == "None":
            continue
            
        no_meter = str(row[4]).strip() if row[4] is not None and str(row[4]) != "None" else ""
        nama = str(row[5]).replace("'", "''").replace("\\", "\\\\").strip() if row[5] is not None and str(row[5]) != "None" else "Pelanggan Jargas"
        alamat = str(row[6]).replace("'", "''").replace("\\", "\\\\").strip() if row[6] is not None and str(row[6]) != "None" else "Kota Prabumulih"
        kelurahan = str(row[2]).replace("'", "''").replace("\\", "\\\\").strip() if row[2] is not None and str(row[2]) != "None" else ""
        tarif = str(row[7]).strip() if row[7] is not None and str(row[7]) != "None" else "RT1"
        sektor = str(row[18]).replace("'", "''").replace("\\", "\\\\").strip() if row[18] is not None and str(row[18]) != "None" else "Rumah Tangga"
        nama_usaha = str(row[19]).replace("'", "''").replace("\\", "\\\\").strip() if row[19] is not None and str(row[19]) != "None" else ""
        no_hp = str(row[8]).strip() if row[8] is not None and str(row[8]) != "None" else ""
        
        lat_lng = str(row[1]).split(',') if row[1] is not None else []
        lat = lat_lng[0].strip() if len(lat_lng) > 0 else ""
        lng = lat_lng[1].strip() if len(lat_lng) > 1 else ""
        
        try:
            bln_tunggakan = int(row[12]) if row[12] is not None else 0
        except:
            bln_tunggakan = 0
            
        try:
            rp_tunggakan = int(row[13]) if row[13] is not None else 0
        except:
            rp_tunggakan = 0
            
        status = str(row[15]).strip() if row[15] is not None and str(row[15]) != "None" else "Aktif"
        gas = str(row[17]).strip() if row[17] is not None and str(row[17]) != "None" else "Gas Dipakai"
        
        val = f"('{no_pelanggan}', '{no_meter}', '{nama}', '{alamat}', '{kelurahan}', '{tarif}', '{sektor}', '{nama_usaha}', '{no_hp}', '{lat}', '{lng}', {bln_tunggakan}, {rp_tunggakan}, '{status}', '{gas}', NOW(), NOW())"
        batch.append(val)
        count += 1
        
        if len(batch) >= 100:
            f.write("INSERT INTO pelanggan (no_pelanggan, no_seri_meteran, nama_pelanggan, alamat_lengkap, kelurahan, tarif, sektor_kategori, nama_usaha, no_hp, latitude, longitude, bulan_tunggakan, rp_tunggakan, status_langganan, gas_pelanggan, created_at, updated_at) VALUES\n" + ",\n".join(batch) + ";\n")
            batch = []
            
    if batch:
        f.write("INSERT INTO pelanggan (no_pelanggan, no_seri_meteran, nama_pelanggan, alamat_lengkap, kelurahan, tarif, sektor_kategori, nama_usaha, no_hp, latitude, longitude, bulan_tunggakan, rp_tunggakan, status_langganan, gas_pelanggan, created_at, updated_at) VALUES\n" + ",\n".join(batch) + ";\n")

    f.write("SET FOREIGN_KEY_CHECKS=1;\n")

print(f"Berhasil membuat SQL dump {count} data pelanggan dengan 100 batch size.")
