File size: 25,241 Bytes
f462120
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
"""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}