import os
import csv
import io
from datetime import date, datetime
from contextlib import asynccontextmanager
from fastapi import FastAPI, Depends, HTTPException, Response, Request, status
from fastapi.responses import HTMLResponse, FileResponse
from typing import List, Optional
import secrets
import smtplib

from lic_webapp.database import init_db
from lic_webapp import auth, crud, schemas, report_email

MAX_IMPORT_BYTES = 5 * 1024 * 1024
MAX_IMPORT_ROWS = 1_000
IMPORT_FIELD_ALIASES = {
    "first name": "first_name", "firstname": "first_name", "first_name": "first_name",
    "last name": "last_name", "lastname": "last_name", "last_name": "last_name",
    "email": "email", "email address": "email",
    "phone": "phone", "phone number": "phone",
    "iba date": "iba_date", "iba submit date": "iba_date", "iba_date": "iba_date",
    "iba status": "iba_status", "iba_status": "iba_status",
    "solution number": "solution_number", "solution_number": "solution_number",
    "rvp": "rvp",
}

def _normalize_import_header(value: object) -> str:
    return " ".join(str(value or "").strip().lower().replace("_", " ").split())

def _import_value(value: object) -> str | None:
    if value is None:
        return None
    if isinstance(value, (date, datetime)):
        return value.date().isoformat() if isinstance(value, datetime) else value.isoformat()
    value = str(value).strip()
    return value or None

def _read_import_rows(filename: str, contents: bytes) -> list[tuple[int, dict[str, object]]]:
    """Read the first sheet/file into numbered records without persisting it."""
    extension = os.path.splitext(filename.lower())[1]
    if extension == ".csv":
        try:
            rows = list(csv.reader(io.StringIO(contents.decode("utf-8-sig"))))
        except (UnicodeDecodeError, csv.Error) as exc:
            raise ValueError("The CSV file could not be read. Save it as UTF-8 CSV and try again.") from exc
    elif extension == ".xlsx":
        try:
            from openpyxl import load_workbook
            workbook = load_workbook(io.BytesIO(contents), read_only=True, data_only=True)
            rows = list(workbook.active.iter_rows(values_only=True))
            workbook.close()
        except Exception as exc:
            raise ValueError("The Excel file could not be read. Upload a valid .xlsx file.") from exc
    else:
        raise ValueError("Upload a .csv or .xlsx file.")

    if not rows:
        raise ValueError("The import file is empty.")
    headers = [IMPORT_FIELD_ALIASES.get(_normalize_import_header(value)) for value in rows[0]]
    if "first_name" not in headers or "last_name" not in headers:
        raise ValueError("The header row must include First Name and Last Name.")
    if len(rows) - 1 > MAX_IMPORT_ROWS:
        raise ValueError(f"Import files may contain at most {MAX_IMPORT_ROWS} recruits.")

    records = []
    for row_number, row in enumerate(rows[1:], start=2):
        record = {
            header: value for index, header in enumerate(headers) if header
            for value in [_import_value(row[index]) if index < len(row) else None]
            if value is not None
        }
        if any(value is not None for value in record.values()):
            records.append((row_number, record))
    if not records:
        raise ValueError("The import file has no recruit rows.")
    return records

@asynccontextmanager
async def lifespan(app: FastAPI):
    # Initialize the SQLite database on startup
    init_db()
    yield
    # Clean up on shutdown if needed
    auth.clean_expired_sessions()

app = FastAPI(
    title="Primerica Recruit Tracker API",
    version="1.0.0",
    lifespan=lifespan
)

# SECURITY HEADERS MIDDLEWARE
@app.middleware("http")
async def add_security_headers(request: Request, call_next):
    response = await call_next(request)
    # Content Security Policy (CSP)
    response.headers["Content-Security-Policy"] = (
        "default-src 'self'; "
        "style-src 'self' 'unsafe-inline' https://fonts.googleapis.com; "
        "font-src 'self' https://fonts.gstatic.com; "
        "img-src 'self' data:; "
        "frame-ancestors 'none';"
    )
    # Anti-clickjacking
    response.headers["X-Frame-Options"] = "DENY"
    # Anti-MIME-sniffing
    response.headers["X-Content-Type-Options"] = "nosniff"
    # Referrer policy
    response.headers["Referrer-Policy"] = "strict-origin-when-cross-origin"
    if request.url.path.startswith("/api/"):
        response.headers["Cache-Control"] = "no-store"
    return response

