# app/services/stats_service.py
# -*- coding: utf-8 -*-
"""
StatsService — один-единственный класс, который:
  • пишет события в activity_log  (log)
  • выдаёт агрегаты для Dashboard’а
"""
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from datetime import datetime, timedelta
from io import StringIO, BytesIO
import csv
from typing import Any, Dict, List

from app.database import get_db_connection


class StatsService:
    # ------------------------------------------------------------
    #  LOG
    # ------------------------------------------------------------
    @staticmethod
    def log(user_id: int, action: str, obj_info: str | None = None) -> None:
        """Пишем событие в activity_log."""
        with get_db_connection() as conn, conn.cursor() as cur:
            cur.execute(
                "INSERT INTO activity_log (user_id, action, obj_info) "
                "VALUES (%s,%s,%s)",
                (user_id, action, obj_info),
            )
            conn.commit()

    # ------------------------------------------------------------
    #  KPI-картки
    # ------------------------------------------------------------
    @staticmethod
    def kpi_counters() -> dict[str, int]:
        """
        Возвращает словарь с ключами:
          • gen_day      — количество генераций за последние 24 часа
          • uploads_day  — количество UPLOAD-ов за 24 часа
          • new_day      — количество новых клиентов за 24 часа
          • gen_week     — количество генераций за последние 7 дней
          • new_week     — количество новых клиентов за 7 дней
          • clients_all  — общее число клиентов
          • clients_ext  — число внешних клиентов
          • clients_pf   — число PF-клиентов
        """
        now = datetime.utcnow()
        day_ago = now - timedelta(days=1)
        week_ago = now - timedelta(days=7)

        with get_db_connection() as conn, conn.cursor() as cur:
            # Генераций за сутки
            cur.execute(
                "SELECT COUNT(*) AS count "
                "  FROM activity_log "
                " WHERE action='generate' AND created_at >= %s",
                (day_ago,),
            )
            gen_day = cur.fetchone()['count']

            # Новых клиентов (строк в users) за сутки
            cur.execute(
                "SELECT COUNT(*) AS count "
                "  FROM users "
                " WHERE created_at >= %s",
                (day_ago,),
            )
            new_day = cur.fetchone()['count']

            # Генераций за неделю
            cur.execute(
                "SELECT COUNT(*) AS count "
                "  FROM activity_log "
                " WHERE action='generate' AND created_at >= %s",
                (week_ago,),
            )
            gen_week = cur.fetchone()['count']

            # Новых клиентов за неделю
            cur.execute(
                "SELECT COUNT(*) AS count "
                "  FROM users "
                " WHERE created_at >= %s",
                (week_ago,),
            )
            new_week = cur.fetchone()['count']

            # Upload-ов за сутки
            cur.execute(
                "SELECT COUNT(*) AS count "
                "  FROM activity_log "
                " WHERE action='upload' AND created_at >= %s",
                (day_ago,),
            )
            uploads_day = cur.fetchone()['count']

            # PF-клиентов (users с is_pf_client = 1)
            cur.execute(
                "SELECT COUNT(*) AS count "
                "  FROM users "
                " WHERE is_pf_client = 1"
            )
            clients_pf = cur.fetchone()['count']

            # Внешних клиентов (users с is_pf_client = 0)
            cur.execute(
                "SELECT COUNT(*) AS count "
                "  FROM users "
                " WHERE is_pf_client = 0"
            )
            clients_ext = cur.fetchone()['count']

            # Всего клиентов = PF + external
            clients_all = clients_pf + clients_ext

        return {
            "gen_day": gen_day,
            "uploads_day": uploads_day,
            "new_day": new_day,
            "gen_week": gen_week,
            "new_week": new_week,
            "clients_all": clients_all,
            "clients_ext": clients_ext,
            "clients_pf": clients_pf,
        }

    # ------------------------------------------------------------
    #  ТОП пользователей
    # ------------------------------------------------------------
    @staticmethod
    def top_users(limit: int = 5) -> List[Dict[str, Any]]:
        with get_db_connection() as conn, conn.cursor() as cur:
            cur.execute(
                """
                SELECT u.username, COUNT(*) AS total
                  FROM activity_log a
                  JOIN users            u ON u.id = a.user_id
                 WHERE a.action='generate'
              GROUP BY a.user_id
              ORDER BY total DESC
                 LIMIT %s
                """,
                (limit,),
            )
            return cur.fetchall()

    # ------------------------------------------------------------
    #  Генерации по дням
    # ------------------------------------------------------------
    @staticmethod
    def daily_generations(days: int = 30) -> List[Dict[str, Any]]:
        date_from = datetime.utcnow() - timedelta(days=days)
        with get_db_connection() as conn, conn.cursor() as cur:
            cur.execute(
                """
                SELECT DATE(created_at) AS day, COUNT(*) AS cnt
                  FROM activity_log
                 WHERE action='generate'
                   AND created_at >= %s
              GROUP BY day
              ORDER BY day
                """,
                (date_from,),
            )
            return cur.fetchall()

    # ------------------------------------------------------------
    #  Клиенты по тарифам
    # ------------------------------------------------------------
    @staticmethod
    def plans_breakdown() -> Dict[str, int]:
        with get_db_connection() as conn, conn.cursor() as cur:
            cur.execute(
                """
                SELECT subscription_plan, COUNT(*) AS cnt
                  FROM users
              GROUP BY subscription_plan
                """
            )
            return {row["subscription_plan"]: row["cnt"] for row in cur}

    # ------------------------------------------------------------
    #  Последние события (без пагинации)
    # ------------------------------------------------------------
    @staticmethod
    def last_activity(limit: int = 20) -> List[Dict[str, Any]]:
        with get_db_connection() as conn, conn.cursor() as cur:
            cur.execute(
                """
                SELECT a.created_at, a.action, a.obj_info, u.username
                  FROM activity_log a
                  JOIN users u ON u.id = a.user_id
              ORDER BY a.created_at DESC
                 LIMIT %s
                """,
                (limit,),
            )
            return cur.fetchall()

    # ------------------------------------------------------------
    #  CSV-экспорт (365 дней)
    # ------------------------------------------------------------
    @staticmethod
    def csv_daily_generations(days: int = 365) -> BytesIO:
        rows = StatsService.daily_generations(days)
        buff = StringIO()
        w = csv.writer(buff)
        w.writerow(("day", "generations"))
        w.writerows((r["day"], r["cnt"]) for r in rows)
        return BytesIO(buff.getvalue().encode("utf-8"))

    # ------------------------------------------------------------
    #  XLSX-экспорт (365 дней)
    # ------------------------------------------------------------
    @staticmethod
    def xlsx_daily_generations(days: int = 365) -> BytesIO:
        rows = StatsService.daily_generations(days)

        wb = Workbook()
        ws = wb.active
        ws.title = "Generations"

        ws.append(("day", "generations"))
        for r in rows:
            ws.append((r["day"], r["cnt"]))

        # Авто-ширина колонок
        for col in ws.columns:
            width = max(len(str(cell.value)) for cell in col) + 2
            ws.column_dimensions[get_column_letter(col[0].column)].width = width

        buf = BytesIO()
        wb.save(buf)
        buf.seek(0)
        return buf