"""Partner endpoints for corporate and educational partners."""

from typing import Optional, Literal, List
from uuid import UUID

from fastapi import APIRouter, Depends, HTTPException, Query, status
from sqlalchemy import func, or_
from sqlalchemy.orm import Session

from app.api.deps import get_current_admin, get_current_user, get_db, require_user_type
from app.models.partner import (
    CorporatePartner, DiscoverySession, DiscoverySessionStatus,
    EducationalPartner, OpportunityApplication,
    OpportunityType, PartnerCohort, PartnerEnrollment, PartnerOpportunity, PartnerType
)
from app.models.user import User, UserType
from app.models.waitlist import Waitlist
from app.models.nin_verification import NINVerification
from app.models.biz_verification import BizVerification
from app.models.application import Application
from app.models.academic import Enrollment
from app.models.payment import Payment, PaymentStatus, PaymentType
from app.schemas.common import SuccessResponse
from app.schemas.partner import (
    CorporatePartnerCreate, CorporatePartnerResponse,
    DiscoverySessionCreate, DiscoverySessionResponse,
    PublicDiscoveryBookRequest,
    EducationalPartnerCreate, EducationalPartnerResponse,
    PartnerCohortResponse, PartnerOpportunityCreate, PartnerOpportunityResponse,
    PartnerStatusUpdate,
)
from app.utils.security import get_password_hash
from app.services.google_calendar_service import GoogleCalendarService
from app.services.email_service import EmailService
from app.config import settings
from datetime import date, datetime, timedelta
import logging
import re

router = APIRouter(prefix="/partners", tags=["partners"])


def generate_corporate_password(company_name: str) -> str:
    """
    Generate a deterministic initial password for corporate partners.
    Pattern: @<First6LettersCapitalized>#<year>POU
    Example: company 'Sucdri Nig Ltd' in 2026 -> '@Sucdri#2026POU'
    """
    base = re.sub(r"[^a-zA-Z]", "", company_name or "")
    segment = (base[:6] or "POUCP")
    segment = segment.capitalize()
    year = datetime.utcnow().year
    return f"@{segment}#{year}POU"


def _slugify(name: str) -> str:
    """
    Lightweight slug generator for partner URLs.
    Example: 'Proconnect EduFinTechCo' -> 'proconnect-edufintechco'
    """
    if not name:
        return ""
    # Keep letters, numbers and spaces; normalize whitespace to single hyphens
    cleaned = re.sub(r"[^a-zA-Z0-9\s-]", "", name)
    cleaned = re.sub(r"\s+", " ", cleaned).strip().lower()
    return cleaned.replace(" ", "-")


def _normalize_name(value: str) -> str:
    """
    Normalize partner names for comparison.
    Lowercases and strips all non-alphanumeric characters so that
    variations like 'Proconnect EduFinTechCo.' and 'Proconnect Edufintechco'
    are treated as the same.
    """
    return re.sub(r"[^a-z0-9]+", "", (value or "").lower())


# ========== Public Corporate Registration ==========

from pydantic import BaseModel, EmailStr, Field
from pydantic import ConfigDict


class CorporateRegisterRequest(BaseModel):
    model_config = ConfigDict(populate_by_name=True)

    company_name: str
    contact_person: Optional[str] = Field(None, alias="contactPerson")  # Primary contact name for reference
    business_address: str
    company_url: str
    official_email: EmailStr
    official_phone: str
    estimated_staff: str
    logo_url: Optional[str] = None  # URL to logo in storage


class EducationalRegisterRequest(BaseModel):
    agency_name: str
    contact_person: str
    email: EmailStr
    phone: str
    company_type: str  # BUSINESS_NAME, COMPANY, INCORPORATED_TRUSTEES, LIMITED_PARTNERSHIP, LIMITED_LIABILITY_PARTNERSHIP
    cac_number: str
    nin: str
    state: str
    lga: str
    address: str
    enrollment_range: str


@router.post("/corporate/register", response_model=SuccessResponse, status_code=status.HTTP_201_CREATED)
async def register_corporate_partner(
    payload: CorporateRegisterRequest,
    db: Session = Depends(get_db),
):
    """
    Public endpoint to register a new corporate partner.
    Creates both a User account (user_type=corporate_partner) and a CorporatePartner record.
    """
    # Ensure email not already used in users
    existing_user = db.query(User).filter(User.email == payload.official_email).first()
    if existing_user:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="A user with this email already exists. Please log in or reset your password.",
        )

    # Ensure corporate partner with same company/email/phone doesn't already exist
    existing_partner = (
        db.query(CorporatePartner)
        .filter(
            or_(
                func.lower(CorporatePartner.company_name) == payload.company_name.lower(),
                CorporatePartner.contact_email == payload.official_email,
                CorporatePartner.contact_phone == payload.official_phone,
            )
        )
        .first()
    )
    if existing_partner:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=(
                "A corporate partner with this company name, email or phone already exists. "
                "Please use your existing login or contact support."
            ),
        )

    # Generate password and hash
    raw_password = generate_corporate_password(payload.company_name)
    password_hash = get_password_hash(raw_password)

    # Create user (inactive until admin approves partner)
    user = User(
        email=payload.official_email,
        password_hash=password_hash,
        user_type=UserType.CORPORATE_PARTNER,
        is_active=False,
    )
    db.add(user)
    db.flush()  # populate user.id

    # Create corporate partner record (inactive until admin approves)
    partner = CorporatePartner(
        user_id=user.id,
        company_name=payload.company_name,
        contact_person=(payload.contact_person or "").strip() or None,
        website=payload.company_url,
        contact_email=payload.official_email,
        contact_phone=payload.official_phone,
        company_size=payload.estimated_staff,
        is_active=False,
        extra_data={
            "business_address": payload.business_address,
            "logo_url": payload.logo_url,
            # Persist a stable slug so referral URLs and backend lookups stay in sync.
            "slug": _slugify(payload.company_name),
        },
    )
    db.add(partner)
    db.commit()
    db.refresh(partner)

    # NOTE: In production, you should email the password instead of returning it.
    return SuccessResponse(
        data={
            "user": {
                "id": str(user.id),
                "email": user.email,
                "userType": user.user_type.value,
            },
            "partner": {
                "id": str(partner.id),
                "company_name": partner.company_name,
            },
            "initial_password": raw_password,
        },
        message="Corporate partner registered successfully. Your account is pending approval; you will be able to log in once an administrator approves it.",
    )


@router.post("/education/register", response_model=SuccessResponse, status_code=status.HTTP_201_CREATED)
async def register_educational_partner(
    payload: EducationalRegisterRequest,
    db: Session = Depends(get_db),
):
    """
    Public endpoint to register a new educational partner (agency).
    Creates both a User account (user_type=educational_partner) and an EducationalPartner record.
    """
    # Ensure email not already used in users
    existing_user = db.query(User).filter(User.email == payload.email).first()
    if existing_user:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="A user with this email already exists. Please log in or reset your password.",
        )

    # Ensure educational partner with same institution/email/phone doesn't already exist
    existing_partner = (
        db.query(EducationalPartner)
        .filter(
            or_(
                func.lower(EducationalPartner.institution_name) == payload.agency_name.lower(),
                EducationalPartner.contact_email == payload.email,
                EducationalPartner.contact_phone == payload.phone,
            )
        )
        .first()
    )
    if existing_partner:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=(
                "An educational partner with this agency name, email or phone already exists. "
                "Please use your existing login or contact support."
            ),
        )

    # Generate password and hash (reuse corporate pattern based on agency name)
    raw_password = generate_corporate_password(payload.agency_name)
    password_hash = get_password_hash(raw_password)

    # Create user (inactive until admin approves partner)
    user = User(
        email=payload.email,
        password_hash=password_hash,
        user_type=UserType.EDUCATIONAL_PARTNER,
        is_active=False,
    )
    db.add(user)
    db.flush()

    # Create educational partner record (inactive until admin approves)
    partner = EducationalPartner(
        user_id=user.id,
        institution_name=payload.agency_name,
        institution_type="agency",
        country="Nigeria",
        contact_person=payload.contact_person,
        contact_email=payload.email,
        contact_phone=payload.phone,
        is_active=False,
        extra_data={
            "company_type": payload.company_type,  # Store company type for CAC verification
            "cac_number": payload.cac_number,
            "nin": payload.nin,
            "state": payload.state,
            "lga": payload.lga,
            "address": payload.address,
            "enrollment_range": payload.enrollment_range,
            # Persist a stable slug derived from agency name for referral URLs.
            "slug": _slugify(payload.agency_name),
        },
    )
    db.add(partner)
    db.commit()
    db.refresh(partner)

    # Send onboarding email with discovery session booking link
    base_url = (settings.frontend_base_url or "https://proconnect-edu.online").rstrip("/")
    discovery_url = f"{base_url}/book-discovery-session?partnerType=edu&partnerId={partner.id}"
    deadline_dt = datetime.utcnow() + timedelta(hours=48)
    booking_deadline_str = deadline_dt.strftime("%B %d, %Y")
    try:
        EmailService.send_agency_onboarding_email(
            contact_person=payload.contact_person,
            agency_name=payload.agency_name,
            partner_email=payload.email,
            discovery_booking_url=discovery_url,
            booking_deadline_date=booking_deadline_str,
        )
    except Exception as e:
        logging.getLogger(__name__).warning(f"Agency onboarding email failed: {e}")

    return SuccessResponse(
        data={
            "user": {
                "id": str(user.id),
                "email": user.email,
                "userType": user.user_type.value,
            },
            "partner": {
                "id": str(partner.id),
                "institution_name": partner.institution_name,
            },
            "initial_password": raw_password,
        },
        message="Educational partner registered successfully. Your account is pending approval; you will be able to log in once an administrator approves it. Check your email for the Discovery Session booking link.",
    )


# ========== Corporate Partners ==========
@router.get("/corporate/me", response_model=SuccessResponse)
async def get_my_corporate_partner(
    db: Session = Depends(get_db),
    current_user_data: dict = Depends(get_current_user),
):
    """Get current user's corporate partner profile with KPIs."""
    user_data = current_user_data
    if user_data.get("type") != "user":
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Authentication required",
        )
    
    user = user_data.get("user")
    
    # Find partner by user_id
    partner = db.query(CorporatePartner).filter(
        CorporatePartner.user_id == user.id
    ).first()
    
    if not partner:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Corporate partner profile not found",
        )
    
    # Calculate KPIs
    enrolled_count = db.query(PartnerEnrollment).filter(
        PartnerEnrollment.corporate_partner_id == partner.id
    ).count()
    
    active_opportunities = db.query(PartnerOpportunity).filter(
        PartnerOpportunity.partner_id == partner.id,
        PartnerOpportunity.is_active == True
    ).count()
    
    total_applications = (
        db.query(OpportunityApplication)
        .join(PartnerOpportunity, OpportunityApplication.opportunity_id == PartnerOpportunity.id)
        .filter(PartnerOpportunity.partner_id == partner.id)
        .count()
    )
    
    partner_data = CorporatePartnerResponse.model_validate(partner).model_dump()
    partner_data.update({
        "kpis": {
            "enrolled_students": enrolled_count,
            "active_opportunities": active_opportunities,
            "total_applications": total_applications,
        }
    })
    
    return SuccessResponse(
        data=partner_data,
        message="Corporate partner profile retrieved successfully",
    )


