dotnet/coupon_center.sql

CREATE TABLE `coupon_center_platforms` (
  `id` varchar(64) NOT NULL COMMENT '平台编码，如 tb/vipshop/tb_flash',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT '平台名称',
  `icon_url` varchar(512) NOT NULL DEFAULT '' COMMENT '平台图标',
  `sort` int NOT NULL DEFAULT 0 COMMENT '排序，越小越靠前',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否启用：1启用，0停用',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status_sort` (`status`, `sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='领券中心平台表';

CREATE TABLE `coupon_center_activities` (
  `id` varchar(64) NOT NULL COMMENT '活动编码，如 act-tb-all',
  `platform_id` varchar(64) NOT NULL DEFAULT '' COMMENT '所属平台ID',
  `name` varchar(128) NOT NULL DEFAULT '' COMMENT '活动名称',
  `short_desc` varchar(255) NOT NULL DEFAULT '' COMMENT '活动短描述',
  `deeplink` varchar(1024) NOT NULL DEFAULT '' COMMENT '活动跳转链接，留空则自动拼 magicchain 链接',
  `sort` int NOT NULL DEFAULT 0 COMMENT '排序，越小越靠前',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否启用：1启用，0停用',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_platform_status_sort` (`platform_id`, `status`, `sort`),
  KEY `idx_status_sort` (`status`, `sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='领券中心活动表';

CREATE TABLE `coupon_center_coupons` (
  `id` varchar(64) NOT NULL COMMENT '券编码，如 cp-tb-max-666',
  `activity_id` varchar(64) NOT NULL DEFAULT '' COMMENT '所属活动ID',
  `value` varchar(64) NOT NULL DEFAULT '' COMMENT '券面额展示值',
  `expire_time` varchar(64) NOT NULL DEFAULT '' COMMENT '过期时间展示文案',
  `category` varchar(128) NOT NULL DEFAULT '' COMMENT '分类',
  `threshold` varchar(128) NOT NULL DEFAULT '' COMMENT '门槛说明',
  `coupon_status` varchar(32) NOT NULL DEFAULT 'available' COMMENT '业务状态，如 available/soldout/expired',
  `sort` int NOT NULL DEFAULT 0 COMMENT '排序，越小越靠前',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否启用：1启用，0停用',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_activity_status_sort` (`activity_id`, `status`, `sort`),
  KEY `idx_status_sort` (`status`, `sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='领券中心券表';

INSERT INTO `coupon_center_platforms` (`id`, `name`, `icon_url`, `sort`, `status`)
VALUES
  ('tb', '淘宝', 'http://doc.molilian.com/images/taobao.webp', 10, 1),
  ('vipshop', '唯品会', 'http://doc.molilian.com/images/tmall.webp', 20, 1),
  ('tb_flash', '淘宝闪购', 'http://doc.molilian.com/images/shangou.webp', 30, 1)
ON DUPLICATE KEY UPDATE
  `name` = VALUES(`name`),
  `icon_url` = VALUES(`icon_url`),
  `sort` = VALUES(`sort`),
  `status` = VALUES(`status`),
  `update_time` = CURRENT_TIMESTAMP;

INSERT INTO `coupon_center_activities` (`id`, `platform_id`, `name`, `short_desc`, `deeplink`, `sort`, `status`)
VALUES
  ('act-tb-all', 'tb', '淘宝领券中心', '全平台', 'magicchain://coupon-center/activity?activityId=act-tb-all', 10, 1),
  ('act-tb-home', 'tb', '淘宝居家券场', '居家日用', 'magicchain://coupon-center/activity?activityId=act-tb-home', 20, 1),
  ('act-vipshop-all', 'vipshop', '唯品会领券中心', '全平台', 'magicchain://coupon-center/activity?activityId=act-vipshop-all', 10, 1),
  ('act-vipshop-beauty', 'vipshop', '唯品会美妆券场', '美妆个护', 'magicchain://coupon-center/activity?activityId=act-vipshop-beauty', 20, 1),
  ('act-tb-flash-medicine', 'tb_flash', '淘宝闪购领券中心', '医药', 'magicchain://coupon-center/activity?activityId=act-tb-flash-medicine', 10, 1),
  ('act-tb-flash-food', 'tb_flash', '淘宝闪购夜宵券场', '美食', 'magicchain://coupon-center/activity?activityId=act-tb-flash-food', 20, 1)
ON DUPLICATE KEY UPDATE
  `platform_id` = VALUES(`platform_id`),
  `name` = VALUES(`name`),
  `short_desc` = VALUES(`short_desc`),
  `deeplink` = VALUES(`deeplink`),
  `sort` = VALUES(`sort`),
  `status` = VALUES(`status`),
  `update_time` = CURRENT_TIMESTAMP;

INSERT INTO `coupon_center_coupons` (`id`, `activity_id`, `value`, `expire_time`, `category`, `threshold`, `coupon_status`, `sort`, `status`)
VALUES
  ('cp-tb-max-666', 'act-tb-all', '最高666元', '3月31日23:59', '全平台', '无门槛', 'available', 10, 1),
  ('cp-tb-88', 'act-tb-all', '88元', '3月25日23:59', '数码家电', '满599元减88元', 'available', 20, 1),
  ('cp-tb-home-30', 'act-tb-home', '30元', '3月29日23:59', '居家', '满199元减30元', 'available', 10, 1),
  ('cp-tb-home-15', 'act-tb-home', '15元', '3月24日23:59', '日用百货', '满99元减15元', 'available', 20, 1),
  ('cp-vipshop-max-30', 'act-vipshop-all', '最高减30', '3月28日23:59', '全平台', '无门槛', 'available', 10, 1),
  ('cp-vipshop-80', 'act-vipshop-all', '80元', '3月26日23:59', '服饰', '满500元减80元', 'available', 20, 1),
  ('cp-vipshop-beauty-50', 'act-vipshop-beauty', '50元', '3月30日23:59', '美妆', '满299元减50元', 'available', 10, 1),
  ('cp-vipshop-beauty-20', 'act-vipshop-beauty', '20元', '3月27日23:59', '个护', '满159元减20元', 'available', 20, 1),
  ('cp-tb-flash-8', 'act-tb-flash-medicine', '8元', '3月22日23:59', '医药', '满8元减8元', 'available', 10, 1),
  ('cp-tb-flash-20', 'act-tb-flash-medicine', '20元', '3月23日23:59', '保健', '满99元减20元', 'available', 20, 1),
  ('cp-tb-flash-food-10', 'act-tb-flash-food', '10元', '3月21日23:59', '美食', '满39元减10元', 'available', 10, 1),
  ('cp-tb-flash-food-5', 'act-tb-flash-food', '5元', '3月20日23:59', '夜宵', '满20元减5元', 'available', 20, 1)
ON DUPLICATE KEY UPDATE
  `activity_id` = VALUES(`activity_id`),
  `value` = VALUES(`value`),
  `expire_time` = VALUES(`expire_time`),
  `category` = VALUES(`category`),
  `threshold` = VALUES(`threshold`),
  `coupon_status` = VALUES(`coupon_status`),
  `sort` = VALUES(`sort`),
  `status` = VALUES(`status`),
  `update_time` = CURRENT_TIMESTAMP;