-- Create user_settings table (if missing) and backfill rows

create table if not exists user_settings (
    user_id uuid primary key references profiles(id) on delete cascade,
    default_post_ttl interval,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create index if not exists idx_user_settings_user_id on user_settings(user_id);

alter table user_settings enable row level security;

drop policy if exists user_settings_select on user_settings;
create policy user_settings_select on user_settings
  for select using (auth.uid() = user_id);

drop policy if exists user_settings_insert on user_settings;
create policy user_settings_insert on user_settings
  for insert with check (auth.uid() = user_id);

drop policy if exists user_settings_update on user_settings;
create policy user_settings_update on user_settings
  for update using (auth.uid() = user_id);

drop policy if exists user_settings_delete on user_settings;
create policy user_settings_delete on user_settings
  for delete using (auth.uid() = user_id);

-- Backfill missing settings rows for existing users
insert into user_settings (user_id)
select p.id
from profiles p
where not exists (
  select 1 from user_settings us where us.user_id = p.id
);

-- Ensure new users get settings row
create or replace function handle_new_user()
returns trigger
language plpgsql
security definer
as $$
begin
  insert into public.profiles (id, handle, display_name)
  values (
    new.id,
    coalesce(new.raw_user_meta_data->>'handle', new.email),
    coalesce(new.raw_user_meta_data->>'display_name', new.email)
  );

  insert into public.trust_state (user_id, harmony_score, tier, posts_today)
  values (new.id, 50, 'new', 0);

  insert into public.user_settings (user_id)
  values (new.id)
  on conflict (user_id) do nothing;

  return new;
end;
$$;