"""
Codesage Backend API
FastAPI server for GitHub App integration
Connects desktop app to GitHub securely
"""

from fastapi import FastAPI, HTTPException, Request, BackgroundTasks
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel
from typing import Optional, List, Dict
import json
import os
from pathlib import Path
from dotenv import load_dotenv
from supabase import create_client, Client
import logging

# Configure logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

from github_api import GitHubAppAuth, GitHubInsights, TokenManager
from webhooks import GitHubWebhookHandler

# Load environment variables from .env file
load_dotenv()


# Initialize FastAPI app
app = FastAPI(
    title="Codesage Backend API",
    description="Enterprise-grade GitHub insights for development teams",
    version="1.0.0"
)

# CORS configuration (allow desktop app to connect)
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],  # In production, specify your desktop app origins
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

# Load environment variables
APP_ID = os.getenv("GITHUB_APP_ID")
PRIVATE_KEY_PATH = os.getenv("GITHUB_PRIVATE_KEY_PATH", "private-key.pem")
PRIVATE_KEY_CONTENT = PRIVATE_KEY_PATH  # Direct private key content
SUPABASE_URL = os.getenv("SUPABASE_URL")
SUPABASE_SECRET_KEY = os.getenv("SUPABASE_SECRET_KEY")
GITHUB_WEBHOOK_SECRET = os.getenv("GITHUB_WEBHOOK_SECRET", "")
DEFAULT_FIREBASE_ID = os.getenv("DEFAULT_FIREBASE_ID", "JDfZoVuGJhdLBEvR7rVCQKBG9r02")

# Initialize GitHub App authentication
if APP_ID:
    try:
        # Try to get private key from environment variable first (production)
        if PRIVATE_KEY_CONTENT:
            private_key = PRIVATE_KEY_CONTENT
            logger.info("Using GitHub private key from GITHUB_PRIVATE_KEY env var")
        # Fallback to file path (development)
        elif os.path.exists(PRIVATE_KEY_PATH):
            with open(PRIVATE_KEY_PATH, "r") as f:
                private_key = f.read()
            logger.info(f"Using GitHub private key from file: {PRIVATE_KEY_PATH}")
        else:
            raise FileNotFoundError(f"Private key not found at {PRIVATE_KEY_PATH} and GITHUB_PRIVATE_KEY env var not set")
        
        github_auth = GitHubAppAuth(APP_ID, private_key)
        token_manager = TokenManager(github_auth)
        logger.info("✅ GitHub App configured successfully")
    except Exception as e:
        github_auth = None
        token_manager = None
        logger.error(f"❌ GitHub App configuration failed: {str(e)}")
else:
    github_auth = None
    token_manager = None
    logger.error("❌ GITHUB_APP_ID not configured")

# Initialize Supabase client
if SUPABASE_URL and SUPABASE_SECRET_KEY:
    supabase: Optional[Client] = create_client(SUPABASE_URL, SUPABASE_SECRET_KEY)
else:
    supabase = None
    print("⚠️ WARNING: Supabase credentials not configured")

# Initialize webhook handler
webhook_handler = GitHubWebhookHandler(supabase, GITHUB_WEBHOOK_SECRET) if supabase else None

# Simple JSON storage for installations
INSTALLATIONS_FILE = "installations.json"


# ============================================================================
# DATA MODELS
# ============================================================================

class InstallationCallback(BaseModel):
    """Data received from GitHub installation callback"""
    installation_id: int
    org: str
    setup_action: Optional[str] = None


class InsightsRequest(BaseModel):
    """Request for repository insights from desktop app"""
    firebase_id: str  # Now required
    org: str
    repo: str


class TokenRefreshRequest(BaseModel):
    """Request to refresh installation token"""
    installation_id: int


class GitHubSyncRequest(BaseModel):
    """Request to sync GitHub data into Supabase"""
    firebase_id: str
    org: str


# ============================================================================
# UTILITY FUNCTIONS
# ============================================================================

def load_installations() -> Dict:
    """Load installations from JSON file"""
    if os.path.exists(INSTALLATIONS_FILE):
        with open(INSTALLATIONS_FILE, "r") as f:
            return json.load(f)
    return {}


def save_installation(org: str, installation_id: int):
    """Save installation to JSON file"""
    installations = load_installations()
    installations[org] = {
        "installation_id": installation_id,
        "installed_at": None  # Could add timestamp
    }
    
    with open(INSTALLATIONS_FILE, "w") as f:
        json.dump(installations, f, indent=2)


