import csv, io, json, re, time
from collections import defaultdict, deque
from datetime import datetime, timezone
from fastapi import APIRouter, Depends, HTTPException, Response
from sqlalchemy import func, or_, select
from sqlalchemy.orm import Session
import httpx
import pyotp
from app.api.deps import current_user, require_roles
from app.core.database import get_db
from app.core.security import decrypt_secret, encrypt_secret, hash_password, verify_password
from app.models.entities import *
from app.connectors.sqlalchemy_connector import SQLAlchemyConnector

router = APIRouter(prefix='/api/v1')
_attempts = defaultdict(deque)

def _connector(s):
    return SQLAlchemyConnector(s.db_type.value,s.host,s.port,s.database,s.username,decrypt_secret(s.encrypted_password),s.options)

def _audit(db,u,action,entity_type,entity_id=None,details=None):
    db.add(AuditLog(actor=u.email,action=action,entity_type=entity_type,entity_id=entity_id,details=details or {})); db.commit()

def _allowed_dataset(db,u,did):
    d=db.get(Dataset,did)
    if not d: raise HTTPException(404,'Dataset não encontrado')
    if u.role in {Role.admin,Role.auditor,Role.data_engineer,Role.data_steward}: return d
    if d.owner_department_id not in {None,u.department_id}: raise HTTPException(403,'Dataset fora do seu escopo')
    if d.allowed_roles and u.role.value not in d.allowed_roles: raise HTTPException(403,'Perfil não autorizado')
    return d

@router.get('/metrics/summary')
def summary(db:Session=Depends(get_db),u:User=Depends(current_user)):
    ds=select(Dataset)
    if u.role not in {Role.admin,Role.auditor,Role.data_engineer,Role.data_steward}: ds=ds.where(or_(Dataset.owner_department_id==u.department_id,Dataset.owner_department_id.is_(None)))
    return {
      'sources':db.scalar(select(func.count()).select_from(DataSource)) or 0,
      'datasets':len(db.scalars(ds).all()),
      'runs_today':db.scalar(select(func.count()).select_from(IngestionRun).where(IngestionRun.started_at>=datetime.now(timezone.utc).replace(hour=0,minute=0,second=0,microsecond=0))) or 0,
      'automations':db.scalar(select(func.count()).select_from(Automation).where(Automation.enabled==True)) or 0,
      'users':db.scalar(select(func.count()).select_from(User).where(User.is_active==True)) or 0,
    }

@router.post('/auth/change-password')
def change_password(payload:dict,db:Session=Depends(get_db),u:User=Depends(current_user)):
    current=payload.get('current_password',''); new=payload.get('new_password','')
    if not verify_password(current,u.password_hash): raise HTTPException(400,'Senha atual incorreta')
    if len(new)<12 or not re.search(r'[A-Z]',new) or not re.search(r'[a-z]',new) or not re.search(r'\d',new): raise HTTPException(400,'A nova senha deve ter 12 caracteres, maiúscula, minúscula e número')
    u.password_hash=hash_password(new); db.commit(); _audit(db,u,'change_password','user',u.id); return {'updated':True}

@router.post('/auth/mfa/setup')
def mfa_setup(db:Session=Depends(get_db),u:User=Depends(current_user)):
    secret=pyotp.random_base32(); prefs=dict(u.preferences or {}); prefs['mfa_secret_enc']=encrypt_secret(secret); u.preferences=prefs; db.commit()
    return {'secret':secret,'otpauth_uri':pyotp.TOTP(secret).provisioning_uri(name=u.email,issuer_name='Chemitec Data Platform')}

@router.post('/auth/mfa/enable')
def mfa_enable(payload:dict,db:Session=Depends(get_db),u:User=Depends(current_user)):
    enc=(u.preferences or {}).get('mfa_secret_enc'); code=str(payload.get('code',''))
    if not enc or not pyotp.TOTP(decrypt_secret(enc)).verify(code,valid_window=1): raise HTTPException(400,'Código MFA inválido')
    u.mfa_enabled=True; db.commit(); _audit(db,u,'enable_mfa','user',u.id); return {'enabled':True}

