package incheon.sgp.thm.service.impl;

import java.math.BigDecimal;
import java.net.URI;
import java.sql.Timestamp;
import java.time.Duration;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;
import java.util.regex.Pattern;

import javax.annotation.PostConstruct;
import javax.net.ssl.SSLContext;

import incheon.sgp.thm.dto.*;
import incheon.sgp.thm.service.SgpThmService;
import org.apache.http.conn.ssl.SSLConnectionSocketFactory;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.ssl.SSLContextBuilder;
import org.locationtech.jts.geom.Coordinate;
import org.locationtech.jts.geom.GeometryFactory;
import org.locationtech.jts.geom.PrecisionModel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.http.client.HttpComponentsClientHttpRequestFactory;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.client.RestClientException;
import org.springframework.web.client.RestTemplate;
import org.springframework.web.util.UriComponentsBuilder;
import org.springframework.transaction.annotation.Transactional;

import incheon.sgp.thm.mapper.SgpThmMapper;
import incheon.sgp.thm.trf.CctvRow;
import incheon.sgp.thm.trf.ItsCctvResponse;
import incheon.sgp.thm.trf.ItsTrafficResponse;
import incheon.sgp.thm.trf.TrafficRow;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@Service
@RequiredArgsConstructor
public class SgpThmServiceImpl implements SgpThmService {
	@Value("${trf.api.base-url:https://openapi.its.go.kr:9443}")
	private String TRF_BASE_URL;

	@Value("${trf.api.key:2c180918cb9c4a92a18239519de3518d}")
	private String TRF_API_KEY;

	@Value("${trf.api.connect-timeout-ms:5000}")
	private int TRF_CONNECT_TIMEOUT_MS;

	@Value("${trf.api.timeout-ms:7000}")
	private int TRF_TIMEOUT_MS;

	@Value("${trf.api.traffic-url:/trafficInfo}")
	private String TRF_TRAFFIC_URL;

	@Value("${trf.api.cctv-url:/cctvInfo}")
	private String TRF_CCTV_URL;

	@Value("${wind.base-url:/resources/sgp/thm/winddata}")
	private String WIND_BASE_URL;

	// ThmServiceImpl
	private final java.util.concurrent.ConcurrentMap<String, Boolean> inFlight = new java.util.concurrent.ConcurrentHashMap<>();

	private final SgpThmMapper sgpThmMapper;
	private RestTemplate trfRestTemplate;

	@PostConstruct
	public void init() {
		try {
			String allowedHost = extractHost(TRF_BASE_URL);
			this.trfRestTemplate = createSecureRestTemplate(
					TRF_CONNECT_TIMEOUT_MS,
					TRF_TIMEOUT_MS,
					allowedHost);
			log.info("ITS RestTemplate initialized (host={})", allowedHost);
		} catch (Exception e) {
			log.error("ITS RestTemplate 초기화 실패, 기본 RestTemplate 사용", e);
			this.trfRestTemplate = new RestTemplate();
		}
	}

	@Override
	public String getThemeListJson() {
		return sgpThmMapper.selectThemeListJson();
	}

	@Override
	public String getThemeJson(String thmId, String q, Integer limit) {
		// q/limit 미지정 시 NULL 전달 → 전체 반환
		String qVal = (StringUtils.hasText(q) ? q : null);
		Integer limVal = (limit != null && limit > 0) ? limit : null;
		return sgpThmMapper.selectThemeJson(thmId, qVal, limVal);
	}

	private static final Map<String, String> CATEGORY_CLASS_MAP = Map.of(
			"CAT_ECO", "ico_lnb-item2",
			"CAT_TRN", "ico_lnb-item6",
			"CAT_DTW", "ico_lnb-item9",
			"CAT_CUL", "ico_lnb-item8",
			"CAT_WEL", "ico_lnb-item1",
			"CAT_ADM", "ico_lnb-item5",
			"CAT_SAF", "ico_lnb-item7",
			"CAT_HOU", "ico_lnb-item3",
			"CAT_ENV", "ico_lnb-item4");
	private static final Pattern FILTER_COLUMN_PATTERN = Pattern.compile("^[A-Za-z0-9_]+$");
	private static final Pattern FILTER_TABLE_PATTERN = Pattern.compile("^[A-Za-z0-9_.]+$");
	private static final String WIND_FILENAME_FORMAT = "%s%s.json";
	private static final DateTimeFormatter WIND_DATE_FMT_COMPACT = DateTimeFormatter.ofPattern("yyyyMMdd");
	private static final DateTimeFormatter WIND_DATE_FMT_DASH = DateTimeFormatter.ofPattern("yyyy-MM-dd");
	private static final DateTimeFormatter WIND_DATE_FMT_SLASH = DateTimeFormatter.ofPattern("yyyy/MM/dd");
	private static final DateTimeFormatter WIND_DATETIME_FMT_COMPACT = DateTimeFormatter.ofPattern("yyyyMMddHHmm");
	private static final DateTimeFormatter WIND_DATETIME_FMT_COMPACT_SEC = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
	private static final DateTimeFormatter WIND_DATETIME_FMT_DASH = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");
	private static final DateTimeFormatter WIND_DATETIME_FMT_DASH_SEC = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
	private static final DateTimeFormatter WIND_DATETIME_FMT_T = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm");
	private static final DateTimeFormatter WIND_DATETIME_FMT_T_SEC = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss");
	private static final DateTimeFormatter WIND_TIME_FMT = DateTimeFormatter.ofPattern("HHmm");
	