def get_installation_id(org: str) -> Optional[int]:
    """Get installation ID for an organization (legacy from file system)"""
    installations = load_installations()
    org_data = installations.get(org)
    
    if org_data:
        return org_data.get("installation_id")
    return None


def get_installation_id_for_user(firebase_id: str) -> Optional[int]:
    """Get installation ID for a user from Supabase Users table"""
    if not supabase:
        logger.error("Supabase not configured")
        return None
    
    try:
        result = supabase.table("Users").select("installation_id").eq("firebase_id", firebase_id).limit(1).execute()
        if result.data and result.data[0].get("installation_id"):
            return result.data[0]["installation_id"]
    except Exception as e:
        logger.error(f"Failed to get installation_id for user {firebase_id}: {str(e)}")
    
    return None


def ensure_user_exists(firebase_id: str):
    if not supabase:
        raise HTTPException(status_code=500, detail="Supabase not configured")

    result = supabase.table("Users").select("firebase_id").eq("firebase_id", firebase_id).limit(1).execute()
    if not result.data:
        raise HTTPException(status_code=404, detail="User not found for firebase_id")


def build_repo_rollup(org: str, repo: str, insights: GitHubInsights) -> Dict:
    repo_info = insights.get_repository_info(org, repo)
    commits = insights.get_commits(org, repo, per_page=100)
    pull_requests = insights.get_pull_requests(org, repo, state="all")
    issues = insights.get_issues(org, repo, state="all")
    contributors = insights.get_contributors(org, repo)
    languages = insights.get_languages(org, repo)

    return {
        "owner": repo_info.get("owner", {}).get("login"),
        "repo": repo_info.get("name"),
        "full_name": repo_info.get("full_name"),
        "private": repo_info.get("private"),
        "default_branch": repo_info.get("default_branch"),
        "language": repo_info.get("language"),
        "html_url": repo_info.get("html_url"),
        "updated_at": repo_info.get("updated_at"),
        "pushed_at": repo_info.get("pushed_at"),
        "description": repo_info.get("description"),
        "stars": repo_info.get("stargazers_count", 0),
        "forks": repo_info.get("forks_count", 0),
        "open_issues": repo_info.get("open_issues_count", 0),
        "created_at": repo_info.get("created_at"),
        "size_kb": repo_info.get("size", 0),
        "commit_count": len(commits),
        "pr_count": len(pull_requests),
        "issue_count": len(issues),
        "contributor_count": len(contributors),
        "languages_json": languages,
    }


