insert_data.sh

#! /bin/bash

if [[ $1 == "test" ]]
then
  PSQL="psql --username=postgres --dbname=worldcuptest -t --no-align -c"
else
  PSQL="psql --username=freecodecamp --dbname=worldcup -t --no-align -c"
fi

# Do not change code above this line. Use the PSQL variable above to query your database.

echo $($PSQL "TRUNCATE teams, games")

cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
  if [[ $YEAR != year ]]
  then
    # insert teams first
  
    WINNER_ID="$($PSQL "
      SELECT team_id FROM teams WHERE name = '$WINNER'
    ")"

    if [[ -z $WINNER_ID ]]
    then
      WINNER_INSERT_RESULT="$($PSQL "
        INSERT INTO teams(name) VALUES('$WINNER')
      ")"

      if [[ $WINNER_INSERT_RESULT == "INSERT 0 1" ]]
      then
        echo "Inserted into teams, $WINNER"
      fi

      WINNER_ID="$($PSQL "
        SELECT team_id FROM teams WHERE name = '$WINNER'
      ")"
    fi

    OPPONENT_ID="$($PSQL "
      SELECT team_id FROM teams WHERE name = '$OPPONENT'
    ")"

    if [[ -z $OPPONENT_ID ]]
    then
      OPPONENT_INSERT_RESULT="$($PSQL "
        INSERT INTO teams(name) VALUES('$OPPONENT')
      ")"

      if [[ $OPPONENT_INSERT_RESULT == "INSERT 0 1" ]]
      then
        echo "Inserted into teams, $OPPONENT"
      fi

      OPPONENT_ID="$($PSQL "
        SELECT team_id FROM teams WHERE name = '$OPPONENT'
      ")"
    fi

    # then insert game

    GAME_INSERT_RESULT="$($PSQL "
      INSERT INTO games(year, round, winner_id, opponent_id, winner_goals, opponent_goals)
      VALUES($YEAR, '$ROUND', $WINNER_ID, $OPPONENT_ID, $WINNER_GOALS, $OPPONENT_GOALS)
    ")"

    if [[ $GAME_INSERT_RESULT == "INSERT 0 1" ]]
    then
      echo "Inserted into games, $YEAR $WINNER - $OPPONENT"
    fi
  fi
done