import pandas as pd
import re

# 1. Excel dosyasını oku
dosya_yolu = 'dus_tum_atamalar.xlsx'
df = pd.read_excel(dosya_yolu)

# Türkiye'deki illerin tam ve hatasız listesi
iller = [
    "ADANA", "ADIYAMAN", "AFYONKARAHİSAR", "AĞRI", "AMASYA", "ANKARA", "ANTALYA", "ARTVİN", "AYDIN", 
    "BALIKESİR", "BİLECİK", "BİNGÖL", "BİTLİS", "BOLU", "BURDUR", "BURSA", "ÇANAKKALE", "ÇANKIRI", 
    "ÇORUM", "DENİZLİ", "DİYARBAKIR", "EDİRNE", "ELAZIĞ", "ERZİNCAN", "ERZURUM", "ESKİŞEHİR", "GAZİANTEP", 
    "GİRESUN", "GÜMÜŞHANE", "HAKKARİ", "HATAY", "ISPARTA", "MERSİN", "İSTANBUL", "İZMİR", "KARS", 
    "KASTAMONU", "KAYSERİ", "KIRKLARELİ", "KIRŞEHİR", "KOCAELİ", "KONYA", "KÜTAHYA", "MALATYA", "MANİSA", 
    "KAHRAMANMARAŞ", "MARDİN", "MUĞLA", "MUŞ", "NEVŞEHİR", "NİĞDE", "ORDU", "RİZE", "SAKARYA", "SAMSUN", 
    "SİİRT", "SİNOP", "SİVAS", "TEKİRDAĞ", "TOKAT", "TRABZON", "TUNCELİ", "ŞANLIURFA", "UŞAK", "VAN", 
    "YOZGAT", "ZONGULDAK", "AKSARAY", "BAYBURT", "KARAMAN", "KIRIKKALE", "BATMAN", "ŞIRNAK", "BARTIN", 
    "ARDAHAN", "IĞDIR", "YALOVA", "KARABÜK", "KİLİS", "OSMANİYE", "DÜZCE", "KKTC"
]

# Kurum isimlerinden ili tespit etmek için özel sözlük
ozel_iller = {
    "BAĞCILAR": "İSTANBUL", "BAKIRKÖY": "İSTANBUL", "BAŞAKŞEHİR": "İSTANBUL", "BEYOĞLU": "İSTANBUL", 
    "HASEKİ": "İSTANBUL", "HAYDARPAŞA": "İSTANBUL", "KANUNİ SULTAN SÜLEYMAN": "İSTANBUL", "ŞİŞLİ": "İSTANBUL",
    "TAKSİM": "İSTANBUL", "ÜMRANİYE": "İSTANBUL", "ZEYNEP KAMİL": "İSTANBUL", "SÜREYYAPAŞA": "İSTANBUL",
    "YEDİKULE": "İSTANBUL", "GÖZTEPE": "İSTANBUL", "KARTAL": "İSTANBUL", "SANCAKTEPE": "İSTANBUL",
    "CERRAHPAŞA": "İSTANBUL", "MARMARA": "İSTANBUL", "BEZMİALEM": "İSTANBUL", "BİRUNİ": "İSTANBUL",
    "KOÇ ÜNİVERSİTESİ": "İSTANBUL", "MEDİPOL": "İSTANBUL", "ACIBADEM": "İSTANBUL", "ATLAS": "İSTANBUL",
    "ÜSKÜDAR": "İSTANBUL", "İSTİNYE": "İSTANBUL", "GÜLHANE": "ANKARA", "ETLİK": "ANKARA", "BİLKENT": "ANKARA", 
    "ATATÜRK SANATORYUM": "ANKARA", "DIŞKAPI": "ANKARA", "YENİMAHALLE": "ANKARA", "SİNCAN": "ANKARA", 
    "DR. ABDURRAHMAN YURTASLAN": "ANKARA", "DR. SAMİ ULUS": "ANKARA", "ULUCANLAR": "ANKARA", 
    "HACETTEPE": "ANKARA", "GAZİ ÜNİVERSİTESİ": "ANKARA", "YILDIRIM BEYAZIT": "ANKARA", "BAŞKENT": "ANKARA", 
    "UFUK": "ANKARA", "LOKMAN HEKİM": "ANKARA", "ATILIM": "ANKARA", "TEPECİK": "İZMİR", "BOZYAKA": "İZMİR", 
    "DR. BEHÇET UZ": "İZMİR", "DR. SUAT SEREN": "İZMİR", "EGE ": "İZMİR", "DOKUZ EYLÜL": "İZMİR", 
    "KATİP ÇELEBİ": "İZMİR", "BAKIRÇAY": "İZMİR", "EKONOMİ": "İZMİR", "ULUDAĞ": "BURSA", "ÇUKUROVA": "ADANA", 
    "AKDENİZ": "ANTALYA", "DİCLE": "DİYARBAKIR", "FIRAT": "ELAZIĞ", "ONDOKUZ MAYIS": "SAMSUN", 
    "ERCİYES": "KAYSERİ", "KARADENİZ TEKNİK": "TRABZON", "PAMUKKALE": "DENİZLİ", "ADNAN MENDERES": "AYDIN", 
    "CELAL BAYAR": "MANİSA", "SÜLEYMAN DEMİREL": "ISPARTA", "SÜTÇÜ İMAM": "KAHRAMANMARAŞ", 
    "ONSEKİZ MART": "ÇANAKKALE", "YÜZÜNCÜ YIL": "VAN", "MUSTAFA KEMAL": "HATAY", "NAMIK KEMAL": "TEKİRDAĞ",
    "BOZOK": "YOZGAT", "BÜLENT ECEVİT": "ZONGULDAK", "DR. BURHAN NALBANTOĞLU": "KKTC", "ADLİ TIP KURUMU": "TÜM TÜRKİYE",
    "ALANYA": "ANTALYA", "BANDIRMA": "BALIKESİR", "GAZİLER FİZİK": "ANKARA"
}

