import django.contrib.postgres.fields.jsonb
from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ("models", "7125_geometry_index_table_update"),
    ]

    sql = """
        DROP VIEW IF EXISTS vw_annotations;

        CREATE OR REPLACE VIEW vw_annotations AS
        SELECT uuid(json_array_elements(t.tiledata::json->n.nodeid::text->'features')->>'id') as feature_id,
            t.tileid,
            t.tiledata,
            t.resourceinstanceid,
            t.nodegroupid,
            n.nodeid,
            jsonb_array_elements(t.tiledata::jsonb->n.nodeid::text->'features') as feature,
            json_array_elements(t.tiledata::json->n.nodeid::text->'features')->'properties'->>'canvas' as canvas
        FROM tiles t
            LEFT JOIN nodes n ON t.nodegroupid = n.nodegroupid
        WHERE (
                (
                    SELECT count(*) AS count
                    FROM jsonb_object_keys(t.tiledata) jsonb_object_keys(jsonb_object_keys)
                    WHERE (
                            jsonb_object_keys.jsonb_object_keys IN (
                                SELECT n_1.nodeid::text AS nodeid
                                FROM nodes n_1
                                WHERE n_1.datatype = 'annotation'::text
                            )
                        )
                )
            ) > 0
        AND n.datatype = 'annotation'::text;
    """

    reverse_sql = "DROP VIEW IF EXISTS vw_annotations;"

    operations = [
        migrations.RunSQL(
            sql,
            reverse_sql,
        ),
    ]

@@ -1457,16 +1457,27 @@
 _sql = %22
+%22%22%0A        
 DROP VIE
@@ -1503,16 +1503,1184 @@
 tations;
+%0A%0A        CREATE OR REPLACE VIEW vw_annotations AS%0A        SELECT json_array_elements(t.tiledata::json-%3En.nodeid::text-%3E'features')-%3E%3E'id' AS feature_id,%0A            t.tileid,%0A            t.tiledata,%0A            t.resourceinstanceid,%0A            t.nodegroupid,%0A            n.nodeid,%0A            jsonb_array_elements(t.tiledata::jsonb-%3En.nodeid::text-%3E'features') AS feature,%0A            json_array_elements(t.tiledata::json-%3En.nodeid::text-%3E'features')-%3E'properties'-%3E%3E'canvas' AS canvas%0A        FROM tiles t%0A            LEFT JOIN nodes n ON t.nodegroupid = n.nodegroupid%0A        WHERE (%0A                (%0A                    SELECT count(*) AS count%0A                    FROM jsonb_object_keys(t.tiledata) jsonb_object_keys(jsonb_object_keys)%0A                    WHERE (%0A                            jsonb_object_keys.jsonb_object_keys IN (%0A                                SELECT n_1.nodeid::text AS nodeid%0A                                FROM nodes n_1%0A                                WHERE n_1.datatype = 'annotation'::text%0A                            )%0A                        )%0A                )%0A            ) %3E 0%0A        AND n.datatype = 'annotation'::text;%0A    %22%22
 %22%0A%0A    o

Update reverse migration, #8935