package incheon.uis.ums.mapper;

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

import org.springframework.stereotype.Component;

import incheon.com.cmm.context.RequestContext;
import incheon.uis.ums.util.UisDynamicFieldUtils;

@Component
public class UisSqlProvider {

    /**
     * PostgreSQL 데이터 타입 매핑
     */
    private static final Map<String, String> DATA_TYPE_MAPPING = Map.of(
        "character varying", "varchar",
        "text", "varchar",
        "integer", "integer", 
        "bigint", "bigint",
        "numeric", "numeric",
        "timestamp with time zone", "timestamp",
        "timestamp without time zone", "timestamp",
        "boolean", "boolean"
    );

    /**
     * 이력 테이블 동적 SELECT 쿼리
     */
    public String batchInsertHistorySelect(Map<String, Object> params) {
    	
        String schemaName = (String) params.get("schemaName");
        String tableName = (String) params.get("tableName");
        
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) params.get("columns");

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT hstry_sn, stts_cd");

        for (Map<String, Object> column : columns) {
            String columnName = (String) column.get("column_name");
            if ("geom".equals(columnName)) {
            	sql.append(", ").append("ST_AsGeoJSON(geom) as geom");
            } else {
            	sql.append(", \"").append(columnName).append("\"");
            }
        }
        
        sql.append(" FROM ").append(schemaName).append(".").append(tableName).append("_h");
        sql.append(" WHERE gid = (#{gid})::bigint");
        sql.append(" ORDER BY hstry_sn DESC LIMIT 30");

