"""
Export API Routes
Provides data export functionality in CSV and Excel formats
"""
from typing import List, Optional, Dict, Any
from datetime import datetime, timedelta
from io import BytesIO, StringIO
import csv
from fastapi import APIRouter, Depends, HTTPException, status, Query
from fastapi.responses import StreamingResponse
from pydantic import BaseModel
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, and_

from app.database import get_db
from app.auth.jwt_handler import get_current_user
from app.models.user import User
from app.models.conversation import Conversation, Message, ConversationStatus
from app.models.billing import Invoice, Payment, UsageLog
from app.models.webhook import ClientWebhook, WebhookDeliveryLog

router = APIRouter()


# ============== Schemas ==============

class ExportRequest(BaseModel):
    format: str = "csv"  # csv or excel
    start_date: Optional[datetime] = None
    end_date: Optional[datetime] = None
    include_archived: bool = False


class ExportResponse(BaseModel):
    export_id: str
    status: str
    download_url: Optional[str] = None


# ============== Helper Functions ==============

def generate_csv(data: List[Dict], columns: List[str]) -> StringIO:
    """Generate CSV from data"""
    output = StringIO()
    writer = csv.DictWriter(output, fieldnames=columns, extrasaction='ignore')
    writer.writeheader()
    writer.writerows(data)
    output.seek(0)
    return output


def generate_excel(data: List[Dict], columns: List[str], sheet_name: str = "Data") -> BytesIO:
    """Generate Excel file from data using openpyxl"""
    try:
        from openpyxl import Workbook
        from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
        from openpyxl.utils import get_column_letter
    except ImportError:
        raise HTTPException(
            status_code=500,
            detail="Excel export requires openpyxl library. Install with: pip install openpyxl"
        )

    wb = Workbook()
    ws = wb.active
    ws.title = sheet_name

    # Header styling
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    header_alignment = Alignment(horizontal="center", vertical="center")
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )

    # Write headers
    for col_idx, column in enumerate(columns, 1):
        cell = ws.cell(row=1, column=col_idx, value=column.replace("_", " ").title())
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_alignment
        cell.border = thin_border

    # Write data
    for row_idx, row_data in enumerate(data, 2):
        for col_idx, column in enumerate(columns, 1):
            value = row_data.get(column, "")
            # Convert datetime to string
            if isinstance(value, datetime):
                value = value.strftime("%Y-%m-%d %H:%M:%S")
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            cell.border = thin_border

    # Auto-adjust column widths
    for col_idx, column in enumerate(columns, 1):
        max_length = len(column)
        for row in ws.iter_rows(min_row=2, min_col=col_idx, max_col=col_idx):
            for cell in row:
                try:
                    if cell.value:
                        max_length = max(max_length, len(str(cell.value)))
                except:
                    pass
        ws.column_dimensions[get_column_letter(col_idx)].width = min(max_length + 2, 50)

    output = BytesIO()
    wb.save(output)
    output.seek(0)
    return output


# ============== Export Endpoints ==============

