from __future__ import annotations
import csv, hashlib, html, json, secrets
from datetime import datetime, timezone
from pathlib import Path
from typing import Any
from sqlalchemy import select
from sqlalchemy.orm import Session
from app.models.entities import BIThemeV036,BIDashboardV036,BIWidgetV036,ReportTemplateV036,ReportRenderV036,BIShareLinkV036

VISUAL_TYPES={'kpi','table','pivot','bar','line','area','pie','donut','scatter','bubble','heatmap','waterfall','funnel','gauge','map','sankey','text','image'}
OUTPUT_FORMATS={'html','csv','xlsx','json'}
ALLOWED_AGGREGATIONS={'sum','avg','count','count_distinct','min','max','none'}

def validate_query_definition(definition:dict)->dict:
    metrics=definition.get('metrics') or []
    dimensions=definition.get('dimensions') or []
    if len(metrics)>20 or len(dimensions)>20: raise ValueError('Máximo de 20 métricas e 20 dimensões por widget')
    for metric in metrics:
        if not isinstance(metric,dict) or not str(metric.get('field','')).strip(): raise ValueError('Métrica inválida')
        if metric.get('aggregation','sum') not in ALLOWED_AGGREGATIONS: raise ValueError('Agregação inválida')
    for dim in dimensions:
        if not isinstance(dim,(dict,str)): raise ValueError('Dimensão inválida')
    limit=int(definition.get('limit',1000))
    if limit<1 or limit>100000: raise ValueError('Limite deve estar entre 1 e 100000')
    definition['limit']=limit
    return definition

def validate_widget(visual_type:str,query_definition:dict,position:dict,drill_config:dict)->None:
    # conditional_formatting is persisted on the widget and evaluated by the governed renderer.
    if visual_type not in VISUAL_TYPES: raise ValueError('Tipo de visual não suportado')
    validate_query_definition(query_definition)
    for key in ('x','y','w','h'):
        if key not in position: raise ValueError('Posição deve conter x, y, w e h')
        if int(position[key])<0: raise ValueError('Posição inválida')
    if int(position['w'])<1 or int(position['h'])<1: raise ValueError('Tamanho inválido')
    levels=drill_config.get('levels') or []
    if len(levels)>10: raise ValueError('Drill-down limitado a dez níveis')

def validate_report_template(page_config:dict,sections:list,parameters:list,output_formats:list)->None:
    if page_config.get('size','A4') not in {'A4','A3','Letter','Legal','custom'}: raise ValueError('Tamanho de página inválido')
    if len(sections)>200: raise ValueError('Máximo de 200 seções')
    ids=set()
    for section in sections:
        sid=str(section.get('id','')).strip()
        if not sid or sid in ids: raise ValueError('Seção sem ID ou duplicada')
        ids.add(sid)
        if section.get('type') not in {'header','footer','text','table','chart','image','page_break','group','summary'}: raise ValueError('Tipo de seção inválido')
    names=set()
    for param in parameters:
        name=str(param.get('name','')).strip()
        if not name or name in names: raise ValueError('Parâmetro sem nome ou duplicado')
        names.add(name)
    if not output_formats or any(x not in OUTPUT_FORMATS for x in output_formats): raise ValueError('Formato de saída inválido')

def create_share_link(db:Session,dashboard:BIDashboardV036,user_id:str,expires_at=None,allowed_domains=None,require_authentication=True):
    raw=secrets.token_urlsafe(32)
    item=BIShareLinkV036(tenant_id=dashboard.tenant_id,dashboard_id=dashboard.id,token_hash=hashlib.sha256(raw.encode()).hexdigest(),expires_at=expires_at,allowed_domains=allowed_domains or [],require_authentication=require_authentication,created_by=user_id)
    db.add(item); db.commit(); db.refresh(item)
    return item,raw

def resolve_share_link(db:Session,raw_token:str):
    digest=hashlib.sha256(raw_token.encode()).hexdigest()
    item=db.scalar(select(BIShareLinkV036).where(BIShareLinkV036.token_hash==digest,BIShareLinkV036.active.is_(True)))
    if not item: return None
    now=datetime.now(timezone.utc)
    if item.expires_at and item.expires_at<now: return None
    return item