        return sql.toString();
    }
    

    /**
     * 탭 연결 테이블 동적 SELECT 쿼리 (Rel-info Target Table Select)
     */
    public String batchTabConnTargetTableSelect(Map<String, Object> params) {
    	
        String schemaName = (String) params.get("schemaName");
        String tableName = (String) params.get("tableName");

        Integer LIMIT = 10;

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

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

        StringBuilder sql = new StringBuilder();
        StringBuilder whereSql = new StringBuilder();

        sql.append("SELECT gid");
        whereSql.append(" WHERE 1=1");

        for (Map<String, Object> column : columns) {
            String columnName = (String) column.get("column_name");
            
            if ("geom".equals(columnName)) {
            	sql.append(", ").append("ST_AsGeoJSON(geom) as geom");
            } else {
                if ("gid".equals(columnName)) continue;
            	sql.append(", \"").append(columnName).append("\"");
            }

            String camelTypeColumnName = UisDynamicFieldUtils.snakeToCamel(columnName);
            if (inputParam.containsKey(camelTypeColumnName)) {
                String dataType = getColumnDataType(columns, columnName);
                whereSql.append(" AND ").append(columnName).append(" = #{inputParam.").append(camelTypeColumnName);
                
                // PostgreSQL 타입 캐스팅 추가
                if (!dataType.equals("varchar")) {
                    whereSql.append("}::").append(dataType);
                } else {
                    whereSql.append("}");
                }
            }
            
            if (inputParam.containsKey(camelTypeColumnName+"_like")) {
                String dataType = getColumnDataType(columns, columnName);
                if (!dataType.equals("varchar")) continue;
                whereSql.append(" AND ").append(columnName).append(" like '%' || #{inputParam.").append(camelTypeColumnName+"_like").append("} || '%'");
            }
        }
        
        sql.append(" FROM ").append(schemaName).append(".").append(tableName);
        sql.append(whereSql);
        sql.append(" ORDER BY gid desc");
        sql.append(" LIMIT ").append(String.valueOf(LIMIT));
        // OFFSET 계산 부분에서 page 파라미터가 String 타입일 수도 있으니 안전하게 변환 처리
        int offsetVal = 0;
        if (inputParam != null && inputParam.containsKey("page")) {
            Object pageObj = inputParam.get("page");
            int pageNum = 1;
            if (pageObj instanceof Number) {
                pageNum = ((Number) pageObj).intValue();
            } else if (pageObj instanceof String) {
                try {
                    pageNum = Integer.parseInt((String) pageObj);
                } catch (NumberFormatException ignored) { pageNum = 1; }
            }
            offsetVal = (pageNum - 1) * LIMIT;
        }
        sql.append(" OFFSET ").append(String.valueOf(offsetVal));

        return sql.toString();
    }

    /**
     * 탭 연결 테이블 동적 INSERT 쿼리 (N:M 관계 연결 시 - 연결 속성 테이블 Insert)
     */
    public String batchTabConnTargetTableInsert(Map<String, Object> params) {
    	
        String schemaName = (String) params.get("schemaName");
        String tableName = (String) params.get("tableName");

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

        StringBuilder sql = new StringBuilder();
        StringBuilder valuesSql = new StringBuilder();
        sql.append("INSERT INTO ").append(schemaName).append(".").append(tableName);
        sql.append(" (");
        for (Map<String, Object> column : columns) {
            String columnName = (String) column.get("column_name");
            if("gid".equals(columnName)) continue;
            String camelTypeColumnName = UisDynamicFieldUtils.snakeToCamel(columnName);
            if (inputParam.containsKey(camelTypeColumnName)) {
                if (valuesSql.length() > 0) {
                    sql.append(", ");
                    valuesSql.append(", ");
                }
                sql.append(columnName);
                String dataType = getColumnDataType(columns, columnName);
                valuesSql.append("#{inputParam.").append(camelTypeColumnName);
                // PostgreSQL 타입 캐스팅 추가
                if (!dataType.equals("varchar")) {
                    valuesSql.append("}::").append(dataType);
                } else {
                    valuesSql.append("}");
                }
            }
        }
        sql.append(") VALUES (");
        sql.append(valuesSql);
        sql.append(") RETURNING *");
        return sql.toString();
    }

    /**
     * 탭 연결 테이블 동적 UPDATE 쿼리 (1:N 관계 연결 시)
     */
    public String batchTabConnTargetTableUpdate(Map<String, Object> params) {
    	
        String schemaName = (String) params.get("schemaName");
        String tableName = (String) params.get("tableName");
        
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> columns = (List<Map<String, Object>>) params.get("columns");
        
        @SuppressWarnings("unchecked")
        Map<String, Object> inputParam = (Map<String, Object>) params.get("inputParam");

        StringBuilder sql = new StringBuilder();
        StringBuilder valuesSql = new StringBuilder();
        sql.append("UPDATE ").append(schemaName).append(".").append(tableName);
        sql.append(" SET ");
        for (Map<String, Object> column : columns) {
            String columnName = (String) column.get("column_name");
            if("gid".equals(columnName)) continue;
            String camelTypeColumnName = UisDynamicFieldUtils.snakeToCamel(columnName);
            if (inputParam.containsKey(camelTypeColumnName)) {
                if (valuesSql.length() > 0) {
                    valuesSql.append(", ");
                }
                
                valuesSql.append(columnName).append(" = #{inputParam.").append(camelTypeColumnName);
                String dataType = getColumnDataType(columns, columnName);
                // PostgreSQL 타입 캐스팅 추가
                if (!dataType.equals("varchar")) {
                    valuesSql.append("}::").append(dataType);
                } else {
                    valuesSql.append("}");
                }
            }
        }
        sql.append(valuesSql);
        sql.append(" WHERE gid = (#{inputParam.gid})::bigint");
        return sql.toString();
    }

    /**
     * 이력 테이블 INSERT - Soft Deleted (STTS_CD='D')
     * soft delete(use_yn='N') 시 전체 스냅샷과 함께 삭제 이력 저장
     */
    @SuppressWarnings("unchecked")
    public String batchInsertHistorySoftDeleted(Map<String, Object> params) {
        String schemaName = (String) params.get("schemaName");
        String tableName = (String) params.get("tableName");

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

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

        Map<String, Object> firstProperties = (Map<String, Object>) features.get(0).get("properties");
        boolean isGeometryColumn = features.get(0).get("geometryString") != null;
        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);
                }
            }
        }

        String currentUserId = null;
        if (hasFrstRegId) {
            currentUserId = RequestContext.getCurrentUserId();
        }

        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(schemaName).append(".").append(tableName);
        if (isGeometryColumn) {
            sql.append(" (gid, geom, stts_cd");
        } else {
            sql.append(" (gid, stts_cd");
        }

        for (String columnName : validColumns) {
            sql.append(", \"").append(columnName).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("(#{features[").append(i).append("].gid}, ");
            if (isGeometryColumn) {
                sql.append("ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(#{features[").append(i).append("].geometryString})), #{srid}), 'D'");
            } else {
                sql.append(" 'D'");
            }

            for (String columnName : validColumns) {
                String dataType = getColumnDataType(columns, columnName);
                sql.append(", #{features[").append(i).append("].properties.").append(columnName);
                if (!dataType.equals("varchar")) {
                    sql.append("}::").append(dataType);
                } else {
                    sql.append("}");
                }
            }

            if (hasFrstRegId) {
                sql.append(", '").append(currentUserId).append("'");
            }
            if (hasFrstRegDt) {
                sql.append(", CURRENT_TIMESTAMP");
            }

            sql.append(")");
        }

        return sql.toString();
    }

    /**
     * 컬럼 데이터 타입 조회
     */
    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"; // 기본값
    }
}