@router.get("/corporate", response_model=SuccessResponse)
async def list_corporate_partners(
    is_active: Optional[bool] = Query(None),
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin),
):
    """List corporate partners with KPIs (admin only)."""
    query = db.query(CorporatePartner)
    
    if is_active is not None:
        query = query.filter(CorporatePartner.is_active == is_active)
    
    partners = query.all()
    
    # Calculate KPIs for each partner
    result = []
    for partner in partners:
        # Count enrolled students
        enrolled_count = db.query(PartnerEnrollment).filter(
            PartnerEnrollment.corporate_partner_id == partner.id
        ).count()
        
        # Count active opportunities
        active_opportunities = db.query(PartnerOpportunity).filter(
            PartnerOpportunity.partner_id == partner.id,
            PartnerOpportunity.is_active == True
        ).count()
        
        # Count total applications
        total_applications = (
            db.query(OpportunityApplication)
            .join(PartnerOpportunity, OpportunityApplication.opportunity_id == PartnerOpportunity.id)
            .filter(PartnerOpportunity.partner_id == partner.id)
            .count()
        )
        
        partner_data = CorporatePartnerResponse.model_validate(partner).model_dump()
        partner_data.update({
            "kpis": {
                "enrolled_students": enrolled_count,
                "active_opportunities": active_opportunities,
                "total_applications": total_applications,
            }
        })
        result.append(partner_data)
    
    return SuccessResponse(
        data=result,
        message="Corporate partners retrieved successfully",
    )


@router.post("/corporate", response_model=SuccessResponse, status_code=status.HTTP_201_CREATED)
async def create_corporate_partner(
    partner_data: CorporatePartnerCreate,
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin),
):
    """Create a corporate partner (admin only)."""
    # Verify user exists
    from app.models.user import User
    user = db.query(User).filter(User.id == partner_data.user_id).first()
    if not user:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="User not found",
        )
    
    # Check if partner already exists for this user
    existing = db.query(CorporatePartner).filter(
        CorporatePartner.user_id == partner_data.user_id
    ).first()
    if existing:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Corporate partner already exists for this user",
        )
    
    partner = CorporatePartner(**partner_data.model_dump())
    db.add(partner)
    db.commit()
    db.refresh(partner)
    
    return SuccessResponse(
        data=CorporatePartnerResponse.model_validate(partner).model_dump(),
        message="Corporate partner created successfully",
    )


@router.get("/corporate/{partner_id}", response_model=SuccessResponse)
async def get_corporate_partner_by_id(
    partner_id: UUID,
    db: Session = Depends(get_db),
):
    """Public endpoint to get corporate partner info by ID (for enrollment forms)."""
    partner = db.query(CorporatePartner).filter(CorporatePartner.id == partner_id).first()
    if not partner:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Corporate partner not found",
        )
    
    partner_data = CorporatePartnerResponse.model_validate(partner).model_dump()
    
    return SuccessResponse(
        data=partner_data,
        message="Corporate partner retrieved successfully",
    )


logger = logging.getLogger(__name__)
GENERIC_DELETE_ERROR = "Unable to delete this partner. Please try again or contact support."


@router.delete("/corporate/{partner_id}")
async def delete_corporate_partner(
    partner_id: UUID,
    db: Session = Depends(get_db),
    current_admin=Depends(get_current_admin),
):
    """Admin only: permanently delete a corporate partner and related data (user, discovery, waitlist refs, biz verification)."""
    partner = db.query(CorporatePartner).filter(CorporatePartner.id == partner_id).first()
    if not partner:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Partner not found",
        )
    user_id = partner.user_id
    try:
        # 1. Null waitlist references to this partner
        db.query(Waitlist).filter(Waitlist.source_id == partner_id).update(
            {Waitlist.source_id: None, Waitlist.source: None, Waitlist.source_name: None},
            synchronize_session="fetch",
        )
        # 2. Delete biz verification for this partner's CAC (from extra_data)
        cac = (partner.extra_data or {}).get("cac_number") if isinstance(partner.extra_data, dict) else None
        if cac:
            db.query(BizVerification).filter(BizVerification.cac_number == cac).delete(synchronize_session="fetch")
        # 3. Delete discovery sessions (optional; cascade will do it when we delete partner)
        db.query(DiscoverySession).filter(DiscoverySession.corporate_partner_id == partner_id).delete(synchronize_session="fetch")
        # 4. Delete partner (cascades opportunities, enrollments)
        db.delete(partner)
        # 5. Delete linked user
        user = db.query(User).filter(User.id == user_id).first()
        if user:
            db.delete(user)
        db.commit()
    except Exception as e:
        db.rollback()
        logger.exception("Corporate partner delete failed for %s: %s", partner_id, e)
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=GENERIC_DELETE_ERROR,
        ) from e
    return SuccessResponse(data=None, message="Partner deleted successfully.")


# POU branding for approval emails
POU_LOGO_URL = "https://ik.imagekit.io/pouasset/general/POULOGO.png?updatedAt=1770897412554"
POU_HEADER_GREEN = "#047857"  # emerald-800


def _plain_text_to_html(text: str) -> str:
    """Convert plain text email body to simple HTML."""
    if not text:
        return ""
    escaped = text.replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;")
    paragraphs = escaped.replace("\r\n", "\n").replace("\r", "\n").split("\n\n")
    html_parts = [f"<p>{p.replace(chr(10), '<br/>')}</p>" for p in paragraphs if p.strip()]
    return "<html><body>" + "".join(html_parts) + "</body></html>"


def _wrap_partner_notification_html(plain_body: str) -> str:
    """Wrap rejection/chat/other partner emails in POU template (logo + green header). Content matches preview; no login details or button."""
    if not plain_body:
        return _wrap_email_template("<p></p>")
    escaped = plain_body.replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;")
    paragraphs = escaped.replace("\r\n", "\n").replace("\r", "\n").split("\n\n")
    body_parts = [f"<p style='margin: 0 0 1em;'>{p.replace(chr(10), '<br/>')}</p>" for p in paragraphs if p.strip()]
    return _wrap_email_template("".join(body_parts))


def _wrap_email_template(inner_html: str) -> str:
    """Wrap content in POU email template with logo and green header."""
    return f"""<!DOCTYPE html>
<html>
<head><meta charset="utf-8"><meta name="viewport" content="width=device-width, initial-scale=1.0"></head>
<body style="margin: 0; padding: 0; font-family: sans-serif; line-height: 1.6;">
<table width="100%" cellpadding="0" cellspacing="0" style="background: #f8fafc;">
  <tr><td style="padding: 24px 16px;" align="center">
    <table width="100%" style="max-width: 600px;" cellpadding="0" cellspacing="0">
      <tr>
        <td style="background: {POU_HEADER_GREEN}; padding: 20px 24px; border-radius: 8px 8px 0 0;" align="center">
          <img src="{POU_LOGO_URL}" alt="POU" style="max-width: 160px; height: auto; display: block;" />
        </td>
      </tr>
      <tr>
        <td style="background: #ffffff; padding: 28px 24px; border: 1px solid #e2e8f0; border-top: none; border-radius: 0 0 8px 8px;">
          {inner_html}
        </td>
      </tr>
    </table>
  </td></tr>
</table>
</body>
</html>"""


def _build_approval_email_html(
    preview_body_plain: str,
    username: str,
    password: str,
    role: str,
    login_url: str,
) -> str:
    """Build approval email: keep preview message and insert login block after 'Welcome to the revenue engine'. Wrapped in POU template with logo and green header."""
    import re
    needle = re.compile(r"Welcome to the revenue engine\.?", re.IGNORECASE)
    login_block_plain = (
        "Use the details below to sign in.\n"
        f"Username: {username}\n"
        f"Password: {password}\n"
        f"Role: {role}"
    )
    if preview_body_plain and needle.search(preview_body_plain):
        match = needle.search(preview_body_plain)
        assert match is not None
        # End of the matched phrase, then skip to end of line (newline or end of string)
        insert_start = match.end()
        line_end = preview_body_plain.find("\n", insert_start)
        if line_end == -1:
            line_end = len(preview_body_plain)
        else:
            line_end += 1  # include the newline in "before"
        before = preview_body_plain[:line_end]
        after = preview_body_plain[line_end:].lstrip()
        combined = before + "\n\n" + login_block_plain + "\n\n" + after
    else:
        base = preview_body_plain or "Your partner application has been approved."
        combined = base.rstrip() + "\n\n" + login_block_plain

    # Convert combined text to HTML
    escaped = combined.replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;")
    paragraphs = escaped.replace("\r\n", "\n").replace("\r", "\n").split("\n\n")
    body_parts = [f"<p style='margin: 0 0 1em;'>{p.replace(chr(10), '<br/>')}</p>" for p in paragraphs if p.strip()]

    # Add Go to Dashboard button and footnote
    login_display = login_url if login_url else "#"
    body_parts.append(
        f"<p style='margin: 1.5em 0 0;'><a href='{login_display}' style='display: inline-block; padding: 12px 24px; background: {POU_HEADER_GREEN}; color: #fff; text-decoration: none; border-radius: 8px; font-weight: bold;'>Go to Dashboard</a></p>"
    )
    body_parts.append("<p style='margin-top: 24px; color: #64748b; font-size: 14px;'>You can change your password after first login.</p>")

    inner_html = "".join(body_parts)
    return _wrap_email_template(inner_html)


@router.patch("/corporate/{partner_id}/status", response_model=SuccessResponse)
async def update_corporate_partner_status(
    partner_id: UUID,
    payload: PartnerStatusUpdate,
    db: Session = Depends(get_db),
    current_admin=Depends(get_current_admin),
):
    """Admin only: set corporate partner active/inactive and sync linked User.is_active. Optionally send notification email."""
    partner = db.query(CorporatePartner).filter(CorporatePartner.id == partner_id).first()
    if not partner:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Corporate partner not found",
        )
    user = db.query(User).filter(User.id == partner.user_id).first()
    if not user:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail="Partner has no linked user",
        )
    to_email = partner.contact_email or user.email
    if to_email:
        if payload.is_active:
            # Approval email: keep preview message, insert login block after "Welcome to the revenue engine", use POU template
            password = generate_corporate_password(partner.company_name)
            base_url = (payload.login_base_url or settings.frontend_base_url or "").rstrip("/")
            login_url = f"{base_url}/login" if base_url else ""
            subject = payload.email_subject or "Your POU Corporate Partner account is approved"
            html_content = _build_approval_email_html(
                preview_body_plain=payload.email_body or "",
                username=user.email,
                password=password,
                role="Corporate Partner",
                login_url=login_url,
            )
            email_result = EmailService.send_email(to=to_email, subject=subject, html_content=html_content)
            if not email_result.get("success"):
                import logging
                logging.getLogger(__name__).warning(f"Partner approval email failed: {email_result.get('error')}")
        elif payload.email_subject and payload.email_body:
            html_content = _wrap_partner_notification_html(payload.email_body)
            email_result = EmailService.send_email(to=to_email, subject=payload.email_subject, html_content=html_content)
            if not email_result.get("success"):
                import logging
                logging.getLogger(__name__).warning(f"Partner notification email failed: {email_result.get('error')}")
    partner.is_active = payload.is_active
    user.is_active = payload.is_active
    db.commit()
    db.refresh(partner)
    partner_data = CorporatePartnerResponse.model_validate(partner).model_dump()
    return SuccessResponse(
        data=partner_data,
        message="Corporate partner status updated successfully.",
    )


