package incheon.product.geoview2d.layer.mapper;

import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Nested;
import org.junit.jupiter.api.Test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.assertThatThrownBy;

/**
 * LayerSqlProvider 단위 테스트.
 * SQL 주입 방어(validateIdentifier), 빈 입력 가드, 동적 SQL 생성을 검증한다.
 */
class LayerSqlProviderTest {

    private final LayerSqlProvider provider = new LayerSqlProvider();

    // ========== 테스트 데이터 헬퍼 ==========

    private Map<String, Object> baseParams(String schema, String table) {
        Map<String, Object> params = new HashMap<>();
        params.put("schemaName", schema);
        params.put("tableName", table);
        return params;
    }

    private List<Map<String, Object>> columns(String... names) {
        return List.of(names).stream().map(name -> {
            Map<String, Object> col = new HashMap<>();
            col.put("column_name", name);
            col.put("data_type", "character varying");
            return col;
        }).toList();
    }

    private List<Map<String, Object>> columnsWithType(String name, String dataType) {
        return List.of(Map.of("column_name", name, "data_type", dataType));
    }

    private Map<String, Object> feature(Map<String, Object> properties) {
        Map<String, Object> feature = new HashMap<>();
        feature.put("properties", properties);
        feature.put("geometryString", "{\"type\":\"Point\",\"coordinates\":[126.7,37.4]}");
        return feature;
    }

    // ========== SQL 주입 방지 ==========

    @Nested
    @DisplayName("SQL 주입 방지 — validateIdentifier")
    class SqlInjectionPrevention {

        @Test
        @DisplayName("세미콜론 포함 스키마명 → IllegalArgumentException")
        void schemaWithSemicolon() {
            Map<String, Object> params = baseParams("public; DROP TABLE users", "my_table");
            params.put("gids", List.of(1));

            assertThatThrownBy(() -> provider.deleteLayerFeatures(params))
                    .isInstanceOf(IllegalArgumentException.class)
                    .hasMessageContaining("스키마명");
        }

        @Test
        @DisplayName("공백 포함 테이블명 → IllegalArgumentException")
        void tableWithSpace() {
            Map<String, Object> params = baseParams("public", "my table");
            params.put("gids", List.of(1));

            assertThatThrownBy(() -> provider.deleteLayerFeatures(params))
                    .isInstanceOf(IllegalArgumentException.class)
                    .hasMessageContaining("테이블명");
        }

        @Test
        @DisplayName("숫자로 시작하는 식별자 → IllegalArgumentException")
        void identifierStartsWithNumber() {
            Map<String, Object> params = baseParams("123schema", "my_table");
            params.put("gids", List.of(1));

            assertThatThrownBy(() -> provider.deleteLayerFeatures(params))
                    .isInstanceOf(IllegalArgumentException.class);
        }

        @Test
        @DisplayName("null 스키마 → IllegalArgumentException")
        void nullSchema() {
            Map<String, Object> params = baseParams(null, "my_table");
            params.put("gids", List.of(1));

            assertThatThrownBy(() -> provider.deleteLayerFeatures(params))
                    .isInstanceOf(IllegalArgumentException.class);
        }

        @Test
        @DisplayName("유효한 식별자(영문/숫자/언더스코어) → 정상 통과")
        void validIdentifier() {
            Map<String, Object> params = baseParams("iccom", "flight_photo_lyr");
            params.put("gids", List.of(1));

            String sql = provider.deleteLayerFeatures(params);
            assertThat(sql).contains("iccom.flight_photo_lyr");
        }
    }

    // ========== 빈 입력 가드 ==========

    @Nested
    @DisplayName("빈 입력 가드 — 빈 리스트/null 시 no-op SQL")
    class EmptyInputGuard {

        @Test
        @DisplayName("INSERT — 빈 features → SELECT 1 WHERE 1=0")
        void insertEmptyFeatures() {
            Map<String, Object> params = baseParams("iccom", "my_table");
            params.put("columns", columns("col1"));
            params.put("features", List.of());

            String sql = provider.batchInsertLayerFeatures(params);
            assertThat(sql).isEqualTo("SELECT 1 WHERE 1=0");
        }

        @Test
        @DisplayName("UPDATE — 빈 features → no-op UPDATE")
        void updateEmptyFeatures() {
            Map<String, Object> params = baseParams("iccom", "my_table");
            params.put("columns", columns("col1"));
            params.put("features", List.of());

            String sql = provider.batchUpdateLayerFeatures(params);
            assertThat(sql).contains("WHERE 1=0");
        }

        @Test
        @DisplayName("DELETE — 빈 gids → no-op DELETE")
        void deleteEmptyGids() {
            Map<String, Object> params = baseParams("iccom", "my_table");
            params.put("gids", List.of());

            String sql = provider.deleteLayerFeatures(params);
            assertThat(sql).contains("WHERE 1=0");
        }
    }

    // ========== 정상 SQL 생성 ==========

    @Nested
    @DisplayName("동적 SQL 생성")
    class SqlGeneration {

        @Test
        @DisplayName("INSERT — geometry + 컬럼 + RETURNING *")
        void insertGeneratesCorrectSql() {
            Map<String, Object> params = baseParams("iccom", "layer_data");
            params.put("columns", columns("name", "code"));
            params.put("features", List.of(feature(Map.of("name", "테스트", "code", "A01"))));

            String sql = provider.batchInsertLayerFeatures(params);
            assertThat(sql)
                    .startsWith("INSERT INTO iccom.layer_data")
                    .contains("geom")
                    .contains("ST_SetSRID(ST_Force2D(ST_GeomFromGeoJSON(")
                    .contains("RETURNING *");
        }

        @Test
        @DisplayName("UPDATE — CASE WHEN 패턴")
        void updateGeneratesCaseWhenSql() {
            Map<String, Object> params = baseParams("iccom", "layer_data");
            params.put("columns", columns("name"));

            Map<String, Object> feat = feature(Map.of("name", "수정"));
            feat.put("gid", 42);
            params.put("features", List.of(feat));

            String sql = provider.batchUpdateLayerFeatures(params);
            assertThat(sql)
                    .startsWith("UPDATE iccom.layer_data SET")
                    .contains("geom = CASE gid")
                    .contains("WHERE gid IN (");
        }

        @Test
        @DisplayName("DELETE — IN 절")
        void deleteGeneratesInClause() {
            Map<String, Object> params = baseParams("iccom", "layer_data");
            params.put("gids", List.of(1, 2, 3));

            String sql = provider.deleteLayerFeatures(params);
            assertThat(sql)
                    .isEqualTo("DELETE FROM iccom.layer_data WHERE gid IN (#{gids[0]}, #{gids[1]}, #{gids[2]})");
        }

        @Test
        @DisplayName("SELECT — null 스키마 → 기본값 incheon_geo")
        void selectWithNullSchemaUsesDefault() {
            Map<String, Object> params = baseParams(null, "my_table");
            // selectLayerFeature는 null schema를 "incheon_geo"로 대체
            params.put("schemaName", null);

            String sql = provider.selectLayerFeature(params);
            assertThat(sql).contains("incheon_geo.my_table");
        }

        @Test
        @DisplayName("INSERT — integer 컬럼은 ::integer 캐스팅")
        void insertCastsNonVarcharTypes() {
            Map<String, Object> params = baseParams("iccom", "layer_data");
            params.put("columns", columnsWithType("population", "integer"));
            params.put("features", List.of(feature(Map.of("population", 1000))));

            String sql = provider.batchInsertLayerFeatures(params);
            assertThat(sql).contains("::integer");
        }
    }
}