Client Hours since Sunday

// Calculate the Sunday preceding today (even if today is Sunday)
let today = new Date();
let daysSinceSunday = today.getDay();
let offset = daysSinceSunday === 0 ? 7 : daysSinceSunday;
let startDate = new Date(today);
startDate.setDate(today.getDate() - offset - 0);
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.toFixed(2).padStart(6, ' ')}\``;
}
 
// Helper to normalise medium display
function formatMedium(medium) {
    if (medium == null || medium === "") return "No Medium";
    if (Array.isArray(medium)) {
        const cleaned = medium
            .map(m => String(m).trim())
            .filter(Boolean);
        return cleaned.length ? cleaned.join(", ") : "No Medium";
    }
    return String(medium).trim() || "No Medium";
}
 
// If medium is an array matching date array length, use the corresponding item.
// Otherwise fall back to the whole medium field.
function getMediumForEntry(mediumField, index) {
    if (Array.isArray(mediumField)) {
        if (mediumField[index] != null && String(mediumField[index]).trim() !== "") {
            return String(mediumField[index]).trim();
        }
        const cleaned = mediumField
            .map(m => String(m).trim())
            .filter(Boolean);
        return cleaned.length ? cleaned.join(", ") : "No Medium";
    }
    return formatMedium(mediumField);
}
 
// Fetch pages and filter by date (supporting arrays)
let pages = dv.pages('"100 CPD Record" and #clienthours')
    .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 pages found for the specified query.");
} else {
    const groupedData = {};
    const agencyTotals = {};
    let grandTotal = 0;
 
    pages.forEach(p => {
        const agency = p.agency || "No Agency";
        const clientref = p.clientref || "No Clientref";
        const key = `${agency} - ${clientref}`;
        const hours = Number(p.hours) || 0;
        const dates = Array.isArray(p.date) ? p.date : [p.date];
        const mediumField = p.medium;
 
        if (!groupedData[key]) {
            groupedData[key] = {
                agency: agency,
                clientref: clientref,
                totalHours: 0,
                entries: []
            };
        }
 
        dates.forEach((d, i) => {
            const dt = new Date(d);
            if (dt >= startDate && dt <= endDate) {
                groupedData[key].entries.push({
                    file: p.file.link,
                    date: formatDate(dt),
                    medium: getMediumForEntry(mediumField, i),
                    hours: formatHours(hours)
                });
 
                groupedData[key].totalHours += hours;
 
                if (!agencyTotals[agency]) agencyTotals[agency] = 0;
                agencyTotals[agency] += hours;
                grandTotal += hours;
            }
        });
    });
 
    const tableData = [];
    const agencyGroups = {};
 
    for (const [key, group] of Object.entries(groupedData)) {
        group.entries.sort((a, b) => new Date(a.date) - new Date(b.date));
        const agency = group.agency;
 
        if (!agencyGroups[agency]) agencyGroups[agency] = [];
 
        group.entries.forEach(entry => {
            agencyGroups[agency].push([entry.file, entry.date, entry.medium, entry.hours]);
        });
 
        agencyGroups[agency].push([
            `**${group.agency} - ${group.clientref}**`,
            "",
            "",
            `\`${group.totalHours.toFixed(2).padStart(6, ' ')}\``
        ]);
    }
 
    for (const [agency, rows] of Object.entries(agencyGroups)) {
        tableData.push([`**${agency}**`, "", "", ""]);
        tableData.push(["----", "----", "----", "----"]);
        tableData.push(...rows);
        tableData.push(["----", "----", "----", "----"]);
        tableData.push([
            `**${agency} Subtotal**`,
            "",
            "",
            `**\`${agencyTotals[agency].toFixed(2).padStart(6, ' ')}\`**`
        ]);
        tableData.push(["====", "====", "====", "===="]);
    }
 
    tableData.push([
        "**Grand Total**",
        "",
        "",
        `**\`${grandTotal.toFixed(2).padStart(6, ' ')}\`**`
    ]);
 
    dv.table(["File", "Date", "Medium", "Hours"], tableData);
}