-- ============================================================
-- MIGRATION: SAP-Style GRN (Goods Receipt Note) Sub-Tables
-- FashCognitive Digital GRN Module
-- ============================================================

-- ========================================
-- 1. ENHANCE grn TABLE — Add new columns
-- ========================================

ALTER TABLE `grn`
  ADD COLUMN `asn_id` int unsigned NULL AFTER `po_id`,
  ADD COLUMN `gate_entry_no` varchar(50) NULL AFTER `challan_no`,
  ADD COLUMN `driver_name` varchar(255) NULL AFTER `vehicle_no`,
  ADD COLUMN `driver_mobile` varchar(50) NULL AFTER `driver_name`,
  ADD COLUMN `transporter_name` varchar(255) NULL AFTER `driver_mobile`,
  ADD COLUMN `shift` varchar(50) NULL DEFAULT NULL AFTER `received_by`,
  ADD COLUMN `total_weight_kg` decimal(14,2) NULL DEFAULT NULL AFTER `total_qty`,
  ADD COLUMN `total_volume_cbm` decimal(10,3) NULL DEFAULT NULL AFTER `total_weight_kg`,
  ADD COLUMN `total_cartons` int unsigned NULL DEFAULT NULL AFTER `total_volume_cbm`,
  ADD COLUMN `total_pallets` int unsigned NULL DEFAULT NULL AFTER `total_cartons`,
  ADD COLUMN `received_at` datetime NULL DEFAULT NULL AFTER `received_date`,
  ADD COLUMN `confirmed_by` int unsigned NULL DEFAULT NULL AFTER `received_by`,
  ADD COLUMN `confirmed_at` datetime NULL DEFAULT NULL,
  ADD COLUMN `po_no_ref` varchar(50) NULL AFTER `po_id`,
  ADD COLUMN `supplier_name_ref` varchar(255) NULL AFTER `supplier_id`,
  ADD COLUMN `buyer_name` varchar(255) NULL DEFAULT NULL,
  ADD COLUMN `season` varchar(50) NULL DEFAULT NULL,
  ADD COLUMN `currency` varchar(10) NULL DEFAULT 'USD',
  ADD COLUMN `delivery_terms` varchar(50) NULL DEFAULT NULL,
  ADD COLUMN `payment_terms` varchar(50) NULL DEFAULT NULL,
  ADD INDEX `idx_grn_asn_id` (`asn_id`);

-- ========================================
-- 2. ENHANCE grn_items TABLE — Add putaway + batch columns
-- ========================================

ALTER TABLE `grn_items`
  ADD COLUMN `ordered_qty` decimal(14,4) NULL DEFAULT NULL AFTER `uom_id`,
  ADD COLUMN `item_name_ref` varchar(255) NULL DEFAULT NULL AFTER `item_id`,
  ADD COLUMN `color_name` varchar(100) NULL DEFAULT NULL AFTER `color`,
  ADD COLUMN `rate` decimal(12,2) NULL DEFAULT NULL AFTER `uom_id`,
  ADD COLUMN `line_total` decimal(14,2) NULL DEFAULT NULL AFTER `rate`,
  ADD COLUMN `warehouse_id` int unsigned NULL DEFAULT NULL AFTER `location_id`,
  ADD COLUMN `rack` varchar(50) NULL DEFAULT NULL AFTER `warehouse_id`,
  ADD COLUMN `bin` varchar(50) NULL DEFAULT NULL AFTER `rack`,
  ADD COLUMN `putaway_status` enum('pending','allocated','put_away') NOT NULL DEFAULT 'pending' AFTER `bin`,
  ADD COLUMN `batch_number` varchar(100) NULL DEFAULT NULL AFTER `batch_no`,
  ADD COLUMN `mfg_date` date NULL DEFAULT NULL AFTER `batch_number`,
  ADD COLUMN `expiry_date_new` date NULL DEFAULT NULL AFTER `mfg_date`,
  ADD COLUMN `internal_lot` varchar(100) NULL DEFAULT NULL AFTER `expiry_date_new`,
  ADD COLUMN `barcode` varchar(100) NULL DEFAULT NULL AFTER `internal_lot`,
  ADD COLUMN `qr_code` varchar(255) NULL DEFAULT NULL AFTER `barcode`;

-- ========================================
-- 3. GRN BATCHES — Batch tracking per GRN line
-- ========================================

