`src/connectors/gsc.js`

292 lines
'use strict';

/**
 * Google Search Console connector — spec §18.
 *
 * Fixture-backed when GSC_SITE_URL / GOOGLE_SERVICE_ACCOUNT_JSON_BASE64 are
 * absent (the default). Loads gsc-queries.json and gsc-pages.json, classifies
 * each query for brand/nonbrand and topic cluster, then UPSERTs into
 * gsc_query_metrics / gsc_page_metrics with is_demo=true.
 *
 * IMPORTANT: Search Console data represents ORGANIC search only.  Never label
 * it as paid search.
 *
 * @module src/connectors/gsc
 */

const path = require('path');
const fs = require('fs');
const crypto = require('crypto');
const { query } = require('../../db');

// ---------------------------------------------------------------------------
// Credentials probe
// ---------------------------------------------------------------------------

function hasCredentials() {
  return Boolean(process.env.GOOGLE_SERVICE_ACCOUNT_JSON_BASE64 && process.env.GSC_SITE_URL);
}

/**
 * @returns {{ connected: boolean, demo: boolean, error?: string }}
 */
async function connectionTest() {
  if (!hasCredentials()) {
    return { connected: false, demo: true, error: 'No GSC_SITE_URL / GOOGLE_SERVICE_ACCOUNT_JSON_BASE64 configured — running in DEMO/fixture mode.' };
  }
  // TODO real API: use googleapis searchconsole.sites.getQueryReport or
  // the webmasters.searchanalytics.query endpoint:
  //
  // const { google } = require('googleapis');
  // const creds = JSON.parse(Buffer.from(process.env.GOOGLE_SERVICE_ACCOUNT_JSON_BASE64,'base64').toString('utf8'));
  // const auth = new google.auth.GoogleAuth({ credentials: creds, scopes: ['https://www.googleapis.com/auth/webmasters.readonly'] });
  // const sc = google.searchconsole({ version: 'v1', auth });
  // await sc.sites.get({ siteUrl: process.env.GSC_SITE_URL });
  throw new Error('GSC live API not enabled (no service account)');
}

// ---------------------------------------------------------------------------
// Brand & cluster classification — spec §18
// ---------------------------------------------------------------------------

/**
 * Classify a query as brand or nonbrand.
 * Brand = contains the token 'rentv' (case-insensitive).
 *
 * @param {string} queryStr
 * @returns {boolean}
 */
function classifyBrandVsNonbrand(queryStr) {
  if (!queryStr || typeof queryStr !== 'string') return false;
  return /rentv/i.test(queryStr);
}

/**
 * Assign one topic cluster to a query string.
 * Implements the §18 cluster taxonomy.
 *
 * Priority order matters: more specific patterns first.
 *
 * @param {string} queryStr
 * @returns {string} one of the cluster keys defined in spec §18
 */
function clusterQuery(queryStr) {
  if (!queryStr || typeof queryStr !== 'string') return 'other';
  const q = queryStr.toLowerCase();

  // Conference / event signals
  if (/\b(conference|summit|event|expo|forum|naiop|uli|boma|icsc|crew|sior|ccim|convention|symposium|award|gala)\b/.test(q)) {
    return 'conference_event';
  }

  // Advertiser category / advertising signals
  if (/\b(advertis|sponsor|media kit|newsletter|eblast|placement|property spotlight|cre talk)\b/.test(q)) {
    return 'advertiser_category';
  }

  // Finance / lending signals
  if (/\b(financ|lend|loan|mortgage|debt|capital|credit|rate|refinanc|bridge|mezzanine|cmbs|note|fund)\b/.test(q)) {
    return 'finance_lending';
  }

  // Brokerage / deal signals
  if (/\b(brokerage|broker|sale|sold|deal|acquisition|disposition|1031|nnn|net lease|cap rate|listing)\b/.test(q)) {
    return 'brokerage_deal';
  }

  // Property type signals
  if (/\b(office|industrial|retail|multifamily|apartment|warehouse|flex|mixed.use|data.center|lab|life.science|hotel|hospitality|land|development)\b/.test(q)) {
    return 'property_type';
  }

  // California market signals
  if (/\b(los angeles|la |orange county|irvine|san diego|inland empire|bay area|san francisco|sacramento|ventura|pasadena|long beach|burbank|riverside|ontario|santa ana|socal|southern california|northern california|california|ca )\b/.test(q)) {
    return 'california_market';
  }

  // Arizona market signals
  if (/\b(phoenix|scottsdale|tempe|mesa|chandler|gilbert|glendale|tucson|arizona|az )\b/.test(q)) {
    return 'arizona_market';
  }

  return 'other';
}

// ---------------------------------------------------------------------------
// Fixture helpers
// ---------------------------------------------------------------------------

const FIXTURES_DIR = path.resolve(__dirname, '../../fixtures');

function loadFixture(name) {
  return JSON.parse(fs.readFileSync(path.join(FIXTURES_DIR, name), 'utf8'));
}

function fixtureChecksum(name) {
  return crypto.createHash('sha256').update(fs.readFileSync(path.join(FIXTURES_DIR, name))).digest('hex');
}

// ---------------------------------------------------------------------------
// Import run bookkeeping (mirrors ga4.js pattern)
// ---------------------------------------------------------------------------