	@Override
	public List<SgpThmCategoryDto> getThemeCategories() {
		List<SgpThmCategoryDto> categories = sgpThmMapper.selectThemeCategories();
		categories.forEach(cat -> {
			String className = CATEGORY_CLASS_MAP.get(cat.getId());
			cat.setClassName(className);
		});
		return categories;
	}

	@Override
	public Map<String, List<SgpThmLayerDto>> getThemeLayersByCategory() {
		List<SgpThmLayerDto> layers = sgpThmMapper.selectThemeLayers();
		Map<String, List<SgpThmLayerDto>> grouped = new LinkedHashMap<>();
		for (SgpThmLayerDto layer : layers) {
			String categoryName = layer.getCategoryName();
			if (!StringUtils.hasText(categoryName)) {
				categoryName = StringUtils.hasText(layer.getCategoryId()) ? layer.getCategoryId() : "기타";
			}
			grouped.computeIfAbsent(categoryName, key -> new ArrayList<>()).add(layer);
		}
		return grouped;
	}

	@Override
	public List<String> getFilterList(String thmId, String filter) {
		if (!StringUtils.hasText(thmId) || !StringUtils.hasText(filter)) {
			return List.of();
		}
		if (!FILTER_COLUMN_PATTERN.matcher(filter).matches()) {
			return List.of();
		}
		String sourceTable = sgpThmMapper.selectThemeFilterSourceTable(thmId);
		if (!StringUtils.hasText(sourceTable) || !FILTER_TABLE_PATTERN.matcher(sourceTable).matches()) {
			return List.of();
		}
		if (!sourceTable.contains(".")) {
			sourceTable = "icsgp." + sourceTable;
		}
		return sgpThmMapper.selectThemeFilterList(sourceTable, filter);
	}

	@Override
	public List<String> getFilterListByParent(String thmId, String filterName1, String filterValue, String filterName2) {
		if (!StringUtils.hasText(thmId)
			|| !StringUtils.hasText(filterName1)
			|| !StringUtils.hasText(filterName2)
			|| !StringUtils.hasText(filterValue)) {
			return List.of();
		}
		if (!FILTER_COLUMN_PATTERN.matcher(filterName1).matches()
			|| !FILTER_COLUMN_PATTERN.matcher(filterName2).matches()) {
			return List.of();
		}
		String sourceTable = sgpThmMapper.selectThemeFilterSourceTable(thmId);
		if (!StringUtils.hasText(sourceTable) || !FILTER_TABLE_PATTERN.matcher(sourceTable).matches()) {
			return List.of();
		}
		if (!sourceTable.contains(".")) {
			sourceTable = "icsgp." + sourceTable;
		}
		return sgpThmMapper.selectThemeFilterListByParent(sourceTable, filterName1, filterValue, filterName2);
	}

	@Override
	public List<Map<String, Object>> getLatestTrafficInfo(int roadType, double minX, double minY, double maxX,
			double maxY) {
		return sgpThmMapper.selectLinkGradesInBbox(roadType, minX, minY, maxX, maxY);
	}

	@Override
	public int updateTrafficInfo() {
		Timestamp latest = sgpThmMapper.findLatestCreatedTs();
		boolean stale = (latest == null) || Duration.between(latest.toInstant(), Instant.now()).toMinutes() >= 5;

		if (stale) {
			log.info("thmService.updateTrafficInfo Start...");
			List<TrafficRow> rows = fetchFromOpenApi();

			if (!rows.isEmpty()) {
				final int PARAMS_PER_ROW = 9; // road_type, link_id, start_node_id, end_node_id, road_name, speed,
												// travel_time, created_ts, raw
				final int MAX_PARAMS = 64000; // 드라이버 한계를 약간 밑으로 잡기
				final int CHUNK = Math.max(1, Math.min(3000, MAX_PARAMS / PARAMS_PER_ROW));

				for (List<TrafficRow> part : chunk(rows, CHUNK)) {
					sgpThmMapper.upsertSnapshotBatch(part);
				}
				for (List<TrafficRow> part : chunk(rows, CHUNK)) {
					sgpThmMapper.upsertLatestBatch(part);
				}
				log.info("thmService.updateTrafficInfo Finish.");
				// 스냅샷 보존정책 삭제는 1회만
				return sgpThmMapper.deleteOldSnapshots();
			}

		} else {
			log.info("thmService.updateTrafficInfo Skip.");
		}
		return 0;
	}

