a
    ;]ri[                     @   sD   d Z ddlmZ ddlZddlZdZdZdZdZ	dd Z
dd	 ZdS )
zjadd frame stats

Revision ID: 8350e5a3e24c
Revises: afaee09a3222
Create Date: 2025-11-26 12:24:15.234425

    )opNZ8350e5a3e24cafaee09a3222c                   C   s   t dtjdt dd t dtjdtjjdddd	d
dddf t dtjdtjjdddd	d
dddf t t	d d S )Nframespoints_countT)nullablegeometry_complexPOLYGONi  FST_GeomFromEWKTgeometry)geometry_typesridspatial_index	from_textnamegeometry_boundsa  
        CREATE OR REPLACE FUNCTION frame_geometry(partition_name VARCHAR, frame_id INT) RETURNS void LANGUAGE plpgsql AS $func$
            DECLARE
                batch_count INT := 0;
                points_per_batch INT := 1000000;
                i INT := 0;
                sum INT := 0;
                geom VARCHAR := '';
                temp_table_name VARCHAR := (partition_name || '_geometry');
            BEGIN
                EXECUTE FORMAT('DROP TABLE if exists %s', temp_table_name);
                EXECUTE FORMAT('CREATE TEMPORARY TABLE %s(total int, geometry geometry)', temp_table_name);

                LOOP
                    EXECUTE FORMAT('
                        WITH geometry_cte AS (
                            SELECT geometry FROM %s ORDER BY id LIMIT ' || points_per_batch || ' OFFSET (' || i * points_per_batch || ')
                        )
                        INSERT INTO %s(total, geometry)
                            SELECT
                                count(*),
                                ST_ConvexHull(ST_Collect(array_agg(geometry))) 
                            FROM geometry_cte;', partition_name, temp_table_name);

                    EXECUTE FORMAT('SELECT min(total) FROM %s', temp_table_name) INTO batch_count;
                    EXIT WHEN batch_count < points_per_batch;
                    i := i + 1;
                END LOOP;

                EXECUTE FORMAT('SELECT sum(total) FROM %s', temp_table_name) INTO sum;

                EXECUTE FORMAT('UPDATE FRAMES SET 
                    points_count = ' || sum || ', 
                    geometry_bounds = (SELECT ST_SetSRID(ST_EXTENT(geometry), 4326) FROM %s), 
                    geometry_complex = (SELECT ST_Union(geometry) FROM %s), 
                    geometry = (SELECT ST_OrientedEnvelope(ST_Union(geometry)) FROM %s) 
                    WHERE id = '|| frame_id || ';', temp_table_name, temp_table_name, temp_table_name);
                EXECUTE FORMAT('DROP TABLE %s', temp_table_name);
            END;
        $func$;
    )
r   
add_columnsaColumnINTEGERgeoalchemy2typesGeometryexecutetext r   r   V/var/www/staging/api/app_home/migrations/insar/alembic/versions/030_add_frame_stats.pyupgrade   s    ,,r   c                   C   s2   t dd t dd t dd t d d S )Nr   r   r   r   zCDROP function frame_geometry(partition_name VARCHAR, frame_id INT);)r   drop_columnr   r   r   r   r   	downgradeD   s    r   )__doc__alembicr   
sqlalchemyr   r   revisiondown_revisionbranch_labels
depends_onr   r   r   r   r   r   <module>   s   0