




#####################################
#
#   *** DEPRECATED ***
#
#   DO NOT USE, MODIFY OR MAINTAIN THIS FILE UNLESS ABSOLUTELY NECESSARY
#
#####################################





$Global:CEDAR_VERSION = "2025r3"
$Global:CEDAR_DATABASES = "CEDAR_LIVE", "CISA_LIVE"
$Global:CEDAR_DATABASES_ACTION_TEST = $false
$Global:CEDAR_SQL_INSTALL_DIR = "C:\SQL2022Media"
$Global:CEDAR_SQL_SERVER_VERSION = "2022"
$Global:CEDAR_SQL_SERVER_ID = "16"
$Global:CEDAR_RELEASE_DOMAIN = "https://releases.cedarportal.co.uk"

function global:FN_CEDAR_REFRESH_SELF
{
    Write-Host ""
    Write-Host "Reloading"
    Write-Host ".\Scripts\install.ps1"
    Write-Host ""

    $CWD = CWD

    try
    {
        $con = (New-Object System.Net.WebClient).DownloadString('https://releases.cedarportal.co.uk/install.ps1.txt')
        iex $con

        if (Test-Path -Path "$CWD/Scripts/install.ps1" -Type Leaf)
        {
            Write-Host "Replacing $CWD/Scripts/install.ps1"
            Set-Content -Path "$CWD/Scripts/install.ps1" -Value $con
        }
    }
    catch
    {
    }
}

function global:CEDAR_DEPS
{
    CEDAR_FN_PS_DEPS

    FN_GENERAL_DEPENDENCIES
    FN_DBATOOLS
}

function global:FN_CEDAR_STEP_1
{
    FN_GENERAL_DEPENDENCIES
    FN_DBATOOLS
}

function global:FN_CEDAR_STEP_IIS
{
    CEDAR_VARS_MACHINE_SET
    CEDAR_INSTALL_IIS
    CEDAR_INSTALL_FILES
    CEDAR_INSTALL_SCRIPTS_FILES
    CEDAR_INSTALL_SCRIPTS_SETUP_PROD
    CEDAR_INSTALL_SCRIPTS_SETUP_PHP_PERM
}

function global:FN_CEDAR_STEP_SQL_SERVER
{
    CEDAR_FN_DATABASE_LOCATION_CHECK
    CEDAR_FN_SQL_SERVER_DEPS
    CEDAR_FN_SQL_SERVER_DOWNLOAD_STD
    CEDAR_FN_SQL_SERVER_INSTALL
}

function global:FN_CEDAR_STEP_SQL_DB
{
    CEDAR_PATH_CHECK_OR_EXTRACT

    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        CEDAR_FN_DATABASE_LOCATION_CHECK
        CEDAR_LINKED_SERVER
        CEDAR_FN_SQL_DB_FRESH_CREATE
        CEDAR_FN_SQL_DB_FRESH
        CEDAR_CISA_TEMPLATE_FETCH
        CEDAR_CISA_TEMPLATE_INSTALL
    }
}

function global:FN_CEDAR_STEP_SQL_DB_LOW
{
    CEDAR_PATH_CHECK_OR_EXTRACT

    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        CEDAR_FN_DATABASE_LOCATION_CHECK
        CEDAR_FN_SQL_DB_FRESH
        CEDAR_CISA_TEMPLATE_FETCH
        CEDAR_CISA_TEMPLATE_INSTALL
    }
}

function global:FN_CEDAR_STEP_FINAL
{
    CEDAR_VARS_RESTORE
    CEDAR_CD_LOCATION
    CEDAR_FN_DATABASE_LOCATION_CHECK
    CEDAR_INSTALL_SCRIPTS_PROD
}

function global:CEDAR_PATH_CHECK
{
    return (Test-Path -Path "")
}

function global:IsAdministrator
{
    return (New-Object Security.Principal.WindowsPrincipal([Security.Principal.WindowsIdentity]::GetCurrent())).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)
}

function global:IsAdministratorOrFatal
{
    $IsAdministrator = (IsAdministrator)
    if ($false -eq $IsAdministrator)
    {
        throw "You are not an Administrator. Reload PowerShell as Administrator"
    }
}

function global:CEDAR_PATH_CHECK_OR_EXTRACT
{
    if ($null -eq $CEDAR_INSTALL_DIRS) {
        CEDAR_VARS_RESTORE
    }

    if ($null -eq $CEDAR_INSTALL_DIRS) {
        CEDAR_PATH_EXTRACT
    }
}

function global:CEDAR_PATH_EXTRACT
{
    $CWD = (Get-Location)
    if (-Not(Test-Path -Path "$CWD/")) {
        throw "Not a CEDAR folder"
    }

    $Global:CEDAR_INSTALL_DIRS = Split-Path -Path (Get-Location) -Leaf
    $Global:CEDAR_INSTALL_BASE = (Get-Location) | Split-Path

    Write-Host "CEDAR_INSTALL_DIRS: $CEDAR_INSTALL_DIRS"
    Write-Host "CEDAR_INSTALL_BASE: $CEDAR_INSTALL_BASE"

    if (-Not(IsAdministrator)) {
        return
    }

    [Environment]::SetEnvironmentVariable('CEDAR_INSTALL_BASE', $CEDAR_INSTALL_BASE, 'Machine')
    [Environment]::SetEnvironmentVariable('CEDAR_INSTALL_DIRS', $CEDAR_INSTALL_DIRS -join ",", 'Machine')
}

