-- JMGC StudyMate PH — Supabase schema
-- Run this entire script in Supabase Dashboard > SQL Editor.
create extension if not exists pgcrypto;

create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  full_name text not null default '',
  email text not null default '',
  photo_url text,
  role text not null default 'student' check (role in ('student','admin')),
  school_level text not null default '',
  grade_level text not null default '',
  school_name text not null default '',
  course text not null default '',
  preferred_language text not null default 'en',
  theme text not null default 'light' check (theme in ('light','dark')),
  account_status text not null default 'active',
  onboarding_completed boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.student_files (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null references auth.users(id) on delete cascade,
  name text not null,
  mime_type text not null,
  size_bytes bigint not null check (size_bytes >= 0 and size_bytes <= 5242880),
  storage_path text not null unique,
  created_at timestamptz not null default now()
);
create index if not exists student_files_owner_created_idx
  on public.student_files(owner_id, created_at desc);

alter table public.profiles enable row level security;
alter table public.student_files enable row level security;

-- A student can only read and update their own profile. Role cannot be changed by a client.
drop policy if exists "profiles_select_own" on public.profiles;
create policy "profiles_select_own" on public.profiles for select to authenticated using (id = auth.uid());
drop policy if exists "profiles_insert_own" on public.profiles;
create policy "profiles_insert_own" on public.profiles for insert to authenticated with check (id = auth.uid() and role = 'student');
drop policy if exists "profiles_update_own" on public.profiles;
create policy "profiles_update_own" on public.profiles for update to authenticated using (id = auth.uid()) with check (id = auth.uid() and role = 'student');

-- File metadata is scoped to the authenticated owner.
drop policy if exists "student_files_select_own" on public.student_files;
create policy "student_files_select_own" on public.student_files for select to authenticated using (owner_id = auth.uid());
drop policy if exists "student_files_insert_own" on public.student_files;
create policy "student_files_insert_own" on public.student_files for insert to authenticated with check (owner_id = auth.uid() and split_part(storage_path, '/', 1) = auth.uid()::text);
drop policy if exists "student_files_delete_own" on public.student_files;
create policy "student_files_delete_own" on public.student_files for delete to authenticated using (owner_id = auth.uid());

-- Create private bucket. The browser uses the anon/publishable key; RLS controls access.
insert into storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
values ('student-files', 'student-files', false, 5242880,
  array['application/pdf','application/msword','application/vnd.openxmlformats-officedocument.wordprocessingml.document',
  'application/vnd.ms-powerpoint','application/vnd.openxmlformats-officedocument.presentationml.presentation',
  'application/vnd.ms-excel','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  'text/plain','image/jpeg','image/png','image/webp'])
on conflict (id) do update set public = false, file_size_limit = 5242880, allowed_mime_types = excluded.allowed_mime_types;

drop policy if exists "student_files_storage_read_own" on storage.objects;
create policy "student_files_storage_read_own" on storage.objects for select to authenticated
using (bucket_id = 'student-files' and (storage.foldername(name))[1] = auth.uid()::text);
drop policy if exists "student_files_storage_insert_own" on storage.objects;
create policy "student_files_storage_insert_own" on storage.objects for insert to authenticated
with check (bucket_id = 'student-files' and (storage.foldername(name))[1] = auth.uid()::text);
drop policy if exists "student_files_storage_delete_own" on storage.objects;
create policy "student_files_storage_delete_own" on storage.objects for delete to authenticated
using (bucket_id = 'student-files' and (storage.foldername(name))[1] = auth.uid()::text);

-- Keep updated_at current.
create or replace function public.set_updated_at()
returns trigger language plpgsql as $$ begin new.updated_at = now(); return new; end; $$;
drop trigger if exists profiles_set_updated_at on public.profiles;
create trigger profiles_set_updated_at before update on public.profiles
for each row execute function public.set_updated_at();

-- Automatically create a student profile after Auth signup.
create or replace function public.handle_new_user()
returns trigger language plpgsql security definer set search_path = '' as $$
begin
  insert into public.profiles (id, full_name, email, photo_url)
  values (new.id, coalesce(new.raw_user_meta_data ->> 'full_name', new.raw_user_meta_data ->> 'name', ''), coalesce(new.email, ''), new.raw_user_meta_data ->> 'avatar_url')
  on conflict (id) do nothing;
  return new;
end;
$$;
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();
