owl-admin/doc/系统设计/数据库设计/物理模型/分析后台设计增加/06业务员列表-增加的表.sql

-- Active: xiaoding_test@@192.168.110.85@3306@xiaoding_test
DROP TABLE IF EXISTS `salesmen`;
CREATE TABLE IF NOT EXISTS `salesmen` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '业务员编号',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户编号',
    `name` VARCHAR(255) NOT NULL COMMENT '姓名',
    `contact_phone` VARCHAR(11) NOT NULL COMMENT '联系电话',
    `contact_address` VARCHAR(255) NOT NULL COMMENT '联系地址',
    `id_card` VARCHAR(18) NOT NULL COMMENT '身份证',
    `id_card_front_photo` VARCHAR(255) NOT NULL COMMENT '身份证正面',
    `id_card_back_photo` VARCHAR(255) NOT NULL COMMENT '身份证反面',
    `id_card_hand_photo` VARCHAR(255) NOT NULL COMMENT '手持身份证',
    `activity_area` VARCHAR(255) NOT NULL COMMENT '活动区域',
    `invite_code` VARCHAR(50) NOT NULL COMMENT '邀请码',
    `audit_time` TIMESTAMP NULL DEFAULT NULL COMMENT '审核时间',
    `auditor` VARCHAR(50) DEFAULT NULL COMMENT '审核人',
    `audit_remark` VARCHAR(255) DEFAULT NULL COMMENT '审核回馈',
    `audit_status` ENUM('待审核', '审核通过', '审核拒绝') NOT NULL DEFAULT '待审核' COMMENT '审核状态',
    `state` ENUM('开启', '关闭') NOT NULL DEFAULT '开启' COMMENT '状态',
    `created_at` TIMESTAMP NULL DEFAULT NULL COMMENT '创建时间',
    `updated_at` TIMESTAMP NULL DEFAULT NULL COMMENT '更新时间',
    `deleted_at` TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_salesmen_user_id` FOREIGN KEY (`user_id`) REFERENCES `member_users` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '业务员表';

DROP TABLE IF EXISTS `salesmen_change_records`;
CREATE TABLE IF NOT EXISTS `salesmen_change_records` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '业务员审核记录编号',
    `salesman_id` BIGINT UNSIGNED NOT NULL COMMENT '业务员编号',
    `change_before` TEXT NOT NULL COMMENT '变更前',
    `change_after` TEXT NOT NULL COMMENT '变更后',
    `changer` VARCHAR(255) NOT NULL COMMENT '变更人',
    `created_at` TIMESTAMP NULL DEFAULT NULL COMMENT '创建时间',
    `updated_at` TIMESTAMP NULL DEFAULT NULL COMMENT '更新时间',
    `deleted_at` TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_change_records_salesman_id` FOREIGN KEY (`salesman_id`) REFERENCES `salesmen` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '业务员变更记录表';

-- 技师报警表
DROP TABLE IF EXISTS `coach_alerts`;
CREATE TABLE IF NOT EXISTS `coach_alerts` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '技师报警编号',
    `coach_id` bigint unsigned NOT NULL COMMENT '技师编号',
    `order_id` bigint unsigned NOT NULL COMMENT '订单编号',
    `alert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '报警时间',
    `alert_type` varchar(255) NOT NULL COMMENT '报警类型',
    `alert_reason` varchar(255) NOT NULL COMMENT '报警原因',
    `alert_status` varchar(255) NOT NULL DEFAULT 'PENDING' COMMENT '报警状态',
    `created_at` timestamp NULL DEFAULT NULL COMMENT '创建时间',
    `updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
    `deleted_at` timestamp NULL DEFAULT NULL COMMENT '删除时间',
    PRIMARY KEY (`id`),
    KEY `idx_coach_id` (`coach_id`),
    KEY `idx_order_id` (`order_id`),
    CONSTRAINT `fk_alerts_coach_id` FOREIGN KEY (`coach_id`) REFERENCES `coach_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_alerts_order_id` FOREIGN KEY (`order_id`) REFERENCES `order_infos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '技师报警表';

DROP TABLE IF EXISTS `coach_change_records`;
CREATE TABLE IF NOT EXISTS `coach_change_records` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '技师变更记录编号',
    `order_id` BIGINT UNSIGNED NOT NULL COMMENT '订单编号',
    `coach_id_before` BIGINT UNSIGNED NOT NULL COMMENT '技师编号(变更前)',
    `coach_id_after` BIGINT UNSIGNED NOT NULL COMMENT '技师编号(变更后)',
    `change_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '变更时间',
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ccr_order_id` FOREIGN KEY (`order_id`) REFERENCES `order_infos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_ccr_coach_id_before` FOREIGN KEY (`coach_id_before`) REFERENCES `coach_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_ccr_coach_id_after` FOREIGN KEY (`coach_id_after`) REFERENCES `coach_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '技师变更记录表';