import pandas as pd
import re
import os

# --- KONFIGURASI FILE ---
FILE_EXCEL = 'data_payroll.xlsx'
FILE_SQL_DUMP = 'users.sql' 
FILE_OUTPUT_SQL = 'hasil_update_payroll.sql'

def get_db_mapping(sql_file):
    """Mengekstrak ID dan Nama dari format VALUES di file SQL"""
    mapping = {}
    # Regex untuk mengambil (id, id_bank, 'no_rek', 'nik', 'name', ...)
    pattern = re.compile(r"\((\d+),\s*\d+,\s*'[^']*',\s*'[^']*',\s*'([^']*)'")
    
    if not os.path.exists(sql_file):
        print(f"❌ Error: File {sql_file} tidak ditemukan!")
        return None

    with open(sql_file, 'r', encoding='utf-8') as f:
        content = f.read()
        matches = pattern.findall(content)
        for id_peg, name in matches:
            mapping[int(id_peg)] = name.strip()
    return mapping

def format_ribuan(val):
    """Membersihkan input dan memaksa format titik ribuan (Contoh: 1.000.000)"""
    if pd.isna(val) or str(val).strip() == '':
        return ""
    
    # Ambil angka saja (buang titik/koma/Rp jika ada)
    clean_val = re.sub(r'[^0-9]', '', str(val))
    
    if not clean_val:
        return ""
    
    # Format ke ribuan dengan titik
    return "{:,}".format(int(clean_val)).replace(',', '.')

def generate_payroll_queries():
    try:
        # 1. Ambil data dari SQL Dump sebagai referensi nama
        db_users = get_db_mapping(FILE_SQL_DUMP)
        if db_users is None: return
        
        # 2. Baca Excel Payroll
        if not os.path.exists(FILE_EXCEL):
            print(f"❌ Error: File {FILE_EXCEL} tidak ditemukan!")
            return
            
        df_excel = pd.read_excel(FILE_EXCEL)
        
        # Bersihkan spasi di nama kolom excel jika ada
        df_excel.columns = [str(c).strip() for c in df_excel.columns]
        
        queries = []
        mismatches = []
        
        print("--- Memulai Proses Validasi & Formatting ---")
        
        for index, row in df_excel.iterrows():
            # Ambil ID dan Nama dari Excel
            id_ex = int(row['ID_PEGAWAI']) 
            nama_ex = str(row['NAMA KARYAWAN']).strip()
            
            # Cek kecocokan ID di Database
            if id_ex in db_users:
                nama_db = db_users[id_ex]
                
                # Cek Mismatch Nama (Hanya peringatan, tetap lanjut buat query)
                if nama_ex.lower() != nama_db.lower():
                    mismatches.append(f"❌ MISMATCH ID {id_ex}: Excel '{nama_ex}' | DB '{nama_db}'")
                
                # Ambil dan Format angka PPH & BPJS
                pph = format_ribuan(row['PPH_21'])
                kes = format_ribuan(row['BPJS_KESEHATAN'])
                tk = format_ribuan(row['BPJS_KETENAGAKERJAAN'])
                
                # Buat Query UPDATE (Escape tanda petik pada nama jika diperlukan, 
                # tapi karena kita update berdasarkan ID, nama tidak perlu masuk ke query)
                sql = f"UPDATE users SET pph21='{pph}', bpjs_kes='{kes}', bpjs_tk='{tk}' WHERE id_pegawai={id_ex};"
                queries.append(sql)
            else:
                mismatches.append(f"⚠️ NOT FOUND: ID {id_ex} ({nama_ex}) tidak ada di database!")

        # 3. Cetak Hasil Mismatch ke Konsol
        if mismatches:
            print(f"\nDitemukan {len(mismatches)} masalah validasi:")
            for m in mismatches:
                print(m)
        
        # 4. Simpan ke File SQL
        if queries:
            with open(FILE_OUTPUT_SQL, 'w', encoding='utf-8') as f:
                f.write("-- GENERATED PAYROLL UPDATE\n")
                f.write("START TRANSACTION;\n\n")
                f.write("\n".join(queries))
                f.write("\n\nCOMMIT;")
            print(f"\n✅ SELESAI! {len(queries)} query siap di: {FILE_OUTPUT_SQL}")
        else:
            print("\n❌ Gagal: Tidak ada query yang dihasilkan.")

    except Exception as e:
        print(f"❌ Terjadi error fatal: {e}")

if __name__ == "__main__":
    generate_payroll_queries()