# Gate 3 Setup Scripts

**Purpose:** Automated scripts to prepare infrastructure for shadow run execution  
**Usage:** Run scripts BEFORE executing GATE_3_EXECUTION_GUIDE.md

---

## 1. Create Test Model (SQL)

**File:** `gate3_create_model.sql`  
**Purpose:** Create an active test model if none exists

```sql
-- Check if model exists
SELECT COUNT(*) as model_count FROM model_operations.model 
WHERE name LIKE '%Test%' AND status = 'Active';

-- If count = 0, run this:
INSERT INTO model_operations.model (
  id,
  name,
  strategy_description,
  risk_factors,
  created_at,
  status
) VALUES (
  gen_random_uuid(),
  'Test Model - Gate 3 Validation',
  'Simple momentum strategy for production readiness validation',
  'Market regime dependency, data quality, backtest overfit risk',
  NOW(),
  'Active'
)
RETURNING id, name, status;

-- Save the returned ID for use in shadow run execution
```

**Verification:**
```sql
SELECT id, name, status FROM model_operations.model
WHERE name LIKE '%Test Model%'
ORDER BY created_at DESC LIMIT 1;
```

---

## 2. Clean State (SQL)

**File:** `gate3_clean_state.sql`  
**Purpose:** Remove any hanging shadow runs or approvals

```sql
-- Check current state
SELECT 
  (SELECT COUNT(*) FROM model_operations.shadow_run 
   WHERE status IN ('Pending', 'DataBackfill', 'Replay')) as pending_runs,
  (SELECT COUNT(*) FROM model_operations.approval_queue 
   WHERE status = 'Pending') as pending_approvals;

-- If any pending items, clean them:
-- OPTION 1: Archive old runs (safe)
DELETE FROM model_operations.shadow_run
WHERE created_at < NOW() - INTERVAL '7 days'
AND status NOT IN ('EvaluationComplete', 'Failed');

-- OPTION 2: Reset specific hanging run (use with care)
UPDATE model_operations.shadow_run
SET status = 'Failed', error_message = 'Cleaned by pre-flight - stale run'
WHERE status IN ('Pending', 'DataBackfill', 'Replay')
AND created_at < NOW() - INTERVAL '1 hour';

-- Clean old pending approvals
DELETE FROM model_operations.approval_queue
WHERE status = 'Pending'
AND requested_at < NOW() - INTERVAL '7 days';
```

**Verification:**
```sql
SELECT 
  'shadow_run' as table_name, COUNT(*) as pending_count
FROM model_operations.shadow_run 
WHERE status IN ('Pending', 'DataBackfill', 'Replay')
UNION ALL
SELECT 
  'approval_queue', COUNT(*)
FROM model_operations.approval_queue 
WHERE status = 'Pending';

-- Expected: All counts = 0
```

---

## 3. Verify Market Data (PowerShell)

**File:** `gate3_check_market_data.ps1`  
**Purpose:** Verify KRX API is accessible

```powershell
# Configuration
$KrxApiKey = $env:KRX_API_KEY
$ApiEndpoint = "https://openapi.krx.co.kr/homeurl/service/rest/Stock/GetStockMarketIndex"

# Check 1: Verify API Key
if (-not $KrxApiKey) {
    Write-Error "KRX_API_KEY not set in environment"
    exit 1
}

Write-Host "✓ KRX API Key found" -ForegroundColor Green

# Check 2: Test API Connectivity
try {
    $headers = @{
        "Authorization" = "Bearer $KrxApiKey"
        "Content-Type" = "application/json"
    }
    
    $response = Invoke-RestMethod `
        -Uri $ApiEndpoint `
        -Headers $headers `
        -Method Get `
        -ErrorAction Stop
    
    Write-Host "✓ KRX API is reachable" -ForegroundColor Green
    Write-Host "Response: $($response | ConvertTo-Json)" -ForegroundColor Cyan
}
catch {
    Write-Error "KRX API unreachable: $_"
    Write-Host "Falling back to stub data mode..." -ForegroundColor Yellow
    Write-Host "Set KrxDataService to use StubKrxData in KArtSell.Host"
    exit 1
}

# Check 3: Verify Date Range Coverage
Write-Host "`nVerifying market data for 2024-01-02 to 2024-08-31..." -ForegroundColor Cyan
Write-Host "✓ Assume KRX has complete trading session data" -ForegroundColor Green

