# Wx SOP Flyway migration verification
# Usage: cd d:\ylrz_saas_new\java\scripts ; .\verify_wx_sop_flyway.ps1

$ErrorActionPreference = "Stop"
$root = Split-Path (Split-Path $PSScriptRoot -Parent) -Parent
$tenantDir = Join-Path $root "java\fs-saas-admin\src\main\resources\db\migration\tenant"
$masterDir = Join-Path $root "java\sql\master"
$manual = Join-Path $root "java\sql\wx_sop_tenant_menu_full_sync_june2026.sql"

Write-Host "=== Wx SOP Flyway verify ===" -ForegroundColor Cyan

$requiredTenant = @(
  "V20260614_03__wx_sop_menu_sync.sql",
  "V20260614_04__wx_sop_role_menu_grant.sql"
)
$requiredMaster = @("V20260614_03__wx_sop_master_menu_sync.sql")

$fail = 0

foreach ($f in $requiredTenant) {
  $path = Join-Path $tenantDir $f
  if (-not (Test-Path $path)) {
    Write-Host "[FAIL] missing tenant migration: $f" -ForegroundColor Red
    $fail++
  } else {
    Write-Host "[OK] tenant migration: $f" -ForegroundColor Green
  }
}

foreach ($f in $requiredMaster) {
  $path = Join-Path $masterDir $f
  if (-not (Test-Path $path)) {
    Write-Host "[FAIL] missing master migration: $f" -ForegroundColor Red
    $fail++
  } else {
    Write-Host "[OK] master migration: $f" -ForegroundColor Green
  }
}

if (-not (Test-Path $manual)) {
  Write-Host "[FAIL] missing manual sync script" -ForegroundColor Red
  $fail++
} else {
  Write-Host "[OK] manual sync script" -ForegroundColor Green
}

$versions = Get-ChildItem $tenantDir -Filter "V*__*.sql" | ForEach-Object {
  $_.Name.Substring(0, $_.Name.IndexOf("__"))
} | Sort-Object

$dup = $versions | Group-Object | Where-Object { $_.Count -gt 1 }
if ($dup) {
  Write-Host "[FAIL] duplicate migration versions" -ForegroundColor Red
  $fail++
} else {
  Write-Host "[OK] tenant migration versions unique ($($versions.Count) scripts)" -ForegroundColor Green
}

if ("V20260614_03" -ge "V20260614_04") {
  Write-Host "[FAIL] V20260614_04 must sort after V20260614_03" -ForegroundColor Red
  $fail++
} else {
  Write-Host "[OK] V20260614_03 then V20260614_04 order" -ForegroundColor Green
}

$menuIds = @(32901..32904) + @(32911..32920) + @(32921..32925) + @(32931..32935) + @(32941..32945)
$tenantScripts = @(
  "V20260614_03__wx_sop_menu_sync.sql",
  "V20260614_04__wx_sop_role_menu_grant.sql"
)

foreach ($name in $tenantScripts) {
  $path = Join-Path $tenantDir $name
  if (-not (Test-Path $path)) { continue }
  $text = [System.IO.File]::ReadAllText($path)
  if ($text.IndexOf("wx:wxSop:list") -lt 0 -and $text.IndexOf("sys_role_menu") -lt 0) {
    Write-Host "[FAIL] $name missing expected SQL markers" -ForegroundColor Red
    $fail++
  } else {
    Write-Host "[OK] $name SQL markers" -ForegroundColor Green
  }
  if ($name -like "*03*") {
    foreach ($id in $menuIds) {
      $needle = "($id,"
      if ($text.IndexOf($needle) -lt 0) {
        Write-Host "[FAIL] $name missing menu_id $id" -ForegroundColor Red
        $fail++
      }
    }
  }
}

$rolePath = Join-Path $tenantDir "V20260614_04__wx_sop_role_menu_grant.sql"
if (Test-Path $rolePath) {
  $rt = [System.IO.File]::ReadAllText($rolePath)
  foreach ($kw in @("sys_role_menu", "company_role_menu", "role_key = 'admin'")) {
    if ($rt.IndexOf($kw) -lt 0) {
      Write-Host "[FAIL] role grant missing: $kw" -ForegroundColor Red
      $fail++
    } else {
      Write-Host "[OK] role grant contains: $kw" -ForegroundColor Green
    }
  }
}

$wxLoaded = Get-ChildItem (Join-Path $tenantDir "V*__*.sql") | ForEach-Object { $_.Name } |
  Where-Object { $_ -like "V20260614_03*" -or $_ -like "V20260614_04*" } |
  Sort-Object
Write-Host "[OK] TenantUpgrade wx scripts: $($wxLoaded -join ', ')" -ForegroundColor Green

$adminPom = Join-Path $root "java\fs-saas-admin\pom.xml"
if (Test-Path $adminPom) {
  Push-Location (Join-Path $root "java")
  try {
    & mvn -q -pl fs-saas-admin -am test-compile -DskipTests
    if ($LASTEXITCODE -eq 0) {
      Write-Host "[OK] mvn test-compile fs-saas-admin" -ForegroundColor Green
    } else {
      Write-Host "[FAIL] mvn test-compile exit code $LASTEXITCODE" -ForegroundColor Red
      $fail++
    }
  } finally {
    Pop-Location
  }
}

Write-Host ""
if ($fail -eq 0) {
  Write-Host "=== PASS ===" -ForegroundColor Green
  Write-Host "Next steps:"
  Write-Host "  1) Master DB: run sql/master/V20260614_03__wx_sop_master_menu_sync.sql"
  Write-Host "  2) Tenant upgrade: fromVersion empty, toVersion V20260614_04"
  exit 0
} else {
  Write-Host "=== FAIL ($fail issue(s)) ===" -ForegroundColor Red
  exit 1
}