#!/usr/bin/env pwsh
<#
.SYNOPSIS
    Creates a zip package for LLM quality analysis of the crawler project.
    Includes: DB schema, sample data, all API Python source code.
#>
param(
    [string]$OutPath = "D:\Docker\crawler\create_zip_for_quality_analysis.zip",
    [string]$TempDir = "D:\temp\crawler_qa_pkg",
    [int]$SampleRows = 200,
    [switch]$NoCleanup
)

$ErrorActionPreference = "Stop"

# ---------------------------------------------------------------------------
# Prepare temp dir
# ---------------------------------------------------------------------------
Remove-Item -LiteralPath $TempDir -Recurse -Force -ErrorAction SilentlyContinue
New-Item -ItemType Directory -Force -Path "$TempDir\src", "$TempDir\db" | Out-Null

Write-Host "=== Gathering files ==="

# ---------------------------------------------------------------------------
# 1. Database schema
# ---------------------------------------------------------------------------
Write-Host "  DB schema..."
$schemaSql = docker exec crawler-db mariadb-dump -u root -proot-secret --no-data --skip-comments crawler_cache 2>&1
if ($LASTEXITCODE -ne 0) {
    Write-Warning "mariadb-dump failed, using fallback CREATE statements"
    $schemaSql = @"
CREATE TABLE domains (id INT AUTO_INCREMENT PRIMARY KEY, domain VARCHAR(255) NOT NULL UNIQUE, first_seen_at DOUBLE NOT NULL, last_crawled_at DOUBLE NOT NULL, crawl_count INT NOT NULL DEFAULT 0, total_bytes BIGINT NOT NULL DEFAULT 0);
CREATE TABLE pages (id INT AUTO_INCREMENT PRIMARY KEY, url VARCHAR(2048) NOT NULL, url_hash CHAR(32) NOT NULL, domain_id INT NOT NULL, stem VARCHAR(512) NOT NULL, status VARCHAR(32) NOT NULL DEFAULT 'ok', content_digest CHAR(64), markdown_bytes INT NOT NULL DEFAULT 0, html_bytes INT NOT NULL DEFAULT 0, image_count INT NOT NULL DEFAULT 0, images_bytes BIGINT NOT NULL DEFAULT 0, crawled_at DOUBLE NOT NULL, http_status INT DEFAULT NULL, elapsed_seconds DOUBLE DEFAULT NULL, consent_status VARCHAR(64) DEFAULT NULL, canonical_url VARCHAR(2048) NULL, canonical_url_hash CHAR(64) NULL, page_type VARCHAR(32) NOT NULL DEFAULT 'url', parent_canonical_url_hash CHAR(64) NULL, action_key CHAR(64) NULL, action_label VARCHAR(512) NULL, cache_policy VARCHAR(32) NULL, first_seen_at DOUBLE NULL, last_seen_at DOUBLE NULL, etag VARCHAR(512) NULL, last_modified VARCHAR(512) NULL);
CREATE TABLE page_links (id INT AUTO_INCREMENT PRIMARY KEY, page_url_hash CHAR(32) NOT NULL, source_url VARCHAR(2048) NOT NULL, target_url VARCHAR(2048) NOT NULL, link_text VARCHAR(1024) NOT NULL DEFAULT '', target_is_crawled BOOL NOT NULL DEFAULT FALSE, target_stem VARCHAR(512) DEFAULT NULL);
CREATE TABLE page_images (id INT AUTO_INCREMENT PRIMARY KEY, page_url_hash CHAR(32) NOT NULL, source_url VARCHAR(2048) NOT NULL, local_path VARCHAR(1024) NOT NULL, content_type VARCHAR(128) NOT NULL DEFAULT '', size_bytes INT NOT NULL DEFAULT 0, from_cache BOOL NOT NULL DEFAULT FALSE);
CREATE TABLE discovered_targets (id INT AUTO_INCREMENT PRIMARY KEY, discovery_key CHAR(71) NOT NULL, target_type VARCHAR(32) NOT NULL DEFAULT 'url', raw_url VARCHAR(2048) NULL, canonical_url VARCHAR(2048) NULL, canonical_url_hash CHAR(64) NULL, parent_canonical_url_hash CHAR(64) NULL, source_url VARCHAR(2048) NULL, link_text VARCHAR(1024) NOT NULL DEFAULT '', action_signature VARCHAR(2048) NULL, first_seen_at DOUBLE NOT NULL, last_seen_at DOUBLE NOT NULL, seen_count INT NOT NULL DEFAULT 1, crawl_status VARCHAR(32) NOT NULL DEFAULT 'discovered', reserved_at DOUBLE NULL, crawled_at DOUBLE NULL, error_text TEXT NULL);
CREATE TABLE page_actions (id INT AUTO_INCREMENT PRIMARY KEY, parent_canonical_url_hash CHAR(64) NOT NULL, action_key CHAR(64) NOT NULL, action_type VARCHAR(32) NOT NULL, selector VARCHAR(2048) NOT NULL DEFAULT '', label VARCHAR(1024) NOT NULL DEFAULT '', href VARCHAR(2048) NULL, before_url VARCHAR(2048) NULL, after_url VARCHAR(2048) NULL, result_type VARCHAR(32) NOT NULL DEFAULT 'unknown', virtual_url VARCHAR(2048) NULL, content_digest CHAR(64) NULL, created_at DOUBLE NOT NULL);
"@
}
Set-Content -LiteralPath "$TempDir\db\schema.sql" -Value $schemaSql -Encoding UTF8

