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 PostalCodeCommune
from app.schemas import PostalCodeCreate, PostalCodeRead
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="/postal-codes", tags=["postal-codes"])


@router.post("", response_model=PostalCodeRead)
def create_postal_code(payload: PostalCodeCreate, session: Session = Depends(get_session)):
    row = PostalCodeCommune(**payload.model_dump(), updated_at=datetime.utcnow())
    session.add(row)
    session.commit()
    session.refresh(row)
    return row


@router.get("", response_model=list[PostalCodeRead])
def search_postal_codes(postal_code: str | None = None, commune: str | None = None, session: Session = Depends(get_session)):
    query = select(PostalCodeCommune)
    if postal_code:
        query = query.where(PostalCodeCommune.postal_code == postal_code)
    if commune:
        query = query.where(PostalCodeCommune.commune.ilike(f"%{commune}%"))
    return session.exec(query.order_by(PostalCodeCommune.postal_code, PostalCodeCommune.commune)).all()


@router.post("/import")
async def import_postal_codes(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, {"postal_code", "commune"})
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc)) from exc
    imported = 0
    for record in dataframe_to_records(df):
        row = PostalCodeCommune(
            postal_code=str(record["postal_code"]).zfill(5),
            commune=str(record["commune"]),
            insee_code=record.get("insee_code"),
            department_code=record.get("department_code"),
            region=record.get("region"),
            active=bool(record.get("active", True)),
            updated_at=datetime.utcnow(),
        )
        session.add(row)
        imported += 1
    session.commit()
    return {"imported": imported}


@router.get("/export")
def export_postal_codes(format: str = "csv", session: Session = Depends(get_session)):
    rows = session.exec(select(PostalCodeCommune)).all()
    records = [
        {
            "id": row.id,
            "postal_code": row.postal_code,
            "commune": row.commune,
            "insee_code": row.insee_code,
            "department_code": row.department_code,
            "region": row.region,
            "active": row.active,
        }
        for row in rows
    ]
    if format == "csv":
        return Response(content=records_to_csv_bytes(records), media_type="text/csv", headers={"Content-Disposition": "attachment; filename=postal_codes.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=postal_codes.xlsx"})
    raise HTTPException(status_code=400, detail="Format attendu: csv ou xlsx")