param(
    [Parameter(Mandatory=$true)]
    [string]$SchemaName,
    
    [Parameter(Mandatory=$false)]
    [string]$ConnString="oracleuser/oraclepass@oraclserver:oracleport/oracleservice"
)

function Invoke-SqlPlus {
    param(
        [Parameter(Mandatory=$true)]
        [string]$Command,
        
        [Parameter(Mandatory=$true)]
        [string]$ConnectionString
    )
    
    $result = Write-Output $Command | sqlplus -S $ConnectionString
    if ($LASTEXITCODE -ne 0) {
        throw "SQLPlus command failed with exit code $LASTEXITCODE"
    }
    return $result
}

# Compile schema
$command = "EXEC DBMS_UTILITY.compile_schema(schema => '$SchemaName', compile_all => false);"

Write-Host "Compiling schema $SchemaName..."
try {
    Invoke-SqlPlus -Command $command -ConnectionString $ConnString
} catch {
    Write-Error "Failed to compile schema: $_"
    exit 1
}

# Check for invalid objects
$command = "SELECT object_name FROM all_objects WHERE owner = '$SchemaName' AND status = 'INVALID';"

Write-Host "Checking for invalid objects in $SchemaName..."
try {
    $invalidObjects = Invoke-SqlPlus -Command $command -ConnectionString $ConnString
    if (-not ($invalidObjects -match "no rows selected")) {
        Write-Error "Invalid objects found in schema. Cannot proceed."
        exit 1
    }
} catch {
    Write-Error "Failed to check invalid objects: $_"
    exit 1
}

Write-Host "Schema ready"
exit 0