"""Read-only Amazon account figures from mirror aggregates and period-matched receipt costs.""" from __future__ import annotations import calendar import datetime as dt import math from decimal import Decimal, InvalidOperation, ROUND_HALF_UP from modules import amazon_profit as profit ECONOMICS = profit.ECONOMICS ADS_TABLES = tuple(f"amzn_ads_{kind}_advertised_products" for kind in ("sp", "sb", "sd")) ACCOUNT_FIELDS = frozenset(("sku", "asin", "period", "units_sold", "net_sales", "fees", "sponsored_ads", "net_proceeds", "cogs_total", "net_profit", "cost_source")) WHAT_IF_FIELDS = ACCOUNT_FIELDS | {"cogs_per_unit", "margin", "odoo_product", "units_per_sku"} ORDERED = "sales_ordered_product_sales_amount" REFUNDS = "sales_refunded_product_sales_amount" def _cents(value): if value is None or value == "": return None try: number = Decimal(str(value)) return int((number * 100).quantize(Decimal("1"), rounding=ROUND_HALF_UP)) if number.is_finite() else None except (InvalidOperation, ValueError, TypeError): return None def _metric(cents, sources, *, reason=None, no_prior=False, validated=True): return {"value": None if cents is None else cents / 100, "validated": validated and cents is not None, "reason": reason, "noPrior": bool(no_prior), "sourceTables": list(sources)} def _bounds(start, end, key): return {"key": key, "start": start.isoformat(), "end": end.isoformat(), "days": max(0, (end - start).days + 1)} def _year_before(day): return day.replace(year=day.year - 1, day=min(day.day, calendar.monthrange(day.year - 1, day.month)[1])) def _comparisons(bounds): start, end = (dt.date.fromisoformat(bounds[k]) for k in ("start", "end")) prior_end = start - dt.timedelta(days=1) return {"prior": _bounds(start - dt.timedelta(days=bounds["days"]), prior_end, "prior"), "yearAgo": _bounds(_year_before(start), _year_before(end), "yearAgo")} def stored_skus(runtime, *, fields=ACCOUNT_FIELDS): """Stream only this tenant's SKU table through the store's paged object reader.""" import core.user_tables as ut backend = runtime._document_store() if callable(getattr(runtime, "_document_store", None)) else runtime reader = getattr(backend, "iter_json_items", None) if not callable(reader): raise profit.SourceUnavailable("Amazon SKU cost lookup requires the paged store reader.") name = runtime.store_key(ut.STORE_KEY) if callable(getattr(runtime, "store_key", None)) else ut.STORE_KEY result = {} for batch in reader(name, (profit.TABLE_KEY, "rows"), batch_size=512): for _rid, row in batch: sku = str(row.get("sku") or "").strip() if not sku: continue if sku in result: raise profit.SourceUnavailable("Amazon SKU cost lookup has duplicate seller SKUs. Refresh the table.") result[sku] = {key: row.get(key) for key in fields} return result def _model_number(value, name, *, fraction=False, whole=False): try: number = Decimal(str(value)) except (InvalidOperation, ValueError, TypeError): raise ValueError(f"{name} must be a finite nonnegative number.") from None if not number.is_finite() or number < 0 or not math.isfinite(float(number)): raise ValueError(f"{name} must be a finite nonnegative number.") if fraction and number > 1: raise ValueError("TACoS must be a fraction from 0 to 1. For 10%, use 0.10.") if whole and number != number.to_integral_value(): raise ValueError("Monthly units must be a whole nonnegative number.") return number def what_if(sku, price=None, landed_cost=None, tacos=None, units=None, *, con, stored, settings, period=None, today=None): """Model a source-backed SKU with observed signed fees and no stored-state changes.""" today = today or profit._today() configured = profit.period_bounds(settings.get("period"), today) recorded = settings.get("lastPeriod") snapshot_missing = not isinstance(recorded, dict) or not all(recorded.get(key) for key in ("key", "start", "end", "label")) saved = configured if snapshot_missing else {key: recorded[key] for key in ("key", "start", "end", "label")} selected = profit.period_bounds(period or configured["key"], today) supplied = {name: _model_number(value, name, fraction=name == "tacos", whole=name == "units") for name, value in (("price", price), ("landed_cost", landed_cost), ("tacos", tacos), ("units", units)) if value is not None} sku = str(sku or "").strip() out = {"sku": sku, "label": "Model", "available": False, "baselinePeriod": saved, "selectedPeriod": selected} def unavailable(reason): return {**out, "reason": reason} row = stored.get(sku) if not row: return unavailable("This SKU is not in Command Center. Refresh the table first.") if not profit.links({"baseline": row}).get(sku, {}).get("code"): return unavailable("Link this SKU to an Odoo product first.") if snapshot_missing and configured["key"] in profit.PERIOD_DAYS: return unavailable("Receipt cost snapshot dates unavailable. Refresh Command Center for this period.") if (saved["start"], saved["end"]) != (selected["start"], selected["end"]): return unavailable("Receipt cost unavailable for selected window. Refresh Command Center for this period.") problem = _ready(con, ECONOMICS) if problem: return unavailable(problem) cols = profit._require(con, ECONOMICS, profit._ECONOMICS_REQUIRED) ads_cols = profit.ads_columns(cols) if not ads_cols: return unavailable("Economics ad spend totals unavailable. Resync Amazon economics.") required = ("sales_net_units_sold", "sales_net_product_sales_amount", "fees_total_amount") invalid = con.execute(f'SELECT count(*) FROM {ECONOMICS} WHERE msku = ? AND ' 'left(CAST(start_date AS VARCHAR),10) BETWEEN ? AND ? AND (' + " OR ".join(f'"{c}" IS NULL OR NOT isfinite("{c}")' for c in required) + "".join(f' OR ("{c}" IS NOT NULL AND NOT isfinite("{c}"))' for c in ads_cols) + " OR (" + " AND ".join(f'"{c}" IS NULL' for c in ads_cols) + ")" + ")", [sku, selected["start"], selected["end"]]).fetchone()[0] if invalid: return unavailable("Economics amounts unavailable. Resync Amazon economics.") item = next((item for item in profit.economics_by_sku(con, selected["start"], selected["end"]) if item["sku"] == sku), None) if item is None: return unavailable("No economics source rows for this SKU and period.") currency = profit.economics_currency(con) if not isinstance(currency, str) or len(currency) != 3 or not currency.isalpha(): return unavailable("Source currency unavailable. Resync Amazon economics.") cost, reason = _cost(item, row, selected, settings, today) if reason: return unavailable(reason) try: sold = _model_number(item["units"], "Source units", whole=True) sales = _model_number(item["net_sales"], "Net sales") spend = _model_number(item["ads"], "Ad spend") displayed_cost = _model_number(row.get("cogs_per_unit"), "Stored unit cost") total_cost = _model_number(row.get("cogs_total"), "Stored total cost") fees = Decimal(str(item["fees"])) stored_margin = Decimal(str(row.get("margin"))) if not sold or not sales or not fees.is_finite() or not stored_margin.is_finite() or spend > sales: return unavailable("Current unit economics cannot be represented. Positive net sales and units with TACoS at most 100% are required.") unit_cost = total_cost / sold if _cents(unit_cost) != _cents(displayed_cost) or cost != _cents(total_cost): return unavailable("Stored unit cost does not match receipt COGS. Refresh Command Center.") actual = profit.compute_row(item, float(unit_cost), 1) if (_cents(actual["net_profit"]) != _cents(row.get("net_profit")) or _cents(Decimal(str(actual["margin"])) * 100) != _cents(stored_margin)): return unavailable("Current margin does not reconcile. Refresh Command Center.") except (ValueError, InvalidOperation, TypeError, OverflowError): return unavailable("Current unit economics unavailable. Refresh Command Center.") baseline = {"price": sales / sold, "amazon_fees_per_unit": fees / sold, "landed_cost": unit_cost, "tacos": spend / sales} modeled_price = supplied.get("price", baseline["price"]) modeled_cost = supplied.get("landed_cost", baseline["landed_cost"]) modeled_tacos = supplied.get("tacos", baseline["tacos"]) monthly_units = supplied.get("units") ads = modeled_price * modeled_tacos fee_per_unit = baseline["amazon_fees_per_unit"] unit_profit = modeled_price - fee_per_unit - ads - modeled_cost result = {"price": modeled_price, "amazon_fees_per_unit": fee_per_unit, "ad_cost_per_unit": ads, "landed_cost": modeled_cost, "tacos": modeled_tacos, "profit_per_unit": unit_profit, "margin": unit_profit / modeled_price if modeled_price else None, "monthly_profit": unit_profit * monthly_units if monthly_units is not None else None, "units": monthly_units} if any(value is not None and not math.isfinite(float(value)) for value in result.values()): raise ValueError("Model inputs produce amounts outside the supported range.") return {**out, "available": True, "reason": None, "currency": currency, "basis": "Net product sales after source refunds and promotions. Observed Amazon fees stay fixed per unit.", "tacosScale": "Fraction from 0 to 1. For 10%, use 0.10.", "monthlyUnitsBasis": "Supplied assumption, separate from units sold in the baseline period.", "baseline": {**{key: float(value) for key, value in baseline.items()}, "units_sold": int(sold), "net_sales": float(sales), "net_profit": actual["net_profit"], "margin": actual["margin"], "stored_margin_percent": float(stored_margin)}, **{key: None if value is None else float(value) for key, value in result.items()}} def _ready(con, table): """Physical tables and completed sync markers are separate from a window's row count.""" from harness import amazon_store if not profit._columns(con, table): return f"Missing source: {table}" if not {"entity", "phase", "cursor_wd", "updated_at", "rows"} <= profit._columns(con, "_sync_state"): return f"Source readiness unavailable: {table}" state = amazon_store._read_state(con, table) if not state or state["state"] not in ("done", "live"): return f"Source still loading: {table}" return None def _cost(item, row, bounds, settings, today): """Use T31's FIFO snapshot only for its exact window and unchanged economics inputs.""" reason = "Receipt cost unavailable for selected window. Refresh Command Center for this period." if not row or settings.get("refusal"): return None, reason saved = profit.period_bounds(settings.get("period"), today) if (saved["start"], saved["end"]) != (bounds["start"], bounds["end"]) or row.get("period") != saved["label"]: return None, reason expected = {"net_sales": item["net_sales"], "fees": item["fees"], "sponsored_ads": item["ads"], "net_proceeds": item["net_sales"] - item["fees"] - item["ads"]} if any(_cents(value) is None for value in expected.values()): return None, "Economics amounts unavailable" if any(_cents(row.get(key)) != _cents(value) for key, value in expected.items()): return None, "Receipt cost snapshot no longer matches the mirror. Refresh Command Center." try: units_match = Decimal(str(row.get("units_sold"))) == Decimal(str(item["units"])) except (InvalidOperation, ValueError, TypeError): units_match = False cost = _cents(row.get("cogs_total")) source = str(row.get("cost_source") or "") # R43: a typed Amazon COGS stands only where the refresh found no Odoo receipt. valid_source = source in ("No units sold", profit.TYPED_COST_SOURCE) or all( part in ("Landed cost", "Purchase price (no landed costs)") for part in source.split(", ")) if not units_match or cost is None or not valid_source: return None, source or reason if _cents(row.get("net_profit")) != _cents(expected["net_proceeds"]) - cost: return None, "Receipt cost snapshot profit does not reconcile. Refresh Command Center." return cost, None def typed_cost_line(count): """How many SKUs in a window use a typed Amazon COGS, in plain words; None when none do.""" if not count: return None return f"{count} SKU uses a typed cost." if count == 1 else f"{count} SKUs use a typed cost." def _ads_coverage(con, bounds): """Read existing per-connection coverage without stamping connection or continuation state.""" from harness import amazon_ads_store as ads from harness import amazon_store as store sources = ["_sync_state", *ADS_TABLES] cols = profit._columns(con, "_sync_state") if not {"entity", "phase", "cursor_wd"} <= cols: return "Connect Amazon Ads", sources markers = {key: (phase, detail) for key, phase, detail in con.execute( "SELECT entity, phase, cursor_wd FROM _sync_state WHERE starts_with(entity, ?)", [store.entity_key("ads_")]).fetchall()} # Continuation keys start with sync_mark_/sync_floor_, outside the ads_ prefix. for key, phase, detail in con.execute( "SELECT entity, phase, cursor_wd FROM _sync_state WHERE starts_with(entity, ?) " "OR starts_with(entity, ?)", [store.entity_key(store.WATERMARK_PREFIX + "ads_"), store.entity_key(store.BACKFILL_FLOOR_PREFIX + "ads_")]).fetchall(): markers[key] = phase, detail prefix = ads.CONNECT_DAY_KEY + "::connection::" connections = {key[len(prefix):]: detail for key, (phase, detail) in markers.items() if key.startswith(prefix) and phase == "done"} if not connections and markers.get(ads.CONNECT_DAY_KEY, (None, None))[0] == "done": connections = {None: markers[ads.CONNECT_DAY_KEY][1]} if not connections: have = any(profit._columns(con, table) for table in ADS_TABLES) return ("Amazon Ads connect day unavailable" if have else "Connect Amazon Ads"), sources for table in ADS_TABLES: have = profit._columns(con, table) if not {"date", "sales"} <= have: return f"Missing source: {table}.sales", sources if "_connection_id" in have: ids = {row[0] or None for row in con.execute( f'SELECT DISTINCT _connection_id FROM "{table}"').fetchall()} if not ids <= connections.keys(): return "Amazon Ads connection coverage unavailable", sources elif any(cid is not None for cid in connections): return "Amazon Ads connection attribution unavailable", sources for cid, day in connections.items(): try: connected = dt.date.fromisoformat(str(day)[:10]).isoformat() except ValueError: return "Amazon Ads connect day unavailable", sources if bounds["start"] < connected: return "Amazon Ads history starts at the connect day", sources mark = table[len(store.PREFIX):] phase = markers.get(ads._state_key(store.entity_key(mark), cid), (None, None))[0] floor = markers.get(ads._state_key(store.entity_key(store.BACKFILL_FLOOR_PREFIX + mark), cid), (None, None))[1] last = markers.get(ads._state_key(store.entity_key(store.WATERMARK_PREFIX + mark), cid), (None, None))[1] try: floor = dt.date.fromisoformat(str(floor)[:10]).isoformat() last = dt.date.fromisoformat(str(last)[:10]).isoformat() except ValueError: return f"Amazon Ads coverage unavailable: {table}", sources if phase != "done" or floor > bounds["start"] or last < bounds["end"]: return f"Amazon Ads coverage incomplete: {table}", sources return None, sources def _attribution(con, bounds, daily): reason, sources = _ads_coverage(con, bounds) ads_by_day = {} if reason is None: union = " UNION ALL ".join(f'SELECT left(CAST(date AS VARCHAR),10) AS day, sales FROM "{t}"' for t in ADS_TABLES) rows = con.execute(f"SELECT day, SUM(sales), count(*), count(sales) FROM ({union}) " "WHERE day BETWEEN ? AND ? GROUP BY day ORDER BY day", [bounds["start"], bounds["end"]]).fetchall() if not rows: reason = "No Amazon Ads source rows for window" elif any(n != present or _cents(sales) is None for _day, sales, n, present in rows): reason = "Amazon Ads sales amounts unavailable" else: ads_by_day = {day: _cents(sales) for day, sales, _n, _present in rows} days = set(daily) | set(ads_by_day) series = [] for day in sorted(days): sales = daily.get(day) ad_sales = ads_by_day.get(day, 0) if reason is None else None missing = reason or ("No economics source rows for day" if sales is None else None) series.append({"day": day, "netSales": _metric(sales, [ECONOMICS], no_prior=sales is None, reason="No economics source rows for day" if sales is None else None), "adSales": _metric(ad_sales, sources, reason=reason, no_prior=reason is not None), "organicSales": _metric(None if missing else sales - ad_sales, [ECONOMICS, *sources], reason=missing, no_prior=missing is not None)}) total = sum(ads_by_day.values()) if reason is None else None product_sales = sum(daily.values()) if daily else None organic_reason = reason or ("No economics source rows for window" if product_sales is None else None) out = {"daily": series, "adSales": _metric(total, sources, reason=reason, no_prior=reason is not None), "organicSales": _metric(None if total is None or product_sales is None else product_sales - total, [ECONOMICS, *sources], reason=organic_reason, no_prior=organic_reason is not None), "reason": reason, "sourceTables": [ECONOMICS, *sources]} out["validate"] = validate_attribution(con, bounds, out) if not out["validate"]["ok"]: for key in ("adSales", "organicSales"): out[key]["validated"] = False out[key]["reason"] = out[key]["reason"] or out["validate"]["reason"] return out def validate_attribution(con, bounds, view): """Reconcile chart totals to independent scalar SQL and enforce the connection coverage wall.""" why, sources = _ads_coverage(con, bounds) why = why or view["reason"] checks = [] if why: return {"ok": False, "state": "unavailable", "reason": why, "sourceTables": sources, "checks": []} params = [bounds["start"], bounds["end"]] union = " UNION ALL ".join(f'SELECT date, sales FROM "{t}"' for t in ADS_TABLES) ad_total = _cents(con.execute(f"SELECT SUM(sales) FROM ({union}) WHERE " "left(CAST(date AS VARCHAR),10) BETWEEN ? AND ?", params).fetchone()[0]) net_total = _cents(con.execute(f"SELECT SUM(sales_net_product_sales_amount) FROM {ECONOMICS} WHERE " "left(CAST(start_date AS VARCHAR),10) BETWEEN ? AND ?", params).fetchone()[0]) def check(name, observed, expected): checks.append({"check": name, "ok": observed is not None and expected is not None and observed == expected, "a": None if observed is None else observed / 100, "b": None if expected is None else expected / 100}) ad_values = [_cents(day["adSales"]["value"]) for day in view["daily"]] organic_values = [_cents(day["organicSales"]["value"]) for day in view["daily"]] check("Daily ad sales equal advertised-product source sales", sum(ad_values) if None not in ad_values else None, ad_total) check("Attributed total equals advertised-product source sales", _cents(view["adSales"]["value"]), ad_total) expected_organic = None if ad_total is None or net_total is None else net_total - ad_total check("Organic total equals source net sales minus attributed sales", _cents(view["organicSales"]["value"]), expected_organic) check("Daily organic sales equal source net sales minus attributed sales", sum(organic_values) if None not in organic_values else None, expected_organic) ok = all(c["ok"] for c in checks) return {"ok": ok, "state": "tied" if ok else "failed", "reason": None if ok else "Attribution does not reconcile", "checks": checks, "sourceTables": [ECONOMICS, *sources]} def _window(con, bounds, stored, settings, today): sources = [ECONOMICS] cols = profit._require(con, ECONOMICS, profit._ECONOMICS_REQUIRED) ads_cols = profit.ads_columns(cols) optional = [column for column in (ORDERED, REFUNDS) if column in cols] sums = ", ".join(f'SUM("{c}"), count("{c}")' for c in optional) where = "left(CAST(start_date AS VARCHAR),10) BETWEEN ? AND ?" count, *breakdown = con.execute(f"SELECT count(*){', ' + sums if sums else ''} FROM {ECONOMICS} WHERE {where}", [bounds["start"], bounds["end"]]).fetchone() no_prior = count == 0 reason = "No economics source rows for window" if no_prior else None amounts = {column: _cents(breakdown[i * 2]) if breakdown[i * 2 + 1] == count and count else None for i, column in enumerate(optional)} invalid = con.execute( f"SELECT count(*) FROM {ECONOMICS} WHERE {where} AND (" + " OR ".join(f'"{c}" IS NULL OR NOT isfinite("{c}")' for c in ("sales_net_product_sales_amount", "fees_total_amount", "sales_net_units_sold")) + "".join(f' OR ("{c}" IS NOT NULL AND NOT isfinite("{c}"))' for c in ads_cols) + " OR NULLIF(trim(msku),'') IS NULL)", [bounds["start"], bounds["end"]]).fetchone()[0] if invalid: raise profit.SourceUnavailable("Economics amounts or SKU unavailable. Resync Amazon economics.") reason = reason or ("Missing source: economics ad spend totals" if not ads_cols else None) items = profit.economics_by_sku(con, bounds["start"], bounds["end"]) if not no_prior else [] activity = {sku for (sku,) in con.execute( f"SELECT msku FROM {ECONOMICS} WHERE {where} GROUP BY msku", [bounds["start"], bounds["end"]]).fetchall()} if count else set() skus, costs, typed = [], [], 0 for item in items: amounts_sku = [_cents(item[key]) for key in ("net_sales", "fees", "ads")] proceeds = amounts_sku[0] - amounts_sku[1] - amounts_sku[2] if None not in amounts_sku else None cost, cost_reason = _cost(item, stored.get(item["sku"]), bounds, settings, today) # A historical SKU with no selected-window activity contributes zero cost. if item["sku"] not in activity: cost, cost_reason = 0, None elif cost is not None and str((stored.get(item["sku"]) or {}).get("cost_source") or "") == profit.TYPED_COST_SOURCE: typed += 1 costs.append(cost) skus.append({"sku": item["sku"], "asin": item.get("asin"), "units": item["units"], "netSales": _metric(_cents(item["net_sales"]), sources, reason=reason, validated=reason is None), "netProceeds": _metric(proceeds if reason is None else None, sources, reason=reason), "cogs": _metric(cost, [profit.TABLE_KEY], reason=cost_reason), "netProfit": _metric(None if cost is None or reason or proceeds is None else proceeds - cost, [ECONOMICS, profit.TABLE_KEY], reason=reason or cost_reason)}) total = lambda field: sum(_cents(item[field]) for item in items) if reason is None else None net_sales, fees, spend = total("net_sales"), total("fees"), total("ads") proceeds = None if net_sales is None else net_sales - fees - spend ordered, refunds = amounts.get(ORDERED), amounts.get(REFUNDS) gross_bridge = net_sales is not None and ordered is not None and refunds is not None and ordered - refunds == net_sales cogs = sum(costs) if costs and all(c is not None for c in costs) else None cost_reason = next((s["cogs"]["reason"] for s in skus if s["cogs"]["value"] is None), reason) if cogs is None and not cost_reason: cost_reason = "Receipt cost unavailable for selected window" typed_line = typed_cost_line(typed) # The typed count rides on the COGS figure only: net profit keeps its own reason and checks. cogs_reason = cost_reason if cost_reason or cogs is None else typed_line components = [] def component(key, label, cents, tables, *, included=True, why=None): components.append({"key": key, "label": label, "included": included, **_metric(cents, tables, reason=why, no_prior=no_prior)}) component("sales", "Ordered product sales" if gross_bridge else "Net product sales", ordered if gross_bridge else net_sales, sources, why=reason) component("adSpend", "Ad spend", None if spend is None else -spend, sources, why=reason) component("amazonFees", "Amazon fees", None if fees is None else -fees, sources, why=reason) component("promotions", "Customer promotions", None, sources, included=False, why="Missing source: customer promotion breakdown. Fee-charge promotions are fee rebates.") component("refunds", "Refunds" if gross_bridge else "Refunds already included in net sales", None if refunds is None else -refunds, sources, included=gross_bridge, why=reason or ("Missing source: " + REFUNDS if refunds is None else None)) component("cogs", "COGS", None if cogs is None else -cogs, [profit.TABLE_KEY], why=cogs_reason) net = None if cogs is None or proceeds is None else proceeds - cogs metrics = {"netSales": _metric(net_sales, sources, reason=reason, no_prior=no_prior), "netProceeds": _metric(proceeds, sources, reason=reason, no_prior=no_prior), "cogs": _metric(cogs, [profit.TABLE_KEY], reason=cogs_reason, no_prior=no_prior), "netProfit": _metric(net, [ECONOMICS, profit.TABLE_KEY], reason=reason or cost_reason, no_prior=no_prior)} ranked = sorted(skus, key=lambda s: (-(s["netSales"]["value"] or 0), s["sku"])) mix = [{"sku": s["sku"], "sales": s["netSales"]} for s in ranked[:10]] if len(ranked) > 10: remainder = [_cents(s["netSales"]["value"]) for s in ranked[10:]] mix.append({"sku": "Other", "isOther": True, "sales": _metric(sum(remainder) if None not in remainder else None, sources, reason=reason, validated=reason is None)}) days = con.execute(f"SELECT left(CAST(start_date AS VARCHAR),10), SUM(sales_net_product_sales_amount) " f"FROM {ECONOMICS} WHERE {where} GROUP BY 1 ORDER BY 1", [bounds["start"], bounds["end"]]).fetchall() daily = {day: _cents(sales) for day, sales in days if _cents(sales) is not None} out = {"period": bounds, "noPrior": no_prior, "sourceRows": count, "reason": reason, "sourceTables": [ECONOMICS, profit.SETTLEMENTS, profit.TABLE_KEY], "metrics": metrics, "waterfall": {"components": components, "netProfit": metrics["netProfit"], "salesBasis": "orderedLessRefunds" if gross_bridge else "netProductSales", "reason": None if gross_bridge else "Refunds and promotions remain embedded in net product sales."}, "skus": skus, "salesMix": mix, "attribution": _attribution(con, bounds, daily), "typedCost": {"skus": typed, "line": typed_line}} out["validate"] = validate(con, out) out["waterfall"]["validate"] = {"ok": all(c["ok"] for c in out["validate"]["checks"] if "Waterfall" in c["check"]), "checks": [c for c in out["validate"]["checks"] if "Waterfall" in c["check"]]} out["salesMixValidate"] = next(c for c in out["validate"]["checks"] if c["check"].startswith("Top ten")) out["validated"] = out["validate"]["ok"] if not out["validated"]: metrics["netProfit"]["validated"] = False metrics["netProfit"]["reason"] = metrics["netProfit"]["reason"] or "Account P&L does not reconcile" return out def validate(con, view): """Independently reconcile the waterfall, SKU sums and settlement source aggregates.""" bounds, metrics = view["period"], view["metrics"] start, end = bounds["start"], bounds["end"] checks = [] def check(name, a, b, *, why=None, tolerance=0): ok = a is not None and b is not None and abs(a - b) <= tolerance and not why checks.append({"check": name, "ok": ok, "a": a, "b": b, "reason": why, "sourceTables": view["sourceTables"]}) observed = profit.net_proceeds_total(con, start, end) if view["sourceRows"] else None amount = metrics["netProceeds"]["value"] check("SKU net proceeds equal independent economics total", amount, observed, tolerance=0.01, why=view["reason"]) before = [c for c in view["waterfall"]["components"] if c["included"] and c["key"] != "cogs"] cents = [_cents(c["value"]) for c in before] bridge = sum(cents) / 100 if cents and all(c is not None for c in cents) else None check("Waterfall before COGS equals SKU net proceeds", bridge, amount, tolerance=0.001, why=view["reason"]) why = _ready(con, profit.SETTLEMENTS) settled, span, proceeds = None, None, bridge if why is None: try: currency = profit.economics_currency(con) span = profit.settlement_coverage(con, start, end, currency) # The tie reads only the days issued settlements cover, on both sides. lo, hi = span["start"] or start, span["end"] or end settled = profit.settlement_components(con, lo, hi, currency) why = settled["reason"] if span["start"] is None: settled = dict(settled, rows=0) why = f"Not verified: settlement components in {dt.date.fromisoformat(start):%B %Y}: no settled days." elif (lo, hi) != (start, end) and bridge is not None: proceeds = profit.net_proceeds_total(con, lo, hi) except profit.SourceUnavailable as exc: why = str(exc) target = settled["total"] if settled and settled["rows"] else None if target is not None and not math.isfinite(target): target, why = None, "Settlement amounts unavailable" tied, gap = profit._tie(proceeds, target) if proceeds is not None and target is not None else (False, None) gap = gap if gap is None or math.isfinite(gap) else None covered = {k: span[k] for k in ("start", "end", "label", "partial", "clear", "sentence")} if span else None checks.append({"check": "Net proceeds tie to settlements within 2%", "ok": tied and not why, "a": proceeds, "b": target, "gap": gap, "tolerance": profit.SETTLEMENT_TOLERANCE, "reason": why or ("No settlement source rows for window" if target is None else None), "components": [{**part, "gap": part["gap"] if math.isfinite(part["gap"]) else None} for part in settled["parts"]] if settled else [], "excluded": settled.get("excluded", {}) if settled else {}, "covered": covered, "note": covered and covered["sentence"], "sourceTables": [ECONOMICS, profit.SETTLEMENTS, "amzn_orders", *ADS_TABLES]}) values = [_cents(s["netProfit"]["value"]) for s in view["skus"]] sku_net = sum(values) / 100 if values and all(c is not None for c in values) else None check("Account net profit equals sum of SKU profit after COGS", metrics["netProfit"]["value"], sku_net, why=metrics["netProfit"]["reason"], tolerance=0.001) sku_before = [_cents(s["netProceeds"]["value"]) for s in view["skus"]] sku_costs = [_cents(s["cogs"]["value"]) for s in view["skus"]] after_costs = ((sum(sku_before) - sum(sku_costs)) / 100 if sku_before and None not in sku_before + sku_costs else None) check("Account net profit equals SKU net proceeds minus SKU COGS", metrics["netProfit"]["value"], after_costs, why=metrics["netProfit"]["reason"], tolerance=0.001) full = [_cents(c["value"]) for c in view["waterfall"]["components"] if c["included"]] check("Waterfall components sum to account net profit", sum(full) / 100 if full and None not in full else None, metrics["netProfit"]["value"], why=metrics["netProfit"]["reason"], tolerance=0.001) mix = [_cents(s["sales"]["value"]) for s in view["salesMix"]] check("Top ten plus Other preserves net product sales", sum(mix) / 100 if None not in mix else None, metrics["netSales"]["value"], why=view["reason"], tolerance=0.001) return {"ok": all(c["ok"] for c in checks), "state": "tied" if all(c["ok"] for c in checks) else "unavailable" if view["reason"] or metrics["netProfit"]["value"] is None or target is None else "failed", "checks": checks, "sourceTables": view["sourceTables"]} def validate_pace(con, bounds, pace, elapsed, month_days, reason): """Check the forecast arithmetic against independent source sums without certifying an outcome.""" if reason or not elapsed: return {"ok": False, "state": "unavailable", "reason": reason or "No complete days in current month", "sourceTables": [ECONOMICS, "_sync_state"], "checks": []} cols = profit._columns(con, ECONOMICS) ad_expr = " + ".join(f'COALESCE("{c}",0)' for c in profit.ads_columns(cols)) or "0" sales, proceeds = con.execute( f"SELECT SUM(sales_net_product_sales_amount), SUM(sales_net_product_sales_amount - fees_total_amount - ({ad_expr})) " f"FROM {ECONOMICS} WHERE left(CAST(start_date AS VARCHAR),10) BETWEEN ? AND ?", [bounds["start"], bounds["end"]]).fetchone() checks = [] for key, source in (("netSales", sales), ("netProceeds", proceeds)): expected = None if source is None else _cents(Decimal(str(source)) * month_days / elapsed) observed = _cents(pace[key]["value"]) checks.append({"check": f"{key} pace equals source daily rate times calendar days", "ok": expected is not None and observed is not None and abs(expected - observed) <= 1, "a": pace[key]["value"], "b": None if expected is None else expected / 100, "reason": pace[key]["reason"]}) return {"ok": all(c["ok"] for c in checks), "state": "estimate", "reason": None, "checks": checks, "sourceTables": [ECONOMICS, "_sync_state"]} class _SourceRows: def __init__(self, rows): self.rows = rows def fetchone(self): return self.rows[0] if self.rows else None def fetchall(self): return list(self.rows) def _settlement_source(sql): """Parse distinct settlement labels and referenced order dates without losing source lines or null keys.""" expressions = {"posted": ("posted_date", f"CAST({profit._moment_sql('s.posted_date')} AS DATE)")} for name, field in (("description", "amount_description"), ("kind", "amount_type"), ("transaction", "transaction_type")): expressions[name] = field, f"lower(regexp_replace(COALESCE({field}, ''), '[^a-zA-Z0-9]', '', 'g'))" fields = [field for field, _expression in expressions.values()] projection = ", ".join(expression.replace("s.posted_date", "posted_date") + " AS " + name for name, (_field, expression) in expressions.items()) keys = ", ".join(f"{field} AS source_{field}" for field in fields) labels = (f"labels AS MATERIALIZED (SELECT {keys}, {projection} " f"FROM (SELECT DISTINCT {', '.join(fields)} FROM {profit.SETTLEMENTS})), ") for name, (_field, expression) in expressions.items(): sql = sql.replace(expression + " AS " + name, "label." + name + " AS " + name) join = " AND ".join(f"s.{field} IS NOT DISTINCT FROM label.source_{field}" for field in fields) sql = sql.replace(f"FROM {profit.SETTLEMENTS} s JOIN streams", f"FROM {profit.SETTLEMENTS} s JOIN labels label ON {join} JOIN streams") sql = sql.replace("FROM amzn_orders GROUP BY amazon_order_id", f"FROM amzn_orders WHERE amazon_order_id IN (SELECT trim(order_id) " f"FROM {profit.SETTLEMENTS}) GROUP BY amazon_order_id") return sql.replace("WITH orders AS (", "WITH " + labels + "orders AS (", 1) class _ValidationSources: """Share settlement source preparation across this request's independent window sums.""" def __init__(self, con, bounds): self.con, self.results, self.spans = con, {}, {} self.bounds = [span for bound in bounds for span in (bound, *_comparisons(bound).values())] def _spans(self, currency=None): if currency in self.spans: return self.spans[currency] spans = {(bound["start"], bound["end"]) for bound in self.bounds} if currency is not None: for start, end in tuple(spans): covered = profit.settlement_coverage(self, start, end, currency) spans.add((covered["start"] or start, covered["end"] or end)) self.spans[currency] = sorted(spans) return self.spans[currency] def _components(self, sql, params): marker = "SELECT component, COALESCE(SUM(currency_amount), 0)" prefix, select = sql.split(marker, 1) aggregates, select = (marker + select).split("FROM dated WHERE", 1) aggregates = aggregates.removeprefix("SELECT component, ") # The grouping keys retain every input to the original window predicate and its bad-row counts. prefix += (" , prepared(component, basis, posted, dates, bad_dates, amount, source_rows, " "bad_amounts, unmatched_orders) AS (SELECT component, basis, posted, dates, bad_dates, " + aggregates + " FROM dated GROUP BY component, basis, posted, dates, bad_dates) ") spans = self._spans(params[0]) for bound in ("lo", "hi", "lo", "hi"): select = select.replace("CAST(? AS DATE)", "span." + bound, 1) ranges = ", ".join(f"({index}, CAST(? AS DATE), CAST(? AS DATE))" for index in range(len(spans))) select = f"FROM prepared JOIN (VALUES {ranges}) AS span(id, lo, hi) ON" + select select = select.replace("GROUP BY component ORDER BY component", "GROUP BY span.id, component ORDER BY span.id, component") bindings = list(params[:2]) + [value for span in spans for value in span] rows = self.con.execute(_settlement_source(prefix) + "SELECT span.id, component, " "COALESCE(SUM(amount), 0), SUM(source_rows), SUM(bad_amounts), SUM(unmatched_orders) " + select, bindings).fetchall() for index, span in enumerate(spans): self.results[(sql, tuple(params[:2]) + span * 2)] = [row[1:] for row in rows if row[0] == index] def _unscoped(self, sql): marker = "AND (CAST(" source, _date_filter = sql.split(marker, 1) source = source.replace("SELECT count(*)", f"SELECT CAST({profit._moment_sql('posted_date')} AS DATE) AS posted", 1) spans = self._spans(profit.economics_currency(self.con)) sums = ["count(*) FILTER (WHERE posted BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) OR posted IS NULL)" for _span in spans] counts = self.con.execute("SELECT " + ", ".join(sums) + " FROM (" + source + ")", [value for span in spans for value in span]).fetchone() for index, count in enumerate(counts): self.results[(sql, spans[index])] = [(count,)] def execute(self, sql, params=()): params = tuple(params or ()) key = sql, params if key in self.results: return _SourceRows(self.results[key]) if sql == "SELECT column_name FROM information_schema.columns WHERE table_name = ?": self.results[key] = self.con.execute(sql, params).fetchall() return _SourceRows(self.results[key]) components = (sql.startswith("WITH orders AS (") and len(params) == 6 and sql.endswith(" GROUP BY component ORDER BY component")) unscoped = (sql.startswith(f"SELECT count(*) FROM {profit.SETTLEMENTS} s\n") and "WHERE h.settlement_id IS NULL" in sql and len(params) == 2) if components: self._components(sql, params) elif unscoped: self._unscoped(sql) if key in self.results: return _SourceRows(self.results[key]) return self.con.execute(sql, params) def _account_bounds(chosen, today): bounds = [chosen, _bounds(today.replace(day=1), today - dt.timedelta(days=1), "mtd")] current = today.replace(day=1) for _ in range(2): last = current - dt.timedelta(days=1) current = last.replace(day=1) bounds.append(_bounds(current, last, current.strftime("%Y-%m"))) return bounds def account_view(con, period=None, *, stored=None, settings=None, today=None): """Return selected, comparison and monthly account windows without refreshing stored inputs.""" today = today or profit._today() selected = profit.period_bounds(period or profit.last_complete_month(today), today) end = min(dt.date.fromisoformat(selected["end"]), today - dt.timedelta(days=1)) chosen = _bounds(dt.date.fromisoformat(selected["start"]), end, selected["key"]) if dt.date.fromisoformat(chosen["start"]).year < 2: raise ValueError("Choose a period with an available year-ago calendar window.") if not profit._mirror_has_economics(con): return {"empty": True, "reason": "Sync Amazon economics first", "period": chosen, "sourceTables": [ECONOMICS], "validated": False} problem = _ready(con, ECONOMICS) if problem: raise profit.SourceUnavailable(problem) profit._require(con, ECONOMICS, profit._ECONOMICS_REQUIRED) if con.execute(f"SELECT count(*) FROM {ECONOMICS} WHERE " "TRY_CAST(left(CAST(start_date AS VARCHAR),10) AS DATE) IS NULL").fetchone()[0]: raise profit.SourceUnavailable("Economics dates unavailable. Resync Amazon economics.") stored, settings = stored or {}, settings or {} periods = _account_bounds(chosen, today) con = _ValidationSources(con, periods) cache = {} def window(bounds): key = bounds["start"], bounds["end"] if key not in cache: cache[key] = _window(con, bounds, stored, settings, today) return {**cache[key], "period": bounds} def compared(bounds): return {**window(bounds), "comparisons": {key: window(span) for key, span in _comparisons(bounds).items()}} mtd = compared(periods[1]) months = [compared(bounds) for bounds in periods[2:]] pace = {} elapsed = mtd["period"]["days"] month_days = calendar.monthrange(today.year, today.month)[1] from harness import amazon_store coverage = [amazon_store._read_state(con, amazon_store.entity_key(prefix + "economics")) for prefix in (amazon_store.BACKFILL_FLOOR_PREFIX, amazon_store.WATERMARK_PREFIX)] floor, last = [str((state or {}).get("detail") or "")[:10] for state in coverage] pace_reason = None if all((state or {}).get("state") == "done" for state in coverage) and \ floor and last and floor <= mtd["period"]["start"] and last >= mtd["period"]["end"] else \ "Economics coverage unavailable for pace forecast" for key, metric in mtd["metrics"].items(): value = _cents(metric["value"]) forecast = None if value is None or not elapsed or pace_reason else int( (Decimal(value) * month_days / elapsed).quantize(Decimal("1"), rounding=ROUND_HALF_UP)) pace[key] = {**_metric(forecast, [*metric["sourceTables"], "_sync_state"], reason=metric["reason"] or pace_reason or ("No complete days in current month" if not elapsed else None), no_prior=metric["noPrior"] or bool(pace_reason)), "projected": True} return {**compared(chosen), "empty": False, "currency": profit.economics_currency(con), "monthToDate": mtd, "previousMonths": months, "paceForecast": {"metrics": pace, "elapsedDays": elapsed, "monthDays": month_days, "sourceTables": [ECONOMICS, profit.TABLE_KEY, "_sync_state"], "reason": pace_reason, "validate": validate_pace(con, mtd["period"], pace, elapsed, month_days, pace_reason), "basis": "Complete days through yesterday at a constant daily pace. This is an estimate."}}