delimiter $$
CREATE PROCEDURE AddPersonalSecurityRole()
BEGIN

  IF NOT(SELECT EXISTS(SELECT 1 FROM security_role WHERE id = '73204777-4330-4BD1-8BC7-58F39B18D423')) THEN

    INSERT INTO security_role (id,name,tenant_id)
    	VALUES ('73204777-4330-4BD1-8BC7-58F39B18D423','CoreOne Suite Personal Data',1);

    INSERT INTO servicecorelogin_role_claim (name,application_id) VALUES
    	 ('1:73204777-4330-4BD1-8BC7-58F39B18D423',1);
    SET @roleClaimId = LAST_INSERT_ID();

    INSERT INTO servicedmcore_resource (readonly_display_name,description,resource_type_id,resource_group_id,resource_identifier,owner_group_id,delete_pending,assign_workflow,deallocate_workflow,is_provisioned,foreign_id,owner_id,deprovisioned_date) VALUES
    	 ('CoreOne Suite Personal Data','CoreOne Suite Personal Data',1,NULL,@roleClaimId,0,0,NULL,NULL,1,NULL,NULL,NULL);
    SET @resourceId = LAST_INSERT_ID();

    INSERT INTO servicedmcore_resource_attribute_value (resource_id,attribute_id,serialized_value,explizit_serialized_value,has_changed,to_delete,display_name,readonly_used_value) VALUES
    	 (@resourceId,177,'<string xmlns="http://schemas.microsoft.com/2003/10/Serialization/">CoreOne Suite Personal Data</string>',NULL,1,0,NULL,'CoreOne Suite Personal Data');

    SELECT `id` INTO @entityTypeId FROM security_entity_type x
    WHERE assembly_qualified_name = 'iTsense.Moving.Backend.Services.DmcoreService.DataInterfaces.Servicedmcore.ICoreIdentity, iTsense.Moving.Backend.Services.DmcoreService';

    INSERT INTO security_right (entity_type_id,role_id,filter_definition,security_mode) VALUES
    	 (@entityTypeId,'73204777-4330-4BD1-8BC7-58F39B18D423','{
            "$type": "iTsense.Moving.Backend.DataHandling.Security.Filter.GenericMyCoreIdentityFilter`1[[iTsense.Moving.Backend.Services.DmcoreService.DataInterfaces.Servicedmcore.ICoreIdentity, iTsense.Moving.Backend.Services.DmcoreService]], iTsense.Moving.Backend.DataHandling",
            "PropertyChain": {
              "$type": "System.String[], mscorlib",
              "$values": [
                "Id"
              ]
            }
    	}',1),
    	(@entityTypeId,'73204777-4330-4BD1-8BC7-58F39B18D423','{
            "$type": "iTsense.Moving.Backend.DataHandling.Security.Filter.GenericMyCoreIdentityFilter`1[[iTsense.Moving.Backend.Services.DmcoreService.DataInterfaces.Servicedmcore.ICoreIdentity, iTsense.Moving.Backend.Services.DmcoreService]], iTsense.Moving.Backend.DataHandling",
            "PropertyChain": {
              "$type": "System.String[], mscorlib",
              "$values": [
                "Id"
              ]
            }
    	}',3);
    	
    SELECT `id` INTO @attributeEntityTypeId FROM security_entity_type x
    WHERE assembly_qualified_name = 'iTsense.Moving.Backend.Services.DmcoreService.DataInterfaces.Servicedmcore.IAttribute, iTsense.Moving.Backend.Services.DmcoreService';

    INSERT INTO security_right (entity_type_id,role_id,filter_definition,security_mode) VALUES
    	 (@attributeEntityTypeId,'73204777-4330-4BD1-8BC7-58F39B18D423','{
      "$type": "iTsense.Moving.Backend.DataHandling.Security.Filter.GenericFullAccessFilter`1[[iTsense.Moving.Backend.Services.DmcoreService.DataInterfaces.Servicedmcore.IAttribute, iTsense.Moving.Backend.Services.DmcoreService]], iTsense.Moving.Backend.DataHandling",
      "ElementType": "iTsense.Moving.Backend.Services.DmcoreService.DataInterfaces.Servicedmcore.IAttribute, iTsense.Moving.Backend.Services.DmcoreService"
    }',1);

    SELECT `id` INTO @attributeMappingEntityTypeId FROM security_entity_type x
    WHERE assembly_qualified_name = 'iTsense.Moving.Backend.Services.DmcoreService.DataInterfaces.Servicedmcore.ICoreIdentityTypeAttributeMapping, iTsense.Moving.Backend.Services.DmcoreService';

    INSERT INTO security_right (entity_type_id,role_id,filter_definition,security_mode) VALUES
    	 (@attributeMappingEntityTypeId,'73204777-4330-4BD1-8BC7-58F39B18D423','{
      "$type": "iTsense.Moving.Backend.DataHandling.Security.Filter.GenericMyCoreIdentityCollectionFilter`2[[iTsense.Moving.Backend.Services.DmcoreService.DataInterfaces.Servicedmcore.ICoreIdentityTypeAttributeMapping, iTsense.Moving.Backend.Services.DmcoreService],[iTsense.Moving.Backend.Services.DmcoreService.DataInterfaces.Servicedmcore.ICoreIdentity, iTsense.Moving.Backend.Services.DmcoreService]], iTsense.Moving.Backend.DataHandling",
      "NotContains": false,
      "PropertyChain": {
        "$type": "System.String[], mscorlib",
        "$values": [
          "CoreIdentityType",
          "CoreIdentities"
        ]
      },
      "SubPropertyChain": {
        "$type": "System.String[], mscorlib",
        "$values": [
          "Id"
        ]
      }
    }',1);

    # add read results for my core identity to default rights
    UPDATE `security_entity_type`
    	SET `default_security_rights` = '{
      "$type": "iTsense.Moving.Backend.DataHandling.Security.Role.ISecurityRight[], iTsense.Moving.Backend.DataHandling",
      "$values": [
        {
          "$type": "iTsense.Moving.Backend.DataHandling.Security.Role.SecurityRight, iTsense.Moving.Backend.DataHandling",
          "Filter": {
            "$type": "iTsense.Moving.Backend.DataHandling.Security.Filter.GenericNoAccessFilter`1[[iTsense.Moving.Backend.Services.DmcoreService.DataInterfaces.Servicedmcore.IQualityOfRegistrationCheckResult, iTsense.Moving.Backend.Services.DmcoreService]], iTsense.Moving.Backend.DataHandling",
            "ElementType": "iTsense.Moving.Backend.Services.DmcoreService.DataInterfaces.Servicedmcore.IQualityOfRegistrationCheckResult, iTsense.Moving.Backend.Services.DmcoreService"
          },
          "Mode": 15
        },
        {
          "$type": "iTsense.Moving.Backend.DataHandling.Security.Role.SecurityRight, iTsense.Moving.Backend.DataHandling",
          "Filter": {
            "$type": "iTsense.Moving.Backend.DataHandling.Security.Filter.GenericMyCoreIdentityFilter`1[[iTsense.Moving.Backend.Services.DmcoreService.DataInterfaces.Servicedmcore.IQualityOfRegistrationCheckResult, iTsense.Moving.Backend.Services.DmcoreService]], iTsense.Moving.Backend.DataHandling",
            "NotContains": false,
            "PropertyChain": {
              "$type": "System.String[], mscorlib",
              "$values": [
                "CoreIdentity",
                "Id"
              ]
            }
          },
          "Mode": 1,
          "ContextBundles": {
            "$type": "iTsense.Moving.Backend.DataHandling.Security.Role.ISecurityContextBundle[], iTsense.Moving.Backend.DataHandling",
            "$values": []
          }
        }
      ]
    }'
    	WHERE `assembly_qualified_name` = 'iTsense.Moving.Backend.Services.DmcoreService.DataInterfaces.Servicedmcore.IQualityOfRegistrationCheckResult, iTsense.Moving.Backend.Services.DmcoreService';


    # access to personal data ui
    INSERT INTO security_role__security_rule_group (security_role_id,security_rule_group_id,security_rule_group_module_guid) VALUES
    	('73204777-4330-4BD1-8BC7-58F39B18D423',9,'12DE95DF-0468-4360-9DBC-1256B0C57E58'),
    	('73204777-4330-4BD1-8BC7-58F39B18D423',19,'12DE95DF-0468-4360-9DBC-1256B0C57E58');



    # access to api for administrator only
    INSERT INTO security_rule_group (id,name_key,module_guid) VALUES
    	 (245,'Security.RuleGroup.Api.User.GetUserRightsLog','2705E0F0-13AD-49DF-8DFF-D3370B3FD211');

  END IF;
END$$

delimiter ;

CALL AddPersonalSecurityRole;
DROP PROCEDURE IF EXISTS AddPersonalSecurityRole;