`server.js`

92 lines
#!/usr/bin/env node
'use strict';
/*
 * dw-chat-analyzer — Dashboard + Leads + export over realestate.dw_chats
 * (Zendesk Chat traffic for Designer Wallcoverings). Node http + pg (parameterized).
 * Basic Auth admin / DW2024!  (override VIEWER_USER / VIEWER_PASS).
 */
const http = require('http'), fs = require('fs'), path = require('path'), crypto = require('crypto');
const { Pool } = require('pg');
const PORT = Number(process.env.PORT || 0), HOST = process.env.HOST || '127.0.0.1';
const USER = process.env.VIEWER_USER || 'admin', PASS = process.env.VIEWER_PASS || 'DW2024!';
const pool = new Pool({ host: process.env.PGHOST || '/tmp', database: process.env.PGDATABASE || 'realestate', max: 6 });
pool.on('error', (e) => console.error('pg pool error', e));
const T = 'dw_chats';

const SORTABLE = new Set(['started_at','duration','department','rating','is_lead','visitor_country','landing_page','ref_terms']);
const FILTERS = ['department','is_lead','lead_tier','ref_engine','visitor_country'];
const SEARCH = ['visitor_intent','ref_terms','landing_page','visitor_name','visitor_city','comment','tags'];

const eq = (a,b)=>{const x=Buffer.from(a),y=Buffer.from(b);return x.length===y.length&&crypto.timingSafeEqual(x,y);};
function authed(req){const h=req.headers.authorization||'';if(!h.startsWith('Basic '))return false;
  const [u,p]=Buffer.from(h.slice(6),'base64').toString('utf8').split(':');return eq(u||'',USER)&&eq(p||'',PASS);}

function where(qs){const c=[],p=[];
  for(const col of FILTERS){const v=qs.get(col);if(v!=null&&v!==''){p.push(col==='is_lead'?v==='true':v);c.push(`${col}=$${p.length}`);}}
  if(qs.get('new')==='1') c.push(`started_at > now() - interval '48 hours'`);
  const q=(qs.get('q')||'').trim();
  if(q){p.push(`%${q}%`);const i=p.length;c.push('('+SEARCH.map(s=>`${s} ILIKE $${i}`).join(' OR ')+')');}
  return {w:c.length?'WHERE '+c.join(' AND '):'',p};}

async function apiChats(qs){
  const {w,p}=where(qs);
  let sort=qs.get('sort')||'started_at'; if(!SORTABLE.has(sort))sort='started_at';
  const dir=(qs.get('dir')||'desc').toLowerCase()==='asc'?'ASC':'DESC';
  const limit=Math.min(Math.max(parseInt(qs.get('limit'),10)||50,1),200), page=Math.max(parseInt(qs.get('page'),10)||1,1);
  const total=Number((await pool.query(`SELECT count(*)::bigint n FROM ${T} ${w}`,p)).rows[0].n);
  const rows=(await pool.query(
    `SELECT id,started_at,department,agents,rating,is_lead,lead_tier,ref_engine,ref_terms,landing_page,page_count,
            visitor_name,visitor_city,visitor_region,visitor_country,visitor_intent,comment,tags,duration,
            zendesk_link, (started_at > now() - interval '48 hours') AS is_new
       FROM ${T} ${w} ORDER BY ${sort} ${dir} NULLS LAST, started_at DESC
      LIMIT $${p.length+1} OFFSET $${p.length+2}`,[...p,limit,(page-1)*limit])).rows;
  return {total,page,limit,sort,dir,rows};
}

