#Select statements for Discourse PSQL server
function Get-ODBC-Data{
   param([string]$query=$(throw 'query is required.'))
   $conn = New-Object System.Data.Odbc.OdbcConnection
  $conn.ConnectionString= "Driver={PostgreSQL Unicode(x64)};Server=discourse;Port=5432;Database=discourse;Uid=user;Pwd=password;"
   $conn.open()
   $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)
   $ds = New-Object system.Data.DataSet
   (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
   $conn.close()
   $ds.Tables[0]
}

#INSERT/UPDATE/DELETE statements for Discourse PSQL server
function Set-ODBC-Data{
   param([string]$query=$(throw 'query is required.'))
  $conn = New-Object System.Data.Odbc.OdbcConnection
  $conn.ConnectionString= "Driver={PostgreSQL Unicode(x64)};Server=discourse;Port=5432;Database=discourse;Uid=user;Pwd=password;"
  $cmd = new-object System.Data.Odbc.OdbcCommand($query,$conn)
  $conn.open()
  $cmd.ExecuteNonQuery()
  $conn.close()
}

#create new users in discourse
function addUser($username, $name)
{
    $uri = "https://discourse/users.json?api_key=enter_key_here"
    $body = "username=" + $username + "&name=" + $name + "&email=" + $username + "@example.com&password=Password1!!&active=true"

    Invoke-WebRequest -Headers @{} -Method POST `
                  -Body $body -Uri $uri  `
                  -ContentType application/x-www-form-urlencoded
}

#return users in a group (name is group name, name1 is user full name, username is user's name)
function GetGroupUsers($group){

    $query = "SELECT groups.id, groups.name, users.name, users.username, users.email FROM groups, group_users, users WHERE (groups.id = group_users.group_id) AND (users.id = group_users.user_id) AND (groups.name = '" + $group + "')"
    return (Get-ODBC-Data -query $query)
}

function GetGroupID($group){

    $query = "SELECT groups.id FROM groups WHERE (LOWER(groups.name) = LOWER('" + $group + "'))"
    return (Get-ODBC-Data -query $query).id
}

function GetUserID($email){
    $query = "SELECT users.id FROM users WHERE (LOWER(users.email) = LOWER('" + $email + "'))"
    return (Get-ODBC-Data -query $query).id
}

function AddUserToGroup($email, $group)
{
    $valid = "true"
    $user_id = GetUserID $email
    $group_id = GetGroupID $group

    foreach($groupuser in (GetGroupUsers $group))
    {
      if($groupuser.email -eq $email)
      {
        $valid = "false"
      }
    }

    if($valid -eq "true")
    {
      $id = (Get-ODBC-Data -query "SELECT MAX(id) FROM group_users").max +1
      $query = "INSERT INTO group_users (id, group_id, user_id, created_at, updated_at, owner, notification_level) VALUES ('"+ $id +"','" + $group_id + "','" + $user_id + "','" + (Get-Date -Format "yyyy-MM-dd HH:mm:ss.fffff") + "','" + (Get-Date -Format "yyyy-MM-dd HH:mm:ss.fffff") + "','f','2')"
      return (Set-ODBC-Data $query)
      }
      else
      {
        return $user + " is already a member of " + $group
      }

}

function RemoveUserFromGroup($email, $group)
{
    $valid = "true"
    $user_id = GetUserID $email
    $group_id = GetGroupID $group

    foreach($groupuser in (GetGroupUsers $group))
    {
      if($groupuser.email -eq $email)
      {
        $valid = "false"
      }
    }

    if($valid -eq "false")
    {
      $id = (Get-ODBC-Data -query "SELECT MAX(id) FROM group_users").max +1
      $query = "DELETE from group_users WHERE (user_id = '" + $user_id + "') AND (group_id = '" + $group_id + "');"
      return (Set-ODBC-Data $query)
      }
      else
      {
        return $email + " is not a member of " + $group
      }

}

function UpdateUserTitles()
{
  $query = "SELECT name, username, email, title FROM users"
  $result = Get-ODBC-Data -query $query

  foreach($person in $result)
  {
    $email = $person.email
    $ad = Get-ADUser -SearchBase "ou=Users,dc=example,dc=com" -filter {mail -eq $email} -Properties title

    if(($ad.title -ne $person.title) -and $ad.title)
    {
      $query = "UPDATE users SET title = '" + $ad.title + "' where email = '" + $email + "'";
      Set-ODBC-Data $query
    }
  }
}