from io import BytesIO
from typing import Optional

import pandas as pd
from fastapi import APIRouter, Query, Depends
from openpyxl.utils import get_column_letter
from sqlmodel import Session
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
from services.data_gas_service import DataGasService

router = APIRouter()


@router.get("/page", response_model=StandardResponse[PageResponse[DataGas]])
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: Session = Depends(get_db)):
    service = DataGasService(db)

    offset, limit = convert_page_param(offset, limit)
    data, total = 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")
def export_data_gas(device_code: Optional[str] = None,
                    start_time: Optional[str] = None,
                    end_time: Optional[str] = None,
                    db: Session = Depends(get_db)):
    service = DataGasService(db)
    data = service.get_data_gas_list(device_code, start_time, end_time)

    # 将查询结果转换为 DataFrame
    data = [{"设备编号": 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"}
    )