import pandas as pd
import sys
sys.path.append('Backend')
from app.parsers.column_finder import find_column, get_value
from app.parsers.mappings import (
    IDENTITAS_MAPPING, PEKERJAAN_MAPPING, STUDI_LANJUT_MAPPING,
    KESESUAIAN_MAPPING, KOMPETENSI_LULUS_MAPPING, KOMPETENSI_SAAT_INI_MAPPING,
    METODE_PEMBELAJARAN_MAPPING, WAKTU_CARI_KERJA_MAPPING, CARA_CARI_KERJA_MAPPING,
    LAMARAN_KEAKTIFAN_MAPPING, KESESUAIAN_PEKERJAAN_MAPPING, get_field_code
)
from app.parsers.converters import konversi_jawaban

all_mapping = {}
all_mapping.update(IDENTITAS_MAPPING)
all_mapping.update(PEKERJAAN_MAPPING)
all_mapping.update(STUDI_LANJUT_MAPPING)
all_mapping.update(KESESUAIAN_MAPPING)
all_mapping.update(KOMPETENSI_LULUS_MAPPING)
all_mapping.update(KOMPETENSI_SAAT_INI_MAPPING)
all_mapping.update(METODE_PEMBELAJARAN_MAPPING)
all_mapping.update(WAKTU_CARI_KERJA_MAPPING)
all_mapping.update(CARA_CARI_KERJA_MAPPING)
all_mapping.update(LAMARAN_KEAKTIFAN_MAPPING)
all_mapping.update(KESESUAIAN_PEKERJAAN_MAPPING)

years = ['2019', '2020', '2021', '2022', '2023']

for yr in years:
    fpath = f'Data_Tracerstudy/Data_{yr}_Tracer_study.csv'
    df = pd.read_csv(fpath, sep=';', encoding='utf-8-sig')
    source_cols = list(df.columns)
    print(f"\n==================== TAHUN {yr} ====================")
    print(f"File: {fpath}")
    print(f"Total rows in CSV: {len(df)}")
    
    # Check skipped row causes for 2021 or any year
    nim_col = find_column(source_cols, IDENTITAS_MAPPING['nim'])
    if nim_col:
        nims = df[nim_col]
        empty_nims = df[nims.isna() | (nims.astype(str).str.strip().isin(['', 'nan', 'None']))]
        print(f"NIM col: '{nim_col}' | Valid NIMs: {len(df) - len(empty_nims)} | Empty NIMs: {len(empty_nims)}")
        if len(empty_nims) > 0:
            print(f"  Empty NIM row indices: {empty_nims.index.tolist()}")
            print(f"  Preview empty NIM row:\n{df.iloc[empty_nims.index[:1]][source_cols[:8]]}")
    else:
        print("CRITICAL: NIM column not found!")
    
    # Check specifically F1602 (kerja_tidak_sesuai_2) and F402 (cari_melamar_tanpa_lowongan)
    for target_field in ['kerja_tidak_sesuai_2', 'cari_melamar_tanpa_lowongan', 'cari_iklan', 'kerja_tidak_sesuai_1']:
        cols = all_mapping.get(target_field, [])
        found = find_column(source_cols, cols)
        fc = get_field_code(target_field, all_mapping)
        print(f"Target '{target_field}' (code {fc}): mapped_to='{found}'")
        if found:
            raw_vals = df[found].dropna().unique()
            print(f"  Raw unique: {raw_vals[:8]}")
            # check konversi_jawaban
            sample_conv = [f"{v} -> {konversi_jawaban(fc, v)}" for v in raw_vals[:5]]
            print(f"  Sample konversi: {sample_conv}")
        else:
            # check if code is in any column
            matching_cols = [c for c in source_cols if fc and fc.lower() in c.lower()]
            print(f"  NOT FOUND in mapping! Matching cols in CSV: {matching_cols}")