# ---------------------------------------------------------------------------
# 2. DB sample data (tables with content)
# ---------------------------------------------------------------------------
Write-Host "  DB sample data..."
$tables = @("domains", "pages", "page_links", "page_images", "discovered_targets", "page_actions")
foreach ($table in $tables) {
    $count = docker exec crawler-db mariadb -u crawler -pcrawler-secret crawler_cache -N -e "SELECT COUNT(*) FROM $table" 2>&1
    if ($LASTEXITCODE -eq 0 -and $count -match '^\d+$' -and [int]$count -gt 0) {
        $data = docker exec crawler-db mariadb -u crawler -pcrawler-secret crawler_cache -e "SELECT * FROM $table LIMIT $SampleRows" 2>&1
        Set-Content -LiteralPath "$TempDir\db\data_$table.txt" -Value $data -Encoding UTF8
        Write-Host "    $table`: $count rows"
    } else {
        Write-Host "    $table`: empty"
        Set-Content -LiteralPath "$TempDir\db\data_$table.txt" -Value "(empty)" -Encoding UTF8
    }
}

# ---------------------------------------------------------------------------
# 3. Python source files (app/)
# ---------------------------------------------------------------------------
Write-Host "  Python sources..."
$srcFiles = @(
    "crawler.py", "server.py", "file_server.py", "crawl_db.py",
    "interactions.py", "gemini_detect.py", "webpage_vision_markdown.py",
    "requirements.txt"
)
foreach ($f in $srcFiles) {
    $src = "D:\Docker\crawler\app\$f"
    if (Test-Path $src) {
        Copy-Item -LiteralPath $src -Destination "$TempDir\src\$f"
        Write-Host "    $f  ($((Get-Item $src).Length) bytes)"
    }
}

# ---------------------------------------------------------------------------
# 4. Config files
# ---------------------------------------------------------------------------
Write-Host "  Config files..."
@("docker-compose.yaml", "Dockerfile", "Dockerfile.mcp", ".env") | ForEach-Object {
    $path = "D:\Docker\crawler\$_"
    if (Test-Path $path) {
        $content = Get-Content -LiteralPath $path -Raw -ErrorAction SilentlyContinue
        if ($_ -eq ".env") {
            # Strip API key values for safety
            $content = $content -replace '([A-Z_]+_API_KEY=).+', '$1***REDACTED***'
            $content = $content -replace '([A-Z_]+_SECRET=).+', '$1***REDACTED***'
            $content = $content -replace '([A-Z_]+_PASSWORD=).+', '$1***REDACTED***'
        }
        Set-Content -LiteralPath "$TempDir\src\$_.txt" -Value $content -Encoding UTF8
        Write-Host "    $_"
    }
}

# ---------------------------------------------------------------------------
# 5. Crawler log (last 500 lines)
# ---------------------------------------------------------------------------
$logPath = "D:\Docker\crawler\data\logs\crawler.log"
if (Test-Path $logPath) {
    $logContent = Get-Content -LiteralPath $logPath -Tail 500 -ErrorAction SilentlyContinue
    Set-Content -LiteralPath "$TempDir\crawler_log_tail500.txt" -Value $logContent -Encoding UTF8
    Write-Host "  crawler_log_tail500.txt"
}

# ---------------------------------------------------------------------------
# 6. Create zip
# ---------------------------------------------------------------------------
Write-Host "`n=== Creating zip ==="
Get-ChildItem -LiteralPath $TempDir -Recurse -File | Compress-Archive -DestinationPath $OutPath -Force

$zipSize = [math]::Round((Get-Item -LiteralPath $OutPath).Length / 1KB, 1)
Write-Host "Created: $OutPath"
Write-Host "Size: $zipSize KB"

# Show contents
Write-Host "`nContents:"
Add-Type -AssemblyName System.IO.Compression.FileSystem
[System.IO.Compression.ZipFile]::OpenRead($OutPath).Entries | ForEach-Object {
    Write-Host "  $($_.FullName)"
}

# Cleanup
if (-not $NoCleanup) {
    Remove-Item -LiteralPath $TempDir -Recurse -Force -ErrorAction SilentlyContinue
    Write-Host "`nTemp dir cleaned."
}
