package incheon.ags.mrb.analysis.mapper;

import incheon.ags.mrb.analysis.vo.FeatureRowDTO;
import org.apache.ibatis.annotations.Param;
import org.geotools.api.feature.simple.SimpleFeatureType;
import org.geotools.api.feature.type.AttributeDescriptor;
import org.locationtech.jts.geom.Geometry;

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

public class SpatialAnalysisSqlProvider {

    private static final String GEOMETRY_ID = "gid";
    private static final String GEOMETRY_COLUMN = "geom";

    private static final Pattern IDENT = Pattern.compile("^[A-Za-z_][A-Za-z0-9_]*$");

    private static void assertIdent(String s) {
        if (s == null || !IDENT.matcher(s).matches()) {
            throw new IllegalArgumentException("Invalid identifier: " + s);
        }
    }

    private static String q(String ident) {
        return "\"" + ident + "\"";
    }

    public static String toGeomSubtype(Class<?> geomBinding) {
        if (geomBinding == null) return "GEOMETRY";
        if (org.locationtech.jts.geom.Point.class.isAssignableFrom(geomBinding)) return "POINT";
        if (org.locationtech.jts.geom.MultiPoint.class.isAssignableFrom(geomBinding)) return "MULTIPOINT";
        if (org.locationtech.jts.geom.LineString.class.isAssignableFrom(geomBinding)) return "LINESTRING";
        if (org.locationtech.jts.geom.MultiLineString.class.isAssignableFrom(geomBinding)) return "MULTILINESTRING";
        if (org.locationtech.jts.geom.Polygon.class.isAssignableFrom(geomBinding)) return "POLYGON";
        if (org.locationtech.jts.geom.MultiPolygon.class.isAssignableFrom(geomBinding)) return "MULTIPOLYGON";
        if (org.locationtech.jts.geom.GeometryCollection.class.isAssignableFrom(geomBinding))
            return "GEOMETRYCOLLECTION";
        return "GEOMETRY";
    }

    public static String toSqlType(Class<?> binding) {
        if (String.class.isAssignableFrom(binding)) return "VARCHAR";
        if (Integer.class.isAssignableFrom(binding)) return "INTEGER";
        if (Long.class.isAssignableFrom(binding)) return "BIGINT";
        if (Short.class.isAssignableFrom(binding)) return "SMALLINT";
        if (Double.class.isAssignableFrom(binding)) return "DOUBLE PRECISION";
        if (Float.class.isAssignableFrom(binding)) return "REAL";
        if (Boolean.class.isAssignableFrom(binding)) return "BOOLEAN";
        if (java.sql.Timestamp.class.isAssignableFrom(binding)) return "TIMESTAMP";
        if (java.sql.Date.class.isAssignableFrom(binding)) return "DATE";
        if (java.util.Date.class.isAssignableFrom(binding)) return "TIMESTAMP";
        if (java.math.BigDecimal.class.isAssignableFrom(binding)) return "NUMERIC";
        if (Object.class.equals(binding) || binding == null) return "VARCHAR";
        throw new IllegalArgumentException("Unsupported attribute type: " + binding.getName());
    }

    public String createTableSql(
            @Param("type") SimpleFeatureType type,
            @Param("schemaName") String schemaName,
            @Param("tableName") String tableName,
            @Param("geomId") String geomId,
            @Param("geomColumn") String geomColumn,
            @Param("geomType") String geomType,
            @Param("srid") Integer srid) {

        assertIdent(schemaName);
        assertIdent(tableName);
        assertIdent(geomId);
        assertIdent(geomColumn);
        assertIdent(geomType);

        String qualifiedName = !schemaName.isBlank()
                ? schemaName + "." + tableName
                : tableName;

        String geomTypeWithSrid = String.format("GEOMETRY(%s, %d)", geomType, srid);
        StringBuilder sb = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                .append(qualifiedName)
                .append(" (")
                .append(geomId)
                .append(" SERIAL4")
                .append(", geom ")
                .append(geomTypeWithSrid);

        Map<String, Integer> counter = new HashMap<>();
        type.getAttributeDescriptors().forEach(desc -> {
            String columnName = desc.getLocalName().toLowerCase().replace(".", "_");
            if (columnName.equalsIgnoreCase(geomId)) {
                return;
            }
            Class<?> binding = desc.getType().getBinding();
            if (org.locationtech.jts.geom.Geometry.class.isAssignableFrom(binding)) {
                return;
            }
            int idx = counter.getOrDefault(columnName, 0);
            if (idx > 0) {
                columnName = columnName + "_" + idx;
            }
            counter.put(desc.getLocalName().toLowerCase().replace(".", "_"), idx + 1);
            sb.append(", ").append(q(columnName)).append(" ").append(toSqlType(binding));
        });

        sb.append(")");
        return sb.toString();
    }