let sc=null,sa=0;
async function apiStats(){
  if(sc&&Date.now()-sa<60000)return sc;
  const one=async(q)=>(await pool.query(q)).rows;
  const h=(await pool.query(`SELECT count(*)::int total,
            count(*) filter (where lead_tier='HOT')::int hot,
            count(*) filter (where lead_tier='WARM')::int warm,
            count(*) filter (where lead_tier='HOT' and started_at > now()-interval '48 hours')::int hot_recent,
            count(*) filter (where started_at > now()-interval '48 hours')::int newc FROM ${T}`)).rows[0];
  const tot=h.total, leads=h.hot, hot=h.hot, warm=h.warm, hot_recent=h.hot_recent, newc=h.newc;
  const terms=await one(`SELECT ref_terms v, count(*)::int n FROM ${T} WHERE ref_terms<>'' GROUP BY 1 ORDER BY n DESC LIMIT 12`);
  const land=await one(`SELECT regexp_replace(landing_page,'^https?://[^/]+','') v, count(*)::int n FROM ${T} WHERE landing_page<>'' GROUP BY 1 ORDER BY n DESC LIMIT 12`);
  const eng=await one(`SELECT coalesce(nullif(ref_engine,''),'(direct/none)') v, count(*)::int n FROM ${T} GROUP BY 1 ORDER BY n DESC LIMIT 8`);
  const dept=await one(`SELECT coalesce(nullif(department,''),'(none)') v, count(*)::int n FROM ${T} GROUP BY 1 ORDER BY n DESC LIMIT 8`);
  const rating=await one(`SELECT coalesce(nullif(rating,''),'unrated') v, count(*)::int n FROM ${T} GROUP BY 1 ORDER BY n DESC`);
  const geo=await one(`SELECT coalesce(nullif(visitor_country,''),'?') v, count(*)::int n FROM ${T} GROUP BY 1 ORDER BY n DESC LIMIT 10`);
  const byday=await one(`SELECT to_char(date_trunc('day',started_at),'YYYY-MM-DD') v, count(*)::int n FROM ${T} WHERE started_at IS NOT NULL GROUP BY 1 ORDER BY 1 DESC LIMIT 30`);
  return sc={total:tot,leads,hot,warm,hot_recent,newc,terms,land,eng,dept,rating,geo,byday}, sa=Date.now(), sc;
}

function send(res,code,body,type='application/json'){res.writeHead(code,{'Content-Type':type,'Cache-Control':'no-store'});
  res.end(typeof body==='string'||Buffer.isBuffer(body)?body:JSON.stringify(body));}

const server=http.createServer(async(req,res)=>{
  if(!authed(req)){res.writeHead(401,{'WWW-Authenticate':'Basic realm="dw-chat"'});return res.end('Auth required');}
  const url=new URL(req.url,'http://x');
  try{
    if(url.pathname==='/'||url.pathname==='/index.html')return send(res,200,fs.readFileSync(path.join(__dirname,'public','index.html')),'text/html; charset=utf-8');
    if(url.pathname==='/api/stats')return send(res,200,await apiStats());
    if(url.pathname==='/api/chats')return send(res,200,await apiChats(url.searchParams));
    // FileMaker pulls this (Insert from URL) to pop an on-screen dialog for new HOT chats.
    if(url.pathname==='/api/new-hot'){
      const since=url.searchParams.get('since')||new Date(Date.now()-864e5).toISOString();
      const r=await pool.query(
        `SELECT id, started_at, coalesce(nullif(visitor_email,''),nullif(visitor_phone,''),'(contact on file)') contact,
                nullif(concat_ws(', ',nullif(visitor_city,''),nullif(visitor_region,''),nullif(visitor_country,'')),'') location,
                regexp_replace(landing_page,'^https?://[^/]+','') landing, zendesk_link
           FROM ${T} WHERE lead_tier='HOT' AND started_at > $1::timestamptz
          ORDER BY started_at DESC LIMIT 25`,[since]);
      return send(res,200,{count:r.rows.length, latest:r.rows[0]?r.rows[0].started_at:since, chats:r.rows});
    }
    if(url.pathname==='/healthz')return send(res,200,{ok:true});
    return send(res,404,{error:'not found'});
  }catch(e){console.error(e);return send(res,500,{error:String(e.message||e)});}
});
server.listen(PORT,HOST,()=>console.log(`dw-chat-analyzer live: http://${HOST}:${server.address().port}  (login ${USER})`));