package br.gov.sigefp.rh.service;

import br.gov.sigefp.rh.api.dto.AgentImportDTO;
import br.gov.sigefp.rh.api.dto.AgentImportResultDTO;
import br.gov.sigefp.rh.domain.Agent;
import br.gov.sigefp.rh.domain.AgentBankAccount;
import br.gov.sigefp.rh.domain.AgentContract;
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.InputStream;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.ArrayList;

@Service
@Slf4j
@RequiredArgsConstructor
public class AgentImportService {

    private final AgentRepository agentRepository;
    private final AgentContractService contractService;
    // Removed AgentService to avoid nested @PreAuthorize issues

    public AgentImportResultDTO importAgents(MultipartFile file) {
        AgentImportResultDTO result = AgentImportResultDTO.builder()
                .errors(new ArrayList<>())
                .build();

        try (InputStream is = file.getInputStream();
                Workbook workbook = new XSSFWorkbook(is)) {

            Sheet sheet = workbook.getSheetAt(0);
            int rowIdx = 0;

            for (Row row : sheet) {
                if (rowIdx == 0) { // Skip Header
                    rowIdx++;
                    continue;
                }

                // Skip empty rows
                if (row.getCell(0) == null || row.getCell(0).getStringCellValue().trim().isEmpty()) {
                    continue;
                }

                try {
                    processRow(row);
                    result.setSuccessCount(result.getSuccessCount() + 1);
                } catch (Exception e) {
                    result.setFailureCount(result.getFailureCount() + 1);
                    String errorMsg = String.format("Linha %d: %s", rowIdx + 1, e.getMessage());
                    result.getErrors().add(errorMsg);
                    log.error("Erro importando linha {}", rowIdx + 1, e);
                }
                result.setTotalProcessed(result.getTotalProcessed() + 1);
                rowIdx++;
            }

        } catch (Exception e) {
            log.error("Erro ao ler arquivo Excel", e);
            result.getErrors().add("Erro fatal ao ler arquivo: " + e.getMessage());
        }

        return result;
    }

    @Transactional
    protected void processRow(Row row) {
        // 1. Parse DTO from Excel row
        AgentImportDTO dto = parseRowToDTO(row);

        // 2. Determine matricula: use from Excel or default to NIF
        String matricula = (dto.getMatricula() != null && !dto.getMatricula().isEmpty())
                ? dto.getMatricula()
                : dto.getNif();

        // 3. Validate unique constraints
        if (agentRepository.existsByMatricula(matricula)) {
            throw new IllegalArgumentException("Matrícula já existe: " + matricula);
        }
        if (agentRepository.existsByNif(dto.getNif())) {
            throw new IllegalArgumentException("NIF já existe: " + dto.getNif());
        }
        if (agentRepository.existsByBiNumber(dto.getBi())) {
            throw new IllegalArgumentException("BI já existe: " + dto.getBi());
        }

        // 4. Build Agent entity directly
        LocalDate hireDate = parseDate(dto.getDataInicioContrato());
        if (hireDate == null) {
            hireDate = LocalDate.now();
        }

        // Data de nomeação: usa do Excel ou default para hireDate
        LocalDate appointmentDate = parseDate(dto.getNomeacao());
        if (appointmentDate == null) {
            appointmentDate = hireDate;
        }

        Agent agent = Agent.builder()
                .fullName(dto.getNome())
                .nif(dto.getNif())
                .biNumber(dto.getBi())
                .matricula(matricula) // Use from Excel or default to NIF
                .birthDate(parseDate(dto.getDataNascimento()))
                .status("ACTIVE")
                .hireDate(hireDate)
                .posseDate(appointmentDate) // Data de nomeação/posse
                .literaryQualification(dto.getHabilitacaoLiteraria())
                .appointmentType("PROVISORIA")
                .functionalSituation("ATIVIDADE_NO_QUADRO")
                .build();

        // 5. Save Agent directly (bypassing AgentService.create() @PreAuthorize)
        Agent savedAgent = agentRepository.save(agent);
        log.info("Agente importado com sucesso: {} (ID: {})", savedAgent.getFullName(), savedAgent.getId());

        // 6. Create Contract (if present)
        if (dto.getTipoContrato() != null && !dto.getTipoContrato().isEmpty()) {
            AgentContract contract = AgentContract.builder()
                    .agent(savedAgent)
                    .contractType(dto.getTipoContrato())
                    .startDate(parseDate(dto.getDataInicioContrato()))
                    .endDate(parseDate(dto.getDataFimContrato()))
                    .isActive(true)
                    .build();

            contractService.saveContract(contract);
        }

        // 7. Create Bank Account (if IBAN provided)
        if (dto.getIban() != null && !dto.getIban().isEmpty()) {
            AgentBankAccount bankAccount = AgentBankAccount.builder()
                    .agent(savedAgent)
                    .bank(dto.getBancoCodigo())
                    .iban(dto.getIban())
                    .isPrimary(true) // First bank account is primary
                    .build();

            savedAgent.getBankAccounts().add(bankAccount);
            agentRepository.save(savedAgent);
        }
    }

