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, Depends, 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()

# Step 1: Define your static models first (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')

# Step 2: Dynamically reflect the mdm_product_view table

table_name = 'mdm_product_view'
table = Table(table_name, metadata, autoload_with=engine)

# Reflect column names and primary keys
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

# Dynamically create the 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)

# Step 3: Define GraphQL schema and types dynamically based on reflected columns

# Function to 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]  # Use String for datetime
    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))

# Step 4: Define GraphQL resolver for fetching product data and executing the stored procedure

@strawberry.type
class Query:
    @strawberry.field
    def get_product_data(self, user: str, page: int = 1, rows: int = 10) -> List[Record]:
        db = SessionLocal()
        try:
            # Call the stored procedure to dynamically update the views
            db.execute(text("CALL jsamdm_qa.productViewSP()"))
            db.commit()

            # 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:
            db.close()

# Step 5: Set up the FastAPI and GraphQL routes

# Create the Strawberry schema
schema = strawberry.Schema(query=Query, config=StrawberryConfig(auto_camel_case=False))

# Set up FastAPI and GraphQL
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")

# Step 6: Run the FastAPI application
if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=8000)