@router.get("/corporate/{partner_id}/students", response_model=SuccessResponse)
async def get_corporate_partner_students(
    partner_id: UUID,
    db: Session = Depends(get_db),
    current_user_data: dict = Depends(get_current_user),
):
    """Get students recommended/enrolled through corporate partner."""
    # Verify partner exists
    partner = db.query(CorporatePartner).filter(CorporatePartner.id == partner_id).first()
    if not partner:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Corporate partner not found",
        )
    
    # Partners can only see their own students
    user_data = current_user_data
    if user_data.get("type") == "user":
        user = user_data.get("user")
        if user.user_type == UserType.CORPORATE_PARTNER and str(partner.user_id) != str(user.id):
            raise HTTPException(
                status_code=status.HTTP_403_FORBIDDEN,
                detail="You can only view your own enrolled students",
            )
    
    enrollments = db.query(PartnerEnrollment).filter(
        PartnerEnrollment.corporate_partner_id == partner_id
    ).all()

    # Get student details
    from app.models.user import User

    students = []
    for enrollment in enrollments:
        student = db.query(User).filter(User.id == enrollment.student_id).first()
        if not student:
            continue

        # Try to find the student's application (for name + programme)
        application = None
        if student.application_id:
            application = (
                db.query(Application)
                .filter(Application.application_id == student.application_id)
                .first()
            )
        if not application and student.email:
            application = (
                db.query(Application)
                .filter(func.lower(Application.email) == student.email.lower())
                .order_by(Application.submitted_at.desc())
                .first()
            )

        # Compute average GPA across the student's enrollments
        avg_grade_point = (
            db.query(func.avg(Enrollment.grade_point))
            .filter(
                Enrollment.student_id == student.id,
                Enrollment.grade_point.isnot(None),
            )
            .scalar()
        )

        first_name = application.first_name if application else None
        last_name = application.last_name if application else None
        full_name = " ".join([n for n in [first_name, last_name] if n]).strip() or None

        students.append(
            {
                "id": str(student.id),
                "name": full_name
                or (student.email.split("@")[0] if student.email else "Student"),
                "email": student.email,
                "matric_number": student.matric_number,
                "enrollment_date": enrollment.enrollment_date.isoformat()
                if enrollment.enrollment_date
                else None,
                "sponsorship_type": enrollment.sponsorship_type,
                "programme": application.programme if application else None,
                "avg_gpa": float(avg_grade_point) if avg_grade_point is not None else None,
            }
        )
    
    return SuccessResponse(
        data=students,
        message="Corporate partner students retrieved successfully",
    )


@router.get("/corporate/me/leads", response_model=SuccessResponse)
async def get_my_corporate_partner_leads(
    db: Session = Depends(get_db),
    current_user_data: dict = Depends(get_current_user),
):
    """
    Get the list of student leads (waitlist entries) that belong to the current
    corporate partner, enriched with application & payment status.

    Returns leads from waitlist where source='corporate' and source_id=partner.id.
    """
    user_data = current_user_data
    if user_data.get("type") != "user":
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Authentication required",
        )

    user = user_data.get("user")

    partner = db.query(CorporatePartner).filter(
        CorporatePartner.user_id == user.id
    ).first()

    if not partner:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Corporate partner profile not found",
        )

    # Normalized partner name for robust matching (case/punctuation insensitive)
    partner_name_norm = _normalize_name(partner.company_name or "")
    partner_name_lower = (partner.company_name or "").lower()

    # Fetch waitlist entries that are likely to belong to this corporate partner.
    # We:
    # - scope to corporate-sourced records where possible
    # - include entries where source_id matches this partner
    # - include entries where source_name matches this partner name (case-insensitive)
    waitlist_entries = (
        db.query(Waitlist)
        .filter(
            or_(
                Waitlist.source == "corporate",
                Waitlist.source_id == partner.id,
                func.lower(Waitlist.source_name) == partner_name_lower,
            ),
        )
        .order_by(Waitlist.created_at.desc())
        .all()
    )

    leads: list[dict] = []

    for entry in waitlist_entries:
        has_applied = False
        application_status = None
        payment_confirmed = False

        # Skip entries that don't actually belong to this partner after normalization
        if not (
            entry.source_id == partner.id
            or _normalize_name(entry.source_name or "") == partner_name_norm
        ):
            continue

        if entry.has_applied and entry.application_id:
            has_applied = True
            app = db.query(Application).filter(Application.id == entry.application_id).first()
            if app:
                application_status = app.status.value if app.status else "pending"
                completed_payment = (
                    db.query(Payment)
                    .filter(
                        Payment.application_id == app.id,
                        Payment.status == PaymentStatus.COMPLETED,
                    )
                    .first()
                )
                if completed_payment:
                    payment_confirmed = True
        else:
            app = db.query(Application).filter(
                func.lower(Application.email) == (entry.email or "").lower()
            ).first()
            if app:
                has_applied = True
                application_status = app.status.value if app.status else "pending"
                completed_payment = (
                    db.query(Payment)
                    .filter(
                        Payment.application_id == app.id,
                        Payment.status == PaymentStatus.COMPLETED,
                    )
                    .first()
                )
                if completed_payment:
                    payment_confirmed = True

        if payment_confirmed:
            display_status = "Paid"
        elif application_status == "approved":
            display_status = "Accepted"
        elif has_applied:
            display_status = "Applied"
        else:
            display_status = "Lead"

        leads.append({
            "id": str(entry.id),
            "name": entry.full_name,
            "email": entry.email,
            "phone": entry.phone,
            "whatsapp_phone": entry.whatsapp_phone,
            "gender": entry.gender,
            "date_of_birth": entry.date_of_birth.isoformat() if entry.date_of_birth else None,
            "age": entry.age,
            "state": entry.state,
            "lga": entry.lga,
            "qualification": entry.qualification,
            "status": display_status,
            "has_applied": has_applied,
            "application_status": application_status,
            "payment_confirmed": payment_confirmed,
            "date": entry.created_at.isoformat() if entry.created_at else None,
        })

    return SuccessResponse(
        data={
            "leads": leads,
            "stats": {
                "total_leads": len(leads),
                "applied_count": sum(1 for l in leads if l.get("has_applied")),
                "paid_count": sum(1 for l in leads if l.get("payment_confirmed")),
            },
        },
        message="Corporate partner leads retrieved successfully",
    )


@router.post("/corporate/{partner_id}/opportunities", response_model=SuccessResponse, status_code=status.HTTP_201_CREATED)
async def create_partner_opportunity(
    partner_id: UUID,
    opportunity_data: PartnerOpportunityCreate,
    db: Session = Depends(get_db),
    current_user_data: dict = Depends(get_current_user),
):
    """Create a job/internship opportunity (partner or admin only)."""
    # Verify partner exists
    partner = db.query(CorporatePartner).filter(CorporatePartner.id == partner_id).first()
    if not partner:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Corporate partner not found",
        )
    
    # Verify user has permission
    user_data = current_user_data
    if user_data.get("type") == "user":
        user = user_data.get("user")
        if user.user_type == UserType.CORPORATE_PARTNER and str(partner.user_id) != str(user.id):
            raise HTTPException(
                status_code=status.HTTP_403_FORBIDDEN,
                detail="You can only create opportunities for your own partner account",
            )
    
    opportunity = PartnerOpportunity(partner_id=partner_id, **opportunity_data.model_dump())
    db.add(opportunity)
    db.commit()
    db.refresh(opportunity)
    
    return SuccessResponse(
        data=PartnerOpportunityResponse.model_validate(opportunity).model_dump(),
        message="Partner opportunity created successfully",
    )


@router.get("/corporate/{partner_id}/opportunities", response_model=SuccessResponse)
async def list_partner_opportunities(
    partner_id: UUID,
    is_active: Optional[bool] = Query(None),
    db: Session = Depends(get_db),
    current_user_data: dict = Depends(get_current_user),
):
    """List job/internship opportunities for a corporate partner (partner or admin)."""
    # Verify partner exists
    partner = db.query(CorporatePartner).filter(CorporatePartner.id == partner_id).first()
    if not partner:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Corporate partner not found",
        )

    # Verify user has permission
    user_data = current_user_data
    if user_data.get("type") == "user":
        user = user_data.get("user")
        if user.user_type == UserType.CORPORATE_PARTNER and str(partner.user_id) != str(user.id):
            raise HTTPException(
                status_code=status.HTTP_403_FORBIDDEN,
                detail="You can only view opportunities for your own partner account",
            )

    q = db.query(PartnerOpportunity).filter(PartnerOpportunity.partner_id == partner_id)
    if is_active is not None:
        q = q.filter(PartnerOpportunity.is_active == is_active)

    opportunities = q.order_by(PartnerOpportunity.created_at.desc()).all()

    return SuccessResponse(
        data=[PartnerOpportunityResponse.model_validate(o).model_dump() for o in opportunities],
        message="Partner opportunities retrieved successfully",
    )


# ========== Educational Partners ==========
@router.get("/education/me", response_model=SuccessResponse)
async def get_my_educational_partner(
    db: Session = Depends(get_db),
    current_user_data: dict = Depends(get_current_user),
):
    """Get current user's educational partner profile with KPIs."""
    user_data = current_user_data
    if user_data.get("type") != "user":
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Authentication required",
        )
    
    user = user_data.get("user")
    
    # Find partner by user_id
    partner = db.query(EducationalPartner).filter(
        EducationalPartner.user_id == user.id
    ).first()
    
    if not partner:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Educational partner profile not found",
        )
    
    # Get cohorts
    cohorts = db.query(PartnerCohort).filter(
        PartnerCohort.educational_partner_id == partner.id
    ).all()
    
    total_enrollments = sum(c.total_enrollments for c in cohorts)
    total_revenue = sum(float(c.revenue_generated) for c in cohorts)
    
    partner_data = EducationalPartnerResponse.model_validate(partner).model_dump()
    partner_data.update({
        "kpis": {
            "total_cohorts": len(cohorts),
            "total_enrollments": total_enrollments,
            "total_revenue": total_revenue,
            "revenue_share": float(partner.revenue_share_percentage or 0),
        },
        "cohorts": [PartnerCohortResponse.model_validate(c).model_dump() for c in cohorts],
    })
    
    return SuccessResponse(
        data=partner_data,
        message="Educational partner profile retrieved successfully",
    )