# AUTH DEPENDENCY
def get_current_user(request: Request) -> dict:
    """
    FastAPI dependency that extracts the session ID from the HttpOnly cookie
    and validates it. Returns the session dict.
    """
    session_id = request.cookies.get("__Host-session")
    if not session_id:
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Authentication credentials were not provided."
        )
    
    user_session = auth.validate_session(session_id)
    if not user_session:
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Session has expired or is invalid."
        )
    return user_session

# CSRF VERIFICATION DEPENDENCY
def verify_csrf(request: Request, current_user: dict = Depends(get_current_user)) -> None:
    """
    FastAPI dependency that validates the CSRF token in the X-CSRF-Token header
    against the token stored in the user's database session.
    """
    csrf_header = request.headers.get("X-CSRF-Token")
    if not csrf_header:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="CSRF token missing in request headers."
        )
    
    # Constant-time comparison
    if not secrets.compare_digest(csrf_header, current_user["csrf_token"]):
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="CSRF token validation failed."
        )

def require_admin(current_user: dict = Depends(get_current_user)) -> dict:
    if current_user["role"] != "admin":
        raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="Administrator access is required.")
    return current_user

def permitted_rvp(current_user: dict) -> Optional[str]:
    """Return the only RVP a lower-privileged user may access."""
    if current_user["role"] == "rvp":
        if not current_user.get("rvp"):
            raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="This RVP account has no RVP assignment.")
        return current_user["rvp"]
    return None

# STATIC PATHS
STATIC_DIR = os.path.join(os.path.dirname(__file__), "static")

# AUTHENTICATION ENDPOINTS
@app.post("/api/login")
def login(login_data: schemas.LoginRequest, response: Response):
    """
    Authenticate user and set cookie. Returns CSRF token and username.
    """
    from lic_webapp.database import get_db_connection
    conn = get_db_connection()
    try:
        row = conn.execute("SELECT id, password_hash, role, rvp, email FROM users WHERE username = ?;", (login_data.username,))
        user = row.fetchone()
    finally:
        conn.close()
        
    if not user or not auth.verify_password(login_data.password, user["password_hash"]):
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Invalid username or password."
        )
        
    session_id, csrf_token = auth.create_session(user["id"])
    
    # Set the cookie with high-security flags
    response.set_cookie(
        key="__Host-session",
        value=session_id,
        httponly=True,
        secure=True, # MUST be True for HTTPS/__Host- prefix
        samesite="strict",
        path="/",
        max_age=auth.SESSION_LIFETIME_HOURS * 3600
    )
    
    return {
        "username": login_data.username,
        "role": user["role"],
        "rvp": user["rvp"],
        "email": user["email"],
        "csrf_token": csrf_token
    }

@app.post("/api/logout")
def logout(request: Request, response: Response):
    """
    Log out the user, clean session, and clear cookie.
    """
    session_id = request.cookies.get("__Host-session")
    if session_id:
        auth.delete_session(session_id)
        
    response.delete_cookie(
        key="__Host-session",
        path="/",
        secure=True,
        httponly=True,
        samesite="strict"
    )
    return {"message": "Logged out successfully"}

@app.get("/api/me", response_model=schemas.UserResponse)
def check_session(current_user: dict = Depends(get_current_user)):
    """
    Returns user details if session is active.
    """
    return {
        "id": current_user["user_id"],
        "username": current_user["username"],
        "role": current_user["role"],
        "rvp": current_user.get("rvp"),
        "email": current_user.get("email"),
        "csrf_token": current_user["csrf_token"]
    }

@app.post("/api/users/rvp", response_model=schemas.RVPUserResponse, dependencies=[Depends(verify_csrf), Depends(require_admin)])
def register_rvp_user(user_data: schemas.RVPUserCreate, current_user: dict = Depends(get_current_user)):
    """Administrators create read-only accounts tied to a single RVP."""
    from lic_webapp.database import get_db_connection
    conn = get_db_connection()
    try:
        cursor = conn.execute(
            "INSERT INTO users (username, password_hash, role, rvp, email, phone) VALUES (?, ?, 'rvp', ?, ?, ?);",
            (user_data.username, auth.hash_password(user_data.password), user_data.rvp, user_data.email, user_data.phone),
        )
        conn.commit()
    except Exception as exc:
        conn.rollback()
        if "UNIQUE constraint failed: users.username" in str(exc):
            raise HTTPException(status_code=409, detail="That username is already in use.") from exc
        raise
    finally:
        conn.close()
    return {"id": cursor.lastrowid, "username": user_data.username, "role": "rvp", "rvp": user_data.rvp,
            "email": user_data.email, "phone": user_data.phone}