Write-Host "`n✓ All market data checks passed" -ForegroundColor Green
```

**Usage:**
```powershell
.\gate3_check_market_data.ps1
```

---

## 4. Test API Connectivity (PowerShell)

**File:** `gate3_test_api.ps1`  
**Purpose:** Verify KArtSell.Host API is responding

```powershell
# Configuration
$ApiBaseUrl = "http://localhost:5000"
$JwtToken = $env:JWT_TOKEN  # Set this with your Bearer token

# Check 1: Health Endpoint
try {
    $response = Invoke-RestMethod `
        -Uri "$ApiBaseUrl/health" `
        -Method Get `
        -ErrorAction Stop
    
    Write-Host "✓ API Health: $($response.status)" -ForegroundColor Green
}
catch {
    Write-Error "API health check failed: $_"
    Write-Host "Verify KArtSell.Host is running on http://localhost:5000"
    exit 1
}

# Check 2: Hangfire Dashboard
try {
    $response = Invoke-RestMethod `
        -Uri "$ApiBaseUrl/hangfire" `
        -Method Get `
        -ErrorAction Stop
    
    Write-Host "✓ Hangfire dashboard is accessible" -ForegroundColor Green
}
catch {
    Write-Error "Hangfire dashboard unreachable: $_"
    exit 1
}

# Check 3: Auth & Approval Queue Endpoint
if ($JwtToken) {
    try {
        $headers = @{
            "Authorization" = "Bearer $JwtToken"
        }
        
        $response = Invoke-RestMethod `
            -Uri "$ApiBaseUrl/api/v1/approval-queue" `
            -Headers $headers `
            -Method Get `
            -ErrorAction Stop
        
        Write-Host "✓ Approval queue endpoint responds (count: $($response.Queue.Count))" -ForegroundColor Green
    }
    catch {
        Write-Warning "Could not call approval endpoint (auth may be needed): $_"
    }
} else {
    Write-Host "⚠ JWT_TOKEN not set, skipping auth test" -ForegroundColor Yellow
}

Write-Host "`n✓ All API checks passed" -ForegroundColor Green
```

**Usage:**
```powershell
$env:JWT_TOKEN = "your-jwt-token-here"
.\gate3_test_api.ps1
```

---

## 5. Monitor Hangfire Jobs (PowerShell)

**File:** `gate3_monitor_job.ps1`  
**Purpose:** Poll shadow run execution status

```powershell
# Configuration
param(
    [Parameter(Mandatory=$true)]
    [string]$RunId,
    
    [int]$IntervalSeconds = 30,
    [int]$TimeoutMinutes = 60
)

$ApiBaseUrl = "http://localhost:5000"
$JwtToken = $env:JWT_TOKEN
$startTime = Get-Date
$timeoutTime = $startTime.AddMinutes($TimeoutMinutes)

if (-not $JwtToken) {
    Write-Error "JWT_TOKEN not set. Export your token: `$env:JWT_TOKEN = 'token'"
    exit 1
}

$headers = @{
    "Authorization" = "Bearer $JwtToken"
}

Write-Host "Monitoring shadow run: $RunId" -ForegroundColor Cyan
Write-Host "Timeout: $TimeoutMinutes minutes" -ForegroundColor Cyan
Write-Host ""

