-- =============================================================================
-- Migration: Unify Factories + Contractors into Production Units
-- Target: master_contractors becomes the single Production Units table.
--   - Adds unit_type enum('internal','contractor')
--   - Backfills the 5 seeded master_factories as internal units (preserving ids 1-5)
--   - Renames every factory_id/managing_factory_id/plant_id reference to unit_id
--   - Extends production_sourcing and finished_goods_receipts for producer routing
--   - Drops master_factories last
-- NOTE: There are NO InnoDB FK constraints on master_factories, so column renames
--   are index-preserving and safe. Run once; data inserts are idempotent.
-- =============================================================================

-- 1. Production Units type on master_contractors
ALTER TABLE `master_contractors`
  ADD COLUMN `unit_type` enum('internal','contractor') NOT NULL DEFAULT 'contractor' AFTER `contractor_code`;

-- 2. Backfill the 5 seeded factories as internal units (preserve historical ids 1-5)
INSERT IGNORE INTO `master_contractors`
  (`id`, `company_id`, `contractor_code`, `contractor_name`, `unit_type`, `contact_person`, `email`, `phone`, `address`, `status`)
VALUES
  (1, 1, 'DGL-001', 'Dhaka Garments Ltd', 'internal', 'Mr. Hasan', 'hasan@dhakagarments.com', '+880-2-1111111', 'Gazipur, Dhaka, Bangladesh', 'active'),
  (2, 1, 'CEPZ-001', 'Chattogram Export Processing Zone', 'internal', 'Ms. Nasrin', 'nasrin@cepz.com', '+880-2-2222222', 'CEPZ, Chattogram, Bangladesh', 'active'),
  (3, 1, 'KCL-001', 'Knit Concern Ltd', 'internal', 'Mr. Rahman', 'rahman@knitconcern.com', '+880-2-3333333', 'Savar, Dhaka, Bangladesh', 'active'),
  (4, 1, 'WDS-001', 'Wash & Dye Solutions', 'internal', 'Mr. Kabir', 'kabir@washdye.com', '+880-2-4444444', 'Narayanganj, Bangladesh', 'active'),
  (5, 1, 'PRG-001', 'PackRight Garments', 'internal', 'Ms. Akhter', 'akhter@packright.com', '+880-2-5555555', 'Tongi, Gazipur, Bangladesh', 'active');

-- 3. Repoint manufacturing-chain columns
ALTER TABLE `production_orders`          RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `process_orders`             RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `packing_orders`             RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `master_production_lines`    RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `master_work_centers`        RENAME COLUMN `factory_id` TO `unit_id`;

-- 4. Repoint warehouse ownership columns
ALTER TABLE `warehouses` RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `warehouses` RENAME COLUMN `managing_factory_id` TO `managing_unit_id`;

-- 5. Repoint internal logistics columns
ALTER TABLE `internal_asns`              RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `internal_grns`              RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `shipment_manifest_items`    RENAME COLUMN `factory_id` TO `unit_id`;

-- 6. Repoint procurement columns (incl. plant_id on PO items)
ALTER TABLE `procurement_orders`         RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `procurement_order_items`    RENAME COLUMN `plant_id` TO `unit_id`;
ALTER TABLE `purchase_requisitions`      RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `material_indents`           RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `material_issues`            RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `material_returns`           RENAME COLUMN `factory_id` TO `unit_id`;

-- 7. Repoint master/planning columns
ALTER TABLE `master_styles`              RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `master_buyer_po_costing`    RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `packing_stations`           RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `style_production`           RENAME COLUMN `factory_id` TO `unit_id`;

-- 8. Repoint QC/users columns
ALTER TABLE `inspection_assignments`     RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `inspection_sessions`        RENAME COLUMN `factory_id` TO `unit_id`;
ALTER TABLE `users`                      RENAME COLUMN `factory_id` TO `unit_id`;

-- 9. Production sourcing producer tracking
ALTER TABLE `production_sourcing`
  ADD COLUMN `produced_qty` int unsigned NOT NULL DEFAULT 0 AFTER `planned_qty`,
  ADD COLUMN `status` enum('planned','in_progress','completed') NOT NULL DEFAULT 'planned' AFTER `produced_qty`,
  ADD COLUMN `fg_warehouse_id` int unsigned DEFAULT NULL AFTER `status`,
  ADD KEY `fg_warehouse_id` (`fg_warehouse_id`);

-- 10. Finished goods receipts producer routing
ALTER TABLE `finished_goods_receipts`
  ADD COLUMN `producer_id` int unsigned DEFAULT NULL AFTER `production_order_id`,
  ADD COLUMN `sourcing_id` int unsigned DEFAULT NULL AFTER `producer_id`,
  ADD KEY `producer_id` (`producer_id`),
  ADD KEY `sourcing_id` (`sourcing_id`);

-- 11. Drop the old factories table (only after all references are repointed)
DROP TABLE IF EXISTS `master_factories`;
