`report.js`

69 lines
'use strict';

// Time-on-server report for the /826/ client area. Usage: node report.js [client_slug]
const path = require('path');
const fs = require('fs');
const { Pool } = require('pg');

(function loadEnv() {
  const p = path.join(__dirname, '.env');
  if (!fs.existsSync(p)) return;
  for (const raw of fs.readFileSync(p, 'utf8').split('\n')) {
    const line = raw.trim();
    if (!line || line.startsWith('#')) continue;
    const i = line.indexOf('='); if (i === -1) continue;
    const k = line.slice(0, i).trim();
    if (!(k in process.env)) process.env[k] = line.slice(i + 1).trim();
  }
})();

const CLIENT = process.argv[2] || process.env.CLIENT_SLUG || 'boomer';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

function fmt(sec) {
  sec = Math.round(sec || 0);
  const h = Math.floor(sec / 3600), m = Math.floor((sec % 3600) / 60), s = sec % 60;
  return `${h}h ${String(m).padStart(2, '0')}m ${String(s).padStart(2, '0')}s`;
}

(async () => {
  const c = (await pool.query(`SELECT name, email FROM clients WHERE slug=$1`, [CLIENT])).rows[0] || { name: CLIENT };
  const tot = (await pool.query(
    `SELECT count(*) sessions, coalesce(sum(duration_seconds),0) secs, coalesce(sum(hit_count),0) hits,
            min(started_at) first_seen, max(last_seen_at) last_seen
     FROM sessions WHERE client_slug=$1`, [CLIENT])).rows[0];

  console.log(`\n=== Time on server — ${c.name}${c.email ? ' <' + c.email + '>' : ''} (/826/) ===`);
  console.log(`Total time on server : ${fmt(tot.secs)}   (${tot.sessions} sessions, ${tot.hits} page hits)`);
  console.log(`First seen           : ${tot.first_seen ? new Date(tot.first_seen).toISOString() : '—'}`);
  console.log(`Last seen            : ${tot.last_seen ? new Date(tot.last_seen).toISOString() : '—'}`);

  const byUser = (await pool.query(
    `SELECT coalesce(remote_user,'(anon)') u, count(*) n, coalesce(sum(duration_seconds),0) secs
     FROM sessions WHERE client_slug=$1 GROUP BY 1 ORDER BY secs DESC`, [CLIENT])).rows;
  if (byUser.length) {
    console.log(`\nBy login:`);
    for (const r of byUser) console.log(`  ${r.u.padEnd(14)} ${fmt(r.secs).padEnd(16)} ${r.n} sessions`);
  }

  const byDay = (await pool.query(
    `SELECT to_char(date_trunc('day', started_at),'YYYY-MM-DD') d, count(*) n, coalesce(sum(duration_seconds),0) secs
     FROM sessions WHERE client_slug=$1 GROUP BY 1 ORDER BY 1 DESC LIMIT 14`, [CLIENT])).rows;
  if (byDay.length) {
    console.log(`\nLast ${byDay.length} active days:`);
    for (const r of byDay) console.log(`  ${r.d}  ${fmt(r.secs).padEnd(16)} ${r.n} sessions`);
  }

  const recent = (await pool.query(
    `SELECT started_at, last_seen_at, duration_seconds, hit_count, remote_user, host(ip) ip
     FROM sessions WHERE client_slug=$1 ORDER BY started_at DESC LIMIT 10`, [CLIENT])).rows;
  if (recent.length) {
    console.log(`\nMost recent sessions:`);
    for (const r of recent) {
      console.log(`  ${new Date(r.started_at).toISOString()}  ${fmt(r.duration_seconds).padEnd(16)} ${String(r.hit_count).padStart(3)} hits  ${r.remote_user || '(anon)'}  ${r.ip}`);
    }
  }
  console.log('');
  await pool.end();
})().catch((e) => { console.error(e.message); process.exit(1); });