`scripts/export-to-gsheet.js`

94 lines
// export-to-gsheet.js — create (or update) a Google Sheet with the CRE data via the claude-gmc
// service account. Uses the Sheets API (enabled) to create + populate; tries Drive API to share with
// Steve (may be disabled). Node crypto only, no external libs.
//   node scripts/export-to-gsheet.js                 # create a new sheet
//   GSHEET_ID=<id> node scripts/export-to-gsheet.js  # update an existing sheet
'use strict';
const fs = require('fs'), crypto = require('crypto'), https = require('https'), path = require('path');
const { Pool } = require('pg');
const ROOT = path.join(__dirname, '..');
const SA = JSON.parse(fs.readFileSync('/Users/macstudio3/Projects/secrets-manager/gmc-sa-146735262.json', 'utf8'));
const SHARE_WITH = 'steve@designerwallcoverings.com';
const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });

const b64u = b => Buffer.from(b).toString('base64').replace(/\+/g, '-').replace(/\//g, '_').replace(/=+$/, '');
function req(method, host, pathname, headers, body) {
  return new Promise((res, rej) => {
    const r = https.request({ method, host, path: pathname, headers }, x => { let d = []; x.on('data', c => d.push(c)); x.on('end', () => res({ status: x.statusCode, body: Buffer.concat(d).toString() })); });
    r.on('error', rej); if (body) r.write(body); r.end();
  });
}
async function token() {
  const now = Math.floor(Date.now() / 1000);
  const claim = { iss: SA.client_email, scope: 'https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive', aud: 'https://oauth2.googleapis.com/token', iat: now, exp: now + 3600 };
  const unsigned = b64u(JSON.stringify({ alg: 'RS256', typ: 'JWT' })) + '.' + b64u(JSON.stringify(claim));
  const jwt = unsigned + '.' + b64u(crypto.createSign('RSA-SHA256').update(unsigned).sign(SA.private_key));
  const body = 'grant_type=' + encodeURIComponent('urn:ietf:params:oauth:grant-type:jwt-bearer') + '&assertion=' + jwt;
  const r = await req('POST', 'oauth2.googleapis.com', '/token', { 'Content-Type': 'application/x-www-form-urlencoded', 'Content-Length': Buffer.byteLength(body) }, body);
  if (r.status !== 200) throw new Error('token ' + r.status + ' ' + r.body.slice(0, 200));
  return JSON.parse(r.body).access_token;
}

// ---- gather data from the cre DB + FHA file ----
const COLS = async (name) => (await pool.query(`SELECT 1 FROM information_schema.columns WHERE table_name=$1 AND column_name='website'`, [name])).rowCount;
async function gather() {
  const webCol = await COLS('broker') ? ', b.website' : ', NULL website';
  const commercial = (await pool.query(`SELECT b.name, f.name firm,
      (SELECT count(*) FROM broker_listing bl WHERE bl.broker_id=b.id)::int listings, b.total_assets, b.phone, b.email ${webCol}
      FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.agent_type='commercial' OR b.agent_type IS NULL
      ORDER BY listings DESC NULLS LAST`)).rows;
  const agents = (await pool.query(`SELECT b.name, f.name firm,
      (SELECT count(*) FROM broker_condo bc WHERE bc.broker_id=b.id)::int condos, b.phone, b.email ${webCol}, b.license
      FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.agent_type='residential'
      ORDER BY condos DESC NULLS LAST`).catch(() => ({ rows: [] }))).rows;
  const firms = (await pool.query(`SELECT f.name firm, count(DISTINCT bl.listing_id)::int listings, count(DISTINCT b.id)::int brokers FROM firm f JOIN broker b ON b.firm_id=f.id JOIN broker_listing bl ON bl.broker_id=b.id GROUP BY f.name ORDER BY 2 DESC LIMIT 200`)).rows;
  const sales = (await pool.query(`SELECT address, city, zip, sold_price, sold_date, beds, baths, sqft FROM closed_sale WHERE sold_price>0 ORDER BY sold_date DESC NULLS LAST`).catch(() => ({ rows: [] }))).rows;
  let condos = [];
  try { condos = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'fha-approved-condos.json'), 'utf8')).condos.filter(c => c.warrant_signal === 'fha_approved'); } catch (_) {}
  return { commercial, agents, firms, sales, condos };
}
const A1 = rows => rows.map(r => r.map(v => v == null ? '' : v));

