import.cypher

// CLEAR DATA
MATCH (n:Officer) DETACH delete n;
MATCH (n:Incident) DETACH delete n;
MATCH (n:Precinct) DETACH delete n;
MATCH (n:Penalty) DETACH delete n;

// SET CONSTRAINTS
CREATE CONSTRAINT officerIdConstraint ON (officer:Officer) ASSERT officer.id IS UNIQUE
CREATE CONSTRAINT incidentIdConstraint ON (incident:Incident) ASSERT incident.id IS UNIQUE
CREATE CONSTRAINT precinctIdConstraint ON (precinct:Precinct) ASSERT precinct.id IS UNIQUE
CREATE CONSTRAINT penaltyIdConstraint ON (penalty:Penalty) ASSERT penalty.id IS UNIQUE

// CREATE PRECINCTS
LOAD CSV WITH HEADERS FROM "http://localhost:11001/project-185bb75c-b944-451a-9c0f-aeba860ae68a/nypd_complaints.csv" AS csvLine
MERGE (precinct:Precinct {pct: COALESCE(csvLine.IncidentPrecinct, 'NO PRECINCT SET')})
RETURN precinct

// CREATE PENALTIES
LOAD CSV WITH HEADERS FROM "http://localhost:11001/project-185bb75c-b944-451a-9c0f-aeba860ae68a/nypd_complaints.csv" AS csvLine
MERGE (penalty:Penalty {id: toInteger(csvLine.ComplaintID), penaltyRec: COALESCE(csvLine.PenaltyRec, "NO PENALTY RECOMMENDATION"), penaltyDesc: COALESCE(csvLine.PenaltyDesc, "NO PENALTY DESCRIPTION")})
return penalty

// CREATE OFFICERS
LOAD CSV WITH HEADERS FROM "http://localhost:11001/project-185bb75c-b944-451a-9c0f-aeba860ae68a/nypd_complaints.csv" AS csvLine
MERGE (officer:Officer {id: toInteger(csvLine.OfficerID), lastName: csvLine.LastName, firstName: csvLine.FirstName, status: csvLine.Status, gender: csvLine.OfficerGender})
RETURN officer

// MAYBE FULL NAMES?
// MAYBE FULL NAMES?
RETURN COALESCE(a.x ,"") + ',' + COALESCE(a.y ,"")
LOAD CSV WITH HEADERS FROM "http://localhost:11001/project-185bb75c-b944-451a-9c0f-aeba860ae68a/nypd_complaints.csv" AS csvLine
MERGE (officer:Officer {id: toInteger(csvLine.OfficerID), fullName: COALESCE(csvLine.FirstName ,"") + ',' + COALESCE(csvLine.LastName ,""), lastName: csvLine.LastName, firstName: csvLine.FirstName, status: csvLine.Status, gender: csvLine.OfficerGender})
RETURN officer


// CREATE INCIDENTS
LOAD CSV WITH HEADERS FROM "http://localhost:11001/project-185bb75c-b944-451a-9c0f-aeba860ae68a/nypd_complaints.csv" AS csvLine
CREATE (incident:Incident {id: csvLine.ComplaintID, date: csvLine.IncidentDate, ccrbDisposition: csvLine.CCRBDisposition, nypdDisposition: csvLine.NYPDDisposition, location: csvLine.LocationType, reason: csvLine.ContactReason, outcome: csvLine.ContactOutcome, impactedRace: csvLine.ImpactedRace, impactedGender: csvLine.ImpactedGender, incidentCommand: csvLine.IncidentCommand})

CREATE INDEX FOR (incident:Incident) on (incident.id)

// CREATE RELATIONSHIPS BETWEEN OFFICERS AND PRECINCTS
LOAD CSV WITH HEADERS FROM "http://localhost:11001/project-185bb75c-b944-451a-9c0f-aeba860ae68a/nypd_complaints.csv" AS csvLine
MATCH (officer:Officer {id: toInteger(csvLine.ShieldNo)}), (precinct:Precinct {pct: csvLine.IncidentPrecinct})
CREATE (officer)-[:MEMBER_OF {currentRank: csvLine.CurrentRankLong}]->(precinct)

// CREATE RELATIONSHIPS BETWEEN OFFICERS AND INCIDENTS
LOAD CSV WITH HEADERS FROM "http://localhost:11001/project-185bb75c-b944-451a-9c0f-aeba860ae68a/nypd_complaints.csv" AS csvLine
MATCH (officer:Officer {id: toInteger(csvLine.OfficerID)}) with csvLine, officer
MATCH (incident:Incident {id: csvLine.ComplaintID}) with csvLine, officer, incident
CREATE (officer)-[:NAMED_IN {allegation: csvLine.Allegation}]->(incident)


// CREATE RELATIONSHIPS BETWEEN INCIDENTS AND PENALTIES
LOAD CSV WITH HEADERS FROM "http://localhost:11001/project-185bb75c-b944-451a-9c0f-aeba860ae68a/nypd_complaints.csv" AS csvLine
MATCH (incident:Incident {id: csvLine.ComplaintID}) with csvLine, incident
MATCH (penalty:Penalty {id: toInteger(csvLine.ComplaintID)}) with csvLine, incident, penalty
CREATE (incident)-[:RECEIVED_PENALTY]->(penalty)

// CREATE RELATIONSHIPS BETWEEN OFFICERS WHO CO-APPEAR IN INCIDENTS
// MATCH (o1:Officer)-[:NAMED_IN]->(i:Incident)<-[:NAMED_IN]-(o2:Officer) WHERE id(o1)<id(o2) CREATE (o1)-[:CO_OCCURANCE { weight: count(i) }]->(o2)
MATCH (o1:Officer)-[:NAMED_IN]->(i:Incident)<-[:NAMED_IN]-(o2:Officer) WHERE id(o1)<id(o2) CREATE (o1)-[:CO_OCCURANCE]->(o2)


// GENERATE OFFICER CENTRALITY
CALL gds.alpha.eigenvector.write({
  nodeProjection: 'Officer',
  relationshipProjection: 'CO_OCCURANCE',
  writeProperty: 'eigenvector'
})
YIELD nodes, iterations, dampingFactor, writeProperty


// STREAM NODES AND RELATIONSHIPS TO GEPHI
match path = (:Officer)-[:NAMED_IN]->(:Incident) WITH path limit 50000 with collect(path) as paths call apoc.gephi.add(null,'workspace1', paths) yield nodes, relationships, time return nodes, relationships, time

// STREAM OFFICER RELATIONSHIPS (NO INCIDENTS)
match path = (o1:Officer)-[:CO_OCCURANCE]->(o2:Officer) 
WHERE o1.eigenvector > 0.25 AND o2.eigenvector > 0.25
WITH path limit 160000 with collect(path) 
as paths call apoc.gephi.add(null,'workspace1', paths, 'weight', ['firstName', 'lastName']) yield nodes, relationships, time 
return nodes, relationships, time

match path = (o1:Officer)-[:CO_OCCURANCE]->(o2:Officer) 
WHERE o1.eigenvector > 0.1 AND o2.eigenvector > 0.1
WITH path limit 160000 with collect(path) 
as paths call apoc.gephi.add(null,'workspace1', paths, 'weight', ['firstName', 'lastName']) yield nodes, relationships, time 
return nodes, relationships, time