def sync_installation_to_supabase(firebase_id: str, org: str, installation_id: int) -> int:
    if not github_auth:
        logger.error("GitHub App not configured")
        raise HTTPException(status_code=500, detail="GitHub App not configured")
    if not supabase:
        logger.error("Supabase not configured")
        raise HTTPException(status_code=500, detail="Supabase not configured")

    try:
        logger.info(f"Starting sync for firebase_id={firebase_id}, org={org}, installation_id={installation_id}")
        
        # Check if user exists
        logger.info(f"Checking if user exists with firebase_id={firebase_id}")
        result = supabase.table("Users").select("firebase_id").eq("firebase_id", firebase_id).limit(1).execute()
        if not result.data:
            logger.error(f"User not found in Supabase for firebase_id={firebase_id}")
            return 0

        token = token_manager.get_token(installation_id)
        repos = github_auth.get_installation_repositories(token)
        logger.info(f"Found {len(repos)} repositories for {org}")
        
        insights = GitHubInsights(token)

        # Build repo rollups
        rows = []
        for i, repo in enumerate(repos):
            try:
                repo_name = repo.get("name")
                logger.info(f"Processing repo {i+1}/{len(repos)}: {repo_name}")
                rollup = build_repo_rollup(org, repo_name, insights)
                rollup["firebase_id"] = firebase_id
                rollup["installation_id"] = installation_id
                rows.append(rollup)
            except Exception as e:
                logger.warning(f"Failed to process repo {repo_name}: {str(e)}")
                continue

        if rows:
            logger.info(f"Upserting {len(rows)} repos into github table")
            supabase.table("github").upsert(rows, on_conflict="firebase_id,full_name").execute()
            logger.info(f"Successfully synced {len(rows)} repos to github table")
            
            # Now sync detail data for each repo
            for i, repo in enumerate(repos):
                try:
                    repo_name = repo.get("name")
                    full_name = f"{org}/{repo_name}"
                    logger.info(f"Syncing details for repo {i+1}/{len(repos)}: {repo_name}")
                    
                    # Get github_id
                    github_result = supabase.table("github").select("id").eq("firebase_id", firebase_id).eq("full_name", full_name).limit(1).execute()
                    if not github_result.data:
                        logger.warning(f"Could not find {full_name} in github table, skipping details")
                        continue
                    
                    github_id = github_result.data[0]["id"]
                    
                    # Sync commits
                    try:
                        commits = insights.get_commits(org, repo_name, per_page=100)
                        commit_rows = []
                        for commit in commits:
                            commit_data = commit.get("commit", {})
                            author_data = commit_data.get("author", {})
                            commit_rows.append({
                                "github_id": github_id,
                                "firebase_id": firebase_id,
                                "sha": commit.get("sha"),
                                "message": commit_data.get("message"),
                                "author": author_data.get("name"),
                                "author_email": author_data.get("email"),
                                "committed_date": author_data.get("date"),
                            })
                        if commit_rows:
                            supabase.table("commits").upsert(commit_rows, on_conflict="firebase_id,sha").execute()
                            logger.info(f"  Stored {len(commit_rows)} commits")
                    except Exception as e:
                        logger.warning(f"  Failed to sync commits: {str(e)}")
                    
                    # Sync issues
                    try:
                        issues = insights.get_issues(org, repo_name, state="all")
                        issue_rows = []
                        for issue in issues:
                            if "pull_request" in issue:
                                continue
                            issue_rows.append({
                                "github_id": github_id,
                                "firebase_id": firebase_id,
                                "issue_id": issue.get("id"),
                                "issue_number": issue.get("number"),
                                "title": issue.get("title"),
                                "body": issue.get("body"),
                                "state": issue.get("state"),
                                "author": issue.get("user", {}).get("login"),
                                "created_at": issue.get("created_at"),
                                "updated_at": issue.get("updated_at"),
                                "url": issue.get("html_url"),
                                "labels": [label.get("name") for label in issue.get("labels", [])],
                                "comments": issue.get("comments", 0),
                                "reactions": issue.get("reactions", {}).get("total_count", 0),
                            })
                        if issue_rows:
                            supabase.table("issues").upsert(issue_rows, on_conflict="firebase_id,issue_id").execute()
                            logger.info(f"  Stored {len(issue_rows)} issues")
                    except Exception as e:
                        logger.warning(f"  Failed to sync issues: {str(e)}")
                    
                    # Sync pull requests
                    try:
                        prs = insights.get_pull_requests(org, repo_name, state="all")
                        pr_rows = []
                        for pr in prs:
                            pr_rows.append({
                                "github_id": github_id,
                                "firebase_id": firebase_id,
                                "pr_id": pr.get("id"),
                                "pr_number": pr.get("number"),
                                "title": pr.get("title"),
                                "body": pr.get("body"),
                                "state": pr.get("state"),
                                "author": pr.get("user", {}).get("login"),
                                "created_at": pr.get("created_at"),
                                "updated_at": pr.get("updated_at"),
                                "merged_at": pr.get("merged_at"),
                                "closed_at": pr.get("closed_at"),
                                "url": pr.get("html_url"),
                                "head_branch": pr.get("head", {}).get("ref"),
                                "base_branch": pr.get("base", {}).get("ref"),
                                "draft": pr.get("draft", False),
                                "mergeable": pr.get("mergeable"),
                                "comments": pr.get("comments", 0),
                                "commits": pr.get("commits", 0),
                                "additions": pr.get("additions", 0),
                                "deletions": pr.get("deletions", 0),
                                "changed_files": pr.get("changed_files", 0),
                            })
                        if pr_rows:
                            supabase.table("pull_requests").upsert(pr_rows, on_conflict="firebase_id,pr_id").execute()
                            logger.info(f"  Stored {len(pr_rows)} pull requests")
                    except Exception as e:
                        logger.warning(f"  Failed to sync pull requests: {str(e)}")
                    
                    # Sync contributors
                    try:
                        contributors = insights.get_contributors(org, repo_name)
                        contributor_rows = []
                        for contributor in contributors:
                            contributor_rows.append({
                                "github_id": github_id,
                                "firebase_id": firebase_id,
                                "username": contributor.get("login"),
                                "contributions": contributor.get("contributions", 0),
                                "avatar_url": contributor.get("avatar_url"),
                                "profile_url": contributor.get("html_url"),
                            })
                        if contributor_rows:
                            supabase.table("contributors").upsert(contributor_rows, on_conflict="firebase_id,github_id,username").execute()
                            logger.info(f"  Stored {len(contributor_rows)} contributors")
                    except Exception as e:
                        logger.warning(f"  Failed to sync contributors: {str(e)}")
                    
                except Exception as e:
                    logger.warning(f"Failed to sync details for {repo_name}: {str(e)}")
                    continue
            
            logger.info(f"Completed full sync for {len(rows)} repos")
        else:
            logger.warning("No repos to sync")

        return len(rows)
    except Exception as e:
        logger.error(f"Sync failed: {str(e)}", exc_info=True)
        return 0


