storage/media_routers.py

import os
import io
import shutil

import sqlite3

from pathlib import Path

from fastapi import APIRouter, UploadFile, File, Query, HTTPException
from fastapi.responses import FileResponse, JSONResponse


from storage.files.file_manager import FileManager

router = APIRouter(prefix="/media", tags=["Media Manager"])
MEDIA_ROOT = Path("/data/media")
file_manager = FileManager(MEDIA_ROOT)
HF_TOKEN = os.getenv("HF_TOKEN")
DB_PATH = Path("/data/db")

def validate_token(token: str):
    """
    Validate the provided token against the HF_TOKEN environment variable.
    Raises an HTTPException if validation fails.
    """
    if HF_TOKEN is None:
        raise RuntimeError("HF_TOKEN environment variable is not set on the server.")

    if token != HF_TOKEN:
        raise HTTPException(status_code=401, detail="Invalid token")

@router.post("/upload_db_file", tags=["Database Manager"])
async def upload_db_file(
    file: UploadFile = File(...),
    token: str = Query(..., description="Token required for authorization")
):
    """
    Upload a file to the /data/db folder.

    Steps:
    - Validate the token.
    - Ensure /data/db exists (create it if missing).
    - Save the uploaded file inside /data/db using its original filename.
    - Return a JSON response confirming the upload.
    """
    validate_token(token)

    # Crear carpeta /data/db si no existe
    DB_PATH.mkdir(parents=True, exist_ok=True)

    final_path = DB_PATH / file.filename

    try:
        # Leer contenido en memoria y guardar
        file_bytes = await file.read()
        with open(final_path, "wb") as f:
            f.write(file_bytes)
    except Exception as exc:
        raise HTTPException(status_code=500, detail=f"Failed to save file: {exc}")

    return JSONResponse(
        status_code=200,
        content={
            "status": "ok",
            "saved_to": str(final_path)
        }
    )

@router.post("/execute_query", tags=["Database Manager"])
async def execute_query(
    db_filename: str = Query(..., description="SQLite .db file name"),
    query: str = Query(..., description="SQL query to execute"),
    token: str = Query(..., description="Token required for authorization")
):
    """
    Execute a SQL query against a specified SQLite database file in /data/db.

    Steps:
    - Validate the token.
    - Ensure the requested .db file exists inside /data/db.
    - Connect to the database using sqlite3.
    - Execute the provided query.
    - Return the results as a list of dictionaries (column: value).
    - Capture and return errors if query execution fails.
    """
    validate_token(token)

    db_file_path = DB_PATH / db_filename

    if not db_file_path.exists() or not db_file_path.is_file():
        raise HTTPException(status_code=404, detail=f"Database file {db_filename} not found")

    try:
        conn = sqlite3.connect(db_file_path)
        conn.row_factory = sqlite3.Row  # para devolver columnas por nombre
        cur = conn.cursor()

        cur.execute(query)
        rows = cur.fetchall()

        # Convert rows to list of dicts
        results = [dict(row) for row in rows]

        conn.commit()
        conn.close()
    except sqlite3.Error as e:
        raise HTTPException(status_code=400, detail=f"SQL error: {e}")
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Unexpected error: {e}")

    return JSONResponse(content={"results": results})

@router.delete("/clear_media", tags=["Media Manager"])
def clear_media(token: str = Query(..., description="Token required for authorization")):
    """
    Delete all contents of the /data/media folder.

    Steps:
    - Validate the token.
    - Ensure the folder exists.
    - Delete all files and subfolders inside /data/media.
    - Return a JSON response confirming the deletion.

    Warning: This will remove all stored videos, clips, and cast CSV files.
    """
    validate_token(token)

    if not MEDIA_ROOT.exists() or not MEDIA_ROOT.is_dir():
        raise HTTPException(status_code=404, detail="/data/media folder does not exist")

    # Delete contents
    for item in MEDIA_ROOT.iterdir():
        try:
            if item.is_dir():
                shutil.rmtree(item)
            else:
                item.unlink()
        except Exception as e:
            raise HTTPException(status_code=500, detail=f"Failed to delete {item}: {e}")

    return {"status": "ok", "message": "All media files deleted successfully"}

@router.post("/upload_cast_csv/{sha1}", tags=["Media Manager"])
async def upload_cast_csv(
    sha1: str,
    cast_file: UploadFile = File(...),
    token: str = Query(..., description="Token required for authorization")
):
    """
    Upload a cast CSV file for a specific video identified by its SHA-1.

    The CSV will be stored under:
        /data/media/<sha1>/cast/cast.csv

    Steps:
    - Validate the token.
    - Ensure /data/media/<sha1> exists.
    - Create /cast folder if missing.
    - Save the CSV file inside /cast.
    """
    validate_token(token)

    base_folder = MEDIA_ROOT / sha1
    if not base_folder.exists() or not base_folder.is_dir():
        raise HTTPException(status_code=404, detail="SHA1 folder not found")

    cast_folder = base_folder / "cast"
    cast_folder.mkdir(parents=True, exist_ok=True)

    final_path = cast_folder / "cast.csv"

    file_bytes = await cast_file.read()
    save_result = file_manager.upload_file(io.BytesIO(file_bytes), final_path)
    if not save_result["operation_success"]:
        raise HTTPException(status_code=500, detail=save_result["error"])

    return JSONResponse(
        status_code=200,
        content={"status": "ok", "saved_to": str(final_path)}
    )


