"""Read API составов проектов: разделы проекта, справочник ролей."""

from __future__ import annotations

import os
import traceback
from typing import Annotated, Any

import pymysql
from fastapi import APIRouter, Depends, HTTPException, Query

from app.labor import _column_lookup, _prefixed_col, _resolve_table
from app.main import (
    _db_name,
    _quote_ident,
    _table_columns,
    get_conn,
    require_api_key,
)
from app.member_roles import list_member_roles
from app.merakomis_schema import SECTION_TABLE
from app.project_members_helpers import fetch_project_row, project_section_ids

router = APIRouter()


def _http_500(e: Exception) -> HTTPException:
    dbg = os.environ.get("DEBUG", "")
    msg = str(e)
    if dbg == "1":
        msg = f"{msg}\n{traceback.format_exc()}"
    return HTTPException(status_code=500, detail=msg)


@router.get("/api/project-sections")
def project_sections(
    _auth: Annotated[None, Depends(require_api_key)],
    project_id: int = Query(..., ge=1),
) -> dict[str, Any]:
    try:
        with get_conn() as c:
            with c.cursor() as cur:
                db = _db_name(cur)
                project = fetch_project_row(cur, db, project_id)
                if not project:
                    raise HTTPException(
                        status_code=404,
                        detail={"code": "project_not_found", "message": "Проект не найден"},
                    )

                section_ids = project_section_ids(cur, db, project_id)
                if not section_ids:
                    return {"project_id": project_id, "total": 0, "items": []}

                section_t = _resolve_table(cur, db, SECTION_TABLE)
                s_cols = _table_columns(cur, db, section_t)
                slut = _column_lookup(s_cols)
                s_id = _prefixed_col(slut, SECTION_TABLE, "id")
                s_name = _prefixed_col(slut, SECTION_TABLE, "name")
                if not s_id or not s_name:
                    return {"project_id": project_id, "total": 0, "items": []}

                placeholders = ", ".join(["%s"] * len(section_ids))
                cur.execute(
                    f"""
                    SELECT {_quote_ident(s_id)} AS section_id,
                           {_quote_ident(s_name)} AS section_name
                    FROM {_quote_ident(section_t)}
                    WHERE {_quote_ident(s_id)} IN ({placeholders})
                    ORDER BY {_quote_ident(s_name)}
                    """,
                    tuple(section_ids),
                )
                items = [
                    {
                        "section_id": int(r["section_id"]),
                        "section_name": r["section_name"] or "",
                    }
                    for r in cur.fetchall()
                ]
        return {"project_id": project_id, "total": len(items), "items": items}
    except HTTPException:
        raise
    except pymysql.Error as e:
        raise HTTPException(status_code=500, detail=str(e)) from e
    except Exception as e:
        raise _http_500(e) from e


@router.get("/api/member-roles")
def member_roles(
    _auth: Annotated[None, Depends(require_api_key)],
    role: int | None = Query(None, ge=1),
) -> dict[str, Any]:
    items = list_member_roles(include_role=role)
    return {"total": len(items), "items": items}