pet-system/script/sql/business/fulfiller.sql

# 2026/02/27 履约者管理模块

# ============================
# 履约者信息表
# ============================
CREATE TABLE `pet_system`.`flf_fulfiller`
(
    `id`            bigint PRIMARY KEY NOT NULL COMMENT '主键ID',
    `user_id`       bigint COMMENT '关联系统用户ID',
    `name`          varchar(50)        NOT NULL COMMENT '姓名',
    `real_name`     varchar(50) COMMENT '身份证真实姓名',
    `phone`         varchar(20)        NOT NULL COMMENT '手机号/账号',
    `password`      varchar(100) COMMENT '登录密码',
    `gender`        tinyint  DEFAULT 1 COMMENT '性别 (1:男, 2:女)',
    `birthday`      date COMMENT '出生日期',
    `age`           int      DEFAULT 0 COMMENT '年龄',
    `avatar`        bigint COMMENT '头像',
    `id_card`       varchar(20) COMMENT '身份证号',
    `id_card_front` bigint COMMENT '身份证正面',
    `id_card_back`  bigint COMMENT '身份证背面',
    `id_card_expiry` date COMMENT '身份证有效期至',
    `service_types` varchar(255) COMMENT '服务类型(JSON数组, 如:宠物接送/上门喂遛/上门洗护)',
    `city_code`     varchar(20) COMMENT '服务城市编码',
    `city_name`     varchar(100) COMMENT '服务城市名称',
    `station_id`    bigint COMMENT '归属站点ID',
    `work_type`     varchar(20) DEFAULT 'part_time' COMMENT '工作性质 (full_time:全职, part_time:兼职)',
    `level_id`      bigint COMMENT '等级ID',
    `points`        int      DEFAULT 0 COMMENT '当前积分',
    `balance`       bigint   DEFAULT 0 COMMENT '账户余额(分)',
    `status`        varchar(20) DEFAULT 'resting' COMMENT '状态 (resting:休息, busy:接单中, disabled:禁用)',
    `auth_id`       tinyint  DEFAULT 0 COMMENT '是否身份证认证 (0:否, 1:是)',
    `auth_qual`     tinyint  DEFAULT 0 COMMENT '是否资质认证 (0:否, 1:是)',
    `qual_images`   text COMMENT '资质证书图片(JSON)',
    `order_count`   int      DEFAULT 0 COMMENT '服务单量',
    `reject_count`  int      DEFAULT 0 COMMENT '拒单量',
    `rating`        decimal(2, 1) DEFAULT 5.0 COMMENT '综合评分',
    `del_flag`      char(1)  DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `create_dept`   bigint(20) COMMENT '创建部门',
    `create_by`     bigint(20) COMMENT '创建者',
    `create_time`   datetime COMMENT '创建时间',
    `update_by`     bigint(20) COMMENT '更新者',
    `update_time`   datetime COMMENT '更新时间'
) ENGINE = innoDB COMMENT = '履约者信息表';

# ============================
# 履约者审核记录表
# ============================
CREATE TABLE `pet_system`.`flf_audit`
(
    `id`              bigint PRIMARY KEY NOT NULL COMMENT '主键ID',
    `fulfiller_id`    bigint COMMENT '履约者ID(通过后关联)',
    `type`            varchar(20)        NOT NULL COMMENT '审核类型 (register:入驻, qualification:资质变更)',
    `name`            varchar(50) COMMENT '申请人姓名',
    `phone`           varchar(20) COMMENT '申请人手机号',
    `gender`          tinyint DEFAULT 0 COMMENT '性别 (1:男, 2:女)',
    `birthday`        date COMMENT '出生日期',
    `work_type`       varchar(20) COMMENT '工作类型 (full_time:全职, part_time:兼职)',
    `city`            varchar(100) COMMENT '意向城市',
    `location`        varchar(255) COMMENT '意向地点',
    `real_name`       varchar(50) COMMENT '证件真实姓名',
    `id_card`         varchar(20) COMMENT '身份证号',
    `id_valid_date`   date COMMENT '证件有效期至',
    `id_card_front`   bigint COMMENT '身份证人像面',
    `id_card_back`    bigint COMMENT '身份证国徽面',
    `qualifications`  text COMMENT '专业资质图片(JSON数组)',
    `service_types`   varchar(255) COMMENT '申请服务类型(JSON数组)',
    `status`          tinyint DEFAULT 0 COMMENT '状态 (0:待审核, 1:已通过, 2:已驳回)',
    `audit_by`        bigint COMMENT '审核人ID',
    `audit_time`      datetime COMMENT '审核时间',
    `reject_reason`   varchar(500) COMMENT '驳回原因',
    `create_time`     datetime COMMENT '申请提交时间',
    KEY `idx_fulfiller_id` (`fulfiller_id`)
) ENGINE = innoDB COMMENT = '履约者审核记录表';

