DROP TABLE IF EXISTS `smile_volunteers`;
CREATE TABLE `smile_volunteers`
(
    `mid`            bigint UNSIGNED NOT NULL AUTO_INCREMENT,
    `uid`            varchar(64)          DEFAULT '' COMMENT '用户id',
    `project_id`     varchar(64)          DEFAULT '' COMMENT '活动id',
    `name`           varchar(32)          DEFAULT '' COMMENT '姓名',
    `img`            varchar(256)         DEFAULT '' COMMENT '头像',
    `id_card`        varchar(32)          DEFAULT '' COMMENT '证件号',
    `sex`            tinyint(2)           DEFAULT 0 COMMENT '性别[0-未知|1-男|2-女]',
    `nation`         varchar(16)          DEFAULT '' COMMENT '民族',
    `status`         tinyint(2)           DEFAULT 0 COMMENT '状态[0-待审核|1-审核通过|2-审核未通过]',
    `school`         varchar(64)          DEFAULT '' COMMENT '学校',
    `school_address` varchar(64)          DEFAULT '' COMMENT '学校地址',
    `specialty`      varchar(32)          DEFAULT '' COMMENT '专长',
    `specialty2`    varchar(32)          DEFAULT '' COMMENT '特长',
    `phone`          varchar(16)          DEFAULT '' COMMENT '手机号',
    `wx_num`         varchar(64)          DEFAULT '' COMMENT '微信号',
    `team_id1`      varchar(64)          DEFAULT '' COMMENT '申请组ID-1',
    `team_id2`      varchar(64)          DEFAULT '' COMMENT '申请组ID-2',
    `team_id3`      varchar(64)          DEFAULT '' COMMENT '申请组ID-3',
    `introduce`      varchar(500)         DEFAULT '' COMMENT '自我介绍',
    `comment`        varchar(255)         DEFAULT '' COMMENT '补充字段',
    `created_at`     datetime        NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at`     datetime        NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`mid`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_unicode_ci COMMENT = '志愿者-志愿者表'
  ROW_FORMAT = DYNAMIC;


DROP TABLE IF EXISTS `smile_volunteers_project`;
CREATE TABLE `smile_volunteers_project`
(
    `mid`        bigint UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id` varchar(64)          DEFAULT '' COMMENT '活动id',
    `title`      varchar(64)          DEFAULT '' COMMENT '项目名称',
    `time_start` datetime             DEFAULT NULL COMMENT '开始时间',
    `time_end`   datetime             DEFAULT NULL COMMENT '结束时间',
    `address`    varchar(256)         DEFAULT '' COMMENT '活动地址',
    `introduce`  varchar(1028)        DEFAULT '' COMMENT '活动介绍',
    `img`        varchar(256)          DEFAULT '' COMMENT '封面图',
    `status`        tinyint(2)         DEFAULT 1 COMMENT '状态[1-上线|0-下线]',
    `comment`    varchar(255)         DEFAULT '' COMMENT '补充字段',
    `created_at` datetime        NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` datetime        NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`mid`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_unicode_ci COMMENT = '志愿者-志愿者项目表'
  ROW_FORMAT = DYNAMIC;

DROP TABLE IF EXISTS `smile_project_team_relation`;
CREATE TABLE `smile_project_team_relation`
(
    `mid`        bigint UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id` varchar(64)          DEFAULT '' COMMENT '活动id',
    `team_id`    varchar(64)          DEFAULT '' COMMENT '职责组id',
    `comment`    varchar(255)         DEFAULT '' COMMENT '补充字段',
    `created_at` datetime        NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` datetime        NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`mid`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_unicode_ci COMMENT = '志愿者-项目关联职责表'
  ROW_FORMAT = DYNAMIC;

DROP TABLE IF EXISTS `smile_volunteers_team`;
CREATE TABLE `smile_volunteers_team`
(
    `mid`        bigint UNSIGNED NOT NULL AUTO_INCREMENT,
    `team_id`    varchar(64)          DEFAULT '' COMMENT '职责组id',
    `name`       varchar(64)          DEFAULT '' COMMENT '组名称',
    `introduce`  varchar(512)         DEFAULT '' COMMENT '组介绍',
    `comment`    varchar(255)         DEFAULT '' COMMENT '补充字段',
    `created_at` datetime        NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` datetime        NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`mid`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_unicode_ci COMMENT = '志愿者-项目职责组表'
  ROW_FORMAT = DYNAMIC;


DROP TABLE IF EXISTS `smile_volunteers_team_relation`;
CREATE TABLE `smile_volunteers_team_relation`
(
    `mid`        bigint UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id` varchar(64)          DEFAULT '' COMMENT '活动id',
    `team_id`    varchar(64)          DEFAULT '' COMMENT '职责组id',
    `uid`        varchar(64)          DEFAULT '' COMMENT '用户id',
    `comment`    varchar(255)         DEFAULT '' COMMENT '补充字段',
    `created_at` datetime        NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` datetime        NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`mid`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_unicode_ci COMMENT = '志愿者-项目职责组志愿者关系'
  ROW_FORMAT = DYNAMIC;