function global:CEDAR_PATH_ASK
{
    Write-Host "******************************************************"
    Write-Host "* Set the path for CEDAR installs                    *"
    Write-Host "* Set the CEDAR sites to be created                  *"
    Write-Host "******************************************************"
    Write-Host "* This information will be used to create the folder *"
    Write-Host "* structure in the next step of the process          *"
    Write-Host "******************************************************"

    Write-Host "Where should CEDAR sites be created? For example C:\inetpub"
    $tmpBase = Read-Host "If you leave this blank C:\ will be used by default "
    if ("" -eq $tmpBase) {
        $Global:CEDAR_INSTALL_BASE = "C:\"
    } else {
        if (-Not(Test-Path -Path $tmpBase -Type Container)) {throw "Path not valid" }
        if ($false -eq (Test-Write $tmpBase)) {throw "Error: Not writeable"}

        $Global:CEDAR_INSTALL_BASE = ($tmpBase.TrimEnd('\').TrimEnd('/')) + "/"
    }

    Write-Host "What CEDAR sites do you need? If you require more than one, separate these with a comma."
    Write-Host "For example CEDAR_LIVE,CEDAR_TEST,CEDAR_STAGE etc."
    $tmpSite = Read-Host "If you leave this blank, a single site CEDAR_LIVE will be created "
    if ("" -eq $tmpSite) {
        $Global:CEDAR_INSTALL_DIRS = ,"CEDAR_LIVE"
    } else {
        $Global:CEDAR_INSTALL_DIRS = $tmpSite -split (',')
    }

    Write-Host "CEDAR will be installed in:"
    ForEach ($site in $CEDAR_INSTALL_DIRS) {
        Write-Host "`t $CEDAR_INSTALL_BASE$site"
    }
    Write-Host ""

    if (-Not(IsAdministrator)) {
        return
    }

    [Environment]::SetEnvironmentVariable('CEDAR_INSTALL_BASE', $CEDAR_INSTALL_BASE, 'Machine')
    [Environment]::SetEnvironmentVariable('CEDAR_INSTALL_DIRS', $CEDAR_INSTALL_DIRS -join ",", 'Machine')
}

function global:CEDAR_PS_VARS_SETUP
{
    CWD
}

function global:CWD
{
    $global:CWD = [string](Get-Location)

    return $CWD
}

function global:ENV_READ
{
    CEDAR_PS_VARS_SETUP

    if (-Not(Test-Path -Path "$CWD/Config/.env" -Type Leaf))
    {
        return
    }

    try
    {
        $ConfigEnv = (Get-Content "$CWD/Config/.env" | Where-Object { $_ -match "=" }) | ForEach-Object { $_ -replace '"', '' } | ConvertFrom-StringData
        foreach ($h in $ConfigEnv.Keys)
        {
            Set-Variable -Name $h -Value $ConfigEnv.$h -Scope Global
        }
    }
    catch
    {
    }
}

function global:CEDAR_VARS_MACHINE_SET
{
    Get-Variable -Name 'CEDAR_INSTALL_DIRS' -ErrorAction Stop

    [Environment]::SetEnvironmentVariable('CEDAR_INSTALL_DIRS', $CEDAR_INSTALL_DIRS -join ",", 'Machine')
    [Environment]::SetEnvironmentVariable('CEDAR_INSTALL_BASE', $CEDAR_INSTALL_BASE, 'Machine')
    [Environment]::SetEnvironmentVariable('CEDAR_INSTALL_USERS', $CEDAR_INSTALL_USERS -join ",", 'Machine')

    Write-Host "Variables set"
}

function global:CEDAR_VARS_RESTORE
{
    $Global:CEDAR_INSTALL_DIRS = ([Environment]::GetEnvironmentVariable('CEDAR_INSTALL_DIRS','Machine') -split ",")
    $Global:CEDAR_INSTALL_BASE = [Environment]::GetEnvironmentVariable('CEDAR_INSTALL_BASE','Machine')
    $Global:CEDAR_INSTALL_USERS = ([Environment]::GetEnvironmentVariable('CEDAR_INSTALL_USERS','Machine') -split ",")
    Write-Host "Variables restored"
    Write-Host "CEDAR_INSTALL_BASE"
    Write-Host $CEDAR_INSTALL_BASE
    Write-Host "CEDAR_INSTALL_DIRS"
    Write-Host $CEDAR_INSTALL_DIRS
    Write-Host "CEDAR_INSTALL_USERS"
    Write-Host $CEDAR_INSTALL_USERS
}

function global:INSTALL_CHOCO
{
    Write-Host "Check Chocolatey installation"

    Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://community.chocolatey.org/install.ps1'))
    choco feature enable -n allowGlobalConfirmation
}

function global:CEDAR_FN_PS_DEPS
{
    IsAdministratorOrFatal

    Write-Host "Setting security policy"

    #    Register-PSRepository -Default -ErrorAction SilentlyContinue
    Set-PSRepository -Name PSGallery -InstallationPolicy Trusted
    Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy ByPass

    INSTALL_CHOCO

    Write-Host "Install Dependencies"

    choco install nuget.commandline --no-progress -y
    choco install powershell-core --no-progress -y

    RefreshEnv.cmd

    # Allow PowerShell scripts to be run
}

function global:CEDAR_FN_PS_DEPS_DEV
{
    Set-PSRepository -Name PSGallery -InstallationPolicy Trusted

    choco install git --no-progress -y
    choco install git-credential-manager-for-windows --no-progress -y
    choco install azure-cli --no-progress -y
    choco install nodejs --no-progress -y
    choco install python --no-progress -y
    choco install k6 --no-progress -y
    choco install visualstudio2019buildtools
    choco install visualcpp-build-tools
    RefreshEnv.cmd
}

function global:FN_ZOHO_ASSIST
{
    Invoke-WebRequest "https://assist.zoho.eu/api/v2/install_urs?encapiKey=yA6KbnsPvlimxWhTRUc70sKNoI40%2Bfw%2Fj3i%2B7yy2e5d1I9bh26FsghZldIa%2BJjvd3dPX4flYP98Tc9%2B974xcfsM9MtEALpTGTuv4P2uV48xhs92FddQnhJyoA7EQFqVIdBs%3D&type=2&file_type=msi" -Out "ZA_Access_My_Department.zip"
    Expand-Archive ZA_Access_My_Department.zip
    msiexec /i .\ZA_Access_My_Department\ZA_Access.msi /quiet /qn /lv SILENT=TRUE
}

function global:FN_DBATOOLS
{
    Write-Host "Installing DBATools. This may take a minute..."

    Install-Module -Name dbatools -RequiredVersion 2.0.0-preview2 -AllowPrerelease -SkipPublisherCheck -Confirm:$false -Repository PSGallery

    Write-Host "Setting SQL Encryption library to allow non-SSL"

    Set-DbatoolsConfig -Name Import.EncryptionMessageCheck -Value $false -PassThru | Register-DbatoolsConfig
    Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register
    Set-DbatoolsConfig -FullName sql.connection.encrypt -Value $false -Register
}

function global:FN_GENERAL_DEPENDENCIES
{
    Install-Module -Name PSWindowsUpdate

    choco install sql-server-management-studio --no-progress -y
    choco install notepadplusplus --no-progress -y
    choco install 7zip --no-progress -y
    choco install dotnet4.7.2 --no-progress -y

    RefreshEnv.cmd
}

function global:CEDAR_FN_DATABASE_LOCATION_CHECK
{
    ENV_READ

    $Global:MIS_DB_HOST = $null
    $Global:MIS_DB_INSTANCE = $null
    $Global:CEDAR_DB_HOST = $null
    $Global:CEDAR_DB_INSTANCE = $null

    if ($null -ne $MIS_SERVER)
    {
        $MIS_SERVER = $MIS_SERVER -replace ('\\', '\')


        if (($MIS_SERVER -match '\\') -eq $true)
        {
            $Global:MIS_DB_HOST = ($MIS_SERVER.split('\')[0])
            $Global:MIS_DB_INSTANCE = ($MIS_SERVER.split('\')[-1])
        }
        else
        {
            $Global:MIS_DB_HOST = $MIS_SERVER
            $Global:MIS_DB_INSTANCE = ''
        }
    }

    if ($null -ne $DB_SERVER)
    {
        if (($DB_SERVER -match '\\') -eq $true)
        {

            $Global:CEDAR_DB_HOST = ($DB_SERVER.split('\')[0])
            $Global:CEDAR_DB_INSTANCE = ($DB_SERVER.split('\')[-1])
        }
        else
        {
            $Global:CEDAR_DB_HOST = $DB_SERVER
            $Global:CEDAR_DB_INSTANCE = ''
        }
    }

    if ($null -ne $Global:DB_DATABASE -AND ($null -ne $Global:CISA_DATABASE))
    {
        [System.Collections.ArrayList]$Global:CEDAR_DATABASES = @()
        $Global:CEDAR_DATABASES += $Global:DB_DATABASE
        $Global:CEDAR_DATABASES += $Global:CISA_DATABASE
    } elseif (($null -ne $Global:DB_DATABASE) -AND ($Global:DB_DATABASE -match '_\w+$'))
    {
        $suffix = ($Global:DB_DATABASE -replace '\w+_(\w+)', '$1')

        [System.Collections.ArrayList]$Global:CEDAR_DATABASES = @()
        $Global:CEDAR_DATABASES += "CEDAR_$suffix"
        $Global:CEDAR_DATABASES += "CISA_$suffix"
    }

    $DB_PASS_CRED = ConvertTo-SecureString -String $Global:DB_PWD -AsPlainText -Force
    $Global:DB_CREDENTIALS = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $Global:DB_UID, $DB_PASS_CRED

    if ($null -eq $CEDAR_DB_HOST -OR $null -eq $CEDAR_DB_INSTANCE)
    {
        CEDAR_FN_DATABASE_LOCATION
    }
    else
    {
        CEDAR_FN_DATABASES_PRINT
    }
}

function global:CEDAR_FN_DATABASE_LOCATION
{
    Write-Host ''
    Write-Host 'Define the location of the CEDAR + MIS Databases'
    Write-Host ''

    $Global:CEDAR_DB_HOST = 'localhost'
    $GLobal:CEDAR_DB_INSTANCE = 'MSSQLSERVER'

    $CEDAR_DB_INSTANCE = Read-Host "SQL Server Instance name for CEDAR? (localhost\MSSQLSERVER) "
    if (($CEDAR_DB_INSTANCE -match '\\') -eq $true)
    {
        $CEDAR_DB_HOST = ($CEDAR_DB_INSTANCE.split('\\')[0])
        $CEDAR_DB_INSTANCE = ($CEDAR_DB_INSTANCE.split('\\')[-1])
    }

    $Global:MIS_DB_HOST = 'localhost'
    $GLobal:MIS_DB_INSTANCE = ''

    $MIS_DB_INSTANCE = Read-Host "SQL Server Instance name for MIS? (localhost\MSSQLSERVER) "
    if (($MIS_DB_INSTANCE -match '\\') -eq $true)
    {
        $MIS_DB_HOST = ($MIS_DB_INSTANCE.split('\\')[0])
        $MIS_DB_INSTANCE = ($MIS_DB_INSTANCE.split('\\')[-1])
    }

    $CEDAR_DB_SUFFIX = Read-Host "CEDAR Database suffix? (e.g. TEST) "
    $CEDAR_DB_SUFFIX = $CEDAR_DB_SUFFIX.ToUpper()

    If (('' -eq $CEDAR_DB_SUFFIX) -OR ('LIVE' -eq $CEDAR_DB_SUFFIX))
    {
        $T_INSTALL_TEST = Read-Host "Do you also want to install ? CEDAR_TEST (Y/N) "
        if ('Y' -eq $T_INSTALL_TEST.ToUpper())
        {
            $CEDAR_DATABASES += 'CEDAR_TEST'
            $CEDAR_DATABASES += 'CISA_TEST'
            $CEDAR_DATABASES_ACTION_TEST = $true
        }
    }

    CEDAR_FN_DATABASES_PRINT
}

function global:CEDAR_FN_DATABASES_PRINT
{
    Write-Host ""
    Write-Host "DB_SERVER: $DB_SERVER"
    Write-Host "CEDAR_DB_HOST: $CEDAR_DB_HOST"
    Write-Host "CEDAR_DB_INSTANCE: $CEDAR_DB_INSTANCE"
    Write-Host "CEDAR_DB: $DB_DATABASE"
    Write-Host "CISA_DB: $CISA_DATABASE"

    Write-Host ""
    Write-Host "MIS_SERVER: $MIS_SERVER"
    Write-Host "MIS_DB_HOST: $MIS_DB_HOST"
    Write-Host "MIS_DB_INSTANCE: $MIS_DB_INSTANCE"
    Write-Host "MIS_DATABASE: $MIS_DATABASE"

    Write-Host ""
    Write-Host "CEDAR Databases:"
    Write-Host $CEDAR_DATABASES
    Write-Host ""
}

function global:CEDAR_FN_SQL_SERVER_DEPS
{
    $ProgressPreference = 'SilentlyContinue'

    # SQL Server dependencies
    Invoke-WebRequest "https://aka.ms/vs/17/release/vc_redist.x64.exe" -Out "vc_redist.x64.exe"
    .\vc_redist.x64.exe /install /quiet /norestart

    # OLE DB Drivers 19.2
    Invoke-WebRequest "https://download.microsoft.com/download/a/9/a/a9aa45ec-02cb-4647-b78c-ae780c6bab63/en-US/19.2.0.0/x64/msoledbsql.msi" -Out "msoledbsql.msi"
    .\msoledbsql.msi /quiet /qn

    #ODBC 17
    Invoke-WebRequest "https://download.microsoft.com/download/6/f/f/6ffefc73-39ab-4cc0-bb7c-4093d64c2669/en-US/17.10.3.1/x64/msodbcsql.msi" -Out "msodbcsql-17.10.msi"
    .\msodbcsql-17.10.msi /passive

    #ODBC 18
    Invoke-WebRequest "https://download.microsoft.com/download/c/5/4/c54c2bf1-87d0-4f6f-b837-b78d34d4d28a/en-US/18.2.1.1/x64/msodbcsql.msi" -Out "msodbcsql-18.2.1.msi"
    .\msodbcsql-18.2.1.msi /passive
}

function global:CEDAR_FN_SQL_SERVER_DOWNLOAD_DEV
{
    [void](New-Item -Path $CEDAR_SQL_INSTALL_DIR -ItemType Directory -ErrorAction SilentlyContinue -Force)

    Set-Location $CEDAR_SQL_INSTALL_DIR

    Invoke-WebRequest -Uri "https://download.microsoft.com/download/c/c/9/cc9c6797-383c-4b24-8920-dc057c1de9d3/SQL2022-SSEI-Dev.exe" -OutFile "$CEDAR_SQL_INSTALL_DIR\SQL2022-SSEI-Dev.exe"

    &"$CEDAR_SQL_INSTALL_DIR\SQL2022-SSEI-Dev.exe" /Action=Download /MediaPath=$CEDAR_SQL_INSTALL_DIR /MediaType=CAB /ENU /QUIET

    &"$CEDAR_SQL_INSTALL_DIR\SQLServer2022-DEV-x64-ENU.exe" /extract:"$CEDAR_SQL_INSTALL_DIR\SQLServer2022-DEV-x64-ENU" /q

    CEDAR_FN_SQL_SERVER_SET_DIR

    CEDAR_CD_LOCATION
}

function global:CEDAR_FN_SQL_SERVER_DOWNLOAD_STD
{
    [void](New-Item -Path $CEDAR_SQL_INSTALL_DIR -ItemType Directory -ErrorAction SilentlyContinue -Force)

    Set-Location $CEDAR_SQL_INSTALL_DIR

    Invoke-WebRequest -Uri "https://download.microsoft.com/download/4/1/b/41b9a8c3-c2b4-4fcc-a3d5-62feed9e6885/SQL2022-SSEI-Eval.exe" -OutFile "$CEDAR_SQL_INSTALL_DIR\SQL2022-SSEI-Eval.exe"

    &"$CEDAR_SQL_INSTALL_DIR\SQL2022-SSEI-Eval.exe" /Action=Download /MediaPath=$CEDAR_SQL_INSTALL_DIR /MediaType=CAB /ENU /QUIET

    &"$CEDAR_SQL_INSTALL_DIR\SQLServer2022-x64-ENU.exe" /extract:"$CEDAR_SQL_INSTALL_DIR\SQLServer2022-STD-x64-ENU" /q

    CEDAR_FN_SQL_SERVER_SET_DIR

    CEDAR_CD_LOCATION
}

function global:CEDAR_FN_SQL_SERVER_SET_DIR
{
    $dir = [string](Get-ChildItem -Directory $CEDAR_SQL_INSTALL_DIR |Select-Object -First 1)
    Set-DbatoolsConfig -Name Path.SQLServerSetup -Value "$dir"
}

function global:CEDAR_FN_SQL_SERVER_INSTALL
{
    Set-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager' -Name 'PendingFileRenameOperations' -Value ''

    CEDAR_FN_DATABASE_LOCATION_CHECK
    CEDAR_FN_SQL_SERVER_SET_DIR

    Set-DbatoolsConfig -FullName Logging.MaxLogFileAge -Value 3

    Write-Host "Check MIS Instance exists - create if not"
    IF ($null -ne $MIS_DB_HOST -And $null -ne $MIS_DB_INSTANCE -And $null -eq (Get-DbaInstanceInstallDate -SqlInstance "$MIS_SERVER" -ErrorAction SilentlyContinue))
    {
        Install-DbaInstance -Version $Global:CEDAR_SQL_SERVER_VERSION -InstanceName "$MIS_DB_INSTANCE" -Feature Engine -AuthenticationMode Mixed -Restart -Confirm:$false

        if ($MIS_SYSTEM -eq 'UNITE')
        {
            Set-DbaSpConfigure -SqlInstance "$MIS_SERVER" -Name 'contained database authentication' -Value 1
        }
    }

    Write-Host "Check CEDAR Instance exists - create if not"
    IF ($null -ne $CEDAR_DB_HOST -And $null -ne $CEDAR_DB_INSTANCE -And $null -eq (Get-DbaInstanceInstallDate -SqlInstance "$DB_SERVER" -ErrorAction SilentlyContinue))
    {
        Install-DbaInstance -Version $Global:CEDAR_SQL_SERVER_VERSION -InstanceName "$CEDAR_DB_INSTANCE" -Feature Engine -AuthenticationMode Mixed -Restart -Confirm:$false
    }
}

function global:CEDAR_SQL_SERVER_CONFIG
{
    ENV_READ

    Set-DbaNetworkConfiguration "$DB_SERVER" -Confirm:$false -EnableProtocol TcpIp
    Set-DbaNetworkConfiguration "$DB_SERVER" -Confirm:$false -EnableProtocol NamedPipes
    Restart-DbaService "$DB_SERVER" -Type Engine
}

function global:CEDAR_FN_SQL_SERVER_USER_MASTER
{
    Write-Host "Adding main cedar login"
    $cdrpwd = ConvertTo-SecureString -String $DB_PWD -AsPlainText -Force
    New-DbaLogin -SqlInstance "$DB_SERVER" -Login $DB_UID -SecurePassword $cdrpwd
}

function global:CEDAR_FN_SQL_SERVER_USERS
{
    ENV_READ

    ForEach ($db in $CEDAR_DATABASES)
    {
        Write-Host "Add cedar user to $db"
        New-DbaDbUser -SqlInstance "$DB_SERVER" -Database $db -Login $DB_UID -Force
        Add-DbaDbRoleMember -SqlInstance "$DB_SERVER" -Database $db -User $DB_UID -Role "db_owner" -Confirm:$false
    }
}

function global:CEDAR_FN_SQL_DB_BACKUP_CEDAR
{
    $bakInstance = 'MSSQLSERVER'
    if ($null -ne $CEDAR_DB_INSTANCE)
    {
        $bakInstance = $CEDAR_DB_INSTANCE
    }

    $dbBakFolder = "C:\Program Files\Microsoft SQL Server\MSSQL$CEDAR_SQL_SERVER_ID.$bakInstance\MSSQL\Backup"

    $srcFolder = "$HOME\Downloads"
    [void](New-Item -Path $dbBakFolder -Type Directory -Force -ErrorAction Continue)
    Move-Item -Path "$srcFolder/*.bak" -Destination $dbBakFolder

    ForEach ($db in $CEDAR_DATABASES)
    {
        Restore-DbaDatabase -SqlInstance "$DB_SERVER" -Path "$dbBakFolder\$db.bak" -DatabaseName $db
    }

    CEDAR_FN_SQL_SERVER_USERS
}

function global:CEDAR_FN_SQL_DB_BACKUP_UNITE
{
    $bakInstance = 'MSSQLSERVER'
    if ($null -ne $CEDAR_DB_INSTANCE)
    {
        $bakInstance = $CEDAR_DB_INSTANCE
    }

    $dbBakFolder = "C:\Program Files\Microsoft SQL Server\MSSQL$CEDAR_SQL_SERVER_ID.$bakInstance\MSSQL\Backup"

    $srcFolder = "$HOME\Downloads"

    [void](New-Item -Path $dbBakFolder -Type Directory -Force -ErrorAction Continue)
    Move-Item -Path "$srcFolder/*.bak" -Destination $dbBakFolder

    ForEach ($db in $MIS_DATABASES_RESTORE)
    {
        Restore-DbaDatabase -SqlInstance "$MIS_SERVER" -Path "$dbBakFolder\$db.bak" -DatabaseName $db
    }
}

function global:CEDAR_FN_SQL_DB_FRESH_CREATE
{
    CEDAR_FN_SQL_SERVER_USER_MASTER

    ForEach ($db in $CEDAR_DATABASES)
    {
        Write-Host "Create DB | $db"
        if ($null -ne $DB_ALTER_USE_WINDOWS_USER -AND $DB_ALTER_USE_WINDOWS_USER -eq 1) {
            New-DbaDatabase -SqlInstance "$DB_SERVER" -Owner $DB_UID -Name $db -Collation "Latin1_General_CI_AS"
        } else {
            New-DbaDatabase -SqlInstance "$DB_SERVER" -Owner $DB_UID -Name $db -Collation "Latin1_General_CI_AS" -SqlCredential $DB_CREDENTIALS
        }
    }
}

function global:CEDAR_FN_SQL_DB_FRESH
{
    if ((Test-Path -Type Leaf -Path "$CWD/Scripts/fresh-db.ps1") -And (-Not((Test-Path -Type Leaf -Path "$CWD/fresh.cedar.sql"))))
    {
        Write-Host "Downloading Fresh files"
        &"$CWD/Scripts/fresh-db.ps1"
    }

    Get-Variable "CISA_DATABASE" -ErrorAction Stop
    Get-Variable "DB_DATABASE" -ErrorAction Stop

    Write-Host "SQL : fresh.SETUP.sql - Setup system"
    DB_QUERY_FILE $DB_SERVER $Global:DB_DATABASE "fresh.SETUP.sql"
    Write-Host "SQL : fresh.cisa.sql - CISA Schema"
    DB_QUERY_FILE $DB_SERVER $Global:CISA_DATABASE "fresh.cisa.sql"
    Write-Host "SQL : fresh.cedar.sql - CEDAR Schema"
    DB_QUERY_FILE $DB_SERVER $Global:DB_DATABASE "fresh.cedar.sql"
    Write-Host "SQL : fresh.INTEGRATION.sql - Scheduled Jobs"
    DB_QUERY_FILE $DB_SERVER $Global:DB_DATABASE "fresh.INTEGRATION.sql"
    Write-Host "SQL : fresh.TASKS.sql - Integration Stub"
    DB_QUERY_FILE $DB_SERVER $Global:DB_DATABASE "fresh.TASKS.sql"
    Write-Host "SQL : fresh.DATA.sql - Base DATA"
    DB_QUERY_FILE $DB_SERVER $Global:DB_DATABASE "fresh.DATA.sql"

    if ($CEDAR_DATABASES_ACTION_TEST -eq $true)
    {
        Write-Host "SQL : Fresh install TEST scripts"

        DB_QUERY_FILE $DB_SERVER CEDAR_TEST "fresh.SETUP.sql"
        DB_QUERY_FILE $DB_SERVER CISA_TEST "fresh.cisa.sql"
        DB_QUERY_FILE $DB_SERVER CEDAR_TEST "fresh.cedar.sql"
        DB_QUERY_FILE $DB_SERVER CEDAR_TEST "fresh.INTEGRATION.sql"
        DB_QUERY_FILE $DB_SERVER CEDAR_TEST "fresh.TASKS.sql"
        DB_QUERY_FILE $DB_SERVER CEDAR_TEST "fresh.DATA.sql"
    }

    CEDAR_FN_SQL_SERVER_USERS
}

function global:DB_QUERY_FILE_TEST
{
    &"$PSScriptRoot/vars.ps1"

    CEDAR_FN_DATABASE_LOCATION_CHECK

    if (Test-Path -Type Leaf -Path "$ROOT/test.sql")
    {
        DB_QUERY_FILE $DB_SERVER $Global:DB_DATABASE "$ROOT/test.sql"
    }
    else
    {
        Write-Error "$ROOT/test.sql does not exist"
    }
}

function global:DB_QUERY_FILE([string]$P_DB_SERVER, [string]$P_DB_DATABASE, [string]$P_FILE)
{
    if ($null -ne $DB_ALTER_USE_WINDOWS_USER -AND $DB_ALTER_USE_WINDOWS_USER -eq 1) {
        Invoke-DbaQuery -SqlInstance $P_DB_SERVER -Database $P_DB_DATABASE -File $P_FILE
        return
    }

    Invoke-DbaQuery -SqlInstance $P_DB_SERVER -Database $P_DB_DATABASE -File $P_FILE -SqlCredential $DB_CREDENTIALS
}

function global:CEDAR_FN_SQL_TEST
{
    CEDAR_FN_DATABASE_LOCATION_CHECK

    Write-Host "Attempting to connect with CEDAR DB User"
    $server = $null

    try
    {
        $server = Connect-DbaInstance -SqlInstance "$DB_SERVER" -SqlCredential $DB_CREDENTIALS -ConnectTimeout 5
        Write-Host "Connection with DB User in .env successful"
    } catch {
        Write-Error "DB user in .env connection method failed"
    }
    Write-Host ""

    if ($null -ne $DB_ALTER_USE_WINDOWS_USER -AND $DB_ALTER_USE_WINDOWS_USER -eq "1") {
        Write-Host ".env has DB_ALTER_USE_WINDOWS_USER=1 so Windows user will be used"
    }

    Write-Host ""
    Write-Host "Attempting to connect with Windows User"
    try
    {
        $server = Connect-DbaInstance -SqlInstance "$DB_SERVER" -ConnectTimeout 5
        Write-Host "Connect with Windows user successful (DB_ALTER_USE_WINDOWS_USER=1)"
    } catch {
        Write-Error "Windows connection method failed"
    }

    if ($null -ne $server -AND(Get-DbaDatabase -SqlInstance $server -Database $Global:DB_DATABASE))
    {
    Write-Host "Checking Database Compatibility mode"

    if ($null -ne $DB_ALTER_USE_WINDOWS_USER -AND $DB_ALTER_USE_WINDOWS_USER -eq 1) {
        $compat = (Get-DbaDbCompatibility -SqlInstance "$DB_SERVER" -Database $Global:DB_DATABASE)
    } else {
        $compat = (Get-DbaDbCompatibility -SqlInstance "$DB_SERVER" -Database $Global:DB_DATABASE -SqlCredential $DB_CREDENTIALS)
    }
    if ($null -eq $compat) {
        Write-Error "Compatibility could not be established"
    } else {
        $compatVer = $compat.Compatibility -replace "Version",""
        if ($null -ne $compatVer -AND $compatVer -lt 150) {
            Write-Error "Database Compatibility Mode of ($Global:DB_DATABASE) set to '$compatVer' minimum version = 150 (2019)."
        } else {
            Write-Host "Database Compatibility Mode of ($Global:DB_DATABASE) set to '$compatVer'"
        }
    }
    }
}

function global:CEDAR_INSTALL_FILES
{
    if ($null -ne $Env:PAT_KEY_B64)
    {
        CEDAR_INSTALL_FILES_GIT
    }
    else
    {
        CEDAR_INSTALL_FILES_ENCRYPTED
    }

    CEDAR_CD_LOCATION
}

function global:CEDAR_CD_LOCATION
{
    #$dir1 = $CEDAR_INSTALL_DIRS | Select-Object
    #$Global:CWD = "$CEDAR_INSTALL_BASE/$dir1"
    #Set-Location -Path "$CWD"

    # Loop through all directories in $CEDAR_INSTALL_DIRS (if you want to visit all)
    foreach ($dir in $CEDAR_INSTALL_DIRS) {
        # Construct the full path using Join-Path for better path handling
        $Global:CWD = Join-Path -Path $CEDAR_INSTALL_BASE -ChildPath $dir

        # Check if the path exists before attempting to change to it
        if (Test-Path $CWD) {
            # Set the location to the directory
            Set-Location -Path $CWD
            Write-Host "Location is set to directory: $CWD"
        } else {
            # If the path doesn't exist, show a message
            Write-Host "The directory '$CWD' does not exist."
        }
    }
}

function global:CEDAR_PAT_SET
{
    Write-Host ""
    Write-Host "*********************************************************"
    Write-Host "Open a browser and login to Azure DevOps"
    Write-Host "https://dev.azure.com/capita-ess/_usersSettings/tokens"
    Write-Host "Create a Personal Access Token and copy it to clipboard"
    Write-Host "Set Expiration 90 days. Code Read."
    Write-Host "*********************************************************"
    Write-Host ""
    Read-Host " "

    Write-Host ""
    Write-Host "*********************************************************"
    Write-Host "Paste in Personal Access Token."
    Write-Host "The screen will be cleared afterwards."
    Write-Host "Press close in the browser dialog"
    Write-Host "*********************************************************"
    Write-Host ""

    $PAT_KEY = Read-Host "Enter your Personal Access Token: "

    $Env:PAT_KEY_B64 = [Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes("`:$PAT_KEY"))
    [Environment]::SetEnvironmentVariable('PAT_KEY_B64', $Env:PAT_KEY_B64, 'User')

    Clear-Host

    if ($null -eq $Env:PAT_KEY_B64 -OR ($null -eq $PAT_KEY))
    {
        Write-Error "PAT Save failed"
    }
    else
    {
        Write-host "PAT Save successful"
    }
}

function global:CEDAR_GITHUB_TOKEN
{
    Write-Host ""
    Write-Host "*********************************************************"
    Write-Host "Press Enter to open a browser and login to Github"
    Write-Host "Browser will access https://github.com/settings/tokens"
    Write-Host "Create a Personal Access Token and copy it to clipboard"
    Write-Host "Set Expiration unlimited."
    Write-Host "*********************************************************"
    Write-Host ""
    Read-Host " "

    Start-Process "https://github.com/settings/tokens"

    Write-Host ""
    Write-Host "*********************************************************"
    Write-Host "Paste in Personal Access Token."
    Write-Host "The screen will be cleared afterwards."
    Write-Host "Press close in the browser dialog"
    Write-Host "*********************************************************"
    Write-Host ""
    $TOKEN = Read-Host "Enter your Personal Access Token: "

    [Environment]::SetEnvironmentVariable('GITHUB_TOKEN', $TOKEN, 'User')

    Clear-Host

    CWD

    if (Test-Path -Path "$CWD/Var/composer.phar" -Type Leaf)
    {
        php "$CWD/Var/composer.phar" config -g github-oauth.github.com $TOKEN
    }
}

function global:CEDAR_INSTALL_FILES_GIT
{
    if ($null -ne $Env:PAT_KEY_B64)
    {
        Write-Error "GIT Personal Access Token not set. Run .\Scripts\git-pull.ps1"
    }

    CEDAR_FN_PS_DEPS_DEV

    Write-Host "Install Azure CLI + Login with whichever account has access to Azure Dev Ops"

    az extension add --name azure-devops

    az login

    # A Browser window will now pop up and ask you to login
    # Use whichever account you use to access ADO

    # Install GIT
    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        Write-host "Cloning to $CEDAR_INSTALL_BASE/$dir"
        git -c http.extraHeader="Authorization: Basic $Env:PAT_KEY_B64" clone https://capita-ess@dev.azure.com/capita-ess/CEDAR/_git/cedar "$CEDAR_INSTALL_BASE/$dir"
        Set-Content -path "$CEDAR_INSTALL_BASE/$dir\version.txt" -Value $CEDAR_VERSION
    }
}

function global:CEDAR_INSTALL_FILES_ENCRYPTED
{
    CEDAR_PS_VARS_SETUP

    $CEDAR_INSTALL_ZIP_FILE = "C:/$CEDAR_VERSION.files.zip"

    if (-Not(Test-Path -Type Leaf -Path "$CEDAR_INSTALL_ZIP_FILE"))
    {
        $FETCH_FILE = "$CEDAR_RELEASE_DOMAIN/$CEDAR_VERSION.files.zip"
        Write-Host "Downloading: $FETCH_FILE"
        Invoke-WebRequest $FETCH_FILE -OutFile $CEDAR_INSTALL_ZIP_FILE
        if (-NOT(Test-Path -Type Leaf -Path $CEDAR_INSTALL_ZIP_FILE))
        {
            Throw "Not Found: $CEDAR_INSTALL_ZIP_FILE"
        }
    }

    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        Expand-Archive -Force "$CEDAR_INSTALL_ZIP_FILE" -DestinationPath "$CEDAR_INSTALL_BASE/$dir"
    }

    CEDAR_INSTALL_DIR_PERMISSIONS
}

function global:IIS_IS_RUNNING
{
    $iisStatus = (iisreset /status)
    $iisStatusMatch -match "\( W3SVC \) : Running"
    $iisRunning = $false
    if ($null -ne $iisStatusMatch) {
        $iisRunning = $true
    }
    Write-Host "IIS Running: $iisRunning"
    $Global:IIS_RUNNING = $iisRunning
    return $iisRunning
}

function global:CEDAR_VERSION_FETCH
{
    $FETCH_FILE = "$CEDAR_RELEASE_DOMAIN/current.json"
    Write-Host "Downloading: $FETCH_FILE"

    try
    {
        $res = (Invoke-WebRequest $FETCH_FILE)
    }
    catch
    {
        if (-NOT(Test-Path -Type Leaf -Path $FETCH_FILE))
        {
            $msg = $_.Exception.Message
            throw "Could not download $FETCH_FILE. Error $msg"
        }

        $res = Get-Content $FETCH_FILE
    }

    try
    {
        $con = $res.Content
        $json = $con | ConvertFrom-JSON

        $Global:CEDAR_VERSION = $json.this

        Write-Host "Current Version: $CURRENT_VERSION"
    }
    catch
    {
        throw "Error getting latest version. Access $FETCH_FILE"
    }
}

function global:CEDAR_FILES
{
    if (IsAdministrator)
    {
        $SAVE_DRIVE = "C:"
    }
    else
    {
        $SAVE_DRIVE = CWD
    }

    if ($null -ne $args[0]) {
        Write-Host "Using version :" + $args[0]
        $CEDAR_VERSION = $args[0]
    } else {
        CEDAR_VERSION_FETCH
    }

    $CEDAR_ZIP_FILE = "$CEDAR_VERSION.files.zip"
    $FETCH_FILE = "$CEDAR_RELEASE_DOMAIN/$CEDAR_ZIP_FILE"
    Write-Host "$FETCH_FILE"
    if (-Not(Test-Path -Path $FETCH_FILE -Type Leaf))
    {
        Invoke-WebRequest $FETCH_FILE -OutFile "$SAVE_DRIVE\$CEDAR_ZIP_FILE"
    }

    $CEDAR_ZIP_FILE = "$CEDAR_VERSION.sql.zip"
    $FETCH_FILE = "$CEDAR_RELEASE_DOMAIN/$CEDAR_ZIP_FILE"
    Write-Host "$FETCH_FILE"
    if (-Not(Test-Path -Path $FETCH_FILE -Type Leaf))
    {
        Invoke-WebRequest $FETCH_FILE -OutFile "$SAVE_DRIVE\$CEDAR_ZIP_FILE"
    }

    $CEDAR_ZIP_FILE = "$CEDAR_VERSION.vendor.zip"
    $FETCH_FILE = "$CEDAR_RELEASE_DOMAIN/$CEDAR_ZIP_FILE"
    Write-Host "$FETCH_FILE"
    if (-Not(Test-Path -Path $FETCH_FILE -Type Leaf))
    {
        Invoke-WebRequest $FETCH_FILE -OutFile "$SAVE_DRIVE\$CEDAR_ZIP_FILE"
    }

    $CEDAR_ZIP_FILE = "$CEDAR_VERSION.scripts.zip"
    $FETCH_FILE = "$CEDAR_RELEASE_DOMAIN/$CEDAR_ZIP_FILE"
    Write-Host "$FETCH_FILE"
    if (-Not(Test-Path -Path $FETCH_FILE -Type Leaf))
    {
        Invoke-WebRequest $FETCH_FILE -OutFile "$SAVE_DRIVE\$CEDAR_ZIP_FILE"
    }

    if ($null -eq $CEDAR_INSTALL_DIRS)
    {
        return
    }

    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        $CEDAR_INSTALL_BASE | oss
        Write-Host "Copying files to $CEDAR_INSTALL_BASE/$dir\Var\Upgrade\"
        Copy-Item -Path "$SAVE_DRIVE/$CEDAR_VERSION.*.zip" -Destination "$CEDAR_INSTALL_BASE/$dir\Var\Upgrade\"
    }
}

function global:CEDAR_INSTALL_PATCH
{
    if ($null -ne $Env:PAT_KEY_B64)
    {
        throw "Patching only for encrypted (where PAT_KEY_B64 is null)"
    }

    CEDAR_PATH_CHECK_OR_EXTRACT

    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        Write-Host "Patching $CEDAR_INSTALL_BASE/$dir"
        &"$CEDAR_INSTALL_BASE/$dir/cedar.ps1 upgrade:patch"
    }
}

function global:CEDAR_INSTALL_DIR_PERMISSIONS
{
    CEDAR_USER_CHECK_SET

    Write-Host "Adding Permissions for $CEDAR_INSTALL_USERS"

    $user = $CEDAR_INSTALL_USERS | Select-Object -First 1
    Write-Host "Setting to : $user"

    CEDAR_PATH_CHECK_OR_EXTRACT

    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        $installPath = "$CEDAR_INSTALL_BASE/$dir"
        Write-Host "Creating $installPath"
        [void](New-Item -Path $installPath -Type Directory -Force -ErrorAction Continue)
        $acl = Get-Acl $installPath
        ForEach ($usr in $CEDAR_INSTALL_USERS)
        {
            $a = New-Object System.Security.AccessControl.FileSystemAccessRule($usr, "FullControl", "ContainerInherit,ObjectInherit", "None", "Allow")
            $acl.AddAccessRule($a)
        }

        $acl.SetAccessRuleProtection($False, $True)
        $acl.SetOwner([System.Security.Principal.NTAccount]$user)
        Set-Acl $installPath $acl
    }
}

function global:CEDAR_INSTALL_IIS
{
    Write-Host "Starting CEDAR Install"

    #PS 7
    try
    {
        if ($PSVersionTable.PSVersion.Major -lt 7)
        {
            Throw ""
        }
    }
    Catch
    {
        Write-Error "Error | Please install PowerShell 7 - 'CEDAR_FN_PS_DEPS'"
    }

    # DIRS SET?
    try
    {
        Get-Variable "CEDAR_INSTALL_DIRS" -ErrorAction Stop
        Get-Variable "CEDAR_INSTALL_BASE" -ErrorAction Stop
        Write-Host "Install DIR: $CEDAR_INSTALL_BASE for $CEDAR_INSTALL_DIRS"
    }
    Catch
    {
        Write-Error "Error | Are the CEDAR_INSTALL_DIRS & CEDAR_INSTALL_BASE parameters set?"
        return
    }

    Write-Host "Installing IIS Feature"
    try
    {
        [void](Enable-WindowsOptionalFeature -Online -FeatureName "IIS-WebServerRole;IIS-WebServer" -ErrorAction Stop -LogLevel 1)
    }
    Catch
    {
        Write-Error "Error | Are you running as Administrator? (Try installing IIS manually)"
        return
    }

    Write-Host "Users Check"
    CEDAR_USER_CHECK_SET
}

function global:CEDAR_USER_CHECK_SET
{
    try
    {
        Get-Variable "CEDAR_INSTALL_USERS" -ErrorAction Stop
    }
    Catch
    {
        CEDAR_USER_SET
    }
}

function global:CEDAR_USER_SET
{
    $Global:CEDAR_INSTALL_USERS = "IUSR", "IIS_IUSRS", $env:UserName
    [Environment]::SetEnvironmentVariable('CEDAR_INSTALL_USERS', $CEDAR_INSTALL_USERS -join ",", 'Machine')
}

function global:CEDAR_USER_SET_ESS
{
    $Global:CEDAR_INSTALL_USERS = "IUSR", "IIS_IUSRS", $env:UserName, "Administrators","Users"
    [Environment]::SetEnvironmentVariable('CEDAR_INSTALL_USERS', $CEDAR_INSTALL_USERS -join ",", 'Machine')
}

function global:CEDAR_INSTALL_SQL
{
    CEDAR_FN_PS_DEPS

    CEDAR_FN_SQL_SERVER_DEPS
}

function global:CEDAR_LINKED_SERVER
{
    if ($null -eq $MIS_SERVER)
    {
        Write-Error "Cannot create Linked Server"
        return
    }

    Write-Host "Creating linked server to $MIS_SERVER on $DB_SERVER"

    try {
        if ($null -ne $DB_ALTER_USE_WINDOWS_USER -AND $DB_ALTER_USE_WINDOWS_USER -eq 1) {
            New-DbaLinkedServer -SqlInstance "$DB_SERVER" -LinkedServer "$MIS_SERVER" -ServerProduct mssql
            return
        }

        New-DbaLinkedServer -SqlInstance "$DB_SERVER" -LinkedServer "$MIS_SERVER" -ServerProduct mssql -SqlCredential $DB_CREDENTIALS

    } catch {
        Write-Host "Linked server create fail"
    }
}

function global:CEDAR_CISA_TEMPLATE_INSTALL
{
    CEDAR_FN_DATABASE_LOCATION_CHECK

    Write-Host "Install CISA Template"

    Write-Host "Write combined-fn.sql to $CISA_DATABASE"
    DB_QUERY_FILE $DB_SERVER $CISA_DATABASE "$CWD/combined-fn.sql"
    Write-Host "Write combined-procedures.sql to $CISA_DATABASE"
    DB_QUERY_FILE $DB_SERVER $CISA_DATABASE "$CWD/combined-procedures.sql"
    Write-Host "Write combined-views.sql to $CISA_DATABASE"
    DB_QUERY_FILE $DB_SERVER $CISA_DATABASE "$CWD/combined-views.sql"

    Write-Host "Setting synonyms to $MIS_SERVER"
    $syn = (Get-Content "$CWD/combined-synonyms.sql")
    $syn = $syn -replace ("QXQ_CEDAR", "$DB_DATABASE")
    $syn = $syn -replace ("QXQ_MIS_LINKED", "$MIS_SERVER")
    $syn = $syn -replace ("QXQ_MIS_DB", $Global:MIS_DATABASE)
    $syn | Out-File "$CWD/combined-synonyms.sql"

    Write-Host "Write combined-synonyms.sql"
    DB_QUERY_FILE $DB_SERVER $CISA_DATABASE "$CWD/combined-synonyms.sql"
}

function global:CEDAR_CISA_TEMPLATE_LOOKUPS
{
    CEDAR_FN_DATABASE_LOCATION_CHECK

    $mis = $Global:MIS_SYSTEM.ToUpper()

    Write-Host "Creating Lookup Tables"
    DB_QUERY_FILE $DB_SERVER $CISA_DATABASE "$CWD/status-copy-$mis.sql"
}

function global:CEDAR_CISA_TEMPLATE_FETCH
{
    ENV_READ
    CEDAR_PS_VARS_SETUP

    $mis = ([string]$Global:MIS_SYSTEM).ToUpper()

    Write-Host "MIS SYSTEM: $mis"

    if ("" -eq $mis)
    {
        throw "MIS_SYSTEM not set in .env"
    }

    Write-Host "Fetching $CEDAR_RELEASE_DOMAIN/cisa-template-$mis.zip"

    $outFile = "$CWD/cisa-template-$mis.zip"
    if (-Not(Test-Path -Path $outFile -Type Leaf))
    {
        Invoke-WebRequest "$CEDAR_RELEASE_DOMAIN/cisa-template-$mis.zip" -OutFile $outFile
    }

    Expand-Archive -Force "$CWD/cisa-template-$mis.zip" -DestinationPath "$CWD"
}

function global:CEDAR_INSTALL_SCRIPTS_FILES
{
    CEDAR_PATH_CHECK_OR_EXTRACT

    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        &"$CEDAR_INSTALL_BASE/$dir\Scripts\fresh-files.ps1"
    }
}

function global:CEDAR_INSTALL_SCRIPTS_SETUP_DEV
{
    CEDAR_PATH_CHECK_OR_EXTRACT

    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        &"$CEDAR_INSTALL_BASE/$dir\Scripts\fresh-iis.ps1"
        &"$CEDAR_INSTALL_BASE/$dir\ScriptsInternal\install-dev.ps1"
        &"$CEDAR_INSTALL_BASE/$dir\Scripts\fresh-iis-site.ps1" "8000"
    }

    Enable-WindowsOptionalFeature -Online -FeatureName WCF-Services45 -All
    Enable-WindowsOptionalFeature -Online -FeatureName WCF-HTTP-Activation45 -All
}

function global:CEDAR_INSTALL_SCRIPTS_DEV
{
    CEDAR_PATH_CHECK_OR_EXTRACT

    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        &"$CEDAR_INSTALL_BASE/$dir\ScriptsInternal\init-dev.ps1"
        &"$CEDAR_INSTALL_BASE/$dir\cedar.ps1" "upgrade:db:sync"
        &"$CEDAR_INSTALL_BASE/$dir\ScriptsInternal\db-restore.ps1"
        &"$CEDAR_INSTALL_BASE/$dir\ScriptsInternal\internal\compiled-css-fetch.ps1"
    }
}

function global:CEDAR_INSTALL_SCRIPTS_SETUP_PROD
{
    CEDAR_VARS_RESTORE
    CEDAR_PATH_CHECK_OR_EXTRACT
    CEDAR_CD_LOCATION
    #&"$CWD/Scripts/fresh-iis.ps1"

    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        Write-Host "Currently adding amd64 file in this directory $dir"
        &"$CEDAR_INSTALL_BASE/$dir\Scripts\fresh-iis.ps1"
        &"$CEDAR_INSTALL_BASE/$dir\Scripts\fresh-iis-site.ps1"
    }
}

function global:CEDAR_INSTALL_SCRIPTS_SETUP_PHP_PERM
{
    CEDAR_VARS_RESTORE
    CEDAR_CD_LOCATION
    #&"$CWD/Scripts/int/php-perm-dirs.ps1"

    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        Write-Host "Setting up permissions for users of directory $dir"
        &"$CEDAR_INSTALL_BASE/$dir\Scripts\int\php-perm-dirs.ps1"
    }
}

function global:CEDAR_USE_VENDOR
{
    if (IsAdministrator) {
        $SAVE_DRIVE = "C:"
    } else {
        $SAVE_DRIVE = CWD
    }

    $ProgressPreference = 'SilentlyContinue'

    if (-Not(Test-Path -Path "$SAVE_DRIVE\$CEDAR_VERSION.vendor.zip"))
    {
        Write-Host "Fetch $CEDAR_RELEASE_DOMAIN/$CEDAR_VERSION.vendor.zip"
        try
        {
            [void](Invoke-WebRequest -ErrorAction SilentlyContinue  -Uri "$CEDAR_RELEASE_DOMAIN/$CEDAR_VERSION.vendor.zip" -OutFile "$SAVE_DRIVE\$CEDAR_VERSION.vendor.zip" -SkipCertificateCheck)
        }
        catch
        {
            throw "Failed to download"
        }
    }

    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        Write-Host "Deleting vendor folder in $CEDAR_INSTALL_BASE/$dir"
        Remove-Item "$CEDAR_INSTALL_BASE/$dir/vendor/" -Recurse -Force -ErrorAction SilentlyContinue

        Write-Host "Extracting vendor folder in $CEDAR_INSTALL_BASE/$dir"
        Expand-Archive -ErrorAction SilentlyContinue -Force "$SAVE_DRIVE/$CEDAR_VERSION.vendor.zip" -DestinationPath "$CEDAR_INSTALL_BASE/$dir/"

        Write-Host "Compser dumpautoload"
        php "$CEDAR_INSTALL_BASE/$dir/Var/composer.phar" --working-dir="$CEDAR_INSTALL_BASE/$dir/Var/" --no-ansi --no-dev --no-interaction --no-plugins --no-progress --no-scripts --optimize-autoloader --prefer-dist install
    }
}