# ============================
# 等级规则表
# ============================
CREATE TABLE `pet_system`.`flf_level`
(
    `id`                  bigint PRIMARY KEY NOT NULL COMMENT '主键ID',
    `level`               int                NOT NULL COMMENT '等级数值(Lv.1, Lv.2...)',
    `name`                varchar(50)        NOT NULL COMMENT '等级名称',
    `icon`                bigint COMMENT '等级图标',
    `bg_image`            bigint COMMENT '等级背景图片',
    `min_points`          int     DEFAULT 0 COMMENT '最低积分',
    `max_points`          int     DEFAULT -1 COMMENT '最高积分(-1为无上限)',
    `holiday_multiplier`  decimal(3, 1) DEFAULT 1.0 COMMENT '节假日积分倍率',
    `upgrade_rules`       text COMMENT '升级规则配置(JSON: periodDays/minOrders/onTimeRate/complaintRate/maxViolations/accumulatedPoints)',
    `downgrade_type`      varchar(20) DEFAULT 'points' COMMENT '降级规则类型 (points:仅积分不足, strict:不满足即降级)',
    `deductions`          text COMMENT '扣分配置(JSON: late/violation/complaint)',
    `status`              tinyint DEFAULT 0 COMMENT '状态 (0:启用, 1:停用)',
    `sort`                int     DEFAULT 0 COMMENT '排序',
    `del_flag`            char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `create_dept`         bigint(20) COMMENT '创建部门',
    `create_by`           bigint(20) COMMENT '创建者',
    `create_time`         datetime COMMENT '创建时间',
    `update_by`           bigint(20) COMMENT '更新者',
    `update_time`         datetime COMMENT '更新时间'
) ENGINE = innoDB COMMENT = '等级规则表';

# ============================
# 等级权益表
# ============================
CREATE TABLE `pet_system`.`flf_right`
(
    `id`          bigint PRIMARY KEY NOT NULL COMMENT '主键ID',
    `name`        varchar(50)        NOT NULL COMMENT '权益名称',
    `icon`        bigint COMMENT '权益图标',
    `description` varchar(500) COMMENT '权益描述',
    `status`      tinyint DEFAULT 0 COMMENT '状态 (0:启用, 1:停用)',
    `del_flag`    char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
    `create_dept` bigint(20) COMMENT '创建部门',
    `create_by`   bigint(20) COMMENT '创建者',
    `create_time` datetime COMMENT '创建时间',
    `update_by`   bigint(20) COMMENT '更新者',
    `update_time` datetime COMMENT '更新时间'
) ENGINE = innoDB COMMENT = '等级权益表';

# ============================
# 等级-权益关联表
# ============================
CREATE TABLE `pet_system`.`flf_level_right`
(
    `level_id` bigint NOT NULL COMMENT '等级ID',
    `right_id` bigint NOT NULL COMMENT '权益ID',
    PRIMARY KEY (`level_id`, `right_id`)
) ENGINE = innoDB COMMENT = '等级-权益关联表';

# ============================
# 积分变动记录表
# ============================
CREATE TABLE `pet_system`.`flf_points_log`
(
    `id`            bigint PRIMARY KEY NOT NULL COMMENT '主键ID',
    `fulfiller_id`  bigint             NOT NULL COMMENT '履约者ID',
    `type`          varchar(20)        NOT NULL COMMENT '变动类型 (add:增加, reduce:扣除)',
    `biz_type`      varchar(20) COMMENT '业务类型 (order:订单, reward:奖励, punish:惩罚, adjust:手动调整)',
    `amount`        int                NOT NULL COMMENT '变动数值(正数)',
    `points_after`  int     DEFAULT 0 COMMENT '变动后积分',
    `reason`        varchar(500) COMMENT '变动原因',
    `operator_id`   bigint COMMENT '操作人ID',
    `create_time`   datetime COMMENT '创建时间',
    KEY `idx_fulfiller_id` (`fulfiller_id`)
) ENGINE = innoDB COMMENT = '积分变动记录表';

# ============================
# 余额变动记录表
# ============================
CREATE TABLE `pet_system`.`flf_balance_log`
(
    `id`            bigint PRIMARY KEY NOT NULL COMMENT '主键ID',
    `fulfiller_id`  bigint             NOT NULL COMMENT '履约者ID',
    `type`          varchar(20)        NOT NULL COMMENT '变动方向 (add:增加, reduce:减少)',
    `sub_type`      varchar(20) COMMENT '资金类型 (reward:奖励, punish:惩罚, salary:工资发放, withdraw:提现, other:其他)',
    `amount`        bigint             NOT NULL COMMENT '变动金额(分, 正数)',
    `balance_after` bigint  DEFAULT 0 COMMENT '变动后余额(分)',
    `reason`        varchar(500) COMMENT '备注说明',
    `operator_id`   bigint COMMENT '操作人ID',
    `create_time`   datetime COMMENT '创建时间',
    KEY `idx_fulfiller_id` (`fulfiller_id`)
) ENGINE = innoDB COMMENT = '余额变动记录表';

# ============================
# 奖惩记录表
# ============================
CREATE TABLE `pet_system`.`flf_reward_log`
(
    `id`            bigint PRIMARY KEY NOT NULL COMMENT '主键ID',
    `fulfiller_id`  bigint             NOT NULL COMMENT '履约者ID',
    `type`          varchar(10)        NOT NULL COMMENT '操作类型 (reward:奖励, punish:惩罚)',
    `target`        varchar(10)        NOT NULL COMMENT '关联项目 (points:积分, balance:余额)',
    `amount`        int                NOT NULL COMMENT '涉及数值',
    `reason`        varchar(500) COMMENT '奖惩原因',
    `operator_id`   bigint COMMENT '操作人ID',
    `operator_name` varchar(50) COMMENT '操作人名称',
    `create_time`   datetime COMMENT '创建时间',
    KEY `idx_fulfiller_id` (`fulfiller_id`)
) ENGINE = innoDB COMMENT = '奖惩记录表';