CPD Results

LOG.info("Getting details for studyId: {} , participantId: {}", studyDisplayId, participantDisplayId);
        // Create params object
        Params params = new Params("DCLP1-001-001");
        model.addAttribute("params", params);

        List<Map<String, LocalDate>> dateRangeValues = List.of(
                new HashMap<String, LocalDate>() {
                    {
                        put("start_date", LocalDate.of(2023, 6, 1));
                        put("end_date", LocalDate.of(2023, 6, 10));
                    }
                });
        LocalDate startDate = LocalDate.of(2023, 6, 1);
        LocalDate endDate = LocalDate.of(2023, 6, 10);

        // Calculate the difference in days
        long daysBetween = ChronoUnit.DAYS.between(startDate, endDate);

        // Format the number of days as an integer
        String formattedDays = String.format("%d", daysBetween);

        model.addAttribute("daysBetween", formattedDays);

        // Sample data for percentage_of_time
        Map<String, String> percentageOfTime = Map.of("percentage_active", "75");

        // Sample data for no_of_days_worn
        Map<String, String> noOfDaysWorn = Map.of("Number_of_Days_CGM_Worn", "28");

        // Sample data for mean_glucose_for_each_patient
        Map<String, String> meanGlucoseForEachPatient = Map.of("MeanGlucose", "150");

        // Sample data for indicator
        Map<String, String> indicator = Map.of("GMI", "7.0");

        // Sample data for glycemic_variability_per_patient
        Map<String, String> glycemicVariabilityPerPatient = Map.of("coefficient_of_variation", "30");

        model.addAttribute("date_range_values", dateRangeValues);
        model.addAttribute("percentage_of_time", percentageOfTime);
        model.addAttribute("no_of_days_worn", noOfDaysWorn);
        model.addAttribute("mean_glucose_for_each_patient", meanGlucoseForEachPatient);
        model.addAttribute("indicator", indicator);
        model.addAttribute("glycemic_variability_per_patient", glycemicVariabilityPerPatient);

        // Create a DecimalFormat object with one decimal place
        DecimalFormat df = new DecimalFormat("#.0");

        // Format the number to one decimal place
        String formattedNumber1 = df.format(1.111);
        String formattedNumber2 = df.format(8.9455);
        String formattedNumber3 = df.format(88.7);
        String formattedNumber4 = df.format(1.2);
        String formattedNumber5 = df.format(0.2);

        // Example data structure for timeInRangesView
        List<TimeInRange> timeInRangesView = List.of(
                new TimeInRange("Participant1", 250, 12.3, "23h 20m", formattedNumber1),
                new TimeInRange("Participant2", 180, 50.0, "195h 40m", formattedNumber2),
                new TimeInRange("Participant3", 70, 50.0, "1947h 10m", formattedNumber3),
                new TimeInRange("Participant4", 54, 50.0, "25h 20m", formattedNumber4),
                new TimeInRange("Participant5", 22, 50.0, "03h 45m", formattedNumber5)
        // Add more entries as needed
        );

        model.addAttribute("timeInRangesView", timeInRangesView);
        model.addAttribute("isCalculationShown", false);

        List<AgpStudyParticipant> studyParticipantsWithRangeView = List.of(
                new AgpStudyParticipant("70.7", "3.71", "25.1", "0.476", "0.0322", "27.3"));

        model.addAttribute("study_participants_with_range_view", studyParticipantsWithRangeView);
        model.addAttribute("time_in_tight_range", 62.1);

        List<ComponentData> componentsPrimary = List.of(
                new ComponentData("Liability Index", 0.113, "Value 1", "mg/dL"),
                new ComponentData("Hypoglycemic Episodes", 349, "hypoglycemic_episodes", ""),
                new ComponentData("Euglycemic Episodes", 23366.0, "euglycemic_episodes", ""),
                new ComponentData("Hyperglycemic Episodes", 2629, "hyperglycemic_episodes", ""),
                new ComponentData("M Value", 0.00224, "m_value", "mg/dL"),
                new ComponentData("Mean Amplitude", 144, "mean_amplitude", ""),
                new ComponentData("Average Daily Risk Range", 144, "average_daily_risk", "mg/dL"),
                new ComponentData("J Index", 645, "j_index", "mg/dL"),
                new ComponentData("Low Blood Glucose Index", 391116186, "lbgi", ""),
                new ComponentData("High Blood Glucose Index", 24485149.1, "hbgi", "")

        );

        model.addAttribute("componentsPrimary", componentsPrimary);
        List<ComponentData> componentsSecondary = List.of(
                new ComponentData("Glycemic Risk Assessment Diabetes Equation (GRADE)", 7.79, "GRADE", ""),
                new ComponentData("Continuous Overall Net Glycemic Action (CONGA)", 5.64, "conga_hourly_mean", ""),
                new ComponentData("Mean of Daily Differences", 0.000835136468891167, "mean_daily_difference", "")

        );

        model.addAttribute("componentsSecondary", componentsSecondary);

        // Page description and notes to show on top of the page
        String[] pageDescription = {
                "The Participants Detail page is a comprehensive report that includes glucose statistics, such as the Ambulatory Glucose Profile (AGP), Glycemia Risk Index (GRI), Daily Glucose Profile, and all other metrics data."

        };
        String[] notes = {
                "The loading time for the AGP and GRI is currently a bit high due to data fetching and calculations. It needs optimization.",
                "Need to optimize the UI of the loader for individual metrics and charts."
        };
        model.addAttribute("pageDescription", pageDescription);
        model.addAttribute("notes", notes);

        return presentation.populateModel("page/investigator/participantInfo", model, request);

}

    private Condition createCondition(Map<String, FilterModel> filter) {
        List<Condition> conditionsList = new ArrayList<>();

        // Process the filter model
        filter.forEach((field, filterModel) -> {
            // Extract the logical operator if present
            List<Condition> filterConditions = new ArrayList<>();
            if (filterModel.operator() == null) {
                String type = filterModel.type();
                Object filterValue = filterModel.filter();
                Object secondFilter = filterModel.secondFilter();
                // Add individual condition to the list of conditions
                filterConditions.add(individualCondition(field, type, filterValue, secondFilter));
                conditionsList.add(and(filterConditions));

            }

            else if (filterModel.conditions() != null) {
                // Iterate over each condition and create the respective condition
                for (TabularRowsRequest.ConditionsFilterModel conditionModel : filterModel.conditions()) {
                    String type = conditionModel.type();
                    Object filterValue = conditionModel.filter();
                    Object secondFilterValue = conditionModel.secondFilter();
                    // Add individual condition to the list of conditions
                    filterConditions.add(individualCondition(field, type, filterValue, secondFilterValue));
                }
                // Combine conditions using the specified operator (AND/OR)
                Condition combinedCondition;
                if (filterModel.operator().equalsIgnoreCase("AND")) {
                    combinedCondition = and(filterConditions);
                } else if (filterModel.operator().equalsIgnoreCase("OR")) {
                    combinedCondition = or(filterConditions); // Use OR here
                } else {
                    throw new IllegalArgumentException("Unknown operator '" + filterModel.operator() + "'");
                }
                // Add combined condition to conditions list
                conditionsList.add(combinedCondition);
            }

        });

        // Combine all filter conditions into the main query condition
        Condition finalCondition = and(conditionsList); // Use AND for combining all conditions

        return finalCondition;
    }

    private Condition individualCondition(final String field, String type, Object filter, Object secondFilter) {
        // Create individual condition based on filter type
        Condition individualCondition = switch (type) {
            case "like" -> field(field).likeIgnoreCase("%" + filter + "%");
            case "equals" -> field(field).eq(param(field, filter));
            case "notEqual" -> field(field).notEqual(param(field, filter));
            case "number" -> field(field).eq(param(field, filter));
            case "date" -> field(field).eq(param(field, filter));
            case "contains" -> field(field).likeIgnoreCase("%" + filter + "%");
            case "notContains" -> field(field).notLikeIgnoreCase("%" + filter + "%");
            case "startsWith" -> field(field).startsWith(filter);
            case "endsWith" -> field(field).endsWith(filter);
            case "lessOrEqual" -> field(field).lessOrEqual(filter);
            case "greaterOrEqual" -> field(field).greaterOrEqual(filter);
            case "greaterThan" -> field(field).greaterThan(filter);
            case "lessThan" -> field(field).lessThan(filter);
            case "between" -> field(field).cast(SQLDataType.REAL)
                    .between(Float.valueOf(filter.toString()),
                            Float.valueOf(secondFilter.toString()));
            case "blank" -> field(field).cast(SQLDataType.VARCHAR).eq(param(field, ""));
            case "notBlank" -> field(field).cast(SQLDataType.VARCHAR).notEqual(param(field, ""));
            default -> throw new IllegalArgumentException(
                    "Unknown filter type '" + type + "' for field '" + field + "'");
        };
        return individualCondition;

    }

