hospital-accompaniment-admin/script/sql/baifc-0928.sql

DROP TABLE IF EXISTS `tb_operating_phone`;
CREATE TABLE `tb_operating_phone` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
    `phone_number` varchar(64) NOT NULL DEFAULT '' COMMENT '服务电话',
    `sort_order` INT DEFAULT 0 COMMENT '排序序号',

    `del_flag` char(1) DEFAULT '0' COMMENT '删除标识(0:未删除，2:已删除)',
    `create_time` datetime DEFAULT NULL COMMENT '创建时间',
    `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
    `update_time` datetime DEFAULT NULL COMMENT '修改时间',
    `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='运营电话信息表';


DROP TABLE IF EXISTS `tb_nursing_worker`;
CREATE TABLE `tb_nursing_worker` (
    `id`          bigint      NOT NULL AUTO_INCREMENT COMMENT 'id',
    `name`        varchar(64) NOT NULL DEFAULT '' COMMENT '姓名',
    `phone_number` varchar(64) NOT NULL DEFAULT '' COMMENT '手机号',
    `sex` varchar(4) NOT NULL DEFAULT '' COMMENT '性别 1-男 2-女',
    `age`  int NOT NULL DEFAULT 0 COMMENT '年龄',
    `work_years` int NOT NULL DEFAULT 0 COMMENT '工作年限',
    `hospital_id` bigint NOT NULL DEFAULT 0 COMMENT '常驻医院id',
    `id_card` varchar(64) NOT NULL DEFAULT '' COMMENT '身份证号',
    `order_receiving_status` varchar(4) NOT NULL DEFAULT '0' COMMENT '接单状态 0-正常 1-关闭',
    `enable_status` varchar(4) NOT NULL DEFAULT '0' COMMENT '用户状态 0-启用 1-禁用',
    `sort_order` INT DEFAULT 0 COMMENT '排序序号',
    `photograph_url` varchar(255) NOT NULL DEFAULT '' COMMENT '形象照片url',

    `del_flag`    char(1)              DEFAULT '0' COMMENT '删除标识(0:未删除，2:已删除)',
    `create_time` datetime             DEFAULT NULL COMMENT '创建时间',
    `create_by`   varchar(64)          DEFAULT '' COMMENT '创建者',
    `update_time` datetime             DEFAULT NULL COMMENT '修改时间',
    `update_by`   varchar(64)          DEFAULT '' COMMENT '更新者',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='护工信息表';

DROP TABLE IF EXISTS `tb_nursing_certificate`;
CREATE TABLE `tb_nursing_certificate`
(
    `id`               bigint       NOT NULL AUTO_INCREMENT COMMENT 'id',
    `worker_id`        bigint       NOT NULL DEFAULT 0 COMMENT '护工id',
    `certificate_name` varchar(64)  NOT NULL DEFAULT '' COMMENT '证书名称',
    `picture_url`      varchar(255) NOT NULL DEFAULT '' COMMENT '证书图片url',

    `del_flag`         char(1)               DEFAULT '0' COMMENT '删除标识(0:未删除，2:已删除)',
    `create_time`      datetime              DEFAULT NULL COMMENT '创建时间',
    `create_by`        varchar(64)           DEFAULT '' COMMENT '创建者',
    `update_time`      datetime              DEFAULT NULL COMMENT '修改时间',
    `update_by`        varchar(64)           DEFAULT '' COMMENT '更新者',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='护工从业证书信息表';

DROP TABLE IF EXISTS `tb_management_teacher`;
CREATE TABLE `tb_management_teacher`
(
    `id`                     bigint       NOT NULL AUTO_INCREMENT COMMENT 'id',
    `name`                   varchar(64)  NOT NULL DEFAULT '' COMMENT '姓名',
    `phone_number`            varchar(64)  NOT NULL DEFAULT '' COMMENT '手机号',
    `sex`                    varchar(4)   NOT NULL DEFAULT '' COMMENT '性别 1-男 2-女',
    `age`                    int          NOT NULL DEFAULT 0 COMMENT '年龄',
    `work_years`             int          NOT NULL DEFAULT 0 COMMENT '工作年限',
    `id_card`                varchar(64)  NOT NULL DEFAULT '' COMMENT '身份证号',
    `enable_status`          varchar(4) NOT NULL DEFAULT '0' COMMENT '状态 0-启用 1-禁用',
    `sort_order`             INT                   DEFAULT 0 COMMENT '排序序号',
    `photograph_url`         varchar(255) NOT NULL DEFAULT '' COMMENT '形象照片url',
    `manage_nursing_flag` varchar(4) NOT NULL DEFAULT 0 COMMENT '是否开启管理护工权限 N-否 Y-是',

    `del_flag`               char(1)               DEFAULT '0' COMMENT '删除标识(0:未删除，2:已删除)',
    `create_time`            datetime              DEFAULT NULL COMMENT '创建时间',
    `create_by`              varchar(64)           DEFAULT '' COMMENT '创建者',
    `update_time`            datetime              DEFAULT NULL COMMENT '修改时间',
    `update_by`              varchar(64)           DEFAULT '' COMMENT '更新者',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='管理老师信息表';


DROP TABLE IF EXISTS `tb_teacher_department_rel`;
CREATE TABLE `tb_teacher_department_rel`
(
    `id`                     bigint       NOT NULL AUTO_INCREMENT COMMENT 'id',
    `teacher_id`  bigint       NOT NULL  COMMENT '管理老师id',
    `hospital_id`  bigint       NOT NULL  COMMENT '医院id',
    `department_id`  bigint       NOT NULL  COMMENT '科室id',

    `del_flag`               char(1)               DEFAULT '0' COMMENT '删除标识(0:未删除，2:已删除)',
    `create_time`            datetime              DEFAULT NULL COMMENT '创建时间',
    `create_by`              varchar(64)           DEFAULT '' COMMENT '创建者',
    `update_time`            datetime              DEFAULT NULL COMMENT '修改时间',
    `update_by`              varchar(64)           DEFAULT '' COMMENT '更新者',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='管理老师管理科室关联表';

DROP TABLE IF EXISTS `tb_order_refund`;
CREATE TABLE `tb_order_refund`  (
    `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `order_refund_no` varchar(100) NOT NULL COMMENT '退款订单号',
    `order_id` bigint(0) NOT NULL DEFAULT 0 COMMENT '订单ID',
    `refund_reason` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '退款原因',
    `refund_quantity` int(0) NOT NULL COMMENT '退款数量',
    `refund_amount` decimal(10, 2) NOT NULL COMMENT '退款金额',
    `refund_way` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '退款方式',
    `refund_desc` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '退款说明',
    `refund_status` tinyint(0) NOT NULL COMMENT '退款状态',
    `actual_refund_amount` decimal(10, 2) NULL DEFAULT NULL COMMENT '实退金额',
    `operation_reason` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '操作原因',
    `operation_time` datetime(0) NULL DEFAULT NULL COMMENT '操作时间',
    `operator_type` tinyint(0) NULL COMMENT '操作类型 1-平台 2-管理老师',
    `operator_id` bigint(0) NULL DEFAULT NULL COMMENT '操作人',
    `refund_trade_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款交易号',
    `complete_time` datetime DEFAULT NULL COMMENT '退款完成时间',
    `service_id` bigint(0) NOT NULL COMMENT '服务ID',
    `service_info` json NULL COMMENT '服务信息冗余',
    `order_num` int(0) NOT NULL COMMENT '下单数量',
    `selling_price` decimal(10, 2) NOT NULL COMMENT '销售价',
    `total_price` decimal(10, 2) NOT NULL COMMENT '应付价格',
    `pay_price` decimal(10, 2) NULL COMMENT '实际支付金额',
    `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
    `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `inx_order_id`(`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '售后订单' ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `tb_order_reviews`;
CREATE TABLE `tb_order_reviews`  (
     `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT 'ID',
     `order_id` bigint(0) NOT NULL DEFAULT 0 COMMENT '订单ID',
     `user_id` bigint(0) NOT NULL DEFAULT 0 COMMENT 'C端用户ID',
     `service_attitude` tinyint(0) NULL DEFAULT NULL COMMENT '服务态度（0-5星级）',
     `dress_appearance` tinyint(0) NULL DEFAULT NULL COMMENT '着装仪态（0-5星级）',
     `communication_ability` tinyint(0) NULL DEFAULT NULL COMMENT '沟通能力（0-5星级）',
     `content` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '内容',
     `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
     `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
     PRIMARY KEY (`id`) USING BTREE,
     INDEX `inx_order_id`(`order_id`) USING BTREE,
     INDEX `inx_user_id`(`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单评价' ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `tb_order_worker_change_record`;
CREATE TABLE `tb_order_worker_change_record`  (
      `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT 'ID',
      `order_id` bigint(0) NOT NULL DEFAULT 0 COMMENT '订单ID',
      `worker_id` bigint(0) NOT NULL COMMENT '护工ID',
      `worker_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '护工姓名',
      `worker_phone_number` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '护工手机号',
      `sex` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '性别 1-男 2-女',
      `operating_teacher_id` bigint(0) NOT NULL COMMENT '操作老师主键',
      `worker_start_time` datetime(0) NULL DEFAULT NULL COMMENT '护工开始时间',
      `worker_end_time` datetime(0) NULL DEFAULT NULL COMMENT '护工结束时间',
      `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
      `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `inx_order_id`(`order_id`) USING BTREE,
      INDEX `inx_worker_id`(`worker_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单更换分配护工记录' ROW_FORMAT = Dynamic;

ALTER TABLE `tb_order`
    ADD COLUMN `pay_price` decimal(10, 2) NULL COMMENT '实际支付金额' AFTER `total_price`,
    ADD COLUMN `pay_method` tinyint DEFAULT NULL COMMENT '支付方式' AFTER `pay_price`,
    ADD COLUMN `pay_time` datetime NULL COMMENT '支付时间' AFTER `pay_method`,
    ADD COLUMN `pay_trade_no` varchar(100) NULL COMMENT '第三方支付交易号' AFTER `pay_time`,
    ADD COLUMN `worker_id` bigint NULL COMMENT '护工ID' AFTER `pay_trade_no`,
    ADD COLUMN `is_reviewed` tinyint DEFAULT NULL COMMENT '是否评价过' AFTER `cancellation_reason`,
    ADD INDEX `inx_worker_id`(`worker_id`);