-- ============================================================
-- FashCognitive - ASN Digital Shipment Passport Migration
-- Enhances basic ASN into SAP-like EWM Shipment document
-- ============================================================

-- -----------------------------------------------------------
-- 1. Add new columns to asn table
-- -----------------------------------------------------------
SELECT COUNT(*) INTO @col_exists FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'asn' AND COLUMN_NAME = 'container_no';
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `asn`
    ADD COLUMN `container_no` varchar(100) DEFAULT NULL AFTER `invoice_no`,
    ADD COLUMN `seal_no` varchar(100) DEFAULT NULL AFTER `container_no`,
    ADD COLUMN `driver_name` varchar(255) DEFAULT NULL AFTER `seal_no`,
    ADD COLUMN `driver_mobile` varchar(50) DEFAULT NULL AFTER `driver_name`,
    ADD COLUMN `transporter_name` varchar(255) DEFAULT NULL AFTER `driver_mobile`,
    ADD COLUMN `shipping_mode` varchar(20) NOT NULL DEFAULT ''road'' AFTER `transporter_name`,
    ADD COLUMN `shipping_marks` text DEFAULT NULL AFTER `shipping_mode`,
    ADD COLUMN `total_pallets` int unsigned DEFAULT NULL AFTER `total_rolls`,
    ADD COLUMN `total_cartons` int unsigned DEFAULT NULL AFTER `total_pallets`,
    ADD COLUMN `total_weight_kg` decimal(14,2) DEFAULT NULL AFTER `total_cartons`,
    ADD COLUMN `total_volume_cbm` decimal(10,3) DEFAULT NULL AFTER `total_weight_kg`,
    ADD COLUMN `gate_entry_completed` tinyint(1) NOT NULL DEFAULT 0 AFTER `total_volume_cbm`,
    ADD COLUMN `gate_entry_at` datetime DEFAULT NULL AFTER `gate_entry_completed`,
    ADD COLUMN `dock_no` varchar(50) DEFAULT NULL AFTER `gate_entry_at`,
    ADD COLUMN `estimated_unloading_min` int unsigned DEFAULT NULL AFTER `dock_no`,
    ADD COLUMN `ai_readiness_score` decimal(5,2) DEFAULT NULL AFTER `estimated_unloading_min`,
    ADD COLUMN `ai_notes` text DEFAULT NULL AFTER `ai_readiness_score`,
    ADD COLUMN `created_by` int unsigned DEFAULT NULL AFTER `ai_notes`',
  'SET @dummy = 0');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- -----------------------------------------------------------
-- 2. ASN Documents (packing list, invoices, certificates, etc.)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `asn_documents` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `asn_id` int unsigned NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_type` varchar(100) DEFAULT NULL,
  `file_size` int unsigned DEFAULT 0,
  `category` enum('packing_list','commercial_invoice','eway_bill','test_certificate',
    'fabric_inspection','lab_report','transport_lr','certificate_of_origin',
    'insurance','other') NOT NULL DEFAULT 'other',
  `uploaded_by` int unsigned DEFAULT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ad_asn` (`asn_id`),
  KEY `idx_ad_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 3. ASN Gate Entry (digital truck inspection)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `asn_gate_entry` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `asn_id` int unsigned NOT NULL,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `gate_entry_no` varchar(50) DEFAULT NULL,
  `entry_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `vehicle_verified` tinyint(1) NOT NULL DEFAULT 0,
  `seal_verified` tinyint(1) NOT NULL DEFAULT 0,
  `documents_verified` tinyint(1) NOT NULL DEFAULT 0,
  `invoice_verified` tinyint(1) NOT NULL DEFAULT 0,
  `packing_list_verified` tinyint(1) NOT NULL DEFAULT 0,
  `po_matched` tinyint(1) NOT NULL DEFAULT 0,
  `quantity_verified` tinyint(1) NOT NULL DEFAULT 0,
  `truck_photo` varchar(500) DEFAULT NULL,
  `driver_photo` varchar(500) DEFAULT NULL,
  `container_photo` varchar(500) DEFAULT NULL,
  `seal_photo` varchar(500) DEFAULT NULL,
  `gate_by` int unsigned DEFAULT NULL,
  `security_by` int unsigned DEFAULT NULL,
  `warehouse_by` int unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('in_progress','completed','rejected') NOT NULL DEFAULT 'in_progress',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_age_asn` (`asn_id`),
  KEY `idx_age_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 4. ASN Timeline (event log for full traceability)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `asn_timeline` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `asn_id` int unsigned NOT NULL,
  `event_type` varchar(100) NOT NULL,
  `event_label` varchar(255) NOT NULL,
  `event_details` text DEFAULT NULL,
  `performed_by` int unsigned DEFAULT NULL,
  `event_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_at_asn` (`asn_id`),
  KEY `idx_at_event` (`event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
