supabase_notifications_recreate.sql

-- Drop existing table and policies
drop table if exists public.notifications cascade;

-- Create notifications table with ALL columns
create table public.notifications (
    id bigint generated by default as identity primary key,
    user_id uuid references auth.users(id) on delete cascade not null,
    sender_id uuid references auth.users(id) on delete set null,
    type text check (type in ('like', 'comment', 'save', 'schedule', 'post', 'badge', 'system', 'friend')) not null,
    message text not null,
    is_read boolean default false,
    link text, -- Explicitly adding link column
    created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Enable RLS
alter table public.notifications enable row level security;

-- Re-create policies
create policy "Users can view their own notifications"
    on public.notifications for select
    using (auth.uid() = user_id);

create policy "Users can update their own notifications"
    on public.notifications for update
    using (auth.uid() = user_id);

create policy "Users can delete their own notifications"
    on public.notifications for delete
    using (auth.uid() = user_id);

create policy "Anyone can insert notifications"
    on public.notifications for insert
    with check (auth.role() = 'authenticated');

-- Reload schema cache
notify pgrst, 'reload config';