nordabiz/blueprints/reports/routes.py

"""
Reports Routes
==============

Business analytics and reporting endpoints.
"""

from datetime import datetime, date
from flask import render_template, request, url_for
from flask_login import login_required
from utils.decorators import member_required, rada_member_required
from sqlalchemy import func
from sqlalchemy.orm import joinedload

from . import bp
from database import SessionLocal, Company, Category, CompanySocialMedia


@bp.route('/', endpoint='reports_index')
@login_required
@member_required
def index():
    """Lista dostępnych raportów."""
    reports = [
        {
            'id': 'staz-czlonkostwa',
            'title': 'Staż członkostwa w Izbie NORDA',
            'description': 'Zestawienie firm według daty przystąpienia do Izby. Pokazuje historię i lojalność członków.',
            'icon': '🏆',
            'url': url_for('.report_membership')
        },
        {
            'id': 'social-media',
            'title': 'Pokrycie Social Media',
            'description': 'Analiza obecności firm w mediach społecznościowych: Facebook, Instagram, LinkedIn, YouTube, TikTok, X.',
            'icon': '📱',
            'url': url_for('.report_social_media')
        },
        {
            'id': 'struktura-branzowa',
            'title': 'Struktura branżowa',
            'description': 'Rozkład firm według kategorii działalności: IT, Budownictwo, Usługi, Produkcja, Handel.',
            'icon': '🏢',
            'url': url_for('.report_categories')
        },
        {
            'id': 'skladki',
            'title': 'Składki członkowskie',
            'description': 'Podsumowanie stanu składek: wykonanie roczne, zaległości, nieprawidłowe kwoty. Raport dla zarządu i Rady Izby.',
            'icon': '💰',
            'url': url_for('.report_fees'),
        },
    ]
    return render_template('reports/index.html', reports=reports)


@bp.route('/staz-czlonkostwa', endpoint='report_membership')
@login_required
@member_required
def membership():
    """Raport: Staż członkostwa w Izbie NORDA."""
    db = SessionLocal()
    try:
        # Firmy z member_since, posortowane od najstarszego
        companies = db.query(Company).filter(
            Company.member_since.isnot(None)
        ).order_by(Company.member_since.asc()).all()

        # Statystyki
        today = date.today()
        stats = {
            'total_with_date': len(companies),
            'total_without_date': db.query(Company).filter(
                Company.member_since.is_(None)
            ).count(),
            'oldest': companies[0] if companies else None,
            'newest': companies[-1] if companies else None,
            'avg_years': sum(
                (today - c.member_since).days / 365.25
                for c in companies
            ) / len(companies) if companies else 0
        }

        # Dodaj obliczony staż do każdej firmy
        for c in companies:
            c.membership_years = int((today - c.member_since).days / 365.25)

        # Dodaj też do oldest i newest
        if stats['oldest']:
            stats['oldest'].membership_years = int((today - stats['oldest'].member_since).days / 365.25)

        return render_template(
            'reports/membership.html',
            companies=companies,
            stats=stats,
            generated_at=datetime.now()
        )
    finally:
        db.close()


@bp.route('/social-media', endpoint='report_social_media')
@login_required
@member_required
def social_media():
    """Raport: Pokrycie Social Media."""
    db = SessionLocal()
    try:
        # Wszystkie firmy z ich profilami social media
        companies = db.query(Company).options(
            joinedload(Company.social_media_profiles)
        ).order_by(Company.name).all()

        platforms = ['facebook', 'instagram', 'linkedin', 'youtube', 'tiktok', 'twitter']

        # Statystyki platform
        platform_stats = {}
        for platform in platforms:
            count = db.query(CompanySocialMedia).filter_by(
                platform=platform
            ).count()
            platform_stats[platform] = {
                'count': count,
                'percent': round(count / len(companies) * 100, 1) if companies else 0
            }

        # Firmy z min. 1 profilem
        companies_with_social = [
            c for c in companies if c.social_media_profiles
        ]

        stats = {
            'total_companies': len(companies),
            'with_social': len(companies_with_social),
            'without_social': len(companies) - len(companies_with_social),
            'coverage_percent': round(
                len(companies_with_social) / len(companies) * 100, 1
            ) if companies else 0
        }

        return render_template(
            'reports/social_media.html',
            companies=companies,
            platforms=platforms,
            platform_stats=platform_stats,
            stats=stats,
            generated_at=datetime.now()
        )
    finally:
        db.close()


