SELECT * FROM ServiceIdentity where clientId='io.netfoundry.core';
delete FROM flyway_schema_history where installed_rank=36;
SELECT * FROM ServiceIdentity where hex(id)='0ab1b47da5de4f39a3bd7b130cacf501';

SELECT id FROM ResourceType WHERE code = 'create-network-host-ping'


SELECT * FROM ResourceAction where code like '%network-controller%';
SELECT * FROM  ActionInclusion where resourceActionId in (SELECT id FROM ResourceAction where code like '%network-controller%');
//edge-router
SELECT * FROM ResourceAction where code like '%edge-router%';
//network-upgrade

SELECT * FROM ResourceAction where code like '%network-upgrade%';

//network-crontab
SELECT * FROM ResourceAction where code like '%network-crontab%';
//network-backup
SELECT * FROM ResourceAction where code like '%network-backup%';
//network-host
SELECT * FROM ResourceAction where code like '%network-host%';

Select * FROM IdentityResourceAction where resourceActionId in (
SELECT id FROM ResourceAction where resourceTypeId in ( SELECT id FROM ResourceType  where code='deployment-network-controller'));

SELECT * FROM ResourceAction where code='install'

SELECT * FROM ResourceAction where DATE(createdAt) = CURDATE()-1;
SELECT * FROM ResourceType where DATE(createdAt) = CURDATE()-1;
SELECT * FROM ActionInclusion where DATE(createdAt) = CURDATE()-2;
SELECT * FROM CustomRoleAction where DATE(createdAt) = CURDATE()-2;
SELECT hex(i.identityId),hex(i.id),i.* FROM IdentityResourceAction i where DATE(createdAt) = CURDATE();




DELETE FROM ResourceType where DATE(createdAt) = CURDATE()-2;
DELETE FROM ResourceAction where DATE(createdAt) = CURDATE()-2;
DELETE FROM IdentityResourceAction i where DATE(createdAt) = CURDATE()-2;
DELETE FROM CustomRoleAction where DATE(createdAt) = CURDATE()-2;
DELETE FROM ActionInclusion where DATE(createdAt) = CURDATE()-2;
DELETE  FROM flyway_schema_history where DATE(installed_on) = CURDATE()-2;

SELECT * FROM flyway_schema_history where DATE(installed_on) = CURDATE()-1;

SELECT a.id FROM ResourceAction a where DATE(createdAt) = CURDATE()-1;
SELECT hex(i.identityId),hex(i.id),i.* FROM IdentityResourceAction i where resourceActionId in (SELECT a.id FROM ResourceAction a where DATE(createdAt) = CURDATE()-1);


SELECT hex(h.id),h.* FROM Host h where ipAddress='129.213.94.186';
SELECT hex(c.id),c.* FROM NetworkController c where hex(hostId) in (SELECT hex(h.id) FROM Host h where ipAddress='129.213.94.186');
;
SELECT * FROM NetworkVersion;

UPDATE  NetworkVersion set networkVersion='7.3.85' where active=true;

INSERT INTO `CoreManagement`.`NetworkVersion` (`id`, `networkVersion`, `zitiVersion`, `recommended`, `minimumNetworkVersionForUpgrade`, `minimumEdgeRouterVersionForUpgrade`, `active`) 
VALUES ( UNHEX('14b42c56d3ab11edafa10242ac120002'),'7.3.89', '0.27.5', 0, '7.3.14', '7.3.14', 1)

SELECT * FROM Attribute where attribute COLLATE utf8mb4_bin  in  ('#ABC-attr');

SELECT * FROM Attribute where attribute COLLATE utf8mb4_bin  in  ('#abc-attr');


select hex(n.networkGroupid),hex(id) from Network n where n.id in (
SELECT a.networkId FROM Attribute  a where attribute LIKE '#abc-attr');




WITH resultIds AS (SELECT id as "RESULT ID", ROW_NUMBER() over (ORDER BY id ASC  ) as "RESULT NUMBER"
  FROM Attribute WHERE ( ( hex(networkId) IN ('2CF8308E7BEE4F68BCCF5AE188CEA568') AND parentType IN ( :null#2 ) 
  AND attributeType IN ( :null#3 ) AND attribute LIKE :param#4 AND deletedAt = :deletedAt#5 )
   AND ( parentType = :parentType#6 AND parentId IN ( SELECT id 
    FROM PlatformService WHERE networkId IN ( SELECT id  FROM Network WHERE networkGroupId IN ( :null#7 ) ) ) ) )), meta AS (SELECT count(*) as "TOTAL COUNT"  FROM resultIds)  SELECT meta.`TOTAL COUNT` as "TOTAL COUNT", resultIds.`RESULT NUMBER` as "RESULT NUMBER", createdAt, deletedAt, attributeType, 
networkId, id, attribute, parentId, parentType, deletedBy, updatedAt 
 FROM meta CROSS JOIN resultIds LEFT JOIN Attribute ON resultIds.`RESULT ID` = id ORDER BY `RESULT NUMBER` ASC   LIMIT 30

WITH resultIds AS (SELECT id as "RESULT ID", ROW_NUMBER() over (ORDER BY id ASC  ) as "RESULT NUMBER"
  FROM Attribute WHERE ( ( hex(networkId) IN ('2CF8308E7BEE4F68BCCF5AE188CEA568') AND parentType IN ('SERVICE' ) 
  AND attributeType IN ( 'SERVICE' ) AND attribute COLLATE utf8mb4_bin LIKE '%#ABC-attr%' AND deletedAt = 0 )
   AND ( parentType = 'SERVICE' AND parentId IN ( SELECT id 
    FROM PlatformService WHERE networkId IN ( SELECT id  FROM Network WHERE hex(networkGroupId)IN ( '3799275463164894BDC6E8FE2F68D14E' ) ) ) ) )), meta AS (SELECT count(*) as "TOTAL COUNT"  FROM resultIds)  SELECT meta.`TOTAL COUNT` as "TOTAL COUNT", resultIds.`RESULT NUMBER` as "RESULT NUMBER", createdAt, deletedAt, attributeType, 
networkId, id, attribute, parentId, parentType, deletedBy, updatedAt 
 FROM meta CROSS JOIN resultIds LEFT JOIN Attribute ON resultIds.`RESULT ID` = id ORDER BY `RESULT NUMBER` ASC   LIMIT 30




SELECT hex(id) FROM Tenant where name='sds02';




select hex(h.id),h.* from Host h where h.ipAddress='129.213.94.186';
select hex(h.id),h.* from Host h where hex(h.networkId)='2C75CA5073AA4D58AF9854ED1E122701'; 

SELECT hex(id),n.* FROM NetworkController n order by n.createdAt desc;
SELECT hex(id),n.* FROM NetworkController n where hex(n.networkId)='2C75CA5073AA4D58AF9854ED1E122701'; 
SELECT hex(id) FROM EdgeRouter where name like '%OCI us-ashburn-1 Edge Router 1%' order by createdAt desc;
SELECT hex(n.id),n.* FROM Network n order by n.createdAt desc;
SELECT hex(n.id),n.* FROM NetworkGroup n order by n.createdAt desc;
SELECT hex(n.id),n.* FROM Network n where hex(n.networkGroupId)='b23b275c062a4e5184b8fb55dcf16eaf';

UPDATE  Network n  set deletedAt=1685346622898 where hex(n.networkGroupId)='b23b275c062a4e5184b8fb55dcf16eaf' and  n.name ='SDS04-auth-test';

SELECT * FROM NetworkVersion;