import sys
sys.path.append('Backend')
from app.config.database import SessionLocal, engine
from app.services.import_service import import_data
from sqlalchemy import text

# Truncate tables before test to ensure clean test
with engine.connect() as conn:
    conn.execute(text("SET FOREIGN_KEY_CHECKS = 0;"))
    for tbl in ['tracer_responses', 'pekerjaan', 'kompetensi', 'alumni']:
        conn.execute(text(f"TRUNCATE TABLE {tbl};"))
    conn.execute(text("SET FOREIGN_KEY_CHECKS = 1;"))
    conn.commit()

with open('Data_Tracerstudy/Data_2024_Tracer_study.csv', 'rb') as f:
    content = f.read()

db = SessionLocal()
try:
    res = import_data('2024', content, 'Data_2024_Tracer_study.csv', db)
    print("Import result:", res)
finally:
    db.close()

# Verify counts in DB
with engine.connect() as conn:
    print("\nDatabase verification after 2024 import:")
    for tbl in ['alumni', 'tracer_responses', 'pekerjaan', 'kompetensi']:
        cnt = conn.execute(text(f"SELECT COUNT(*) FROM {tbl};")).scalar()
        print(f"  {tbl}: {cnt} rows")

    # Sample a row from tracer_responses
    sample = conn.execute(text("SELECT alumni_id, kode_pt, status_saat_ini, provinsi_kerja, kab_kota_kerja, jenis_perusahaan_full, nama_perusahaan, etika_lulus_full, teknologi_informasi_lulus_full, tanggal_masuk_studi, metode_perkuliahan, cari_iklan, jumlah_perusahaan_dilamar FROM tracer_responses LIMIT 1;")).fetchone()
    print("\nSample tracer_response row:")
    print(sample)