Untitled

stmt = (
    info.context.session.query(Product)

    # Joining related tables based on your view logic
    .join(ProductCountry, Product.id == ProductCountry.id)
    .join(UsersCountry, ProductCountry.country_id == UsersCountry.country_id)
    .join(UsersTherapeutic, Product.th_area == UsersTherapeutic.ta_id)

    # Filter to ensure data consistency for the current user
    .filter(UsersCountry.user_id == info.context.current_user.id)
    .filter(UsersTherapeutic.user_id == info.context.current_user.id)

    # Checking active records and non-deleted status
    .filter(Product.is_deleted == 0)
    .filter(Product.last_active == 1)

    # Apply the downstream view filters for publishing and record state
    .outerjoin(DownstreamRecordView, Product.mdm_id == DownstreamRecordView.mdm_id_2)
    .outerjoin(DownstreamPublish, Product.record_id == DownstreamPublish.record_id)

    .filter(
        or_(
            Product.last_active > 0,
            Product.current_record == 1,
            Product.is_deleted == 1,
        )
    )

    # Project specific columns into the query based on view
    .add_columns(
        Product.record_id.label('RECORD_ID'),
        Product.mdm_id.label('MDM_ID'),
        Product.series_id.label('SERIES_ID'),
        Product.reltio_id.label('RELTIO_ID'),
        Product.country_id.label('COUNTRY_ID'),
        Product.product_name.label('PRODUCT_NAME'),
        Product.group_type.label('GROUP_TYPE'),
        Product.janssen_mstr_prdct_nm.label('JANSSEN_MSTR_PRDCT_NM'),
        Product.product_phase.label('PRODUCT_PHASE'),
        Product.generic_name.label('GENERIC_NAME'),
        Product.jnj_flag.label('JNJ_FLAG'),
        Product.th_area.label('TH_AREA'),
        Product.product_status.label('PRODUCT_STATUS'),
        Product.ta_name.label('TA_NAME'),
        DownstreamRecordView.dwn_7.label('DWN_7'),
        DownstreamRecordView.dwn_8.label('DWN_8'),
        case(
            [(DownstreamPublish.record_id.isnot(None), 'Yes')],
            else_='No'
        ).label('PUBLISHED'),
    )
)