@router.get("/education/me/leads", response_model=SuccessResponse)
async def get_my_educational_partner_leads(
    db: Session = Depends(get_db),
    current_user_data: dict = Depends(get_current_user),
):
    """
    Get the list of student leads (waitlist entries) that belong to the current
    educational partner, enriched with application & payment status.

    Also returns summary statistics:
      - total_leads
      - applied_count  (students who submitted an application)
      - paid_count     (students whose application has a completed payment)
      - target         (the licensing cycle target – 1 000)
      - target_percent (leads / target * 100)
      - commission_potential (paid_count * 14 075)
    """
    user_data = current_user_data
    if user_data.get("type") != "user":
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Authentication required",
        )

    user = user_data.get("user")

    # Find educational partner for the authenticated user
    partner = db.query(EducationalPartner).filter(
        EducationalPartner.user_id == user.id
    ).first()

    if not partner:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Educational partner profile not found",
        )

    # ---------- Fetch waitlist entries that belong to this partner ----------
    # Normalized partner name for robust matching (case/punctuation insensitive)
    partner_name_norm = _normalize_name(partner.institution_name or "")
    partner_name_lower = (partner.institution_name or "").lower()

    # Match by canonical UUID (source_id) OR unique source_name, and
    # prefer edu/educational sources but still include legacy rows.
    waitlist_entries = (
        db.query(Waitlist)
        .filter(
            or_(
                Waitlist.source.in_(["edu", "educational"]),
                Waitlist.source_id == partner.id,
            ),
            or_(
                Waitlist.source_id == partner.id,
                func.lower(Waitlist.source_name) == partner_name_lower,
            ),
        )
        .order_by(Waitlist.created_at.desc())
        .all()
    )

    # ---------- Enrich each entry with application + payment status ----------
    ACCEPTANCE_FEE = 10_500
    TUITION_FEE = 100_000
    COMMISSION_RATE = 0.15  # 15 %
    PER_STUDENT_COMMISSION = round((ACCEPTANCE_FEE + TUITION_FEE) * COMMISSION_RATE)  # 16 575? Actually the UI says 14 075, let's keep the constant from the UI
    # The UI shows: Acceptance Fee ₦10,500 + Tuition Fee ₦100,000 => 15 % = ₦16,575
    # but sidebar says "Total Partner Commission ₦14,075". We'll compute dynamically.
    PER_STUDENT_COMMISSION = round((ACCEPTANCE_FEE + TUITION_FEE) * COMMISSION_RATE)

    leads: list[dict] = []
    applied_count = 0
    paid_count = 0

    for entry in waitlist_entries:
        # Skip entries that don't actually belong to this partner after normalization
        if not (
            entry.source_id == partner.id
            or _normalize_name(entry.source_name or "") == partner_name_norm
        ):
            continue

        # Determine application status by matching email in applications table
        has_applied = False
        application_status = None
        payment_confirmed = False

        app = None
        # First check the waitlist's own tracking columns
        if entry.has_applied and entry.application_id:
            has_applied = True
            app = db.query(Application).filter(Application.id == entry.application_id).first()
            if app:
                application_status = app.status.value if app.status else "pending"
                # Check if application has a completed payment
                completed_payment = (
                    db.query(Payment)
                    .filter(
                        Payment.application_id == app.id,
                        Payment.status == PaymentStatus.COMPLETED,
                    )
                    .first()
                )
                if completed_payment:
                    payment_confirmed = True
        else:
            # Fallback: look up by email match in applications table
            app = db.query(Application).filter(
                func.lower(Application.email) == (entry.email or "").lower()
            ).first()
            if app:
                has_applied = True
                application_status = app.status.value if app.status else "pending"
                completed_payment = (
                    db.query(Payment)
                    .filter(
                        Payment.application_id == app.id,
                        Payment.status == PaymentStatus.COMPLETED,
                    )
                    .first()
                )
                if completed_payment:
                    payment_confirmed = True

        if has_applied:
            applied_count += 1
        if payment_confirmed:
            paid_count += 1

        # Derive a human-readable status
        if payment_confirmed:
            display_status = "Paid"
        elif application_status == "approved":
            display_status = "Accepted"
        elif has_applied:
            display_status = "Applied"
        else:
            display_status = "Lead"

        nin = app.nin if app else None
        waec_number = app.waec_number if app else None
        waec_year = app.waec_year if app else None
        waec_details = None
        if waec_number and waec_year:
            waec_details = f"{waec_number} / {waec_year}"
        elif waec_number:
            waec_details = str(waec_number)
        application_id = app.application_id if app else None
        leads.append({
            "id": str(entry.id),
            "name": entry.full_name,
            "email": entry.email,
            "phone": entry.phone,
            "whatsapp_phone": entry.whatsapp_phone,
            "gender": entry.gender,
            "date_of_birth": entry.date_of_birth.isoformat() if entry.date_of_birth else None,
            "age": entry.age,
            "state": entry.state,
            "lga": entry.lga,
            "qualification": entry.qualification,
            "status": display_status,
            "has_applied": has_applied,
            "application_status": application_status,
            "payment_confirmed": payment_confirmed,
            "date": entry.created_at.isoformat() if entry.created_at else None,
            "partner_id": str(partner.id),
            "partner_name": partner.institution_name or "Educational Partner",
            "application_id": application_id,
            "nin": nin,
            "waec_number": waec_number,
            "waec_year": waec_year,
            "waec_details": waec_details,
        })

    # ---------- Summary statistics ----------
    total_leads = len(leads)
    target = 1000
    target_percent = round((total_leads / target) * 100, 1) if target > 0 else 0
    commission_potential = paid_count * PER_STUDENT_COMMISSION

    return SuccessResponse(
        data={
            "leads": leads,
            "stats": {
                "total_leads": total_leads,
                "applied_count": applied_count,
                "paid_count": paid_count,
                "target": target,
                "target_percent": target_percent,
                "commission_potential": commission_potential,
            },
        },
        message="Educational partner leads retrieved successfully",
    )


@router.get("/education/{partner_id}", response_model=SuccessResponse)
async def get_educational_partner_by_id(
    partner_id: UUID,
    db: Session = Depends(get_db),
):
    """Public endpoint to get educational partner info by ID (for enrollment forms)."""
    partner = db.query(EducationalPartner).filter(EducationalPartner.id == partner_id).first()
    if not partner:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Educational partner not found",
        )

    partner_data = EducationalPartnerResponse.model_validate(partner).model_dump()

    return SuccessResponse(
        data=partner_data,
        message="Educational partner retrieved successfully",
    )


@router.patch("/education/{partner_id}/status", response_model=SuccessResponse)
async def update_educational_partner_status(
    partner_id: UUID,
    payload: PartnerStatusUpdate,
    db: Session = Depends(get_db),
    current_admin=Depends(get_current_admin),
):
    """Admin only: set educational partner active/inactive and sync linked User.is_active. Optionally send notification email."""
    partner = db.query(EducationalPartner).filter(EducationalPartner.id == partner_id).first()
    if not partner:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Educational partner not found",
        )
    user = db.query(User).filter(User.id == partner.user_id).first()
    if not user:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail="Partner has no linked user",
        )
    to_email = partner.contact_email or user.email
    if to_email:
        if payload.is_active:
            # Approval email: keep preview message, insert login block after "Welcome to the revenue engine", use POU template
            password = generate_corporate_password(partner.institution_name or "")
            base_url = (payload.login_base_url or settings.frontend_base_url or "").rstrip("/")
            login_url = f"{base_url}/login" if base_url else ""
            subject = payload.email_subject or "Your POU Educational Partner account is approved"
            html_content = _build_approval_email_html(
                preview_body_plain=payload.email_body or "",
                username=user.email,
                password=password,
                role="Educational Partner",
                login_url=login_url,
            )
            email_result = EmailService.send_email(to=to_email, subject=subject, html_content=html_content)
            if not email_result.get("success"):
                import logging
                logging.getLogger(__name__).warning(f"Partner approval email failed: {email_result.get('error')}")
        elif payload.email_subject and payload.email_body:
            html_content = _wrap_partner_notification_html(payload.email_body)
            email_result = EmailService.send_email(to=to_email, subject=payload.email_subject, html_content=html_content)
            if not email_result.get("success"):
                import logging
                logging.getLogger(__name__).warning(f"Partner notification email failed: {email_result.get('error')}")
    partner.is_active = payload.is_active
    user.is_active = payload.is_active
    db.commit()
    db.refresh(partner)
    partner_data = EducationalPartnerResponse.model_validate(partner).model_dump()
    return SuccessResponse(
        data=partner_data,
        message="Educational partner status updated successfully.",
    )


@router.delete("/education/{partner_id}")
async def delete_educational_partner(
    partner_id: UUID,
    db: Session = Depends(get_db),
    current_admin=Depends(get_current_admin),
):
    """Admin only: permanently delete an educational partner and related data (user, discovery, waitlist refs, NIN verification)."""
    partner = db.query(EducationalPartner).filter(EducationalPartner.id == partner_id).first()
    if not partner:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Partner not found",
        )
    user_id = partner.user_id
    try:
        # 1. Null waitlist references to this partner
        db.query(Waitlist).filter(Waitlist.source_id == partner_id).update(
            {Waitlist.source_id: None, Waitlist.source: None, Waitlist.source_name: None},
            synchronize_session="fetch",
        )
        # 2. Delete NIN verification for this partner's NIN (from extra_data)
        nin = (partner.extra_data or {}).get("nin") if isinstance(partner.extra_data, dict) else None
        if nin:
            db.query(NINVerification).filter(NINVerification.nin == nin).delete(synchronize_session="fetch")
        # 3. Delete discovery sessions (cascade will also remove when we delete partner)
        db.query(DiscoverySession).filter(DiscoverySession.educational_partner_id == partner_id).delete(synchronize_session="fetch")
        # 4. Delete partner (cascades cohorts)
        db.delete(partner)
        # 5. Delete linked user
        user = db.query(User).filter(User.id == user_id).first()
        if user:
            db.delete(user)
        db.commit()
    except Exception as e:
        db.rollback()
        logger.exception("Educational partner delete failed for %s: %s", partner_id, e)
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=GENERIC_DELETE_ERROR,
        ) from e
    return SuccessResponse(data=None, message="Partner deleted successfully.")


