Jesus Barrasa script to import transfers

import_all_transfers.cql

//Clean up the db
MATCH (n)
OPTIONAL MATCH (n)-[r]-()
DELETE n,r;

//Load Players
USING PERIODIC COMMIT
LOAD CSV FROM "file:c:/all_transfers.csv" AS row
MERGE (:Player {player_name: row[1], player_url: row[2], player_position: row[9]});

//Index players
CREATE INDEX ON :Player(player_url);

//Load Clubs
USING PERIODIC COMMIT
LOAD CSV FROM "file:c:/all_transfers.csv" AS row
MERGE (:Club {club_name: row[3], club_name_short: row[4], club_country: row[5]})
MERGE (:Club {club_name: row[6], club_name_short: row[7], club_country: row[8]});

//Load Transfers
LOAD CSV FROM "file:c:/all_transfers.csv" AS row
MATCH (player:Player {player_url: row[2]})
MATCH (source:Club {club_name: row[3], club_country: row[5]})
MATCH (destination:Club {club_name: row[6], club_name_short: row[7], club_country: row[8]})
CREATE (t:Transfer {transfer_season: row[0], transfer_player_age: row[10], transfer_rank: row[13], transfer_fee_text: row[11], transfer_fee_num: row[12]})
CREATE (t)-[:OF_PLAYER]->(player), (t)-[:FROM_CLUB]->(source), (t)-[:TO_CLUB]->(destination);

// convert numeric transfer fees
MATCH (t:Transfer)
WITH t, 
CASE 
 WHEN t.transfer_fee_num =~ ".*,.*" THEN toInt(replace(t.transfer_fee_num,',',''))*10000
 ELSE toInt(t.transfer_fee_num)*1000
END AS numeric_fee
SET t.transfer_fee = numeric_fee
REMOVE t.transfer_fee_num;

//Add year to transfer
MATCH (t:Transfer) 
WITH distinct t.transfer_season as raw_season, toInt(left(t.transfer_season,2)) as date 
WITH raw_season, CASE WHEN date>89 THEN date + 1900
 ELSE date + 2000 END AS year
MATCH (t:Transfer) WHERE t.transfer_season = raw_season 
SET t.transfer_year = year;

// Add cash flow
MATCH (t:Transfer) WITH DISTINCT t.transfer_year AS year 
MATCH (seller)<-[:FROM_CLUB]-(t:Transfer)-[:TO_CLUB]->(buyer)
WHERE t.transfer_year = year
WITH year, seller, buyer,sum(t.transfer_fee) AS cash_flow
CREATE (buyer)-[:CASH_FLOW {total: cash_flow, year: year}]->(seller);