-- 지정판매소 PII 블라인드 인덱스 컬럼(정확일치 검색용) 추가 — HMAC-SHA256 값 저장(64 hex)
-- 실행: mysql -u jongryangje -p jongryangje_dev < writable/database/designated_shop_add_pii_bidx.sql
-- 멱등. 컬럼 추가 후, 암호화/백필은 spark 명령(pii:protect-designated-shops)으로 수행.

SET @db = DATABASE();

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='designated_shop' AND COLUMN_NAME='ds_tel_bidx')>0,
  'SELECT ''ds_tel_bidx exists''',
  'ALTER TABLE `designated_shop` ADD COLUMN `ds_tel_bidx` CHAR(64) NOT NULL DEFAULT '''' COMMENT ''전화 블라인드인덱스(HMAC)'' , ADD INDEX `idx_ds_tel_bidx` (`ds_tel_bidx`)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='designated_shop' AND COLUMN_NAME='ds_rep_name_bidx')>0,
  'SELECT ''ds_rep_name_bidx exists''',
  'ALTER TABLE `designated_shop` ADD COLUMN `ds_rep_name_bidx` CHAR(64) NOT NULL DEFAULT '''' COMMENT ''대표자명 블라인드인덱스(HMAC)'' , ADD INDEX `idx_ds_rep_name_bidx` (`ds_rep_name_bidx`)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 참고: 암호문(ENC:...)은 평문보다 길어짐. 아래 PII 컬럼이 짧으면 길이 확대 필요할 수 있음(확인 후 조정).
-- ALTER TABLE `designated_shop`
--   MODIFY `ds_rep_name` VARCHAR(255) NULL, MODIFY `ds_tel` VARCHAR(255) NULL,
--   MODIFY `ds_rep_phone` VARCHAR(255) NULL, MODIFY `ds_email` VARCHAR(255) NULL,
--   MODIFY `ds_va_account` VARCHAR(255) NULL;