import io
from datetime import datetime, timezone
from fastapi import APIRouter, Depends, HTTPException
from fastapi.responses import StreamingResponse
from pydantic import BaseModel, Field
from sqlalchemy import or_, select
from sqlalchemy.orm import Session
import pandas as pd
from app.core.database import get_db
from app.api.deps import current_user, require_roles
from app.models.entities import (User,Role,Dataset,DatasetPermission,PermissionEffect,DataAccessRequest,AccessStatus,
 DataCatalogEntry,DataLineage,DataQualityRule,SavedQuery,Notification,Dashboard,DashboardWidget,Report,AuditLog)
from app.services.analytics import execute_definition, latest_frame

router=APIRouter(prefix='/api/v1/enterprise',tags=['enterprise'])
def audit(db,u,action,etype,eid='',details=None): db.add(AuditLog(actor=u.email,action=action,entity_type=etype,entity_id=eid,details=details or {})); db.commit()

class PermissionIn(BaseModel):
    dataset_id:str; subject_type:str; subject_id:str; actions:list[str]=['read']; allowed_columns:list[str]=[]; denied_columns:list[str]=[]; row_filter:dict={}; effect:PermissionEffect=PermissionEffect.allow; valid_until:datetime|None=None
class AccessRequestIn(BaseModel): dataset_id:str; requested_actions:list[str]=['read']; requested_columns:list[str]=[]; purpose:str=Field(min_length=10); expires_at:datetime|None=None
class ReviewIn(BaseModel): approved:bool; notes:str=''; expires_at:datetime|None=None
class CatalogIn(BaseModel): business_name:str=''; description:str=''; owner_id:str|None=None; steward_id:str|None=None; domain:str=''; classification:str='internal'; tags:list[str]=[]; glossary_terms:list[str]=[]; retention_days:int=1825; certified:bool=False
class LineageIn(BaseModel): upstream_dataset_id:str; downstream_dataset_id:str; transformation:dict={}
class QualityRuleIn(BaseModel): name:str; rule_type:str; column_name:str|None=None; config:dict={}; severity:str='warning'; enabled:bool=True
class QueryIn(BaseModel): dataset_id:str; definition:dict={}; max_rows:int=10000
class SavedQueryIn(BaseModel): name:str; dataset_id:str; query_definition:dict={}; shared_department_id:str|None=None
class WidgetExecuteIn(BaseModel): filters:list[dict]=[]; max_rows:int=5000

@router.post('/permissions')
def create_permission(p:PermissionIn,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.director,Role.data_steward))):
    if p.subject_type not in {'user','role','department','team'}: raise HTTPException(400,'Tipo de sujeito inválido')
    x=DatasetPermission(**p.model_dump(),created_by=u.id); db.add(x); db.commit(); db.refresh(x); audit(db,u,'create_permission','dataset',p.dataset_id,{'subject':p.subject_type}); return {'id':x.id}

@router.get('/permissions/{dataset_id}')
def list_permissions(dataset_id:str,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.director,Role.data_steward,Role.auditor))):
    return [{'id':x.id,'subject_type':x.subject_type,'subject_id':x.subject_id,'actions':x.actions,'columns':x.allowed_columns,'row_filter':x.row_filter,'effect':x.effect} for x in db.scalars(select(DatasetPermission).where(DatasetPermission.dataset_id==dataset_id)).all()]

@router.post('/access-requests')
def request_access(p:AccessRequestIn,db:Session=Depends(get_db),u:User=Depends(current_user)):
    if not db.get(Dataset,p.dataset_id): raise HTTPException(404,'Dataset não encontrado')
    x=DataAccessRequest(**p.model_dump(),requester_id=u.id); db.add(x); db.commit(); db.refresh(x); return {'id':x.id,'status':x.status}

@router.get('/access-requests')
def access_requests(db:Session=Depends(get_db),u:User=Depends(current_user)):
    q=select(DataAccessRequest)
    if u.role not in {Role.admin,Role.director,Role.data_steward,Role.manager}: q=q.where(DataAccessRequest.requester_id==u.id)
    return [{'id':x.id,'requester_id':x.requester_id,'dataset_id':x.dataset_id,'actions':x.requested_actions,'purpose':x.purpose,'status':x.status,'created_at':x.created_at} for x in db.scalars(q.order_by(DataAccessRequest.created_at.desc())).all()]

