-- +goose Up

-- 用户足迹与收藏：跟账号走，不再只存在浏览器本地。
-- kind=history 由商品详情页浏览上报，kind=favorite 由收藏按钮切换。
CREATE TABLE IF NOT EXISTS listing_collections (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  listing_id BIGINT UNSIGNED NOT NULL,
  kind VARCHAR(16) NOT NULL COMMENT 'history=浏览记录, favorite=收藏',
  occurred_at DATETIME NOT NULL COMMENT '最近一次浏览或收藏时间',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_listing_collections_user_listing_kind (user_id, listing_id, kind),
  KEY idx_listing_collections_user_kind_time (user_id, kind, occurred_at),
  KEY idx_listing_collections_listing (listing_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户浏览足迹与收藏';

-- +goose Down

DROP TABLE IF EXISTS listing_collections;