Subversion Repositories SmartDukaan

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

-- External API v2: category specs, client->category exposure,

-- brand identifier. Apply BEFORE deploying the build that maps them.

--

-- STATUS: sections 1-5 + Mongo categoryId backfill APPLIED ON PROD

-- (hadb1 + shop2020 content Mongo) 2026-08-04..10. Staging (saholic-test)

-- NOT applied — run this file there before any staging deploy of >= r37204.

-- Spec-value derivation (POST /content/specs/derive?categoryId=10006/10007)

-- still pending on prod.

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

-- 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.update({_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

--

-- NOTE: legacy update(), NOT updateOne() — the prod mongo shell (3.x on

-- shop2020) has no updateOne on DBCollection; updateOne fails mid-script.

--

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

-- 5. FOFO sidebar menu: Technology > External API Clients ---------------

-- Read-only admin screen (/apiClientAccess, action_class api-client-access)

-- showing api_client rows with their store + category mappings.

-- Granted to Technology (cs.ticket_category 8, all escalation levels) and

-- HO admin (Business Intelligence 19, escalation 3).

-- APPLIED ON PROD hadb1 2026-08-04.

INSERT INTO auth.menu (display_text, description, parent_menu_id, sequence, action_class)

VALUES ('External API Clients', 'API clients with mapped stores and categories', 184, 10, 'api-client-access');

SET @menu_id = LAST_INSERT_ID();

INSERT INTO auth.menu_category (menu_id, category_id, escalation_type) VALUES

  (@menu_id, 8, '0'), (@menu_id, 8, '1'), (@menu_id, 8, '2'), (@menu_id, 8, '3'),

  (@menu_id, 19, '3');