"""Read-only Operator dashboard figures from campaign reports and Amazon economics.""" import datetime as dt import hashlib import math from modules import amazon_operator as op AD_TYPES = ("sp", "sb", "sd") ECONOMICS = "amzn_economics" METRICS = ("spend", "ad_sales", "orders", "total_sales", "organic_sales", "acos", "tacos") SORTS = {"spend", "sales", "orders", "acos", "campaign"} def _period(start, end): try: first, last = (dt.date.fromisoformat(str(value)) for value in (start, end)) except (TypeError, ValueError) as exc: raise ValueError("Use YYYY-MM-DD for the overview dates.") from exc if first > last: raise ValueError("The overview start must be on or before its end.") return first.isoformat(), last.isoformat() def _ratio(spend, sales): return round(100 * spend / sales, 2) if spend is not None and sales is not None and sales > 0 else None def _figures(spend, sales, orders, total): return {"spend": round(spend, 2), "ad_sales": round(sales, 2), "orders": orders, "total_sales": round(total, 2) if total is not None else None, "organic_sales": round(total - sales, 2) if total is not None else None, "acos": _ratio(spend, sales), "tacos": _ratio(spend, total)} def _campaign_source(cur): """Union campaign reports without reading product reports or materializing source rows.""" parts = [] for ad_type in AD_TYPES: table = op._ads_table(ad_type, "campaigns") cols = op._columns(cur, table) if not cols: continue missing = {"date", "campaign_id", "cost", "sales", "orders"} - cols if missing: return None, f"{table} lacks {', '.join(sorted(missing))}. Sync Amazon Ads first." text = lambda name: f'COALESCE(CAST("{name}" AS VARCHAR), \'\')' if name in cols else "''" parts.append( f"SELECT '{ad_type}' AS ad_type, {text('_connection_id')} AS connection_id, " f"{text('profile_id')} AS profile_id, {text('campaign_id')} AS campaign_id, " f"{text('campaign_name')} AS campaign, TRY_CAST(\"date\" AS DATE) AS day, " 'TRY_CAST(cost AS DOUBLE) AS spend, TRY_CAST(sales AS DOUBLE) AS sales, ' f'TRY_CAST(orders AS DOUBLE) AS orders FROM {table}') return (" UNION ALL ".join(parts), None) if parts else (None, op.NOT_INSTALLED) def _empty(start, end, reason, *, no_prior=False, first=None, last=None): return {"start": start, "end": end, "state": "unavailable", "reason": reason, "noPrior": no_prior, "firstDataDate": first, "dataDate": last, "totals": dict.fromkeys(METRICS), "daily": [], "campaigns": {"items": [], "total": 0, "cursor": None}} def _page(cur, source, start, end, cursor, limit, sort, descending): """Page grouped campaigns in SQL, with a stable identity across types, profiles and connections.""" if isinstance(limit, bool) or not isinstance(limit, int) or limit < 1: raise ValueError("The campaign page size must be a positive integer.") if sort not in SORTS: raise ValueError("Choose a campaign sort: spend, sales, orders, acos or campaign.") scope = hashlib.sha256(f"{start}|{end}|{sort}|{descending}".encode()).hexdigest()[:16] offset = 0 if cursor: decoded = op._decode(cursor) if len(decoded) != 2 or decoded[0] != scope or not str(decoded[1]).isdigit(): raise ValueError("The campaign cursor does not match this overview.") offset = int(decoded[1]) grouped = ( f"WITH reports AS ({source}) SELECT ad_type, connection_id, profile_id, campaign_id, " "arg_max(campaign, day) AS campaign, SUM(spend) AS spend, SUM(sales) AS sales, SUM(orders) AS orders, " "CASE WHEN SUM(sales) > 0 THEN SUM(spend) / SUM(sales) * 100 END AS acos " "FROM reports WHERE day BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) GROUP BY 1, 2, 3, 4") total = cur.execute(f"SELECT COUNT(*) FROM ({grouped})", [start, end]).fetchone()[0] direction = "DESC" if descending else "ASC" rows = cur.execute( f"SELECT * FROM ({grouped}) ORDER BY {sort} {direction} NULLS LAST, " "ad_type, connection_id, profile_id, campaign_id LIMIT ? OFFSET ?", [start, end, limit, offset]).fetchall() fields = ("ad_type", "connection_id", "profile_id", "campaign_id", "campaign", "spend", "sales", "orders", "acos") items = [{k: round(v, 2) if k in ("spend", "sales", "acos") and v is not None else v for k, v in zip(fields, row)} for row in rows] next_offset = offset + len(rows) return {"items": items, "total": total, "cursor": op._encode([scope, next_offset]) if next_offset < total else None} def overview(tenant_key, start, end, *, st=None, cursor=None, limit=50, sort="spend", descending=True): """Return daily percentages, sales and spend plus one server-paged row per campaign.""" start, end = _period(start, end) st = op._rt(tenant_key, st) with op._reader(tenant_key, st) as cur: if cur is None: return _empty(start, end, op.NOT_INSTALLED) source, reason = _campaign_source(cur) if source is None: return _empty(start, end, reason) first, last = cur.execute(f"SELECT MIN(day), MAX(day) FROM ({source})").fetchone() if first is None: return _empty(start, end, "No campaign history has arrived from Amazon Ads.") first, last = str(first), str(last) if end < first: return _empty(start, end, f"No complete Ads history exists before {first}.", no_prior=True, first=first, last=last) bad = " OR ".join(f"{k} IS NULL OR NOT isfinite({k})" for k in ("spend", "sales", "orders")) if cur.execute(f"SELECT COUNT(*) FROM ({source}) WHERE day BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) " f"AND ({bad})", [start, end]).fetchone()[0]: return _empty(start, end, "Campaign reports contain missing or invalid metrics.", first=first, last=last) daily_ads = {} result = cur.execute(f"SELECT day, SUM(spend), SUM(sales), SUM(orders) FROM ({source}) " "WHERE day BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) GROUP BY day ORDER BY day", [start, end]) while batch := result.fetchmany(256): daily_ads.update({str(row[0]): row[1:] for row in batch}) economics = {} econ_cols = op._columns(cur, ECONOMICS) reason = f"Ads history starts on {first}. Earlier figures are unavailable." if start < first else None if {"start_date", "sales_net_product_sales_amount"} <= econ_cols: result = cur.execute( 'SELECT TRY_CAST(left(CAST(start_date AS VARCHAR), 10) AS DATE) AS day, ' 'SUM(TRY_CAST(sales_net_product_sales_amount AS DOUBLE)), ' 'COUNT(*) FILTER (WHERE TRY_CAST(sales_net_product_sales_amount AS DOUBLE) IS NULL ' 'OR NOT isfinite(TRY_CAST(sales_net_product_sales_amount AS DOUBLE))) ' f'FROM {ECONOMICS} WHERE left(CAST(start_date AS VARCHAR), 10) BETWEEN ? AND ? ' 'GROUP BY day ORDER BY day', [start, end]) while batch := result.fetchmany(256): economics.update({str(day): value if not invalid else None for day, value, invalid in batch}) else: reason = "Amazon economics sales are unavailable. Sync Amazon economics first." daily = [] day = dt.date.fromisoformat(start) while day.isoformat() <= end: key = day.isoformat() if key < first or key > last: daily.append({"date": key, **dict.fromkeys(METRICS)}) else: spend, sales, orders = daily_ads.get(key, (0.0, 0.0, 0.0)) daily.append({"date": key, **_figures(spend, sales, orders, economics.get(key))}) if key == end: break day += dt.timedelta(days=1) spend, sales, orders = (sum(row[i] for row in daily_ads.values()) for i in range(3)) total = sum(economics.values()) if economics and all(v is not None for v in economics.values()) else None if any(row["total_sales"] is None for row in daily): reason = reason or "Some days have no economics sales or campaign history. Missing figures are unavailable." return {"start": start, "end": end, "state": "partial" if reason else "ready", "reason": reason, "noPrior": start < first, "firstDataDate": first, "dataDate": last, "totals": _figures(spend, sales, orders, total), "daily": daily, "campaigns": _page(cur, source, start, end, cursor, limit, sort, descending)} def validate(tenant_key, start, end, *, st=None, report=None): """Reconcile each figure against separate campaign and economics aggregations, independent of overview's union.""" start, end = _period(start, end) st = op._rt(tenant_key, st) report = overview(tenant_key, start, end, st=st) if report is None else report independent = {"spend": 0.0, "ad_sales": 0.0, "orders": 0.0, "total_sales": None} days, errors = {}, [] with op._reader(tenant_key, st) as cur: if cur is None: return {"ok": False, "detail": op.NOT_INSTALLED, "independent": independent} for ad_type in ("sp", "sb", "sd"): table = op._ads_table(ad_type, "campaigns") cols = op._columns(cur, table) if not cols: continue if not {"date", "cost", "sales", "orders"} <= cols: errors.append(f"{table} has missing metrics.") continue result = cur.execute( 'SELECT TRY_CAST("date" AS DATE), SUM(TRY_CAST(cost AS DOUBLE)), ' 'SUM(TRY_CAST(sales AS DOUBLE)), SUM(TRY_CAST(orders AS DOUBLE)) ' f'FROM {table} WHERE TRY_CAST("date" AS DATE) BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) ' 'GROUP BY 1', [start, end]) while batch := result.fetchmany(256): for day, spend, sales, orders in batch: values = (spend, sales, orders) if any(v is None or not math.isfinite(v) for v in values): errors.append(f"{table} has invalid metrics.") continue rec = days.setdefault(str(day), dict(spend=0.0, ad_sales=0.0, orders=0.0, total_sales=None)) for key, value in zip(("spend", "ad_sales", "orders"), values): rec[key] += value independent[key] += value if {"start_date", "sales_net_product_sales_amount"} <= op._columns(cur, ECONOMICS): result = cur.execute( 'SELECT left(CAST(start_date AS VARCHAR), 10), SUM(TRY_CAST(sales_net_product_sales_amount AS DOUBLE)) ' f'FROM {ECONOMICS} WHERE left(CAST(start_date AS VARCHAR), 10) BETWEEN ? AND ? GROUP BY 1', [start, end]) while batch := result.fetchmany(256): for day, sales in batch: if sales is None or not math.isfinite(sales): errors.append("Economics has invalid sales.") continue days.setdefault(day, dict(spend=0.0, ad_sales=0.0, orders=0.0, total_sales=None))["total_sales"] = sales independent["total_sales"] = (independent["total_sales"] or 0.0) + sales else: errors.append("Economics sales are unavailable.") def expected_figures(row): spend, sales, total = row["spend"], row["ad_sales"], row["total_sales"] return {"spend": round(spend, 2), "ad_sales": round(sales, 2), "orders": row["orders"], "total_sales": round(total, 2) if total is not None else None, "organic_sales": round(total - sales, 2) if total is not None else None, "acos": round(spend * 100 / sales, 2) if sales > 0 else None, "tacos": round(spend * 100 / total, 2) if total is not None and total > 0 else None} expected = expected_figures(independent) def ties(actual, wanted): return all((actual.get(k) is None if v is None else isinstance(actual.get(k), (int, float)) and math.isfinite(actual[k]) and abs(actual[k] - v) <= 0.01) for k, v in wanted.items()) if report.get("start") != start or report.get("end") != end or report.get("state") == "unavailable": errors.append("The requested overview window is unavailable or mismatched.") if not ties(report.get("totals", {}), expected): errors.append("Overview totals do not tie to campaign cost, campaign sales and economics sales.") observed = {row.get("date"): row for row in report.get("daily", [])} for day, row in days.items(): wanted = expected_figures(row) if not ties(observed.get(day, {}), wanted): errors.append(f"Daily figures do not tie on {day}.") return {"ok": not errors, "detail": " ".join(errors) or "Campaign cost, ad sales and economics sales tie.", "app_total": report.get("totals"), "independent": expected}