@router.put('/users/{uid}')
def update_user(uid:str,payload:dict,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.director,Role.manager))):
    x=db.get(User,uid)
    if not x: raise HTTPException(404,'Usuário não encontrado')
    if u.role==Role.manager and x.department_id!=u.department_id: raise HTTPException(403,'Fora do departamento')
    for key in ['full_name','job_title','phone','department_id','manager_id','is_active']:
        if key in payload: setattr(x,key,payload[key])
    if 'role' in payload:
        if u.role==Role.manager: raise HTTPException(403,'Gerente não altera perfis')
        x.role=Role(payload['role'])
    db.commit(); _audit(db,u,'update','user',x.id); return {'updated':True}

@router.get('/teams/{tid}')
def team_detail(tid:str,db:Session=Depends(get_db),u:User=Depends(current_user)):
    t=db.get(Team,tid)
    if not t: raise HTTPException(404,'Equipe não encontrada')
    if u.role not in {Role.admin,Role.director} and t.department_id!=u.department_id and t.leader_id!=u.id: raise HTTPException(403,'Sem acesso')
    members=db.scalars(select(User).join(TeamMember,TeamMember.user_id==User.id).where(TeamMember.team_id==tid)).all()
    return {'id':t.id,'name':t.name,'leader_id':t.leader_id,'department_id':t.department_id,'description':t.description,'members':[{'id':m.id,'name':m.full_name,'email':m.email,'job_title':m.job_title} for m in members]}

@router.post('/teams/{tid}/members')
def add_members(tid:str,payload:dict,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.director,Role.manager))):
    t=db.get(Team,tid)
    if not t: raise HTTPException(404,'Equipe não encontrada')
    if u.role==Role.manager and t.department_id!=u.department_id: raise HTTPException(403,'Fora do departamento')
    existing={x for x in db.scalars(select(TeamMember.user_id).where(TeamMember.team_id==tid)).all()}
    for uid in payload.get('member_ids',[]):
        member=db.get(User,uid)
        if member and member.department_id==t.department_id and uid not in existing: db.add(TeamMember(team_id=tid,user_id=uid))
    db.commit(); _audit(db,u,'add_members','team',tid); return {'updated':True}

@router.get('/datasets/{did}/preview')
def preview_dataset(did:str,limit:int=50,db:Session=Depends(get_db),u:User=Depends(current_user)):
    d=_allowed_dataset(db,u,did); source=db.get(DataSource,d.source_id); limit=max(1,min(limit,200))
    try:
        result=_connector(source).preview(d.source_object,limit)
    except AttributeError:
        result={'columns':[],'rows':[],'warning':'Conector sem método preview'}
    _audit(db,u,'preview','dataset',did,{'limit':limit}); return result