(async () => {
  const tok = await token();
  const auth = { Authorization: 'Bearer ' + tok };
  const { commercial, agents, firms, sales, condos } = await gather();
  const d = v => v ? String(v).slice(0, 10) : '';

  const sheets = [
    { title: 'Commercial Brokers', header: ['Broker', 'Firm', 'Listings', 'Total Crexi book', 'Phone', 'Email', 'Website'],
      rows: commercial.map(b => [b.name, b.firm, b.listings, b.total_assets, b.phone, b.email, b.website]) },
    { title: 'Residential Agents', header: ['Agent', 'Firm', 'Condos', 'Phone', 'Email', 'Website', 'License'],
      rows: agents.map(a => [a.name, a.firm, a.condos, a.phone, a.email, a.website, a.license]) },
    { title: 'Firms', header: ['Firm', 'Listings', 'Brokers'], rows: firms.map(f => [f.firm, f.listings, f.brokers]) },
    { title: 'Warrantable Condos (FHA)', header: ['Project', 'City', 'ZIP', 'Status (proxy)', 'Expiration'],
      rows: condos.map(c => [c.project_name, c.city, c.zip, 'FHA-approved (proxy, not lender-verified)', c.expiration_date]) },
    { title: 'Closed Sales (Redfin ~5yr)', header: ['Address', 'City', 'ZIP', 'Sold Price', 'Sold Date', 'Beds', 'Baths', 'SqFt'],
      rows: sales.map(s => [s.address, s.city, s.zip, s.sold_price, d(s.sold_date), s.beds, s.baths, s.sqft]) }
  ];
  console.log('tabs:', sheets.map(s => `${s.title}(${s.rows.length})`).join(', '));

  let id = process.env.GSHEET_ID;
  if (!id) {
    const create = JSON.stringify({ properties: { title: 'LA County CRE — Brokers · Agents · Firms · Condos · Sales' }, sheets: sheets.map(s => ({ properties: { title: s.title } })) });
    const r = await req('POST', 'sheets.googleapis.com', '/v4/spreadsheets', { ...auth, 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(create) }, create);
    if (r.status !== 200) throw new Error('create ' + r.status + ' ' + r.body.slice(0, 300));
    id = JSON.parse(r.body).spreadsheetId;
    console.log('created spreadsheet', id);
  }
  // populate each tab
  for (const s of sheets) {
    const data = [s.header, ...A1(s.rows)];
    const body = JSON.stringify({ values: data });
    const range = encodeURIComponent(`${s.title}!A1`);
    const r = await req('PUT', 'sheets.googleapis.com', `/v4/spreadsheets/${id}/values/${range}?valueInputOption=RAW`, { ...auth, 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(body) }, body);
    console.log(`  ${s.title}: ${r.status === 200 ? s.rows.length + ' rows' : 'ERR ' + r.status + ' ' + r.body.slice(0, 120)}`);
  }
  // try to share with Steve (Drive API — may be disabled)
  const perm = JSON.stringify({ role: 'writer', type: 'user', emailAddress: SHARE_WITH });
  const sh = await req('POST', 'www.googleapis.com', `/drive/v3/files/${id}/permissions?sendNotificationEmail=true`, { ...auth, 'Content-Type': 'application/json', 'Content-Length': Buffer.byteLength(perm) }, perm);
  console.log('share with ' + SHARE_WITH + ': ' + (sh.status === 200 ? 'OK' : 'FAILED ' + sh.status + ' (' + (JSON.parse(sh.body).error?.message || '').slice(0, 80) + ')'));
  console.log('\nURL: https://docs.google.com/spreadsheets/d/' + id + '/edit');
  await pool.end();
})().catch(e => { console.error('ERR', e.message); process.exit(1); });