-- 
-- 仕入先別仕入推移表（参照）
-- 
CREATE OR REPLACE VIEW v_supplier_transition_reference_lists
AS

WITH monthly AS (
    SELECT
        su.supplier_code,                 -- 仕入先コード
        su.supplier_name,                 -- 仕入先名
        YEAR(ph.receipt_date) AS y,       -- 年
        DATE_FORMAT(ph.receipt_date, '%m') AS m,   -- 月（2桁）
        DATE_FORMAT(ph.receipt_date, '%Y%m') AS ym, -- 年月
        SUM(ph.total_standard_jpy
          + ph.total_reduced_jpy
          + ph.total_exempt_jpy) AS total  -- 月次合計
    FROM purchase_headers ph
    LEFT JOIN suppliers su ON ph.supplier_id = su.id
    GROUP BY
        su.supplier_code,
        su.supplier_name,
        y, m, ym
)

SELECT
    cur.supplier_code,
    cur.supplier_name,
    cur.y  AS current_y,     -- 当年
    cur.ym AS current_ym,  -- 当年月（YYYYMM）
    CASE
        WHEN prev.total IS NULL OR prev.total = 0
            THEN 0
        ELSE prev.total
    END AS last_year_achievement,   -- 前年同月実績
    CASE
        WHEN cur.total IS NULL OR cur.total = 0
            THEN 0
        ELSE cur.total
    END AS current_year_achievement,   -- 当年実績
    CASE
        WHEN prev.total IS NOT NULL AND prev.total <> 0 
        AND cur.total IS NOT NULL AND cur.total <> 0
            THEN ROUND(cur.total / prev.total * 100, 0)  -- 前年・当年実績が無い／0 のときはNULL
        ELSE 0   -- 前年比（％）
    END AS year_on_year
FROM monthly cur
LEFT JOIN monthly prev
    ON prev.supplier_code = cur.supplier_code
   AND prev.y = cur.y - 1     -- 1年前
   AND prev.m = cur.m;        -- 同月
