Feature/refacture l3 core services

gso/migrations/versions/2025-03-18_c9c9fdf624b5_re_model_ias.py

+"""Re-model L3CoreServices
+
+    GEANT_IP = "GÉANT IP"
+    IMPORTED_GEANT_IP = "IMPORTED GÉANT IP"
+    GWS = "GWS"
+    IMPORTED_GWS = "IMPORTED GWS"
+    LHCONE = "LHCONE"
+    IMPORTED_LHCONE = "IMPORTED LHCONE"
+    COPERNICUS = "COPERNICUS"
+    IMPORTED_COPERNICUS = "IMPORTED COPERNICUS"
+
+Revision ID: e1afa3790f32
+Revises: b96b0ecf6906
+Create Date: 2025-03-17 10:23:45.917222
+
+"""
+import sqlalchemy as sa
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision = 'e1afa3790f32'
+down_revision = 'b96b0ecf6906'
+branch_labels = None
+depends_on = None
+
+
+def upgrade() -> None:
+    conn = op.get_bind()
+    conn.execute(
+        sa.text(
+            """
+DELETE FROM fixed_inputs
+WHERE fixed_inputs.product_id IN (
+    SELECT products.product_id FROM products WHERE products.name IN ('IAS', 'Imported IAS')
+)
+  AND fixed_inputs.name = 'l3_core_service_type'
+            """
+        )
+    )
+    conn.execute(
+        sa.text(
+            """
+INSERT INTO product_blocks (name, description, tag, status)
+VALUES ('IASProductBlock', 'An Internet Access Service for general internet access', 'IAS', 'active')
+RETURNING product_blocks.product_block_id
+            """
+        )
+    )
+    conn.execute(
+        sa.text(
+            """
+INSERT INTO resource_types (resource_type, description)
+VALUES ('ias_flavor', 'The flavor of the IAS service')
+RETURNING resource_types.resource_type_id
+            """
+        )
+    )
+    conn.execute(
+        sa.text(
+            """ 
+    INSERT INTO product_block_relations
+VALUES (
+    (SELECT product_blocks.product_block_id FROM product_blocks WHERE name = 'IASProductBlock'),
+    (SELECT product_blocks.product_block_id FROM product_blocks WHERE name = 'L3CoreServiceBlock'),
+    NULL,
+    NULL
+);
+            """
+        )
+    )
+
+    conn.execute(
+        sa.text(
+            """
+UPDATE product_product_blocks
+SET product_block_id = (
+    SELECT product_block_id FROM product_blocks WHERE name = 'IASProductBlock'
+)
+WHERE product_block_id = (
+    SELECT product_block_id FROM product_blocks WHERE name = 'L3CoreServiceBlock'
+)
+  AND product_id IN (
+    SELECT product_id FROM products WHERE name IN ('IAS', 'Imported IAS')
+);
+            """
+        )
+    )
+
+    conn.execute(
+        sa.text(
+            """
+UPDATE products
+SET product_type = 'IAS'
+WHERE product_type = 'L3CoreService'
+  AND name = 'IAS';
+            """
+        )
+    )
+
+    conn.execute(
+        sa.text(
+            """
+UPDATE products
+SET product_type = 'ImportedIAS'
+WHERE product_type = 'ImportedL3CoreService'
+  AND name = 'Imported IAS';
+            """
+        )
+    )
+
+    conn.execute(
+        sa.text(
+            """
+-- Step 1: Insert new subscription_instances for 'IASProductBlock' and return their IDs
+WITH inserted AS (
+    INSERT INTO subscription_instances (subscription_id, product_block_id)
+    SELECT
+        s.subscription_id,
+        (
+            SELECT product_block_id
+            FROM product_blocks
+            WHERE name = 'IASProductBlock'
+        ) AS product_block_id
+    FROM subscriptions s
+    WHERE s.product_id = (
+        SELECT product_id
+        FROM products
+        WHERE products.name = 'IAS'
+    )
+    RETURNING subscription_instance_id, subscription_id
+)
+
+-- Step 2: Link newly inserted "IASProductBlock" instances to the existing "L3CoreServiceBlock" instance
+INSERT INTO subscription_instance_relations (
+    in_use_by_id,
+    depends_on_id,
+    order_id,
+    domain_model_attr
+)
+SELECT
+    i.subscription_instance_id AS in_use_by_id,
+    e.subscription_instance_id AS depends_on_id,
+    0 AS order_id,
+    'l3_core' AS domain_model_attr
+FROM inserted i
+JOIN subscription_instances e
+  ON e.subscription_id = i.subscription_id
+  AND e.product_block_id = (
+      SELECT product_block_id
+      FROM product_blocks
+      WHERE name = 'L3CoreServiceBlock'
+  );
+            """
+        )
+    )
+    
+    conn.execute(
+        sa.text(
+            """
+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 ('IASProductBlock')),
+    (SELECT resource_types.resource_type_id FROM resource_types WHERE resource_types.resource_type IN ('ias_flavor'))
+)
+            """
+        )
+    )
+
+    conn.execute(
+        sa.text(
+            """
+
+                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 = 'IASProductBlock'
+                    )
+                )
+                INSERT INTO subscription_instance_values (subscription_instance_id, resource_type_id, value)
+                SELECT
+                    subscription_instance_ids.subscription_instance_id,
+                    resource_types.resource_type_id,
+                    'None'
+                    -- TODO we need to check this with Simone to see what the default value should be, in case it is empty string, this wont be show up in the GUI
+                FROM resource_types
+                CROSS JOIN subscription_instance_ids
+                WHERE resource_types.resource_type = 'ias_flavor'
+
+            """
+        )
+    )
+
+
+def downgrade() -> None:
+    conn = op.get_bind()