@router.post('/access-requests/{rid}/review')
def review_access(rid:str,p:ReviewIn,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.director,Role.data_steward,Role.manager))):
    x=db.get(DataAccessRequest,rid)
    if not x or x.status!=AccessStatus.pending: raise HTTPException(404,'Solicitação pendente não encontrada')
    x.status=AccessStatus.approved if p.approved else AccessStatus.rejected; x.reviewer_id=u.id; x.review_notes=p.notes; x.reviewed_at=datetime.now(timezone.utc); x.expires_at=p.expires_at or x.expires_at
    if p.approved: db.add(DatasetPermission(dataset_id=x.dataset_id,subject_type='user',subject_id=x.requester_id,actions=x.requested_actions,allowed_columns=x.requested_columns,effect=PermissionEffect.allow,valid_until=x.expires_at,created_by=u.id))
    db.add(Notification(user_id=x.requester_id,title='Solicitação de acesso analisada',body=f'Status: {x.status.value}',level='success' if p.approved else 'warning',link='/#governance'))
    db.commit(); audit(db,u,'review_access','access_request',rid,{'approved':p.approved}); return {'status':x.status}

@router.put('/catalog/{dataset_id}')
def upsert_catalog(dataset_id:str,p:CatalogIn,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.data_steward,Role.data_engineer))):
    x=db.scalar(select(DataCatalogEntry).where(DataCatalogEntry.dataset_id==dataset_id)) or DataCatalogEntry(dataset_id=dataset_id)
    for k,v in p.model_dump().items(): setattr(x,k,v)
    db.add(x); db.commit(); db.refresh(x); audit(db,u,'catalog_update','dataset',dataset_id); return {'id':x.id}

@router.get('/catalog')
def catalog(db:Session=Depends(get_db),u:User=Depends(current_user)):
    q=select(Dataset,DataCatalogEntry).outerjoin(DataCatalogEntry,DataCatalogEntry.dataset_id==Dataset.id)
    return [{'dataset_id':d.id,'technical_name':d.name,'source_object':d.source_object,'business_name':c.business_name if c else '', 'description':c.description if c else '', 'classification':c.classification if c else 'internal','quality_score':c.quality_score if c else 0,'certified':c.certified if c else False} for d,c in db.execute(q).all()]

@router.post('/lineage')
def add_lineage(p:LineageIn,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.data_engineer,Role.data_steward))):
    x=DataLineage(**p.model_dump(),created_by=u.id); db.add(x); db.commit(); return {'id':x.id}

@router.get('/lineage/{dataset_id}')
def lineage(dataset_id:str,db:Session=Depends(get_db),u:User=Depends(current_user)):
    rows=db.scalars(select(DataLineage).where(or_(DataLineage.upstream_dataset_id==dataset_id,DataLineage.downstream_dataset_id==dataset_id))).all()
    return [{'id':x.id,'upstream':x.upstream_dataset_id,'downstream':x.downstream_dataset_id,'transformation':x.transformation} for x in rows]

@router.post('/quality/{dataset_id}/rules')
def add_quality_rule(dataset_id:str,p:QualityRuleIn,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.data_steward,Role.data_engineer))):
    x=DataQualityRule(dataset_id=dataset_id,**p.model_dump()); db.add(x); db.commit(); return {'id':x.id}

@router.post('/quality/{dataset_id}/run')
def run_quality(dataset_id:str,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.data_steward,Role.data_engineer))):
    dataset=db.get(Dataset,dataset_id); df=latest_frame(db,dataset); results=[]; passed=0
    rules=db.scalars(select(DataQualityRule).where(DataQualityRule.dataset_id==dataset_id,DataQualityRule.enabled==True)).all()
    for r in rules:
        ok=True; detail={}; col=r.column_name
        if r.rule_type=='not_null': detail={'nulls':int(df[col].isna().sum())}; ok=detail['nulls']==0
        elif r.rule_type=='unique': detail={'duplicates':int(df[col].duplicated().sum())}; ok=detail['duplicates']==0
        elif r.rule_type=='range': detail={'min':float(df[col].min()),'max':float(df[col].max())}; ok=detail['min']>=r.config.get('min',detail['min']) and detail['max']<=r.config.get('max',detail['max'])
        elif r.rule_type=='accepted_values': detail={'invalid':int((~df[col].isin(r.config.get('values',[]))).sum())}; ok=detail['invalid']==0
        elif r.rule_type=='row_count': detail={'rows':len(df)}; ok=len(df)>=r.config.get('min',0) and len(df)<=r.config.get('max',10**12)
        r.last_status='passed' if ok else 'failed'; r.last_result=detail; r.last_run_at=datetime.now(timezone.utc); passed+=int(ok); results.append({'id':r.id,'name':r.name,'status':r.last_status,'result':detail})
    score=100 if not rules else round(100*passed/len(rules)); cat=db.scalar(select(DataCatalogEntry).where(DataCatalogEntry.dataset_id==dataset_id))
    if cat: cat.quality_score=score
    db.commit(); audit(db,u,'quality_run','dataset',dataset_id,{'score':score}); return {'score':score,'rules':results}

