package incheon.product.geoview2d.layer.mapper;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import org.apache.commons.lang3.ObjectUtils;
import org.springframework.stereotype.Component;

/**
 * 레이어 편집 동적 SQL 생성기.
 * 다양한 테이블 구조에 대응하는 동적 SQL을 생성한다.
 * 감사 컬럼(frst_reg_id, frst_reg_dt, last_mdfcn_id, last_mdfcn_dt) 자동 처리.
 */
@Component
public class LayerSqlProvider {

    private static final Pattern SAFE_IDENTIFIER = Pattern.compile("^[a-zA-Z_][a-zA-Z0-9_]*$");

    private static final Map<String, String> DATA_TYPE_MAPPING = Map.ofEntries(
            Map.entry("character varying", "varchar"),
            Map.entry("text", "varchar"),
            Map.entry("integer", "integer"),
            Map.entry("bigint", "bigint"),
            Map.entry("smallint", "smallint"),
            Map.entry("numeric", "numeric"),
            Map.entry("double precision", "double precision"),
            Map.entry("real", "real"),
            Map.entry("boolean", "boolean"),
            Map.entry("timestamp without time zone", "timestamp"),
            Map.entry("timestamp with time zone", "timestamp"),
            Map.entry("date", "date")
    );

    private static final String[] SEQUENCE_COLUMNS = {"idn", "ftr_idn"};
    private static final String[] AUDIT_INSERT_COLUMNS = {"frst_reg_id", "frst_reg_dt", "last_mdfcn_id", "last_mdfcn_dt"};
    private static final String[] AUDIT_UPDATE_COLUMNS = {"last_mdfcn_id", "last_mdfcn_dt"};

    /**
     * 청크 배치 INSERT SQL (RETURNING *).
     */
    public String batchInsertLayerFeatures(Map<String, Object> params) {
        String schemaName = validateIdentifier((String) params.get("schemaName"), "스키마명");
        String tableName = validateIdentifier((String) params.get("tableName"), "테이블명");

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) params.get("columns");
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> features = (List<Map<String, Object>>) params.get("features");

        if (features == null || features.isEmpty()) {
            return "SELECT 1 WHERE 1=0";
        }

        @SuppressWarnings("unchecked")
        Map<String, Object> firstProperties = (Map<String, Object>) features.get(0).get("properties");
        List<String> validColumns = new ArrayList<>();
        boolean[] auditFlags = detectAuditColumns(columns, AUDIT_INSERT_COLUMNS);
        boolean hasFrstRegId = auditFlags[0];
        boolean hasFrstRegDt = auditFlags[1];
        boolean hasLastMdfcnId = auditFlags[2];
        boolean hasLastMdfcnDt = auditFlags[3];

        if (firstProperties != null) {
            for (Map<String, Object> column : columns) {
                String columnName = (String) column.get("column_name");
                if (isAuditColumn(columnName)) continue;
                if (firstProperties.containsKey(columnName)) {
                    validColumns.add(columnName);
                }
            }
        }

        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(schemaName).append(".").append(tableName).append(" (geom");

        for (String col : validColumns) {
            if (isSequenceColumn(col)) continue;
            sql.append(", \"").append(col).append("\"");
        }
        appendAuditColumnNames(sql, hasFrstRegId, hasFrstRegDt, hasLastMdfcnId, hasLastMdfcnDt);
        sql.append(") VALUES ");

        for (int i = 0; i < features.size(); i++) {
            if (i > 0) sql.append(", ");
            sql.append("(ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{features[").append(i).append("].geometryString})), #{srid})");

            for (String col : validColumns) {
                if (isSequenceColumn(col)) continue;
                String dataType = getColumnDataType(columns, col);
                sql.append(", #{features[").append(i).append("].properties.").append(col);
                if (!"varchar".equals(dataType)) {
                    sql.append("}::").append(dataType);
                } else {
                    sql.append("}");
                }
            }
            appendAuditColumnValues(sql, hasFrstRegId, hasFrstRegDt, hasLastMdfcnId, hasLastMdfcnDt);
            sql.append(")");
        }

