create table if not exists public.campaign_assets (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  created_by_auth_user_id uuid references auth.users(id) on delete set null,
  updated_by_auth_user_id uuid references auth.users(id) on delete set null,
  storage_path text not null unique,
  file_name text not null,
  file_size bigint,
  mime_type text,
  alt_text text,
  uploaded_via text not null default 'builder',
  last_used_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists campaign_assets_org_created_idx
  on public.campaign_assets (organization_id, created_at desc);

create index if not exists campaign_assets_org_last_used_idx
  on public.campaign_assets (organization_id, last_used_at desc nulls last);

alter table public.campaign_assets enable row level security;