@router.get("/conversations")
async def export_conversations(
    format: str = Query(default="csv", regex="^(csv|excel)$"),
    start_date: Optional[datetime] = None,
    end_date: Optional[datetime] = None,
    status_filter: Optional[str] = None,
    include_messages: bool = False,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_current_user)
):
    """
    Export conversations to CSV or Excel

    Parameters:
    - format: csv or excel
    - start_date: Filter by start date
    - end_date: Filter by end date
    - status_filter: Filter by status (active, closed, archived)
    - include_messages: Include message content (increases file size)
    """
    if not current_user.client_id:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="User must be associated with a client"
        )

    # Build query
    query = select(Conversation).where(Conversation.client_id == current_user.client_id)

    if start_date:
        query = query.where(Conversation.started_at >= start_date)
    if end_date:
        query = query.where(Conversation.started_at <= end_date)
    if status_filter:
        query = query.where(Conversation.status == ConversationStatus(status_filter))

    query = query.order_by(Conversation.started_at.desc())

    result = await db.execute(query)
    conversations = result.scalars().all()

    # Prepare data
    columns = [
        "id", "session_id", "channel", "status", "user_name", "user_email",
        "subject", "message_count", "total_tokens_used", "overall_sentiment",
        "resolved", "started_at", "ended_at", "source_url"
    ]

    if include_messages:
        columns.append("messages")

    data = []
    for conv in conversations:
        row = {
            "id": conv.id,
            "session_id": conv.session_id,
            "channel": conv.channel.value if hasattr(conv.channel, 'value') else conv.channel,
            "status": conv.status.value if hasattr(conv.status, 'value') else conv.status,
            "user_name": conv.user_name,
            "user_email": conv.user_email,
            "subject": conv.subject,
            "message_count": conv.message_count,
            "total_tokens_used": conv.total_tokens_used,
            "overall_sentiment": conv.overall_sentiment,
            "resolved": "Yes" if conv.resolved else "No",
            "started_at": conv.started_at,
            "ended_at": conv.ended_at,
            "source_url": conv.source_url
        }

        if include_messages:
            # Fetch messages for this conversation
            msg_query = select(Message).where(Message.conversation_id == conv.id).order_by(Message.created_at)
            msg_result = await db.execute(msg_query)
            messages = msg_result.scalars().all()
            row["messages"] = " | ".join([
                f"[{m.role.value if hasattr(m.role, 'value') else m.role}]: {m.content[:100]}..."
                for m in messages
            ])

        data.append(row)

    # Generate export
    filename = f"conversations_{datetime.utcnow().strftime('%Y%m%d_%H%M%S')}"

    if format == "excel":
        output = generate_excel(data, columns, "Conversations")
        return StreamingResponse(
            output,
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            headers={"Content-Disposition": f"attachment; filename={filename}.xlsx"}
        )
    else:
        output = generate_csv(data, columns)
        return StreamingResponse(
            iter([output.getvalue()]),
            media_type="text/csv",
            headers={"Content-Disposition": f"attachment; filename={filename}.csv"}
        )


@router.get("/messages")
async def export_messages(
    format: str = Query(default="csv", regex="^(csv|excel)$"),
    conversation_id: Optional[int] = None,
    start_date: Optional[datetime] = None,
    end_date: Optional[datetime] = None,
    role_filter: Optional[str] = None,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_current_user)
):
    """
    Export messages to CSV or Excel
    """
    if not current_user.client_id:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="User must be associated with a client"
        )

    # Get valid conversation IDs for this client
    conv_query = select(Conversation.id).where(Conversation.client_id == current_user.client_id)
    if conversation_id:
        conv_query = conv_query.where(Conversation.id == conversation_id)
    conv_result = await db.execute(conv_query)
    valid_conv_ids = [c for c in conv_result.scalars().all()]

    if not valid_conv_ids:
        raise HTTPException(status_code=404, detail="No conversations found")

    # Build message query
    query = select(Message).where(Message.conversation_id.in_(valid_conv_ids))

    if start_date:
        query = query.where(Message.created_at >= start_date)
    if end_date:
        query = query.where(Message.created_at <= end_date)
    if role_filter:
        query = query.where(Message.role == role_filter)

    query = query.order_by(Message.created_at)

    result = await db.execute(query)
    messages = result.scalars().all()

    columns = [
        "id", "conversation_id", "role", "content", "input_tokens",
        "output_tokens", "total_tokens", "model", "response_time_ms",
        "feedback_rating", "created_at"
    ]

    data = []
    for msg in messages:
        data.append({
            "id": msg.id,
            "conversation_id": msg.conversation_id,
            "role": msg.role.value if hasattr(msg.role, 'value') else msg.role,
            "content": msg.content,
            "input_tokens": msg.input_tokens,
            "output_tokens": msg.output_tokens,
            "total_tokens": msg.total_tokens,
            "model": msg.model,
            "response_time_ms": msg.response_time_ms,
            "feedback_rating": msg.feedback_rating,
            "created_at": msg.created_at
        })

    filename = f"messages_{datetime.utcnow().strftime('%Y%m%d_%H%M%S')}"

    if format == "excel":
        output = generate_excel(data, columns, "Messages")
        return StreamingResponse(
            output,
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            headers={"Content-Disposition": f"attachment; filename={filename}.xlsx"}
        )
    else:
        output = generate_csv(data, columns)
        return StreamingResponse(
            iter([output.getvalue()]),
            media_type="text/csv",
            headers={"Content-Disposition": f"attachment; filename={filename}.csv"}
        )


