[CmdletBinding()]
param(
    [Parameter(Mandatory=$false)]
    [string]$Email = "test.com"
)
Begin {
    $modules = @(
        "secretserver"
        "automation"
    )
    # Load  modules if not present
    if (-not (get-module $modules)) {
        Import-AHModule $modules -ErrorAction Stop
    }
    Import-Module "SqlServer","dbatools","NetScaler" -Verbose:$false -ErrorAction Stop

    # Name of SQL Instance where database is located
    $SqlInstance = "dbserv"

    # Name of the database
    $DbName = "test"

    # Name of the schema in which the above database is in
    $Schema = "dbo"

    # connect to NetScalers using the login nsexsremetrics
    if ($env:Winusername) {
        Write-verbose "$(Get-date): Creating Secret Server Login" -Verbose
        $SecurePassword = ConvertTo-SecureString $env:WinPassword -AsPlainText -Force -ErrorAction Stop
        $SSCred = New-Object System.Management.Automation.PSCredential ($env:WinUserName, $SecurePassword) -ErrorAction Stop
    }
    $NScred = Get-SecretServerCredential -SamAccountName "nsexs" -OperatorCredentials $SSCred -ErrorAction Stop -Verbose:$false

    # Get Primary NetScalers
    $Netscalers = Get-Primary | select-object -ExpandProperty DNSName
    
    $props =    [PSCustomObject]@{Table = "NetScaler_Servicegroup";NSStatType = "servicegroup";NSStatProps = ("name,servicegroupname,effectivestate,servicetype,state" -split ',')},
                [PSCustomObject]@{Table = "NetScaler_service";     NSStatType = "service";     NSStatProps = ("name,primaryipaddress,servicetype,state" -split ',')},
                [PSCustomObject]@{Table = "NetScaler_stat";        NSStatType = "lbvserver";   NSStatProps = ("name,primaryipaddress,type,state" -split ',')}
}
Process {
    # Collect information from three sets of parameters, for all servers
    ForEach ($p in $props){
        # Connect to each netscaler and insert its stats into the database table
        foreach ($NetScaler in $NetScalers) {
            Connect-NetScaler -Hostname $NetScaler -Credential $NScred
            Get-NSStat -Type $p.NSStatType | 
                Where-object { $_.name -notlike "*test*" } |
                    Select-Object $p.NSStatProps |
                        Write-SqlTableData -ServerInstance $SqlInstance -DatabaseName $DbName -SchemaName $Schema -TableName $p.Table -Force
        }
    }
}