-- 在庫一覧表(倉庫別)
CREATE OR REPLACE VIEW v_inventory_list_by_warehouse
AS

SELECT 
    ill.id AS inventory_lot_logs_id,                    -- ID
    CONCAT(LEFT(ill.year_month, 4), '/', RIGHT(ill.year_month, 2)) AS 'year_month',  -- 年月
    w.warehouse_name,                                   -- 倉庫
    p.product_code,                                     -- 商品コード
    p.product_name,                                     -- 商品名
    CASE
        WHEN p.is_gt_catalog = 1 THEN '掲載商品'
        WHEN p.is_gt_catalog = 2 THEN '非掲載'
        ELSE '-'
    END AS is_gt_catalog,                               -- カタログ掲載
    ill.opening_quantity,                               -- 前月末残数量
    ill.received_quantity,                              -- 入庫数量
    (ill.shipped_sales_quantity + ill.shipped_other_quantity) AS shipped_quantity, -- 出庫数量
    ill.closing_quantity,                               -- 当月末残数量
    ill.cost_price,                                     -- 在庫評価単価
    (ill.closing_quantity * ill.cost_price) AS inventory_amount,                   -- 在庫金額
    DATE_FORMAT(ill.created_at, '%Y/%m/%d %H:%i:%s') AS created_at,  -- 作成日時
    DATE_FORMAT(ill.updated_at, '%Y/%m/%d %H:%i:%s') AS updated_at,  -- 更新日時
    IFNULL(sub.details_json, '[]') AS details_json,     -- 詳細情報(JSON文字列)
    il.product_id,  -- 商品ID                                    
    il.warehouse_id -- 倉庫ID
                                     
FROM inventory_lot_logs AS ill

-- ロット在庫
LEFT JOIN inventory_lots AS il
  ON il.id = ill.inventory_lot_id

-- 明細を 1 親(ill.id) = 1 行に集約して返すサブクエリ
LEFT JOIN (
  SELECT
      d.inventory_lot_log_id,  -- 親キー
      CONCAT(
        '[',
        IFNULL(
          GROUP_CONCAT(
            CONCAT(
              '{',
                '"id":', 
                    d.id,
                    ',',
                '"transaction_date_ymd":', 
                    JSON_QUOTE(DATE_FORMAT(d.transaction_date, '%Y-%m-%d')),
                    ',',
                '"transaction_type":', 
                    JSON_QUOTE(CASE
                        WHEN d.transaction_type = 1 THEN '入庫'
                        WHEN d.transaction_type = 2 THEN '売上出庫'
                        WHEN d.transaction_type = 3 THEN '移動出庫'
                        WHEN d.transaction_type = 4 THEN 'セット品組立'
                        WHEN d.transaction_type = 5 THEN 'セット品バラシ'
                        ELSE ''
                    END),
                    ',',
                '"receiving_date":', 
                    JSON_QUOTE(CASE
                        WHEN d.quantity > 0 THEN DATE_FORMAT(d.transaction_date, '%Y/%m/%d')
                        ELSE ''
                    END),
                    ',',
                '"receiving_quantity":', 
                    JSON_QUOTE(CASE
                        WHEN d.quantity > 0 THEN d.quantity
                        ELSE '0'
                    END),
                    ',',
                '"shipping_date":', 
                    JSON_QUOTE(CASE
                        WHEN d.quantity < 0 THEN DATE_FORMAT(d.transaction_date, '%Y/%m/%d')
                        ELSE ''
                    END),
                    ',',
                '"shipping_quantity":', 
                    JSON_QUOTE(CASE
                        WHEN d.quantity < 0 THEN ABS(d.quantity)
                        ELSE '0'
                    END),
              '}'
            )
            ORDER BY d.transaction_date desc, d.id asc
            SEPARATOR ','
          ),
          ''
        ),
        ']'
      ) AS details_json
  FROM inventory_lot_log_details AS d
  GROUP BY d.inventory_lot_log_id
) AS sub
  ON sub.inventory_lot_log_id = ill.id
  

-- 商品マスタ
LEFT JOIN products AS p ON p.id = il.product_id

-- 倉庫マスタ
LEFT JOIN warehouses AS w ON w.id = il.warehouse_id;