Supervision Hours since Sunday 3 weeks ago

// Calculate the Sunday preceding today (even if today is Sunday)
let today = new Date();
let daysSinceSunday = today.getDay();
let offset = daysSinceSunday === 0 ? 28 : daysSinceSunday + 21;
let startDate = new Date(today);
startDate.setDate(today.getDate() - offset);
startDate.setHours(0, 0, 0, 0);
 
// Define endDate as today
let endDate = new Date();
endDate.setHours(23, 59, 59, 999);
 
// Helper to format dates as YYYY-MM-DD
function formatDate(date) {
    const dt = new Date(date);
    const year = dt.getFullYear();
    const month = String(dt.getMonth() + 1).padStart(2, '0');
    const day = String(dt.getDate()).padStart(2, '0');
    return `${year}-${month}-${day}`;
}
 
// Format hours with simulated decimal alignment using monospace font
function formatHours(hours) {
    return `\`${(hours ?? 0).toFixed(2).padStart(6, ' ')}\``;
}
 
// Fetch pages and filter by date
let pages = dv.pages('"100 CPD Record" and #supervision')
    .where(p => {
        const dates = Array.isArray(p.date) ? p.date : [p.date];
        return dates.some(d => {
            const dt = new Date(d);
            return dt >= startDate && dt <= endDate;
        });
    })
    .sort(p => {
        const d = Array.isArray(p.date) ? p.date[0] : p.date;
        return d;
    }, 'asc');
 
// Debug
dv.paragraph(`Start Date: ${startDate.toDateString()} | End Date: ${endDate.toDateString()}`);
dv.paragraph(`Total pages fetched: ${pages.length}`);
 
if (pages.length === 0) {
    dv.paragraph("No supervision entries found for the specified query.");
} else {
    const groupedData = {};
    const bodyTotals = {};
    let grandTotal = 0;
 
    pages.forEach(p => {
        const body = p.body || "No Body";
        const who = p.who || "No Who";
        const key = `${body} - ${who}`;
        const hours = p.hours || 0;
        const dates = Array.isArray(p.date) ? p.date : [p.date];
        const medium = Array.isArray(p.medium)
            ? p.medium.join(", ")
            : (p.medium ?? "—");
 
        if (!groupedData[key]) {
            groupedData[key] = {
                body,
                who,
                totalHours: 0,
                entries: []
            };
        }
 
        dates.forEach(d => {
            const dt = new Date(d);
            if (dt >= startDate && dt <= endDate) {
                groupedData[key].entries.push({
                    file: p.file.link,
                    date: formatDate(dt),
                    medium,
                    hours: formatHours(hours)
                });
                groupedData[key].totalHours += hours;
 
                if (!bodyTotals[body]) bodyTotals[body] = 0;
                bodyTotals[body] += hours;
                grandTotal += hours;
            }
        });
    });
 
    const tableData = [];
    const bodyGroups = {};
 
    for (const [key, group] of Object.entries(groupedData)) {
        group.entries.sort((a, b) => new Date(a.date) - new Date(b.date));
        const body = group.body;
 
        if (!bodyGroups[body]) bodyGroups[body] = [];
 
        group.entries.forEach(entry => {
            bodyGroups[body].push([entry.file, entry.date, entry.medium, entry.hours]);
        });
 
        // summary row per who
        bodyGroups[body].push([
            `**${group.body} - ${group.who}**`,
            "",
            "",
            `\`${group.totalHours.toFixed(2).padStart(6, ' ')}\``
        ]);
    }
 
    for (const [body, rows] of Object.entries(bodyGroups)) {
        tableData.push([`**${body}**`, "", "", ""]);
        tableData.push(["----", "----", "----", "----"]);
        tableData.push(...rows);
        tableData.push(["----", "----", "----", "----"]);
        tableData.push([
            `**${body} Subtotal**`,
            "",
            "",
            `**\`${bodyTotals[body].toFixed(2).padStart(6, ' ')}\`**`
        ]);
        tableData.push(["====", "====", "====", "===="]);
    }
 
    // grand total
    tableData.push([
        "**Grand Total**",
        "",
        "",
        `**\`${grandTotal.toFixed(2).padStart(6, ' ')}\`**`
    ]);
 
    dv.table(["File", "Date", "Medium", "Hours"], tableData);
}