function global:CEDAR_INSTALL_SCRIPTS_PROD
{
    CEDAR_PATH_CHECK_OR_EXTRACT

    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        &"$CEDAR_INSTALL_BASE/$dir\Scripts\init-prod.ps1"

        &"$CEDAR_INSTALL_BASE/$dir\cedar.ps1" "upgrade:db:sync"
    }

    CEDAR_RESET_USERS
}

function global:CEDAR_RESET_USERS
{
    CEDAR_PATH_CHECK_OR_EXTRACT

    ForEach ($dir in $CEDAR_INSTALL_DIRS)
    {
        &"$CEDAR_INSTALL_BASE/$dir\cedar.ps1" capability:manage add non-dangerous SITE_ADMINISTRATOR
        &"$CEDAR_INSTALL_BASE/$dir\cedar.ps1" capability:manage add sensitive SITE_ADMINISTRATOR
        &"$CEDAR_INSTALL_BASE/$dir\cedar.ps1" capability:manage remove IMPERSONATE_PREVENT SITE_ADMINISTRATOR
        &"$CEDAR_INSTALL_BASE/$dir\cedar.ps1" capability:manage add SUPERUSER PLK_ADMIN
        &"$CEDAR_INSTALL_BASE/$dir\cedar.ps1" capability:manage add MY_VIEW PLK_STAFF
        &"$CEDAR_INSTALL_BASE/$dir\cedar.ps1" capability:manage add MY_VIEW PLK_APPLICANT
        &"$CEDAR_INSTALL_BASE/$dir\cedar.ps1" user:create cedar.support PLK_SYSTEM SITE_ADMINISTRATOR
        &"$CEDAR_INSTALL_BASE/$dir\cedar.ps1" user:create super.admin PLK_ADMIN PLK_ADMIN
        &"$CEDAR_INSTALL_BASE/$dir\cedar.ps1" user:password cedar.support universe.ship
        &"$CEDAR_INSTALL_BASE/$dir\cedar.ps1" user:password super.admin universe.ship
        &"$CEDAR_INSTALL_BASE/$dir\cedar.ps1" user:create temp.admin PLK_SYSTEM SITE_ADMINISTRATOR
        &"$CEDAR_INSTALL_BASE/$dir\cedar.ps1" user:password temp.admin
    }
}

