from datetime import datetime

from fastapi import APIRouter, Depends, File, HTTPException, Response, UploadFile
from sqlmodel import Session, select

from app.db import get_session
from app.models import ReferentialItem
from app.schemas import ReferentialItemCreate, ReferentialItemRead
from app.services.import_export import dataframe_to_records, read_tabular_file, records_to_csv_bytes, records_to_xlsx_bytes, validate_required_columns

router = APIRouter(prefix="/referentials", tags=["referentials"])


@router.post("", response_model=ReferentialItemRead)
def create_referential_item(payload: ReferentialItemCreate, session: Session = Depends(get_session)):
    item = ReferentialItem(**payload.model_dump())
    session.add(item)
    session.commit()
    session.refresh(item)
    return item


@router.get("", response_model=list[ReferentialItemRead])
def list_referential_items(type: str | None = None, active: bool | None = None, session: Session = Depends(get_session)):
    query = select(ReferentialItem)
    if type:
        query = query.where(ReferentialItem.type == type)
    if active is not None:
        query = query.where(ReferentialItem.active == active)
    return session.exec(query.order_by(ReferentialItem.type, ReferentialItem.code)).all()


@router.post("/import")
async def import_referentials(file: UploadFile = File(...), session: Session = Depends(get_session)):
    try:
        content = await file.read()
        df = read_tabular_file(content, file.filename or "")
        validate_required_columns(df, {"type", "code", "label"})
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc)) from exc
    imported = 0
    for record in dataframe_to_records(df):
        item = ReferentialItem(
            type=record["type"],
            code=str(record["code"]),
            label=str(record["label"]),
            value_numeric=record.get("value_numeric"),
            value_text=record.get("value_text"),
            unit=record.get("unit"),
            active=bool(record.get("active", True)),
            version=str(record.get("version", "v1")),
            updated_at=datetime.utcnow(),
        )
        session.add(item)
        imported += 1
    session.commit()
    return {"imported": imported}


@router.get("/export")
def export_referentials(format: str = "csv", session: Session = Depends(get_session)):
    items = session.exec(select(ReferentialItem)).all()
    records = [
        {
            "id": item.id,
            "type": item.type,
            "code": item.code,
            "label": item.label,
            "value_numeric": item.value_numeric,
            "value_text": item.value_text,
            "unit": item.unit,
            "active": item.active,
            "version": item.version,
        }
        for item in items
    ]
    if format == "csv":
        return Response(content=records_to_csv_bytes(records), media_type="text/csv", headers={"Content-Disposition": "attachment; filename=referentials.csv"})
    if format == "xlsx":
        return Response(content=records_to_xlsx_bytes(records), media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", headers={"Content-Disposition": "attachment; filename=referentials.xlsx"})
    raise HTTPException(status_code=400, detail="Format attendu: csv ou xlsx")