Subversion Repositories SmartDukaan

-- =====================================================================
-- Third-party external API: client auth tables + delta-sync timestamps
-- =====================================================================
-- MUST be applied BEFORE deploying the build that maps update_timestamp
-- (Hibernate SELECTs the new columns).
--
-- Pre-flight sizing (decide plain ALTER vs pt-online-schema-change):
--   SELECT table_schema, table_name, table_rows, ROUND(data_length/1024/1024) mb
--   FROM information_schema.tables
--   WHERE (table_schema='catalog' AND table_name IN ('tag_listing','item','catalog'))
--      OR (table_schema='fofo' AND table_name='current_inventory_snapshot');
-- MySQL 5.7: ADD COLUMN is ALGORITHM=INPLACE (online DML) but a full table
-- rebuild. catalog.* tables are small; if current_inventory_snapshot is
-- multi-million rows / >~1GB use pt-osc off-peak.

-- 1. API client auth ---------------------------------------------------

CREATE TABLE fofo.api_client (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  api_key_hash CHAR(64) NOT NULL COMMENT 'lowercase hex SHA-256 of raw key',
  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_api_client_key_hash (api_key_hash),
  UNIQUE KEY uq_api_client_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fofo.api_client_store (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  api_client_id INT UNSIGNED NOT NULL,
  fofo_id INT NOT NULL,
  create_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_client_store (api_client_id, fofo_id),
  KEY idx_acs_fofo (fofo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Delta-sync update timestamps -------------------------------------
-- Existing rows get the ALTER-time value; acceptable because a partner's
-- first sync is always full (no updatedSince).
-- ON UPDATE CURRENT_TIMESTAMP fires only when a column value actually
-- changes; no-op UPDATEs do not bump it (correct for delta sync).
-- Hard DELETEs remain invisible to deltas -> partners advised to run a
-- weekly full sync.

ALTER TABLE catalog.tag_listing
  ADD COLUMN update_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ADD KEY idx_tl_update_ts (update_timestamp);

ALTER TABLE catalog.item
  ADD COLUMN update_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ADD KEY idx_item_update_ts (update_timestamp);

ALTER TABLE catalog.catalog
  ADD COLUMN update_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE fofo.current_inventory_snapshot
  ADD COLUMN update_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ADD KEY idx_cis_update_ts (update_timestamp);

-- 3. Mongo one-time backfill (run in mongo shell, CONTENT db, AFTER the
--    build that stamps lastModified in Mongo.insertOrUpdateById is live).
--    Docs missing lastModified are treated as always-included in deltas,
--    so deploy/backfill ordering is safe either way.
--
--   db.siteContent.updateMany(
--     { lastModified: { $exists: false } },
--     { $set: { lastModified: NumberLong(Date.now()) } }
--   )

-- 4. Client seeding template -------------------------------------------
-- Generate a raw key offline (e.g. openssl rand -hex 32), give it to the
-- partner once, store only the hash:
--
--   INSERT INTO fofo.api_client (name, api_key_hash)
--   VALUES ('<PARTNER_NAME>', SHA2('<raw-key>', 256));
--
--   INSERT INTO fofo.api_client_store (api_client_id, fofo_id)
--   SELECT ac.id, fs.id
--   FROM fofo.api_client ac
--   JOIN fofo.fofo_store fs ON fs.id IN (<fofo ids to expose>)
--   WHERE ac.name = '<PARTNER_NAME>';