-- 
-- 入荷予定リスト（公開用）
-- 
CREATE OR REPLACE VIEW v_public_expected_receipt_list AS
SELECT 
    -- 営業公開入荷日
    agg.public_expected_receipt_date,
    agg.public_expected_receipt_date_df,
    -- 状況
    agg.status,
    -- 仕入先
    MIN(agg.supplier_id) AS supplier_id,
    -- 仕入先名
    agg.supplier_name,
    -- 内容
    MIN(
      CAST(agg.content AS CHAR CHARACTER SET utf8mb4)
      COLLATE utf8mb4_bin
    ) AS content,
    -- 入荷形態
    agg.receipt_type,
    -- 備考
    MIN(
      CAST(agg.note_text AS CHAR CHARACTER SET utf8mb4)
      COLLATE utf8mb4_bin
    ) AS note,
    -- INVOICE NO.
    agg.invoice_number,
    -- 商品
    GROUP_CONCAT(DISTINCT v.product_id ORDER BY v.product_id SEPARATOR ',') AS product_id,
    -- 商品名
    GROUP_CONCAT(DISTINCT v.product_name ORDER BY v.product_name SEPARATOR '\n') AS product_name
FROM 
    -- 入荷予定リスト
    v_public_expected_receipts v

-- 集約データ
JOIN (
    SELECT 
        MIN(public_expected_receipt_date) AS public_expected_receipt_date,
        MIN(public_expected_receipt_date_df) AS public_expected_receipt_date_df,
        status,
        MIN(supplier_id) AS supplier_id,
        supplier_name,
        GROUP_CONCAT(content SEPARATOR '\n') AS content,
        GROUP_CONCAT(note_text SEPARATOR '\n') AS note_text,
        receipt_type,
        invoice_number
    FROM 
        -- 入荷予定リスト
        v_public_expected_receipts
    GROUP BY 
        public_expected_receipt_date_df,
        status,
        supplier_name,
        receipt_type,
        invoice_number
) agg
ON v.public_expected_receipt_date_df = agg.public_expected_receipt_date_df
AND v.status = agg.status
AND COALESCE(v.supplier_name, '') = COALESCE(agg.supplier_name, '')
AND COALESCE(v.receipt_type, '') = COALESCE(agg.receipt_type, '')
AND COALESCE(v.invoice_number, '') = COALESCE(agg.invoice_number, '')

GROUP BY 
    agg.public_expected_receipt_date,
    agg.public_expected_receipt_date_df,
    agg.status,
    agg.supplier_name,
    agg.receipt_type,
    agg.invoice_number