"""
SQLAlchemy storage implementation for subscriptions.
"""

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

from ..core.storage import SubscriptionStorage
from ...database.models.news import (
    NewsSubscription,
    SubscriptionType,
    SubscriptionStatus,
)


class SQLSubscriptionStorage(SubscriptionStorage):
    """SQLAlchemy implementation of subscription storage"""

    def __init__(self, session: Optional[Session] = None):
        """Initialize with a database session from the user's encrypted database"""
        self._session = session

    @property
    def session(self):
        """Get database session"""
        return self._session

    def create(self, data: Dict[str, Any]) -> str:
        """Create a new subscription"""
        subscription_id = data.get("id") or self.generate_id()

        with self.session as session:
            subscription = NewsSubscription(
                id=subscription_id,
                user_id=data["user_id"],
                name=data.get("name"),
                subscription_type=data["subscription_type"],
                query_or_topic=data["query_or_topic"],
                refresh_interval_minutes=data["refresh_interval_minutes"],
                frequency=data.get("frequency", "daily"),
                source_type=data.get("source_type"),
                source_id=data.get("source_id"),
                created_from=data.get("created_from"),
                folder=data.get("folder"),
                folder_id=data.get("folder_id"),
                notes=data.get("notes"),
                status=data.get("status", "active"),
                is_active=data.get("is_active", True),
                model_provider=data.get("model_provider"),
                model=data.get("model"),
                search_strategy=data.get("search_strategy"),
                custom_endpoint=data.get("custom_endpoint"),
                search_engine=data.get("search_engine"),
                search_iterations=data.get("search_iterations", 3),
                questions_per_iteration=data.get("questions_per_iteration", 5),
                next_refresh=datetime.now(timezone.utc)
                + timedelta(minutes=data["refresh_interval_minutes"]),
            )

            session.add(subscription)
            session.commit()

            logger.info(
                f"Created subscription {subscription_id} for user {data['user_id']}"
            )
            return subscription_id

    def get(self, id: str) -> Optional[Dict[str, Any]]:
        """Get a subscription by ID"""
        with self.session as session:
            subscription = (
                session.query(NewsSubscription).filter_by(id=id).first()
            )
            if not subscription:
                return None

            # Convert to dict manually
            return {
                "id": subscription.id,
                "user_id": subscription.user_id,
                "name": subscription.name,
                "subscription_type": subscription.subscription_type,
                "query_or_topic": subscription.query_or_topic,
                "refresh_interval_minutes": subscription.refresh_interval_minutes,
                "created_at": subscription.created_at,
                "updated_at": subscription.updated_at,
                "last_refresh": subscription.last_refresh,
                "next_refresh": subscription.next_refresh,
                "expires_at": subscription.expires_at,
                "source_type": subscription.source_type,
                "source_id": subscription.source_id,
                "created_from": subscription.created_from,
                "folder": subscription.folder,
                "folder_id": subscription.folder_id,
                "notes": subscription.notes,
                "status": subscription.status,
                "is_active": getattr(subscription, "is_active", True),
                "refresh_count": subscription.refresh_count,
                "results_count": subscription.results_count,
                "last_error": subscription.last_error,
                "error_count": subscription.error_count,
                "model_provider": getattr(subscription, "model_provider", None),
                "model": getattr(subscription, "model", None),
                "search_strategy": getattr(
                    subscription, "search_strategy", None
                ),
                "custom_endpoint": getattr(
                    subscription, "custom_endpoint", None
                ),
                "search_engine": getattr(subscription, "search_engine", None),
                "search_iterations": getattr(
                    subscription, "search_iterations", 3
                ),
                "questions_per_iteration": getattr(
                    subscription, "questions_per_iteration", 5
                ),
            }

    def update(self, id: str, data: Dict[str, Any]) -> bool:
        """Update a subscription"""
        with self.session as session:
            subscription = (
                session.query(NewsSubscription).filter_by(id=id).first()
            )
            if not subscription:
                return False

            # Update allowed fields
            updateable_fields = [
                "name",
                "refresh_interval_minutes",
                "status",
                "is_active",
                "expires_at",
                "folder_id",
                "model_provider",
                "model",
                "search_strategy",
                "custom_endpoint",
                "search_engine",
                "search_iterations",
                "questions_per_iteration",
            ]
            for field in updateable_fields:
                if field in data:
                    # bearer:disable python_lang_code_injection
                    setattr(subscription, field, data[field])

            # Recalculate next refresh if interval changed
            if "refresh_interval_minutes" in data:
                subscription.next_refresh = datetime.now(
                    timezone.utc
                ) + timedelta(minutes=data["refresh_interval_minutes"])

            session.commit()
            return True

    def delete(self, id: str) -> bool:
        """Delete a subscription"""
        with self.session as session:
            subscription = (
                session.query(NewsSubscription).filter_by(id=id).first()
            )
            if not subscription:
                return False

            session.delete(subscription)
            session.commit()
            return True

    def list(
        self,
        filters: Optional[Dict[str, Any]] = None,
        limit: int = 100,
        offset: int = 0,
    ) -> List[Dict[str, Any]]:
        """List subscriptions with optional filtering"""
        with self.session as session:
            query = session.query(NewsSubscription)

            if filters:
                if "user_id" in filters:
                    query = query.filter_by(user_id=filters["user_id"])
                if "status" in filters:
                    query = query.filter_by(
                        status=SubscriptionStatus(filters["status"])
                    )
                if "subscription_type" in filters:
                    query = query.filter_by(
                        subscription_type=SubscriptionType(
                            filters["subscription_type"]
                        )
                    )

            subscriptions = query.limit(limit).offset(offset).all()
            # Detach from session and convert to dicts
            result = []
            for sub in subscriptions:
                session.expunge(sub)
                result.append(
                    {
                        "id": sub.id,
                        "user_id": sub.user_id,
                        "name": sub.name,
                        "subscription_type": sub.subscription_type,
                        "query_or_topic": sub.query_or_topic,
                        "refresh_interval_minutes": sub.refresh_interval_minutes,
                        "created_at": sub.created_at,
                        "updated_at": sub.updated_at,
                        "last_refresh": sub.last_refresh,
                        "next_refresh": sub.next_refresh,
                        "status": sub.status,
                        "folder": sub.folder,
                        "notes": sub.notes,
                    }
                )
            return result

    def get_active_subscriptions(
        self, user_id: Optional[str] = None
    ) -> List[Dict[str, Any]]:
        """Get all active subscriptions"""
        with self.session as session:
            query = session.query(NewsSubscription).filter_by(
                status=SubscriptionStatus.ACTIVE
            )

            if user_id:
                query = query.filter_by(user_id=user_id)

            subscriptions = query.all()
            return [sub.to_dict() for sub in subscriptions]

    def get_due_subscriptions(self, limit: int = 100) -> List[Dict[str, Any]]:
        """Get subscriptions that are due for refresh"""
        with self.session as session:
            now = datetime.now(timezone.utc)

            subscriptions = (
                session.query(NewsSubscription)
                .filter(
                    NewsSubscription.status == SubscriptionStatus.ACTIVE,
                    NewsSubscription.next_refresh <= now,
                )
                .limit(limit)
                .all()
            )

            return [sub.to_dict() for sub in subscriptions]

    def update_refresh_time(
        self,
        subscription_id: str,
        last_refresh: datetime,
        next_refresh: datetime,
    ) -> bool:
        """Update refresh timestamps after processing"""
        with self.session as session:
            subscription = (
                session.query(NewsSubscription)
                .filter_by(id=subscription_id)
                .first()
            )
            if not subscription:
                return False

            subscription.last_refresh = last_refresh
            subscription.next_refresh = next_refresh
            session.commit()
            return True

    def increment_stats(self, subscription_id: str, results_count: int) -> bool:
        """Increment refresh count and update results count"""
        with self.session as session:
            subscription = (
                session.query(NewsSubscription)
                .filter_by(id=subscription_id)
                .first()
            )
            if not subscription:
                return False

            subscription.refresh_count += 1
            subscription.total_runs = subscription.refresh_count  # Keep in sync
            subscription.results_count = results_count
            session.commit()
            return True

    def pause_subscription(self, subscription_id: str) -> bool:
        """Pause a subscription"""
        with self.session as session:
            subscription = (
                session.query(NewsSubscription)
                .filter_by(id=subscription_id)
                .first()
            )
            if not subscription:
                return False

            subscription.status = SubscriptionStatus.PAUSED
            session.commit()
            return True

    def resume_subscription(self, subscription_id: str) -> bool:
        """Resume a paused subscription"""
        with self.session as session:
            subscription = (
                session.query(NewsSubscription)
                .filter_by(id=subscription_id)
                .first()
            )
            if (
                not subscription
                or subscription.status != SubscriptionStatus.PAUSED
            ):
                return False

            subscription.status = SubscriptionStatus.ACTIVE
            # Reset next refresh time
            subscription.next_refresh = datetime.now(timezone.utc) + timedelta(
                minutes=subscription.refresh_interval_minutes
            )
            session.commit()
            return True

    def expire_subscription(self, subscription_id: str) -> bool:
        """Mark a subscription as expired"""
        with self.session as session:
            subscription = (
                session.query(NewsSubscription)
                .filter_by(id=subscription_id)
                .first()
            )
            if not subscription:
                return False

            subscription.status = SubscriptionStatus.EXPIRED
            subscription.expires_at = datetime.now(timezone.utc)
            session.commit()
            return True
