"""
Database models for news subscriptions and related functionality.
These tables are created in per-user encrypted databases.
"""

from sqlalchemy import (
    Column,
    Integer,
    String,
    JSON,
    Text,
    Boolean,
    ForeignKey,
    Enum,
    and_,
)
from sqlalchemy_utc import UtcDateTime, utcnow
import enum

from .base import Base


class CardType(enum.Enum):
    """Types of cards in the system"""

    NEWS = "news"
    RESEARCH = "research"
    UPDATE = "update"
    OVERVIEW = "overview"


class RatingType(enum.Enum):
    """Types of ratings"""

    RELEVANCE = "relevance"  # Thumbs up/down
    QUALITY = "quality"  # 1-5 stars


class SubscriptionType(enum.Enum):
    """Types of subscriptions"""

    SEARCH = "search"
    TOPIC = "topic"


class SubscriptionStatus(enum.Enum):
    """Status of subscriptions"""

    ACTIVE = "active"
    PAUSED = "paused"
    EXPIRED = "expired"
    ERROR = "error"


class NewsSubscription(Base):
    """User's news subscriptions"""

    __tablename__ = "news_subscriptions"

    id = Column(String(50), primary_key=True)

    # Subscription details
    name = Column(String(255))  # Optional friendly name
    subscription_type = Column(
        String(20), nullable=False
    )  # 'search' or 'topic'
    query_or_topic = Column(Text, nullable=False)
    refresh_interval_minutes = Column(
        Integer, default=1440
    )  # Default 24 hours = 1440 minutes
    frequency = Column(
        String(50), default="daily"
    )  # daily, weekly, hourly, etc.

    # Timing
    created_at = Column(UtcDateTime, default=utcnow())
    updated_at = Column(
        UtcDateTime,
        default=utcnow(),
        onupdate=utcnow(),
    )
    last_refresh = Column(UtcDateTime)
    next_refresh = Column(UtcDateTime)
    expires_at = Column(UtcDateTime)  # Optional expiration

    # Source tracking
    source_type = Column(String(50))  # 'manual', 'research', 'news_topic'
    source_id = Column(String(100))  # ID of source (research_id, news_id)
    created_from = Column(Text)  # Description of source

    # Organization
    folder = Column(String(100))  # Folder name
    folder_id = Column(String(36))  # Folder ID
    notes = Column(Text)  # User notes

    # Model configuration
    model_provider = Column(String(50))  # OLLAMA, OPENAI, ANTHROPIC, etc.
    model = Column(String(100))  # Specific model name
    search_strategy = Column(String(50))  # Strategy for searches
    custom_endpoint = Column(String(255))  # Custom API endpoint if used

    # Search configuration
    search_engine = Column(String(50))  # Search engine to use
    search_iterations = Column(
        Integer, default=3
    )  # Number of search iterations
    questions_per_iteration = Column(
        Integer, default=5
    )  # Questions per iteration

    # State
    # `status` is the single source of truth for whether a subscription is
    # switched on. create_subscription() and update_subscription() write only
    # status; scheduling decisions (active_filter / due_filter below) key off
    # it exclusively.
    status = Column(String(20), default="active")
    # Legacy mirror of `status == "active"`. It is NOT kept in sync on the
    # create/update paths (create_subscription leaves it at the default True
    # even for paused subscriptions), so it must never be used to decide
    # whether to run a subscription — use status / the filters below instead.
    # Retained only for backwards-compatible serialization; not dropped because
    # this model lives in per-user encrypted databases where a column migration
    # is costly and risky.
    is_active = Column(Boolean, default=True)  # Legacy mirror; see above
    error_count = Column(Integer, default=0)
    last_error = Column(Text)

    # Additional data
    extra_data = Column(JSON)  # Additional flexible data

    @classmethod
    def active_filter(cls):
        """SQLAlchemy predicate for subscriptions that are switched on.

        Single authoritative definition of "active", keyed on the `status`
        column (see the column comments above). Use this instead of
        re-spelling ``status == "active"`` or filtering on ``is_active``.
        """
        return cls.status == SubscriptionStatus.ACTIVE.value

    @classmethod
    def due_filter(cls, now):
        """SQLAlchemy predicate for active subscriptions whose run is due.

        ``now`` should be a timezone-aware UTC datetime. The ``next_refresh
        is not None`` guard matters: a freshly created subscription always has
        next_refresh set, but defending against NULL keeps a stray NULL row
        from ever being treated as "infinitely overdue".
        """
        return and_(
            cls.active_filter(),
            cls.next_refresh.is_not(None),
            cls.next_refresh <= now,
        )