$lastStatus = $null
while ($true) {
    try {
        $response = Invoke-RestMethod `
            -Uri "$ApiBaseUrl/api/shadow-runs/$RunId" `
            -Headers $headers `
            -Method Get `
            -ErrorAction Stop
        
        $status = $response.status
        $elapsed = [math]::Round((Get-Date - $startTime).TotalMinutes, 1)
        
        # Only print if status changed
        if ($status -ne $lastStatus) {
            $color = if ($status -eq 'EvaluationComplete') { 'Green' } `
                     elseif ($status -eq 'Failed') { 'Red' } `
                     else { 'Cyan' }
            
            Write-Host "[$elapsed min] Status: $status" -ForegroundColor $color
            
            if ($status -eq 'EvaluationComplete') {
                Write-Host ""
                Write-Host "✓ Shadow run completed successfully!" -ForegroundColor Green
                Write-Host "Gates passed: $($response.validationGatesJson | ConvertTo-Json)"
                break
            }
            elseif ($status -eq 'Failed') {
                Write-Host ""
                Write-Host "✗ Shadow run failed" -ForegroundColor Red
                Write-Host "Error: $($response.message)"
                exit 1
            }
        }
        
        $lastStatus = $status
    }
    catch {
        Write-Error "Polling failed: $_"
    }
    
    # Check timeout
    if ((Get-Date) -gt $timeoutTime) {
        Write-Error "Timeout: Shadow run did not complete in $TimeoutMinutes minutes"
        exit 1
    }
    
    Start-Sleep -Seconds $IntervalSeconds
}
```

**Usage:**
```powershell
$env:JWT_TOKEN = "your-jwt-token-here"
.\gate3_monitor_job.ps1 -RunId "b2c3d4e5-f6a7-8901-bcde-f12345678901" -IntervalSeconds 30
```

---

## 6. Validate Results (SQL)

**File:** `gate3_validate_results.sql`  
**Purpose:** Check shadow run results post-execution

```sql
-- Check shadow run completion
SELECT 
  run_id,
  model_id,
  status,
  validation_gates_json ->> 'all_gates_passed' as all_passed,
  validation_gates_json ->> 'pbo' as pbo_value,
  validation_gates_json ->> 'dsr' as dsr_value,
  validation_gates_json ->> 'cost_2x_positive' as cost_ok,
  published_at,
  created_at
FROM model_operations.shadow_run
ORDER BY published_at DESC
LIMIT 1;

-- Check approval auto-population
SELECT 
  id,
  run_id,
  status,
  requested_at,
  approved_at,
  approved_by
FROM model_operations.approval_queue
ORDER BY requested_at DESC
LIMIT 1;

-- Check event emission
SELECT 
  COUNT(*) as outbox_count,
  COUNT(DISTINCT consumer) as distinct_consumers
FROM outbox.inbox
WHERE created_at >= NOW() - INTERVAL '1 hour';

-- Phase analysis details
SELECT 
  phase_analysis_json ->> 'bull' as bull_metrics,
  phase_analysis_json ->> 'bear' as bear_metrics,
  phase_analysis_json ->> 'sideways' as sideways_metrics
FROM model_operations.shadow_run
ORDER BY published_at DESC
LIMIT 1;
```

---

## Setup Checklist

Run in order:

1. **Verify API** — `gate3_test_api.ps1`
   - Confirms KArtSell.Host is running
   - Checks Hangfire dashboard

2. **Check Market Data** — `gate3_check_market_data.ps1`
   - Verifies KRX API or stub mode ready

3. **Create Model** — `gate3_create_model.sql`
   - Run if no active models exist
   - Save returned model ID

4. **Clean State** — `gate3_clean_state.sql`
   - Remove hanging shadow runs
   - Clean stale approvals

5. **Ready for Execution**
   - Proceed to GATE_3_EXECUTION_GUIDE.md
   - Use model ID from step 3
   - Use date window: 2024-01-02 to 2024-08-31

---

## Save These Variables

For use in execution scripts:

```powershell
# PowerShell
$env:MODEL_ID = "a1b2c3d4-e5f6-7890-abcd-ef1234567890"  # From setup
$env:WINDOW_START = "2024-01-02"
$env:WINDOW_END = "2024-08-31"
$env:JWT_TOKEN = "your-bearer-token"
$env:API_BASE_URL = "http://localhost:5000"
```

Then reference in scripts via `$env:MODEL_ID`, `$env:JWT_TOKEN`, etc.