	// 백그라운드 래퍼
	@Async("trfExecutor")
	@Override
	public void updateTrafficInfoBg() {
		String key = "run";
		if (inFlight.putIfAbsent(key, Boolean.TRUE) != null) {
			log.info("updateTrafficInfo alredy running.");
			return; // 이미 진행 중
		}
		try {
			updateTrafficInfo(); // 동기 로직 호출
		} catch (RestClientException | DataAccessException e) {
			log.error("[TRF] background update failed", e);
		} finally {
			inFlight.remove(key);
		}
	}
	@Autowired private RestTemplate restTemplate;
	/** OpenAPI 호출 → TrafficRow 변환 */
	private List<TrafficRow> fetchFromOpenApi() {
		// 기본 연결 설정 (ENV 없으면 디폴트 사용)
		final String baseUrl = TRF_BASE_URL;
		final String apiKey = TRF_API_KEY;

		// 요구사항: roadType 매핑 (0→all, 1→ex, 2→its)
		final String typeParam = "all";

		// 요구사항: BBOX 고정
		final String minX = "126.1033";
		final String minY = "37.21217";
		final String maxX = "126.79755";
		final String maxY = "37.82877";

		UriComponentsBuilder uriB = UriComponentsBuilder.fromHttpUrl(baseUrl + TRF_TRAFFIC_URL)
				.queryParam("apiKey", apiKey).queryParam("type", typeParam).queryParam("minX", minX)
				.queryParam("minY", minY).queryParam("maxX", maxX).queryParam("maxY", maxY)
				.queryParam("getType", "json");

		ItsTrafficResponse res = restTemplate.getForObject(uriB.build(true).toUri(), ItsTrafficResponse.class);
		if (res == null || res.getBody() == null || res.getBody().getItems() == null) {
			return List.of();
		}

		// DB 스키마 호환: road_type은 0=고속, 1=국도만 허용
		// 요청 roadType(0=all,1=ex,2=its) ➜ DB코드(0 or 1)로 매핑
		final int dbRoadType = 0;

		return res.getBody().getItems().stream().map(it -> toRow(dbRoadType, it)).filter(Objects::nonNull)
				.collect(java.util.stream.Collectors.toList());
	}

	@Override
	public List<Map<String, Object>> getCctvList(Integer gid, double minX, double minY, double maxX, double maxY) {
		return sgpThmMapper.selectCctvList(gid, minX, minY, maxX, maxY);
	}

	@Override
	public List<Map<String, Object>> getCctvNewList(double minX, double minY, double maxX, double maxY) {
		List<Map<String, Object>> rows = sgpThmMapper.selectCctvNewList(minX, minY, maxX, maxY);
		if (rows == null || rows.isEmpty()) {
			return List.of();
		}

		List<Map<String, Object>> result = new ArrayList<>(rows.size());
		for (Map<String, Object> row : rows) {
			if (row == null || row.isEmpty()) {
				continue;
			}

			String cctvId = toNullableString(row.get("cctvid"));
			String cctvName = toNullableString(row.get("cctvname"));

			List<Double> position = new ArrayList<>(2);
			position.add(toDouble(row.get("xcoord")));
			position.add(toDouble(row.get("ycoord")));

			Map<String, Object> attribute = new LinkedHashMap<>();
			attribute.put("cctvid", cctvId);
			attribute.put("cctvname", cctvName);

			Map<String, Object> mapped = new LinkedHashMap<>();
			mapped.put("video_id", row.get("video_id"));
			mapped.put("position", position);
			mapped.put("name", cctvName);
			mapped.put("attribute", attribute);
			result.add(mapped);
		}
		return result;
	}

	@Override
	public int updateTrafficCctv() {
		Timestamp latest = sgpThmMapper.findLatestCreatedCctvTs();
		boolean stale = (latest == null) || Duration.between(latest.toInstant(), Instant.now()).toMinutes() >= 5;

		if (stale) {
			log.info("thmService.updateTrafficCctv Start...");
			List<CctvRow> rows = fetchFromCctvOpenApi();
			if (!rows.isEmpty()) {
				final int PARAMS_PER_ROW = 5; // road_type, link_id, start_node_id, end_node_id, road_name, speed,
												// travel_time, created_ts, raw
				final int MAX_PARAMS = 64000; // 드라이버 한계를 약간 밑으로 잡기
				final int CHUNK = Math.max(1, Math.min(3000, MAX_PARAMS / PARAMS_PER_ROW));

				for (List<CctvRow> part : chunk(rows, CHUNK)) {
					sgpThmMapper.upsertCctvSnapshotBatch(part);
				}
				for (List<CctvRow> part : chunk(rows, CHUNK)) {
					sgpThmMapper.upsertCctvLatestBatch(part);
				}
				log.info("thmService.updateTrafficCctv Finish.");
				// 스냅샷 보존정책 삭제는 1회만
				return sgpThmMapper.deleteOldCctvSnapshots();
			}

		} else {
			log.info("thmService.updateTrafficCctv Skip.");
		}
		return 0;
	}

	// 백그라운드 래퍼
	@Async("trfCctvExecutor")
	@Override
	public void updateTrafficCctvBg() {
		String key = "run";
		if (inFlight.putIfAbsent(key, Boolean.TRUE) != null) {
			log.info("updateTrafficCctv alredy running.");
			return; // 이미 진행 중
		}
		try {
			updateTrafficCctv(); // 동기 로직 호출
		} catch (RestClientException | DataAccessException e) {
			log.error("[TRF] background update failed", e);
		} finally {
			inFlight.remove(key);
		}
	}

	/** OpenAPI 호출 → CctvRow 변환 (ex + its 모두) */
	private List<CctvRow> fetchFromCctvOpenApi() {

		// DB road_type 매핑 예시: ex=0, its=1
		List<CctvRow> result = new ArrayList<>();

		// type=ex
		result.addAll(fetchFromCctvOpenApiByType("ex", 0));

		// type=its
		result.addAll(fetchFromCctvOpenApiByType("its", 1));

		return result;
	}

