#!/usr/bin/env python3
"""
Channel Configuration Module

Handles loading, validating, and accessing channel configurations.
Single source of truth for all channel-related operations.
"""

import sqlite3
from dataclasses import dataclass, field
from pathlib import Path
from typing import Dict, List, Optional, Any
import yaml


CONFIG_PATH = Path(__file__).parent / "channels.yaml"
DB_PATH = Path(__file__).parent / "output" / "trends_history.db"


@dataclass
class RedditSource:
    """A Reddit subreddit source."""
    name: str
    priority: str = "secondary"  # primary or secondary
    subscribers: int = 0


@dataclass
class RSSSource:
    """An RSS feed source."""
    name: str
    url: str


@dataclass
class ChannelSources:
    """Sources configuration for a channel."""
    reddit: List[RedditSource] = field(default_factory=list)
    rss: List[RSSSource] = field(default_factory=list)
    youtube_categories: List[int] = field(default_factory=list)

    def get_reddit_names(self, priority: str = None) -> List[str]:
        """Get subreddit names, optionally filtered by priority."""
        if priority:
            return [r.name for r in self.reddit if r.priority == priority]
        return [r.name for r in self.reddit]

    def get_rss_urls(self) -> List[str]:
        """Get all RSS feed URLs."""
        return [r.url for r in self.rss]


@dataclass
class ChannelKeywords:
    """Keyword filters for a channel."""
    include: List[str] = field(default_factory=list)
    exclude: List[str] = field(default_factory=list)


@dataclass
class Channel:
    """Represents a single channel configuration."""
    id: str
    name: str
    short_name: str
    description: str
    color: str
    enabled: bool
    sources: ChannelSources
    keywords: ChannelKeywords
    curiosity_queries: List[str]
    scoring_weights: Dict[str, float]
    competitive_gap: str = ""
    moderation_risk: str = ""
    risk_notes: str = ""
    itch_description: str = ""
    format_description: str = ""
    hook_criteria: str = ""

    @classmethod
    def from_dict(cls, channel_id: str, data: Dict) -> "Channel":
        """Create a Channel from a dictionary."""
        sources_data = data.get("sources", {})

        # Parse reddit sources (can be list of strings or list of dicts)
        reddit_sources = []
        for r in sources_data.get("reddit", []):
            if isinstance(r, str):
                reddit_sources.append(RedditSource(name=r))
            elif isinstance(r, dict):
                reddit_sources.append(RedditSource(
                    name=r.get("name", ""),
                    priority=r.get("priority", "secondary"),
                    subscribers=r.get("subscribers", 0)
                ))

        # Parse RSS sources (can be list of strings or list of dicts)
        rss_sources = []
        for r in sources_data.get("rss", []):
            if isinstance(r, str):
                rss_sources.append(RSSSource(name=r, url=r))
            elif isinstance(r, dict):
                rss_sources.append(RSSSource(
                    name=r.get("name", ""),
                    url=r.get("url", "")
                ))

        sources = ChannelSources(
            reddit=reddit_sources,
            rss=rss_sources,
            youtube_categories=sources_data.get("youtube_categories", [])
        )

        keywords_data = data.get("keywords", {})
        keywords = ChannelKeywords(
            include=keywords_data.get("include", []),
            exclude=keywords_data.get("exclude", [])
        )

        return cls(
            id=channel_id,
            name=data.get("name", channel_id),
            short_name=data.get("short_name", channel_id),
            description=data.get("description", ""),
            color=data.get("color", "#666666"),
            enabled=data.get("enabled", True),
            sources=sources,
            keywords=keywords,
            curiosity_queries=data.get("curiosity_queries", []),
            scoring_weights=data.get("scoring_weights", {}),
            competitive_gap=data.get("competitive_gap", ""),
            moderation_risk=data.get("moderation_risk", ""),
            risk_notes=data.get("risk_notes", ""),
            itch_description=data.get("itch", ""),
            format_description=data.get("format", ""),
            hook_criteria=data.get("hook_criteria", "")
        )

    def to_dict(self) -> Dict:
        """Convert to dictionary for JSON serialization."""
        return {
            "id": self.id,
            "name": self.name,
            "short_name": self.short_name,
            "description": self.description,
            "color": self.color,
            "enabled": self.enabled,
            "sources": {
                "reddit": [{"name": r.name, "priority": r.priority, "subscribers": r.subscribers}
                          for r in self.sources.reddit],
                "rss": [{"name": r.name, "url": r.url} for r in self.sources.rss],
                "youtube_categories": self.sources.youtube_categories
            },
            "keywords": {
                "include": self.keywords.include,
                "exclude": self.keywords.exclude
            },
            "curiosity_queries": self.curiosity_queries,
            "scoring_weights": self.scoring_weights,
            "competitive_gap": self.competitive_gap,
            "moderation_risk": self.moderation_risk,
            "risk_notes": self.risk_notes,
            "itch_description": self.itch_description,
            "format_description": self.format_description,
            "hook_criteria": self.hook_criteria
        }

    def get_all_sources(self) -> List[Dict[str, str]]:
        """Get all sources as a flat list with type labels."""
        sources = []
        for r in self.sources.reddit:
            sources.append({
                "type": "reddit",
                "name": f"r/{r.name}",
                "priority": r.priority,
                "subscribers": r.subscribers
            })
        for r in self.sources.rss:
            sources.append({
                "type": "rss",
                "name": r.name,
                "url": r.url
            })
        for cat_id in self.sources.youtube_categories:
            sources.append({"type": "youtube", "name": f"Category {cat_id}"})
        return sources

    def has_sources(self) -> bool:
        """Check if channel has any sources configured."""
        return bool(
            self.sources.reddit or
            self.sources.rss or
            self.sources.youtube_categories
        )


