import ast
from datetime import datetime, timezone
from typing import Any
from croniter import croniter
from fastapi import APIRouter, Depends, HTTPException
from pydantic import BaseModel, Field
from sqlalchemy import func, or_, select
from sqlalchemy.orm import Session
from app.api.deps import current_user
from app.core.database import get_db
from app.models.entities import *
from app.services.analytics import execute_definition

router=APIRouter(prefix='/api/v1/studio',tags=['Dashboard Studio V005'])

class DashboardPayload(BaseModel):
    name:str=Field(min_length=2,max_length=180)
    department_id:str|None=None
    layout:dict={}
    filters:dict={}
    is_shared:bool=False

class WidgetPayload(BaseModel):
    title:str=Field(min_length=1,max_length=180)
    widget_type:str
    dataset_id:str
    metric:dict={}
    visualization:dict={}
    position:dict={}

class CalculatedFieldPayload(BaseModel):
    dataset_id:str
    name:str=Field(min_length=1,max_length=160)
    expression:str=Field(min_length=1,max_length=1000)
    result_type:str='number'
    shared_department_id:str|None=None

class ExecutePayload(BaseModel):
    filters:list[dict]=[]
    drill_path:list[dict]=[]
    max_rows:int=Field(default=5000,ge=1,le=50000)

class SchedulePayload(BaseModel):
    cron:str
    timezone:str='America/Sao_Paulo'
    formats:list[str]=['pdf']
    recipients:list[str]=[]
    subject_template:str='Relatório {{report_name}}'
    body_template:str='Segue o relatório agendado.'
    enabled:bool=True

ALLOWED_WIDGETS={'kpi','table','bar','line','area','pie','donut','scatter','heatmap','funnel','gauge'}
ALLOWED_FUNCS={'abs','round','min','max'}

def _can_edit(d:Dashboard,u:User):
    return d.owner_id==u.id or u.role in {Role.admin,Role.director} or (u.role==Role.manager and d.department_id==u.department_id)

def _validate_expression(expr:str):
    tree=ast.parse(expr,mode='eval')
    for node in ast.walk(tree):
        if isinstance(node,(ast.Import,ast.ImportFrom,ast.Attribute,ast.Lambda,ast.DictComp,ast.ListComp,ast.SetComp,ast.GeneratorExp)): raise HTTPException(400,'Expressão não permitida')
        if isinstance(node,ast.Call) and (not isinstance(node.func,ast.Name) or node.func.id not in ALLOWED_FUNCS): raise HTTPException(400,'Função não permitida')
    return True

@router.get('/dashboards')
def list_dashboards(db:Session=Depends(get_db),u:User=Depends(current_user)):
    q=select(Dashboard).where(or_(Dashboard.owner_id==u.id,Dashboard.department_id==u.department_id,Dashboard.is_shared==True)).order_by(Dashboard.created_at.desc())
    return [{'id':d.id,'name':d.name,'owner_id':d.owner_id,'department_id':d.department_id,'layout':d.layout,'filters':d.filters,'is_shared':d.is_shared} for d in db.scalars(q).all()]

@router.post('/dashboards')
def create_dashboard(p:DashboardPayload,db:Session=Depends(get_db),u:User=Depends(current_user)):
    d=Dashboard(**p.model_dump(),owner_id=u.id); db.add(d); db.commit(); db.refresh(d)
    return {'id':d.id}

@router.get('/dashboards/{dashboard_id}')
def get_dashboard(dashboard_id:str,db:Session=Depends(get_db),u:User=Depends(current_user)):
    d=db.get(Dashboard,dashboard_id)
    if not d or not (d.owner_id==u.id or d.is_shared or d.department_id==u.department_id): raise HTTPException(404,'Dashboard não encontrado')
    widgets=db.scalars(select(DashboardWidget).where(DashboardWidget.dashboard_id==d.id)).all()
    return {'id':d.id,'name':d.name,'layout':d.layout,'filters':d.filters,'is_shared':d.is_shared,'widgets':[{'id':w.id,'title':w.title,'widget_type':w.widget_type,'dataset_id':w.dataset_id,'metric':w.metric,'visualization':w.visualization,'position':w.position} for w in widgets]}

@router.put('/dashboards/{dashboard_id}')
def update_dashboard(dashboard_id:str,p:DashboardPayload,db:Session=Depends(get_db),u:User=Depends(current_user)):
    d=db.get(Dashboard,dashboard_id)
    if not d or not _can_edit(d,u): raise HTTPException(403,'Sem permissão')
    version=(db.scalar(select(func.max(DashboardRevision.version)).where(DashboardRevision.dashboard_id==d.id)) or 0)+1
    widgets=db.scalars(select(DashboardWidget).where(DashboardWidget.dashboard_id==d.id)).all()
    db.add(DashboardRevision(dashboard_id=d.id,version=version,created_by=u.id,snapshot={'dashboard':{'name':d.name,'layout':d.layout,'filters':d.filters,'is_shared':d.is_shared},'widgets':[{'title':w.title,'widget_type':w.widget_type,'dataset_id':w.dataset_id,'metric':w.metric,'visualization':w.visualization,'position':w.position} for w in widgets]}))
    for k,v in p.model_dump().items(): setattr(d,k,v)
    db.commit(); return {'updated':True,'revision':version}

