app/api/v1/sheets.py

from __future__ import annotations

import time
from typing import Any, Dict, Optional

from fastapi import APIRouter, Depends, Header, HTTPException

from app.config import get_settings
from app.core.logger import get_logger
from app.models.schemas import (
    SheetsAppendRequest,
    SheetsBatchClearByDataFilterRequest,
    SheetsBatchClearRequest,
    SheetsBatchGetByDataFilterRequest,
    SheetsBatchUpdateByDataFilterRequest,
    SheetsBatchUpdateRequest,
    SheetsBatchUpdateValuesRequest,
    SheetsCopySheetRequest,
    SheetsCreateSpreadsheetRequest,
    SheetsGenericResponse,
    SheetsGetByDataFilterRequest,
    SheetsRefreshResponse,
    SheetsSearchDeveloperMetadataRequest,
    SheetsValueUpdateRequest,
)
from app.services.sheets_service import (
    SheetsAPIError,
    SheetsCredentials,
    SheetsService,
)

router = APIRouter(prefix="/google/sheets", tags=["Google Sheets"])
_logger = get_logger(__name__)
_settings = get_settings()

_sheets_service = SheetsService()


def get_sheets_service() -> SheetsService:
    return _sheets_service


async def close_sheets_service() -> None:
    await _sheets_service.close()


def _credentials(
    x_access_token: Optional[str] = Header(None, alias="X-Access-Token"),
    x_refresh_token: Optional[str] = Header(None, alias="X-Refresh-Token"),
    x_client_id: Optional[str] = Header(None, alias="X-Client-Id"),
    x_client_secret: Optional[str] = Header(None, alias="X-Client-Secret"),
    x_token_expires_at: Optional[float] = Header(None, alias="X-Token-Expires-At"),
) -> SheetsCredentials:
    if not x_access_token:
        raise HTTPException(
            status_code=401,
            detail="X-Access-Token header is required.",
        )
    return SheetsCredentials(
        access_token=x_access_token,
        refresh_token=x_refresh_token,
        client_id=x_client_id,
        client_secret=x_client_secret,
        expires_at=x_token_expires_at,
    )


def _http_error(exc: SheetsAPIError) -> HTTPException:
    return HTTPException(status_code=exc.status_code, detail=exc.message)


def _elapsed_ms(start: float) -> float:
    return round((time.perf_counter() - start) * 1000, 2)


def _ok(
    start: float,
    creds: SheetsCredentials,
    data: Any = None,
    *,
    error: Optional[str] = None,
) -> SheetsGenericResponse:
    return SheetsGenericResponse(
        success=error is None,
        time_ms=_elapsed_ms(start),
        data=data,
        refreshed_access_token=creds.refreshed_access_token,
        error=error,
    )


# ---------------------------------------------------------------------------
# Service metadata & token lifecycle
# ---------------------------------------------------------------------------

@router.get("/scopes", response_model=SheetsGenericResponse,
            summary="List all supported Google Sheets OAuth scopes")
async def list_scopes(
    service: SheetsService = Depends(get_sheets_service),
):
    start = time.perf_counter()
    scopes = await service.list_available_scopes()
    _logger.info("Listed %d Sheets scopes (%.2fms)", len(scopes), _elapsed_ms(start))
    return SheetsGenericResponse(success=True, time_ms=_elapsed_ms(start), data=scopes)


@router.post("/token/refresh", response_model=SheetsRefreshResponse,
             summary="Refresh an access token (stateless, returned to the client)")
