"""Content store — tracks session files published for inline viewing.

Files live in the session directory (settings.SESSION_FILES_DIR/<session_id>/).
Publishing only registers metadata in the DB; the file itself is NOT copied.
The URL points to the existing /sessions/{id}/files/{filename} endpoint.
"""

import asyncio
import uuid
from datetime import datetime, timedelta, timezone
from pathlib import Path
from typing import TYPE_CHECKING

import structlog

from navi.config import settings
from navi.session_files import ensure_session_dir, session_dir

if TYPE_CHECKING:
    import asyncpg

log = structlog.get_logger()

_EXT_TO_TYPE = {
    ".stl": "stl",
    ".html": "html",
    ".htm": "html",
    ".svg": "svg",
    ".pdf": "pdf",
    ".png": "image",
    ".jpg": "image",
    ".jpeg": "image",
    ".gif": "image",
    ".webp": "image",
    ".mp4": "video",
    ".webm": "video",
}


def _detect_content_type(filename: str) -> str:
    ext = Path(filename).suffix.lower()
    return _EXT_TO_TYPE.get(ext, "unknown")


async def _get_db_pool():
    """Get asyncpg pool from deps (lazy import to avoid circular)."""
    from navi.api.deps import get_memory_store

    store = get_memory_store()
    return await store._get_pool()


async def ensure_tables() -> None:
    """Create session_content table if missing."""
    try:
        pool = await _get_db_pool()
    except Exception:
        log.warning("content_store.no_db", exc_info=True)
        return
    async with pool.acquire() as conn:
        await conn.execute(
            """CREATE TABLE IF NOT EXISTS session_content (
                id          TEXT PRIMARY KEY,
                session_id  TEXT NOT NULL,
                filename    TEXT NOT NULL,
                content_type TEXT NOT NULL,
                title       TEXT,
                created_at  TIMESTAMPTZ NOT NULL
            )"""
        )
        await conn.execute(
            "CREATE INDEX IF NOT EXISTS idx_session_content_session ON session_content (session_id)"
        )
        await conn.execute(
            "CREATE UNIQUE INDEX IF NOT EXISTS idx_session_content_file "
            "ON session_content (session_id, filename)"
        )


async def publish(
    session_id: str,
    filename: str,
    title: str | None = None,
    content_type: str | None = None,
) -> dict:
    """Register a file from the session directory for inline viewing.

    The file must already exist in settings.SESSION_FILES_DIR/{session_id}/.
    Returns {"id": "...", "url": "...", "filename": "...", "content_type": "..."}
    """
    src = ensure_session_dir(session_id) / filename
    if not src.exists():
        raise FileNotFoundError(
            f"File '{filename}' not found in session directory: {session_dir(session_id)}"
        )
    if not src.is_file():
        raise IsADirectoryError(f"Path is a directory, not a file: {src}")

    detected_type = content_type or _detect_content_type(filename)
    safe_filename = filename.replace("/", "_").replace("\\", "_")
    content_id = f"{session_id}_{safe_filename}"

    try:
        pool = await _get_db_pool()
        async with pool.acquire() as conn:
            await conn.execute(
                """INSERT INTO session_content
                    (id, session_id, filename, content_type, title, created_at)
                   VALUES ($1, $2, $3, $4, $5, $6)
                   ON CONFLICT (session_id, filename) DO UPDATE
                   SET content_type = EXCLUDED.content_type,
                       title = EXCLUDED.title,
                       created_at = EXCLUDED.created_at""",
                content_id,
                session_id,
                filename,
                detected_type,
                title or filename,
                datetime.now(timezone.utc),
            )
    except Exception:
        log.warning("content_store.db_upsert_failed", content_id=content_id, exc_info=True)
        raise

    base_url = settings.public_url.rstrip("/")
    url = f"{base_url}/sessions/{session_id}/files/{filename}"

    log.info(
        "content_store.published",
        content_id=content_id,
        session_id=session_id,
        filename=filename,
        type=detected_type,
        url=url,
    )
    return {
        "id": content_id,
        "url": url,
        "filename": filename,
        "content_type": detected_type,
        "title": title or filename,
    }


async def list_for_session(session_id: str) -> list[dict]:
    """Return all published content items for a session."""
    try:
        pool = await _get_db_pool()
    except Exception:
        return []
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            "SELECT id, filename, content_type, title, created_at "
            "FROM session_content WHERE session_id=$1 ORDER BY created_at DESC",
            session_id,
        )
    return [
        {
            "id": r["id"],
            "filename": r["filename"],
            "content_type": r["content_type"],
            "title": r["title"],
            "created_at": r["created_at"].isoformat() if hasattr(r["created_at"], "isoformat") else str(r["created_at"]),
        }
        for r in rows
    ]


async def delete_content(content_id: str) -> bool:
    """Remove a content item from DB (file on disk is NOT deleted — it lives in session_dir)."""
    try:
        pool = await _get_db_pool()
        async with pool.acquire() as conn:
            result = await conn.execute("DELETE FROM session_content WHERE id=$1", content_id)
            return int(result.split()[1]) > 0
    except Exception:
        log.warning("content_store.db_delete_failed", content_id=content_id, exc_info=True)
        return False


async def cleanup_old(days: int = 30) -> int:
    """Delete DB records older than N days (files on disk are managed by session lifecycle)."""
    cutoff = datetime.now(timezone.utc) - timedelta(days=days)
    try:
        pool = await _get_db_pool()
    except Exception:
        return 0
    async with pool.acquire() as conn:
        result = await conn.execute(
            "DELETE FROM session_content WHERE created_at < $1", cutoff
        )
    deleted = int(result.split()[1])
    log.info("content_store.cleanup", deleted=deleted, cutoff=cutoff)
    return deleted