4ventures-client-portal/scripts/refresh_agency_connections.ts

/**
 * One-shot: bootstrap Google + discover Meta → fichas, then print audit.
 * Usage: npx tsx --env-file=.env.local scripts/refresh_agency_connections.ts
 */
import { bootstrapAgencyGoogle } from '../src/lib/agency-google-bootstrap';
import { discoverAndMatch } from '../src/lib/ads-discovery';
import { serviceHeaders, supabaseUrl } from '../src/lib/agency';

async function get(path: string) {
  const headers = serviceHeaders();
  if (!headers) throw new Error('no service role');
  const r = await fetch(`${supabaseUrl()}${path}`, {
    headers,
    cache: 'no-store',
  });
  if (!r.ok) throw new Error(`${path}: ${await r.text()}`);
  return r.json();
}

async function organicCount(tenantId: string): Promise<number> {
  const headers = serviceHeaders();
  if (!headers) return 0;
  const r = await fetch(
    `${supabaseUrl()}/rest/v1/organic_daily?tenant_id=eq.${tenantId}&select=day`,
    {
      headers: { ...headers, Prefer: 'count=exact', Range: '0-0' },
      cache: 'no-store',
    }
  );
  const cr = r.headers.get('content-range') || '';
  if (cr.includes('/')) return Number(cr.split('/').pop()) || 0;
  return 0;
}

async function main() {
  console.log('=== 1. Google bootstrap (Drive + GA4 + GSC + Ads + sync) ===');
  const boot = await bootstrapAgencyGoogle({
    includeGoogleAds: true,
    syncOrganic: true,
    organicDays: 28,
  });
  console.log(JSON.stringify(boot, null, 2));

  console.log('\n=== 2. Meta discover (Ads + FB + IG) ===');
  const meta = await discoverAndMatch({
    apply: true,
    providers: ['meta_ads', 'google_ads'],
  });
  console.log(
    JSON.stringify(
      {
        meta: {
          accounts: meta.meta?.accounts,
          linked: meta.meta?.linked,
          pagesLinked: meta.meta?.pagesLinked,
          igLinked: meta.meta?.igLinked,
          error: meta.meta?.error,
        },
        google: {
          accounts: meta.google?.accounts,
          linked: meta.google?.linked,
          error: meta.google?.error,
        },
      },
      null,
      2
    )
  );

  console.log('\n=== 3. Audit por cliente ===');
  const tenants = (await get(
    '/rest/v1/tenants?select=id,name,slug&order=name.asc'
  )) as { id: string; name: string; slug: string }[];
  const integs = (await get(
    '/rest/v1/integrations?provider=in.(ga4,search_console,google_drive,google_ads,meta_ads,facebook,instagram)&select=tenant_id,provider,status,external_account_id,error_message,meta'
  )) as {
    tenant_id: string;
    provider: string;
    status: string;
    external_account_id?: string | null;
    error_message?: string | null;
    meta?: Record<string, unknown> | null;
  }[];

  const by = new Map<string, typeof integs>();
  for (const i of integs) {
    const arr = by.get(i.tenant_id) || [];
    arr.push(i);
    by.set(i.tenant_id, arr);
  }

  const providers = [
    'google_ads',
    'meta_ads',
    'ga4',
    'search_console',
    'google_drive',
    'facebook',
    'instagram',
  ] as const;

  type Row = {
    name: string;
    organic_days: number;
    channels: Record<string, string>;
    gaps: string[];
  };
  const rows: Row[] = [];

  for (const t of tenants) {
    const map = new Map(
      (by.get(t.id) || []).map((i) => [i.provider, i] as const)
    );
    const channels: Record<string, string> = {};
    const gaps: string[] = [];
    for (const p of providers) {
      const i = map.get(p);
      if (i?.status === 'connected' && i.external_account_id) {
        const id = String(i.external_account_id);
        channels[p] = id.length > 42 ? id.slice(0, 40) + '…' : id;
      } else if (i?.status === 'error') {
        channels[p] = `ERR:${(i.error_message || '').slice(0, 40)}`;
        gaps.push(p);
      } else {
        channels[p] = '—';
        gaps.push(p);
      }
    }
    const org = await organicCount(t.id);
    rows.push({ name: t.name, organic_days: org, channels, gaps });
  }

  console.log(
    [
      'Cliente'.padEnd(28),
      'GAds',
      'Meta',
      'GA4',
      'GSC',
      'Drive',
      'FB',
      'IG',
      'OrgDías',
    ].join('\t')
  );
  for (const r of rows) {
    const c = r.channels;
    const flag = (v: string) => (v === '—' ? '—' : v.startsWith('ERR') ? 'ERR' : 'OK');
    console.log(
      [
        r.name.slice(0, 28).padEnd(28),
        flag(c.google_ads),
        flag(c.meta_ads),
        flag(c.ga4),
        flag(c.search_console),
        flag(c.google_drive),
        flag(c.facebook),
        flag(c.instagram),
        String(r.organic_days),
      ].join('\t')
    );
  }

  console.log('\n=== Detalle IDs (solo conectados) ===');
  for (const r of rows) {
    const linked = Object.entries(r.channels).filter(([, v]) => v !== '—' && !v.startsWith('ERR'));
    if (!linked.length && r.organic_days === 0) continue;
    console.log(`\n${r.name} · organic_daily=${r.organic_days}`);
    for (const [p, v] of linked) console.log(`  ${p}: ${v}`);
  }

  const withAds = rows.filter(
    (r) => r.channels.google_ads !== '—' || r.channels.meta_ads !== '—'
  ).length;
  const withOrg = rows.filter(
    (r) =>
      r.channels.ga4 !== '—' ||
      r.channels.search_console !== '—' ||
      r.organic_days > 0
  ).length;
  const withSocial = rows.filter(
    (r) => r.channels.facebook !== '—' || r.channels.instagram !== '—'
  ).length;
  console.log('\n=== RESUMEN ===');
  console.log(
    JSON.stringify(
      {
        tenants: rows.length,
        with_ads: withAds,
        with_organic_link_or_data: withOrg,
        with_fb_or_ig: withSocial,
        organic_tenants_with_rows: rows.filter((r) => r.organic_days > 0).length,
      },
      null,
      2
    )
  );
}

main().catch((e) => {
  console.error(e);
  process.exit(1);
});