from sqlalchemy import delete, select, update, and_, desc, insert
from sqlalchemy.ext.asyncio import AsyncSession
from module_admin.entity.do.sys_statistics_do import SysStatistics
from module_admin.entity.vo.sys_statistics_vo import Sys_statisticsModel, Sys_statisticsPageQueryModel
from utils.page_util import PageUtil
from datetime import datetime, date


class Sys_statisticsDao:
    """
    系统统计数据模块数据库操作层
    """
    
    @classmethod
    async def add_count(cls, db: AsyncSession, field: str, user_name:str , count: int = 1):
        stmt = (
            select(SysStatistics)
            .where(
                SysStatistics.stat_date == datetime.now().date()
            )
        )
        result = (await db.execute(stmt)).scalars().first()
        if result:
            update_stmt = (
                update(SysStatistics)
                .where(
                    SysStatistics.statistic_id == result.statistic_id
                )
                .values(
                    {
                        field: getattr(result, field) + count,
                        "update_time": datetime.now(),
                        "update_by": user_name,
                    }
                )
            )
            await db.execute(update_stmt)
        else:
            insert_stmt = (
                insert(SysStatistics)
                .values(
                    {
                        "stat_date": datetime.now().date(),
                        field: 1,
                        "create_time": datetime.now(),
                        "update_time": datetime.now(),
                        "create_by": user_name,
                    }
                )
            )
            await db.execute(insert_stmt)
    
    @classmethod
    async def add_model_call_count(cls, db: AsyncSession, user_name: str):
        stmt = (
            select(SysStatistics)
            .where(
                SysStatistics.stat_date == datetime.now().date()
            )
        )
        result = (await db.execute(stmt)).scalars().first()
        if result:
            update_stmt = (
                update(SysStatistics)
                .where(
                    SysStatistics.statistic_id == result.statistic_id
                )
                .values(
                    {
                        "llm_call": result.llm_call + 1,
                        "update_time": datetime.now(),
                        "update_by": user_name,
                    }
                )
            )
            await db.execute(update_stmt)
        else:
            insert_stmt = (
                insert(SysStatistics)
                .values(
                    {
                        "stat_date": datetime.now().date(),
                        "llm_call": 1,
                        "create_time": datetime.now(),
                        "update_time": datetime.now(),
                        "create_by": user_name,
                    }
                )
            )
            await db.execute(insert_stmt)
            
        
            


    @classmethod
    async def get_access_control_success_rate(cls, db: AsyncSession, start_time: datetime, end_time: datetime):
        stmt = (
            select(SysStatistics.access_control_success_rate, SysStatistics.stat_date)
            .where(
                and_(
                    SysStatistics.stat_date >= start_time,
                    SysStatistics.stat_date <= end_time
                )
            )
            .order_by(desc(SysStatistics.stat_date))
        )

        return  (await db.execute(stmt)).mappings().all()

    @classmethod
    async def get_visitor_count(cls, db: AsyncSession, start_time: date, end_time: date):
        stmt = (
            select(SysStatistics.visitor_guide, SysStatistics.stat_date)
            .where(
                and_(
                    SysStatistics.stat_date >= start_time,
                    SysStatistics.stat_date <= end_time,
                )
            )
            .order_by(desc(SysStatistics.stat_date))
        )

        return (await db.execute(stmt)).mappings().all()

    @classmethod
    async def get_statistics_data(cls, db: AsyncSession, dd: date):
        stmt = (
            select(SysStatistics.llm_call,SysStatistics.access_control_count, SysStatistics.visitor_guide, SysStatistics.exhibition_explanation)
            .where(SysStatistics.stat_date == dd)
        )
        return (await db.execute(stmt)).mappings().first()


    @classmethod
    async def get_sys_statistics_detail_by_id(cls, db: AsyncSession, statistic_id: int):
        """
        根据统计项目ID获取系统统计数据详细信息

        :param db: orm对象
        :param statistic_id: 统计项目ID
        :return: 系统统计数据信息对象
        """
        sys_statistics_info = (
            (
                await db.execute(
                    select(SysStatistics)
                    .where(
                        SysStatistics.statistic_id == statistic_id
                    )
                )
            )
            .scalars()
            .first()
        )

        return sys_statistics_info

    @classmethod
    async def get_sys_statistics_detail_by_info(cls, db: AsyncSession, sys_statistics: Sys_statisticsModel):
        """
        根据系统统计数据参数获取系统统计数据信息

        :param db: orm对象
        :param sys_statistics: 系统统计数据参数对象
        :return: 系统统计数据信息对象
        """
        sys_statistics_info = (
            (
                await db.execute(
                    select(SysStatistics).where(
                    )
                )
            )
            .scalars()
            .first()
        )

        return sys_statistics_info

    @classmethod
    async def get_sys_statistics_list(cls, db: AsyncSession, query_object: Sys_statisticsPageQueryModel, is_page: bool = False):
        """
        根据查询参数获取系统统计数据列表信息

        :param db: orm对象
        :param query_object: 查询参数对象
        :param is_page: 是否开启分页
        :return: 系统统计数据列表信息对象
        """
        query = (
            select(SysStatistics)
            .where(
                SysStatistics.stat_date == query_object.stat_date if query_object.stat_date else True,
            )
            .order_by(SysStatistics.statistic_id)
            .distinct()
        )
        sys_statistics_list = await PageUtil.paginate(db, query, query_object.page_num, query_object.page_size, is_page)

        return sys_statistics_list

    @classmethod
    async def add_sys_statistics_dao(cls, db: AsyncSession, sys_statistics: Sys_statisticsModel):
        """
        新增系统统计数据数据库操作

        :param db: orm对象
        :param sys_statistics: 系统统计数据对象
        :return:
        """
        db_sys_statistics = SysStatistics(**sys_statistics.model_dump(exclude={}))
        db.add(db_sys_statistics)
        await db.flush()

        return db_sys_statistics

    @classmethod
    async def edit_sys_statistics_dao(cls, db: AsyncSession, sys_statistics: dict):
        """
        编辑系统统计数据数据库操作

        :param db: orm对象
        :param sys_statistics: 需要更新的系统统计数据字典
        :return:
        """
        await db.execute(update(SysStatistics), [sys_statistics])

    @classmethod
    async def delete_sys_statistics_dao(cls, db: AsyncSession, sys_statistics: Sys_statisticsModel):
        """
        删除系统统计数据数据库操作

        :param db: orm对象
        :param sys_statistics: 系统统计数据对象
        :return:
        """
        await db.execute(delete(SysStatistics).where(SysStatistics.statistic_id.in_([sys_statistics.statistic_id])))