@router.get("/education", response_model=SuccessResponse)
async def list_educational_partners(
    is_active: Optional[bool] = Query(None),
    db: Session = Depends(get_db),
    current_admin = Depends(get_current_admin),
):
    """List educational partners with cohorts and revenue data."""
    query = db.query(EducationalPartner)
    
    if is_active is not None:
        query = query.filter(EducationalPartner.is_active == is_active)
    
    partners = query.all()
    
    # Calculate KPIs for each partner
    result = []
    for partner in partners:
        # Get cohorts
        cohorts = db.query(PartnerCohort).filter(
            PartnerCohort.educational_partner_id == partner.id
        ).all()
        
        total_enrollments = sum(c.total_enrollments for c in cohorts)
        total_revenue = sum(float(c.revenue_generated) for c in cohorts)
        
        partner_data = EducationalPartnerResponse.model_validate(partner).model_dump()
        partner_data["extra_data"] = partner.extra_data or {}
        partner_data.update({
            "kpis": {
                "total_cohorts": len(cohorts),
                "total_enrollments": total_enrollments,
                "total_revenue": total_revenue,
                "revenue_share": float(partner.revenue_share_percentage or 0),
            },
            "cohorts": [PartnerCohortResponse.model_validate(c).model_dump() for c in cohorts],
        })
        result.append(partner_data)
    
    return SuccessResponse(
        data=result,
        message="Educational partners retrieved successfully",
    )


# State -> Region mapping for Nigeria (aligned with frontend GeographicDashboard)
STATE_TO_REGION = {
    "Lagos": "South West", "Oyo": "South West", "Ogun": "South West",
    "Ondo": "South West", "Osun": "South West", "Ekiti": "South West",
    "Rivers": "South South", "Edo": "South South", "Delta": "South South",
    "Akwa Ibom": "South South", "Bayelsa": "South South", "Cross River": "South South",
    "Enugu": "South East", "Anambra": "South East", "Ebonyi": "South East",
    "Imo": "South East", "Abia": "South East",
    "Abuja": "North Central", "Benue": "North Central", "Kogi": "North Central",
    "Kwara": "North Central", "Nasarawa": "North Central", "Niger": "North Central",
    "Plateau": "North Central",
    "Kano": "North West", "Kaduna": "North West", "Jigawa": "North West",
    "Kebbi": "North West", "Sokoto": "North West", "Zamfara": "North West",
    "Adamawa": "North East", "Bauchi": "North East", "Borno": "North East",
    "Gombe": "North East", "Taraba": "North East", "Yobe": "North East",
}


def _normalize_state(s: Optional[str]) -> Optional[str]:
    if not s or not str(s).strip():
        return None
    t = str(s).strip().title()
    # Handle FCT/Abuja
    if t.upper() in ("FCT", "ABUJA"):
        return "Abuja"
    if t in STATE_TO_REGION:
        return t
    # Try case-insensitive match
    for k in STATE_TO_REGION:
        if k.lower() == t.lower():
            return k
    return t


@router.get("/dashboard-overview", response_model=SuccessResponse)
async def get_partner_dashboard_overview(
    db: Session = Depends(get_db),
    current_admin=Depends(get_current_admin),
):
    """
    Admin/CRO: partner dashboard overview stats.
    Returns agencies+partners counts, pending approval, revenue (acceptance+tuition from student payments), pipeline status, and national engagement by state.
    """
    # 1. Partner counts (edu + corporate)
    edu_all = db.query(EducationalPartner).all()
    corp_all = db.query(CorporatePartner).all()
    total_approved = sum(1 for ep in edu_all if ep.is_active) + sum(1 for cp in corp_all if cp.is_active)
    pending_approval = sum(1 for ep in edu_all if not ep.is_active) + sum(1 for cp in corp_all if not cp.is_active)

    # 2. Revenue from student payments (acceptance + tuition)
    acc_rows = (
        db.query(func.coalesce(func.sum(Payment.amount), 0))
        .filter(Payment.status == PaymentStatus.COMPLETED, Payment.payment_type == PaymentType.ACCEPTANCE_FEE)
        .scalar()
    )
    tuition_rows = (
        db.query(func.coalesce(func.sum(Payment.amount), 0))
        .filter(Payment.status == PaymentStatus.COMPLETED, Payment.payment_type == PaymentType.TUITION)
        .scalar()
    )
    acceptance_revenue = float(acc_rows or 0)
    tuition_revenue = float(tuition_rows or 0)

    # 3. Pipeline status (partners only: approved vs pending; engaged/rejected = 0)
    pipeline_status = {
        "approved": total_approved,
        "engaged": 0,
        "pending": pending_approval,
        "rejected": 0,
    }

    # 4. National engagement by state (agencies + partners per state)
    state_buckets: dict[str, dict] = {s: {"state": s, "agencies": 0, "partners": 0} for s in STATE_TO_REGION}
    for ep in edu_all:
        st = _normalize_state((ep.extra_data or {}).get("state") if isinstance(ep.extra_data, dict) else None)
        if st and st in state_buckets:
            state_buckets[st]["agencies"] += 1
    for cp in corp_all:
        st = _normalize_state((cp.extra_data or {}).get("state") if isinstance(cp.extra_data, dict) else None)
        if not st:
            # Derive partner state from waitlist entries. Match leads linked by either
            # source_id (canonical UUID) or source_name (unique partner name), while
            # still scoping to corporate-sourced records.
            lead = (
                db.query(Waitlist.state)
                .filter(
                    Waitlist.source == "corporate",
                    or_(
                        Waitlist.source_id == cp.id,
                        Waitlist.source_name == cp.company_name,
                    ),
                )
                .first()
            )
            if lead:
                st = _normalize_state(lead[0])
        if not st:
            st = "Lagos"
        if st not in state_buckets:
            state_buckets[st] = {"state": st, "agencies": 0, "partners": 0}
        state_buckets[st]["partners"] += 1
    engagement_by_state = sorted(
        [{"state": v["state"], "val": v["agencies"] + v["partners"]} for v in state_buckets.values() if v["agencies"] or v["partners"]],
        key=lambda x: -x["val"],
    )[:12]

    return SuccessResponse(
        data={
            "total_agencies_and_partners": total_approved,
            "pending_approval": pending_approval,
            "acceptance_revenue": acceptance_revenue,
            "tuition_revenue": tuition_revenue,
            "pipeline_status": pipeline_status,
            "engagement_by_state": engagement_by_state,
        },
        message="Partner dashboard overview retrieved successfully",
    )


@router.get("/geographic-intel", response_model=SuccessResponse)
async def get_geographic_intel(
    db: Session = Depends(get_db),
    current_admin=Depends(get_current_admin),
):
    """
    Admin/CRO: geographic intelligence for partner dashboard.
    Returns agencies (edu), corporate partners, lead pipeline, and revenue by state/region.
    """
    # Initialize state-level buckets
    state_data: dict[str, dict] = {}
    for state_name, region in STATE_TO_REGION.items():
        state_data[state_name] = {
            "state": state_name,
            "region": region,
            "agencies": 0,
            "partners": 0,
            "students": 0,
            "students_agencies": 0,
            "students_partners": 0,
            "revenue": 0,
            "revenue_agencies": 0,
            "revenue_partners": 0,
        }

    # 1. Educational partners (agencies) - state from extra_data
    edu_partners = db.query(EducationalPartner).filter(EducationalPartner.is_active == True).all()
    for ep in edu_partners:
        state = _normalize_state((ep.extra_data or {}).get("state") if isinstance(ep.extra_data, dict) else None)
        if not state:
            state = "Lagos"  # Fallback so agencies without state still appear in regional breakdown
        if state and state in state_data:
            state_data[state]["agencies"] += 1
        elif state:
            state_data[state] = state_data.get(state) or {
                "state": state,
                "region": STATE_TO_REGION.get(state, "South West"),
                "agencies": 0,
                "partners": 0,
                "students": 0,
                "students_agencies": 0,
                "students_partners": 0,
                "revenue": 0,
                "revenue_agencies": 0,
                "revenue_partners": 0,
            }
            state_data[state]["agencies"] += 1

    # 2. Corporate partners - state from extra_data, or derive from primary lead state
    corp_partners = db.query(CorporatePartner).filter(CorporatePartner.is_active == True).all()
    edu_partner_ids = {ep.id for ep in edu_partners}
    corp_partner_ids = {cp.id for cp in corp_partners}

    for cp in corp_partners:
        state = _normalize_state((cp.extra_data or {}).get("state") if isinstance(cp.extra_data, dict) else None)
        if not state:
            # Derive from waitlist: most common state among leads from this corporate partner
            lead_states = (
                db.query(Waitlist.state, func.count(Waitlist.id).label("cnt"))
                .filter(Waitlist.source == "corporate", Waitlist.source_id == cp.id)
                .group_by(Waitlist.state)
                .order_by(func.count(Waitlist.id).desc())
                .limit(1)
                .first()
            )
            if lead_states:
                state = _normalize_state(lead_states[0])
        if not state:
            state = "Lagos"  # Fallback so partners without state still appear in regional breakdown
        if state and state in state_data:
            state_data[state]["partners"] += 1
        elif state:
            state_data[state] = state_data.get(state) or {
                "state": state,
                "region": STATE_TO_REGION.get(state, "South West"),
                "agencies": 0,
                "partners": 0,
                "students": 0,
                "students_agencies": 0,
                "students_partners": 0,
                "revenue": 0,
                "revenue_agencies": 0,
                "revenue_partners": 0,
            }
            state_data[state]["partners"] += 1

    # 3. Partner-sourced waitlist entries (lead pipeline) - attribute to partner's state
    waitlist_entries = (
        db.query(Waitlist)
        .filter(
            or_(
                Waitlist.source.in_(["edu", "educational", "corporate"]),
                Waitlist.source_id.isnot(None),
            )
        )
        .all()
    )
    # Build partner_id -> state mapping
    partner_state: dict[UUID, str] = {}
    for ep in edu_partners:
        st = _normalize_state((ep.extra_data or {}).get("state") if isinstance(ep.extra_data, dict) else None)
        if st:
            partner_state[ep.id] = st
    for cp in corp_partners:
        st = _normalize_state((cp.extra_data or {}).get("state") if isinstance(cp.extra_data, dict) else None)
        if not st:
            ls = (
                db.query(Waitlist.state)
                .filter(Waitlist.source == "corporate", Waitlist.source_id == cp.id)
                .first()
            )
            if ls:
                st = _normalize_state(ls[0])
        if st:
            partner_state[cp.id] = st
    for entry in waitlist_entries:
        if entry.source_id and entry.source_id in partner_state:
            st = partner_state[entry.source_id]
            if st in state_data:
                state_data[st]["students"] += 1
                if entry.source_id in edu_partner_ids:
                    state_data[st]["students_agencies"] += 1
                elif entry.source_id in corp_partner_ids:
                    state_data[st]["students_partners"] += 1

    # 4. Revenue from paid candidates referred by partners
    # Applications linked to waitlist (by application_id or email) with source edu/corporate
    partner_referred_emails: set[str] = set()
    partner_referred_app_ids: set[UUID] = set()
    for entry in waitlist_entries:
        if entry.source_id:
            partner_referred_emails.add((entry.email or "").lower())
            if entry.application_id:
                partner_referred_app_ids.add(entry.application_id)
    # Also link by email for applications not in waitlist.application_id
    for entry in waitlist_entries:
        if not entry.source_id:
            continue
        app = (
            db.query(Application)
            .filter(func.lower(Application.email) == (entry.email or "").lower())
            .first()
        )
        if app:
            partner_referred_app_ids.add(app.id)

    completed_payments = []
    if partner_referred_app_ids:
        completed_payments = (
            db.query(Payment)
            .filter(Payment.status == PaymentStatus.COMPLETED, Payment.application_id.in_(partner_referred_app_ids))
            .all()
        )
    # Attribute revenue to partner's state and partner type per application
    app_to_partner_state: dict[UUID, str] = {}
    app_to_partner_type: dict[UUID, str] = {}  # 'agency' | 'partner'
    for entry in waitlist_entries:
        if not entry.source_id or entry.source_id not in partner_state:
            continue
        st = partner_state[entry.source_id]
        ptype = "agency" if entry.source_id in edu_partner_ids else "partner"
        if entry.application_id:
            app_to_partner_state[entry.application_id] = st
            app_to_partner_type[entry.application_id] = ptype
        else:
            app = db.query(Application).filter(func.lower(Application.email) == (entry.email or "").lower()).first()
            if app:
                app_to_partner_state[app.id] = st
                app_to_partner_type[app.id] = ptype
    total_revenue = 0
    for pmt in completed_payments:
        amt = float(pmt.amount or 0)
        total_revenue += amt
        st = app_to_partner_state.get(pmt.application_id)
        ptype = app_to_partner_type.get(pmt.application_id)
        if st and st in state_data:
            state_data[st]["revenue"] += amt
            if ptype == "agency":
                state_data[st]["revenue_agencies"] += amt
            elif ptype == "partner":
                state_data[st]["revenue_partners"] += amt

    # Regional aggregation
    region_data: dict[str, dict] = {}
    for r in ["South West", "South South", "South East", "North Central", "North West", "North East"]:
        region_data[r] = {
            "region": r, "agencies": 0, "partners": 0, "students": 0,
            "students_agencies": 0, "students_partners": 0,
            "revenue": 0, "revenue_agencies": 0, "revenue_partners": 0,
        }
    for sd in state_data.values():
        r = sd["region"]
        if r in region_data:
            region_data[r]["agencies"] += sd["agencies"]
            region_data[r]["partners"] += sd["partners"]
            region_data[r]["students"] += sd["students"]
            region_data[r]["students_agencies"] += sd.get("students_agencies", 0)
            region_data[r]["students_partners"] += sd.get("students_partners", 0)
            region_data[r]["revenue"] += sd["revenue"]
            region_data[r]["revenue_agencies"] += sd.get("revenue_agencies", 0)
            region_data[r]["revenue_partners"] += sd.get("revenue_partners", 0)

    # Use direct counts for totals - partners/agencies without state must still be counted
    total_agencies = len(edu_partners)
    total_partners = len(corp_partners)
    total_students = sum(1 for e in waitlist_entries if e.source_id)
    # total_revenue already computed from completed_payments

    return SuccessResponse(
        data={
            "total_agencies": total_agencies,
            "total_partners": total_partners,
            "total_students": total_students,
            "total_revenue": total_revenue,
            "state_data": list(state_data.values()),
            "region_data": list(region_data.values()),
        },
        message="Geographic intel retrieved successfully",
    )


