package incheon.uis.uld.service.impl;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import incheon.uis.uld.service.GeoJsonSyncService;
import incheon.uis.uld.service.SyncMode;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.io.File;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.*;
import java.sql.Date;
import java.util.*;

@Service
@Slf4j
public class GeoJsonSyncServiceImpl implements GeoJsonSyncService {
    @Autowired private ObjectMapper objectMapper;

    @Autowired private DataSource dataSource;

    /* ---------------------------------- 통계 ---------------------------------- */
    private static final class Stats {
        int total, ins, upd, del, skip;
        @Override public String toString() {
            return String.format("total=%d, ins=%d, upd=%d, del=%d, skip=%d", total, ins, upd, del, skip);
        }
    }

    /* ----------------------- 테이블 참조(스키마/따옴표 안전) ---------------------- */
    private static final class TableRef {
        final String schema;
        final String name;
        TableRef(String schema, String name){ this.schema=schema; this.name=name; }
        String qualified() {
            return (schema != null ? quoteIdent(schema) + "." : "") + quoteIdent(name);
        }
        String regclassKey(){
            return (schema != null ? schema + "." : "") + name;
        }
    }
    private static String quoteIdent(String id){
        if (id == null) return null;
        if (id.startsWith("\"") && id.endsWith("\"")) return id;
        return "\"" + id.replace("\"","\"\"") + "\"";
    }
    private static TableRef parseTableRef(String s){
        if (s == null) return new TableRef(null, null);
        String in = s.trim();
        if (in.startsWith("\"") && in.endsWith("\"") && in.indexOf('.') > 0) {
            in = in.substring(1, in.length()-1);
        }
        String schema = null, name = in;
        int dot = in.indexOf('.');
        if (dot > 0) {
            schema = in.substring(0, dot).trim();
            name   = in.substring(dot + 1).trim();
        }
        if (schema != null && schema.length() >= 2 && schema.startsWith("\"") && schema.endsWith("\"")) {
            schema = schema.substring(1, schema.length()-1);
        }
        if (name.length() >= 2 && name.startsWith("\"") && name.endsWith("\"")) {
            name = name.substring(1, name.length()-1);
        }
        return new TableRef(schema, name);
    }

    /* -------------------------------- 외부 API --------------------------------- */
    @Override
    public Map<String, String> syncFiles(List<String> geojsonPaths,
                                         Integer defaultSrid,
                                         SyncMode mode,
                                         String pkColumn,
                                         String overrideTable) throws Exception {
        Map<String, String> report = new LinkedHashMap<>();
        if (geojsonPaths == null || geojsonPaths.isEmpty()) return report;
        for (String p : geojsonPaths) {
            report.put(p, syncSingle(p, defaultSrid, mode, pkColumn, overrideTable).toString());
        }
        return report;
    }