class SubscriptionFolder(Base):
    """Folders for organizing subscriptions"""

    __tablename__ = "subscription_folders"

    id = Column(String(36), primary_key=True)  # UUID
    name = Column(String(100), nullable=False)
    description = Column(Text)
    color = Column(String(7))  # Hex color
    icon = Column(String(50))  # Icon identifier

    # Timestamps
    created_at = Column(UtcDateTime, default=utcnow())
    updated_at = Column(
        UtcDateTime,
        default=utcnow(),
        onupdate=utcnow(),
    )

    # Settings
    is_default = Column(Boolean, default=False)
    sort_order = Column(Integer, default=0)

    def to_dict(self):
        """Convert folder to dictionary."""
        return {
            "id": self.id,
            "name": self.name,
            "description": self.description,
            "color": self.color,
            "icon": self.icon,
            "created_at": self.created_at.isoformat()
            if self.created_at
            else None,
            "updated_at": self.updated_at.isoformat()
            if self.updated_at
            else None,
            "is_default": self.is_default,
            "sort_order": self.sort_order,
        }


class NewsCard(Base):
    """Individual news cards/items"""

    __tablename__ = "news_cards"

    id = Column(String(50), primary_key=True)

    # Content
    title = Column(String(500), nullable=False)
    summary = Column(Text)
    content = Column(Text)
    url = Column(String(1000))

    # Source info
    source_name = Column(String(200))
    source_type = Column(String(50))  # 'research', 'rss', 'api', etc.
    source_id = Column(String(100))  # ID in source system

    # Categorization
    category = Column(String(100))
    tags = Column(JSON)  # List of tags
    card_type = Column(Enum(CardType), default=CardType.NEWS)

    # Timing
    published_at = Column(UtcDateTime)
    discovered_at = Column(UtcDateTime, default=utcnow())

    # Interaction tracking
    is_read = Column(Boolean, default=False)
    read_at = Column(UtcDateTime)
    is_saved = Column(Boolean, default=False)
    saved_at = Column(UtcDateTime)

    # Metadata
    extra_data = Column(JSON)  # Flexible additional data

    # Subscription link
    subscription_id = Column(String(50), ForeignKey("news_subscriptions.id"))


class UserRating(Base):
    """User ratings/feedback on news items"""

    __tablename__ = "news_user_ratings"

    id = Column(Integer, primary_key=True)

    # What was rated
    card_id = Column(String(50), ForeignKey("news_cards.id"), nullable=False)
    rating_type = Column(Enum(RatingType), nullable=False)

    # Rating value
    rating_value = Column(String(20))  # 'up', 'down', or numeric

    # When
    created_at = Column(UtcDateTime, default=utcnow())

    # Optional feedback
    comment = Column(Text)
    tags = Column(JSON)  # User-applied tags


class UserPreference(Base):
    """User preferences for news"""

    __tablename__ = "news_user_preferences"

    id = Column(Integer, primary_key=True)

    # Preference key-value pairs
    key = Column(String(100), nullable=False, unique=True)
    value = Column(JSON)

    # Metadata
    created_at = Column(UtcDateTime, default=utcnow())
    updated_at = Column(
        UtcDateTime,
        default=utcnow(),
        onupdate=utcnow(),
    )


class NewsInterest(Base):
    """User's declared interests for news"""

    __tablename__ = "news_interests"

    id = Column(Integer, primary_key=True)

    # Interest details
    topic = Column(String(200), nullable=False)
    interest_type = Column(String(50))  # 'positive', 'negative', 'keyword'
    strength = Column(Integer, default=5)  # 1-10 scale

    # Timing
    created_at = Column(UtcDateTime, default=utcnow())
    expires_at = Column(UtcDateTime)  # Optional expiration

    # Source
    source = Column(String(50))  # 'manual', 'inferred', 'imported'
    source_id = Column(String(100))
