from datetime import datetime
from typing import List, Optional

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 read_tabular_file, records_to_csv_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.dict(), 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: Optional[str] = None, commune: Optional[str] = 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("%{}%".format(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()
        records = read_tabular_file(content, file.filename or "")
        validate_required_columns(records, {"postal_code", "commune"})
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc))

    imported = 0
    for record in records:
        row = PostalCodeCommune(
            postal_code=str(record["postal_code"]).zfill(5),
            commune=str(record["commune"]),
            insee_code=record.get("insee_code") or None,
            department_code=record.get("department_code") or None,
            region=record.get("region") or None,
            active=str(record.get("active", "true")).lower() != "false",
            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)):
    if format != "csv":
        raise HTTPException(status_code=400, detail="Format attendu: csv")

    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
    ]
    return Response(content=records_to_csv_bytes(records), media_type="text/csv", headers={"Content-Disposition": "attachment; filename=postal_codes.csv"})