"""
News routes backed by separate news database (ashlya_a_news)
"""
import logging
from flask import Blueprint, request, jsonify, send_file
import os
import mimetypes
from utils import require_api_key, format_api_response, create_error_response, format_not_found_error
from news_database import ManagedNewsDB

logger = logging.getLogger(__name__)

news_bp = Blueprint('news', __name__)


def _row_to_article(row):
    return {
        'id': row.get('id'),
        'article_id': row.get('article_id'),
        'title': row.get('title'),
        'summary': row.get('summary'),
        'source': row.get('source'),
        'source_url': row.get('source_url'),
        'category': row.get('category'),
        'published_time': row.get('published_time').isoformat() if row.get('published_time') else None,
        'scraped_at': row.get('scraped_at').isoformat() if row.get('scraped_at') else None,
        'url': row.get('url'),
        'hash': row.get('hash'),
        'views': row.get('views'),
        'image_path': row.get('image_path'),
        'image_url': f"/api/news/image/{row.get('id')}" if row.get('id') else None,
        'is_rewritten': row.get('is_rewritten'),
        'rewrite_strategy': row.get('rewrite_strategy'),
    }


@news_bp.route('/api/news/latest', methods=['GET'])
@require_api_key
def latest_news():
    try:
        limit = request.args.get('limit', default=12, type=int)
        offset = request.args.get('offset', default=0, type=int)
        category = request.args.get('category')
        category_like = request.args.get('category_like')
        source = request.args.get('source')
        with ManagedNewsDB() as conn:
            cursor = conn.cursor(dictionary=True)
            base = "SELECT id, article_id, title, summary, source, source_url, category, published_time, scraped_at, url, `hash`, views, image_path, is_rewritten, rewrite_strategy FROM news_articles"
            where = []
            params = []
            if category:
                where.append("category = %s")
                params.append(category)
            if category_like:
                where.append("LOWER(category) LIKE %s")
                params.append(f"%{category_like.lower()}%")
            if source:
                where.append("source = %s")
                params.append(source)
            if where:
                base += " WHERE " + " AND ".join(where)
            base += " ORDER BY published_time DESC, scraped_at DESC LIMIT %s OFFSET %s"
            params.extend([limit, offset])
            cursor.execute(base, tuple(params))
            rows = cursor.fetchall() or []
            articles = [_row_to_article(r) for r in rows]
            cursor.close()
            return format_api_response({'items': articles, 'count': len(articles)}, include_legacy_fields={'items': articles, 'count': len(articles)})
    except Exception as e:
        logger.error(f"/api/news/latest error: {e}")
        return create_error_response(
            "Failed to retrieve news articles",
            status_code=500,
            error_code="NEWS_FETCH_ERROR",
            error_details=str(e) if logger.level == logging.DEBUG else None
        )


@news_bp.route('/api/news/categories', methods=['GET'])
@require_api_key
def news_categories():
    try:
        with ManagedNewsDB() as conn:
            cursor = conn.cursor(dictionary=True)
            cursor.execute("SELECT id, name, nepali_name, article_count FROM news_categories ORDER BY article_count DESC, name ASC")
            cats = cursor.fetchall() or []
            cursor.close()
            return format_api_response(cats, include_legacy_fields=cats)
    except Exception as e:
        logger.error(f"/api/news/categories error: {e}")
        return create_error_response(
            "Failed to retrieve news categories",
            status_code=500,
            error_code="NEWS_CATEGORIES_ERROR"
        )


@news_bp.route('/api/news/sources', methods=['GET'])
@require_api_key
def news_sources():
    try:
        with ManagedNewsDB() as conn:
            cursor = conn.cursor(dictionary=True)
            cursor.execute("SELECT id, domain, name, reliability_score, is_active, article_count, last_scraped FROM news_sources ORDER BY article_count DESC")
            srcs = cursor.fetchall() or []
            for s in srcs:
                if s.get('last_scraped'):
                    s['last_scraped'] = s['last_scraped'].isoformat()
            cursor.close()
            return format_api_response(srcs, include_legacy_fields=srcs)
    except Exception as e:
        logger.error(f"/api/news/sources error: {e}")
        return create_error_response(
            "Failed to retrieve news sources",
            status_code=500,
            error_code="NEWS_SOURCES_ERROR"
        )