private Condition createCondition(Map<String, FilterModel> filter) {
        List<Condition> conditionsList = new ArrayList<>();

        // Process the filter model
        filter.forEach((field, filterModel) -> {
            // Extract the logical operator if present
            List<Condition> filterConditions = new ArrayList<>();
            if (filterModel.operator() == null) {
                String type = filterModel.type();
                Object filterValue = filterModel.filter();
                Object secondFilter = filterModel.secondFilter();
                // Add individual condition to the list of conditions
                filterConditions.add(individualCondition(field, type, filterValue, secondFilter));
                conditionsList.add(and(filterConditions));

            }

            else if (filterModel.conditions() != null) {
                // Iterate over each condition and create the respective condition
                for (TabularRowsRequest.ConditionsFilterModel conditionModel : filterModel.conditions()) {
                    String type = conditionModel.type();
                    Object filterValue = conditionModel.filter();
                    Object secondFilterValue = conditionModel.secondFilter();
                    // Add individual condition to the list of conditions
                    filterConditions.add(individualCondition(field, type, filterValue, secondFilterValue));
                }
                // Combine conditions using the specified operator (AND/OR)
                Condition combinedCondition;
                if (filterModel.operator().equalsIgnoreCase("AND")) {
                    combinedCondition = and(filterConditions);
                } else if (filterModel.operator().equalsIgnoreCase("OR")) {
                    combinedCondition = or(filterConditions); // Use OR here
                } else {
                    throw new IllegalArgumentException("Unknown operator '" + filterModel.operator() + "'");
                }
                // Add combined condition to conditions list
                conditionsList.add(combinedCondition);
            }

        });

        // Combine all filter conditions into the main query condition
        Condition finalCondition = and(conditionsList); // Use AND for combining all conditions

        return finalCondition;
    }

    private Condition individualCondition(final String field, String type, Object filter, Object secondFilter) {
        // Create individual condition based on filter type
        Condition individualCondition = switch (type) {
            case "like" -> field(field).likeIgnoreCase("%" + filter + "%");
            case "equals" -> field(field).eq(param(field, filter));
            case "notEqual" -> field(field).notEqual(param(field, filter));
            case "number" -> field(field).eq(param(field, filter));
            case "date" -> field(field).eq(param(field, filter));
            case "contains" -> field(field).likeIgnoreCase("%" + filter + "%");
            case "notContains" -> field(field).notLikeIgnoreCase("%" + filter + "%");
            case "startsWith" -> field(field).startsWith(filter);
            case "endsWith" -> field(field).endsWith(filter);
            case "lessOrEqual" -> field(field).lessOrEqual(filter);
            case "greaterOrEqual" -> field(field).greaterOrEqual(filter);
            case "greaterThan" -> field(field).greaterThan(filter);
            case "lessThan" -> field(field).lessThan(filter);
            case "between" -> field(field).cast(SQLDataType.REAL)
                    .between(Float.valueOf(filter.toString()),
                            Float.valueOf(secondFilter.toString()));
            case "blank" -> field(field).cast(SQLDataType.VARCHAR).eq(param(field, ""));
            case "notBlank" -> field(field).cast(SQLDataType.VARCHAR).notEqual(param(field, ""));
            default -> throw new IllegalArgumentException(
                    "Unknown filter type '" + type + "' for field '" + field + "'");
        };
        return individualCondition;

    }

    @Hidden

private Condition createCondition(Map<String, FilterModel> filter) {
        List<Condition> conditionsList = new ArrayList<>();

        // Process the filter model
        filter.forEach((field, filterModel) -> {
            // Extract the logical operator if present
            List<Condition> filterConditions = new ArrayList<>();
            if (filterModel.operator() == null) {
                String type = filterModel.type();
                Object filterValue = filterModel.filter();
                Object secondFilter = filterModel.secondFilter();
                // Add individual condition to the list of conditions
                filterConditions.add(individualCondition(field, type, filterValue, secondFilter));
                conditionsList.add(and(filterConditions));

            }

            else if (filterModel.conditions() != null) {
                // Iterate over each condition and create the respective condition
                for (TabularRowsRequest.ConditionsFilterModel conditionModel : filterModel.conditions()) {
                    String type = conditionModel.type();
                    Object filterValue = conditionModel.filter();
                    Object secondFilterValue = conditionModel.secondFilter();
                    // Add individual condition to the list of conditions
                    filterConditions.add(individualCondition(field, type, filterValue, secondFilterValue));
                }
                // Combine conditions using the specified operator (AND/OR)
                Condition combinedCondition;
                if (filterModel.operator().equalsIgnoreCase("AND")) {
                    combinedCondition = and(filterConditions);
                } else if (filterModel.operator().equalsIgnoreCase("OR")) {
                    combinedCondition = or(filterConditions); // Use OR here
                } else {
                    throw new IllegalArgumentException("Unknown operator '" + filterModel.operator() + "'");
                }
                // Add combined condition to conditions list
                conditionsList.add(combinedCondition);
            }

        });

        // Combine all filter conditions into the main query condition
        Condition finalCondition = and(conditionsList); // Use AND for combining all conditions

        return finalCondition;
    }

    private Condition individualCondition(final String field, String type, Object filter, Object secondFilter) {
        // Create individual condition based on filter type
        Condition individualCondition = switch (type) {
            case "like" -> field(field).likeIgnoreCase("%" + filter + "%");
            case "equals" -> field(field).eq(param(field, filter));
            case "notEqual" -> field(field).notEqual(param(field, filter));
            case "number" -> field(field).eq(param(field, filter));
            case "date" -> field(field).eq(param(field, filter));
            case "contains" -> field(field).likeIgnoreCase("%" + filter + "%");
            case "notContains" -> field(field).notLikeIgnoreCase("%" + filter + "%");
            case "startsWith" -> field(field).startsWith(filter);
            case "endsWith" -> field(field).endsWith(filter);
            case "lessOrEqual" -> field(field).lessOrEqual(filter);
            case "greaterOrEqual" -> field(field).greaterOrEqual(filter);
            case "greaterThan" -> field(field).greaterThan(filter);
            case "lessThan" -> field(field).lessThan(filter);
            case "between" -> field(field).cast(SQLDataType.REAL)
                    .between(Float.valueOf(filter.toString()),
                            Float.valueOf(secondFilter.toString()));
            case "blank" -> field(field).cast(SQLDataType.VARCHAR).eq(param(field, ""));
            case "notBlank" -> field(field).cast(SQLDataType.VARCHAR).notEqual(param(field, ""));
            default -> throw new IllegalArgumentException(
                    "Unknown filter type '" + type + "' for field '" + field + "'");
        };
        return individualCondition;

    }

