import io, os, smtplib, tempfile
from datetime import datetime, timezone
from email.message import EmailMessage
from pathlib import Path
import pandas as pd
from croniter import croniter
from sqlalchemy import select
from app.core.database import SessionLocal
from app.models.entities import ReportSchedule, ReportDelivery, Report, Dataset, User
from app.services.analytics import execute_definition
from app.services.storage import ObjectStorage

def _render(report, data, fmt):
    df=pd.DataFrame(data['rows'],columns=data['columns'])
    if fmt=='csv': return df.to_csv(index=False).encode('utf-8-sig'),'text/csv'
    if fmt=='xlsx':
        b=io.BytesIO()
        with pd.ExcelWriter(b,engine='xlsxwriter') as writer:
            df.to_excel(writer,index=False,sheet_name='Dados'); ws=writer.sheets['Dados']; ws.freeze_panes(1,0); ws.autofilter(0,0,max(len(df),1),max(len(df.columns)-1,0))
            for i,c in enumerate(df.columns): ws.set_column(i,i,min(max(len(str(c))+2,12),45))
        return b.getvalue(),'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    if fmt=='pdf':
        from reportlab.lib.pagesizes import landscape,A4
        from reportlab.platypus import SimpleDocTemplate,Table,TableStyle,Paragraph,Spacer
        from reportlab.lib import colors
        from reportlab.lib.styles import getSampleStyleSheet
        b=io.BytesIO(); doc=SimpleDocTemplate(b,pagesize=landscape(A4),title=report.name); styles=getSampleStyleSheet()
        rows=[list(df.columns)]+df.head(1000).astype(str).values.tolist(); table=Table(rows,repeatRows=1)
        table.setStyle(TableStyle([('BACKGROUND',(0,0),(-1,0),colors.HexColor('#16324F')),('TEXTCOLOR',(0,0),(-1,0),colors.white),('GRID',(0,0),(-1,-1),.25,colors.grey),('FONTSIZE',(0,0),(-1,-1),7),('VALIGN',(0,0),(-1,-1),'TOP')]))
        doc.build([Paragraph(report.name,styles['Title']),Spacer(1,12),table]); return b.getvalue(),'application/pdf'
    raise ValueError('Formato inválido')

def execute_due_report_schedules():
    db=SessionLocal(); now=datetime.now(timezone.utc); processed=[]
    try:
        schedules=db.scalars(select(ReportSchedule).where(ReportSchedule.enabled==True,ReportSchedule.next_run_at<=now)).all()
        for s in schedules:
            delivery=ReportDelivery(schedule_id=s.id,status='running',formats=s.formats,recipients=s.recipients,started_at=now); db.add(delivery); db.flush()
            try:
                report=db.get(Report,s.report_id); owner=db.get(User,s.owner_id); dataset=db.get(Dataset,report.dataset_id) if report else None
                if not report or not dataset or not owner: raise ValueError('Relatório, dataset ou proprietário indisponível')
                data=execute_definition(db,owner,dataset,report.definition,50000); storage=ObjectStorage(); keys=[]; attachments=[]
                for fmt in s.formats:
                    body,media=_render(report,data,fmt); key=f'reports/{s.id}/{now.strftime("%Y/%m/%d/%H%M%S")}/{report.id}.{fmt}'
                    with tempfile.NamedTemporaryFile(delete=False,suffix='.'+fmt) as f: f.write(body); path=f.name
                    try: storage.upload(path,key)
                    finally: os.unlink(path)
                    keys.append(key); attachments.append((f'{report.name}.{fmt}',body,media))
                smtp=(owner.preferences or {}).get('smtp',{}); msg=EmailMessage(); msg['Subject']=s.subject_template.replace('{{report_name}}',report.name); msg['From']=smtp.get('from',owner.email); msg['To']=', '.join(s.recipients); msg.set_content(s.body_template.replace('{{report_name}}',report.name))
                if smtp.get('attach_reports',True):
                    for filename,body,media in attachments:
                        maintype,subtype=media.split('/',1); msg.add_attachment(body,maintype=maintype,subtype=subtype,filename=filename)
                if s.recipients and smtp.get('host'):
                    with smtplib.SMTP(smtp['host'],int(smtp.get('port',587)),timeout=30) as client:
                        if smtp.get('starttls',True): client.starttls()
                        if smtp.get('username'): client.login(smtp['username'],smtp.get('password',''))
                        client.send_message(msg)
                delivery.status='success'; delivery.object_keys=keys; processed.append(s.id)
            except Exception as exc:
                delivery.status='failed'; delivery.error=str(exc)[:4000]
            delivery.finished_at=datetime.now(timezone.utc); s.last_run_at=now; s.next_run_at=croniter(s.cron,now).get_next(datetime)
        db.commit(); return {'processed':processed,'count':len(processed)}
    finally: db.close()