    private AgentImportDTO parseRowToDTO(Row row) {
        // Column order: NOME, NIF, MATRICULA, BI, DATA_NASCIMENTO, SEXO, ESTADO_CIVIL,
        // HABILITACAO_LITERARIA, CATEGORIA_CODIGO, UNIDADE_ORG_CODIGO,
        // NOMEACAO, TIPO_CONTRATO, DATA_INICIO_CONTRATO, DATA_FIM_CONTRATO,
        // BANCO_CODIGO, IBAN
        return AgentImportDTO.builder()
                .nome(getCellString(row, 0))
                .nif(getCellString(row, 1))
                .matricula(getCellString(row, 2)) // MATRICULA column
                .bi(getCellString(row, 3))
                .dataNascimento(getCellString(row, 4))
                .sexo(getCellString(row, 5))
                .estadoCivil(getCellString(row, 6))
                .habilitacaoLiteraria(getCellString(row, 7))
                .categoriaCodigo(getCellString(row, 8))
                .unidadeOrgCodigo(getCellString(row, 9))
                .nomeacao(getCellString(row, 10)) // NOMEACAO (data de nomeação)
                .tipoContrato(getCellString(row, 11))
                .dataInicioContrato(getCellString(row, 12))
                .dataFimContrato(getCellString(row, 13))
                .bancoCodigo(getCellString(row, 14))
                .iban(getCellString(row, 15))
                .build();
    }

    private String getCellString(Row row, int index) {
        Cell cell = row.getCell(index);
        if (cell == null)
            return null;
        DataFormatter formatter = new DataFormatter();
        return formatter.formatCellValue(cell).trim();
    }

    private LocalDate parseDate(String dateStr) {
        if (dateStr == null || dateStr.isEmpty())
            return null;

        // Formatos suportados
        String[] patterns = {
                "dd/MM/yyyy",
                "dd-MM-yyyy",
                "yyyy-MM-dd",
                "d/M/yyyy",
                "d-M-yyyy",
                "dd/MM/yy",
                "d/M/yy"
        };

        for (String pattern : patterns) {
            try {
                return LocalDate.parse(dateStr, DateTimeFormatter.ofPattern(pattern));
            } catch (DateTimeParseException ignored) {
                // Try next pattern
            }
        }

        // Tenta interpretar número serial do Excel (dias desde 1899-12-30)
        try {
            double excelDate = Double.parseDouble(dateStr);
            // Excel date serial number: days since 1899-12-30
            return LocalDate.of(1899, 12, 30).plusDays((long) excelDate);
        } catch (NumberFormatException ignored) {
            // Not a number
        }

        throw new IllegalArgumentException("Data inválida: " + dateStr + ". Use dd/MM/yyyy");
    }
}
