# Import required modules for PowerShell Core compatibility
Import-Module Microsoft.Graph.Groups
Import-Module Microsoft.Graph.Users
Import-Module Microsoft.Graph.Authentication
Import-Module SqlServer

# Authentication - uncomment as needed
#Connect-MgGraph -Scopes "Group.Read.All", "User.Read.All", "GroupMember.Read.All"
#Connect-AzAccount

[string] $date = Get-Date -Format "yyyy-MM-dd HHmm"
$filename = ".\$date User group mappings.csv"


Function Get-RecursiveMgGroupMemberUsers{
    [cmdletbinding()]
    param(
       [parameter(Mandatory=$True,ValueFromPipeline=$true)]
       $MgGroup
    )
        Begin{
            # Check if Microsoft Graph is connected
            $context = Get-MgContext
            If(-not($context)){
                Write-Warning "Microsoft Graph not connected. Please run Connect-MgGraph first."
                throw "Microsoft Graph connection required"
            }
        }
        Process {
            Write-Verbose -Message "Enumerating $($MgGroup.DisplayName)"
            
            # Get group members using Microsoft Graph
            $Members = Get-MgGroupMember -GroupId $MgGroup.Id -All
            
            # Filter for user members and get full user details
            $UserMembers = @()
            $UserMemberIds = $Members | Where-Object {$_.AdditionalProperties["@odata.type"] -eq "#microsoft.graph.user"}
            
            foreach ($userMember in $UserMemberIds) {
                try {
                    $userDetails = Get-MgUser -UserId $userMember.Id -ErrorAction Stop
                    $UserMembers += $userDetails
                }
                catch {
                    Write-Warning "Could not retrieve user details for ID: $($userMember.Id)"
                }
            }
            
            # Process nested groups recursively
            $GroupMembers = $Members | Where-Object {$_.AdditionalProperties["@odata.type"] -eq "#microsoft.graph.group"}
            If($GroupMembers){
                foreach ($groupMember in $GroupMembers) {
                    try {
                        $nestedGroup = Get-MgGroup -GroupId $groupMember.Id -ErrorAction Stop
                        $UserMembers += Get-RecursiveMgGroupMemberUsers -MgGroup $nestedGroup
                    }
                    catch {
                        Write-Warning "Could not process nested group ID: $($groupMember.Id)"
                    }
                }
            }
        }
        end {
            Return $UserMembers
        }
    }

# Get SQL records
Write-Host ("Get SQL records") -foreground Yellow

$access_token_secure = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
$access_token = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($access_token_secure))

$signInConnectionString = "Data Source=signin-effectory.database.windows.net;Initial Catalog=SignIn;Persist Security Info=False;Encrypt=True;TrustServerCertificate=False;Application Name=CloudEngineering";
$eceConnectionString = "Data Source=c0m7f8nybr.database.windows.net;Initial Catalog='Effectory Extranet';Persist Security Info=False;Encrypt=True;TrustServerCertificate=False;Application Name=CloudEngineering";

$mappings = Invoke-Sqlcmd -Query "select gm.GroupId, gm.[Description] as GroupMappingName from GroupMappings gm" -ConnectionString $signInConnectionString -AccessToken $access_token
$mappingClaims = Invoke-Sqlcmd -Query "select distinct gm.GroupId, gm.[Description] as GroupMappingName, gmc.ClaimType, gmc.ClaimValue from GroupMappings gm inner join GroupMappingClaims gmc on gmc.GroupMappingId = gm.GroupMappingId" -ConnectionString $signInConnectionString -AccessToken $access_token

$accounts = Invoke-Sqlcmd -Query "Select distinct a.AccountId, a.Name, a.OwnerId from portal.Account  a inner join portal.Project p on p.AccountID = a.AccountID inner join portal.Survey s on p.ProjectID = s.ProjectID" -ConnectionString $eceConnectionString -AccessToken $access_token
$surveys = Invoke-Sqlcmd -Query "select s.SurveyID, s.Name from portal.Survey s" -ConnectionString $eceConnectionString -AccessToken $access_token
$owners = Invoke-Sqlcmd -Query "select o.Id as OwnerId, o.Name from portal.AccountOwner o" -ConnectionString $eceConnectionString -AccessToken $access_token

# map claims
Write-Host ("Map claims - will take a while") -foreground Yellow

class MappingItem {
    [string] $GroupId = ""
    [string] $GroupMappingName = ""
    [string] $ClaimType = ""
    [string] $ClaimValue = ""
    [string] $AccountID = ""
    [string] $AccountName = ""
    [string] $SurveyID = ""
    [string] $SurveyName = ""
    [string] $OwnerId = ""
    [string] $OwnerName = ""
}