@app.get("/api/users/rvp", response_model=List[schemas.RVPUserResponse])
def list_rvp_users(current_user: dict = Depends(require_admin)):
    from lic_webapp.database import get_db_connection
    conn = get_db_connection()
    try:
        rows = conn.execute("SELECT id, username, role, rvp, email, phone FROM users WHERE role = 'rvp' ORDER BY rvp, username;").fetchall()
        return [dict(row) for row in rows]
    finally:
        conn.close()

# RECRUIT MANAGEMENT API ENDPOINTS
@app.get("/api/recruits", response_model=List[schemas.RecruitResponse])
def list_recruits(current_user: dict = Depends(get_current_user)):
    return crud.get_recruits(permitted_rvp(current_user))

@app.get("/api/recruits/{recruit_id}", response_model=schemas.RecruitResponse)
def get_recruit_details(recruit_id: int, current_user: dict = Depends(get_current_user)):
    recruit = crud.get_recruit(recruit_id, permitted_rvp(current_user))
    if not recruit:
        raise HTTPException(status_code=404, detail="Recruit not found")
    return recruit

@app.post("/api/recruits", response_model=schemas.RecruitResponse, dependencies=[Depends(verify_csrf), Depends(require_admin)])
def add_recruit(recruit_data: schemas.RecruitCreate, current_user: dict = Depends(get_current_user)):
    return crud.create_recruit(recruit_data)

@app.post("/api/recruits/import", response_model=schemas.RecruitImportResponse, dependencies=[Depends(verify_csrf), Depends(require_admin)])
async def import_recruits(request: Request, current_user: dict = Depends(get_current_user)):
    """Import new recruits from a UTF-8 CSV or an Excel .xlsx workbook."""
    filename = request.headers.get("X-Import-Filename", "")
    contents = await request.body()
    if not filename:
        raise HTTPException(status_code=400, detail="Choose a file to import.")
    if not contents:
        raise HTTPException(status_code=400, detail="The selected file is empty.")
    if len(contents) > MAX_IMPORT_BYTES:
        raise HTTPException(status_code=413, detail="Import files must be 5 MB or smaller.")
    try:
        rows = _read_import_rows(filename, contents)
    except ValueError as exc:
        raise HTTPException(status_code=422, detail=str(exc)) from exc

    recruits = []
    errors = []
    for row_number, record in rows:
        try:
            recruits.append((row_number, schemas.RecruitCreate(**record)))
        except Exception as exc:
            messages = getattr(exc, "errors", lambda: [])()
            detail = "; ".join(item.get("msg", "Invalid value") for item in messages) or str(exc)
            errors.append(f"Row {row_number}: {detail}")

    first_row_by_solution = {}
    candidates = []
    skipped = []
    for row_number, recruit in recruits:
        if not recruit.solution_number:
            candidates.append((row_number, recruit))
            continue
        solution_key = recruit.solution_number.casefold()
        if solution_key in first_row_by_solution:
            skipped.append(
                f"Row {row_number}: Solution Number {recruit.solution_number} duplicates row "
                f"{first_row_by_solution[solution_key]}."
            )
        else:
            first_row_by_solution[solution_key] = row_number
            candidates.append((row_number, recruit))

    existing_solution_numbers = crud.get_existing_solution_numbers(
        list(first_row_by_solution)
    )
    importable_recruits = []
    for row_number, recruit in candidates:
        if recruit.solution_number and recruit.solution_number.casefold() in existing_solution_numbers:
            skipped.append(f"Row {row_number}: Solution Number is already assigned to an existing recruit.")
        else:
            importable_recruits.append(recruit)
    if errors:
        raise HTTPException(status_code=422, detail=" ".join(errors[:10]))
    return {"imported": crud.create_recruits(importable_recruits), "skipped": skipped}

