package gc.mda.signal_batch.batch.processor;

import gc.mda.signal_batch.domain.vessel.model.VesselTrack;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.batch.item.ItemProcessor;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.jdbc.core.JdbcTemplate;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import gc.mda.signal_batch.global.util.LineStringMUtils;
import gc.mda.signal_batch.global.util.TrackSimplificationUtils;

import javax.sql.DataSource;


@Slf4j
@ConditionalOnProperty(name = "vessel.batch.scheduler.enabled", havingValue = "true", matchIfMissing = true)
@RequiredArgsConstructor
public class DailyTrackProcessor implements ItemProcessor<VesselTrack.VesselKey, VesselTrack> {

    private final DataSource queryDataSource;
    private final JdbcTemplate jdbcTemplate;
    private static final DateTimeFormatter TIMESTAMP_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    @Override
    public VesselTrack process(VesselTrack.VesselKey vesselKey) throws Exception {
        LocalDateTime dayBucket = vesselKey.getTimeBucket()
                .withHour(0)
                .withMinute(0)
                .withSecond(0)
                .withNano(0);

        String sql = """
            WITH ordered_tracks AS (
                SELECT *
                FROM signal.t_vessel_tracks_hourly
                WHERE mmsi = ?
                    AND time_bucket >= ?
                    AND time_bucket < ?
                    AND track_geom IS NOT NULL
                    AND public.ST_NPoints(track_geom) > 0
                ORDER BY time_bucket
            ),
            merged_coords AS (
                SELECT
                    mmsi,
                    string_agg(
                        substring(public.ST_AsText(track_geom) from 'M \\((.+)\\)'),
                        ','
                        ORDER BY time_bucket
                    ) FILTER (WHERE track_geom IS NOT NULL) as all_coords
                FROM ordered_tracks
                GROUP BY mmsi
            ),
            merged_tracks AS (
                SELECT
                    mc.mmsi,
                    TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS') as time_bucket,
                    public.ST_GeomFromText('LINESTRING M(' || mc.all_coords || ')') as merged_geom,
                    (SELECT MAX(max_speed) FROM ordered_tracks WHERE mmsi = mc.mmsi) as max_speed,
                    (SELECT SUM(point_count) FROM ordered_tracks WHERE mmsi = mc.mmsi) as total_points,
                    (SELECT MIN(time_bucket) FROM ordered_tracks WHERE mmsi = mc.mmsi) as start_time,
                    (SELECT MAX(time_bucket) FROM ordered_tracks WHERE mmsi = mc.mmsi) as end_time,
                    (SELECT start_position FROM ordered_tracks WHERE mmsi = mc.mmsi ORDER BY time_bucket LIMIT 1) as start_pos,
                    (SELECT end_position FROM ordered_tracks WHERE mmsi = mc.mmsi ORDER BY time_bucket DESC LIMIT 1) as end_pos
                FROM merged_coords mc
            ),
            calculated_tracks AS (
                SELECT
                    *,
                    public.ST_Length(merged_geom::geography) / 1852.0 as total_distance,
                    CASE
                        WHEN public.ST_NPoints(merged_geom) > 0 THEN
                            public.ST_M(public.ST_PointN(merged_geom, public.ST_NPoints(merged_geom))) -
                            public.ST_M(public.ST_PointN(merged_geom, 1))
                        ELSE
                            EXTRACT(EPOCH FROM
                                TO_TIMESTAMP(end_pos->>'time', 'YYYY-MM-DD HH24:MI:SS') - TO_TIMESTAMP(start_pos->>'time', 'YYYY-MM-DD HH24:MI:SS')
                            )
                    END as time_diff_seconds
                FROM merged_tracks
            )
            SELECT
                mmsi,
                time_bucket,
                merged_geom,
                total_distance,
                CASE
                    WHEN time_diff_seconds > 0 THEN
                        CAST(LEAST((total_distance / (time_diff_seconds / 3600.0)), 9999.99) AS numeric(6,2))
                    ELSE 0
                END as avg_speed,
                max_speed,
                total_points,
                start_time,
                end_time,
                start_pos,
                end_pos,
                public.ST_AsText(merged_geom) as geom_text
            FROM calculated_tracks
        """;

        LocalDateTime startTime = dayBucket;
        LocalDateTime endTime = dayBucket.plusDays(1);

        Timestamp startTimestamp = Timestamp.valueOf(startTime);
        Timestamp endTimestamp = Timestamp.valueOf(endTime);
        Timestamp dayBucketTimestamp = Timestamp.valueOf(dayBucket);

        log.debug("DailyTrackProcessor params - mmsi: {}, startTime: {}, endTime: {}, dayBucket: {}",
            vesselKey.getMmsi(), startTimestamp, endTimestamp, dayBucketTimestamp);

        try {
            return jdbcTemplate.queryForObject(sql,
                    (rs, rowNum) -> {
                        try {
                            return buildDailyTrack(rs, dayBucket);
                        } catch (Exception e) {
                            throw new RuntimeException("Failed to build daily track", e);
                        }
                    },
                    vesselKey.getMmsi(),
                    startTimestamp, endTimestamp, dayBucketTimestamp
            );
        } catch (org.springframework.dao.EmptyResultDataAccessException e) {
            log.warn("No hourly data found for vessel {} in time range {}-{}, skipping daily aggregation",
                    vesselKey.getMmsi(), startTimestamp, endTimestamp);
            return null;
        } catch (Exception e) {
            log.error("Failed to process daily track for vessel {}: {}",
                    vesselKey.getMmsi(), e.getMessage(), e);
            return null;
        }
    }

