Schema Reference

create table public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  email text,
  role text check (role in ('admin', 'client')) default 'client',
  client_id uuid references public.clients (id) on delete set null,
  full_name text,
  avatar_url text,
  created_at timestamptz default now()
);

create table public.clients (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  url text,
  screenshot_url text,

  -- Google integrations (agency-level OAuth, per-client property IDs)
  ga4_property_id text,
  gsc_site_url text,
  gbp_location_id text,
  gbp_place_id text,
  youtube_channel_id text,
  google_ads_customer_id text,

  -- Other integrations
  ghl_location_id text,
  meta_ad_account_id text,
  dataforseo_domain text,
  sheets_rfms_id text,

  -- Local SEO config
  local_seo_keywords jsonb,    -- { local_pack: { keywords, embed_url }, maps: { keywords, embed_url } }

  -- Phase 1 — branding & program identity
  tier_label text,             -- e.g. 'Highway — Overdrive'
  tier_color text,             -- hex for the badge override
  booking_url text,            -- per-client Calendly / GHL calendar
  welcome_video_url text,      -- Loom / Riverside link
  client_logo_url text,        -- white-label logo shown in HubNav + Client Info header

  -- Phase 1.1 — Client Info hub (shown in the bento grid)
  info_legal_name text,
  info_industry text,
  info_founded_year int,
  info_employee_count text,
  info_about text,

  info_primary_contact_name text,   -- drives the Home greeting first name
  info_primary_contact_role text,
  info_primary_contact_email text,
  info_primary_contact_phone text,

  info_address text,
  info_city text,
  info_state text,
  info_zip text,
  info_country text,

  info_domain text,
  info_domain_registrar text,
  info_domain_expiration date,
  info_hosting_provider text,
  info_ssl_expiration date,

  info_brand_colors text,           -- comma-separated hex strings
  info_brand_fonts text,

  info_social_facebook text,
  info_social_instagram text,
  info_social_linkedin text,
  info_social_youtube text,
  info_social_tiktok text,

  info_billing_email text,
  info_tax_id text,
  info_notes text,

  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

create table public.integrations (
  id uuid primary key default gen_random_uuid(),
  client_id uuid references public.clients (id) on delete cascade,
  provider text not null,    -- 'google_master_agency' | 'meta_ads' | 'ghl' | etc.
  is_active boolean default true,
  access_token text,
  refresh_token text,
  expires_at timestamptz,
  scope text,
  metadata jsonb,            -- provider-specific extras
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

create table public.portal_data (
  id uuid primary key default gen_random_uuid(),
  client_id uuid unique references public.clients (id) on delete cascade,
  snapshot jsonb,
  scorecard jsonb,
  gameplan jsonb,
  deliverables jsonb,
  kpi_reporting jsonb,
  content_tracker jsonb,
  documents jsonb,
  updated_at timestamptz default now()
);

create table public.external_metrics (
  id uuid primary key default gen_random_uuid(),
  client_id uuid references public.clients (id) on delete cascade,
  key text not null,
  value numeric,
  formatted text,
  change numeric,
  updated_at timestamptz default now(),
  unique (client_id, key)
);

create table public.seo_rankings (
  id uuid primary key default gen_random_uuid(),
  client_id uuid references public.clients (id) on delete cascade,
  domain text,
  week_date date,
  summary jsonb,        -- { totalKeywords, top3, top5, top10, top20, notFound }
  backlinks jsonb,      -- { totalBacklinks, referringDomains, referringIPs, domainRank }
  rankings jsonb,       -- [{ keyword, rank, url, found }, ...]
  unique (client_id, week_date)
);

-- read: admin or matching client_id
create policy "read own or admin" on public.clients for select using (
  exists (select 1 from profiles
          where profiles.id = auth.uid()
            and (profiles.role = 'admin' or profiles.client_id = clients.id))
);

-- write: admin only (clients are read-only)
create policy "admin write" on public.clients for all using (
  exists (select 1 from profiles
          where profiles.id = auth.uid() and profiles.role = 'admin')
);