class ChannelConfigLoader:
    """Loads and manages channel configurations."""

    def __init__(self, config_path: Path = CONFIG_PATH):
        self.config_path = config_path
        self._config: Optional[Dict] = None
        self._channels: Optional[Dict[str, Channel]] = None

    def _load_config(self) -> Dict:
        """Load raw config from YAML file."""
        if self._config is None:
            with open(self.config_path, "r") as f:
                self._config = yaml.safe_load(f)
        return self._config

    def _parse_channels(self) -> Dict[str, Channel]:
        """Parse channel configurations."""
        if self._channels is None:
            config = self._load_config()
            self._channels = {}
            for channel_id, channel_data in config.get("channels", {}).items():
                self._channels[channel_id] = Channel.from_dict(channel_id, channel_data)
        return self._channels

    def get_all_channels(self) -> Dict[str, Channel]:
        """Get all channel configurations."""
        return self._parse_channels()

    def get_enabled_channels(self) -> Dict[str, Channel]:
        """Get only enabled channels."""
        return {
            cid: ch for cid, ch in self._parse_channels().items()
            if ch.enabled
        }

    def get_channel(self, channel_id: str) -> Optional[Channel]:
        """Get a specific channel by ID."""
        return self._parse_channels().get(channel_id)

    def get_settings(self) -> Dict[str, Any]:
        """Get global settings."""
        return self._load_config().get("settings", {})

    def get_default_scoring_weights(self) -> Dict[str, float]:
        """Get default scoring weights."""
        settings = self.get_settings()
        return settings.get("default_scoring_weights", {
            "demand_signal": 1.0,
            "content_gap": 1.0,
            "explainability": 1.0,
            "evergreen_potential": 1.0,
            "audience_breadth": 1.0
        })

    def reload(self):
        """Force reload of configuration."""
        self._config = None
        self._channels = None


# Database operations for channels
def init_channel_tables(db_path: Path = DB_PATH):
    """Initialize channel-related database tables."""
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # Channels table - stores channel metadata synced from YAML
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS channels (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            short_name TEXT,
            description TEXT,
            color TEXT,
            enabled INTEGER DEFAULT 1,
            config_json TEXT,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_at TEXT DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # Item-channel mapping - many-to-many relationship
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS item_channels (
            item_id INTEGER NOT NULL,
            channel_id TEXT NOT NULL,
            confidence REAL DEFAULT 1.0,
            matched_by TEXT,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (item_id, channel_id),
            FOREIGN KEY (item_id) REFERENCES trend_items(id),
            FOREIGN KEY (channel_id) REFERENCES channels(id)
        )
    """)

    # Index for faster lookups
    cursor.execute("""
        CREATE INDEX IF NOT EXISTS idx_item_channels_channel
        ON item_channels(channel_id)
    """)

    cursor.execute("""
        CREATE INDEX IF NOT EXISTS idx_item_channels_item
        ON item_channels(item_id)
    """)

    # Analysis runs per channel
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS channel_analysis_runs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            channel_id TEXT NOT NULL,
            run_date TEXT NOT NULL,
            items_analyzed INTEGER,
            opportunities_found INTEGER,
            status TEXT,
            FOREIGN KEY (channel_id) REFERENCES channels(id)
        )
    """)

    conn.commit()
    conn.close()
    print("Channel tables initialized successfully.")


