app_3.py

import csv
import sqlite3
import hashlib
import subprocess
import time
import string
import math
from pathlib import Path
from typing import Optional, List, Iterator, Tuple, Dict

from fastapi.responses import RedirectResponse
from starlette.middleware.sessions import SessionMiddleware
from fastapi import FastAPI, HTTPException, Query, Request
from fastapi.responses import HTMLResponse, StreamingResponse, FileResponse, JSONResponse
from fastapi.middleware.cors import CORSMiddleware

APP_DIR = Path(__file__).parent.resolve()
DB_PATH = APP_DIR / "annotations.db"
MAPPING_CSV = APP_DIR / "mapping.csv"

# ✅ Reference folder (source of truth for all 500 words)
REFERENCE_DIR = Path(r"/home/antpc/Desktop/5000_RTH_Videos (Copy)").resolve()

# SAFE caches (originals untouched)
PREVIEW_DIR = APP_DIR / "previews"
FRAMES_DIR = APP_DIR / "frames_cache"
PREVIEW_DIR.mkdir(exist_ok=True)
FRAMES_DIR.mkdir(exist_ok=True)

# IMPORTANT: restrict file serving to these base folders
ALLOWED_BASE_DIRS = [
    REFERENCE_DIR,
    Path(r"/mnt/9a528fe4-4fe8-4dff-9a0c-8b1a3cf3d7ba").resolve(),
]

COLLECTED_BASE = Path(r"/mnt/9a528fe4-4fe8-4dff-9a0c-8b1a3cf3d7ba").resolve()

VIDEO_EXTS = {".mp4", ".mpg", ".mov", ".mkv", ".avi", ".webm"}

CHUNK_SIZE = 1024 * 1024  # 1MB

# Frames settings (moderate quality for viewing)
FRAMES_FPS = 12
FRAMES_MAX = 240
FRAMES_HEIGHT = 360
JPG_Q = 5

# ✅ in-memory reference map: word -> reference video path
REFERENCE_MAP: Dict[str, str] = {}
REFERENCE_WORDS: List[str] = []


app = FastAPI(title="ISL Annotation Tool (User-wise, Option A: all reference words)")


app.add_middleware(
    SessionMiddleware,
    secret_key="isl_annotation_secret_987654",

    # ✅ Explicit cookie name
    session_cookie="isl_session",

    # ✅ Works well for normal browser navigation
    same_site="lax",

    # ✅ Set True only if you use HTTPS
    https_only=False,
)

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=False,
    allow_methods=["*"],
    allow_headers=["*"],
)

# ---------------- DB ----------------

def db() -> sqlite3.Connection:
    conn = sqlite3.connect(
        DB_PATH,
        timeout=30,
        check_same_thread=False
    )
    conn.row_factory = sqlite3.Row

    # ✅ Enable WAL to prevent write locks
    conn.execute("PRAGMA journal_mode=WAL;")
    conn.execute("PRAGMA synchronous=NORMAL;")

    return conn



def init_db():
    conn = db()
    cur = conn.cursor()

    # ✅ videos table
    cur.execute("""
        CREATE TABLE IF NOT EXISTS videos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            word TEXT NOT NULL,
            reference_path TEXT NOT NULL,
            collected_path TEXT NOT NULL,
            user_id TEXT NOT NULL
        )
    """)

    # ✅ annotations table (double annotation)
    cur.execute("""
        CREATE TABLE IF NOT EXISTS annotations (
            video_id INTEGER,
            annotator TEXT NOT NULL,
            label TEXT NOT NULL CHECK(label IN ('correct','comment')),
            note TEXT,
            updated_at TEXT DEFAULT (datetime('now')),
            PRIMARY KEY (video_id, annotator)
        )
    """)

    # indexes
    cur.execute("CREATE INDEX IF NOT EXISTS idx_videos_word_user ON videos(word, user_id)")
    cur.execute("CREATE INDEX IF NOT EXISTS idx_videos_user ON videos(user_id)")

    conn.commit()
    conn.close()

