Build-DatabaseScripts.ps1

# Build-DatabaseScripts.ps1
# Consolidates SQL scripts from src/<Database>/<Provider>/ into dist/<Database>/<Database>.<Provider>.sql

param(
    [string]$SrcDir = "",
    [string]$DistDir = "dist",
    [string]$ConfigFile = "config.json"
)

# Default paths - assumes script runs from jaunty project root
if ([string]::IsNullOrEmpty($SrcDir)) {
    $SrcDir = "C:\home\syed\code\remote\databases\src"
}
if (-not (Test-Path $SrcDir)) {
    $SrcDir = "..\..\home\syed\code\remote\databases\src"
}
if (-not (Test-Path $SrcDir)) {
    $SrcDir = "src"
}

$ErrorActionPreference = "Stop"

# Load config
$configPath = Join-Path (Split-Path -Parent $MyInvocation.MyCommand.Path) $ConfigFile
if (-not (Test-Path $configPath)) {
    $configPath = $ConfigFile
}
if (Test-Path $configPath) {
    $config = Get-Content $configPath -Raw | ConvertFrom-Json
} else {
    Write-Host "Config file not found: $configPath" -ForegroundColor Yellow
    $config = $null
}

# Get default connection string based on provider
function Get-DefaultConnectionString {
    param([string]$Provider, [string]$DatabaseName)
    
    switch ($Provider) {
        "SqlServer" {
            return "Server=localhost;Database=$DatabaseName;Trusted_Connection=True;TrustServerCertificate=True"
        }
        "Postgres" {
            return "Host=localhost;Database=$DatabaseName;Username=postgres;Password=postgres"
        }
        "MySql" {
            return "Server=localhost;Database=$DatabaseName;Uid=root;Pwd=root"
        }
        "Sqlite" {
            return "Data Source=$DatabaseName.db"
        }
        default {
            return ""
        }
    }
}

# Get connection string from config or use default
function Get-ConnectionString {
    param([string]$Provider, [string]$DatabaseName)
    
    if ($config) {
        $providerKey = $Provider
        if ($Provider -eq "MySql") { $providerKey = "MySql" }
        
        $connStr = $config.$providerKey.ConnectionString
        if (-not [string]::IsNullOrWhiteSpace($connStr)) {
            return $connStr
        }
    }
    
    return Get-DefaultConnectionString -Provider $Provider -DatabaseName $DatabaseName
}

# Directories to ignore (not database providers)
$IgnoreDirs = @("bin", "obj", ".git", ".vs", "node_modules")

# Folder order mapping (prefix -> display order)
$FolderOrder = @{
    "1. " = 1
    "2. " = 2
    "3. " = 3
    "4. " = 4
    "5. " = 5
    "6. " = 6
    "7. " = 7
    "8. " = 8
    "9. " = 9
}

# Default folder order if not prefixed
$DefaultFolders = @("Tables", "Views", "Data", "Constraints", "Stored Procedures")

function Get-OrderedFolders {
    param([string]$ProviderPath)
    
    $folders = Get-ChildItem -Directory -Path $ProviderPath | Where-Object { 
        $_.Name -notin $IgnoreDirs 
    }
    
    # Check if folders have number prefixes
    $hasPrefix = $false
    foreach ($folder in $folders) {
        foreach ($prefix in $FolderOrder.Keys) {
            if ($folder.Name.StartsWith($prefix)) {
                $hasPrefix = $true
                break
            }
        }
        if ($hasPrefix) { break }
    }
    
    $ordered = @()
    foreach ($folder in $folders) {
        if ($hasPrefix) {
            # Use explicit order from prefix
            $order = 999
            $cleanName = $folder.Name
            foreach ($prefix in $FolderOrder.Keys) {
                if ($folder.Name.StartsWith($prefix)) {
                    $order = $FolderOrder[$prefix]
                    $cleanName = $folder.Name.Substring($prefix.Length).Trim()
                    break
                }
            }
            $ordered += [PSCustomObject]@{
                Order = $order
                Name = $cleanName
                DisplayName = $folder.Name
                FullPath = $folder.FullName
            }
        } else {
            # Use default order based on name matching
            $order = 999
            $cleanName = $folder.Name
            for ($i = 0; $i -lt $DefaultFolders.Count; $i++) {
                if ($folder.Name -eq $DefaultFolders[$i]) {
                    $order = $i + 1
                    break
                }
            }
            $ordered += [PSCustomObject]@{
                Order = $order
                Name = $folder.Name
                DisplayName = $folder.Name
                FullPath = $folder.FullName
            }
        }
    }
    
    return $ordered | Sort-Object Order
}

