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 (
    YEAR_CONFIG, 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, parse_2024_value

# Combine all mappings
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)

def analyze_year(yr):
    fpath = f'Data_Tracerstudy/Data_{yr}_Tracer_study.csv'
    df = pd.read_csv(fpath, sep=';', encoding='utf-8-sig').dropna(how='all')
    source_cols = list(df.columns)
    year_config = YEAR_CONFIG.get(yr, {})
    is_2024_format = year_config.get('is_text_format', False)
    skip_jumlah = year_config.get('skip_jumlah_perusahaan', False)
    
    print(f"\n==================== TAHUN {yr} ====================")
    print(f"Total rows: {len(df)}, Format 2024: {is_2024_format}")
    
    # Track NULL counts per field across all rows
    null_counts = {field: 0 for field in all_mapping if field not in ['nim', 'nama', 'tahun_lulus', 'kode_prodi']}
    total_samples = len(df)
    
    for idx, row in df.iterrows():
        for field, cols in all_mapping.items():
            if field in ['nim', 'nama', 'tahun_lulus', 'kode_prodi']:
                continue
            if skip_jumlah and field == 'jumlah_perusahaan':
                continue
                
            val = get_value(row, source_cols, cols)
            
            # Simulate what import_service does:
            if val is None or pd.isna(val):
                null_counts[field] += 1
                continue
            if isinstance(val, str) and val.strip() in ['', 'nan', 'None', '-', '_']:
                null_counts[field] += 1
                continue
                
            field_code = get_field_code(field, all_mapping)
            fc_upper = str(field_code).upper() if field_code else ''
            
            if is_2024_format:
                kode, teks = parse_2024_value(val)
                if field in ['bulan_dapat_kerja', 'bulan_dapat_kerja_setelah', 'bulan_cari_sebelum_lulus', 'bulan_cari_setelah_wisuda']:
                    if kode is None and not isinstance(val, (int, float)):
                        null_counts[field] += 1
                else:
                    if not teks and kode is None and not str(val).strip():
                        null_counts[field] += 1
            else:
                converted = konversi_jawaban(field_code, val)
                if field in ['bulan_dapat_kerja', 'bulan_dapat_kerja_setelah', 'bulan_cari_sebelum_lulus', 'bulan_cari_setelah_wisuda']:
                    try:
                        int(float(str(val).strip()))
                    except:
                        null_counts[field] += 1
                elif converted is not None:
                    pass # has value
                elif str(val).strip() in ['0', '0.0'] and fc_upper in ['F1761', 'F1762', 'F1763', 'F1764', 'F1765', 'F1766', 'F1767', 'F1768', 'F1769', 'F1770', 'F1771', 'F1772', 'F1773', 'F1774', 'F21', 'F22', 'F23', 'F24', 'F25', 'F26', 'F27']:
                    null_counts[field] += 1
                else:
                    pass
                    
    # Report fields that are 100% NULL or >50% NULL
    always_null = [f for f, c in null_counts.items() if c == total_samples]
    mostly_null = [f for f, c in null_counts.items() if total_samples > c >= total_samples * 0.5]
    
    print(f"Fields 100% NULL ({len(always_null)} fields):")
    for f in always_null:
        fc = get_field_code(f, all_mapping)
        mapped_col = find_column(source_cols, all_mapping[f])
        print(f"  - {f} (code: {fc}) -> col in CSV: {repr(mapped_col)}")
        if mapped_col:
            raw_sample = df[mapped_col].dropna().unique()
            print(f"      CSV raw sample: {raw_sample[:5]}")
            
    print(f"\nFields >50% NULL ({len(mostly_null)} fields):")
    for f in mostly_null[:10]:
        fc = get_field_code(f, all_mapping)
        mapped_col = find_column(source_cols, all_mapping[f])
        print(f"  - {f} (code: {fc}): {null_counts[f]}/{total_samples} NULL ({null_counts[f]/total_samples*100:.1f}%) -> col: {repr(mapped_col)}")

for yr in ['2019', '2020', '2021', '2022', '2023']:
    analyze_year(yr)