def load_mapping_if_needed():
    if not MAPPING_CSV.exists():
        raise RuntimeError(f"mapping.csv not found at {MAPPING_CSV}")

    conn = db()
    cur = conn.cursor()
    cur.execute("SELECT COUNT(*) AS c FROM videos")
    count = cur.fetchone()["c"]

    if count > 0:
        conn.close()
        return

    with MAPPING_CSV.open("r", encoding="utf-8") as f:
        reader = csv.DictReader(f)
        rows = [(r["word"], r["reference_path"], r["collected_path"], r["user_id"]) for r in reader]

    cur.executemany(
        "INSERT INTO videos(word, reference_path, collected_path, user_id) VALUES (?,?,?,?)",
        rows
    )
    conn.commit()
    conn.close()

# ---------------- PATH SAFETY ----------------

def is_allowed_path(p: Path) -> bool:
    try:
        rp = p.resolve()
    except Exception:
        return False
    for base in ALLOWED_BASE_DIRS:
        if str(rp).startswith(str(base)):
            return True
    return False

def is_collected_path(p: Path) -> bool:
    try:
        rp = p.resolve()
    except Exception:
        return False
    return str(rp).startswith(str(COLLECTED_BASE))

# ---------------- OPTION A (REFERENCE WORDS FROM FOLDER) ----------------

def scan_reference_folder() -> None:
    """
    Build REFERENCE_MAP from filenames in REFERENCE_DIR.
    Word is filename without extension, lowercased.
    """
    global REFERENCE_MAP, REFERENCE_WORDS

    if not REFERENCE_DIR.exists():
        raise RuntimeError(f"REFERENCE_DIR not found: {REFERENCE_DIR}")

    ref_map: Dict[str, str] = {}
    for p in REFERENCE_DIR.iterdir():
        if p.is_file() and p.suffix.lower() in VIDEO_EXTS:
            word = p.stem.strip().lower()
            if word:
                ref_map[word] = str(p.resolve())

    if not ref_map:
        raise RuntimeError(f"No reference videos found in: {REFERENCE_DIR}")

    REFERENCE_MAP = ref_map
    REFERENCE_WORDS = sorted(ref_map.keys())

def get_reference_words() -> List[str]:
    return REFERENCE_WORDS

def get_reference_path(word: str) -> str:
    w = word.strip().lower()
    if w not in REFERENCE_MAP:
        raise HTTPException(status_code=404, detail=f"Reference video not found for word: {word}")
    return REFERENCE_MAP[w]

# ---------------- PREVIEW (SAFE, browser-compatible) ----------------

def ffprobe_video_codec(p: Path) -> Optional[str]:
    try:
        out = subprocess.check_output(
            [
                "ffprobe", "-v", "error",
                "-select_streams", "v:0",
                "-show_entries", "stream=codec_name",
                "-of", "default=nw=1",
                str(p)
            ],
            text=True
        )
        for line in out.splitlines():
            if line.startswith("codec_name="):
                return line.split("=", 1)[1].strip()
    except Exception:
        return None
    return None

def preview_path_for(original: Path) -> Path:
    st = original.stat()
    key = f"{original.resolve()}|{st.st_mtime_ns}|{st.st_size}"
    h = hashlib.md5(key.encode("utf-8")).hexdigest()
    return PREVIEW_DIR / f"{h}.mp4"

def run_ffmpeg(cmd: List[str]) -> None:
    p = subprocess.run(cmd, text=True, capture_output=True)
    if p.returncode != 0:
        log_file = APP_DIR / "ffmpeg_errors.log"
        with log_file.open("a", encoding="utf-8") as f:
            f.write("\n\n=== FFMPEG FAILED ===\n")
            f.write("CMD: " + " ".join(cmd) + "\n")
            f.write("STDERR:\n" + (p.stderr or "") + "\n")
            f.write("STDOUT:\n" + (p.stdout or "") + "\n")
        tail = "\n".join((p.stderr or "").splitlines()[-25:])
        raise RuntimeError(tail if tail.strip() else "ffmpeg failed with no stderr output")

