Reworked devices and sites - added new attribute to manage local connection to IAS

• Made si- and lt- networks optional
• Added a new attr to manage when you need lt-
• si- is probably gonna. die
• moved ts_address from device to site.

gso/migrations/versions/2023-08-07_7728b3ef410c_modify_sites_and_device_pb.py

+"""Modify sites and device PB.
+
+Revision ID: 7728b3ef410c
+Revises: e57ebfade80b
+Create Date: 2023-08-07 12:53:31.090424
+
+"""
+import sqlalchemy as sa
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision = '7728b3ef410c'
+down_revision = 'e57ebfade80b'
+branch_labels = None
+depends_on = None
+
+
+def upgrade() -> None:
+    conn = op.get_bind()
+    conn.execute("""
+DELETE FROM product_block_resource_types WHERE product_block_resource_types.product_block_id IN (SELECT product_blocks.product_block_id FROM product_blocks WHERE product_blocks.name IN ('DeviceBlock')) AND product_block_resource_types.resource_type_id = (SELECT resource_types.resource_type_id FROM resource_types WHERE resource_types.resource_type IN ('device_ts_address'))
+    """)
+    conn.execute("""
+DELETE FROM subscription_instance_values USING product_block_resource_types WHERE subscription_instance_values.subscription_instance_id IN (SELECT subscription_instances.subscription_instance_id FROM subscription_instances WHERE subscription_instances.subscription_instance_id IN (SELECT product_blocks.product_block_id FROM product_blocks WHERE product_blocks.name IN ('DeviceBlock'))) AND product_block_resource_types.resource_type_id = (SELECT resource_types.resource_type_id FROM resource_types WHERE resource_types.resource_type IN ('device_ts_address'))
+    """)
+    conn.execute("""
+DELETE FROM subscription_instance_values WHERE subscription_instance_values.resource_type_id IN (SELECT resource_types.resource_type_id FROM resource_types WHERE resource_types.resource_type IN ('device_ts_address'))
+    """)
+    conn.execute("""
+DELETE FROM resource_types WHERE resource_types.resource_type IN ('device_ts_address')
+    """)
+    conn.execute("""
+INSERT INTO resource_types (resource_type, description) VALUES ('device_is_ias_connected', 'device has lt to IAS') RETURNING resource_types.resource_type_id
+    """)
+    conn.execute("""
+INSERT INTO resource_types (resource_type, description) VALUES ('site_ts_address', 'Address of the terminal server') RETURNING resource_types.resource_type_id
+    """)
+    conn.execute("""
+INSERT INTO product_block_resource_types (product_block_id, resource_type_id) VALUES ((SELECT product_blocks.product_block_id FROM product_blocks WHERE product_blocks.name IN ('SiteBlock')), (SELECT resource_types.resource_type_id FROM resource_types WHERE resource_types.resource_type IN ('site_ts_address')))
+    """)
+    conn.execute("""
+INSERT INTO product_block_resource_types (product_block_id, resource_type_id) VALUES ((SELECT product_blocks.product_block_id FROM product_blocks WHERE product_blocks.name IN ('DeviceBlock')), (SELECT resource_types.resource_type_id FROM resource_types WHERE resource_types.resource_type IN ('device_is_ias_connected')))
+    """)
+    conn.execute("""
+
+                WITH subscription_instance_ids AS (
+                    SELECT subscription_instances.subscription_instance_id
+                    FROM   subscription_instances
+                    WHERE  subscription_instances.product_block_id IN (
+                        SELECT product_blocks.product_block_id
+                        FROM   product_blocks
+                        WHERE  product_blocks.name = 'SiteBlock'
+                    )
+                )
+
+                INSERT INTO
+                    subscription_instance_values (subscription_instance_id, resource_type_id, value)
+                SELECT
+                    subscription_instance_ids.subscription_instance_id,
+                    resource_types.resource_type_id,
+                    'Address of the terminal server'
+                FROM resource_types
+                CROSS JOIN subscription_instance_ids
+                WHERE resource_types.resource_type = 'site_ts_address'
+        
+    """)
+    conn.execute("""
+
+                WITH subscription_instance_ids AS (
+                    SELECT subscription_instances.subscription_instance_id
+                    FROM   subscription_instances
+                    WHERE  subscription_instances.product_block_id IN (
+                        SELECT product_blocks.product_block_id
+                        FROM   product_blocks
+                        WHERE  product_blocks.name = 'DeviceBlock'
+                    )
+                )
+
+                INSERT INTO
+                    subscription_instance_values (subscription_instance_id, resource_type_id, value)
+                SELECT
+                    subscription_instance_ids.subscription_instance_id,
+                    resource_types.resource_type_id,
+                    'true'
+                FROM resource_types
+                CROSS JOIN subscription_instance_ids
+                WHERE resource_types.resource_type = 'device_is_ias_connected'
+        
+    """)
+
+
+def downgrade() -> None:
+    conn = op.get_bind()
+    conn.execute("""
+DELETE FROM product_block_resource_types WHERE product_block_resource_types.product_block_id IN (SELECT product_blocks.product_block_id FROM product_blocks WHERE product_blocks.name IN ('SiteBlock')) AND product_block_resource_types.resource_type_id = (SELECT resource_types.resource_type_id FROM resource_types WHERE resource_types.resource_type IN ('site_ts_address'))
+    """)
+    conn.execute("""
+DELETE FROM subscription_instance_values USING product_block_resource_types WHERE subscription_instance_values.subscription_instance_id IN (SELECT subscription_instances.subscription_instance_id FROM subscription_instances WHERE subscription_instances.subscription_instance_id IN (SELECT product_blocks.product_block_id FROM product_blocks WHERE product_blocks.name IN ('SiteBlock'))) AND product_block_resource_types.resource_type_id = (SELECT resource_types.resource_type_id FROM resource_types WHERE resource_types.resource_type IN ('site_ts_address'))
+    """)
+    conn.execute("""
+DELETE FROM product_block_resource_types WHERE product_block_resource_types.product_block_id IN (SELECT product_blocks.product_block_id FROM product_blocks WHERE product_blocks.name IN ('DeviceBlock')) AND product_block_resource_types.resource_type_id = (SELECT resource_types.resource_type_id FROM resource_types WHERE resource_types.resource_type IN ('device_is_ias_connected'))
+    """)
+    conn.execute("""
+DELETE FROM subscription_instance_values USING product_block_resource_types WHERE subscription_instance_values.subscription_instance_id IN (SELECT subscription_instances.subscription_instance_id FROM subscription_instances WHERE subscription_instances.subscription_instance_id IN (SELECT product_blocks.product_block_id FROM product_blocks WHERE product_blocks.name IN ('DeviceBlock'))) AND product_block_resource_types.resource_type_id = (SELECT resource_types.resource_type_id FROM resource_types WHERE resource_types.resource_type IN ('device_is_ias_connected'))
+    """)
+    conn.execute("""
+DELETE FROM subscription_instance_values WHERE subscription_instance_values.resource_type_id IN (SELECT resource_types.resource_type_id FROM resource_types WHERE resource_types.resource_type IN ('device_is_ias_connected', 'site_ts_address'))
+    """)
+    conn.execute("""
+DELETE FROM resource_types WHERE resource_types.resource_type IN ('device_is_ias_connected', 'site_ts_address')
+    """)