import asyncio
import io
import uuid
from datetime import datetime, timezone

import httpx
from fastapi import APIRouter, Depends, HTTPException, status
from fastapi.responses import StreamingResponse
from openpyxl import Workbook
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.crud.payment_providers import adapter_for
from app.crud.secrets import Scrubber, fill_templates, load_values, referenced_names
from app.crud import file_transfer, firebase_push, google_apps, sql_providers
from app.crud.rpa_providers import adapter_for as rpa_adapter_for
from app.core.database import get_db
from app.core.timezones import localize, tenant_zone
from app.core.permissions import require_permission
from app.core.security import AuthContext, get_auth_context
from app.crud.audit import write_audit_log
from app.crud.base import CRUDBase
from app.models.reporting import Connector, Report
from app.models.workflow import FormSubmission, StepHistory, WorkflowInstance
from app.schemas.reporting import (
    ConnectorCreate,
    ConnectorRead,
    ConnectorTestResult,
    ConnectorUpdate,
    ReportCreate,
    ReportRead,
    ReportUpdate,
)

router = APIRouter()  # tags live on the two sub-routers below; a tag here would
# propagate onto both when included, duplicating every request into an extra folder

reports_router = APIRouter(prefix="/reports", tags=["reports"])
connectors_router = APIRouter(prefix="/connectors", tags=["connectors"])

reports_crud = CRUDBase[Report, ReportCreate, ReportUpdate](Report)
connectors_crud = CRUDBase[Connector, ConnectorCreate, ConnectorUpdate](Connector)

# FR-RPT-001: the four supported report data sources, and the columns each
# exposes. approval_log and sla_compliance aren't modeled as their own
# tables — they're views over step_history / workflow_instances.
_SOURCE_MODELS = {
    "workflow_instances": (WorkflowInstance, WorkflowInstance.started_at),
    "form_submissions": (FormSubmission, FormSubmission.submitted_at),
    "approval_log": (StepHistory, StepHistory.occurred_at),
    "sla_compliance": (WorkflowInstance, WorkflowInstance.started_at),
}


# ---- Reports -------------------------------------------------------------

@reports_router.get("", response_model=list[ReportRead])
async def list_reports(
    skip: int = 0,
    limit: int = 100,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("reports", "view")),
) -> list[Report]:
    return await reports_crud.list(db, ctx.tenant_id, skip=skip, limit=limit)


@reports_router.get("/{report_id}", response_model=ReportRead)
async def get_report(
    report_id: uuid.UUID,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("reports", "view")),
) -> Report:
    return await reports_crud.get(db, ctx.tenant_id, report_id)


@reports_router.post("", response_model=ReportRead, status_code=status.HTTP_201_CREATED)
async def create_report(
    payload: ReportCreate,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("reports", "create")),
) -> Report:
    return await reports_crud.create(db, ctx.tenant_id, payload, created_by=ctx.user_id)


@reports_router.patch("/{report_id}", response_model=ReportRead)
async def update_report(
    report_id: uuid.UUID,
    payload: ReportUpdate,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("reports", "edit")),
) -> Report:
    return await reports_crud.update(db, ctx.tenant_id, report_id, payload)


@reports_router.delete("/{report_id}", status_code=status.HTTP_204_NO_CONTENT)
async def delete_report(
    report_id: uuid.UUID,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("reports", "delete")),
) -> None:
    await reports_crud.delete(db, ctx.tenant_id, report_id)