def ensure_h264_preview(original: Path) -> Path:
    """
    Preview-only, original untouched.
    Outputs strict browser-friendly: H.264 + yuv420p + faststart.
    """
    out = preview_path_for(original)

    if out.exists():
        try:
            if out.stat().st_size == 0:
                out.unlink()
        except Exception:
            pass

    if out.exists():
        try:
            if out.stat().st_size > 0:
                return out
        except Exception:
            pass

    lock = out.with_suffix(".lock")
    if lock.exists():
        for _ in range(240):
            if out.exists():
                try:
                    if out.stat().st_size > 0:
                        return out
                except Exception:
                    pass
            time.sleep(0.5)

    lock.write_text("1", encoding="utf-8")
    tmp = out.with_suffix(".tmp.mp4")

    vf = "scale=-2:480"

    common_in = [
        "-hide_banner",
        "-fflags", "+genpts",
        "-analyzeduration", "100M",
        "-probesize", "100M",
        "-i", str(original),
    ]

    common_out = [
        "-vf", vf,
        "-c:v", "libx264",
        "-preset", "veryfast",
        "-crf", "23",
        "-pix_fmt", "yuv420p",
        "-profile:v", "main",
        "-level", "3.1",
        "-movflags", "+faststart",
        str(tmp)
    ]

    cmd1 = ["ffmpeg", "-y", *common_in,
            "-map", "0:v:0", "-map", "0:a:0?",
            "-c:a", "aac", "-b:a", "128k",
            *common_out]

    cmd2 = ["ffmpeg", "-y", *common_in,
            "-map", "0:v:0", "-an",
            *common_out]

    cmd3 = ["ffmpeg", "-y",
            "-hide_banner",
            "-fflags", "+genpts",
            "-err_detect", "ignore_err",
            "-analyzeduration", "100M",
            "-probesize", "100M",
            "-i", str(original),
            "-map", "0:v:0", "-map", "0:a:0?",
            "-c:a", "aac", "-b:a", "128k",
            *common_out]

    try:
        if tmp.exists():
            try: tmp.unlink()
            except Exception: pass

        try:
            run_ffmpeg(cmd1)
        except Exception:
            if tmp.exists():
                try: tmp.unlink()
                except Exception: pass
            try:
                run_ffmpeg(cmd2)
            except Exception:
                if tmp.exists():
                    try: tmp.unlink()
                    except Exception: pass
                run_ffmpeg(cmd3)

        if (not tmp.exists()) or tmp.stat().st_size == 0:
            raise RuntimeError("Preview temp file missing or empty after ffmpeg.")

        tmp.replace(out)

        if out.stat().st_size == 0:
            raise RuntimeError("Preview output is empty after rename.")

        return out

    finally:
        if tmp.exists():
            try: tmp.unlink()
            except Exception: pass
        if lock.exists():
            try: lock.unlink()
            except Exception: pass

# ---------------- SAFE RANGE STREAMING (NEVER 416) ----------------

def parse_range(range_header: str, file_size: int) -> Optional[Tuple[int, int]]:
    if not range_header:
        return None
    if not range_header.startswith("bytes="):
        return None
    spec = range_header.replace("bytes=", "", 1).strip()
    if "," in spec or "-" not in spec:
        return None

    start_s, end_s = spec.split("-", 1)
    start_s, end_s = start_s.strip(), end_s.strip()

    try:
        if start_s == "":
            length = int(end_s)
            if length <= 0:
                return None
            start = max(file_size - length, 0)
            end = file_size - 1
            return start, end

        start = int(start_s)
        end = file_size - 1 if end_s == "" else int(end_s)

        if start < 0 or end < 0 or start > end or start >= file_size:
            return None
        end = min(end, file_size - 1)
        return start, end
    except Exception:
        return None

def file_iterator(path: Path, start: int, end: int) -> Iterator[bytes]:
    with path.open("rb") as f:
        f.seek(start)
        remaining = end - start + 1
        while remaining > 0:
            chunk = f.read(min(CHUNK_SIZE, remaining))
            if not chunk:
                break
            remaining -= len(chunk)
            yield chunk

# ---------------- FRAMES FALLBACK (SAFE) ----------------

