Subversion Repositories SmartDukaan

-- ============================================================================

--  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', '', NULL

WHERE 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, 99

FROM catalog.brand b

WHERE b.name = 'Hot Deal'

  AND NOT EXISTS (SELECT 1 FROM catalog.brand_category bc

                   WHERE 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, 99

FROM catalog.brand b

WHERE b.name = 'Hot Deal'

  AND NOT EXISTS (SELECT 1 FROM catalog.brand_category bc

                   WHERE 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_deal

  ADD COLUMN oem_brand VARCHAR(64) NULL

  COMMENT '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_deal

  ADD COLUMN oem_catalog_id INT NULL

  COMMENT '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_deal

  ADD 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_deal

  DROP 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_listing

  DROP 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)

-- ============================================================================