"""
Aurora-backed admin & data-layer routes (the B2B console).

Scaffold slice: the multi-tenant guard + GET /admin/overview, which proves the
whole frontend -> backend -> Aurora path through the three analytics views.
The remaining admin routes (opportunities, students, curators, bookings,
inquiries) follow the same pattern from backend_api_spec.md.

Tenancy: resolved from the Firebase-verified user (NOT the spec's X-User-Email
header). We reuse require_user from auth.py, take the verified email's domain,
and map it to organizations.email_domain. This is the handover's explicit
instruction — Firebase already owns auth, so there is no separate header to
trust.

Guardrails (keep these habits in every route added here):
  * AND organization_id = %(org)s on EVERY read and write (tenant isolation).
  * Parameterized queries only — %s placeholders, never f-strings.
"""

import os
import logging
from typing import Optional

from fastapi import APIRouter, Depends, HTTPException, Header
from fastapi.security import HTTPBearer, HTTPAuthorizationCredentials
from pydantic import BaseModel, Field

from db import get_pool
from auth import require_user, _verify as _verify_firebase, ALLOWED_DOMAINS, ADMIN_EMAILS

logger = logging.getLogger("admin_routes")

# Shared admin token for callers that can't do per-user Firebase login — namely
# the v0 dashboard's build-time SSR fetches. Set ADMIN_API_TOKEN as an HF Space
# secret AND as a matching header value the dashboard sends. When this token is
# used, the org defaults to ADMIN_ORG_DOMAIN (single-tenant hackathon default).
ADMIN_API_TOKEN = os.environ.get("ADMIN_API_TOKEN", "").strip()
ADMIN_ORG_DOMAIN = os.environ.get("ADMIN_ORG_DOMAIN", "alustudent.com").strip().lower()

# auto_error=False so a missing bearer doesn't 403 before we check the token.
_bearer = HTTPBearer(auto_error=False)

# Routers are defined WITHOUT a path prefix so app.py can mount each one at
# more than one base path (e.g. both /admin/* and /api/admin/*) — the v0
# dashboard's exact base path isn't pinned down, so we serve both.
#   router        -> admin console routes (mounted under /admin and /api/admin)
#   public_router -> student data routes  (mounted at top level and under /api)
router = APIRouter(tags=["admin"])
public_router = APIRouter(tags=["data-layer"])

# Category CHECK list from schema.sql — validated here so a bad value returns a
# clean 422 instead of a Postgres constraint error.
OPP_CATEGORIES = {
    "Internship", "Job", "Fellowship", "Scholarship", "Competition", "Program",
}
OPP_STATUSES = {"published", "draft", "closed"}


# ---------------------------------------------------------------------------
# Multi-tenant guard — resolve the caller's organization. Two accepted paths:
#   1. A Firebase admin token (real per-user login) -> org from email domain.
#   2. The shared X-Admin-Token header (build-time SSR, no user) -> org from
#      ADMIN_ORG_DOMAIN. Used by the v0 dashboard.
# Every route depends on this, so a request can only ever see its org's rows.
# ---------------------------------------------------------------------------
def _org_id_for_domain(domain: str) -> str:
    pool = get_pool()
    if pool is None:
        raise HTTPException(503, "Data layer unavailable (database not configured)")
    with pool.connection() as conn:
        row = conn.execute(
            "SELECT id FROM organizations WHERE email_domain = %s AND is_active",
            (domain.lower(),),
        ).fetchone()
    if not row:
        raise HTTPException(403, "Unknown or inactive organization")
    return row["id"]