function global:CEDAR_SSL_SELF
{
    ENV_READ

    $domain = $WEB_URL -replace("https?://","") -replace("(:\d+)$","")
    Write-host "Creating SSL for '$domain'"

    $cer = (New-SelfsignedCertificate -Subject "CN=$domain" -DnsName $domain -FriendlyName "uniteqavm (SAN)" -CertStoreLocation cert:\localmachine\my).Thumbprint
    Write-host "Exporting SSL KEY: '$cer'"

    $DesktopPath = [Environment]::GetFolderPath("Desktop")
    [void](Export-Certificate -cert "cert:\localmachine\my\$cer" -FilePath "$DesktopPath\cert.cer")

    Write-Host "Installing cert to Trusted Root"
    [void](Import-Certificate -FilePath "$DesktopPath\cert.cer" -CertStoreLocation cert:\localmachine\Root)
}

function global:Test-Write {
    [CmdletBinding()]
    param (
        [parameter()] [ValidateScript({[IO.Directory]::Exists($_.FullName)})]
        [IO.DirectoryInfo] $Path
    )
    try {
        $testPath = Join-Path $Path ([IO.Path]::GetRandomFileName())
        [IO.File]::Create($testPath, 1, 'DeleteOnClose') > $null
        return $true
    } catch {
        return $false
    } finally {
        Remove-Item $testPath -ErrorAction SilentlyContinue
    }
}

Write-Host "CEDAR install scripts loaded successfully"