        sql.append(" RETURNING *");
        return sql.toString();
    }

    /**
     * 청크 배치 UPDATE SQL (CASE WHEN 방식).
     */
    public String batchUpdateLayerFeatures(Map<String, Object> params) {
        String schemaName = validateIdentifier((String) params.get("schemaName"), "스키마명");
        String tableName = validateIdentifier((String) params.get("tableName"), "테이블명");

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) params.get("columns");
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> features = (List<Map<String, Object>>) params.get("features");

        if (features == null || features.isEmpty()) {
            return "UPDATE " + schemaName + "." + tableName + " SET gid = gid WHERE 1=0";
        }

        @SuppressWarnings("unchecked")
        Map<String, Object> firstProperties = (Map<String, Object>) features.get(0).get("properties");
        List<String> validColumns = new ArrayList<>();
        boolean[] auditFlags = detectAuditColumns(columns, AUDIT_UPDATE_COLUMNS);
        boolean hasLastMdfcnId = auditFlags[0];
        boolean hasLastMdfcnDt = auditFlags[1];

        if (firstProperties != null) {
            for (Map<String, Object> column : columns) {
                String columnName = (String) column.get("column_name");
                if ("last_mdfcn_id".equalsIgnoreCase(columnName) || "last_mdfcn_dt".equalsIgnoreCase(columnName)) continue;
                if (firstProperties.containsKey(columnName)) {
                    validColumns.add(columnName);
                }
            }
        }

        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(schemaName).append(".").append(tableName).append(" SET ");

        // geometry CASE WHEN
        sql.append("geom = CASE gid ");
        for (int i = 0; i < features.size(); i++) {
            sql.append("WHEN #{features[").append(i).append("].gid} THEN ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{features[")
               .append(i).append("].geometryString})), #{srid}) ");
        }
        sql.append("ELSE geom END");

        // property columns CASE WHEN
        for (String col : validColumns) {
            String dataType = getColumnDataType(columns, col);
            sql.append(", \"").append(col).append("\" = CASE gid ");
            for (int i = 0; i < features.size(); i++) {
                sql.append("WHEN #{features[").append(i).append("].gid} THEN #{features[")
                   .append(i).append("].properties.").append(col).append("}");
                if (!"varchar".equals(dataType)) {
                    sql.append("::").append(dataType);
                }
                sql.append(" ");
            }
            sql.append("ELSE \"").append(col).append("\" END");
        }

        if (hasLastMdfcnId) {
            sql.append(", last_mdfcn_id = #{currentUserId}");
        }
        if (hasLastMdfcnDt) {
            sql.append(", last_mdfcn_dt = CURRENT_TIMESTAMP");
        }

        sql.append(" WHERE gid IN (");
        for (int i = 0; i < features.size(); i++) {
            if (i > 0) sql.append(", ");
            sql.append("#{features[").append(i).append("].gid}");
        }
        sql.append(")");
        return sql.toString();
    }

    /**
     * 배치 DELETE SQL.
     */
    public String deleteLayerFeatures(Map<String, Object> params) {
        String schemaName = validateIdentifier((String) params.get("schemaName"), "스키마명");
        String tableName = validateIdentifier((String) params.get("tableName"), "테이블명");

        @SuppressWarnings("unchecked")
        List<Integer> gids = (List<Integer>) params.get("gids");

        if (gids == null || gids.isEmpty()) {
            return "DELETE FROM " + schemaName + "." + tableName + " WHERE 1=0";
        }

        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM ").append(schemaName).append(".").append(tableName).append(" WHERE gid IN (");
        for (int i = 0; i < gids.size(); i++) {
            if (i > 0) sql.append(", ");
            sql.append("#{gids[").append(i).append("]}");
        }
        sql.append(")");
        return sql.toString();
    }

    /**
     * 피처 조회 SQL (검증용).
     */
    public String selectLayerFeature(Map<String, Object> params) {
        String schemaName = (String) params.get("schemaName");
        if (schemaName == null || schemaName.trim().isEmpty()) {
            schemaName = "incheon_geo";
        }
        schemaName = validateIdentifier(schemaName, "스키마명");
        String tableName = validateIdentifier((String) params.get("tableName"), "테이블명");
        return "SELECT gid, ST_AsGeoJSON(geom) as geometry FROM " + schemaName + "." + tableName + " WHERE gid = #{gid}";
    }

    /**
     * Added 이력 INSERT SQL (STTS_CD='C').
     */
    public String batchInsertHistoryAdded(Map<String, Object> params) {
        return buildHistoryInsertSql(params, "C", false);
    }

    /**
     * Modified 이력 INSERT SQL (STTS_CD='U').
     */
    public String batchInsertHistoryModified(Map<String, Object> params) {
        return buildHistoryInsertSql(params, "U", true);
    }

    /**
     * Deleted 이력 INSERT SQL (STTS_CD='D').
     */
    public String batchInsertHistoryDeleted(Map<String, Object> params) {
        String schemaName = validateIdentifier((String) params.get("schemaName"), "스키마명");
        String tableName = validateIdentifier((String) params.get("tableName"), "테이블명");

        @SuppressWarnings("unchecked")
        List<Integer> gids = (List<Integer>) params.get("gids");

        if (gids == null || gids.isEmpty()) {
            return "SELECT 1 WHERE 1=0";
        }

        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(schemaName).append(".").append(tableName)
           .append(" (gid, stts_cd) VALUES ");
        for (int i = 0; i < gids.size(); i++) {
            if (i > 0) sql.append(", ");
            sql.append("(#{gids[").append(i).append("]}, 'D')");
        }
        return sql.toString();
    }

    // ========== Private helpers ==========

    private String buildHistoryInsertSql(Map<String, Object> params, String statusCode, boolean includeGid) {
        String schemaName = validateIdentifier((String) params.get("schemaName"), "스키마명");
        String tableName = validateIdentifier((String) params.get("tableName"), "테이블명");

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) params.get("columns");
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> features = (List<Map<String, Object>>) params.get("features");

        if (features == null || features.isEmpty()) {
            return "SELECT 1 WHERE 1=0";
        }

        @SuppressWarnings("unchecked")
        Map<String, Object> firstProperties = (Map<String, Object>) features.get(0).get("properties");
        boolean hasGeometry = ObjectUtils.isNotEmpty(features.get(0).get("geometryString"));
        List<String> validColumns = new ArrayList<>();
        boolean hasFrstRegId = false;
        boolean hasFrstRegDt = false;

        if (firstProperties != null) {
            for (Map<String, Object> column : columns) {
                String columnName = (String) column.get("column_name");
                if ("stts_cd".equalsIgnoreCase(columnName)) continue;
                if ("frst_reg_id".equalsIgnoreCase(columnName)) { hasFrstRegId = true; continue; }
                if ("frst_reg_dt".equalsIgnoreCase(columnName)) { hasFrstRegDt = true; continue; }
                if (firstProperties.containsKey(columnName)) {
                    validColumns.add(columnName);
                }
            }
        }

        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(schemaName).append(".").append(tableName).append(" (");
        if (includeGid) sql.append("gid, ");
        if (hasGeometry) sql.append("geom, ");
        sql.append("stts_cd");

        for (String col : validColumns) {
            sql.append(", \"").append(col).append("\"");
        }
        if (hasFrstRegId) sql.append(", frst_reg_id");
        if (hasFrstRegDt) sql.append(", frst_reg_dt");
        sql.append(") VALUES ");

        for (int i = 0; i < features.size(); i++) {
            if (i > 0) sql.append(", ");
            sql.append("(");
            if (includeGid) {
                sql.append("#{features[").append(i).append("].gid}, ");
            }
            if (hasGeometry) {
                sql.append("ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{features[").append(i).append("].geometryString})), #{srid}), ");
            }
            sql.append("'").append(statusCode).append("'");

            for (String col : validColumns) {
                String dataType = getColumnDataType(columns, col);
                sql.append(", #{features[").append(i).append("].properties.").append(col);
                if (!"varchar".equals(dataType)) {
                    sql.append("}::").append(dataType);
                } else {
                    sql.append("}");
                }
            }
            if (hasFrstRegId) sql.append(", #{currentUserId}");
            if (hasFrstRegDt) sql.append(", CURRENT_TIMESTAMP");
            sql.append(")");
        }
        return sql.toString();
    }

    private boolean isSequenceColumn(String columnName) {
        for (String seq : SEQUENCE_COLUMNS) {
            if (seq.equals(columnName)) return true;
        }
        return false;
    }

    private boolean isAuditColumn(String columnName) {
        for (String audit : AUDIT_INSERT_COLUMNS) {
            if (audit.equalsIgnoreCase(columnName)) return true;
        }
        return false;
    }

    private boolean[] detectAuditColumns(List<Map<String, Object>> columns, String[] auditNames) {
        boolean[] flags = new boolean[auditNames.length];
        if (columns == null) return flags;
        for (Map<String, Object> column : columns) {
            String name = (String) column.get("column_name");
            for (int i = 0; i < auditNames.length; i++) {
                if (auditNames[i].equalsIgnoreCase(name)) {
                    flags[i] = true;
                }
            }
        }
        return flags;
    }

    private void appendAuditColumnNames(StringBuilder sql, boolean hasFrstRegId, boolean hasFrstRegDt, boolean hasLastMdfcnId, boolean hasLastMdfcnDt) {
        if (hasFrstRegId) sql.append(", frst_reg_id");
        if (hasFrstRegDt) sql.append(", frst_reg_dt");
        if (hasLastMdfcnId) sql.append(", last_mdfcn_id");
        if (hasLastMdfcnDt) sql.append(", last_mdfcn_dt");
    }

    private void appendAuditColumnValues(StringBuilder sql, boolean hasFrstRegId, boolean hasFrstRegDt, boolean hasLastMdfcnId, boolean hasLastMdfcnDt) {
        if (hasFrstRegId) sql.append(", #{currentUserId}");
        if (hasFrstRegDt) sql.append(", CURRENT_TIMESTAMP");
        if (hasLastMdfcnId) sql.append(", #{currentUserId}");
        if (hasLastMdfcnDt) sql.append(", CURRENT_TIMESTAMP");
    }

    private String getColumnDataType(List<Map<String, Object>> columns, String columnName) {
        if (columns == null) return "varchar";
        for (Map<String, Object> column : columns) {
            if (columnName.equals(column.get("column_name"))) {
                String dataType = (String) column.get("data_type");
                return DATA_TYPE_MAPPING.getOrDefault(dataType, "varchar");
            }
        }
        return "varchar";
    }

    private String validateIdentifier(String identifier, String label) {
        if (identifier == null || !SAFE_IDENTIFIER.matcher(identifier).matches()) {
            throw new IllegalArgumentException("유효하지 않은 " + label + ": " + identifier);
        }
        return identifier;
    }
}