import_transfer.cql

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

//-----------------------
//Import players
//-----------------------
CREATE CONSTRAINT ON (a:PLAYER)
ASSERT a.id IS UNIQUE;

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/transfers.csv" AS line
FIELDTERMINATOR ','
WITH line
WHERE line.player_profile_source IS NOT NULL
MERGE (a:PLAYER {id: line.player_profile_source})

ON CREATE SET a.player_name= line.player_name,
    a.age= toInt(line.age),
    a.nationality= line.nationality,
    a.nationality_source= line.nationality_source,
    a.nationality_title= line.nationality_title,
    a.nationality_alt= line.nationality_alt,
    a.position= line.position,
    a.transfer_fee= line.transfer_fee,
    a.market_value= line.market_value,
    a.player_profile= line.player_profile,
    a.player_profile_source= line.player_profile_source,
    a.player_profile_title= line.player_profile_title,
    a.player_profile_text= line.player_profile_text,
    a._resultNumber= line._resultNumber,
    a._source= line._source,
    a._pageUrl= line._pageUrl,
    a.current_club_name= line.current_club_name,
    a.moving_from_club_name= line.moving_from_club_name;
  
//-----------------------
//Import clubs
//-----------------------
CREATE CONSTRAINT ON (a:CLUB)
ASSERT a.name IS UNIQUE;

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/transfers.csv" AS line
FIELDTERMINATOR ','
MERGE (a:CLUB {name: line.current_club_name});

USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/transfers.csv" AS line
FIELDTERMINATOR ','
MERGE (a:CLUB {name: line.moving_from_club_name});

//-----------------------
//Create transfers
//-----------------------
USING PERIODIC COMMIT 2000
LOAD CSV WITH HEADERS FROM "file:c:/transfers.csv" AS line
FIELDTERMINATOR ','
WITH line
MATCH (a:CLUB {name: line.moving_from_club_name}), (b:PLAYER {id: line.player_profile_source}), (c:CLUB {name: line.current_club_name})
MERGE (a)-[:TRANSFER]->(b)-[:TRANSFER]->(c);

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