import json, re, time
from collections import defaultdict, deque
import pyotp
from datetime import datetime, timezone
from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy import or_, select
from sqlalchemy.orm import Session
from app.api.deps import current_user, require_roles
from app.api.schemas import *
from app.connectors.sqlalchemy_connector import SQLAlchemyConnector
from app.core.database import get_db
from app.core.security import create_access_token, decrypt_secret, encrypt_secret, hash_password, verify_password
from app.models.entities import *
from app.workers.tasks import ingest_dataset_task
router=APIRouter(prefix='/api/v1')
_login_attempts=defaultdict(deque)
def audit(db,actor,action,entity_type,entity_id=None,details=None): db.add(AuditLog(actor=actor,action=action,entity_type=entity_type,entity_id=entity_id,details=details or {})); db.commit()
def connector(source): return SQLAlchemyConnector(source.db_type.value,source.host,source.port,source.database,source.username,decrypt_secret(source.encrypted_password),source.options)
def visible_dataset_query(user):
    q=select(Dataset)
    if user.role not in {Role.admin,Role.auditor,Role.data_engineer,Role.data_steward}: q=q.where(or_(Dataset.owner_department_id==user.department_id,Dataset.owner_department_id.is_(None)))
    return q
@router.post('/auth/login')
def login(p:LoginRequest,db:Session=Depends(get_db)):
    now=time.time(); bucket=_login_attempts[p.email.lower()]
    while bucket and bucket[0] < now-900: bucket.popleft()
    if len(bucket)>=5: raise HTTPException(429,'Muitas tentativas. Aguarde 15 minutos.')
    u=db.scalar(select(User).where(User.email==p.email))
    if not u or not u.is_active or not verify_password(p.password,u.password_hash): bucket.append(now); raise HTTPException(401,'Credenciais inválidas')
    if u.mfa_enabled:
        enc=(u.preferences or {}).get('mfa_secret_enc')
        if not enc or not p.mfa_code or not pyotp.TOTP(decrypt_secret(enc)).verify(p.mfa_code,valid_window=1): bucket.append(now); raise HTTPException(401,'Código MFA inválido')
    bucket.clear(); u.last_login_at=datetime.now(timezone.utc); db.commit(); audit(db,u.email,'login','user',u.id); return {'access_token':create_access_token(u.id,u.role.value),'token_type':'bearer','user':{'id':u.id,'name':u.full_name,'role':u.role,'department_id':u.department_id}}
@router.get('/me')
def me(u:User=Depends(current_user)): return {'id':u.id,'email':u.email,'full_name':u.full_name,'job_title':u.job_title,'phone':u.phone,'role':u.role,'department_id':u.department_id,'manager_id':u.manager_id,'mfa_enabled':u.mfa_enabled,'preferences':u.preferences}
@router.put('/me')
def update_me(p:ProfileUpdate,db:Session=Depends(get_db),u:User=Depends(current_user)):
    for k,v in p.model_dump().items(): setattr(u,k,v)
    db.commit(); audit(db,u.email,'update_profile','user',u.id); return {'updated':True}
@router.post('/users')
def create_user(p:UserCreate,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.director,Role.manager))):
    if db.scalar(select(User).where(User.email==p.email)): raise HTTPException(409,'E-mail já cadastrado')
    if u.role==Role.manager and p.department_id!=u.department_id: raise HTTPException(403,'Gerente só pode criar usuários no próprio departamento')
    x=User(**p.model_dump(exclude={'password'}),password_hash=hash_password(p.password)); db.add(x); db.commit(); db.refresh(x); audit(db,u.email,'create','user',x.id); return {'id':x.id}
@router.get('/users')
def users(db:Session=Depends(get_db),u:User=Depends(current_user)):
    q=select(User)
    if u.role==Role.manager: q=q.where(User.department_id==u.department_id)
    elif u.role not in {Role.admin,Role.director}: q=q.where(or_(User.id==u.id,User.manager_id==u.id))
    return [{'id':x.id,'email':x.email,'name':x.full_name,'role':x.role,'department_id':x.department_id,'manager_id':x.manager_id} for x in db.scalars(q).all()]
@router.post('/departments')
def create_department(p:DepartmentCreate,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.director))):
    x=Department(**p.model_dump()); db.add(x); db.commit(); db.refresh(x); audit(db,u.email,'create','department',x.id); return {'id':x.id}
@router.get('/departments')
def departments(db:Session=Depends(get_db),_:User=Depends(current_user)): return [{'id':x.id,'name':x.name,'code':x.code,'parent_id':x.parent_id} for x in db.scalars(select(Department).where(Department.active==True)).all()]
@router.post('/teams')
def create_team(p:TeamCreate,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.director,Role.manager))):
    x=Team(name=p.name,department_id=p.department_id,leader_id=p.leader_id,description=p.description); db.add(x); db.flush(); [db.add(TeamMember(team_id=x.id,user_id=i)) for i in p.member_ids]; db.commit(); audit(db,u.email,'create','team',x.id); return {'id':x.id}
