-- 商户 H5 人员账号 openid（member.type=3），支持同一客户多人员独立登录
SET @db = DATABASE();

SET @sql = IF(
  (SELECT COUNT(*) FROM information_schema.statistics
   WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'member' AND INDEX_NAME = 'idx_member_openid') = 0,
  'ALTER TABLE `member` ADD INDEX `idx_member_openid` (`openid`)',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 将历史 yw_customer.openid 迁移到默认联系人 member.openid
UPDATE `member` m
INNER JOIN `yw_customer` c ON c.member_id = m.id AND c.isdeleted = 0
SET m.openid = c.openid, m.edit_date = NOW()
WHERE m.isdeleted = 0
  AND m.type = 3
  AND c.openid IS NOT NULL
  AND c.openid != ''
  AND (m.openid IS NULL OR m.openid = '');