import java.io.File;
import java.io.IOException;
import java.util.function.Consumer;

import org.apache.poi.ss.usermodel.*;
import org.junit.jupiter.api.Assertions;

/**
 * Main entry point for Excel-specific assertions.
 * Provides a fluent API for testing Excel file contents.
 *
 * <p>Usage:</p>
 * <pre>{@code
 * ExcelAssertions.assertThat(file)
 *     .sheet(0)
 *     .hasRowCount(10)
 *     .cell(0, 0)
 *     .hasStringValue("Header");
 * }</pre>
 */
public class ExcelAssertions {

    /**
     * Entry point for workbook assertions from a file.
     */
    public static WorkbookAssert assertThat(File file) {
        try {
            Workbook workbook = WorkbookFactory.create(file);
            return new WorkbookAssert(workbook, true);
        } catch (IOException e) {
            throw new AssertionError("Failed to open workbook: " + file, e);
        }
    }

    /**
     * Entry point for workbook assertions from a Workbook object.
     */
    public static WorkbookAssert assertThat(Workbook workbook) {
        return new WorkbookAssert(workbook, false);
    }

    public static SheetAssert assertThat(Sheet sheet) {
        return new SheetAssert(sheet, sheet.getWorkbook());
    }

    public static RowAssert assertThat(Row row) {
        return new RowAssert(row, row.getSheet());
    }

    public static CellAssert assertThat(Cell cell) {
        return new CellAssert(cell, cell.getSheet());
    }

    /**
     * Workbook-level assertions.
     */
    public static class WorkbookAssert implements AutoCloseable {
        protected final Workbook workbook;
        private final boolean shouldClose;

        public WorkbookAssert(Workbook workbook, boolean shouldClose) {
            this.workbook = workbook;
            this.shouldClose = shouldClose;
        }

        public WorkbookAssert hasSheetCount(int expected) {
            Assertions.assertEquals(
                    expected,
                    workbook.getNumberOfSheets(),
                    "Expected sheet count " + expected + " but was " + workbook.getNumberOfSheets());
            return this;
        }

        public WorkbookAssert hasSheetNamed(String name) {
            Sheet sheet = workbook.getSheet(name);
            Assertions.assertNotNull(sheet, "Sheet named '" + name + "' not found");
            return this;
        }

        public SheetAssert sheet(int index) {
            Sheet sheet = workbook.getSheetAt(index);
            Assertions.assertNotNull(sheet, "Sheet at index " + index + " is null");
            return new SheetAssert(sheet, workbook);
        }

        public SheetAssert sheet(String name) {
            Sheet sheet = workbook.getSheet(name);
            Assertions.assertNotNull(sheet, "Sheet named '" + name + "' not found");
            return new SheetAssert(sheet, workbook);
        }

        @Override
        public void close() throws Exception {
            if (shouldClose && workbook != null) {
                workbook.close();
            }
        }
    }

    /**
     * Sheet-level assertions.
     */
    public static class SheetAssert {
        protected final Sheet sheet;
        protected final Workbook workbook;

        public SheetAssert(Sheet sheet, Workbook workbook) {
            this.sheet = sheet;
            this.workbook = workbook;
        }

        public WorkbookAssert and() {
            return new WorkbookAssert(workbook, false);
        }

        public SheetAssert satisfies(Consumer<Sheet> consumer) {
            consumer.accept(this.sheet);
            return this;
        }

        public SheetAssert hasRowCount(int expected) {
            int actual = sheet.getPhysicalNumberOfRows();
            Assertions.assertEquals(expected, actual, "Expected row count " + expected + " but was " + actual);
            return this;
        }

        public RowAssert row(int rowIndex) {
            Row row = sheet.getRow(rowIndex);
            Assertions.assertNotNull(row, "Row at index " + rowIndex + " does not exist");
            return new RowAssert(row, sheet);
        }

        public SheetAssert hasColumnWidth(int col, int expectedWidth) {
            int actualWidth = sheet.getColumnWidth(col);
            Assertions.assertEquals(
                    expectedWidth,
                    actualWidth,
                    "Expected column width " + expectedWidth + " for column " + col + " but was " + actualWidth);
            return this;
        }

        public CellAssert cell(int row, int col) {
            return row(row).cell(col);
        }

        public SheetAssert hasCellAt(int row, int col) {
            cell(row, col);
            return this;
        }
    }

    /**
     * Row-level assertions.
     */
    public static class RowAssert {
        private final Row row;
        private final Sheet sheet;

