BEGIN
    DECLARE v_json LONGTEXT;

    SELECT JSON_OBJECT(
        'engineerName', U.ContactName,
        'companyReportDetailId', CRDR.CompanyReportDetailId,
        'companyDetailId', CD.CompanyDetailId,
        'companyNo', CD.CompanyNo,
        'companyArea', CD.CompanyArea,
        'assetId', CD.AssetId,
        'sapNo', CD.SapNo,
        'dateOfReading', CRD.ReportDate,
        'companyReportDetailIssuesId', CRDI.CompanyReportDetailIssuesId,
        'companyReportDetailReadingId', CRDR.CompanyReportDetailReadingId,
        'vibrationTypeId', CRDI.VibrationTypeId,
        'vibrationTypeName', VT.Description,
        'value', CRDI.Value,
        'unitsId', CRDI.UnitsId,
        'unitsName', UOM.Description,
        'brgId', CRDI.BrgId,
        'brgIdName', BGC.Description,
        'primaryIssue', CRDI.PrimaryIssue,
        'secondaryIssue', CRDI.SecondaryIssue,
        'longDescription', CRDI.LongDescription,
        'shortDescription', CRDI.ShortDescription,
        'primaryFaulId', CRDI.PrimaryFaulId,
        'secondaryFalutId', CRDI.SecondaryFalutId,
        'priorityNoId', CRDI.PriorityNoId,
        'graphDataImage', CRDI.Graph,

        'currentReportStatus', JSON_OBJECT(
            'globalCodeId', GC.GlobalCodeId,
            'globalCodeName', GC.Description
        ),

        'systemImagesResponses', (
            SELECT IFNULL(
                JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'imagesId', SI.ImagesId,
                        'imagesReferenceId', SI.ImagesReferenceId,
                        'imageType', SI.ImageType,
                        'imageUrl', SI.ImageUrl,
                        'title', SI.Title,
                        'description', SI.Description,
                        'imageSourceId', SI.ImageSourceId,
                        'imageSourceName', SRC.Description
                    )
                ),
                JSON_ARRAY()
            )
            FROM systemimages SI
            LEFT JOIN globalcodes SRC ON SRC.GlobalCodeId = SI.ImageSourceId
            WHERE SI.ImagesReferenceId = CRDI.CompanyReportDetailIssuesId
              AND SI.IsDeleted = 0
        ),

        'systemVideoResponses', JSON_ARRAY(),

        'historicalAssetAlarms', (
            SELECT IFNULL(
                JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'MonthYearName', CONCAT(
                            LEFT(MONTHNAME(STR_TO_DATE(CONCAT(LPAD(F.MonthValue, 2, '0'), '-01-', F.YearValue), '%m-%d-%Y')), 3),
                            '-', F.YearValue
                        ),
                        'ColorValue', F.ColorValue,
                        'CompanyReportDetailReadingId', F.CompanyReportDetailReadingId
                    )
                ),
                JSON_ARRAY()
            )
           FROM (
        SELECT F.*
        FROM companydetailfaultstatus F
        WHERE F.CompanyDetailId = CD.CompanyDetailId
        ORDER BY F.YearValue DESC, F.MonthValue DESC
        LIMIT 12
    ) AS F
        ),

        'historicalAssetAlarmsData', NULL
    )
    INTO v_json
    FROM companyreportdetails CRD
    INNER JOIN companyreportdetailreadings CRDR ON CRDR.CompanyReportDetailId = CRD.CompanyReportDetailId
    INNER JOIN companyreportdetailissues CRDI ON CRDI.CompanyReportDetailId = CRD.CompanyReportDetailId
    INNER JOIN companydetails CD ON CD.CompanyDetailId = CRDR.CompanyDetailId
    INNER JOIN users U ON U.UserId = CRD.UserId
    LEFT JOIN globalcodes GC ON GC.GlobalCodeId = CRD.ReportStatus
    LEFT JOIN globalcodes VT ON VT.GlobalCodeId = CRDI.VibrationTypeId
    LEFT JOIN globalcodes UOM ON UOM.GlobalCodeId = CRDI.UnitsId
    LEFT JOIN globalcodes BGC ON BGC.GlobalCodeId = CRDI.BrgId
    WHERE CD.CompanyDetailId = p_CompanyDetailId
      AND CRDR.CompanyReportDetailReadingId = p_CompanyReportDetailReadingId
    LIMIT 1;

    SELECT JSON_OBJECT(
        'shortCMResponsesList', NULL,
        'fullCMResponse', CAST(v_json AS JSON),
        'companyReportDetailResponses', NULL,
        'count', 1,
        'message', IF(v_json IS NULL, 'No Data Found', 'Data Found'),
        'status', IF(v_json IS NULL, FALSE, TRUE)
    ) AS FinalResponse;
END