WITH RECURSIVE cte_name AS (
    -- ベースケース（再帰の起点・1回目の結果）
    SELECT ...

    UNION ALL

    -- 再帰ステップ（前の結果を参照して次の結果を生成）
    SELECT ...
    FROM cte_name  -- 自分自身を参照
    WHERE ...      -- 終了条件（これがないと無限ループ）
)
SELECT * FROM cte_name;

-- STEP1: 日付シーケンスを再帰CTEで生成する

WITH RECURSIVE date_series AS (
    -- ベースケース：開始日
    SELECT DATE '2024-01-01' AS dt

    UNION ALL

    -- 再帰ステップ：1日ずつ増やす
    SELECT DATE_ADD('day', 1, dt)
    FROM date_series
    WHERE dt < DATE '2024-12-31'  -- 終了条件：年末で止まる
)
SELECT dt
FROM date_series
ORDER BY dt;

-- STEP2: 欠損日を0として補完した日次売上

WITH RECURSIVE date_series AS (
    SELECT DATE '2024-01-01' AS dt
    UNION ALL
    SELECT DATE_ADD('day', 1, dt)
    FROM date_series
    WHERE dt < DATE '2024-12-31'
),
daily_sales AS (
    SELECT
        order_date,
        COUNT(order_id)   AS order_count,
        SUM(total_amount) AS total_sales
    FROM orders
    WHERE
        status     = 'completed'
        AND order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31'
    GROUP BY order_date
)
SELECT
    ds.dt                               AS sales_date,
    -- 売上がない日はNULLになるのでCOALESCEで0に補完
    COALESCE(da.order_count, 0)         AS order_count,
    COALESCE(da.total_sales, 0)         AS total_sales,

    -- 曜日情報も付与
    CASE EXTRACT(DOW FROM ds.dt)
        WHEN 1 THEN '月' WHEN 2 THEN '火' WHEN 3 THEN '水'
        WHEN 4 THEN '木' WHEN 5 THEN '金' WHEN 6 THEN '土'
        WHEN 7 THEN '日'
    END  AS day_of_week,

    -- 土日フラグ
    CASE WHEN EXTRACT(DOW FROM ds.dt) IN (6, 7) THEN 1 ELSE 0 END  AS is_weekend
FROM date_series   ds
LEFT JOIN daily_sales  da  ON ds.dt = da.order_date
ORDER BY ds.dt;

-- STEP3: 月次カレンダーシーケンス

WITH RECURSIVE month_series AS (
    SELECT DATE_TRUNC('month', DATE '2023-01-01')  AS month_start
    UNION ALL
    SELECT DATE_ADD('month', 1, month_start)
    FROM month_series
    WHERE month_start < DATE_TRUNC('month', DATE '2024-12-01')
)
SELECT
    month_start,
    DATE_ADD('day', -1, DATE_ADD('month', 1, month_start))  AS month_end,  -- 月末日
    EXTRACT(YEAR FROM month_start)   AS year_num,
    EXTRACT(MONTH FROM month_start)  AS month_num
FROM month_series
ORDER BY month_start;

-- categories テーブル（カテゴリ階層マスタ）
-- category_id   : カテゴリID
-- category_name : カテゴリ名
-- parent_id     : 親カテゴリのID（ルートカテゴリはNULL）

-- STEP4: カテゴリ階層を再帰的に展開する

WITH RECURSIVE category_tree AS (
    -- ベースケース：起点となるカテゴリ（今回はコスメ C001）
    SELECT
        category_id,
        category_name,
        parent_id,
        0             AS depth,           -- 深さ（起点は0）
        category_name AS path             -- 階層パス
    FROM categories
    WHERE category_id = 'C001'  -- 展開したいルートカテゴリ

    UNION ALL

    -- 再帰ステップ：親が前回の結果にある子カテゴリを追加
    SELECT
        c.category_id,
        c.category_name,
        c.parent_id,
        ct.depth + 1                                   AS depth,
        ct.path || ' > ' || c.category_name            AS path
    FROM categories      c
    JOIN category_tree   ct  ON c.parent_id = ct.category_id
)
SELECT
    category_id,
    category_name,
    parent_id,
    depth,
    path
FROM category_tree
ORDER BY path;

-- STEP5: コスメカテゴリ以下のすべての商品を取得する

WITH RECURSIVE category_tree AS (
    SELECT category_id, category_name, parent_id, 0 AS depth
    FROM categories
    WHERE category_id = 'C001'  -- コスメが起点

    UNION ALL

    SELECT c.category_id, c.category_name, c.parent_id, ct.depth + 1
    FROM categories     c
    JOIN category_tree  ct  ON c.parent_id = ct.category_id
),
-- コスメ以下のすべてのカテゴリIDを取得
cosme_category_ids AS (
    SELECT category_id FROM category_tree
)
SELECT
    p.product_id,
    p.product_name,
    p.category_id,
    c.category_name,
    ct.depth,
    p.price
FROM products             p
JOIN categories           c   ON p.category_id = c.category_id
JOIN cosme_category_ids   ci  ON p.category_id = ci.category_id
JOIN category_tree        ct  ON p.category_id = ct.category_id
ORDER BY ct.depth, c.category_name, p.product_name;

-- STEP6: 全カテゴリの階層パスを一括展開

WITH RECURSIVE category_tree AS (
    -- ベースケース：すべてのルートカテゴリ（parent_id IS NULL）
    SELECT
        category_id,
        category_name,
        parent_id,
        0             AS depth,
        CAST(category_id AS VARCHAR)  AS id_path,    -- IDの経路（ループ検知に使える）
        category_name AS name_path
    FROM categories
    WHERE parent_id IS NULL  -- ルートカテゴリ

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_id,
        ct.depth + 1,
        ct.id_path || '/' || c.category_id,
        ct.name_path || ' > ' || c.category_name
    FROM categories     c
    JOIN category_tree  ct  ON c.parent_id = ct.category_id
    -- 安全のため深さの上限を設ける
    WHERE ct.depth < 10
)
SELECT
    category_id,
    category_name,
    depth,
    name_path  AS full_path
FROM category_tree
ORDER BY full_path;

WHERE ct.depth < 10  -- 10階層を超えたら再帰を止める

-- 自分のIDが経路にすでに含まれていれば循環 → 再帰を止める
WHERE ct.id_path NOT LIKE '%' || c.category_id || '%'