@router.get("/admin/candidates", response_model=SuccessResponse)
async def admin_list_partner_candidates(
    db: Session = Depends(get_db),
    current_admin=Depends(get_current_admin),
):
    """Admin/CRO: list all student candidates from partner-sourced waitlist (edu/corporate), with application and payment status."""
    waitlist_entries = (
        db.query(Waitlist)
        .filter(
            or_(
                Waitlist.source.in_(["edu", "educational", "corporate"]),
                Waitlist.source_id.isnot(None),
            )
        )
        .order_by(Waitlist.created_at.desc())
        .all()
    )

    candidates: List[dict] = []
    for entry in waitlist_entries:
        has_applied = False
        application_status = None
        payment_confirmed = False
        app = None

        if entry.has_applied and entry.application_id:
            app = db.query(Application).filter(Application.id == entry.application_id).first()
        if not app:
            app = (
                db.query(Application)
                .filter(func.lower(Application.email) == (entry.email or "").lower())
                .first()
            )
        if app:
            has_applied = True
            application_status = app.status.value if app.status else "pending"
            completed = (
                db.query(Payment)
                .filter(
                    Payment.application_id == app.id,
                    Payment.status == PaymentStatus.COMPLETED,
                )
                .first()
            )
            if completed:
                payment_confirmed = True

        if payment_confirmed:
            display_status = "Paid"
        elif application_status == "approved":
            display_status = "Accepted"
        elif has_applied:
            display_status = "Applied"
        else:
            display_status = "Lead"

        partner_name = entry.source_name or "Partner"
        partner_id = str(entry.source_id) if entry.source_id else None
        if entry.source_id:
            ep = db.query(EducationalPartner).filter(EducationalPartner.id == entry.source_id).first()
            if ep:
                partner_name = ep.institution_name or partner_name
            else:
                cp = db.query(CorporatePartner).filter(CorporatePartner.id == entry.source_id).first()
                if cp:
                    partner_name = cp.company_name or partner_name

        programme = app.programme if app else entry.qualification or "—"
        application_id = app.application_id if app else None
        nin = app.nin if app else None
        waec_number = app.waec_number if app else None
        waec_year = app.waec_year if app else None
        waec_details = None
        if waec_number and waec_year:
            waec_details = f"{waec_number} / {waec_year}"
        elif waec_number:
            waec_details = str(waec_number)
        candidates.append({
            "id": str(entry.id),
            "name": entry.full_name,
            "email": entry.email,
            "phone": entry.phone,
            "whatsapp_phone": entry.whatsapp_phone,
            "gender": entry.gender,
            "date_of_birth": entry.date_of_birth.isoformat() if entry.date_of_birth else None,
            "age": entry.age,
            "state": entry.state,
            "lga": entry.lga,
            "qualification": entry.qualification,
            "courseInterest": programme,
            "application_id": application_id,
            "status": display_status,
            "has_applied": has_applied,
            "application_status": application_status,
            "payment_confirmed": payment_confirmed,
            "date": entry.created_at.isoformat() if entry.created_at else None,
            "partner_id": partner_id,
            "partner_name": partner_name,
            "partnerId": partner_id,
            "partnerName": partner_name,
            "nin": nin,
            "waec_number": waec_number,
            "waec_year": waec_year,
            "waec_details": waec_details,
        })

    return SuccessResponse(
        data=candidates,
        message=f"Retrieved {len(candidates)} partner-sourced candidates",
    )


# ========== Discovery Sessions ==========

# Standard available time slots
STANDARD_SLOTS = ["09:00 AM", "10:30 AM", "01:00 PM", "02:30 PM", "04:00 PM"]


def _parse_slot_to_datetime(d: date, slot: str) -> datetime:
    """Parse '09:00 AM' style slot and combine with date to produce datetime."""
    m = re.match(r"(\d{1,2}):(\d{2})\s*(AM|PM)", slot, re.I)
    if not m:
        raise ValueError(f"Invalid time slot format: {slot}")
    h, mi = int(m.group(1)), int(m.group(2))
    if m.group(3).upper() == "PM" and h != 12:
        h += 12
    elif m.group(3).upper() == "AM" and h == 12:
        h = 0
    return datetime.combine(d, datetime.min.time().replace(hour=h, minute=mi))


# ========== Public Discovery (no auth - for post-registration booking) ==========

@router.get("/discovery/public/partner-info", response_model=SuccessResponse)
async def get_public_partner_info(
    partner_type: str = Query(..., description="edu or corporate"),
    partner_id: UUID = Query(...),
    db: Session = Depends(get_db),
):
    """Public: get partner info for discovery booking prefill. No auth required."""
    partner_type_lower = partner_type.lower()
    if partner_type_lower == "edu" or partner_type_lower == "educational":
        partner = db.query(EducationalPartner).filter(EducationalPartner.id == partner_id).first()
        if not partner:
            raise HTTPException(status_code=404, detail="Educational partner not found")
        return SuccessResponse(
            data={
                "agency_name": partner.institution_name or "Agency",
                "contact_person": partner.contact_person or "",
                "email": partner.contact_email or "",
                "partner_id": str(partner.id),
                "partner_type": "edu",
            },
            message="Partner info retrieved",
        )
    elif partner_type_lower == "corporate":
        partner = db.query(CorporatePartner).filter(CorporatePartner.id == partner_id).first()
        if not partner:
            raise HTTPException(status_code=404, detail="Corporate partner not found")
        return SuccessResponse(
            data={
                "agency_name": partner.company_name or "Corporate Partner",
                "contact_person": partner.contact_person or "",
                "email": partner.contact_email or "",
                "partner_id": str(partner.id),
                "partner_type": "corporate",
            },
            message="Partner info retrieved",
        )
    raise HTTPException(status_code=400, detail="Invalid partner_type. Use edu or corporate")


@router.get("/discovery/public/available-slots", response_model=SuccessResponse)
async def get_public_available_slots(
    date: str = Query(..., description="Date in YYYY-MM-DD format"),
    db: Session = Depends(get_db),
):
    """Public: get available discovery slots for a date. No auth required."""
    try:
        target_date = datetime.strptime(date, "%Y-%m-%d").date()
        target_datetime_start = datetime.combine(target_date, datetime.min.time())
        target_datetime_end = datetime.combine(target_date, datetime.max.time())
    except ValueError:
        raise HTTPException(status_code=400, detail="Invalid date format. Use YYYY-MM-DD")
    booked_sessions = db.query(DiscoverySession).filter(
        DiscoverySession.scheduled_date >= target_datetime_start,
        DiscoverySession.scheduled_date <= target_datetime_end,
        DiscoverySession.status == DiscoverySessionStatus.SCHEDULED,
    ).all()
    booked_slots = [s.scheduled_time for s in booked_sessions]
    available_slots = [s for s in STANDARD_SLOTS if s not in booked_slots]
    return SuccessResponse(
        data={"date": date, "available_slots": available_slots, "booked_slots": booked_slots},
        message="Available slots retrieved",
    )


