# Heilind SKU selection and product-page PDFs Find up to **25 different SKUs shared by `ecom_prod` and `ecom_conversion`** where conversion has substantially more populated attributes. Select across actual manufacturer data, then capture the corresponding live Heilind product pages. For example, `COC120X10149X` becomes: [https://www.heilind.com/coc120x10149x.html](https://www.heilind.com/coc120x10149x.html) **Important:** The database counts describe prod versus conversion. The PDFs show what is on the live website at capture time. They do **not** show conversion-only data unless that data has been published there. ## Windows quick start Install Python 3.10 or newer, extract this folder, and open a terminal in it. ```powershell py -m pip install -r requirements.txt py -m playwright install chromium ``` Edit `config.example.json`: - Set `connection.host`, `connection.port`, and `connection.user` for a read-only account with access to both databases on the same server. - Leave the password out of the file. The script prompts for it, or reads the environment variable named by `password_env`. - Set `ssl_ca` to your CA certificate path if your database requires certificate-verified TLS. JSON Windows paths need doubled backslashes, such as `C:\\certs\\ca.pem`. - Confirm how manufacturer data is stored. The script only auto-selects a manufacturer field when it finds exactly one candidate in the chosen location. It never guesses from the SKU prefix. First inspect the actual columns and indexes: ```powershell py heilind_sku_pdf.py --config config.example.json --inspect-schema ``` Then run selection and PDF capture: ```powershell py heilind_sku_pdf.py --config config.example.json ``` A new timestamped output folder is created. No source tables or indexes are created, updated, or deleted. Use the same database network/VPN access that your normal SQL client uses. ## Outputs | File | Contents | | --- | --- | | `selection_summary.html` | SKU, manufacturer, both attribute counts, net gain, increase, product link, and PDF status. | | `selection_summary.pdf` | Printable copy of the selection summary. | | `selection.json` | Selected SKU records, populated attribute-code lists, thresholds, scan scope, timestamps, URLs, and capture results. No database password. | | `product_pdfs/` | A separate PDF for each successfully captured product page. | | `all_selected_product_pages.pdf` | Summary plus all successfully captured product PDFs, with SKU bookmarks. Failed captures are not silently substituted. | | `html_snapshots/` | Optional rendered HTML snapshots when `save_html_snapshot` is true. External images/CSS are not downloaded into a self-contained HTML archive. | By default, product PDFs are **one continuous tall page** sized to the full document, not a screenshot of the visible viewport. Extremely long pages are paginated to avoid PDF page-size limits. Set `capture.pdf_layout` to `paged` if you prefer page breaks. Product PDFs preserve desktop page width; use your PDF viewer's fit-to-paper setting when printing. The combined PDF may contain mixed page sizes. ## What counts as a good difference? Defaults, all adjustable in `selection`: | Setting | Default | Meaning | | --- | ---: | --- | | `target_skus` | 25 | Desired number of separate SKUs. | | `min_added_attributes` | 5 | Conversion count minus production count must be at least 5. | | `min_percent_increase` | 50 | At least 50% more than production. | | `min_conversion_attributes` | 8 | Conversion must have at least 8 populated attributes. | | `max_per_manufacturer` | 3 | No more than 3 SKUs from a manufacturer group. | For example, **prod 8 / conversion 18** qualifies (+10, +125%). **Prod 20 / conversion 25** fails the default 50% test. For **prod 0**, the percentage is undefined, not infinite: only the absolute-gain and minimum-conversion tests apply. Within each database, a populated attribute means a distinct **attribute code** with at least one non-empty stored value. Multiple multiselect option IDs count as one attribute. Duplicate rows do not inflate the count. `0` and `False` count as real values. The configurable default empty strings/tokens are `""`, `"null"`, `"[]"`, and `"{}"`, ignoring surrounding whitespace and token case. The default excluded codes are SKU and common manufacturer/brand codes. Adjust this list if you want different fields counted, or add internal operational fields that should not count as enrichment. Attribute codes themselves are compared exactly; there is no cross-schema attribute mapping. Stored option IDs count as non-empty values. This selection does **not** resolve every option label or validate every value's correctness. Orphaned attribute IDs without a corresponding attribute definition are excluded and reported. A new schema can split or rename attributes, so count gain is an example-selection heuristic, not proof of accuracy, comparable completeness, or overall project improvement. ## Manufacturer setup Manufacturer grouping uses **conversion** data only. A SKU with missing or ambiguous manufacturer data is excluded. ### Manufacturer stored directly on `product_entity` If the actual column is `mfg_code`, replace the `manufacturer` block with: ```json "manufacturer": { "source": "column", "column": "mfg_code", "label_lookup": null } ``` Use the real column name from your schema, not this example if it differs. ### Manufacturer stored as an EAV attribute If the actual attribute code is `brand`: ```json "manufacturer": { "source": "attribute", "attribute_code": "brand", "label_lookup": null } ``` If that attribute stores a numeric ID, the script can still group by that ID **within conversion**. The summary labels it `Stored manufacturer key 123`; it does not invent a manufacturer name. Multiple different raw values for a product are treated as ambiguous. Alternate spellings or multiple keys for the same real manufacturer are not automatically reconciled. ### Optional readable manufacturer labels Only configure this after verifying the actual schema. For an EAV option table with `option_id`, `attribute_id`, and `value` columns: ```json "manufacturer": { "source": "attribute", "attribute_code": "brand", "label_lookup": { "table": "eav_attribute_option_value", "id_column": "option_id", "label_column": "value", "attribute_id_column": "attribute_id", "filters": {} } } ``` Set `id_column` to the actual referenced key (for example, `value_id` only if that is what the manufacturer value references). Set `filters` for a required locale or store if necessary, such as `{"store_id": 0}` **only if that column exists**. Ownership and ambiguous-label checks prevent accidental lookup against another attribute's options. This optional EAV lookup requires the owner attribute column in the same table. If your schema uses a separate option-owner bridge, leave `label_lookup` null and use stored keys, or have the lookup adapted to your exact schema. Do not remove the ownership condition just to make an ID join run. For a physical manufacturer column that references a manufacturer table, `label_lookup` can name that table's ID and label columns; `attribute_id_column` is not needed in column mode. ## Table and column mappings The defaults follow the queries in this conversation; their exact column names have **not** been verified against your server: ```json "schema": { "product_table": "product_entity", "entity_id": "entity_id", "sku": "sku", "value_table": "product_entity_attribute_value", "value_entity_id": "entity_id", "value_attribute_id": "attribute_id", "value": "value", "attribute_table": "eav_attributes", "attribute_id": "attribute_id", "attribute_code": "attribute_code" } ``` Add only the overrides you need to the config. If the databases differ, use: ```json "schema_overrides": { "prod": {"value": "your_actual_value_column"}, "conversion": {"value": "your_actual_conversion_value_column"} } ``` The selector treats the configured value column as stored scalar/option data; it does not join a separate attribute-value storage table automatically. If values are stored in several typed tables, adapt the retrieval first. ## Performance and sampling The script does not sort or compare all attribute rows in a catalog-wide SQL query. Instead it: 1. Checks schema and indexes before the scan. 2. Divides the conversion product integer-ID range into 20 segments. 3. Reads up to 250 products from each segment, then advances by ID in further rounds if needed. 4. Looks up matching production SKUs and retrieves values only for that batch of shared products. 5. Counts attributes in Python and keeps the strongest qualifying examples per manufacturer. 6. Chooses one example per manufacturer before selecting second or third examples. Default scan ceiling: **50,000 conversion products**. It stops earlier when a completed range pass supplies enough qualifying examples. This is a deterministic search for useful examples, **not a random sample, global top-25 list, or representative measure of enrichment coverage**. Sparse ID ranges or clustering can still affect manufacturer coverage. If fewer than 25 qualify, it reports the shortfall without weakening the criteria. Increase `scan_limit`, change thresholds, or correct manufacturer mapping as appropriate. Setting `stop_when_enough` to false searches up to the configured scan limit even after enough examples exist. Reduce `batch_size` to 100 if individual batches are slow. Required in both databases: - Integer product `entity_id` with a single-column unique/primary index. - A usable full-length leading index on product `sku`. - A usable leading index on the value table's product `entity_id` column. The script **stops if required indexes are absent**; it does not create them. Ask the DBA to review indexes using the inspection output. SKU uniqueness is checked among rows encountered during the scan. SKU matching is case-insensitive in Python after the database lookup; this assumes case variants are not separate products. Attribute codes remain exact. Each batch uses a short read-only transaction. The report records the scan start/end and per-SKU measurement time. It is not one catalog-wide frozen snapshot, and in-flight imports can change later results. Run against a stable replica/snapshot or outside import activity when you need reproducible evidence. The database account needs SELECT access to the named tables and permission for normal session timeout/read-only transaction settings; no write grants are needed. ## Review selection before opening product pages ```powershell py heilind_sku_pdf.py --config config.example.json --select-only ``` Review the generated HTML. Then capture that exact saved selection without another database scan: ```powershell py heilind_sku_pdf.py --config config.example.json --capture-only "C:\Workspace\heilind_sku_capture_YYYYMMDD_HHMMSS\selection.json" ``` Replace the example folder with the actual generated folder. This also resumes an interrupted capture run, skipping successfully saved PDFs that still open correctly. Failed captures are retried only when you explicitly run it again. Selected URLs are retained from the manifest; changing `url_template` does not rewrite previously selected URLs. ## Whole-page capture and limitations The browser uses screen styles, opens native HTML `details` elements, scrolls from top to bottom to trigger lazy loading, waits for fonts/images, and prints every page. `capture.content_selector` defaults to `main`; change it to the site's real product-content container if needed. If the site lacks a `main` element, use a verified product selector or `body` after checking the page manually. Hidden tabs, custom collapsed specification panels, consent overlays, and nested scrolling containers can need site-specific handling. `expand_selectors` accepts **explicit selectors for read-only product-content controls only**. Leave it empty until the actual selectors have been checked. Do not add checkout, cart, login, consent, or account-action selectors. No arbitrary buttons are clicked automatically. A full-page PDF captures the rendered page; it cannot include content that the site never loaded or still keeps hidden in a tab. The script checks HTTP errors, obvious not-found/challenge pages, unexpected redirects, and nearly empty content. If the exact full SKU is absent from visible page text, or images fail to load, it marks the PDF `saved_needs_review`, not verified. These are heuristic checks; review the PDFs before using them as evidence. HTTP 401, 403, 429, or detected security challenges stop further site capture. The script does not bypass login, CAPTCHAs, rate limits, TLS validation, or other access controls. Use an approved workflow or ask the site owner if capture is blocked. Exit codes: `0` completed; `1` configuration/runtime error; `2` partial selection, failed/pending captures, or saved PDFs requiring review; `130` interrupted. ## Tests and verification scope Run the included offline unit tests: ```powershell py -m unittest -v test_heilind_sku_pdf.py ``` All 16 offline unit tests passed during development. They cover counting, exclusions, diversity, thresholds, sampling ranges, URL encoding, bound SQL parameters, and a simulated read-only scan. The included browser fixture test checks full-height PDF output, the last section of the page, native expanded details, pagination, all 25 summary entries, PDF merging, and rejected error pages. It intercepts every HTTP request with local test content; it does not read the live website. Run it separately after installing Chromium: ```powershell py test_browser_pdf.py --output local_pdf_test ``` Use a new test output directory each time. **This browser test could not run in the authoring environment because the required Chromium download timed out. PDF rendering and visual layout have not been verified here.** No production or conversion database has been accessed to test this package, and the example live product page could not be fetched from this environment. Schema, manufacturer mapping, and actual site-specific panel behavior must be checked on your machine. Implementation references: [Playwright PDF output](https://playwright.dev/python/docs/api/class-page#page-pdf), [PyMySQL connection options](https://pymysql.readthedocs.io/en/latest/modules/connections.html).