-- Active: xiaoding_test@@192.168.110.85@3306@xiaoding_test
/*
您是一位专业的dba，同时会业务分析。请参考以上数据库的设计文档，给我写一个sql，关联查询出：
项目名称、项目分类、项目价格、项目时长、项目分成、区域管理、下单数量、状态、图片、开启/关闭
*/
DROP TABLE IF EXISTS report_shop_list;

CREATE TABLE report_shop_list AS

/* 店铺列表视图 */
DROP VIEW IF EXISTS manage_shop_list;

CREATE OR REPLACE VIEW manage_shop_list AS
WITH
    /* 店铺技师数量统计 */
    coach_count AS (
        SELECT cu.shop_id, COUNT(DISTINCT cu.id) /* 技师总数 */ AS coach_total
        FROM coach_users cu
        WHERE
            cu.deleted_at IS NULL
        GROUP BY
            cu.shop_id
    ),
    /* 店铺服务项目数量统计 */
    service_count AS (
        SELECT ss.shop_id, COUNT(DISTINCT ss.id) /* 服务项目总数 */ AS service_total
        FROM shop_services ss
        WHERE
            ss.deleted_at IS NULL
        GROUP BY
            ss.shop_id
    )
SELECT
    si.id as shop_id /* 店铺编号 */,
    sar.shop_name as shop_name /* 店铺名称 */,
    sar.contact_name as contact_name /* 店铺负责人 */,
    sar.contact_phone as contact_phone /* 店铺电话 */,
    sar.shop_address as shop_address /* 店铺位置 */,
    sar.longitude as longitude /* 经度 */,
    sar.latitude as latitude /* 纬度 */,
    sar.operating_area as operating_area /* 面积 */,
    sar.business_hours as business_hours /* 营业时间 */,
    sar.rating as rating /* 店铺星级 */,
    sar.storefront_photo as storefront_photo /* 店铺门头照 */,
    sar.storefront_front_photo as storefront_front_photo /* 店铺门头照正面 */,
    sar.storefront_back_photo as storefront_back_photo /* 店铺门头照背面 */,
    sar.state as state /* 认证状态 */,
    COALESCE(cc.coach_total, 0) as coach_total /* 技师总数 */,
    COALESCE(sc.service_total, 0) as service_total /* 服务项目总数 */
FROM
    shop_infos si
    LEFT JOIN shop_auth_records sar ON si.auth_record_id = sar.id
    AND sar.deleted_at IS NULL
    LEFT JOIN coach_count cc ON si.id = cc.shop_id
    LEFT JOIN service_count sc ON si.id = sc.shop_id
WHERE
    si.deleted_at IS NULL
ORDER BY si.id DESC;