@router.post("/discovery/public/book", response_model=SuccessResponse, status_code=status.HTTP_201_CREATED)
async def book_discovery_public(
    body: PublicDiscoveryBookRequest,
    db: Session = Depends(get_db),
):
    """Public: book discovery session using partner_type and partner_id (from email link). No auth required."""
    pt = body.partner_type.lower()
    if pt not in ("edu", "educational", "corporate"):
        raise HTTPException(status_code=400, detail="Invalid partner_type")
    partner = None
    partner_type_enum = None
    corporate_partner_id = None
    educational_partner_id = None
    user_id = None
    partner_name = ""
    partner_email = ""

    if pt in ("edu", "educational"):
        ep = db.query(EducationalPartner).filter(EducationalPartner.id == body.partner_id).first()
        if not ep:
            raise HTTPException(status_code=404, detail="Educational partner not found")
        partner = ep
        partner_type_enum = PartnerType.EDUCATIONAL
        educational_partner_id = ep.id
        user_id = ep.user_id
        partner_name = ep.institution_name or "Educational Partner"
        partner_email = ep.contact_email or ""
    else:
        cp = db.query(CorporatePartner).filter(CorporatePartner.id == body.partner_id).first()
        if not cp:
            raise HTTPException(status_code=404, detail="Corporate partner not found")
        partner = cp
        partner_type_enum = PartnerType.CORPORATE
        corporate_partner_id = cp.id
        user_id = cp.user_id
        partner_name = cp.company_name or "Corporate Partner"
        partner_email = cp.contact_email or ""

    if not user_id:
        raise HTTPException(status_code=400, detail="Partner has no user account")

    existing = db.query(DiscoverySession).filter(
        DiscoverySession.user_id == user_id,
        DiscoverySession.status == DiscoverySessionStatus.SCHEDULED,
    ).first()
    if existing:
        raise HTTPException(status_code=400, detail="You already have a scheduled discovery session.")

    if body.scheduled_time not in STANDARD_SLOTS:
        raise HTTPException(status_code=400, detail=f"Invalid time slot. Available: {', '.join(STANDARD_SLOTS)}")

    session_date = body.scheduled_date
    target_start = datetime.combine(session_date, datetime.min.time())
    target_end = datetime.combine(session_date, datetime.max.time())
    conflicting = db.query(DiscoverySession).filter(
        DiscoverySession.scheduled_date >= target_start,
        DiscoverySession.scheduled_date <= target_end,
        DiscoverySession.scheduled_time == body.scheduled_time,
        DiscoverySession.status == DiscoverySessionStatus.SCHEDULED,
    ).first()
    if conflicting:
        raise HTTPException(status_code=409, detail=f"Time slot {body.scheduled_time} is already booked.")

    scheduled_dt = _parse_slot_to_datetime(body.scheduled_date, body.scheduled_time)
    discovery_session = DiscoverySession(
        partner_type=partner_type_enum,
        corporate_partner_id=corporate_partner_id,
        educational_partner_id=educational_partner_id,
        user_id=user_id,
        scheduled_date=scheduled_dt,
        scheduled_time=body.scheduled_time,
        status=DiscoverySessionStatus.SCHEDULED,
        notes=body.notes,
        extra_data={"verification_status": "pending"},
    )
    db.add(discovery_session)
    db.commit()
    db.refresh(discovery_session)

    try:
        event_id, html_link, meet_link = GoogleCalendarService.create_discovery_event(
            partner_name=partner_name,
            partner_email=partner_email,
            scheduled_start=scheduled_dt,
        )
        if event_id or html_link or meet_link:
            discovery_session.google_calendar_event_id = event_id
            discovery_session.google_calendar_link = html_link
            extra = discovery_session.extra_data or {}
            extra["google_meet_link"] = meet_link
            discovery_session.extra_data = extra
            db.add(discovery_session)
            db.commit()
            db.refresh(discovery_session)
    except Exception:
        pass

    # Send email notification to partner and admin
    contact_person = (partner.contact_person or "") if partner else ""
    calendar_link = discovery_session.google_calendar_link or ""
    scheduled_date_str = discovery_session.scheduled_date.strftime("%A, %B %d, %Y") if discovery_session.scheduled_date else ""
    try:
        EmailService.send_discovery_session_booked_email(
            partner_name=partner_name,
            partner_email=partner_email,
            contact_person=contact_person or partner_name,
            scheduled_date=scheduled_date_str,
            scheduled_time=discovery_session.scheduled_time or "",
            calendar_link=calendar_link,
        )
    except Exception as e:
        logging.getLogger(__name__).warning("Discovery session booked email failed: %s", e)

    return SuccessResponse(
        data=DiscoverySessionResponse.model_validate(discovery_session).model_dump(),
        message="Discovery session booked successfully",
    )


@router.post("/discovery/admin/book", response_model=SuccessResponse, status_code=status.HTTP_201_CREATED)
async def admin_book_discovery_session(
    body: PublicDiscoveryBookRequest,
    db: Session = Depends(get_db),
    current_user_data: dict = Depends(get_current_user),
):
    """
    Admin: book a discovery session on behalf of a partner.

    Uses the same semantics as the public booking endpoint but:
      - Requires an authenticated admin
      - Does NOT restrict partners to a single scheduled session (only prevents slot conflicts)
    """
    user_data = current_user_data
    if user_data.get("type") != "admin":
        raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="Admin access required")

    pt = body.partner_type.lower()
    if pt not in ("edu", "educational", "corporate"):
        raise HTTPException(status_code=400, detail="Invalid partner_type")

    partner = None
    partner_type_enum = None
    corporate_partner_id = None
    educational_partner_id = None
    user_id = None
    partner_name = ""
    partner_email = ""

    if pt in ("edu", "educational"):
        ep = db.query(EducationalPartner).filter(EducationalPartner.id == body.partner_id).first()
        if not ep:
            raise HTTPException(status_code=404, detail="Educational partner not found")
        partner = ep
        partner_type_enum = PartnerType.EDUCATIONAL
        educational_partner_id = ep.id
        user_id = ep.user_id
        partner_name = ep.institution_name or "Educational Partner"
        partner_email = ep.contact_email or ""
    else:
        cp = db.query(CorporatePartner).filter(CorporatePartner.id == body.partner_id).first()
        if not cp:
            raise HTTPException(status_code=404, detail="Corporate partner not found")
        partner = cp
        partner_type_enum = PartnerType.CORPORATE
        corporate_partner_id = cp.id
        user_id = cp.user_id
        partner_name = cp.company_name or "Corporate Partner"
        partner_email = cp.contact_email or ""

    if not user_id:
        raise HTTPException(status_code=400, detail="Partner has no user account")

    # Allow multiple sessions per partner, but still prevent time-slot conflicts
    if body.scheduled_time not in STANDARD_SLOTS:
        raise HTTPException(status_code=400, detail=f"Invalid time slot. Available: {', '.join(STANDARD_SLOTS)}")

    session_date = body.scheduled_date
    target_start = datetime.combine(session_date, datetime.min.time())
    target_end = datetime.combine(session_date, datetime.max.time())
    conflicting = db.query(DiscoverySession).filter(
        DiscoverySession.scheduled_date >= target_start,
        DiscoverySession.scheduled_date <= target_end,
        DiscoverySession.scheduled_time == body.scheduled_time,
        DiscoverySession.status == DiscoverySessionStatus.SCHEDULED,
    ).first()
    if conflicting:
        raise HTTPException(status_code=409, detail=f"Time slot {body.scheduled_time} is already booked.")

    scheduled_dt = _parse_slot_to_datetime(body.scheduled_date, body.scheduled_time)
    discovery_session = DiscoverySession(
        partner_type=partner_type_enum,
        corporate_partner_id=corporate_partner_id,
        educational_partner_id=educational_partner_id,
        user_id=user_id,
        scheduled_date=scheduled_dt,
        scheduled_time=body.scheduled_time,
        status=DiscoverySessionStatus.SCHEDULED,
        notes=body.notes,
        extra_data={"verification_status": "pending"},
    )
    db.add(discovery_session)
    db.commit()
    db.refresh(discovery_session)

    try:
        event_id, html_link, meet_link = GoogleCalendarService.create_discovery_event(
            partner_name=partner_name,
            partner_email=partner_email,
            scheduled_start=scheduled_dt,
        )
        if event_id or html_link or meet_link:
            discovery_session.google_calendar_event_id = event_id
            discovery_session.google_calendar_link = html_link
            extra = discovery_session.extra_data or {}
            extra["google_meet_link"] = meet_link
            discovery_session.extra_data = extra
            db.add(discovery_session)
            db.commit()
            db.refresh(discovery_session)
    except Exception:
        # Keep booking successful even if Google Calendar integration fails
        pass

    # Optional: send email notification, same as public booking
    contact_person = (partner.contact_person or "") if partner else ""
    calendar_link = discovery_session.google_calendar_link or ""
    scheduled_date_str = (
        discovery_session.scheduled_date.strftime("%A, %B %d, %Y") if discovery_session.scheduled_date else ""
    )
    try:
        EmailService.send_discovery_session_booked_email(
            partner_name=partner_name,
            partner_email=partner_email,
            contact_person=contact_person or partner_name,
            scheduled_date=scheduled_date_str,
            scheduled_time=discovery_session.scheduled_time or "",
            calendar_link=calendar_link,
        )
    except Exception as e:
        logging.getLogger(__name__).warning("Admin discovery session booked email failed: %s", e)

    return SuccessResponse(
        data=DiscoverySessionResponse.model_validate(discovery_session).model_dump(),
        message="Discovery session booked successfully",
    )


# ========== Authenticated Discovery ==========

@router.get("/discovery/available-slots", response_model=SuccessResponse)
async def get_available_slots(
    date: str = Query(..., description="Date in YYYY-MM-DD format"),
    db: Session = Depends(get_db),
    current_user_data: dict = Depends(get_current_user),
):
    """
    Get available discovery session slots for a specific date.
    Returns slots that are not yet booked.
    """
    try:
        # Parse date
        target_date = datetime.strptime(date, "%Y-%m-%d").date()
        target_datetime_start = datetime.combine(target_date, datetime.min.time())
        target_datetime_end = datetime.combine(target_date, datetime.max.time())
    except ValueError:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Invalid date format. Use YYYY-MM-DD",
        )
    
    # Get all booked sessions for this date
    booked_sessions = db.query(DiscoverySession).filter(
        DiscoverySession.scheduled_date >= target_datetime_start,
        DiscoverySession.scheduled_date <= target_datetime_end,
        DiscoverySession.status == DiscoverySessionStatus.SCHEDULED
    ).all()
    
    # Extract booked time slots
    booked_slots = [session.scheduled_time for session in booked_sessions]
    
    # Available slots are those not in booked_slots
    available_slots = [slot for slot in STANDARD_SLOTS if slot not in booked_slots]
    
    return SuccessResponse(
        data={
            "date": date,
            "available_slots": available_slots,
            "booked_slots": booked_slots,
        },
        message="Available slots retrieved successfully",
    )