LOG.info("Update Participant Inline Data Query: {}", query);
            final var result = query
                    .fetchOneInto(JSONB.class);
            LOG.info("Edit participant Result: {}", result);
            if (result != null) {
                @SuppressWarnings("unchecked")
                Map<String, Object> resultMap = new ObjectMapper().readValue(result.data(), Map.class);
                String status = (String) resultMap.get("status");
                String message = (String) resultMap.get("message");
                final var studyId = getStudyIdOfParticipant(participantId);
                String hubInteractionId = interactionService.getHubIntercationIdOfStudyParticipant(studyId);
                if ("failure".equalsIgnoreCase(status)) {
                    LOG.info("Database status is failure. Message: {}", message);
                    LOG.warn("Failed to edit participant data: {}", message);
                    throw new IllegalArgumentException("Failed to save participant data: " + message);
                } else if ("success".equalsIgnoreCase(status)) {
                    interactionService.saveStudyParticipantInteraction(studyId, participantId,
                            hubInteractionId, ActionType.UPDATE_PARTICIPANT, ActionDescription.UPDATE_PARTICIPANT,
                            null, null, jsonInput.data(), result.data(), null,
                            ResponseCode.SUCCESS, ActionStatus.SUCCESS, ActionType.UPDATE_PARTICIPANT,
                            ActionStatus.SUCCESS);
                    return result;
                } else {
                    LOG.info("Database status is not success. Message: {} Status {}", message, status);
                    LOG.warn("Failed to edit participant data: {}", message);
                    throw new IllegalArgumentException("Failed to save participant data: " + message);
                }
            } else {
                LOG.warn("Database function returned null. Participant data may not have been saved.");
                throw new IllegalArgumentException("Failed to save participant data: Database function returned null.");
            }
        } catch (Exception e) {
            LOG.error("Error while updating participant data for participantId={}: {}", participantId, e.getMessage(),
                    e);
            final var message = "Error while updating participant data for participantId: " + participantId + " . "

public Object parentFailedParticipantFileInteraction(
            final @RequestBody @Nonnull TabularRowsRequest payload,
            final @PathVariable String schemaName,
            final @PathVariable String masterTableNameOrViewName,
            @RequestParam(required = false, defaultValue = "*") String columns,
            @RequestHeader(value = "X-Include-Generated-SQL-In-Response", required = false) boolean includeGeneratedSqlInResp,
            @RequestHeader(value = "X-Include-Generated-SQL-In-Error-Response", required = false, defaultValue = "true") boolean includeGeneratedSqlInErrorResp) {

        try {
            TypableTable typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class,
                    schemaName,
                    masterTableNameOrViewName);
            var bindValues = new ArrayList<Object>();

            final var userId = userNameService.getUserId();
            final var organizationId = partyService.getOrganizationPartyIdByUser(userId);
            Condition finalCondition = payload.filterModel() != null
                    ? customTabularFilter.createCondition(payload.filterModel())
                    : null;
            var query = udiPrimeDbConfig.dsl()
                    .selectDistinct(DSL.field("study_id"),
                            DSL.field("study_display_id"),
                            DSL.field("study_title"),
                            DSL.field("file_category"),
                            DSL.field("organization_name"),
                            DSL.field(
                                    "organization_party_id")) // Selecting the specified fields
                    .from(typableTable.table())
                    .where("1=1");
            if (finalCondition != null) {
                query = query.and(finalCondition);
            }
            query = query.and((DSL.field("study_id").isNotNull()))

bindValues.add(FileType.DATABASE);
            if (!userNameService.isAdmin()) {
                final var currentUserId = userNameService.getCurrentuserPartyId();
                query = query.and(DSL.field("created_by").eq(DSL.value(currentUserId)));
                bindValues.add(currentUserId);

            }
            if (payload.sortModel() != null && !payload.sortModel().isEmpty()) {
                query = (@org.jetbrains.annotations.NotNull SelectConditionStep<Record6<Object, Object, Object, Object, Object, Object>>) query
                        .orderBy(
                                customTabularFilter.createSortCondition(payload.sortModel(),
                                        typableTable));
            }

            LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
            LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName, masterTableNameOrViewName, (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "List of Database file uploaded against a study")

try {
            TypableTable typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class,
                    schemaName,
                    masterTableNameOrViewName);
            var bindValues = new ArrayList<Object>();

            final var userId = userNameService.getUserId();
            final var organizationId = partyService.getOrganizationPartyIdByUser(userId);
            Condition finalCondition = payload.filterModel() != null
                    ? customTabularFilter.createCondition(payload.filterModel())
                    : null;
            var query = udiPrimeDbConfig.dsl()
                    .selectDistinct(DSL.field("study_id"),
                            DSL.field("study_display_id"),
                            DSL.field("study_title"),
                            DSL.field("file_category"),
                            DSL.field("organization_name"),
                            DSL.field(
                                    "organization_party_id")) // Selecting the specified fields
                    .from(typableTable.table())
                    .where("1=1");
            if (finalCondition != null) {
                query = query.and(finalCondition);
            }
            query = query.and((DSL.field("study_id").isNotNull()))
                    .and(DSL.field("study_display_id").isNotNull())
                    .and(DSL.field("interaction_status").eq(ActionStatus.FAILED))
                    .and(DSL.field("organization_party_id").eq(DSL.value(organizationId)))
                    .and(DSL.field("file_category").eq(DSL.value(FileType.PARTICIPANT)));

if (presentation.isSuperAdmin()) {
                LOG.info("Guest access detected. Fetching permissions for 'Guest'");
                final var query = dsl.select(DSL.field("role_name"))
                        .from(DSL.table("drh_stateless_authentication.super_admin_view"))
                        .where(DSL.field("party_id").eq(userPartyId));
                roleNames = query.fetch(DSL.field("role_name"), String.class);

            } else {
                final var query = dsl
                        .select(DSL.field("jsonb_agg(DISTINCT user_roles)", JSONB.class))
                        .from("drh_stateless_authentication.user_list_view")
                        .where(DSL.field("party_id").eq(userPartyId));
                LOG.info("Query for fetching user roles: {}", query);
                JSONB roleJson = query.fetchOneInto(JSONB.class);

                if (roleJson == null || roleJson.data() == null) {
                    LOG.warn("No roles found for userPartyId: {}", userPartyId);
                    return (Map.of("status", "error", "message", "User roles not found")).toString();
                }

                roleNames = extractRoleNamesFromJson(roleJson);
                LOG.info("Extracted role names: {}", roleNames);

            }

            final var query = dsl
                    .selectDistinct(DSL.field("permission_name", String.class),
                            DSL.field("resource_type", String.class))

"investigators", "publications", "authors", "institutions", "labs", "sites", "elaboration"));
        // Validate each table
        for (Map.Entry<String, Set<String>> entry : tableColumnsMap.entrySet()) {
            String tableName = entry.getKey();
            Set<String> requiredColumns = entry.getValue();

            String query = "PRAGMA table_info(" + tableName + ")";
            Result<Record> result = sqliteDsl.fetch(query);
            LOG.info("Record of Table : {} {}", tableName, result);

            // Extract column names
            Set<String> existingColumns = new HashSet<>();
            LOG.info("Reading existing columns of table {}", tableName);
            for (Record record : result) {
                LOG.info("Column : {}.{}", tableName, record.get("name", String.class));
                existingColumns.add(record.get("name", String.class));
            }

            // If any table is missing required columns, return false
            if (!existingColumns.containsAll(requiredColumns)) {
                Set<String> missingColumns = new HashSet<>(requiredColumns);
                missingColumns.removeAll(existingColumns); // Get missing columns

                LOG.warn("Missing required columns: {} of Table: {}", missingColumns, tableName);
                LOG.debug("Table: {}, Existing columns: {}", tableName, existingColumns);
                LOG.debug("Table: {},Required columns: {}", tableName, requiredColumns);
                LOG.debug("Table: {},Missing columns: {}", tableName, missingColumns);
                return false;
            }
        }

        return true; // Both tables have all required columns
    }

    public boolean validateParticipantData(String filePath, DSLContext sqliteDsl) throws Exception {

public class LogName {
    // LEVEL 0
    public static final String ERROR = "Error";

    // LEVEL 1
    public static final String SKIP_LOGIN = "Skip Login";
    public static final String ORCID_LOGIN = "Orcid Login";
    public static final String GIT_LOGIN = "GitHub Login";
    public static final String HOME = "Home";
    public static final String STUDIES = "Studies";
    public static final String COHORT = "Cohort";
    public static final String ASK_DRH = "Ask DRH";
    // public static final String ACTIVITY_LOG = "Activity Log";
    public static final String CONSOLE = "Console";
    public static final String DOCUMENTATION = "Documentation";
    public static final String PROFILE = "Profile";

    // LEVEL 2
    // STUDIES
    public static final String DASHBOARD = "Dashboard";
    public static final String ALL_STUDIES = "All Studies";
    public static final String POPULATION_PERCENTAGE = "Population Percentage";
    public static final String MY_STUDIES = "My Studies";
    // ADK_DRH
    public static final String ASK_DRH_DATA = "Ask DRH Data";
    public static final String ASK_DRH_RESEARCH_JOURNAL = "Ask DRH Research Journal";
    public static final String ASK_DRH_ICODE = "Ask DRH iCODE";
    // CONSOLE
    public static final String CONSOLE_PROJECT = "Project";
    public static final String CONSOLE_HEALTH_INFORMATION = "Health Information";
    public static final String CONSOLE_SCHEMA = "Schema";
    // DOCUMENTATION
    public static final String DOCUMENTATION_OPEN_API = "DRH Open API UI";
    public static final String DOCUMENTATION_ANNOUNCEMENTS = "Announcements";

    // LEVEL 3
    public static final String STUDIES_INDIVIDUAL = "Study Details";
    public static final String STUDIES_PARTICIPANT = "Participant Detils";
    public static final String ALL_STUDIES_CGM = "All CGM Files";
    public static final String STUDIES_CGM = "Study CGM Files";

    // LEVEL 4
    public static final String AI_ASK_DRH_DATA = "Ask DRH Data - Vanna.ai Interaction";

HttpServletResponse response = getCurrentResponse();

        // final var requestUrl = request.getRequestURI();
        final var statusCode = response.getStatus();
        final var httpMethod = request.getMethod();
        final var userAgent = request.getHeader("User-Agent");
        final var localHost = InetAddress.getLocalHost();
        final var ipAddress = localHost.getHostAddress();
        final var provenance = "%s.doFilterInternal".formatted(ActivityLogService.class.getName());
        final var initiator = request.getRemoteUser();
        final var initiatorHost = request.getRemoteHost();
        final var sessionId = request.getSession().getId();
        // extractSessionContent(request);
        final var userId = userNameService.getUserId();
        String userName = userNameService.getUserName();
        final var appVersion = presentation.getVersion().toString();

        Map<String, Object> dataMap = new HashMap<>();
        dataMap.put("httpMethod", httpMethod);
        dataMap.put("userAgent", userAgent);
        dataMap.put("ipAddress", ipAddress);
        dataMap.put("provenance", provenance);
        dataMap.put("statusCode", statusCode);
        dataMap.put("initiator", initiator);
        dataMap.put("initiatorHost", initiatorHost);

Field<String> parentFileInteractionId = DSL.field("p.file_interaction_id", String.class);
            // Construct the jOOQ query
            var query = udiPrimeDbConfig.dsl()
                    .selectDistinct(DSL.field("p.study_id"),
                            DSL.field("p.study_display_id"),
                            DSL.field("p.study_title"),
                            DSL.field("p.file_category"),
                            DSL.field("p.organization_name"),
                            DSL.field(
                                    "p.organization_party_id")) // Selecting the specified fields
                    .from(p)
                    .where("1=1");
            if (finalCondition != null) {
                query = query.and(finalCondition);
            }
            query = query.and((DSL.field("p.interaction_hierarchy")
                    .eq(DSL.value("null")))
                    .or(DSL.field("p.interaction_hierarchy").isNull()))
                    .and(DSL.field("p.study_id").isNotNull())
                    .and(DSL.field("p.study_display_id").isNotNull())
                    .and(DSL.field("p.interaction_status").notEqual(ActionStatus.FAILED))
                    .and(DSL.field("p.organization_party_id").eq(DSL.value(organizationId)))
                    .and(DSL.field("p.file_category").eq(DSL.value(FileType.PARTICIPANT)))

ResponseEntity<String> response = orcidUserDetailService.getOrcidUserInfo(orcidId);

            String name = orcidUserDetailService.extractFullName(response.getBody().toString());
            String email = orcidUserDetailService.extractEmail(response.getBody().toString());
            String institution = orcidUserDetailService.extractInstitution(response.getBody().toString());
            Map<String, Object> userDetails = new HashMap<>(Map.of("orcidId", orcidId));
            userDetails.put("userName", name);
            userDetails.put("userEmail", email);
            userDetails.put("userInstitution", institution);
            // Return the response
            return Response.builder()
                    .data(userDetails)
                    .status("success")
                    .message("Successfully read ORCID details")
                    .errors(null)
                    .build();

        } catch (Exception e) {
            return Response.builder()
                    .data(new HashMap<>())
                    .status("error")
                    .message("Error fetching ORCID details")

}

        Map<String, Object> heirarchy = results.isEmpty() ? null : results.get(0);
        if (heirarchy != null) {
            if (heirarchy.get("hierarchy_path") != null)
                activityLog.setHierarchyPath((heirarchy.get("hierarchy_path").toString()) + ", "
                        + requestUrl);
            else
                activityLog.setHierarchyPath(requestUrl);
            if (logDetails != null) {
                if (heirarchy.get("activity_hierarchy") != null) {
                    activityLog.setActivityHierarchy(
                            (heirarchy.get("activity_hierarchy").toString()) + ", " +
                                    activityLog.getActivityType());
                } else {
                    activityLog.setActivityHierarchy(activityLog.getActivityType());
                }
            }

        } else {
            if (logDetails != null) {
                activityLog.setHierarchyPath(requestUrl);
                activityLog.setActivityHierarchy(activityLog.getActivityType());
            }

        }
        activityLog.setSessionUniqueId(uniqueSessionId);
        return activityLog;
    }

    private void setActivityDataFromLogDetails(LogDetails logDetails, ActivityLog activityLog) {

final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class,
                    schemaName,
                    masterTableNameOrViewName);

            var bindValues = new ArrayList<Object>();

            final var userId = userNameService.getUserId();
            final var organizationId = partyService.getOrganizationPartyIdByUser(userId);
            Condition finalCondition = payload.filterModel() != null
                    ? customTabularFilter.createCondition(payload.filterModel())
                    : null;
            // Construct the jOOQ query
            var query = udiPrimeDbConfig.dsl()
                    .selectDistinct(DSL.field("study_id"),
                            DSL.field("study_display_id"),
                            DSL.field("study_title"),
                            DSL.field("file_category"),
                            DSL.field("organization_name"),
                            DSL.field(
                                    "organization_party_id")) // Selecting the specified fields
                    .from(typableTable.table())
                    .where("1=1");
            if (finalCondition != null) {
                query = query.and(finalCondition);
            }
            query = query.and(((DSL.field("interaction_hierarchy")

try {
            TypableTable typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class,
                    schemaName,
                    masterTableNameOrViewName);
            var bindValues = new ArrayList<Object>();

            final var userId = userNameService.getUserId();
            final var organizationId = partyService.getOrganizationPartyIdByUser(userId);
            Condition finalCondition = payload.filterModel() != null
                    ? customTabularFilter.createCondition(payload.filterModel())
                    : null;
            var query = udiPrimeDbConfig.dsl()
                    .selectDistinct(DSL.field("study_id"),
                            DSL.field("study_display_id"),
                            DSL.field("study_title"),
                            DSL.field("file_category"),
                            DSL.field("organization_name"),
                            DSL.field(
                                    "organization_party_id")) // Selecting the specified fields
                    .from(typableTable.table()).where("1=1");
            if (finalCondition != null) {
                query = query.and(finalCondition);
            }
            query = query.and(((DSL.field("study_id").isNotNull())))

}

            if (payload.sortModel() != null && !payload.sortModel().isEmpty()) {
                query = (@NotNull SelectConditionStep<Record>) query.orderBy(
                        customTabularFilter.createSortCondition(payload.sortModel(),
                                typableTable));
            }
            LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
            LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName, masterTableNameOrViewName, (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (

        DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "Interaction details of a successful study_interaction_id value")

JSONB dbData, DSLContext sqliteDsl, String sqliteDbName) {
        LOG.info("Copying tables from SQLite to Postgres");
        try {
            LOG.info("Distinct db_file_ids from SQLITE: {}", distinctDbFileIds);
            final var sqStudyDisplayId = duckDsl.fetchOne(
                    "SELECT DISTINCT study_display_id FROM " + sqliteDbName + ".participant")
                    .get("study_display_id", String.class);
            LOG.info("Study Display Id from SQLITE: {}", sqStudyDisplayId);
            final var pgStudyDisplayId = dsl
                    .selectDistinct(DSL.field("study_display_id", String.class))
                    .from("drh_stateless_research_study.research_study_view")
                    .where(DSL.field("study_id").eq(DSL.value(
                            studyId)))
                    .fetchOneInto(String.class);
            LOG.info("Study Display Id from POSTGRES: {}", pgStudyDisplayId);
            LOG.debug("dbFileId: {}", distinctDbFileIds);
            if (!(sqStudyDisplayId.toString()).equals(pgStudyDisplayId.toString())) {
                LOG.debug("Study display id mismatch between SQLite and Postgres, SQLite: {}, Postgres: {}",
                        sqStudyDisplayId, pgStudyDisplayId);
                return "Study display id mismatch between SQLite and Postgres";
            } else {
                if (!validateRequiredColumns(filePath, sqliteDsl)) {

}

    private String prepareJson(String fileId, String fileName, String fileURL, String uploadTimestamp, long fileSize,
            String studyId, String userPartyId, String organizationPartyId) {
        ObjectMapper objectMapper = new ObjectMapper();
        ObjectNode jsonObject = objectMapper.createObjectNode();
        jsonObject.put("db_file_id", fileId);
        jsonObject.put("file_name", fileName);
        jsonObject.put("file_url", fileURL);
        jsonObject.put("upload_timestamp", uploadTimestamp);
        jsonObject.put("uploaded_by", userPartyId);
        jsonObject.put("file_size", fileSize);
        jsonObject.put("study_id", studyId);
        jsonObject.put("org_party_id", organizationPartyId);
        jsonObject.put("current_user_id", userPartyId);
        return jsonObject.toString();
    }

    private boolean exists(String studyd) {
        return dsl.fetchExists(
                dsl.selectOne().from("drh_stateless_raw_data.cgm_raw_db_view").where("study_id = ?", studyd));
    }

    public Pair<DSLContext, Connection> createSQLiteDSL(String sqliteFilePath) throws Exception {

private void detachSqliteDatabase(String sqliteDbName) {
        if (isDatabaseAttached(sqliteDbName)) {
            LOG.info("Detaching SQLite database");
            duckDsl.execute("DETACH " + sqliteDbName + ";");
            LOG.info("SQLite database detached");
        } else {
            LOG.info("SQLite database " + sqliteDbName + " is not attached. Skipping detachment.");
        }
    }

    private void detachPostgresDatabase() {
        try {
            LOG.info("Checking if Postgres database '{}' is attached", postgresDbName);

            // Check if the database is attached
            String query = "SHOW DATABASES;";
            List<String> attachedDatabases = duckDsl.fetch(query)
                    .getValues(0, String.class);

            if (attachedDatabases.contains(postgresDbName)) {
                LOG.info("Detaching Postgres database '{}'", postgresDbName);
                duckDsl.execute("DETACH DATABASE " + postgresDbName + ";");
            } else {
                LOG.info("Postgres database '{}' is not attached, skipping detach", postgresDbName);
            }
        } catch (Exception e) {
            LOG.error("Error while detaching Postgres database: {}", e.getMessage(), e);
        }
    }

    private boolean isDatabaseAttached(String sqliteDbName) {

final @PathVariable String fileInteractionId) {

        // Fetch the result using the dynamically determined table and column; if
        // jOOQ-generated types were found, automatic column value mapping will occur
        final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class, schemaName,
                masterTableNameOrViewName);
        final var query = udiPrimeDbConfig.dsl().selectFrom(typableTable.table())
                .where((typableTable.column("file_interaction_id").eq(DSL.value(
                        fileInteractionId)))
                        .or((DSL.field("jsonb_path_query_first(interaction_hierarchy::jsonb, '$[0]')::text")
                                .eq(DSL.val("\"" + fileInteractionId + "\"")))))
                .and(DSL.field("interaction_status").eq(DSL.val(ActionStatus.SUCCESS)));
        LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
        LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());
        final var response = query.fetch().intoMaps();

        return Map.of("rows", response);
    }

final @PathVariable String fileInteractionId) {

        // Fetch the result using the dynamically determined table and column; if
        // jOOQ-generated types were found, automatic column value mapping will occur
        final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class, schemaName,
                masterTableNameOrViewName);
        final var query = udiPrimeDbConfig.dsl().selectFrom(typableTable.table())
                .where((typableTable.column("file_interaction_id").eq(DSL.value(
                        fileInteractionId)))
                        .or((DSL.field("jsonb_path_query_first(interaction_hierarchy::jsonb, '$[0]')::text")
                                .eq(DSL.val("\"" + fileInteractionId + "\"")))))
                .and(DSL.field("interaction_status").eq(DSL.val(ActionStatus.FAILED)));
        LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
        LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

        final var response = query.fetch().intoMaps();

        return Map.of("rows", response);
    }

query = (@org.jetbrains.annotations.NotNull SelectConditionStep<Record6<Object, Object, Object, Object, Object, Object>>) query
                        .orderBy(
                                customTabularFilter.createSortCondition(payload.sortModel(),
                                        typableTable));
            }

            LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
            LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName, masterTableNameOrViewName, (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "List of Database file uploaded against a study")

query = (@org.jetbrains.annotations.NotNull SelectConditionStep<Record6<Object, Object, Object, Object, Object, Object>>) query
                        .orderBy(
                                customTabularFilter.createSortCondition(payload.sortModel(),
                                        typableTable));
            }

            LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
            LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName, masterTableNameOrViewName, (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "Successful CGM File Interaction against a specific participant")