	/** type 파라미터별 개별 호출용 내부 메서드 */
	private List<CctvRow> fetchFromCctvOpenApiByType(String typeParam, int dbRoadType) {
		// 기본 연결 설정 (ENV 없으면 디폴트 사용)
		final String baseUrl = TRF_BASE_URL;
		final String apiKey = TRF_API_KEY;

		// BBOX 고정
		final String minX = "126.1033";
		final String minY = "37.21217";
		final String maxX = "126.79755";
		final String maxY = "37.82877";

		UriComponentsBuilder uriB = UriComponentsBuilder
				.fromHttpUrl(baseUrl + TRF_CCTV_URL)
				.queryParam("apiKey", apiKey)
				.queryParam("type", typeParam) // ex / its
				.queryParam("minX", minX)
				.queryParam("minY", minY)
				.queryParam("maxX", maxX)
				.queryParam("maxY", maxY)
				.queryParam("getType", "json")
				.queryParam("cctvType", 4);

		// 앞에서 만든 응답 클래스 사용 (예: CctvApiResponse)
		ItsCctvResponse res = restTemplate.getForObject(uriB.build(true).toUri(), ItsCctvResponse.class);
		if (res == null || res.getResponse() == null || res.getResponse().getData() == null) {
			return List.of();
		}

		return res.getResponse().getData().stream()
				.map(item -> toRow(dbRoadType, item)) // dbRoadType, item → CctvRow 변환 메서드
				.filter(Objects::nonNull)
				.collect(Collectors.toList());
	}

	private static final DateTimeFormatter F = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
	private static final ZoneId KST = ZoneId.of("Asia/Seoul");

	private TrafficRow toRow(int roadType, ItsTrafficResponse.Item i) {
		try {
			BigDecimal sp = parseBig(i.getSpeed());
			BigDecimal tt = parseBig(i.getTravelTime());
			var zdt = LocalDateTime.parse(i.getCreatedDate(), F).atZone(KST);
			String raw = toRawJson(i);
			return new TrafficRow(roadType, i.getLinkId(), i.getStartNodeId(), i.getEndNodeId(), i.getRoadName(), sp,
					tt, Timestamp.from(zdt.toInstant()), raw);
		} catch (NumberFormatException | DateTimeParseException ex) {
			return null;
		}
	}

	private static final GeometryFactory GEOM_FACTORY = new GeometryFactory(new PrecisionModel(), 4326);

	private CctvRow toRow(int dbRoadType, ItsCctvResponse.Data it) {
		if (it == null) {
			return null;
		}

		CctvRow row = new CctvRow();

		// name, url
		row.setName(it.getCctvname());
		row.setUrl(it.getCctvurl());

		// base_date: 현재 시각(KST) 기준 timestamp
		LocalDateTime nowKst = LocalDateTime.now(KST);
		row.setBaseDate(Timestamp.valueOf(nowKst));

		// geom: (coordx, coordy) → Point(4326)
		if (it.getCoordx() != null && it.getCoordy() != null) {
			row.setGeom(
					GEOM_FACTORY.createPoint(
							new Coordinate(it.getCoordx(), it.getCoordy())));
		}

		// road_type: int
		row.setRoadType(dbRoadType);

		return row;
	}

	private static BigDecimal parseBig(String v) {
		return (v == null || v.isBlank()) ? null : new BigDecimal(v);
	}

	private static String q(String s) {
		return "\"" + (s == null ? "" : s.replace("\"", "\\\"")) + "\"";
	}

	private static String toRawJson(ItsTrafficResponse.Item i) {
		return "{" + q("roadName") + ":" + q(i.getRoadName()) + "," + q("roadDrcType") + ":" + q(i.getRoadDrcType())
				+ "," + q("linkNo") + ":" + q(i.getLinkNo()) + "," + q("linkId") + ":" + q(i.getLinkId()) + ","
				+ q("startNodeId") + ":" + q(i.getStartNodeId()) + "," + q("endNodeId") + ":" + q(i.getEndNodeId())
				+ "," + q("speed") + ":" + q(i.getSpeed()) + "," + q("travelTime") + ":" + q(i.getTravelTime()) + ","
				+ q("createdDate") + ":" + q(i.getCreatedDate()) + "}";
	}

	private String extractHost(String baseUrl) {
		try {
			URI uri = UriComponentsBuilder.fromUriString(baseUrl).build().toUri();
			if (uri.getHost() == null || uri.getHost().isBlank()) {
				throw new IllegalArgumentException("host is blank");
			}
			return uri.getHost();
		} catch (Exception e) {
			throw new IllegalStateException("ITS base-url host 파싱 실패: " + baseUrl, e);
		}
	}

	private RestTemplate createSecureRestTemplate(int connectTimeout, int readTimeout, String... allowedHosts)
			throws Exception {
		SSLContext sslContext = SSLContextBuilder.create()
				.loadTrustMaterial((chain, authType) -> true)
				.build();

		String[] enabledProtocols = new String[] { "TLSv1", "TLSv1.1", "TLSv1.2", "TLSv1.3" };

		SSLConnectionSocketFactory socketFactory = new SSLConnectionSocketFactory(
				sslContext,
				enabledProtocols,
				null,
				(hostname, session) -> {
					for (String allowedHost : allowedHosts) {
						if (allowedHost != null && hostname.equalsIgnoreCase(allowedHost.trim())) {
							return true;
						}
					}
					log.error("허용되지 않은 호스트 접근 시도: {}", hostname);
					return false;
				});

		CloseableHttpClient httpClient = HttpClients.custom()
				.setSSLSocketFactory(socketFactory)
				.build();

		HttpComponentsClientHttpRequestFactory factory = new HttpComponentsClientHttpRequestFactory(httpClient);
		factory.setConnectTimeout(connectTimeout);
		factory.setReadTimeout(readTimeout);

		return new RestTemplate(factory);
	}

