"""Daily sales digest — SAP HANA snapshot cached in Redis, refreshed once per day."""
from __future__ import annotations

import json
import logging
import os
from datetime import date, datetime
from zoneinfo import ZoneInfo

logger = logging.getLogger("apex")

_TTL_SECONDS = 86400  # 24 hours


_IST = ZoneInfo("Asia/Kolkata")


def _today_ist() -> date:
    return datetime.now(tz=_IST).date()


def _today_key(tenant_id: str) -> str:
    return f"digest:{tenant_id}:{_today_ist().isoformat()}"


def _fetch_fresh_digest(hana_conn) -> dict:
    """Run three lightweight SAP HANA queries and return a summary dict."""
    digest: dict = {}
    hana_date = _today_ist().strftime("%Y%m%d")   # HANA stores BILLINGDATE as YYYYMMDD
    display_date = _today_ist().isoformat()        # human-readable YYYY-MM-DD for output

    queries = {
        "total_revenue": (
            "SELECT COALESCE(SUM(CAST(NETVALUE AS DOUBLE)), 0) AS val "
            "FROM sapabap1.ztab_salesv1 "
            f"WHERE BILLINGDATE = '{hana_date}'"
        ),
        "top_product": (
            "SELECT MATERIAL, SUM(CAST(NETVALUE AS DOUBLE)) AS rev "
            "FROM sapabap1.ztab_salesv1 "
            f"WHERE BILLINGDATE = '{hana_date}' "
            "GROUP BY MATERIAL ORDER BY rev DESC LIMIT 1"
        ),
        "top_branch": (
            "SELECT WERKS, SUM(CAST(NETVALUE AS DOUBLE)) AS rev "
            "FROM sapabap1.ztab_salesv1 "
            f"WHERE BILLINGDATE = '{hana_date}' "
            "GROUP BY WERKS ORDER BY rev DESC LIMIT 1"
        ),
    }

    cursor = hana_conn.cursor()
    try:
        cursor.execute(queries["total_revenue"])
        row = cursor.fetchone()
        digest["total_revenue"] = float(row[0]) if row else 0.0

        cursor.execute(queries["top_product"])
        row = cursor.fetchone()
        digest["top_product"] = {"name": row[0], "revenue": float(row[1])} if row else {}

        cursor.execute(queries["top_branch"])
        row = cursor.fetchone()
        digest["top_branch"] = {"name": row[0], "revenue": float(row[1])} if row else {}
    except Exception as exc:
        logger.error("[Digest] HANA query error: %s", exc)
    finally:
        cursor.close()

    digest["date"] = display_date
    return digest


class DailyDigestService:
    """Build and cache a daily sales snapshot for a tenant."""

    def __init__(self, redis_client, hana_conn_factory=None):
        self._redis = redis_client
        self._hana_factory = hana_conn_factory  # callable() → hdbcli connection

    def get_digest(self, tenant_id: str) -> dict:
        """Return today's digest from cache; fetch fresh if missing."""
        key = _today_key(tenant_id)
        try:
            cached = self._redis.get(key)
            if cached:
                return json.loads(cached)
        except Exception as exc:
            logger.warning("[Digest] Redis read error: %s", exc)

        if not self._hana_factory:
            return {"error": "No SAP HANA connection configured", "date": _today_ist().isoformat()}

        try:
            conn = self._hana_factory()
            digest = _fetch_fresh_digest(conn)
            conn.close()
        except Exception as exc:
            logger.error("[Digest] HANA connection error: %s", exc)
            return {"error": str(exc), "date": _today_ist().isoformat()}

        try:
            self._redis.set(key, json.dumps(digest, ensure_ascii=False), ex=_TTL_SECONDS)
        except Exception as exc:
            logger.warning("[Digest] Redis write error: %s", exc)

        return digest

    def format_digest_text(self, digest: dict) -> str:
        """Human-readable one-paragraph digest for email and chat."""
        if "error" in digest:
            return f"Today's digest unavailable: {digest['error']}"

        rev = digest.get("total_revenue", 0)
        product = digest.get("top_product", {})
        branch = digest.get("top_branch", {})
        d = digest.get("date", "today")

        parts = [f"Sales digest for {d}."]
        if rev:
            parts.append(f"Total revenue: ₹{rev:,.0f}.")
        if product:
            parts.append(f"Top product: {product.get('name')} (₹{product.get('revenue', 0):,.0f}).")
        if branch:
            parts.append(f"Top branch: {branch.get('name')} (₹{branch.get('revenue', 0):,.0f}).")

        return " ".join(parts)