package gc.mda.signal_batch.domain.debug;

import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.Parameter;
import io.swagger.v3.oas.annotations.tags.Tag;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

import javax.sql.DataSource;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;

@Slf4j
@RestController
@RequestMapping("/api/debug")
@Tag(name = "시간 분석 디버그 API", description = "항적 데이터의 시간 정보 및 Unix timestamp 분석 API")
public class DebugTimeController {

    private final DataSource queryDataSource;

    public DebugTimeController(@Qualifier("queryDataSource") DataSource queryDataSource) {
        this.queryDataSource = queryDataSource;
    }
    
    @GetMapping("/time-analysis")
    @Operation(summary = "시간 데이터 분석", description = "특정 선박의 항적 데이터에서 시간 정보(time_bucket, Unix timestamp)를 상세 분석합니다. DB 서버 시간, 최근 데이터, 시간 차이 분석을 포함합니다")
    public Map<String, Object> analyzeTimeData(
            @Parameter(description = "MMSI (기본: 440331240)") @RequestParam(defaultValue = "440331240") String mmsi,
            @Parameter(description = "시작 시간 (형식: yyyy-MM-ddTHH:mm:ss)") @RequestParam(defaultValue = "2025-08-26T08:02:59") String startTime,
            @Parameter(description = "종료 시간 (형식: yyyy-MM-ddTHH:mm:ss)") @RequestParam(defaultValue = "2025-08-27T08:02:59") String endTime) {
        
        JdbcTemplate jdbcTemplate = new JdbcTemplate(queryDataSource);
        Map<String, Object> result = new HashMap<>();
        
        LocalDateTime start = LocalDateTime.parse(startTime);
        LocalDateTime end = LocalDateTime.parse(endTime);
        
        result.put("requestInfo", Map.of(
            "mmsi", mmsi,
            "startTime", startTime,
            "endTime", endTime,
            "startTimestamp", start.atZone(ZoneId.of("Asia/Seoul")).toEpochSecond(),
            "endTimestamp", end.atZone(ZoneId.of("Asia/Seoul")).toEpochSecond()
        ));
        
        // 1. DB 서버 시간 정보 확인
        String timeSql = """
            SELECT 
                NOW() as current_time,
                CURRENT_SETTING('TimeZone') as timezone,
                EXTRACT(epoch FROM NOW()) as current_unix_timestamp
        """;
        
        Map<String, Object> serverTime = jdbcTemplate.queryForMap(timeSql);
        result.put("serverInfo", serverTime);
        
        // 2. 요청 범위의 실제 데이터 확인
        String dataSql = """
            SELECT 
                time_bucket,
                EXTRACT(epoch FROM time_bucket) as time_bucket_unix,
                substring(public.ST_AsText(track_geom), 1, 200) as track_sample,
                distance_nm,
                avg_speed,
                point_count
            FROM signal.t_vessel_tracks_5min 
            WHERE mmsi = ?
            AND time_bucket BETWEEN ? AND ?
            ORDER BY time_bucket
            LIMIT 10
        """;
        
        List<Map<String, Object>> dataRows = jdbcTemplate.query(dataSql, 
            (rs, rowNum) -> {
                Map<String, Object> row = new HashMap<>();
                row.put("time_bucket", rs.getTimestamp("time_bucket"));
                row.put("time_bucket_unix", rs.getLong("time_bucket_unix"));
                row.put("track_sample", rs.getString("track_sample"));
                row.put("distance_nm", rs.getBigDecimal("distance_nm"));
                row.put("avg_speed", rs.getBigDecimal("avg_speed"));
                row.put("point_count", rs.getInt("point_count"));
                
                // Track geometry에서 첫 번째 점의 unix timestamp 추출
                String trackSample = rs.getString("track_sample");
                if (trackSample != null && trackSample.contains("LINESTRING M")) {
                    try {
                        String coordsPart = trackSample.substring(trackSample.indexOf("(") + 1);
                        if (coordsPart.contains(",")) {
                            String firstPoint = coordsPart.split(",")[0].trim();
                            String[] parts = firstPoint.split("\\s+");
                            if (parts.length >= 3) {
                                row.put("first_point_unix", parts[2]);
                                // Unix timestamp를 readable date로 변환
                                long unixTime = Long.parseLong(parts[2]);
                                LocalDateTime dateTime = LocalDateTime.ofInstant(
                                    java.time.Instant.ofEpochSecond(unixTime), 
                                    ZoneId.of("Asia/Seoul")
                                );
                                row.put("first_point_kst", dateTime.format(DateTimeFormatter.ISO_LOCAL_DATE_TIME));
                            }
                        }
                    } catch (Exception e) {
                        row.put("parse_error", e.getMessage());
                    }
                }
                
                return row;
            },
            mmsi, Timestamp.valueOf(start), Timestamp.valueOf(end)
        );
        
        result.put("queryResults", dataRows);
        
        // 3. 더 넓은 범위에서 해당 선박의 최신 데이터 확인
        String recentSql = """
            SELECT 
                time_bucket,
                EXTRACT(epoch FROM time_bucket) as time_bucket_unix,
                substring(public.ST_AsText(track_geom), 1, 100) as track_sample
            FROM signal.t_vessel_tracks_5min 
            WHERE mmsi = ?
            ORDER BY time_bucket DESC
            LIMIT 5
        """;
        
        List<Map<String, Object>> recentRows = jdbcTemplate.query(recentSql,
            (rs, rowNum) -> {
                Map<String, Object> row = new HashMap<>();
                row.put("time_bucket", rs.getTimestamp("time_bucket"));
                row.put("time_bucket_unix", rs.getLong("time_bucket_unix"));
                row.put("track_sample", rs.getString("track_sample"));
                return row;
            },
            mmsi
        );
        
        result.put("recentData", recentRows);
        
        // 4. 시간 차이 분석
        if (!dataRows.isEmpty()) {
            Map<String, Object> firstRow = dataRows.get(0);
            Map<String, Object> lastRow = dataRows.get(dataRows.size() - 1);
            
            Map<String, Object> analysis = new HashMap<>();
            analysis.put("foundRecords", dataRows.size());
            analysis.put("firstRecord", Map.of(
                "time_bucket", firstRow.get("time_bucket"),
                "unix_from_geometry", firstRow.get("first_point_unix"),
                "kst_from_geometry", firstRow.get("first_point_kst")
            ));
            analysis.put("lastRecord", Map.of(
                "time_bucket", lastRow.get("time_bucket"),
                "unix_from_geometry", lastRow.get("first_point_unix"),
                "kst_from_geometry", lastRow.get("first_point_kst")
            ));
            
            result.put("analysis", analysis);
        }
        
        return result;
    }
}