def frames_key_for(original: Path) -> str:
    st = original.stat()
    key = f"{original.resolve()}|{st.st_mtime_ns}|{st.st_size}|frames|{FRAMES_FPS}|{FRAMES_HEIGHT}|{JPG_Q}"
    return hashlib.md5(key.encode("utf-8")).hexdigest()

def ffprobe_duration_seconds(p: Path) -> Optional[float]:
    try:
        out = subprocess.check_output(
            ["ffprobe", "-v", "error", "-show_entries", "format=duration", "-of", "default=nw=1", str(p)],
            text=True
        )
        for line in out.splitlines():
            if line.startswith("duration="):
                return float(line.split("=", 1)[1].strip())
    except Exception:
        return None
    return None

def ensure_frames(original: Path) -> Tuple[str, int]:
    key = frames_key_for(original)
    out_dir = FRAMES_DIR / key
    out_dir.mkdir(exist_ok=True)

    existing = sorted(out_dir.glob("frame_*.jpg"))
    if existing:
        return key, len(existing)

    lock = out_dir / ".lock"
    if lock.exists():
        for _ in range(240):
            existing = sorted(out_dir.glob("frame_*.jpg"))
            if existing:
                return key, len(existing)
            time.sleep(0.5)

    lock.write_text("1", encoding="utf-8")

    try:
        dur = ffprobe_duration_seconds(original)
        if dur is not None and dur > 0:
            target = int(dur * FRAMES_FPS)
            target = max(1, min(target, FRAMES_MAX))
        else:
            target = FRAMES_MAX

        vf = f"fps={FRAMES_FPS},scale=-2:{FRAMES_HEIGHT}"
        pattern = str(out_dir / "frame_%06d.jpg")

        cmd = [
            "ffmpeg", "-y",
            "-hide_banner",
            "-fflags", "+genpts",
            "-analyzeduration", "100M",
            "-probesize", "100M",
            "-i", str(original),
            "-vf", vf,
            "-frames:v", str(target),
            "-q:v", str(JPG_Q),
            pattern
        ]
        run_ffmpeg(cmd)

        frames = sorted(out_dir.glob("frame_*.jpg"))
        if not frames:
            raise RuntimeError("No frames extracted (file may be corrupted).")

        return key, len(frames)

    finally:
        if lock.exists():
            try: lock.unlink()
            except Exception: pass

ROLE_RANGES = {}

def build_role_ranges():
    global ROLE_RANGES

    ROLE_RANGES = {}

    VIDEOS_PER_ROLE = 25

    total_words = len(REFERENCE_WORDS)
    num_groups = math.ceil(total_words / VIDEOS_PER_ROLE)

    base_letters = string.ascii_uppercase[:num_groups]

    for i, c in enumerate(base_letters):
        start = i * VIDEOS_PER_ROLE
        end = min(start + VIDEOS_PER_ROLE, total_words)

        ROLE_RANGES[f"{c}1"] = (start, end)
        ROLE_RANGES[f"{c}2"] = (start, end)

    print("✅ ROLE_RANGES built:", ROLE_RANGES.keys())

# ---------------- STARTUP ----------------

@app.on_event("startup")
def startup():
    init_db()
    load_mapping_if_needed()
    scan_reference_folder() 
    build_role_ranges()
     # ✅ this is what makes words = 500

# ---------------- UI ----------------

@app.get("/isl/", response_class=HTMLResponse)
def index(request: Request):

    # ✅ If not logged in → go to login page
    return RedirectResponse(url="/isl/login")

    # ✅ If logged in → load annotation tool
    html = APP_DIR / "index_5.html"
    if not html.exists():
        return HTMLResponse("<h3>index.html not found</h3>", status_code=404)

    return HTMLResponse(html.read_text(encoding="utf-8"))

@app.get("/isl/admin", response_class=HTMLResponse)
def admin_page(request: Request):

    role = request.session.get("role")
    if role != "Z":
        raise HTTPException(status_code=403, detail="Admin only")

    html = APP_DIR / "admin_review.html"
    if not html.exists():
        return HTMLResponse("<h3>admin_review.html not found</h3>", status_code=404)

    return HTMLResponse(html.read_text(encoding="utf-8"))

