-- ============================================
-- SPA管理系统 - 会员积分系统SQL
-- ============================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. 会员等级表
DROP TABLE IF EXISTS `spa_member_level`;
CREATE TABLE `spa_member_level` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `level_no` int NOT NULL COMMENT '等级编号',
  `level_name` varchar(50) NOT NULL COMMENT '等级名称',
  `upgrade_amount` decimal(10,2) DEFAULT 0.00 COMMENT '升级所需消费金额',
  `upgrade_orders` int DEFAULT 0 COMMENT '升级所需订单数',
  `discount_rate` decimal(5,2) DEFAULT 100.00 COMMENT '折扣率%',
  `points_rate` decimal(5,2) DEFAULT 1.00 COMMENT '积分倍率',
  `birthday_bonus` decimal(10,2) DEFAULT 0.00 COMMENT '生日礼金',
  `icon` varchar(500) DEFAULT NULL COMMENT '等级图标',
  `bg_color` varchar(20) DEFAULT NULL COMMENT '背景色',
  `status` tinyint DEFAULT 1 COMMENT '状态 1启用 2停用',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_level_no` (`level_no`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员等级表';

-- 2. 会员权益表
DROP TABLE IF EXISTS `spa_member_rights`;
CREATE TABLE `spa_member_rights` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `level_id` bigint unsigned NOT NULL COMMENT '会员等级ID',
  `right_type` tinyint NOT NULL COMMENT '权益类型 1优惠券 2积分 3服务 4其他',
  `right_name` varchar(100) NOT NULL COMMENT '权益名称',
  `right_desc` varchar(500) DEFAULT NULL COMMENT '权益描述',
  `right_value` varchar(200) DEFAULT NULL COMMENT '权益值',
  `sort` int DEFAULT 0 COMMENT '排序',
  PRIMARY KEY (`id`),
  KEY `idx_level_id` (`level_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员权益表';

-- 3. 会员升级记录表
DROP TABLE IF EXISTS `spa_member_upgrade_log`;
CREATE TABLE `spa_member_upgrade_log` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `customer_id` bigint unsigned NOT NULL COMMENT '客户ID',
  `old_level` tinyint DEFAULT 0 COMMENT '原等级',
  `new_level` tinyint NOT NULL COMMENT '新等级',
  `upgrade_type` tinyint DEFAULT 1 COMMENT '升级类型 1自动 2手动',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员升级记录表';

-- 4. 积分规则表
DROP TABLE IF EXISTS `spa_points_rule`;
CREATE TABLE `spa_points_rule` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `rule_name` varchar(100) NOT NULL COMMENT '规则名称',
  `rule_type` tinyint NOT NULL COMMENT '规则类型 1消费 2签到 3分享 4评价 5其他',
  `points_value` int NOT NULL COMMENT '积分值',
  `condition_value` decimal(10,2) DEFAULT NULL COMMENT '条件值',
  `daily_limit` int DEFAULT 0 COMMENT '每日限制次数 0不限',
  `status` tinyint DEFAULT 1 COMMENT '状态 1启用 2停用',
  PRIMARY KEY (`id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分规则表';

-- 5. 积分记录表
DROP TABLE IF EXISTS `spa_points_log`;
CREATE TABLE `spa_points_log` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `customer_id` bigint unsigned NOT NULL COMMENT '客户ID',
  `change_type` tinyint NOT NULL COMMENT '变动类型 1获得 2消费 3过期 4调整',
  `change_points` int NOT NULL COMMENT '变动积分',
  `before_points` int NOT NULL COMMENT '变动前积分',
  `after_points` int NOT NULL COMMENT '变动后积分',
  `related_id` bigint unsigned DEFAULT NULL COMMENT '关联业务ID',
  `related_type` varchar(50) DEFAULT NULL COMMENT '关联业务类型',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分记录表';

-- 6. 积分商品表
DROP TABLE IF EXISTS `spa_points_goods`;
CREATE TABLE `spa_points_goods` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `goods_name` varchar(100) NOT NULL COMMENT '商品名称',
  `goods_image` varchar(500) DEFAULT NULL COMMENT '商品图片',
  `goods_desc` text COMMENT '商品描述',
  `points_price` int NOT NULL COMMENT '积分价格',
  `stock` int DEFAULT 0 COMMENT '库存数量',
  `exchange_limit` int DEFAULT 0 COMMENT '兑换限制 0不限',
  `sort` int DEFAULT 0 COMMENT '排序',
  `status` tinyint DEFAULT 1 COMMENT '状态 1上架 2下架',
  PRIMARY KEY (`id`),
  KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分商品表';

-- 7. 积分兑换记录表
DROP TABLE IF EXISTS `spa_points_exchange`;
CREATE TABLE `spa_points_exchange` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `exchange_no` varchar(50) NOT NULL COMMENT '兑换单号',
  `customer_id` bigint unsigned NOT NULL COMMENT '客户ID',
  `goods_id` bigint unsigned NOT NULL COMMENT '商品ID',
  `goods_name` varchar(100) NOT NULL COMMENT '商品名称',
  `points_cost` int NOT NULL COMMENT '消耗积分',
  `quantity` int DEFAULT 1 COMMENT '兑换数量',
  `status` tinyint DEFAULT 1 COMMENT '状态 1待发货 2已发货 3已完成 4已取消',
  `express_company` varchar(100) DEFAULT NULL COMMENT '快递公司',
  `express_no` varchar(100) DEFAULT NULL COMMENT '快递单号',
  `receiver_name` varchar(100) DEFAULT NULL COMMENT '收货人',
  `receiver_phone` varchar(20) DEFAULT NULL COMMENT '收货电话',
  `receiver_address` varchar(500) DEFAULT NULL COMMENT '收货地址',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_exchange_no` (`exchange_no`),
  KEY `idx_customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分兑换记录表';

SET FOREIGN_KEY_CHECKS = 1;