import pandas as pd
import glob
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 parse_2024_value, parse_date

# Inspect max length for every column mapped to tracer_responses
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)

max_lengths = {}
problem_values = []

for fpath in sorted(glob.glob('Data_Tracerstudy/*2025*.csv')):
    df = pd.read_csv(fpath, sep=';', encoding='utf-8-sig')
    source_cols = list(df.columns)
    for idx, row in df.iterrows():
        nim = get_value(row, source_cols, IDENTITAS_MAPPING['nim'], '')
        if not nim or str(nim).strip() in ['', 'nan']:
            continue
        for field, cols in all_mapping.items():
            if field in ['nim', 'nama', 'tahun_lulus', 'kode_prodi']:
                continue
            val = get_value(row, source_cols, cols)
            if val is not None and not pd.isna(val):
                kode, teks = parse_2024_value(val)
                final_val = teks if teks else (str(kode) if kode is not None else str(val).strip())
                val_len = len(str(final_val))
                if field not in max_lengths or val_len > max_lengths[field]:
                    max_lengths[field] = val_len
                
                # Check known DB column limits
                # jumlah_perusahaan: VARCHAR(50)
                if field == 'jumlah_perusahaan' and val_len > 50:
                    problem_values.append((fpath, idx, field, val_len, final_val))
                # dapet_kerja_6_bulan: VARCHAR(50)
                if field == 'dapet_kerja_6_bulan' and val_len > 50:
                    problem_values.append((fpath, idx, field, val_len, final_val))
                # tingkatan_tempat_kerja: VARCHAR(50)
                if field == 'tingkatan_tempat_kerja' and val_len > 50:
                    problem_values.append((fpath, idx, field, val_len, final_val))
                # status_saat_ini: VARCHAR(50) or 255
                if field == 'status_saat_ini' and val_len > 50:
                    problem_values.append((fpath, idx, field, val_len, final_val))
                # tanggal_masuk_studi
                if field == 'tanggal_masuk_studi':
                    parsed = parse_date(val)
                    # check if valid date
                    if val and not parsed:
                        problem_values.append((fpath, idx, field, 'INVALID_DATE', val))

print("=== POTENTIAL DB COLUMN OVERFLOWS IN 2025 ===")
for p in problem_values:
    print(f"File: {p[0].split('/')[-1].split(chr(92))[-1]}, Row {p[1]}, Field {p[2]}, Len/Status: {p[3]}")
    print(f"   Value: {repr(p[4])[:100]}")

print("\n=== TOP MAX LENGTHS IN 2025 ===")
for f, l in sorted(max_lengths.items(), key=lambda x: x[1], reverse=True)[:15]:
    print(f"  {f}: max length {l}")