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
    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
    );