In app/templates/dashboard.html, inside the <script> block, add these two functions somewhere above document.addEventListener("DOMContentLoaded", ...):

  function buildReminderScript(rem) {
    const customer = rem.customer_name || "[Customer]";
    const advisor = "[Your Name]";
    const centre = "Lincoln Audi";
    const dateStr = rem.date || "your upcoming booking";
    const timeStr = rem.time || "";

    return `Hi ${customer}, it’s ${advisor} from ${centre}.\n\nJust a quick reminder about ${dateStr}${timeStr ? " at " + timeStr : ""} for your Audi (${rem.reg || ""}). This is booked as: ${rem.reason}.\n\nIf anything has changed or you need to adjust the time, just reply to this message and we’ll sort it for you.\n\nThanks,\n${advisor}\n${centre}`;
  }

  async function loadReminders() {
    const list = document.getElementById("reminders-list");
    const advisorSel = document.getElementById("rem-advisor");
    const windowSel = document.getElementById("rem-window");
    if (!list) return;

    list.innerHTML = '<li class="text-xs text-neutral-400">Loading…</li>';

    const params = new URLSearchParams();
    if (windowSel?.value && windowSel.value !== "today") {
      params.set("window", windowSel.value);
    }
    if (advisorSel?.value) {
      params.set("advisor", advisorSel.value);
    }

    try {
      const res = await fetch("/api/reminders" + (params.toString() ? "?" + params.toString() : ""));
      if (!res.ok) throw new Error("Failed to load reminders");
      const payload = await res.json();
      const items = payload.data || [];

      list.innerHTML = "";

      if (!items.length) {
        list.innerHTML = '<li class="text-xs text-neutral-400">No upcoming appointments in this window.</li>';
        return;
      }

      const advisorSet = new Set();
      items.forEach((rem) => advisorSet.add(rem.advisor));

      // Populate advisor dropdown from data if empty apart from "All"
      if (advisorSel && advisorSel.options.length <= 1) {
        advisorSet.forEach((name) => {
          const opt = document.createElement("option");
          opt.value = name;
          opt.textContent = name;
          advisorSel.appendChild(opt);
        });
      }

      items.forEach((rem) => {
        const li = document.createElement("li");
        li.className =
          "border border-dp-silver rounded-xl px-3 py-2 bg-white hover:border-dp-blue/40 hover:shadow-card cursor-pointer transition";

        li.innerHTML = `
          <div class="flex items-center justify-between gap-2">
            <div class="text-[11px] text-neutral-500">${rem.date} • ${rem.time || ""}</div>
            <div class="text-[11px] text-neutral-500">${rem.advisor || ""}</div>
          </div>
          <div class="text-sm font-medium text-dp-ink">${rem.customer_name || ""} • ${rem.reg || ""}</div>
          <div class="text-[11px] text-neutral-500 mt-0.5">${rem.reason || ""}</div>
        `;

        li.addEventListener("click", () => {
          const label = document.getElementById("rem-selected-label");
          const ta = document.getElementById("rem-script-preview");
          if (!label || !ta) return;
          label.textContent = `${rem.customer_name || ""} • ${rem.date || ""} ${rem.time || ""}`;
          ta.value = buildReminderScript(rem);
          ta.scrollTop = 0;
        });

        list.appendChild(li);
      });
    } catch (e) {
      console.error(e);
      list.innerHTML = '<li class="text-xs text-red-500">Error loading reminders.</li>';
    }
  }

  async function loadTco() {
    const tableContainer = document.getElementById("tco-table");
    const modelSel = document.getElementById("tco-model");
    const talking = document.getElementById("tco-talking-points");
    if (!tableContainer) return;

    tableContainer.textContent = "Loading…";

    const params = new URLSearchParams();
    if (modelSel?.value) params.set("model", modelSel.value);

    try {
      const res = await fetch("/api/tco" + (params.toString() ? "?" + params.toString() : ""));
      if (!res.ok) throw new Error("Failed to load TCO");
      const payload = await res.json();
      const rows = payload.data || [];

      if (!rows.length) {
        tableContainer.textContent = "No TCO data available yet.";
        if (talking) talking.textContent = "Once TCO data is populated, you’ll see simple talking points here for each model.";
        return;
      }

      // Populate model selector on first load
      if (modelSel && modelSel.options.length <= 1) {
        rows.forEach((r) => {
          const opt = document.createElement("option");
          opt.value = r.model;
          opt.textContent = r.model;
          modelSel.appendChild(opt);
        });
      }

      const table = document.createElement("table");
      table.className = "w-full text-[11px] text-left border-collapse";

      const thead = document.createElement("thead");
      thead.innerHTML = `
        <tr class="border-b border-dp-silver/80 text-neutral-500">
          <th class="py-1 pr-2 font-medium">Model</th>
          <th class="py-1 pr-2 font-medium">Finance p/m</th>
          <th class="py-1 pr-2 font-medium">Insurance est.</th>
          <th class="py-1 pr-2 font-medium">Energy / fuel</th>
          <th class="py-1 pr-2 font-medium">Service plan</th>
          <th class="py-1 pr-2 font-medium">Tyres & misc.</th>
        </tr>
      `;

      const tbody = document.createElement("tbody");
      rows.forEach((r) => {
        const tr = document.createElement("tr");
        tr.className = "border-b border-dp-silver/40";

        tr.innerHTML = `
          <td class="py-1 pr-2">${r.model}</td>
          <td class="py-1 pr-2">£${r.monthly_payment}</td>
          <td class="py-1 pr-2">£${r.insurance_est}</td>
          <td class="py-1 pr-2">£${r.energy_fuel}</td>
          <td class="py-1 pr-2">£${r.service_plan}</td>
          <td class="py-1 pr-2">£${r.tyres_misc}</td>
        `;

        tr.addEventListener("click", () => {
          if (!talking) return;
          const total =
            r.monthly_payment +
            r.insurance_est +
            r.energy_fuel +
            r.service_plan +
            r.tyres_misc;

          talking.textContent =
            `For the ${r.model}, the total monthly ownership picture is around £${total.toFixed(
              0
            )}. ` +
            `That includes the finance payment, an estimate for insurance, realistic energy or fuel, the service plan and an allowance for tyres and general wear. ` +
            `Use this to move the conversation away from just “monthly price” and towards overall value, convenience and budget confidence.`;
        });

        tbody.appendChild(tr);
      });

      table.appendChild(thead);
      table.appendChild(tbody);

      tableContainer.innerHTML = "";
      tableContainer.appendChild(table);
    } catch (e) {
      console.error(e);
      tableContainer.textContent = "Error loading TCO data.";
      if (talking) talking.textContent = "Unable to load TCO talking points right now.";
    }
  }