@router.post('/dashboards/{dashboard_id}/widgets')
def create_widget(dashboard_id:str,p:WidgetPayload,db:Session=Depends(get_db),u:User=Depends(current_user)):
    d=db.get(Dashboard,dashboard_id)
    if not d or not _can_edit(d,u): raise HTTPException(403,'Sem permissão')
    if p.widget_type not in ALLOWED_WIDGETS: raise HTTPException(400,'Tipo de widget inválido')
    w=DashboardWidget(dashboard_id=d.id,**p.model_dump()); db.add(w); db.commit(); db.refresh(w); return {'id':w.id}

@router.put('/widgets/{widget_id}')
def update_widget(widget_id:str,p:WidgetPayload,db:Session=Depends(get_db),u:User=Depends(current_user)):
    w=db.get(DashboardWidget,widget_id); d=db.get(Dashboard,w.dashboard_id) if w else None
    if not w or not d or not _can_edit(d,u): raise HTTPException(403,'Sem permissão')
    if p.widget_type not in ALLOWED_WIDGETS: raise HTTPException(400,'Tipo inválido')
    for k,v in p.model_dump().items(): setattr(w,k,v)
    db.commit(); return {'updated':True}

@router.delete('/widgets/{widget_id}')
def delete_widget(widget_id:str,db:Session=Depends(get_db),u:User=Depends(current_user)):
    w=db.get(DashboardWidget,widget_id); d=db.get(Dashboard,w.dashboard_id) if w else None
    if not w or not d or not _can_edit(d,u): raise HTTPException(403,'Sem permissão')
    db.delete(w); db.commit(); return {'deleted':True}

@router.post('/widgets/{widget_id}/execute')
def execute_widget(widget_id:str,p:ExecutePayload,db:Session=Depends(get_db),u:User=Depends(current_user)):
    w=db.get(DashboardWidget,widget_id); d=db.get(Dashboard,w.dashboard_id) if w else None
    if not w or not d or not (d.owner_id==u.id or d.is_shared or d.department_id==u.department_id): raise HTTPException(404,'Widget não encontrado')
    dataset=db.get(Dataset,w.dataset_id)
    definition=dict(w.metric or {})
    definition['filters']=(definition.get('filters') or [])+(d.filters.get('rules',[]) if isinstance(d.filters,dict) else [])+p.filters+p.drill_path
    result=execute_definition(db,u,dataset,definition,p.max_rows)
    result['visualization']=w.visualization; result['widget_type']=w.widget_type; result['title']=w.title
    return result

@router.get('/dashboards/{dashboard_id}/revisions')
def revisions(dashboard_id:str,db:Session=Depends(get_db),u:User=Depends(current_user)):
    d=db.get(Dashboard,dashboard_id)
    if not d or not _can_edit(d,u): raise HTTPException(403,'Sem permissão')
    return [{'id':r.id,'version':r.version,'created_at':r.created_at,'created_by':r.created_by} for r in db.scalars(select(DashboardRevision).where(DashboardRevision.dashboard_id==dashboard_id).order_by(DashboardRevision.version.desc())).all()]

@router.post('/calculated-fields')
def create_calculated_field(p:CalculatedFieldPayload,db:Session=Depends(get_db),u:User=Depends(current_user)):
    _validate_expression(p.expression)
    f=CalculatedField(**p.model_dump(),owner_id=u.id); db.add(f); db.commit(); db.refresh(f); return {'id':f.id}

@router.get('/calculated-fields/{dataset_id}')
def calculated_fields(dataset_id:str,db:Session=Depends(get_db),u:User=Depends(current_user)):
    q=select(CalculatedField).where(CalculatedField.dataset_id==dataset_id,or_(CalculatedField.owner_id==u.id,CalculatedField.shared_department_id==u.department_id))
    return [{'id':f.id,'name':f.name,'expression':f.expression,'result_type':f.result_type} for f in db.scalars(q).all()]

@router.post('/reports/{report_id}/schedules')
def create_schedule(report_id:str,p:SchedulePayload,db:Session=Depends(get_db),u:User=Depends(current_user)):
    report=db.get(Report,report_id)
    if not report or not (report.owner_id==u.id or u.role in {Role.admin,Role.director}): raise HTTPException(404,'Relatório não encontrado')
    if not croniter.is_valid(p.cron): raise HTTPException(400,'Expressão cron inválida')
    valid_formats=[x for x in p.formats if x in {'csv','xlsx','pdf'}]
    if not valid_formats: raise HTTPException(400,'Formato inválido')
    s=ReportSchedule(report_id=report_id,owner_id=u.id,**p.model_dump(exclude={'formats'}),formats=valid_formats,next_run_at=croniter(p.cron,datetime.now(timezone.utc)).get_next(datetime)); db.add(s); db.commit(); db.refresh(s)
    return {'id':s.id,'next_run_at':s.next_run_at}

@router.get('/report-schedules')
def schedules(db:Session=Depends(get_db),u:User=Depends(current_user)):
    q=select(ReportSchedule).where(ReportSchedule.owner_id==u.id).order_by(ReportSchedule.created_at.desc())
    return [{'id':s.id,'report_id':s.report_id,'cron':s.cron,'timezone':s.timezone,'formats':s.formats,'recipients':s.recipients,'enabled':s.enabled,'last_run_at':s.last_run_at,'next_run_at':s.next_run_at} for s in db.scalars(q).all()]