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