@router.get('/teams/mine')
def my_teams(db:Session=Depends(get_db),u:User=Depends(current_user)):
    ids=select(TeamMember.team_id).where(TeamMember.user_id==u.id); q=select(Team).where(or_(Team.leader_id==u.id,Team.id.in_(ids))); return [{'id':x.id,'name':x.name,'leader_id':x.leader_id,'department_id':x.department_id} for x in db.scalars(q).all()]
@router.post('/sources')
def create_source(p:DataSourceCreate,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.data_engineer))):
    x=DataSource(**p.model_dump(exclude={'password'}),encrypted_password=encrypt_secret(p.password)); db.add(x); db.commit(); db.refresh(x); audit(db,u.email,'create','data_source',x.id); return {'id':x.id}
@router.get('/sources')
def sources(db:Session=Depends(get_db),u:User=Depends(current_user)): return [{'id':x.id,'name':x.name,'db_type':x.db_type,'database':x.database,'enabled':x.enabled} for x in db.scalars(select(DataSource)).all()]
@router.post('/sources/{sid}/test')
def test_source(sid:str,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.data_engineer))):
    s=db.get(DataSource,sid); ok=connector(s).test() if s else False; audit(db,u.email,'test_connection','data_source',sid,{'success':ok}); return {'success':ok}
@router.get('/sources/{sid}/catalog')
def catalog(sid:str,schema:str|None=None,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.data_engineer,Role.data_steward))):
    s=db.get(DataSource,sid)
    if not s: raise HTTPException(404,'Fonte não encontrada')
    rows=connector(s).catalog(schema); audit(db,u.email,'browse_catalog','data_source',sid,{'objects':len(rows)}); return {'objects':rows}
@router.post('/sources/{sid}/select-tables')
def select_tables(sid:str,objects:list[dict],db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.data_engineer,Role.data_steward))):
    made=[]
    for o in objects:
        x=Dataset(source_id=sid,name=o.get('alias') or o['qualified_name'].replace('.','_'),source_object=o['qualified_name'],extraction_mode=o.get('extraction_mode','full'),incremental_column=o.get('incremental_column'),schedule_cron=o.get('schedule_cron'),owner_department_id=o.get('department_id') or u.department_id,sensitive_columns=o.get('sensitive_columns',[]),masking_policy=o.get('masking_policy',{})); db.add(x); db.flush(); made.append(x.id)
    db.commit(); audit(db,u.email,'bulk_select_tables','data_source',sid,{'datasets':len(made)}); return {'created':made}
@router.post('/datasets')
def create_dataset(p:DatasetCreate,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.data_engineer,Role.data_steward))): x=Dataset(**p.model_dump()); db.add(x); db.commit(); db.refresh(x); audit(db,u.email,'create','dataset',x.id); return {'id':x.id}
@router.get('/datasets')
def datasets(db:Session=Depends(get_db),u:User=Depends(current_user)): return [{'id':x.id,'name':x.name,'source_object':x.source_object,'zone':x.destination_zone,'department_id':x.owner_department_id,'schedule':x.schedule_cron} for x in db.scalars(visible_dataset_query(u)).all()]
@router.post('/datasets/{did}/run')
def run_dataset(did:str,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.data_engineer))): task=ingest_dataset_task.delay(did); audit(db,u.email,'queue_ingestion','dataset',did,{'task_id':task.id}); return {'queued':True,'task_id':task.id}
@router.post('/dashboards')
def dashboard_create(p:DashboardCreate,db:Session=Depends(get_db),u:User=Depends(current_user)): x=Dashboard(**p.model_dump(),owner_id=u.id); db.add(x); db.commit(); db.refresh(x); return {'id':x.id}
@router.post('/dashboards/{did}/widgets')
def widget_create(did:str,p:WidgetCreate,db:Session=Depends(get_db),u:User=Depends(current_user)): x=DashboardWidget(**p.model_dump(),dashboard_id=did); db.add(x); db.commit(); return {'id':x.id}
@router.get('/dashboards')
def 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)); return [{'id':x.id,'name':x.name,'layout':x.layout,'is_shared':x.is_shared} for x in db.scalars(q).all()]
@router.post('/reports')
def report_create(p:ReportCreate,db:Session=Depends(get_db),u:User=Depends(current_user)): x=Report(**p.model_dump(),owner_id=u.id); db.add(x); db.commit(); db.refresh(x); return {'id':x.id}
@router.get('/reports')
def reports(db:Session=Depends(get_db),u:User=Depends(current_user)): return [{'id':x.id,'name':x.name,'format':x.format,'definition':x.definition} for x in db.scalars(select(Report).where(or_(Report.owner_id==u.id,Report.department_id==u.department_id))).all()]
@router.post('/automations')
def automation_create(p:AutomationCreate,db:Session=Depends(get_db),u:User=Depends(current_user)): x=Automation(**p.model_dump(),owner_id=u.id); db.add(x); db.commit(); db.refresh(x); return {'id':x.id}
@router.get('/automations')
def automations(db:Session=Depends(get_db),u:User=Depends(current_user)): return [{'id':x.id,'name':x.name,'trigger':x.trigger_type,'action':x.action_type,'schedule':x.schedule_cron,'enabled':x.enabled} for x in db.scalars(select(Automation).where(Automation.owner_id==u.id)).all()]
@router.post('/messages')
def send_message(p:MessageCreate,db:Session=Depends(get_db),u:User=Depends(current_user)): x=Message(**p.model_dump(),sender_id=u.id); db.add(x); db.commit(); return {'id':x.id}
@router.get('/messages')
def messages(db:Session=Depends(get_db),u:User=Depends(current_user)): return [{'id':x.id,'sender_id':x.sender_id,'subject':x.subject,'body':x.body,'created_at':x.created_at} for x in db.scalars(select(Message).where(or_(Message.recipient_id==u.id,Message.sender_id==u.id)).order_by(Message.created_at.desc())).all()]
def parse_api_docs(text_doc,spec):
    if spec: return spec
    try: return json.loads(text_doc)
    except Exception: pass
    routes=[]
    for method,path in re.findall(r'(?im)\b(GET|POST|PUT|PATCH|DELETE)\s+(/[^\s]+)',text_doc): routes.append({'method':method.upper(),'path':path})
    return {'openapi':'generated','paths':{r['path']:{r['method'].lower():{'summary':'Importado da documentação'}} for r in routes}}