def veri_ayikla(row):
    # Sütun adı "program_adi" veya "Program Adı" olabilir
    prog_adi = str(row.get('program_adi', row.get('Program Adı', '')))
        
    if pd.isna(prog_adi) or prog_adi.strip() in ['nan', '']:
        return pd.Series([None, None, None])
        
    # DUS'ta alan adı (uzmanlık dalı) / işaretinden sonra yer alır
    parcalar = prog_adi.split('/')
    alan = parcalar[-1].strip() if len(parcalar) > 1 else None
    kurum = "/".join(parcalar[:-1]).strip()
    
    il = None
    
    # 1. Adım: Kurum adının sonundaki parantez içine bak (Örn: (ANKARA))
    paren_match = re.search(r'\((.*?)\)', kurum)
    if paren_match:
        pot_il = paren_match.group(1).upper().replace('İ', 'I')
        for c in iller:
            if c == pot_il or c in pot_il:
                il = c
                break
    
    # 2. Adım: Özel listemizde eşleştirme yap
    kurum_upper = kurum.upper().replace('İ', 'I')
    if not il:
        for key, val in ozel_iller.items():
            if key.replace('İ', 'I') in kurum_upper:
                il = val
                break
                
    # 3. Adım: Doğrudan metnin içinde il adı geçiyor mu? (Örn: Bursa Şehir Hastanesi)
    if not il:
        kurum_clean = kurum_upper.replace("T.C.", "").replace("SAĞLIK BAKANLIĞI", "").strip()
        for c in iller:
            if kurum_clean.startswith(c) or f" {c} " in kurum_clean:
                il = c
                break
                
    return pd.Series([kurum, il, alan])

# Yeni sütunları 'kurum_adi', 'il', 'alan' olarak oluştur
df[['kurum_adi', 'il', 'alan']] = df.apply(veri_ayikla, axis=1)

# Temizlenmiş veriyi yeni bir excel olarak kaydet
df.to_excel('dus_yerlestirmeler.xlsx', index=False)
print("İşlem tamamlandı! 'dus_yerlestirmeler.xlsx' başarıyla oluşturuldu.")