async def token_refresh(
    body: Dict[str, str],
    service: SheetsService = Depends(get_sheets_service),
):
    start = time.perf_counter()
    client_id = body.get("client_id", "")
    client_secret = body.get("client_secret", "")
    refresh_token = body.get("refresh_token", "")
    if not (client_id and client_secret and refresh_token):
        raise HTTPException(
            status_code=400,
            detail="client_id, client_secret and refresh_token are required.",
        )
    try:
        tokens = await service.refresh_access_token(
            client_id=client_id,
            client_secret=client_secret,
            refresh_token=refresh_token,
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info("Sheets token refreshed (%.2fms)", _elapsed_ms(start))
    return SheetsRefreshResponse(
        success=True,
        access_token=tokens.get("access_token"),
        expires_in=int(tokens.get("expires_in", 0)),
        token_type=tokens.get("token_type", "Bearer"),
        scope=tokens.get("scope"),
    )


# ---------------------------------------------------------------------------
# Spreadsheets
# ---------------------------------------------------------------------------

@router.post("/spreadsheets", response_model=SheetsGenericResponse,
             summary="Create a new spreadsheet")
async def create_spreadsheet(
    body: SheetsCreateSpreadsheetRequest,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
):
    start = time.perf_counter()
    try:
        data = await service.create_spreadsheet(
            creds,
            title=body.title,
            sheets=body.sheets,
            properties=body.properties,
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info("Sheets spreadsheet '%s' created (%.2fms)", body.title, _elapsed_ms(start))
    return _ok(start, creds, data)


@router.get("/spreadsheets", response_model=SheetsGenericResponse,
            summary="List all spreadsheets (drive.files.list, filtered to sheets)")
async def list_spreadsheets(
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
    page_size: int = 100,
    page_token: Optional[str] = None,
    include_trashed: bool = False,
):
    start = time.perf_counter()
    if page_size < 1 or page_size > 1000:
        raise HTTPException(status_code=400, detail="page_size must be between 1 and 1000.")
    try:
        data = await service.list_spreadsheets(
            creds,
            page_size=page_size,
            page_token=page_token,
            include_trashed=include_trashed,
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    files = (data or {}).get("files", []) or []
    _logger.info("Sheets listed %d spreadsheets (%.2fms)", len(files), _elapsed_ms(start))
    return _ok(start, creds, data)


@router.get("/spreadsheets/{spreadsheet_id}", response_model=SheetsGenericResponse,
            summary="Get spreadsheet metadata (spreadsheets.get)")
async def get_spreadsheet(
    spreadsheet_id: str,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
    ranges: Optional[str] = None,
    include_grid_data: bool = False,
):
    start = time.perf_counter()
    try:
        data = await service.get_spreadsheet(
            creds,
            spreadsheet_id,
            ranges=ranges.split(",") if ranges else None,
            include_grid_data=include_grid_data,
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info("Sheets spreadsheet %s fetched (%.2fms)", spreadsheet_id, _elapsed_ms(start))
    return _ok(start, creds, data)


@router.delete("/spreadsheets/{spreadsheet_id}", response_model=SheetsGenericResponse,
               summary="Delete a spreadsheet (drive.files.delete, pre-validated as a sheet)")
async def delete_spreadsheet(
    spreadsheet_id: str,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
):
    start = time.perf_counter()
    try:
        data = await service.delete_spreadsheet(creds, spreadsheet_id)
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info("Sheets spreadsheet %s deleted (%.2fms)", spreadsheet_id, _elapsed_ms(start))
    return _ok(start, creds, data)


@router.get("/spreadsheets/{spreadsheet_id}/sheets/{sheet_id}",
            response_model=SheetsGenericResponse,
            summary="Get a single sheet's metadata (spreadsheets.sheets.get)")
async def get_sheet(
    spreadsheet_id: str,
    sheet_id: int,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
    ranges: Optional[str] = None,
    include_grid_data: bool = False,
):
    start = time.perf_counter()
    try:
        data = await service.get_sheet(
            creds,
            spreadsheet_id,
            sheet_id,
            ranges=ranges.split(",") if ranges else None,
            include_grid_data=include_grid_data,
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info(
        "Sheets sheet %d of %s fetched (%.2fms)", sheet_id, spreadsheet_id, _elapsed_ms(start)
    )
    return _ok(start, creds, data)


@router.post("/spreadsheets/{spreadsheet_id}:batchUpdate",
             response_model=SheetsGenericResponse,
             summary="Apply batch mutations (addSheet, updateCells, etc.)")
async def batch_update(
    spreadsheet_id: str,
    body: SheetsBatchUpdateRequest,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
):
    start = time.perf_counter()
    try:
        data = await service.batch_update(
            creds, spreadsheet_id, requests=body.requests
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info("Sheets spreadsheet %s batch-updated (%.2fms)", spreadsheet_id, _elapsed_ms(start))
    return _ok(start, creds, data)


@router.post("/spreadsheets/{spreadsheet_id}:getByDataFilter",
             response_model=SheetsGenericResponse,
             summary="Read a spreadsheet filtered by DataFilter objects (spreadsheets.getByDataFilter)")
async def get_spreadsheet_by_data_filter(
    spreadsheet_id: str,
    body: SheetsGetByDataFilterRequest,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
):
    start = time.perf_counter()
    try:
        data = await service.get_spreadsheet_by_data_filter(
            creds,
            spreadsheet_id,
            data_filters=body.data_filters,
            include_grid_data=body.include_grid_data,
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info(
        "Sheets spreadsheet %s read by data filter (%.2fms)", spreadsheet_id, _elapsed_ms(start)
    )
    return _ok(start, creds, data)


@router.post("/spreadsheets/{spreadsheet_id}/sheets/{sheet_id}:copyTo",
             response_model=SheetsGenericResponse,
             summary="Copy a sheet to another spreadsheet (sheets.copyTo)")
async def copy_sheet(
    spreadsheet_id: str,
    sheet_id: int,
    body: SheetsCopySheetRequest,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
):
    start = time.perf_counter()
    try:
        data = await service.copy_sheet(
            creds,
            spreadsheet_id,
            sheet_id,
            destination_spreadsheet_id=body.destination_spreadsheet_id,
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info(
        "Sheets sheet %d copied from %s (%.2fms)", sheet_id, spreadsheet_id, _elapsed_ms(start)
    )
    return _ok(start, creds, data)


# ---------------------------------------------------------------------------
# Values
# ---------------------------------------------------------------------------

@router.get("/spreadsheets/{spreadsheet_id}/values/{range}",
            response_model=SheetsGenericResponse,
            summary="Read cell values from a range (values.get)")
async def get_values(
    spreadsheet_id: str,
    range: str,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
    major_dimension: Optional[str] = None,
    value_render_option: Optional[str] = None,
    date_time_render_option: Optional[str] = None,
):
    start = time.perf_counter()
    try:
        data = await service.get_values(
            creds,
            spreadsheet_id,
            range,
            major_dimension=major_dimension,
            value_render_option=value_render_option,
            date_time_render_option=date_time_render_option,
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info("Sheets values %s!%s fetched (%.2fms)", spreadsheet_id, range, _elapsed_ms(start))
    return _ok(start, creds, data)


@router.get("/spreadsheets/{spreadsheet_id}/values:batchGet",
            response_model=SheetsGenericResponse,
            summary="Read multiple ranges in one call (values.batchGet)")
async def batch_get_values(
    spreadsheet_id: str,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
    ranges: Optional[str] = None,
    major_dimension: Optional[str] = None,
    value_render_option: Optional[str] = None,
    date_time_render_option: Optional[str] = None,
):
    start = time.perf_counter()
    if not ranges:
        raise HTTPException(status_code=400, detail="At least one 'ranges' value is required.")
    try:
        data = await service.batch_get_values(
            creds,
            spreadsheet_id,
            ranges=ranges.split(","),
            major_dimension=major_dimension,
            value_render_option=value_render_option,
            date_time_render_option=date_time_render_option,
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info("Sheets batchGet %s fetched (%.2fms)", spreadsheet_id, _elapsed_ms(start))
    return _ok(start, creds, data)


@router.put("/spreadsheets/{spreadsheet_id}/values/{range}",
            response_model=SheetsGenericResponse,
            summary="Overwrite cell values in a range (values.update)")
async def update_values(
    spreadsheet_id: str,
    range: str,
    body: SheetsValueUpdateRequest,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
    value_input_option: str = "RAW",
):
    start = time.perf_counter()
    try:
        data = await service.update_values(
            creds,
            spreadsheet_id,
            range,
            values=body.values,
            major_dimension=body.major_dimension,
            value_input_option=value_input_option,
            include_values_in_response=body.include_values_in_response,
            value_render_option=body.value_render_option,
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info("Sheets values %s!%s updated (%.2fms)", spreadsheet_id, range, _elapsed_ms(start))
    return _ok(start, creds, data)


@router.post("/spreadsheets/{spreadsheet_id}/values/{range}:append",
             response_model=SheetsGenericResponse,
             summary="Append rows to a range (values.append)")
async def append_values(
    spreadsheet_id: str,
    range: str,
    body: SheetsAppendRequest,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
    value_input_option: str = "USER_ENTERED",
    insert_data_option: Optional[str] = None,
):
    start = time.perf_counter()
    try:
        data = await service.append_values(
            creds,
            spreadsheet_id,
            range,
            values=body.values,
            major_dimension=body.major_dimension,
            value_input_option=value_input_option,
            insert_data_option=insert_data_option,
            include_values_in_response=False,
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info("Sheets values %s!%s appended (%.2fms)", spreadsheet_id, range, _elapsed_ms(start))
    return _ok(start, creds, data)


@router.post("/spreadsheets/{spreadsheet_id}/values/{range}:clear",
             response_model=SheetsGenericResponse,
             summary="Clear cell values in a range (values.clear)")
async def clear_values(
    spreadsheet_id: str,
    range: str,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
):
    start = time.perf_counter()
    try:
        data = await service.clear_values(creds, spreadsheet_id, range)
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info("Sheets values %s!%s cleared (%.2fms)", spreadsheet_id, range, _elapsed_ms(start))
    return _ok(start, creds, data)


@router.post("/spreadsheets/{spreadsheet_id}/values:batchUpdate",
             response_model=SheetsGenericResponse,
             summary="Write multiple ranges in one call (values.batchUpdate)")
async def batch_update_values(
    spreadsheet_id: str,
    body: SheetsBatchUpdateValuesRequest,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
    value_input_option: str = "RAW",
):
    start = time.perf_counter()
    try:
        data = await service.batch_update_values(
            creds,
            spreadsheet_id,
            data=body.data,
            value_input_option=value_input_option,
            include_values_in_response=body.include_values_in_response,
            value_render_option=body.value_render_option,
            response_value_render_option=body.response_value_render_option,
            response_date_time_render_option=body.response_date_time_render_option,
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info("Sheets values batch-updated on %s (%.2fms)", spreadsheet_id, _elapsed_ms(start))
    return _ok(start, creds, data)


@router.post("/spreadsheets/{spreadsheet_id}/values:batchClear",
             response_model=SheetsGenericResponse,
             summary="Clear multiple ranges in one call (values.batchClear)")
async def batch_clear_values(
    spreadsheet_id: str,
    body: SheetsBatchClearRequest,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
):
    start = time.perf_counter()
    try:
        data = await service.batch_clear_values(
            creds, spreadsheet_id, ranges=body.ranges
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info("Sheets values batch-cleared on %s (%.2fms)", spreadsheet_id, _elapsed_ms(start))
    return _ok(start, creds, data)


@router.post("/spreadsheets/{spreadsheet_id}/values:batchGetByDataFilter",
             response_model=SheetsGenericResponse,
             summary="Read multiple ranges by DataFilter (values.batchGetByDataFilter)")
async def batch_get_values_by_data_filter(
    spreadsheet_id: str,
    body: SheetsBatchGetByDataFilterRequest,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
):
    start = time.perf_counter()
    try:
        data = await service.batch_get_values_by_data_filter(
            creds,
            spreadsheet_id,
            data_filters=body.data_filters,
            major_dimension=body.major_dimension,
            value_render_option=body.value_render_option,
            date_time_render_option=body.date_time_render_option,
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info(
        "Sheets batchGetByDataFilter on %s (%.2fms)", spreadsheet_id, _elapsed_ms(start)
    )
    return _ok(start, creds, data)


@router.post("/spreadsheets/{spreadsheet_id}/values:batchUpdateByDataFilter",
             response_model=SheetsGenericResponse,
             summary="Write multiple DataFilter ranges (values.batchUpdateByDataFilter)")
async def batch_update_values_by_data_filter(
    spreadsheet_id: str,
    body: SheetsBatchUpdateByDataFilterRequest,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
    value_input_option: str = "RAW",
):
    start = time.perf_counter()
    try:
        data = await service.batch_update_values_by_data_filter(
            creds,
            spreadsheet_id,
            data=body.data,
            value_input_option=value_input_option,
            include_values_in_response=body.include_values_in_response,
            value_render_option=body.value_render_option,
            response_value_render_option=body.response_value_render_option,
            response_date_time_render_option=body.response_date_time_render_option,
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info(
        "Sheets batchUpdateByDataFilter on %s (%.2fms)", spreadsheet_id, _elapsed_ms(start)
    )
    return _ok(start, creds, data)


@router.post("/spreadsheets/{spreadsheet_id}/values:batchClearByDataFilter",
             response_model=SheetsGenericResponse,
             summary="Clear multiple DataFilter ranges (values.batchClearByDataFilter)")
async def batch_clear_values_by_data_filter(
    spreadsheet_id: str,
    body: SheetsBatchClearByDataFilterRequest,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
):
    start = time.perf_counter()
    try:
        data = await service.batch_clear_values_by_data_filter(
            creds, spreadsheet_id, data_filters=body.data_filters
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info(
        "Sheets batchClearByDataFilter on %s (%.2fms)", spreadsheet_id, _elapsed_ms(start)
    )
    return _ok(start, creds, data)


# ---------------------------------------------------------------------------
# Developer metadata
# ---------------------------------------------------------------------------

@router.get("/spreadsheets/{spreadsheet_id}/developerMetadata/{metadata_id}",
            response_model=SheetsGenericResponse,
            summary="Get a single developer metadata entry (developerMetadata.get)")
async def get_developer_metadata(
    spreadsheet_id: str,
    metadata_id: int,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
):
    start = time.perf_counter()
    try:
        data = await service.get_developer_metadata(creds, spreadsheet_id, metadata_id)
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info(
        "Sheets developer metadata %d fetched from %s (%.2fms)",
        metadata_id,
        spreadsheet_id,
        _elapsed_ms(start),
    )
    return _ok(start, creds, data)


@router.post("/spreadsheets/{spreadsheet_id}/developerMetadata:search",
             response_model=SheetsGenericResponse,
             summary="Search developer metadata (developerMetadata.search)")
async def search_developer_metadata(
    spreadsheet_id: str,
    body: SheetsSearchDeveloperMetadataRequest,
    creds: SheetsCredentials = Depends(_credentials),
    service: SheetsService = Depends(get_sheets_service),
):
    start = time.perf_counter()
    try:
        data = await service.search_developer_metadata(
            creds, spreadsheet_id, data_filters=body.data_filters or None
        )
    except SheetsAPIError as exc:
        raise _http_error(exc) from exc
    _logger.info(
        "Sheets developer metadata searched on %s (%.2fms)", spreadsheet_id, _elapsed_ms(start)
    )
    return _ok(start, creds, data)