import streamlit as st

from sqlalchemy import create_engine,inspect as sqlinspect
from passlib.context import CryptContext
from fastapi import Depends, HTTPException, status
from fastapi.security import OAuth2PasswordBearer

pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
oauth2_scheme = OAuth2PasswordBearer(tokenUrl="token")
oauth2 = Depends(oauth2_scheme)

@st.cache_data(ttl=600)
def getTableMetadata(pdatabase,table):
    conn = st.connection(pdatabase, type="sql")
    # Criando o motor SQLAlchemy
    engine = create_engine(conn._instance.url)

    # Inspecionando as tabelas
    inspector = sqlinspect(engine)

    if table == None:
        ret = []
        schem = inspector.get_schema_names()
        for x in schem:
            table_names = inspector.get_table_names(schema=x)
            tsort = sorted(table_names)
            for t in tsort:
                ret.append(f"{x}.{t}")            
        return ret

    # Obter informações sobre uma tabela específica
    schtab = table.split('.')
    if len(schtab)>1:
        table_columns = inspector.get_columns(schtab[1],schtab[0])
    else:
        table_columns = inspector.get_columns(table)
    return table_columns

@st.cache_data(ttl=600)
def get_foreign_keys(pdatabase,table):
    conn = st.connection(pdatabase, type="sql")
    # Criando o motor SQLAlchemy
    engine = create_engine(conn._instance.url)

    # Inspecionando as tabelas
    inspector = sqlinspect(engine)

    
    schtab = table.split('.')
    if len(schtab)>1:
        fks = inspector.get_foreign_keys(schtab[1],schtab[0])  
    else:
        fks = inspector.get_foreign_keys(table)  
    return fks


@st.cache_resource(ttl=600)
def get_value_from_cache():
    return {}
  