@router.get('/dashboards/{did}')
def dashboard_detail(did:str,db:Session=Depends(get_db),u:User=Depends(current_user)):
    d=db.get(Dashboard,did)
    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')
    ws=db.scalars(select(DashboardWidget).where(DashboardWidget.dashboard_id==did)).all()
    return {'id':d.id,'name':d.name,'layout':d.layout,'filters':d.filters,'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 ws]}

@router.delete('/dashboards/{did}')
def dashboard_delete(did:str,db:Session=Depends(get_db),u:User=Depends(current_user)):
    d=db.get(Dashboard,did)
    if not d or (d.owner_id!=u.id and u.role!=Role.admin): raise HTTPException(404,'Dashboard não encontrado')
    db.delete(d); db.commit(); return {'deleted':True}

@router.get('/reports/{rid}/export')
def report_export(rid:str,output:str='csv',db:Session=Depends(get_db),u:User=Depends(current_user)):
    r=db.get(Report,rid)
    if not r or not (r.owner_id==u.id or r.department_id==u.department_id or u.role==Role.admin): raise HTTPException(404,'Relatório não encontrado')
    if not r.dataset_id: raise HTTPException(400,'Relatório sem dataset')
    d=_allowed_dataset(db,u,r.dataset_id); source=db.get(DataSource,d.source_id); data=_connector(source).preview(d.source_object,min(int((r.definition or {}).get('limit',500)),5000))
    if output=='json': return data
    buff=io.StringIO(); writer=csv.writer(buff); writer.writerow(data.get('columns',[])); writer.writerows(data.get('rows',[]))
    return Response(buff.getvalue(),media_type='text/csv',headers={'Content-Disposition':f'attachment; filename="{r.name}.csv"'})

@router.post('/api-builder/{aid}/execute')
def execute_api(aid:str,payload:dict,db:Session=Depends(get_db),u:User=Depends(current_user)):
    a=db.get(ApiDefinition,aid)
    if not a or not a.enabled: raise HTTPException(404,'Integração não encontrada')
    if a.department_id and a.department_id!=u.department_id and u.role not in {Role.admin,Role.data_engineer}: raise HTTPException(403,'Sem acesso')
    method=payload.get('method','GET').upper(); path=payload.get('path','/'); allowed={(r['method'].upper(),r['path']) for r in a.generated_routes}
    if (method,path) not in allowed: raise HTTPException(400,'Rota não autorizada pela definição')
    headers={'Accept':'application/json'}; secret=decrypt_secret(a.encrypted_auth) if a.encrypted_auth else ''
    if a.auth_type=='bearer' and secret: headers['Authorization']=f'Bearer {secret}'
    elif a.auth_type=='api_key' and secret: headers[payload.get('api_key_header','X-API-Key')]=secret
    try:
        with httpx.Client(timeout=30,follow_redirects=False) as client:
            res=client.request(method,a.base_url.rstrip('/')+path,headers=headers,params=payload.get('params'),json=payload.get('body'))
        body=res.text[:200000]
    except Exception as exc: raise HTTPException(502,f'Falha na API externa: {exc}')
    _audit(db,u,'execute','api_definition',aid,{'method':method,'path':path,'status':res.status_code})
    return {'status_code':res.status_code,'headers':dict(res.headers),'body':body}

@router.get('/ai/providers')
def ai_providers(db:Session=Depends(get_db),u:User=Depends(current_user)):
    return [{'id':x.id,'name':x.name,'provider':x.provider,'model':x.model,'enabled':x.enabled,'policy':x.policy} for x in db.scalars(select(AIProvider).where(AIProvider.enabled==True)).all()]

def _mask_value(value,policy):
    if value is None:return None
    mode=policy or 'mask'
    if mode=='remove':return None
    s=str(value)
    if mode=='last4':return '*'*max(0,len(s)-4)+s[-4:]
    return '***'

def _safe_context(db,u,dataset_ids,max_rows):
    out=[]
    for did in dataset_ids[:5]:
        d=_allowed_dataset(db,u,did); s=db.get(DataSource,d.source_id); data=_connector(s).preview(d.source_object,min(max_rows,100))
        cols=data.get('columns',[]); sensitive=set(d.sensitive_columns or []); rows=[]
        for row in data.get('rows',[]):
            record={c:(_mask_value(v,(d.masking_policy or {}).get(c)) if c in sensitive else v) for c,v in zip(cols,row)}; rows.append(record)
        out.append({'dataset':d.name,'columns':cols,'rows':rows})
    return out

@router.post('/ai/query/execute')
def ai_execute(payload:dict,db:Session=Depends(get_db),u:User=Depends(current_user)):
    provider=db.get(AIProvider,payload.get('provider_id'))
    if not provider or not provider.enabled: raise HTTPException(404,'Provedor não encontrado')
    prompt=str(payload.get('prompt','')).strip()
    if not prompt: raise HTTPException(400,'Prompt vazio')
    context=_safe_context(db,u,payload.get('dataset_ids',[]),min(int(payload.get('max_rows',50)),100))
    policy='Você é um analista corporativo. Use somente o contexto sanitizado. Não invente dados, não revele segredos e informe quando o contexto for insuficiente.'
    full=f'{policy}\n\nCONTEXTO SANITIZADO:\n{json.dumps(context,ensure_ascii=False,default=str)}\n\nSOLICITAÇÃO:\n{prompt}'
    key=decrypt_secret(provider.encrypted_api_key)
    try:
        with httpx.Client(timeout=90) as client:
            if provider.provider.lower()=='openai':
                endpoint=provider.endpoint or 'https://api.openai.com/v1/responses'
                res=client.post(endpoint,headers={'Authorization':f'Bearer {key}','Content-Type':'application/json'},json={'model':provider.model,'input':full,'store':False})
                res.raise_for_status(); j=res.json(); answer=j.get('output_text') or ''.join(c.get('text','') for o in j.get('output',[]) for c in o.get('content',[]) if c.get('type')=='output_text')
            elif provider.provider.lower()=='gemini':
                endpoint=provider.endpoint or f'https://generativelanguage.googleapis.com/v1beta/models/{provider.model}:generateContent'
                res=client.post(endpoint,headers={'x-goog-api-key':key,'Content-Type':'application/json'},json={'contents':[{'parts':[{'text':full}]}]})
                res.raise_for_status(); j=res.json(); answer=''.join(p.get('text','') for c in j.get('candidates',[]) for p in c.get('content',{}).get('parts',[]))
            else: raise HTTPException(400,'Provedor não suportado')
    except HTTPException: raise
    except Exception as exc: raise HTTPException(502,f'Falha no provedor de IA: {exc}')
    conv=AIConversation(user_id=u.id,provider_id=provider.id,prompt=prompt,sanitized_context={'dataset_ids':payload.get('dataset_ids',[]),'rows_per_dataset':min(int(payload.get('max_rows',50)),100)},response=answer,status='success'); db.add(conv); db.commit(); _audit(db,u,'ai_execute','ai_conversation',conv.id)
    return {'id':conv.id,'response':answer,'status':'success'}

from fastapi import Request
@router.api_route('/integration/inbound/{aid}/{path:path}',methods=['GET','POST','PUT','PATCH','DELETE'])
async def inbound_api(aid:str,path:str,request:Request,db:Session=Depends(get_db)):
    a=db.get(ApiDefinition,aid)
    if not a or not a.enabled or a.direction!='inbound': raise HTTPException(404,'API de entrada não encontrada')
    route='/'+path; method=request.method.upper(); allowed={(r['method'].upper(),r['path']) for r in a.generated_routes}
    if (method,route) not in allowed: raise HTTPException(404,'Rota não publicada')
    secret=decrypt_secret(a.encrypted_auth) if a.encrypted_auth else ''
    if a.auth_type=='bearer' and request.headers.get('authorization')!=f'Bearer {secret}': raise HTTPException(401,'Não autorizado')
    if a.auth_type=='api_key' and request.headers.get('x-api-key')!=secret: raise HTTPException(401,'Não autorizado')
    try: body=await request.json()
    except Exception: body={}
    tx=ApiTransaction(api_definition_id=aid,direction='inbound',method=method,path=route,status_code=202,request_data={'query':dict(request.query_params),'body':body},response_data={'accepted':True}); db.add(tx); db.commit()
    return {'accepted':True,'transaction_id':tx.id}

@router.get('/api-builder/{aid}/transactions')
def api_transactions(aid:str,db:Session=Depends(get_db),u:User=Depends(current_user)):
    a=db.get(ApiDefinition,aid)
    if not a or (a.department_id and a.department_id!=u.department_id and u.role not in {Role.admin,Role.data_engineer}): raise HTTPException(404,'Integração não encontrada')
    rows=db.scalars(select(ApiTransaction).where(ApiTransaction.api_definition_id==aid).order_by(ApiTransaction.created_at.desc()).limit(100)).all()
    return [{'id':x.id,'direction':x.direction,'method':x.method,'path':x.path,'status_code':x.status_code,'created_at':x.created_at} for x in rows]