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

import gc.mda.signal_batch.domain.vessel.dto.CompactVesselTrack;
import gc.mda.signal_batch.domain.vessel.dto.TrackResponse;
import gc.mda.signal_batch.domain.vessel.dto.IntegrationVessel;
import gc.mda.signal_batch.domain.vessel.service.IntegrationVesselService;
import gc.mda.signal_batch.global.util.IntegrationSignalConstants;
import gc.mda.signal_batch.global.util.TrackConverter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

/**
 * GIS 서비스 V2 - CompactVesselTrack 기반 응답
 * WebSocket API와 동일한 응답 구조 제공
 */
@Slf4j
@Service
public class GisServiceV2 {

    private final DataSource queryDataSource;
    private final IntegrationVesselService integrationVesselService;

    // 선박 정보 캐시 (TTL: 1시간)
    private final ConcurrentHashMap<String, VesselInfoCache> vesselInfoCache = new ConcurrentHashMap<>();
    private static final long VESSEL_CACHE_TTL = 3600_000; // 1시간

    public GisServiceV2(@Qualifier("queryDataSource") DataSource queryDataSource,
                        IntegrationVesselService integrationVesselService) {
        this.queryDataSource = queryDataSource;
        this.integrationVesselService = integrationVesselService;
    }

    /**
     * 해구별 선박 항적 조회 (V2 - CompactVesselTrack 반환)
     */
    public List<CompactVesselTrack> getHaeguTracks(Integer haeguNo, int minutes, boolean filterByIntegration) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(queryDataSource);
        List<TrackResponse> rawTracks = new ArrayList<>();

        LocalDateTime now = LocalDateTime.now();
        LocalDateTime startTime = now.minusMinutes(minutes);

        if (minutes > 60) {
            LocalDateTime currentHour = now.withMinute(0).withSecond(0).withNano(0);

            if (minutes <= 1440) {
                // hourly 테이블에서 과거 데이터 조회
                String hourlySql = """
                    SELECT DISTINCT t.sig_src_cd, t.target_id, t.time_bucket,
                           public.ST_AsText(t.track_geom) as track_geom,
                           t.distance_nm, t.avg_speed, t.max_speed, t.point_count
                    FROM signal.t_vessel_tracks_hourly t
                    WHERE EXISTS (
                        SELECT 1 FROM signal.t_grid_vessel_tracks g
                        WHERE g.sig_src_cd = t.sig_src_cd
                            AND g.target_id = t.target_id
                            AND g.haegu_no = %d
                            AND g.time_bucket >= '%s'
                    )
                    AND t.time_bucket >= '%s'
                    AND t.time_bucket < '%s'
                    ORDER BY t.sig_src_cd, t.target_id, t.time_bucket
                """.formatted(haeguNo, startTime, startTime, currentHour);

                rawTracks.addAll(jdbcTemplate.query(hourlySql, this::mapTrackResponse));
            }

            // 5min 테이블에서 최근 데이터 조회
            String recentSql = """
                SELECT DISTINCT t.sig_src_cd, t.target_id, t.time_bucket,
                       public.ST_AsText(t.track_geom) as track_geom,
                       t.distance_nm, t.avg_speed, t.max_speed, t.point_count
                FROM signal.t_vessel_tracks_5min t
                WHERE EXISTS (
                    SELECT 1 FROM signal.t_grid_vessel_tracks g
                    WHERE g.sig_src_cd = t.sig_src_cd
                        AND g.target_id = t.target_id
                        AND g.haegu_no = %d
                        AND g.time_bucket >= '%s'
                )
                AND t.time_bucket >= '%s'
                ORDER BY t.sig_src_cd, t.target_id, t.time_bucket
            """.formatted(haeguNo, startTime, currentHour);

            rawTracks.addAll(jdbcTemplate.query(recentSql, this::mapTrackResponse));

        } else {
            // 1시간 이하는 5분 테이블만 사용
            String sql = """
                SELECT DISTINCT t.sig_src_cd, t.target_id, t.time_bucket,
                       public.ST_AsText(t.track_geom) as track_geom,
                       t.distance_nm, t.avg_speed, t.max_speed, t.point_count
                FROM signal.t_vessel_tracks_5min t
                WHERE EXISTS (
                    SELECT 1 FROM signal.t_grid_vessel_tracks g
                    WHERE g.sig_src_cd = t.sig_src_cd
                        AND g.target_id = t.target_id
                        AND g.haegu_no = %d
                        AND g.time_bucket >= NOW() - INTERVAL '%d minutes'
                )
                AND t.time_bucket >= NOW() - INTERVAL '%d minutes'
                ORDER BY t.sig_src_cd, t.target_id, t.time_bucket
            """.formatted(haeguNo, minutes, minutes);

            rawTracks = jdbcTemplate.query(sql, this::mapTrackResponse);
        }

        // CompactVesselTrack으로 변환
        List<CompactVesselTrack> result = TrackConverter.convert(rawTracks, this::getVesselInfo);

