-- =======================================================================
-- Adsterra ad configuration (admin-controlled)
-- =======================================================================
-- Single-row settings table that lets the admin toggle ads on/off, pick a
-- placement and frequency (how many completed chats between ad shows),
-- and paste the raw Adsterra <script> snippet they generated in their
-- Adsterra dashboard. The table is a singleton (fixed id = 'main') so
-- we never have to deal with "which settings row is active?" — the
-- admin UI simply upserts by id.
-- =======================================================================

create table if not exists public.ad_settings (
  id               text primary key default 'main',
  enabled          boolean not null default false,
  -- how many completed 1:1 chats between each ad impression
  frequency        integer not null default 3
    check (frequency >= 1 and frequency <= 50),
  -- which kind of Adsterra unit this script renders
  placement        text not null default 'interstitial'
    check (placement in ('interstitial','banner_top','banner_bottom','popunder','native')),
  -- raw HTML/<script> snippet from Adsterra. NULL = no ad configured yet.
  script           text,
  -- optional label for the admin, e.g. "300x250 banner"
  label            text,
  updated_at       timestamptz not null default now()
);

-- Keep `updated_at` fresh on every change.
create or replace function public.touch_ad_settings_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at := now();
  return new;
end;
$$;

drop trigger if exists trg_ad_settings_updated_at on public.ad_settings;
create trigger trg_ad_settings_updated_at
before update on public.ad_settings
for each row execute procedure public.touch_ad_settings_updated_at();

-- Seed the singleton row so the admin UI has something to load on first
-- visit (ads are disabled by default — admin must explicitly enable).
insert into public.ad_settings (id, enabled, frequency, placement, script, label)
values ('main', false, 3, 'interstitial', null, null)
on conflict (id) do nothing;

-- RLS: the public API reads *only* the (enabled, frequency, placement,
-- script) columns via a server route that uses the service role key,
-- so we keep RLS disabled here — nobody reads this table directly from
-- the browser. (Service role bypasses RLS anyway; we just document the
-- intent.)
alter table public.ad_settings disable row level security;
