package at.procon.ted.service;

import at.procon.ted.service.SimilaritySearchService.SimilarDocument;
import at.procon.ted.service.SimilaritySearchService.SimilaritySearchResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;

/**
 * Service for exporting similarity search results to Excel (XLSX) format.
 *
 * @author Martin.Schweitzer@procon.co.at and claude.ai
 */
@Service
@Slf4j
public class ExcelExportService {

    private static final String[] HEADERS = {
            "Rang",
            "Ähnlichkeit %",
            "Publication ID",
            "Projekt Titel",
            "Auftraggeber",
            "Land",
            "Stadt",
            "Vertragsart",
            "Verfahrensart",
            "Publikationsdatum",
            "Einreichfrist",
            "Geschätzter Wert",
            "Währung",
            "CPV Codes",
            "TED Link"
    };

    /**
     * Export similarity search results to an Excel file.
     *
     * @param response       the similarity search response
     * @param sourceFilename the name of the source PDF file
     * @param outputDir      the output directory for the Excel file
     * @return the path to the generated Excel file
     */
    public String exportToExcel(SimilaritySearchResponse response, String sourceFilename, String outputDir) throws IOException {
        // Ensure output directory exists
        File dir = new File(outputDir);
        if (!dir.exists()) {
            dir.mkdirs();
        }

        // Generate output filename
        String baseName = sourceFilename != null
                ? sourceFilename.replaceAll("\\.[^.]+$", "")  // Remove extension
                : "search_results";
        String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss"));
        String outputFilename = baseName + "_results_" + timestamp + ".xlsx";
        File outputFile = new File(dir, outputFilename);

        log.info("Exporting {} results to Excel: {}", response.getResultCount(), outputFile.getAbsolutePath());

        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Ähnliche Ausschreibungen");

            // Create styles
            CellStyle headerStyle = createHeaderStyle(workbook);
            CellStyle linkStyle = createLinkStyle(workbook);
            CellStyle percentStyle = createPercentStyle(workbook);
            CellStyle dateStyle = createDateStyle(workbook);
            CellStyle currencyStyle = createCurrencyStyle(workbook);

            // Create header row
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < HEADERS.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(HEADERS[i]);
                cell.setCellStyle(headerStyle);
            }

            // Add data rows (source info is in filename, no need to add it here)
            List<SimilarDocument> results = response.getResults();
            int rowNum = 1;  // Start directly after header
            int rank = 1;

            for (SimilarDocument doc : results) {
                Row row = sheet.createRow(rowNum++);
                int colNum = 0;

                // Rang
                row.createCell(colNum++).setCellValue(rank++);

                // Ähnlichkeit %
                Cell simCell = row.createCell(colNum++);
                if (doc.getSimilarityPercent() != null) {
                    simCell.setCellValue(doc.getSimilarityPercent());
                    simCell.setCellStyle(percentStyle);
                }

                // Publication ID
                row.createCell(colNum++).setCellValue(doc.getPublicationId() != null ? doc.getPublicationId() : "");

                // Projekt Titel
                row.createCell(colNum++).setCellValue(doc.getProjectTitle() != null ? doc.getProjectTitle() : "");

                // Auftraggeber
                row.createCell(colNum++).setCellValue(doc.getBuyerName() != null ? doc.getBuyerName() : "");

                // Land
                row.createCell(colNum++).setCellValue(doc.getBuyerCountryCode() != null ? doc.getBuyerCountryCode() : "");

                // Stadt
                row.createCell(colNum++).setCellValue(doc.getBuyerCity() != null ? doc.getBuyerCity() : "");

                // Vertragsart
                row.createCell(colNum++).setCellValue(doc.getContractNature() != null ? doc.getContractNature() : "");

                // Verfahrensart
                row.createCell(colNum++).setCellValue(doc.getProcedureType() != null ? doc.getProcedureType() : "");

                // Publikationsdatum
                Cell pubDateCell = row.createCell(colNum++);
                if (doc.getPublicationDate() != null) {
                    pubDateCell.setCellValue(doc.getPublicationDate().toString());
                    pubDateCell.setCellStyle(dateStyle);
                }

                // Einreichfrist
                Cell deadlineCell = row.createCell(colNum++);
                if (doc.getSubmissionDeadline() != null) {
                    deadlineCell.setCellValue(doc.getSubmissionDeadline().toLocalDate().toString());
                    deadlineCell.setCellStyle(dateStyle);
                }

                // Geschätzter Wert
                Cell valueCell = row.createCell(colNum++);
                if (doc.getEstimatedValue() != null) {
                    valueCell.setCellValue(doc.getEstimatedValue().doubleValue());
                    valueCell.setCellStyle(currencyStyle);
                }

                // Währung
                row.createCell(colNum++).setCellValue(
                        doc.getEstimatedValueCurrency() != null ? doc.getEstimatedValueCurrency() : "");

                // CPV Codes
                String cpvCodes = doc.getCpvCodes() != null && !doc.getCpvCodes().isEmpty()
                        ? String.join(", ", doc.getCpvCodes())
                        : "";
                row.createCell(colNum++).setCellValue(cpvCodes);

                // TED Link (Hyperlink)
                Cell linkCell = row.createCell(colNum);
                if (doc.getNoticeUrl() != null && !doc.getNoticeUrl().isEmpty()) {
                    linkCell.setCellValue("Zur Ausschreibung");
                    linkCell.setCellStyle(linkStyle);

                    CreationHelper createHelper = workbook.getCreationHelper();
                    Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
                    hyperlink.setAddress(doc.getNoticeUrl());
                    linkCell.setHyperlink(hyperlink);
                }
            }

            // Auto-size columns
            for (int i = 0; i < HEADERS.length; i++) {
                sheet.autoSizeColumn(i);
                // Set minimum width for some columns
                if (i == 3) { // Projekt Titel
                    sheet.setColumnWidth(i, Math.max(sheet.getColumnWidth(i), 50 * 256));
                }
                if (i == 4) { // Auftraggeber
                    sheet.setColumnWidth(i, Math.max(sheet.getColumnWidth(i), 30 * 256));
                }
            }

            // Add filter
            sheet.setAutoFilter(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 0, HEADERS.length - 1));

            // Freeze header row
            sheet.createFreezePane(0, 1);

            // Write to file
            try (FileOutputStream fos = new FileOutputStream(outputFile)) {
                workbook.write(fos);
            }

            log.info("Excel export completed: {} ({} results)", outputFile.getAbsolutePath(), results.size());
            return outputFile.getAbsolutePath();
        }
    }

    private CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);
        style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        return style;
    }

    private CellStyle createLinkStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setUnderline(Font.U_SINGLE);
        font.setColor(IndexedColors.BLUE.getIndex());
        style.setFont(font);
        return style;
    }

    private CellStyle createPercentStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        return style;
    }

    private CellStyle createDateStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        return style;
    }

    private CellStyle createCurrencyStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("#,##0.00"));
        style.setAlignment(HorizontalAlignment.RIGHT);
        return style;
    }
}