        // 통합선박 필터링 적용
        if (filterByIntegration && integrationVesselService.isEnabled()) {
            result = filterByIntegration(result);
        }

        log.debug("V2 API: Fetched {} compact tracks for haegu {} in last {} minutes",
                result.size(), haeguNo, minutes);

        return result;
    }

    /**
     * 영역별 선박 항적 조회 (V2 - CompactVesselTrack 반환)
     */
    public List<CompactVesselTrack> getAreaTracks(String areaId, int minutes, boolean filterByIntegration) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(queryDataSource);
        List<TrackResponse> rawTracks = new ArrayList<>();

        LocalDateTime now = LocalDateTime.now();
        LocalDateTime startTime = now.minusMinutes(minutes);

        if (minutes > 60) {
            LocalDateTime currentHour = now.withMinute(0).withSecond(0).withNano(0);

            if (minutes <= 1440) {
                // hourly 테이블에서 과거 데이터 조회
                String hourlySql = """
                    SELECT DISTINCT t.sig_src_cd, t.target_id, t.time_bucket,
                           public.ST_AsText(t.track_geom) as track_geom,
                           t.distance_nm, t.avg_speed, t.max_speed, t.point_count
                    FROM signal.t_vessel_tracks_hourly t
                    WHERE EXISTS (
                        SELECT 1 FROM signal.t_area_vessel_tracks a
                        WHERE a.sig_src_cd = t.sig_src_cd
                            AND a.target_id = t.target_id
                            AND a.area_id = '%s'
                            AND a.time_bucket >= '%s'
                    )
                    AND t.time_bucket >= '%s'
                    AND t.time_bucket < '%s'
                    ORDER BY t.sig_src_cd, t.target_id, t.time_bucket
                """.formatted(areaId, startTime, startTime, currentHour);

                rawTracks.addAll(jdbcTemplate.query(hourlySql, this::mapTrackResponse));
            }

            // 5min 테이블에서 최근 데이터 조회
            String recentSql = """
                SELECT DISTINCT t.sig_src_cd, t.target_id, t.time_bucket,
                       public.ST_AsText(t.track_geom) as track_geom,
                       t.distance_nm, t.avg_speed, t.max_speed, t.point_count
                FROM signal.t_vessel_tracks_5min t
                WHERE EXISTS (
                    SELECT 1 FROM signal.t_area_vessel_tracks a
                    WHERE a.sig_src_cd = t.sig_src_cd
                        AND a.target_id = t.target_id
                        AND a.area_id = '%s'
                        AND a.time_bucket >= '%s'
                )
                AND t.time_bucket >= '%s'
                ORDER BY t.sig_src_cd, t.target_id, t.time_bucket
            """.formatted(areaId, startTime, currentHour);

            rawTracks.addAll(jdbcTemplate.query(recentSql, this::mapTrackResponse));

        } else {
            // 1시간 이하는 5분 테이블만 사용
            String sql = """
                SELECT DISTINCT t.sig_src_cd, t.target_id, t.time_bucket,
                       public.ST_AsText(t.track_geom) as track_geom,
                       t.distance_nm, t.avg_speed, t.max_speed, t.point_count
                FROM signal.t_vessel_tracks_5min t
                WHERE EXISTS (
                    SELECT 1 FROM signal.t_area_vessel_tracks a
                    WHERE a.sig_src_cd = t.sig_src_cd
                        AND a.target_id = t.target_id
                        AND a.area_id = '%s'
                        AND a.time_bucket >= NOW() - INTERVAL '%d minutes'
                )
                AND t.time_bucket >= NOW() - INTERVAL '%d minutes'
                ORDER BY t.sig_src_cd, t.target_id, t.time_bucket
            """.formatted(areaId, minutes, minutes);

            rawTracks = jdbcTemplate.query(sql, this::mapTrackResponse);
        }

        // CompactVesselTrack으로 변환
        List<CompactVesselTrack> result = TrackConverter.convert(rawTracks, this::getVesselInfo);

        // 통합선박 필터링 적용
        if (filterByIntegration && integrationVesselService.isEnabled()) {
            result = filterByIntegration(result);
        }

        log.debug("V2 API: Fetched {} compact tracks for area {} in last {} minutes",
                result.size(), areaId, minutes);

        return result;
    }

    /**
     * TrackResponse 매핑
     */
    private TrackResponse mapTrackResponse(ResultSet rs, int rowNum) throws SQLException {
        return TrackResponse.builder()
                .sigSrcCd(rs.getString("sig_src_cd"))
                .targetId(rs.getString("target_id"))
                .timeBucket(rs.getObject("time_bucket", LocalDateTime.class))
                .trackGeom(rs.getString("track_geom"))
                .distanceNm(rs.getBigDecimal("distance_nm"))
                .avgSpeed(rs.getBigDecimal("avg_speed"))
                .maxSpeed(rs.getBigDecimal("max_speed"))
                .pointCount(rs.getInt("point_count"))
                .build();
    }

    /**
     * 선박 정보 조회 (캐시 우선)
     */
    private TrackConverter.VesselInfo getVesselInfo(String sigSrcCd, String targetId) {
        String cacheKey = sigSrcCd + "_" + targetId;

        VesselInfoCache cached = vesselInfoCache.get(cacheKey);
        if (cached != null && !cached.isExpired()) {
            return new TrackConverter.VesselInfo(cached.shipName, cached.shipType);
        }

        // DB 조회
        JdbcTemplate jdbcTemplate = new JdbcTemplate(queryDataSource);
        try {
            String sql = """
                SELECT ship_nm, ship_ty
                FROM signal.t_vessel_latest_position
                WHERE sig_src_cd = ? AND target_id = ?
                LIMIT 1
            """;

            Map<String, Object> result = jdbcTemplate.queryForMap(sql, sigSrcCd, targetId);
            String shipName = result.get("ship_nm") != null ? result.get("ship_nm").toString() : "-";
            String shipType = result.get("ship_ty") != null ? result.get("ship_ty").toString() : "-";

            // 캐시 저장
            vesselInfoCache.put(cacheKey, new VesselInfoCache(shipName, shipType));

            return new TrackConverter.VesselInfo(shipName, shipType);
        } catch (Exception e) {
            return new TrackConverter.VesselInfo("-", "-");
        }
    }

    /**
     * 통합선박 기준 필터링
     */
    private List<CompactVesselTrack> filterByIntegration(List<CompactVesselTrack> tracks) {
        if (tracks == null || tracks.isEmpty()) {
            return tracks;
        }

        // 1. 모든 트랙의 통합선박 정보 조회
        Map<String, IntegrationVessel> vesselIntegrations = new HashMap<>();
        for (CompactVesselTrack track : tracks) {
            String key = track.getSigSrcCd() + "_" + track.getTargetId();
            if (!vesselIntegrations.containsKey(key)) {
                IntegrationVessel integration = integrationVesselService.findByVessel(
                        track.getSigSrcCd(), track.getTargetId()
                );
                vesselIntegrations.put(key, integration);
            }
        }

        // 2. 통합선박별 그룹핑
        Map<Long, List<CompactVesselTrack>> groupedByIntegration = new HashMap<>();
        Map<Long, IntegrationVessel> integrationMap = new HashMap<>();

        long tempSeq = -1;
        for (CompactVesselTrack track : tracks) {
            String key = track.getSigSrcCd() + "_" + track.getTargetId();
            IntegrationVessel integration = vesselIntegrations.get(key);

            Long seq;
            if (integration != null) {
                seq = integration.getIntgrSeq();
                integrationMap.putIfAbsent(seq, integration);
            } else {
                seq = tempSeq--;
            }

            groupedByIntegration.computeIfAbsent(seq, k -> new ArrayList<>()).add(track);
        }

        // 3. 각 그룹에서 최고 우선순위 신호만 선택
        List<CompactVesselTrack> result = new ArrayList<>();

        for (Map.Entry<Long, List<CompactVesselTrack>> entry : groupedByIntegration.entrySet()) {
            Long seq = entry.getKey();
            List<CompactVesselTrack> groupTracks = entry.getValue();

            if (seq < 0) {
                // 통합정보 없는 단독 선박
                CompactVesselTrack firstTrack = groupTracks.get(0);
                String soloIntegrationId = IntegrationSignalConstants.generateSoloIntegrationId(
                        firstTrack.getSigSrcCd(),
                        firstTrack.getTargetId()
                );
                groupTracks.forEach(t -> t.setIntegrationTargetId(soloIntegrationId));
                result.addAll(groupTracks);
            } else {
                // 통합선박 → 존재하는 신호 중 최고 우선순위 선택
                IntegrationVessel integration = integrationMap.get(seq);

                Set<String> existingSigSrcCds = groupTracks.stream()
                        .map(CompactVesselTrack::getSigSrcCd)
                        .collect(Collectors.toSet());

                String selectedSigSrcCd = integrationVesselService.selectHighestPriorityFromExisting(existingSigSrcCds);

                List<CompactVesselTrack> selectedTracks = groupTracks.stream()
                        .filter(t -> t.getSigSrcCd().equals(selectedSigSrcCd))
                        .collect(Collectors.toList());

                String integrationId = integration.generateIntegrationId();
                selectedTracks.forEach(t -> t.setIntegrationTargetId(integrationId));

                result.addAll(selectedTracks);
            }
        }

        log.info("[INTEGRATION_FILTER] V2 API - Filtered {} tracks to {} tracks", tracks.size(), result.size());
        return result;
    }

    /**
     * 선박 정보 캐시 내부 클래스
     */
    private static class VesselInfoCache {
        String shipName;
        String shipType;
        long cacheTime;

        VesselInfoCache(String shipName, String shipType) {
            this.shipName = shipName;
            this.shipType = shipType;
            this.cacheTime = System.currentTimeMillis();
        }

        boolean isExpired() {
            return System.currentTimeMillis() - cacheTime > VESSEL_CACHE_TTL;
        }
    }
}