@reports_router.get("/{report_id}/export.xlsx")
async def export_report_xlsx(
    report_id: uuid.UUID,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("reports", "view")),
) -> StreamingResponse:
    """
    FR-RPT-004: export to .xlsx. FR-RPT-005 (PDF export) is NOT implemented
    here — the SRS's recommended approach (8.4) renders the same on-screen
    report layout via a headless browser (Playwright), which needs a
    frontend report view to render in the first place; that's real
    follow-up work, not something to fake with a placeholder here.
    """
    report = await reports_crud.get(db, ctx.tenant_id, report_id)
    model, order_column = _SOURCE_MODELS[report.source]

    stmt = select(model).where(model.tenant_id == ctx.tenant_id)
    for key, value in (report.filters or {}).items():
        if key == "date_from":
            stmt = stmt.where(order_column >= value)
        elif key == "date_to":
            stmt = stmt.where(order_column <= value)
        elif hasattr(model, key):
            stmt = stmt.where(getattr(model, key) == value)
    stmt = stmt.order_by(order_column.desc()).limit(10_000)  # NFR-PERF-003 async path applies above this
    rows = (await db.execute(stmt)).scalars().all()

    columns = report.columns or [c.name for c in model.__table__.columns]
    tz = await tenant_zone(db, ctx.tenant_id)

    wb = Workbook()
    ws = wb.active
    ws.title = report.name[:31] or "Report"
    ws.append(columns)
    for row in rows:
        ws.append([_cell_value(localize(getattr(row, col, None), tz)) for col in columns])

    buffer = io.BytesIO()
    wb.save(buffer)
    buffer.seek(0)

    report.last_run_at = datetime.now(timezone.utc)
    await write_audit_log(
        db, ctx.tenant_id, ctx.user_id, "report.exported", "report", report_id, {"format": "xlsx", "rows": len(rows)}
    )
    await db.flush()

    filename = f"{report.name.replace(' ', '_')}.xlsx"
    return StreamingResponse(
        buffer,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": f'attachment; filename="{filename}"'},
    )


def _cell_value(value: object) -> object:
    if isinstance(value, uuid.UUID):
        return str(value)
    if isinstance(value, datetime):
        return value.replace(tzinfo=None)  # openpyxl rejects tz-aware datetimes
    if isinstance(value, (dict, list)):
        return str(value)
    return value


# ---- Connectors -------------------------------------------------------------

@connectors_router.get("", response_model=list[ConnectorRead])
async def list_connectors(
    skip: int = 0,
    limit: int = 100,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("connectors", "view")),
) -> list[Connector]:
    return await connectors_crud.list(db, ctx.tenant_id, skip=skip, limit=limit)


@connectors_router.get("/{connector_id}", response_model=ConnectorRead)
async def get_connector(
    connector_id: uuid.UUID,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("connectors", "view")),
) -> Connector:
    return await connectors_crud.get(db, ctx.tenant_id, connector_id)


@connectors_router.post("", response_model=ConnectorRead, status_code=status.HTTP_201_CREATED)
async def create_connector(
    payload: ConnectorCreate,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("connectors", "create")),
) -> Connector:
    return await connectors_crud.create(db, ctx.tenant_id, payload)


@connectors_router.patch("/{connector_id}", response_model=ConnectorRead)
async def update_connector(
    connector_id: uuid.UUID,
    payload: ConnectorUpdate,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("connectors", "edit")),
) -> Connector:
    return await connectors_crud.update(db, ctx.tenant_id, connector_id, payload)


@connectors_router.delete("/{connector_id}", status_code=status.HTTP_204_NO_CONTENT)
async def delete_connector(
    connector_id: uuid.UUID,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("connectors", "delete")),
) -> None:
    await connectors_crud.delete(db, ctx.tenant_id, connector_id)