    /* ------------------------------ 핵심 처리부 -------------------------------- */
    private Stats syncSingle(String geojsonPath,
                             Integer defaultSrid,
                             SyncMode mode,
                             String pkCol,
                             String overrideTable) throws Exception {

        File f = new File(geojsonPath);
        if (!f.exists()) throw new IllegalArgumentException("GeoJSON 없음: " + geojsonPath);

        // 인코딩 안전 로딩 → JSON
        String jsonText = readTextSmart(f.toPath()).trim();
        JsonNode root = objectMapper.readTree(jsonText);

        // 테이블명 (override > json 메타 > 파일명) + 기본 스키마(icuis_test) + 모두 소문자
        String tableName = (overrideTable != null && !overrideTable.isBlank())
                ? overrideTable
                : readTableName(root);
        if (tableName == null || tableName.isBlank()) {
            tableName = fallbackTableFromFilename(geojsonPath);
        }
        String combined = (tableName.contains("."))
                ? tableName.toLowerCase(Locale.ROOT)
                : ("icuis_test." + tableName.toLowerCase(Locale.ROOT));
        TableRef tref = parseTableRef(combined);

        // PK 컬럼명 소문자 통일
        String pkColLower = (pkCol != null && !pkCol.isBlank())
                ? pkCol.toLowerCase(Locale.ROOT)
                : pkCol;

        // 입력 SRID
        Integer srid = readSrid(root);
        if (srid == null) srid = defaultSrid;

        // features
        JsonNode features = root.get("features");
        if (features == null || !features.isArray()) {
            throw new IllegalStateException("features 없음: " + geojsonPath);
        }

        Stats stats = new Stats();

        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);
            try {
                // 지오메트리 컬럼명 및 컬럼 SRID
                String geomCol = findGeometryColumn(conn, tref);
                int columnSrid = findColumnSrid(conn, tref, geomCol);
                int inSridEff = resolveInSrid(conn, srid, columnSrid);

                // 존재 컬럼/타입 Map
                Set<String> existingCols   = loadExistingColumnsLower(conn, tref);
                Map<String,String> typeMap = loadColumnTypesLower(conn, tref);

                for (JsonNode feat : features) {
                    stats.total++;

                    JsonNode props = feat.get("properties");
                    JsonNode geomNode = feat.get("geometry");
                    if (props == null || props.isMissingNode()) { stats.skip++; continue; }

                    // PK 값
                    Object pkVal = pickPrimaryKeyIgnoreCase(props, pkColLower);
                    if (pkVal == null && mode != SyncMode.INSERT_ONLY) { stats.skip++; continue; }

                    // props → 소문자 키, OBJECTID/GEOM 제외, DB 존재 컬럼만 유지
                    Map<String, Object> cols = propsToMapLower(props, pkColLower);
                    cols.keySet().retainAll(existingCols);

                    // 값 타입을 DB 컬럼 타입에 맞게 강제 변환
                    for (Map.Entry<String,Object> e : new ArrayList<>(cols.entrySet())) {
                        cols.put(e.getKey(), coerceForColumn(e.getKey(), e.getValue(), typeMap));
                    }
                    // PK도 강제 변환
                    if (pkColLower != null && pkVal != null) {
                        pkVal = coerceForColumn(pkColLower, pkVal, typeMap);
                    }

                    // 모드별 실행 (지오메트리 SRID 자동 변환 포함)
                    switch (mode) {
                        case INSERT_ONLY -> stats.ins += insertRow(
                                conn, tref, pkColLower, pkVal, cols, geomNode, inSridEff, geomCol, columnSrid, mode);

                        case UPDATE_ONLY -> {
                            if (existsByPk(conn, tref, pkColLower, pkVal))
                                stats.upd += updateRow(
                                        conn, tref, pkColLower, pkVal, cols, geomNode, inSridEff, geomCol, columnSrid);
                            else stats.skip++;
                        }

                        case DELETE_ONLY -> stats.del += deleteRow(conn, tref, pkColLower, pkVal);

                        case UPSERT -> {
                            if (existsByPk(conn, tref, pkColLower, pkVal))
                                stats.upd += updateRow(
                                        conn, tref, pkColLower, pkVal, cols, geomNode, inSridEff, geomCol, columnSrid);
                            else
                                stats.ins += insertRow(
                                        conn, tref, pkColLower, pkVal, cols, geomNode, inSridEff, geomCol, columnSrid, mode);
                        }
                    }
                }

                conn.commit();
            } catch (Exception e) {
                conn.rollback();
                throw e;
            }
        }

        return stats;
    }

    /* ----------------------------- 파일 로딩 유틸 ----------------------------- */
    private static String readTextSmart(Path p) throws java.io.IOException {
        byte[] bytes = Files.readAllBytes(p);

        // UTF-8 BOM (EF BB BF) 제거 후 UTF-8로
        if (bytes.length >= 3 &&
                (bytes[0] & 0xFF) == 0xEF &&
                (bytes[1] & 0xFF) == 0xBB &&
                (bytes[2] & 0xFF) == 0xBF) {
            return new String(bytes, 3, bytes.length - 3, StandardCharsets.UTF_8);
        }

        // fallback (원 코드 의도 유지)
        for (String cs : new String[] { "MS949", "EUC-KR", "ISO-8859-1" }) {
            try {
                String s = new String(bytes, Charset.forName(cs));
                log.debug("[ENC] fallback={} : {}", cs, p.getFileName());
                return s;
            } catch (java.nio.charset.IllegalCharsetNameException | java.nio.charset.UnsupportedCharsetException e) {
                log.debug("[ENC] unsupported charset={} : {}", cs, p.getFileName(), e);
            }
        }

        // 마지막: UTF-8 (예외 없음)
        return new String(bytes, StandardCharsets.UTF_8);
    }


    /* ----------------------------- GeoJSON 메타 ------------------------------ */
    private static String readTableName(JsonNode root) {
        JsonNode n = root.get("name");
        if (n != null && n.isTextual() && !n.asText().isBlank()) return n.asText();
        JsonNode l = root.get("layer");
        if (l != null && l.isTextual() && !l.asText().isBlank()) return l.asText();
        JsonNode t = root.get("table");
        if (t != null && t.isTextual() && !t.asText().isBlank()) return t.asText();
        return null;
    }
    private static String fallbackTableFromFilename(String path) {
        String fn = Paths.get(path).getFileName().toString();
        int dot = fn.lastIndexOf('.');
        if (dot > 0) fn = fn.substring(0, dot);
        return fn;
    }
    private static Integer readSrid(JsonNode root) {
        if (root == null) return null;

        JsonNode crsName = root.path("crs").path("properties").path("name");
        if (!crsName.isTextual()) return null;

        String digits = crsName.asText().replaceAll("[^0-9]", "");
        if (digits.isBlank()) return null;

        try {
            return Integer.parseInt(digits);
        } catch (NumberFormatException e) {
            return null;
        }
    }

    /* -------------------------- PK/프로퍼티 추출 --------------------------- */
    private static Object pickPrimaryKeyIgnoreCase(JsonNode props, String pkColLower) {
        if (pkColLower != null && !pkColLower.isBlank()) {
            JsonNode n = getPropIgnoreCase(props, pkColLower);
            if (n != null && !n.isNull()) return n.isNumber()? n.numberValue() : n.asText();
        }
        for (String c : new String[]{"gid","objectid","id"}) {
            JsonNode n = getPropIgnoreCase(props, c);
            if (n != null && !n.isNull()) return n.isNumber()? n.numberValue() : n.asText();
        }
        return null;
    }
    private static JsonNode getPropIgnoreCase(JsonNode props, String keyLower) {
        Iterator<String> it = props.fieldNames();
        while (it.hasNext()) {
            String k = it.next();
            if (k.equalsIgnoreCase(keyLower)) return props.get(k);
        }
        return null;
    }
    /** props → 소문자 키(geom/objectid 제외), PK 제외 */
    private static Map<String,Object> propsToMapLower(JsonNode props, String pkColLower) {
        Map<String,Object> map = new LinkedHashMap<>();
        props.fieldNames().forEachRemaining(name -> {
            String lower = name.toLowerCase(Locale.ROOT);
            if ("geom".equals(lower) || "objectid".equals(lower)) return;
            if (pkColLower != null && lower.equals(pkColLower)) return;

            JsonNode v = props.get(name);
            if (v == null || v.isNull()) return;

            Object val = v.isNumber() ? v.numberValue()
                    : (v.isBoolean() ? v.booleanValue() : v.asText());
            map.put(lower, val);
        });
        return map;
    }

    /* --------------------------- DB 메타/유틸 ---------------------------- */
    private static String findGeometryColumn(Connection conn, TableRef t) throws SQLException {
        String schema = t.schema != null ? t.schema.replace("\"","") : null;
        String name   = t.name.replace("\"","");

        String sql = """
            SELECT column_name
            FROM information_schema.columns
            WHERE table_name = ?
              AND (? IS NULL OR table_schema = ?)
              AND udt_name IN ('geometry','geography')
            ORDER BY ordinal_position
            LIMIT 1
            """;
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            if (schema == null) {
                ps.setNull(2, Types.VARCHAR);
                ps.setNull(3, Types.VARCHAR);
            } else {
                ps.setString(2, schema);
                ps.setString(3, schema);
            }
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) return rs.getString(1);
            }
        }
        String[] commons = { "geom", "the_geom", "wkb_geometry", "shape" };

        for (String c : commons) {
            String probe = "SELECT " + quoteIdent(c) + " FROM " + t.qualified() + " LIMIT 0";
            try (Statement st = conn.createStatement()) {
                st.execute(probe);
                return c; // 첫 성공 컬럼
            } catch (SQLException e) {
                log.debug(
                        "geometry column probe 실패: table={}, column={}",
                        t.qualified(), c
                );
            }
        }
        return null;
    }

    /** 컬럼 SRID 조회 (없거나 모르면 0) */
    private static int findColumnSrid(Connection conn, TableRef t, String geomCol) throws SQLException {
        if (geomCol == null) return 0;
        String schema = (t.schema != null) ? t.schema.replace("\"","") : "public";
        String name   = t.name.replace("\"","");
        String sql = "SELECT Find_SRID(?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, schema);
            ps.setString(2, name);
            ps.setString(3, geomCol);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) return rs.getInt(1);
            }
        }
        return 0;
    }

    /** spatial_ref_sys에 SRID가 존재하는지 */
    private static boolean sridExists(Connection conn, int srid) {
        if (srid <= 0) return false;
        String sql = "SELECT 1 FROM spatial_ref_sys WHERE srid = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, srid);
            try (ResultSet rs = ps.executeQuery()) { return rs.next(); }
        } catch (SQLException e) {
            return false;
        }
    }

    /** 입력 SRID가 없거나 미등록이면 컬럼 SRID로 대체(둘 다 없으면 그대로 0) */
    private static int resolveInSrid(Connection conn, Integer inputSrid, int columnSrid) {
        int in = (inputSrid != null ? inputSrid : 0);
        if (in > 0 && sridExists(conn, in)) return in;      // 등록되어 있으면 그대로 사용
        if (columnSrid > 0) return columnSrid;              // 미등록이면 컬럼 SRID로 강제
        return in;                                          // 둘 다 없으면 입력값(또는 0)
    }


    private static boolean existsByPk(Connection conn, TableRef t, String pkCol, Object pkVal) throws SQLException {
        String pk = (pkCol != null) ? pkCol.toLowerCase(Locale.ROOT) : pkCol;
        String sql = "SELECT 1 FROM " + t.qualified() + " WHERE " + quoteIdent(pk) + " = ? LIMIT 1";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setObject(1, pkVal);
            try (ResultSet rs = ps.executeQuery()) { return rs.next(); }
        }
    }
    private static int deleteRow(Connection conn, TableRef t, String pkCol, Object pkVal) throws SQLException {
        String pk = (pkCol != null) ? pkCol.toLowerCase(Locale.ROOT) : pkCol;
        String sql = "DELETE FROM " + t.qualified() + " WHERE " + quoteIdent(pk) + " = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setObject(1, pkVal);
            return ps.executeUpdate();
        }
    }

    /** 테이블 실제 컬럼명 목록(소문자) */
    private static Set<String> loadExistingColumnsLower(Connection conn, TableRef t) throws SQLException {
        String schema = (t.schema != null) ? t.schema.replace("\"","") : "public";
        String name   = t.name.replace("\"","");
        String sql = """
          SELECT column_name
          FROM information_schema.columns
          WHERE table_schema = ?
            AND table_name   = ?
        """;
        Set<String> cols = new HashSet<>();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, schema);
            ps.setString(2, name);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) cols.add(rs.getString(1).toLowerCase(Locale.ROOT));
            }
        }
        return cols;
    }

    /** 컬럼 → data_type 맵(소문자 키) */
    private static Map<String,String> loadColumnTypesLower(Connection conn, TableRef t) throws SQLException {
        String schema = (t.schema != null) ? t.schema.replace("\"","") : "public";
        String name   = t.name.replace("\"","");
        String sql = """
          SELECT column_name, data_type
          FROM information_schema.columns
          WHERE table_schema = ?
            AND table_name   = ?
        """;
        Map<String,String> map = new HashMap<>();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, schema);
            ps.setString(2, name);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    map.put(rs.getString(1).toLowerCase(Locale.ROOT),
                            rs.getString(2).toLowerCase(Locale.ROOT));
                }
            }
        }
        return map;
    }

    /** 값 → DB data_type에 맞게 강제 변환 (timestamp/date/int/bigint/numeric/boolean 등) */
    private static Object coerceForColumn(String colLower, Object val, Map<String,String> typeMap) {
        if (val == null) return null;
        String t = typeMap.get(colLower);
        if (t == null) return val;

        if (val instanceof String s) {
            s = s.trim();
            if (s.isEmpty()) return null;

            try {
                if (t.contains("timestamp")) {
                    java.time.LocalDateTime ldt = parseToLocalDateTime(s);
                    if (ldt != null) return Timestamp.valueOf(ldt);
                    if (s.matches("^\\d{10}$")) return new Timestamp(Long.parseLong(s) * 1000L);
                    if (s.matches("^\\d{13}$")) return new Timestamp(Long.parseLong(s));
                    return val;
                }
                if (t.equals("date")) {
                    java.time.LocalDate ld = parseToLocalDate(s);
                    if (ld != null) return Date.valueOf(ld);
                    return val;
                }
                if (t.equals("integer") || t.equals("smallint")) {
                    if (s.matches("^-?\\d+$")) return Integer.valueOf(s);
                    return val;
                }
                if (t.equals("bigint")) {
                    if (s.matches("^-?\\d+$")) return Long.valueOf(s);
                    return val;
                }
                if (t.startsWith("numeric") || t.equals("real") || t.equals("double precision") || t.equals("decimal")) {
                    if (s.matches("^-?\\d+(\\.\\d+)?$")) return new java.math.BigDecimal(s);
                    return val;
                }
                if (t.equals("boolean")) {
                    String ls = s.toLowerCase(Locale.ROOT);
                    if (ls.equals("true") || ls.equals("t") || ls.equals("1") || ls.equals("y") || ls.equals("yes")) return true;
                    if (ls.equals("false") || ls.equals("f") || ls.equals("0") || ls.equals("n") || ls.equals("no")) return false;
                    return val;
                }
            } catch (Exception ignore) {
                return val;
            }
        }
        return val;
    }
    private static java.time.LocalDateTime parseToLocalDateTime(String s) {
        String[] patterns = {
                "yyyy-MM-dd HH:mm:ss","yyyy/MM/dd HH:mm:ss","yyyy.MM.dd HH:mm:ss",
                "yyyy-MM-dd'T'HH:mm:ss","yyyyMMdd HHmmss"
        };
        if (s.matches("^\\d{8}$")) {
            s = s.substring(0,4)+"-"+s.substring(4,6)+"-"+s.substring(6,8)+" 00:00:00";
            patterns = new String[] {"yyyy-MM-dd HH:mm:ss"};
        } else if (s.matches("^\\d{4}[-/.]\\d{2}[-/.]\\d{2}$")) {
            s = s.replace('/', '-').replace('.', '-') + " 00:00:00";
            patterns = new String[] {"yyyy-MM-dd HH:mm:ss"};
        }
        for (String p : patterns) {
            try {
                return java.time.LocalDateTime.parse(
                        s,
                        java.time.format.DateTimeFormatter.ofPattern(p)
                );
            } catch (java.time.format.DateTimeParseException e) {
                log.debug("LocalDateTime parse 실패: value='{}', pattern='{}'", s, p);
            }
        }

        try {
            return java.time.LocalDateTime.parse(
                    s,
                    java.time.format.DateTimeFormatter.ISO_LOCAL_DATE_TIME
            );
        } catch (java.time.format.DateTimeParseException e) {
            log.debug("LocalDateTime ISO_LOCAL_DATE_TIME parse 실패: value='{}'", s);
        }

        try {
            java.time.Instant inst = java.time.Instant.parse(s);
            return java.time.LocalDateTime.ofInstant(
                    inst,
                    java.time.ZoneId.systemDefault()
            );
        } catch (java.time.format.DateTimeParseException e) {
            log.debug("Instant parse 실패: value='{}'", s);
        }

        log.debug("LocalDateTime 파싱 불가: value='{}'", s);
        return null;

    }
    private static java.time.LocalDate parseToLocalDate(String s) {
        if (s == null || s.isBlank()) {
            log.debug("LocalDate parse 실패: 값이 비어있음");
            return null;
        }

        String[] patterns = { "yyyy-MM-dd", "yyyy/MM/dd", "yyyy.MM.dd", "yyyyMMdd" };

        for (String p : patterns) {
            try {
                if (p.equals("yyyyMMdd") && s.matches("^\\d{8}$")) {
                    return java.time.LocalDate.of(
                            Integer.parseInt(s.substring(0, 4)),
                            Integer.parseInt(s.substring(4, 6)),
                            Integer.parseInt(s.substring(6, 8))
                    );
                } else {
                    return java.time.LocalDate.parse(
                            s,
                            java.time.format.DateTimeFormatter.ofPattern(p)
                    );
                }
            } catch (java.time.format.DateTimeParseException | NumberFormatException e) {
                log.debug("LocalDate parse 실패: value='{}', pattern='{}'", s, p);
            }
        }
        return null;
    }


    /* ----------------------- SRID 변환용 유틸 (핵심 추가) ----------------------- */
    /** 지오메트리 파라미터 바인딩: SRID 다르면 Transform, 없으면 컬럼 SRID로 세팅 */
    private static int bindGeomParam(PreparedStatement ps, int startIdx,
                                     JsonNode geomNode, Integer inputSrid, int columnSrid) throws SQLException {
        int inSrid = (inputSrid != null && inputSrid > 0) ? inputSrid : columnSrid;

        if (columnSrid > 0 && inSrid > 0 && inSrid != columnSrid) {
            // ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON(?), ?), ?)
            ps.setString(startIdx++, geomNode.toString());
            ps.setInt(startIdx++, inSrid);
            ps.setInt(startIdx++, columnSrid);
            return 3;
        } else {
            // ST_SetSRID(ST_GeomFromGeoJSON(?), ?)
            ps.setString(startIdx++, geomNode.toString());
            ps.setInt(startIdx++, (inSrid > 0 ? inSrid : 4326));
            return 2;
        }
    }
    /** INSERT/UPDATE에서 사용할 지오메트리 SQL 조각 */
    private static String geomSqlExpr(Integer inputSrid, int columnSrid) {
        int inSrid = (inputSrid != null && inputSrid > 0) ? inputSrid : columnSrid;
        if (columnSrid > 0 && inSrid > 0 && inSrid != columnSrid) {
            return "ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON(?), ?), ?)";
        } else {
            return "ST_SetSRID(ST_GeomFromGeoJSON(?), ?)";
        }
    }

    /** pk 컬럼에 PK 또는 UNIQUE 인덱스가 존재하는지 확인 */
    private static boolean hasUniqueOrPkOnColumn(Connection conn, TableRef t, String pkLower) throws SQLException {
        if (pkLower == null || pkLower.isBlank()) return false;

        String regclass = (t.schema != null ? t.schema.replace("\"","") + "." : "") + t.name.replace("\"","");
        String sql = """
                    SELECT 1
                    FROM pg_index i
                    JOIN pg_class c   ON c.oid = i.indrelid
                    JOIN pg_namespace n ON n.oid = c.relnamespace
                    JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(i.indkey)
                    WHERE (quote_ident(n.nspname) || '.' || quote_ident(c.relname)) = quote_ident(?)::text
                      AND (i.indisunique = true OR i.indisprimary = true)
                      AND a.attname = ?
                    LIMIT 1
                """;
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, regclass);
            ps.setString(2, pkLower);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next();
            }
        }
    }


    /* --------------------------- INSERT / UPDATE --------------------------- */
    private static int insertRow(Connection conn, TableRef t, String pkCol, Object pkVal,
                                 Map<String,Object> cols, JsonNode geomNode,
                                 Integer srid, String geomCol, int columnSrid,
                                 SyncMode mode) throws Exception {

        LinkedHashMap<String,Object> kv = new LinkedHashMap<>(cols); // lower keys
        String pk = (pkCol != null) ? pkCol.toLowerCase(Locale.ROOT) : pkCol;

        kv.remove(pk);
        kv.keySet().removeIf(c -> "geom".equals(c) || "objectid".equals(c));
        if (pkVal != null) kv.put(pk, pkVal);

        List<String> colNames = new ArrayList<>();
        List<Object> values   = new ArrayList<>();
        for (String c : kv.keySet()) { colNames.add(quoteIdent(c)); values.add(kv.get(c)); }

        boolean writeGeom = (geomCol != null && geomNode != null && !geomNode.isNull());

        // --- 경로 A: PK/UNIQUE 인덱스가 있는 경우 → ON CONFLICT DO NOTHING ---
        boolean canUseOnConflict = false;
        if (pk != null && !pk.isBlank() && (mode == SyncMode.INSERT_ONLY || mode == SyncMode.UPSERT)) {
            canUseOnConflict = hasUniqueOrPkOnColumn(conn, t, pk);
        }

        if (canUseOnConflict) {
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO ").append(t.qualified()).append(" (")
                    .append(String.join(", ", colNames));
            if (writeGeom) sql.append(", ").append(quoteIdent(geomCol.toLowerCase(Locale.ROOT)));
            sql.append(") VALUES (")
                    .append(String.join(", ", Collections.nCopies(values.size(), "?")));
            if (writeGeom) sql.append(", ").append(geomSqlExpr(srid, columnSrid));
            sql.append(") ON CONFLICT (").append(quoteIdent(pk)).append(") DO NOTHING");

            try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
                int idx = 1;
                for (Object v : values) ps.setObject(idx++, v);
                if (writeGeom) idx += bindGeomParam(ps, idx, geomNode, srid, columnSrid);
                return ps.executeUpdate(); // 충돌 시 0 반환
            }
        }

        // --- 경로 B: 유니크 인덱스가 없거나 pk가 비어있음 → WHERE NOT EXISTS 가드 ---
        // INSERT ... SELECT ... WHERE NOT EXISTS (SELECT 1 FROM t WHERE pk = ?)
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(t.qualified()).append(" (")
                .append(String.join(", ", colNames));
        if (writeGeom) sql.append(", ").append(quoteIdent(geomCol.toLowerCase(Locale.ROOT)));
        sql.append(") SELECT ")
                .append(String.join(", ", Collections.nCopies(values.size(), "?")));
        if (writeGeom) sql.append(", ").append(geomSqlExpr(srid, columnSrid));

        if (pk != null && !pk.isBlank() && pkVal != null) {
            sql.append(" WHERE NOT EXISTS (SELECT 1 FROM ").append(t.qualified())
                    .append(" WHERE ").append(quoteIdent(pk)).append(" = ?)");
        }

        try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            int idx = 1;
            // SELECT 값들
            for (Object v : values) ps.setObject(idx++, v);
            if (writeGeom) idx += bindGeomParam(ps, idx, geomNode, srid, columnSrid);
            // WHERE NOT EXISTS 의 pk 바인딩
            if (pk != null && !pk.isBlank() && pkVal != null) {
                ps.setObject(idx, pkVal);
            }
            return ps.executeUpdate();
        }
    }

    private static int updateRow(Connection conn, TableRef t, String pkCol, Object pkVal,
                                 Map<String,Object> cols, JsonNode geomNode,
                                 Integer srid, String geomCol, int columnSrid) throws Exception {

        LinkedHashMap<String,Object> kv = new LinkedHashMap<>(cols); // lower keys
        String pk = (pkCol != null) ? pkCol.toLowerCase(Locale.ROOT) : pkCol;

        kv.remove(pk);
        kv.keySet().removeIf(c -> "geom".equals(c) || "objectid".equals(c));

        boolean writeGeom = (geomCol != null && geomNode != null && !geomNode.isNull());
        List<String> sets = new ArrayList<>();
        for (String c : kv.keySet()) sets.add(quoteIdent(c) + " = ?");
        if (writeGeom) sets.add(quoteIdent(geomCol.toLowerCase(Locale.ROOT)) + " = " + geomSqlExpr(srid, columnSrid));
        if (sets.isEmpty()) return 0;

        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(t.qualified()).append(" SET ")
                .append(String.join(", ", sets))
                .append(" WHERE ").append(quoteIdent(pk)).append(" = ?");

        try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            int idx = 1;
            for (Object v : kv.values()) ps.setObject(idx++, v);
            if (writeGeom) idx += bindGeomParam(ps, idx, geomNode, srid, columnSrid);
            ps.setObject(idx, pkVal);
            return ps.executeUpdate();
        }
    }
}