@router.get("/invoices")
async def export_invoices(
    format: str = Query(default="csv", regex="^(csv|excel)$"),
    start_date: Optional[datetime] = None,
    end_date: Optional[datetime] = None,
    status_filter: Optional[str] = None,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_current_user)
):
    """
    Export invoices to CSV or Excel
    """
    if not current_user.client_id:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="User must be associated with a client"
        )

    query = select(Invoice).where(Invoice.client_id == current_user.client_id)

    if start_date:
        query = query.where(Invoice.created_at >= start_date)
    if end_date:
        query = query.where(Invoice.created_at <= end_date)
    if status_filter:
        query = query.where(Invoice.status == status_filter)

    query = query.order_by(Invoice.created_at.desc())

    result = await db.execute(query)
    invoices = result.scalars().all()

    columns = [
        "id", "invoice_number", "amount", "tax_amount", "total_amount",
        "currency", "status", "due_date", "paid_at", "billing_period_start",
        "billing_period_end", "notes", "created_at"
    ]

    data = []
    for inv in invoices:
        data.append({
            "id": inv.id,
            "invoice_number": inv.invoice_number,
            "amount": float(inv.amount),
            "tax_amount": float(inv.tax_amount or 0),
            "total_amount": float(inv.total_amount),
            "currency": inv.currency,
            "status": inv.status.value if hasattr(inv.status, 'value') else inv.status,
            "due_date": inv.due_date,
            "paid_at": inv.paid_at,
            "billing_period_start": inv.billing_period_start,
            "billing_period_end": inv.billing_period_end,
            "notes": inv.notes,
            "created_at": inv.created_at
        })

    filename = f"invoices_{datetime.utcnow().strftime('%Y%m%d_%H%M%S')}"

    if format == "excel":
        output = generate_excel(data, columns, "Invoices")
        return StreamingResponse(
            output,
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            headers={"Content-Disposition": f"attachment; filename={filename}.xlsx"}
        )
    else:
        output = generate_csv(data, columns)
        return StreamingResponse(
            iter([output.getvalue()]),
            media_type="text/csv",
            headers={"Content-Disposition": f"attachment; filename={filename}.csv"}
        )


@router.get("/usage")
async def export_usage(
    format: str = Query(default="csv", regex="^(csv|excel)$"),
    start_date: Optional[datetime] = None,
    end_date: Optional[datetime] = None,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_current_user)
):
    """
    Export usage logs to CSV or Excel
    """
    if not current_user.client_id:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="User must be associated with a client"
        )

    # Default to last 30 days
    if not start_date:
        start_date = datetime.utcnow() - timedelta(days=30)
    if not end_date:
        end_date = datetime.utcnow()

    query = select(UsageLog).where(
        UsageLog.client_id == current_user.client_id,
        UsageLog.created_at >= start_date,
        UsageLog.created_at <= end_date
    ).order_by(UsageLog.created_at.desc())

    result = await db.execute(query)
    usage_logs = result.scalars().all()

    columns = [
        "id", "action_type", "tokens_used", "cost", "metadata", "created_at"
    ]

    data = []
    for log in usage_logs:
        data.append({
            "id": log.id,
            "action_type": log.action_type,
            "tokens_used": log.tokens_used,
            "cost": float(log.cost) if log.cost else 0,
            "metadata": str(log.metadata) if log.metadata else "",
            "created_at": log.created_at
        })

    filename = f"usage_{datetime.utcnow().strftime('%Y%m%d_%H%M%S')}"

    if format == "excel":
        output = generate_excel(data, columns, "Usage")
        return StreamingResponse(
            output,
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            headers={"Content-Disposition": f"attachment; filename={filename}.xlsx"}
        )
    else:
        output = generate_csv(data, columns)
        return StreamingResponse(
            iter([output.getvalue()]),
            media_type="text/csv",
            headers={"Content-Disposition": f"attachment; filename={filename}.csv"}
        )


