-- 
-- 与信チェックリスト（参照）（受注残高なし）
-- 
CREATE OR REPLACE VIEW v_credit_check_list_backlog_no_balance
AS 
SELECT
    -- 得意先グループID
    CASE
        WHEN c.customer_group_id IS NULL THEN c.id
        ELSE c.customer_group_id
    END AS customer_group_id,
    -- 得意先名
    CASE
        WHEN c.customer_group_id IS NULL THEN c.customer_name
        ELSE cg.customer_group_name
    END AS customer_name,
    -- 売掛残高(A)
    c.current_balance_amount AS accounts_receivable_balance,
    -- 与信限度額(G)
    CASE
        WHEN c.customer_group_id IS NULL THEN c.individual_regular_credit_limit
        ELSE
            CASE
                WHEN ROW_NUMBER() OVER (
                    PARTITION BY c.customer_group_id
                    ORDER BY c.id ASC
                ) = 1
                THEN
                    -- 得意先グループは一つのグループで複数レコードになる可能性があり、その場合、同額の通常与信額が複数取得されるため、最初のレコードのみ有効にする
                    cg.regular_credit_limit
                ELSE 0
            END
    END AS regular_credit_limit,
    -- 与信残高(G)-(A)
    COALESCE(
        CASE
            WHEN c.customer_group_id IS NULL THEN c.individual_regular_credit_limit
            ELSE
                CASE
                    WHEN ROW_NUMBER() OVER (
                        PARTITION BY c.customer_group_id
                        ORDER BY c.id ASC
                    ) = 1
                    THEN
                        -- 得意先グループは一つのグループで複数レコードになる可能性があり、その場合、同額の通常与信額が複数取得されるため、最初のレコードのみ有効にする
                        cg.regular_credit_limit
                    ELSE 0
                END
        END
    , 0) - (c.current_balance_amount) AS credit_balance

FROM
    -- 得意先マスタ + 売掛残高
    -- 【参考】OrderAssist.checkCreditlimitの「(2)売掛集計 monthly_accounts_receivablesの最終month_end_dateデータを参照」
    (
        -- 得意先マスタ + 売掛残高（得意先マスタ）
        SELECT c1.*, mar1.current_balance_amount  FROM
        (
            SELECT * FROM customers WHERE customer_group_id IS NULL
        ) c1
        LEFT JOIN
        (
            SELECT
                customer_id, SUM(current_balance_amount) AS current_balance_amount
            FROM
                -- 売掛集計
                monthly_accounts_receivables
            GROUP BY customer_id
        ) mar1
        ON
            c1.id = mar1.customer_id

        UNION ALL

        -- 得意先マスタ + 売掛残高（得意先グループ）
        SELECT c2.*, mar2.current_balance_amount FROM
        (
            SELECT * FROM customers WHERE customer_group_id IS NOT NULL
        ) c2
        LEFT JOIN
        (
            SELECT customer_id, current_balance_amount FROM
            (
                SELECT
                    month_end_date,
                    customer_id,
                    SUM(current_balance_amount) AS current_balance_amount,
                    ROW_NUMBER() OVER (
                        PARTITION BY customer_id
                        ORDER BY month_end_date DESC
                    ) rn
                FROM
                    -- 売掛集計
                    monthly_accounts_receivables
                GROUP BY
                    month_end_date,
                    customer_id
            ) d
                WHERE rn = 1
        ) mar2
        ON
            c2.id = mar2.customer_id
    ) c
LEFT JOIN
    -- 得意先グループ
    customer_groups cg ON c.customer_group_id = cg.id

ORDER BY customer_group_id ASC
