Untitled

from sqlalchemy import select, text
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.future import select
from sqlalchemy.orm import joinedload
from config.settings import engine

# Function to execute the stored procedure in the database
async def execute_stored_procedure(session: AsyncSession):
    # Stored procedure execution
    await session.execute(text("CALL jsamdm_qa.productViewSP()"))
    await session.commit()

# Fetch dynamic fields for products
async def fetch_dynamic_product_fields(session: AsyncSession):
    # Fetch all dynamic fields (pivot logic from the stored procedure)
    pivot_query = text("""
        SELECT mp.RECORD_ID, 
               GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(mfd.FIELD_NAME = "', mfd.FIELD_NAME, '", mpf.FIELD_VALUE, NULL)) AS ', mfd.FIELD_NAME)) AS pivot_fields
        FROM mdm_field_details AS mfd
        LEFT JOIN mdm_product_field AS mpf ON mfd.FIELD_ID = mpf.FIELD_ID
        WHERE mfd.IS_DYNAMIC = 'Y' AND mfd.IS_ACTIVE = 'Y' AND mfd.IS_DELETED = '0'
        GROUP BY mpf.RECORD_ID
    """)
    
    result = await session.execute(pivot_query)
    pivot_query_result = result.fetchall()
    
    # Dynamically create a query to fetch products and dynamic fields
    product_query = f"""
        SELECT mp.*, ms.STATUS_DESC as REQUEST_STATUS, mc.COUNTRY_NAME, mr.REGION_NAME, mt.TA_NAME, 
               {pivot_query_result[0]['pivot_fields']}
        FROM mdm_product mp
        LEFT JOIN mdm_status ms ON mp.REQUEST_STATUS_ID = ms.STATUS_ID
        LEFT JOIN mdm_countries mc ON mc.COUNTRY_ID = mp.COUNTRY_ID
        LEFT JOIN mdm_region_countries mrc ON mrc.COUNTRY_ID = mc.COUNTRY_ID
        LEFT JOIN mdm_regions mr ON mr.REGION_ID = mrc.REGION_ID
        LEFT JOIN mdm_therapeutic mt ON mt.TA_ID = mp.TH_AREA AND mt.STATUS_ID = 1
        WHERE (mp.LAST_ACTIVE > 0 OR mp.CURRENT_RECORD = 1 OR mp.IS_DELETED = 1) AND mp.IS_DELETED != 2
    """
    
    dynamic_query = text(product_query)
    result = await session.execute(dynamic_query)
    products = result.fetchall()

    return products

# Fetch product list and dynamic fields
async def fetch_products(session: AsyncSession, limit: int = 10, offset: int = 0):
    # First, execute the stored procedure
    await execute_stored_procedure(session)
    
    # Then, fetch the product data including dynamic fields
    products = await fetch_dynamic_product_fields(session)
    
    # Paginate the products based on limit and offset
    return products[offset: offset + limit]

# Example of GraphQL resolver for fetching product data
async def resolve_products(limit: int, offset: int, session: AsyncSession):
    products = await fetch_products(session, limit=limit, offset=offset)
    return products

# Example query for product details
async def resolve_field_details(is_active: bool, is_deleted: bool, session: AsyncSession):
    field_details_query = select(field_details).where(
        field_details.c.is_active == is_active,
        field_details.c.is_deleted == is_deleted
    )
    result = await session.execute(field_details_query)
    return result.fetchall()