<#
.SYNOPSIS
    Edu CDC pipeline end-to-end test script
.DESCRIPTION
    Validates the full CDC pipeline: MySQL binlog -> Debezium -> Kafka -> data-ana -> ClickHouse
    Steps:
      1. Insert test grade into MySQL
      2. Wait for Debezium capture + data-ana consume
      3. Query ClickHouse to verify data synced
      4. Call data-ana API to verify query works
.PARAMETER StudentId
    Custom test student_id (default: cdc-test-<timestamp>)
.EXAMPLE
    .\scripts\test-cdc.ps1
    .\scripts\test-cdc.ps1 -StudentId "my-test-001"
#>
param(
    [string]$StudentId = "cdc-test-$(Get-Date -Format 'yyyyMMddHHmmss')"
)

$ErrorActionPreference = "Continue"

Write-Host "========================================" -ForegroundColor Cyan
Write-Host "  Edu CDC Pipeline E2E Test" -ForegroundColor Cyan
Write-Host "========================================" -ForegroundColor Cyan
Write-Host ""

$examId = "exam-$StudentId"
$gradeId = "grade-$StudentId"
$testScore = 92.5
$classId = "cls-cdc-test"

Write-Host "Test parameters:" -ForegroundColor Yellow
Write-Host "  StudentId: $StudentId"
Write-Host "  ExamId:    $examId"
Write-Host "  GradeId:   $gradeId"
Write-Host "  Score:     $testScore"
Write-Host "  ClassId:   $classId"
Write-Host ""

# ===== 1. Pre-check =====
Write-Host "[1/5] Pre-check..." -ForegroundColor Yellow

# Check Debezium connector
$connectorStatus = $null
try {
    $connectorStatus = Invoke-RestMethod -Uri "http://localhost:8083/connectors/edu-mysql-source/status" -Method Get -TimeoutSec 3 -ErrorAction Stop
} catch {
    Write-Host "  [FAIL] Debezium Connect not reachable. Start infrastructure first." -ForegroundColor Red
    exit 1
}
if ($connectorStatus.connector.state -ne "RUNNING") {
    Write-Host "  [FAIL] Debezium connector state: $($connectorStatus.connector.state)" -ForegroundColor Red
    exit 1
}
Write-Host "  [OK] Debezium connector: RUNNING" -ForegroundColor Green

# Check data-ana service
try {
    $null = Invoke-RestMethod -Uri "http://localhost:3006/healthz" -Method Get -TimeoutSec 3 -ErrorAction Stop
    Write-Host "  [OK] data-ana service: running" -ForegroundColor Green
} catch {
    Write-Host "  [FAIL] data-ana service not reachable. Start application services first." -ForegroundColor Red
    exit 1
}

# Check CDC consumer status
try {
    $readyz = Invoke-RestMethod -Uri "http://localhost:3006/readyz" -Method Get -TimeoutSec 3 -ErrorAction Stop
    $cdcStatus = $readyz.services.cdc_consumer
    if ($cdcStatus -ne "running") {
        Write-Host "  [WARN] CDC consumer status: $cdcStatus (KAFKA_BROKERS may not be set)" -ForegroundColor Yellow
    } else {
        Write-Host "  [OK] CDC consumer: running" -ForegroundColor Green
    }
} catch {
    Write-Host "  [WARN] Cannot get /readyz status" -ForegroundColor Yellow
}

Write-Host ""

# ===== 2. Insert test data into MySQL =====
Write-Host "[2/5] Inserting test data into MySQL..." -ForegroundColor Yellow