@app.get("/isl/tool", response_class=HTMLResponse)
def tool(request: Request):

    # must be logged in
    if not request.session.get("role"):
        return RedirectResponse(url="/isl/login")

    html = APP_DIR / "index_5.html"
    return HTMLResponse(html.read_text(encoding="utf-8"))



@app.get("/isl/login", response_class=HTMLResponse)
def login_page():
    return HTMLResponse("""
    <html>
    <head>
      <title>ISL Login</title>
      <style>
        body{
          font-family: Arial;
          background:#0b0f17;
          color:white;
          display:flex;
          justify-content:center;
          align-items:center;
          height:100vh;
        }
        .box{
          background:#121a27;
          padding:35px;
          border-radius:14px;
          text-align:center;
          width:320px;
        }
        input{
          padding:12px;
          font-size:16px;
          width:90%;
          border-radius:8px;
          border:none;
          outline:none;
          text-align:center;
        }
        button{
          padding:10px 20px;
          margin-top:15px;
          border:none;
          border-radius:10px;
          cursor:pointer;
          font-weight:bold;
        }
      </style>
    </head>

    <body>
      <div class="box">
        <h2>Annotation Tool Login</h2>
        <p>Enter key: <b>user_A1 ... user_U2</b> or <b>user_Z</b></p>

        <input id="keyBox" placeholder="user_A"/>

        <br/>
        <button onclick="doLogin()">Sign In</button>

        <p id="msg"></p>
      </div>

      <script>
      async function doLogin(){
        const key = document.getElementById("keyBox").value.trim();

        // ✅ Correct API path (must include /isl/)
        const res = await fetch("/isl/api/login", {
          method:"POST",
          headers: {"Content-Type":"application/json"},
          body: JSON.stringify({key})
        });

        if(res.ok){
          // ✅ Redirect into annotation tool
          window.location.href = "/isl/tool";
        } else {
          document.getElementById("msg").innerText =
            "❌ Invalid Key!";
        }
      }
      </script>
    </body>
    </html>
    """)


# ------------------------------------

@app.post("/isl/api/login")
async def login(data: dict, request: Request):

    key = data.get("key", "").strip()

    if not key.startswith("user_"):
        raise HTTPException(status_code=401, detail="Invalid login key")

    role = key.replace("user_", "").upper()

    # Admin
    if role == "Z":
        request.session["role"] = "Z"
        return {"ok": True, "role": "Z"}

    # Normal roles A–T
    if role not in ROLE_RANGES:
        raise HTTPException(status_code=401, detail="Invalid login key")

    request.session["role"] = role
    return {"ok": True, "role": role}




# ---------------- WHO AM I (ROLE CHECK) ----------------

@app.get("/isl/api/me")
def me(request: Request):

    role = request.session.get("role")

    if not role:
        raise HTTPException(status_code=401, detail="Not logged in")

    return {"role": role}

# ---------------- USER API ----------------

@app.get("/isl/api/users")
def list_users():
    conn = db()
    cur = conn.cursor()
    cur.execute("SELECT DISTINCT user_id FROM videos ORDER BY user_id")
    users = [r["user_id"] for r in cur.fetchall()]

    cur.execute("""
        SELECT COUNT(*) AS total,
               SUM(CASE WHEN a.video_id IS NOT NULL THEN 1 ELSE 0 END) AS labeled
        FROM videos v
            LEFT JOIN annotations a ON a.video_id = v.id
    """)
    overall = cur.fetchone()
    conn.close()

    return {
        "users": users,
        "total_videos": int(overall["total"]),
        "labeled_videos": int(overall["labeled"] or 0),
        "reference_words": len(REFERENCE_WORDS),
    }
@app.get("/isl/api/user/{user_id}/words")
def user_words(user_id: str, request: Request):

    role = request.session.get("role")
    if not role:
        raise HTTPException(status_code=401, detail="Not logged in")

    all_words = get_reference_words()

    # Admin sees all words
    if role == "Z":
        allowed_words = all_words

    # Normal users see slice
    elif role in ROLE_RANGES:
        start, end = ROLE_RANGES[role]
        allowed_words = all_words[start:end]

    else:
        raise HTTPException(status_code=403, detail="Invalid role")

    return {
        "user_id": user_id,
        "words": allowed_words,
        "total_words": len(allowed_words),
        "role": role
    }



