"""数据导入 API"""
from fastapi import APIRouter, UploadFile, File, Depends, Response
from sqlalchemy.orm import Session
from app.core.database import get_db
from app.middleware.permission_middleware import require_permission
from app.services.import_service import ImportService
import shutil
import io
from openpyxl import Workbook

router = APIRouter(prefix="/api/import", tags=["数据导入"])


@router.get("/template")
def download_template():
    """下载导入数据模板"""
    wb = Workbook()
    ws = wb.active
    ws.title = "ONU设备导入模板"

    # 表头：序号|区域|学校名称|楼宇|场所类型|房间号|MAC地址|备注
    headers = ["mac_address", "region", "school_name", "building", "place_type", "room_number", "notes"]
    ws.append(headers)

    # 示例数据
    example_data = [
        ["AA:BB:CC:DD:EE:01", "区域1", "学校1", "1号楼", "教室", "101", ""],
        ["AA:BB:CC:DD:EE:02", "区域1", "学校1", "1号楼", "办公室", "102", ""],
    ]
    for row in example_data:
        ws.append(row)

    # 设置列宽
    ws.column_dimensions['A'].width = 20  # mac_address
    ws.column_dimensions['B'].width = 12  # region
    ws.column_dimensions['C'].width = 18  # school_name
    ws.column_dimensions['D'].width = 12  # building
    ws.column_dimensions['E'].width = 12  # place_type
    ws.column_dimensions['F'].width = 12  # room_number
    ws.column_dimensions['G'].width = 20  # notes

    # 保存到内存
    output = io.BytesIO()
    wb.save(output)
    output.seek(0)

    return Response(
        content=output.getvalue(),
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": "attachment; filename=onu_import_template.xlsx"}
    )


@router.post("/upload")
async def upload_excel(
    file: UploadFile = File(...),
    olt_id: int = None,
    db: Session = Depends(get_db),
    _: dict = Depends(require_permission('device.import')),
):
    """上传并导入 Excel 文件（仅导入 MAC 信息，不关联 OLT）"""
    file_path = f"/tmp/{file.filename}"
    with open(file_path, "wb") as buffer:
        shutil.copyfileobj(file.file, buffer)

    service = ImportService(db)
    records = service.parse_excel(file_path)
    validation = service.validate_data(records)

    created = updated = 0
    if validation['valid']:
        result = service.import_devices(validation['valid'], olt_id)
        created = result['created']
        updated = result['updated']

    return {
        "success": created + updated,
        "created": created,
        "updated": updated,
        "failed": validation['invalid']
    }