function Build-DatabaseScripts {
    param([System.IO.DirectoryInfo]$DatabaseDir)
    
    $dbName = $DatabaseDir.Name
    Write-Host "`n=== Building: $dbName ===" -ForegroundColor Cyan
    
    # Find all provider directories
    $providers = Get-ChildItem -Directory -Path $DatabaseDir.FullName | Where-Object { 
        $_.Name -notin $IgnoreDirs 
    }
    
    if ($providers.Count -eq 0) {
        Write-Host "  No providers found in $dbName" -ForegroundColor Yellow
        return
    }
    
    foreach ($provider in $providers) {
        $providerName = $provider.Name
        Write-Host "  Provider: $providerName" -ForegroundColor Gray
        
        # Get ordered folders
        $folders = Get-OrderedFolders -ProviderPath $provider.FullName
        
        # Output file
        $outputDir = Join-Path $DistDir $dbName
        if (-not (Test-Path $outputDir)) {
            New-Item -ItemType Directory -Path $outputDir -Force | Out-Null
        }
        
        $outputFile = Join-Path $outputDir "$dbName.$providerName.sql"
        
        # Clear output file
        if (Test-Path $outputFile) {
            Remove-Item $outputFile
        }
        
        # Ensure file exists with a comment header
        "-- Consolidated SQL script for $dbName - $providerName" | Out-File $outputFile -Encoding utf8
        
        # Special handling for SQL Server - set quoted identifier
        if ($providerName -eq "SqlServer") {
            "SET QUOTED_IDENTIFIER ON;" | Out-File $outputFile -Append -Encoding utf8
            "GO" | Out-File $outputFile -Append -Encoding utf8
        }
        
        # Special handling for SQLite - add header
        if ($providerName -eq "Sqlite") {
            "PRAGMA foreign_keys = OFF;" | Out-File $outputFile -Append -Encoding utf8
            "BEGIN TRANSACTION;" | Out-File $outputFile -Append -Encoding utf8
        }
        
        # Process each folder in order
        foreach ($folder in $folders) {
            $sqlFiles = Get-ChildItem -Path $folder.FullPath -Filter "*.sql" | Sort-Object Name
            
            if ($sqlFiles.Count -gt 0) {
                Write-Host "    $($folder.DisplayName): $($sqlFiles.Count) files" -ForegroundColor DarkGray
                
                foreach ($file in $sqlFiles) {
                    # Add GO before Views and Stored Procedures for SQL Server
                    if ($providerName -eq "SqlServer" -and $folder.Name -match "View|Stored") {
                        "GO" | Out-File $outputFile -Append -Encoding utf8
                    }
                    
                    # Add separators to prevent "bleeding" between statements
                    "`n;" | Out-File $outputFile -Append -Encoding utf8
                    
                    $content = Get-Content $file.FullName -Raw
                    
                    $content | Out-File $outputFile -Append -Encoding utf8
                    "`n;" | Out-File $outputFile -Append -Encoding utf8
                    
                    # Add GO after Views and Stored Procedures for SQL Server
                    if ($providerName -eq "SqlServer" -and $folder.Name -match "View|Stored") {
                        "GO" | Out-File $outputFile -Append -Encoding utf8
                    }
                }
            }
        }
        
        # Special handling for SQLite - add footer
        if ($providerName -eq "Sqlite") {
            "COMMIT;" | Out-File $outputFile -Append -Encoding utf8
            "PRAGMA foreign_keys = ON;" | Out-File $outputFile -Append -Encoding utf8
        }
        
        Write-Host "    -> $outputFile" -ForegroundColor Green
        
        # Get and display connection string
        $connStr = Get-ConnectionString -Provider $providerName -DatabaseName $dbName
        Write-Host "    Connection: $connStr" -ForegroundColor DarkGray
    }
}

# Main
Write-Host "=== SQL Script Builder ===" -ForegroundColor Magenta
Write-Host "Source: $SrcDir"
Write-Host "Output: $DistDir`n" -ForegroundColor Magenta

# Find all database directories
$databases = Get-ChildItem -Directory -Path $SrcDir | Where-Object { 
    $_.Name -notin $IgnoreDirs 
}

if ($databases.Count -eq 0) {
    Write-Host "No database directories found in $SrcDir" -ForegroundColor Red
    exit 1
}

Write-Host "Found databases: $($databases.Name -join ', ')" -ForegroundColor Yellow

# Create dist directory if needed
if (-not (Test-Path $DistDir)) {
    New-Item -ItemType Directory -Path $DistDir -Force | Out-Null
}

# Build scripts for each database
foreach ($db in $databases) {
    Build-DatabaseScripts -DatabaseDir $db
}

Write-Host "`n=== Build Complete ===" -ForegroundColor Magenta