param(
    [string]$dbHost = "localhost",
    [string]$port = "5432",
    [string]$user,
    [string]$db,
    [string]$password = "",
    [string]$schema,
    [string]$superuser = "",
    [string]$superuserPassword = ""
)

$tempOutput = "_sorted_ddl.sql"

# Set PGPASSWORD if provided
if ($password) {
    $env:PGPASSWORD = $password
}

# Check if pg_get_tabledef function exists
$functionExists = psql -h $dbHost -p $port -U $user -d $db -t -A -q -c "SELECT EXISTS(SELECT 1 FROM pg_proc WHERE proname = 'pg_get_tabledef' AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public'))::text;"

if ($functionExists -ne "true") {
    if (-not $superuser) {
        Write-Error "Function pg_get_tabledef does not exist. Provide superuser and superuserPassword to install it."
        exit 1
    }

    $origPassword = $env:PGPASSWORD

    if ($superuserPassword) {
        $env:PGPASSWORD = $superuserPassword
    } else {
        Remove-Item Env:PGPASSWORD -ErrorAction SilentlyContinue
    }

    Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] - Installing pg_get_tabledef function using superuser credentials..." -ForegroundColor Yellow
    psql -h $dbHost -p $port -U $superuser -d $db -q -f pg_get_tabledef.sql

    # Restore original PGPASSWORD
    if ($origPassword) {
        $env:PGPASSWORD = $origPassword
    } else {
        Remove-Item Env:PGPASSWORD -ErrorAction SilentlyContinue
    }

    if ($password) {
        $env:PGPASSWORD = $password
    }
}


Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] - Generating extraction script..." -ForegroundColor Green
psql -h $dbHost -p $port -U $user -d $db -q -f extract_ddl_func.sql
psql -h $dbHost -p $port -U $user -d $db -t -A -F '' -q -c "select EXTRACT_AXIUM_DDL('$schema', '$db', '$dbHost', '$port', '$user', '$tempOutput')" > _extract_ddl.bat

Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] - Cleaning up temporary table and function..." -ForegroundColor Green
psql -h $dbHost -p $port -U $user -d $db -q -c "DROP FUNCTION IF EXISTS EXTRACT_AXIUM_DDL;"

Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] - Executing extraction script..." -ForegroundColor Green
.\_extract_ddl.bat

Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] - Processing extracted DDL..." -ForegroundColor Green

$cleaned = Get-Content ".\$tempOutput"

$schema_regex = "$schema[.;]"
$skip_set_regex = '^SET '
$skip_dumped_regex = '^-- Dumped '
$skip_schema_regex = '^-- Schema: '
$skip_pgconfig_regex = '^SELECT pg_catalog\.set_config'
$skip_restrict_regex = '^\\restrict'
$skip_unrestrict_regex = '^\\unrestrict'
$create_seq_regex = '^CREATE SEQUENCE'
$block_end_regex = '^(\-\-.*|CREATE|ALTER)'
$start_with_regex = '^START WITH \d+'
$increment_by_regex = '^INCREMENT BY (-?\d+)'
$maxvalue_regex = '^MAXVALUE (\d+)'
$replace_start_with_regex = '(START WITH )\d+'

$sb = New-Object System.Text.StringBuilder

Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] - ...Setting encoding" -ForegroundColor Green
$sb.AppendLine("SET client_encoding = 'UTF8';")

Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] - ...Adding types" -ForegroundColor Green
$sb.AppendLine((Get-Content ".\axium_types.sql" -Raw))

Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] - ...Adding temporary tables" -ForegroundColor Green
$sb.AppendLine((Get-Content ".\axium_global_temp_tables.sql" -Raw))


$i = 0
while ($i -lt $cleaned.Count) {
    $line = $cleaned[$i] -replace $schema_regex, ""

    if ($line -match $skip_set_regex -or 
        $line -match $skip_dumped_regex -or 
        $line -match $skip_schema_regex -or 
        $line -match $skip_pgconfig_regex -or 
        $line -match $skip_restrict_regex -or 
        $line -match $skip_unrestrict_regex) {
        $i++
        continue
    }
    
    if ($line -match $create_seq_regex) {
        # Start of a sequence block
        $block = New-Object System.Collections.Generic.List[string]
        $block.Add($line)
        $i++
        $increment = $null
        $maxValue = $null
        $startLineIndex = -1
        $blockIndex = 1
        while ($i -lt $cleaned.Count -and $cleaned[$i] -notmatch $block_end_regex) {
            $block.Add($cleaned[$i])
            $current = $cleaned[$i].Trim()
            if ($current -match $start_with_regex) {
                $startLineIndex = $blockIndex
            } elseif ($current -match $increment_by_regex) {
                $increment = $matches[1]
            } elseif ($current -match $maxvalue_regex) {
                $maxValue = $matches[1]
            }
            $i++
            $blockIndex++
        }
        # Now process the block
        if ($increment) {
            if ([int]$increment -gt 0) {
                # Positive increment: set START WITH 1
                if ($startLineIndex -ge 0) {
                    $block[$startLineIndex] = $block[$startLineIndex] -replace $replace_start_with_regex, '${1}1'
                }
            } elseif ($maxValue) {
                # Negative increment: set START WITH to MAXVALUE, keep increment
                if ($startLineIndex -ge 0) {
                    $block[$startLineIndex] = $block[$startLineIndex] -replace $replace_start_with_regex, "`${1}$maxValue"
                }
            }
        }
        foreach ($bl in $block) {
            [void]$sb.AppendLine($bl)
        }
    } else {
        [void]$sb.AppendLine($line)
        $i++
    }
}
$sb.ToString() | Out-File -FilePath "$schema`_ddl.sql" -Encoding utf8

Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] - Cleaning up temporary files..." -ForegroundColor Green
#Remove-Item _*

Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] - Done! DDL extracted to $schema`_ddl.sql" -ForegroundColor Green

# Unset PGPASSWORD if it was set
if ($password) {
    Remove-Item Env:PGPASSWORD
}