    public String insertFeatureSql(@Param("feature") FeatureRowDTO feature,
                                   @Param("type") SimpleFeatureType type,
                                   @Param("schemaName") String schemaName,
                                   @Param("tableName") String tableName) {

        assertIdent(schemaName);
        assertIdent(tableName);

        if (feature == null) {
            throw new IllegalArgumentException("feature is null");
        }

        final String qn = !schemaName.isBlank()
                ? schemaName + "." + tableName : tableName;

        StringBuilder cols = new StringBuilder(GEOMETRY_COLUMN);
        Map<String, Integer> counter = new HashMap<>();
        type.getAttributeDescriptors().forEach(d -> {
            if (!Geometry.class.isAssignableFrom(d.getType().getBinding())) {
                String columnName = d.getLocalName().toLowerCase().replace(".", "_");
                if (columnName.equalsIgnoreCase(GEOMETRY_ID)) {
                    return;
                }
                int idx = counter.getOrDefault(columnName, 0);
                if (idx > 0) {
                    columnName = columnName + "_" + idx;
                }
                counter.put(d.getLocalName().toLowerCase().replace(".", "_"), idx + 1);
                cols.append(", ").append(q(columnName));
            }
        });

        StringBuilder values = new StringBuilder("(");
        values.append("ST_GeomFromWKB(#{feature.geom,jdbcType=BINARY}, 3857)");
        int featureIndex = 0;
        for (int i = 0; i < type.getAttributeDescriptors().size(); i++) {
            AttributeDescriptor desc = type.getDescriptor(i);
            if (desc.getLocalName().equalsIgnoreCase(GEOMETRY_ID)) {
                featureIndex++;
                continue;
            }
            if (Geometry.class.isAssignableFrom(desc.getType().getBinding())) {
                continue;
            }
            Class<?> binding = desc.getType().getBinding();
            String sqlType = toSqlType(binding);
            values.append(", (#{feature.attrs[").append(featureIndex).append("]}::").append(sqlType).append(")");
            featureIndex++;
        }
        values.append(")");

        return "<script>" +
                "INSERT INTO " + qn +
                " (" + cols + ") VALUES " +
                values +
                "</script>";
    }

    public String dropTableSql(@Param("schemaName") String schemaName,
                               @Param("tableName") String tableName) {

        assertIdent(schemaName);
        assertIdent(tableName);

        String qualified = q(schemaName) + "." + q(tableName);
        return "DROP TABLE IF EXISTS " + qualified;
    }

    public String createUniqueIndexSql(@Param("schema") String schema,
                                       @Param("table") String table,
                                       @Param("column") String column,
                                       @Param("indexName") String indexName) {
        assertIdent(schema);
        assertIdent(table);
        assertIdent(column);
        assertIdent(indexName);

        return "CREATE UNIQUE INDEX IF NOT EXISTS " +
                indexName + " ON " +
                schema + "." + table +
                " (" + column + ")";
    }

    public String createSpatialIndexSql(@Param("schema") String schema,
                                        @Param("table") String table,
                                        @Param("column") String column,
                                        @Param("indexName") String indexName) {
        assertIdent(schema);
        assertIdent(table);
        assertIdent(column);
        assertIdent(indexName);

        return "CREATE INDEX IF NOT EXISTS " +
                indexName +
                " ON " +
                schema + "." + table +
                " USING GIST (" + column + ")";
    }


}