$ErrorActionPreference = "Stop"

$projectRoot = Split-Path -Parent $PSScriptRoot
$sourcePath = Join-Path $projectRoot "database\zhibaotong-stage1-stage2-stage3-mysql.sql"
$targetPath = Join-Path $projectRoot "database\zhibaotong-stage3-mysql.sql"

$content = [System.IO.File]::ReadAllText($sourcePath, [System.Text.Encoding]::UTF8)
$sessionStartMarker = "/*!40101 SET @OLD_CHARACTER_SET_CLIENT"
$firstDatabaseMarker = '-- Current Database: `insurance_s1_core`'
$stage3Marker = '-- Current Database: `insurance_s3_core`'

$sessionStart = $content.IndexOf($sessionStartMarker, [System.StringComparison]::Ordinal)
$firstDatabaseStart = $content.IndexOf(
    $firstDatabaseMarker,
    [System.StringComparison]::Ordinal
)
$stage3Start = $content.IndexOf($stage3Marker, [System.StringComparison]::Ordinal)

if ($sessionStart -lt 0 -or $firstDatabaseStart -lt 0 -or $stage3Start -lt 0) {
    throw "Expected stage boundaries were not found in the unified SQL."
}

$sessionPreamble = $content.Substring(
    $sessionStart,
    $firstDatabaseStart - $sessionStart
)
$stage3Body = $content.Substring($stage3Start)
$header = @"
-- Zhibaotong Stage 3 MySQL initialization script
-- Source: zhibaotong-stage1-stage2-stage3-mysql.sql
-- Databases: insurance_s3_core, insurance_s3_agent, insurance_s3_analytics
-- Warning: importing this file rebuilds same-named tables in these databases.

"@

$output = $header + $sessionPreamble + $stage3Body
$utf8WithoutBom = [System.Text.UTF8Encoding]::new($false)
[System.IO.File]::WriteAllText($targetPath, $output, $utf8WithoutBom)

Write-Host "Generated: $targetPath"