# ============================================================================
# API ENDPOINTS
# ============================================================================

@app.get("/")
def root():
    """Health check endpoint"""
    return {
        "service": "Codesage Backend API",
        "status": "operational",
        "configured": github_auth is not None
    }


@app.get("/health")
def health_check():
    """Detailed health check"""
    return {
        "status": "healthy",
        "github_app_configured": github_auth is not None,
        "installations_count": len(load_installations())
    }


@app.get("/github/callback")
async def github_callback(request: Request, background_tasks: BackgroundTasks):
    """
    Handle GitHub App installation callback
    
    After org installs Codesage, GitHub redirects here with installation_id and state (firebase_uid)
    Frontend should redirect to: https://github.com/apps/{app_slug}/installations/new?state={firebase_uid}
    GitHub redirects back with: /github/callback?installation_id=...&state={firebase_uid}
    """
    # Get query parameters
    query_params = dict(request.query_params)
    
    installation_id = query_params.get("installation_id")
    setup_action = query_params.get("setup_action")
    firebase_id = query_params.get("state")  # GitHub passes firebase_uid via state parameter
    
    # Fallback to default if state not provided (for backward compatibility)
    if not firebase_id:
        logger.warning("No firebase_id in state parameter, using default")
        firebase_id = DEFAULT_FIREBASE_ID
    
    if not installation_id:
        return {
            "success": False,
            "message": "Missing installation_id parameter",
            "received_params": query_params
        }
    
    try:
        installation_id = int(installation_id)
    except ValueError:
        return {
            "success": False,
            "message": "Invalid installation_id format",
            "installation_id": installation_id
        }
    
    # Try to get org name if credentials are configured
    org_name = None
    
    if github_auth:
        try:
            # Get installation token to verify and fetch org info
            token_data = github_auth.get_installation_token(installation_id)
            token = token_data["token"]
            
            # Get installation repositories to extract org name
            repos = github_auth.get_installation_repositories(token)
            
            if repos:
                org_name = repos[0]["owner"]["login"]
                
        except Exception as e:
            print(f"⚠️ Could not authenticate with GitHub: {str(e)}")
            print(f"   Installation ID {installation_id} captured but org name unknown")
    
    # Save with org name if we got it, otherwise use placeholder
    if org_name:
        save_installation(org_name, installation_id)
        sync_result = None
        if supabase:
            # Save installation_id to Users table
            try:
                supabase.table("Users").update({"installation_id": installation_id}).eq("firebase_id", firebase_id).execute()
                logger.info(f"Saved installation_id {installation_id} for user {firebase_id}")
            except Exception as e:
                logger.error(f"Failed to save installation_id to Users table: {str(e)}")
            
            # Run heavy sync in background so callback returns immediately
            background_tasks.add_task(
                sync_installation_to_supabase,
                firebase_id,  # Use firebase_id from state parameter
                org_name,
                installation_id
            )
            sync_result = {
                "queued": True,
                "firebase_id": firebase_id
            }

        return {
            "success": True,
            "message": f"✅ Successfully installed Codesage for {org_name}",
            "org": org_name,
            "installation_id": installation_id,
            "supabase_sync": sync_result
        }
    else:
        # Save with placeholder - user can update via /github/install
        placeholder = f"installation_{installation_id}"
        save_installation(placeholder, installation_id)
        return {
            "success": True,
            "message": "✅ Installation ID captured! Use /github/install to register with org name",
            "installation_id": installation_id,
            "note": f"Saved as '{placeholder}'. Update your .env file with correct private key path and use POST /github/install to register with org name"
        }


