Subversion Repositories SmartDukaan

-- =====================================================================
-- External API v2: category specs, client->category exposure,
-- brand identifier. Apply BEFORE deploying the build that maps them.
-- =====================================================================

-- 1. Category spec vocabulary (keys governed per category) -------------
CREATE TABLE catalog.category_spec_definition (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id INT NOT NULL,
  spec_key VARCHAR(50) NOT NULL COMMENT 'machine key, e.g. ram',
  display_label VARCHAR(100) NOT NULL,
  position TINYINT NOT NULL COMMENT 'maps to SpecValue<N> upload column, 1-based',
  active TINYINT(1) NOT NULL DEFAULT 1,
  create_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cat_spec (category_id, spec_key),
  UNIQUE KEY uq_cat_pos (category_id, position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO catalog.category_spec_definition (category_id, spec_key, display_label, position) VALUES
  (10006, 'ram',    'RAM',              1),
  (10006, 'memory', 'Internal Storage', 2),
  (10007, 'ram',    'RAM',              1),
  (10007, 'memory', 'Internal Storage', 2);

-- 2. Client -> category exposure (mirrors fofo.api_client_store) -------
CREATE TABLE fofo.api_client_category (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  api_client_id INT UNSIGNED NOT NULL,
  category_id INT NOT NULL,
  create_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_client_category (api_client_id, category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- COOKIEE = api_client id 1
INSERT INTO fofo.api_client_category (api_client_id, category_id) VALUES (1, 10006), (1, 10007);

-- 3. Brand identifier per SKU (optional, bulk-uploader supplied) -------
ALTER TABLE catalog.item ADD COLUMN brand_identifier VARCHAR(100) NULL;

-- 4. Mongo categoryId backfill -----------------------------------------
-- Docs without categoryId are INVISIBLE to the category-scoped
-- catalogMaster; run this right after deploy. Generates one updateOne
-- per doc; pipe the output into the mongo shell on the content Mongo
-- (prod content Mongo is on shop2020, NOT 192.168.142.217).
--
--   mysql -N -e "SELECT CONCAT('db.siteContent.updateOne({_id:', c.id,
--     '},{\$set:{categoryId:NumberInt(', c.category_id, ')}});')
--     FROM catalog.catalog c" > /tmp/mongo_category_backfill.js
--   mongo <content-db> /tmp/mongo_category_backfill.js
--
-- NumberInt is REQUIRED: NumberLong would break Gson doc parsing.
-- categorySpecs derivation for existing 10006/10007 docs is done via the
-- one-time endpoint POST /content/specs/derive?categoryId=... (see
-- CategorySpecDerivationService).