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

-- Active: 1735094924500@@192.168.110.85@3306@xiaoding_test
/* 技师基本信息视图 */
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
ORDER BY
    cir.created_at DESC;

/* 技师实名认证记录视图 */
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.avatar 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
ORDER BY
    cr.created_at DESC;

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

CREATE VIEW v_coach_qual_record AS
SELECT DISTINCT
    cqr.id AS id /* 资质证书记录ID */,
    cu.id AS coach_id /* 技师ID */,
    cir.id AS info_record_id /* 技师信息记录ID */,
    cir.nickname AS nickname /* 技师昵称 */,
    cir.gender AS gender /* 技师性别 */,
    cir.mobile AS mobile /* 技师手机号码 */,
    cir.intention_city AS intention_city /* 技师期望城市 */,
    cir.introduction AS introduction /* 技师个人简介 */,
    cir.avatar AS avatar /* 头像图片 */,
    cir.life_photos AS life_photos /* 生活照片数组 */,
    cqr.qual_type AS qual_type /* 资质类型 */,
    cqr.qual_photo AS qual_photo /* 资质图片 */,
    cqr.business_license AS business_license /* 技师营业执照 */,
    cqr.health_cert AS health_cert /* 技师健康证 */,
    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 /* 技师身份证手持照片 */,
    cu.state AS coach_state /* 技师状态 */,
    cqr.state AS qualification_record_state /* 审核状态 */,
    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.id = cu.real_auth_record_id /* 最后关联实名认证记录表 */
WHERE
    cqr.deleted_at IS NULL
    AND cu.deleted_at IS NULL
    AND cir.deleted_at IS NULL
    AND cr.deleted_at IS NULL
ORDER BY
    cqr.created_at DESC;