# -*- coding: utf-8 -*-
"""ETL 状态监控 API

提供 2 个端点：
- GET /api/etl-status/cursors      — 返回各任务的数据游标（最后抓取时间、记录数）
- GET /api/etl-status/recent-runs  — 返回最近 50 条任务执行记录

所有端点需要 JWT 认证。
游标端点查询 ETL 数据库（meta.etl_cursor），
执行记录端点查询 zqyy_app 数据库（task_execution_log）。
"""

from __future__ import annotations

import logging

from fastapi import APIRouter, Depends, HTTPException, status
from psycopg2 import OperationalError

from app.auth.dependencies import CurrentUser, get_current_user
from app.database import get_connection, get_etl_global_readonly_connection
from app.schemas.etl_status import CursorInfo, RecentRun

logger = logging.getLogger(__name__)

router = APIRouter(prefix="/api/etl-status", tags=["ETL 状态"])

# 最近执行记录条数上限
_RECENT_RUNS_LIMIT = 50


# ── GET /api/etl-status/cursors ──────────────────────────────

@router.get("/cursors", response_model=list[CursorInfo])
async def list_cursors(
    user: CurrentUser = Depends(get_current_user),
) -> list[CursorInfo]:
    """返回各 ODS 表的最新数据游标。

    查询 ETL 数据库中的 meta.etl_cursor 表。
    如果该表不存在，返回空列表而非报错。
    """
    # CHANGE 2026-03-23 | 系统管理后台全局视角，不按门店过滤
    conn = get_etl_global_readonly_connection()
    try:
        with conn.cursor() as cur:
            # CHANGE 2026-02-15 | 对齐新库 etl_feiqiu 六层架构：etl_admin → meta
            cur.execute(
                """
                SELECT EXISTS (
                    SELECT 1
                    FROM information_schema.tables
                    WHERE table_schema = 'meta'
                      AND table_name   = 'etl_cursor'
                )
                """
            )
            exists = cur.fetchone()[0]
            if not exists:
                return []

            cur.execute(
                """
                SELECT t.task_code, c.last_start, c.last_end
                FROM meta.etl_cursor c
                JOIN meta.etl_task t ON c.task_id = t.task_id
                ORDER BY t.task_code
                """
            )
            rows = cur.fetchall()

        return [
            CursorInfo(
                task_code=row[0],
                last_start=str(row[1]) if row[1] is not None else None,
                last_end=str(row[2]) if row[2] is not None else None,
            )
            for row in rows
        ]
    except OperationalError as exc:
        logger.error("ETL 游标查询连接错误: %s", exc)
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail="ETL 数据库连接错误",
        )
    finally:
        conn.close()


# ── GET /api/etl-status/recent-runs ──────────────────────────

@router.get("/recent-runs", response_model=list[RecentRun])
async def list_recent_runs(
    user: CurrentUser = Depends(get_current_user),
) -> list[RecentRun]:
    """返回最近 50 条任务执行记录。

    查询 zqyy_app 数据库中的 task_execution_log 表，
    按 site_id 过滤，按 started_at DESC 排序。
    """
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            # CHANGE 2026-03-23 | 系统管理后台全局视角，不按门店过滤
            cur.execute(
                """
                SELECT id, task_codes, status, started_at,
                       finished_at, duration_ms, exit_code
                FROM task_execution_log
                ORDER BY started_at DESC
                LIMIT %s
                """,
                (_RECENT_RUNS_LIMIT,),
            )
            rows = cur.fetchall()

        return [
            RecentRun(
                id=str(row[0]),
                task_codes=list(row[1]) if row[1] else [],
                status=row[2],
                started_at=str(row[3]),
                finished_at=str(row[4]) if row[4] is not None else None,
                duration_ms=row[5],
                exit_code=row[6],
            )
            for row in rows
        ]
    except OperationalError as exc:
        logger.error("执行记录查询连接错误: %s", exc)
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail="数据库连接错误",
        )
    finally:
        conn.close()
