-- 1. (Optional) Clean up the database
MATCH (n) DETACH DELETE n;

-- 2. Create indexes for faster matching
CREATE INDEX ON :JobType(id);
CREATE INDEX ON :ObjectType(id);
CREATE INDEX ON :Job(id);
CREATE INDEX ON :Object(id);
CREATE INDEX ON :MgmtStatus(id);

-- 3. Create Nodes from CSV files

-- Job Types
LOAD CSV FROM '/data/job_types.csv' WITH HEADER AS row
CREATE (n:JobType {
  id: row.id, 
  name: row.name, 
  standard_duration: toInteger(row.standard_duration), 
  standard_cost: toInteger(row.standard_cost)
});

-- Object Types
LOAD CSV FROM '/data/object_types.csv' WITH HEADER AS row
CREATE (n:ObjectType {
  id: row.id, 
  name: row.name, 
  category: row.category
});

-- Job Instances
LOAD CSV FROM '/data/jobs.csv' WITH HEADER AS row
CREATE (n:Job {
  id: toInteger(row.id), 
  name: row.name, 
  duration: toInteger(row.duration), 
  cost: toInteger(row.cost), 
  job_no: row.job_no
});

-- Object Instances
LOAD CSV FROM '/data/objects.csv' WITH HEADER AS row
CREATE (n:Object {
  id: row.id, 
  name: row.name, 
  obj_no: row.obj_no
});

-- Management Status Nodes
LOAD CSV FROM '/data/mgmt_status.csv' WITH HEADER AS row
CREATE (n:MgmtStatus {
  id: row.id, 
  type: row.type, 
  status: row.status
});


-- 4. Create Relationships from relations.csv

-- Job IS_A JobType
LOAD CSV FROM '/data/relations.csv' WITH HEADER AS row
WITH row WHERE row.type = 'IS_A' AND toIntegerOrNull(row.from_id) IS NOT NULL
MATCH (from:Job {id: toInteger(row.from_id)})
MATCH (to:JobType {id: row.to_id})
CREATE (from)-[:IS_A]->(to);

-- Object IS_A ObjectType
LOAD CSV FROM '/data/relations.csv' WITH HEADER AS row
WITH row WHERE row.type = 'IS_A' AND toIntegerOrNull(row.from_id) IS NULL
MATCH (from:Object {id: row.from_id})
MATCH (to:ObjectType {id: row.to_id})
CREATE (from)-[:IS_A]->(to);

-- Job PRECEDES Job
LOAD CSV FROM '/data/relations.csv' WITH HEADER AS row
WITH row WHERE row.type = 'PRECEDES'
MATCH (from:Job {id: toInteger(row.from_id)})
MATCH (to:Job {id: toInteger(row.to_id)})
CREATE (from)-[:PRECEDES]->(to);

-- Job REQUIRES Object
LOAD CSV FROM '/data/relations.csv' WITH HEADER AS row
WITH row WHERE row.type = 'REQUIRES'
MATCH (from:Job {id: toInteger(row.from_id)})
MATCH (to:Object {id: row.to_id})
CREATE (from)-[:REQUIRES {quantity: toInteger(row.quantity), unit: row.unit}]->(to);

-- Job HAS_STATUS MgmtStatus
LOAD CSV FROM '/data/relations.csv' WITH HEADER AS row
WITH row WHERE row.type = 'HAS_STATUS'
MATCH (from:Job {id: toInteger(row.from_id)})
MATCH (to:MgmtStatus {id: row.to_id})
CREATE (from)-[:HAS_STATUS]->(to);