# review_service/main.py
from fastapi import FastAPI, Request
import anthropic
import httpx
import base64
import os

app = FastAPI()
client = anthropic.Anthropic()

GITHUB_TOKEN = os.environ["GITHUB_TOKEN"]

REVIEW_SYSTEM = """You are a database reliability engineer reviewing database migrations.
You understand PostgreSQL, MySQL, and SQLite migration patterns and their failure modes.

For each migration, identify:
1. CRITICAL: Will cause downtime or data loss
2. WARNING: Risk under specific conditions (high load, large table, replication)
3. INFO: Style or best practice suggestions

Format your review as JSON:
{
  "verdict": "PASS" | "WARN" | "FAIL",
  "findings": [
    {
      "severity": "CRITICAL" | "WARNING" | "INFO",
      "line": <line number or null>,
      "finding": "Description of the issue",
      "recommendation": "What to do instead"
    }
  ],
  "summary": "One sentence overall assessment"
}"""

REVIEW_CHECKLIST = """
Review this migration for:

CRITICAL (must fix):
- ALTER TABLE on large tables without CONCURRENTLY or non-blocking DDL
- DROP COLUMN or DROP TABLE with no data validation first
- Truncating tables
- NOT NULL constraint added without default or multi-step approach
- Foreign key constraint without NOT VALID deferment on large tables
- DELETE without WHERE clause
- UPDATE that affects all rows without batching

WARNING (consider carefully):
- Index creation without CONCURRENTLY
- Column type changes (may require rewrite)
- Renaming columns without migration period
- Missing downgrade/rollback implementation
- Long-running transactions that could block other operations
- Adding index on already-indexed column

INFO (best practices):
- No comment explaining the business reason
- Hardcoded values instead of parameterized
- Not using IF EXISTS / IF NOT EXISTS
"""

async def get_migration_files(pr_number: int, repo: str) -> list[dict]:
    """Get changed migration files from a GitHub PR."""
    async with httpx.AsyncClient() as http:
        resp = await http.get(
            f"https://api.github.com/repos/{repo}/pulls/{pr_number}/files",
            headers={"Authorization": f"token {GITHUB_TOKEN}"}
        )
        files = resp.json()

    migration_files = []
    for file in files:
        filename = file["filename"]
        # Match common migration file patterns
        if any(pat in filename for pat in [
            "migrations/", "alembic/", "flyway/", "liquibase/",
            "_migration.", "migrate_"
        ]) and any(filename.endswith(ext) for ext in [".sql", ".py", ".xml"]):
            migration_files.append({
                "filename": filename,
                "status": file["status"],  # added, modified, removed
                "patch": file.get("patch", ""),
                "raw_url": file.get("raw_url", ""),
            })

    return migration_files

async def fetch_file_content(raw_url: str) -> str:
    async with httpx.AsyncClient() as http:
        resp = await http.get(
            raw_url,
            headers={"Authorization": f"token {GITHUB_TOKEN}"}
        )
        return resp.text

async def review_migration(filename: str, content: str, db_type: str = "postgresql") -> dict:
    prompt = f"""
Database type: {db_type}
Migration file: {filename}

{REVIEW_CHECKLIST}

Migration content:

# alembic/versions/abc123_add_index.py
def upgrade():
    op.create_index(
        'ix_orders_customer_id',
        'orders',
        ['customer_id']
    )

def downgrade():
    op.drop_index('ix_orders_customer_id', table_name='orders')

# test_review.py
import asyncio
from review_service.main import review_migration

# Dangerous migration
dangerous = """
def upgrade():
    op.execute("UPDATE users SET status = 'active'")
    op.add_column('orders', sa.Column('total', sa.Float(), nullable=False))

def downgrade():
    pass
"""

# Safe migration
safe = """
def upgrade():
    op.add_column(
        'users',
        sa.Column('preferences', postgresql.JSONB(), nullable=True)
    )

def downgrade():
    op.drop_column('users', 'preferences')
"""

async def main():
    print("=== Dangerous migration ===")
    result = await review_migration("test_migration.py", dangerous)
    print(result)

    print("\n=== Safe migration ===")
    result = await review_migration("test_migration.py", safe)
    print(result)

asyncio.run(main())

if any(pat in filename for pat in [
    "migrations/", "alembic/", "db/migrate/",
    "flyway/", "V__", "R__",  # Flyway versioned and repeatable migrations
    "liquibase/", "changelog/",  # Liquibase changesets
]) and any(filename.endswith(ext) for ext in [".sql", ".py", ".xml", ".yaml"]):