Untitled

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, select, MetaData, Table, Float, DateTime, Boolean, LargeBinary, BigInteger, text
from sqlalchemy.orm import relationship, sessionmaker, declarative_base
from fastapi import FastAPI, HTTPException
import strawberry
from strawberry.fastapi import GraphQLRouter
from typing import Optional, List
from datetime import datetime
import uvicorn
from strawberry.schema.config import StrawberryConfig
import dataclasses
 
# Database setup
DATABASE_URL = "mysql+pymysql://jsa_dev_admin:RM#AXR02NhAp@itx-acm-jsa-mdm-dev.czijpxum5el7.us-east-1.rds.amazonaws.com/jsamdm_dev"
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()
metadata = MetaData()
 
# Static Models (User, UserCountry, UserTherapeutic)
class User(Base):
    __tablename__ = 'mdm_users'
    MDM_USER_ID = Column(Integer, primary_key=True)
    MDM_LOGIN = Column(String, unique=True)
    countries = relationship('UserCountry', back_populates='user')
    therapeutics = relationship('UserTherapeutic', back_populates='user')
 
class UserCountry(Base):
    __tablename__ = 'mdm_users_countries'
    USER_CNTRY_ID = Column(Integer, primary_key=True)
    USER_ID = Column(Integer, ForeignKey('mdm_users.MDM_USER_ID'))
    COUNTRY_ID = Column(Integer)
    user = relationship('User', back_populates='countries')
 
class UserTherapeutic(Base):
    __tablename__ = 'mdm_users_therapeutics'
    USER_TA_ID = Column(Integer, primary_key=True)
    USER_ID = Column(Integer, ForeignKey('mdm_users.MDM_USER_ID'))
    TA_ID = Column(Integer)
    user = relationship('User', back_populates='therapeutics')
 
# Dynamically reflect the mdm_product_view table
table_name = 'mdm_product_view'
table = Table(table_name, metadata, autoload_with=engine)
column_names = table.columns.keys()
primary_keys = [key.name for key in table.primary_key.columns]
if not primary_keys:
    primary_keys = ['RECORD_ID']  # Use default primary key if none is found
 
# Dynamic ORM model
attributes = {'__tablename__': table_name}
for column in table.columns:
    column_type = column.type
    if column.name in primary_keys:
        attributes[column.name] = Column(column_type, primary_key=True)  # Treat as primary key
    else:
        attributes[column.name] = Column(column_type)  # Non-primary columns
DynamicModel = type('DynamicModel', (Base,), attributes)
 
# Map SQLAlchemy types to GraphQL types
def get_graphql_type(sqlalchemy_type):
    if isinstance(sqlalchemy_type, String):
        return Optional[str]
    elif isinstance(sqlalchemy_type, Integer):
        return Optional[int]
    elif isinstance(sqlalchemy_type, BigInteger):
        return Optional[int]
    elif isinstance(sqlalchemy_type, Float):
        return Optional[float]
    elif isinstance(sqlalchemy_type, DateTime):
        return Optional[str]
    elif isinstance(sqlalchemy_type, Boolean):
        return Optional[bool]
    elif isinstance(sqlalchemy_type, LargeBinary):
        return Optional[str]  # Use String for binary data
    else:
        return Optional[str]  # Default to String if type is not explicitly handled
 
# Create Strawberry type dynamically based on table columns
@strawberry.type
class Record:
    pass
 
fields = []
for column in table.columns:
    graphql_type = get_graphql_type(column.type)
    fields.append((column.name, graphql_type))
Record = strawberry.type(dataclasses.make_dataclass('Record', fields))
 
# GraphQL resolver for fetching product data
@strawberry.type
class Query:
    @strawberry.field
    def get_product_data(self, user: str, page: int = 1, rows: int = 10) -> List[Record]:
        db = SessionLocal()
        try:
            # Fetch user details and associated countries and therapeutic areas
            user_query = db.execute(select(User).filter_by(MDM_LOGIN=user))
            user_obj = user_query.scalar_one_or_none()
 
            if user_obj is None:
                raise HTTPException(status_code=404, detail="User not found")
 
            # Get country and TA IDs associated with the user
            country_ids = [uc.COUNTRY_ID for uc in user_obj.countries]
            ta_ids = [ut.TA_ID for ut in user_obj.therapeutics]
 
            # Build dynamic SQL query
            query = db.query(DynamicModel).filter(DynamicModel.CURRENT_RECORD == 1)
 
            if country_ids:
                query = query.filter(DynamicModel.COUNTRY_ID.in_(country_ids))
            if ta_ids:
                query = query.filter(DynamicModel.TH_AREA.in_(ta_ids))
 
            # Apply pagination
            query = query.offset((page - 1) * rows).limit(rows)
 
            results = query.all()
 
            # Convert results into Record type
            data_list = []
            for result in results:
                data = {}
                for column in column_names:
                    value = getattr(result, column)
                    if isinstance(value, datetime):
                        value = value.isoformat()
                    data[column] = value
                data_list.append(Record(**data))
 
            return data_list
        finally:
            # Call the stored procedure here after fetching data
            try:
                db.execute(text("CALL jsamdm_qa.productViewSP()"))
                db.commit()
            except Exception as e:
                db.rollback()
                raise HTTPException(status_code=500, detail=f"Failed to refresh views: {str(e)}")
            db.close()
 
# FastAPI and GraphQL Setup
schema = strawberry.Schema(query=Query, config=StrawberryConfig(auto_camel_case=False))
app = FastAPI()
 
@app.get("/")
def read_root():
    return {"message": "Welcome to the FastAPI application. Go to /graphql to access the GraphQL interface."}
 
graphql_app = GraphQLRouter(schema)
app.include_router(graphql_app, prefix="/graphql")
 
# Run the FastAPI application
if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=8000)