async function startRun(kind, sourceFile, checksum) {
  const res = await query(
    `INSERT INTO analytics_import_runs (kind, source_file, checksum, is_demo, status)
     VALUES ($1,$2,$3,true,'RUNNING') RETURNING id`,
    [kind, sourceFile, checksum]
  );
  return res.rows[0].id;
}

async function finishRun(runId, rowCount, error) {
  await query(
    `UPDATE analytics_import_runs
        SET finished_at = now(), status = $2, row_count = $3, error = $4
      WHERE id = $1`,
    [runId, error ? 'ERROR' : 'SUCCESS', rowCount, error || null]
  );
}

// ---------------------------------------------------------------------------
// Importers
// ---------------------------------------------------------------------------

/**
 * Load gsc-queries.json → gsc_query_metrics.
 * Applies classifyBrandVsNonbrand() + clusterQuery() to each row.
 *
 * @param {boolean} dryRun
 * @returns {{ inserted: number, skipped: number, rejected: [], runId: string|null, dryRun: boolean }}
 */
async function importQueries(dryRun = false) {
  const fixture = loadFixture('gsc-queries.json');
  const rows = fixture.rows;
  const checksum = fixtureChecksum('gsc-queries.json');
  let runId = null;
  let inserted = 0;

  if (!dryRun) {
    runId = await startRun('GSC_QUERIES', 'fixtures/gsc-queries.json', checksum);
    const dates = [...new Set(rows.map((r) => r.metric_date))];
    for (const d of dates) {
      await query('DELETE FROM gsc_query_metrics WHERE metric_date = $1 AND is_demo = true', [d]);
    }
  }

  try {
    for (const row of rows) {
      if (!row.metric_date || !row.query) continue;
      const isBrand = classifyBrandVsNonbrand(row.query);
      const cluster = clusterQuery(row.query);

      if (!dryRun) {
        await query(
          `INSERT INTO gsc_query_metrics
             (metric_date, query, country, device, clicks, impressions, ctr, position,
              is_brand, cluster, is_demo)
           VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,true)`,
          [
            row.metric_date, row.query, row.country, row.device,
            row.clicks, row.impressions, row.ctr, row.position,
            isBrand, cluster,
          ]
        );
      }
      inserted++;
    }

    if (!dryRun) await finishRun(runId, inserted, null);
    return { inserted, skipped: 0, rejected: [], runId, dryRun };
  } catch (err) {
    if (!dryRun && runId) await finishRun(runId, inserted, err.message);
    throw err;
  }
}

/**
 * Load gsc-pages.json → gsc_page_metrics.
 *
 * @param {boolean} dryRun
 * @returns {{ inserted: number, skipped: number, rejected: [], runId: string|null, dryRun: boolean }}
 */
async function importPages(dryRun = false) {
  const fixture = loadFixture('gsc-pages.json');
  const rows = fixture.rows;
  const checksum = fixtureChecksum('gsc-pages.json');
  let runId = null;
  let inserted = 0;

  if (!dryRun) {
    runId = await startRun('GSC_PAGES', 'fixtures/gsc-pages.json', checksum);
    const dates = [...new Set(rows.map((r) => r.metric_date))];
    for (const d of dates) {
      await query('DELETE FROM gsc_page_metrics WHERE metric_date = $1 AND is_demo = true', [d]);
    }
  }

  try {
    for (const row of rows) {
      if (!row.metric_date || !row.page) continue;
      if (!dryRun) {
        await query(
          `INSERT INTO gsc_page_metrics
             (metric_date, page, country, device, clicks, impressions, ctr, position, is_demo)
           VALUES ($1,$2,$3,$4,$5,$6,$7,$8,true)`,
          [
            row.metric_date, row.page, row.country, row.device,
            row.clicks, row.impressions, row.ctr, row.position,
          ]
        );
      }
      inserted++;
    }

    if (!dryRun) await finishRun(runId, inserted, null);
    return { inserted, skipped: 0, rejected: [], runId, dryRun };
  } catch (err) {
    if (!dryRun && runId) await finishRun(runId, inserted, err.message);
    throw err;
  }
}

/**
 * Import all GSC data (queries + pages).
 *
 * TODO real API: replace fixture branches with googleapis searchconsole calls:
 *
 *   const body = {
 *     startDate: '2026-05-08',
 *     endDate: '2026-08-06',
 *     dimensions: ['query', 'country', 'device', 'date'],
 *     rowLimit: 25000,
 *   };
 *   const res = await sc.searchanalytics.query({ siteUrl: process.env.GSC_SITE_URL, requestBody: body });
 *   // paginate via startRow until res.data.rows.length < rowLimit
 *
 * @param {{ dryRun?: boolean }} options
 * @returns {Promise<{ queries: object, pages: object, demo: true }>}
 */
async function importAll({ dryRun = false } = {}) {
  if (hasCredentials()) {
    throw new Error('GSC live API not enabled (no service account) — implement the TODO real API branch.');
  }

  const [queries, pages] = await Promise.all([
    importQueries(dryRun),
    importPages(dryRun),
  ]);

  return { queries, pages, demo: true };
}

module.exports = {
  connectionTest,
  importAll,
  importQueries,
  importPages,
  classifyBrandVsNonbrand,
  clusterQuery,
  hasCredentials,
};