"""HANA KPI cells — Sales dashboard data."""
import calendar, datetime, json, logging, os
from zoneinfo import ZoneInfo
from hdbcli import dbapi
from dotenv import load_dotenv
load_dotenv()

logger = logging.getLogger("apex.cells")
_IST = ZoneInfo("Asia/Kolkata")

HANA_HOST     = os.getenv("HANA_HOST")
HANA_PORT     = int(os.getenv("HANA_PORT") or 30015)
HANA_USER     = os.getenv("HANA_USER")
HANA_PASSWORD = os.getenv("HANA_PASSWORD")

def _connect():
    return dbapi.connect(address=HANA_HOST, port=HANA_PORT, user=HANA_USER, password=HANA_PASSWORD)

def _amt():
    return "CASE WHEN VBTYP IN ('N','O') THEN NETVALUE * -1 ELSE NETVALUE END * CASE WHEN CURRENCY='USD' THEN KURRF/100 ELSE 1 END"

def _d(d): return d.strftime("%Y%m%d")

def fetch_cells_data(email=None, external_where=""):
    conn = _connect()
    cur  = conn.cursor()
    ew   = external_where
    a    = _amt()

    today     = datetime.datetime.now(tz=_IST).date()
    yesterday = today - datetime.timedelta(days=1)

    # Month helpers
    cm, cy    = today.month, today.year
    cm_start  = datetime.date(cy, cm, 1)
    pm_date   = cm_start - datetime.timedelta(days=1)
    pm, py_   = pm_date.month, pm_date.year
    pm_start  = datetime.date(py_, pm, 1)
    pm_end    = pm_date

    cm_name   = calendar.month_abbr[cm]
    pm_name   = calendar.month_abbr[pm]

    # YTD
    fy_start  = datetime.date(cy, 4, 1) if cm >= 4 else datetime.date(cy-1, 4, 1)
    fy_prev   = datetime.date(fy_start.year-1, 4, 1)
    fy_prev_end = fy_prev + (today - fy_start)

    # QTD
    qm        = (cm-1)//3*3+1
    qtd_start = datetime.date(cy, qm, 1)
    qtd_py_s  = datetime.date(cy-1, qm, 1)
    try:    qtd_py_e = today.replace(year=cy-1)
    except: qtd_py_e = datetime.date(cy-1, 2, 28)

    # Prior year same months
    py_cm_start = datetime.date(cy-1, cm, 1)
    try:    py_cm_end_d = _d(today.replace(year=cy-1))
    except: py_cm_end_d = _d(datetime.date(cy-1, 2, 28))

    py_pm_start = datetime.date(py_-1, pm, 1)
    py_pm_end   = datetime.date(py_-1, pm, calendar.monthrange(py_-1, pm)[1])

    scalar_queries = {
        "Today's Sales":          f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE='{_d(today)}'{ew}",
        "Yesterday's Sales":      f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE='{_d(yesterday)}'{ew}",
        "YTD":                    f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE>='{_d(fy_start)}' AND BILLINGDATE<='{_d(today)}'{ew}",
        "YTD PY":                 f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE>='{_d(fy_prev)}' AND BILLINGDATE<='{_d(fy_prev_end)}'{ew}",
        "QTD":                    f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE>='{_d(qtd_start)}' AND BILLINGDATE<='{_d(today)}'{ew}",
        "QTD PY":                 f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE>='{_d(qtd_py_s)}' AND BILLINGDATE<='{_d(qtd_py_e)}'{ew}",
        f"{cm_name} {cy}":        f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{_d(cm_start)}' AND '{_d(today)}'{ew}",
        f"{cm_name} {cy-1}":      f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{_d(py_cm_start)}' AND '{py_cm_end_d}'{ew}",
        f"{pm_name} {py_}":       f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{_d(pm_start)}' AND '{_d(pm_end)}'{ew}",
        f"{pm_name} {py_-1}":     f"SELECT SUM({a}) FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{_d(py_pm_start)}' AND '{_d(py_pm_end)}'{ew}",
    }

    results = {}
    for label, sql in scalar_queries.items():
        try:
            cur.execute(sql)
            val = cur.fetchone()[0]
            if val is not None:
                cr = float(val) / 1e7
                results[label] = round(cr, 2) if label == "Today's Sales" else int(round(cr))
            else:
                results[label] = 0
        except Exception as e:
            logger.error("[KPI] %s: %s", label, e)
            results[label] = 0

    # Top 5 Brands MTD
    try:
        cur.execute(f"SELECT TOP 5 COALESCE(NULLIF(TRIM(BRANDNAME),''),BRANDCODE) AS BRAND, ROUND(SUM({a})/10000000,2) AS REVENUE_CR FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{_d(cm_start)}' AND '{_d(today)}'{ew} GROUP BY BRANDCODE,BRANDNAME ORDER BY SUM({a}) DESC")
        results["top_brands"] = [{"name":r[0],"value":float(r[1] or 0)} for r in cur.fetchall()]
    except Exception as e:
        logger.error("[top_brands] %s", e); results["top_brands"] = []

    # Top 5 Customers MTD
    try:
        cur.execute(f"SELECT TOP 5 COALESCE(NULLIF(TRIM(PARTNERNAME),''),PARTNERCODE) AS CUSTOMER, ROUND(SUM({a})/10000000,2) AS REVENUE_CR FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{_d(cm_start)}' AND '{_d(today)}'{ew} GROUP BY PARTNERCODE,PARTNERNAME ORDER BY SUM({a}) DESC")
        results["top_customers"] = [{"name":r[0],"value":float(r[1] or 0)} for r in cur.fetchall()]
    except Exception as e:
        logger.error("[top_customers] %s", e); results["top_customers"] = []

    # Region Performance MTD
    try:
        cur.execute(f"SELECT TOP 8 COALESCE(NULLIF(TRIM(STATENAME),''),'Unknown') AS STATE, ROUND(SUM({a})/10000000,2) AS REVENUE_CR FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{_d(cm_start)}' AND '{_d(today)}'{ew} GROUP BY STATENAME ORDER BY SUM({a}) DESC")
        results["region_performance"] = [{"name":r[0],"value":float(r[1] or 0)} for r in cur.fetchall()]
    except Exception as e:
        logger.error("[region_performance] %s", e); results["region_performance"] = []

    # Top 5 Products MTD
    try:
        cur.execute(f"SELECT TOP 5 COALESCE(NULLIF(TRIM(MAT_GRP_NAME),''),MATERIALGROUP) AS PRODUCT, ROUND(SUM({a})/10000000,2) AS REVENUE_CR FROM sapabap1.ztab_salesv1 WHERE BILLINGDATE BETWEEN '{_d(cm_start)}' AND '{_d(today)}'{ew} GROUP BY MATERIALGROUP,MAT_GRP_NAME ORDER BY SUM({a}) DESC")
        results["top_products"] = [{"name":r[0],"value":float(r[1] or 0)} for r in cur.fetchall()]
    except Exception as e:
        logger.error("[top_products] %s", e); results["top_products"] = []

    cur.close(); conn.close()
    return results