$sqlInsert = @"
INSERT INTO core_edu_exams (id, class_id, subject_id, title, exam_date, total_score, created_at, updated_at)
VALUES ('$examId', '$classId', 'sub-math', 'CDC Test Exam', NOW(), 100, NOW(), NOW())
ON DUPLICATE KEY UPDATE updated_at=NOW();
INSERT INTO core_edu_grades (id, exam_id, student_id, score, rank_in_class, created_at, updated_at)
VALUES ('$gradeId', '$examId', '$StudentId', $testScore, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE score=$testScore, updated_at=NOW();
"@

docker exec edu-mysql mysql -uedu -pchangeme next_edu_cloud -e $sqlInsert 2>&1 | Out-Null
if ($LASTEXITCODE -eq 0) {
    Write-Host "  [OK] Inserted: exam=$examId / grade=$gradeId / student=$StudentId / score=$testScore" -ForegroundColor Green
} else {
    Write-Host "  [FAIL] MySQL insert failed" -ForegroundColor Red
    exit 1
}

Write-Host ""

# ===== 3. Wait for CDC propagation =====
Write-Host "[3/5] Waiting for CDC propagation (5s)..." -ForegroundColor Yellow
Start-Sleep -Seconds 5
Write-Host "  [OK] Wait complete" -ForegroundColor Green
Write-Host ""

# ===== 4. Verify ClickHouse data =====
Write-Host "[4/5] Verifying ClickHouse data..." -ForegroundColor Yellow

$chQuery = "SELECT student_id, class_id, exam_id, score, last_updated FROM edu_analytics.student_dashboard_view WHERE student_id = '$StudentId' ORDER BY last_updated DESC LIMIT 5"
$chResult = docker exec edu-clickhouse clickhouse-client --user default --password clickhouse -q $chQuery 2>$null

if ($chResult) {
    Write-Host "  [OK] ClickHouse returned data:" -ForegroundColor Green
    Write-Host "  $chResult" -ForegroundColor White

    if ($chResult -match $StudentId -and $chResult -match "$testScore") {
        Write-Host ""
        Write-Host "  [OK] Verified: student_id match + score=$testScore match" -ForegroundColor Green
        if ($chResult -match $classId) {
            Write-Host "  [OK] class_id filled via exam cache: $classId" -ForegroundColor Green
        } else {
            Write-Host "  [WARN] class_id not filled (exam cache may have missed, check event order)" -ForegroundColor Yellow
        }
    } else {
        Write-Host "  [FAIL] Data mismatch: expected student=$StudentId, score=$testScore" -ForegroundColor Red
    }
} else {
    Write-Host "  [FAIL] ClickHouse has no data for student_id=$StudentId" -ForegroundColor Red
    Write-Host "  Possible causes:" -ForegroundColor Yellow
    Write-Host "    1. Debezium did not capture MySQL change (check connector status)" -ForegroundColor White
    Write-Host "    2. data-ana consumer not running (check /readyz cdc_consumer)" -ForegroundColor White
    Write-Host "    3. Kafka topic name mismatch (check debezium-register.json)" -ForegroundColor White
    exit 1
}

Write-Host ""

# ===== 5. Verify data-ana API =====
Write-Host "[5/5] Verifying data-ana query API..." -ForegroundColor Yellow

$h = @{Authorization="Bearer dev-token"}

try {
    $weakness = Invoke-RestMethod -Uri "http://localhost:3006/analytics/student/$StudentId/weakness" -Method Get -Headers $h -TimeoutSec 5 -ErrorAction Stop
    Write-Host "  [OK] /analytics/student/$StudentId/weakness" -ForegroundColor Green
    Write-Host "  Response: $($weakness | ConvertTo-Json -Depth 3)" -ForegroundColor Gray
} catch {
    Write-Host "  [WARN] /analytics/student/$StudentId/weakness failed: $($_.Exception.Message)" -ForegroundColor Yellow
}

try {
    $perf = Invoke-RestMethod -Uri "http://localhost:3006/analytics/class/$classId/performance" -Method Get -Headers $h -TimeoutSec 5 -ErrorAction Stop
    Write-Host "  [OK] /analytics/class/$classId/performance" -ForegroundColor Green
    Write-Host "  Response: $($perf | ConvertTo-Json -Depth 3)" -ForegroundColor Gray
} catch {
    Write-Host "  [WARN] /analytics/class/$classId/performance failed: $($_.Exception.Message)" -ForegroundColor Yellow
}

Write-Host ""
Write-Host "========================================" -ForegroundColor Cyan
Write-Host "  CDC Pipeline Test Complete" -ForegroundColor Cyan
Write-Host "  MySQL -> Debezium -> Kafka -> data-ana -> ClickHouse [OK]" -ForegroundColor Green
Write-Host "========================================" -ForegroundColor Cyan