from datetime import datetime
from decimal import Decimal

import pytz
import sqlalchemy as sa
from flask import jsonify
from flask_login import current_user
from flask_restx import Resource, reqparse

from controllers.console import api
from controllers.console.app.wraps import get_app_model
from controllers.console.wraps import account_initialization_required, setup_required
from core.app.entities.app_invoke_entities import InvokeFrom
from extensions.ext_database import db
from extensions.ext_redis import redis_client
from libs.helper import DatetimeString
from libs.login import login_required
from models import AppMode, Message


class DailyMessageStatistic(Resource):
    @setup_required
    @login_required
    @account_initialization_required
    @get_app_model
    def get(self, app_model):
        account = current_user

        parser = reqparse.RequestParser()
        parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        args = parser.parse_args()

        timezone = pytz.timezone(account.timezone)
        utc_timezone = pytz.utc

        # Check if querying "today" data
        is_today = False
        if args["start"] and args["end"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")

            now = datetime.now(timezone)
            today_date = now.date()
            if start_datetime.date() == today_date and end_datetime.date() == today_date:
                is_today = True

        # Choose time truncation based on whether it's today
        # Use TO_CHAR with HH24 to ensure 24-hour format (not 12-hour)
        if is_today:
            time_trunc = "TO_CHAR(DATE_TRUNC('hour', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD HH24:MI:SS')"
        else:
            time_trunc = "TO_CHAR(DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD')"

        sql_query = f"""SELECT
    {time_trunc} AS date,
    COUNT(*) AS message_count
FROM
    messages
WHERE
    app_id = :app_id"""
        arg_dict = {"tz": account.timezone, "app_id": app_model.id}

        if args["start"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            start_datetime = start_datetime.replace(second=0)

            start_datetime_timezone = timezone.localize(start_datetime)
            start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at >= :start"
            arg_dict["start"] = start_datetime_utc

        if args["end"]:
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")
            end_datetime = end_datetime.replace(second=0)

            end_datetime_timezone = timezone.localize(end_datetime)
            end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at < :end"
            arg_dict["end"] = end_datetime_utc

        sql_query += " GROUP BY date ORDER BY date"

        response_data = []

        with db.engine.begin() as conn:
            rs = conn.execute(sa.text(sql_query), arg_dict)
            for i in rs:
                # date is already formatted string from TO_CHAR
                response_data.append({"date": i.date, "message_count": i.message_count})

        return jsonify({"data": response_data})


class DailyConversationStatistic(Resource):
    @setup_required
    @login_required
    @account_initialization_required
    @get_app_model
    def get(self, app_model):
        account = current_user

        parser = reqparse.RequestParser()
        parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        args = parser.parse_args()

        timezone = pytz.timezone(account.timezone)
        utc_timezone = pytz.utc

        # Check if querying "today" data
        is_today = False
        if args["start"] and args["end"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")

            now = datetime.now(timezone)
            today_date = now.date()
            if start_datetime.date() == today_date and end_datetime.date() == today_date:
                is_today = True

        # Choose time truncation based on whether it's today
        if is_today:
            # Hourly aggregation - use TO_CHAR to ensure correct string format
            date_column = sa.func.to_char(
                sa.func.date_trunc("hour", sa.text("created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz")),
                sa.text("'YYYY-MM-DD HH24:MI:SS'")
            ).label("date")
        else:
            # Daily aggregation
            date_column = sa.func.to_char(
                sa.func.date_trunc("day", sa.text("created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz")),
                sa.text("'YYYY-MM-DD'")
            ).label("date")

        # Count conversations based on conversation creation time (not message time)
        # This ensures consistency with AverageSessionInteractionStatistic
        from models.model import Conversation
        stmt = (
            sa.select(
                date_column,
                sa.func.count(Conversation.id).label("conversation_count"),
            )
            .select_from(Conversation)
            .where(Conversation.app_id == app_model.id)
        )

        if args["start"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            start_datetime = start_datetime.replace(second=0)
            start_datetime_timezone = timezone.localize(start_datetime)
            start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)
            stmt = stmt.where(Conversation.created_at >= start_datetime_utc)

        if args["end"]:
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")
            end_datetime = end_datetime.replace(second=0)
            end_datetime_timezone = timezone.localize(end_datetime)
            end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)
            stmt = stmt.where(Conversation.created_at < end_datetime_utc)

        stmt = stmt.group_by("date").order_by("date")

        response_data = []
        with db.engine.begin() as conn:
            rs = conn.execute(stmt, {"tz": account.timezone})
            for row in rs:
                # date is already a formatted string from TO_CHAR
                response_data.append({"date": row.date, "conversation_count": row.conversation_count})

        return jsonify({"data": response_data})


class DailyTerminalsStatistic(Resource):
    @setup_required
    @login_required
    @account_initialization_required
    @get_app_model
    def get(self, app_model):
        account = current_user

        parser = reqparse.RequestParser()
        parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        args = parser.parse_args()

        timezone = pytz.timezone(account.timezone)
        utc_timezone = pytz.utc

        # Check if querying "today" data
        is_today = False
        if args["start"] and args["end"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")

            now = datetime.now(timezone)
            today_date = now.date()
            if start_datetime.date() == today_date and end_datetime.date() == today_date:
                is_today = True

        # Choose time truncation based on whether it's today
        # Use TO_CHAR with HH24 to ensure 24-hour format (not 12-hour)
        if is_today:
            time_trunc = "TO_CHAR(DATE_TRUNC('hour', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD HH24:MI:SS')"
        else:
            time_trunc = "TO_CHAR(DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD')"

        sql_query = f"""SELECT
    {time_trunc} AS date,
    COUNT(DISTINCT messages.from_end_user_id) AS terminal_count
FROM
    messages
WHERE
    app_id = :app_id"""
        arg_dict = {"tz": account.timezone, "app_id": app_model.id}

        if args["start"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            start_datetime = start_datetime.replace(second=0)

            start_datetime_timezone = timezone.localize(start_datetime)
            start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at >= :start"
            arg_dict["start"] = start_datetime_utc

        if args["end"]:
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")
            end_datetime = end_datetime.replace(second=0)

            end_datetime_timezone = timezone.localize(end_datetime)
            end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at < :end"
            arg_dict["end"] = end_datetime_utc

        sql_query += " GROUP BY date ORDER BY date"

        response_data = []

        with db.engine.begin() as conn:
            rs = conn.execute(sa.text(sql_query), arg_dict)
            for i in rs:
                # date is already formatted string from TO_CHAR
                response_data.append({"date": i.date, "terminal_count": i.terminal_count})

        return jsonify({"data": response_data})


class DailyTokenCostStatistic(Resource):
    @setup_required
    @login_required
    @account_initialization_required
    @get_app_model
    def get(self, app_model):
        account = current_user

        parser = reqparse.RequestParser()
        parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        args = parser.parse_args()

        timezone = pytz.timezone(account.timezone)
        utc_timezone = pytz.utc

        # Check if querying "today" data
        is_today = False
        if args["start"] and args["end"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")

            now = datetime.now(timezone)
            today_date = now.date()
            if start_datetime.date() == today_date and end_datetime.date() == today_date:
                is_today = True

        # Choose time truncation based on whether it's today
        # Use TO_CHAR with HH24 to ensure 24-hour format (not 12-hour)
        if is_today:
            time_trunc = "TO_CHAR(DATE_TRUNC('hour', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD HH24:MI:SS')"
        else:
            time_trunc = "TO_CHAR(DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD')"

        sql_query = f"""SELECT
    {time_trunc} AS date,
    (SUM(messages.message_tokens) + SUM(messages.answer_tokens)) AS token_count,
    SUM(total_price) AS total_price
FROM
    messages
WHERE
    app_id = :app_id"""
        arg_dict = {"tz": account.timezone, "app_id": app_model.id}

        if args["start"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            start_datetime = start_datetime.replace(second=0)

            start_datetime_timezone = timezone.localize(start_datetime)
            start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at >= :start"
            arg_dict["start"] = start_datetime_utc

        if args["end"]:
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")
            end_datetime = end_datetime.replace(second=0)

            end_datetime_timezone = timezone.localize(end_datetime)
            end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at < :end"
            arg_dict["end"] = end_datetime_utc

        sql_query += " GROUP BY date ORDER BY date"

        response_data = []

        with db.engine.begin() as conn:
            rs = conn.execute(sa.text(sql_query), arg_dict)
            for i in rs:
                # date is already formatted string from TO_CHAR
                response_data.append(
                    {"date": i.date, "token_count": i.token_count, "total_price": i.total_price, "currency": "USD"}
                )

        return jsonify({"data": response_data})


class AverageSessionInteractionStatistic(Resource):
    @setup_required
    @login_required
    @account_initialization_required
    @get_app_model(mode=[AppMode.CHAT, AppMode.AGENT_CHAT, AppMode.ADVANCED_CHAT])
    def get(self, app_model):
        account = current_user

        parser = reqparse.RequestParser()
        parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        args = parser.parse_args()

        timezone = pytz.timezone(account.timezone)
        utc_timezone = pytz.utc

        # Check if querying "today" data
        is_today = False
        if args["start"] and args["end"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")

            now = datetime.now(timezone)
            today_date = now.date()
            if start_datetime.date() == today_date and end_datetime.date() == today_date:
                is_today = True

        # Choose time truncation based on whether it's today
        if is_today:
            time_trunc = "TO_CHAR(DATE_TRUNC('hour', c.created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD HH24:MI:SS')"
        else:
            time_trunc = "TO_CHAR(DATE_TRUNC('day', c.created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD')"

        # Simplified SQL: calculate average message count per conversation created in the time period
        # This reflects user stickiness - average interaction depth of conversations created in this period
        sql_query = f"""SELECT
    {time_trunc} AS date,
    AVG(COALESCE(msg_counts.message_count, 0)) AS interactions
FROM
    conversations c
LEFT JOIN
    (
        SELECT
            conversation_id,
            COUNT(id) AS message_count
        FROM
            messages
        WHERE
            app_id = :app_id
        GROUP BY conversation_id
    ) msg_counts
    ON c.id = msg_counts.conversation_id
WHERE
    c.app_id = :app_id"""

        arg_dict = {"tz": account.timezone, "app_id": app_model.id}

        if args["start"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            start_datetime = start_datetime.replace(second=0)

            start_datetime_timezone = timezone.localize(start_datetime)
            start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND c.created_at >= :start"
            arg_dict["start"] = start_datetime_utc

        if args["end"]:
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")
            end_datetime = end_datetime.replace(second=0)

            end_datetime_timezone = timezone.localize(end_datetime)
            end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND c.created_at < :end"
            arg_dict["end"] = end_datetime_utc

        sql_query += """
GROUP BY
    date
ORDER BY
    date"""

        response_data = []

        with db.engine.begin() as conn:
            rs = conn.execute(sa.text(sql_query), arg_dict)
            for i in rs:
                # date is already formatted string from TO_CHAR
                response_data.append(
                    {"date": i.date, "interactions": float(i.interactions.quantize(Decimal("0.01")))}
                )

        return jsonify({"data": response_data})


class UserSatisfactionRateStatistic(Resource):
    @setup_required
    @login_required
    @account_initialization_required
    @get_app_model
    def get(self, app_model):
        account = current_user

        parser = reqparse.RequestParser()
        parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        args = parser.parse_args()

        timezone = pytz.timezone(account.timezone)
        utc_timezone = pytz.utc

        # Check if querying "today" data
        is_today = False
        if args["start"] and args["end"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")

            now = datetime.now(timezone)
            today_date = now.date()
            if start_datetime.date() == today_date and end_datetime.date() == today_date:
                is_today = True

        # Choose time truncation based on whether it's today
        if is_today:
            time_trunc = "TO_CHAR(DATE_TRUNC('hour', m.created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD HH24:MI:SS')"
        else:
            time_trunc = "TO_CHAR(DATE_TRUNC('day', m.created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD')"

        sql_query = f"""SELECT
    {time_trunc} AS date,
    COUNT(m.id) AS message_count,
    COUNT(mf.id) AS feedback_count
FROM
    messages m
LEFT JOIN
    message_feedbacks mf
    ON mf.message_id=m.id AND mf.rating='like'
WHERE
    m.app_id = :app_id"""
        arg_dict = {"tz": account.timezone, "app_id": app_model.id}

        if args["start"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            start_datetime = start_datetime.replace(second=0)

            start_datetime_timezone = timezone.localize(start_datetime)
            start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND m.created_at >= :start"
            arg_dict["start"] = start_datetime_utc

        if args["end"]:
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")
            end_datetime = end_datetime.replace(second=0)

            end_datetime_timezone = timezone.localize(end_datetime)
            end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND m.created_at < :end"
            arg_dict["end"] = end_datetime_utc

        sql_query += " GROUP BY date ORDER BY date"

        response_data = []

        with db.engine.begin() as conn:
            rs = conn.execute(sa.text(sql_query), arg_dict)
            for i in rs:
                # date is already formatted string from TO_CHAR
                response_data.append(
                    {
                        "date": i.date,
                        "rate": round((i.feedback_count * 1000 / i.message_count) if i.message_count > 0 else 0, 2),
                    }
                )

        return jsonify({"data": response_data})


class AverageResponseTimeStatistic(Resource):
    @setup_required
    @login_required
    @account_initialization_required
    @get_app_model(mode=AppMode.COMPLETION)
    def get(self, app_model):
        account = current_user

        parser = reqparse.RequestParser()
        parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        args = parser.parse_args()

        timezone = pytz.timezone(account.timezone)
        utc_timezone = pytz.utc

        # Check if querying "today" data
        is_today = False
        if args["start"] and args["end"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")

            now = datetime.now(timezone)
            today_date = now.date()
            if start_datetime.date() == today_date and end_datetime.date() == today_date:
                is_today = True

        # Choose time truncation based on whether it's today
        if is_today:
            time_trunc = "TO_CHAR(DATE_TRUNC('hour', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD HH24:MI:SS')"
        else:
            time_trunc = "TO_CHAR(DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD')"

        sql_query = f"""SELECT
    {time_trunc} AS date,
    AVG(provider_response_latency) AS latency
FROM
    messages
WHERE
    app_id = :app_id"""
        arg_dict = {"tz": account.timezone, "app_id": app_model.id}

        if args["start"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            start_datetime = start_datetime.replace(second=0)

            start_datetime_timezone = timezone.localize(start_datetime)
            start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at >= :start"
            arg_dict["start"] = start_datetime_utc

        if args["end"]:
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")
            end_datetime = end_datetime.replace(second=0)

            end_datetime_timezone = timezone.localize(end_datetime)
            end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at < :end"
            arg_dict["end"] = end_datetime_utc

        sql_query += " GROUP BY date ORDER BY date"

        response_data = []

        with db.engine.begin() as conn:
            rs = conn.execute(sa.text(sql_query), arg_dict)
            for i in rs:
                # date is already formatted string from TO_CHAR
                response_data.append({"date": i.date, "latency": round(i.latency * 1000, 4)})

        return jsonify({"data": response_data})


class TokensPerSecondStatistic(Resource):
    @setup_required
    @login_required
    @account_initialization_required
    @get_app_model
    def get(self, app_model):
        account = current_user

        parser = reqparse.RequestParser()
        parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        args = parser.parse_args()

        timezone = pytz.timezone(account.timezone)
        utc_timezone = pytz.utc

        # Check if querying "today" data
        is_today = False
        if args["start"] and args["end"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")

            now = datetime.now(timezone)
            today_date = now.date()
            if start_datetime.date() == today_date and end_datetime.date() == today_date:
                is_today = True

        # Choose time truncation based on whether it's today
        if is_today:
            time_trunc = "TO_CHAR(DATE_TRUNC('hour', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD HH24:MI:SS')"
        else:
            time_trunc = "TO_CHAR(DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD')"

        sql_query = f"""SELECT
    {time_trunc} AS date,
    CASE
        WHEN SUM(provider_response_latency) = 0 THEN 0
        ELSE (SUM(answer_tokens) / SUM(provider_response_latency))
    END as tokens_per_second
FROM
    messages
WHERE
    app_id = :app_id"""
        arg_dict = {"tz": account.timezone, "app_id": app_model.id}

        if args["start"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            start_datetime = start_datetime.replace(second=0)

            start_datetime_timezone = timezone.localize(start_datetime)
            start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at >= :start"
            arg_dict["start"] = start_datetime_utc

        if args["end"]:
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")
            end_datetime = end_datetime.replace(second=0)

            end_datetime_timezone = timezone.localize(end_datetime)
            end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at < :end"
            arg_dict["end"] = end_datetime_utc

        sql_query += " GROUP BY date ORDER BY date"

        response_data = []

        with db.engine.begin() as conn:
            rs = conn.execute(sa.text(sql_query), arg_dict)
            for i in rs:
                # date is already formatted string from TO_CHAR
                response_data.append({"date": i.date, "tps": round(i.tokens_per_second, 4)})

        return jsonify({"data": response_data})


class ResponseTimePercentilesStatistic(Resource):
    """Response time percentiles (P99/P95/P50) statistic."""

    @setup_required
    @login_required
    @account_initialization_required
    @get_app_model
    def get(self, app_model):
        account = current_user

        parser = reqparse.RequestParser()
        parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        args = parser.parse_args()

        timezone = pytz.timezone(account.timezone)
        utc_timezone = pytz.utc

        # Check if querying "today" data
        is_today = False
        if args["start"] and args["end"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")

            # If start and end are both on today, use hourly granularity
            now = datetime.now(timezone)
            today_date = now.date()
            if start_datetime.date() == today_date and end_datetime.date() == today_date:
                is_today = True

        # Choose time truncation based on whether it's today
        # Use TO_CHAR with HH24 to ensure 24-hour format (not 12-hour)
        if is_today:
            # Hourly aggregation for today
            time_trunc = "TO_CHAR(DATE_TRUNC('hour', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD HH24:MI:SS')"
        else:
            # Daily aggregation for other periods
            time_trunc = "TO_CHAR(DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD')"

        sql_query = f"""SELECT
    {time_trunc} AS date,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY provider_response_latency) AS p50,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY provider_response_latency) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY provider_response_latency) AS p99,
    AVG(provider_response_latency) AS avg_latency
FROM
    messages
WHERE
    app_id = :app_id"""
        arg_dict = {"tz": account.timezone, "app_id": app_model.id}

        if args["start"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            start_datetime = start_datetime.replace(second=0)

            start_datetime_timezone = timezone.localize(start_datetime)
            start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at >= :start"
            arg_dict["start"] = start_datetime_utc

        if args["end"]:
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")
            end_datetime = end_datetime.replace(second=0)

            end_datetime_timezone = timezone.localize(end_datetime)
            end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at < :end"
            arg_dict["end"] = end_datetime_utc

        sql_query += " GROUP BY date ORDER BY date"

        response_data = []

        with db.engine.begin() as conn:
            rs = conn.execute(sa.text(sql_query), arg_dict)
            for i in rs:
                # date is already formatted string from TO_CHAR
                response_data.append(
                    {
                        "date": i.date,
                        "p50": round(i.p50 * 1000, 4) if i.p50 else 0,
                        "p95": round(i.p95 * 1000, 4) if i.p95 else 0,
                        "p99": round(i.p99 * 1000, 4) if i.p99 else 0,
                        "avg": round(i.avg_latency * 1000, 4) if i.avg_latency else 0,
                    }
                )

        return jsonify({"data": response_data})


class TokenUsageDetailStatistic(Resource):
    """Token usage with input/output breakdown statistic."""

    @setup_required
    @login_required
    @account_initialization_required
    @get_app_model
    def get(self, app_model):
        account = current_user

        parser = reqparse.RequestParser()
        parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        args = parser.parse_args()

        timezone = pytz.timezone(account.timezone)
        utc_timezone = pytz.utc

        # Check if querying "today" data
        is_today = False
        if args["start"] and args["end"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")

            # If start and end are both on today, use hourly granularity
            now = datetime.now(timezone)
            today_date = now.date()
            if start_datetime.date() == today_date and end_datetime.date() == today_date:
                is_today = True

        # Choose time truncation based on whether it's today
        # Use TO_CHAR with HH24 to ensure 24-hour format (not 12-hour)
        if is_today:
            # Hourly aggregation for today
            time_trunc = "TO_CHAR(DATE_TRUNC('hour', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD HH24:MI:SS')"
        else:
            # Daily aggregation for other periods
            time_trunc = "TO_CHAR(DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD')"

        sql_query = f"""SELECT
    {time_trunc} AS date,
    SUM(messages.message_tokens) AS input_tokens,
    SUM(messages.answer_tokens) AS output_tokens,
    (SUM(messages.message_tokens) + SUM(messages.answer_tokens)) AS total_tokens,
    SUM(total_price) AS total_price
FROM
    messages
WHERE
    app_id = :app_id"""
        arg_dict = {"tz": account.timezone, "app_id": app_model.id}

        if args["start"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            start_datetime = start_datetime.replace(second=0)

            start_datetime_timezone = timezone.localize(start_datetime)
            start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at >= :start"
            arg_dict["start"] = start_datetime_utc

        if args["end"]:
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")
            end_datetime = end_datetime.replace(second=0)

            end_datetime_timezone = timezone.localize(end_datetime)
            end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at < :end"
            arg_dict["end"] = end_datetime_utc

        sql_query += " GROUP BY date ORDER BY date"

        response_data = []

        with db.engine.begin() as conn:
            rs = conn.execute(sa.text(sql_query), arg_dict)
            for i in rs:
                # date is already formatted string from TO_CHAR
                response_data.append(
                    {
                        "date": i.date,
                        "input_tokens": i.input_tokens or 0,
                        "output_tokens": i.output_tokens or 0,
                        "total_tokens": i.total_tokens or 0,
                        "total_price": i.total_price,
                        "currency": "USD",
                    }
                )

        return jsonify({"data": response_data})


class ErrorRateStatistic(Resource):
    """Error rate statistic."""

    @setup_required
    @login_required
    @account_initialization_required
    @get_app_model
    def get(self, app_model):
        account = current_user

        parser = reqparse.RequestParser()
        parser.add_argument("start", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        parser.add_argument("end", type=DatetimeString("%Y-%m-%d %H:%M"), location="args")
        args = parser.parse_args()

        timezone = pytz.timezone(account.timezone)
        utc_timezone = pytz.utc

        # Check if querying "today" data
        is_today = False
        if args["start"] and args["end"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")

            # If start and end are both on today, use hourly granularity
            now = datetime.now(timezone)
            today_date = now.date()
            if start_datetime.date() == today_date and end_datetime.date() == today_date:
                is_today = True

        # Choose time truncation based on whether it's today
        # Use TO_CHAR with HH24 to ensure 24-hour format (not 12-hour)
        if is_today:
            # Hourly aggregation for today
            time_trunc = "TO_CHAR(DATE_TRUNC('hour', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD HH24:MI:SS')"
        else:
            # Daily aggregation for other periods
            time_trunc = "TO_CHAR(DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE :tz), 'YYYY-MM-DD')"

        sql_query = f"""SELECT
    {time_trunc} AS date,
    COUNT(*) AS total_count,
    COUNT(CASE WHEN status != 'normal' THEN 1 END) AS error_count
FROM
    messages
WHERE
    app_id = :app_id"""
        arg_dict = {"tz": account.timezone, "app_id": app_model.id}

        if args["start"]:
            start_datetime = datetime.strptime(args["start"], "%Y-%m-%d %H:%M")
            start_datetime = start_datetime.replace(second=0)

            start_datetime_timezone = timezone.localize(start_datetime)
            start_datetime_utc = start_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at >= :start"
            arg_dict["start"] = start_datetime_utc

        if args["end"]:
            end_datetime = datetime.strptime(args["end"], "%Y-%m-%d %H:%M")
            end_datetime = end_datetime.replace(second=0)

            end_datetime_timezone = timezone.localize(end_datetime)
            end_datetime_utc = end_datetime_timezone.astimezone(utc_timezone)

            sql_query += " AND created_at < :end"
            arg_dict["end"] = end_datetime_utc

        sql_query += " GROUP BY date ORDER BY date"

        response_data = []

        with db.engine.begin() as conn:
            rs = conn.execute(sa.text(sql_query), arg_dict)
            for i in rs:
                error_rate = (i.error_count * 100 / i.total_count) if i.total_count > 0 else 0
                # date is already formatted string from TO_CHAR
                response_data.append(
                    {
                        "date": i.date,
                        "total_count": i.total_count,
                        "error_count": i.error_count,
                        "error_rate": round(error_rate, 2),
                    }
                )

        return jsonify({"data": response_data})


class ConcurrentRequestsStatistic(Resource):
    """Concurrent requests statistic."""

    @setup_required
    @login_required
    @account_initialization_required
    @get_app_model
    def get(self, app_model):
        """Get real-time concurrent requests count."""
        try:
            # Get active requests key pattern for this app
            active_requests_key = f"dify:rate_limit:{app_model.id}:active_requests"

            # Get count of active requests from Redis
            concurrent_count = 0
            if redis_client.exists(active_requests_key):
                concurrent_count = redis_client.hlen(active_requests_key)

            # Return current concurrent requests count
            return jsonify({
                "data": {
                    "current_concurrent_requests": concurrent_count,
                    "timestamp": datetime.now().isoformat(),
                }
            })
        except Exception as e:
            # If Redis is unavailable or any error occurs, return 0
            return jsonify({
                "data": {
                    "current_concurrent_requests": 0,
                    "timestamp": datetime.now().isoformat(),
                    "error": str(e),
                }
            })


api.add_resource(DailyMessageStatistic, "/apps/<uuid:app_id>/statistics/daily-messages")
api.add_resource(DailyConversationStatistic, "/apps/<uuid:app_id>/statistics/daily-conversations")
api.add_resource(DailyTerminalsStatistic, "/apps/<uuid:app_id>/statistics/daily-end-users")
api.add_resource(DailyTokenCostStatistic, "/apps/<uuid:app_id>/statistics/token-costs")
api.add_resource(AverageSessionInteractionStatistic, "/apps/<uuid:app_id>/statistics/average-session-interactions")
api.add_resource(UserSatisfactionRateStatistic, "/apps/<uuid:app_id>/statistics/user-satisfaction-rate")
api.add_resource(AverageResponseTimeStatistic, "/apps/<uuid:app_id>/statistics/average-response-time")
api.add_resource(TokensPerSecondStatistic, "/apps/<uuid:app_id>/statistics/tokens-per-second")

# P0 New Statistics
api.add_resource(ResponseTimePercentilesStatistic, "/apps/<uuid:app_id>/statistics/response-time-percentiles")
api.add_resource(TokenUsageDetailStatistic, "/apps/<uuid:app_id>/statistics/token-usage-detail")
api.add_resource(ErrorRateStatistic, "/apps/<uuid:app_id>/statistics/error-rate")
api.add_resource(ConcurrentRequestsStatistic, "/apps/<uuid:app_id>/statistics/concurrent-requests")