Blame | Last modification | View Log | RSS feed
-- ============================================================================-- Hot Deal brand - SCHEMA 2026-09-15---- Spec : docs/superpowers/specs/2026-09-10-hot-deal-brand-design.md-- Plan : docs/superpowers/plans/2026-09-11-hot-deal-brand.md---- "Hot Deal" becomes a real brand in catalog.brand. Brand membership replaces-- the model_hot_deal date window as the hot-deal flag, and model_hot_deal is-- reduced to per-SKU attributes (the five partner-facing pills) plus-- oem_brand, which feeds the new Solr field oem_brand_s.---- RUN ORDER - this file is applied in THREE separate passes:---- PASS 1 (steps 1-3) before hot_deal_brand_migration.sql-- PASS 2 (step 4) after hot_deal_brand_migration.sql-- PASS 3 (steps 5-6) after the new wars are DEPLOYED---- Do not run the whole file in one go. Each pass is marked below.---- Steps 1 and 2 are idempotent. The ALTER statements are not - re-running-- them errors with "Duplicate column name" / "Duplicate key name", which is-- harmless and expected. MySQL 5.7 has no ADD COLUMN IF NOT EXISTS.-- ============================================================================-- =========================== PASS 1 =======================================-- Run BEFORE hot_deal_brand_migration.sql-- ============================================================================-- ----------------------------------------------------------------------------- 1. The brand itself.-- logo_url is NOT NULL with no default, so it must be supplied.-- catalog.brand is latin1; 'Hot Deal' is pure ASCII, so there is no-- charset risk here (see the latin1 rupee/emoji class of bug).-- ---------------------------------------------------------------------------INSERT INTO catalog.brand (name, logo_url, logo_url_transparent)SELECT 'Hot Deal', '', NULLWHERE NOT EXISTS (SELECT 1 FROM catalog.brand WHERE name = 'Hot Deal');-- ----------------------------------------------------------------------------- 2. Visibility in the brand pickers.---- catalog.brand_category is a SEPARATE taxonomy from catalog.category:-- group 3 = handsets -> covers category 10006 Mobile Phone-- and 10007 Refurbished Mobile-- group 6 = accessories &-- other devices -> covers category 10024 Ear Buds-- and 14202 LED TV---- The in-scope SKUs span all four categories, so BOTH rows are required.-- Omitting either makes the brand invisible in that family's pickers.-- ---------------------------------------------------------------------------INSERT INTO catalog.brand_category (brand_id, category_id, active, `rank`)SELECT b.id, 3, 1, 99FROM catalog.brand bWHERE b.name = 'Hot Deal'AND NOT EXISTS (SELECT 1 FROM catalog.brand_category bcWHERE bc.brand_id = b.id AND bc.category_id = 3);INSERT INTO catalog.brand_category (brand_id, category_id, active, `rank`)SELECT b.id, 6, 1, 99FROM catalog.brand bWHERE b.name = 'Hot Deal'AND NOT EXISTS (SELECT 1 FROM catalog.brand_category bcWHERE bc.brand_id = b.id AND bc.category_id = 6);-- ----------------------------------------------------------------------------- 3. model_hot_deal gains oem_brand.---- Once catalog.brand reads 'Hot Deal' the original brand is gone from every-- queryable field. oem_brand preserves it and is the ONLY source of the-- Solr field oem_brand_s, which drives the brand chips inside the hot-deal-- listing.---- It deliberately does NOT go into Solr's brand_ss. brand_ss is-- multi-valued and feeds SolrService.brandExclusionFq, so an OEM value-- there would hide the SKU from a partner blocked on that OEM - the exact-- opposite of the intended behaviour, which is that channel and non-channel-- stock are different brands.-- ---------------------------------------------------------------------------ALTER TABLE catalog.model_hot_dealADD COLUMN oem_brand VARCHAR(64) NULLCOMMENT 'brand before the Hot Deal move; sole source of Solr oem_brand_s';-- ----------------------------------------------------------------------------- 3b. Optional link to the CHANNEL-side catalog entry for the same model.---- A hot-deal SKU is bought outside the OEM channel, but the same model-- usually also exists as normal channel stock under its real brand. This-- records that counterpart where ops can identify one.---- Nullable on purpose - plenty of hot-deal models have no channel twin, and-- a wrong mapping is worse than none, so it is never inferred. It is set-- only by an explicit choice in the admin.-- ---------------------------------------------------------------------------ALTER TABLE catalog.model_hot_dealADD COLUMN oem_catalog_id INT NULLCOMMENT 'catalog.catalog.id of the same model under its OEM brand, if one exists';-- =========================== PASS 2 =======================================-- Run AFTER hot_deal_brand_migration.sql-- ============================================================================-- ----------------------------------------------------------------------------- 4. One attributes row per catalog.---- model_hot_deal is now an attributes table, so catalog_item_id is its-- natural key. This is placed after the migration on purpose: if the-- migration left duplicates this ALTER fails loudly, which is the desired-- outcome - a silent duplicate would mean two conflicting pill rows for one-- SKU, and buildTags would pick an arbitrary one.-- ---------------------------------------------------------------------------ALTER TABLE catalog.model_hot_dealADD UNIQUE KEY uk_model_hot_deal_catalog (catalog_item_id);-- =========================== PASS 3 =======================================-- Run only AFTER the new wars are deployed. Both statements drop columns the-- running code must already have stopped using.-- ============================================================================-- ----------------------------------------------------------------------------- 5. The window and brand columns are meaningless once brand drives-- membership: there is no date window, no per-brand cap, and one brand.-- `activated` is separately dead - a leftover of the activated -> fresh-- rename (see model_hot_deal_activated_to_fresh.sql).-- ---------------------------------------------------------------------------ALTER TABLE catalog.model_hot_dealDROP COLUMN start_date,DROP COLUMN end_date,DROP COLUMN brand_id,DROP COLUMN activated;-- ----------------------------------------------------------------------------- 6. catalog.tag_listing.hot_deals had no readers before this work, and after-- it no writers either: the last one was-- V2FofoIndentController.hotdealUpdate (POST /v2/fofo/indent/confirm--- hotdeals-pause), removed in the same change.-- ---------------------------------------------------------------------------ALTER TABLE catalog.tag_listingDROP COLUMN hot_deals;-- ============================ ROLLBACK ====================================-- PASS 3:-- ALTER TABLE catalog.tag_listing ADD COLUMN hot_deals TINYINT(1) NOT NULL DEFAULT 0;-- ALTER TABLE catalog.model_hot_deal-- ADD COLUMN start_date DATE NOT NULL DEFAULT '2000-01-01',-- ADD COLUMN end_date DATE NOT NULL DEFAULT '2099-12-31',-- ADD COLUMN brand_id INT NOT NULL DEFAULT 0,-- ADD COLUMN activated TINYINT(1) NULL;-- (the old hot_deals values are NOT recoverable - they were the 31 legacy-- rows, all of which are represented in the migration freeze table)---- PASS 2:-- ALTER TABLE catalog.model_hot_deal DROP INDEX uk_model_hot_deal_catalog;---- PASS 1:-- ALTER TABLE catalog.model_hot_deal DROP COLUMN oem_brand;-- DELETE FROM catalog.brand_category-- WHERE brand_id = (SELECT id FROM catalog.brand WHERE name = 'Hot Deal');-- DELETE FROM catalog.brand WHERE name = 'Hot Deal';-- (only safe while no catalog/item row still points at the brand - run the-- data migration rollback first)-- ============================================================================