@app.get("/isl/api/user/{user_id}/word/{word}")
def user_word_detail(user_id: str, word: str, request: Request):

    role = request.session.get("role")
    if not role:
        raise HTTPException(status_code=401, detail="Not logged in")

    words = get_reference_words()

    # ✅ Restrict access for normal users
    if role != "Z":
        start, end = ROLE_RANGES[role]
        allowed_words = words[start:end]

        if word.strip().lower() not in allowed_words:
            raise HTTPException(status_code=403, detail="Word not allowed")

    word_l = word.strip().lower()

    # Reference video path
    reference_path = get_reference_path(word_l)

    # ✅ Fetch collected clips + ONLY this annotator’s label
    conn = db()
    cur = conn.cursor()

    cur.execute("""
        SELECT v.id,
               v.collected_path,
               v.user_id,

               a.label,
               a.note,
               a.updated_at

        FROM videos v

        LEFT JOIN annotations a
          ON a.video_id = v.id
         AND a.annotator = ?

        WHERE v.user_id = ?
          AND v.word = ?

        ORDER BY v.collected_path
    """, (role, user_id, word_l))

    vids = [dict(r) for r in cur.fetchall()]
    conn.close()

    idx = words.index(word_l)

    return {
        "user_id": user_id,
        "word": word_l,
        "reference_path": reference_path,
        "videos": vids,
        "index": idx,
        "total_words": len(words),
        "prev_word": words[idx - 1] if idx > 0 else None,
        "next_word": words[idx + 1] if idx < len(words) - 1 else None,
    }

# ---------------- ANNOTATION + EXPORT ----------------



@app.post("/isl/api/annotate")
def annotate(video_id: int, label: str, request: Request, note: Optional[str] = None):

    role = request.session.get("role")

    # 1. Must be logged in
    if not role:
        raise HTTPException(status_code=401, detail="Not logged in")

    # 2. ❌ Admin Z must not annotate
    if role == "Z":
        raise HTTPException(
            status_code=403,
            detail="Admin cannot annotate videos"
        )

    # 3. Validate label
    if label not in ("correct", "comment"):
        raise HTTPException(status_code=400, detail="Invalid label")

    annotator = role   # example: A1, A2

    conn = db()
    cur = conn.cursor()

    # 4. Insert or update annotation
    cur.execute("""
        INSERT INTO annotations(video_id, annotator, label, note)
        VALUES (?,?,?,?)
        ON CONFLICT(video_id, annotator) DO UPDATE SET
            label=excluded.label,
            note=excluded.note,
            updated_at=datetime('now')
    """, (video_id, annotator, label, note))

    conn.commit()
    conn.close()

    return {"ok": True, "annotator": annotator}




@app.get("/isl/api/export.csv")
def export_csv(request: Request):

    role = request.session.get("role")
    if not role:
        raise HTTPException(401, "Not logged in")

    conn = db()
    cur = conn.cursor()

    cur.execute("""
        SELECT v.id AS video_id,
               v.word,
               v.user_id,
               v.collected_path,
               a.label,
               a.note,
               a.updated_at

        FROM videos v

        LEFT JOIN annotations a
          ON a.video_id = v.id
         AND a.annotator = ?

        ORDER BY v.word, v.user_id
    """, (role,))

    rows = cur.fetchall()
    conn.close()

    def gen():
        import io
        out = io.StringIO()
        writer = csv.writer(out)

        writer.writerow([
            "video_id","word","user_id",
            "collected_path",
            "label","note","updated_at"
        ])
        yield out.getvalue()
        out.seek(0); out.truncate(0)

        for r in rows:
            writer.writerow([
                r["video_id"],
                r["word"],
                r["user_id"],
                r["collected_path"],
                r["label"] or "",
                r["note"] or "",
                r["updated_at"] or ""
            ])
            yield out.getvalue()
            out.seek(0); out.truncate(0)

    return StreamingResponse(
        gen(),
        media_type="text/csv",
        headers={"Content-Disposition": f"attachment; filename=export_{role}.csv"}
    )