@router.post('/query/execute')
def execute_query(p:QueryIn,db:Session=Depends(get_db),u:User=Depends(current_user)):
    dataset=db.get(Dataset,p.dataset_id)
    if not dataset: raise HTTPException(404,'Dataset não encontrado')
    result=execute_definition(db,u,dataset,p.definition,p.max_rows); audit(db,u,'query_execute','dataset',p.dataset_id,{'rows':result['row_count']}); return result

@router.post('/queries')
def save_query(p:SavedQueryIn,db:Session=Depends(get_db),u:User=Depends(current_user)):
    x=SavedQuery(**p.model_dump(),owner_id=u.id); db.add(x); db.commit(); return {'id':x.id}

@router.get('/queries')
def list_queries(db:Session=Depends(get_db),u:User=Depends(current_user)):
    q=select(SavedQuery).where(or_(SavedQuery.owner_id==u.id,SavedQuery.shared_department_id==u.department_id)); return [{'id':x.id,'name':x.name,'dataset_id':x.dataset_id,'definition':x.query_definition} for x in db.scalars(q).all()]

@router.post('/dashboards/{dashboard_id}/widgets/{widget_id}/execute')
def execute_widget(dashboard_id:str,widget_id:str,p:WidgetExecuteIn,db:Session=Depends(get_db),u:User=Depends(current_user)):
    d=db.get(Dashboard,dashboard_id); w=db.get(DashboardWidget,widget_id)
    if not d or not w or w.dashboard_id!=dashboard_id: raise HTTPException(404,'Widget não encontrado')
    if d.owner_id!=u.id and not d.is_shared and d.department_id!=u.department_id: raise HTTPException(403,'Sem acesso ao dashboard')
    dataset=db.get(Dataset,w.dataset_id); definition=dict(w.metric or {}); definition['filters']=(definition.get('filters') or [])+p.filters
    return execute_definition(db,u,dataset,definition,p.max_rows)

@router.get('/reports/{report_id}/export/{fmt}')
def export_report(report_id:str,fmt:str,db:Session=Depends(get_db),u:User=Depends(current_user)):
    report=db.get(Report,report_id)
    if not report or (report.owner_id!=u.id and report.department_id!=u.department_id): raise HTTPException(404,'Relatório não encontrado')
    dataset=db.get(Dataset,report.dataset_id); data=execute_definition(db,u,dataset,report.definition,50000); df=pd.DataFrame(data['rows'],columns=data['columns']); safe=''.join(c for c in report.name if c.isalnum() or c in '-_ ') or 'report'
    if fmt=='csv':
        body=df.to_csv(index=False).encode('utf-8-sig'); media='text/csv'; ext='csv'
    elif 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)); ws.set_column(0,max(len(df.columns)-1,0),18)
        body=b.getvalue(); media='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'; ext='xlsx'
    elif 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(500).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]); body=b.getvalue(); media='application/pdf'; ext='pdf'
    else: raise HTTPException(400,'Formato inválido')
    audit(db,u,'report_export','report',report_id,{'format':fmt,'rows':len(df)}); return StreamingResponse(io.BytesIO(body),media_type=media,headers={'Content-Disposition':f'attachment; filename="{safe}.{ext}"'})

@router.get('/notifications')
def notifications(db:Session=Depends(get_db),u:User=Depends(current_user)):
    return [{'id':x.id,'title':x.title,'body':x.body,'level':x.level,'link':x.link,'read_at':x.read_at,'created_at':x.created_at} for x in db.scalars(select(Notification).where(Notification.user_id==u.id).order_by(Notification.created_at.desc()).limit(100)).all()]

@router.post('/notifications/{nid}/read')
def read_notification(nid:str,db:Session=Depends(get_db),u:User=Depends(current_user)):
    x=db.get(Notification,nid)
    if not x or x.user_id!=u.id: raise HTTPException(404,'Notificação não encontrada')
    x.read_at=datetime.now(timezone.utc); db.commit(); return {'ok':True}