CREATE TABLE IF NOT EXISTS `grn_batches` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `grn_id` int unsigned NOT NULL,
  `grn_item_id` int unsigned NOT NULL,
  `batch_number` varchar(100) NOT NULL,
  `mfg_date` date NULL DEFAULT NULL,
  `expiry_date` date NULL DEFAULT NULL,
  `supplier_lot` varchar(100) NULL DEFAULT NULL,
  `internal_lot` varchar(100) NULL DEFAULT NULL,
  `quantity` decimal(14,4) NOT NULL DEFAULT 0,
  `uom_id` int unsigned NOT NULL,
  `status` enum('active','expired','consumed','quarantine') NOT NULL DEFAULT 'active',
  `notes` text NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_grn_batches_grn_id` (`grn_id`),
  KEY `idx_grn_batches_grn_item_id` (`grn_item_id`),
  KEY `idx_grn_batches_batch_number` (`batch_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 4. GRN ROLLS — Individual fabric rolls
-- ========================================

CREATE TABLE IF NOT EXISTS `grn_rolls` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `grn_id` int unsigned NOT NULL,
  `grn_item_id` int unsigned NOT NULL,
  `roll_no` varchar(50) NOT NULL,
  `roll_barcode` varchar(100) NULL DEFAULT NULL,
  `length_meters` decimal(10,2) NULL DEFAULT NULL,
  `width_cm` decimal(8,2) NULL DEFAULT NULL,
  `weight_kg` decimal(10,2) NULL DEFAULT NULL,
  `gsm` decimal(8,2) NULL DEFAULT NULL,
  `shade_grade` varchar(20) NULL DEFAULT NULL,
  `defect_notes` text NULL DEFAULT NULL,
  `image_path` varchar(500) NULL DEFAULT NULL,
  `status` enum('received','inspected','passed','rejected','in_stock','consumed') NOT NULL DEFAULT 'received',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_grn_rolls_grn_id` (`grn_id`),
  KEY `idx_grn_rolls_roll_barcode` (`roll_barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 5. GRN IMAGES — Photos captured during receiving
-- ========================================