	private static <T> List<List<T>> chunk(List<T> list, int size) {
		int n = list.size();
		java.util.ArrayList<List<T>> out = new java.util.ArrayList<>((n + size - 1) / size);
		for (int i = 0; i < n; i += size) {
			out.add(list.subList(i, Math.min(n, i + size)));
		}
		return out;
	}

	// roadType 기준으로 도로 이름/도로 종류 리스트 조회
	@Override
	public Map<String, Object> getRoadNames(
			Integer roadType,
			String searchName,
			Integer page,
			Integer size) {
		// 기본값 처리
		if (page == null || page < 1) {
			page = 1;
		}
		if (size == null || size < 1) {
			size = 10;
		}

		int offset = (page - 1) * size;

		// 전체 쿼리 결과 수 / 전체 페이지 수 조회
		int totalCount = sgpThmMapper.countRoadNames(roadType, searchName);
		int totalPages = (int) Math.ceil((double) totalCount / size);

		// 실제 데이터 조회
		List<Map<String, Object>> rows = sgpThmMapper.selectRoadNames(roadType, searchName, offset, size);

		// 응답 포맷 구성
		Map<String, Object> result = new HashMap<>();
		result.put("page", page);
		result.put("size", size);
		result.put("searchName", searchName);
		result.put("roadType", roadType);
		result.put("totalCount", totalCount);
		result.put("totalPages", totalPages);
		result.put("data", rows);

		return result;
	}

	// 단일 도로이름으로 해당 도로 내 링크 목록 조회
	@Override
	public List<Map<String, Object>> getLinksByRoadName(String roadName) {
		return sgpThmMapper.selectLinksByRoadName(roadName);
	}

	@Override
	public SgpThmSubwayDto getSubwayBySubwayName(String subwayName) {

		if (subwayName == null || subwayName.trim().isEmpty()) {
			return null;
		}

		final String name = subwayName.trim();

		// 1) 노선 단건 조회
		SgpThmSubwayDto subway = sgpThmMapper.selectSubwayByName(name);
		if (subway == null) {
			return null;
		}

		// 2) 역사 목록 조회
		List<SgpThmStationDto> stations = sgpThmMapper.selectStationsBySubwayName(subway.getSubwayNm());

		subway.setStations(
				(stations == null) ? new SgpThmStationDto[0]
						: stations.toArray(new SgpThmStationDto[0]));

		// 3) 역사 형태(공간) 조회 (rte_nm 기준)
		List<SgpThmStationShpDto> stationShps = sgpThmMapper.selectStationShpsBySubwayName(name);

		subway.setStationShps(
				(stationShps == null) ? new SgpThmStationShpDto[0]
						: stationShps.toArray(new SgpThmStationShpDto[0]));

		// 4) exits는 아직 없으니 빈 배열 세팅 (null 방지)
		subway.setExits(new SgpThmSubwayDto.SgpThmSubwayExitDto[0]);

		return subway;
	}

	@Override
	public String getSimulationJsonBySimId(String simId) {
		if (!StringUtils.hasText(simId)) {
			return "{}";
		}
		String json = sgpThmMapper.selectSimulationJsonBySimId(simId.trim());
		return (json == null || json.isBlank()) ? "{}" : json;
	}

	@Override
	public SgpThmTilesetClippingDto getTilesetClippingById(String clipId) {
		if (!StringUtils.hasText(clipId)) {
			return null;
		}
		return sgpThmMapper.selectTilesetClippingById(clipId.trim());
	}

	@Override
	public String getElectionGeomByPoiId(String poiId) {
		if (poiId == null || poiId.trim().isEmpty()) {
			throw new IllegalArgumentException("poi_id는 필수입니다.");
		}
		return sgpThmMapper.selectElectionGeomByPoiId(poiId);
	}

	@Override
	public List<SgpThmBiotopeGeomDto> getAllBiotopeGeoms() {
		return sgpThmMapper.selectAllBiotopeGeoms();
	}

