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');