-- 
-- 階層部門付き営業社員リスト
-- 
CREATE OR REPLACE VIEW v_sales_reps_with_dept
AS 
-- 部門は第１階層と第２階層のみを取得
WITH RECURSIVE hierarchy AS (

    -- 最上位階層（root）
    SELECT
        id,
        parent_department_id,
        id AS root_id,
        department_name AS root_department_name,
        sort_order AS root_sort_order,
        0 AS second_id,
        REPEAT(' ', 50) AS second_department_name,
        0 AS second_sort_order,
        1 AS level
    FROM departments
    WHERE parent_department_id IS NULL 

    UNION ALL 

    -- 子階層を再帰的に展開
    SELECT
        c.id,
        c.parent_department_id,
        h.root_id,
        h.root_department_name,
        h.root_sort_order,
        CASE
            WHEN h.level = 1 THEN c.id
            ELSE h.second_id
        END AS second_id,
        CASE
            WHEN h.level = 1 THEN c.department_name
            ELSE h.second_department_name
        END AS second_department_name,
        CASE
            WHEN h.level = 1 THEN c.sort_order
            ELSE h.second_sort_order
        END AS second_sort_order,
        h.level + 1 AS level
    FROM departments c
    INNER JOIN hierarchy h ON c.parent_department_id = h.id
)

-- 営業社員に階層部門（第１階層・第２階層）を結合
SELECT
    u.id AS user_id,    -- 社員ID
    u.user_code,        -- 社員コード
    u.user_name,        -- 社員名
    u.sort_order AS user_sort_order,    -- 社員表示順
    h.id AS department_id,              -- 所属部門ID
    h.level AS department_level,        -- 所属部門階層レベル
    h.root_id AS department_lv1_id,                 -- 第１階層部門ID
    h.root_department_name AS department_lv1_name,  -- 第１階層部門名
    h.root_sort_order AS department_lv1_sort_order, -- 第１階層部門表示順
    CASE
        WHEN h.second_id = 0 THEN NULL
        ELSE h.second_id
    END AS department_lv2_id,           -- 第２階層部門ID
    TRIM(h.second_department_name) 
     AS department_lv2_name,            -- 第２階層部門名
    CASE
        WHEN h.second_sort_order = 0 THEN NULL
        ELSE h.second_sort_order
    END  AS department_lv2_sort_order   -- 第２階層部門表示順
FROM hierarchy h 
    -- 営業社員のみを取得
    RIGHT JOIN (
        SELECT id, user_code, sort_order,
               CONCAT(family_name, given_name) AS user_name,
               department_id 
          FROM users 
         WHERE is_active = 1 AND is_sales_user = 1 ) u 
    ON u.department_id = h.id