ZeroTrustFileShare

npm install @supabase/supabase-js

npm install @hcaptcha/react-hcaptcha

create table if not exists roles (
  name text primary key
);

insert into roles (name)
values ('admin'), ('faculty'), ('student')
on conflict do nothing;

create table if not exists profiles (
  id uuid references auth.users(id) on delete cascade,
  role text not null default 'student'
    references roles(name),
  primary key (id)
);

create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id, role)
  values (new.id, 'student');
  return new;
end;
$$ language plpgsql security definer;

drop trigger if exists on_auth_user_created on auth.users;

create trigger on_auth_user_created
after insert on auth.users
for each row execute procedure public.handle_new_user();

select * from profiles;

update profiles
set role = 'admin'
where id = 'USER_UUID_HERE';

update profiles
set role = 'faculty'
where id = 'USER_UUID_HERE';

create table resources (
  id uuid default gen_random_uuid() primary key,
  title text not null,
  file_url text not null,
  sensitivity text check (sensitivity in ('low','medium','high')),
  visibility text check (visibility in ('student','faculty','admin')),
  uploaded_by uuid references auth.users(id),
  created_at timestamp with time zone default now()
);

alter table resources enable row level security;

create policy "faculty can upload resources"
on resources
for insert
with check (
  auth.uid() = uploaded_by
  and exists (
    select 1 from profiles
    where profiles.id = auth.uid()
    and profiles.role = 'faculty'
  )
);

create policy "role based read"
on resources
for select
using (
  visibility = 'student'
  or (
    visibility = 'faculty'
    and exists (
      select 1 from profiles
      where profiles.id = auth.uid()
      and profiles.role in ('faculty','admin')
    )
  )
  or (
    visibility = 'admin'
    and exists (
      select 1 from profiles
      where profiles.id = auth.uid()
      and profiles.role = 'admin'
    )
  )
);

create policy "faculty upload pdfs"
on storage.objects
for insert
with check (
  bucket_id = 'pdfs'
  and exists (
    select 1 from profiles
    where profiles.id = auth.uid()
    and profiles.role = 'faculty'
  )
);

create table audit_logs (
  id bigint generated by default as identity primary key,
  action text not null,
  actor text,
  details text,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Enable Realtime for this table (Critical for the live dashboard effect)
alter publication supabase_realtime add table audit_logs;