	@Override
	@Transactional(readOnly = true)
	public void streamAllBiotopeGeoms(java.io.OutputStream outputStream,
			com.fasterxml.jackson.databind.ObjectMapper mapper) {

		// A/B 테스트 스위치: true = Optimized (직접 좌표), false = Legacy (ST_AsGeoJSON)
		final boolean USE_OPTIMIZED_STREAM = true; // TODO: 테스트 후 true로 변경

		long startTime = System.currentTimeMillis();
		log.info("NDJSON stream starting... (mode: {})", USE_OPTIMIZED_STREAM ? "OPTIMIZED" : "LEGACY");

		if (USE_OPTIMIZED_STREAM) {
			// ===== OPTIMIZED: bio_code가 DB에서 숫자로 변환되어 옴 =====
			// bio_val이 이미 "0"~"5" 숫자 문자열이므로 파싱 간단
			java.util.function.Function<incheon.sgp.thm.dto.SgpThmBiotopeGeomDto, Object> transformer = dto -> {
				try {
					// 1. bio_code 변환 (이미 숫자 문자열)
					int bioCode = Integer.parseInt(dto.getBioVal().trim());

					// 2. GeoJSON 파싱 (Legacy와 동일)
					com.fasterxml.jackson.databind.JsonNode root = mapper.readTree(dto.getGeom());
					com.fasterxml.jackson.databind.JsonNode coords = root.get("coordinates");
					String type = root.get("type").asText();

					java.util.List<Integer> ringCounts = new java.util.ArrayList<>();
					java.util.List<Double> flatCoords = new java.util.ArrayList<>();

					// 3. 좌표 Flattening & Ring 카운팅
					if ("MultiPolygon".equals(type)) {
						for (com.fasterxml.jackson.databind.JsonNode polygon : coords) {
							for (com.fasterxml.jackson.databind.JsonNode ring : polygon) {
								int count = 0;
								for (com.fasterxml.jackson.databind.JsonNode coord : ring) {
									flatCoords.add(coord.get(0).asDouble());
									flatCoords.add(coord.get(1).asDouble());
									count++;
								}
								ringCounts.add(count);
							}
						}
					} else if ("Polygon".equals(type)) {
						for (com.fasterxml.jackson.databind.JsonNode ring : coords) {
							int count = 0;
							for (com.fasterxml.jackson.databind.JsonNode coord : ring) {
								flatCoords.add(coord.get(0).asDouble());
								flatCoords.add(coord.get(1).asDouble());
								count++;
							}
							ringCounts.add(count);
						}
					}

					return new Object[] { dto.getPoiId(), bioCode, ringCounts, flatCoords };

				} catch (Exception e) {
					log.error("Failed to compress biotope geometry for id={}", dto.getPoiId(), e);
					return null;
				}
			};

			incheon.sgp.thm.handler.SgpThmNdjsonHandler<incheon.sgp.thm.dto.SgpThmBiotopeGeomDto> handler = new incheon.sgp.thm.handler.SgpThmNdjsonHandler<>(
					outputStream, mapper, 500, transformer);

			sgpThmMapper.streamAllBiotopeGeomsOptimized(handler);

			long elapsed = System.currentTimeMillis() - startTime;
			log.info("NDJSON stream completed (OPTIMIZED): {} rows in {} ms", handler.getCount(), elapsed);

		} else {
			// ===== LEGACY: ST_AsGeoJSON + Java 파싱 =====
			java.util.function.Function<incheon.sgp.thm.dto.SgpThmBiotopeGeomDto, Object> transformer = dto -> {
				try {
					// 1. bio_code 변환 (로마자 -> 숫자)
					int bioCode = 0;
					String val = dto.getBioVal();
					if (val != null) {
						val = val.trim();
						if (val.equals("1") || val.contains("Ⅰ") || val.contains("I"))
							bioCode = 1;
						else if (val.equals("2") || val.contains("Ⅱ") || val.contains("II"))
							bioCode = 2;
						else if (val.equals("3") || val.contains("Ⅲ") || val.contains("III"))
							bioCode = 3;
						else if (val.equals("4") || val.contains("Ⅳ") || val.contains("IV"))
							bioCode = 4;
						else if (val.equals("5") || val.contains("Ⅴ") || val.contains("V"))
							bioCode = 5;
					}

					// 2. GeoJSON 파싱
					com.fasterxml.jackson.databind.JsonNode root = mapper.readTree(dto.getGeom());
					com.fasterxml.jackson.databind.JsonNode coords = root.get("coordinates");
					String type = root.get("type").asText();

					java.util.List<Integer> ringCounts = new java.util.ArrayList<>();
					java.util.List<Double> flatCoords = new java.util.ArrayList<>();

					// 3. 좌표 Flattening & Ring 카운팅
					if ("MultiPolygon".equals(type)) {
						for (com.fasterxml.jackson.databind.JsonNode polygon : coords) {
							for (com.fasterxml.jackson.databind.JsonNode ring : polygon) {
								int count = 0;
								for (com.fasterxml.jackson.databind.JsonNode coord : ring) {
									flatCoords.add(coord.get(0).asDouble());
									flatCoords.add(coord.get(1).asDouble());
									count++;
								}
								ringCounts.add(count);
							}
						}
					} else if ("Polygon".equals(type)) {
						for (com.fasterxml.jackson.databind.JsonNode ring : coords) {
							int count = 0;
							for (com.fasterxml.jackson.databind.JsonNode coord : ring) {
								flatCoords.add(coord.get(0).asDouble());
								flatCoords.add(coord.get(1).asDouble());
								count++;
							}
							ringCounts.add(count);
						}
					}

					return new Object[] { dto.getPoiId(), bioCode, ringCounts, flatCoords };

				} catch (Exception e) {
					log.error("Failed to compress biotope geometry for id={}", dto.getPoiId(), e);
					return null;
				}
			};

			incheon.sgp.thm.handler.SgpThmNdjsonHandler<incheon.sgp.thm.dto.SgpThmBiotopeGeomDto> handler = new incheon.sgp.thm.handler.SgpThmNdjsonHandler<>(
					outputStream, mapper, 500, transformer);

			sgpThmMapper.streamAllBiotopeGeomsLegacy(handler);

			long elapsed = System.currentTimeMillis() - startTime;
			log.info("NDJSON stream completed (LEGACY): {} rows in {} ms", handler.getCount(), elapsed);
		}
	}

