"""Read SP Stream hours in the Ads profile's timezone and reconcile daily campaign cost.""" import datetime as dt import math from zoneinfo import ZoneInfo, ZoneInfoNotFoundError from modules import amazon_operator as op SOURCE = "amzn_ads_sp_hourly" DAILY = "amzn_ads_sp_campaigns" METRICS = ("spend", "clicks", "orders", "sales") EMPTY = "Hourly data starts the hour after it is turned on" UNSUPPORTED = "Hourly data is available for Sponsored Products only." def _days(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 hourly dates.") from exc if first > last: raise ValueError("The hourly start must be on or before its end.") while True: yield first.isoformat() if first == last: break first += dt.timedelta(days=1) def _scope(campaign_id, connection_id, profile_id): clauses, args = ['CAST(campaign_id AS VARCHAR) = ?'], [str(campaign_id)] for key, value in (("_connection_id", connection_id), ("profile_id", profile_id)): if value is not None: clauses.append(f'CAST("{key}" AS VARCHAR) = ?') args.append(str(value)) return " AND ".join(clauses), args def _scoped_identity(cur, campaign_id, connection_id, profile_id): if not {"campaign_id", "profile_id", "_connection_id", "date", "cost"} <= op._columns(cur, DAILY): return None, "Scoped daily campaign reports are unavailable. Sync Amazon Ads first." where, args = _scope(campaign_id, connection_id, profile_id) count = cur.execute(f"SELECT COUNT(*) FROM (SELECT DISTINCT _connection_id, profile_id FROM {DAILY} " f"WHERE {where})", args).fetchone()[0] if count != 1: return None, "Choose one campaign connection and Ads profile with daily history." connection, profile = cur.execute(f"SELECT DISTINCT _connection_id, profile_id FROM {DAILY} WHERE {where}", args).fetchone() if not connection or not profile: return None, "The campaign report has no verified connection and Ads profile." return (str(connection), str(profile)), None def _occurrences(day, hour, timezone): """Count real local hours, including the spring gap and autumn repeat.""" wall = dt.datetime.combine(dt.date.fromisoformat(day), dt.time(hour)) instants = set() for fold in (0, 1): local = wall.replace(tzinfo=timezone, fold=fold) utc = local.astimezone(dt.timezone.utc) if utc.astimezone(timezone).replace(tzinfo=None) == wall: instants.add(utc) return len(instants) def _base(campaign_id, days, ad_type, connection_id, profile_id, reason, state="unavailable"): return {"campaign_id": str(campaign_id), "ad_type": ad_type, "connection_id": connection_id, "profile_id": profile_id, "start": days[0], "end": days[-1], "timezone": None, "currency": None, "attribution_window": "14d", "state": state, "reason": reason, "source_table": SOURCE, "rows": [], "daily_rows": [{"date": day, "hour": hour, **dict.fromkeys((*METRICS, "acos")), "observed": False, "occurrences": 0, "expected_occurrences": 1, "metric_occurrences": dict.fromkeys(METRICS, 0), "reason": reason} for day in days for hour in range(24)], "totals": dict.fromkeys((*METRICS, "acos"))} def _publish_hours(report): """Publish one aggregate per hour, withholding each metric until every expected occurrence reports it.""" buckets = [[] for _ in range(24)] for row in report["daily_rows"]: buckets[row["hour"]].append(row) report["rows"] = [] for hour, slots in enumerate(buckets): active = [slot for slot in slots if slot["expected_occurrences"]] row = {"hour": hour, "observed": any(slot["observed"] for slot in active), "occurrences": sum(slot["occurrences"] for slot in active), "expected_occurrences": sum(slot["expected_occurrences"] for slot in active), "metric_occurrences": {metric: sum(slot["metric_occurrences"][metric] for slot in active) for metric in METRICS}, "reason": "; ".join(dict.fromkeys(slot["reason"] for slot in active if slot["reason"])) or None} if report["start"] == report["end"]: row["date"] = report["start"] for metric in METRICS: complete = active and all(slot[metric] is not None and math.isfinite(slot[metric]) and slot["metric_occurrences"][metric] >= slot["expected_occurrences"] for slot in active) value = sum(slot[metric] for slot in active) if complete else None row[metric] = value if value is not None and math.isfinite(value) else None row["acos"] = _acos(row["spend"], row["sales"]) if not active: row["reason"] = "This hour does not occur in profile time" report["rows"].append(row) def _stream(cur, report): required = {"campaign_id", "_connection_id", "profile_id", "currency", "timezone", "date", "hour", "time_window_start", "attribution_window", *METRICS} if not required <= op._columns(cur, SOURCE): return "empty", EMPTY where, args = _scope(report["campaign_id"], report["connection_id"], report["profile_id"]) metadata = cur.execute(f"SELECT DISTINCT timezone, currency FROM {SOURCE} WHERE {where}", args) identities = [] while batch := metadata.fetchmany(256): identities.extend(batch) if not identities: return "empty", EMPTY if len(identities) != 1 or not all(identities[0]): return "unavailable", "The hourly source has inconsistent profile timezone or currency." timezone, currency = identities[0] try: ZoneInfo(str(timezone)) except (ZoneInfoNotFoundError, ValueError): return "unavailable", "The Ads profile timezone is unavailable." report.update(timezone=str(timezone), currency=str(currency)) if "currency" in op._columns(cur, DAILY): mismatched = cur.execute(f"SELECT COUNT(*) FROM {DAILY} WHERE {where} " 'AND TRY_CAST("date" AS DATE) BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) ' 'AND currency IS DISTINCT FROM ?', [*args, report["start"], report["end"], currency]).fetchone()[0] if mismatched: return "unavailable", "Hourly and daily campaign currencies do not match." # Bucket aware timestamps in profile time, never the server's or UTC's calendar. source = (f"SELECT *, timezone(?, TRY_CAST(time_window_start AS TIMESTAMPTZ)) AS local_time " f"FROM {SOURCE} WHERE {where}") valid = ("regexp_matches(time_window_start, '(Z|[+-][0-9]{2}:[0-9]{2})$') AND local_time IS NOT NULL " 'AND CAST(local_time AS DATE) = TRY_CAST("date" AS DATE) ' 'AND hour(local_time) = TRY_CAST("hour" AS INTEGER) AND minute(local_time) = 0 ' 'AND second(local_time) = 0') period = 'TRY_CAST("date" AS DATE) BETWEEN CAST(? AS DATE) AND CAST(? AS DATE)' params = [timezone, *args, report["start"], report["end"]] invalid = cur.execute(f"WITH source AS ({source}) SELECT COUNT(*) FROM source WHERE ({period} " "OR CAST(local_time AS DATE) BETWEEN CAST(? AS DATE) AND CAST(? AS DATE)) " f"AND NOT COALESCE(({valid}), FALSE)", [*params, report["start"], report["end"]]).fetchone()[0] if invalid: return "unavailable", "Hourly timestamps do not match the Ads profile's local date and hour." incompatible = cur.execute(f"WITH source AS ({source}) SELECT COUNT(*) FROM source WHERE {period} " "AND attribution_window IS DISTINCT FROM '14d'", params).fetchone()[0] if incompatible: return "unavailable", "Hourly sales and orders need a compatible 14d attribution window." expressions = [] for metric in METRICS: value = f'TRY_CAST("{metric}" AS DOUBLE)' expressions.append(f'CASE WHEN COUNT(*) FILTER (WHERE "{metric}" IS NOT NULL ' f'AND ({value} IS NULL OR NOT isfinite({value}))) > 0 THEN NULL ' f'ELSE SUM({value}) END AS "{metric}"') result = cur.execute(f"WITH source AS ({source}) SELECT CAST(local_time AS DATE), hour(local_time), " + ", ".join(expressions) + ", COUNT(DISTINCT TRY_CAST(time_window_start AS TIMESTAMPTZ)), " + ", ".join(f'COUNT(DISTINCT CASE WHEN TRY_CAST("{metric}" AS DOUBLE) IS NOT NULL ' f'AND isfinite(TRY_CAST("{metric}" AS DOUBLE)) ' 'THEN TRY_CAST(time_window_start AS TIMESTAMPTZ) END)' for metric in METRICS) + " FROM source " "WHERE CAST(local_time AS DATE) BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) " "GROUP BY 1, 2 ORDER BY 1, 2", params) observed = {} while batch := result.fetchmany(256): for day, hour, *values in batch: observed[(str(day), hour)] = values tz = ZoneInfo(str(timezone)) for row in report["daily_rows"]: values = observed.get((row["date"], row["hour"])) expected = _occurrences(row["date"], row["hour"], tz) row["expected_occurrences"] = expected if not values: row["reason"] = "No hourly coverage" if expected else "This hour does not occur in profile time" continue row.update(zip(METRICS, values[:4])) row.update(observed=True, occurrences=values[4], metric_occurrences=dict(zip(METRICS, values[5:])), reason=None) missing = [metric for metric in METRICS if row[metric] is None or not math.isfinite(row[metric])] if missing: for metric in missing: row[metric] = None row["reason"] = "Unreported or invalid " + ", ".join(missing) if row["occurrences"] < expected: row["reason"] = "Incomplete coverage of the repeated profile hour" elif any(count < expected for count in row["metric_occurrences"].values()): row["reason"] = "Unreported or partial " + ", ".join( metric for metric, count in row["metric_occurrences"].items() if count < expected) row["acos"] = _acos(row["spend"], row["sales"]) active = [row for row in report["daily_rows"] if row["expected_occurrences"]] for metric in METRICS: if active and all(row[metric] is not None and row.get("metric_occurrences", {}).get(metric, 0) >= row["expected_occurrences"] for row in active): total = sum(row[metric] for row in active) report["totals"][metric] = total if math.isfinite(total) else None report["totals"]["acos"] = _acos(report["totals"]["spend"], report["totals"]["sales"]) return ("partial", "Some hourly metrics or hours are unavailable.") if any( row["reason"] and row["expected_occurrences"] for row in report["daily_rows"]) else ("ready", None) def _acos(spend, sales): value = spend / sales * 100 if spend is not None and sales is not None and sales > 0 else None return round(value, 2) if value is not None and math.isfinite(value) else None def by_hour(tenant_key, campaign_id, start, end, *, st=None, ad_type="sp", connection_id=None, profile_id=None): """Return 24 hour-of-day aggregates and every daily slot for the requested profile-local window.""" days = list(_days(start, end)) if not str(campaign_id or "").strip(): raise ValueError("Choose a campaign for the hourly report.") report = _base(campaign_id, days, ad_type, connection_id, profile_id, EMPTY) if ad_type != "sp": report.update(state="unsupported", reason=UNSUPPORTED) _publish_hours(report) report["validation"] = {"ok": False, "detail": UNSUPPORTED, "days": [{"date": day, "ok": False, "hourly_spend": None, "daily_spend": None, "difference_pct": None, "missing_hours": 24, "reasons": [UNSUPPORTED]} for day in days]} return report st = op._rt(tenant_key, st) with op._reader(tenant_key, st) as cur: identity, reason = _scoped_identity(cur, campaign_id, connection_id, profile_id) if cur else (None, op.NOT_INSTALLED) if identity: report.update(connection_id=identity[0], profile_id=identity[1]) state, reason = _stream(cur, report) report.update(state=state, reason=reason) else: report["reason"] = reason if report["state"] == "unavailable": for row in report["daily_rows"]: row["reason"] = report["reason"] _publish_hours(report) report["validation"] = validate(tenant_key, campaign_id, start, end, st=st, report=report, ad_type=ad_type, connection_id=report["connection_id"], profile_id=report["profile_id"]) if report["state"] == "ready" and not report["validation"]["ok"]: report.update(state="partial", reason=report["validation"]["detail"]) return report def validate(tenant_key, campaign_id, start, end, *, st=None, report=None, ad_type="sp", connection_id=None, profile_id=None): """Check published hour aggregates and daily slots against source metrics, coverage and daily campaign cost.""" days = list(_days(start, end)) st = op._rt(tenant_key, st) if report is None: return by_hour(tenant_key, campaign_id, start, end, st=st, ad_type=ad_type, connection_id=connection_id, profile_id=profile_id)["validation"] costs, independent, coverage = {}, {}, {} timezone = None source_identity = None reason = None with op._reader(tenant_key, st) as cur: identity, reason = _scoped_identity(cur, campaign_id, connection_id, profile_id) if cur else (None, op.NOT_INSTALLED) if identity and ad_type == "sp": where, args = _scope(campaign_id, *identity) result = cur.execute(f'SELECT TRY_CAST("date" AS DATE), SUM(TRY_CAST(cost AS DOUBLE)), ' 'COUNT(*) FILTER (WHERE TRY_CAST(cost AS DOUBLE) IS NULL ' 'OR NOT isfinite(TRY_CAST(cost AS DOUBLE))) ' f'FROM {DAILY} WHERE {where} AND TRY_CAST("date" AS DATE) ' 'BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) GROUP BY 1', [*args, start, end]) while batch := result.fetchmany(256): costs.update({str(day): value if not bad else None for day, value, bad in batch}) if {"date", "hour", "_connection_id", "profile_id", "campaign_id", "time_window_start", "timezone", "currency", *METRICS} <= op._columns(cur, SOURCE): metadata = cur.execute(f"SELECT DISTINCT timezone, currency FROM {SOURCE} WHERE {where}", args) identities = [] while batch := metadata.fetchmany(256): identities.extend(batch) if len(identities) == 1 and all(identities[0]): source_identity = tuple(map(str, identities[0])) try: timezone = ZoneInfo(str(identities[0][0])) except (ZoneInfoNotFoundError, ValueError): pass metrics = [] for metric in METRICS: value = f'TRY_CAST("{metric}" AS DOUBLE)' metrics.append(f'CASE WHEN COUNT(*) FILTER (WHERE "{metric}" IS NOT NULL ' f'AND ({value} IS NULL OR NOT isfinite({value}))) > 0 THEN NULL ELSE SUM({value}) END') result = cur.execute(f'SELECT TRY_CAST("date" AS DATE), TRY_CAST("hour" AS INTEGER), ' + ', '.join(metrics) + ', COUNT(DISTINCT TRY_CAST(time_window_start AS TIMESTAMPTZ)), ' + ', '.join(f'COUNT(DISTINCT CASE WHEN TRY_CAST("{metric}" AS DOUBLE) IS NOT NULL ' f'AND isfinite(TRY_CAST("{metric}" AS DOUBLE)) ' 'THEN TRY_CAST(time_window_start AS TIMESTAMPTZ) END)' for metric in METRICS) + f' FROM {SOURCE} WHERE {where} ' 'AND TRY_CAST("date" AS DATE) BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) ' 'GROUP BY 1, 2', [*args, start, end]) while batch := result.fetchmany(256): for day, hour, *values in batch: independent[(str(day), hour)] = values[:4] coverage[(str(day), hour)] = values[4:] else: reason = reason or UNSUPPORTED scope_matches = (str(report.get("campaign_id")) == str(campaign_id) and report.get("ad_type") == ad_type == "sp" and report.get("start") == days[0] and report.get("end") == days[-1] and source_identity == (report.get("timezone"), report.get("currency")) and report.get("attribution_window") == "14d" and report.get("source_table") == SOURCE and identity == (report.get("connection_id"), report.get("profile_id"))) checks = [] expected_counts = {(day, hour): _occurrences(day, hour, timezone) if timezone else 1 for day in days for hour in range(24)} for day in days: rows = [row for row in report.get("daily_rows", []) if row.get("date") == day] reasons = [reason] if reason else [] if not scope_matches: reasons.append("The hourly report does not match the requested campaign scope.") if report.get("state") in ("unavailable", "unsupported"): reasons.append(report.get("reason") or "Hourly data unavailable") if len(rows) != 24 or {row.get("hour") for row in rows} != set(range(24)) \ or len(report.get("daily_rows", [])) != len(days) * 24: reasons.append("The report needs all 24 local-hour slots.") relevant = [row for row in rows if expected_counts.get((day, row.get("hour")), 1)] missing = [row for row in relevant if coverage.get((day, row.get("hour")), [0] * 5)[0] < expected_counts.get((day, row.get("hour")), 1)] if missing: reasons.append(f"Missing coverage for {len(missing)} local hours.") missing_metrics = [metric for metric in METRICS if any(row.get(metric) is None for row in relevant)] if missing_metrics: reasons.append("Unreported metrics: " + ", ".join(missing_metrics) + ".") incomplete = [metric for index, metric in enumerate(METRICS) if any( coverage.get((day, row.get("hour")), [0] * 5)[index + 1] < expected_counts.get((day, row.get("hour")), 1) for row in relevant)] if incomplete: reasons.append("Incomplete metric coverage: " + ", ".join(incomplete) + ".") for row in rows: counts = coverage.get((day, row.get("hour")), [0] * 5) if (row.get("expected_occurrences") != expected_counts.get((day, row.get("hour"))) or row.get("occurrences") != counts[0] or row.get("observed") != bool(counts[0]) or row.get("metric_occurrences") != dict(zip(METRICS, counts[1:]))): reasons.append(f'Hour {row["hour"]}: coverage does not tie to reported source occurrences.') values = independent.get((day, row.get("hour")), [None] * len(METRICS)) expected = {metric: value if value is not None and math.isfinite(value) else None for metric, value in zip(METRICS, values)} expected["acos"] = _acos(expected["spend"], expected["sales"]) if any((row.get(metric) is not None if value is None else not isinstance(row.get(metric), (int, float)) or not math.isfinite(row[metric]) or not math.isclose(row[metric], value, rel_tol=1e-9, abs_tol=1e-9)) for metric, value in expected.items()): reasons.append(f'Hour {row["hour"]}: figures do not tie to reported source metrics.') spend = [row.get("spend") for row in rows if row.get("spend") is not None] observed = sum(spend) if spend and all(isinstance(value, (int, float)) and math.isfinite(value) for value in spend) else None daily = costs.get(day) if daily is None or not math.isfinite(daily): reasons.append("Daily campaign cost unavailable.") discrepancy = abs(observed - daily) if observed is not None and daily is not None and math.isfinite(daily) else None pct = discrepancy * 100 / abs(daily) if discrepancy is not None and daily != 0 else None if discrepancy is not None and discrepancy > abs(daily) * .02 + 1e-9: reasons.append("Hourly spend differs from daily campaign cost by more than 2%. Amazon may restate reports.") if observed is None: reasons.append("Hourly spend unavailable.") checks.append({"date": day, "ok": not reasons, "hourly_spend": observed, "daily_spend": daily, "difference_pct": pct, "missing_hours": len(missing), "reasons": reasons}) published = report.get("rows", []) aggregate_errors = [] if len(published) != 24 or [row.get("hour") for row in published] != list(range(24)): aggregate_errors.append("The published report needs exactly 24 hour-of-day aggregates.") for row in published: hour = row.get("hour") slots = [(day, hour) for day in days if expected_counts.get((day, hour), 0)] expected = {} for index, metric in enumerate(METRICS): values = [independent.get(slot, [None] * 4)[index] for slot in slots] complete = slots and all(value is not None and math.isfinite(value) for value in values) and all( coverage.get(slot, [0] * 5)[index + 1] >= expected_counts[slot] for slot in slots) value = sum(values) if complete else None expected[metric] = value if value is not None and math.isfinite(value) else None expected["acos"] = _acos(expected["spend"], expected["sales"]) if any((row.get(metric) is not None if value is None else not isinstance(row.get(metric), (int, float)) or not math.isfinite(row[metric]) or not math.isclose(row[metric], value, rel_tol=1e-9, abs_tol=1e-9)) for metric, value in expected.items()): aggregate_errors.append(f"Hour {hour}: published figures do not tie to independently aggregated source metrics.") counts = [sum(coverage.get(slot, [0] * 5)[index] for slot in slots) for index in range(5)] if (row.get("expected_occurrences") != sum(expected_counts[slot] for slot in slots) or row.get("occurrences") != counts[0] or row.get("observed") != bool(counts[0]) or row.get("metric_occurrences") != dict(zip(METRICS, counts[1:])) or (row.get("date") != days[0] if len(days) == 1 else row.get("date") is not None)): aggregate_errors.append(f"Hour {hour}: published scope or coverage does not match the requested window.") if aggregate_errors: checks[0]["ok"] = False checks[0]["reasons"].extend(aggregate_errors) totals = {} active = [slot for slot, count in expected_counts.items() if count] for index, metric in enumerate(METRICS): values = [independent.get(slot, [None] * 4)[index] for slot in active] complete = active and all(coverage.get(slot, [0] * 5)[index + 1] >= expected_counts[slot] for slot in active) and all(value is not None and math.isfinite(value) for value in values) total = sum(values) if complete else None totals[metric] = total if total is not None and math.isfinite(total) else None totals["acos"] = _acos(totals["spend"], totals["sales"]) actual = report.get("totals", {}) if any((actual.get(metric) is not None if value is None else not isinstance(actual.get(metric), (int, float)) or not math.isfinite(actual[metric]) or not math.isclose(actual[metric], value, rel_tol=1e-9, abs_tol=1e-9)) for metric, value in totals.items()): checks[0]["ok"] = False checks[0]["reasons"].append("Hourly totals do not tie to independently aggregated source metrics.") errors = [f'{check["date"]}: {" ".join(check["reasons"])}' for check in checks if not check["ok"]] return {"ok": not errors, "detail": " ".join(errors) or "Each profile-local day ties to daily campaign cost within 2%.", "days": checks}