package br.gov.sigefp.rh.service;

import br.gov.sigefp.common.exception.BusinessException;
import br.gov.sigefp.rh.domain.*;
import br.gov.sigefp.rh.repository.AttendanceRecordRepository;
import br.gov.sigefp.rh.repository.MonthlyAttendanceSheetRepository;
import br.gov.sigefp.rh.repository.AgentRepository;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.time.LocalDate;
import java.util.UUID;

@Service
@RequiredArgsConstructor
@Transactional
@Slf4j
public class AttendanceService {

    private final MonthlyAttendanceSheetRepository sheetRepository;
    private final AttendanceRecordRepository recordRepository;
    private final AgentRepository agentRepository;

    public MonthlyAttendanceSheet getOrCreateSheet(UUID orgUnitId, int month, int year) {
        return sheetRepository.findByOrgUnitIdAndMonthAndYear(orgUnitId, month, year)
                .orElseGet(() -> {
                    MonthlyAttendanceSheet sheet = MonthlyAttendanceSheet.builder()
                            .orgUnitId(orgUnitId)
                            .month(month)
                            .year(year)
                            .status(AttendanceSheetStatus.DRAFT)
                            .build();
                    return sheetRepository.save(sheet);
                });
    }

    public void approveSheet(UUID sheetId, String approverName) {
        MonthlyAttendanceSheet sheet = sheetRepository.findById(sheetId)
                .orElseThrow(() -> new BusinessException("Folha não encontrada", "SHEET_NOT_FOUND",
                        org.springframework.http.HttpStatus.NOT_FOUND));

        sheet.setStatus(AttendanceSheetStatus.APPROVED);
        sheet.setApprovedBy(approverName);
        sheetRepository.save(sheet);
        log.info("Folha de Ponto {} aprovada por {}", sheetId, approverName);
    }

    public void reopenSheet(UUID sheetId) {
        MonthlyAttendanceSheet sheet = sheetRepository.findById(sheetId)
                .orElseThrow(() -> new BusinessException("Folha não encontrada", "SHEET_NOT_FOUND",
                        org.springframework.http.HttpStatus.NOT_FOUND));

        if (AttendanceSheetStatus.CLOSED.equals(sheet.getStatus())) {
            throw new BusinessException("Folha já processada pela contabilidade não pode ser reaberta.", "SHEET_CLOSED",
                    org.springframework.http.HttpStatus.FORBIDDEN);
        }

        sheet.setStatus(AttendanceSheetStatus.DRAFT);
        sheetRepository.save(sheet);
    }

    public void importExcel(UUID sheetId, MultipartFile file) {
        MonthlyAttendanceSheet sheet = sheetRepository.findById(sheetId)
                .orElseThrow(() -> new BusinessException("Folha não encontrada", "SHEET_NOT_FOUND",
                        org.springframework.http.HttpStatus.NOT_FOUND));

        if (!AttendanceSheetStatus.DRAFT.equals(sheet.getStatus())) {
            throw new BusinessException("A folha deve estar em Rascunho para importação.", "INVALID_STATUS",
                    org.springframework.http.HttpStatus.BAD_REQUEST);
        }

        try (Workbook workbook = new XSSFWorkbook(file.getInputStream())) {
            Sheet excelSheet = workbook.getSheetAt(0);

            // Clear existing records to avoid duplication or stale data
            recordRepository.deleteBySheetId(sheetId);

            for (Row row : excelSheet) {
                if (row.getRowNum() < 1)
                    continue; // Skip Header

                Cell matriculaCell = row.getCell(0);
                if (matriculaCell == null)
                    continue;

                String matricula = getCellValueAsString(matriculaCell);
                Agent agent = agentRepository.findByMatricula(matricula)
                        .orElse(null);

                if (agent == null)
                    continue; // Skip unknown agents

                // Iterate Days (Cols 1 to 31)
                for (int day = 1; day <= 31; day++) {
                    Cell dayCell = row.getCell(day);
                    String code = getCellValueAsString(dayCell);

                    if (code != null && !code.isEmpty()) {
                        AttendanceType type = parseType(code);
                        if (type != null) {
                            try {
                                LocalDate date = LocalDate.of(sheet.getYear(), sheet.getMonth(), day);
                                AttendanceRecord record = AttendanceRecord.builder()
                                        .sheet(sheet)
                                        .agent(agent)
                                        .date(date)
                                        .type(type)
                                        .observation("Importado via Excel")
                                        .build();
                                recordRepository.save(record);
                            } catch (Exception e) {
                                // Invalid date (e.g., Feb 30), ignore
                            }
                        }
                    }
                }
            }
        } catch (IOException e) {
            throw new BusinessException("Erro ao ler arquivo Excel", "IO_ERROR",
                    org.springframework.http.HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

    private String getCellValueAsString(Cell cell) {
        if (cell == null)
            return null;
        return switch (cell.getCellType()) {
            case STRING -> cell.getStringCellValue();
            case NUMERIC -> String.valueOf((int) cell.getNumericCellValue());
            default -> null;
        };
    }

    private AttendanceType parseType(String code) {
        return switch (code.toUpperCase().trim()) {
            case "F" -> AttendanceType.ABSENCE_UNJUSTIFIED;
            case "J" -> AttendanceType.ABSENCE_JUSTIFIED;
            case "M" -> AttendanceType.SICK_LEAVE;
            case "V" -> AttendanceType.VACATION;
            default -> null;
        };
    }
}