if (citationRecordId == null) {
                throw new IllegalStateException("Citation ID not returned from saveStudyCitation");
            }
            LOG.info("Saving authors for studyId: {}, citationId: {}", studyId, citationId);
            Object authorResponse = researchStudyService.saveAuthors(
                    studyId,
                    request.collaboration_team().coAuthorsName(),
                    citationId);

            responseMap.put("authorResponse", authorResponse.toString());

            return Response.builder()
                    .status("success")
                    .message("Citations and authors saved successfully")
                    .data(responseMap)
                    .errors(null)
                    .build();

        } catch (Exception e) {
            LOG.error("Failed to save citations", e);
            return Response.builder()
                    .status("error")
                    .message("Failed to save citations")
                    .data(Collections.emptyMap())
                    .errors(e.getMessage())
                    .build();
        }
    }

    @PostMapping("/research-study/citations")

public JdbcResponse(Builder builder) {
        this.data = builder.data;
        this.status = builder.status;
        this.message = builder.message;
        this.errors = builder.errors;
    }

    public static Builder builder() {
        return new Builder();
    }

    public static class Builder {
        private Map<String, Object> data;
        private String status;
        private String message;
        private Object errors;

        public Builder data(Map<String, Object> data) {
            this.data = data;
            return this;
        }

        public Builder status(String status) {
            this.status = status;
            return this;
        }

        public Builder message(String message) {
            this.message = message;
            return this;
        }

        public Builder errors(Object errors) {
            this.errors = errors;
            return this;
        }

        public JdbcResponse build() {

Map<String, Object> heirarchy = results.isEmpty() ? null : results.get(0);
        if (heirarchy != null) {
            if (heirarchy.get("hierarchy_path") != null)
                activityLog.setHierarchyPath((heirarchy.get("hierarchy_path").toString()) + ", "
                        + requestUrl);
            else
                activityLog.setHierarchyPath(requestUrl);
            if (logDetails != null) {
                if (heirarchy.get("activity_hierarchy") != null) {
                    activityLog.setActivityHierarchy(
                            (heirarchy.get("activity_hierarchy").toString()) + ", " +
                                    activityLog.getActivityType());
                } else {
                    activityLog.setActivityHierarchy(activityLog.getActivityType());
                }
            }

        } else {
            if (logDetails != null) {
                activityLog.setHierarchyPath(requestUrl);
                activityLog.setActivityHierarchy(activityLog.getActivityType());
            }

        }

Map<String, Object> heirarchy = results.isEmpty() ? null : results.get(0);
        if (heirarchy != null) {
            if (heirarchy.get("hierarchy_path") != null)
                activityLog.setHierarchyPath((heirarchy.get("hierarchy_path").toString()) + ", "
                        + requestUrl);
            else
                activityLog.setHierarchyPath(requestUrl);
            if (logDetails != null) {
                if (heirarchy.get("activity_hierarchy") != null) {
                    activityLog.setActivityHierarchy(
                            (heirarchy.get("activity_hierarchy").toString()) + ", " +
                                    activityLog.getActivityType());
                } else {
                    activityLog.setActivityHierarchy(activityLog.getActivityType());
                }
            }

        } else {
            if (logDetails != null) {
                activityLog.setHierarchyPath(requestUrl);
                activityLog.setActivityHierarchy(activityLog.getActivityType());
            }

        }

public Response saveResearchStudySettings(@RequestBody ResearchStudySettingsRequest request) {

        try {
            if (!userNameService.getCurrentuserPartyId()
                    .equalsIgnoreCase(researchStudyService.getStudyOwner(request.studyId()))) {
                LOG.warn("Access denied: User {} is not the owner of study {}", userNameService.getCurrentuserPartyId(),
                        request.studyId());
                return Response.builder()
                        .data(Map.of())
                        .status("error")
                        .message("Access denied: Only the study owner is permitted to edit this research study.")
                        .errors(null)
                        .build();
            }
            if (researchStudyService.getResearchStudyArchiveStatus(request.studyId())) {
                LOG.warn("Access denied: Study {} is archived.", request.studyId());

                Response response = Response.builder()
                        .status("failure")
                        .message("The study is archived; edits and updates are not allowed.")
                        .build();

                return response;
            }
            LOG.info("save research study settings: {}", request);

String responseString = studyResponse.toString();
            JsonNode responseJson = OBJECT_MAPPER.readTree(responseString);

            if (responseJson.has("status") && "failure".equals(responseJson.get("status").asText())) {
                String errorMessage = responseJson.has("message") ? responseJson.get("message").asText()
                        : "Unknown error occurred.";
                JsonNode errorDetails = responseJson.has("error_details") ? responseJson.get("error_details") : null;
                return Response.builder()
                        .data(Map.of())
                        .status("error")
                        .message(errorMessage)
                        .errors(errorDetails != null ? errorDetails.toString() : null)
                        .build();
            }
            return Response.builder()
                    .data(Map.of("studyId",

Object principal = authentication.getPrincipal();
            String organizationPartyId = "";
            if (principal instanceof UserDetails) {
                organizationPartyId = dsl
                        .select(DSL.field("organization_party_id"))
                        .from("drh_stateless_authentication.super_admin_view")
                        .where(DSL.field("email").eq(DSL.val(userId)))
                        .limit(1) // To ensure we only check if at least one row exists
                        .fetchOneInto(String.class);
            } else {

                LOG.info("Fetching user details for user ID: {}", userId);
                organizationPartyId = dsl
                        .select(DSL.field("organization_party_id"))
                        .from("drh_stateless_authentication.user_profile_view")
                        .where(DSL.field("provider_user_id").eq(DSL.val(userId)))
                        .limit(1) // To ensure we only check if at least one row exists
                        .fetchOneInto(String.class);
            }
            LOG.debug("Fetched organizationPartyId for user {}: {}", userId, organizationPartyId);

public Response saveResearchStudySettings(@RequestBody ResearchStudySettingsRequest request) {

        try {
            if (!userNameService.getCurrentuserPartyId()
                    .equalsIgnoreCase(researchStudyService.getStudyOwner(request.studyId()))) {
                LOG.warn("Access denied: User {} is not the owner of study {}", userNameService.getCurrentuserPartyId(),
                        request.studyId());
                return Response.builder()
                        .data(Map.of())
                        .status("error")
                        .message("Access denied: Only the study owner is permitted to edit this research study.")
                        .errors(null)
                        .build();
            }
            if (researchStudyService.getResearchStudyArchiveStatus(request.studyId())) {
                LOG.warn("Access denied: Study {} is archived.", request.studyId());

                Response response = Response.builder()
                        .status("failure")
                        .message("The study is archived; edits and updates are not allowed.")
                        .build();

                return response;
            }
            LOG.info("save research study settings: {}", request);

@Valid @RequestBody ParticipantDataRequest request) {
        try {
            if (!userNameService.getCurrentuserPartyId()
                    .equalsIgnoreCase(researchStudyService.getStudyOwner(request.studyId()))) {
                LOG.warn("Access denied: User {} is not the owner of study {}", userNameService.getCurrentuserPartyId(),
                        request.studyId());
                return Response.builder()
                        .data(Map.of())
                        .status("error")
                        .message("Access denied: Only the study owner is permitted to edit this research study.")
                        .errors(null)
                        .build();
            }
            if (researchStudyService.getResearchStudyArchiveStatus(request.studyId())) {
                LOG.warn("Access denied: Study {} is archived.", request.studyId());

                Response response = Response.builder()
                        .status("failure")
                        .message("The study is archived; edits and updates are not allowed.")
                        .build();

                return response;
            }
            LOG.info("Saving participant data: {}", request);

final var metadata = pubMedService.getMetadata(pubmedId);
        try {
            LOG.info("Metadata corresponds to the given is PUBMED ID: {}", metadata);
            if (metadata != null) {
                return Response.builder()
                        .data(new HashMap<>(Map.of("metadata",
                                metadata)))
                        .status("success")
                        .message("Metadata found")
                        .build();
            }
            return Response.builder()
                    .data(new HashMap<>())
                    .status("failed")
                    .message("Details not found")
                    .build();
        } catch (Exception e) {
            return Response.builder()
                    .data(new HashMap<>())
                    .status("error")
                    .message("Failed to read Metadata")
                    .errors("Error in reading Metadata: ")
                    .build();
        }

    }

    @GetMapping("/crossref/metadata")

final @PathVariable String participantId) {

        final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class, schemaName,
                masterTableNameOrViewName);
        var p = typableTable.table().as("p");
        var c = typableTable.table().as("c"); // For child interactions

        Field<String> parentFileInteractionId = DSL.field("p.file_interaction_id", String.class);
        final var query = udiPrimeDbConfig.dsl().selectFrom(p)
                .where((DSL.field("p.participant_id").eq(DSL.value(
                        participantId)))
                        .and((DSL.field("p.interaction_hierarchy")
                                .eq(DSL.val("null"))))
                        .and(DSL.field("p.file_category").eq(DSL.value(FileType.CGM)))

try {
            if (!userNameService.getCurrentuserPartyId()
                    .equalsIgnoreCase(researchStudyService.getStudyOwner(request.studyId()))) {
                LOG.warn("Access denied: User {} is not the owner of study {}", userNameService.getCurrentuserPartyId(),
                        request.studyId());
                return Response.builder()
                        .data(Map.of())
                        .status("error")
                        .message("Access denied: Only the study owner is permitted to edit this research study.")
                        .errors(null)
                        .build();
            }
            if (researchStudyService.getResearchStudyArchiveStatus(request.studyId())) {
                LOG.warn("Access denied: Study {} is archived.", request.studyId());

                Response response = Response.builder()
                        .status("failure")
                        .message("The study is archived; edits and updates are not allowed.")
                        .build();

                return response;
            }
            LOG.info("save research study settings: {}", request);

try {
            if (!userNameService.getCurrentuserPartyId()
                    .equalsIgnoreCase(researchStudyService.getStudyOwner(request.studyId()))) {
                LOG.warn("Access denied: User {} is not the owner of study {}", userNameService.getCurrentuserPartyId(),
                        request.studyId());
                return Response.builder()
                        .data(Map.of())
                        .status("error")
                        .message("Access denied: Only the study owner is permitted to edit this research study.")
                        .errors(null)
                        .build();
            }
            if (researchStudyService.getResearchStudyArchiveStatus(request.studyId())) {
                LOG.warn("Access denied: Study {} is archived.", request.studyId());

                Response response = Response.builder()
                        .status("failure")
                        .message("The study is archived; edits and updates are not allowed.")
                        .build();

                return response;
            }
            LOG.info("Saving participant data: {}", request);

try {
            if (!userNameService.getCurrentuserPartyId()
                    .equalsIgnoreCase(researchStudyService.getStudyOwner(request.studyId()))) {
                LOG.warn("Access denied: User {} is not the owner of study {}", userNameService.getCurrentuserPartyId(),
                        request.studyId());
                return Response.builder()
                        .data(Map.of())
                        .status("error")
                        .message("Access denied: Only the study owner is permitted to edit this research study.")
                        .errors(null)
                        .build();
            }
            if (researchStudyService.getResearchStudyArchiveStatus(request.studyId())) {
                LOG.warn("Access denied: Study {} is archived.", request.studyId());

                Response response = Response.builder()
                        .status("failure")
                        .message("The study is archived; edits and updates are not allowed.")
                        .build();

                return response;
            }
            LOG.info("Updating participant data: {}", request);

JsonNode responseJson = OBJECT_MAPPER.readTree(response.toString());
            if (responseJson.has("status") && "failure".equals(responseJson.get("status").asText())) {
                String errorMessage = responseJson.has("message") ? responseJson.get("message").asText()
                        : "Unknown error occurred.";
                JsonNode errorDetails = responseJson.has("error_details") ? responseJson.get("error_details") : null;
                LOG.error("Error fetching roles and permissions: " + errorMessage);
                return Response.builder()
                        .data(Map.of())
                        .status("error")
                        .message(errorMessage)
                        .errors(errorDetails != null ? errorDetails.toString() : null)
                        .build();
            }

public ResponseEntity<Object> getDiscoFeed() {
        String url = "https://orcid.org/Shibboleth.sso/DiscoFeed";

        try {
            // Set up headers with the cookie
            HttpHeaders headers = new HttpHeaders();
            headers.add(HttpHeaders.COOKIE, "geolocation=US");

            // Create the HTTP entity with headers
            HttpEntity<Void> requestEntity = new HttpEntity<>(headers);

            // Make the API call with the custom headers
            ResponseEntity<String> response = restTemplate.exchange(
                    url,
                    HttpMethod.GET,
                    requestEntity,
                    String.class);
            if (response.getStatusCode().is2xxSuccessful() && response.getBody() != null) {
                // Parse the JSON response
                ObjectMapper objectMapper = new ObjectMapper();
                List<Map<String, Object>> jsonResponse = objectMapper.readValue(
                        response.getBody(),
                        new TypeReference<List<Map<String, Object>>>() {
                        });

                // Extract 'value' from 'DisplayNames'
                List<String> displayNames = jsonResponse.stream()

try {
            if (!userNameService.getCurrentuserPartyId()
                    .equalsIgnoreCase(researchStudyService.getStudyOwner(request.studyId()))) {
                LOG.warn("Access denied: User {} is not the owner of study {}", userNameService.getCurrentuserPartyId(),
                        request.studyId());
                return Response.builder()
                        .data(Map.of())
                        .status("error")
                        .message("Access denied: Only the study owner is permitted to edit this research study.")
                        .errors(null)
                        .build();
            }
            if (researchStudyService.getResearchStudyArchiveStatus(request.studyId())) {
                LOG.warn("Access denied: Study {} is archived.", request.studyId());

                Response response = Response.builder()
                        .status("failure")
                        .message("The study is archived; edits and updates are not allowed.")
                        .build();

                return response;
            }

query = (@NotNull SelectConditionStep<Record>) query.orderBy(
                        customTabularFilter.createSortCondition(payload.sortModel(),
                                typableTable));
            }
            LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
            LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName, masterTableNameOrViewName, (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (

        DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "Interaction details of a successful study_interaction_id value")

query = (@NotNull SelectConditionStep<Record>) query.orderBy(
                        customTabularFilter.createSortCondition(payload.sortModel(),
                                typableTable));
            }
            LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
            LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName, masterTableNameOrViewName, (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (

        DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "Details of a failed study_interaction_id value")

try {
            if (!userNameService.getCurrentuserPartyId()
                    .equalsIgnoreCase(researchStudyService.getStudyOwner(request.studyId()))) {
                LOG.warn("Access denied: User {} is not the owner of study {}", userNameService.getCurrentuserPartyId(),
                        request.studyId());
                return Response.builder()
                        .data(Map.of())
                        .status("error")
                        .message("Access denied: Only the study owner is permitted to edit this research study.")
                        .errors(null)
                        .build();
            }
            if (researchStudyService.getResearchStudyArchiveStatus(request.studyId())) {
                LOG.warn("Access denied: Study {} is archived.", request.studyId());

                Response response = Response.builder()
                        .status("failure")
                        .message("The study is archived; edits and updates are not allowed.")
                        .build();

                return response;
            }

query = (@org.jetbrains.annotations.NotNull SelectConditionStep<Record6<Object, Object, Object, Object, Object, Object>>) query
                        .orderBy(
                                customTabularFilter.createSortCondition(payload.sortModel(),
                                        typableTable));
            }

            LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
            LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName, masterTableNameOrViewName, (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "List of Database file uploaded against a study")

query = (@org.jetbrains.annotations.NotNull SelectConditionStep<Record6<Object, Object, Object, Object, Object, Object>>) query
                        .orderBy(
                                customTabularFilter.createSortCondition(payload.sortModel(),
                                        typableTable));
            }

            LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
            LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName, masterTableNameOrViewName, (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "Successful CGM File Interaction against a specific participant")

query = (SelectConditionStep<Record6<Object, Object, Object, Object, Object, Object>>) query
                        .orderBy(
                                customTabularFilter.createSortCondition(payload.sortModel(),
                                        typableTable));
            }
            LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
            LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName, masterTableNameOrViewName, (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (

        DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "Failed CGM File Interaction against a specific CGM file")

query = (@org.jetbrains.annotations.NotNull SelectConditionStep<Record6<Object, Object, Object, Object, Object, Object>>) query
                        .orderBy(
                                customTabularFilter.createSortCondition(payload.sortModel(),
                                        typableTable));
            }

            LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
            LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName, masterTableNameOrViewName, (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "Successful Meals Or Fitness File Interaction against a specific participant")

query = (@org.jetbrains.annotations.NotNull SelectConditionStep<Record6<Object, Object, Object, Object, Object, Object>>) query
                        .orderBy(
                                customTabularFilter.createSortCondition(payload.sortModel(),
                                        typableTable));
            }
            LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
            LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName, masterTableNameOrViewName, (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "Failed Meals Or Fitness File Interaction against a specific Meals Or Fitness file")

query = (@org.jetbrains.annotations.NotNull SelectConditionStep<Record6<Object, Object, Object, Object, Object, Object>>) query
                        .orderBy(
                                customTabularFilter.createSortCondition(payload.sortModel(),
                                        typableTable));
            }

            LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
            LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName, masterTableNameOrViewName, (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "Successful CGM File Interaction against a specific participant")

query = (SelectConditionStep<Record6<Object, Object, Object, Object, Object, Object>>) query
                        .orderBy(
                                customTabularFilter.createSortCondition(payload.sortModel(),
                                        typableTable));
            }
            LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
            LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName, masterTableNameOrViewName, (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (

        DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "Failed CGM File Interaction against a specific CGM file")

}

    private @NotNull SortField<Object> createSortCondition(
            List<lib.aide.tabular.TabularRowsRequest.SortModel> sortModel,
            TypableTable participantDashboardTable) {
        for (final var sort : sortModel) {
            final var sortField = participantDashboardTable.column(sort.colId());
            if ((sort.sort()).equals("asc")) {
                return field(sortField).asc();
            } else if ((sort.sort()).equals("desc")) {
                return field(sortField).desc();
            } else {
                LOG.info("Not a Valid Sort Field");
            }
        }
        return null;
    }

    @SuppressWarnings("unchecked")
    @Operation(summary = "SQL Custom Dashboard Summary ")

private boolean isDatabaseAttached(String sqliteDbName) {
        String checkQuery = "PRAGMA database_list;";
        List<Record> attachedDatabases = duckDsl.fetch(checkQuery);

        boolean isDatabaseAttached = false;

        // Check if the database is attached
        for (Record db : attachedDatabases) {
            if (db.get("name").equals(sqliteDbName)) {
                isDatabaseAttached = true;
                break;
            }
        }
        return isDatabaseAttached;
    }

    private void attachPostgresDatabase() {
        try {
            LOG.info("Attaching Postgres database as {}", postgresDbName);
            duckDsl.execute("DROP SCHEMA IF EXISTS " + postgresDbName + " CASCADE;");
            duckDsl.execute(
                    "ATTACH '' AS " + postgresDbName + " (TYPE POSTGRES);");
            LOG.info("PostgreSQL database attached as: {}", postgresDbName);
        } catch (Exception e) {
            LOG.error("Error while attaching Postgres database: {}", e.getMessage(), e);
        }
    }

    private void attachSqliteDatabase(String tempFilePath, String sqliteDbName) {

JsonNode responseJson = OBJECT_MAPPER.readTree(studyResponse);

            if (responseJson.has("status") && "failure".equals(responseJson.get("status").asText())) {
                String errorMessage = responseJson.has("message") ? responseJson.get("message").asText()
                        : "Unknown error occurred.";
                JsonNode errorDetails = responseJson.has("error_details") ? responseJson.get("error_details") : null;
                return Response.builder()
                        .data(Map.of())
                        .status("error")
                        .message(errorMessage)
                        .errors(errorDetails != null ? errorDetails.toString() : null)
                        .build();
            }
            return Response.builder()
                    .data(Map.of("studyId",

final @PathVariable String fileInteractionId) {

        // Fetch the result using the dynamically determined table and column; if
        // jOOQ-generated types were found, automatic column value mapping will occur
        final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class, schemaName,
                masterTableNameOrViewName);
        final var query = udiPrimeDbConfig.dsl().selectFrom(typableTable.table())
                .where((typableTable.column("file_interaction_id").eq(DSL.value(
                        fileInteractionId)))
                        .or((DSL.field("jsonb_path_query_first(interaction_hierarchy::jsonb, '$[0]')::text")
                                .eq(DSL.val("\"" + fileInteractionId + "\"")))))
                .and(DSL.field("interaction_status").eq(DSL.val(ActionStatus.SUCCESS)));
        LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
        LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

final @PathVariable String fileInteractionId) {

        // Fetch the result using the dynamically determined table and column; if
        // jOOQ-generated types were found, automatic column value mapping will occur
        final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class, schemaName,
                masterTableNameOrViewName);
        final var query = udiPrimeDbConfig.dsl().selectFrom(typableTable.table())
                .where((typableTable.column("file_interaction_id").eq(DSL.value(
                        fileInteractionId)))
                        .or((DSL.field("jsonb_path_query_first(interaction_hierarchy::jsonb, '$[0]')::text")
                                .eq(DSL.val("\"" + fileInteractionId + "\"")))))
                .and(DSL.field("interaction_status").eq(DSL.val(ActionStatus.FAILED)));
        LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
        LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

public class TabularRowsStudyCGMInteractionControllerCustom {
    static private final Logger LOG = LoggerFactory.getLogger(
            TabularRowsStudyCGMInteractionControllerCustom.class);

    private final UdiSecondaryDbConfig udiPrimeDbConfig;
    private final UserNameService userNameService;
    private final PartyService partyService;
    private final CustomTabularFilter customTabularFilter;

    @Bean
    public com.fasterxml.jackson.databind.Module jsonbModule() {
        com.fasterxml.jackson.databind.module.SimpleModule module = new SimpleModule();
        module.addSerializer(org.jooq.JSONB.class, new JsonSerializer<org.jooq.JSONB>() {
            @Override
            public void serialize(org.jooq.JSONB value, JsonGenerator gen, SerializerProvider serializers)
                    throws IOException {
                gen.writeRawValue(value.data()); // or gen.writeString(value.data());
            }
        });
        return module;
    }

    public TabularRowsStudyCGMInteractionControllerCustom(final UdiSecondaryDbConfig udiPrimeDbConfig,

public Object childSuccessStudyInteraction(final @PathVariable(required = false) String schemaName,
            final @PathVariable String masterTableNameOrViewName, final @PathVariable String studyInteractionId) {

        // Fetch the result using the dynamically determined table and column; if
        // jOOQ-generated types were found, automatic column value mapping will occur
        final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class, schemaName,
                masterTableNameOrViewName);
        final var query = udiPrimeDbConfig.dsl().selectFrom(typableTable.table())
                .where((typableTable.column("study_interaction_id").eq(DSL.value(
                        studyInteractionId)))
                        .or((DSL.field("jsonb_path_query_first(interaction_hierarchy::jsonb, '$[0]')::text")
                                .eq(DSL.val("\"" + studyInteractionId + "\"")))))
                .and(DSL.field("interaction_status").eq(DSL.val(ActionStatus.SUCCESS)));

public Object childSuccessStudyParticipantInteraction(final @PathVariable(required = false) String schemaName,
            final @PathVariable String masterTableNameOrViewName,
            final @PathVariable String studyParticipantInteractionId) {

        // Fetch the result using the dynamically determined table and column; if
        // jOOQ-generated types were found, automatic column value mapping will occur
        final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class, schemaName,
                masterTableNameOrViewName);
        final var query = udiPrimeDbConfig.dsl().selectFrom(typableTable.table())
                .where((typableTable.column("participant_interaction_id").eq(DSL.value(
                        studyParticipantInteractionId)))
                        .or((DSL.field("jsonb_path_query_first(interaction_hierarchy::jsonb, '$[0]')::text")
                                .eq(DSL.val("\"" + studyParticipantInteractionId + "\"")))))
                .and(DSL.field("interaction_status").eq(DSL.val(ActionStatus.SUCCESS)));

JsonNode responseJson = OBJECT_MAPPER.readTree(studyResponse);

            if (responseJson.has("status") && "failure".equals(responseJson.get("status").asText())) {
                String errorMessage = responseJson.has("message") ? responseJson.get("message").asText()
                        : "Unknown error occurred.";
                JsonNode errorDetails = responseJson.has("error_details") ? responseJson.get("error_details") : null;
                return Response.builder()
                        .data(Map.of())
                        .status("error")
                        .message(errorMessage)
                        .errors(errorDetails != null ? errorDetails.toString() : null)
                        .build();
            }
            return Response.builder()
                    .data(Map.of("studyId",

.orderBy(
                                customTabularFilter.createSortCondition(payload.sortModel(),
                                        typableTable));
            }

            LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
            LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName, masterTableNameOrViewName, (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "List of Database file uploaded against a study")

bindValues.add(ActionStatus.FAILED);
            if (!userNameService.isAdmin()) {
                final var currentUserId = userNameService.getCurrentuserPartyId();
                query = query.and(DSL.field("created_by").eq(DSL.value(currentUserId)));
                bindValues.add(currentUserId);

            }
            if (payload.sortModel() != null && !payload.sortModel().isEmpty()) {
                query = (@NotNull SelectConditionStep<Record6<Object, Object, Object, Object, Object, Object>>) ((SelectConditionStep<?>) query)
                        .orderBy(
                                customTabularFilter.createSortCondition(payload.sortModel(), typableTable));
            }

            LOG.info("Get Participant File Interaction Details Corresponds to the schema {}:", schemaName);

JsonNode responseJson = OBJECT_MAPPER.readTree(resultObj.toString());
            if (responseJson.has("status") && "failure".equals(responseJson.get("status").asText())) {
                String errorMessage = responseJson.has("message") ? responseJson.get("message").asText()
                        : "Unknown error occurred.";
                JsonNode errorDetails = responseJson.has("error_details") ? responseJson.get("error_details") : null;
                LOG.error("Error updating archive status : " + errorMessage);
                return Response.builder()
                        .data(Map.of())
                        .status("error")
                        .message(errorMessage)
                        .errors(errorDetails != null ? errorDetails.toString() : null)
                        .build();
            }

public Object childSuccessMealsOrFitnessFileInteraction(
            final @PathVariable String fileInteractionId) {
        final var schemaName = "drh_stateless_activity_audit";
        final var masterTableNameOrViewName = "file_interaction_view";
        try {

            // Fetch the result using the dynamically determined table and column; if
            // jOOQ-generated types were found, automatic column value mapping will occur
            final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class, schemaName,
                    masterTableNameOrViewName);
            final var query = udiPrimeDbConfig.dsl().selectFrom(typableTable.table())
                    .where((typableTable.column("file_interaction_id").eq(DSL.value(
                            fileInteractionId)))
                            .or((DSL.field("jsonb_path_query_first(interaction_hierarchy::jsonb, '$[0]')::text")
                                    .eq(DSL.val("\"" + fileInteractionId + "\"")))))
                    .and(DSL.field("interaction_status").eq(DSL.val(ActionStatus.SUCCESS)));

DSL.val(request.citation_data_source().name()), DSL.cast(DSL.val(activityData), JSONB.class)));

            LOG.info("Executing SQL Query: {}", query);
            JSONB result = query.fetchOneInto(JSONB.class);
            LOG.info("Study citation saved successfully for studyId: {}", studyId);
            if (result != null) {
                return new ObjectMapper().readValue(result.data(), new TypeReference<Map<String, Object>>() {
                });
            } else {
                return Map.of("status", "error", "message", "No data returned");
            }
        } catch (Exception e) {
            LOG.error("Error saving study citation: {}", e.getMessage(), e);
            return Map.of("status", "error", "message", "Failed to save study citation");
        }
    }

public Object parentDatabaseFileInteraction(
            final @RequestBody @Nonnull TabularRowsRequest payload,
            final @PathVariable String schemaName,
            final @PathVariable String masterTableNameOrViewName,
            @RequestParam(required = false, defaultValue = "*") String columns,
            @RequestHeader(value = "X-Include-Generated-SQL-In-Response", required = false) boolean includeGeneratedSqlInResp,
            @RequestHeader(value = "X-Include-Generated-SQL-In-Error-Response", required = false, defaultValue = "true") boolean includeGeneratedSqlInErrorResp)
            throws SQLException {

        try {
            final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class,
                    schemaName,
                    masterTableNameOrViewName);

            var bindValues = new ArrayList<Object>();

            final var userId = userNameService.getUserId();
            final var organizationId = partyService.getOrganizationPartyIdByUser(userId);

var query = udiPrimeDbConfig.dsl()
                    .selectDistinct(DSL.field("study_id"), DSL.field("organization_party_id"), DSL
                            .field("study_display_id"),
                            DSL.field(
                                    "study_title"),
                            DSL.field("organization_name"), DSL.field(
                                    "created_by_name"),
                            DSL.field(
                                    createdAt))

                    .from(typableTable
                            .table().as("spv"))
                    .where("1=1");
            if (finalCondition != null) {
                query = query.and(finalCondition);
            }
            query = query.and(
                    studyParticipantInteractionId.in(subquery)
                            .or(studyId.isNull()))
                    .and(createdBy.eq(userId))

public Response editResearchStudy(@PathVariable String studyId, @RequestBody JsonNode studyData) {
        if (!userNameService.getCurrentuserPartyId().equalsIgnoreCase(researchStudyService.getStudyOwner(studyId))) {
            LOG.warn("Access denied: User {} is not the owner of study {}", userNameService.getCurrentuserPartyId(),
                    studyId);
            return Response.builder()
                    .data(Map.of())
                    .status("error")
                    .message("Access denied: Only the study owner is permitted to edit this research study.")
                    .errors(null)
                    .build();
        }
        if (researchStudyService.getResearchStudyArchiveStatus(studyId)) {
            LOG.warn("Access denied: Study {} is archived.", studyId);

            Response response = Response.builder()
                    .status("failure")
                    .message("The study is archived; edits and updates are not allowed.")
                    .build();

            return response;
        }

private @NotNull SortField<Object> createSortCondition(
            List<lib.aide.tabular.TabularRowsRequest.SortModel> sortModel,
            TypableTable participantDashboardTable) {
        for (final var sort : sortModel) {
            final var sortField = participantDashboardTable.column(sort.colId());
            if ((sort.sort()).equals("asc")) {
                return field(sortField).asc();
            } else if ((sort.sort()).equals("desc")) {
                return field(sortField).desc();
            } else {
                LOG.info("Not a Valid Sort Field");
            }
        }
        return null;
    }

final var query = DSL.selectOne()
                .from("drh_stateless_activity_audit.file_interaction_view")
                .where(DSL.field("study_id").eq(DSL.val(studyId)))
                .and(DSL.field("file_category").eq(DSL.value(FileType.DATABASE)))
                .and(DSL.field("interaction_action_type_id")
                        .eq(DSL.val(masterService.getActiontype(ActionType.SAVE_DB_CONTENT))))
                .and(DSL.field("interaction_status_id")
                        .eq(DSL.val(masterService.getInteractionStatus(FileProcessingStatus.SUCCESS))));
        LOG.info("Check entry for Study Database File . Query: {}", query);
        final var exists = dsl.fetchExists(query);

public Object childDatabaseFileInteractionModal(final @PathVariable(required = false) String schemaName,
            final @PathVariable String masterTableNameOrViewName,
            final @PathVariable String fileInteractionId) {

        // Fetch the result using the dynamically determined table and column; if
        // jOOQ-generated types were found, automatic column value mapping will occur
        final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class, schemaName,
                masterTableNameOrViewName);
        final var query = udiPrimeDbConfig.dsl().selectFrom(typableTable.table())
                .where(typableTable.column("file_interaction_id").eq(DSL.value(
                        fileInteractionId)));
        LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
        LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());
        // Execute the query and return the result
        return query.fetch().intoMaps();
    }

@RequestBody PublicationUpdateRequest request) {
        String studyId = request.collaboration_team().studyId();
        Map<String, Object> responseMap = new HashMap<>();

        LOG.info("Saving citations for studyId: {}", studyId);
        try {
            if (!userNameService.getCurrentuserPartyId()
                    .equalsIgnoreCase(researchStudyService.getStudyOwner(studyId))) {
                LOG.warn("Access denied: User {} is not the owner of study {}", userNameService.getCurrentuserPartyId(),
                        studyId);
                return Response.builder()
                        .data(Map.of())
                        .status("error")
                        .message("Access denied: Only the study owner is permitted to edit this research study.")
                        .errors(null)
                        .build();
            }

public void saveStudyInteraction(String studyId, String hubInteractionId, String interactionType,
            String description, String fromState,
            String toState, String request, String response, String errorResponse, int responseCode, String status,
            String actionType, String actionStatus) {
        try {
            final var userId = userNameService.getUserId();
            final var userPartyId = partyService.getPartyIdByUserId(userId);
            final var organizationPartyId = partyService.getOrganizationPartyIdByUser(userId);
            final var uri = HttpRequestResponseUtil.getCurrentRequest().getRequestURI();

            ObjectNode jsonNode = objectMapper.createObjectNode();

            jsonNode.put("hub_interaction_id", hubInteractionId);
            jsonNode.put("study_id", studyId);
            jsonNode.put("organization_party_id", organizationPartyId);

.orderBy(DSL.field("min_created_at").desc()); // if paginated
            LOG.info("Get Study Details Corresponds to a Query {}:", query.getSQL());
            return new JooqRowsSupplier.Builder()
                    .withRequest(payload)
                    .withQuery(Tables.class, schemaName.toLowerCase(), masterTableNameOrViewName,
                            (Query) query,
                            bindValues)
                    .withDSL(udiPrimeDbConfig.dsl())
                    .withLogger(LOG)
                    .includeGeneratedSqlInResp(includeGeneratedSqlInResp)
                    .includeGeneratedSqlInErrorResp(includeGeneratedSqlInErrorResp)
                    .build()
                    .response();

        } catch (

        DataAccessException e) {
            throw new RuntimeException("Error executing SQL query for '" + schemaName + "'", e);
        }
    }

    @Operation(summary = "SQL Columns from a master table or view for a specific column value")

final var userId = userNameService.getUserId();
            final var organizationId = partyService.getOrganizationPartyIdByUser(userId);
            // Construct the jOOQ query
            var query = udiPrimeDbConfig.dsl()
                    .selectDistinct(DSL.field("p.study_id"),
                            DSL.field("p.study_display_id"),
                            DSL.field("p.study_title"),
                            DSL.field("p.file_category"),
                            DSL.field("p.organization_name"),
                            DSL.field(
                                    "p.organization_party_id")) // Selecting the specified fields
                    .from(p)
                    .where("1=1");
            if (finalCondition != null) {
                query = query.and(finalCondition);
            }

            query = query.and(DSL.field("p.study_id").isNotNull())

public String cgmparticipantData(@PathVariable String studyId,
            @PathVariable String participantId,
            Model model, final HttpServletRequest request, @RequestParam(required = false) String tab) {
        model.addAttribute("studyId", studyId);
        model.addAttribute("participantId", participantId);
        model.addAttribute("tab", tab);
        String studyDisplayId = researchStudyService.getStudyDisplayId(studyId);
        String participantDisplayId = researchStudyService.getParticipantDisplayId(participantId);

        model.addAttribute("studyDisplayId", studyDisplayId);
        model.addAttribute("participantDisplayId", participantDisplayId);
        LOG.info("Getting details for studyId: {} , participantId: {}", studyDisplayId, participantDisplayId);
        String[] pageDescription = {

final @PathVariable String fileInteractionId) {

        // Fetch the result using the dynamically determined table and column; if
        // jOOQ-generated types were found, automatic column value mapping will occur
        final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class, schemaName,
                masterTableNameOrViewName);
        final var query = udiPrimeDbConfig.dsl().selectFrom(typableTable.table())
                .where((typableTable.column("file_interaction_id").eq(DSL.value(
                        fileInteractionId)))
                        .or((DSL.field("jsonb_path_query_first(interaction_hierarchy::jsonb, '$[0]')::text")
                                .eq(DSL.val("\"" + fileInteractionId + "\"")))))
                .and(DSL.field("interaction_status").eq(DSL.val(ActionStatus.SUCCESS)));

public Object parentFailedParticipantFileInteraction(
            final @RequestBody @Nonnull TabularRowsRequest payload,
            final @PathVariable String schemaName,
            final @PathVariable String masterTableNameOrViewName,
            @RequestParam(required = false, defaultValue = "*") String columns,
            @RequestHeader(value = "X-Include-Generated-SQL-In-Response", required = false) boolean includeGeneratedSqlInResp,
            @RequestHeader(value = "X-Include-Generated-SQL-In-Error-Response", required = false, defaultValue = "true") boolean includeGeneratedSqlInErrorResp) {

        try {
            TypableTable typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class,
                    schemaName,
                    masterTableNameOrViewName);
            var bindValues = new ArrayList<Object>();

            final var userId = userNameService.getUserId();

final @PathVariable String fileInteractionId) {

        // Fetch the result using the dynamically determined table and column; if
        // jOOQ-generated types were found, automatic column value mapping will occur
        final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class, schemaName,
                masterTableNameOrViewName);
        final var query = udiPrimeDbConfig.dsl().selectFrom(typableTable.table())
                .where((typableTable.column("file_interaction_id").eq(DSL.value(
                        fileInteractionId)))
                        .or((DSL.field("jsonb_path_query_first(interaction_hierarchy::jsonb, '$[0]')::text")
                                .eq(DSL.val("\"" + fileInteractionId + "\"")))))
                .and(DSL.field("interaction_status").eq(DSL.val(ActionStatus.FAILED)));

final @PathVariable String fileInteractionId) {

        // Fetch the result using the dynamically determined table and column; if
        // jOOQ-generated types were found, automatic column value mapping will occur
        final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class, schemaName,
                masterTableNameOrViewName);
        final var query = udiPrimeDbConfig.dsl().selectFrom(typableTable.table())
                .where((typableTable.column("file_interaction_id").eq(DSL.value(
                        fileInteractionId)))
                        .or((DSL.field("jsonb_path_query_first(interaction_hierarchy::jsonb, '$[0]')::text")
                                .eq(DSL.val("\"" + fileInteractionId + "\"")))))
                .and(DSL.field("interaction_status").eq(DSL.val(ActionStatus.SUCCESS)));

final @PathVariable String fileInteractionId) {

        // Fetch the result using the dynamically determined table and column; if
        // jOOQ-generated types were found, automatic column value mapping will occur
        final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class, schemaName,
                masterTableNameOrViewName);
        final var query = udiPrimeDbConfig.dsl().selectFrom(typableTable.table())
                .where((typableTable.column("file_interaction_id").eq(DSL.value(
                        fileInteractionId)))
                        .or((DSL.field("jsonb_path_query_first(interaction_hierarchy::jsonb, '$[0]')::text")
                                .eq(DSL.val("\"" + fileInteractionId + "\"")))))
                .and(DSL.field("interaction_status").eq(DSL.val(ActionStatus.FAILED)));

public Object childDatabaseFileInteractionModal(final @PathVariable(required = false) String schemaName,
            final @PathVariable String masterTableNameOrViewName,
            final @PathVariable String fileInteractionId) {

        // Fetch the result using the dynamically determined table and column; if
        // jOOQ-generated types were found, automatic column value mapping will occur
        final var typableTable = JooqRowsSupplier.TypableTable.fromTablesRegistry(Tables.class, schemaName,
                masterTableNameOrViewName);
        final var query = udiPrimeDbConfig.dsl().selectFrom(typableTable.table())
                .where(typableTable.column("file_interaction_id").eq(DSL.value(
                        fileInteractionId)));
        LOG.info("Get Study Interaction Details Corresponds to the schema {}:", schemaName);
        LOG.info("Get Study Interaction Details Corresponds to a Query {}:", query.getSQL());

private final CustomTabularFilter customTabularFilter;

    @Bean
    public com.fasterxml.jackson.databind.Module jsonbModule() {
        com.fasterxml.jackson.databind.module.SimpleModule module = new SimpleModule();
        module.addSerializer(org.jooq.JSONB.class, new JsonSerializer<org.jooq.JSONB>() {
            @Override
            public void serialize(org.jooq.JSONB value, JsonGenerator gen, SerializerProvider serializers)
                    throws IOException {
                gen.writeRawValue(value.data()); // or gen.writeString(value.data());
            }
        });
        return module;
    }

    public TabularRowsDatabaseFileInteractionControllerCustom(final UdiSecondaryDbConfig udiPrimeDbConfig,

private final CustomTabularFilter customTabularFilter;

    @Bean
    public com.fasterxml.jackson.databind.Module jsonbModule() {
        com.fasterxml.jackson.databind.module.SimpleModule module = new SimpleModule();
        module.addSerializer(org.jooq.JSONB.class, new JsonSerializer<org.jooq.JSONB>() {
            @Override
            public void serialize(org.jooq.JSONB value, JsonGenerator gen, SerializerProvider serializers)
                    throws IOException {
                gen.writeRawValue(value.data()); // or gen.writeString(value.data());
            }
        });
        return module;
    }

    public TabularRowsParticipantFileInteractionControllerCustom(final UdiSecondaryDbConfig udiPrimeDbConfig,

private final CustomTabularFilter customTabularFilter;

    @Bean
    public com.fasterxml.jackson.databind.Module jsonbModule() {
        com.fasterxml.jackson.databind.module.SimpleModule module = new SimpleModule();
        module.addSerializer(org.jooq.JSONB.class, new JsonSerializer<org.jooq.JSONB>() {
            @Override
            public void serialize(org.jooq.JSONB value, JsonGenerator gen, SerializerProvider serializers)
                    throws IOException {
                gen.writeRawValue(value.data()); // or gen.writeString(value.data());
            }
        });
        return module;
    }

    public TabularRowsStudyCGMInteractionControllerCustom(final UdiSecondaryDbConfig udiPrimeDbConfig,

private final CustomTabularFilter customTabularFilter;

    @Bean
    public com.fasterxml.jackson.databind.Module jsonbModule() {
        com.fasterxml.jackson.databind.module.SimpleModule module = new SimpleModule();
        module.addSerializer(org.jooq.JSONB.class, new JsonSerializer<org.jooq.JSONB>() {
            @Override
            public void serialize(org.jooq.JSONB value, JsonGenerator gen, SerializerProvider serializers)
                    throws IOException {
                gen.writeRawValue(value.data()); // or gen.writeString(value.data());
            }
        });
        return module;
    }

    public TabularRowsStudyInteractionControllerCustom(final UdiSecondaryDbConfig udiPrimeDbConfig,

private final CustomTabularFilter customTabularFilter;

    @Bean
    public com.fasterxml.jackson.databind.Module jsonbModule() {
        com.fasterxml.jackson.databind.module.SimpleModule module = new SimpleModule();
        module.addSerializer(org.jooq.JSONB.class, new JsonSerializer<org.jooq.JSONB>() {
            @Override
            public void serialize(org.jooq.JSONB value, JsonGenerator gen, SerializerProvider serializers)
                    throws IOException {
                gen.writeRawValue(value.data()); // or gen.writeString(value.data());
            }
        });
        return module;
    }

    public TabularRowsStudyMealsOrFitnessInteractionControllerCustom(final UdiSecondaryDbConfig udiPrimeDbConfig,