def _rows_from_payload(data_definition:dict,parameters:dict)->list[dict]:
    rows=data_definition.get('sample_rows') or data_definition.get('rows') or []
    if not isinstance(rows,list): raise ValueError('Fonte de dados do relatório inválida')
    limit=min(int(data_definition.get('limit',10000)),100000)
    return [r for r in rows[:limit] if isinstance(r,dict)]

def _render_html(template:ReportTemplateV036,rows:list[dict],parameters:dict)->bytes:
    title=html.escape(template.name)
    parts=[f'<!doctype html><html><head><meta charset="utf-8"><title>{title}</title><style>body{{font-family:Arial,sans-serif;margin:28px}}table{{border-collapse:collapse;width:100%}}th,td{{border:1px solid #bbb;padding:6px;text-align:left}}.page-break{{page-break-after:always}}</style></head><body>']
    for section in template.sections:
        kind=section.get('type')
        if kind=='header': parts.append(f"<header><h1>{html.escape(str(section.get('text',template.name)))}</h1></header>")
        elif kind=='footer': parts.append(f"<footer>{html.escape(str(section.get('text','')))}</footer>")
        elif kind=='text': parts.append(f"<p>{html.escape(str(section.get('text','')))}</p>")
        elif kind=='page_break': parts.append('<div class="page-break"></div>')
        elif kind in {'table','group','summary'}:
            cols=section.get('columns') or (list(rows[0].keys()) if rows else [])
            parts.append('<table><thead><tr>'+''.join(f'<th>{html.escape(str(c))}</th>' for c in cols)+'</tr></thead><tbody>')
            for row in rows: parts.append('<tr>'+''.join(f'<td>{html.escape(str(row.get(c,"")))}</td>' for c in cols)+'</tr>')
            parts.append('</tbody></table>')
        elif kind=='chart': parts.append('<div data-chart="'+html.escape(json.dumps(section.get('config') or {}))+'">Gráfico disponível na visualização interativa.</div>')
        elif kind=='image': parts.append(f"<img src=\"{html.escape(str(section.get('src','')))}\" alt=\"\">")
    parts.append('</body></html>')
    return ''.join(parts).encode('utf-8')

def render_report(db:Session,template:ReportTemplateV036,user_id:str,output_format:str,parameters:dict,root='storage/reports'):
    if output_format not in template.output_formats or output_format not in OUTPUT_FORMATS: raise ValueError('Formato não autorizado pelo template')
    render=ReportRenderV036(tenant_id=template.tenant_id,template_id=template.id,requested_by=user_id,output_format=output_format,parameters=parameters,status='running')
    db.add(render); db.commit(); db.refresh(render)
    try:
        rows=_rows_from_payload(template.data_definition,parameters)
        folder=Path(root).resolve()/template.tenant_id
        folder.mkdir(parents=True,exist_ok=True)
        path=folder/f'{render.id}.{output_format}'
        if output_format=='html': path.write_bytes(_render_html(template,rows,parameters))
        elif output_format=='json': path.write_text(json.dumps(rows,ensure_ascii=False,indent=2),encoding='utf-8')
        elif output_format=='csv':
            cols=list(rows[0].keys()) if rows else []
            with path.open('w',newline='',encoding='utf-8-sig') as fh:
                w=csv.DictWriter(fh,fieldnames=cols); w.writeheader(); w.writerows(rows)
        elif output_format=='xlsx':
            from openpyxl import Workbook
            wb=Workbook(write_only=True); ws=wb.create_sheet('Relatório')
            cols=list(rows[0].keys()) if rows else []
            ws.append(cols)
            for row in rows: ws.append([row.get(c) for c in cols])
            wb.save(path)
        blob=path.read_bytes(); render.file_path=str(path); render.checksum_sha256=hashlib.sha256(blob).hexdigest(); render.size_bytes=len(blob); render.row_count=len(rows); render.status='success'; render.completed_at=datetime.now(timezone.utc)
    except Exception as exc:
        render.status='failed'; render.error_message=str(exc); render.completed_at=datetime.now(timezone.utc)
    db.commit(); db.refresh(render); return render