CREATE TABLE IF NOT EXISTS `grn_images` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `grn_id` int unsigned NOT NULL,
  `grn_item_id` int unsigned NULL DEFAULT NULL,
  `category` enum('truck','container','seal','material','damaged','fabric_roll','barcode_label','gate_entry','other') NOT NULL DEFAULT 'other',
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_type` varchar(100) NULL DEFAULT NULL,
  `file_size` int unsigned NULL DEFAULT 0,
  `caption` varchar(255) NULL DEFAULT NULL,
  `uploaded_by` int unsigned NULL DEFAULT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_grn_images_grn_id` (`grn_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 6. GRN QUALITY RESULTS — Detailed QC inspection per item
-- ========================================

CREATE TABLE IF NOT EXISTS `grn_quality_results` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `grn_id` int unsigned NOT NULL,
  `grn_item_id` int unsigned NOT NULL,
  `inspection_type` varchar(100) NOT NULL DEFAULT 'incoming',
  `inspector_name` varchar(255) NULL DEFAULT NULL,
  `inspection_date` datetime NULL DEFAULT NULL,
  `sample_size` decimal(14,4) NULL DEFAULT NULL,
  `passed_qty` decimal(14,4) NULL DEFAULT NULL,
  `rejected_qty` decimal(14,4) NULL DEFAULT NULL,
  `result` enum('pending','passed','conditional','failed') NOT NULL DEFAULT 'pending',
  `fabric_4point` varchar(20) NULL DEFAULT NULL,
  `shade_approval` varchar(20) NULL DEFAULT NULL,
  `gsm_test` varchar(20) NULL DEFAULT NULL,
  `width_check` varchar(20) NULL DEFAULT NULL,
  `shrinkage_test` varchar(20) NULL DEFAULT NULL,
  `lab_test` varchar(20) NULL DEFAULT NULL,
  `defects` json NULL DEFAULT NULL,
  `remarks` text NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_grn_qr_grn_id` (`grn_id`),
  KEY `idx_grn_qr_grn_item_id` (`grn_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 7. GRN REJECTIONS — Detailed rejection records
-- ========================================

CREATE TABLE IF NOT EXISTS `grn_rejections` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `grn_id` int unsigned NOT NULL,
  `grn_item_id` int unsigned NOT NULL,
  `rejection_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `rejection_type` varchar(100) NOT NULL DEFAULT 'quality',
  `defect_type` varchar(100) NULL DEFAULT NULL,
  `defect_description` text NULL DEFAULT NULL,
  `disposition` enum('return_to_supplier','scrap','rework','hold') NOT NULL DEFAULT 'return_to_supplier',
  `image_path` varchar(500) NULL DEFAULT NULL,
  `inspected_by` int unsigned NULL DEFAULT NULL,
  `inspected_at` datetime NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_grn_rej_grn_id` (`grn_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 8. GRN PUTAWAY — Warehouse bin allocation
-- ========================================

CREATE TABLE IF NOT EXISTS `grn_putaway` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `grn_id` int unsigned NOT NULL,
  `grn_item_id` int unsigned NOT NULL,
  `warehouse_id` int unsigned NOT NULL,
  `rack` varchar(50) NULL DEFAULT NULL,
  `bin` varchar(50) NULL DEFAULT NULL,
  `location_code` varchar(100) NULL DEFAULT NULL,
  `allocated_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `putaway_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `status` enum('suggested','allocated','put_away','confirmed') NOT NULL DEFAULT 'suggested',
  `putaway_by` int unsigned NULL DEFAULT NULL,
  `putaway_at` datetime NULL DEFAULT NULL,
  `notes` text NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_grn_putaway_grn_id` (`grn_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 9. GRN BARCODES — Generated barcodes
-- ========================================

CREATE TABLE IF NOT EXISTS `grn_barcodes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `grn_id` int unsigned NOT NULL,
  `grn_item_id` int unsigned NULL DEFAULT NULL,
  `barcode_type` enum('barcode','qr_code') NOT NULL DEFAULT 'barcode',
  `barcode_value` varchar(255) NOT NULL,
  `barcode_image_path` varchar(500) NULL DEFAULT NULL,
  `label_text` varchar(255) NULL DEFAULT NULL,
  `generated_by` int unsigned NULL DEFAULT NULL,
  `generated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_grn_barcodes_grn_id` (`grn_id`),
  KEY `idx_grn_barcodes_value` (`barcode_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 10. GRN APPROVALS — Multi-level approval workflow
-- ========================================

CREATE TABLE IF NOT EXISTS `grn_approvals` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `grn_id` int unsigned NOT NULL,
  `approval_level` int unsigned NOT NULL DEFAULT 1,
  `role_required` varchar(100) NOT NULL DEFAULT 'warehouse_manager',
  `approver_name` varchar(255) NULL DEFAULT NULL,
  `approver_id` int unsigned NULL DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `comments` text NULL DEFAULT NULL,
  `approved_at` datetime NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_grn_approvals_grn_id` (`grn_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 11. GRN AI ANALYSIS — AI inspection results
-- ========================================

CREATE TABLE IF NOT EXISTS `grn_ai_analysis` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `grn_id` int unsigned NOT NULL,
  `grn_item_id` int unsigned NULL DEFAULT NULL,
  `analysis_type` varchar(100) NOT NULL DEFAULT 'fabric_inspection',
  `readiness_score` decimal(5,2) NULL DEFAULT NULL,
  `shade_variation` varchar(20) NULL DEFAULT NULL,
  `width_variation_pct` decimal(6,2) NULL DEFAULT NULL,
  `moisture_level` varchar(50) NULL DEFAULT NULL,
  `defect_density` varchar(20) NULL DEFAULT NULL,
  `predicted_grade` varchar(10) NULL DEFAULT NULL,
  `ai_recommendation` varchar(255) NULL DEFAULT NULL,
  `ai_raw_data` json NULL DEFAULT NULL,
  `analyzed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_grn_ai_grn_id` (`grn_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 12. GRN INVENTORY TRANSACTIONS — Stock impact log
-- ========================================

CREATE TABLE IF NOT EXISTS `grn_inventory_transactions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `grn_id` int unsigned NOT NULL,
  `grn_item_id` int unsigned NOT NULL,
  `warehouse_id` int unsigned NOT NULL,
  `item_id` int unsigned NOT NULL,
  `color` varchar(100) NULL DEFAULT NULL,
  `size` varchar(100) NULL DEFAULT NULL,
  `lot_no` varchar(100) NULL DEFAULT NULL,
  `batch_number` varchar(100) NULL DEFAULT NULL,
  `uom_id` int unsigned NOT NULL,
  `qty_before` decimal(14,4) NOT NULL DEFAULT 0,
  `qty_received` decimal(14,4) NOT NULL DEFAULT 0,
  `qty_after` decimal(14,4) NOT NULL DEFAULT 0,
  `unit_cost` decimal(12,2) NULL DEFAULT NULL,
  `total_cost` decimal(14,2) NULL DEFAULT NULL,
  `movement_type` varchar(50) NOT NULL DEFAULT 'purchase_in',
  `reference_type` varchar(50) NOT NULL DEFAULT 'grn',
  `reference_id` int unsigned NOT NULL,
  `notes` text NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_grn_invtx_grn_id` (`grn_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================================
-- 13. GRN APPROVAL SIGNATURES (digital)
-- ========================================

CREATE TABLE IF NOT EXISTS `grn_approval_signatures` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `grn_id` int unsigned NOT NULL,
  `approval_id` int unsigned NOT NULL,
  `signer_name` varchar(255) NOT NULL,
  `signer_role` varchar(100) NOT NULL,
  `signature_data` text NULL DEFAULT NULL,
  `signed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_grn_sigs_grn_id` (`grn_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SELECT 'GRN migration completed successfully' as result;
