"""PEJ (nuclear energy) section routes — filtered lens on ZOPK data."""

import math

from flask import render_template, request, abort
from flask_login import login_required
from sqlalchemy import func
from sqlalchemy.dialects.postgresql import array as pg_array

from . import bp
from database import (
    SessionLocal, ZOPKNews, ZOPKMilestone,
    ZOPKCompanyLink, Company, Announcement, Category
)
from blueprints.pej_constants import get_nuclear_project_ids, LINK_TYPE_LABELS


@bp.route('/pej')
@login_required
def pej_index():
    """PEJ landing page — hero, stats, news, timeline, top companies, announcements."""
    db = SessionLocal()
    try:
        nuclear_ids = get_nuclear_project_ids(db)
        if not nuclear_ids:
            abort(404)

        # Stats
        companies_count = db.query(func.count(ZOPKCompanyLink.id)).filter(
            ZOPKCompanyLink.project_id.in_(nuclear_ids),
            ZOPKCompanyLink.relevance_score >= 25
        ).scalar() or 0

        news_count = db.query(func.count(ZOPKNews.id)).filter(
            ZOPKNews.project_id.in_(nuclear_ids),
            ZOPKNews.status.in_(['approved', 'auto_approved'])
        ).scalar() or 0

        milestones_count = db.query(func.count(ZOPKMilestone.id)).filter(
            ZOPKMilestone.category == 'nuclear'
        ).scalar() or 0

        # Latest news (4)
        news = db.query(ZOPKNews).filter(
            ZOPKNews.project_id.in_(nuclear_ids),
            ZOPKNews.status.in_(['approved', 'auto_approved'])
        ).order_by(ZOPKNews.published_at.desc()).limit(4).all()

        # Nuclear milestones
        milestones = db.query(ZOPKMilestone).filter(
            ZOPKMilestone.category == 'nuclear'
        ).order_by(ZOPKMilestone.target_date.asc()).all()

        # Top 6 companies by relevance
        top_companies = db.query(ZOPKCompanyLink, Company).join(
            Company, ZOPKCompanyLink.company_id == Company.id
        ).filter(
            ZOPKCompanyLink.project_id.in_(nuclear_ids),
            ZOPKCompanyLink.relevance_score >= 25,
            Company.status == 'active'
        ).order_by(ZOPKCompanyLink.relevance_score.desc()).limit(6).all()

        # PEJ announcements (status='published' in Announcement model)
        announcements = db.query(Announcement).filter(
            Announcement.categories.op('@>')(pg_array(['pej'])),
            Announcement.status == 'published'
        ).order_by(Announcement.created_at.desc()).limit(3).all()

        return render_template('pej/index.html',
            companies_count=companies_count,
            news_count=news_count,
            milestones_count=milestones_count,
            news=news,
            milestones=milestones,
            top_companies=top_companies,
            announcements=announcements,
            link_type_labels=LINK_TYPE_LABELS
        )
    finally:
        db.close()


@bp.route('/pej/local-content')
@login_required
def pej_local_content():
    """Full list of Norda companies matched to nuclear projects."""
    db = SessionLocal()
    try:
        nuclear_ids = get_nuclear_project_ids(db)
        if not nuclear_ids:
            abort(404)

        page = request.args.get('page', 1, type=int)
        per_page = 20
        category_filter = request.args.get('category', 0, type=int)
        link_type_filter = request.args.get('link_type', '')
        search_query = request.args.get('q', '')

        query = db.query(ZOPKCompanyLink, Company).join(
            Company, ZOPKCompanyLink.company_id == Company.id
        ).filter(
            ZOPKCompanyLink.project_id.in_(nuclear_ids),
            ZOPKCompanyLink.relevance_score >= 25,
            Company.status == 'active'
        )

        if category_filter:
            query = query.filter(Company.category_id == category_filter)
        if link_type_filter:
            query = query.filter(ZOPKCompanyLink.link_type == link_type_filter)
        if search_query:
            query = query.filter(Company.name.ilike(f'%{search_query}%'))

        total = query.count()
        results = query.order_by(
            ZOPKCompanyLink.relevance_score.desc()
        ).offset((page - 1) * per_page).limit(per_page).all()

        # Get distinct categories for filter dropdown
        category_ids = db.query(Company.category_id).join(
            ZOPKCompanyLink, Company.id == ZOPKCompanyLink.company_id
        ).filter(
            ZOPKCompanyLink.project_id.in_(nuclear_ids),
            ZOPKCompanyLink.relevance_score >= 25,
            Company.status == 'active',
            Company.category_id.isnot(None)
        ).distinct().all()
        category_ids = [c[0] for c in category_ids if c[0]]
        categories = db.query(Category).filter(
            Category.id.in_(category_ids)
        ).order_by(Category.name).all() if category_ids else []

        link_types = db.query(ZOPKCompanyLink.link_type).filter(
            ZOPKCompanyLink.project_id.in_(nuclear_ids),
            ZOPKCompanyLink.relevance_score >= 25
        ).distinct().all()
        link_types = sorted([lt[0] for lt in link_types if lt[0]])

        total_pages = math.ceil(total / per_page) if total > 0 else 1

        return render_template('pej/local_content.html',
            results=results,
            total=total,
            page=page,
            per_page=per_page,
            total_pages=total_pages,
            categories=categories,
            link_types=link_types,
            link_type_labels=LINK_TYPE_LABELS,
            category_filter=category_filter,
            link_type_filter=link_type_filter,
            search_query=search_query
        )
    finally:
        db.close()


@bp.route('/pej/aktualnosci')
@login_required
def pej_news():
    """Nuclear news list with pagination."""
    db = SessionLocal()
    try:
        nuclear_ids = get_nuclear_project_ids(db)
        if not nuclear_ids:
            abort(404)

        page = request.args.get('page', 1, type=int)
        per_page = 20

        query = db.query(ZOPKNews).filter(
            ZOPKNews.project_id.in_(nuclear_ids),
            ZOPKNews.status.in_(['approved', 'auto_approved'])
        ).order_by(ZOPKNews.published_at.desc())

        total = query.count()
        news = query.offset((page - 1) * per_page).limit(per_page).all()

        total_pages = math.ceil(total / per_page) if total > 0 else 1

        return render_template('pej/news.html',
            news=news,
            page=page,
            total=total,
            total_pages=total_pages
        )
    finally:
        db.close()