-- =====================================================
-- Phase 1: 受注残管理表ストアドプロシージャ修正
-- 修正内容: deleted_at条件の削除（物理削除対応）
-- 作成日: 2025-12-15
-- =====================================================
--
-- 【変更点】
-- 1. Step 1（行78相当）: oh.deleted_at IS NULL を削除
-- 2. Step 2（行112相当）: poh.deleted_at IS NULL を削除
-- 3. デッドコード削除: tmp_pattern1_used_purchases テーブル作成を削除
-- 4. Step 6のSELECT文とStep 7のクリーンアップをコメント解除
-- =====================================================

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_generate_backorder_report$$

CREATE PROCEDURE sp_generate_backorder_report(
  IN p_product_id BIGINT,      -- 商品絞り込み（NULL=全件）
  IN p_customer_id BIGINT,     -- 顧客絞り込み（NULL=全件）
  IN p_sales_rep_id BIGINT     -- 担当者絞り込み（NULL=全件）
)
BEGIN
  -- テンポラリテーブルのクリーンアップ（念のため）
  DROP TEMPORARY TABLE IF EXISTS tmp_orders;
  DROP TEMPORARY TABLE IF EXISTS tmp_purchase_orders;
  DROP TEMPORARY TABLE IF EXISTS tmp_pattern1_allocations;
  DROP TEMPORARY TABLE IF EXISTS tmp_pattern2_allocations;
  DROP TEMPORARY TABLE IF EXISTS tmp_allocations;

  -- =====================================================
  -- Step 1: 受注データの取得と累積計算
  -- =====================================================
  CREATE TEMPORARY TABLE tmp_orders AS
  SELECT
    od.id AS order_detail_id,
    od.order_id,
    oh.slip_number,
    od.line_no,
    od.product_id,
    od.product_code,
    od.product_name,
    p.product_character,
    c.category_name,
    oh.order_date,
    oh.shipment_date,
    oh.order_number,
    oh.allocation_policy,                      -- 引当ポリシー
    od.order_qty,
    od.backorder_qty,
    od.unit_price,
    (od.backorder_qty * od.unit_price) AS backorder_amount,  -- 受注残金額
    od.priority_allocation_flag,
    cust.customer_code,
    cust.customer_name,
    u.user_code AS sales_rep_code,
    CONCAT(u.family_name, ' ', COALESCE(u.given_name, '')) AS sales_rep_name,
    -- Window関数で受注累積を計算
    SUM(od.backorder_qty) OVER (
      PARTITION BY od.product_id
      ORDER BY p.product_name, od.product_code,
               od.priority_allocation_flag DESC,  -- 優先指定が最優先
               oh.allocation_policy,              -- 引当ポリシーが次点
               oh.order_date, oh.slip_number
      ROWS UNBOUNDED PRECEDING
    ) AS order_accumulated,
    -- 受注割当前数（1つ前までの累積）
    COALESCE(
      SUM(od.backorder_qty) OVER (
        PARTITION BY od.product_id
        ORDER BY p.product_name, od.product_code,
                 od.priority_allocation_flag DESC,
                 oh.allocation_policy,
                 oh.order_date, oh.slip_number
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
      ),
      0
    ) AS order_pre_allocated
  FROM order_details od
  INNER JOIN order_headers oh ON od.order_id = oh.id
  INNER JOIN products p ON od.product_id = p.id
  LEFT JOIN categories c ON p.category_lv2_id = c.id
  INNER JOIN customers cust ON oh.customer_id = cust.id
  LEFT JOIN order_category_sales_reps ocsr ON oh.id = ocsr.order_id
    AND ocsr.category_id = p.category_lv1_id  -- 商品の大分類と担当者区分をマッチング
  LEFT JOIN users u ON ocsr.sales_rep_id = u.id
  WHERE od.backorder_qty > 0  -- 受注残のみ
    -- deleted_at条件は削除（物理削除対応）
    AND (p_product_id IS NULL OR od.product_id = p_product_id)
    AND (p_customer_id IS NULL OR oh.customer_id = p_customer_id)
    AND (p_sales_rep_id IS NULL OR u.id = p_sales_rep_id);

  -- =====================================================
  -- Step 2: 発注データの取得と累積計算
  -- =====================================================
  CREATE TEMPORARY TABLE tmp_purchase_orders AS
  SELECT
    pod.id AS purchase_order_detail_id,
    pod.purchase_order_id,
    poh.purchase_order_number,
    pod.product_id,
    pod.product_code,
    pod.product_name,
    p.product_character,
    p.stock_control_type,
    c.category_name,
    -- 入荷予定日の優先順位: public_expected_receipt_date > expected_receipt_date
    COALESCE(pod.public_expected_receipt_date, pod.expected_receipt_date) AS arrival_date,
    pod.backorder_qty AS arrival_qty,
    pod.job_number,
    -- Window関数で発注累積を計算（入荷予定日の昇順）
    SUM(pod.backorder_qty) OVER (
      PARTITION BY pod.product_id
      ORDER BY COALESCE(pod.public_expected_receipt_date, pod.expected_receipt_date)
      ROWS UNBOUNDED PRECEDING
    ) AS purchase_accumulated
  FROM purchase_order_details pod
  INNER JOIN purchase_order_headers poh ON pod.purchase_order_id = poh.id
  INNER JOIN products p ON pod.product_id = p.id
  LEFT JOIN categories c ON p.category_lv2_id = c.id
  WHERE pod.backorder_qty > 0  -- 発注残のみ
    -- deleted_at条件は削除（物理削除対応）
    AND (p_product_id IS NULL OR pod.product_id = p_product_id);

  -- =====================================================
  -- Step 3: パターン①の引き当て処理（allocation_policy IN (1,4,5)）
  -- 出荷予定日マッチング方式
  -- =====================================================
  CREATE TEMPORARY TABLE tmp_pattern1_allocations AS
  SELECT
    o.order_detail_id,
    o.order_id,
    o.slip_number,
    o.line_no,
    o.product_id,
    o.product_code,
    o.product_name,
    o.product_character,
    o.category_name,
    o.order_date,
    o.shipment_date,
    o.order_number,
    o.allocation_policy,
    o.order_qty,
    o.backorder_qty,
    o.unit_price,
    o.backorder_amount,
    o.priority_allocation_flag,
    o.customer_code,
    o.customer_name,
    o.sales_rep_code,
    o.sales_rep_name,
    o.order_accumulated,
    o.order_pre_allocated,
    po.purchase_order_detail_id,
    po.purchase_order_number,
    po.arrival_date,
    po.arrival_qty,
    po.job_number,
    po.purchase_accumulated,
    po.stock_control_type,
    -- 受注可能残
    (po.purchase_accumulated - o.order_accumulated) AS available_qty,
    -- 引き当てパターン
    1 AS allocation_pattern
  FROM tmp_orders o
  INNER JOIN tmp_purchase_orders po ON (
    po.product_id = o.product_id
    AND po.arrival_date < o.shipment_date
    AND o.order_pre_allocated < po.purchase_accumulated
    -- Window関数で各受注に対して arrival_date DESC順に番号付け
    AND po.purchase_order_detail_id = (
      SELECT po2.purchase_order_detail_id
      FROM tmp_purchase_orders po2
      WHERE po2.product_id = o.product_id
        AND po2.arrival_date < o.shipment_date
        AND o.order_pre_allocated < po2.purchase_accumulated
      ORDER BY po2.arrival_date DESC
      LIMIT 1
    )
  )
  WHERE o.allocation_policy IN (1, 4, 5)
    AND o.shipment_date IS NOT NULL;

  -- =====================================================
  -- Step 4: パターン②の引き当て処理（allocation_policy IN (2,3)）
  -- 直近入荷割り当て方式
  -- =====================================================
  -- 注: tmp_pattern1_used_purchases は未使用のため削除

  CREATE TEMPORARY TABLE tmp_pattern2_allocations AS
  SELECT
    o.order_detail_id,
    o.order_id,
    o.slip_number,
    o.line_no,
    o.product_id,
    o.product_code,
    o.product_name,
    o.product_character,
    o.category_name,
    o.order_date,
    o.shipment_date,
    o.order_number,
    o.allocation_policy,
    o.order_qty,
    o.backorder_qty,
    o.unit_price,
    o.backorder_amount,
    o.priority_allocation_flag,
    o.customer_code,
    o.customer_name,
    o.sales_rep_code,
    o.sales_rep_name,
    o.order_accumulated,
    o.order_pre_allocated,
    po.purchase_order_detail_id,
    po.purchase_order_number,
    po.arrival_date,
    po.arrival_qty,
    po.job_number,
    po.purchase_accumulated,
    po.stock_control_type,
    -- 受注可能残
    (po.purchase_accumulated - o.order_accumulated) AS available_qty,
    -- 引き当てパターン
    2 AS allocation_pattern
  FROM tmp_orders o
  INNER JOIN tmp_purchase_orders po ON (
    po.product_id = o.product_id
    AND o.order_pre_allocated < po.purchase_accumulated
    -- 直近の入荷予定日（arrival_date ASC）を選択
    AND po.purchase_order_detail_id = (
      SELECT po2.purchase_order_detail_id
      FROM tmp_purchase_orders po2
      WHERE po2.product_id = o.product_id
        AND o.order_pre_allocated < po2.purchase_accumulated
      ORDER BY po2.arrival_date ASC
      LIMIT 1
    )
  )
  WHERE o.allocation_policy IN (2, 3)
    OR (o.allocation_policy IN (1, 4, 5) AND o.shipment_date IS NULL);

  -- =====================================================
  -- Step 5: パターン①と②を統合 + パターン③（受注なし・入荷のみ）を追加
  -- =====================================================
  CREATE TEMPORARY TABLE tmp_allocations AS
  SELECT * FROM tmp_pattern1_allocations
  UNION ALL
  SELECT * FROM tmp_pattern2_allocations
  UNION ALL
  -- パターン③: 受注がなく入荷のみある商品
  SELECT
    NULL AS order_detail_id,
    NULL AS order_id,
    NULL AS slip_number,
    NULL AS line_no,
    po.product_id,
    po.product_code,
    po.product_name,
    po.product_character,
    po.category_name,
    NULL AS order_date,
    NULL AS shipment_date,
    NULL AS order_number,
    NULL AS allocation_policy,
    NULL AS order_qty,
    NULL AS backorder_qty,
    NULL AS unit_price,
    NULL AS backorder_amount,
    NULL AS priority_allocation_flag,
    NULL AS customer_code,
    NULL AS customer_name,
    NULL AS sales_rep_code,
    NULL AS sales_rep_name,
    NULL AS order_accumulated,
    NULL AS order_pre_allocated,
    po.purchase_order_detail_id,
    po.purchase_order_number,
    po.arrival_date,
    po.arrival_qty,
    po.job_number,
    po.purchase_accumulated,
    po.stock_control_type,
    po.purchase_accumulated AS available_qty,  -- 全量が受注可能残
    3 AS allocation_pattern  -- パターン③: 受注なし
  FROM tmp_purchase_orders po
  WHERE NOT EXISTS (
    SELECT 1 FROM tmp_orders o WHERE o.product_id = po.product_id
  );

  -- =====================================================
  -- Step 6: 最終結果を返却
  -- =====================================================
  SELECT
    slip_number AS '受注No',
    line_no AS '行',
    product_code AS '商品コード',
    product_name AS '商品名',
    product_character AS '商品性格',
    category_name AS '中分類名',
    order_date AS '受注日付',
    order_qty AS '受注数',
    backorder_qty AS '受注残',
    unit_price AS '受注単価',
    backorder_amount AS '受注残金額',
    customer_code AS '得意先コード',
    customer_name AS '得意先名',
    sales_rep_code AS '担当者コード',
    sales_rep_name AS '担当者名',
    shipment_date AS '出荷予定日',
    order_number AS '注文No',
    priority_allocation_flag AS '優先指定',
    allocation_policy AS '引当ポリシー',
    order_accumulated AS '受注累積',
    purchase_order_number AS '発注番号',
    arrival_date AS '入荷予定日',
    arrival_qty AS '入荷予定数',
    job_number AS 'JobNo',
    purchase_accumulated AS '発注累積',
    available_qty AS '受注可能残',
    stock_control_type AS '在庫管理区分',
    allocation_pattern AS '引き当てパターン'
  FROM tmp_allocations
  ORDER BY
    product_name,
    product_code,
    priority_allocation_flag DESC,
    allocation_policy,
    order_date,
    slip_number;

  -- =====================================================
  -- Step 7: テンポラリテーブルのクリーンアップ
  -- 注: tmp_allocationsは呼び出し元（proc_generate_backorder_report_by_product等）で
  --     参照されるため、ここではクリーンアップしない。
  --     呼び出し元が使用完了後に適宜クリーンアップすること。
  -- =====================================================
  -- DROP TEMPORARY TABLE IF EXISTS tmp_orders;
  -- DROP TEMPORARY TABLE IF EXISTS tmp_purchase_orders;
  -- DROP TEMPORARY TABLE IF EXISTS tmp_pattern1_allocations;
  -- DROP TEMPORARY TABLE IF EXISTS tmp_pattern2_allocations;
  -- DROP TEMPORARY TABLE IF EXISTS tmp_allocations;
END$$

DELIMITER ;