@app.post("/github/install")
def register_installation(data: InstallationCallback):
    """
    Manually register an installation (alternative to callback)
    
    Use this during development or for manual setup
    """
    save_installation(data.org, data.installation_id)
    
    return {
        "success": True,
        "message": f"Installation registered for {data.org}",
        "installation_id": data.installation_id
    }


@app.post("/github/webhooks")
async def handle_github_webhook(request: Request):
    """
    Handle GitHub webhook events in real-time
    
    GitHub sends POST requests here when repository events occur:
    - push: New commits pushed
    - pull_request: PR created/updated/merged
    - issues: Issue created/updated/closed
    - repository: Repository created/deleted/renamed
    
    Verify signature header: X-Hub-Signature-256
    Event type header: X-GitHub-Event
    """
    if not webhook_handler:
        return {
            "success": False,
            "message": "Webhook handler not configured (Supabase not available)"
        }
    
    try:
        # Get headers
        signature = request.headers.get("X-Hub-Signature-256")
        event_type = request.headers.get("X-GitHub-Event")
        
        if not signature or not event_type:
            return {
                "success": False,
                "message": "Missing required headers (X-Hub-Signature-256, X-GitHub-Event)"
            }
        
        # Get raw body for signature verification
        body = await request.body()
        
        # Verify signature
        if not webhook_handler.verify_signature(body, signature):
            logger.warning(f"Invalid webhook signature for {event_type}")
            return {
                "success": False,
                "message": "Invalid signature"
            }
        
        # Parse payload
        payload = await request.json()
        
        # Route to handler
        result = webhook_handler.handle_webhook(event_type, payload)
        
        return result
        
    except Exception as e:
        logger.error(f"Failed to process webhook: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"Failed to process webhook: {str(e)}"
        }


@app.get("/installations")
def list_installations():
    """
    List all registered installations
    Shows which orgs have installed Codesage
    """
    installations = load_installations()
    
    return {
        "count": len(installations),
        "installations": installations
    }


@app.get("/installations/{org}")
def get_org_installation(org: str):
    """Get installation details for a specific org"""
    installation_id = get_installation_id(org)
    
    if not installation_id:
        raise HTTPException(status_code=404, detail=f"No installation found for {org}")
    
    return {
        "org": org,
        "installation_id": installation_id
    }