	@Override
	@Transactional
	public Map<String, Object> upsertWindMeta(String date, Integer year, Integer month, Integer day, String time, String filename) {
		String normalizedFilename = StringUtils.hasText(filename) ? filename.trim() : null;
		LocalDateTime now = LocalDateTime.now(KST).withSecond(0).withNano(0);

		LocalDate targetDate;
		LocalTime targetTime;

		boolean hasSplitDate = year != null || month != null || day != null;
		if (hasSplitDate) {
			if (year == null || month == null || day == null) {
				throw new IllegalArgumentException("year, month, day는 함께 전달해야 합니다.");
			}

			targetDate = LocalDate.of(year, month, day);
			String normalizedTime = normalizeWindTime(time);
			if (!StringUtils.hasText(normalizedTime)) {
				normalizedTime = now.format(WIND_TIME_FMT);
			}
			targetTime = parseWindTime(normalizedTime);

		} else if (StringUtils.hasText(date)) {
			WindDateInput parsed = parseWindDate(date);
			targetDate = parsed.date();
			if (parsed.hasTime()) {
				targetTime = parsed.time();
			} else {
				String normalizedTime = normalizeWindTime(time);
				targetTime = StringUtils.hasText(normalizedTime) ? parseWindTime(normalizedTime) : now.toLocalTime();
			}

		} else {
			targetDate = now.toLocalDate();
			String normalizedTime = normalizeWindTime(time);
			targetTime = StringUtils.hasText(normalizedTime) ? parseWindTime(normalizedTime) : now.toLocalTime();
		}

		String normalizedTime = targetTime.format(WIND_TIME_FMT);
		if (!StringUtils.hasText(normalizedFilename)) {
			normalizedFilename = buildWindFilename(targetDate, normalizedTime);
		}

		Long id = sgpThmMapper.upsertWindMeta(
				targetDate.getYear(),
				targetDate.getMonthValue(),
				targetDate.getDayOfMonth(),
				normalizedTime,
				normalizedFilename);

		Map<String, Object> saved = sgpThmMapper.selectWindById(id);
		if (saved != null) {
			return saved;
		}

		Map<String, Object> fallback = new HashMap<>();
		fallback.put("id", id);
		fallback.put("year", targetDate.getYear());
		fallback.put("month", targetDate.getMonthValue());
		fallback.put("day", targetDate.getDayOfMonth());
		fallback.put("time", normalizedTime);
		fallback.put("filename", normalizedFilename);
		return fallback;
	}

	@Override
	@Transactional(readOnly = true)
	public List<Map<String, Object>> searchWindMeta(String date, String date2, String filename) {
		String normalizedFilename = StringUtils.hasText(filename) ? filename.trim() : null;
		boolean hasDate = StringUtils.hasText(date);

		if (!hasDate && StringUtils.hasText(date2)) {
			throw new IllegalArgumentException("date2는 date와 함께 전달해야 합니다.");
		}

		if (!hasDate && !StringUtils.hasText(normalizedFilename)) {
			throw new IllegalArgumentException("date 또는 filename 중 하나는 필수입니다.");
		}

		if (!hasDate) {
			return toWindUrlRows(sgpThmMapper.selectWindByFilename(normalizedFilename));
		}

		WindDateInput startInput = parseWindDate(date);

		if (!StringUtils.hasText(date2)) {
			if (!startInput.hasTime()) {
				return toWindUrlRows(sgpThmMapper.selectWindByDay(
						startInput.date().getYear(),
						startInput.date().getMonthValue(),
						startInput.date().getDayOfMonth(),
						normalizedFilename));
			}

			String dayKey = toWindDayKey(startInput.date());
			String hourStartKey = dayKey + String.format("%02d00", startInput.time().getHour());
			String hourEndKey = dayKey + String.format("%02d59", startInput.time().getHour());

			List<Map<String, Object>> hourRows = sgpThmMapper.selectWindByRange(hourStartKey, hourEndKey, normalizedFilename);
			if (hourRows == null || hourRows.isEmpty()) {
				return toWindUrlRows(sgpThmMapper.selectWindByDay(
						startInput.date().getYear(),
						startInput.date().getMonthValue(),
						startInput.date().getDayOfMonth(),
						normalizedFilename));
			}

			return toWindUrlRows(hourRows);
		}

		WindDateInput endInput = parseWindDate(date2);
		LocalDateTime startDateTime = startInput.hasTime()
				? LocalDateTime.of(startInput.date(), startInput.time())
				: startInput.date().atTime(0, 0);
		LocalDateTime endDateTime = endInput.hasTime()
				? LocalDateTime.of(endInput.date(), endInput.time())
				: endInput.date().atTime(23, 59);

		if (endDateTime.isBefore(startDateTime)) {
			throw new IllegalArgumentException("date2는 date보다 이후 시각이어야 합니다.");
		}

		return toWindUrlRows(sgpThmMapper.selectWindByRange(
				toWindDateTimeKey(startDateTime),
				toWindDateTimeKey(endDateTime),
				normalizedFilename));
	}

	private record WindDateInput(LocalDate date, LocalTime time, boolean hasTime) {
	}

	private WindDateInput parseWindDate(String raw) {
		if (!StringUtils.hasText(raw)) {
			throw new IllegalArgumentException("date 값이 비어 있습니다.");
		}

		String value = raw.trim();

		LocalDateTime asDateTime = tryParseWindDateTime(value);
		if (asDateTime != null) {
			return new WindDateInput(
					asDateTime.toLocalDate(),
					asDateTime.toLocalTime().withSecond(0).withNano(0),
					true);
		}

		LocalDate asDate = tryParseWindDate(value);
		if (asDate != null) {
			return new WindDateInput(asDate, null, false);
		}

		throw new IllegalArgumentException("date 형식이 올바르지 않습니다. (예: 2026-02-11, 20260211, 2026-02-11 09:00)");
	}

	private LocalDate tryParseWindDate(String value) {
		DateTimeFormatter[] formats = new DateTimeFormatter[] {
				WIND_DATE_FMT_DASH,
				WIND_DATE_FMT_SLASH,
				WIND_DATE_FMT_COMPACT
		};
		for (DateTimeFormatter format : formats) {
			try {
				return LocalDate.parse(value, format);
			} catch (DateTimeParseException ignored) {
				// next format
			}
		}
		return null;
	}

