"""Cypher queries for the synchronization.""" # ruff: noqa subaccount_insert_update_with_uuid_cypher = """ MERGE (sa:SubAccount:Subaccount:Label { id: toInteger($subaccount_id) }) ON CREATE SET sa.name = TRIM($subaccount_name), sa.isDeleted = $date_deleted IS NOT NULL, sa.createdAt = datetime(), sa.createdBy = 'lambda-kinesis-to-neo4j/subaccount-insert-update-cypher', sa.lastModifiedAt = datetime(), sa.lastModifiedBy = 'lambda-kinesis-to-neo4j/subaccount-insert-update-cypher', sa.uuid = $subaccount_uuid ON MATCH SET sa.uuid = COALESCE($subaccount_uuid, sa.uuid), sa.name = TRIM($subaccount_name), sa.isDeleted = $date_deleted IS NOT NULL, sa.lastModifiedAt = datetime(), sa.lastModifiedBy = 'lambda-kinesis-to-neo4j/subaccount-insert-update-cypher' WITH sa MERGE (v:Vendor:Label:Orchard {id: toInteger($vendor_id) }) ON CREATE SET v.createdAt = datetime(), v.createdBy = 'lambda-kinesis-to-neo4j/subaccount-insert-update-cypher', v.lastModifiedAt = datetime(), v.lastModifiedBy = 'lambda-kinesis-to-neo4j/subaccount-insert-update-cypher', v.uuid = '' MERGE (v)-[o:OWNS]->(sa) ON CREATE SET o.createdAt = datetime(), o.createdBy = 'lambda-kinesis-to-neo4j/subaccount-insert-update-cypher', o.lastModifiedAt = datetime(), o.lastModifiedBy = 'lambda-kinesis-to-neo4j/subaccount-insert-update-cypher' WITH sa MATCH p=()-[]->(sa {isDeleted: true}) WITH relationships(p) as list UNWIND list as relation WITH relation WHERE TYPE(relation) IN ["HAS_FOLLOWED","HAS_FOLLOWED_EMAIL_NOTIFICATION_NEW_RELEASE","HAS_FOLLOWED_LABEL_ANALYTICS_DIGEST","HAS_FOLLOWED_SPIKE_DETECTOR","HAS_FOLLOWED_VIDEO_PRODUCT_APPROVAL","HAS_FOLLOWED_VIDEO_PRODUCT_REJECTION"] CALL apoc.refactor.setType(relation, apoc.text.join(['DELETED_', apoc.rel.type(relation)], '')) YIELD input, output RETURN relation """ vendor_insert_update_cypher = """ MERGE (v:Vendor { id: toInteger($vendor_id) }) ON CREATE SET v.uuid = $vendor_uuid, v.name = TRIM(coalesce($company, $name)), v.isDistributor = $is_distributor, v.status = $status, v.vendorId = toInteger($vendor_id), v.labelIdentifier = $label_identifier, v.createdAt = datetime(), v.createdBy = 'lambda-kinesis-to-neo4j/vendor-insert-update-cypher', v.lastModifiedAt = datetime(), v.lastModifiedBy = 'lambda-kinesis-to-neo4j/vendor-insert-update-cypher', v:Label:Orchard ON MATCH SET v.uuid = COALESCE($vendor_uuid, v.uuid), v.name = TRIM(coalesce($company, $name)), v.isDistributor = $is_distributor, v.status = $status, v.vendorId = toInteger($vendor_id), v.labelIdentifier = $label_identifier, v.lastModifiedAt = datetime(), v.lastModifiedBy = 'lambda-kinesis-to-neo4j/vendor-insert-update-cypher', v:Label:Orchard WITH v OPTIONAL MATCH (p:Profile { profileId: toInteger($assigned_to), profileType: 'OrchAdminProfile' }) WITH v, p FOREACH (_p IN CASE WHEN NOT p IS NULL THEN [1] ELSE [] END | MERGE (p)<-[at:ASSIGNED_TO]-(v) ON CREATE SET at.createdAt = datetime(), at.createdBy = 'lambda-kinesis-to-neo4j/vendor-insert-update-cypher', at.lastModifiedAt = datetime(), at.lastModifiedBy = 'lambda-kinesis-to-neo4j/vendor-insert-update-cypher' ) WITH v OPTIONAL MATCH (check_p:Profile {profileType: 'OrchAdminProfile'})<-[check_at:ASSIGNED_TO]-(v) WHERE NOT check_p.profileId = toInteger($assigned_to) WITH check_p, check_at, v FOREACH (_check_p IN CASE WHEN NOT check_p IS NULL THEN [1] ELSE [] END | MERGE (check_p)<-[d_at:DELETED_ASSIGNED_TO]-(v) ON CREATE SET d_at.createdAt = datetime(), d_at.createdBy = 'lambda-kinesis-to-neo4j/vendor-insert-update-cypher', d_at.lastModifiedAt = datetime(), d_at.lastModifiedBy = 'lambda-kinesis-to-neo4j/vendor-insert-update-cypher' DELETE check_at ) WITH v OPTIONAL MATCH (existingCompany:CompanyBrand)-[existingRel:HAS_LABEL]->(v) CALL apoc.do.when( existingCompany is not null and existingCompany.companyBrandId is not null and existingCompany.companyBrandId <> $company_brand_id, 'MATCH (updatedCompany:CompanyBrand {companyBrandId: company_brand_id}) OPTIONAL MATCH (updatedCompany)-[delRel:DELETED_HAS_LABEL]->(v) DELETE delRel WITH v, updatedCompany, existingRel MERGE (updatedCompany)-[newRel:HAS_LABEL]->(v) ON CREATE SET newRel.createdAt = datetime() SET newRel.lastModifiedAt = datetime() WITH v, existingRel CALL apoc.refactor.setType(existingRel, "DELETED_HAS_LABEL") YIELD input, output RETURN v', 'MATCH (c:CompanyBrand {companyBrandId: company_brand_id}) MERGE (c)-[rel:HAS_LABEL]->(v) ON CREATE SET rel.createdAt = datetime() SET rel.lastModifiedAt = datetime() RETURN v', {v:v, existingCompany:existingCompany, existingRel:existingRel, company_brand_id:$company_brand_id}) YIELD value RETURN value.v as vendor """ def get_labelprofile_insert_cypher(): labelprofile_insert_cypher = """ MERGE (p:Profile {{ profileId: toInteger($id), profileType: 'LabelProfile' }}) ON CREATE SET p.uuid = randomUUID(), p.fullCatalogAccess = false SET p.profileName = $login, p.language = $language, p.roles = $roles, p.updatedOn = datetime(), p.updatedBy = 'kinesis-to-neo4j/labelprofile_insert_cypher' WITH p MERGE (a:{resource_type} {{ id: toInteger($resource_id) }}) ON CREATE SET a.uuid = $uuid, a.createdAt = datetime(), a.createdBy = 'kinesis-to-neo4j/labelprofile_insert_cypher', a.lastModifiedAt = datetime(), a.lastModifiedBy = 'kinesis-to-neo4j/labelprofile_insert_cypher' MERGE (p)-[rel:HAS_ACCESS_TO]->(a) MERGE (p)-[fol:HAS_FOLLOWED]->(a) SET fol.dateCreated = datetime(), fol.automatic = TRUE MERGE (p)-[fol_enr:HAS_FOLLOWED_EMAIL_NOTIFICATION_REJECTION]->(a) SET fol_enr.dateCreated = datetime(), fol_enr.automatic = TRUE MERGE (p)-[fol_ennr:HAS_FOLLOWED_EMAIL_NOTIFICATION_NEW_RELEASE]->(a) SET fol_ennr.dateCreated = datetime(), fol_ennr.automatic = TRUE WITH p MATCH (s:Subscription) WHERE s.name in ['email_notification_rejection', 'email_notification_new_release'] MERGE (p)-[newrel:HAS_SUBSCRIPTION]->(s) WITH p, s MATCH(i:Identity {{ auth0UserId: $auth0_user_id }}) MERGE (i)-[hp:HAS_PROFILE]->(p) ON CREATE SET hp.createdAt = datetime(), hp.createdBy = 'kinesis-to-neo4j/get_labelprofile_insert_cypher', hp.lastModifiedAt = datetime(), hp.lastModifiedBy = 'kinesis-to-neo4j/get_labelprofile_insert_cypher' RETURN i, p, s """ return labelprofile_insert_cypher labelprofile_no_identity_insert_cypher = """ MERGE (p:Profile {{ profileId: toInteger($id), profileType: 'LabelProfile' }}) ON CREATE SET p.uuid = randomUUID(), p.fullCatalogAccess = false SET p.profileName = TRIM(coalesce($login, $profile_name)), p.language = $language, p.roles = $roles, p.updatedOn = datetime(), p.updatedBy = 'kinesis-to-neo4j/labelprofile_no_identity_insert_cypher' WITH p MERGE (a:{resource_type} {{ id: toInteger($resource_id) }}) ON CREATE SET a.uuid = $uuid, a.createdAt = datetime(), a.createdBy = 'kinesis-to-neo4j/labelprofile_no_identity_insert_cypher', a.lastModifiedAt = datetime(), a.lastModifiedBy = 'kinesis-to-neo4j/labelprofile_no_identity_insert_cypher' MERGE (p)-[rel:HAS_ACCESS_TO]->(a) MERGE (p)-[fol:HAS_FOLLOWED]->(a) SET fol.dateCreated = datetime(), fol.automatic = TRUE RETURN p """ def get_profile_update_cypher(relation_name, resource_type): exclude_has_followed = '' if relation_name == 'HAS_ACCESS_TO': exclude_has_followed = "\n WHERE NOT type(old) STARTS WITH 'HAS_FOLLOWED'" profile_update_cypher = ( f""" MERGE (p:Profile {{ profileId: toInteger($id), profileType: 'LabelProfile' }}) ON CREATE SET p.uuid = randomUUID() SET p.profileName = $login, p.language = $language, p.roles = $roles WITH p MATCH (a:{resource_type} {{ id: toInteger($resource_id) }}) WITH p, a OPTIONAL MATCH (p)-[old]->(a) """ + exclude_has_followed + f""" DETACH DELETE old WITH p, a MERGE (p)-[rel:{relation_name}]->(a) SET rel.updatedOn = datetime(), rel.updatedBy = 'kinesis-to-neo4j/labelprofile_update_cypher' RETURN p.profileId """ ) return profile_update_cypher link_labelprofile_identity_cypher = """ MATCH (p:Profile { profileId: toInteger($id), profileType: 'LabelProfile' }), (i:Identity { auth0UserId: $auth0_user_id }) MERGE (i)-[rel:HAS_PROFILE]->(p) ON CREATE SET rel.createdAt = datetime(), rel.createdBy = 'kinesis-to-neo4j/link_labelprofile_identity_cypher', rel.lastModifiedAt = datetime(), rel.lastModifiedBy = 'kinesis-to-neo4j/link_labelprofile_identity_cypher' RETURN i, p """ labelprofile_no_identity_update_cypher = """ MATCH (p:Profile { profileId: toInteger($id), profileType: 'LabelProfile' }) OPTIONAL MATCH (i2:Identity)-[old]->(p) DETACH DELETE old RETURN p """ labelprofile_replace_roles_cypher = """ MATCH (p:Profile { profileId: toInteger($vend_contact_id), profileType: 'LabelProfile' }) SET p.roles = $roles """ create_insightsprofile_if_not_exists_cypher = """ MATCH (pl:Profile { profileId: toInteger($vend_contact_id), profileType: 'LabelProfile' }) <-[:HAS_PROFILE]-(i:Identity) WITH i MATCH (inc:IncrementId {nodeName: 'Profile'}) OPTIONAL MATCH (i)-[r:HAS_PROFILE]->(pi:Profile { profileType: 'InsightsProfile' }) CALL apoc.do.when( pi is null, \"CREATE (pNew:Profile { profileId: inc.id, profileType: 'InsightsProfile' }) SET pNew.profileName = coalesce(i.name, i.email), inc.id = inc.id+1, pNew.roles = ['analytics'], pNew.updatedOn = datetime(), pNew.updatedBy = 'kinesis-to-neo4j', pNew.uuid = randomUUID() MERGE (i)-[relNew:HAS_PROFILE]->(pNew) ON CREATE SET relNew.createdAt = datetime(), relNew.createdBy = 'kinesis-to-neo4j/create_insightsprofile_if_not_exists_cypher', relNew.lastModifiedAt = datetime(), relNew.lastModifiedBy = 'kinesis-to-neo4j/create_insightsprofile_if_not_exists_cypher' RETURN pNew as p\", \"RETURN pi as p\", {pi:pi, i:i, inc:inc, roles:$roles} ) YIELD value RETURN value.p as p """ insightsprofile_add_access_cypher = """ MATCH (pl:Profile {{ profileId: toInteger($vend_contact_id), profileType: 'LabelProfile' }}) <-[:HAS_PROFILE]-(i:Identity) WITH i MATCH (i)-[:HAS_PROFILE]->(pi:Profile {{ profileType: 'InsightsProfile' }}) SET pi.roles = ['analytics'] MERGE (a:{resource_type} {{ id: toInteger($resource_id) }}) ON CREATE SET a.uuid = $uuid, a.createdAt = datetime(), a.createdBy = 'kinesis-to-neo4j/insightsprofile_add_access_cypher', a.lastModifiedAt = datetime(), a.lastModifiedBy = 'kinesis-to-neo4j/insightsprofile_add_access_cypher' MERGE (pi)-[rel:HAS_ACCESS_TO]->(a) WITH a, pi OPTIONAL MATCH (pi)-[old:DELETED_HAS_ACCESS_TO]->(a) DETACH DELETE old """ insightsprofile_delete_access_cypher = """ MATCH (pl:Profile {{ profileId: toInteger($vend_contact_id), profileType: 'LabelProfile' }}) <-[]-(i:Identity) WITH i MATCH (i)-[:HAS_PROFILE]->(pi:Profile {{ profileType: 'InsightsProfile' }}) -[rel:HAS_ACCESS_TO]->(a:{resource_type} {{ id: toInteger($resource_id) }}) CALL apoc.refactor.setType(rel, 'DELETED_HAS_ACCESS_TO') YIELD input, output RETURN input, output """ settingsprofile_add_access_cypher = """ MATCH (pl:Profile {{ profileId: toInteger($vend_contact_id), profileType: 'LabelProfile' }}) <-[:HAS_PROFILE]-(i:Identity) WHERE pl.brand <> 'awal' WITH i MATCH (i)-[:HAS_PROFILE]->(ps:Profile {{ profileType: 'SettingsProfile' }}) MERGE (a:{resource_type} {{ id: toInteger($resource_id) }}) ON CREATE SET a.uuid = $uuid, a.createdAt = datetime(), a.createdBy = 'kinesis-to-neo4j/settingsprofile_add_access_cypher', a.lastModifiedAt = datetime(), a.lastModifiedBy = 'kinesis-to-neo4j/settingsprofile_add_access_cypher' MERGE (ps)-[rel:HAS_ADMIN_ACCESS_TO]->(a) SET rel.updatedOn = datetime(), rel.lastUpdatedBy = 'lambda-kinesis-to-neo4j/settingsprofile_add_access_cypher' WITH a, ps OPTIONAL MATCH (ps)-[old:DELETED_HAS_ADMIN_ACCESS_TO]->(a) DETACH DELETE old """ settingsprofile_delete_access_cypher = """ MATCH (pl:Profile {{ profileId: toInteger($vend_contact_id), profileType: 'LabelProfile' }}) <-[]-(i:Identity) WHERE pl.brand <> 'awal' WITH i MATCH (i)-[:HAS_PROFILE]->(pi:Profile {{ profileType: 'SettingsProfile' }}) -[rel:HAS_ADMIN_ACCESS_TO]->(a:{resource_type} {{ id: toInteger($resource_id) }}) SET rel.updatedOn = datetime(), rel.lastUpdatedBy = 'lambda-kinesis-to-neo4j/settingsprofile_delete_access_cypher' WITH rel CALL apoc.refactor.setType(rel, 'DELETED_HAS_ADMIN_ACCESS_TO') YIELD input, output RETURN input, output """ artist_info_insert_cypher = """ MERGE (ai:ArtistInfo {id: toInteger($artist_id)}) ON CREATE SET ai.name = $name, ai.vendorId = toInteger($vendor_id), ai.createdAt = datetime(), ai.createdBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher', ai.lastModifiedAt = datetime(), ai.lastModifiedBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher' ON MATCH SET ai.name = $name, ai.vendorId = toInteger($vendor_id), ai.lastModifiedAt = datetime(), ai.lastModifiedBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher' WITH ai OPTIONAL MATCH (:LabelParticipant)-[oldRel:CREATED_FROM]->(ai) DELETE oldRel WITH DISTINCT ai MERGE (v:Vendor:Label:Orchard {id: ai.vendorId}) ON CREATE SET v.createdAt = datetime(), v.uuid = '', v.createdBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher', v.lastModifiedAt = datetime(), v.lastModifiedBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher' MERGE (v)-[ha:HAS_ARTIST]->(ai) ON CREATE SET ha.createdAt = datetime(), ha.createdBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher', ha.lastModifiedAt = datetime(), ha.lastModifiedBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher' FOREACH (notDistr IN CASE WHEN NOT $is_distributor THEN [1] ELSE [] END | MERGE (lp:LabelParticipant:Orchard { normalizedName: toLower(trim(apoc.text.replace(ai.name, '[ ]+', ''))), vendorId: toInteger(ai.vendorId), subaccountId: 0 }) ON CREATE SET lp.id = toInteger(rand() * datetime.realtime().epochMillis), lp.uuid = randomUUID(), lp.createdAt = datetime(), lp.createdBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher', lp.lastModifiedAt = datetime(), lp.lastModifiedBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher', lp.name = trim(apoc.text.replace(ai.name, '[ ]{2,}', ' ')) ON MATCH SET lp.lastModifiedAt = datetime(), lp.lastModifiedBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher' MERGE (lp)-[cf:CREATED_FROM]->(ai) ON CREATE SET cf.createdAt = datetime(), cf.createdBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher', cf.lastModifiedAt = datetime(), cf.lastModifiedBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher' MERGE (v)-[hlp:HAS_LABEL_PARTICIPANT]->(lp) ON CREATE SET hlp.createdAt = datetime(), hlp.createdBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher', hlp.lastModifiedAt = datetime(), hlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher' ) WITH ai, v MATCH (ai)<-[:HAS_ARTIST]-(pr:Project) WHERE pr.vendorId IS NOT NULL AND pr.subaccountId IS NOT NULL OPTIONAL MATCH (pr)-[rel:CREATED_FOR_PARTICIPANT]->(:LabelParticipant) DELETE rel WITH DISTINCT ai, pr, v CALL apoc.util.sleep(1) MERGE (lp:LabelParticipant:Orchard { vendorId: pr.vendorId, subaccountId: pr.subaccountId, normalizedName: toLower(trim(apoc.text.replace(ai.name, '[ ]+', ''))) }) ON CREATE SET lp.id = toInteger(rand() * datetime.realtime().epochMillis), lp.uuid = randomUUID(), lp.createdAt = datetime(), lp.createdBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher', lp.lastModifiedAt = datetime(), lp.lastModifiedBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher', lp.name = trim(apoc.text.replace(ai.name, '[ ]{2,}', ' ')) MERGE (pr)-[cfp:CREATED_FOR_PARTICIPANT]->(lp) ON CREATE SET cfp.createdAt = datetime(), cfp.createdBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher', cfp.lastModifiedAt = datetime(), cfp.lastModifiedBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher' MERGE (lp)-[cf:CREATED_FROM]->(ai) ON CREATE SET cf.createdAt = datetime(), cf.createdBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher', cf.lastModifiedAt = datetime(), cf.lastModifiedBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher' MERGE (v)-[hlp:HAS_LABEL_PARTICIPANT]->(lp) ON CREATE SET hlp.createdAt = datetime(), hlp.createdBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher', hlp.lastModifiedAt = datetime(), hlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher' WITH lp WHERE lp.subaccountId <> 0 MERGE (sa:SubAccount:Subaccount:Label { id: lp.subaccountId }) ON CREATE SET sa.createdAt = datetime(), sa.uuid = '', sa.createdBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher', sa.lastModifiedAt = datetime(), sa.lastModifiedBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher' MERGE (sa)-[hlp:HAS_LABEL_PARTICIPANT]->(lp) ON CREATE SET hlp.createdAt = datetime(), hlp.createdBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher', hlp.lastModifiedAt = datetime(), hlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/artist-info-insert-cypher' RETURN lp """ artist_info_delete_cypher = """ MATCH (ai:ArtistInfo {id: toInteger($artist_id)}) DETACH DELETE ai """ project_insert_cypher = """ MERGE (pr:Project {id: toInteger($project_id)}) ON CREATE SET pr.vendorId = toInteger($vendor_id), pr.subaccountId = toInteger($subaccount_id), pr.artistId = toInteger($artist_id), pr.deletions = ($deletions = 'Y'), pr.createdAt = datetime(), pr.createdBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', pr.lastModifiedAt = datetime(), pr.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher' ON MATCH SET pr.vendorId = toInteger($vendor_id), pr.subaccountId = toInteger($subaccount_id), pr.artistId = toInteger($artist_id), pr.deletions = ($deletions = 'Y'), pr.lastModifiedAt = datetime(), pr.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher' MERGE (v:Vendor:Label:Orchard {id: pr.vendorId}) ON CREATE SET v.createdAt = datetime(), v.uuid = '', v.createdBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', v.lastModifiedAt = datetime(), v.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher' MERGE (pr)-[btv:BELONGS_TO]->(v) ON CREATE SET btv.createdAt = datetime(), btv.createdBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', btv.lastModifiedAt = datetime(), btv.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher' FOREACH (isSubAccount IN CASE WHEN pr.subaccountId <> 0 THEN [1] ELSE [] END | MERGE (sa:SubAccount:Subaccount:Label {id: pr.subaccountId}) ON CREATE SET sa.createdAt = datetime(), sa.uuid = '', sa.createdBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', sa.lastModifiedAt = datetime(), sa.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher' MERGE (pr)-[bts:BELONGS_TO]->(sa) ON CREATE SET bts.createdAt = datetime(), bts.createdBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', bts.lastModifiedAt = datetime(), bts.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher' MERGE (v)-[os:OWNS]->(sa) ON CREATE SET os.createdAt = datetime(), os.createdBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', os.lastModifiedAt = datetime(), os.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher' ) FOREACH (isArtistInfo IN CASE WHEN pr.artistId <> 0 THEN [1] ELSE [] END | MERGE (ai:ArtistInfo {id: pr.artistId}) ON CREATE SET ai.createdAt = datetime(), ai.createdBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', ai.lastModifiedAt = datetime(), ai.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher' MERGE (pr)-[ha:HAS_ARTIST]->(ai) ON CREATE SET ha.createdAt = datetime(), ha.createdBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', ha.lastModifiedAt = datetime(), ha.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher' ) WITH pr, v CALL apoc.util.sleep(toInteger(rand() * 40) + 10) MATCH (pr)-[:HAS_ARTIST]->(ai) WHERE ai.name IS NOT NULL MERGE (lp:LabelParticipant:Orchard { vendorId: pr.vendorId, subaccountId: pr.subaccountId, normalizedName: toLower(trim(apoc.text.replace(ai.name, '[ ]+', ''))) }) ON CREATE SET lp.id = toInteger(rand() * datetime.realtime().epochMillis), lp.uuid = randomUUID(), lp.createdAt = datetime(), lp.createdBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', lp.lastModifiedAt = datetime(), lp.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', lp.name = trim(apoc.text.replace(ai.name, '[ ]{2,}', ' ')) MERGE (lp)-[cf:CREATED_FROM]->(ai) ON CREATE SET cf.createdAt = datetime(), cf.createdBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', cf.lastModifiedAt = datetime(), cf.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher' MERGE (pr)-[cfp:CREATED_FOR_PARTICIPANT]->(lp) ON CREATE SET cfp.createdAt = datetime(), cfp.createdBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', cfp.lastModifiedAt = datetime(), cfp.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher' MERGE (v)-[hlp:HAS_LABEL_PARTICIPANT]->(lp) ON CREATE SET hlp.createdAt = datetime(), hlp.createdBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', hlp.lastModifiedAt = datetime(), hlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher' FOREACH (hasSubAccount IN CASE WHEN NOT (lp.subaccountId = 0) THEN [1] ELSE [] END | MERGE (sa:SubAccount:Subaccount:Label {id: lp.subaccountId}) ON CREATE SET sa.createdAt = datetime(), sa.uuid = '', sa.createdBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', sa.lastModifiedAt = datetime(), sa.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher' MERGE (sa)-[shlp:HAS_LABEL_PARTICIPANT]->(lp) ON CREATE SET shlp.createdAt = datetime(), shlp.createdBy = 'lambda-kinesis-to-neo4j/project-insert-cypher', shlp.lastModifiedAt = datetime(), shlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-insert-cypher' ) RETURN lp """ project_update_cypher = """ MERGE (pr:Project {id: toInteger($project_id)}) ON CREATE SET pr.vendorId = toInteger($vendor_id), pr.subaccountId = toInteger($subaccount_id), pr.artistId = toInteger($artist_id), pr.deletions = ($deletions = 'Y'), pr.createdAt = datetime(), pr.createdBy = 'lambda-kinesis-to-neo4j/project-update-cypher', pr.lastModifiedAt = datetime(), pr.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher' ON MATCH SET pr.vendorId = toInteger($vendor_id), pr.subaccountId = toInteger($subaccount_id), pr.artistId = toInteger($artist_id), pr.deletions = ($deletions = 'Y'), pr.lastModifiedAt = datetime(), pr.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher' MERGE (v:Vendor:Label:Orchard {id: pr.vendorId}) ON CREATE SET v.createdAt = datetime(), v.uuid = '', v.createdBy = 'lambda-kinesis-to-neo4j/project-update-cypher', v.lastModifiedAt = datetime(), v.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher' MERGE (pr)-[bt:BELONGS_TO]->(v) ON CREATE SET bt.createdAt = datetime(), bt.createdBy = 'lambda-kinesis-to-neo4j/project-update-cypher', bt.lastModifiedAt = datetime(), bt.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher' WITH DISTINCT pr, v OPTIONAL MATCH (pr)-[incorrect_ownership:BELONGS_TO]->(sa:Subaccount:Label) WHERE sa.id <> pr.subaccountId DELETE incorrect_ownership WITH DISTINCT pr, v FOREACH (isSubAccount IN CASE WHEN pr.subaccountId <> 0 THEN [1] ELSE [] END | MERGE (sa:SubAccount:Subaccount:Label {id: pr.subaccountId}) ON CREATE SET sa.createdAt = datetime(), sa.uuid = '', sa.createdBy = 'lambda-kinesis-to-neo4j/project-update-cypher', sa.lastModifiedAt = datetime(), sa.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher' MERGE (pr)-[bts:BELONGS_TO]-(sa) ON CREATE SET bts.createdAt = datetime(), bts.createdBy = 'lambda-kinesis-to-neo4j/project-update-cypher', bts.lastModifiedAt = datetime(), bts.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher' MERGE (v)-[o:OWNS]->(sa) ON CREATE SET o.createdAt = datetime(), o.createdBy = 'lambda-kinesis-to-neo4j/project-update-cypher', o.lastModifiedAt = datetime(), o.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher' ) FOREACH (isArtistInfo IN CASE WHEN pr.artistId <> 0 THEN [1] ELSE [] END | MERGE (ai:ArtistInfo {id: pr.artistId}) ON CREATE SET ai.createdAt = datetime(), ai.createdBy = 'lambda-kinesis-to-neo4j/project-update-cypher', ai.lastModifiedAt = datetime(), ai.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher' MERGE (pr)-[ha:HAS_ARTIST]->(ai) ON CREATE SET ha.createdAt = datetime(), ha.createdBy = 'lambda-kinesis-to-neo4j/project-update-cypher', ha.lastModifiedAt = datetime(), ha.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher' ) WITH pr, v, toInteger($artist_id) AS artistId MATCH (:LabelParticipant)<-[cfp:CREATED_FOR_PARTICIPANT]-(pr)-[ha:HAS_ARTIST]->(oldAI:ArtistInfo) WHERE oldAI.id <> artistId DELETE ha, cfp WITH pr, v CALL apoc.util.sleep(1) MATCH (pr)-[:HAS_ARTIST]->(ai) WHERE ai.name IS NOT NULL MERGE (lp:LabelParticipant:Orchard { vendorId: pr.vendorId, subaccountId: pr.subaccountId, normalizedName: toLower(trim(apoc.text.replace(ai.name, '[ ]+', ''))) }) ON CREATE SET lp.id = toInteger(rand() * datetime.realtime().epochMillis), lp.uuid = randomUUID(), lp.createdAt = datetime(), lp.createdBy = 'lambda-kinesis-to-neo4j/project-update-cypher', lp.lastModifiedAt = datetime(), lp.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher', lp.name = trim(apoc.text.replace(ai.name, '[ ]{2,}', ' ')) MERGE (lp)-[cf:CREATED_FROM]->(ai) ON CREATE SET cf.createdAt = datetime(), cf.createdBy = 'lambda-kinesis-to-neo4j/project-update-cypher', cf.lastModifiedAt = datetime(), cf.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher' MERGE (pr)-[cfp:CREATED_FOR_PARTICIPANT]->(lp) ON CREATE SET cfp.createdAt = datetime(), cfp.createdBy = 'lambda-kinesis-to-neo4j/project-update-cypher', cfp.lastModifiedAt = datetime(), cfp.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher' MERGE (v)-[hlp:HAS_LABEL_PARTICIPANT]->(lp) ON CREATE SET hlp.createdAt = datetime(), hlp.createdBy = 'lambda-kinesis-to-neo4j/project-update-cypher', hlp.lastModifiedAt = datetime(), hlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher' FOREACH (hasSubAccount IN CASE WHEN NOT (lp.subaccountId = 0) THEN [1] ELSE [] END | MERGE (sa:SubAccount:Subaccount:Label {id: lp.subaccountId}) ON CREATE SET sa.createdAt = datetime(), sa.uuid = '', sa.createdBy = 'lambda-kinesis-to-neo4j/project-update-cypher', sa.lastModifiedAt = datetime(), sa.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher' MERGE (sa)-[shlp:HAS_LABEL_PARTICIPANT]->(lp) ON CREATE SET shlp.createdAt = datetime(), shlp.createdBy = 'lambda-kinesis-to-neo4j/project-update-cypher', shlp.lastModifiedAt = datetime(), shlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/project-update-cypher' ) RETURN lp """ project_delete_cypher = """ MATCH (pr:Project {id: toInteger($project_id)}) DETACH DELETE pr """ product_insert_cypher = """ MERGE (p:Product:Orchard {id: toInteger($release_id)}) ON CREATE SET p.createdAt = datetime(), p.createdBy = 'lambda-kinesis-to-neo4j/product-insert-cypher' SET p.projectId = toInteger($project_id), p.upc = toString($upc), p.displayUpc = $display_upc, p.name = $release_name, p.deletions = ($deletions = 'Y'), p.releaseStatus = $release_status, p.releaseDate = date(CASE WHEN $release_date = '0000-00-00' THEN NULL ELSE $release_date END), p.label = CASE WHEN NOT (TRIM($label) = '') THEN TRIM($label) ELSE NULL END, p.notForDistribution = $not_for_distribution, p.contextType = $context_type, p.preorderDate = date(CASE WHEN $preorder_date IN ['0000-00-00', '1900-01-01'] THEN NULL ELSE $preorder_date END), p.saleStartDate = date(CASE WHEN $sale_start_date = '0000-00-00' THEN NULL ELSE $sale_start_date END), p.lastModifiedAt = datetime(), p.lastModifiedBy = 'lambda-kinesis-to-neo4j/product-insert-cypher' MERGE (pr:Project {id: p.projectId}) ON CREATE SET pr.createdAt = datetime(), pr.createdBy = 'lambda-kinesis-to-neo4j/product-insert-cypher', pr.lastModifiedAt = datetime(), pr.lastModifiedBy = 'lambda-kinesis-to-neo4j/product-insert-cypher' MERGE (pr)-[r:INCLUDES]->(p) ON CREATE SET r.createdAt = datetime(), r.createdBy = 'lambda-kinesis-to-neo4j/product-insert-cypher', r.lastModifiedAt = datetime(), r.lastModifiedBy = 'lambda-kinesis-to-neo4j/product-insert-cypher' """ product_move_remove_old_project_cypher = """ MATCH (p:Product {id: toInteger($release_id)})<-[oldR:INCLUDES]-(pr:Project) WHERE pr.id <> toInteger($project_id) DELETE oldR """ product_move_copy_label_participants_cypher = """ MATCH (p:Product {id: toInteger($release_id)})<-[lpToP]-(lp:LabelParticipant) CALL apoc.util.sleep(1) MERGE (nlp:LabelParticipant:Orchard {normalizedName: lp.normalizedName, vendorId: lp.vendorId, subaccountId: toInteger($subaccount_id)}) ON CREATE SET nlp.id = toInteger(rand() * datetime.realtime().epochMillis), nlp.uuid = randomUUID(), nlp.createdAt = datetime(), nlp.createdBy = 'lambda-kinesis-to-neo4j/product-move-copy-label-participants-cypher', nlp.name = lp.name, nlp.spotifyId = lp.spotifyId SET nlp += apoc.map.removeKeys(lp, ['id', 'uuid', 'subaccountId', 'createdAt', 'createdBy', 'spotifyId']), nlp.spotifyId = COALESCE(nlp.spotifyId, lp.spotifyId) SET nlp.lastModifiedAt = datetime(), nlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/product-move-copy-label-participants-cypher' MERGE (v:Vendor:Label:Orchard {id: nlp.vendorId}) ON CREATE SET v.createdAt = datetime(), v.uuid = '', v.createdBy = 'lambda-kinesis-to-neo4j/product-move-copy-label-participants-cypher', v.lastModifiedAt = datetime(), v.lastModifiedBy = 'lambda-kinesis-to-neo4j/product-move-copy-label-participants-cypher' MERGE (v)-[hlp:HAS_LABEL_PARTICIPANT]->(nlp) ON CREATE SET hlp.createdAt = datetime(), hlp.createdBy = 'lambda-kinesis-to-neo4j/product-move-copy-label-participants-cypher', hlp.lastModifiedAt = datetime(), hlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/product-move-copy-label-participants-cypher' FOREACH (hasSubAccount IN CASE WHEN NOT (nlp.subaccountId = 0) THEN [1] ELSE [] END | MERGE (sa:SubAccount:Subaccount:Label {id: nlp.subaccountId}) ON CREATE SET sa.createdAt = datetime(), sa.uuid = '', sa.createdBy = 'lambda-kinesis-to-neo4j/product-move-copy-label-participants-cypher', sa.lastModifiedAt = datetime(), sa.lastModifiedBy = 'lambda-kinesis-to-neo4j/product-move-copy-label-participants-cypher' MERGE (sa)-[shlp:HAS_LABEL_PARTICIPANT]->(nlp) ON CREATE SET shlp.createdAt = datetime(), shlp.createdBy = 'lambda-kinesis-to-neo4j/product-move-copy-label-participants-cypher', shlp.lastModifiedAt = datetime(), shlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/product-move-copy-label-participants-cypher' ) WITH * CALL apoc.merge.relationship( nlp, type(lpToP), {participated_as: lpToP.participated_as}, lpToP, p, {lastModifiedAt: datetime(), lastModifiedBy: 'lambda-kinesis-to-neo4j/product-move-copy-label-participants-cypher'} ) YIELD rel AS rel1 MATCH (lp)-[lpToRa]-(ra:ReleaseArtist {productId: $release_id}) CALL apoc.merge.relationship( nlp, type(lpToRa), {}, lpToRa, ra, {lastModifiedAt: datetime(), lastModifiedBy: 'lambda-kinesis-to-neo4j/product-move-copy-label-participants-cypher'} ) YIELD rel AS rel2 RETURN * """ product_move_remove_old_label_participants = """ MATCH (p:Product {id: toInteger($release_id)})<-[lpToP]-(lp:LabelParticipant) WHERE lp.subaccountId <> toInteger($subaccount_id) MATCH (lp)-[lpToRa]-(ra:ReleaseArtist {productId: toInteger($release_id)}) DELETE lpToP, lpToRa; """ # when this clones LP to new subaccount, if there is an LP with same normalizedName and spotifyId on new subaccount # then don't overwrite spotifyId. product_move_copy_track_label_participants_cypher = """ MATCH (:Product {id: toInteger($release_id)})-[:INCLUDES]->(t:Track)-[lpToT]-(lp:LabelParticipant) WHERE lp.subaccountId <> toInteger($subaccount_id) CALL apoc.util.sleep(1) MERGE (nlp:LabelParticipant:Orchard {normalizedName: lp.normalizedName, vendorId: lp.vendorId, subaccountId: toInteger($subaccount_id)}) ON CREATE SET nlp.id = toInteger(rand() * datetime.realtime().epochMillis), nlp.uuid = randomUUID(), nlp.createdAt = datetime(), nlp.createdBy = 'lambda-kinesis-to-neo4j/product-move-copy-track-label-participants-cypher', nlp.name = lp.name, nlp.spotifyId = lp.spotifyId SET nlp += apoc.map.removeKeys(lp, ['id', 'uuid', 'subaccountId', 'createdAt', 'createdBy', 'spotifyId']), nlp.spotifyId = COALESCE(nlp.spotifyId, lp.spotifyId) SET nlp.lastModifiedAt = datetime(), nlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/product-move-copy-track-label-participants-cypher' MERGE (v:Vendor:Label:Orchard {id: nlp.vendorId}) ON CREATE SET v.createdAt = datetime(), v.uuid = '', v.createdBy = 'lambda-kinesis-to-neo4j/product-move-copy-track-label-participants-cypher', v.lastModifiedAt = datetime(), v.lastModifiedBy = 'lambda-kinesis-to-neo4j/product-move-copy-track-label-participants-cypher' MERGE (v)-[hlp:HAS_LABEL_PARTICIPANT]->(nlp) ON CREATE SET hlp.createdAt = datetime(), hlp.createdBy = 'lambda-kinesis-to-neo4j/product-move-copy-track-label-participants-cypher', hlp.lastModifiedAt = datetime(), hlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/product-move-copy-track-label-participants-cypher' FOREACH (hasSubAccount IN CASE WHEN NOT (nlp.subaccountId = 0) THEN [1] ELSE [] END | MERGE (sa:SubAccount:Subaccount:Label {id: nlp.subaccountId}) ON CREATE SET sa.createdAt = datetime(), sa.uuid = '', sa.createdBy = 'lambda-kinesis-to-neo4j/product-move-copy-track-label-participants-cypher', sa.lastModifiedAt = datetime(), sa.lastModifiedBy = 'lambda-kinesis-to-neo4j/product-move-copy-track-label-participants-cypher' MERGE (sa)-[shlp:HAS_LABEL_PARTICIPANT]->(nlp) ON CREATE SET shlp.createdAt = datetime(), shlp.createdBy = 'lambda-kinesis-to-neo4j/product-move-copy-track-label-participants-cypher', shlp.lastModifiedAt = datetime(), shlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/product-move-copy-track-label-participants-cypher' ) WITH * CALL apoc.merge.relationship( nlp, type(lpToT), {participated_as: lpToT.participated_as}, lpToT, t, {lastModifiedAt: datetime(), lastModifiedBy: 'lambda-kinesis-to-neo4j/product-move-copy-track-label-participants-cypher'} ) YIELD rel AS rel1 OPTIONAL MATCH (lp)-[lpToTa]->(ta:TrackArtist)--(t) CALL apoc.do.when( lpToTa IS NOT NULL, 'CALL apoc.merge.relationship(nlp, type(lpToTa), {}, lpToTa, ta) YIELD rel AS rel2 RETURN rel2', '', {nlp: nlp, lpToTa: lpToTa, ta: ta} ) YIELD value AS value1 WITH DISTINCT lp, t, nlp OPTIONAL MATCH (lp)-[lpToTw]->(tw:TrackWriter)--(t) CALL apoc.do.when( lpToTw IS NOT NULL, 'CALL apoc.merge.relationship(nlp, type(lpToTw), {}, lpToTw, tw) YIELD rel AS rel3 RETURN rel3', '', {nlp: nlp, lpToTw: lpToTw, tw: tw} ) YIELD value AS value2 RETURN * """ product_move_remove_old_track_label_participants = """ MATCH (:Product {id: toInteger($release_id)})-[:INCLUDES]->(t:Track)-[lpToT]-(lp:LabelParticipant) WHERE lp.subaccountId <> toInteger($subaccount_id) OPTIONAL MATCH (lp)-[lpToTa]->(ta:TrackArtist)--(t) OPTIONAL MATCH (lp)-[lpToTw]->(tw:TrackWriter)--(t) DELETE lpToT, lpToTa, lpToTw; """ product_move_copy_label_sound_recordings_cypher = """ MATCH (p:Product {id: toInteger($release_id)})-[:INCLUDES]->(t:Track)-[lsrToT]->(lsr:LabelSoundRecording) MERGE (nlsr:LabelSoundRecording:Orchard { isrc: toUpper(lsr.isrc), vendorId: lsr.vendorId, subaccountId: toInteger($subaccount_id) }) ON CREATE SET nlsr.id = randomUUID(), nlsr.createdAt = datetime(), nlsr.createdBy = 'lambda-kinesis-to-neo4j/product-move-copy-label-sound-recordings-cypher' SET nlsr += apoc.map.removeKeys(lsr, ['id', 'isrc', 'subaccountId', 'createdAt', 'createdBy']) SET nlsr.lastModifiedAt = datetime(), nlsr.lastModifiedBy = 'lambda-kinesis-to-neo4j/product-move-copy-label-sound-recordings-cypher' WITH * CALL apoc.merge.relationship( t, type(lsrToT), {}, lsrToT, nlsr, {lastModifiedAt: datetime(), lastModifiedBy: 'lambda-kinesis-to-neo4j/product-move-copy-label-sound-recordings-cypher'} ) YIELD rel DELETE lsrToT WITH lsr MATCH (lsr) WHERE NOT (lsr)-[]-() DELETE lsr """ moved_label_participants_cypher = """ MATCH (:Product {id: toInteger($release_id)})--(lp:LabelParticipant) RETURN lp UNION MATCH (:Product {id: toInteger($release_id)})-[:INCLUDES]->(:Track)--(lp:LabelParticipant) RETURN lp """ product_delete_cypher = """ MATCH (p:Product {id: toInteger($release_id)}) DETACH DELETE p """ release_artist_insert_cypher = """ CALL apoc.util.sleep(1) MERGE (ra:ReleaseArtist {id: toInteger($release_artist_id)}) ON CREATE SET ra.productId = toInteger($release_id), ra.name = $artist_name, ra.role = $role, ra.createdAt = datetime(), ra.createdBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher', ra.lastModifiedAt = datetime(), ra.lastModifiedBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher' ON MATCH SET ra.productId = toInteger($release_id), ra.name = $artist_name, ra.role = $role, ra.lastModifiedAt = datetime(), ra.lastModifiedBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher' MERGE (p:Product:Orchard {id: ra.productId}) ON CREATE SET p.createdAt = datetime(), p.createdBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher', p.lastModifiedAt = datetime(), p.lastModifiedBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher' MERGE (p)-[hsa:HAS_SOURCE_ARTIST]->(ra) ON CREATE SET hsa.createdAt = datetime(), hsa.createdBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher', hsa.lastModifiedAt = datetime(), hsa.lastModifiedBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher' MERGE ( lp:LabelParticipant:Orchard { normalizedName: toLower(trim(apoc.text.replace(ra.name, '[ ]+', ''))), vendorId: $vendor_id, subaccountId: $subaccount_id } ) ON CREATE SET lp.id = toInteger(rand() * datetime.realtime().epochMillis), lp.uuid = randomUUID(), lp.createdAt = datetime(), lp.createdBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher', lp.lastModifiedAt = datetime(), lp.lastModifiedBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher', lp.name = trim(apoc.text.replace(ra.name, '[ ]{2,}', ' ')) ON MATCH SET lp.lastModifiedAt = datetime(), lp.lastModifiedBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher' MERGE (lp)-[pi:PARTICIPATED_IN {participated_as: ra.role}]->(p) ON CREATE SET pi.createdAt = datetime(), pi.createdBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher', pi.lastModifiedAt = datetime(), pi.lastModifiedBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher' MERGE (lp)-[cf:CREATED_FROM]->(ra) ON CREATE SET cf.createdAt = datetime(), cf.createdBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher', cf.lastModifiedAt = datetime(), cf.lastModifiedBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher' MERGE (v:Vendor:Label:Orchard {id: lp.vendorId}) ON CREATE SET v.createdAt = datetime(), v.uuid = '', v.createdBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher', v.lastModifiedAt = datetime(), v.lastModifiedBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher' MERGE (v)-[hlp:HAS_LABEL_PARTICIPANT]->(lp) ON CREATE SET hlp.createdAt = datetime(), hlp.createdBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher', hlp.lastModifiedAt = datetime(), hlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher' FOREACH (hasSubAccount IN CASE WHEN NOT (lp.subaccountId = 0) THEN [1] ELSE [] END | MERGE (sa:SubAccount:Subaccount:Label {id: lp.subaccountId}) ON CREATE SET sa.createdAt = datetime(), sa.uuid = '', sa.createdBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher', sa.lastModifiedAt = datetime(), sa.lastModifiedBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher' MERGE (sa)-[shlp:HAS_LABEL_PARTICIPANT]->(lp) ON CREATE SET shlp.createdAt = datetime(), shlp.createdBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher', shlp.lastModifiedAt = datetime(), shlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/release-artist-insert-cypher' ) RETURN lp """ release_artist_delete_cypher = """ MATCH (ra:ReleaseArtist {id: toInteger($release_artist_id)}) MATCH (ra)-[:CREATED_FROM]-(lp:LabelParticipant) OPTIONAL MATCH (lp)-[pi:PARTICIPATED_IN {participated_as: $role}]- (p:Product {id: toInteger($release_id)}) WITH *, [node IN [ra, lp, p] WHERE node IS NOT NULL] AS nodesToLock CALL apoc.lock.nodes(nodesToLock) OPTIONAL MATCH (lp)-[:CREATED_FROM]-(sameRa:ReleaseArtist {role: $role, productId: toInteger($release_id)}) WHERE sameRa.id <> $release_artist_id FOREACH (noDup IN CASE WHEN sameRa IS NULL THEN [1] ELSE [] END | DETACH DELETE pi, ra ) FOREACH (dup IN CASE WHEN sameRa IS NOT NULL THEN [1] ELSE [] END | DETACH DELETE ra ) """ track_insert_cypher = """ MERGE (t:Track:Orchard {id: toInteger($id)}) ON CREATE SET t.createdAt = datetime(), t.createdBy = 'lambda-kinesis-to-neo4j/track-insert-cypher' SET t.name = coalesce($track_name, t.name), t.isrc = toUpper(coalesce($isrc, t.isrc)), t.volumeNumber = $cd, t.trackNumber = $track_id, t.type = coalesce($track_type, t.type), t.version = $version, t.offerType = $offer_type, t.sourceModifiedAt = datetime(REPLACE($last_updated, " ", "T")), t.lastModifiedtAt = datetime(), t.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-insert-cypher' """ track_insert_with_product_cypher = """ MERGE (t:Track:Orchard {id: toInteger($id)}) ON CREATE SET t.createdAt = datetime(), t.createdBy = 'lambda-kinesis-to-neo4j/track-insert-with-product-cypher' SET t.name = $track_name, t.isrc = toUpper($isrc), t.volumeNumber = $cd, t.trackNumber = $track_id, t.type = coalesce($track_type, t.type), t.version = $version, t.productId = toInteger($release_id), t.offerType = $offer_type, t.sourceModifiedAt = datetime(REPLACE($last_updated, " ", "T")), t.lastModifiedAt = datetime(), t.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-insert-with-product-cypher' FOREACH (hasIsrc IN CASE WHEN COALESCE($isrc, "") <> "" AND $isrc =~ '^[A-Za-z]{2}[-]?[0-9A-Za-z]{3}[-]?[0-9]{2}[-]?[0-9]{5}$' THEN [1] ELSE [] END | MERGE (sr:LabelSoundRecording:Orchard { isrc: toUpper($isrc), vendorId: $vendor_id, subaccountId: $subaccount_id } ) ON CREATE SET sr.id = randomUUID(), sr.createdAt = datetime(), sr.createdBy = 'lambda-kinesis-to-neo4j/track-insert-with-product-cypher' SET sr.name = CASE WHEN COALESCE($track_name, '') = '' THEN sr.name ELSE $track_name END, sr.lastModifiedAt = datetime(), sr.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-insert-with-product-cypher', sr.version = $version MERGE (t)-[ipo:IS_PLACEMENT_OF]->(sr) ON CREATE SET ipo.createdAt = datetime(), ipo.createdBy = 'lambda-kinesis-to-neo4j/track-insert-with-product-cypher', ipo.lastModifiedAt = datetime(), ipo.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-insert-with-product-cypher' ) MERGE (p:Product:Orchard {id: t.productId}) ON CREATE SET p.createdAt = datetime(), p.createdBy = 'lambda-kinesis-to-neo4j/track-insert-with-product-cypher', p.lastModifiedAt = datetime(), p.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-insert-with-product-cypher' MERGE (p)-[i:INCLUDES]->(t) ON CREATE SET i.createdAt = datetime(), i.createdBy = 'lambda-kinesis-to-neo4j/track-insert-with-product-cypher', i.lastModifiedAt = datetime(), i.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-insert-with-product-cypher' """ track_update_cypher = """ MERGE (t:Track:Orchard {id: toInteger($id)}) ON CREATE SET t.createdAt = datetime(), t.createdBy = 'lambda-kinesis-to-neo4j/track-update-cypher' MERGE (p:Product:Orchard {id: toInteger($release_id)}) ON CREATE SET p.createdAt = datetime(), p.createdBy = 'lambda-kinesis-to-neo4j/track-update-cypher', p.lastModifiedAt = datetime(), p.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-update-cypher' MERGE (p)-[i:INCLUDES]->(t) ON CREATE SET i.createdAt = datetime(), i.createdBy = 'lambda-kinesis-to-neo4j/track-update-cypher', i.lastModifiedAt = datetime(), i.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-update-cypher' WITH t, toUpper($isrc) as isrc, $track_name as track_name, $track_type as track_type, $release_id as release_id, $vendor_id as vendor_id, $subaccount_id as subaccount_id, $last_updated as last_updated, $version as version, $offer_type as offer_type OPTIONAL MATCH (t)-[oldR:IS_PLACEMENT_OF]-> (oldSr:LabelSoundRecording:Orchard) OPTIONAL MATCH (oldSr)<-[oldSrR:IS_PLACEMENT_OF]-(:Track) WITH t, oldR, oldSr, isrc, track_name, track_type, release_id, vendor_id, subaccount_id, count(oldSrR) AS oldSrRelNumber, last_updated, version, offer_type FOREACH (diffIsrc IN CASE WHEN COALESCE(isrc, "") <> COALESCE(t.isrc, "") THEN [1] ELSE [] END | FOREACH (multipleRelations IN CASE WHEN oldSrRelNumber > 1 THEN [1] ELSE [] END | DELETE oldR ) FOREACH (singleRelation IN CASE WHEN oldSrRelNumber = 1 THEN [1] ELSE [] END | DETACH DELETE oldSr ) ) FOREACH (hasIsrc IN CASE WHEN COALESCE(isrc, "") <> "" AND isrc =~ '^[A-Za-z]{2}[-]?[0-9A-Za-z]{3}[-]?[0-9]{2}[-]?[0-9]{5}$' THEN [1] ELSE [] END | MERGE (sr:LabelSoundRecording:Orchard { isrc: toUpper(isrc), vendorId: vendor_id, subaccountId: subaccount_id } ) ON CREATE SET sr.id = randomUUID(), sr.createdAt = datetime(), sr.createdBy = 'lambda-kinesis-to-neo4j/track-update-cypher' SET sr.version = version, sr.lastModifiedAt = datetime(), sr.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-update-cypher' MERGE (t)-[ipo:IS_PLACEMENT_OF]->(sr) ON CREATE SET ipo.createdAt = datetime(), ipo.createdBy = 'lambda-kinesis-to-neo4j/track-update-cypher', ipo.lastModifiedAt = datetime(), ipo.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-update-cypher' FOREACH (trackNameChanged IN CASE WHEN t.name <> track_name OR COALESCE(isrc, "") <> COALESCE(t.isrc, "") THEN [1] ELSE [] END | SET sr.name = CASE WHEN COALESCE(track_name, '') = '' THEN sr.name ELSE track_name END ) ) SET t.name = track_name, t.isrc = isrc, t.volumeNumber = $cd, t.trackNumber = $track_id, t.type = track_type, t.productId = toInteger(release_id), t.version = version, t.offerType = offer_type, t.sourceModifiedAt = datetime(REPLACE(last_updated, " ", "T")), t.lastModifiedAt = datetime(), t.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-update-cypher' """ track_delete_cypher = """ MATCH (t:Track:Orchard {id: toInteger($id)}) OPTIONAL MATCH (t)-[r:IS_PLACEMENT_OF]-(sr:LabelSoundRecording:Orchard) WITH t, sr OPTIONAL MATCH (sr)<-[oldSrR:IS_PLACEMENT_OF]-(:Track) WITH t, sr, count(oldSrR) as oldSrRelNumber FOREACH (singleRelation IN CASE WHEN oldSrRelNumber = 1 THEN [1] ELSE [] END | DETACH DELETE sr ) DETACH DELETE t """ track_artist_insert_cypher = """ CALL apoc.util.sleep(1) MERGE (ta:TrackArtist {id: toInteger($id)}) ON CREATE SET ta.trackId = toInteger($track_id), ta.name = $name, ta.type = $type, ta.createdAt = datetime(), ta.createdBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher', ta.lastModifiedAt = datetime(), ta.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher' ON MATCH SET ta.trackId = toInteger($track_id), ta.name = $name, ta.type = $type, ta.lastModifiedAt = datetime(), ta.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher' MERGE (t:Track:Orchard {id: $track_id}) ON CREATE SET t.createdAt = datetime(), t.createdBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher', t.lastModifiedAt = datetime(), t.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher' MERGE (t)-[hsa:HAS_SOURCE_ARTIST]->(ta) ON CREATE SET hsa.createdAt = datetime(), hsa.createdBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher', hsa.lastModifiedAt = datetime(), hsa.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher' MERGE ( lp:LabelParticipant:Orchard { normalizedName: toLower(trim(apoc.text.replace($name, '[ ]+', ''))), vendorId: $vendor_id, subaccountId: $subaccount_id } ) ON CREATE SET lp.id = toInteger(rand() * datetime.realtime().epochMillis), lp.uuid = randomUUID(), lp.createdAt = datetime(), lp.createdBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher', lp.lastModifiedAt = datetime(), lp.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher', lp.name = trim(apoc.text.replace($name, '[ ]{2,}', ' ')) ON MATCH SET lp.lastModifiedAt = datetime(), lp.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher' MERGE (lp)-[participation:PARTICIPATED_IN {participated_as: $type}]->(t) ON CREATE SET participation.createdAt = datetime(), participation.createdBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher', participation.lastModifiedAt = datetime(), participation.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher' MERGE (lp)-[cf:CREATED_FROM]->(ta) ON CREATE SET cf.createdAt = datetime(), cf.createdBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher', cf.lastModifiedAt = datetime(), cf.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher' MERGE (v:Vendor:Label:Orchard {id: lp.vendorId}) ON CREATE SET v.createdAt = datetime(), v.uuid = '', v.createdBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher', v.lastModifiedAt = datetime(), v.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher' MERGE (v)-[hlp:HAS_LABEL_PARTICIPANT]->(lp) ON CREATE SET hlp.createdAt = datetime(), hlp.createdBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher', hlp.lastModifiedAt = datetime(), hlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher' FOREACH (hasSubAccount IN CASE WHEN not (lp.subaccountId = 0) THEN [1] ELSE [] END | MERGE (sa:SubAccount:Subaccount:Label {id: lp.subaccountId}) ON CREATE SET sa.createdAt = datetime(), sa.uuid = '', sa.createdBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher', sa.lastModifiedAt = datetime(), sa.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher' MERGE (sa)-[shlp:HAS_LABEL_PARTICIPANT]->(lp) ON CREATE SET shlp.createdAt = datetime(), shlp.createdBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher', shlp.lastModifiedAt = datetime(), shlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-artist-insert-cypher' ) RETURN lp """ track_artist_delete_cypher = """ MATCH (ta:TrackArtist {id: toInteger($id)}) MATCH (ta)-[:CREATED_FROM]-(lp:LabelParticipant) OPTIONAL MATCH (lp)-[pit:PARTICIPATED_IN {participated_as: $type}]-> (t:Track:Orchard {id: toInteger($track_id)}) WITH *, [node IN [ta, lp, t] WHERE node IS NOT NULL] AS nodesToLock CALL apoc.lock.nodes(nodesToLock) OPTIONAL MATCH (lp)-[:CREATED_FROM]-(sameTa:TrackArtist {type: $type, trackId: toInteger($track_id)}) WHERE sameTa.id <> toInteger($id) FOREACH (noDup IN CASE WHEN sameTa IS NULL THEN [1] ELSE [] END | DETACH DELETE pit, ta ) FOREACH (dup IN CASE WHEN sameTa IS NOT NULL THEN [1] ELSE [] END | DETACH DELETE ta ) """ track_writer_insert_cypher = """ CALL apoc.util.sleep(1) MERGE (tw:TrackWriter {id: toInteger($track_writer_id)}) ON CREATE SET tw.uniqueTrackId = toInteger($unique_track_id), tw.writerName = $writer_name, tw.createdAt = datetime(), tw.createdBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher', tw.lastModifiedAt = datetime(), tw.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher' ON MATCH SET tw.uniqueTrackId = toInteger($unique_track_id), tw.writerName = $writer_name, tw.lastModifiedAt = datetime(), tw.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher' MERGE (t:Track:Orchard {id: tw.uniqueTrackId}) ON CREATE SET t.createdAt = datetime(), t.createdBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher', t.lastModifiedAt = datetime(), t.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher' MERGE (t)-[hsa:HAS_SOURCE_ARTIST]->(tw) ON CREATE SET hsa.createdAt = datetime(), hsa.createdBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher', hsa.lastModifiedAt = datetime(), hsa.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher' MERGE ( lp:LabelParticipant:Orchard { normalizedName: toLower(trim(apoc.text.replace($writer_name, '[ ]+', ''))), vendorId: $vendor_id, subaccountId: $subaccount_id } ) ON CREATE SET lp.id = toInteger(rand() * datetime.realtime().epochMillis), lp.uuid = randomUUID(), lp.createdAt = datetime(), lp.createdBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher', lp.lastModifiedAt = datetime(), lp.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher', lp.name = trim(apoc.text.replace($writer_name, '[ ]{2,}', ' ')) ON MATCH SET lp.lastModifiedAt = datetime(), lp.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher' MERGE (lp)-[participation:PARTICIPATED_IN {participated_as: 'track_writer'}]->(t) ON CREATE SET participation.createdAt = datetime(), participation.createdBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher', participation.lastModifiedAt = datetime(), participation.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher' ON MATCH SET participation.lastModifiedAt = datetime(), participation.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher' MERGE (lp)-[cf:CREATED_FROM]->(tw) ON CREATE SET cf.createdAt = datetime(), cf.createdBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher', cf.lastModifiedAt = datetime(), cf.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher' MERGE (v:Vendor:Label:Orchard {id: lp.vendorId}) ON CREATE SET v.createdAt = datetime(), v.uuid = '', v.createdBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher', v.lastModifiedAt = datetime(), v.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher' MERGE (v)-[hlp:HAS_LABEL_PARTICIPANT]->(lp) ON CREATE SET hlp.createdAt = datetime(), hlp.createdBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher', hlp.lastModifiedAt = datetime(), hlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher' FOREACH (hasSubAccount IN CASE WHEN not (lp.subaccountId = 0) THEN [1] ELSE [] END | MERGE (sa:SubAccount:Subaccount:Label {id: lp.subaccountId}) ON CREATE SET sa.createdAt = datetime(), sa.uuid = '', sa.createdBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher', sa.lastModifiedAt = datetime(), sa.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher' MERGE (sa)-[shlp:HAS_LABEL_PARTICIPANT]->(lp) ON CREATE SET shlp.createdAt = datetime(), shlp.createdBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher', shlp.lastModifiedAt = datetime(), shlp.lastModifiedBy = 'lambda-kinesis-to-neo4j/track-writer-insert-cypher' ) RETURN lp """ track_writer_delete_cypher = """ MATCH (tw:TrackWriter {id: toInteger($track_writer_id)}) MATCH (tw)-[cf:CREATED_FROM]-(lp:LabelParticipant) OPTIONAL MATCH (lp)-[pit:PARTICIPATED_IN {participated_as: 'track_writer'}]-> (t:Track:Orchard {id: toInteger($unique_track_id)}) WITH *, [node IN [tw, lp, t] WHERE node IS NOT NULL] AS nodesToLock CALL apoc.lock.nodes(nodesToLock) OPTIONAL MATCH (lp)-[:CREATED_FROM]- (sameTw:TrackWriter {uniqueTrackId: toInteger($unique_track_id)}) WHERE sameTw.id <> toInteger($track_writer_id) FOREACH (noDup IN CASE WHEN sameTw IS NULL THEN [1] ELSE [] END | DETACH DELETE pit, tw ) FOREACH (dup IN CASE WHEN sameTw IS NOT NULL THEN [1] ELSE [] END | DETACH DELETE tw ) """ track_set_sequence_numbers = """ MATCH (t:Track:Orchard { id: toInteger($track_id) }) CALL apoc.lock.nodes([t]) OPTIONAL MATCH (t)<-[rel:PARTICIPATED_IN]-(lp:LabelParticipant) OPTIONAL MATCH (t)-[:HAS_SOURCE_ARTIST]->(originalDataSource) WHERE EXISTS((lp)-[:CREATED_FROM]->(originalDataSource)) AND ( ( rel.participated_as = 'track_writer' AND originalDataSource:TrackWriter ) OR ( rel.participated_as <> 'track_writer' AND originalDataSource:TrackArtist AND originalDataSource.type = rel.participated_as ) ) WITH rel, min(originalDataSource.id) AS orderInRole, CASE rel.participated_as WHEN 'performer' THEN 1 WHEN 'featuring' THEN 2 WHEN 'remixer' THEN 3 WHEN 'producer' THEN 4 WHEN 'track_writer' THEN 5 ELSE 6 END AS roleValue ORDER BY roleValue, orderInRole WITH collect(rel) AS sequence UNWIND range(0, size(sequence)-1) AS sequenceNumber WITH sequence[sequenceNumber] AS rel, sequenceNumber SET rel.sequenceNumber = sequenceNumber """ participant_external_link_upsert_twitter_cypher = """ MATCH (lp:LabelParticipant:Orchard { id: toInteger($label_participant_id) }) SET lp.twitterId = toString($store_artist_id) """ participant_external_link_upsert_youtube_cypher = """ MATCH (lp:LabelParticipant:Orchard { id: toInteger($label_participant_id) }) SET lp.youtubeId = toString($store_artist_id) """ participant_external_link_upsert_facebook_cypher = """ MATCH (lp:LabelParticipant:Orchard { id: toInteger($label_participant_id) }) SET lp.facebookId = toString($store_artist_id) """ participant_external_link_upsert_instagram_cypher = """ MATCH (lp:LabelParticipant:Orchard { id: toInteger($label_participant_id) }) SET lp.instagramId = toString($store_artist_id) """ get_all_labelprofiles_cypher = """ MATCH (i:Identity)-[rel:{relation_to_profile}]->(p:Profile {{profileId: toInteger($id), profileType: 'LabelProfile'}}) SET i.active = '{is_active}' """ set_label_uuids_for_label_participant_by_uuid = """ MATCH (lp:LabelParticipant {uuid: $uuid}) OPTIONAL MATCH (lp)<-[:HAS_LABEL_PARTICIPANT]-(v:Vendor) OPTIONAL MATCH (v)<-[:HAS_LABEL]-(cb:CompanyBrand)-[:BELONGS_TO]->(pc:ParentCompany) OPTIONAL MATCH (lp)<-[:HAS_LABEL_PARTICIPANT]-(sa:Subaccount) SET lp.companyBrandUUID = cb.uuid, lp.vendorUUID = v.uuid, lp.subaccountUUID = sa.uuid, lp.parentCompanyUUID = pc.uuid RETURN lp """ link_global_participant_to_label_participant = """ MATCH (lp:LabelParticipant {uuid: $uuid}) WHERE lp.spotifyId IS NOT NULL AND NOT EXISTS((:GlobalParticipant { spotifyId: lp.spotifyId })-[:REPRESENTS]->(lp)) MERGE (gp:GlobalParticipant {spotifyId: lp.spotifyId}) ON CREATE SET gp.id = randomUUID(), gp.name = lp.name, gp.appleMusicId = lp.appleMusicId, gp.createdAt = datetime(), gp.createdBy = 'kinesis-to-neo4j/link_global_participant_to_label_participant', gp.lastModifiedAt = datetime(), gp.lastModifiedBy = 'kinesis-to-neo4j/link_global_participant_to_label_participant' MERGE (gp)-[gpToLp:REPRESENTS]->(lp) ON CREATE SET gpToLp.createdAt = datetime(), gpToLp.createdBy = "kinesis-to-neo4j/link_global_participant_to_label_participant" ON MATCH SET gpToLp.lastModifiedAt = datetime(), gpToLp.lastModifiedBy = "kinesis-to-neo4j/link_global_participant_to_label_participant" """ set_label_uuids_for_label_participant_by_params = """ MATCH (lp:LabelParticipant { normalizedName: toLower(trim(apoc.text.replace($name, '[ ]+', ''))), vendorId: $vendor_id, subaccountId: $subaccount_id }) OPTIONAL MATCH (lp)<-[:HAS_LABEL_PARTICIPANT]-(v:Vendor) OPTIONAL MATCH (v)<-[:HAS_LABEL]-(cb:CompanyBrand)-[:BELONGS_TO]->(pc:ParentCompany) OPTIONAL MATCH (lp)<-[:HAS_LABEL_PARTICIPANT]-(sa:Subaccount) SET lp.companyBrandUUID = cb.uuid, lp.vendorUUID = v.uuid, lp.subaccountUUID = sa.uuid, lp.parentCompanyUUID = pc.uuid RETURN lp """ add_service_tier_for_vendor_cypher = """ MATCH (v:Vendor {vendorId: $vendor_id}) WITH v MATCH (st:ServiceTier {uuid: $service_tier_uuid}) WITH v, st MERGE (v)-[ist:IN_SERVICE_TIER]->(st) ON CREATE SET ist.createdAt = $current_time, ist.createdBy = 'lambda-kinesis-to-neo4j/vendor-service-tier-insert-cypher' """ delete_service_tier_for_vendor_cypher = """ MATCH (v:Vendor {vendorId: $vendor_id})-[r:IN_SERVICE_TIER]-> (st:ServiceTier {uuid: $service_tier_uuid}) SET r.lastModifiedAt = $current_time, r.lastModifiedBy = 'lambda-kinesis-to-neo4j/vendor-service-tier-delete-cypher' WITH r CALL apoc.refactor.setType(r, 'DELETED_IN_SERVICE_TIER') YIELD input, output RETURN input, output """ # Parent company UUID mapping (only 2 known parent companies) # 1 = The Orchard, 2 = Sony Music PARENT_COMPANY_UUID_MAP = { 1: 'f1594122-7f99-4916-b103-08b0444c7b46', # Sony Music 2: '955a1bbd-b623-4ea1-ab5f-8d6620c442fb', # The Orchard } company_brand_insert_update_cypher = """ MERGE (cb:CompanyBrand {uuid: $uuid}) ON CREATE SET cb.companyBrandId = toInteger($id), cb.name = $name, cb.displayName = $display_name, cb.createdAt = datetime(), cb.createdBy = 'lambda-kinesis-to-neo4j/company-brand-insert-update-cypher', cb.lastModifiedAt = datetime(), cb.lastModifiedBy = 'lambda-kinesis-to-neo4j/company-brand-insert-update-cypher' ON MATCH SET cb.companyBrandId = toInteger($id), cb.name = $name, cb.displayName = $display_name, cb.lastModifiedAt = datetime(), cb.lastModifiedBy = 'lambda-kinesis-to-neo4j/company-brand-insert-update-cypher' WITH cb MATCH (pc:ParentCompany {uuid: $parent_company_uuid}) MERGE (cb)-[bt:BELONGS_TO]->(pc) ON CREATE SET bt.createdAt = datetime(), bt.createdBy = 'lambda-kinesis-to-neo4j/company-brand-insert-update-cypher', bt.lastModifiedAt = datetime(), bt.lastModifiedBy = 'lambda-kinesis-to-neo4j/company-brand-insert-update-cypher' ON MATCH SET bt.lastModifiedAt = datetime(), bt.lastModifiedBy = 'lambda-kinesis-to-neo4j/company-brand-insert-update-cypher' RETURN cb """