"""Capture permitted gallery sources and adapt their facts to the image brief contract.""" from copy import deepcopy import json import math import re from core import pdp_images from harness.amazon_pictures import _size, allowed_url from modules import pdp_search_model as model TYPE_LABELS = {"main": "Main", "benefits": "Key benefits", "lifestyle": "Lifestyle", "size": "Size and scale", "details": "Details", "comparison": "Comparison", "how_to_use": "How to use", "in_the_box": "In the box", "variants": "Variants", "trust": "Trust"} TEXT_PARTS = ("title", "highlights", "bullets") BRIEF_PARTS = (*TEXT_PARTS, "desc", "details") DETAIL_FIELDS = { "item_dimensions": ("item_dimensions", "item_display_dimensions"), "item_weight": ("item_weight",), "package_dimensions": ("package_dimensions", "item_package_dimensions"), "package_weight": ("package_weight", "item_package_weight"), "number_of_items": ("number_of_items", "item_package_quantity"), "number_of_pieces": ("number_of_pieces",), "unit_count": ("unit_count",), "material": ("material",), "color": ("color", "colour"), "size": ("size",), "shape": ("shape", "item_shape"), "uses": ("uses", "recommended_uses_for_product"), } EXTRA_FACTS = ("height", "width", "length", "diameter", "scent", "wax", "burn_time", "special_feature", "product_benefit", "country_of_origin", "care_instructions") COUNT_CLAIM = re.compile(r"\b(\d+)\s*[- ]*(?:packs?|pk|pcs|pieces?|count|ct|units?|items?|candles?)\b|" r"\b(?:pack|set|box)\s+of\s+(\d+)\b", re.I) QUALIFIER = re.compile(r"\b(?:no|not|never|without|only|up to|at most|at least|approximately|about)\b") SHOT_TIMES = ("0 to 5 s", "5 to 15 s", "15 to 25 s", "25 to 35 s", "35 to 45 s") def capture(rt, asin, *, source_scope, picks_reader=None): """Read full facts and the raw catalog row through the same captured source permissions.""" context = model.listing_facts(rt, asin, source_scope=source_scope) with model._scoped_reader(rt, source_scope, tables=("amzn_catalog_items",)) as cur: catalog = model._source_listing(cur, "amzn_catalog_items", asin, []) if picks_reader is not None: context["research"] = research_sources(rt, asin, catalog, source_scope=source_scope, picks_reader=picks_reader) return context, catalog def _json(value, default): return json.loads(value) if isinstance(value, str) else value if value is not None else default def _largest(images, *, slots=None): chosen = {} for image in images: slot, link = image.get("variant"), image.get("link") if not slot or (slots is not None and slot not in slots): continue if not isinstance(link, str) or not allowed_url(link): raise ValueError("A gallery image link could not be read. Refresh the saved listing.") row = {"slot": slot, "img": link, "width": _size(image.get("width")) or None, "height": _size(image.get("height")) or None} if slot not in chosen or _dimensions(row) > _dimensions(chosen[slot]): chosen[slot] = row return [chosen[slot] for slot in sorted(chosen, key=lambda slot: (slot != "MAIN", slot))] def _dimensions(row): width, height = _size(row["width"]), _size(row["height"]) return max(width, height), width * height def catalog_images(catalog): """Choose the largest catalog rendition of each permitted Amazon gallery slot.""" groups = _json(catalog.get("images"), []) return _largest([image for group in groups for image in group.get("images", [])], slots=pdp_images.SLOTS) def _columns(cur, table): return {row[0] for row in cur.execute( "SELECT column_name FROM information_schema.columns WHERE table_name=?", [table]).fetchall()} def _latest_snapshot(cur, asin): table = "amzn_competitor_snapshots" if not {"asin", "snapshot_date", "image_variants"} <= _columns(cur, table): return None return cur.execute(f"SELECT snapshot_date,image_variants FROM {table} WHERE asin=? " "ORDER BY snapshot_date DESC LIMIT 1", [asin]).fetchone() def _current_links(variants): records = ([{"variant": slot, "link": value} if isinstance(value, str) else {**value, "variant": slot} for slot, value in variants.items()] if isinstance(variants, dict) else [{"variant": item} if isinstance(item, str) else item for item in variants]) current = {} for row in records: links = current.setdefault(row["variant"], set()) if row.get("link"): links.add(row["link"]) return current def _current_records(cur, asin, snapshot): current = _current_links(_json(snapshot[1], [])) if not current: return [] clauses, params, exact = [], [asin, snapshot[0]], [] for slot, links in current.items(): clause = "variant=?" params.append(slot) if links: clause += " AND link IN (" + ",".join("?" for _ in links) + ")" params.extend(sorted(links)) exact.append(slot) clauses.append("(" + clause + ")") # Explicit current links can include unchanged renditions first stored on different days. keep = "variant IN (" + ",".join("?" for _ in exact) + ") OR " if exact else "" query = ("WITH selected AS (SELECT variant,link,width,height,snapshot_date, " "MAX(snapshot_date) OVER (PARTITION BY variant) AS latest FROM amzn_competitor_images " "WHERE asin=? AND snapshot_date<=? AND (" + " OR ".join(clauses) + ")) " "SELECT variant,link,width,height FROM selected WHERE " + keep + "snapshot_date=latest " "ORDER BY variant,link LIMIT 256 OFFSET ?") rows, offset = [], 0 while True: result = cur.execute(query, [*params, *exact, offset]) names = [field[0] for field in result.description] batch = result.fetchall() rows.extend(dict(zip(names, row)) for row in batch) if len(batch) < 256: return rows offset += len(batch) def _stored_gallery(cur, asin): snapshot = _latest_snapshot(cur, asin) if snapshot is None or not {"asin", "variant", "link", "width", "height", "snapshot_date"} <= _columns( cur, "amzn_competitor_images"): return [] return _largest(_current_records(cur, asin, snapshot)) def _listing_updated(cur, asin, skus): listing = model._source_listing(cur, "amzn_listing_items", asin, skus) summaries = _json(listing.get("summaries"), []) return max((str(summary["lastUpdatedDate"]) for summary in summaries if summary.get("lastUpdatedDate")), default=None) def research_sources(rt, asin, catalog, *, source_scope, picks_reader): """Read Setup selections once and retain every current permitted gallery image.""" picks = list(dict.fromkeys(pick["asin"] for pick in picks_reader(rt, asin) if pick["asin"] != asin)) tables = ("amzn_listings", "amzn_listing_items", "amzn_competitor_snapshots", "amzn_competitor_images") with model._scoped_reader(rt, source_scope, tables=tables) as cur: listing = model._source_listing(cur, "amzn_listings", asin, []) updated = _listing_updated(cur, asin, [listing["seller_sku"]] if listing.get("seller_sku") else []) galleries = {asin: catalog_images(catalog), **{pick: _stored_gallery(cur, pick) for pick in picks}} stamps = {owner: updated if owner == asin else _listing_updated(cur, owner, []) for owner in galleries} rows = [{"asin": owner, **image, "last_updated": stamps[owner]} for owner, gallery in galleries.items() for image in gallery] return {"asin": asin, "picks": picks, "rows": rows, "last_updated": updated} def facts(context, asin, *, drafts=None, complete=False): """Attach provenance without inventing facts, replacing text only when a saved draft exists.""" result = {key: {"value": deepcopy(value), "source": "Live listing"} for key, value in context.get("pd", {}).get(asin, {}).items() if value} for part in TEXT_PARTS: draft = (drafts or {}).get(part) value = model.live_part(context, asin, part)["live"] if draft is None else draft for key in (part, *model.FACT_FIELDS[part]): result.pop(key, None) if value: result[part] = {"value": deepcopy(value), "source": f"Saved {part} draft" if draft is not None else "Live listing"} return _brief_facts(context, asin, drafts or {}, result) if complete else result def _brief_facts(context, asin, drafts, result): permitted = {*DETAIL_FIELDS, *EXTRA_FACTS} raw = context.get("pd", {}).get(asin, {}) sources = context.get("fact_sources", {}) facts = {key: deepcopy(value) for key, value in result.items() if key in EXTRA_FACTS} for key, aliases in DETAIL_FIELDS.items(): alias = next((name for name in aliases if raw.get(name)), None) if alias: facts[key] = {"value": deepcopy(raw[alias]), "source": "Product details"} if alias in sources: facts[key].update(value=_attribute_text(sources[alias]["value"]), provenance=deepcopy(sources[alias])) for part, label in (("title", "Title"), ("highlights", "Item highlights"), ("bullets", "Bullet"), ("desc", "Description")): draft = drafts.get(part) value = model.live_part(context, asin, part)["live"] if draft is None else draft if not value: continue facts[part] = {"value": deepcopy(value), "source": f"Saved {part} draft" if draft is not None else label} source = next((sources[name] for name in model.FACT_FIELDS[part] if name in sources), None) if source and draft is None: facts[part]["provenance"] = deepcopy(source) detail_draft = drafts.get("details") if detail_draft is not None: facts = {key: value for key, value in facts.items() if key not in permitted} for key, aliases in DETAIL_FIELDS.items(): alias = next((name for name in aliases if detail_draft.get(name)), None) if alias: value = deepcopy(detail_draft[alias]) facts[key] = {"value": _attribute_text(value), "source": "Product details draft", "provenance": {"part": "details", "field": alias, "value": value}} for key in EXTRA_FACTS: if key in facts and key in sources: facts[key].update(value=_attribute_text(sources[key]["value"]), source="Product details", provenance=deepcopy(sources[key])) if not facts.get("item_dimensions", {}).get("value"): facts["item_dimensions"] = {"value": None, "source": "Missing", "label": "Item dimensions"} return facts def _attribute_text(value): if isinstance(value, list): return "\n".join(filter(None, (_attribute_text(item) for item in value))) if isinstance(value, dict): if "value" in value: return " ".join(filter(None, (_attribute_text(value["value"]), str(value.get("unit") or "")))) return ", ".join(f"{key}: {_attribute_text(item)}" for key, item in value.items() if key not in ("marketplace_id", "language_tag")) return str(value) if value is not None else "" def _normalized(value): return " ".join(re.findall(r"\w+|[%./-]", _attribute_text(value).casefold())) def supported_claim(text, source_facts): """Accept sourced excerpts only, with explicit product counts checked against the canonical attribute.""" normalized = _normalized(text) if not normalized: return False count = model._product_count({key: value.get("value") for key, value in source_facts.items()}) if count and any((left or right) != count for left, right in COUNT_CLAIM.findall(text)): return False excerpts = [_normalized(fact["value"]) for fact in source_facts.values() if fact.get("value") is not None and fact.get("source") not in (None, "Missing")] if any((not QUALIFIER.search(excerpt) or normalized == excerpt) and " " + normalized + " " in " " + excerpt + " " for excerpt in excerpts): return True parts = [part.strip() for part in re.split(r"[,;\n]", text) if part.strip()] return len(parts) > 1 and all(supported_claim(part, source_facts) for part in parts) def _sum_known(values): return None if any(value is None for value in values) else sum(values) def money_searches(rt, asin, *, source_scope=None): """Read every search in the latest reported month, preserving unknown volume and purchase price.""" table, term, cols = model.TABLE, model.TERM, model.COLUMNS projection = (term, cols["vol"], cols["tp"], cols["pm"]) with model._scoped_reader(rt, source_scope, tables=(table,)) as cur: month = cur.execute(f"SELECT MAX(SUBSTR(CAST(start_date AS VARCHAR),1,7)) FROM {table} " "WHERE asin=? AND UPPER(_report_period)='MONTH'", [asin]).fetchone()[0] grouped, offset = {}, 0 while month is not None: batch = cur.execute(f"SELECT {','.join(projection)} FROM {table} WHERE asin=? " "AND UPPER(_report_period)='MONTH' AND SUBSTR(CAST(start_date AS VARCHAR),1,7)=? " f"ORDER BY {term},start_date,{','.join(projection[1:])} LIMIT 256 OFFSET ?", [asin, month, offset]).fetchall() for query, volume, purchases, price in batch: values = grouped.setdefault(query, {"volume": [], "money": []}) values["volume"].append(float(volume) if volume is not None else None) money = float(purchases) * float(price) if purchases is not None and price is not None else ( 0.0 if purchases is not None and float(purchases) == 0 else None) values["money"].append(money) if len(batch) < 256: break offset += len(batch) rows = [{"q": query, **{key: _sum_known(value) for key, value in metrics.items()}} for query, metrics in grouped.items()] return {"month": month, "rows": sorted(rows, key=lambda row: (-(row["money"] or 0), row["q"]))} def brief_row(slot, kind, authored, source_facts, searches): """Attach sourced copy and report-backed keywords to one requested slot and type.""" allowed = {row["q"]: row for row in searches["rows"]} words = list(dict.fromkeys(word for word in authored["keywords"] if word in allowed)) known = searches["month"] is not None why = {"words": words, "month": searches["month"], **{key: _sum_known([allowed[word][key] for word in words]) if known else None for key in ("volume", "money")}} return {"slot": slot, "type": kind, "scene": authored["scene"], "headline": authored["headline"] if supported_claim(authored["headline"], source_facts) else "", "callouts": [text for text in authored["callouts"] if supported_claim(text, source_facts)], "avoid": authored["avoid"], "keywords": words, "why": why} def _sql_totals(cur, asin, month, words): cols = model.COLUMNS volume, purchases, price = (f"CAST({cols[key]} AS DOUBLE)" for key in ("vol", "tp", "pm")) sums = (f"CASE WHEN COUNT(*)=COUNT({volume}) THEN COALESCE(SUM({volume}),0) END, " f"CASE WHEN COUNT(*)=SUM(CASE WHEN {purchases}=0 OR ({purchases} IS NOT NULL AND {price} IS NOT NULL) " f"THEN 1 ELSE 0 END) OR COUNT(*)=0 THEN COALESCE(SUM({purchases}*COALESCE({price},0)),0) END") placeholders = ",".join("?" for _ in words) or "NULL" return cur.execute(f"SELECT {sums},COUNT(DISTINCT {model.TERM}) FROM {model.TABLE} WHERE asin=? AND UPPER(_report_period)='MONTH' " f"AND SUBSTR(CAST(start_date AS VARCHAR),1,7)=? AND {model.TERM} IN ({placeholders})", [asin, month, *words]).fetchone() def _ties(actual, expected): if actual is None or expected is None: return actual is expected return isinstance(actual, (float, int)) and math.isclose(actual, float(expected), rel_tol=1e-9, abs_tol=1e-7) def validate(rt, asin, workflow, *, source_scope=None): """Tie every stored brief row to independent SQL sums for its exact ASIN, month and search words.""" rows = pdp_images.state(rt, workflow, asin)["rows"] checks = [] with model._scoped_reader(rt, source_scope, tables=(model.TABLE,)) as cur: for row in rows: why = row.get("why", {}) words, month = why.get("words", []), why.get("month") volume, money, count = _sql_totals(cur, asin, month, words) if month else (None, None, 0) expected = (volume, money) actual = [why.get(key) for key in ("volume", "money")] matched = (row.get("keywords") == words and len(words) == len(set(words)) and count == len(words) and bool(why) and all(_ties(a, e) for a, e in zip(actual, expected))) checks.append({"slot": row["slot"], "ok": matched, "actual": dict(zip(("volume", "money"), actual)), "expected": dict(zip(("volume", "money"), [float(v) if v is not None else None for v in expected]))}) return {"asin": asin, "workflow": workflow, "ok": bool(checks) and all(row["ok"] for row in checks), "rows": checks} def maker_row(row, asin, source_facts): """Map the stored type code to the prototype label without changing the prompt compiler.""" return {**deepcopy(row), "asin": asin, "role": TYPE_LABELS[row["type"]], "product": source_facts.get("title", {}).get("value") or ""}