# Public image proxy by article id (no API key to allow <img> usage)
@news_bp.route('/api/news/image/<int:article_id>', methods=['GET'])
def news_image(article_id: int):
    try:
        with ManagedNewsDB() as conn:
            cursor = conn.cursor(dictionary=True)
            cursor.execute("SELECT image_path FROM news_articles WHERE id = %s", (article_id,))
            row = cursor.fetchone()
            cursor.close()
            if not row or not row.get('image_path'):
                return jsonify({'success': False, 'message': 'Image not found'}), 404
            image_path = row['image_path']
            if not os.path.exists(image_path):
                return jsonify({'success': False, 'message': 'File not found on server'}), 404
            mime, _ = mimetypes.guess_type(image_path)
            return send_file(image_path, mimetype=mime or 'image/jpeg')
    except Exception as e:
        logger.error(f"/api/news/image/{article_id} error: {e}")
        return create_error_response(
            "Failed to retrieve news image",
            status_code=500,
            error_code="NEWS_IMAGE_ERROR"
        )


@news_bp.route('/api/news/article/<int:article_id>', methods=['GET'])
@require_api_key
def news_article_detail(article_id: int):
    try:
        with ManagedNewsDB() as conn:
            cursor = conn.cursor(dictionary=True)
            cursor.execute(
                """
                SELECT id, article_id, title, summary, content, original_content, source, source_url, category,
                       published_time, scraped_at, url, `hash`, views, image_path, is_rewritten, rewrite_strategy
                FROM news_articles WHERE id = %s
                """,
                (article_id,)
            )
            row = cursor.fetchone()
            cursor.close()
            if not row:
                return format_not_found_error("Article", article_id)
            article = _row_to_article(row)
            article['content'] = row.get('content') or row.get('original_content') or row.get('summary')
            article['external_url'] = row.get('source_url') or row.get('url')
            return format_api_response(article, include_legacy_fields=article)
    except Exception as e:
        logger.error(f"/api/news/article/{article_id} error: {e}")
        return create_error_response(
            "Failed to retrieve article details",
            status_code=500,
            error_code="NEWS_ARTICLE_ERROR"
        )


@news_bp.route('/api/news/student-categories', methods=['GET'])
@require_api_key
def student_categories():
    """Return student-focused categories aggregated from available articles.

    This endpoint maps various source categories to a canonical set oriented to students.
    """
    try:
        canonical = [
            { 'slug': 'education', 'name': 'Education', 'keywords': ['educat', 'school', 'college', 'university'] },
            { 'slug': 'scholarships', 'name': 'Scholarships', 'keywords': ['scholar', 'fellowship', 'grant'] },
            { 'slug': 'exams', 'name': 'Exams', 'keywords': ['exam', 'entrance', 'test', 'board'] },
            { 'slug': 'results', 'name': 'Results', 'keywords': ['result', 'score', 'merit', 'rank'] },
            { 'slug': 'careers', 'name': 'Careers', 'keywords': ['career', 'placement', 'recruit'] },
            { 'slug': 'jobs', 'name': 'Jobs & Internships', 'keywords': ['job', 'intern'] },
            { 'slug': 'technology', 'name': 'Technology', 'keywords': ['tech', 'technology', 'ai', 'software'] },
            { 'slug': 'campus', 'name': 'Campus', 'keywords': ['campus', 'event', 'fest', 'orientation'] },
            { 'slug': 'study', 'name': 'Study Tips', 'keywords': ['study', 'learning', 'productivity'] },
        ]

        with ManagedNewsDB() as conn:
            cursor = conn.cursor(dictionary=True)
            cursor.execute("SELECT category, COUNT(*) as cnt FROM news_articles WHERE category IS NOT NULL AND category <> '' GROUP BY category")
            rows = cursor.fetchall() or []
            cursor.close()

        # Aggregate counts into canonical buckets
        for c in canonical:
            c['count'] = 0
        for r in rows:
            cat = (r.get('category') or '').lower()
            cnt = int(r.get('cnt') or 0)
            for c in canonical:
                if any(k in cat for k in c['keywords']):
                    c['count'] += cnt
                    break

        # Only return categories that have some presence; keep at least Education
        filtered = [c for c in canonical if c.get('count', 0) > 0] or [canonical[0]]
        return format_api_response(filtered, include_legacy_fields=filtered)
    except Exception as e:
        logger.error(f"/api/news/student-categories error: {e}")
        return create_error_response(
            "Failed to retrieve student categories",
            status_code=500,
            error_code="STUDENT_CATEGORIES_ERROR"
        )