	private LocalDateTime tryParseWindDateTime(String value) {
		DateTimeFormatter[] formats = new DateTimeFormatter[] {
				WIND_DATETIME_FMT_DASH_SEC,
				WIND_DATETIME_FMT_DASH,
				WIND_DATETIME_FMT_T_SEC,
				WIND_DATETIME_FMT_T,
				WIND_DATETIME_FMT_COMPACT_SEC,
				WIND_DATETIME_FMT_COMPACT
		};
		for (DateTimeFormatter format : formats) {
			try {
				return LocalDateTime.parse(value, format).withSecond(0).withNano(0);
			} catch (DateTimeParseException ignored) {
				// next format
			}
		}
		return null;
	}

	private String normalizeWindTime(String rawTime) {
		if (!StringUtils.hasText(rawTime)) {
			return null;
		}

		String digits = rawTime.replaceAll("[^0-9]", "");
		if (!StringUtils.hasText(digits)) {
			return null;
		}

		if (digits.length() == 1) {
			digits = "0" + digits + "00";
		} else if (digits.length() == 2) {
			digits = digits + "00";
		} else if (digits.length() == 3) {
			digits = "0" + digits;
		} else if (digits.length() == 6) {
			digits = digits.substring(0, 4);
		} else if (digits.length() != 4) {
			throw new IllegalArgumentException("time 형식이 올바르지 않습니다. (예: 0900, 09:00)");
		}

		int hour = Integer.parseInt(digits.substring(0, 2));
		int minute = Integer.parseInt(digits.substring(2, 4));
		if (hour < 0 || hour > 23 || minute < 0 || minute > 59) {
			throw new IllegalArgumentException("time 값은 00:00~23:59 범위여야 합니다.");
		}

		return String.format("%02d%02d", hour, minute);
	}

	private LocalTime parseWindTime(String normalizedTime) {
		int hour = Integer.parseInt(normalizedTime.substring(0, 2));
		int minute = Integer.parseInt(normalizedTime.substring(2, 4));
		return LocalTime.of(hour, minute);
	}

	private String buildWindFilename(LocalDate date, String normalizedTime) {
		return String.format(WIND_FILENAME_FORMAT, date.format(WIND_DATE_FMT_COMPACT), normalizedTime);
	}

	private List<Map<String, Object>> toWindUrlRows(List<Map<String, Object>> rows) {
		if (rows == null || rows.isEmpty()) {
			return List.of();
		}

		List<Map<String, Object>> mapped = new ArrayList<>(rows.size());
		for (Map<String, Object> row : rows) {
			mapped.add(toWindUrlRow(row));
		}
		return mapped;
	}

	private Map<String, Object> toWindUrlRow(Map<String, Object> row) {
		if (row == null || row.isEmpty()) {
			return Map.of();
		}

		Integer year = toInteger(row.get("year"));
		Integer month = toInteger(row.get("month"));
		Integer day = toInteger(row.get("day"));
		String filename = row.get("filename") == null ? null : String.valueOf(row.get("filename"));

		Map<String, Object> out = new LinkedHashMap<>();
		out.put("id", row.get("id"));
		out.put("year", year);
		out.put("month", month);
		out.put("day", day);
		out.put("time", row.get("time"));
		out.put("url", buildWindUrl(year, month, day, filename));
		return out;
	}

	private Integer toInteger(Object value) {
		if (value == null) {
			return null;
		}
		if (value instanceof Number number) {
			return number.intValue();
		}
		try {
			return Integer.parseInt(String.valueOf(value));
		} catch (NumberFormatException e) {
			return null;
		}
	}

	private Double toDouble(Object value) {
		if (value == null) {
			return null;
		}
		if (value instanceof Number number) {
			return number.doubleValue();
		}
		try {
			return Double.parseDouble(String.valueOf(value));
		} catch (NumberFormatException e) {
			return null;
		}
	}

	private String toNullableString(Object value) {
		return value == null ? null : String.valueOf(value);
	}

	private String buildWindUrl(Integer year, Integer month, Integer day, String filename) {
		if (!StringUtils.hasText(filename) || year == null || month == null || day == null) {
			return null;
		}

		String baseUrl = normalizeBaseUrl(WIND_BASE_URL);
		return String.format("%s/%04d/%02d/%02d/%s", baseUrl, year, month, day, filename);
	}

	private String normalizeBaseUrl(String baseUrl) {
		if (!StringUtils.hasText(baseUrl)) {
			return "";
		}

		String normalized = baseUrl.trim();
		while (normalized.endsWith("/")) {
			normalized = normalized.substring(0, normalized.length() - 1);
		}
		return normalized;
	}

	private String toWindDayKey(LocalDate date) {
		return String.format("%04d%02d%02d", date.getYear(), date.getMonthValue(), date.getDayOfMonth());
	}

	private String toWindDateTimeKey(LocalDateTime dateTime) {
		return String.format("%04d%02d%02d%02d%02d",
				dateTime.getYear(),
				dateTime.getMonthValue(),
				dateTime.getDayOfMonth(),
				dateTime.getHour(),
				dateTime.getMinute());
	}

	@Override
	public List<Map<String, Object>> getWasteBagPriceList(String sggName) {
		List<Map<String, Object>> wasteBag = sgpThmMapper.getWasteBagPriceList(sggName);
		return wasteBag;
	}
}