Google Search Console

Tenant clicks "Connect Google Search Console" in Dashboard → Settings → Integrations
        │
        ▼
Platform redirects to Google OAuth consent screen
  scope: https://www.googleapis.com/auth/webmasters.readonly
        │
        ▼
Google redirects back with authorization code
        │
        ▼
Platform exchanges code for access_token + refresh_token
Stores in integrations table:
  provider:      'google_search_console'
  api_key:       encrypt(refresh_token)
  metadata:      { siteUrl, propertyType, accessToken, accessTokenExpiresAt }

interface GSCIntegrationMetadata {
  siteUrl:              string;   // e.g. "https://acmeplumbing.com/" or "sc-domain:acmeplumbing.com"
  propertyType:         'url'  | 'domain';
  accessToken:          string;   // Short-lived (1hr) — refreshed automatically
  accessTokenExpiresAt: string;   // ISO 8601
}

GOOGLE_CLIENT_ID=xxxxxxxx.apps.googleusercontent.com
GOOGLE_CLIENT_SECRET=GOCSPX-xxxxxxxxxxxxxxxxxxxx
GOOGLE_REDIRECT_URI=https://app.leadmetrics.io/api/auth/callback/google

async function getGSCClient(integration: Integration): Promise<google.searchconsole_v1.Searchconsole> {
  const meta = integration.metadata as GSCIntegrationMetadata;
 
  if (isExpired(meta.accessTokenExpiresAt)) {
    const refreshToken = decrypt(integration.api_key);
    const { credentials } = await oauth2Client.refreshAccessToken(refreshToken);
    // Update stored access token
    await db.update(integrations)
      .set({ metadata: { ...meta, accessToken: credentials.access_token, accessTokenExpiresAt: ... } })
      .where(eq(integrations.id, integration.id));
    oauth2Client.setCredentials(credentials);
  } else {
    oauth2Client.setCredentials({ access_token: meta.accessToken });
  }
 
  return google.searchconsole({ version: 'v1', auth: oauth2Client });
}

class GSCTool {
  async getPerformance(
    integration: Integration,
    options: {
      startDate:  string;   // YYYY-MM-DD
      endDate:    string;   // YYYY-MM-DD
      dimensions: ('query' | 'page' | 'device' | 'country')[];
      rowLimit?:  number;   // Max 25000
      filters?:   {
        dimension: 'query' | 'page' | 'device' | 'country';
        operator:  'equals' | 'contains' | 'notContains' | 'includingRegex' | 'excludingRegex';
        expression: string;
      }[];
    },
  ): Promise<GSCPerformanceRow[]> {
    const client = await getGSCClient(integration);
    const meta = integration.metadata as GSCIntegrationMetadata;
 
    const response = await client.searchanalytics.query({
      siteUrl: meta.siteUrl,
      requestBody: {
        startDate:  options.startDate,
        endDate:    options.endDate,
        dimensions: options.dimensions,
        rowLimit:   options.rowLimit ?? 1000,
        dataState:  'final',   // 'all' includes partial data for recent days
        ...(options.filters?.length && {
          dimensionFilterGroups: [{
            filters: options.filters.map(f => ({
              dimension:  f.dimension,
              operator:   f.operator,
              expression: f.expression,
            })),
          }],
        }),
      },
    });
 
    return (response.data.rows ?? []).map(row => ({
      keys:         row.keys ?? [],
      clicks:       row.clicks ?? 0,
      impressions:  row.impressions ?? 0,
      ctr:          row.ctr ?? 0,
      position:     row.position ?? 0,
    }));
  }
 
  /**
   * Top keywords (queries) by clicks for a date range.
   * Used by Report Writer for the organic keywords section.
   */
  async getTopKeywords(
    integration: Integration,
    options: { startDate: string; endDate: string; limit?: number },
  ): Promise<{ keyword: string; clicks: number; impressions: number; ctr: number; position: number }[]> {
    const rows = await this.getPerformance(integration, {
      startDate:  options.startDate,
      endDate:    options.endDate,
      dimensions: ['query'],
      rowLimit:   options.limit ?? 25,
    });
    return rows.map(r => ({
      keyword:     r.keys[0] ?? '',
      clicks:      r.clicks,
      impressions: r.impressions,
      ctr:         r.ctr,
      position:    r.position,
    }));
  }
 
  /**
   * Top pages by clicks for a date range.
   * Used by Report Writer for the content performance section.
   */
  async getTopPages(
    integration: Integration,
    options: { startDate: string; endDate: string; limit?: number },
  ): Promise<{ page: string; clicks: number; impressions: number; position: number }[]> {
    const rows = await this.getPerformance(integration, {
      startDate:  options.startDate,
      endDate:    options.endDate,
      dimensions: ['page'],
      rowLimit:   options.limit ?? 25,
    });
    return rows.map(r => ({
      page:        r.keys[0] ?? '',
      clicks:      r.clicks,
      impressions: r.impressions,
      position:    r.position,
    }));
  }
 