    private VesselTrack buildDailyTrack(ResultSet rs, LocalDateTime dayBucket) throws Exception {
        VesselTrack.TrackPosition startPos = null;
        VesselTrack.TrackPosition endPos = null;

        String startPosJson = rs.getString("start_pos");
        String endPosJson = rs.getString("end_pos");

        if (startPosJson != null) {
            startPos = parseTrackPosition(startPosJson);
        }
        if (endPosJson != null) {
            endPos = parseTrackPosition(endPosJson);
        }

        String dailyLineStringM = rs.getString("geom_text");
        String simplifiedLineStringM = TrackSimplificationUtils.simplifyDailyTrack(dailyLineStringM);

        if (!dailyLineStringM.equals(simplifiedLineStringM)) {
            TrackSimplificationUtils.SimplificationStats stats =
                    TrackSimplificationUtils.getSimplificationStats(dailyLineStringM, simplifiedLineStringM);
            log.debug("일별 궤적 간소화 - vessel: {}, 원본: {}포인트, 간소화: {}포인트 ({}% 감소)",
                    rs.getString("mmsi"),
                    stats.originalPoints, stats.simplifiedPoints, (int)stats.reductionRate);
        }

        return VesselTrack.builder()
                .mmsi(rs.getString("mmsi"))
                .timeBucket(dayBucket)
                .trackGeom(simplifiedLineStringM)
                .distanceNm(rs.getBigDecimal("total_distance"))
                .avgSpeed(rs.getBigDecimal("avg_speed"))
                .maxSpeed(rs.getBigDecimal("max_speed"))
                .pointCount(rs.getInt("total_points"))
                .startPosition(startPos)
                .endPosition(endPos)
                .build();
    }

    private VesselTrack.TrackPosition parseTrackPosition(String json) {
        try {
            String latStr = LineStringMUtils.extractJsonValue(json, "lat");
            String lonStr = LineStringMUtils.extractJsonValue(json, "lon");
            String timeStr = LineStringMUtils.extractJsonValue(json, "time");
            String sogStr = LineStringMUtils.extractJsonValue(json, "sog");

            return VesselTrack.TrackPosition.builder()
                    .lat(latStr != null ? Double.parseDouble(latStr) : null)
                    .lon(lonStr != null ? Double.parseDouble(lonStr) : null)
                    .time(timeStr != null ? LocalDateTime.parse(timeStr, TIMESTAMP_FORMATTER) : null)
                    .sog(sogStr != null ? new BigDecimal(sogStr) : null)
                    .build();
        } catch (Exception e) {
            log.error("Failed to parse track position: {}", json, e);
            return null;
        }
    }
}