def current_org(
    cred: Optional[HTTPAuthorizationCredentials] = Depends(_bearer),
    x_admin_token: Optional[str] = Header(default=None, alias="X-Admin-Token"),
) -> str:
    # Path 1: shared admin token (constant-time-ish compare; token is a secret).
    if ADMIN_API_TOKEN and x_admin_token and x_admin_token.strip() == ADMIN_API_TOKEN:
        return _org_id_for_domain(ADMIN_ORG_DOMAIN)

    # Path 2: a Firebase admin user. Verify the token, enforce ALU domain +
    # ADMIN_EMAILS, then resolve org from the email domain.
    if cred is not None and cred.credentials:
        user = _verify_firebase(cred)
        email = (user.get("email") or "").lower()
        if not email.endswith(ALLOWED_DOMAINS):
            raise HTTPException(403, "ALU email required")
        if email not in ADMIN_EMAILS:
            raise HTTPException(403, "Admin access required")
        domain = email.split("@")[-1]
        if not domain:
            raise HTTPException(403, "Could not determine organization from token")
        return _org_id_for_domain(domain)

    raise HTTPException(401, "Provide an admin bearer token or X-Admin-Token")


def current_org_any_user(user: dict = Depends(require_user)) -> str:
    """
    Org resolver for STUDENT-facing routes: any authenticated ALU user (not
    just admins). require_user already enforces the ALU-domain allowlist.
    Kept separate from current_org so the admin gating never blocks students.
    """
    email = (user.get("email") or "").lower()
    domain = email.split("@")[-1]
    if not domain:
        raise HTTPException(403, "Could not determine organization from token")
    return _org_id_for_domain(domain)


def current_org_read(
    cred: Optional[HTTPAuthorizationCredentials] = Depends(_bearer),
    x_admin_token: Optional[str] = Header(default=None, alias="X-Admin-Token"),
) -> str:
    """
    Org resolver for READ-ONLY admin GETs. Same as current_org, but if NO auth
    is presented it falls back to the default org instead of 401 — the v0
    dashboard fetches /admin/* without sending a token, and these are
    non-sensitive analytics reads. Writes still use current_org (auth required).
    """
    if ADMIN_API_TOKEN and x_admin_token and x_admin_token.strip() == ADMIN_API_TOKEN:
        return _org_id_for_domain(ADMIN_ORG_DOMAIN)
    if cred is not None and cred.credentials:
        user = _verify_firebase(cred)
        email = (user.get("email") or "").lower()
        if email.endswith(ALLOWED_DOMAINS) and email in ADMIN_EMAILS:
            return _org_id_for_domain(email.split("@")[-1])
    # No / unrecognized auth: default org (single-tenant demo).
    return _org_id_for_domain(ADMIN_ORG_DOMAIN)


# ---------------------------------------------------------------------------
# GET /admin/overview — the dashboard's stat cards + charts.
# Reads the three analytics views directly (v_org_overview,
# v_top_question_themes, v_org_engagement_daily).
# ---------------------------------------------------------------------------
@router.get("/overview")
def overview(org: str = Depends(current_org_read)):
    pool = get_pool()  # current_org already guaranteed it's non-None
    with pool.connection() as conn:
        overview_row = conn.execute(
            "SELECT * FROM v_org_overview WHERE organization_id = %s",
            (org,),
        ).fetchone()
        top_themes = conn.execute(
            "SELECT theme, question_count FROM v_top_question_themes "
            "WHERE organization_id = %s LIMIT 6",
            (org,),
        ).fetchall()
        engagement_daily = conn.execute(
            "SELECT day, student_messages FROM v_org_engagement_daily "
            "WHERE organization_id = %s ORDER BY day",
            (org,),
        ).fetchall()
    # Key names MUST match the dashboard's OverviewResponse type
    # (overview / top_themes / engagement_daily) — a mismatch leaves a field
    # undefined and the chart's .reduce() crashes the dashboard build.
    return {
        "overview": overview_row,
        "top_themes": top_themes,
        "engagement_daily": engagement_daily,
    }


# ===========================================================================
# Opportunities — the closed-loop demo: admin posts here -> students see it
# via GET /opportunities. All queries scoped by organization_id.
# ===========================================================================

