-- not including shell
-- not including breadcrumbs from sqlpage_aide_navigation
-- not including page title from sqlpage_aide_navigation
SELECT
'html' as component;
SELECT '<div class="fs-3 p-1 fw-bold" style="background-color: #E3E3E2; text-black;">GLUCOSE STATISTICS AND TARGETS</div><div class="px-4">' as html;
SELECT
'<div class="card-content my-1">'|| strftime('%Y-%m-%d', MIN(Date_Time)) || ' - ' || strftime('%Y-%m-%d', MAX(Date_Time)) || ' <span style="float: right;">'|| CAST(julianday(MAX(Date_Time)) - julianday(MIN(Date_Time)) AS INTEGER) ||' days</span></div>' as html
FROM
combined_cgm_tracing
WHERE
participant_id = $participant_id
AND Date_Time BETWEEN $start_date AND $end_date;
SELECT
'<div class="card-content my-1" style="display: flex; flex-direction: row; justify-content: space-between;"><b>Time CGM Active</b> <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;"><b>' || ROUND(
(COUNT(DISTINCT DATE(Date_Time)) /
ROUND((julianday(MAX(Date_Time)) - julianday(MIN(Date_Time)) + 1))
) * 100, 2) || '</b> % <formula-component content="This metric calculates the percentage of time during a specific period (e.g., a day, week, or month) that the CGM device is actively collecting data. It takes into account the total duration of the monitoring period and compares it to the duration during which the device was operational and recording glucose readings."></formula-component></div></div></div>' as html
FROM
combined_cgm_tracing
WHERE
participant_id = $participant_id
AND Date_Time BETWEEN $start_date AND $end_date;
SELECT
'<div class="card-content my-1" style="display: flex; flex-direction: row; justify-content: space-between;"><b>Number of Days CGM Worn</b> <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;"><b>'|| COUNT(DISTINCT DATE(Date_Time)) ||'</b> days<formula-component content="This metric represents the total number of days the CGM device was worn by the user over a monitoring period. It helps in assessing the adherence to wearing the device as prescribed."></formula-component></div></div></div>' as html
FROM
combined_cgm_tracing
WHERE
participant_id = $participant_id
AND Date_Time BETWEEN $start_date AND $end_date;
SELECT
'<div class="card-body" style="background-color: #E3E3E2;border: 1px solid black;">
<div class="table-responsive">
<table class="table">
<tbody class="table-tbody list">
<tr>
<th colspan="2" style="text-align: center;">
Ranges And Targets For Type 1 or Type 2 Diabetes
</th>
</tr>
<tr>
<th>
Glucose Ranges
</th>
<th>
Targets % of Readings (Time/Day)
</th>
</tr>
<tr>
<td>Target Range 70-180 mg/dL</td>
<td>Greater than 70% (16h 48min)</td>
</tr>
<tr>
<td>Below 70 mg/dL</td>
<td>Less than 4% (58min)</td>
</tr>
<tr>
<td>Below 54 mg/dL</td>
<td>Less than 1% (14min)</td>
</tr>
<tr>
<td>Above 180 mg/dL</td>
<td>Less than 25% (6h)</td>
</tr>
<tr>
<td>Above 250 mg/dL</td>
<td>Less than 5% (1h 12min)</td>
</tr>
<tr>
<td colspan="2">Each 5% increase in time in range (70-180 mg/dL) is clinically beneficial.</td>
</tr>
</tbody>
</table>
</div>
</div>' as html;
SELECT
'<div class="card-content my-1" style="display: flex; flex-direction: row; justify-content: space-between;"><b>Mean Glucose</b> <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;"><b>'|| ROUND(AVG(CGM_Value), 2) ||'</b> mg/dL<formula-component content="Mean glucose reflects the average glucose level over the monitoring period, serving as an indicator of overall glucose control. It is a simple yet powerful measure in glucose management."></formula-component></div></div></div>' as html
FROM
combined_cgm_tracing
WHERE
participant_id = $participant_id
AND Date_Time BETWEEN $start_date AND $end_date;
SELECT
'<div class="card-content my-1" style="display: flex; flex-direction: row; justify-content: space-between;"><b>Glucose Management Indicator (GMI)</b> <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;"><b>'|| ROUND(AVG(CGM_Value) * 0.155 + 95, 2) ||'</b> %<formula-component content="GMI provides an estimated A1C level based on mean glucose, which can be used as an indicator of long-term glucose control. GMI helps in setting and assessing long-term glucose goals."></formula-component></div></div></div>' as html
FROM
combined_cgm_tracing
WHERE
participant_id = $participant_id
AND Date_Time BETWEEN $start_date AND $end_date;
SELECT
'<div class="card-content my-1" style="display: flex; flex-direction: row; justify-content: space-between;"><b>Glucose Variability</b> <div style="display: flex; justify-content: flex-end; align-items: center;"><div style="display: flex;align-items: center;gap: 0.1rem;"><b>'|| ROUND((SQRT(AVG(CGM_Value * CGM_Value) - AVG(CGM_Value) * AVG(CGM_Value)) / AVG(CGM_Value)) * 100, 2) ||'</b> %<formula-component content="Glucose variability measures fluctuations in glucose levels over time, calculated as the coefficient of variation (%CV). A lower %CV indicates more stable glucose control."></formula-component></div></div></div>' as html
FROM
combined_cgm_tracing
WHERE
participant_id = $participant_id
AND Date_Time BETWEEN $start_date AND $end_date;
SELECT
'<div class="card-content my-1">Defined as percent coefficient of variation (%CV); target ≤36%</div></div>' as html;