package gc.mda.signal_batch.domain.vessel.service;

import gc.mda.signal_batch.domain.vessel.dto.RecentVesselPositionDto;
import gc.mda.signal_batch.global.util.SignalKindCode;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Slf4j
@Service
@RequiredArgsConstructor
public class VesselPositionService {

    @Qualifier("queryJdbcTemplate")
    private final JdbcTemplate queryJdbcTemplate;

    // 캐시 서비스 (선택적 의존성 - 활성화 시에만 주입)
    @Autowired(required = false)
    private VesselLatestPositionCache cache;

    /**
     * 최근 위치 업데이트된 선박 조회
     *
     * 조회 전략:
     * 1. 캐시 활성화 시: 메모리 캐시에서 조회 (빠른 응답)
     * 2. 캐시 비활성화/실패 시: DB에서 직접 조회 (기존 로직)
     *
     * @param minutes 조회할 시간 범위 (분 단위)
     * @return 필터링된 선박 위치 목록
     */
    public List<RecentVesselPositionDto> getRecentVesselPositions(int minutes) {
        // 캐시 우선 조회
        if (cache != null) {
            try {
                List<RecentVesselPositionDto> cachedResult = cache.getFilteredPositions(minutes);

                if (!cachedResult.isEmpty()) {
                    log.debug("Cache hit: returned {} vessels for minutes={}", cachedResult.size(), minutes);
                    return cachedResult;
                } else {
                    log.debug("Cache returned empty result, falling back to DB query");
                }
            } catch (Exception e) {
                log.warn("Cache query failed, falling back to DB: {}", e.getMessage());
            }
        }

        // 캐시 미사용 또는 실패 시 DB 조회 (기존 로직)
        return getRecentVesselPositionsFromDB(minutes);
    }

    /**
     * DB에서 직접 조회 (기존 로직 유지 - Fallback)
     */
    private List<RecentVesselPositionDto> getRecentVesselPositionsFromDB(int minutes) {
        log.debug("Querying DB for vessel positions (cache miss or disabled)");

        // 디버깅을 위해 DB 현재 시간과 임계값 시간 확인
        if (log.isDebugEnabled()) {
            String debugSql = """
                SELECT
                    NOW() as db_now,
                    NOW() - INTERVAL '%d minutes' as threshold_time,
                    (SELECT MAX(last_update) FROM signal.t_ais_position) as max_last_update,
                    (SELECT COUNT(*) FROM signal.t_ais_position WHERE last_update >= NOW() - INTERVAL '%d minutes') as matching_count
                """.formatted(minutes, minutes);

            queryJdbcTemplate.query(debugSql, rs -> {
                log.debug("DB NOW: {}", rs.getTimestamp("db_now"));
                log.debug("Threshold time (NOW - {} min): {}", minutes, rs.getTimestamp("threshold_time"));
                log.debug("Max last_update in table: {}", rs.getTimestamp("max_last_update"));
                log.debug("Count matching time condition: {}", rs.getInt("matching_count"));
            });
        }

        String sql = """
            SELECT
                mmsi,
                lon,
                lat,
                sog,
                cog,
                name as ship_nm,
                vessel_type as ship_ty,
                last_update
            FROM signal.t_ais_position
            WHERE last_update >= NOW() - INTERVAL '%d minutes'
            ORDER BY last_update DESC
            """.formatted(minutes);

        log.info("Fetching vessel positions from DB within {} minutes", minutes);

        List<RecentVesselPositionDto> results = queryJdbcTemplate.query(sql, new VesselPositionRowMapper());

        log.info("Found {} vessels from DB within {} minutes", results.size(), minutes);

        return results;
    }
    
    private static class VesselPositionRowMapper implements RowMapper<RecentVesselPositionDto> {
        @Override
        public RecentVesselPositionDto mapRow(ResultSet rs, int rowNum) throws SQLException {
            String mmsi = rs.getString("mmsi");
            String shipTy = rs.getString("ship_ty");

            // shipKindCode 계산 (vesselType 기반, extraInfo 없음)
            String shipKindCode = SignalKindCode.resolve(shipTy, null).getCode();

            // nationalCode 계산 (MMSI 앞 3자리 = MID)
            String nationalCode = mmsi != null && mmsi.length() >= 3
                    ? mmsi.substring(0, 3) : "000";

            return RecentVesselPositionDto.builder()
                    .mmsi(mmsi)
                    .lon(rs.getDouble("lon"))
                    .lat(rs.getDouble("lat"))
                    .sog(rs.getBigDecimal("sog"))
                    .cog(rs.getBigDecimal("cog"))
                    .shipNm(rs.getString("ship_nm"))
                    .shipTy(shipTy)
                    .shipKindCode(shipKindCode)
                    .nationalCode(nationalCode)
                    .lastUpdate(rs.getTimestamp("last_update") != null ?
                            rs.getTimestamp("last_update").toLocalDateTime() : null)
                    .build();
        }
    }
}