Untitled

product_view_resolver.py:
from typing import List, Optional
from sqlalchemy.orm import Session, joinedload
from sqlalchemy import or_

from app.graphql.schema import Info
from app.graphql.types.product_type import ProductType, ProductFieldType, StatusType, CountryType, TherapeuticType
from app.models import Product, User


async def product_data(
    self, info: Info, user: str = None, page: int = 1, rows: int = 10
) -> List[ProductType]:
    session: Session = info.context.session
    try:
        # Fetch user details
        user_obj = session.query(User)
        if user:
            user_obj = user_obj.filter_by(login=user).first()
        if not user_obj:
            return "User not found"
        # Get associated countries and therapeutic areas
        country_ids = [uc.id for uc in user_obj.users_countries]
        ta_ids = [ut.id for ut in user_obj.users_therapeutics]
        # Apply filters on Product records
        products_query = session.query(Product).filter(Product.is_deleted != 2)
        print(f"Products after is_deleted filter: {products_query.count()}")
        products_query = products_query.filter(
            or_(
            Product.last_active > 0,
            Product.current_record == 1,
            Product.is_deleted == 1
            )
        )
        print(f"Products after last active filter: {products_query.count()}")
        if country_ids:
            products_query = products_query.filter(Product.country_id.in_(country_ids))
        print(f"Products after country filter: {products_query.count()}")
        if ta_ids:
            products_query = products_query.filter(Product.th_area.in_(ta_ids))
        print(f"Products after therapeutic area filter: {products_query.count()}")
        products = products_query.options(
            joinedload(Product.status),
            joinedload(Product.users_country),
            joinedload(Product.therapeutic),
            joinedload(Product.product_fields)
        ).all()

        # Construct product response
        response = []
        for product in products:
            product_fields = [
                ProductFieldType(
                    record_id=pf.record_id,  # Corrected to use product_field_id
                    field_id=pf.field_id,
                    max_field_value=pf.field_value,  # Set field_value as max_field_value
                    field_detail=FieldDetailType(
                        field_id=pf.field_detail.field_id,
                        field_name=pf.field_detail.field_name
                    )
                )
                for pf in product.product_fields
            ]
            
            response.append(ProductType(
                record_id=product.record_id,
                janssen_mstr_prdct_nm=product.janssen_mstr_prdct_nm,
                product_phase=product.product_phase,
                jnj_full_compound_id=product.jnj_full_compound_id,
                generic_name=product.generic_name,
                jnj_flag=product.jnj_flag,
                product_status=product.product_status,
                current_record=product.current_record,
                last_active=product.last_active,
                created_date=product.created_date,
                updated_date=product.updated_date,
                status=StatusType(
                    status_id=product.status.id,
                    status_desc=product.status.description
                ),
                country=CountryType(
                    country_id=product.country.id,
                    country_name=product.country.name
                ),
                therapeutic=TherapeuticType(
                    ta_id=product.therapeutic.id,
                    ta_name=product.therapeutic.name
                ),
                product_fields=product_fields  # Add product_fields to the response
            ))

        return response

    finally:
        session.close()

prdView.py:
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, and_, or_
from sqlalchemy.orm import sessionmaker, relationship, declarative_base, joinedload
from fastapi import FastAPI, Depends, HTTPException
import strawberry
from strawberry.fastapi import GraphQLRouter
from typing import List, Optional
from strawberry.schema.config import StrawberryConfig

# 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"
Base = declarative_base()
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# SQLAlchemy models
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')

class Status(Base):
    __tablename__ = "mdm_status"
    STATUS_ID = Column(Integer, primary_key=True)
    STATUS_DESC = Column(String)

class TherapeuticArea(Base):
    __tablename__ = "mdm_therapeutic"
    TA_ID = Column(Integer, primary_key=True)
    TA_NAME = Column(String)

class Country(Base):
    __tablename__ = "mdm_countries"
    COUNTRY_ID = Column(Integer, primary_key=True)
    COUNTRY_NAME = Column(String)

class Product(Base):
    __tablename__ = "mdm_product"
    record_id = Column("RECORD_ID", Integer, primary_key=True)
    country_id = Column("COUNTRY_ID", Integer, ForeignKey("mdm_countries.COUNTRY_ID"))
    request_status_id = Column("REQUEST_STATUS_ID", Integer, ForeignKey("mdm_status.STATUS_ID"))
    th_area = Column("TH_AREA", Integer, ForeignKey("mdm_therapeutic.TA_ID"))
    is_deleted = Column("IS_DELETED", Integer)
    janssen_mstr_prdct_nm = Column("JANSSEN_MSTR_PRDCT_NM", String)
    product_phase = Column("PRODUCT_PHASE", String)
    jnj_full_compound_id = Column("JNJ_FULL_COMPOUND_ID", String)
    generic_name = Column("GENERIC_NAME", String)
    jnj_flag = Column("JNJ_FLAG", String)
    product_status = Column("PRODUCT_STATUS", String)
    current_record = Column("CURRENT_RECORD", Integer)
    last_active = Column("LAST_ACTIVE", Integer)
    created_date = Column("CREATED_DATE", String)
    updated_date = Column("UPDATED_DATE", String)
    
    # Relationships
    status = relationship("Status")
    therapeutic_area = relationship("TherapeuticArea")
    country = relationship("Country")
    product_fields = relationship("ProductField", back_populates="product")

class FieldDetail(Base):
    __tablename__ = "mdm_field_details"
    field_id = Column(Integer, primary_key=True)
    field_name = Column(String)
    is_dynamic = Column(String)
    is_active = Column(String)
    is_deleted = Column(String)

    # Back reference to ProductField
    product_fields = relationship("ProductField", back_populates="field_detail")

