loopable / platform /modules /opportunity_finder.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
b2aa446 verified
Raw History Blame Contribute Delete
91.9 kB
"""Opportunity finder App: search terms in our departments whose search frequency rank rose week over week. Sources (read only, through the tenant Amazon mirror, never written, no SP-API call): amzn_brand_analytics search terms per department: search_frequency_rank, the top 3 clicked ASINs (click_share_rank 1 to 3), newest _period_start in full amzn_search_query_performance our ASINs (SQP reports our ASINs only) amzn_listings our ASINs (asin1) amzn_fba_fee_estimates comparable SKU price and total Amazon fees product_product Odoo cost of the product named by the SKU profit link Store documents (tenant runtime store, App owned; removing the App empties every one, rule 13): opportunity_finder_index {weeks: [period_start, ...], last_sync, last_error} opportunity_finder_week_<period_start> {week, period, capturedAt, departments, terms, evidence} opportunity_finder_items {items: {id: {term, status, owner, due, note, reason, asin, history}}} opportunity_finder_settings {departments, min_rise_pct, default_owner} The mirror keeps the newest brand analytics week in full and older weeks for watched terms only (`amazon_store._retain_weekly`). So a week's rank of an unwatched term survives only in this module's week document, and `on_amazon_sync` writes that document at every sync. A rank is a position: a lower number is searched more, so a term rises when its number falls. The department filter and the rank comparison run in DuckDB over every row, with no cap (rule 1). """ import contextlib import contextvars import datetime as dt import hashlib import json import logging import math import os import re import threading from decimal import Decimal from pathlib import Path APP_LABEL = "Opportunity finder" STATUSES = ("Rising", "Opportunity", "Accepted", "Served", "Dismissed") DEFAULT_STATUS = "Rising" DECISIONS = {"approved": "Accepted", "dismissed": "Dismissed"} DEFAULT_LIMIT = 200 DUE_DAYS = 7 DEFAULTS = {"min_rise_pct": 10.0} KEY_INDEX = "opportunity_finder_index" KEY_SETTINGS = "opportunity_finder_settings" KEY_ITEMS = "opportunity_finder_items" WEEK_PREFIX = "opportunity_finder_week_" BA_TABLE = "amzn_brand_analytics" SQP_TABLE = "amzn_search_query_performance" LISTINGS_TABLE = "amzn_listings" FEE_TABLE = "amzn_fba_fee_estimates" PROFIT_TABLE = "ut_amazon_sku_profit" BA_NEED = ("department_name", "search_term", "search_frequency_rank", "_period_start") #: The synced table key (no prefix) that starts a new history point. TRIGGER = "brand_analytics" NO_MIRROR = "The Amazon mirror is not on this server yet. It appears after the first sync." #: Brand analytics holds no rows. Each cause below gets its own sentence, never this one over held rows. NOT_SYNCED = "Brand analytics has not synced yet, so no search term can be compared." #: Rows synced before weeks were dated. The next pass on such a table loads the two newest weeks. NO_WEEK = "Brand analytics rows carry no week date, so the next Amazon sync loads the two newest weeks." NO_FIELDS = "Brand analytics rows miss a field the finder reads, so no search term can be compared." #: Amazon has not finished the newest week. `{day}` is its end date in words, such as October 24. WAITING = "Amazon has not finished the week ending {day} yet, so the finder reads it at the next sync." WAITING_NEXT = "Amazon has not finished the newest week yet, so the finder reads it at the next sync." REFUSED = ("Amazon refused the brand analytics report. Check Brand Registry and the Brand Analytics role " "in Seller Central.") STORE_FAILED = "The last brand analytics report could not be stored, so the next Amazon sync loads it again." #: `amazonStatus` values that are Amazon's own refusal, as `amazon_store._sync_problem` records them. AMAZON_REFUSALS = ("FATAL", "CANCELLED", "NOT_OFFERED") #: `_sync_state` markers of the brand analytics pass, matched bare or behind an account prefix. WAITING_KEY = "amzn_waiting_brand_analytics" REFUSED_KEY = "amzn_refused_brand_analytics" READ_FAILED = ("The Amazon mirror could not be read just now, so no search term can be compared. " "Try again in a minute.") HISTORY_FAILED = "Weekly history could not be saved. The next Amazon sync will try again." NO_VOLUME = "Brand analytics reports rank, not search volume." NO_GROWTH = "Search volume growth needs dated volume reports." GRID_COLUMNS = ( {"key": "term", "label": "Search term", "kind": "text", "width": 160, "action": "open"}, {"key": "searchVolume", "label": "Search volume", "kind": "number", "width": 140, "reasonKey": "searchVolumeReason"}, {"key": "growthPct", "label": "Volume growth", "kind": "percent", "width": 140, "reasonKey": "growthReason"}, {"key": "trendPoints", "label": "Rank trend", "kind": "trend", "width": 100, "x": "week", "y": "rank", "reverse": True}, {"key": "priorRank", "label": "Prior rank", "kind": "number", "width": 70, "optional": True}, {"key": "rank", "label": "Rank", "kind": "number", "width": 90, "priorKey": "priorRank"}, {"key": "risePct", "label": "Rank change", "kind": "percent", "width": 90}, {"key": "status", "label": "Status", "kind": "status", "width": 90}, {"key": "accept", "label": "Accept", "kind": "action", "width": 80, "action": "accept", "requiresEdit": True}, {"key": "top3", "label": "Top 3 clicked", "kind": "list", "width": 180, "optional": True}, {"key": "owner", "label": "Owner", "kind": "text", "width": 140, "optional": True}, ) BAD_PERIOD = "The newest Brand analytics week has no valid date, so no week can be compared." NO_DEPARTMENTS = "No brand analytics row shows one of your ASINs yet, so the finder has no department to search." NOT_READY = "The finder compares two weekly syncs. One week is held so far." NO_HISTORY = "No weekly history yet. The first week is written at the next Amazon sync." READ_ONLY = "Your account can view the opportunity finder but not change it." UNKNOWN_ITEM = "That search term is not in the opportunity finder." NOT_RISING = "Its search frequency rank did not rise this week." OLDER_WEEK = ("Only the newest brand analytics week can be tied. The mirror keeps older weeks for " "Competitor watch's watched terms only.") LIMIT = ("Each week of history is one store document, limited to 128 MB. " "Split a week by department when its rank and share history nears that size.") #: A mirror probe longer than this is interrupted and reads as busy, under the 45 s request deadline. PROBE_SECONDS = 20.0 #: A fired probe bound repeats interrupt() this often: DuckDB can lose a single one on a parameterized execute. INTERRUPT_EVERY = 0.05 #: Mirror facts and rising lists kept per process, keyed on the mirror mark. Oldest goes first. MEMO_KEEP = 16 #: Accounts that sign in for a process, never a person who can own an item. SERVICE_ACCOUNTS = ("admin", "automation", "scheduler", "qa-runner", "leadership") LOG = logging.getLogger("aios.opportunity_finder") class UnknownItem(KeyError, ValueError): """An item id the finder does not hold. A KeyError for the router, a ValueError for C4.""" def _now(): return dt.datetime.now(dt.timezone.utc) def _stamp(at=None): return (at or _now()).strftime("%Y-%m-%dT%H:%M:%SZ") def _rt(tenant_key, st=None): """The tenant runtime store handle (get and update), or raise when it cannot be resolved.""" if st is not None: return st from harness import amazon_store rt = amazon_store._runtime_for(tenant_key) if rt is None: raise RuntimeError(f"The store for tenant {tenant_key!r} could not be opened.") return rt def _doc(st, key): try: value = st.get(key) except Exception: # noqa: BLE001 value = None return value if isinstance(value, dict) else {} def _text(value): return "" if value is None else str(value).strip() def norm(term): """The stored form of a search term: lower case and trimmed, as the SQL `lower(trim())` reads it.""" return _text(term).lower() def item_id(term): """One stable id per search term, the same across weeks.""" return "o" + hashlib.sha1(norm(term).encode("utf-8")).hexdigest()[:16] # --------------------------------------------------------------------------- mirror reads def _mirror_path(tenant_key, st): from harness import amazon_store rt = st if hasattr(st, "datastore_path") else None return amazon_store._tenant_mirror_path(tenant_key, rt) class _Unreadable: """The mirror exists but no cursor could be opened on it.""" def __init__(self, cause): self.cause = cause class _ReadFailed(Exception): """A mirror probe failed or was interrupted. Never read as an absent table.""" #: The mirror mark of the cursor `_reader` yielded: (path, install generation, file stat). _MARK = contextvars.ContextVar("opportunity_finder_mark", default=None) _MEMO = {} _MEMO_LOCK = threading.Lock() def _remember(key, build, keep=None): """`build()` once per key. A failed build, or one `keep` refuses, is not kept: the next request reads again.""" if key is None: return build() with _MEMO_LOCK: if key in _MEMO: return _MEMO[key] value = build() if keep is not None and not keep(value): return value with _MEMO_LOCK: _MEMO[key] = value while len(_MEMO) > MEMO_KEEP: _MEMO.pop(next(iter(_MEMO))) return value def _mark(cur, shared): """Moves whenever the mirror rows can have changed: an install, a version swap or a rewrite. The file is the one the cursor reads, so a reader that followed the serving pointer to `<stem>.v<N>.duckdb` gets a new mark, even where the logical path and its stats stay put.""" from harness import datastore rows = _bounded(cur, "SELECT path FROM duckdb_databases() WHERE database_name = current_database()") path = rows[0][0] if rows and rows[0][0] else "" generation = None if shared: with contextlib.suppress(Exception): generation = (datastore.mirror_generation(), str(datastore.serving_version().get("version"))) stamps = [] for part in (Path(path), Path(str(path) + ".wal")): try: stat = os.stat(part) stamps.append((stat.st_ino, stat.st_mtime_ns, stat.st_size)) except OSError: stamps.append(None) return (str(path), generation, tuple(stamps)) @contextlib.contextmanager def _reader(tenant_key, st, opener=None): """A read cursor on the tenant mirror, None when there is no mirror, `_Unreadable` when it cannot open. `opener` is the typed `ut_amzn_*` door the route hands in (modules never import api): it asserts the tenant and returns `datastore.ro_cursor()` on the serving mirror version. Without it, the shared instance serves when it is this mirror, else the file the mirror's serving pointer names.""" from harness import datastore path = Path(_mirror_path(tenant_key, st)) try: shared = Path(datastore.DB_PATH).resolve() == path.resolve() except Exception: # noqa: BLE001 shared = False try: physical = path if shared or opener is not None else Path(datastore.serving_file(path)[0]) present = physical.exists() or (opener is not None and Path(datastore.serving_file()[0]).exists()) except Exception: # noqa: BLE001 physical, present = path, path.exists() if not present: yield None return cur = None try: if opener is not None: cur = opener() elif shared: if hasattr(st, "assert_datastore_matches"): st.assert_datastore_matches() cur = datastore.ro_cursor() else: import duckdb cur = duckdb.connect(str(physical), read_only=True) mark = _mark(cur, shared or opener is not None) except Exception as exc: # noqa: BLE001 if cur is not None: with contextlib.suppress(Exception): cur.close() LOG.exception("Opportunity finder mirror could not open") yield _Unreadable(READ_FAILED) return token = _MARK.set(mark) try: yield cur finally: _MARK.reset(token) cur.close() def _interrupt(cur): with contextlib.suppress(Exception): cur.interrupt() def _bounded(cur, sql, params=()): """Every row of one query, interrupted past PROBE_SECONDS. Any failure raises `_ReadFailed`. Past the bound, interrupt() repeats every INTERRUPT_EVERY until the query ends: one call can be lost.""" if cur is None or isinstance(cur, _Unreadable): raise _ReadFailed(READ_FAILED) lock, done = threading.Lock(), threading.Event() def _fire(): while True: with lock: if done.is_set(): return _interrupt(cur) if done.wait(INTERRUPT_EVERY): return timer = threading.Timer(PROBE_SECONDS, _fire) timer.daemon = True timer.start() try: return cur.execute(sql, list(params)).fetchall() except Exception as exc: # noqa: BLE001 raise _ReadFailed(READ_FAILED) from exc finally: with lock: done.set() timer.cancel() def _types(cur, table): """{column: data type} of a mirror table, {} when the table is absent. A failed read raises.""" if not table.replace("_", "").isalnum(): raise ValueError(f"{table!r} is not a table name.") return {str(n): str(t) for n, t in _bounded( cur, f"SELECT column_name, data_type FROM information_schema.columns WHERE table_name = '{table}'")} def _columns(cur, table): """The column names of a mirror table, empty when the table is absent. A failed read raises `_ReadFailed`.""" return set(_types(cur, table)) def iso_period(value): """Accept an ISO calendar date or timestamp with a valid ISO calendar date prefix.""" text = _text(value) if len(text) < 10 or text[4:5] != "-" or text[7:8] != "-": return False try: dt.date.fromisoformat(text[:10]) if len(text) > 10: if text[10] not in ("T", " "): return False dt.datetime.fromisoformat(text.replace("Z", "+00:00")) except ValueError: return False return True def _our_asins(cur): """SQP ASINs plus listing `asin1`: the ASINs the seller owns.""" ours = set() if "asin" in _columns(cur, SQP_TABLE): ours |= {_text(r[0]) for r in _bounded( cur, f'SELECT DISTINCT CAST("asin" AS VARCHAR) FROM {SQP_TABLE} WHERE "asin" IS NOT NULL')} if "asin1" in _columns(cur, LISTINGS_TABLE): ours |= {_text(r[0]) for r in _bounded( cur, f'SELECT DISTINCT CAST("asin1" AS VARCHAR) FROM {LISTINGS_TABLE} WHERE "asin1" IS NOT NULL')} return {a for a in ours if a} def _period(cur, kind="VARCHAR"): """The newest brand analytics `_period_start` as text, or None. One column scan, bounded. A text column takes MAX as stored. A DATE or TIMESTAMP column casts only the one MAX value.""" newest = ('MAX("_period_start")' if str(kind).upper().startswith("VARCHAR") else 'CAST(MAX("_period_start") AS VARCHAR)') rows = _bounded(cur, f'SELECT {newest} FROM {BA_TABLE}') value = str(rows[0][0]) if rows and rows[0][0] is not None else None return value[:10] if value is not None and iso_period(value) else value _TERM = 'lower(trim(CAST("search_term" AS VARCHAR)))' _DEPT = 'trim(CAST("department_name" AS VARCHAR))' _IN_PERIOD = 'substr(CAST("_period_start" AS VARCHAR), 1, 10) = ?' _IN_DEPTS = f"{_DEPT} IN (SELECT unnest(?::VARCHAR[]))" def _derived_departments(cur, ours): """R49 default: every department where a brand analytics row clicked one of our ASINs, any period.""" if not ours or "clicked_asin" not in _columns(cur, BA_TABLE): return [] rows = _bounded( cur, f'SELECT DISTINCT {_DEPT} FROM {BA_TABLE} ' f'WHERE trim(CAST("clicked_asin" AS VARCHAR)) IN (SELECT unnest(?::VARCHAR[])) ' f'AND "department_name" IS NOT NULL', [sorted(ours)]) return sorted(_text(r[0]) for r in rows if _text(r[0])) _MONTHS = ("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December") def _day_words(value): """`2026-10-24` as `October 24`, or None when the text holds no ISO date.""" match = re.search(r"\d{4}-\d{2}-\d{2}", _text(value)) try: day = dt.date.fromisoformat(match.group(0)) if match else None except ValueError: day = None return f"{_MONTHS[day.month - 1]} {day.day}" if day else None def ba_marker(rows): """("waiting", day words or None), ("refused" or "failed", None) or None from (entity, detail, updated_at) rows. The newest of the waiting and refused markers wins. A refusal whose text says the week is not available yet is Amazon still building that week, not a refusal.""" found = [] for entity, detail, updated in rows or (): entity = _text(entity) for key in (WAITING_KEY, REFUSED_KEY): if entity == key or entity.endswith("__" + key): found.append((_text(updated), key, _text(detail))) if not found: return None _updated, key, detail = max(found) try: body = json.loads(detail) except ValueError: body = None body = body if isinstance(body, dict) else {} said = " ".join(_text(body.get(k)) for k in ("error", "reason", "diagnostic")) or detail # A detail cut short is not JSON, so the end date is also read straight from the text. end = (_text(body.get("weekEnd")) or _found(r'"weekEnd"\s*:\s*"(\d{4}-\d{2}-\d{2})', detail) or _found(r"end date \[(\d{4}-\d{2}-\d{2})\]", said)) if key == WAITING_KEY or "not available yet" in said.lower(): return "waiting", _day_words(end) # Only Amazon's own refusal names Brand Registry; a failure on our side (memory, a parse) reads as one. if _text(body.get("amazonStatus")).upper() in AMAZON_REFUSALS or re.search(r"\b403\b", said): return "refused", None return "failed", None def _found(pattern, text): match = re.search(pattern, text or "") return match.group(1) if match else "" def _markers(cur): """The brand analytics marker rows in the mirror's `_sync_state`. A missing table or a failed read is none.""" try: cols = {str(r[0]) for r in _bounded( cur, "SELECT column_name FROM information_schema.columns WHERE table_name = '_sync_state'")} if not {"entity", "cursor_wd"} <= cols: return [] when = "CAST(updated_at AS VARCHAR)" if "updated_at" in cols else "NULL" return _bounded(cur, f"SELECT entity, CAST(cursor_wd AS VARCHAR), {when} FROM _sync_state " "WHERE ends_with(entity, ?) OR ends_with(entity, ?)", [WAITING_KEY, REFUSED_KEY]) except _ReadFailed: return [] def not_synced(cur, columns): """One plain sentence per cause that keeps brand analytics from comparing a week. A failed row probe raises.""" held = bool(columns) and bool(_bounded(cur, f"SELECT 1 FROM {BA_TABLE} LIMIT 1")) marker = ba_marker(_markers(cur)) if marker and marker[0] == "refused": return [REFUSED] out = [] if marker and marker[0] == "failed": out.append(STORE_FAILED) elif marker: out.append(WAITING.format(day=marker[1]) if marker[1] else WAITING_NEXT) if held: out.append(NO_FIELDS if set(BA_NEED) - set(columns) - {"_period_start"} else NO_WEEK) return out or [NOT_SYNCED] def _synced(facts): return set(BA_NEED) <= facts["columns"] and facts["period"] is not None def _facts(cur, fresh=False): """The mirror facts every read starts from, once per mirror mark: columns, newest period, ours, derived. A not-synced answer is never kept, so a mirror that gains the table is read at the next request.""" def build(): types = _types(cur, BA_TABLE) facts = {"columns": set(types), "period": None, "ours": set(), "derived": [], "period_type": types.get("_period_start")} if not set(BA_NEED) <= facts["columns"]: return facts facts["period"] = _period(cur, types.get("_period_start", "VARCHAR")) if facts["period"] is None or not iso_period(facts["period"]): return facts try: facts["ours"] = _our_asins(cur) facts["derived"] = _derived_departments(cur, facts["ours"]) except _ReadFailed: raise except Exception as exc: # noqa: BLE001 raise _ReadFailed(READ_FAILED) from exc return facts mark = _MARK.get() facts = _remember(None if mark is None or fresh else ("facts", mark), build, keep=_synced) if not _synced(facts): LOG.warning("[opportunity-finder] %s not synced: file %s, missing columns %s, newest period %r", BA_TABLE, mark[0] if mark else "?", sorted(set(BA_NEED) - facts["columns"]), facts["period"]) return facts def _source(cur, st, fresh=False): """What the mirror holds now: {period, departments, ours, problems}. Departments follow the setting.""" out = {"period": None, "departments": [], "ours": set(), "problems": [], "derived": [], "periodType": None} if cur is None: out["problems"].append(NO_MIRROR) return out try: facts = _facts(cur, fresh) except _ReadFailed: out["problems"].append(READ_FAILED) return out out["periodType"] = facts.get("period_type") if not _synced(facts): try: out["problems"].extend(not_synced(cur, facts["columns"])) except _ReadFailed: out["problems"].append(READ_FAILED) return out if not iso_period(facts["period"]): out["problems"].append(BAD_PERIOD.format(value=facts["period"])) return out out["period"] = facts["period"] out["ours"] = set(facts["ours"]) out["derived"] = list(facts["derived"]) chosen = _doc(st, KEY_SETTINGS).get("departments") out["departments"] = sorted({_text(d) for d in chosen if _text(d)}) if chosen else out["derived"] if not out["departments"]: out["problems"].append(NO_DEPARTMENTS) return out def week_ranks(cur, period, departments): """{term: rank} for every in-department term of one period. One SQL pass, no cap (rule 1).""" rows = cur.execute( f'SELECT {_TERM}, MIN(TRY_CAST(trim(CAST("search_frequency_rank" AS VARCHAR)) AS BIGINT)) ' f'FROM {BA_TABLE} WHERE {_IN_PERIOD} AND {_IN_DEPTS} AND "search_term" IS NOT NULL GROUP BY 1', [period, list(departments)]).fetchall() return {str(t): (int(r) if r is not None else None) for t, r in rows} def _rising_rows(cur, period, departments, prior, min_pct): """Every in-department term whose rank number fell at least `min_pct` percent from `prior`, with its top 3. The prior week's ranks join as unnested list parameters, so the comparison runs in SQL.""" pairs = sorted((t, int(r)) for t, r in prior.items() if r is not None and int(r) > 0) has_top = {"clicked_asin", "click_share_rank"} <= _columns(cur, BA_TABLE) cols = _columns(cur, BA_TABLE) share = 'TRY_CAST(trim(CAST("click_share" AS VARCHAR)) AS DOUBLE)' if "click_share" in cols else "NULL" conv = 'TRY_CAST(trim(CAST("conversion_share" AS VARCHAR)) AS DOUBLE)' if "conversion_share" in cols else "NULL" pos = 'TRY_CAST(trim(CAST("click_share_rank" AS VARCHAR)) AS INTEGER)' top = (f'SELECT {_TERM} AS term, list(struct_pack(asin := trim(CAST("clicked_asin" AS VARCHAR)), ' f'pos := {pos}, share := {share}, conv := {conv}) ORDER BY {pos}) AS top3 FROM {BA_TABLE} ' f'WHERE {_IN_PERIOD} AND {_IN_DEPTS} AND {pos} BETWEEN 1 AND 3 AND "clicked_asin" IS NOT NULL ' f'GROUP BY 1') if has_top else "SELECT NULL::VARCHAR AS term, NULL AS top3 WHERE FALSE" params_top = [period, list(departments)] if has_top else [] sql = ( f'WITH cur AS (SELECT {_TERM} AS term, ' f'MIN(TRY_CAST(trim(CAST("search_frequency_rank" AS VARCHAR)) AS BIGINT)) AS rank FROM {BA_TABLE} ' f'WHERE {_IN_PERIOD} AND {_IN_DEPTS} AND "search_term" IS NOT NULL GROUP BY 1), ' f'prior AS (SELECT unnest(?::VARCHAR[]) AS term, unnest(?::BIGINT[]) AS rank), ' f'top AS ({top}) ' f'SELECT cur.term, prior.rank, cur.rank, prior.rank - cur.rank, ' f'(prior.rank - cur.rank) * 100.0 / prior.rank AS pct, top.top3 ' f'FROM cur JOIN prior ON prior.term = cur.term LEFT JOIN top ON top.term = cur.term ' f'WHERE cur.rank IS NOT NULL AND cur.rank < prior.rank ' f'AND (prior.rank - cur.rank) * 100.0 / prior.rank >= ? ' f'ORDER BY pct DESC, cur.term') params = [period, list(departments), [t for t, _r in pairs], [r for _t, r in pairs], *params_top, float(min_pct)] mark = _MARK.get() key = (None if mark is None else ("rising", mark, period, tuple(departments), float(min_pct), hash(tuple(pairs)))) return _remember(key, lambda: _bounded(cur, sql, params)) def _top3_list(raw, ours): out = [] for entry in raw or (): if not isinstance(entry, dict) or not entry.get("asin"): continue out.append({"asin": entry["asin"], "rank": entry.get("pos"), "clickShare": entry.get("share"), "conversionShare": entry.get("conv"), "isOurs": entry["asin"] in ours}) return out def _number(value): try: number = float(value) except (TypeError, ValueError): return None return number if math.isfinite(number) else None def _share_rows(cur, departments, period=None, term=None): """Page top-clicked ASIN shares by term and source week before projecting evidence.""" cols = _columns(cur, BA_TABLE) if not {"clicked_asin", "click_share_rank", *BA_NEED} <= cols: return share = 'TRY_CAST("click_share" AS DOUBLE)' if "click_share" in cols else "NULL" conv = 'TRY_CAST("conversion_share" AS DOUBLE)' if "conversion_share" in cols else "NULL" where, params = [_IN_DEPTS], [list(departments)] if period is not None: where.append(_IN_PERIOD) params.append(period) if term is not None: where.append(f"{_TERM} = ?") params.append(term) selected = f'{" AND ".join(where)} AND TRY_CAST("click_share_rank" AS INTEGER) = 1' week_key = 'substr(CAST("_period_start" AS VARCHAR), 1, 10)' sql = (f'WITH page AS (SELECT DISTINCT {_TERM} AS term, {week_key} AS week FROM {BA_TABLE} ' f'WHERE {selected} ORDER BY 1, 2 LIMIT ? OFFSET ?), ' f'picked AS (SELECT {_TERM} AS term, {week_key} AS week, ' f'TRY_CAST("search_frequency_rank" AS BIGINT) AS rank, CAST("clicked_asin" AS VARCHAR) AS asin, ' f'{share} AS click_share, {conv} AS conversion_share, row_number() OVER (' f'PARTITION BY {_TERM}, {week_key} ' f'ORDER BY TRY_CAST("search_frequency_rank" AS BIGINT), {_DEPT}, "clicked_asin") AS pick ' f'FROM {BA_TABLE} JOIN page ON {_TERM} = page.term AND {week_key} = page.week WHERE {selected}) ' 'SELECT term, week, rank, asin, click_share, conversion_share FROM picked WHERE pick = 1 ' 'ORDER BY term, week') offset = 0 while True: page = _bounded(cur, sql, [*params, 1024, offset, *params]) for name, week, rank, asin, click, conversion in page: if iso_period(week): yield name, {"week": week, "rank": rank, "asin": asin, "clickShare": _number(click), "conversionShare": _number(conversion)} if len(page) < 1024: return offset += len(page) def _monthly_volume(cur, term): """SQP query volume once per reported month, never summed across the seller's ASINs.""" needed = {"search_query_data_search_query", "search_query_data_search_query_volume", "start_date", "end_date"} cols = _columns(cur, SQP_TABLE) if not needed <= cols: return [] # Current SQP is monthly. A declared different period must never be relabelled. period = ('AND upper(CAST("_report_period" AS VARCHAR)) IN (\'MONTH\', \'MONTHLY\')' if "_report_period" in cols else "") rows = _bounded(cur, f'SELECT substr(CAST(start_date AS VARCHAR), 1, 10), ' f'substr(CAST(end_date AS VARCHAR), 1, 10), ' f'MIN(TRY_CAST(search_query_data_search_query_volume AS DOUBLE)), ' f'MAX(TRY_CAST(search_query_data_search_query_volume AS DOUBLE)) FROM {SQP_TABLE} ' 'WHERE lower(trim(CAST(search_query_data_search_query AS VARCHAR))) = ? ' f'{period} GROUP BY 1, 2 ' 'HAVING COUNT(*) = COUNT(TRY_CAST(search_query_data_search_query_volume AS DOUBLE)) ' 'ORDER BY 1, 2', [term]) out = [] for start, end, low, high in rows: if not iso_period(start) or not iso_period(end) or low != high or _number(low) is None or low < 0: continue first, last = dt.date.fromisoformat(start), dt.date.fromisoformat(end) following = (first.replace(day=28) + dt.timedelta(days=4)).replace(day=1) if first.day == 1 and last == following - dt.timedelta(days=1): out.append({"periodStart": start, "periodEnd": end, "period": "monthly", "volume": low}) return out def _term_observations(st, term): """Project one term's saved observations without loading a weekly population.""" backend = st._document_store() if callable(getattr(st, "_document_store", None)) else st held = {} for week in _index(st): name = WEEK_PREFIX + week key = st.store_key(name) if callable(getattr(st, "store_key", None)) else name rank, evidence, captured = backend.get_json_paths( key, [("terms", term), ("evidence", term), ("capturedAt",)]) held[week] = {"rank": rank, "evidence": evidence or {}, "capturedAt": captured} return held def _evidence(cur, st, term, departments, *, monthly_allowed=True, observations=None): weekly = {} held_weeks = _term_observations(st, term) if observations is None else observations for week, observation in held_weeks.items(): rank = observation["rank"] if rank is not None: held = observation["evidence"] weekly[week] = {"week": week, "rank": rank, "asin": held.get("asin"), "clickShare": held.get("clickShare"), "conversionShare": held.get("conversionShare")} for _name, point in _share_rows(cur, departments, term=term): weekly[point["week"]] = point monthly = _monthly_volume(cur, term) if monthly_allowed else [] return {"weekly": [weekly[w] for w in sorted(weekly)], "monthly": monthly, "demandBasis": "monthly" if monthly else "rank", "shareBasis": "Top clicked ASIN", "reason": ("Displayed SQP volume is monthly. Weekly evidence shows rank and shares." if monthly else "No monthly volume is held. Search frequency rank is shown.")} def _validation(ok, status, source, detail): return {"ok": ok, "status": status, "source": source, "detail": detail} def _same_number(actual, expected, tolerance=0): return (_number(actual) is not None and _number(expected) is not None and abs(float(actual) - float(expected)) <= tolerance) def validate_evidence(cur, st, term, departments, evidence, *, monthly_allowed=True): """Check displayed figures against separately selected source rows and dated retained observations.""" held = _term_observations(st, term) source = {} cols = _columns(cur, BA_TABLE) if {"clicked_asin", "click_share_rank", *BA_NEED} <= cols: click = 'TRY_CAST(click_share AS DOUBLE)' if "click_share" in cols else "NULL" conv = 'TRY_CAST(conversion_share AS DOUBLE)' if "conversion_share" in cols else "NULL" offset = 0 while True: rows = _bounded(cur, f'SELECT substr(CAST(_period_start AS VARCHAR), 1, 10), ' f'TRY_CAST(search_frequency_rank AS BIGINT), CAST(clicked_asin AS VARCHAR), ' f'{click}, {conv} FROM {BA_TABLE} WHERE {_TERM} = ? AND {_IN_DEPTS} ' 'AND TRY_CAST(click_share_rank AS INTEGER) = 1 ' f'ORDER BY 1, 2, {_DEPT}, clicked_asin LIMIT 1024 OFFSET ?', [term, list(departments), offset]) for week, rank, asin, share, conversion in rows: source.setdefault(week, {"rank": rank, "asin": asin, "clickShare": share, "conversionShare": conversion}) if len(rows) < 1024: break offset += len(rows) checks = [] for point in evidence["weekly"]: week = point["week"] saved = held.get(week) or {} expected = source.get(week) retained = expected is None if retained: expected = {"rank": saved.get("rank"), **(saved.get("evidence") or {})} fields = {} for field in ("rank", "clickShare", "conversionShare"): actual, target = point.get(field), expected.get(field) valid_range = (_number(actual) is not None and (float(actual) > 0 if field == "rank" else 0 <= float(actual) <= 1)) same = actual is None and target is None or valid_range and _same_number(actual, target) if field != "rank" and point.get("asin") != expected.get("asin"): same = False if not same: result = _validation(False, "invalid", BA_TABLE, "The figure does not match its brand analytics observation.") point[field] = None elif actual is None: result = _validation(None, "unavailable", BA_TABLE, "This figure was not reported for this week.") elif retained: result = _validation(None, "retained_observation", BA_TABLE, "Saved source observation. The mirror no longer holds this term's week.") result["capturedAt"] = saved.get("capturedAt") else: result = _validation(True, "matched_source_observation", BA_TABLE, "Matches the brand analytics mirror observation for this week.") fields[field] = result checks.append(result) point["validation"] = fields if "volume" in point: point.pop("volume") fields["volume"] = _validation(False, "invalid", SQP_TABLE, "Weekly search volume is unavailable and is hidden.") checks.append(fields["volume"]) if any(value["ok"] is False for value in fields.values()): point["reason"] = "A figure failed validation and is hidden." volumes = {} needed = {"search_query_data_search_query", "search_query_data_search_query_volume", "start_date", "end_date"} cols = _columns(cur, SQP_TABLE) if monthly_allowed else set() if monthly_allowed and needed <= cols: report = 'CAST(_report_period AS VARCHAR)' if "_report_period" in cols else "'MONTH'" offset = 0 while True: rows = _bounded(cur, f'SELECT substr(CAST(start_date AS VARCHAR), 1, 10), ' 'substr(CAST(end_date AS VARCHAR), 1, 10), ' f'TRY_CAST(search_query_data_search_query_volume AS DOUBLE), {report} ' f'FROM {SQP_TABLE} WHERE lower(trim(CAST(search_query_data_search_query AS VARCHAR))) = ? ' 'ORDER BY 1, 2, 3, 4 LIMIT 1024 OFFSET ?', [term, offset]) for start, end, volume, report_period in rows: if _text(report_period).upper() in {"MONTH", "MONTHLY"}: volumes.setdefault((start, end), []).append(volume) if len(rows) < 1024: break offset += len(rows) for point in evidence["monthly"]: start, end = point["periodStart"], point["periodEnd"] values = volumes.get((start, end), []) full_month = False if iso_period(start) and iso_period(end): first, last = dt.date.fromisoformat(start), dt.date.fromisoformat(end) full_month = (first.day == 1 and (last + dt.timedelta(days=1)).day == 1 and (first.year, first.month) == (last.year, last.month)) ok = (full_month and point.get("period") == "monthly" and bool(values) and _number(point.get("volume")) is not None and point["volume"] >= 0 and all(_same_number(point["volume"], value) for value in values)) result = _validation(bool(ok), "matched_source_observation" if ok else "invalid", SQP_TABLE, "Matches monthly SQP volume once per query across ASINs." if ok else "Monthly volume does not match SQP and is hidden.") if not ok: point["volume"], point["reason"] = None, result["detail"] point["validation"] = result checks.append(result) ok = False if any(c["ok"] is False for c in checks) else ( True if checks and all(c["ok"] is True for c in checks) else None) result = _validation(ok, "invalid" if ok is False else "matched_source_observation" if ok else "partial", f"{BA_TABLE}, {SQP_TABLE}" if monthly_allowed else BA_TABLE, "Some figures are retained observations or unavailable." if ok is None else "A figure failed source validation." if ok is False else "Figures match their source observations.") evidence["validation"] = result return result def _match_words(value): return {w for w in re.findall(r"[a-z0-9]+", norm(value)) if len(w) > 1 and w not in {"the", "and", "for", "with", "of"}} def _margin_values(price, fees, cost): """Comparable unit economics, using its own selling price, total fees and linked Odoo cost.""" profit = price - fees - cost return {"profit": round(profit, 4), "margin": round(profit / price, 6)} def _estimate(cur, term, link_reader=None, *, allowed=True): """Choose the closest linked product name, then use that SKU's fee estimate and Odoo unit cost.""" empty = {"label": "Estimate", "comparable": None, "profit": None, "margin": None} if not allowed: return {**empty, "reason": "The estimate needs access to SKU links, Amazon fees and Odoo products."} if link_reader is None: return {**empty, "reason": "No comparable SKU is linked to an Odoo product."} cols = _columns(cur, "product_product") if not {"default_code", "name", "standard_price"} <= cols: return {**empty, "reason": "Odoo product costs have not synced yet."} query, best, best_key = _match_words(term), None, None active = ' AND COALESCE(TRY_CAST(active AS BOOLEAN), FALSE)' if "active" in cols else "" for page in link_reader(): links = [] for row in page: code = _text(row.get("odoo_product")) bracket = re.match(r"\[([^\]]+)\]", code) code = bracket.group(1).strip() if bracket else code if code and _text(row.get("sku")): links.append((row, code)) if not links: continue products = _bounded(cur, 'SELECT CAST(default_code AS VARCHAR), MIN(CAST(name AS VARCHAR)), ' 'MIN(TRY_CAST(standard_price AS DOUBLE)) FROM product_product ' 'WHERE CAST(default_code AS VARCHAR) IN (SELECT unnest(?::VARCHAR[]))' f'{active} GROUP BY 1 HAVING COUNT(*) = 1', [[code for _row, code in links]]) by_code = {code: (name, cost) for code, name, cost in products} for row, code in links: if code not in by_code or not query: continue name, cost = by_code[code] words = _match_words(name) overlap = len(query & words) if not overlap or overlap / len(query) < 0.5: continue key = (-overlap / len(query), -overlap / len(query | words), _text(row["sku"]), code) if best_key is None or key < best_key: units = _number(row.get("units_per_sku")) best_key = key best = {"sku": _text(row["sku"]), "odooCode": code, "name": name, "unitCost": _number(cost), "unitsPerSku": 1.0 if units is None else units} if best is None: return {**empty, "reason": "No comparable SKU is linked to an Odoo product."} result = {**empty, "comparable": best} if best["unitCost"] is None or best["unitCost"] < 0 or best["unitsPerSku"] <= 0: return {**result, "reason": "The closest SKU has no valid Odoo unit cost or pack size."} cols = _columns(cur, FEE_TABLE) if not {"sku", "your_price", "estimated_fee_total", "currency"} <= cols: return {**result, "reason": "Amazon fee estimates have not synced yet."} snapshot = 'CAST("_snapshot_date" AS VARCHAR)' if "_snapshot_date" in cols else "NULL::VARCHAR" fees = _bounded(cur, f'SELECT TRY_CAST(your_price AS DOUBLE), TRY_CAST(estimated_fee_total AS DOUBLE), ' f'CAST(currency AS VARCHAR), {snapshot} FROM {FEE_TABLE} WHERE trim(CAST(sku AS VARCHAR)) = ? ' f'QUALIFY dense_rank() OVER (ORDER BY {snapshot} DESC NULLS LAST) = 1', [best["sku"]]) unique = set(fees) if len(unique) != 1: return {**result, "reason": "The closest SKU has no single current Amazon fee estimate."} price, fee, currency, snapshot_date = next(iter(unique)) if (_number(price) is None or price <= 0 or _number(fee) is None or fee < 0 or not _text(currency)): return {**result, "reason": "The closest SKU has no valid price, currency or total fee."} cost = _number(best["unitCost"] * best["unitsPerSku"]) if cost is None: return {**result, "reason": "The closest SKU's pack cost is outside the supported number range."} values = _margin_values(price, fee, cost) if any(_number(value) is None for value in values.values()): return {**result, "reason": "The closest SKU's estimate is outside the supported number range."} return {**result, **values, "price": price, "fees": fee, "cost": cost, "currency": currency, "feeDate": snapshot_date, "reason": None, "basis": "Closest name match. Odoo cost per Amazon unit."} def validate_estimate(cur, estimate, link_reader=None, *, allowed=True): """Reconcile the named comparable and independently subtract its source fees and linked Odoo pack cost.""" sources = f"{PROFIT_TABLE}, {FEE_TABLE}, product_product" if not allowed or link_reader is None or estimate.get("margin") is None: result = _validation(None, "unavailable", sources, estimate.get("reason") or "No estimate is available.") estimate["validation"] = result return result comparable = estimate.get("comparable") or {} sku, code = comparable.get("sku"), comparable.get("odooCode") links = set() for page in link_reader(): for row in page: if _text(row.get("sku")) != sku: continue raw = _text(row.get("odoo_product")) linked_code = raw[1:].split("]", 1)[0].strip() if raw.startswith("[") and "]" in raw else raw units = _number(row.get("units_per_sku")) links.add((linked_code, 1.0 if units is None else units)) product_cols, fee_cols = _columns(cur, "product_product"), _columns(cur, FEE_TABLE) products, fees = [], [] if {"default_code", "name", "standard_price"} <= product_cols: active = ' AND COALESCE(TRY_CAST(active AS BOOLEAN), FALSE)' if "active" in product_cols else "" products = _bounded(cur, 'SELECT CAST(name AS VARCHAR), TRY_CAST(standard_price AS DOUBLE) ' f'FROM product_product WHERE CAST(default_code AS VARCHAR) = ?{active}', [code]) if {"sku", "your_price", "estimated_fee_total", "currency"} <= fee_cols: dated = 'CAST(_snapshot_date AS VARCHAR)' if "_snapshot_date" in fee_cols else "NULL::VARCHAR" fees = _bounded(cur, f'SELECT TRY_CAST(your_price AS DOUBLE), TRY_CAST(estimated_fee_total AS DOUBLE), ' f'CAST(currency AS VARCHAR), {dated} FROM {FEE_TABLE} WHERE trim(CAST(sku AS VARCHAR)) = ? ' f'ORDER BY {dated} DESC NULLS LAST', [sku]) latest = {tuple(row) for row in fees if row[3] == fees[0][3]} if fees else set() ok = False if len(products) == len(links) == len(latest) == 1: name, unit_cost = products[0] linked_code, units = next(iter(links)) price, fee, currency, date = next(iter(latest)) if (linked_code == code and _number(unit_cost) is not None and unit_cost >= 0 and units > 0 and _number(price) is not None and price > 0 and _number(fee) is not None and fee >= 0): # Decimal subtraction is independent of the serving calculator and its displayed inputs. pack_cost = Decimal(str(unit_cost)) * Decimal(str(units)) profit = Decimal(str(price)) - Decimal(str(fee)) - pack_cost margin = profit / Decimal(str(price)) ok = (comparable.get("name") == name and _same_number(comparable.get("unitCost"), unit_cost) and _same_number(comparable.get("unitsPerSku"), units) and _same_number(estimate.get("price"), price) and _same_number(estimate.get("fees"), fee) and _same_number(estimate.get("cost"), pack_cost, 0.0000001) and estimate.get("currency") == currency and estimate.get("feeDate") == date and _same_number(estimate.get("profit"), profit, 0.00005001) and _same_number(estimate.get("margin"), margin, 0.0000005001)) result = _validation(bool(ok), "matched_source_observation" if ok else "invalid", sources, "Matches linked SKU, Amazon fee and Odoo cost observations. Live Odoo validation is unavailable." if ok else "The estimate does not match its source fees and Odoo cost, so it is hidden.") if not ok: estimate.update(profit=None, margin=None, reason=result["detail"]) estimate["validation"] = result return result def reason_for(prior, current, pct, ours): """One plain sentence pair for an item and its proposal.""" tail = ("One of your ASINs is in its top 3." if ours else "None of your ASINs is in its top 3.") return f"Search frequency rank rose from {prior:,} to {current:,} ({pct:.0f}%). {tail}" # --------------------------------------------------------------------------- settings and owner def _is_service(name, rec): name = str(name or "").strip().lower() return (not name or name.startswith("qa") or name in SERVICE_ACCOUNTS or name.endswith("-admin") or bool(rec.get("platform_admin"))) def owner_account(st, tenant_key=None): """R31: the tenant's earliest active person Administrator, else its `admin` account.""" users = _doc(st, "users") want = str(tenant_key or "").strip().lower() admins = [(str(name), rec) for name, rec in users.items() if isinstance(rec, dict) and rec.get("active", True) and str(rec.get("role") or "").lower() == "admin" and (not want or str(rec.get("tenant") or "royal-imports").strip().lower() == want)] people = sorted(((n, r) for n, r in admins if not _is_service(n, r)), key=lambda item: ("@" not in item[0], not item[1].get("password_changed_at"), str(item[1].get("password_changed_at") or ""), item[0])) if people: return str(people[0][1].get("name") or people[0][0]) names = sorted(n for n, _r in admins) return "admin" if "admin" in names or not names else names[0] def settings(tenant_key, *, st=None): """The threshold, the department override and the default owner, with defaults filled in.""" st = _rt(tenant_key, st) doc = _doc(st, KEY_SETTINGS) out = {k: float(doc.get(k, v)) for k, v in DEFAULTS.items()} chosen = doc.get("departments") out["departments"] = list(chosen) if chosen else None out["defaultOwnerSetting"] = doc.get("default_owner") or None out["defaultOwner"] = out["defaultOwnerSetting"] or owner_account(st, tenant_key) out["statuses"] = list(STATUSES) out["dueDays"] = DUE_DAYS out["limit"] = LIMIT out["defaults"] = {"min_rise_pct": DEFAULTS["min_rise_pct"], "departments": "Every department where one of your ASINs is clicked."} return out def _editor(st, user): """The editing account's name from a username or a user dict. PermissionError for a viewer.""" name, rec = None, user if isinstance(user, str): name = user.strip() rec = _doc(st, "users").get(name) if not isinstance(rec, dict) or rec.get("active") is False: raise PermissionError(READ_ONLY) role = str(rec.get("role") or "user").strip().lower() if role == "viewer" or rec.get("can_edit") is False or rec.get("canEdit") is False: raise PermissionError(READ_ONLY) return str(rec.get("username") or name or rec.get("name") or "unknown") def set_settings(tenant_key, user=None, *, min_rise_pct=None, departments=None, default_owner=None, st=None): """Set the rise threshold (0 to below 100), the department list ([] restores the default) or the owner.""" st = _rt(tenant_key, st) if user is not None: _editor(st, user) changes = {} if min_rise_pct is not None: try: number = float(min_rise_pct) except (TypeError, ValueError): raise ValueError("The rise threshold must be a number.") from None if not 0 <= number < 100: raise ValueError("The rise threshold must be 0 or more and below 100.") changes["min_rise_pct"] = number if departments is not None: if isinstance(departments, str) or not isinstance(departments, (list, tuple)): raise ValueError("Departments must be a list of department names.") changes["departments"] = sorted({_text(d) for d in departments if _text(d)}) or None def _apply(doc): doc = doc if isinstance(doc, dict) else {} doc.update(changes) if default_owner is not None: doc["default_owner"] = str(default_owner).strip() or None return doc st.update(KEY_SETTINGS, _apply) return settings(tenant_key, st=st) # --------------------------------------------------------------------------- history def _index(st): return sorted({w for w in _doc(st, KEY_INDEX).get("weeks") or () if isinstance(w, str) and len(w) == 10 and iso_period(w)}) def weeks(tenant_key, *, st=None): """Every held week (period start), oldest first.""" return _index(_rt(tenant_key, st)) def _scope_grains(cur, period, departments): """Keep only source values constant across every row contributing to a term's saved rank.""" rows = _bounded(cur, f'SELECT {_TERM}, list(DISTINCT CAST(search_term AS VARCHAR)), ' f'list(DISTINCT CAST(department_name AS VARCHAR)) FROM {BA_TABLE} ' f'WHERE {_IN_PERIOD} AND {_IN_DEPTS} AND search_term IS NOT NULL GROUP BY 1', [period, list(departments)]) return {term: {key: values[0] for key, values in (("search_term", spellings), ("department_name", depts)) if len(values) == 1} for term, spellings, depts in rows} def _scope_week(doc, source_scope): """Apply the shared source evaluator before ranks or saved evidence enter a comparison.""" if source_scope is None: return doc, None terms = doc.get("terms") or {} grains = doc.get("scopeGrains") or {} records = [{"__scope_key": term, **grains.get(term, {})} for term in terms] scope = source_scope("ut_" + BA_TABLE, records) if scope is None: return doc, None visible, allowed = scope if not set(BA_NEED) <= visible: raise PermissionError("Search-term comparisons need the term, department, week and rank fields.") kept = {term: rank for term, rank in terms.items() if term in allowed} # Saved share evidence has no row provenance. Detail recomputes it or names its limit. return {**doc, "terms": kept, "evidence": {}, "scopeGrains": {t: grains.get(t, {}) for t in kept}}, visible def _scope_ownership(source_scope): """Ownership needs unrestricted identities and rows, not unrelated volume fields.""" if source_scope is not None: for source, identity in ((SQP_TABLE, "asin"), (LISTINGS_TABLE, "asin1")): scope = source_scope("ut_" + source, [{"__scope_key": "ownership"}]) if scope is not None and (identity not in scope[0] or "ownership" not in scope[1]): raise PermissionError("Department and ASIN ownership inference cannot apply this auxiliary source limit.") def week_point(tenant_key, week, *, st=None, source_scope=None): return _scope_week(_doc(_rt(tenant_key, st), WEEK_PREFIX + week), source_scope)[0] def _held_periods(cur): """Distinct dated Brand Analytics weeks, without inventing missing calendar weeks.""" return sorted({value[:10] for value, in _bounded( cur, f'SELECT DISTINCT CAST("_period_start" AS VARCHAR) FROM {BA_TABLE} ' 'WHERE "_period_start" IS NOT NULL') if iso_period(value)}) def record_week(tenant_key, *, st=None, now=None): """Backfill every held dated week and refresh the newest point without discarding retained history.""" st = _rt(tenant_key, st) now = now or _now() with _reader(tenant_key, st) as cur: source = _source(cur, st, fresh=True) if cur is None or not set(BA_NEED) <= _columns(cur, BA_TABLE): return {"state": "nothing", "problems": source["problems"]} periods = _held_periods(cur) if not periods: return {"state": "nothing", "problems": source["problems"]} chosen = _doc(st, KEY_SETTINGS).get("departments") departments = source["departments"] if source["period"] else ( sorted({_text(d) for d in chosen if _text(d)}) if chosen else _derived_departments(cur, _our_asins(cur))) if not departments: return {"state": "nothing", "problems": [NO_DEPARTMENTS]} src = {"departments": departments} newest = periods[-1] newest_count = 0 for week in periods: terms = week_ranks(cur, week, src["departments"]) evidence = {term: point for term, point in _share_rows(cur, src["departments"], period=week)} grains = _scope_grains(cur, week, src["departments"]) doc = {"week": week, "period": week, "departments": src["departments"], "terms": terms, "evidence": evidence, "scopeGrains": grains} old = _doc(st, WEEK_PREFIX + week) # Older mirror weeks contain watched terms only. Keep full observations already saved. if week != newest and old: for key in ("terms", "evidence", "scopeGrains"): doc[key] = {**(old.get(key) or {}), **doc[key]} doc["departments"] = sorted(set(old.get("departments") or ()) | set(src["departments"])) if any(old.get(key) != value for key, value in doc.items()): doc["capturedAt"] = _stamp(now) st.update(WEEK_PREFIX + week, lambda _old, point=doc: point) if week == newest: newest_count = len(terms) def _add(index): index = index if isinstance(index, dict) else {} index["weeks"] = sorted({*(index.get("weeks") or ()), week}) index["last_sync"] = _stamp(now) index.pop("last_error", None) return index st.update(KEY_INDEX, _add) return {"state": "recorded", "week": newest, "weeks": periods, "terms": newest_count} def on_amazon_sync(tenant_key, synced_tables, *, st=None, now=None): """Record held history after Brand Analytics sync, including repeated callback delivery.""" try: if TRIGGER not in set(synced_tables or ()): return {"state": "skipped"} return record_week(tenant_key, st=st, now=now) except Exception as exc: # noqa: BLE001 LOG.exception("Opportunity finder weekly history could not be saved") try: def _note(doc): doc = doc if isinstance(doc, dict) else {} doc["last_error"] = HISTORY_FAILED return doc _rt(tenant_key, st).update(KEY_INDEX, _note) except Exception: # noqa: BLE001 pass return {"state": "failed", "error": HISTORY_FAILED} # --------------------------------------------------------------------------- the finder def _prior_week(st, period, source_scope=None): """The newest held week before the mirror's newest period, as (week, {term: rank}).""" before = [w for w in _index(st) if period and w < period[:10]] if not before: return None, {} return before[-1], week_point(None, before[-1], st=st, source_scope=source_scope).get("terms") or {} def _next_sync(st, now=None): """Read the resident daily slot from durable config, with no invented schedule when it is absent.""" from zoneinfo import ZoneInfo cfg = _doc(st, "odoo_connector_config") if cfg.get("frozen") or cfg.get("syncEvery") == "manual": return None try: hour, minute = (int(part) for part in cfg["syncAt"].split(":")) clock = (now or _now()).astimezone(ZoneInfo(cfg["syncTz"])) due = clock.replace(hour=hour, minute=minute, second=0, microsecond=0) if due <= clock: due += dt.timedelta(days=1) return _stamp(due.astimezone(dt.timezone.utc)) except (AttributeError, KeyError, TypeError, ValueError): return None def find(tenant_key, *, st=None, opener=None, source_scope=None): """Every rising in-department term against the prior held week, sorted by rise. No cap (rule 1).""" st = _rt(tenant_key, st) _scope_ownership(source_scope) min_pct = settings(tenant_key, st=st)["min_rise_pct"] with _reader(tenant_key, st, opener) as cur: src = _source(cur, st) prior_week, prior = _prior_week(st, src["period"], source_scope) visible, allowed = None, None if source_scope is not None and src["period"] and src["departments"]: grains = _scope_grains(cur, src["period"], src["departments"]) scoped, visible = _scope_week({"terms": dict.fromkeys(grains), "scopeGrains": grains}, source_scope) if visible is not None: allowed = set(scoped["terms"]) prior = {term: rank for term, rank in prior.items() if term in allowed} src["departments"] = [row[0] for row in _bounded( cur, f'SELECT DISTINCT {_DEPT} FROM {BA_TABLE} WHERE {_IN_PERIOD} AND {_IN_DEPTS} ' f'AND {_TERM} IN (SELECT unnest(?::VARCHAR[])) ORDER BY 1', [src["period"], src["departments"], sorted(allowed)])] rows = [] if src["period"] and src["departments"] and prior: try: rows = _rising_rows(cur, src["period"], src["departments"], prior, min_pct) except _ReadFailed: src["problems"].append(READ_FAILED) problems = list(src["problems"]) if src["period"] and src["departments"] and not prior_week: cause = NOT_READY if _index(st) else NO_HISTORY upcoming = _next_sync(st) problems.append(cause + (f" Next sync {_day_words(upcoming)}." if upcoming else " The next sync date is unavailable.")) error = _doc(st, KEY_INDEX).get("last_error") if error: problems.append(HISTORY_FAILED) windows = _windows(st, src["period"], prior_week) found = [] for term, before, now_rank, rise, pct, top in rows: top3 = _top3_list(top, src["ours"]) if visible is not None: top3 = _mask_top3(top3, visible) mine = [t["asin"] for t in top3 if t["isOurs"]] found.append({"id": item_id(term), "term": term, "title": term, "priorRank": int(before), "rank": int(now_rank), "rise": int(rise), "risePct": round(float(pct), 2), "ours": bool(mine), "asin": mine[0] if mine else None, "top3": top3, "rising": True, "periodStart": src["period"], "priorPeriodStart": windows[1]["periodStart"] if len(windows) > 1 else None, "reason": reason_for(int(before), int(now_rank), float(pct), bool(mine))}) if visible is not None and not {"clicked_asin", "click_share_rank"} <= visible: found[-1].update(ours=None, asin=None, reason=found[-1]["reason"].split(". ", 1)[0] + ".") return {"week": src["period"][:10] if src["period"] else None, "period": src["period"], "priorWeek": prior_week, "departments": src["departments"], "derivedDepartments": src["derived"], "windows": windows, "periodType": src["periodType"], "rising": found, "problems": problems, "_allowedTerms": allowed, "_visible": visible} def _mask_top3(rows, visible): if not {"clicked_asin", "click_share_rank"} <= visible: return [] return [{**row, "clickShare": row.get("clickShare") if "click_share" in visible else None, "conversionShare": row.get("conversionShare") if "conversion_share" in visible else None} for row in rows] def _windows(st, period, prior_week): """The compared weeks, newest first: each window's week key and its `_period_start` text.""" out = [] if period: out.append({"window": "current", "week": period[:10], "periodStart": period, "iso": iso_period(period)}) if prior_week: text = _text(_doc(st, WEEK_PREFIX + prior_week).get("period")) or prior_week out.append({"window": "prior", "week": prior_week, "periodStart": text, "iso": iso_period(text)}) return out def _stored(st): return _doc(st, KEY_ITEMS).get("items") or {} def _row(fields, state): """One C4 item: the computed fields with the stored status, owner, due and note.""" return {**fields, "searchVolume": None, "searchVolumeReason": NO_VOLUME, "growthPct": None, "growthReason": NO_GROWTH, "trendMetric": "Search frequency rank", "status": state.get("status") or DEFAULT_STATUS, "owner": state.get("owner"), "due": state.get("due"), "note": state.get("note"), "sku": None} def _all_items(st, found): stored = _stored(st) out = [_row(f, stored.get(f["id"]) or {}) for f in found["rising"]] seen = {r["id"] for r in out} rest = [] for iid, state in stored.items(): if iid in seen or not isinstance(state, dict) or not state.get("status"): continue if found.get("_allowedTerms") is not None: # Historical prose can quote source values. Only freshly computed scoped rows are served. continue rest.append(_row({"id": iid, "term": state.get("term"), "title": state.get("term"), "priorRank": None, "rank": None, "rise": None, "risePct": None, "ours": bool(state.get("asin")), "asin": state.get("asin"), "top3": [], "rising": False, "reason": " ".join(x for x in (NOT_RISING, state.get("reason")) if x)}, state)) rest.sort(key=lambda r: _text(r["term"])) return out + rest def _page(rows, cursor, limit): try: start = int(cursor or 0) size = int(limit) except (TypeError, ValueError): raise ValueError("The page cursor is not valid.") from None if start < 0 or size < 1: raise ValueError("The page cursor is not valid.") end = start + size return rows[start:end], (str(end) if end < len(rows) else None) def _page_trends(st, page, source_scope=None): """Project complete rank history for page terms and apply the source wall to each dated point.""" backend = st._document_store() if callable(getattr(st, "_document_store", None)) else st terms = sorted({norm(row.get("term")) for row in page if row.get("term")}) trends = {term: [] for term in terms} if not terms: return trends for week in _index(st): name = WEEK_PREFIX + week key = st.store_key(name) if callable(getattr(st, "store_key", None)) else name values = backend.get_json_paths(key, [(kind, term) for term in terms for kind in ("terms", "scopeGrains")]) ranks = {term: values[2 * index] for index, term in enumerate(terms) if values[2 * index] is not None} grains = {term: values[2 * index + 1] or {} for index, term in enumerate(terms)} scoped, _visible = _scope_week({"terms": ranks, "scopeGrains": grains}, source_scope) for term, rank in scoped["terms"].items(): trends[term].append({"week": week, "rank": rank}) return trends def _readiness(st, found): """Describe recorded history and the configured next sync without inventing missing weeks or a schedule.""" held = len(_index(st)) return {"heldWeeks": held, "weeksNeeded": max(0, 2 - held), "nextSync": _next_sync(st), "readiness": "waiting" if held < 2 else "unavailable" if found.get("problems") else "ready", "columnSpecs": list(GRID_COLUMNS)} def items(tenant_key, user=None, *, status=None, cursor=None, limit=DEFAULT_LIMIT, st=None, opener=None, source_scope=None): """C4: rising terms and held opportunities, one page. Counts cover every item.""" if status is not None and status not in STATUSES: raise ValueError("Status must be one of: " + ", ".join(STATUSES) + ".") st = _rt(tenant_key, st) found = find(tenant_key, st=st, opener=opener, source_scope=source_scope) every = _all_items(st, found) if found.get("_visible") is not None: every = [{**row, "note": None} for row in every] counts = {s: 0 for s in STATUSES} for item in every: counts[item["status"]] = counts.get(item["status"], 0) + 1 wanted = [i for i in every if status is None or i["status"] == status] page, nxt = _page(wanted, cursor, limit) trends = _page_trends(st, page, source_scope) page = [{**row, "trendPoints": trends.get(norm(row.get("term")), [])} for row in page] return {"items": page, "cursor": nxt, "counts": counts, "total": len(wanted), "week": found["week"], "priorWeek": found["priorWeek"], "departments": found["departments"], "problems": found["problems"], "weeks": _index(st), "windows": found["windows"], "periodType": found["periodType"], **_readiness(st, found)} def _lookup(st, iid, found=None): """(computed fields or None, stored state or None, term) for one id. UnknownItem when neither holds it.""" state = _stored(st).get(iid) fields = None if found is not None: fields = next((f for f in found["rising"] if f["id"] == iid), None) term = (fields or {}).get("term") or (state or {}).get("term") if term is None: for week in reversed(_index(st)): term = next((t for t in (_doc(st, WEEK_PREFIX + week).get("terms") or {}) if item_id(t) == iid), None) if term is not None: break if fields is None and not (isinstance(state, dict) and state.get("status")) and term is None: raise UnknownItem(UNKNOWN_ITEM) return fields, state if isinstance(state, dict) else None, term def set_status(tenant_key, user, iid, status, *, note=None, owner=None, due=None, st=None, opener=None): """C4: move one item, optionally its owner and due date. Every change appends a history event.""" st = _rt(tenant_key, st) return _move(tenant_key, st, _editor(st, user), iid, status, note=note, owner=owner, due=due, opener=opener) def _move(tenant_key, st, who, iid, status, *, note=None, owner=None, due=None, opener=None): if status not in STATUSES: raise ValueError("Status must be one of: " + ", ".join(STATUSES) + ".") if due is not None: try: due = dt.date.fromisoformat(_text(due)[:10]).isoformat() except ValueError: raise ValueError("Due must be a date such as 2026-10-30.") from None fields, state, term = _lookup(st, iid, find(tenant_key, st=st, opener=opener)) if fields is None and state is None: raise UnknownItem(UNKNOWN_ITEM) owner = _text(owner) or None if status == "Opportunity" and owner is None and not (state or {}).get("owner"): owner = settings(tenant_key, st=st)["defaultOwner"] if status == "Opportunity" and due is None and not (state or {}).get("due"): due = (_now().date() + dt.timedelta(days=DUE_DAYS)).isoformat() at = _stamp() def _apply(doc): doc = doc if isinstance(doc, dict) else {} row = doc.setdefault("items", {}).setdefault(iid, {}) row["term"] = term row["status"] = status if fields is not None: row["reason"] = fields["reason"] row["asin"] = fields["asin"] event = {"at": at, "by": who, "event": "status", "status": status} if owner is not None: row["owner"] = event["owner"] = owner if due is not None: row["due"] = event["due"] = due if note is not None: row["note"] = str(note) if note: event["note"] = str(note) row.setdefault("history", []).append(event) return doc st.update(KEY_ITEMS, _apply) saved = _stored(st).get(iid) or {} return {"id": iid, **{k: v for k, v in saved.items() if k != "history"}} def proposals(tenant_key, *, st=None): """C7: every item in status Opportunity, each once.""" st = _rt(tenant_key, st) stored = {i: s for i, s in _stored(st).items() if isinstance(s, dict) and s.get("status") == "Opportunity"} if not stored: return [] live = {f["id"]: f for f in find(tenant_key, st=st)["rising"]} out = [] for iid in sorted(stored, key=lambda i: _text(stored[i].get("term"))): state = stored[iid] term = _text(state.get("term")) reason = (live.get(iid) or {}).get("reason") or " ".join( x for x in (NOT_RISING, state.get("reason")) if x) out.append({"source_kind": "opportunity_finder", "item_id": iid, "sku": None, "action": f'Serve the rising search term "{term}" with a listing, keywords or ads.', "reason": reason, "term": term, "owner": state.get("owner"), "due": state.get("due")}) return out def on_decision(tenant_key, iid, decision, reason, *, st=None): """C7: approved moves the item to Accepted, dismissed to Dismissed. The reason is kept as a note.""" if decision not in DECISIONS: raise ValueError("Decision must be approved or dismissed.") st = _rt(tenant_key, st) return _move(tenant_key, st, "Amazon operator", iid, DECISIONS[decision], note=_text(reason) or None) def item_detail(tenant_key, user, iid, *, st=None, opener=None, link_reader=None, margin_allowed=True, monthly_allowed=True, source_scope=None): """One item with its history, its weekly rank trend and its newest top 3.""" st = _rt(tenant_key, st) found = find(tenant_key, st=st, opener=opener, source_scope=source_scope) fields, state, term = _lookup(st, iid, found) if found.get("_allowedTerms") is not None and term not in found["_allowedTerms"]: raise UnknownItem(UNKNOWN_ITEM) row = next((r for r in _all_items(st, found) if r["id"] == iid), None) if row is None: row = _row({"id": iid, "term": term, "title": term, "rising": False, "reason": NOT_RISING, "asin": None, "top3": []}, state or {}) if found.get("_visible") is not None: if fields is None: raise PermissionError("This saved opportunity has no scoped comparison. Its stored reason cannot be masked safely.") row["note"] = None detail = term_detail(tenant_key, iid, st=st, _term=term, _found=found, opener=opener, link_reader=link_reader, margin_allowed=margin_allowed, monthly_allowed=monthly_allowed, source_scope=source_scope) row["trendPoints"] = detail["trend"] return {"item": row, "history": [] if found.get("_visible") is not None else list((state or {}).get("history") or ()), **detail} def term_detail(tenant_key, iid, *, st=None, _term=None, _found=None, opener=None, link_reader=None, margin_allowed=True, monthly_allowed=True, source_scope=None): """The weekly rank trend from history, oldest first, plus the newest week's top 3 clicked ASINs.""" st = _rt(tenant_key, st) term = _term found = _found if found is None and (term is None or source_scope is not None): found = find(tenant_key, st=st, opener=opener, source_scope=source_scope) found = found or {} if term is None: _fields, _state, term = _lookup(st, iid, found) if term is None: raise UnknownItem(UNKNOWN_ITEM) if found.get("_allowedTerms") is not None and term not in found["_allowedTerms"]: raise UnknownItem(UNKNOWN_ITEM) if found.get("_visible") is not None: trend = _page_trends(st, [{"term": term}], source_scope).get(term, []) row = next((row for row in found["rising"] if row["term"] == term), {}) reason = "Saved share evidence and margin estimates cannot apply this source limit." return {"id": iid, "term": term, "trend": trend, "top3": row.get("top3", []), "week": found["week"], "evidence": {"weekly": [], "monthly": [], "reason": reason}, "estimate": {"label": "Estimate", "comparable": None, "profit": None, "margin": None, "reason": reason}, "validation": {"evidence": _validation(None, "unavailable", BA_TABLE, reason), "estimate": _validation(None, "unavailable", FEE_TABLE, reason)}, **_readiness(st, found)} observations = _term_observations(st, term) trend = [{"week": week, "rank": held["rank"]} for week, held in observations.items() if held["rank"] is not None] top3, period = [], None evidence = {"weekly": [], "monthly": [], "demandBasis": "rank", "shareBasis": "Top clicked ASIN", "reason": "Weekly evidence is unavailable until brand analytics syncs."} estimate = {"label": "Estimate", "comparable": None, "profit": None, "margin": None, "reason": "The estimate is unavailable until the mirror can be read."} validation = {"evidence": _validation(None, "unavailable", BA_TABLE, evidence["reason"]), "estimate": _validation(None, "unavailable", FEE_TABLE, estimate["reason"])} with _reader(tenant_key, st, opener) as cur: src = _source(cur, st) period = src["period"] if period and src["departments"]: evidence = _evidence(cur, st, term, src["departments"], monthly_allowed=monthly_allowed, observations=observations) estimate = _estimate(cur, term, link_reader, allowed=margin_allowed) validation = {"evidence": validate_evidence(cur, st, term, src["departments"], evidence, monthly_allowed=monthly_allowed), "estimate": validate_estimate(cur, estimate, link_reader, allowed=margin_allowed)} if period and src["departments"] and {"clicked_asin", "click_share_rank"} <= _columns(cur, BA_TABLE): pos = 'TRY_CAST(trim(CAST("click_share_rank" AS VARCHAR)) AS INTEGER)' cols = _columns(cur, BA_TABLE) share = 'TRY_CAST(trim(CAST("click_share" AS VARCHAR)) AS DOUBLE)' if "click_share" in cols else "NULL" conv = ('TRY_CAST(trim(CAST("conversion_share" AS VARCHAR)) AS DOUBLE)' if "conversion_share" in cols else "NULL") rows = _bounded( cur, f'SELECT trim(CAST("clicked_asin" AS VARCHAR)), {pos}, {share}, {conv} FROM {BA_TABLE} ' f'WHERE {_IN_PERIOD} AND {_IN_DEPTS} AND {_TERM} = ? AND {pos} BETWEEN 1 AND 3 ' f'ORDER BY 2', [period, src["departments"], term]) top3 = _top3_list([{"asin": a, "pos": p, "share": s, "conv": c} for a, p, s, c in rows], src["ours"]) return {"id": iid, "term": term, "trend": trend, "top3": top3, "week": period[:10] if period else None, "evidence": evidence, "estimate": estimate, "validation": validation, **_readiness(st, found)} def validate(tenant_key, *, week=None, start=None, end=None, st=None, opener=None, source_scope=None): """Rule 8: the stored week's term count against a SQL distinct count of the mirror week, our departments.""" st = _rt(tenant_key, st) _scope_ownership(source_scope) with _reader(tenant_key, st, opener) as cur: src = _source(cur, st, fresh=True) period = src["period"] if period is None: return {"ok": False, "app_total": None, "independent_total": None, "week": None, "detail": " ".join(src["problems"]) or NOT_SYNCED} newest = period[:10] asked = _text(week)[:10] or None outside = ((asked is not None and asked != newest) or (start is not None and newest < _text(start)[:10]) or (end is not None and newest >= _text(end)[:10])) if outside: return {"ok": False, "app_total": None, "independent_total": None, "week": asked or newest, "detail": OLDER_WEEK} independent = 0 shown_departments = list(src["departments"]) if src["departments"]: allowed = None if source_scope is not None: grains = _scope_grains(cur, period, src["departments"]) scoped, visible = _scope_week({"terms": dict.fromkeys(grains), "scopeGrains": grains}, source_scope) if visible is not None: allowed = sorted(scoped["terms"]) shown_departments = [row[0] for row in _bounded( cur, f'SELECT DISTINCT {_DEPT} FROM {BA_TABLE} WHERE {_IN_PERIOD} AND {_IN_DEPTS} ' f'AND {_TERM} IN (SELECT unnest(?::VARCHAR[])) ORDER BY 1', [period, src["departments"], allowed])] independent = int(_bounded( cur, f'SELECT COUNT(DISTINCT {_TERM}) FROM {BA_TABLE} WHERE {_IN_PERIOD} AND {_IN_DEPTS}' + (f' AND {_TERM} IN (SELECT unnest(?::VARCHAR[]))' if allowed is not None else ''), [period, src["departments"], *([allowed] if allowed is not None else [])])[0][0]) doc = week_point(tenant_key, newest, st=st, source_scope=source_scope) if not doc: return {"ok": False, "app_total": None, "independent_total": independent, "week": newest, "detail": f"No stored week for {newest}. It is written at the next Amazon sync."} app_total = len(doc.get("terms") or {}) same = sorted(doc.get("departments") or ()) == sorted(src["departments"]) ok = app_total == independent and same and bool(src["departments"]) detail = (f"{app_total:,} search terms in the week of {newest} tie to the brand analytics week in your " f"departments." if ok else f"The week of {newest} holds {app_total:,} search terms, and brand analytics holds {independent:,} " f"in your departments." + ("" if same else " The department list changed after the sync.")) return {"ok": ok, "app_total": app_total, "independent_total": independent, "week": newest, "departments": shown_departments, "detail": detail} def _dashboard_window(start, end): first = dt.date.fromisoformat(str(start)) if start else None last = dt.date.fromisoformat(str(end)) if end else None if first and last and first >= last: raise ValueError("The start must be before the end.") return first, last def _dashboard_states(st): """Stream owned item states through the store's bounded member reader, never the whole document.""" backend = st._document_store() if callable(getattr(st, "_document_store", None)) else st key = st.store_key(KEY_ITEMS) if callable(getattr(st, "store_key", None)) else KEY_ITEMS yield from backend.iter_json_items(key, ("items",), batch_size=DEFAULT_LIMIT) def _dashboard_open_count(st): return sum(1 for page in _dashboard_states(st) for _iid, row in page if row.get("status") in ("Opportunity", "Accepted")) def _dashboard_volume_sql(cur): needed = {"search_query_data_search_query", "search_query_data_search_query_volume", "start_date", "end_date"} cols = _columns(cur, SQP_TABLE) if cur is not None and not isinstance(cur, _Unreadable) else set() if not needed <= cols: return None declared = 'upper(trim(CAST(_report_period AS VARCHAR)))' if "_report_period" in cols else "NULL::VARCHAR" return (f"SELECT lower(trim(CAST(search_query_data_search_query AS VARCHAR))) AS term, " "TRY_CAST(substr(CAST(start_date AS VARCHAR), 1, 10) AS DATE) AS first, " "TRY_CAST(substr(CAST(end_date AS VARCHAR), 1, 10) AS DATE) AS last, " f"{declared} AS declared, TRY_CAST(search_query_data_search_query_volume AS DOUBLE) AS volume " f"FROM {SQP_TABLE}") def _dashboard_volume_pages(cur, start, end): """Page term and source-period keys before projecting repeated ASIN query volumes.""" source = _dashboard_volume_sql(cur) if source is None: return offset = 0 while True: rows = _bounded(cur, f"WITH source AS ({source}), page AS (SELECT DISTINCT term, first, last " "FROM source WHERE term <> '' AND first IS NOT NULL AND last IS NOT NULL " "AND (?::DATE IS NULL OR last >= ?) AND (?::DATE IS NULL OR first < ?) " "ORDER BY term, first, last LIMIT ? OFFSET ?) " "SELECT p.term, p.first, p.last, min(s.volume), max(s.volume), count(*), count(s.volume), " "list(DISTINCT s.declared) FROM page p JOIN source s " "ON (s.term, s.first, s.last) = (p.term, p.first, p.last) " "GROUP BY 1, 2, 3 ORDER BY 1, 2, 3", [start, start, end, end, DEFAULT_LIMIT, offset]) if not rows: return yield rows offset += len(rows) def _dashboard_volume(cur, start, end): weekly, monthly, invalid = [], [], False for page in _dashboard_volume_pages(cur, start, end): for term, first, last, low, high, count, reported, declarations in page: declared = {v for v in declarations if v} following = (first.replace(day=28) + dt.timedelta(days=4)).replace(day=1) week = (last - first).days == 6 and first.weekday() == 6 and declared <= {"WEEK", "WEEKLY"} month = first.day == 1 and last == following - dt.timedelta(days=1) and declared <= {"MONTH", "MONTHLY"} if not week and not month: continue if count != reported or low != high or _number(low) is None or low < 0: invalid = True continue (weekly if week else monthly).append({"x": first.isoformat(), "value": low, "label": term, "period_end": last.isoformat(), "period": "weekly" if week else "monthly"}) period, points = ("weekly", weekly) if weekly else ("monthly", monthly) points.sort(key=lambda row: (row["x"], -row["value"], row["label"])) newest = max((row["x"] for row in points), default=None) value = sum(row["value"] for row in points if row["x"] == newest) if points and not invalid else None reason = ("Query volumes disagree across ASIN rows." if invalid else None if weekly else "Weekly search volume is unavailable. Monthly data is shown." if monthly else "Weekly search volume is unavailable.") return {"id": "search_volume", "label": "Weekly search volume" if weekly else "Monthly search volume" if monthly else "Weekly search volume", "value": value, "unit": "searches", "series": points if value is not None else [], "source_table": SQP_TABLE, "validated": False, "reason": reason, "period": period if points else None} def validate_dashboard(cur, st, figure, *, start=None, end=None): """Independently count owned open states or reconcile each volume with its raw SQP source rows.""" first, last = _dashboard_window(start, end) if figure["id"] == "open_opportunities": counts = {} for page in _dashboard_states(st): for _iid, row in page: state = row.get("status") counts[state] = counts.get(state, 0) + 1 expected = counts.get("Opportunity", 0) + counts.get("Accepted", 0) ok = figure.get("value") == expected and figure.get("series") == [] elif figure["id"] == "search_volume": source = _dashboard_volume_sql(cur) facts, okay, offset = {}, source is not None, 0 # This selection reads source rows afresh and never calls the serving volume reducer. if source is not None: while True: rows = _bounded(cur, f"WITH source AS ({source}) SELECT term, first, last, declared, volume " "FROM source WHERE term <> '' AND (?::DATE IS NULL OR last >= ?) " "AND (?::DATE IS NULL OR first < ?) ORDER BY term, first, last, declared, volume " "LIMIT ? OFFSET ?", [first, first, last, last, DEFAULT_LIMIT, offset]) if not rows: break for term, day, until, declared, volume in rows: if day is None or until is None: continue weekly = (until - day).days == 6 and day.weekday() == 6 and declared in (None, "", "WEEK", "WEEKLY") monthly = (day.day == 1 and (until + dt.timedelta(days=1)).day == 1 and (until - day).days in (27, 28, 29, 30) and declared in (None, "", "MONTH", "MONTHLY")) if not weekly and not monthly: continue key = (term, day.isoformat(), until.isoformat(), "weekly" if weekly else "monthly") held, consistent = facts.get(key, (volume, True)) facts[key] = (held, consistent and held == volume and _number(volume) is not None and volume >= 0) offset += len(rows) del rows use = "weekly" if any(k[3] == "weekly" for k in facts) else "monthly" points = [] for (term, day, until, period), (volume, consistent) in facts.items(): if not consistent: okay = False elif period == use: points.append({"x": day, "value": volume, "label": term, "period_end": until, "period": period}) points.sort(key=lambda row: (row["x"], -row["value"], row["label"])) newest = max((p["x"] for p in points), default=None) total = sum(p["value"] for p in points if p["x"] == newest) ok = bool(okay and points and figure.get("series") == points and figure.get("value") == total and figure.get("period") == use) else: ok = False source = KEY_ITEMS if figure["id"] == "open_opportunities" else SQP_TABLE ok = ok and figure.get("source_table") == source reason = figure.get("reason") if figure.get("value") is None else None return {"ok": ok, "reason": figure.get("reason") if ok else reason or "This figure does not tie to its source."} def dashboard(tenant_key, start=None, end=None, *, st=None, opener=None, volume_allowed=True): """C6 counts open owned opportunities and reports SQP volumes in their original source period.""" st = _rt(tenant_key, st) first, last = _dashboard_window(start, end) # Open is current Opportunity plus Accepted state, independent of the selected demand period. opened = {"id": "open_opportunities", "label": "Open opportunities", "value": _dashboard_open_count(st), "unit": "opportunities", "series": [], "source_table": KEY_ITEMS, "validated": False, "reason": None} with _reader(tenant_key, st, opener) as cur: volume = _dashboard_volume(cur if volume_allowed else None, first, last) if not volume_allowed: volume["reason"] = "Search volume is unavailable with your access limit." figures = [opened, volume] for figure in figures: check = validate_dashboard(cur if volume_allowed else None, st, figure, start=start, end=end) figure["validated"], figure["reason"] = check["ok"], check["reason"] if not check["ok"]: figure["value"], figure["series"] = None, [] return {"figures": figures} def validate_figure(tenant, figure, *, start=None, end=None, st=None, opener=None, volume_allowed=False): """Recheck owned states or admitted SQP rows with their original source periods.""" st = _rt(tenant, st) identity = figure.get("id") if identity not in ("open_opportunities", "search_volume") or (identity == "search_volume" and not volume_allowed): return {"ok": False, "id": identity, "reason": "This figure is unavailable with your access."} if identity == "open_opportunities": check = validate_dashboard(None, st, figure, start=start, end=end) else: with _reader(tenant, st, opener) as cur: check = validate_dashboard(cur, st, figure, start=start, end=end) return {"ok": check.get("ok") is True, "id": identity, "reason": check.get("reason")} def keys(tenant_key, *, st=None): """Every store key this App owns for the tenant.""" st = _rt(tenant_key, st) return [KEY_INDEX, KEY_SETTINGS, KEY_ITEMS, *(WEEK_PREFIX + w for w in _index(st))] def remove(tenant_key, *, st=None): """Rule 13: empty every App document, history and items included. No mirror table is touched.""" st = _rt(tenant_key, st) owned = keys(tenant_key, st=st) for key in owned: st.update(key, lambda _doc: {}) return {"removed": owned} remove_app_data = remove
"""Opportunity finder App: search terms in our departments whose search frequency rank rose week over week.
Sources (read only, through the tenant Amazon mirror, never written, no SP-API call):
amzn_brand_analytics search terms per department: search_frequency_rank, the top 3 clicked
ASINs (click_share_rank 1 to 3), newest _period_start in full
amzn_search_query_performance our ASINs (SQP reports our ASINs only)
amzn_listings our ASINs (asin1)
amzn_fba_fee_estimates comparable SKU price and total Amazon fees
product_product Odoo cost of the product named by the SKU profit link
Store documents (tenant runtime store, App owned; removing the App empties every one, rule 13):
opportunity_finder_index {weeks: [period_start, ...], last_sync, last_error}
opportunity_finder_week_<period_start> {week, period, capturedAt, departments, terms, evidence}
opportunity_finder_items {items: {id: {term, status, owner, due, note, reason, asin, history}}}
opportunity_finder_settings {departments, min_rise_pct, default_owner}
The mirror keeps the newest brand analytics week in full and older weeks for watched terms only
(`amazon_store._retain_weekly`). So a week's rank of an unwatched term survives only in this module's
week document, and `on_amazon_sync` writes that document at every sync.
A rank is a position: a lower number is searched more, so a term rises when its number falls.
The department filter and the rank comparison run in DuckDB over every row, with no cap (rule 1).
"""
import contextlib
import contextvars
import datetime as dt
import hashlib
import json
import logging
import math
import os
import re
import threading
from decimal import Decimal
from pathlib import Path
APP_LABEL = "Opportunity finder"
STATUSES = ("Rising", "Opportunity", "Accepted", "Served", "Dismissed")
DEFAULT_STATUS = "Rising"
DECISIONS = {"approved": "Accepted", "dismissed": "Dismissed"}
DEFAULT_LIMIT = 200
DUE_DAYS = 7
DEFAULTS = {"min_rise_pct": 10.0}
KEY_INDEX = "opportunity_finder_index"
KEY_SETTINGS = "opportunity_finder_settings"
KEY_ITEMS = "opportunity_finder_items"
WEEK_PREFIX = "opportunity_finder_week_"
BA_TABLE = "amzn_brand_analytics"
SQP_TABLE = "amzn_search_query_performance"
LISTINGS_TABLE = "amzn_listings"
FEE_TABLE = "amzn_fba_fee_estimates"
PROFIT_TABLE = "ut_amazon_sku_profit"
BA_NEED = ("department_name", "search_term", "search_frequency_rank", "_period_start")
#: The synced table key (no prefix) that starts a new history point.
TRIGGER = "brand_analytics"
NO_MIRROR = "The Amazon mirror is not on this server yet. It appears after the first sync."
#: Brand analytics holds no rows. Each cause below gets its own sentence, never this one over held rows.
NOT_SYNCED = "Brand analytics has not synced yet, so no search term can be compared."
#: Rows synced before weeks were dated. The next pass on such a table loads the two newest weeks.
NO_WEEK = "Brand analytics rows carry no week date, so the next Amazon sync loads the two newest weeks."
NO_FIELDS = "Brand analytics rows miss a field the finder reads, so no search term can be compared."
#: Amazon has not finished the newest week. `{day}` is its end date in words, such as October 24.
WAITING = "Amazon has not finished the week ending {day} yet, so the finder reads it at the next sync."
WAITING_NEXT = "Amazon has not finished the newest week yet, so the finder reads it at the next sync."
REFUSED = ("Amazon refused the brand analytics report. Check Brand Registry and the Brand Analytics role "
"in Seller Central.")
STORE_FAILED = "The last brand analytics report could not be stored, so the next Amazon sync loads it again."
#: `amazonStatus` values that are Amazon's own refusal, as `amazon_store._sync_problem` records them.
AMAZON_REFUSALS = ("FATAL", "CANCELLED", "NOT_OFFERED")
#: `_sync_state` markers of the brand analytics pass, matched bare or behind an account prefix.
WAITING_KEY = "amzn_waiting_brand_analytics"
REFUSED_KEY = "amzn_refused_brand_analytics"
READ_FAILED = ("The Amazon mirror could not be read just now, so no search term can be compared. "
"Try again in a minute.")
HISTORY_FAILED = "Weekly history could not be saved. The next Amazon sync will try again."
NO_VOLUME = "Brand analytics reports rank, not search volume."
NO_GROWTH = "Search volume growth needs dated volume reports."
GRID_COLUMNS = (
{"key": "term", "label": "Search term", "kind": "text", "width": 160, "action": "open"},
{"key": "searchVolume", "label": "Search volume", "kind": "number", "width": 140,
"reasonKey": "searchVolumeReason"},
{"key": "growthPct", "label": "Volume growth", "kind": "percent", "width": 140, "reasonKey": "growthReason"},
{"key": "trendPoints", "label": "Rank trend", "kind": "trend", "width": 100,
"x": "week", "y": "rank", "reverse": True},
{"key": "priorRank", "label": "Prior rank", "kind": "number", "width": 70, "optional": True},
{"key": "rank", "label": "Rank", "kind": "number", "width": 90, "priorKey": "priorRank"},
{"key": "risePct", "label": "Rank change", "kind": "percent", "width": 90},
{"key": "status", "label": "Status", "kind": "status", "width": 90},
{"key": "accept", "label": "Accept", "kind": "action", "width": 80, "action": "accept", "requiresEdit": True},
{"key": "top3", "label": "Top 3 clicked", "kind": "list", "width": 180, "optional": True},
{"key": "owner", "label": "Owner", "kind": "text", "width": 140, "optional": True},
)
BAD_PERIOD = "The newest Brand analytics week has no valid date, so no week can be compared."
NO_DEPARTMENTS = "No brand analytics row shows one of your ASINs yet, so the finder has no department to search."
NOT_READY = "The finder compares two weekly syncs. One week is held so far."
NO_HISTORY = "No weekly history yet. The first week is written at the next Amazon sync."
READ_ONLY = "Your account can view the opportunity finder but not change it."
UNKNOWN_ITEM = "That search term is not in the opportunity finder."