def sync_channels_to_db(db_path: Path = DB_PATH, config_path: Path = CONFIG_PATH):
    """Sync channel configurations from YAML to database."""
    import json

    loader = ChannelConfigLoader(config_path)
    channels = loader.get_all_channels()

    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    for channel_id, channel in channels.items():
        cursor.execute("""
            INSERT INTO channels (id, name, short_name, description, color, enabled, config_json, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
            ON CONFLICT(id) DO UPDATE SET
                name = excluded.name,
                short_name = excluded.short_name,
                description = excluded.description,
                color = excluded.color,
                enabled = excluded.enabled,
                config_json = excluded.config_json,
                updated_at = CURRENT_TIMESTAMP
        """, (
            channel_id,
            channel.name,
            channel.short_name,
            channel.description,
            channel.color,
            1 if channel.enabled else 0,
            json.dumps(channel.to_dict())
        ))

    conn.commit()
    conn.close()
    print(f"Synced {len(channels)} channels to database.")


def tag_item_to_channel(
    item_id: int,
    channel_id: str,
    confidence: float = 1.0,
    matched_by: str = "manual",
    db_path: Path = DB_PATH
):
    """Tag a trend item to a channel."""
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    cursor.execute("""
        INSERT OR REPLACE INTO item_channels (item_id, channel_id, confidence, matched_by)
        VALUES (?, ?, ?, ?)
    """, (item_id, channel_id, confidence, matched_by))

    conn.commit()
    conn.close()


def get_items_for_channel(channel_id: str, db_path: Path = DB_PATH) -> List[Dict]:
    """Get all trend items tagged to a specific channel."""
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    cursor.execute("""
        SELECT ti.*, ic.confidence, ic.matched_by
        FROM trend_items ti
        JOIN item_channels ic ON ti.id = ic.item_id
        WHERE ic.channel_id = ?
        ORDER BY ic.confidence DESC, ti.final_score DESC
    """, (channel_id,))

    items = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return items


def get_channel_stats(channel_id: str, db_path: Path = DB_PATH) -> Dict:
    """Get statistics for a channel."""
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    cursor.execute("""
        SELECT COUNT(*) as item_count
        FROM item_channels
        WHERE channel_id = ?
    """, (channel_id,))
    item_count = cursor.fetchone()[0]

    cursor.execute("""
        SELECT MAX(run_date) as last_analysis,
               SUM(opportunities_found) as total_opportunities
        FROM channel_analysis_runs
        WHERE channel_id = ?
    """, (channel_id,))
    row = cursor.fetchone()

    conn.close()

    return {
        "channel_id": channel_id,
        "item_count": item_count,
        "last_analysis": row[0] if row else None,
        "total_opportunities": row[1] if row else 0
    }


# Singleton loader instance
_loader: Optional[ChannelConfigLoader] = None


def get_loader() -> ChannelConfigLoader:
    """Get the singleton channel config loader."""
    global _loader
    if _loader is None:
        _loader = ChannelConfigLoader()
    return _loader


def get_channel(channel_id: str) -> Optional[Channel]:
    """Convenience function to get a channel."""
    return get_loader().get_channel(channel_id)


def get_all_channels() -> Dict[str, Channel]:
    """Convenience function to get all channels."""
    return get_loader().get_all_channels()


def get_enabled_channels() -> Dict[str, Channel]:
    """Convenience function to get enabled channels."""
    return get_loader().get_enabled_channels()


if __name__ == "__main__":
    # Initialize database tables
    init_channel_tables()

    # Sync channels from YAML to database
    sync_channels_to_db()

    # Display loaded channels
    print("\nLoaded channels:")
    for channel_id, channel in get_all_channels().items():
        status = "enabled" if channel.enabled else "disabled"
        reddit_count = len(channel.sources.reddit)
        rss_count = len(channel.sources.rss)
        keywords_count = len(channel.keywords.include)
        primary_reddit = len([r for r in channel.sources.reddit if r.priority == "primary"])
        print(f"  {channel_id}: {channel.name} ({status})")
        print(f"    Reddit: {reddit_count} ({primary_reddit} primary) | RSS: {rss_count} | Keywords: {keywords_count}")