@router.post("/discovery/book", response_model=SuccessResponse, status_code=status.HTTP_201_CREATED)
async def book_discovery_session(
    booking_data: DiscoverySessionCreate,
    db: Session = Depends(get_db),
    current_user_data: dict = Depends(get_current_user),
):
    """
    Book a discovery session for the current partner.
    Validates slot availability and prevents double-booking.
    """
    user_data = current_user_data
    if user_data.get("type") != "user":
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Authentication required",
        )
    
    user = user_data.get("user")
    
    # Determine partner type and get partner record
    partner_type = None
    corporate_partner_id = None
    educational_partner_id = None
    
    if user.user_type == UserType.CORPORATE_PARTNER:
        partner_type = PartnerType.CORPORATE
        partner = db.query(CorporatePartner).filter(CorporatePartner.user_id == user.id).first()
        if not partner:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="Corporate partner profile not found",
            )
        corporate_partner_id = partner.id
    elif user.user_type == UserType.EDUCATIONAL_PARTNER:
        partner_type = PartnerType.EDUCATIONAL
        partner = db.query(EducationalPartner).filter(EducationalPartner.user_id == user.id).first()
        if not partner:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="Educational partner profile not found",
            )
        educational_partner_id = partner.id
    else:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Only partners can book discovery sessions",
        )
    
    # Check if partner already has a scheduled session
    existing_session = db.query(DiscoverySession).filter(
        DiscoverySession.user_id == user.id,
        DiscoverySession.status == DiscoverySessionStatus.SCHEDULED
    ).first()
    
    if existing_session:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="You already have a scheduled discovery session. Please cancel it first to book a new one.",
        )
    
    # Validate slot is available
    session_date = booking_data.scheduled_date.date()
    target_datetime_start = datetime.combine(session_date, datetime.min.time())
    target_datetime_end = datetime.combine(session_date, datetime.max.time())
    
    # Check if slot is already booked
    conflicting_session = db.query(DiscoverySession).filter(
        DiscoverySession.scheduled_date >= target_datetime_start,
        DiscoverySession.scheduled_date <= target_datetime_end,
        DiscoverySession.scheduled_time == booking_data.scheduled_time,
        DiscoverySession.status == DiscoverySessionStatus.SCHEDULED
    ).first()
    
    if conflicting_session:
        raise HTTPException(
            status_code=status.HTTP_409_CONFLICT,
            detail=f"Time slot {booking_data.scheduled_time} is already booked. Please select another slot.",
        )
    
    # Validate time slot is in standard slots
    if booking_data.scheduled_time not in STANDARD_SLOTS:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"Invalid time slot. Available slots: {', '.join(STANDARD_SLOTS)}",
        )
    
    # Create discovery session
    discovery_session = DiscoverySession(
        partner_type=partner_type,
        corporate_partner_id=corporate_partner_id,
        educational_partner_id=educational_partner_id,
        user_id=user.id,
        scheduled_date=booking_data.scheduled_date,
        scheduled_time=booking_data.scheduled_time,
        status=DiscoverySessionStatus.SCHEDULED,
        notes=booking_data.notes,
        extra_data={"verification_status": "pending"},
    )
    
    db.add(discovery_session)
    db.commit()
    db.refresh(discovery_session)

    # Optional: Create Google Calendar + Meet invite (best-effort; doesn't block booking)
    try:
        partner_name = ""
        partner_email = user.email
        if partner_type == PartnerType.CORPORATE and corporate_partner_id:
            cp = db.query(CorporatePartner).filter(CorporatePartner.id == corporate_partner_id).first()
            partner_name = (cp.company_name if cp else "") or "Corporate Partner"
        elif partner_type == PartnerType.EDUCATIONAL and educational_partner_id:
            ep = db.query(EducationalPartner).filter(EducationalPartner.id == educational_partner_id).first()
            partner_name = (ep.institution_name if ep else "") or "Educational Partner"

        event_id, html_link, meet_link = GoogleCalendarService.create_discovery_event(
            partner_name=partner_name,
            partner_email=partner_email,
            scheduled_start=booking_data.scheduled_date,
        )
        if event_id or html_link or meet_link:
            discovery_session.google_calendar_event_id = event_id
            discovery_session.google_calendar_link = html_link
            # Store meet link in extra_data to avoid schema migration
            extra = discovery_session.extra_data or {}
            extra["google_meet_link"] = meet_link
            discovery_session.extra_data = extra
            db.add(discovery_session)
            db.commit()
            db.refresh(discovery_session)
    except Exception:
        # Logging is handled inside the service; keep booking successful even if google fails.
        pass

    return SuccessResponse(
        data=DiscoverySessionResponse.model_validate(discovery_session).model_dump(),
        message="Discovery session booked successfully",
    )


@router.get("/discovery/my-session", response_model=SuccessResponse)
async def get_my_discovery_session(
    db: Session = Depends(get_db),
    current_user_data: dict = Depends(get_current_user),
):
    """Get the current partner's discovery session booking."""
    user_data = current_user_data
    if user_data.get("type") != "user":
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Authentication required",
        )
    
    user = user_data.get("user")
    
    # Get most recent scheduled session
    session = db.query(DiscoverySession).filter(
        DiscoverySession.user_id == user.id,
        DiscoverySession.status == DiscoverySessionStatus.SCHEDULED
    ).order_by(DiscoverySession.scheduled_date.desc()).first()
    
    if not session:
        return SuccessResponse(
            data=None,
            message="No scheduled discovery session found",
        )
    
    return SuccessResponse(
        data=DiscoverySessionResponse.model_validate(session).model_dump(),
        message="Discovery session retrieved successfully",
    )


@router.get("/discovery/sessions", response_model=SuccessResponse)
async def admin_list_discovery_sessions(
    status_filter: Optional[str] = Query(None, description="scheduled|completed|cancelled|no_show"),
    date_from: Optional[str] = Query(None, description="YYYY-MM-DD"),
    date_to: Optional[str] = Query(None, description="YYYY-MM-DD"),
    db: Session = Depends(get_db),
    current_user_data: dict = Depends(get_current_user),
):
    """Admin: list discovery sessions for verification/approval."""
    user_data = current_user_data
    if user_data.get("type") != "admin":
        raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="Admin access required")

    q = db.query(DiscoverySession)

    if status_filter:
        try:
            q = q.filter(DiscoverySession.status == DiscoverySessionStatus(status_filter))
        except Exception:
            raise HTTPException(status_code=400, detail="Invalid status filter")

    def _parse_date(s: str):
        return datetime.strptime(s, "%Y-%m-%d").date()

    if date_from:
        try:
            df = _parse_date(date_from)
            q = q.filter(DiscoverySession.scheduled_date >= datetime.combine(df, datetime.min.time()))
        except Exception:
            raise HTTPException(status_code=400, detail="Invalid date_from. Use YYYY-MM-DD")

    if date_to:
        try:
            dt = _parse_date(date_to)
            q = q.filter(DiscoverySession.scheduled_date <= datetime.combine(dt, datetime.max.time()))
        except Exception:
            raise HTTPException(status_code=400, detail="Invalid date_to. Use YYYY-MM-DD")

    sessions = q.order_by(DiscoverySession.scheduled_date.desc()).limit(500).all()

    # Enrich with partner identity + meet link
    data = []
    for s in sessions:
        partner_name = None
        if s.partner_type == PartnerType.CORPORATE and s.corporate_partner_id:
            cp = db.query(CorporatePartner).filter(CorporatePartner.id == s.corporate_partner_id).first()
            partner_name = cp.company_name if cp else None
        if s.partner_type == PartnerType.EDUCATIONAL and s.educational_partner_id:
            ep = db.query(EducationalPartner).filter(EducationalPartner.id == s.educational_partner_id).first()
            partner_name = ep.institution_name if ep else None

        user = db.query(User).filter(User.id == s.user_id).first()
        meet_link = (s.extra_data or {}).get("google_meet_link")
        verification_status = (s.extra_data or {}).get("verification_status", "pending")

        data.append(
            {
                **DiscoverySessionResponse.model_validate(s).model_dump(),
                "partner_name": partner_name,
                "partner_email": user.email if user else None,
                "google_meet_link": meet_link,
                "verification_status": verification_status,
            }
        )

    return SuccessResponse(data=data, message="Discovery sessions retrieved successfully")


class DiscoverySessionAdminUpdate(BaseModel):
    verification_status: Optional[Literal["pending", "approved", "rejected"]] = None
    status: Optional[Literal["scheduled", "completed", "cancelled", "no_show"]] = None
    notes: Optional[str] = None


@router.patch("/discovery/sessions/{session_id}", response_model=SuccessResponse)
async def admin_update_discovery_session(
    session_id: UUID,
    payload: DiscoverySessionAdminUpdate,
    db: Session = Depends(get_db),
    current_user_data: dict = Depends(get_current_user),
):
    """Admin: approve/reject/mark status for a discovery session."""
    user_data = current_user_data
    if user_data.get("type") != "admin":
        raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="Admin access required")

    s = db.query(DiscoverySession).filter(DiscoverySession.id == session_id).first()
    if not s:
        raise HTTPException(status_code=404, detail="Discovery session not found")

    if payload.status:
        s.status = DiscoverySessionStatus(payload.status)

    if payload.notes is not None:
        s.notes = payload.notes

    if payload.verification_status:
        # Assign a new dict so SQLAlchemy detects the JSON column change (in-place mutation may not persist)
        extra = dict(s.extra_data or {})
        extra["verification_status"] = payload.verification_status
        extra["verified_at"] = datetime.utcnow().isoformat()
        s.extra_data = extra

    db.add(s)
    db.commit()
    db.refresh(s)

    meet_link = (s.extra_data or {}).get("google_meet_link")
    verification_status = (s.extra_data or {}).get("verification_status", "pending")
    return SuccessResponse(
        data={**DiscoverySessionResponse.model_validate(s).model_dump(), "google_meet_link": meet_link, "verification_status": verification_status},
        message="Discovery session updated successfully",
    )


@router.post("/discovery/sessions/{session_id}/sync-google", response_model=SuccessResponse)
async def admin_sync_discovery_session_google(
    session_id: UUID,
    db: Session = Depends(get_db),
    current_user_data: dict = Depends(get_current_user),
):
    """Admin: create (or re-create) Google Calendar + Meet invite for a session."""
    user_data = current_user_data
    if user_data.get("type") != "admin":
        raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="Admin access required")

    s = db.query(DiscoverySession).filter(DiscoverySession.id == session_id).first()
    if not s:
        raise HTTPException(status_code=404, detail="Discovery session not found")

    user = db.query(User).filter(User.id == s.user_id).first()
    partner_name = "Partner"
    if s.partner_type == PartnerType.CORPORATE and s.corporate_partner_id:
        cp = db.query(CorporatePartner).filter(CorporatePartner.id == s.corporate_partner_id).first()
        partner_name = (cp.company_name if cp else None) or "Corporate Partner"
    if s.partner_type == PartnerType.EDUCATIONAL and s.educational_partner_id:
        ep = db.query(EducationalPartner).filter(EducationalPartner.id == s.educational_partner_id).first()
        partner_name = (ep.institution_name if ep else None) or "Educational Partner"

    event_id, html_link, meet_link = GoogleCalendarService.create_discovery_event(
        partner_name=partner_name,
        partner_email=(user.email if user else ""),
        scheduled_start=s.scheduled_date,
    )

    if not (event_id or html_link or meet_link):
        raise HTTPException(status_code=500, detail="Failed to create Google Calendar/Meet invite (check google settings)")

    s.google_calendar_event_id = event_id
    s.google_calendar_link = html_link
    extra = s.extra_data or {}
    extra["google_meet_link"] = meet_link
    s.extra_data = extra
    db.add(s)
    db.commit()
    db.refresh(s)

    verification_status = (s.extra_data or {}).get("verification_status", "pending")
    return SuccessResponse(
        data={**DiscoverySessionResponse.model_validate(s).model_dump(), "google_meet_link": meet_link, "verification_status": verification_status},
        message="Google Calendar/Meet invite synced successfully",
    )