Untitled

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 = ""
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()
            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]

            # 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)