-- ============================================================
-- Demo Seed: Level 2/3 Execution Data
-- Confirmation history, material consumption + costing ledger,
-- and bundle movement passports for PROD-2026-000002.
--
-- One-time seed for a fresh/demo database. Populates the
-- ExecutionConsole tabs (Confirmations, Bundles, Costing) and
-- the Control Center with realistic working data.
--
-- Wires up:
--   production_confirmations          -> 3 operation/final confirmations
--   production_order_materials        -> +500 consumed (Polybag)
--   bom_consumption                   -> 1 manual consumption row
--   production_live_costs             -> material + labor + overhead
--   production_bundle_movements       -> full lifecycle for 2 bundles
--   production_orders                 -> totals recomputed from confirmations
--   production_order_bundles          -> completed_qty / station backfill
-- ============================================================

-- ── 1. Confirmations (order 2, ops: 17 Shoulder Seam, 18 Neck Collar Attach, 25 Quality Check)
INSERT INTO production_confirmations
  (company_id, production_order_id, process_order_id, operation_id, bundle_id, confirmation_type,
   input_qty, output_qty, rejected_qty, rework_qty, scrap_qty, confirmed_by, confirmed_at, notes)
VALUES
  (1, 2, NULL, 17, NULL, 'operation', 2000, 1200, 5, 2, 0, 1, '2026-07-28 11:20:00', 'Morning shift output'),
  (1, 2, NULL, 18, NULL, 'operation', 1250, 1500, 8, 3, 0, 1, '2026-07-30 16:45:00', 'Neck collar attach batch'),
  (1, 2, NULL, 25, NULL, 'final',    1000,  800, 5, 1, 5, 1, '2026-08-01 09:10:00', 'Endline final QC');

-- ── 2. Manual material consumption (Polybag, material id 13)
UPDATE production_order_materials
   SET consumed_qty = consumed_qty + 500,
       wastage_qty  = wastage_qty + 10,
       updated_at   = NOW()
 WHERE id = 13 AND prod_order_id = 2;

INSERT INTO bom_consumption
  (company_id, production_order_id, process_order_id, operation_id, confirmation_id, material_id,
   item_id, item_name, bom_qty, output_qty, consumed_qty, wastage_qty, uom, batch_no, lot_no, rate, total_cost, consumed_at)
VALUES
  (1, 2, NULL, 25, NULL, 13, NULL, 'Polybag 30x40cm', 0, 0, 500, 10, 'PCS', 'B-8877', 'L-1', 0.03, 15.00, '2026-08-01 09:15:00');

-- ── 3. Costing ledger (material / labor / overhead)
INSERT INTO production_live_costs
  (company_id, production_order_id, cost_category, cost_subcategory, item_name,
   planned_amount, actual_amount, reference_type, reference_id, recorded_at)
VALUES
  (1, 2, 'material',  'manual_consumption', 'Polybag 30x40cm',                      60.00, 15.00, 'material_consumption', 13, '2026-08-01 09:15:00'),
  (1, 2, 'labor',     'labor_entry',        'Direct Labor (4 operators, 32 hrs)',  116.00, 116.00, 'labor_entry',         NULL, '2026-07-31 18:00:00'),
  (1, 2, 'overhead',  'factory_overhead',   'Factory Overhead - Electricity & Rent', 500.00, 480.00, 'overhead',           NULL, '2026-07-31 18:00:00');

-- ── 4. Bundle movement passports
-- Bundle 61 (B-20260720-0001, status completed) -> full lifecycle
INSERT INTO production_bundle_movements
  (company_id, bundle_id, prod_order_id, from_operation_id, to_operation_id, operator_name, action, qty, notes, scanned_at)
VALUES
  (1, 61, 2, NULL, 14, 'Rahim Uddin',  'created',  200, 'Bundle created',        '2026-07-20 08:05:00'),
  (1, 61, 2, 14,   14, 'Rahim Uddin',  'scanned',  200, 'Scan at Front Panel Cut', '2026-07-21 09:30:00'),
  (1, 61, 2, 14,   15, 'Rahim Uddin',  'advanced', 200, 'Advanced to Back Panel Cut', '2026-07-22 10:15:00'),
  (1, 61, 2, 15,   17, 'Rahim Uddin',  'advanced', 200, 'Advanced to Shoulder Seam',  '2026-07-25 14:40:00'),
  (1, 61, 2, 17,   18, 'Rahim Uddin',  'advanced', 200, 'Advanced to Neck Collar Attach', '2026-07-28 11:00:00'),
  (1, 61, 2, 18,   25, 'Jamil Ahmed',  'sent_qc',  200, 'Sent to Quality Check',  '2026-07-31 16:20:00'),
  (1, 61, 2, 25,   25, 'Jamil Ahmed',  'passed',   200, 'QC passed',              '2026-08-01 09:05:00'),
  (1, 61, 2, 25,   NULL, 'Jamil Ahmed', 'completed', 200, 'Bundle completed',     '2026-08-01 09:40:00');

-- Bundle 72 (B-20260720-0012, status in_process) -> in-flight
INSERT INTO production_bundle_movements
  (company_id, bundle_id, prod_order_id, from_operation_id, to_operation_id, operator_name, action, qty, notes, scanned_at)
VALUES
  (1, 72, 2, NULL, 14, 'Farida Begum', 'created',  200, 'Bundle created',        '2026-07-22 08:10:00'),
  (1, 72, 2, 14,   14, 'Farida Begum', 'scanned',  200, 'Scan at Front Panel Cut', '2026-07-24 09:45:00'),
  (1, 72, 2, 14,   15, 'Farida Begum', 'advanced', 200, 'Advanced to Back Panel Cut', '2026-07-28 13:30:00');

-- Backfill bundle status fields to stay consistent with the movements
UPDATE production_order_bundles SET completed_qty = 200, current_station = 'Quality Check' WHERE id = 61;
UPDATE production_order_bundles SET current_operation_id = 15, current_station = 'Back Panel Cut' WHERE id = 72;

-- ── 5. Recompute order 2 totals from the confirmation ledger (mirrors updateProdOrderTotals)
UPDATE production_orders po
LEFT JOIN (
  SELECT production_order_id,
         COALESCE(SUM(output_qty),0) AS completed,
         COALESCE(SUM(rejected_qty),0) AS rejected,
         COALESCE(SUM(scrap_qty),0) AS scrapped
  FROM production_confirmations
  WHERE production_order_id = 2
  GROUP BY production_order_id
) t ON t.production_order_id = po.id
SET po.completed_qty  = COALESCE(t.completed, po.completed_qty),
    po.rejected_qty   = COALESCE(t.rejected, po.rejected_qty),
    po.scrapped_qty   = COALESCE(t.scrapped, po.scrapped_qty),
    po.completion_pct = ROUND(COALESCE(t.completed, 0) / NULLIF(po.planned_qty, 0) * 100, 2),
    po.updated_at     = NOW()
WHERE po.id = 2;