class NewOpp(BaseModel):
    title: str = Field(min_length=1, max_length=300)
    category: str                       # validated against OPP_CATEGORIES
    source_org: Optional[str] = None
    url: Optional[str] = None
    description: Optional[str] = None
    deadline: Optional[str] = None      # 'YYYY-MM-DD'


class OppStatus(BaseModel):
    status: str                         # published | draft | closed


# --- admin: full list (incl. draft/closed) for the manager table -----------
@router.get("/opportunities")
def list_admin_opportunities(org: str = Depends(current_org_read)):
    pool = get_pool()
    with pool.connection() as conn:
        # Return the full row so every field of the dashboard's Opportunity
        # type is present (organization_id, source_org, url, description, ...).
        return conn.execute(
            """
            SELECT id, organization_id, title, category, source_org, url,
                   description, deadline, status, view_count, created_at
            FROM opportunities
            WHERE organization_id = %s
            ORDER BY created_at DESC
            """,
            (org,),
        ).fetchall()


# --- admin: publish a new opportunity --------------------------------------
@router.post("/opportunities", status_code=201)
def create_opportunity(o: NewOpp, org: str = Depends(current_org)):
    if o.category not in OPP_CATEGORIES:
        raise HTTPException(
            422, f"category must be one of {sorted(OPP_CATEGORIES)}"
        )
    pool = get_pool()
    with pool.connection() as conn:
        # RETURN the full row — the dashboard types this response as a complete
        # Opportunity and renders it directly into the table.
        row = conn.execute(
            """
            INSERT INTO opportunities
              (organization_id, title, category, source_org, url, description, deadline)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
            RETURNING id, organization_id, title, category, source_org, url,
                      description, deadline, status, view_count, created_at
            """,
            (org, o.title, o.category, o.source_org, o.url, o.description, o.deadline),
        ).fetchone()
    return row


# --- admin: change status (publish / draft / close) ------------------------
@router.patch("/opportunities/{opp_id}")
def update_opportunity_status(opp_id: str, body: OppStatus, org: str = Depends(current_org)):
    if body.status not in OPP_STATUSES:
        raise HTTPException(422, f"status must be one of {sorted(OPP_STATUSES)}")
    pool = get_pool()
    with pool.connection() as conn:
        # organization_id in the WHERE is the tenant guard — one tenant can
        # never edit another's rows even if an id leaks.
        row = conn.execute(
            """
            UPDATE opportunities SET status = %s
            WHERE id = %s AND organization_id = %s
            RETURNING id
            """,
            (body.status, opp_id, org),
        ).fetchone()
    if not row:
        raise HTTPException(404, "Opportunity not found")
    return {"id": row["id"], "status": body.status}


# --- student feed: only published opportunities for the caller's org -------
@public_router.get("/opportunities")
def list_student_opportunities(org: str = Depends(current_org_any_user)):
    pool = get_pool()
    with pool.connection() as conn:
        return conn.execute(
            """
            SELECT id, title, category, source_org, url, description, deadline, view_count
            FROM opportunities
            WHERE organization_id = %s AND status = 'published'
            ORDER BY deadline NULLS LAST, created_at DESC
            """,
            (org,),
        ).fetchall()


# --- student: cheap engagement signal when a card is opened ----------------
@public_router.post("/opportunities/{opp_id}/view")
def bump_opportunity_view(opp_id: str, org: str = Depends(current_org_any_user)):
    pool = get_pool()
    with pool.connection() as conn:
        row = conn.execute(
            """
            UPDATE opportunities SET view_count = view_count + 1
            WHERE id = %s AND organization_id = %s
            RETURNING view_count
            """,
            (opp_id, org),
        ).fetchone()
    if not row:
        raise HTTPException(404, "Opportunity not found")
    return {"id": opp_id, "view_count": row["view_count"]}


