-- ============================================================================
-- Phase 9 — Warehouse Live Ops & Smooth-Run Quality
-- Adds: pick quality (mispick) event log, warehouse team roster,
--       cross-dock session workflow.
-- Idempotent: safe to run multiple times.
-- ============================================================================

CREATE TABLE IF NOT EXISTS `pick_quality_events` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `event_no` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `wave_id` int unsigned DEFAULT NULL,
  `wave_no` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pick_wave_item_id` int unsigned DEFAULT NULL,
  `outbound_order_id` int unsigned DEFAULT NULL,
  `outbound_order_item_id` int unsigned DEFAULT NULL,
  `inventory_serial_id` int unsigned DEFAULT NULL,
  `item_id` int unsigned DEFAULT NULL,
  `item_code` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `item_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `expected_serial_no` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `scanned_serial_no` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `expected_bin_code` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `scanned_bin_code` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `qty` decimal(14,4) DEFAULT NULL,
  `error_type` enum('wrong_item','wrong_serial','wrong_qty','wrong_bin','damaged','other') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'other',
  `reason_note` text COLLATE utf8mb4_unicode_ci,
  `status` enum('open','resolved','dismissed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `resolution` enum('rework','repick','returned','accept','other') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `repick_wave_id` int unsigned DEFAULT NULL,
  `detected_by` int unsigned DEFAULT NULL,
  `detected_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_by` int unsigned DEFAULT NULL,
  `resolved_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_pqe_no` (`company_id`,`event_no`),
  KEY `idx_pqe_status` (`company_id`,`status`),
  KEY `idx_pqe_wave` (`wave_id`),
  KEY `idx_pqe_type` (`error_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `warehouse_roster_assignments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `warehouse_id` int unsigned DEFAULT NULL,
  `user_id` int unsigned NOT NULL,
  `shift_id` int unsigned DEFAULT NULL,
  `shift_date` date NOT NULL,
  `role` enum('picker','receiver','loader','putaway','replenisher','checker','supervisor','manager') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'picker',
  `status` enum('scheduled','active','completed','absent','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'scheduled',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_roster_slot` (`company_id`,`user_id`,`shift_date`,`shift_id`),
  KEY `idx_roster_date` (`company_id`,`shift_date`,`role`),
  KEY `idx_roster_shift` (`shift_id`),
  KEY `idx_roster_user` (`user_id`),
  KEY `idx_roster_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crossdock_sessions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `session_no` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `warehouse_id` int unsigned DEFAULT NULL,
  `dock_door_id` int unsigned DEFAULT NULL,
  `inbound_ref_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inbound_ref_id` int unsigned DEFAULT NULL,
  `inbound_ref_no` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `outbound_order_id` int unsigned DEFAULT NULL,
  `outbound_no` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('planned','active','staged','completed','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'planned',
  `total_units` decimal(14,4) NOT NULL DEFAULT '0.0000',
  `staged_units` decimal(14,4) NOT NULL DEFAULT '0.0000',
  `loaded_units` decimal(14,4) NOT NULL DEFAULT '0.0000',
  `created_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `completed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_cd_no` (`company_id`,`session_no`),
  KEY `idx_cd_status` (`company_id`,`status`),
  KEY `idx_cd_outbound` (`outbound_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crossdock_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT '1',
  `session_id` int unsigned NOT NULL,
  `inventory_serial_id` int unsigned DEFAULT NULL,
  `serial_no` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `item_id` int unsigned DEFAULT NULL,
  `item_code` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `item_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `qty` decimal(14,4) NOT NULL DEFAULT '0.0000',
  `source_station` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `from_location` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `staging_bin_code` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('pending','staged','loaded','completed','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `outbound_order_item_id` int unsigned DEFAULT NULL,
  `staged_at` datetime DEFAULT NULL,
  `loaded_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_cdi_session` (`session_id`),
  KEY `idx_cdi_serial` (`inventory_serial_id`),
  KEY `idx_cdi_status` (`status`),
  CONSTRAINT `fk_cdi_session` FOREIGN KEY (`session_id`) REFERENCES `crossdock_sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
