owl-admin/doc/系统设计/数据库设计/物理模型/分析后台设计增加/03技师认证.sql

DROP TABLE IF EXISTS report_coach_auth;

CREATE TABLE report_coach_auth AS

/* 技师认证视图 */

DROP VIEW IF EXISTS manage_coach_auth;

CREATE OR REPLACE VIEW manage_coach_auth AS
WITH
    /* 资质证书信息 */
    qualification_photos AS (
        SELECT
            cq.id,
            /* 从业资格证 */
            MAX(
                CASE
                    WHEN cq.qual_type = 'QUALIFICATION' THEN cq.qual_photo
                END
            ) AS qualification_photo,
            /* 健康证 */
            MAX(
                CASE
                    WHEN cq.qual_type = 'HEALTH' THEN cq.qual_photo
                END
            ) AS health_photo,
            /* 生活照 */
            MAX(
                CASE
                    WHEN cq.qual_type = 'LIFE_PHOTO' THEN cq.qual_photo
                END
            ) AS life_photo,
            /* 工作照 */
            MAX(
                CASE
                    WHEN cq.qual_type = 'WORK_PHOTO' THEN cq.qual_photo
                END
            ) AS work_photo
        FROM coach_qual_records cq
        WHERE
            cq.deleted_at IS NULL
        GROUP BY
            cq.id
    )
SELECT
    cu.id AS id /* 技师ID */,
    ci.id AS info_record_id /* 技师信息记录ID */,
    cr.id AS real_auth_record_id /* 实名认证记录ID */,
    qp.id AS qualification_record_id /* 资质证书记录ID */,
    ci.nickname AS nickname /* 技师昵称 */,
    ci.gender AS gender /* 性别 */,
    ci.birthday AS birthday /* 出生日期 */,
    ci.intention_city AS intention_city /* 期望城市 */,
    ci.mobile AS mobile /* 手机号码 */,
    ci.introduction AS introduction /* 个人简介 */,
    CASE
        WHEN cu.real_auth_record_id IS NULL THEN '未认证'
        WHEN cu.qualification_record_id IS NULL THEN '实名认证完成'
        ELSE '全部认证完成'
    END AS auth_state /* 认证进度 */,
    cu.state AS state /* 认证状态 */,
    cr.id_card_front_photo AS id_card_front_photo /* 身份证照片正面 */,
    cr.id_card_back_photo AS id_card_back_photo /* 身份证照片反面 */,
    qp.qualification_photo AS qualification_photo /* 从业资格证图片 */,
    qp.health_photo AS health_photo /* 健康证 */,
    qp.life_photo AS life_photo /* 生活照 */,
    qp.work_photo AS work_photo /* 工作照 */
FROM
    coach_users cu
    LEFT JOIN coach_info_records ci ON cu.info_record_id = ci.id
    LEFT JOIN coach_real_records cr ON cu.real_auth_record_id = cr.id
    LEFT JOIN qualification_photos qp ON cu.qualification_record_id = qp.id
WHERE
    cu.deleted_at IS NULL
    AND ci.deleted_at IS NULL
    AND (
        cr.deleted_at IS NULL
        OR cr.deleted_at IS NULL
    );

/* 技师基本信息视图 */
DROP VIEW IF EXISTS v_coach_info;

CREATE OR REPLACE VIEW v_coach_info AS
SELECT
    cir.id AS id /* 记录ID */,
    cu.id AS coach_id /* 技师ID */,
    cir.nickname AS nickname /* 昵称 */,
    cir.gender AS gender /* 性别 */,
    cir.mobile AS mobile /* 手机号码 */,
    cir.birthday AS birthday /* 出生日期 */,
    cir.intention_city AS intention_city /* 意向城市 */,
    cir.introduction AS introduction /* 个人简介 */,
    cu.state AS coach_state /* 技师状态 */,
    cir.state AS info_record_state /* 审核状态 */,
    cir.avatar AS avatar /* 头像图片 */,
    cir.life_photos AS life_photos /* 生活照片数组 */,
    cir.created_at AS created_at /* 申请时间 */,
    cir.auditor AS auditor /* 审核人 */,
    cir.audit_time AS audit_time /* 审核时间 */,
    cir.audit_remark AS audit_remark /* 审核备注 */,
    cir.work_years AS work_years /* 工作年限 */