@app.get("/repos/{org}")
def list_org_repositories(org: str):
    """
    List all repositories accessible for an org
    This is what the desktop app calls first
    """
    if not github_auth:
        raise HTTPException(status_code=500, detail="GitHub App not configured")
    
    installation_id = get_installation_id(org)
    if not installation_id:
        raise HTTPException(status_code=404, detail=f"No installation found for {org}")
    
    try:
        # Get fresh token
        token = token_manager.get_token(installation_id)
        
        # Fetch repositories
        repos = github_auth.get_installation_repositories(token)
        
        # Return simplified list
        return {
            "org": org,
            "count": len(repos),
            "repositories": [
                {
                    "name": repo["name"],
                    "full_name": repo["full_name"],
                    "private": repo["private"],
                    "default_branch": repo.get("default_branch"),
                    "language": repo.get("language"),
                    "updated_at": repo.get("updated_at")
                }
                for repo in repos
            ]
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Failed to fetch repositories: {str(e)}")


@app.post("/insights/commits")
def get_commits(data: InsightsRequest):
    """
    Get commit history for a repository and store in Supabase
    firebase_id is required and will be passed in request body
    """
    if not github_auth:
        raise HTTPException(status_code=500, detail="GitHub App not configured")
    if not supabase:
        raise HTTPException(status_code=500, detail="Supabase not configured")
    
    installation_id = get_installation_id_for_user(data.firebase_id)
    if not installation_id:
        raise HTTPException(status_code=404, detail=f"No GitHub installation found for user {data.firebase_id}")
    
    try:
        # Fetch commits from GitHub
        token = token_manager.get_token(installation_id)
        insights = GitHubInsights(token)
        commits = insights.get_commits(data.org, data.repo, per_page=100)
        
        # Get github table id for this repo
        full_name = f"{data.org}/{data.repo}"
        github_result = supabase.table("github").select("id").eq("firebase_id", data.firebase_id).eq("full_name", full_name).limit(1).execute()
        
        if not github_result.data:
            raise HTTPException(status_code=404, detail=f"Repo {full_name} not found in github table for this user")
        
        github_id = github_result.data[0]["id"]
        
        # Transform and store commits
        commit_rows = []
        for commit in commits:
            commit_data = commit.get("commit", {})
            author_data = commit_data.get("author", {})
            
            commit_rows.append({
                "github_id": github_id,
                "firebase_id": data.firebase_id,
                "sha": commit.get("sha"),
                "message": commit_data.get("message"),
                "author": author_data.get("name"),
                "author_email": author_data.get("email"),
                "committed_date": author_data.get("date"),
            })
        
        if commit_rows:
            supabase.table("commits").upsert(commit_rows, on_conflict="firebase_id,sha").execute()
            logger.info(f"Stored {len(commit_rows)} commits for {full_name}")
        
        return {
            "org": data.org,
            "repo": data.repo,
            "count": len(commits),
            "stored": len(commit_rows),
            "commits": commits
        }
    except Exception as e:
        logger.error(f"Failed to fetch/store commits: {str(e)}")
        raise HTTPException(status_code=500, detail=f"Failed to fetch commits: {str(e)}")


@app.post("/insights/pull-requests")
def get_pull_requests(data: InsightsRequest):
    """Get pull requests for a repository and store in Supabase
    firebase_id is required and will be passed in request body
    """
    if not github_auth:
        raise HTTPException(status_code=500, detail="GitHub App not configured")
    if not supabase:
        raise HTTPException(status_code=500, detail="Supabase not configured")
    
    installation_id = get_installation_id_for_user(data.firebase_id)
    if not installation_id:
        raise HTTPException(status_code=404, detail=f"No GitHub installation found for user {data.firebase_id}")
    
    try:
        # Fetch pull requests from GitHub
        token = token_manager.get_token(installation_id)
        insights = GitHubInsights(token)
        prs = insights.get_pull_requests(data.org, data.repo, state="all")
        
        # Get github table id for this repo
        full_name = f"{data.org}/{data.repo}"
        github_result = supabase.table("github").select("id").eq("firebase_id", data.firebase_id).eq("full_name", full_name).limit(1).execute()
        
        if not github_result.data:
            raise HTTPException(status_code=404, detail=f"Repo {full_name} not found in github table for this user")
        
        github_id = github_result.data[0]["id"]
        
        # Transform and store pull requests
        pr_rows = []
        for pr in prs:
            pr_rows.append({
                "github_id": github_id,
                "firebase_id": data.firebase_id,
                "pr_id": pr.get("id"),
                "pr_number": pr.get("number"),
                "title": pr.get("title"),
                "body": pr.get("body"),
                "state": pr.get("state"),
                "author": pr.get("user", {}).get("login"),
                "created_at": pr.get("created_at"),
                "updated_at": pr.get("updated_at"),
                "merged_at": pr.get("merged_at"),
                "closed_at": pr.get("closed_at"),
                "url": pr.get("html_url"),
                "head_branch": pr.get("head", {}).get("ref"),
                "base_branch": pr.get("base", {}).get("ref"),
                "draft": pr.get("draft", False),
                "mergeable": pr.get("mergeable"),
                "comments": pr.get("comments", 0),
                "commits": pr.get("commits", 0),
                "additions": pr.get("additions", 0),
                "deletions": pr.get("deletions", 0),
                "changed_files": pr.get("changed_files", 0),
            })
        
        if pr_rows:
            supabase.table("pull_requests").upsert(pr_rows, on_conflict="firebase_id,pr_id").execute()
            logger.info(f"Stored {len(pr_rows)} pull requests for {full_name}")
        
        return {
            "org": data.org,
            "repo": data.repo,
            "count": len(prs),
            "stored": len(pr_rows),
            "pull_requests": prs
        }
    except Exception as e:
        logger.error(f"Failed to fetch/store pull requests: {str(e)}")
        raise HTTPException(status_code=500, detail=f"Failed to fetch pull requests: {str(e)}")


@app.post("/insights/issues")
def get_issues(data: InsightsRequest):
    """Get issues for a repository and store in Supabase
    firebase_id is required and will be passed in request body
    """
    if not github_auth:
        raise HTTPException(status_code=500, detail="GitHub App not configured")
    if not supabase:
        raise HTTPException(status_code=500, detail="Supabase not configured")
    
    installation_id = get_installation_id_for_user(data.firebase_id)
    if not installation_id:
        raise HTTPException(status_code=404, detail=f"No GitHub installation found for user {data.firebase_id}")
    
    try:
        # Fetch issues from GitHub
        token = token_manager.get_token(installation_id)
        insights = GitHubInsights(token)
        issues = insights.get_issues(data.org, data.repo, state="all")
        
        # Get github table id for this repo
        full_name = f"{data.org}/{data.repo}"
        github_result = supabase.table("github").select("id").eq("firebase_id", data.firebase_id).eq("full_name", full_name).limit(1).execute()
        
        if not github_result.data:
            raise HTTPException(status_code=404, detail=f"Repo {full_name} not found in github table for this user")
        
        github_id = github_result.data[0]["id"]
        
        # Transform and store issues
        issue_rows = []
        for issue in issues:
            # Skip pull requests (they appear in issues API too)
            if "pull_request" in issue:
                continue
                
            issue_rows.append({
                "github_id": github_id,
                "firebase_id": data.firebase_id,
                "issue_id": issue.get("id"),
                "issue_number": issue.get("number"),
                "title": issue.get("title"),
                "body": issue.get("body"),
                "state": issue.get("state"),
                "author": issue.get("user", {}).get("login"),
                "created_at": issue.get("created_at"),
                "updated_at": issue.get("updated_at"),
                "url": issue.get("html_url"),
                "labels": [label.get("name") for label in issue.get("labels", [])],
                "comments": issue.get("comments", 0),
                "reactions": issue.get("reactions", {}).get("total_count", 0),
            })
        
        if issue_rows:
            supabase.table("issues").upsert(issue_rows, on_conflict="firebase_id,issue_id").execute()
            logger.info(f"Stored {len(issue_rows)} issues for {full_name}")
        
        return {
            "org": data.org,
            "repo": data.repo,
            "count": len(issues),
            "stored": len(issue_rows),
            "issues": issues
        }
    except Exception as e:
        logger.error(f"Failed to fetch/store issues: {str(e)}")
        raise HTTPException(status_code=500, detail=f"Failed to fetch issues: {str(e)}")


@app.post("/github/sync")
def sync_github_to_supabase(data: GitHubSyncRequest):
    """
    Sync GitHub repo data and rollup stats into Supabase
    Trigger this after user creation with firebase_id
    """
    if not github_auth:
        raise HTTPException(status_code=500, detail="GitHub App not configured")
    if not supabase:
        raise HTTPException(status_code=500, detail="Supabase not configured")

    installation_id = get_installation_id_for_user(data.firebase_id)
    if not installation_id:
        raise HTTPException(status_code=404, detail=f"No GitHub installation found for user {data.firebase_id}")

    try:
        synced = sync_installation_to_supabase(data.firebase_id, data.org, installation_id)
        return {
            "success": True,
            "org": data.org,
            "synced_repos": synced
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Failed to sync GitHub data: {str(e)}")


@app.post("/insights/contributors")
def get_contributors(data: InsightsRequest):
    """Get contributors and their stats, store in Supabase
    firebase_id is required and will be passed in request body
    """
    if not github_auth:
        raise HTTPException(status_code=500, detail="GitHub App not configured")
    if not supabase:
        raise HTTPException(status_code=500, detail="Supabase not configured")
    
    installation_id = get_installation_id_for_user(data.firebase_id)
    if not installation_id:
        raise HTTPException(status_code=404, detail=f"No GitHub installation found for user {data.firebase_id}")
    
    try:
        # Fetch contributors from GitHub
        token = token_manager.get_token(installation_id)
        insights = GitHubInsights(token)
        contributors = insights.get_contributors(data.org, data.repo)
        
        # Get github table id for this repo
        full_name = f"{data.org}/{data.repo}"
        github_result = supabase.table("github").select("id").eq("firebase_id", data.firebase_id).eq("full_name", full_name).limit(1).execute()
        
        if not github_result.data:
            raise HTTPException(status_code=404, detail=f"Repo {full_name} not found in github table for this user")
        
        github_id = github_result.data[0]["id"]
        
        # Transform and store contributors
        contributor_rows = []
        for contributor in contributors:
            contributor_rows.append({
                "github_id": github_id,
                "firebase_id": data.firebase_id,
                "username": contributor.get("login"),
                "contributions": contributor.get("contributions", 0),
                "avatar_url": contributor.get("avatar_url"),
                "profile_url": contributor.get("html_url"),
            })
        
        if contributor_rows:
            supabase.table("contributors").upsert(contributor_rows, on_conflict="firebase_id,github_id,username").execute()
            logger.info(f"Stored {len(contributor_rows)} contributors for {full_name}")
        
        return {
            "org": data.org,
            "repo": data.repo,
            "count": len(contributors),
            "stored": len(contributor_rows),
            "contributors": contributors
        }
    except Exception as e:
        logger.error(f"Failed to fetch/store contributors: {str(e)}")
        raise HTTPException(status_code=500, detail=f"Failed to fetch contributors: {str(e)}")


@app.post("/insights/overview")
def get_repository_overview(data: InsightsRequest):
    """
    Get comprehensive repository overview
    Combines multiple insights into one powerful response
    """
    if not github_auth:
        raise HTTPException(status_code=500, detail="GitHub App not configured")
    
    installation_id = get_installation_id_for_user(data.firebase_id)
    if not installation_id:
        raise HTTPException(status_code=404, detail=f"No GitHub installation found for user {data.firebase_id}")
    
    try:
        token = token_manager.get_token(installation_id)
        insights = GitHubInsights(token)
        
        # Fetch multiple insights
        repo_info = insights.get_repository_info(data.org, data.repo)
        languages = insights.get_languages(data.org, data.repo)
        contributors = insights.get_contributors(data.org, data.repo)
        recent_commits = insights.get_commits(data.org, data.repo, per_page=10)
        
        return {
            "org": data.org,
            "repo": data.repo,
            "overview": {
                "name": repo_info.get("name"),
                "description": repo_info.get("description"),
                "stars": repo_info.get("stargazers_count"),
                "forks": repo_info.get("forks_count"),
                "open_issues": repo_info.get("open_issues_count"),
                "default_branch": repo_info.get("default_branch"),
                "created_at": repo_info.get("created_at"),
                "updated_at": repo_info.get("updated_at"),
                "size": repo_info.get("size"),
                "languages": languages,
                "contributors_count": len(contributors),
                "recent_commits_count": len(recent_commits)
            }
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Failed to fetch overview: {str(e)}")


@app.post("/insights/activity")
def get_repository_activity(data: InsightsRequest):
    """Get repository activity stats (code frequency, commit activity)"""
    if not github_auth:
        raise HTTPException(status_code=500, detail="GitHub App not configured")
    
    installation_id = get_installation_id_for_user(data.firebase_id)
    if not installation_id:
        raise HTTPException(status_code=404, detail=f"No GitHub installation found for user {data.firebase_id}")
    
    try:
        logger.info(f"Fetching activity for {data.org}/{data.repo}")
        token = token_manager.get_token(installation_id)
        insights = GitHubInsights(token)
        
        logger.info("Getting code frequency...")
        code_frequency = insights.get_code_frequency(data.org, data.repo)
        logger.info(f"Code frequency returned: {len(code_frequency) if isinstance(code_frequency, list) else 'not a list'}")
        
        logger.info("Getting commit activity...")
        commit_activity = insights.get_commit_activity(data.org, data.repo)
        logger.info(f"Commit activity returned: {len(commit_activity) if isinstance(commit_activity, list) else 'not a list'}")
        
        return {
            "org": data.org,
            "repo": data.repo,
            "code_frequency": code_frequency,
            "commit_activity": commit_activity
        }
    except Exception as e:
        logger.error(f"Failed to fetch activity: {str(e)}", exc_info=True)
        raise HTTPException(status_code=500, detail=f"Failed to fetch activity: {str(e)}")


@app.post("/github/sync/test")
def test_sync(data: GitHubSyncRequest):
    """
    Test endpoint to debug sync issues
    Shows what's happening without background task
    """
    logger.info(f"Test sync called for firebase_id={data.firebase_id}, org={data.org}")
    
    installation_id = get_installation_id_for_user(data.firebase_id)
    if not installation_id:
        return {
            "success": False,
            "error": f"No GitHub installation found for user {data.firebase_id}",
            "available_orgs": list(load_installations().keys())
        }

    try:
        synced = sync_installation_to_supabase(data.firebase_id, data.org, installation_id)
        return {
            "success": True,
            "org": data.org,
            "firebase_id": data.firebase_id,
            "synced_repos": synced
        }
    except Exception as e:
        logger.error(f"Test sync failed: {str(e)}", exc_info=True)
        return {
            "success": False,
            "error": str(e),
            "org": data.org,
            "firebase_id": data.firebase_id
        }