"""Database-based report storage implementation."""

from typing import Dict, Any, List, Optional
from loguru import logger
from sqlalchemy.orm import Session

from .base import ReportStorage
from ..database.models import ResearchHistory


class DatabaseReportStorage(ReportStorage):
    """Store reports in the database with caching support."""

    def __init__(self, session: Session):
        """Initialize database storage.

        Args:
            session: SQLAlchemy database session
        """
        self.session = session

    def save_report(
        self,
        research_id: str,
        content: str,
        metadata: Optional[Dict[str, Any]] = None,
        username: Optional[str] = None,
    ) -> bool:
        """Save report to database."""
        try:
            research = (
                self.session.query(ResearchHistory)
                .filter_by(id=research_id)
                .first()
            )

            if not research:
                logger.error(f"Research {research_id} not found")
                return False

            research.report_content = content  # type: ignore[assignment]

            if metadata:
                if research.research_meta:
                    research.research_meta.update(metadata)  # type: ignore[union-attr]
                else:
                    research.research_meta = metadata  # type: ignore[assignment]

            self.session.commit()
            logger.info(f"Saved report for research {research_id} to database")
            return True

        except Exception:
            logger.exception("Error saving report to database")
            self.session.rollback()
            return False

    def get_report(
        self, research_id: str, username: Optional[str] = None
    ) -> Optional[str]:
        """Return raw ``report_content`` for a research row.

        IMPORTANT: ``report_content`` is the answer body only — the
        assembled "## Sources" / "## Research Metrics" sections live in
        ``research_resources`` and the
        per-research metrics tables and are stitched in by
        ``web.services.report_assembly_service.assemble_full_report``.

        Do **not** use this method for any user-facing display path.
        Call ``assemble_full_report(research, db_session)`` instead so
        legacy rows (which embed sources/metrics inline in
        ``report_content``) and new rows render identically. Current
        callers of this method use the truncated content for
        notification teasers / summary previews where answer-only is
        the desired shape.
        """
        try:
            research = (
                self.session.query(ResearchHistory)
                .filter_by(id=research_id)
                .first()
            )

            if not research or not research.report_content:
                return None

            return research.report_content  # type: ignore[return-value]

        except Exception:
            logger.exception("Error getting report from database")
            return None

    def get_report_with_metadata(
        self, research_id: str, username: Optional[str] = None
    ) -> Optional[Dict[str, Any]]:
        """Return ``report_content`` + research metadata for a row.

        Same answer-only caveat as :meth:`get_report` — see that
        docstring before using ``["content"]`` for any user-facing
        rendering path; prefer ``assemble_full_report`` instead.
        """
        try:
            research = (
                self.session.query(ResearchHistory)
                .filter_by(id=research_id)
                .first()
            )

            if not research or not research.report_content:
                return None

            return {
                "content": research.report_content,
                "metadata": research.research_meta or {},
                "query": research.query,
                "mode": research.mode,
                "created_at": research.created_at,
                "completed_at": research.completed_at,
                "duration_seconds": research.duration_seconds,
            }

        except Exception:
            logger.exception("Error getting report with metadata")
            return None

    def list_reports(
        self, username: Optional[str] = None
    ) -> List[Dict[str, Any]]:
        """List reports from database."""
        try:
            # Select only the metadata columns the listing returns —
            # never load the large ``report_content`` body, which would
            # pull every report into memory at once (#4560). Filtering on
            # the column does not load it.
            query = self.session.query(
                ResearchHistory.id,
                ResearchHistory.query,
                ResearchHistory.mode,
                ResearchHistory.created_at,
                ResearchHistory.completed_at,
            ).filter(ResearchHistory.report_content.isnot(None))
            results = query.all()
            return [
                {
                    "id": r.id,
                    "query": r.query,
                    "mode": r.mode,
                    "created_at": r.created_at,
                    "completed_at": r.completed_at,
                }
                for r in results
            ]
        except Exception:
            logger.exception("Error listing reports from database")
            return []

    def delete_report(
        self, research_id: str, username: Optional[str] = None
    ) -> bool:
        """Delete report from database."""
        try:
            research = (
                self.session.query(ResearchHistory)
                .filter_by(id=research_id)
                .first()
            )

            if not research:
                return False

            research.report_content = None  # type: ignore[assignment]
            self.session.commit()

            return True

        except Exception:
            logger.exception("Error deleting report")
            self.session.rollback()
            return False

    def report_exists(
        self, research_id: str, username: Optional[str] = None
    ) -> bool:
        """Check if report exists in database."""
        try:
            research = (
                self.session.query(ResearchHistory)
                .filter_by(id=research_id)
                .first()
            )

            return research is not None and research.report_content is not None

        except Exception:
            logger.exception("Error checking if report exists")
            return False