@bp.route('/struktura-branzowa', endpoint='report_categories')
@login_required
@member_required
def categories():
    """Raport: Struktura branżowa."""
    db = SessionLocal()
    try:
        # Grupowanie po category_id (kolumna FK, nie relacja)
        category_counts = db.query(
            Company.category_id,
            func.count(Company.id).label('count')
        ).group_by(Company.category_id).all()

        total = sum(c.count for c in category_counts)

        # Pobierz mapę kategorii (id -> name) jednym zapytaniem
        category_map = {cat.id: cat.name for cat in db.query(Category).all()}

        categories_list = []
        for cat in category_counts:
            cat_id = cat.category_id
            cat_name = category_map.get(cat_id, 'Brak kategorii') if cat_id else 'Brak kategorii'

            examples = db.query(Company.name).filter(
                Company.category_id == cat_id
            ).limit(3).all()

            categories_list.append({
                'name': cat_name,
                'count': cat.count,
                'percent': round(cat.count / total * 100, 1) if total else 0,
                'examples': [e.name for e in examples]
            })

        # Sortuj od największej
        categories_list.sort(key=lambda x: x['count'], reverse=True)

        return render_template(
            'reports/categories.html',
            categories=categories_list,
            total=total,
            generated_at=datetime.now()
        )
    finally:
        db.close()


@bp.route('/skladki', endpoint='report_fees')
@login_required
@rada_member_required
def fees_report():
    """Raport składek członkowskich — dla zarządu i Rady Izby. Dynamiczny, generowany z bazy."""
    from database import MembershipFee

    db = SessionLocal()
    try:
        year = request.args.get('year', datetime.now().year, type=int)

        fees = db.query(MembershipFee).filter(MembershipFee.fee_year == year).all()

        by_company = {}
        for f in fees:
            if f.company_id not in by_company:
                by_company[f.company_id] = []
            by_company[f.company_id].append(f)

        total_companies_with_fees = len(by_company)
        total_active = db.query(Company).filter(Company.status == 'active').count()

        # Membership structure breakdown
        subsidiaries_count = db.query(Company).filter(
            Company.status == 'active',
            Company.fee_included_in_parent == True
        ).count()
        paying_companies = db.query(Company).filter(
            Company.status == 'active',
            Company.fee_included_in_parent != True
        ).count()

        # Fee tier breakdown: standard (200) vs premium (300+)
        from sqlalchemy import func, distinct
        standard_fee = 0
        premium_fee = 0
        for company_id, company_fees in by_company.items():
            if company_fees:
                avg_amount = sum(float(f.amount) for f in company_fees) / len(company_fees)
                if avg_amount > 200:
                    premium_fee += 1
                else:
                    standard_fee += 1

        resigned_count = db.query(Company).filter(
            Company.membership_status == 'resigned'
        ).count()

        fully_paid = 0
        partially_paid = 0
        no_payments = 0
        wrong_amounts = 0
        total_due = 0
        total_paid = 0

        for company_id, company_fees in by_company.items():
            paid_months = sum(1 for f in company_fees if f.status == 'paid')
            partial_months = sum(1 for f in company_fees if f.status == 'partial')
            total_months = len(company_fees)

            for f in company_fees:
                total_due += float(f.amount)
                total_paid += float(f.amount_paid or 0)

            if partial_months > 0:
                wrong_amounts += 1

            if paid_months == total_months:
                fully_paid += 1
            elif paid_months == 0 and partial_months == 0:
                no_payments += 1
            else:
                partially_paid += 1

        execution_pct = (total_paid / total_due * 100) if total_due > 0 else 0
        outstanding = total_due - total_paid

        monthly_stats = []
        MONTHS_NAMES = {
            1: 'Styczeń', 2: 'Luty', 3: 'Marzec', 4: 'Kwiecień',
            5: 'Maj', 6: 'Czerwiec', 7: 'Lipiec', 8: 'Sierpień',
            9: 'Wrzesień', 10: 'Październik', 11: 'Listopad', 12: 'Grudzień'
        }
        for m in range(1, 13):
            month_fees = [f for f in fees if f.fee_month == m]
            if month_fees:
                m_due = sum(float(f.amount) for f in month_fees)
                m_paid = sum(float(f.amount_paid or 0) for f in month_fees)
                m_pct = (m_paid / m_due * 100) if m_due > 0 else 0
                monthly_stats.append({'month': m, 'due': int(m_due), 'paid': int(m_paid), 'pct': round(m_pct, 1)})

        return render_template(
            'reports/fees.html',
            year=year,
            years=list(range(2022, datetime.now().year + 2)),
            total_active=total_active,
            total_companies_with_fees=total_companies_with_fees,
            fully_paid=fully_paid,
            partially_paid=partially_paid,
            no_payments=no_payments,
            wrong_amounts=wrong_amounts,
            total_due=int(total_due),
            total_paid=int(total_paid),
            outstanding=int(outstanding),
            execution_pct=round(execution_pct, 1),
            monthly_stats=monthly_stats,
            months_names=MONTHS_NAMES,
            generated_at=datetime.now(),
            subsidiaries_count=subsidiaries_count,
            paying_companies=paying_companies,
            standard_fee=standard_fee,
            premium_fee=premium_fee,
            resigned_count=resigned_count,
        )
    finally:
        db.close()