@router.get("/download_cast_csv/{sha1}", tags=["Media Manager"])
def download_cast_csv(
    sha1: str,
    token: str = Query(..., description="Token required for authorization")
):
    """
    Download the cast CSV for a specific video identified by its SHA-1.

    The CSV is expected under:
        /data/media/<sha1>/cast/cast.csv

    Steps:
    - Validate the token.
    - Ensure /data/media/<sha1> and /cast exist.
    - Return the CSV as a FileResponse.
    - Raise 404 if any folder or file is missing.
    """
    MEDIA_ROOT = Path("/data/media")
    file_manager = FileManager(MEDIA_ROOT)
    HF_TOKEN = os.getenv("HF_TOKEN")
    validate_token(token)

    base_folder = MEDIA_ROOT / sha1
    cast_folder = base_folder / "cast"
    csv_path = cast_folder / "cast.csv"

    if not base_folder.exists() or not base_folder.is_dir():
        raise HTTPException(status_code=404, detail="SHA1 folder not found")
    if not cast_folder.exists() or not cast_folder.is_dir():
        raise HTTPException(status_code=404, detail="Cast folder not found")
    if not csv_path.exists() or not csv_path.is_file():
        raise HTTPException(status_code=404, detail="Cast CSV not found")

    # Convert to relative path for FileManager
    relative_path = csv_path.relative_to(MEDIA_ROOT)
    handler = file_manager.get_file(relative_path)
    if handler is None:
        raise HTTPException(status_code=404, detail="Cast CSV not accessible")
    handler.close()

    return FileResponse(
        path=csv_path,
        media_type="text/csv",
        filename="cast.csv"
    )

@router.post("/upload_original_video", tags=["Media Manager"])
async def upload_video(
    video: UploadFile = File(...),
    token: str = Query(..., description="Token required for authorization")
    ):
    """
    Saves an uploaded video by hashing it with SHA1 and placing it under:
    /data/media/<sha1>/clip/<original_filename>

    Steps:
    - Compute SHA1 of the uploaded video.
    - Ensure /data/media exists.
    - Create folder /data/media/<sha1> if missing.
    - Create folder /data/media/<sha1>/clip if missing.
    - Save the video inside /data/media/<sha1>/clip/.
    """
    MEDIA_ROOT = Path("/data/media")
    file_manager = FileManager(MEDIA_ROOT)
    HF_TOKEN = os.getenv("HF_TOKEN")
    validate_token(token)

    # Read content into memory (needed to compute hash twice)
    file_bytes = await video.read()

    # Create an in-memory file handler
    file_handler = io.BytesIO(file_bytes)

    # Compute SHA1 using your FileManager method
    try:
        sha1 = file_manager.compute_sha1(file_handler)
    except Exception as exc:
        raise HTTPException(status_code=500, detail=f"SHA1 computation failed: {exc}")

    # Ensure /data/media exists
    MEDIA_ROOT.mkdir(parents=True, exist_ok=True)

    # Path: /data/media/<sha1>
    video_root = MEDIA_ROOT / sha1
    video_root.mkdir(parents=True, exist_ok=True)

    # Path: /data/media/<sha1>/clip
    clip_dir = video_root / "clip"
    clip_dir.mkdir(parents=True, exist_ok=True)

    # Final file path
    final_path = clip_dir / video.filename

    # Save file using your FileManager.upload_file
    save_result = file_manager.upload_file(io.BytesIO(file_bytes), final_path)

    if not save_result["operation_success"]:
        raise HTTPException(status_code=500, detail=save_result["error"])

    return JSONResponse(
        status_code=200,
        content={
            "status": "ok",
            "sha1": sha1,
            "saved_to": str(final_path)
        }
    )


@router.get("/download_original_video/{sha1}", tags=["Media Manager"])
def download_video(
    sha1: str,
    token: str = Query(..., description="Token required for authorization")
    ):
    """
    Download a stored video by its SHA-1 directory name.

    This endpoint looks for a video stored under the path:
        /data/media/<sha1>/clip/
    and returns the first MP4 file found in that folder.

    The method performs the following steps:
    - Checks if the SHA-1 folder exists inside the media root.
    - Validates that the "clip" subfolder exists.
    - Searches for the first .mp4 file inside the clip folder.
    - Uses the FileManager.get_file method to ensure the file is accessible.
    - Returns the video directly as a FileResponse.

    Parameters
    ----------
    sha1 : str
        The SHA-1 hash corresponding to the directory where the video is stored.

    Returns
    -------
    FileResponse
        A streaming response containing the MP4 video.

    Raises
    ------
    HTTPException
        - 404 if the SHA-1 folder does not exist.
        - 404 if the clip folder is missing.
        - 404 if no MP4 files are found.
        - 404 if the file cannot be retrieved using FileManager.
    """
    MEDIA_ROOT = Path("/data/media")
    file_manager = FileManager(MEDIA_ROOT)
    HF_TOKEN = os.getenv("HF_TOKEN")
    validate_token(token)

    sha1_folder = MEDIA_ROOT / sha1
    clip_folder = sha1_folder / "clip"

    if not sha1_folder.exists() or not sha1_folder.is_dir():
        raise HTTPException(status_code=404, detail="SHA1 folder not found")

    if not clip_folder.exists() or not clip_folder.is_dir():
        raise HTTPException(status_code=404, detail="Clip folder not found")

    # Find first MP4 file
    mp4_files = list(clip_folder.glob("*.mp4"))
    if not mp4_files:
        raise HTTPException(status_code=404, detail="No MP4 files found")

    video_path = mp4_files[0]

    # Convert to relative path for FileManager
    relative_path = video_path.relative_to(MEDIA_ROOT)

    handler = file_manager.get_file(relative_path)
    if handler is None:
        raise HTTPException(status_code=404, detail="Video not accessible")

    handler.close()

    return FileResponse(
        path=video_path,
        media_type="video/mp4",
        filename=video_path.name
    )