supabase/schema.sql

create extension if not exists pgcrypto;

create table if not exists public.distillation_requests (
    id uuid primary key default gen_random_uuid (),
    source_dataset text not null,
    student_model text not null,
    submitter_name text null,
    additional_notes text not null default '',
    upvotes integer not null default 0,
    voted_ips text [] not null default '{}',
    owner_id text not null,
    created_at timestamptz not null default now(),
    status text not null default 'pending'
);

create table if not exists public.dataset_requests (
    id uuid primary key default gen_random_uuid (),
    source_model text not null,
    submitter_name text null,
    dataset_size text not null default '250x',
    reasoning_depth text not null default 'high',
    topics text [] not null default '{}',
    additional_notes text not null default '',
    upvotes integer not null default 0,
    voted_ips text [] not null default '{}',
    owner_id text not null,
    created_at timestamptz not null default now(),
    status text not null default 'pending'
);

alter table public.distillation_requests
add column if not exists submitter_name text;

alter table public.dataset_requests
add column if not exists submitter_name text;

create table if not exists public.request_comments (
    id uuid primary key default gen_random_uuid (),
    request_type text not null check (
        request_type in ('distillation', 'dataset')
    ),
    request_id uuid not null,
    body text not null,
    author text not null,
    role text not null check (role in ('admin', 'user')),
    owner_id text not null,
    created_at timestamptz not null default now(),
    edited_at timestamptz null
);

create index if not exists idx_request_comments_request on public.request_comments (request_type, request_id);

create index if not exists idx_request_comments_created_at on public.request_comments (created_at);

create or replace function public.toggle_upvote_distillation(request_id uuid, voter_ip text)
returns table(success boolean, upvotes integer, action text)
language plpgsql
security definer
as $$
declare
    current_ips text[];
    current_upvotes integer;
begin
    select r.voted_ips, r.upvotes
    into current_ips, current_upvotes
    from public.distillation_requests r
    where r.id = request_id
    for update;

    if not found then
        success := false;
        upvotes := 0;
        action := null;
        return next;
        return;
    end if;

    if voter_ip = any(current_ips) then
        update public.distillation_requests as r
        set
            voted_ips = array_remove(r.voted_ips, voter_ip),
            upvotes = greatest(r.upvotes - 1, 0)
        where r.id = request_id
        returning r.upvotes into upvotes;

        success := true;
        action := 'unvoted';
        return next;
        return;
    end if;

    update public.distillation_requests as r
    set
        voted_ips = array_append(r.voted_ips, voter_ip),
        upvotes = r.upvotes + 1
    where r.id = request_id
    returning r.upvotes into upvotes;

    success := true;
    action := 'upvoted';
    return next;
end;
$$;

create or replace function public.toggle_upvote_dataset(request_id uuid, voter_ip text)
returns table(success boolean, upvotes integer, action text)
language plpgsql
security definer
as $$
declare
    current_ips text[];
    current_upvotes integer;
begin
    select r.voted_ips, r.upvotes
    into current_ips, current_upvotes
    from public.dataset_requests r
    where r.id = request_id
    for update;

    if not found then
        success := false;
        upvotes := 0;
        action := null;
        return next;
        return;
    end if;

    if voter_ip = any(current_ips) then
        update public.dataset_requests as r
        set
            voted_ips = array_remove(r.voted_ips, voter_ip),
            upvotes = greatest(r.upvotes - 1, 0)
        where r.id = request_id
        returning r.upvotes into upvotes;

        success := true;
        action := 'unvoted';
        return next;
        return;
    end if;

    update public.dataset_requests as r
    set
        voted_ips = array_append(r.voted_ips, voter_ip),
        upvotes = r.upvotes + 1
    where r.id = request_id
    returning r.upvotes into upvotes;

    success := true;
    action := 'upvoted';
    return next;
end;
$$;

alter table public.distillation_requests enable row level security;

alter table public.dataset_requests enable row level security;

alter table public.request_comments enable row level security;

drop policy if exists public_select on public.distillation_requests;

drop policy if exists public_insert on public.distillation_requests;

drop policy if exists public_update on public.distillation_requests;

drop policy if exists public_delete on public.distillation_requests;

drop policy if exists public_select on public.dataset_requests;

drop policy if exists public_insert on public.dataset_requests;

drop policy if exists public_update on public.dataset_requests;

drop policy if exists public_delete on public.dataset_requests;

drop policy if exists public_select on public.request_comments;

drop policy if exists public_insert on public.request_comments;

drop policy if exists public_update on public.request_comments;

drop policy if exists public_delete on public.request_comments;

create policy public_select on public.distillation_requests for
select using (true);

create policy public_insert on public.distillation_requests for insert
with
    check (true);

create policy public_select on public.dataset_requests for
select using (true);

create policy public_insert on public.dataset_requests for insert
with
    check (true);

create policy public_select on public.request_comments for
select using (true);

create policy public_insert on public.request_comments for insert
with
    check (role = 'user');

grant
select, insert on table public.distillation_requests to anon, authenticated;

grant
select, insert on table public.dataset_requests to anon, authenticated;

grant
select, insert on table public.request_comments to anon, authenticated;

grant
execute on function public.toggle_upvote_distillation (uuid, text) to anon,
authenticated;

grant
execute on function public.toggle_upvote_dataset (uuid, text) to anon,
authenticated;