@router.post('/api-builder/import')
def import_api(p:ApiImport,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin,Role.data_engineer))):
    spec=parse_api_docs(p.documentation,p.specification); generated=[{'method':m.upper(),'path':path,'operation':cfg.get('operationId') or cfg.get('summary','')} for path,ops in spec.get('paths',{}).items() for m,cfg in ops.items()]
    x=ApiDefinition(name=p.name,direction=p.direction,base_url=p.base_url,specification=spec,generated_routes=generated,auth_type=p.auth_type,encrypted_auth=encrypt_secret(p.auth_secret) if p.auth_secret else '',owner_id=u.id,department_id=p.department_id); db.add(x); db.commit(); db.refresh(x); audit(db,u.email,'generate_api','api_definition',x.id,{'routes':len(generated)}); return {'id':x.id,'routes':generated,'openapi':spec}
@router.get('/api-builder')
def api_defs(db:Session=Depends(get_db),u:User=Depends(current_user)): return [{'id':x.id,'name':x.name,'direction':x.direction,'base_url':x.base_url,'routes':x.generated_routes} for x in db.scalars(select(ApiDefinition)).all()]
@router.post('/ai/providers')
def ai_provider(p:AIProviderCreate,db:Session=Depends(get_db),u:User=Depends(require_roles(Role.admin))): x=AIProvider(**p.model_dump(exclude={'api_key'}),encrypted_api_key=encrypt_secret(p.api_key)); db.add(x); db.commit(); return {'id':x.id}
@router.post('/ai/query')
def ai_query(p:AIQuery,db:Session=Depends(get_db),u:User=Depends(current_user)):
    allowed={x.id for x in db.scalars(visible_dataset_query(u)).all()}; requested=[x for x in p.dataset_ids if x in allowed]
    context={'dataset_ids':requested,'max_rows':min(p.max_rows,1000),'policy':'Somente dados autorizados, sanitizados e minimizados; nenhuma credencial ou linha bruta é enviada automaticamente.'}
    x=AIConversation(user_id=u.id,provider_id=p.provider_id,prompt=p.prompt,sanitized_context=context,status='queued'); db.add(x); db.commit(); audit(db,u.email,'ai_query','ai_conversation',x.id,{'datasets':requested}); return {'id':x.id,'status':'queued','security':context['policy']}
@router.get('/runs')
def runs(db:Session=Depends(get_db),_:User=Depends(current_user)): return [{'id':x.id,'dataset_id':x.dataset_id,'status':x.status,'rows':x.row_count,'started_at':x.started_at,'finished_at':x.finished_at,'error':x.error_message} for x in db.scalars(select(IngestionRun).order_by(IngestionRun.started_at.desc()).limit(100)).all()]
@router.get('/audit')
def audits(db:Session=Depends(get_db),_:User=Depends(require_roles(Role.admin,Role.auditor))): return [{'actor':x.actor,'action':x.action,'entity_type':x.entity_type,'entity_id':x.entity_id,'details':x.details,'created_at':x.created_at} for x in db.scalars(select(AuditLog).order_by(AuditLog.created_at.desc()).limit(300)).all()]