Async Import Architecture

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class Attachment {
    @Id @GeneratedValue
    private Long id;

    @Column(unique = true, nullable = false)
    private String uuid;

    @Lob @Basic(fetch = FetchType.EAGER)
    private byte[] data;

    @ManyToOne(optional = false)
    private Organisation organisation;

    @NotNull
    private String mediaType;

    private Instant expiryDate;

    @NotNull
    private String name;
}

private static final Set<String> ALLOWED_TYPES = Set.of(
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",  // xlsx
    "application/vnd.ms-excel",                                           // xls
    "text/csv",
    "text/plain"  // csv with wrong mime type
);

public enum ImportType {
    EVENT_PARTICIPANT,
    MEMBERSHIP,
    RESULT
}

public enum ImportJobStatus {
    UPLOADED,
    COLUMN_MAPPING,
    CELL_MAPPING,
    PROCESSING,
    COMPLETED,
    FAILED,
    CANCELLED
}

public enum MappingStatus {
    AUTO_MATCHED,     // System matched with high confidence
    MANUAL_MATCHED,   // User confirmed or corrected
    UNMATCHED,        // Cannot match, needs user input
    IGNORED           // User chose to skip
}

public enum ImportOutcome {
    CREATED,
    UPDATED,
    SKIPPED,
    ERROR
}

public interface CellValueResolver {
    /**
     * Attempt to resolve a source value to an entity ID.
     */
    CellResolutionResult resolve(String sourceValue, ImportContext context);

    /**
     * Get all valid target values for UI dropdown.
     */
    List<TargetOptionDTO> getAvailableTargets(ImportContext context);
}

@Configuration
@EnableAsync
public class AsyncConfig {

    @Bean(name = "importTaskExecutor")
    public Executor importTaskExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(2);
        executor.setMaxPoolSize(5);
        executor.setQueueCapacity(25);
        executor.setThreadNamePrefix("import-");
        executor.initialize();
        return executor;
    }
}

@Service
public class ImportProcessingService {

    @Async("importTaskExecutor")
    public CompletableFuture<Void> processImport(UUID jobId) {
        ImportJob job = findByUuid(jobId);

        try {
            job.setStatus(PROCESSING);
            save(job);

            SpreadsheetReader reader = createReader(job);
            int rowIndex = 1;  // Skip header

            while (rowIndex < reader.getRowCount()) {
                // Check cancellation
                if (isCancelled(jobId)) return complete();

                try {
                    processRow(reader.getRow(rowIndex), job);
                    job.incrementSuccess();
                } catch (UnmappedCellException e) {
                    // Pause for user input
                    savePendingCellMapping(job, e);
                    job.setStatus(CELL_MAPPING);
                    save(job);
                    return complete();
                } catch (Exception e) {
                    saveError(job, rowIndex, e);
                    job.incrementError();
                }

                job.setProcessedRows(rowIndex);
                if (rowIndex % 100 == 0) save(job);
                rowIndex++;
            }

            job.setStatus(COMPLETED);
            job.setCompletedAt(Instant.now());
            save(job);

        } catch (Exception e) {
            job.setStatus(FAILED);
            save(job);
        }

        return complete();
    }
}

@Service
public class ImportCleanupService {

    @Scheduled(cron = "0 0 2 * * ?")  // Daily at 2 AM
    @Transactional
    public void cleanupExpiredImports() {
        Instant now = Instant.now();

        // Completed/Failed/Cancelled: 7 days
        deleteByStatusesOlderThan(
            Set.of(COMPLETED, FAILED, CANCELLED),
            now.minus(7, ChronoUnit.DAYS)
        );

        // Abandoned: 90 days
        deleteByStatusesOlderThan(
            Set.of(UPLOADED, COLUMN_MAPPING, CELL_MAPPING),
            now.minus(90, ChronoUnit.DAYS)
        );
    }

    private void deleteByStatusesOlderThan(Set<Status> statuses, Instant cutoff) {
        List<ImportJob> jobs = repository.findByStatusInAndCreatedAtBefore(statuses, cutoff);
        for (ImportJob job : jobs) {
            attachmentRepository.delete(job.getSourceFile());
            repository.delete(job);
        }
    }
}