@app.put("/api/recruits/{recruit_id}", response_model=schemas.RecruitResponse, dependencies=[Depends(verify_csrf), Depends(require_admin)])
def update_recruit_details(recruit_id: int, recruit_data: schemas.RecruitUpdate, current_user: dict = Depends(get_current_user)):
    try:
        updated = crud.update_recruit(recruit_id, recruit_data)
    except ValueError as exc:
        raise HTTPException(status_code=409, detail=str(exc)) from exc
    if not updated:
        raise HTTPException(status_code=404, detail="Recruit not found")
    return updated

@app.post("/api/recruits/{recruit_id}/exams", response_model=schemas.RecruitResponse, dependencies=[Depends(verify_csrf), Depends(require_admin)])
def log_exam_attempt(recruit_id: int, attempt_data: schemas.ExamAttemptCreate, current_user: dict = Depends(get_current_user)):
    updated = crud.add_exam_attempt(recruit_id, attempt_data)
    if not updated:
        raise HTTPException(status_code=404, detail="Recruit not found")
    return updated

@app.delete("/api/recruits/{recruit_id}", dependencies=[Depends(verify_csrf), Depends(require_admin)])
def remove_recruit(recruit_id: int, current_user: dict = Depends(get_current_user)):
    success = crud.delete_recruit(recruit_id)
    if not success:
        raise HTTPException(status_code=404, detail="Recruit not found")
    return {"message": "Recruit deleted successfully"}

# REPORT ENDPOINTS
@app.get("/api/reports/next-class")
def report_next_class(current_user: dict = Depends(get_current_user)):
    """
    Returns all recruits registered for the LLQP class who have not yet
    completed all attendance days — i.e. the next class roster.
    """
    return crud.get_next_class_report(permitted_rvp(current_user))

@app.get("/api/reports/weekly-status")
def report_weekly_status(current_user: dict = Depends(get_current_user)):
    """
    Returns all recruits with their current pipeline status,
    sorted by RVP then by last name. Used for the weekly status report.
    """
    return crud.get_weekly_status_report(permitted_rvp(current_user))

def _weekly_email_for_user(user_id: int) -> tuple[str, str, str, str]:
    """Build from current database state; the caller cannot choose a recipient."""
    from lic_webapp.database import get_db_connection
    conn = get_db_connection()
    try:
        user = conn.execute(
            "SELECT rvp, email FROM users WHERE id = ? AND role = 'rvp';", (user_id,)
        ).fetchone()
    finally:
        conn.close()
    if not user:
        raise HTTPException(status_code=404, detail="RVP user not found.")
    if not user['email']:
        raise HTTPException(status_code=422, detail="Add an email address to this RVP user before sending.")
    subject, plain, html = report_email.render_weekly_email(
        user['rvp'], crud.get_weekly_status_report(user['rvp'])
    )
    return user['email'], subject, plain, html

@app.get("/api/reports/weekly-email/{user_id}")
def preview_weekly_email(user_id: int, current_user: dict = Depends(require_admin)):
    recipient, subject, _plain, html = _weekly_email_for_user(user_id)
    return {"recipient": recipient, "subject": subject, "html": html}

@app.post("/api/reports/weekly-email/{user_id}", dependencies=[Depends(verify_csrf)])
def send_weekly_email(user_id: int, current_user: dict = Depends(require_admin)):
    recipient, subject, plain, html = _weekly_email_for_user(user_id)
    try:
        report_email.send_weekly_email(recipient, subject, plain, html)
    except RuntimeError as exc:
        raise HTTPException(status_code=503, detail=str(exc)) from exc
    except (OSError, smtplib.SMTPException) as exc:
        raise HTTPException(status_code=502, detail="Email delivery failed. Check SMTP settings and try again.") from exc
    return {"message": "Report sent.", "recipient": recipient}

# STATIC FILES SERVING (SPA ROUTES)
@app.get("/")
def serve_index():
    return FileResponse(os.path.join(STATIC_DIR, "index.html"), headers={"Cache-Control": "no-cache"})

@app.get("/app.css")
def serve_css():
    return FileResponse(os.path.join(STATIC_DIR, "app.css"), headers={"Cache-Control": "no-cache"})

@app.get("/app.js")
def serve_js():
    return FileResponse(os.path.join(STATIC_DIR, "app.js"), headers={"Cache-Control": "no-cache"})