import csv
import io
from datetime import datetime

from openpyxl import Workbook
from sqlalchemy.orm import Session
from sqlalchemy import desc

from backend.models.video import Video
from backend.models.creator import Creator


class Exporter:
    """Export data to CSV and Excel formats."""

    def export_videos_csv(
        self, db: Session, platform: str = "", min_score: float = 0
    ) -> str:
        videos = self._get_videos(db, platform, min_score)
        output = io.StringIO()
        writer = csv.writer(output)
        writer.writerow([
            "ID", "Plataforma", "Título", "URL", "Criador",
            "Views", "Likes", "Comentários", "Shares",
            "Viral Score", "Engajamento %", "Duração (s)",
            "Publicado em", "Coletado em", "Hashtags",
        ])
        for v in videos:
            writer.writerow([
                v.id, v.platform, v.title[:100], v.url,
                v.creator.username if v.creator else "",
                v.views, v.likes, v.comments, v.shares,
                v.viral_score, v.engagement_rate, v.duration,
                v.published_at.isoformat() if v.published_at else "",
                v.collected_at.isoformat() if v.collected_at else "",
                ", ".join(v.hashtags or []),
            ])
        return output.getvalue()

    def export_videos_excel(
        self, db: Session, platform: str = "", min_score: float = 0
    ) -> bytes:
        videos = self._get_videos(db, platform, min_score)
        wb = Workbook()

        # Videos sheet
        ws = wb.active
        ws.title = "Vídeos Virais"
        headers = [
            "ID", "Plataforma", "Título", "URL", "Criador",
            "Views", "Likes", "Comentários", "Shares",
            "Viral Score", "Engajamento %", "Duração (s)",
            "Publicado em", "Coletado em", "Hashtags",
        ]
        ws.append(headers)

        for col in range(1, len(headers) + 1):
            ws.cell(row=1, column=col).font = ws.cell(row=1, column=col).font.copy(bold=True)

        for v in videos:
            ws.append([
                v.id, v.platform, v.title[:100], v.url,
                v.creator.username if v.creator else "",
                v.views, v.likes, v.comments, v.shares,
                v.viral_score, v.engagement_rate, v.duration,
                v.published_at.isoformat() if v.published_at else "",
                v.collected_at.isoformat() if v.collected_at else "",
                ", ".join(v.hashtags or []),
            ])

        # Creators sheet
        ws2 = wb.create_sheet("Criadores")
        ws2.append(["ID", "Plataforma", "Username", "Nome", "Seguidores", "Vídeos", "Views Médias", "Engajamento Médio", "Monitorado"])
        creators = db.query(Creator).order_by(desc(Creator.followers)).all()
        for c in creators:
            video_count = db.query(Video).filter(Video.creator_id == c.id).count()
            ws2.append([
                c.id, c.platform, c.username, c.display_name,
                c.followers, video_count, round(c.avg_views),
                round(c.avg_engagement, 2), "Sim" if c.tracked else "Não",
            ])

        # Summary sheet
        ws3 = wb.create_sheet("Resumo")
        ws3.append(["Métrica", "Valor"])
        ws3.append(["Total de Vídeos", len(videos)])
        ws3.append(["Total de Criadores", len(creators)])
        ws3.append(["Data do Relatório", datetime.utcnow().strftime("%d/%m/%Y %H:%M")])

        platform_counts = {}
        for v in videos:
            platform_counts[v.platform] = platform_counts.get(v.platform, 0) + 1
        for p, c in platform_counts.items():
            ws3.append([f"Vídeos {p.title()}", c])

        viral = len([v for v in videos if v.viral_score >= 60])
        ws3.append(["Vídeos Virais (score >= 60)", viral])

        if videos:
            avg_score = sum(v.viral_score for v in videos) / len(videos)
            ws3.append(["Score Médio", round(avg_score, 1)])

        output = io.BytesIO()
        wb.save(output)
        return output.getvalue()

    def export_creators_csv(self, db: Session, tracked_only: bool = False) -> str:
        query = db.query(Creator)
        if tracked_only:
            query = query.filter(Creator.tracked == True)  # noqa: E712
        creators = query.order_by(desc(Creator.followers)).all()

        output = io.StringIO()
        writer = csv.writer(output)
        writer.writerow(["ID", "Plataforma", "Username", "Nome", "Seguidores", "Views Médias", "Engajamento", "Monitorado"])
        for c in creators:
            writer.writerow([
                c.id, c.platform, c.username, c.display_name,
                c.followers, round(c.avg_views), round(c.avg_engagement, 2),
                "Sim" if c.tracked else "Não",
            ])
        return output.getvalue()

    def _get_videos(self, db: Session, platform: str, min_score: float) -> list:
        query = db.query(Video)
        if platform:
            query = query.filter(Video.platform == platform)
        if min_score > 0:
            query = query.filter(Video.viral_score >= min_score)
        return query.order_by(desc(Video.viral_score)).all()
