<#
.SYNOPSIS
    SQL ROLLUP VERIFICATION SCRIPT (POWERSHELL)
.DESCRIPTION
    This script verifies that the 'master_schema_rollup.sql' file is an exact
    concatenation of the individual SQL component files. This helps ensure
    that all database setup scripts are synchronized.

    The script will exit with code 0 if they match, and 1 if they don't.
.EXAMPLE
    From the root of the project, run:
    .\sql\verify_rollup.ps1
#>

# Set the script to stop on errors
$ErrorActionPreference = "Stop"

# Define file paths relative to the script's location
$scriptDirectory = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition
$ProjectRoot = Resolve-Path -Path (Join-Path $scriptDirectory "..")
$MasterFile = Join-Path $ProjectRoot "sql\master_schema_rollup.sql"

# The individual files to concatenate, IN ORDER.
$SourceFiles = @(
    (Join-Path $ProjectRoot "sql\initial_schema.sql"),
    (Join-Path $ProjectRoot "sql\initial_data.sql"),
    (Join-Path $ProjectRoot "sql\initial_triggers_and_functions.sql")
)

Write-Host "Comparing concatenated content with '$MasterFile'..."

# Compare the master file with the concatenated content of the source files.
$Difference = Compare-Object -ReferenceObject (Get-Content $MasterFile) -DifferenceObject (Get-Content $SourceFiles)

if ($null -eq $Difference) {
    Write-Host "✅ Success: '$MasterFile' is up to date with the individual SQL files." -ForegroundColor Green
    exit 0
} else {
    Write-Host "❌ Error: '$MasterFile' is out of sync. Differences found." -ForegroundColor Red
    Write-Host "--- $MasterFile"
    Write-Host "+++ Concatenated Source Files"
    
    # Format the output to look like a diff
    foreach ($diffLine in $Difference) {
        if ($diffLine.SideIndicator -eq "<=") {
            Write-Host ("- " + $diffLine.InputObject) -ForegroundColor Red
        } elseif ($diffLine.SideIndicator -eq "=>") {
            Write-Host ("+ " + $diffLine.InputObject) -ForegroundColor Green
        }
    }
    exit 1
}