-- point_transactions（ポイントトランザクションログ）
-- transaction_id  : トランザクションID（主キー）
-- customer_id     : 顧客ID
-- event_type      : イベント種別（'earn'=付与, 'use'=使用, 'expire'=失効）
-- points          : ポイント数（正の整数。符号はevent_typeで管理）
-- order_id        : 紐づく注文ID（NULLの場合は手動付与・失効処理）
-- occurred_at     : 発生日時
-- expiry_date     : このポイントの有効期限（付与イベントのみ）
-- note            : メモ

-- STEP1: 残高の累積計算

WITH signed_transactions AS (
    SELECT
        transaction_id,
        customer_id,
        event_type,
        points,
        occurred_at,
        expiry_date,

        -- 符号付きポイント（付与はプラス、使用・失効はマイナス）
        CASE
            WHEN event_type = 'earn'   THEN  points
            WHEN event_type = 'use'    THEN -points
            WHEN event_type = 'expire' THEN -points
            ELSE 0
        END  AS signed_points
    FROM point_transactions
),
running_balance AS (
    SELECT
        transaction_id,
        customer_id,
        event_type,
        points,
        signed_points,
        occurred_at,
        expiry_date,

        -- 顧客ごとに時系列順で累積和を計算
        SUM(signed_points) OVER (
            PARTITION BY customer_id
            ORDER BY occurred_at ASC, transaction_id ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )  AS running_balance
    FROM signed_transactions
)
SELECT *
FROM running_balance
ORDER BY customer_id, occurred_at, transaction_id;

-- STEP2: 顧客ごとの現在残高

SELECT
    customer_id,
    SUM(
        CASE
            WHEN event_type = 'earn'   THEN  points
            WHEN event_type = 'use'    THEN -points
            WHEN event_type = 'expire' THEN -points
            ELSE 0
        END
    )  AS current_balance,

    -- 内訳も一緒に出す
    SUM(CASE WHEN event_type = 'earn'   THEN points ELSE 0 END)  AS total_earned,
    SUM(CASE WHEN event_type = 'use'    THEN points ELSE 0 END)  AS total_used,
    SUM(CASE WHEN event_type = 'expire' THEN points ELSE 0 END)  AS total_expired
FROM point_transactions
GROUP BY customer_id
ORDER BY current_balance DESC;

-- STEP3: マイナス残高の検知

WITH signed_transactions AS (
    SELECT
        transaction_id,
        customer_id,
        event_type,
        points,
        occurred_at,
        CASE
            WHEN event_type = 'earn'   THEN  points
            WHEN event_type = 'use'    THEN -points
            WHEN event_type = 'expire' THEN -points
            ELSE 0
        END  AS signed_points
    FROM point_transactions
),
running_balance AS (
    SELECT
        transaction_id,
        customer_id,
        event_type,
        occurred_at,
        SUM(signed_points) OVER (
            PARTITION BY customer_id
            ORDER BY occurred_at ASC, transaction_id ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )  AS running_balance
    FROM signed_transactions
)
SELECT
    customer_id,
    transaction_id,
    event_type,
    occurred_at,
    running_balance
FROM running_balance
WHERE running_balance < 0  -- マイナスになった時点のレコードを検出
ORDER BY customer_id, occurred_at;

-- STEP4: 失効間近ポイントの抽出（来月末に失効するポイントがある顧客）

WITH current_balance AS (
    SELECT
        customer_id,
        SUM(
            CASE
                WHEN event_type = 'earn'   THEN  points
                WHEN event_type = 'use'    THEN -points
                WHEN event_type = 'expire' THEN -points
                ELSE 0
            END
        )  AS balance
    FROM point_transactions
    GROUP BY customer_id
),
expiring_points AS (
    -- 来月末までに失効する予定の付与ポイントを持つ顧客
    SELECT
        customer_id,
        SUM(points)   AS expiring_amount,
        MIN(expiry_date) AS earliest_expiry
    FROM point_transactions
    WHERE
        event_type   = 'earn'
        AND expiry_date IS NOT NULL
        -- 来月末までに失効する
        AND expiry_date <= DATE_ADD('month', 1,
            DATE_TRUNC('month', CURRENT_DATE)
        )
        -- 有効期限がまだ切れていない（今日以降が失効日）
        AND expiry_date >= CURRENT_DATE
    GROUP BY customer_id
)
SELECT
    ep.customer_id,
    cb.balance                AS current_balance,
    ep.expiring_amount        AS points_expiring_soon,
    ep.earliest_expiry        AS expiry_date,
    DATE_DIFF('day', CURRENT_DATE, ep.earliest_expiry)  AS days_until_expiry,
    '失効間近リマインド'      AS segment_name
FROM expiring_points   ep
JOIN current_balance   cb  ON ep.customer_id = cb.customer_id
-- 残高があること（すでにすべて使い切った顧客は除外）
WHERE cb.balance > 0
ORDER BY days_until_expiry ASC, ep.expiring_amount DESC;

-- STEP5: 特定時点の残高を再現する

SELECT
    customer_id,
    SUM(
        CASE
            WHEN event_type = 'earn'   THEN  points
            WHEN event_type = 'use'    THEN -points
            WHEN event_type = 'expire' THEN -points
            ELSE 0
        END
    )  AS balance_at_that_time
FROM point_transactions
WHERE
    customer_id  = 'C001'
    AND occurred_at <= TIMESTAMP '2024-06-01 23:59:59'  -- この時点以前のイベントだけ
GROUP BY customer_id;

-- ポイント発行・利用の月次推移

SELECT
    DATE_TRUNC('month', occurred_at)  AS month,
    SUM(CASE WHEN event_type = 'earn'   THEN points ELSE 0 END)  AS earned,
    SUM(CASE WHEN event_type = 'use'    THEN points ELSE 0 END)  AS used,
    SUM(CASE WHEN event_type = 'expire' THEN points ELSE 0 END)  AS expired,
    -- 利用率：使用ポイント ÷ 付与ポイント
    ROUND(
        SUM(CASE WHEN event_type = 'use' THEN points ELSE 0 END) * 100.0
        / NULLIF(SUM(CASE WHEN event_type = 'earn' THEN points ELSE 0 END), 0)
    , 1)  AS usage_rate_pct,
    -- 失効率：失効ポイント ÷ 付与ポイント
    ROUND(
        SUM(CASE WHEN event_type = 'expire' THEN points ELSE 0 END) * 100.0
        / NULLIF(SUM(CASE WHEN event_type = 'earn' THEN points ELSE 0 END), 0)
    , 1)  AS expire_rate_pct
FROM point_transactions
GROUP BY DATE_TRUNC('month', occurred_at)
ORDER BY month;