WITH spatial_clusters AS (
    SELECT 
        gid,
        street,
        house_no,
        localdistrict,
        geom,
        -- Räumliche Vor-Gruppierung
        ST_ClusterDBSCAN(geom, eps := 0.00005, minpoints := 1) OVER () as raw_cluster_id
    FROM stolpersteine
    WHERE street LIKE 'B%'
),
clustered_groups AS (
    SELECT 
        street,
        house_no,
        localdistrict,
        COUNT(gid) as count,
        ARRAY_AGG(gid) as gids,
        ST_X(ST_Centroid(ST_Collect(geom))) as lon,
        ST_Y(ST_Centroid(ST_Collect(geom))) as lat,
        raw_cluster_id,
        -- Nummerierung pro Adresse (1, 2, 3...)
        DENSE_RANK() OVER (
            PARTITION BY localdistrict, street, house_no 
            ORDER BY raw_cluster_id
        ) as cluster_index
    FROM spatial_clusters
    GROUP BY 
        raw_cluster_id, 
        street, 
        house_no, 
        localdistrict
)
SELECT 
    -- Der Semantic Key: -c2, -c3 etc. nur ab dem zweiten Cluster
    LOWER(
        REPLACE(localdistrict, ' ', '-') || '-' || 
        REPLACE(street, ' ', '-') || '-' || 
        REPLACE(house_no, ' ', '-') || 
        CASE 
            WHEN cluster_index > 1 THEN '-c' || cluster_index 
            ELSE '' 
        END
    ) AS semantic_key,
    street,
    house_no,
    localdistrict,
    count,
    gids,
    lon,
    lat,
    cluster_index
FROM clustered_groups
ORDER BY localdistrict, street, house_no, cluster_index;