-- ============================================================
-- Migration: Production Bundle Movements
-- Apparel MES - Level 2 Execution (Bundle scan / QR traceability)
--
-- Adds an append-only trail of every bundle movement across
-- stations/operations so WIP can be traced (bundle passport).
-- Companion section §12 in database/production_schema.sql.
-- ============================================================

CREATE TABLE IF NOT EXISTS `production_bundle_movements` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `bundle_id` int unsigned NOT NULL COMMENT 'production_order_bundles.id',
  `prod_order_id` int unsigned DEFAULT NULL COMMENT 'production_orders.id',
  `from_station` varchar(100) DEFAULT NULL,
  `to_station` varchar(100) DEFAULT NULL,
  `from_operation_id` int unsigned DEFAULT NULL COMMENT 'production_order_operations.id',
  `to_operation_id` int unsigned DEFAULT NULL COMMENT 'production_order_operations.id',
  `operator_name` varchar(150) DEFAULT NULL,
  `action` enum('created','scanned','advanced','sent_qc','passed','rejected','packed','completed','remark') NOT NULL DEFAULT 'scanned',
  `qty` int unsigned DEFAULT 0,
  `notes` varchar(255) DEFAULT NULL,
  `scanned_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `bundle_id` (`bundle_id`),
  KEY `prod_order_id` (`prod_order_id`),
  KEY `scanned_at` (`scanned_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