FROM
    coach_info_records cir /* 技师基本信息记录 */
    LEFT JOIN coach_users cu ON cu.id = cir.coach_id /* 技师ID */
WHERE
    cir.deleted_at IS NULL
    AND cu.deleted_at IS NULL;

/* 技师资质认证记录视图 */
DROP VIEW IF EXISTS v_coach_qual_record;

CREATE VIEW v_coach_qual_record AS
SELECT
    cqr.id AS id /* 资质证书记录ID */,
    cu.id AS coach_id /* 技师ID */,
    cir.id AS info_record_id /* 技师信息记录ID */,
    cqr.qual_type AS qual_type /* 资质类型 */,
    cqr.qual_photo AS qual_photo /* 资质图片 */,
    cir.state AS info_record_state /* 技师信息记录状态 */,
    cu.state AS coach_state /* 技师状态 */,
    cqr.state AS qualification_record_state /* 审核状态 */,
    cir.nickname AS nickname /* 技师昵称 */,
    cir.gender AS gender /* 技师性别 */,
    cir.mobile AS mobile /* 技师手机号码 */,
    cir.intention_city AS intention_city /* 技师期望城市 */,
    cir.introduction AS introduction /* 技师个人简介 */,
    cir.portrait_images AS avatar /* 头像图片 */,
    cir.life_photos AS life_photos /* 生活照片数组 */,
    cqr.business_license AS business_license /* 技师营业执照 */,
    cqr.health_cert AS health_certificate /* 技师健康证 */,
    cr.id_card_front_photo AS id_card_front_photo /* 技师身份证照片正面 */,
    cr.id_card_back_photo AS id_card_back_photo /* 技师身份证照片反面 */,
    cr.id_card_hand_photo AS id_card_hand_photo /* 技师身份证手持照片 */,
    cqr.created_at AS created_at /* 申请时间 */,
    cqr.auditor AS auditor /* 审核人 */,
    cqr.audit_time AS audit_time /* 审核时间 */,
    cqr.audit_remark AS audit_remark /* 审核备注 */
FROM
    coach_qual_records cqr
    LEFT JOIN coach_users cu ON cu.id = cqr.coach_id /* 先关联技师用户表 */
    LEFT JOIN coach_info_records cir ON cir.id = cu.info_record_id /* 再关联技师信息记录表 */
    LEFT JOIN coach_real_records cr ON cr.coach_id = cqr.coach_id /* 最后关联实名认证记录表 */
WHERE
    cqr.deleted_at IS NULL
    AND cu.deleted_at IS NULL
    AND cir.deleted_at IS NULL;

/* 技师实名认证记录视图 */
DROP VIEW IF EXISTS v_coach_real_record;

CREATE VIEW v_coach_real_record AS
SELECT
    cr.id AS id /* 实名认证记录ID */,
    cu.id AS coach_id /* 技师ID */,
    cir.id AS info_record_id /* 技师信息记录ID */,
    cir.nickname AS nickname /* 技师昵称 */,
    cir.portrait_images AS avatar /* 头像图片 */,
    cir.gender AS gender /* 技师性别 */,
    cir.mobile AS mobile /* 技师手机号码 */,
    cir.intention_city AS intention_city /* 技师期望城市 */,
    cr.real_name AS real_name /* 真实姓名 */,
    cr.id_card AS id_card /* 身份证号码 */,
    cu.state AS coach_state /* 技师状态 */,
    cr.state AS state /* 审核状态 */,
    cr.id_card_front_photo AS id_card_front_photo /* 身份证照片正面 */,
    cr.id_card_back_photo AS id_card_back_photo /* 身份证照片反面 */,
    cr.id_card_hand_photo AS id_card_hand_photo /* 身份证手持照片 */,
    cr.created_at AS created_at /* 申请时间 */,
    cr.auditor AS auditor /* 审核人 */,
    cr.audit_time AS audit_time /* 审核时间 */,
    cr.audit_remark AS audit_remark /* 审核备注 */
FROM
    coach_real_records cr
    LEFT JOIN coach_users cu ON cu.id = cr.coach_id
    LEFT JOIN coach_info_records cir ON cir.id = cu.info_record_id
WHERE
    cr.deleted_at IS NULL
    AND cu.deleted_at IS NULL
    AND cir.deleted_at IS NULL;