quorlasocial/dashboard/server/published.js

// "Published" feed: every post that went out on a client's accounts, with its numbers.
// Instagram and Facebook come from the nightly snapshots (so posts made outside Quorla show too);
// other platforms come from what Quorla published, with YouTube counts fetched on refresh.
import fs from "node:fs";
import path from "node:path";
import { DATA_DIR } from "./config.js";
import * as clients from "./clients.js";
import * as metrics from "./metrics.js";
import * as sched from "../../connector/src/schedule.js";
import * as extra from "../../connector/src/platforms/index.js";

const STATS_FILE = path.join(DATA_DIR, "post-stats.json");
const readStats = () => { try { return JSON.parse(fs.readFileSync(STATS_FILE, "utf8")); } catch { return { fetchedAt: null, posts: {} }; } };

const num = (v) => (typeof v === "number" && Number.isFinite(v) ? v : null);
const sum = (...vs) => { const xs = vs.map(num).filter((x) => x !== null); return xs.length ? xs.reduce((a, b) => a + b, 0) : null; };

function igItem(client, p, viaQuorla) {
  const i = p.insights || {};
  const likes = num(p.like_count), comments = num(p.comments_count), saves = num(i.saved), shares = num(i.shares);
  return {
    key: `instagram:${p.id}`, client: client.slug, clientName: client.name, platform: "instagram",
    id: p.id, at: p.timestamp, caption: p.caption || "", permalink: p.permalink || null,
    format: p.media_product_type === "REELS" ? "Reel" : p.media_type === "CAROUSEL_ALBUM" ? "Carousel" : p.media_type === "VIDEO" ? "Video" : "Image",
    thumb: p.thumbnail_url || p.media_url || null,
    metrics: { reach: num(i.reach), views: num(i.views), likes, comments, saves, shares },
    interactions: num(i.total_interactions) ?? sum(likes, comments, saves, shares),
    viaQuorla,
  };
}

function fbItem(client, p, viaQuorla) {
  return {
    key: `facebook:${p.id}`, client: client.slug, clientName: client.name, platform: "facebook",
    id: p.id, at: p.created_time, caption: p.message || "", permalink: p.permalink_url || null, format: "Post",
    thumb: p.picture || null,
    metrics: { reach: null, views: null, likes: num(p.reactions), comments: num(p.comments), saves: null, shares: num(p.shares) },
    interactions: sum(p.reactions, p.comments, p.shares),
    viaQuorla,
  };
}

/** All published posts, newest first. Filters: client slug, platform id, days back. */
export function feed({ client, platform, days = 30 } = {}) {
  const since = Date.now() - Number(days || 30) * 86400000;
  const all = clients.listClients().filter((c) => !client || c.slug === client);
  const quorla = sched.listPosts({}).filter((p) => ["published", "partial"].includes(p.status));
  const quorlaIds = new Set(quorla.flatMap((p) => Object.values(p.results || {}).map((r) => r?.id).filter(Boolean).map(String)));
  const stats = readStats().posts;
  const items = [];

  for (const c of all) {
    const snap = metrics.readSnapshot(c.slug);
    for (const p of snap?.ig?.posts || []) items.push(igItem(c, p, quorlaIds.has(String(p.id))));
    for (const p of snap?.fb?.posts || []) items.push(fbItem(c, p, quorlaIds.has(String(p.id))));
  }
  const seen = new Set(items.map((i) => i.key));
  const byClient = Object.fromEntries(all.map((c) => [c.slug, c]));

  // Platforms the snapshots don't cover, plus Quorla posts newer than the last snapshot.
  for (const p of quorla) {
    const c = byClient[p.client];
    if (!c) continue;
    for (const [pl, r] of Object.entries(p.results || {})) {
      if (!r?.id || r.cancelled) continue;
      const key = `${pl}:${r.id}`;
      if (seen.has(key)) continue;
      const s = stats[key] || {};
      items.push({
        key, client: c.slug, clientName: c.name, platform: pl, id: String(r.id),
        at: p.publishedAt || p.publishAt, caption: p.options?.[pl]?.caption ?? p.caption ?? "", permalink: r.permalink || null,
        format: p.type === "REELS" ? "Video" : p.type === "CAROUSEL" ? "Carousel" : p.type === "TEXT" ? "Text" : "Image",
        thumb: s.thumb || null, media: p.media?.[0] || null,
        metrics: { reach: null, views: num(s.views), likes: num(s.likes), comments: num(s.comments), saves: null, shares: null },
        interactions: sum(s.likes, s.comments),
        viaQuorla: true, note: r.inbox ? "Sent to the TikTok app to finish" : null,
      });
      seen.add(key);
    }
  }

  const list = items
    .filter((i) => (!platform || i.platform === platform) && i.at && new Date(i.at).getTime() >= since)
    .sort((a, b) => new Date(b.at) - new Date(a.at));
  const reachKnown = list.filter((i) => i.metrics.reach !== null);
  return {
    items: list,
    totals: {
      posts: list.length,
      viaQuorla: list.filter((i) => i.viaQuorla).length,
      reach: reachKnown.reduce((s, i) => s + i.metrics.reach, 0),
      interactions: list.reduce((s, i) => s + (i.interactions || 0), 0),
      avgInteractions: list.length ? Math.round(list.reduce((s, i) => s + (i.interactions || 0), 0) / list.length) : 0,
    },
    snapshotsAt: Object.fromEntries(all.map((c) => [c.slug, metrics.readSnapshot(c.slug)?.fetchedAt || null])),
    statsAt: readStats().fetchedAt,
  };
}