[MappingItem[]]$mappingItems = @()
foreach ($mappingClaim in $mappingClaims) 
{
    Write-Host ("*") -foreground White -NoNewline

    if ($mappingClaim.ClaimValue -like 'o.*') 
    {
        
        $owner = $owners | Where-Object OwnerId -eq  ([int]$mappingClaim.ClaimValue.Replace('o.',''))

        [MappingItem] $mappingItem = [MappingItem]::new()
        $mappingItem.GroupId = $mappingClaim.GroupId
        $mappingItem.GroupMappingName = $mappingClaim.GroupMappingName
        $mappingItem.ClaimType = $mappingClaim.ClaimType
        $mappingItem.ClaimValue = $mappingClaim.ClaimValue
        $mappingItem.OwnerId = $owner.OwnerId
        $mappingItem.OwnerName = $owner.Name
    
        $mappingItems += $mappingItem

        # $ownerAccounts = $accounts | Where-Object OwnerId -eq $owner.OwnerId

        # foreach ($account in $ownerAccounts) 
        # {
        #   Write-Host (".") -foreground White -NoNewline

        #   [MappingItem] $mappingItem = [MappingItem]::new()
        #   $mappingItem.GroupId = $mappingClaim.GroupId
        #   $mappingItem.GroupMappingName = $mappingClaim.GroupMappingName
        #   $mappingItem.ClaimType = $mappingClaim.ClaimType
        #   $mappingItem.ClaimValue = $mappingClaim.ClaimValue
        #   $mappingItem.OwnerId = $owner.OwnerId
        #   $mappingItem.OwnerName = $owner.Name
        #   $mappingItem.AccountID = $account.AccountID
        #   $mappingItem.AccountName = $account.Name
      
        #   $mappingItems += $mappingItem
        # }
    } 
    else 
    {
        if ($mappingClaim.ClaimValue -like 'a.*') 
        {
            $account = $accounts | Where-Object AccountID -eq  ([int]$mappingClaim.ClaimValue.Replace('a.',''))

            [MappingItem] $mappingItem = [MappingItem]::new()
            $mappingItem.GroupId = $mappingClaim.GroupId
            $mappingItem.GroupMappingName = $mappingClaim.GroupMappingName
            $mappingItem.ClaimType = $mappingClaim.ClaimType
            $mappingItem.ClaimValue = $mappingClaim.ClaimValue
            $mappingItem.AccountID = $account.AccountID
            $mappingItem.AccountName = $account.Name
            $mappingItems += $mappingItem
        } 
        else 
        {
            if ($mappingClaim.ClaimValue -like 's.*') 
            {
                $survey = $surveys | Where-Object SurveyID -eq  ([int]$mappingClaim.ClaimValue.Replace('s.',''))

                [MappingItem] $mappingItem = [MappingItem]::new()
                $mappingItem.GroupId = $mappingClaim.GroupId
                $mappingItem.GroupMappingName = $mappingClaim.GroupMappingName
                $mappingItem.ClaimType = $mappingClaim.ClaimType
                $mappingItem.ClaimValue = $mappingClaim.ClaimValue
                $mappingItem.SurveyID = $survey.SurveyID
                $mappingItem.SurveyName = $survey.Name
                $mappingItems += $mappingItem
            }
        }
    }
}
Write-Host ("x") -foreground White 

# Create check list
Write-Host ("Create check list") -foreground Yellow

class UserMappingItem {
    [string] $GroupId = ""
    [string] $GroupMappingName = ""
    [string] $UserObjectId = ""
    [string] $UserDisplayName = ""
    [string] $UserMail = ""
    [string] $UserUserPrincipalName = ""
    [string] $UserJobTitle = ""
    [string] $UserDepartment = ""
    [string] $UserUserType = ""
    [string] $ClaimType = ""
    [string] $ClaimValue = ""
    [string] $AccountID = ""
    [string] $AccountName = ""
    [string] $SurveyID = ""
    [string] $SurveyName = ""
    [string] $OwnerId = ""
    [string] $OwnerName = ""
}

[UserMappingItem[]]$userMappingItems = @()
$a=0
$noMappings = $mappings.Length
[string] $itemDate

foreach($mapping in $mappings) {
    
    $u=0
    $a++    
    $itemDate = Get-Date -Format "yyyy-MM-dd HHmm"
    $mappingName = $mapping.GroupMappingName
    Write-Host ("[$itemDate] [$a/$noMappings] - Mapping '$mappingName'") -foreground Green

    #get users in mapping
    try {
        $group = Get-MgGroup -GroupId $mapping.GroupId -ErrorAction Stop
        $usersInMapping = Get-RecursiveMgGroupMemberUsers -MgGroup $group
    }
    catch {
        Write-Warning "Could not retrieve group with ID: $($mapping.GroupId). Error: $($_.Exception.Message)"
        continue
    }

    #get mapping claims
    $mappingItemsInMapping = $mappingItems | Where-Object GroupId -eq $mapping.GroupId

    $noUsers = $usersInMapping.Length
    foreach($user in $usersInMapping) {
        $u++
        $itemDate = Get-Date -Format "yyyy-MM-dd HHmm"
        $userName = $user.DisplayName
        Write-Host ("[$itemDate] [$a/$noMappings] - Mapping '$mappingName' - [$u/$noUsers] User '$userName'") -foreground White
    
        foreach($mappingItem in $mappingItemsInMapping) {

            [UserMappingItem] $userMappingItem = [UserMappingItem]::new()
            $userMappingItem.GroupId = $mappingItem.GroupId
            $userMappingItem.GroupMappingName = $mappingItem.GroupMappingName

            # Microsoft Graph user properties (property names are the same)
            $userMappingItem.UserObjectId = $user.Id
            $userMappingItem.UserDisplayName = $user.DisplayName
            $userMappingItem.UserMail = $user.Mail
            $userMappingItem.UserUserPrincipalName = $user.UserPrincipalName
            $userMappingItem.UserJobTitle = $user.JobTitle
            $userMappingItem.UserDepartment = $user.Department
            $userMappingItem.UserUserType = $user.UserType

            $userMappingItem.ClaimType = $mappingItem.ClaimType
            $userMappingItem.ClaimValue = $mappingItem.ClaimValue
            $userMappingItem.AccountID =  $mappingItem.AccountID 
            $userMappingItem.AccountName = $mappingItem.AccountName
            $userMappingItem.SurveyID = $mappingItem.SurveyID
            $userMappingItem.SurveyName = $mappingItem.SurveyName
            $userMappingItem.OwnerId = $mappingItem.OwnerId
            $userMappingItem.OwnerName = $mappingItem.OwnerName

            $userMappingItems += $userMappingItem
        }

        $userMappingItems | Export-Csv -Path $filename -Append -NoTypeInformation
    }
}