        public RowAssert(Row row, Sheet sheet) {
            this.row = row;
            this.sheet = sheet;
        }

        public SheetAssert and() {
            return new SheetAssert(sheet, sheet.getWorkbook());
        }

        public RowAssert satisfies(Consumer<Row> consumer) {
            consumer.accept(this.row);
            return this;
        }

        public RowAssert hasCellCount(int expected) {
            int actual = row.getPhysicalNumberOfCells();
            Assertions.assertEquals(expected, actual,
                    "Expected cell count " + expected + " at row " + row.getRowNum() + " but was " + actual);
            return this;
        }

        public RowAssert hasHeight(short expected) {
            Assertions.assertEquals(expected, row.getHeight(),
                    "Expected row height " + expected + " but was " + row.getHeight());
            return this;
        }

        public CellAssert cell(int col) {
            Cell cell = row.getCell(col);
            Assertions.assertNotNull(cell, "Cell at row " + row.getRowNum() + ", column " + col + " does not exist");
            return new CellAssert(cell, sheet);
        }
    }

    /**
     * Cell-level assertions.
     */
    public static class CellAssert {
        private final Cell cell;
        private final Sheet sheet;

        public CellAssert(Cell cell, Sheet sheet) {
            this.cell = cell;
            this.sheet = sheet;
        }

        public RowAssert and() {
            return new RowAssert(cell.getRow(), sheet);
        }

        public CellAssert satisfies(Consumer<Cell> consumer) {
            consumer.accept(this.cell);
            return this;
        }

        public CellAssert hasStringValue(String expected) {
            String actual = getCellValueAsString();
            Assertions.assertEquals(
                    expected, actual, "Expected cell value '" + expected + "' but was '" + actual + "'");
            return this;
        }

        public CellAssert hasNumericValue(double expected) {
            Assertions.assertEquals(
                    CellType.NUMERIC, cell.getCellType(), "Cell is not numeric, got: " + cell.getCellType());
            double actual = cell.getNumericCellValue();
            Assertions.assertEquals(
                    expected, actual, 0.001, "Expected numeric value " + expected + " but was " + actual);
            return this;
        }

        public CellAssert hasBooleanValue(boolean expected) {
            Assertions.assertEquals(
                    CellType.BOOLEAN, cell.getCellType(), "Cell is not boolean, got: " + cell.getCellType());
            boolean actual = cell.getBooleanCellValue();
            Assertions.assertEquals(expected, actual, "Expected boolean value " + expected + " but was " + actual);
            return this;
        }

        public CellAssert hasFormulaValue() {
            Assertions.assertEquals(
                    CellType.FORMULA, cell.getCellType(), "Cell is not a formula, got: " + cell.getCellType());
            return this;
        }

        public CellAssert isEmpty() {
            CellType type = cell.getCellType();
            Assertions.assertTrue(
                    type == CellType.BLANK
                            || (type == CellType.STRING
                                    && cell.getStringCellValue().trim().isEmpty()),
                    "Expected cell to be empty, but was: " + getCellValueAsString());
            return this;
        }

        public CellAssert hasType(CellType expected) {
            CellType actual = cell.getCellType();
            Assertions.assertEquals(expected, actual, "Expected cell type " + expected + " but was " + actual);
            return this;
        }

        public CellAssert hasFontColor(short expectedColorIndex) {
            Font font = sheet.getWorkbook().getFontAt(cell.getCellStyle().getFontIndex());
            Assertions.assertEquals(
                    expectedColorIndex,
                    font.getColor(),
                    "Expected font color index " + expectedColorIndex + " but was " + font.getColor());
            return this;
        }

        public CellAssert hasFillColor(short expectedColorIndex) {
            Assertions.assertEquals(
                    expectedColorIndex,
                    cell.getCellStyle().getFillForegroundColor(),
                    "Expected fill color index " + expectedColorIndex + " but was "
                            + cell.getCellStyle().getFillForegroundColor());
            return this;
        }

        private String getCellValueAsString() {
            switch (cell.getCellType()) {
                case STRING:
                    return cell.getStringCellValue();
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        return cell.getDateCellValue().toString();
                    } else {
                        double value = cell.getNumericCellValue();
                        // Convert to integer if it's a whole number
                        if (value == Math.floor(value)) {
                            return String.valueOf((long) value);
                        } else {
                            return String.valueOf(value);
                        }
                    }
                case BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                case FORMULA:
                    return cell.getCellFormula();
                case BLANK:
                    return "";
                default:
                    return cell.getStringCellValue();
            }
        }
    }
}