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

import gc.mda.signal_batch.domain.gis.dto.GisBoundaryResponse;
import gc.mda.signal_batch.domain.vessel.dto.TrackResponse;
import gc.mda.signal_batch.domain.vessel.dto.VesselStatsResponse;
import gc.mda.signal_batch.domain.vessel.dto.VesselTracksRequest;
import gc.mda.signal_batch.domain.vessel.dto.CompactVesselTrack;
import gc.mda.signal_batch.global.util.SignalKindCode;
import gc.mda.signal_batch.global.util.TrackSimplificationUtils;
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.sql.Timestamp;
import java.time.Duration;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;


@Slf4j
@Service
public class GisService {

    private final DataSource queryDataSource;

    public GisService(@Qualifier("queryDataSource") DataSource queryDataSource) {
        this.queryDataSource = queryDataSource;
    }

    public List<GisBoundaryResponse> getHaeguBoundaries() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(queryDataSource);

        String sql = """
            SELECT haegu_no, center_lat, center_lon,
                   public.ST_AsGeoJSON(geom) as geom_json
            FROM signal.t_haegu_definitions
            ORDER BY haegu_no
        """;

        return jdbcTemplate.query(sql, (rs, rowNum) ->
            GisBoundaryResponse.builder()
                .haeguNo(rs.getInt("haegu_no"))
                .centerLat(rs.getDouble("center_lat"))
                .centerLon(rs.getDouble("center_lon"))
                .geomJson(rs.getString("geom_json"))
                .build()
        );
    }

    public Map<Integer, VesselStatsResponse> getHaeguVesselStats(int minutes) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(queryDataSource);

        String sql = """
            SELECT haegu_no,
                   COUNT(DISTINCT mmsi) as vessel_count,
                   COALESCE(SUM(distance_nm), 0) as total_distance,
                   COALESCE(AVG(avg_speed), 0) as avg_speed,
                   COUNT(*) as active_tracks
            FROM signal.t_grid_vessel_tracks
            WHERE time_bucket >= NOW() - INTERVAL '%d minutes'
            GROUP BY haegu_no
        """.formatted(minutes);

        Map<Integer, VesselStatsResponse> result = new HashMap<>();

        jdbcTemplate.query(sql, rs -> {
            result.put(rs.getInt("haegu_no"),
                VesselStatsResponse.builder()
                    .vesselCount(rs.getInt("vessel_count"))
                    .totalDistance(rs.getBigDecimal("total_distance"))
                    .avgSpeed(rs.getBigDecimal("avg_speed"))
                    .activeTracks(rs.getInt("active_tracks"))
                    .build()
            );
        });

        return result;
    }

    public List<GisBoundaryResponse> getAreaBoundaries() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(queryDataSource);

        String sql = """
            SELECT area_id, area_name,
                   public.ST_Y(public.ST_Centroid(area_geom)) as center_lat,
                   public.ST_X(public.ST_Centroid(area_geom)) as center_lon,
                   public.ST_AsGeoJSON(area_geom) as geom_json
            FROM signal.t_areas
            ORDER BY area_id
        """;

        return jdbcTemplate.query(sql, (rs, rowNum) ->
            GisBoundaryResponse.builder()
                .areaId(rs.getString("area_id"))
                .areaName(rs.getString("area_name"))
                .centerLat(rs.getDouble("center_lat"))
                .centerLon(rs.getDouble("center_lon"))
                .geomJson(rs.getString("geom_json"))
                .build()
        );
    }

    public Map<String, VesselStatsResponse> getAreaVesselStats(int minutes) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(queryDataSource);

        String sql = """
            SELECT area_id,
                   COUNT(DISTINCT mmsi) as vessel_count,
                   COALESCE(SUM(distance_nm), 0) as total_distance,
                   COALESCE(AVG(avg_speed), 0) as avg_speed,
                   COUNT(*) as active_tracks
            FROM signal.t_area_vessel_tracks
            WHERE time_bucket >= NOW() - INTERVAL '%d minutes'
            GROUP BY area_id
        """.formatted(minutes);

        Map<String, VesselStatsResponse> result = new HashMap<>();

        jdbcTemplate.query(sql, rs -> {
            result.put(rs.getString("area_id"),
                VesselStatsResponse.builder()
                    .vesselCount(rs.getInt("vessel_count"))
                    .totalDistance(rs.getBigDecimal("total_distance"))
                    .avgSpeed(rs.getBigDecimal("avg_speed"))
                    .activeTracks(rs.getInt("active_tracks"))
                    .build()
            );
        });

        return result;
    }

    public List<TrackResponse> getHaeguTracks(Integer haeguNo, int minutes) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(queryDataSource);
        List<TrackResponse> allTracks = 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) {
                String hourlySql = """
                    SELECT DISTINCT t.mmsi, 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.mmsi = t.mmsi
                            AND g.haegu_no = %d
                            AND g.time_bucket >= '%s'
                    )
                    AND t.time_bucket >= '%s'
                    AND t.time_bucket < '%s'
                    ORDER BY t.mmsi, t.time_bucket
                """.formatted(haeguNo, startTime, startTime, currentHour);

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

            String recentSql = """
                SELECT DISTINCT t.mmsi, 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.mmsi = t.mmsi
                        AND g.haegu_no = %d
                        AND g.time_bucket >= '%s'
                )
                AND t.time_bucket >= '%s'
                ORDER BY t.mmsi, t.time_bucket
            """.formatted(haeguNo, currentHour, currentHour);

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

        } else {
            String sql = """
                SELECT DISTINCT t.mmsi, 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.mmsi = t.mmsi
                        AND g.haegu_no = %d
                        AND g.time_bucket >= NOW() - INTERVAL '%d minutes'
                )
                AND t.time_bucket >= NOW() - INTERVAL '%d minutes'
                ORDER BY t.mmsi, t.time_bucket
            """.formatted(haeguNo, minutes, minutes);

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

        log.debug("Fetched {} tracks for haegu {} in last {} minutes",
                  allTracks.size(), haeguNo, minutes);

        return allTracks;
    }

    private TrackResponse mapTrackResponse(ResultSet rs, int rowNum) throws SQLException {
        return TrackResponse.builder()
                .mmsi(rs.getString("mmsi"))
                .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();
    }

    public List<TrackResponse> getAreaTracks(String areaId, int minutes) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(queryDataSource);
        List<TrackResponse> allTracks = 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) {
                String hourlySql = """
                    SELECT DISTINCT t.mmsi, 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.mmsi = t.mmsi
                            AND a.area_id = '%s'
                            AND a.time_bucket >= '%s'
                    )
                    AND t.time_bucket >= '%s'
                    AND t.time_bucket < '%s'
                    ORDER BY t.mmsi, t.time_bucket
                """.formatted(areaId, startTime, startTime, currentHour);

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

            String recentSql = """
                SELECT DISTINCT t.mmsi, 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.mmsi = t.mmsi
                        AND a.area_id = '%s'
                        AND a.time_bucket >= '%s'
                )
                AND t.time_bucket >= '%s'
                ORDER BY t.mmsi, t.time_bucket
            """.formatted(areaId, currentHour, currentHour);

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

        } else {
            String sql = """
                SELECT DISTINCT t.mmsi, 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.mmsi = t.mmsi
                        AND a.area_id = '%s'
                        AND a.time_bucket >= NOW() - INTERVAL '%d minutes'
                )
                AND t.time_bucket >= NOW() - INTERVAL '%d minutes'
                ORDER BY t.mmsi, t.time_bucket
            """.formatted(areaId, minutes, minutes);

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

        log.debug("Fetched {} tracks for area {} in last {} minutes",
                  allTracks.size(), areaId, minutes);

        return allTracks;
    }

    /**
     * 선박별 항적 조회 (계층적 보완 조회 + 간소화)
     */
    public List<CompactVesselTrack> getVesselTracks(VesselTracksRequest request) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(queryDataSource);
        List<CompactVesselTrack> results = new ArrayList<>();

        LocalDateTime startTime = request.getStartTime();
        LocalDateTime endTime = request.getEndTime();

        for (String mmsi : request.getVessels()) {
            List<TrackResponse> tracks = queryVesselTracksWithFallback(
                    jdbcTemplate, mmsi, startTime, endTime);

            tracks.sort((t1, t2) -> t1.getTimeBucket().compareTo(t2.getTimeBucket()));

            if (!tracks.isEmpty()) {
                CompactVesselTrack compactTrack = buildCompactVesselTrack(mmsi, tracks);
                results.add(compactTrack);
            }
        }

        return results;
    }

    /**
     * 계층적 보완 조회 로직
     */
    private List<TrackResponse> queryVesselTracksWithFallback(
            JdbcTemplate jdbcTemplate, String mmsi,
            LocalDateTime startTime, LocalDateTime endTime) {

        List<TrackResponse> allTracks = new ArrayList<>();
        Duration duration = Duration.between(startTime, endTime);
        long hours = duration.toHours();

        LocalDateTime now = LocalDateTime.now();
        LocalDateTime safeHourlyBoundary = now.withMinute(0).withSecond(0).withNano(0);
        if (now.getMinute() < 15) {
            safeHourlyBoundary = safeHourlyBoundary.minusHours(1);
        }
        LocalDateTime safeDailyBoundary = now.toLocalDate().atStartOfDay().minusDays(1);

        // === 1단계: Daily 테이블 조회 ===
        Set<LocalDate> coveredDays = new HashSet<>();
        if (hours >= 24 && startTime.toLocalDate().isBefore(safeDailyBoundary.toLocalDate().plusDays(1))) {
            LocalDate dailyStart = startTime.toLocalDate();
            LocalDate dailyEnd = endTime.toLocalDate().isBefore(safeDailyBoundary.toLocalDate())
                    ? endTime.toLocalDate()
                    : safeDailyBoundary.toLocalDate();

            if (!dailyEnd.isBefore(dailyStart)) {
                List<TrackResponse> dailyTracks = queryDailyTracks(
                        jdbcTemplate, mmsi, dailyStart, dailyEnd);

                for (TrackResponse track : dailyTracks) {
                    coveredDays.add(track.getTimeBucket().toLocalDate());
                }
                allTracks.addAll(dailyTracks);

                log.debug("[FALLBACK] Daily: {} days covered for {}",
                        coveredDays.size(), mmsi);
            }
        }

        // === 2단계: Daily 누락 구간 → Hourly에서 보완 ===
        if (hours >= 24) {
            LocalDate dailyStart = startTime.toLocalDate();
            LocalDate dailyEnd = endTime.toLocalDate().isBefore(safeDailyBoundary.toLocalDate())
                    ? endTime.toLocalDate()
                    : safeDailyBoundary.toLocalDate();

            List<LocalDate> missingDays = detectMissingDays(dailyStart, dailyEnd, coveredDays);

            for (LocalDate missingDay : missingDays) {
                LocalDateTime dayStart = missingDay.atStartOfDay();
                LocalDateTime dayEnd = missingDay.plusDays(1).atStartOfDay();

                List<TrackResponse> fallbackTracks = queryHourlyTracks(
                        jdbcTemplate, mmsi, dayStart, dayEnd);

                for (TrackResponse track : fallbackTracks) {
                    track.setTrackGeom(TrackSimplificationUtils.simplifyDailyTrack(track.getTrackGeom()));
                }
                allTracks.addAll(fallbackTracks);

                log.debug("[FALLBACK] Daily missing {} → {} hourly segments (simplified to daily level)",
                        missingDay, fallbackTracks.size());
            }
        }

        // === 3단계: Hourly 테이블 조회 ===
        Set<LocalDateTime> coveredHours = new HashSet<>();
        LocalDateTime hourlyStart = hours >= 24
                ? safeDailyBoundary.plusDays(1)
                : startTime.withMinute(0).withSecond(0).withNano(0);
        LocalDateTime hourlyEnd = endTime.isBefore(safeHourlyBoundary) ? endTime : safeHourlyBoundary;

        if (hours > 1 && hourlyStart.isBefore(hourlyEnd)) {
            List<TrackResponse> hourlyTracks = queryHourlyTracks(
                    jdbcTemplate, mmsi, hourlyStart, hourlyEnd);

            for (TrackResponse track : hourlyTracks) {
                coveredHours.add(track.getTimeBucket().withMinute(0).withSecond(0).withNano(0));
            }
            allTracks.addAll(hourlyTracks);

            log.debug("[FALLBACK] Hourly: {} hours covered for {}",
                    coveredHours.size(), mmsi);
        }

        // === 4단계: Hourly 누락 구간 → 5min에서 보완 ===
        if (hours > 1 && hourlyStart.isBefore(hourlyEnd)) {
            List<LocalDateTime> missingHours = detectMissingHours(hourlyStart, hourlyEnd, coveredHours);

            for (LocalDateTime missingHour : missingHours) {
                LocalDateTime hourStart = missingHour;
                LocalDateTime hourEnd = missingHour.plusHours(1);

                List<TrackResponse> fallbackTracks = query5minTracks(
                        jdbcTemplate, mmsi, hourStart, hourEnd);

                for (TrackResponse track : fallbackTracks) {
                    track.setTrackGeom(TrackSimplificationUtils.simplifyHourlyTrack(track.getTrackGeom()));
                }
                allTracks.addAll(fallbackTracks);

                log.debug("[FALLBACK] Hourly missing {} → {} 5min segments (simplified to hourly level)",
                        missingHour, fallbackTracks.size());
            }
        }

        // === 5단계: 5min 테이블 조회 (최신 데이터) ===
        LocalDateTime fiveMinStart = safeHourlyBoundary.isAfter(startTime) ? safeHourlyBoundary : startTime;
        if (endTime.isAfter(fiveMinStart)) {
            List<TrackResponse> fiveMinTracks = query5minTracks(
                    jdbcTemplate, mmsi, fiveMinStart, endTime);
            allTracks.addAll(fiveMinTracks);

            log.debug("[FALLBACK] 5min: {} segments for {} ({} ~ {})",
                    fiveMinTracks.size(), mmsi, fiveMinStart, endTime);
        }

        return allTracks;
    }

    /**
     * Daily 테이블 조회
     */
    private List<TrackResponse> queryDailyTracks(
            JdbcTemplate jdbcTemplate, String mmsi,
            LocalDate startDate, LocalDate endDate) {

        String sql = """
            SELECT mmsi,
                   time_bucket::timestamp as time_bucket,
                   public.ST_AsText(track_geom) as track_geom,
                   distance_nm, avg_speed, max_speed, point_count
            FROM signal.t_vessel_tracks_daily
            WHERE mmsi = ?
            AND time_bucket BETWEEN ?::date AND ?::date
            ORDER BY time_bucket
        """;

        return jdbcTemplate.query(sql, this::mapTrackResponse,
                mmsi,
                java.sql.Date.valueOf(startDate), java.sql.Date.valueOf(endDate));
    }

    /**
     * Hourly 테이블 조회
     */
    private List<TrackResponse> queryHourlyTracks(
            JdbcTemplate jdbcTemplate, String mmsi,
            LocalDateTime startTime, LocalDateTime endTime) {

        String sql = """
            SELECT mmsi, time_bucket,
                   public.ST_AsText(track_geom) as track_geom,
                   distance_nm, avg_speed, max_speed, point_count
            FROM signal.t_vessel_tracks_hourly
            WHERE mmsi = ?
            AND time_bucket >= ? AND time_bucket < ?
            ORDER BY time_bucket
        """;

        return jdbcTemplate.query(sql, this::mapTrackResponse,
                mmsi,
                Timestamp.valueOf(startTime), Timestamp.valueOf(endTime));
    }

    /**
     * 5min 테이블 조회
     */
    private List<TrackResponse> query5minTracks(
            JdbcTemplate jdbcTemplate, String mmsi,
            LocalDateTime startTime, LocalDateTime endTime) {

        String sql = """
            SELECT mmsi, time_bucket,
                   public.ST_AsText(track_geom) as track_geom,
                   distance_nm, avg_speed, max_speed, point_count
            FROM signal.t_vessel_tracks_5min
            WHERE mmsi = ?
            AND time_bucket >= ? AND time_bucket < ?
            ORDER BY time_bucket
        """;

        return jdbcTemplate.query(sql, this::mapTrackResponse,
                mmsi,
                Timestamp.valueOf(startTime), Timestamp.valueOf(endTime));
    }

    /**
     * 누락된 일자 감지
     */
    private List<LocalDate> detectMissingDays(LocalDate start, LocalDate end, Set<LocalDate> coveredDays) {
        List<LocalDate> missingDays = new ArrayList<>();
        LocalDate current = start;

        while (!current.isAfter(end)) {
            if (!coveredDays.contains(current)) {
                missingDays.add(current);
            }
            current = current.plusDays(1);
        }

        return missingDays;
    }

    /**
     * 누락된 시간 감지
     */
    private List<LocalDateTime> detectMissingHours(
            LocalDateTime start, LocalDateTime end, Set<LocalDateTime> coveredHours) {

        List<LocalDateTime> missingHours = new ArrayList<>();
        LocalDateTime current = start.withMinute(0).withSecond(0).withNano(0);

        while (current.isBefore(end)) {
            if (!coveredHours.contains(current)) {
                missingHours.add(current);
            }
            current = current.plusHours(1);
        }

        return missingHours;
    }

    private CompactVesselTrack buildCompactVesselTrack(
            String mmsi,
            List<TrackResponse> tracks) {

        List<double[]> geometry = new ArrayList<>();
        List<String> timestamps = new ArrayList<>();
        List<Double> speeds = new ArrayList<>();
        double totalDistance = 0;
        double maxSpeed = 0;

        for (TrackResponse track : tracks) {
            if (track.getTrackGeom() != null && !track.getTrackGeom().isEmpty()) {
                try {
                    String wkt = track.getTrackGeom();
                    if (wkt.startsWith("LINESTRING M")) {
                        String coordsPart = wkt.substring("LINESTRING M(".length() + 1, wkt.length() - 1);
                        String[] points = coordsPart.split(",");

                        for (String point : points) {
                            String[] parts = point.trim().split("\\s+");
                            if (parts.length >= 3) {
                                double lon = Double.parseDouble(parts[0]);
                                double lat = Double.parseDouble(parts[1]);
                                String timestamp = parts[2];

                                geometry.add(new double[]{lon, lat});
                                timestamps.add(timestamp);

                                if (track.getAvgSpeed() != null) {
                                    speeds.add(track.getAvgSpeed().doubleValue());
                                } else {
                                    speeds.add(0.0);
                                }
                            }
                        }
                    }
                } catch (Exception e) {
                    log.warn("Failed to parse track geometry: {}", e.getMessage());
                }
            }

            if (track.getDistanceNm() != null) {
                totalDistance += track.getDistanceNm().doubleValue();
            }
            if (track.getMaxSpeed() != null && track.getMaxSpeed().doubleValue() > maxSpeed) {
                maxSpeed = track.getMaxSpeed().doubleValue();
            }
        }

        double avgSpeed = speeds.stream()
            .filter(s -> s > 0)
            .mapToDouble(Double::doubleValue)
            .average()
            .orElse(0.0);

        Map<String, String> vesselInfo = getVesselInfo(mmsi);
        String shipName = vesselInfo.get("ship_name");
        String shipType = vesselInfo.get("ship_type");

        String nationalCode = (mmsi != null && mmsi.length() >= 3) ? mmsi.substring(0, 3) : null;
        String shipKindCode = SignalKindCode.resolve(shipType, null).getCode();

        return CompactVesselTrack.builder()
            .vesselId(mmsi)
            .nationalCode(nationalCode)
            .geometry(geometry)
            .timestamps(timestamps)
            .speeds(speeds)
            .totalDistance(totalDistance)
            .avgSpeed(avgSpeed)
            .maxSpeed(maxSpeed)
            .pointCount(geometry.size())
            .shipName(shipName)
            .shipType(shipType)
            .shipKindCode(shipKindCode)
            .build();
    }

    private Map<String, String> getVesselInfo(String mmsi) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(queryDataSource);
        try {
            String sql = """
                SELECT ship_nm as ship_name, vessel_type as ship_type
                FROM signal.t_ais_position
                WHERE mmsi = ?
                LIMIT 1
            """;

            return jdbcTemplate.queryForMap(sql, mmsi)
                .entrySet().stream()
                .collect(Collectors.toMap(
                    Map.Entry::getKey,
                    e -> e.getValue() != null ? e.getValue().toString() : ""
                ));
        } catch (Exception e) {
            return Map.of("ship_name", "", "ship_type", "");
        }
    }
}
