from io import BytesIO
from typing import Optional

import pandas as pd
from fastapi import APIRouter, Query, Depends
from sqlalchemy.ext.asyncio import AsyncSession

from starlette.responses import StreamingResponse

from apis.base import StandardResponse, PageResponse, convert_page_param, standard_response
from db.database import get_db
from entity.base import parse_datetime
from entity.data_gas import DataGas, DataGasInfo
from services.data_gas_service import DataGasService

router = APIRouter()


@router.get("/page", response_model=StandardResponse[PageResponse[DataGasInfo]])
async def get_gas_page(
        device_code: Optional[str] = None,
        start_time: Optional[str] = None,
        end_time: Optional[str] = None,
        offset: int = Query(0, ge=1),  # 从第几页开始

        limit: int = Query(10, ge=1),  # 每页显示多少条记录
        db: AsyncSession = Depends(get_db)):

    service = DataGasService(db)

    offset, limit = convert_page_param(offset, limit)
    data, total = await service.get_data_gas_page(device_code, parse_datetime(start_time), parse_datetime(end_time),
                                                  offset, limit)

    return standard_response(
        data=PageResponse(total=total, items=data)
    )


@router.get("/export")
async def export_data_gas(device_code: Optional[str] = None,
                          start_time: Optional[str] = None,
                          end_time: Optional[str] = None,
                          db: AsyncSession = Depends(get_db)):
    async with db:
        service = DataGasService(db)
        data = await  service.get_data_gas_list(device_code, start_time, end_time)

        # 将查询结果转换为 DataFrame
        data = [
            {"设备名称": item.device_name, "设备编号": item.device_code, "燃气浓度（ppm.m）": item.gas_value, "时间": item.ts}
            for item in data]
        df = pd.DataFrame(data)

        # 使用 BytesIO 生成内存中的 Excel 文件
        output = BytesIO()
        with pd.ExcelWriter(output, engine="openpyxl") as writer:
            df.to_excel(writer, index=False)

            # 获取工作表
            worksheet = writer.sheets["Sheet1"]

            # 设置固定的列宽，例如宽度为 20
            fixed_width = 30
            for col in worksheet.columns:
                col_letter = col[0].column_letter  # 获取列字母
                worksheet.column_dimensions[col_letter].width = fixed_width

        output.seek(0)

        # 返回内存中的 Excel 文件作为响应
        return StreamingResponse(
            output,
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            headers={"Content-Disposition": "attachment; filename=gas.xlsx"}
        )