// Load product catalog
//CALL apoc.load.jdbc("jdbc:mysql://localhost:3306/furniturestore?user=root","SELECT * FROM products") YIELD row

LOAD CSV WITH HEADERS FROM "https://docs.google.com/spreadsheets/u/1/d/1AL4uijztdNowNitO7H1aPJO1ZTxgpujyi7acRAA69FE/export?format=csv&id=1AL4uijztdNowNitO7H1aPJO1ZTxgpujyi7acRAA69FE&gid=0" AS row

MERGE (parent_category:Category {name: row.parent_category})
MERGE (category:Category {name: row.category})
MERGE (category)-[:PARENT_CATEGORY]->(parent_category)
MERGE (p:Product {sku: toString(row.sku)})
SET p.name  = row.name,
    p.price = toFloat(row.price)
MERGE (p)-[:IN_CATEGORY]->(category)
MERGE (d:Designer {name: row.designer})
MERGE (p)-[:DESIGNED_BY]-(d)
RETURN *;

CALL db.schema();

// Inspect a single product
MATCH (p:Product {sku: "4839321"})-[:IN_CATEGORY]->(c:Category)
RETURN *

// Recommend from same category
MATCH (p:Product {sku: "4839321"})
MATCH (p)-[:IN_CATEGORY]->(:Category)<-[:IN_CATEGORY]-(rec:Product)
RETURN rec;

// Recommend from same category
// Overlapping designer
MATCH (p:Product {sku: "4839321"})
MATCH (p)-[:IN_CATEGORY]->(c:Category)<-[:IN_CATEGORY]-(rec:Product)
MATCH (p)-[:DESIGNED_BY]->(d:Designer)<-[:DESIGNED_BY]-(rec)
RETURN *;

// Walk the hierarchy of product categories
MATCH (p:Product {sku: "4839321"})
MATCH path=(p)-[*1..2]->(c:Category)<-[*1..2]-(rec:Product)
RETURN *;

// Load customers
LOAD CSV WITH HEADERS FROM "https://docs.google.com/spreadsheets/u/0/d/1wb7obY4WF08aeb4ey-NUsiB4BZzRHTDoGKZEH-jmv_k/export?format=csv&id=1wb7obY4WF08aeb4ey-NUsiB4BZzRHTDoGKZEH-jmv_k&gid=573101337" AS row
MERGE (c:Customer {customerid: row.customerid})
SET c.name = row.Name
MERGE (city:City {name: row.City})
MERGE (c)-[:LIVES_IN]->(city)
RETURN *;

// Load orders
LOAD CSV WITH HEADERS FROM "https://docs.google.com/spreadsheets/u/0/d/1wb7obY4WF08aeb4ey-NUsiB4BZzRHTDoGKZEH-jmv_k/export?format=csv&id=1wb7obY4WF08aeb4ey-NUsiB4BZzRHTDoGKZEH-jmv_k&gid=749858493" AS row
MERGE (o:Order {orderid: row.orderid})
WITH *
MATCH (c:Customer {customerid: row.customerid})
MATCH (p:Product {sku: row.sku})
MERGE (c)-[:PLACED]->(o)
MERGE (o)-[:CONTAINS]->(p)
RETURN *;

CALL db.schema();

MATCH (c:Customer {name: "Nicole Ramsey"})
MATCH (c)-[:PLACED]->(:Order)-[:CONTAINS]->(p:Product)
MATCH (p)<-[:CONTAINS]-(:Order)<-[:PLACED]-(u:Customer)
MATCH (u)-[:PLACED]->(:Order)-[:CONTAINS]->(rec:Product)
RETURN rec.sku AS sku, rec.name AS name, rec.price AS price, COUNT(*) AS score ORDER BY score DESC LIMIT 5

// Load customer reviews
CALL apoc.load.json("http://guides.neo4j.com/sandbox/retail-recommendations/data/reviews.json") YIELD value AS row
MATCH (c:Customer {customerid: toString(row.customerid)})
MATCH (p:Product {sku: toString(row.sku)})
MERGE (c)-[r:REVIEWED]->(p)
SET r.rating = round(toFloat(row.review))
RETURN *;

MATCH (c:Customer {name: "Nicole Ramsey"})
MATCH (c)-[r:REVIEWED]->(p)<-[:REVIEWED]-(u:Customer)
MATCH (u)-[r2:REVIEWED]->(rec:Product)
WHERE r.rating > 2 AND r2.rating > 3
RETURN DISTINCT rec.sku AS sku, rec.name AS name, rec.price, r2.rating AS rating;

// Load inventory
LOAD CSV WITH HEADERS FROM "https://docs.google.com/spreadsheets/u/0/d/1wb7obY4WF08aeb4ey-NUsiB4BZzRHTDoGKZEH-jmv_k/export?format=csv&id=1wb7obY4WF08aeb4ey-NUsiB4BZzRHTDoGKZEH-jmv_k&gid=966157202" AS row
MERGE (s:Store {name: row.store})
WITH *
MATCH (c:City {name: row.store})
MERGE (s)-[:IN_CITY]-(c)
WITH *
MATCH (p:Product {sku: row.sku})
MERGE (p)-[r:INVENTORY]->(s)
SET r.count = toInt(row.number)
RETURN *;

MATCH (c:Customer {name: "Nicole Ramsey"})
MATCH (c)-[r:REVIEWED]->(p)<-[:REVIEWED]-(u:Customer)
MATCH (u)-[r2:REVIEWED]->(rec:Product)
WHERE r.rating > 2 AND r2.rating > 3
MATCH (rec)-[i:INVENTORY]->(s:Store)-[:IN_CITY]->(:City)<-[:LIVES_IN]-(c)
WHERE i.count > 0
RETURN DISTINCT rec.sku AS sku, rec.name AS name, rec.price, r2.rating AS rating