@router.get("/webhooks")
async def export_webhooks(
    format: str = Query(default="csv", regex="^(csv|excel)$"),
    include_delivery_logs: bool = False,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_current_user)
):
    """
    Export webhook configurations and optionally delivery logs
    """
    if not current_user.client_id:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="User must be associated with a client"
        )

    query = select(ClientWebhook).where(
        ClientWebhook.client_id == current_user.client_id,
        ClientWebhook.deleted_at.is_(None)
    )

    result = await db.execute(query)
    webhooks = result.scalars().all()

    columns = [
        "id", "name", "url", "events", "is_active", "total_deliveries",
        "successful_deliveries", "failed_deliveries", "last_delivery_at",
        "last_success_at", "last_failure_at", "created_at"
    ]

    data = []
    for wh in webhooks:
        data.append({
            "id": wh.id,
            "name": wh.name,
            "url": wh.url,
            "events": ", ".join(wh.events) if wh.events else "",
            "is_active": "Yes" if wh.is_active else "No",
            "total_deliveries": wh.total_deliveries,
            "successful_deliveries": wh.successful_deliveries,
            "failed_deliveries": wh.failed_deliveries,
            "last_delivery_at": wh.last_delivery_at,
            "last_success_at": wh.last_success_at,
            "last_failure_at": wh.last_failure_at,
            "created_at": wh.created_at
        })

    filename = f"webhooks_{datetime.utcnow().strftime('%Y%m%d_%H%M%S')}"

    if format == "excel":
        output = generate_excel(data, columns, "Webhooks")
        return StreamingResponse(
            output,
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            headers={"Content-Disposition": f"attachment; filename={filename}.xlsx"}
        )
    else:
        output = generate_csv(data, columns)
        return StreamingResponse(
            iter([output.getvalue()]),
            media_type="text/csv",
            headers={"Content-Disposition": f"attachment; filename={filename}.csv"}
        )


@router.get("/analytics-summary")
async def export_analytics_summary(
    format: str = Query(default="csv", regex="^(csv|excel)$"),
    start_date: Optional[datetime] = None,
    end_date: Optional[datetime] = None,
    db: AsyncSession = Depends(get_db),
    current_user: User = Depends(get_current_user)
):
    """
    Export analytics summary report
    """
    if not current_user.client_id:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="User must be associated with a client"
        )

    # Default to last 30 days
    if not start_date:
        start_date = datetime.utcnow() - timedelta(days=30)
    if not end_date:
        end_date = datetime.utcnow()

    # Get conversations for the period
    conv_query = select(Conversation).where(
        Conversation.client_id == current_user.client_id,
        Conversation.started_at >= start_date,
        Conversation.started_at <= end_date
    )
    conv_result = await db.execute(conv_query)
    conversations = conv_result.scalars().all()

    # Calculate daily stats
    daily_stats = {}
    for conv in conversations:
        date_key = conv.started_at.strftime("%Y-%m-%d")
        if date_key not in daily_stats:
            daily_stats[date_key] = {
                "date": date_key,
                "total_conversations": 0,
                "total_messages": 0,
                "total_tokens": 0,
                "resolved_conversations": 0,
                "avg_messages_per_conversation": 0
            }

        daily_stats[date_key]["total_conversations"] += 1
        daily_stats[date_key]["total_messages"] += conv.message_count
        daily_stats[date_key]["total_tokens"] += conv.total_tokens_used or 0
        if conv.resolved:
            daily_stats[date_key]["resolved_conversations"] += 1

    # Calculate averages
    for date_key in daily_stats:
        if daily_stats[date_key]["total_conversations"] > 0:
            daily_stats[date_key]["avg_messages_per_conversation"] = round(
                daily_stats[date_key]["total_messages"] / daily_stats[date_key]["total_conversations"], 2
            )

    data = list(daily_stats.values())
    data.sort(key=lambda x: x["date"])

    columns = [
        "date", "total_conversations", "total_messages", "total_tokens",
        "resolved_conversations", "avg_messages_per_conversation"
    ]

    filename = f"analytics_summary_{datetime.utcnow().strftime('%Y%m%d_%H%M%S')}"

    if format == "excel":
        output = generate_excel(data, columns, "Analytics Summary")
        return StreamingResponse(
            output,
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            headers={"Content-Disposition": f"attachment; filename={filename}.xlsx"}
        )
    else:
        output = generate_csv(data, columns)
        return StreamingResponse(
            iter([output.getvalue()]),
            media_type="text/csv",
            headers={"Content-Disposition": f"attachment; filename={filename}.csv"}
        )