"""
Router untuk Import Data Alumni dari CSV/Excel
Mendukung semua kolom tracer study 2019-2025

File ini adalah router utama yang mendelegasikan ke import_service.py
"""

from fastapi import APIRouter, UploadFile, File, HTTPException, Depends, Form
from sqlalchemy.orm import Session
from sqlalchemy import func

from app.config.database import get_db
from app.services.import_service import import_data
from app.models.alumni import Alumni
from app.models.models import TracerResponse, Kompetensi, Pekerjaan

router = APIRouter(prefix="", tags=["Import Data"])


@router.post("/import-full")
async def import_full(
    tahun: str = Form(...),
    file: UploadFile = File(...),
    db: Session = Depends(get_db)
):
    """
    Import CSV dengan SEMUA kolom tracer study.
    Data akan disimpan ke tabel: alumni, pekerjaan, kompetensi, tracer_responses
    """
    try:
        content = await file.read()
        result = import_data(tahun, content, file.filename, db)

        if result["status"] == "error":
            raise HTTPException(status_code=400, detail=result["message"])

        return result

    except HTTPException:
        raise
    except Exception as e:
        db.rollback()
        import traceback
        print(f"Import error: {traceback.format_exc()}")
        raise HTTPException(status_code=400, detail=f"Error: {str(e)}")


@router.get("/stats")
async def get_import_stats(db: Session = Depends(get_db)):
    """Ambil statistik data alumni di database"""
    total_alumni = db.query(Alumni).count()
    total_pekerjaan = db.query(Pekerjaan).count()
    total_kompetensi = db.query(Kompetensi).count()
    total_tracer = db.query(TracerResponse).count()

    prodi_stats = db.query(
        Alumni.kode_prodi,
        func.count(Alumni.id).label('count')
    ).group_by(Alumni.kode_prodi).all()

    tahun_stats = db.query(
        Alumni.tahun_lulus,
        func.count(Alumni.id).label('count')
    ).group_by(Alumni.tahun_lulus).order_by(Alumni.tahun_lulus.desc()).all()

    return {
        "total_alumni": total_alumni,
        "total_pekerjaan": total_pekerjaan,
        "total_kompetensi": total_kompetensi,
        "total_tracer_responses": total_tracer,
        "by_prodi": [{"prodi": p, "count": c} for p, c in prodi_stats],
        "by_tahun": [{"tahun": t, "count": c} for t, c in tahun_stats]
    }


@router.get("/template")
async def download_template():
    """Download template CSV untuk import data"""
    import csv
    from fastapi.responses import StreamingResponse
    import io

    template_data = [
        ["nimhsmsmh", "nmmhsmsmh", "kdpstmsmh", "tahun_lulus", "F8", "F5b", "F5c", "F1101", "F5a1", "F5a2", "F1761", "F1763", "F1765", "F1767", "F1769", "F1771", "F1773"],
        ["A001", "Nama Alumni 1", "57401", "2024", "1", "PT ABC", "Software Engineer", "3", "Jawa Timur", "Jember", "4", "4", "3", "4", "4", "4", "3"],
        ["A002", "Nama Alumni 2", "56401", "2024", "2", "", "", "", "", "", "3", "3", "3", "3", "3", "3", "3"]
    ]

    output = io.StringIO()
    writer = csv.writer(output, delimiter=';')
    for row in template_data:
        writer.writerow(row)

    output.seek(0)
    return StreamingResponse(
        output,
        media_type='text/csv',
        headers={'Content-Disposition': 'attachment; filename=template_import_alumni.csv'}
    )