/** Fetch counts for Quorla-published posts on platforms with a stats reader (YouTube), last 90 days. */
export async function refreshStats() {
  const since = Date.now() - 90 * 86400000;
  const want = {};
  for (const p of sched.listPosts({})) {
    if (!["published", "partial"].includes(p.status) || new Date(p.publishedAt || p.publishAt).getTime() < since) continue;
    for (const [pl, r] of Object.entries(p.results || {})) {
      if (pl !== "youtube" || !r?.id) continue;
      ((want[p.client] ||= {})[pl] ||= []).push(String(r.id));
    }
  }
  const out = readStats();
  const errors = [];
  for (const [client, byPl] of Object.entries(want)) {
    for (const [pl, ids] of Object.entries(byPl)) {
      try {
        const s = await extra.postStats(pl, client, ids);
        for (const [id, v] of Object.entries(s)) out.posts[`${pl}:${id}`] = v;
      } catch (e) { errors.push(`${client} ${pl}: ${e.message}`); }
    }
  }
  out.fetchedAt = new Date().toISOString();
  fs.writeFileSync(STATS_FILE, JSON.stringify(out, null, 1));
  return { fetchedAt: out.fetchedAt, errors };
}

const WINDOWS = [[6, 9], [9, 12], [12, 15], [15, 18], [18, 21], [21, 24]];
const WEEKDAYS = ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"];
const median = (xs) => { const s = [...xs].sort((a, b) => a - b); const m = s.length >> 1; return s.length ? (s.length % 2 ? s[m] : (s[m - 1] + s[m]) / 2) : null; };

/**
 * When this client's Instagram posts do best, from their own last 120 days: each post's reach divided by the
 * client's median reach, grouped by weekday and 3-hour window (client's local time). Needs 8+ posts with reach.
 */
export function bestTimes(slug, timezone) {
  const posts = feed({ client: slug, platform: "instagram", days: 120 }).items.filter((i) => i.metrics.reach != null && i.metrics.reach > 0);
  if (posts.length < 8) return { basedOn: posts.length, slots: [], times: [] };
  const base = median(posts.map((p) => p.metrics.reach));
  const fmt = new Intl.DateTimeFormat("en-CA", { timeZone: timezone, weekday: "short", hour: "2-digit", minute: "2-digit", hour12: false });
  const groups = {};
  for (const p of posts) {
    const parts = Object.fromEntries(fmt.formatToParts(new Date(p.at)).map((x) => [x.type, x.value]));
    const hour = Number(parts.hour) % 24, minute = Number(parts.minute);
    const w = WINDOWS.findIndex(([a, b]) => hour >= a && hour < b);
    if (w < 0) continue;
    const day = WEEKDAYS.indexOf(parts.weekday.slice(0, 3));
    (groups[`${day}-${w}`] ||= { day, w, ratios: [], minutes: [] }).ratios.push(p.metrics.reach / base);
    groups[`${day}-${w}`].minutes.push(hour * 60 + minute);
  }
  const hhmm = (m) => `${String(Math.floor(m / 60)).padStart(2, "0")}:${String(Math.round(m % 60 / 15) * 15 % 60).padStart(2, "0")}`;
  const slots = Object.values(groups).filter((g) => g.ratios.length >= 2)
    .map((g) => ({ day: WEEKDAYS[g.day], window: `${String(WINDOWS[g.w][0]).padStart(2, "0")}:00–${String(WINDOWS[g.w][1] % 24).padStart(2, "0")}:00`, time: hhmm(median(g.minutes)), posts: g.ratios.length, lift: Math.round((median(g.ratios) - 1) * 100) }))
    .filter((s) => s.lift > 0).sort((a, b) => b.lift - a.lift).slice(0, 3);
  return { basedOn: posts.length, medianReach: base, slots, times: [...new Set(slots.map((s) => s.time))] };
}