3. Phase 1: core implementation

-- Users Table: OAuth authentication and user accounts
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    reddit_id VARCHAR(100) UNIQUE,          -- Reddit user ID from OAuth
    username VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP
);

CREATE INDEX idx_users_reddit_id ON users(reddit_id);

-- Articles Table: Aggregated news from all sources
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    source VARCHAR(50) NOT NULL,            -- 'newsapi', 'guardian', 'reddit'
    external_id VARCHAR(255) NOT NULL,      -- Source's article ID
    title TEXT NOT NULL,
    description TEXT,
    content TEXT,
    author VARCHAR(255),
    url TEXT NOT NULL,
    image_url TEXT,
    published_at TIMESTAMP NOT NULL,
    fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(source, external_id)             -- Prevent duplicates
);

-- Performance indexes
CREATE INDEX idx_articles_source ON articles(source);
CREATE INDEX idx_articles_published ON articles(published_at DESC);
CREATE INDEX idx_articles_fetched ON articles(fetched_at DESC);

-- Full-text search index
CREATE INDEX idx_articles_title_search ON articles USING GIN(to_tsvector('english', title));
CREATE INDEX idx_articles_content_search ON articles USING GIN(to_tsvector('english', content));

-- User Preferences: Personalization settings
CREATE TABLE user_preferences (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    preferred_sources TEXT[],               -- Array: ['newsapi', 'guardian']
    keywords TEXT[],                        -- Array: ['python', 'ai', 'climate']
    categories TEXT[],                      -- Array: ['technology', 'science']
    notification_enabled BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_preferences_user_id ON user_preferences(user_id);

-- Search History: Track user searches for analytics
CREATE TABLE search_history (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,  -- NULL for anonymous
    query TEXT NOT NULL,
    results_count INTEGER DEFAULT 0,
    searched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_search_history_user_id ON search_history(user_id);
CREATE INDEX idx_search_history_searched_at ON search_history(searched_at DESC);

-- Article Sentiments: Extension feature for sentiment analysis
CREATE TABLE article_sentiments (
    id SERIAL PRIMARY KEY,
    article_id INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    polarity FLOAT NOT NULL,                -- -1.0 (negative) to 1.0 (positive)
    subjectivity FLOAT NOT NULL,            -- 0.0 (objective) to 1.0 (subjective)
    sentiment_label VARCHAR(20),            -- 'positive', 'negative', 'neutral'
    analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(article_id)
);

CREATE INDEX idx_article_sentiments_article_id ON article_sentiments(article_id);
CREATE INDEX idx_article_sentiments_label ON article_sentiments(sentiment_label);

-- Relationships Summary:
-- users (1) ←→ (many) user_preferences
-- users (1) ←→ (many) search_history
-- articles (1) ←→ (1) article_sentiments

"""
NewsAPI client for fetching articles.
API Documentation: https://newsapi.org/docs/endpoints/everything
"""
import httpx
from typing import List, Optional
from app.config import settings


class NewsAPIClient:
    """Client for NewsAPI.org external API."""

    BASE_URL = "https://newsapi.org/v2"

    def __init__(self, api_key: str):
        self.api_key = api_key
        self.client = httpx.AsyncClient(timeout=30.0)

    async def search_articles(
        self, 
        query: str, 
        language: str = "en",
        sort_by: str = "relevancy",
        page_size: int = 20
    ) -> List[dict]:
        """
        Search articles using NewsAPI everything endpoint.

        Args:
            query: Search keywords
            language: Two-letter language code
            sort_by: 'relevancy', 'popularity', or 'publishedAt'
            page_size: Results per page (max 100)

        Returns:
            List of article dictionaries in standardized format

        Raises:
            httpx.HTTPError: On request failure
        """
        url = f"{self.BASE_URL}/everything"
        params = {
            "q": query,
            "language": language,
            "sortBy": sort_by,
            "pageSize": min(page_size, 100),  # API maximum is 100
            "apiKey": self.api_key
        }

        response = await self.client.get(url, params=params)
        response.raise_for_status()  # Raises exception for 4xx/5xx

        data = response.json()

        # Check API-specific error format
        if data.get("status") != "ok":
            raise Exception(f"NewsAPI error: {data.get('message', 'Unknown error')}")

        # Transform to standardized format
        articles = []
        for article in data.get("articles", []):
            articles.append({
                "title": article.get("title"),
                "description": article.get("description"),
                "url": article.get("url"),
                "source": "newsapi",
                "source_name": article.get("source", {}).get("name"),
                "author": article.get("author"),
                "published_at": article.get("publishedAt"),
                "image_url": article.get("urlToImage"),
                "content": article.get("content")
            })

        return articles

    async def close(self):
        """Close the HTTP client."""
        await self.client.aclose()

"""
Guardian API client for fetching articles.
API Documentation: https://open-platform.theguardian.com/documentation/
"""
import httpx
from typing import List, Optional
from datetime import datetime


class GuardianAPIClient:
    """Client for The Guardian Open Platform API."""

    BASE_URL = "https://content.guardianapis.com"

    def __init__(self, api_key: str):
        self.api_key = api_key
        self.client = httpx.AsyncClient(timeout=30.0)

    async def search_articles(
        self,
        query: str,
        page_size: int = 20,
        order_by: str = "relevance"
    ) -> List[dict]:
        """
        Search articles using Guardian content search endpoint.

        Args:
            query: Search keywords
            page_size: Results per page (max 50)
            order_by: 'newest', 'oldest', or 'relevance'

        Returns:
            List of article dictionaries in standardized format
        """
        url = f"{self.BASE_URL}/search"
        params = {
            "q": query,
            "page-size": min(page_size, 50),  # Guardian's max
            "order-by": order_by,
            "show-fields": "bodyText,thumbnail,byline",  # Request extra fields
            "api-key": self.api_key
        }

        response = await self.client.get(url, params=params)
        response.raise_for_status()

        data = response.json()

        # Check Guardian-specific response format
        if data.get("response", {}).get("status") != "ok":
            raise Exception(f"Guardian API error: {data.get('message', 'Unknown error')}")

        # Transform to standardized format
        articles = []
        for article in data.get("response", {}).get("results", []):
            fields = article.get("fields", {})

            articles.append({
                "title": article.get("webTitle"),
                "description": self._truncate_text(fields.get("bodyText", ""), 300),
                "url": article.get("webUrl"),
                "source": "guardian",
                "source_name": "The Guardian",
                "author": fields.get("byline"),
                "published_at": article.get("webPublicationDate"),
                "image_url": fields.get("thumbnail"),
                "content": fields.get("bodyText")
            })

        return articles

    def _truncate_text(self, text: str, max_length: int) -> str:
        """Truncate text to max_length, ending at word boundary."""
        if len(text) <= max_length:
            return text

        truncated = text[:max_length]
        # Find last space to avoid cutting mid-word
        last_space = truncated.rfind(' ')
        if last_space > 0:
            truncated = truncated[:last_space]

        return truncated + "..."

    async def close(self):
        """Close the HTTP client."""
        await self.client.aclose()

"""
Reddit API client with OAuth 2.0 authentication.
API Documentation: https://www.reddit.com/dev/api/
OAuth Documentation: https://github.com/reddit-archive/reddit/wiki/OAuth2
"""
import httpx
import base64
from typing import List, Optional, Dict
from datetime import datetime, timedelta


class RedditAPIClient:
    """Client for Reddit API with OAuth 2.0 support."""

    BASE_URL = "https://oauth.reddit.com"
    AUTH_URL = "https://www.reddit.com/api/v1/authorize"
    TOKEN_URL = "https://www.reddit.com/api/v1/access_token"

    def __init__(self, client_id: str, client_secret: str, redirect_uri: str):
        self.client_id = client_id
        self.client_secret = client_secret
        self.redirect_uri = redirect_uri
        self.client = httpx.AsyncClient(timeout=30.0)

    def get_authorization_url(self, state: str) -> str:
        """
        Generate OAuth authorization URL for user to visit.

        Args:
            state: Random string for CSRF protection (validate on callback)

        Returns:
            URL to redirect user to for Reddit authorization
        """
        params = {
            "client_id": self.client_id,
            "response_type": "code",
            "state": state,
            "redirect_uri": self.redirect_uri,
            "duration": "permanent",  # Request refresh token
            "scope": "identity read"  # Permissions we need
        }

        # Build query string manually for proper encoding
        query = "&".join(f"{k}={v}" for k, v in params.items())
        return f"{self.AUTH_URL}?{query}"

    async def exchange_code_for_token(self, code: str) -> Dict[str, any]:
        """
        Exchange authorization code for access token.

        Args:
            code: Authorization code from OAuth callback

        Returns:
            Dictionary with 'access_token', 'refresh_token', 'expires_in'

        Raises:
            Exception: If token exchange fails
        """
        # Reddit requires Basic auth with client credentials
        auth_string = f"{self.client_id}:{self.client_secret}"
        auth_bytes = auth_string.encode('utf-8')
        auth_b64 = base64.b64encode(auth_bytes).decode('utf-8')

        headers = {
            "Authorization": f"Basic {auth_b64}",
            "User-Agent": "NewsIntelligencePlatform/1.0"  # Required by Reddit
        }

        data = {
            "grant_type": "authorization_code",
            "code": code,
            "redirect_uri": self.redirect_uri
        }

        response = await self.client.post(
            self.TOKEN_URL,
            headers=headers,
            data=data
        )
        response.raise_for_status()

        token_data = response.json()

        # Calculate expiration time
        expires_in = token_data.get("expires_in", 3600)  # Usually 1 hour
        token_data["expires_at"] = datetime.utcnow() + timedelta(seconds=expires_in)

        return token_data

    async def refresh_access_token(self, refresh_token: str) -> Dict[str, any]:
        """
        Use refresh token to get new access token.

        Args:
            refresh_token: Refresh token from initial authorization

        Returns:
            Dictionary with new 'access_token' and 'expires_in'
        """
        auth_string = f"{self.client_id}:{self.client_secret}"
        auth_bytes = auth_string.encode('utf-8')
        auth_b64 = base64.b64encode(auth_bytes).decode('utf-8')

        headers = {
            "Authorization": f"Basic {auth_b64}",
            "User-Agent": "NewsIntelligencePlatform/1.0"
        }

        data = {
            "grant_type": "refresh_token",
            "refresh_token": refresh_token
        }

        response = await self.client.post(
            self.TOKEN_URL,
            headers=headers,
            data=data
        )
        response.raise_for_status()

        token_data = response.json()
        expires_in = token_data.get("expires_in", 3600)
        token_data["expires_at"] = datetime.utcnow() + timedelta(seconds=expires_in)

        return token_data

    async def search_subreddit(
        self,
        access_token: str,
        subreddit: str = "news",
        query: str = "",
        limit: int = 20
    ) -> List[dict]:
        """
        Search a subreddit for posts matching query.

        Args:
            access_token: Valid OAuth access token
            subreddit: Subreddit to search (without r/ prefix)
            query: Search keywords
            limit: Number of results (max 100)

        Returns:
            List of post dictionaries in standardized format
        """
        url = f"{self.BASE_URL}/r/{subreddit}/search"

        headers = {
            "Authorization": f"Bearer {access_token}",
            "User-Agent": "NewsIntelligencePlatform/1.0"
        }

        params = {
            "q": query,
            "limit": min(limit, 100),
            "sort": "relevance",
            "restrict_sr": "true",  # Search only this subreddit
            "type": "link"  # Only link posts, not text posts
        }

        response = await self.client.get(url, headers=headers, params=params)
        response.raise_for_status()

        data = response.json()

        # Transform Reddit's complex structure
        articles = []
        for child in data.get("data", {}).get("children", []):
            post = child.get("data", {})

            # Skip if not a valid article
            if post.get("is_self") or not post.get("url"):
                continue

            articles.append({
                "title": post.get("title"),
                "description": post.get("selftext", "")[:300] or None,
                "url": post.get("url"),
                "source": "reddit",
                "source_name": f"r/{subreddit}",
                "author": f"u/{post.get('author')}",
                "published_at": datetime.fromtimestamp(post.get("created_utc", 0)).isoformat(),
                "image_url": post.get("thumbnail") if post.get("thumbnail", "").startswith("http") else None,
                "content": None  # Reddit doesn't provide full content
            })

        return articles

    async def close(self):
        """Close the HTTP client."""
        await self.client.aclose()