# Add Personnel to database;
# This script describes how to load Personnel data into the CTFS/ForestGeo database 
# using a csv file with the columns matching the table below. 
# If a Role already exists please use the Role from the database making sure that
# it exactly matches in spelling and capitalisation. 

# Note The database does not permit more than one person with exactly the same 
#  first and last name.  Please add initials to the end of the first name if needed. 

# Shameema Esufali June 16 2020

# 1. Load the data file *********************************************************

CREATE TABLE stagePersonnel(
FirstName CHAR(32),
LastName CHAR(32),
Role CHAR(128)
)ENGINE=MyISAM;

# Format for Windows file references. 
# LOAD DATA LOCAL INFILE '..\datafiles\personneldata.csv' INTO TABLE stagePersonnel fields

LOAD DATA LOCAL INFILE '../datafiles/personneldata.csv' INTO TABLE stagePersonnel fields
TERMINATED BY ',' ENCLOSED BY '' IGNORE 1 LINES (FirstName, LastName, Role);


# *******************************************************************************
# 2. Add people *****************************************************************
INSERT into Personnel (firstname, lastname) 
 SELECT DISTINCT firstname,lastname FROM stagePersonnel a
 WHERE NOT EXISTS 
  (SELECT 1 FROM  Personnel b 
   WHERE a.firstname=b.firstname and a.lastname=b.lastname);
   
ALTER TABLE stagePersonnel ADD COLUMN PersonnelID int;
UPDATE stagePersonnel a, Personnel b SET a.PersonnelID=b.PersonnelID 
 WHERE a.firstname=b.firstname and a.lastname=b.lastname ;
 
 

# *******************************************************************************
# 3. Add Roles ******************************************************************
INSERT INTO RoleReference (Description) 
 SELECT DISTINCT Role a from stagePersonnel a
 WHERE NOT EXISTS 
  (SELECT 1 FROM RoleReference b 
   WHERE a.Role=b.Description);
   
ALTER TABLE stagePersonnel ADD COLUMN RoleID int;  
UPDATE stagePersonnel a, RoleReference b SET a.RoleID=b.RoleID 
 WHERE a.Role=b.Description ;
 
 

# *******************************************************************************
# 4. Create links between people and their Roles ********************************
INSERT INTO PersonnelRole (PersonnelID,RoleID) 
 SELECT DISTINCT PersonnelID,RoleID from stagePersonnel a
 WHERE NOT EXISTS 
  (SELECT 1 FROM PersonnelRole b 
   WHERE a.PersonnelID=b.PersonnelID AND a.RoleID=b.RoleID);
 
# ******************************************************************************* 


