package at.procon.dip.extraction.impl;

import at.procon.dip.domain.document.ContentRole;
import at.procon.dip.domain.document.DocumentType;
import at.procon.dip.extraction.spi.DocumentExtractor;
import at.procon.dip.extraction.spi.ExtractedStructuredPayload;
import at.procon.dip.extraction.spi.ExtractionRequest;
import at.procon.dip.extraction.spi.ExtractionResult;
import at.procon.dip.ingestion.util.DocumentImportSupport;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

@Component
@Order(50)
public class SpreadsheetDocumentExtractor implements DocumentExtractor {
    @Override
    public boolean supports(DocumentType documentType, String mimeType) {
        return DocumentImportSupport.isSpreadsheetMime(mimeType);
    }

    @Override
    public ExtractionResult extract(ExtractionRequest extractionRequest) {
        String mimeType = DocumentImportSupport.normalizeMediaType(
                extractionRequest.detectionResult().mimeType());
        String extension = DocumentImportSupport.extensionOf(
                extractionRequest.sourceDescriptor().fileName());
        try {
            String extracted = extractSpreadsheetText(
                    mimeType,
                    extension,
                    extractionRequest.binaryContent(),
                    extractionRequest.textContent());

            if (!StringUtils.hasText(extracted)) {
                return new ExtractionResult(
                        Map.of(),
                        List.of(),
                        List.of("No spreadsheet text content extracted"));
            }

            String normalized = normalizeText(extracted);
            String title = extractionRequest.sourceDescriptor().fileName();
            return new ExtractionResult(
                    Map.of(ContentRole.NORMALIZED_TEXT, normalized),
                    List.of(new ExtractedStructuredPayload("spreadsheet-document", Map.of("title", title))),
                    List.of());
        } catch (Exception e) {
            return new ExtractionResult(
                    Map.of(),
                    List.of(),
                    List.of("Spreadsheet extraction failed: " + e.getMessage()));
        }
    }

    private String extractSpreadsheetText(
            String mimeType,
            String extension,
            byte[] binaryContent,
            String textContent) throws IOException {

        if ("text/csv".equals(mimeType)
                || "text/tab-separated-values".equals(mimeType)
                || "application/csv".equals(mimeType)
                || "application/x-csv".equals(mimeType)
                || "csv".equals(extension)
                || "tsv".equals(extension)) {
            if (StringUtils.hasText(textContent)) {
                return textContent;
            }
            return binaryContent == null ? null : new String(binaryContent, StandardCharsets.UTF_8);
        }

        if (binaryContent == null || binaryContent.length == 0) {
            return null;
        }

        if ("ods".equals(extension)
                || "application/vnd.oasis.opendocument.spreadsheet".equals(mimeType)) {
            throw new IOException("ODS extraction not supported yet");
        }

        try (Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(binaryContent))) {
            return extractWorkbookText(workbook);
        }
    }

    private String extractWorkbookText(Workbook workbook) {
        StringBuilder sb = new StringBuilder();
        DataFormatter formatter = new DataFormatter();

        for (int s = 0; s < workbook.getNumberOfSheets(); s++) {
            Sheet sheet = workbook.getSheetAt(s);
            if (sheet == null) {
                continue;
            }

            if (sb.length() > 0) {
                sb.append("\n\n");
            }
            sb.append("# Sheet: ").append(sheet.getSheetName()).append("\n");

            for (Row row : sheet) {
                List<String> cells = new ArrayList<>();
                for (Cell cell : row) {
                    String value = formatter.formatCellValue(cell);
                    if (value != null) {
                        value = value.trim();
                    }
                    cells.add(value == null ? "" : value);
                }

                while (!cells.isEmpty() && cells.get(cells.size() - 1).isBlank()) {
                    cells.remove(cells.size() - 1);
                }

                if (!cells.isEmpty()) {
                    sb.append(String.join("\t", cells)).append("\n");
                }
            }
        }

        return sb.toString();
    }

    private String normalizeText(String text) {
        if (text == null) {
            return null;
        }
        return text.replace("\r\n", "\n")
                .replace('\r', '\n')
                .replaceAll("\\n{3,}", "\n\n")
                .replaceAll("[ \\t]+\\n", "\n")
                .trim();
    }
}