@connectors_router.post("/{connector_id}/test", response_model=ConnectorTestResult)
async def test_connector(
    connector_id: uuid.UUID,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("connectors", "edit")),
) -> ConnectorTestResult:
    """
    FR-CNN-003: real, best-effort reachability checks — an HTTP request for
    webhook, a TCP connect for email_smtp. This verifies network
    reachability of the configured endpoint, not that credentials are
    valid; a full SMTP auth handshake or webhook signature round-trip is
    out of scope here. realtime_notification has no external endpoint at
    all (see app/crud/notifications.py — it just writes a row to the
    in-app notification list), so there's nothing to reach and it always
    reports success.
    """
    connector = await connectors_crud.get(db, ctx.tenant_id, connector_id)
    # {{secret.NAME}} references are filled in for the test; the result is
    # shown and stored, so any secret value echoed into it is masked.
    secret_names = referenced_names(connector.configuration or {})
    secret_values = await load_values(db, ctx.tenant_id, secret_names) if secret_names else {}
    config = fill_templates(connector.configuration or {}, secret_values)

    try:
        if connector.type == "webhook":
            url = config.get("url")
            if not url:
                raise ValueError("configuration.url is required for a webhook connector")
            async with httpx.AsyncClient(timeout=5.0) as client:
                resp = await client.request(config.get("method", "GET"), url)
            result = ConnectorTestResult(success=resp.status_code < 500, detail=f"HTTP {resp.status_code}")
        elif connector.type in ("email_smtp", "email_imap"):
            host, port = config.get("host"), config.get("port")
            if not host or not port:
                raise ValueError("configuration.host and configuration.port are required")
            reader, writer = await asyncio.wait_for(asyncio.open_connection(host, int(port)), timeout=5.0)
            writer.close()
            await writer.wait_closed()
            result = ConnectorTestResult(success=True, detail=f"TCP connect to {host}:{port} succeeded")
        elif connector.type == "realtime_notification":
            result = ConnectorTestResult(success=True, detail="No external endpoint to test — writes directly to the in-app notification list.")
        elif connector.type == "sms":
            url = config.get("api_url")
            if not url:
                raise ValueError("configuration.api_url is required for an sms connector")
            headers = {"Authorization": f"Bearer {config['api_key']}"} if config.get("api_key") else {}
            async with httpx.AsyncClient(timeout=5.0) as client:
                resp = await client.get(url, headers=headers)
            result = ConnectorTestResult(success=resp.status_code < 500, detail=f"HTTP {resp.status_code}")
        elif connector.type == "payment_gateway":
            # A real, charge-free credential check per provider
            # (app/crud/payment_providers.py) — e.g. a signed Paytm status
            # lookup, a Razorpay/Stripe authenticated read.
            ok, detail = await adapter_for(config).test_credentials(config)
            result = ConnectorTestResult(success=ok, detail=detail[:500])
        elif connector.type == "rpa":
            # Logs in to the RPA platform and lists what it can run — never
            # starts a job (app/crud/rpa_providers.py).
            ok, detail = await rpa_adapter_for(config).test_credentials(config)
            result = ConnectorTestResult(success=ok, detail=detail[:500])
        elif connector.type == "database":
            # Signs in and reads the server version (app/crud/sql_providers.py).
            ok, detail = await sql_providers.test_credentials(config)
            result = ConnectorTestResult(success=ok, detail=detail[:500])
        elif connector.type == "file_transfer":
            # Signs in and lists the base folder (app/crud/file_transfer.py);
            # for SFTP it also reports the host key fingerprint to pin.
            ok, detail = await file_transfer.test_credentials(config)
            result = ConnectorTestResult(success=ok, detail=detail[:500])
        elif connector.type == "google":
            # Signs in and asks Drive who this is (app/crud/google_apps.py).
            ok, detail = await google_apps.test_credentials(config)
            result = ConnectorTestResult(success=ok, detail=detail[:500])
        elif connector.type == "firebase":
            # A validate-only send: nothing reaches any device (app/crud/firebase_push.py).
            ok, detail = await firebase_push.test_credentials(config)
            result = ConnectorTestResult(success=ok, detail=detail[:500])
        else:
            result = ConnectorTestResult(success=False, detail=f"Unknown connector type {connector.type}")
    except Exception as exc:  # noqa: BLE001 - surfaced to the caller as the test result, not a 500
        result = ConnectorTestResult(success=False, detail=str(exc))

    result.detail = Scrubber(secret_values)(result.detail)
    connector.last_tested_at = datetime.now(timezone.utc)
    connector.last_test_result = result.detail
    await db.flush()
    return result


router.include_router(reports_router)
router.include_router(connectors_router)