"""
Assessment Controller — Read operations for Ghost Shopper assessments.

Functions:
    get_assessments_by_visit  — All assessment items for a visit (grouped by category)
"""

import uuid
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession
from fastapi import UploadFile


CATEGORY_LABELS = {
    "A": "Pelayanan Awal (Frontliner/Kasir)",
    "B": "Kualitas Produk (Minuman & Makanan)",
    "C": "Kebersihan dan Suasana Outlet",
    "D": "Kepatuhan SOP & Keselamatan",
    "E": "Keseluruhan Pengalaman Pelanggan",
}


async def get_assessments_by_visit(
    db: AsyncSession, visit_id: uuid.UUID
) -> dict | None:
    """
    Get all assessment items for a visit, grouped by category A–E.
    Returns None if visit not found.
    """
    # Check visit exists
    result = await db.execute(
        text("SELECT id FROM visits WHERE id = :vid"), {"vid": visit_id}
    )
    if not result.fetchone():
        return None

    # Get all assessments
    assess_result = await db.execute(text("""
        SELECT id, category, item_no, criteria, score, raw_value, notes, description
        FROM assessments
        WHERE visit_id = :vid
        ORDER BY category, item_no
    """), {"vid": visit_id})

    grouped = {}
    total_score = 0.0
    total_count = 0

    for row in assess_result.fetchall():
        cat = row[1]
        if cat not in grouped:
            grouped[cat] = {
                "category": cat,
                "label": CATEGORY_LABELS.get(cat, cat),
                "items": [],
                "avg_score": 0.0,
            }
        grouped[cat]["items"].append({
            "id": str(row[0]),
            "item_no": row[2],
            "criteria": row[3],
            "score": float(row[4]),
            "raw_value": row[5],
            "notes": row[6],
            "description": row[7],
        })
        total_score += float(row[4])
        total_count += 1

    # Calculate per-category averages
    for cat_data in grouped.values():
        items = cat_data["items"]
        if items:
            cat_data["avg_score"] = round(
                sum(i["score"] for i in items) / len(items), 2
            )

    return {
        "status": "success",
        "data": {
            "visit_id": str(visit_id),
            "categories": grouped,
            "overall_avg": round(total_score / total_count, 2) if total_count > 0 else 0.0,
            "total_items": total_count,
        },
    }

async def update_assessment(
    db: AsyncSession, assessment_id: uuid.UUID, update_data: dict
) -> dict | None:
    """Update an assessment item (score, raw_value, notes, description)."""
    # Check if exists
    result = await db.execute(
        text("SELECT id FROM assessments WHERE id = :id"), {"id": assessment_id}
    )
    if not result.fetchone():
        return None

    # Filter allowed update fields
    allowed = {"score", "raw_value", "notes", "description"}
    updates = {k: v for k, v in update_data.items() if k in allowed}
    
    if not updates:
        return {"status": "success", "message": "No valid fields to update"}

    set_clauses = []
    params = {"id": assessment_id}
    for k, v in updates.items():
        set_clauses.append(f"{k} = :{k}")
        params[k] = v

    query = text(f"UPDATE assessments SET {', '.join(set_clauses)} WHERE id = :id")
    await db.execute(query, params)
    await db.commit()

    return {"status": "success", "message": "Assessment updated successfully"}

async def upload_assessment_photo(
    db: AsyncSession, assessment_id: uuid.UUID, file: UploadFile
) -> dict | None:
    """Mock upload photo to assessment."""
    import os
    from datetime import datetime
    
    # Check if assessment exists
    result = await db.execute(text("SELECT visit_id FROM assessments WHERE id = :id"), {"id": assessment_id})
    row = result.fetchone()
    if not row:
        return None
    visit_id = row[0]

    # Save file
    upload_dir = "uploads/assessments"
    os.makedirs(upload_dir, exist_ok=True)
    file_ext = os.path.splitext(file.filename)[1] if file.filename else ".jpg"
    safe_name = f"assessment_{assessment_id}_{uuid.uuid4().hex[:8]}{file_ext}"
    file_path = os.path.join(upload_dir, safe_name)
    
    content = await file.read()
    with open(file_path, "wb") as f:
        f.write(content)

    # Insert to DB
    photo_id = uuid.uuid4()
    await db.execute(
        text("""
            INSERT INTO photos (id, visit_id, assessment_id, file_path, file_name, file_size, mime_type, uploaded_at)
            VALUES (:id, :vid, :aid, :path, :name, :size, :mime, :now)
        """),
        {
            "id": photo_id,
            "vid": visit_id,
            "aid": assessment_id,
            "path": file_path,
            "name": safe_name,
            "size": len(content),
            "mime": file.content_type or "image/jpeg",
            "now": datetime.utcnow()
        }
    )
    await db.commit()

    return {"status": "success", "data": {"id": str(photo_id), "file_path": file_path}}

async def get_assessment_photos(
    db: AsyncSession, assessment_id: uuid.UUID
) -> list[dict] | None:
    """Get all photos for an assessment item."""
    result = await db.execute(
        text("SELECT id FROM assessments WHERE id = :id"), {"id": assessment_id}
    )
    if not result.fetchone():
        return None

    photos_result = await db.execute(
        text("SELECT id, file_path, file_name FROM photos WHERE assessment_id = :id"),
        {"id": assessment_id}
    )
    return [
        {"id": str(row[0]), "file_path": row[1], "file_name": row[2]}
        for row in photos_result
    ]