  /**
   * Weekly click trend for a specific keyword over the past N weeks.
   * Used by Keyword Researcher and Site Auditor for trend analysis.
   */
  async getKeywordTrend(
    integration: Integration,
    options: { keyword: string; weeks?: number },
  ): Promise<{ weekStart: string; clicks: number; impressions: number; position: number }[]> {
    const weeks     = options.weeks ?? 12;
    const endDate   = subtractDays(new Date(), 3);   // GSC 3-day lag
    const startDate = subtractDays(endDate, weeks * 7);
 
    const rows = await this.getPerformance(integration, {
      startDate:  toISODate(startDate),
      endDate:    toISODate(endDate),
      dimensions: ['date', 'query'],
      rowLimit:   25000,
      filters: [{
        dimension:  'query',
        operator:   'equals',
        expression: options.keyword,
      }],
    });
 
    // Aggregate daily rows into weekly buckets
    const weeks_map = new Map<string, { clicks: number; impressions: number; positions: number[]; }>();
    for (const row of rows) {
      const date      = row.keys[0];   // 'YYYY-MM-DD'
      const weekStart = getWeekStart(date);
      const bucket    = weeks_map.get(weekStart) ?? { clicks: 0, impressions: 0, positions: [] };
      bucket.clicks      += row.clicks;
      bucket.impressions += row.impressions;
      bucket.positions.push(row.position);
      weeks_map.set(weekStart, bucket);
    }
 
    return [...weeks_map.entries()]
      .sort(([a], [b]) => a.localeCompare(b))
      .map(([weekStart, b]) => ({
        weekStart,
        clicks:      b.clicks,
        impressions: b.impressions,
        position:    b.positions.length
          ? b.positions.reduce((s, p) => s + p, 0) / b.positions.length
          : 0,
      }));
  }
 
  /**
   * Inspect the indexing status of a specific URL.
   * Used by Site Auditor to check whether key pages are indexed.
   */
  async getUrlInspection(
    integration: Integration,
    url: string,
  ): Promise<{
    indexingState:      'INDEXED' | 'NOT_INDEXED' | 'EXCLUDED' | 'UNKNOWN';
    coverageState?:     string;   // Human-readable GSC coverage state
    lastCrawlTime?:     string;   // ISO 8601
    robotsTxtState?:    'ALLOWED' | 'DISALLOWED';
    indexingAllowed?:   boolean;
    googleCanonical?:   string;
    verdict?:           string;   // e.g. 'PASS', 'FAIL'
  }> {
    const client = await getGSCClient(integration);
    const meta   = integration.metadata as GSCIntegrationMetadata;
 
    const response = await client.urlInspection.index.inspect({
      requestBody: {
        inspectionUrl: url,
        siteUrl:       meta.siteUrl,
      },
    });
 
    const result = response.data.inspectionResult;
    const idx    = result?.indexStatusResult;
 
    return {
      indexingState:    (idx?.verdict as any) === 'PASS' ? 'INDEXED' : 'NOT_INDEXED',
      coverageState:    idx?.coverageState ?? undefined,
      lastCrawlTime:    idx?.lastCrawlTime ?? undefined,
      robotsTxtState:   idx?.robotsTxtState as any ?? undefined,
      indexingAllowed:  idx?.indexingState === 'INDEXING_ALLOWED',
      googleCanonical:  idx?.googleCanonicalUrl ?? undefined,
      verdict:          idx?.verdict ?? undefined,
    };
  }
 
  /**
   * List all sitemaps submitted for the property.
   * Used by Site Auditor to validate sitemap submission.
   */
  async getSitemaps(
    integration: Integration,
  ): Promise<{
    path:              string;
    type:              string;   // e.g. 'sitemap', 'atomFeed'
    lastSubmitted?:    string;   // ISO 8601
    lastDownloaded?:   string;   // ISO 8601
    errors:            number;
    warnings:          number;
    urlCount?:         number;
  }[]> {
    const client = await getGSCClient(integration);
    const meta   = integration.metadata as GSCIntegrationMetadata;
 
    const response = await client.sitemaps.list({ siteUrl: meta.siteUrl });
 
    return (response.data.sitemap ?? []).map(s => ({
      path:            s.path ?? '',
      type:            s.type ?? 'sitemap',
      lastSubmitted:   s.lastSubmitted ?? undefined,
      lastDownloaded:  s.lastDownloaded ?? undefined,
      errors:          Number(s.errors  ?? 0),
      warnings:        Number(s.warnings ?? 0),
      urlCount:        s.contents?.[0]?.submitted ? Number(s.contents[0].submitted) : undefined,
    }));
  }
 
  /**
   * Verify that the integration is valid and has at least one accessible property.
   */
  async verify(integration: Integration): Promise<void> {
    const properties = await this.listProperties(integration);
    if (properties.length === 0) {
      throw new Error('No verified Search Console properties found for this account');
    }
  }
 
  async listProperties(integration: Integration): Promise<string[]> {
    const client = await getGSCClient(integration);
    const response = await client.sites.list();
    return (response.data.siteEntry ?? [])
      .filter(s => s.permissionLevel === 'siteOwner' || s.permissionLevel === 'siteFullUser')
      .map(s => s.siteUrl!)
      .filter(Boolean);
  }
}