from datetime import datetime, timezone
from sqlalchemy import select
from sqlalchemy.orm import Session
from app.connectors.sqlalchemy_connector import SQLAlchemyConnector
from app.core.security import decrypt_secret
from app.models.entities import (DataSource,DataFieldMetadata,TenantModuleAssignment,
    VisualGovernancePolicy,User,TeamMember)

MODULES=[
 ('data_lake','Data Lake'),('connectors','Conectores de Bancos'),('catalog','Catálogo Visual'),
 ('governance','Governança de Dados'),('etl','ETL/ELT e Sincronização'),('bi','Dashboards e BI'),
 ('reports','Relatórios'),('scheduling','Agendamentos'),('workflow','Workflow'),('api_studio','API Studio'),
 ('ai','IA Corporativa e RAG'),('communication','Comunicação'),('mobile','Aplicativo/PWA'),
 ('mdm','Master Data Management'),('data_quality','Qualidade de Dados'),('observability','Observabilidade'),
 ('high_availability','Alta Disponibilidade'),('semantic_layer','Camada Semântica'),
 ('federated_query','Consultas Federadas'),('saas_admin','Administração SaaS'),('billing','Billing')]

def module_catalog():
    return [{'code':c,'name':n,'category':'core' if c in {'data_lake','connectors','catalog','governance'} else 'optional'} for c,n in MODULES]

def ensure_module_assignments(db:Session,tenant_id:str,user_id:str|None=None):
    current={x.module_code:x for x in db.scalars(select(TenantModuleAssignment).where(TenantModuleAssignment.tenant_id==tenant_id)).all()}
    for code,_ in MODULES:
        if code not in current:
            db.add(TenantModuleAssignment(tenant_id=tenant_id,module_code=code,enabled=code in {'data_lake','connectors','catalog','governance'},activated_by=user_id))
    db.commit()

def connector(source:DataSource):
    return SQLAlchemyConnector(source.db_type.value,source.host,source.port,source.database,source.username,decrypt_secret(source.encrypted_password),source.options)

def discover_and_sync_fields(db:Session,tenant_id:str,source:DataSource,schema:str|None,user_id:str):
    objects=connector(source).catalog(schema); count=0
    for obj in objects:
        for col in obj.get('columns',[]):
            item=db.scalar(select(DataFieldMetadata).where(DataFieldMetadata.tenant_id==tenant_id,DataFieldMetadata.source_id==source.id,DataFieldMetadata.qualified_object==obj['qualified_name'],DataFieldMetadata.column_name==col['name']))
            if not item:
                item=DataFieldMetadata(tenant_id=tenant_id,source_id=source.id,qualified_object=obj['qualified_name'],column_name=col['name'],physical_type=col.get('type',''),nullable=col.get('nullable',True),friendly_name=col['name'].replace('_',' ').title(),updated_by=user_id)
                db.add(item); count+=1
            else:
                item.physical_type=col.get('type',item.physical_type); item.nullable=col.get('nullable',item.nullable); item.updated_by=user_id; item.updated_at=datetime.now(timezone.utc)
    db.commit(); return objects,count

def effective_policies(db:Session,tenant_id:str,user:User,dataset_id:str):
    subjects=[('user',user.id),('role',getattr(user.role,'value',user.role))]
    if user.department_id: subjects.append(('department',user.department_id))
    team_ids=db.scalars(select(TeamMember.team_id).where(TeamMember.user_id==user.id)).all()
    subjects.extend(('team',x) for x in team_ids)
    rows=db.scalars(select(VisualGovernancePolicy).where(VisualGovernancePolicy.tenant_id==tenant_id,VisualGovernancePolicy.dataset_id==dataset_id,VisualGovernancePolicy.enabled==True)).all()
    return [x for x in rows if (x.subject_type,x.subject_id) in subjects]