@app.get("/api/admin/export_all.csv")
def export_all(request: Request):

    role = request.session.get("role")
    if role != "Z":
        raise HTTPException(403, "Admin only")

    conn = db()
    cur = conn.cursor()

    # ---------------------------------------------------------
    # Load all videos
    # ---------------------------------------------------------
    cur.execute("""
        SELECT id, word, user_id, collected_path
        FROM videos
        ORDER BY id
    """)
    videos = cur.fetchall()

    # ---------------------------------------------------------
    # Load all annotations
    # ---------------------------------------------------------
    cur.execute("""
        SELECT video_id, annotator, label, note
        FROM annotations
        ORDER BY video_id, annotator
    """)
    anns = cur.fetchall()

    conn.close()

    # ---------------------------------------------------------
    # Build map: video_id -> list of annotations
    # ---------------------------------------------------------
    ann_map = {}
    for a in anns:
        ann_map.setdefault(a["video_id"], []).append(dict(a))

    # ---------------------------------------------------------
    # CSV Generator
    # ---------------------------------------------------------
    def gen():
        import io
        out = io.StringIO()
        writer = csv.writer(out)

        # ✅ Final Columns
        writer.writerow([
            "video_id",
            "word",
            "user_id",
            "collected_path",

            "label_1",
            "note_1",

            "label_2",
            "note_2",

            "review_status"
        ])

        yield out.getvalue()
        out.seek(0)
        out.truncate(0)

        # ---------------------------------------------------------
        # Process each video row
        # ---------------------------------------------------------
        for v in videos:

            vid = v["id"]
            word = v["word"]
            user_id = v["user_id"]
            path = v["collected_path"]

            annotations = ann_map.get(vid, [])

            # Defaults
            label_1 = ""
            note_1 = ""
            label_2 = ""
            note_2 = ""

            # -------------------------------------------------
            # Fill annotator 1 and 2
            # -------------------------------------------------
            if len(annotations) >= 1:
                label_1 = annotations[0]["label"]
                note_1 = annotations[0]["note"] or ""

            if len(annotations) >= 2:
                label_2 = annotations[1]["label"]
                note_2 = annotations[1]["note"] or ""

            # -------------------------------------------------
            # Determine review_status
            # -------------------------------------------------
            if len(annotations) == 0:
                status = "not_annotated"

            elif len(annotations) == 1:
                status = "one_missing"

            else:
                # Both exist
                if label_1 == "correct" and label_2 == "correct":
                    status = "ok"

                elif label_1 == "comment" and label_2 == "comment":
                    status = "both_commented"

                else:
                    status = "mismatch"

            # -------------------------------------------------
            # Write final row
            # -------------------------------------------------
            writer.writerow([
                vid,
                word,
                user_id,
                path,

                label_1,
                note_1,

                label_2,
                note_2,

                status
            ])

            yield out.getvalue()
            out.seek(0)
            out.truncate(0)

    return StreamingResponse(
        gen(),
        media_type="text/csv",
        headers={
            "Content-Disposition": "attachment; filename=admin_full_export.csv"
        }
    )


# ---------------- ADMIN REVIEW DASHBOARD ----------------