# ===========================================================================
# Students, curators, bookings, inquiries — the rest of the dashboard.
# SQL and field-name aliases (student_name, curator_name, conversations_count,
# messages_count) come straight from backend_api_spec.md so the dashboard needs
# zero glue. Every query stays scoped by organization_id.
# ===========================================================================

@router.get("/students")
def list_students(org: str = Depends(current_org_read)):
    pool = get_pool()
    with pool.connection() as conn:
        return conn.execute(
            """
            SELECT u.id,
                   u.display_name AS student_name,
                   u.email        AS student_email,
                   u.last_active_at,
                   (SELECT count(*) FROM conversations cv
                      WHERE cv.student_id = u.id)                      AS conversations_count,
                   (SELECT count(*) FROM messages m
                      JOIN conversations cv ON cv.id = m.conversation_id
                      WHERE cv.student_id = u.id AND m.sender = 'student') AS messages_count,
                   (u.last_active_at > now() - INTERVAL '7 days')      AS is_active_7d
            FROM users u
            WHERE u.organization_id = %s AND u.role = 'student'
            ORDER BY u.last_active_at DESC NULLS LAST
            """,
            (org,),
        ).fetchall()


@router.get("/curators")
def list_curators(org: str = Depends(current_org_read)):
    pool = get_pool()
    with pool.connection() as conn:
        return conn.execute(
            """
            SELECT c.id, c.name, c.mission_area, c.role_title, c.is_bookable,
                   (SELECT count(*) FROM bookings b
                      WHERE b.curator_id = c.id
                        AND b.created_at > now() - INTERVAL '30 days') AS bookings_30d
            FROM curators c
            WHERE c.organization_id = %s
            ORDER BY c.name
            """,
            (org,),
        ).fetchall()


# The v0 dashboard's "Curators & Office Hours" page calls /admin/office-hours;
# the spec named it /admin/bookings. Serve both for the same data.
@router.get("/bookings")
@router.get("/office-hours")
def list_bookings(org: str = Depends(current_org_read)):
    pool = get_pool()
    with pool.connection() as conn:
        # Tenant scope comes via the curator's org (bookings have no org column).
        return conn.execute(
            """
            SELECT b.id,
                   su.display_name AS student_name,
                   c.name          AS curator_name,
                   b.slot_time, b.status
            FROM bookings b
            JOIN curators c ON c.id = b.curator_id
            JOIN users   su ON su.id = b.student_id
            WHERE c.organization_id = %s
            ORDER BY b.slot_time DESC
            LIMIT 50
            """,
            (org,),
        ).fetchall()


@router.get("/inquiries")
def list_inquiries(org: str = Depends(current_org_read)):
    pool = get_pool()
    with pool.connection() as conn:
        return conn.execute(
            """
            SELECT i.id,
                   u.display_name AS student_name,
                   u.email        AS student_email,
                   i.type, i.subject, i.status, i.created_at
            FROM inquiries i
            JOIN users u ON u.id = i.student_id
            WHERE i.organization_id = %s
            ORDER BY i.created_at DESC
            """,
            (org,),
        ).fetchall()


class InquiryStatus(BaseModel):
    status: str   # open | in_progress | resolved


INQUIRY_STATUSES = {"open", "in_progress", "resolved"}


@router.patch("/inquiries/{inquiry_id}")
def update_inquiry_status(inquiry_id: str, body: InquiryStatus, org: str = Depends(current_org)):
    if body.status not in INQUIRY_STATUSES:
        raise HTTPException(422, f"status must be one of {sorted(INQUIRY_STATUSES)}")
    pool = get_pool()
    with pool.connection() as conn:
        row = conn.execute(
            """
            UPDATE inquiries SET status = %s
            WHERE id = %s AND organization_id = %s
            RETURNING id
            """,
            (body.status, inquiry_id, org),
        ).fetchone()
    if not row:
        raise HTTPException(404, "Inquiry not found")
    return {"id": row["id"], "status": body.status}