class ProductField(Base):
    __tablename__ = "mdm_product_field"
    product_field_id = Column(Integer, primary_key=True)
    record_id = Column(Integer, ForeignKey("mdm_product.RECORD_ID"))
    field_value = Column(String) 
    field_id = Column(Integer, ForeignKey("mdm_field_details.field_id"))

    # Relationships
    product = relationship("Product", back_populates="product_fields")
    field_detail = relationship("FieldDetail", back_populates="product_fields")

# GraphQL types
@strawberry.type
class StatusType:
    status_id: int
    status_desc: str

@strawberry.type
class TherapeuticAreaType:
    ta_id: int
    ta_name: str

@strawberry.type
class CountryType:
    country_id: int
    country_name: str

@strawberry.type
class FieldDetailType:
    field_id: int
    field_name: str

@strawberry.type
class ProductFieldType:
    record_id: int
    field_id: int
    max_field_value: Optional[str]  # Corrected definition
    field_detail: FieldDetailType

@strawberry.type
class ProductType:
    record_id: int
    janssen_mstr_prdct_nm: Optional[str]
    product_phase: str
    jnj_full_compound_id: str
    generic_name: str
    jnj_flag:  Optional[str]
    product_status: str
    current_record: int
    last_active: int
    created_date: str
    updated_date: str
    status: StatusType
    country: CountryType
    therapeutic_area: TherapeuticAreaType
    product_fields: List[ProductFieldType]  # Added product_fields relationship

# GraphQL query
@strawberry.type
class Query:
    @strawberry.field
    def products(self, user: str) -> List[ProductType]:
        session = SessionLocal()
        try:
            # Fetch user details
            user_obj = session.query(User).filter(User.MDM_LOGIN == user).first()
            # print("USER: ", user_obj.countries)
            if not user_obj:
                raise HTTPException(status_code=404, detail="User not found")
            
            # Get associated countries and therapeutic areas
            country_ids = [uc.COUNTRY_ID for uc in user_obj.countries]
            ta_ids = [ut.TA_ID for ut in user_obj.therapeutics]
            print("IDS: ", country_ids, ta_ids)

            products_query = session.query(Product).filter(Product.is_deleted != 2)
            print(f"Products after is_deleted filter: {products_query.count()}")
            products_query = products_query.filter(
                or_(
                Product.last_active > 0,
                Product.current_record == 1,
                Product.is_deleted == 1
                )
            )
            print(f"Products after last active filter: {products_query.count()}")
            if country_ids:
                products_query = products_query.filter(Product.country_id.in_(country_ids))
            print(f"Products after country filter: {products_query.count()}")
            if ta_ids:
                products_query = products_query.filter(Product.th_area.in_(ta_ids))
            print(f"Products after therapeutic area filter: {products_query.count()}")
            products = products_query.options(
                joinedload(Product.status),
                joinedload(Product.country),
                joinedload(Product.therapeutic_area),
                joinedload(Product.product_fields)
            ).all()

            # Apply filters on Product records
            # products = (
            #     session.query(Product)
            #     .filter(
            #         Product.is_deleted != 2,  # Check if product is not marked as deleted
            #         or_(
            #             Product.last_active > 0,  # Last active condition
            #             Product.current_record == 1,  # Current record condition
            #             Product.is_deleted == 1  # Allow deleted product if marked with 1
            #         ),
            #         Product.country_id.in_(country_ids) if country_ids else True,
            #         Product.th_area.in_(ta_ids) if ta_ids else True
            #     )
            #     .options(
            #         joinedload(Product.status),
            #         joinedload(Product.country),
            #         joinedload(Product.therapeutic_area),
            #         joinedload(Product.product_fields)  # Load product_fields relationship
            #     )
            #     #.limit(10)
            #     .all()
            # )

            # Construct product response
            response = []
            for product in products:
                product_fields = [
                    ProductFieldType(
                        record_id=pf.record_id,  # Corrected to use product_field_id
                        field_id=pf.field_id,
                        max_field_value=pf.field_value,  # Set field_value as max_field_value
                        field_detail=FieldDetailType(
                            field_id=pf.field_detail.field_id,
                            field_name=pf.field_detail.field_name
                        )
                    )
                    for pf in product.product_fields
                ]
                
                response.append(ProductType(
                    record_id=product.record_id,
                    janssen_mstr_prdct_nm=product.janssen_mstr_prdct_nm,
                    product_phase=product.product_phase,
                    jnj_full_compound_id=product.jnj_full_compound_id,
                    generic_name=product.generic_name,
                    jnj_flag=product.jnj_flag,
                    product_status=product.product_status,
                    current_record=product.current_record,
                    last_active=product.last_active,
                    created_date=product.created_date,
                    updated_date=product.updated_date,
                    status=StatusType(
                        status_id=product.status.STATUS_ID,
                        status_desc=product.status.STATUS_DESC
                    ),
                    country=CountryType(
                        country_id=product.country.COUNTRY_ID,
                        country_name=product.country.COUNTRY_NAME
                    ),
                    therapeutic_area=TherapeuticAreaType(
                        ta_id=product.therapeutic_area.TA_ID,
                        ta_name=product.therapeutic_area.TA_NAME
                    ),
                    product_fields=product_fields  # Add product_fields to the response
                ))

            return response

        finally:
            session.close()

# GraphQL setup
schema = strawberry.Schema(query=Query, config=StrawberryConfig(auto_camel_case=False))
graphql_app = GraphQLRouter(schema)

app = FastAPI()
app.include_router(graphql_app, prefix="/graphql")

if __name__ == "__main__":
    import uvicorn
    uvicorn.run(app, host="0.0.0.0", port=8000)