-- ========================================
-- FTZ (Foreign Trade Zone) MODULE SCHEMA
-- ========================================

-- FTZ Reconciliations (Annual reporting)
CREATE TABLE IF NOT EXISTS ftz_reconciliations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    zone_lot_id INT NOT NULL,
    reconciliation_year INT NOT NULL,
    beginning_quantity DECIMAL(15,4) NOT NULL DEFAULT 0,
    beginning_value DECIMAL(15,2) NOT NULL DEFAULT 0,
    total_receipts DECIMAL(15,4) NOT NULL DEFAULT 0,
    total_transfers_in DECIMAL(15,4) NOT NULL DEFAULT 0,
    total_consumption DECIMAL(15,4) NOT NULL DEFAULT 0,
    total_removals DECIMAL(15,4) NOT NULL DEFAULT 0,
    total_transfers_out DECIMAL(15,4) NOT NULL DEFAULT 0,
    total_adjustments_positive DECIMAL(15,4) NOT NULL DEFAULT 0,
    total_adjustments_negative DECIMAL(15,4) NOT NULL DEFAULT 0,
    ending_quantity DECIMAL(15,4) NOT NULL DEFAULT 0,
    ending_value DECIMAL(15,2) NOT NULL DEFAULT 0,
    calculated_quantity DECIMAL(15,4) NOT NULL DEFAULT 0,
    variance DECIMAL(15,4) NOT NULL DEFAULT 0,
    reconciliation_date DATE NOT NULL,
    certified_by INT NULL,
    certification_date DATE NULL,
    submitted_to_cbp TINYINT(1) DEFAULT 0,
    submission_date DATE NULL,
    notes TEXT NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_year_lot (zone_lot_id, reconciliation_year),
    INDEX idx_year (reconciliation_year),
    INDEX idx_zone_lot (zone_lot_id),
    FOREIGN KEY (zone_lot_id) REFERENCES ftz_zone_lots(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (certified_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FTZ Admissions (Goods entering the zone)
CREATE TABLE IF NOT EXISTS ftz_admissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    zone_lot_id INT NOT NULL,
    admission_number VARCHAR(50) NOT NULL,
    admission_date DATE NOT NULL,
    entry_number VARCHAR(50) NULL,
    supplier_id INT NULL,
    carrier VARCHAR(100) NULL,
    vessel_flight VARCHAR(100) NULL,
    manifest_number VARCHAR(50) NULL,
    quantity_admitted DECIMAL(15,4) NOT NULL,
    total_value DECIMAL(15,2) NOT NULL,
    receiving_location VARCHAR(100) NULL,
    cbp_officer VARCHAR(100) NULL,
    documentation_path VARCHAR(255) NULL,
    notes TEXT NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_zone_lot (zone_lot_id),
    INDEX idx_admission_date (admission_date),
    INDEX idx_admission_number (admission_number),
    FOREIGN KEY (zone_lot_id) REFERENCES ftz_zone_lots(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FTZ Removals (Goods leaving the zone)
CREATE TABLE IF NOT EXISTS ftz_removals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    zone_lot_id INT NOT NULL,
    removal_number VARCHAR(50) NOT NULL,
    removal_date DATE NOT NULL,
    removal_type ENUM('DOMESTIC_CONSUMPTION', 'EXPORT', 'DESTRUCTION', 'TRANSFER') NOT NULL,
    entry_number VARCHAR(50) NULL,
    customer_id INT NULL,
    quantity_removed DECIMAL(15,4) NOT NULL,
    total_value DECIMAL(15,2) NOT NULL,
    duty_paid DECIMAL(15,2) NULL,
    destination VARCHAR(255) NULL,
    carrier VARCHAR(100) NULL,
    documentation_path VARCHAR(255) NULL,
    notes TEXT NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_zone_lot (zone_lot_id),
    INDEX idx_removal_date (removal_date),
    INDEX idx_removal_type (removal_type),
    FOREIGN KEY (zone_lot_id) REFERENCES ftz_zone_lots(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FTZ Movements (Internal transfers and adjustments)
CREATE TABLE IF NOT EXISTS ftz_movements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    zone_lot_id INT NOT NULL,
    movement_type ENUM('INTERNAL_TRANSFER', 'FUNGIBLE_TRANSFER', 'ADJUSTMENT', 'RECLASSIFICATION') NOT NULL,
    movement_date DATE NOT NULL,
    quantity DECIMAL(15,4) NOT NULL COMMENT 'Positive for additions, negative for reductions',
    from_location VARCHAR(100) NULL,
    to_location VARCHAR(100) NULL,
    reason TEXT NULL,
    authorized_by INT NULL,
    documentation_path VARCHAR(255) NULL,
    notes TEXT NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_zone_lot (zone_lot_id),
    INDEX idx_movement_date (movement_date),
    INDEX idx_movement_type (movement_type),
    FOREIGN KEY (zone_lot_id) REFERENCES ftz_zone_lots(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (authorized_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FTZ Manufacturing Events (Production activities in the zone)
CREATE TABLE IF NOT EXISTS ftz_manufacturing_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    zone_lot_id INT NOT NULL,
    event_type ENUM('COMPONENT_CONSUMPTION', 'FINISHED_GOODS_PRODUCTION', 'WASTE_GENERATION') NOT NULL,
    production_date DATE NOT NULL,
    work_order_number VARCHAR(50) NULL,
    component_quantity DECIMAL(15,4) NULL,
    finished_quantity DECIMAL(15,4) NULL,
    waste_quantity DECIMAL(15,4) NULL,
    production_notes TEXT NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_zone_lot (zone_lot_id),
    INDEX idx_production_date (production_date),
    INDEX idx_event_type (event_type),
    FOREIGN KEY (zone_lot_id) REFERENCES ftz_zone_lots(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FTZ Destructions (Waste or scrap disposal)
CREATE TABLE IF NOT EXISTS ftz_destructions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    zone_lot_id INT NOT NULL,
    destruction_number VARCHAR(50) NOT NULL,
    destruction_date DATE NOT NULL,
    quantity_destroyed DECIMAL(15,4) NOT NULL,
    destruction_method ENUM('INCINERATION', 'LANDFILL', 'RECYCLING', 'OTHER') NOT NULL,
    reason TEXT NULL,
    cbp_witness VARCHAR(100) NULL,
    witness_date DATE NULL,
    documentation_path VARCHAR(255) NULL,
    notes TEXT NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_zone_lot (zone_lot_id),
    INDEX idx_destruction_date (destruction_date),
    FOREIGN KEY (zone_lot_id) REFERENCES ftz_zone_lots(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FTZ Discrepancies (Inventory variances)
CREATE TABLE IF NOT EXISTS ftz_discrepancies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    zone_lot_id INT NOT NULL,
    discrepancy_date DATE NOT NULL,
    expected_quantity DECIMAL(15,4) NOT NULL,
    actual_quantity DECIMAL(15,4) NOT NULL,
    variance DECIMAL(15,4) NOT NULL,
    discrepancy_type ENUM('OVERAGE', 'SHORTAGE', 'DAMAGE', 'QUALITY_ISSUE') NOT NULL,
    investigation_status ENUM('PENDING', 'IN_PROGRESS', 'RESOLVED', 'WRITTEN_OFF') DEFAULT 'PENDING',
    resolution_notes TEXT NULL,
    resolved_by INT NULL,
    resolved_at DATETIME NULL,
    cbp_notified TINYINT(1) DEFAULT 0,
    cbp_notification_date DATE NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_zone_lot (zone_lot_id),
    INDEX idx_discrepancy_date (discrepancy_date),
    INDEX idx_status (investigation_status),
    FOREIGN KEY (zone_lot_id) REFERENCES ftz_zone_lots(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;