@app.get("/isl/api/admin/review_summary")
def admin_review_summary(request: Request):

    # ---------------- Admin Check ----------------
    role = request.session.get("role")
    if role != "Z":
        raise HTTPException(403, "Admin only")

    conn = db()
    cur = conn.cursor()

    # ---------------- Load All Videos ----------------
    cur.execute("""
        SELECT id, word, user_id, collected_path
        FROM videos
        ORDER BY word, user_id
    """)
    videos = [dict(r) for r in cur.fetchall()]

    # ---------------- Load All Annotations ----------------
    cur.execute("""
        SELECT video_id, annotator, label, note, updated_at
        FROM annotations
    """)
    annotations = [dict(r) for r in cur.fetchall()]

    conn.close()

    # ---------------- Map: video_id -> annotations ----------------
    ann_map = {}
    for a in annotations:
        ann_map.setdefault(a["video_id"], []).append(a)

    # ---------------- Categories ----------------
    both_comment = []     # both validators commented
    mismatch = []         # one correct, one comment
    one_missing = []      # only one validator annotated

    # ---------------- Counts ----------------
    not_annotated = 0     # no annotation yet
    annotated = 0         # both correct

    # ---------------- Categorize Videos ----------------
    for v in videos:
        vid = v["id"]

        anns = ann_map.get(vid, [])

        # only consider first 2 validators
        anns = anns[:2]

        # Case 1: Not annotated at all
        if len(anns) == 0:
            not_annotated += 1
            continue

        # Case 2: Only one validator annotated
        if len(anns) == 1:
            one_missing.append({
                **v,
                "annotations": anns
            })
            continue

        # Case 3: Two annotations exist
        labels = [x["label"] for x in anns]

        # Fully annotated and correct
        if all(l == "correct" for l in labels):
            annotated += 1
            continue

        # Both commented
        if all(l == "comment" for l in labels):
            both_comment.append({
                **v,
                "annotations": anns
            })
            continue

        # Mismatch (one correct, one comment)
        if "correct" in labels and "comment" in labels:
            mismatch.append({
                **v,
                "annotations": anns
            })
            continue

    # ---------------- Final Response ----------------
    return {
        "annotated": annotated,
        "not_annotated": not_annotated,

        "one_missing": one_missing,
        "mismatch": mismatch,
        "both_comment": both_comment
    }


# ---------------- FRAMES API + SERVE ----------------

@app.get("/isl/api/frames")
def frames_api(path: str = Query(...)):
    p = Path(path)
    if not p.exists() or not p.is_file():
        raise HTTPException(status_code=404, detail="File not found")
    if p.suffix.lower() not in VIDEO_EXTS:
        raise HTTPException(status_code=400, detail="Not a supported video file")
    if not is_allowed_path(p):
        raise HTTPException(status_code=403, detail="Path not allowed")

    try:
        key, count = ensure_frames(p)
        return JSONResponse({"key": key, "count": count, "fps": FRAMES_FPS})
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Frame extraction failed: {e}")

@app.get("/isl/frames/{key}/{name}")
def frames_serve(key: str, name: str):
    p = (FRAMES_DIR / key / name).resolve()
    if not str(p).startswith(str((FRAMES_DIR / key).resolve())):
        raise HTTPException(status_code=403, detail="Invalid path")
    if not p.exists() or not p.is_file():
        raise HTTPException(status_code=404, detail="Frame not found")
    return FileResponse(str(p), media_type="image/jpeg")

# ---------------- MEDIA (PREVIEW + SAFE RANGE) ----------------

@app.get("/isl/media")
def media(request: Request, path: str = Query(...)):
    p = Path(path)
    if not p.exists() or not p.is_file():
        raise HTTPException(status_code=404, detail="File not found")
    if p.suffix.lower() not in VIDEO_EXTS:
        raise HTTPException(status_code=400, detail="Not a supported video file")
    if not is_allowed_path(p):
        raise HTTPException(status_code=403, detail="Path not allowed")

    codec = ffprobe_video_codec(p)
    serve_path = p

    # For collected clips: always use preview for consistent browser playback
    if is_collected_path(p) or (codec is None) or (codec != "h264"):
        serve_path = ensure_h264_preview(p)

    size = serve_path.stat().st_size
    range_header = request.headers.get("range", "")
    parsed = parse_range(range_header, size)

    headers = {
        "Accept-Ranges": "bytes",
        "Content-Type": "video/mp4",
        "Cache-Control": "no-store",
    }

    if parsed is None:
        return StreamingResponse(
            file_iterator(serve_path, 0, size - 1),
            status_code=200,
            headers={**headers, "Content-Length": str(size)}
        )

    start, end = parsed
    headers.update({
        "Content-Range": f"bytes {start}-{end}/{size}",
        "Content-Length": str(end - start + 1),
    })

    return StreamingResponse(
        file_iterator(serve_path, start, end),
        status_code=206,
        headers=headers
    )