import sys
sys.path.append('Backend')
from app.config.database import engine
from sqlalchemy import text
import re

def parse_status_pekerjaan_new(status_sp):
    if status_sp is None:
        return None

    sp = str(status_sp).strip().lower()
    if sp in ['', 'nan', 'none', '0', '0.0', '-']:
        return None

    code_match = re.search(r'(?:^|\s|\()([1-5])(?:\s*[-–)]|$)', sp)
    if code_match:
        code = code_match.group(1)
        if code == '1':
            return 'bekerja'
        elif code == '2':
            return 'belum_bekerja'
        elif code == '3':
            return 'wiraswasta'
        elif code == '4':
            return 'melanjutkan'
        elif code == '5':
            return 'mencari_kerja'

    if 'tidak kerja' in sp or 'mencari kerja' in sp or 'sedang mencari' in sp:
        return 'mencari_kerja'
    elif 'belum' in sp:
        return 'belum_bekerja'
    elif 'wiraswasta' in sp:
        return 'wiraswasta'
    elif 'melanjutkan' in sp or 'pendidikan' in sp or 'studi' in sp:
        return 'melanjutkan'
    elif 'bekerja' in sp:
        return 'bekerja'

    return None

with engine.connect() as conn:
    # Get all records from tracer_responses
    rows = conn.execute(text("SELECT alumni_id, status_saat_ini FROM tracer_responses;")).fetchall()
    print(f"Total tracer responses: {len(rows)}")
    
    updated_alumni = 0
    updated_pekerjaan = 0
    
    for alumni_id, raw_status in rows:
        correct_status = parse_status_pekerjaan_new(raw_status)
        if correct_status:
            conn.execute(
                text("UPDATE alumni SET status_kerja = :st WHERE id = :aid"),
                {"st": correct_status, "aid": alumni_id}
            )
            conn.execute(
                text("UPDATE pekerjaan SET status_pekerjaan = :st WHERE alumni_id = :aid"),
                {"st": correct_status, "aid": alumni_id}
            )
            updated_alumni += 1
            updated_pekerjaan += 1
            
    conn.commit()
    print(f"Successfully updated status for {updated_alumni} rows in alumni and pekerjaan!")
    
    # Check new status counts
    res_alumni = conn.execute(text("SELECT status_kerja, COUNT(*) FROM alumni GROUP BY status_kerja;")).fetchall()
    print("\nUpdated alumni status_kerja counts:")
    for r in res_alumni:
        print(f"  {r[0]}: {r[1]}")
        
    res_pek = conn.execute(text("SELECT status_pekerjaan, COUNT(*) FROM pekerjaan GROUP BY status_pekerjaan;")).fetchall()
    print("\nUpdated pekerjaan status_pekerjaan counts:")
    for r in res_pek:
        print(f"  {r[0]}: {r[1]}")