-- SEMESTER TRACKER — SQL d'initialisation -- Colle tout dans le SQL Editor de Supabase create table if not exists public.ues ( id text primary key, user_id uuid not null references auth.users(id) on delete cascade, name text not null, color text default '#3b82f6', description text default '', semester int default 1, created_at timestamptz default now() ); create table if not exists public.courses ( id text primary key, user_id uuid not null references auth.users(id) on delete cascade, ue_id text not null, num text default '', title text not null, type text default 'CM', status text default 'todo', created_at timestamptz default now() ); create table if not exists public.schedule_events ( id text primary key, user_id uuid not null references auth.users(id) on delete cascade, title text not null, date text not null, start_h int not null default 8, start_m int not null default 0, end_h int not null default 10, end_m int not null default 0, color text default '#3b82f6' ); create table if not exists public.notes ( id text primary key, user_id uuid not null references auth.users(id) on delete cascade, title text default 'Nouvelle note', body text default '', updated_at bigint default extract(epoch from now())::bigint * 1000 ); create table if not exists public.settings ( user_id uuid primary key references auth.users(id) on delete cascade, exam_date text default '', deadline_weeks int default 2 ); create table if not exists public.course_files ( id text primary key, user_id uuid not null references auth.users(id) on delete cascade, course_id text not null, file_type text not null, file_name text default '', file_size bigint default 0, uploaded_at timestamptz default now(), unique(user_id, course_id, file_type) ); create table if not exists public.homework ( id text primary key, user_id uuid not null references auth.users(id) on delete cascade, title text not null, ue text default '', due_date text default '', done boolean not null default false, notes text default '', created_at timestamptz default now() ); alter table public.ues enable row level security; alter table public.courses enable row level security; alter table public.schedule_events enable row level security; alter table public.notes enable row level security; alter table public.settings enable row level security; -- ── Moteur Anki : état complet d une carte ───────────────────────────────── -- card_reviews est créée par le SQL admin, pas par ce script : le bloc est -- conditionnel pour ne pas interrompre une installation neuve. do $mig$ begin if to_regclass('public.card_reviews') is not null then execute 'alter table public.card_reviews add column if not exists card_kind text'; execute 'alter table public.card_reviews add column if not exists remaining_steps int default 0'; execute 'alter table public.card_reviews add column if not exists lapses int not null default 0'; execute 'alter table public.card_reviews add column if not exists leech boolean not null default false'; end if; end $mig$; -- Planning de révisions : date de validation et butoir par semestre alter table public.settings add column if not exists plan_deadline_s1 text default ''; alter table public.settings add column if not exists plan_deadline_s2 text default ''; alter table public.settings add column if not exists plan_mode text default 'anki'; alter table public.settings add column if not exists plan_steps text default ''; alter table public.settings add column if not exists plan_costs text default ''; alter table public.settings add column if not exists hospital_days text default ''; -- Texte des PDF, extrait une fois pour qu'Agnes puisse y chercher. -- user_id null = cours partagé par l'admin : indexé une seule fois pour toute -- la promo, puisque le PDF lui-même est déjà public. create table if not exists public.course_texts ( id text primary key, user_id uuid references auth.users(id) on delete cascade, admin_course_id text, course_id text, title text default '', ue_num text default '', study_year text default '', semester int, pages int default 0, content text default '', indexed_at timestamptz default now() ); create index if not exists course_texts_title_idx on public.course_texts (title); alter table public.course_texts enable row level security; do $ct$ begin if not exists (select 1 from pg_policies where tablename='course_texts' and policyname='course_texts_read') then create policy "course_texts_read" on public.course_texts for select to authenticated using (user_id is null or auth.uid() = user_id); end if; if not exists (select 1 from pg_policies where tablename='course_texts' and policyname='course_texts_own') then create policy "course_texts_own" on public.course_texts for all to authenticated using (auth.uid() = user_id) with check (auth.uid() = user_id); end if; end $ct$; -- Date du dernier passage (lecture ou révision) : c'est elle qui permet de -- savoir ce qui a déjà été fait aujourd'hui, et donc ce qui reste à placer. alter table public.courses add column if not exists last_pass_at text; do $lp$ begin if to_regclass('public.course_progress') is not null then execute 'alter table public.course_progress add column if not exists last_pass_at text'; end if; end $lp$; -- Chronomètre : une ligne par séance de travail chronométrée. create table if not exists public.study_sessions ( id text primary key, user_id uuid not null references auth.users(id) on delete cascade, course_id text not null, shared boolean not null default true, course_title text default '', pass_index int, day text not null, seconds int not null, created_at timestamptz default now() ); create index if not exists study_sessions_user_idx on public.study_sessions (user_id, course_id); -- Périodes de travail réelles d'une séance, pauses exclues : [[début, fin], …] en millisecondes. alter table public.study_sessions add column if not exists segments text default ''; alter table public.study_sessions enable row level security; do $ss$ begin if not exists (select 1 from pg_policies where tablename='study_sessions' and policyname='study_sessions_own') then create policy "study_sessions_own" on public.study_sessions for all to authenticated using (auth.uid() = user_id) with check (auth.uid() = user_id); end if; end $ss$; alter table public.courses add column if not exists done_at text; alter table public.courses add column if not exists seen_at text; alter table public.courses add column if not exists excluded boolean not null default false; alter table public.courses add column if not exists plan_reps int; do $plan$ begin if to_regclass('public.course_progress') is not null then execute 'alter table public.course_progress add column if not exists done_at text'; execute 'alter table public.course_progress add column if not exists seen_at text'; execute 'alter table public.course_progress add column if not exists excluded boolean not null default false'; execute 'alter table public.course_progress add column if not exists plan_reps int'; end if; end $plan$; alter table public.course_files enable row level security; alter table public.homework enable row level security; do $$ begin if not exists (select 1 from pg_policies where tablename='ues' and policyname='own_ues') then create policy "own_ues" on public.ues for all using (auth.uid()=user_id) with check (auth.uid()=user_id); end if; if not exists (select 1 from pg_policies where tablename='courses' and policyname='own_courses') then create policy "own_courses" on public.courses for all using (auth.uid()=user_id) with check (auth.uid()=user_id); end if; if not exists (select 1 from pg_policies where tablename='schedule_events' and policyname='own_schedule') then create policy "own_schedule" on public.schedule_events for all using (auth.uid()=user_id) with check (auth.uid()=user_id); end if; if not exists (select 1 from pg_policies where tablename='notes' and policyname='own_notes') then create policy "own_notes" on public.notes for all using (auth.uid()=user_id) with check (auth.uid()=user_id); end if; if not exists (select 1 from pg_policies where tablename='settings' and policyname='own_settings') then create policy "own_settings" on public.settings for all using (auth.uid()=user_id) with check (auth.uid()=user_id); end if; if not exists (select 1 from pg_policies where tablename='course_files' and policyname='own_files_meta') then create policy "own_files_meta" on public.course_files for all using (auth.uid()=user_id) with check (auth.uid()=user_id); end if; if not exists (select 1 from pg_policies where tablename='homework' and policyname='own_homework') then create policy "own_homework" on public.homework for all using (auth.uid()=user_id) with check (auth.uid()=user_id); end if; end $$; -- Table profils (visible par toute la promo pour le chat) create table if not exists public.profiles ( id uuid primary key references auth.users(id) on delete cascade, name text default '', study_year text default '', serie text default '', updated_at timestamptz default now() ); -- Colonne is_editor sur profiles (éditeurs peuvent créer des flashcards) alter table public.profiles add column if not exists is_editor boolean default false; -- Table messages (messagerie directe entre étudiants) create table if not exists public.messages ( id bigint generated always as identity primary key, user_id uuid not null references auth.users(id) on delete cascade, receiver_id uuid not null references auth.users(id) on delete cascade, content text not null, created_at timestamptz default now() ); -- Ajouter receiver_id si la table existe déjà sans cette colonne alter table public.messages add column if not exists receiver_id uuid references auth.users(id) on delete cascade; alter table public.profiles enable row level security; alter table public.messages enable row level security; -- Les règles d'accès des profils et des messages sont posées plus bas, dans la -- section « Droits vérifiés par la base ». -- Activer le temps réel sur la table messages alter publication supabase_realtime add table public.messages; -- Colonnes salle et description pour l'emploi du temps admin alter table public.admin_schedule_events add column if not exists location text default ''; alter table public.admin_schedule_events add column if not exists description text default ''; insert into storage.buckets (id, name, public) values ('course-files','course-files',false) on conflict do nothing; do $$ begin if not exists (select 1 from pg_policies where tablename='objects' and policyname='own_storage') then create policy "own_storage" on storage.objects for all using (bucket_id='course-files' and auth.uid()::text=(storage.foldername(name))[1]) with check (bucket_id='course-files' and auth.uid()::text=(storage.foldername(name))[1]); end if; end $$; -- ─── Tables admin ──────────────────────────────────────────────────────────── alter table public.ues add column if not exists semester int default 1; create table if not exists public.ue_templates ( id text primary key, study_year text not null, semester int not null, num text not null, name text not null, color text default '#3b82f6', description text default '' ); create table if not exists public.admin_courses ( id text primary key, study_year text not null, semester int not null, ue_num text not null, num text default '', title text not null, type text default 'CM', pdf_name text default '', apkg_name text default '' ); alter table public.ue_templates enable row level security; alter table public.admin_courses enable row level security; insert into storage.buckets (id, name, public) values ('admin-files','admin-files',true) on conflict do nothing; -- Règles d'accès des UEs, cours et fichiers partagés : section « Droits vérifiés par la base ». -- Flashcards personnelles : cartes créées par l'étudiant + surcharges/masquages de cartes partagées create table if not exists public.user_flashcards ( id text primary key, user_id uuid not null references auth.users(id) on delete cascade, admin_course_id text not null, base_id text, -- null = carte perso ; sinon surcharge d'une flashcard partagée card_type text default 'standard', question text default '', reponse text default '', notes text default '', deleted boolean not null default false, -- masque une carte partagée pour cet utilisateur created_at timestamptz default now() ); create index if not exists idx_user_flashcards_uc on public.user_flashcards(user_id, admin_course_id); -- Compteur « nombre de fois vu » par cours et par utilisateur create table if not exists public.course_views ( user_id uuid not null references auth.users(id) on delete cascade, course_id text not null, count int not null default 0, primary key (user_id, course_id) ); alter table public.user_flashcards enable row level security; alter table public.course_views enable row level security; do $$ begin if not exists (select 1 from pg_policies where tablename='user_flashcards' and policyname='own_user_flashcards') then create policy "own_user_flashcards" on public.user_flashcards for all using (auth.uid() = user_id) with check (auth.uid() = user_id); end if; if not exists (select 1 from pg_policies where tablename='course_views' and policyname='own_course_views') then create policy "own_course_views" on public.course_views for all using (auth.uid() = user_id) with check (auth.uid() = user_id); end if; end $$; -- ═══ Droits vérifiés par la base ═══════════════════════════════════════════ -- Même contenu que supabase/securite.sql, sans la nomination du premier -- administrateur : coche is_admin sur ton compte dans Table Editor → profiles. -- Lecture réservée aux étudiants connectés, messages lisibles par leurs seuls -- participants, écriture des contenus partagés réservée aux éditeurs, rôles -- attribuables par un administrateur seulement. alter table public.profiles add column if not exists is_admin boolean not null default false; create table if not exists public.editor_invites ( email text primary key, created_at timestamptz default now() ); alter table public.editor_invites enable row level security; create or replace function public.request_role() returns text language sql stable as $$ select coalesce(nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'role', '') $$; create or replace function public.is_admin() returns boolean language sql stable security definer set search_path = public as $$ select coalesce((select p.is_admin from public.profiles p where p.id = auth.uid()), false) $$; create or replace function public.is_editor() returns boolean language sql stable security definer set search_path = public as $$ select coalesce((select p.is_editor or p.is_admin from public.profiles p where p.id = auth.uid()), false) $$; create or replace function public.protect_profile_roles() returns trigger language plpgsql security definer set search_path = public as $$ declare invited boolean; begin if public.request_role() in ('anon', 'authenticated') and not public.is_admin() then select exists ( select 1 from public.editor_invites i join auth.users u on lower(u.email) = lower(i.email) where u.id = new.id ) into invited; if tg_op = 'INSERT' then new.is_admin := false; new.is_editor := invited; else new.is_admin := old.is_admin; new.is_editor := coalesce(old.is_editor, false) or invited; end if; end if; return new; end $$; drop trigger if exists protect_profile_roles on public.profiles; create trigger protect_profile_roles before insert or update on public.profiles for each row execute function public.protect_profile_roles(); drop policy if exists "update_profile" on public.profiles; drop policy if exists "Chacun modifie son profil" on public.profiles; drop policy if exists "Tout le monde peut voir les profils" on public.profiles; drop policy if exists "read_profiles" on public.profiles; drop policy if exists "own_profile" on public.profiles; drop policy if exists "profiles_read" on public.profiles; drop policy if exists "profiles_insert_own" on public.profiles; drop policy if exists "profiles_update_own" on public.profiles; drop policy if exists "profiles_update_admin" on public.profiles; drop policy if exists "profiles_delete_own" on public.profiles; create policy "profiles_read" on public.profiles for select to authenticated using (true); create policy "profiles_insert_own" on public.profiles for insert to authenticated with check (auth.uid() = id); create policy "profiles_update_own" on public.profiles for update to authenticated using (auth.uid() = id) with check (auth.uid() = id); create policy "profiles_update_admin" on public.profiles for update to authenticated using (public.is_admin()) with check (public.is_admin()); create policy "profiles_delete_own" on public.profiles for delete to authenticated using (auth.uid() = id); drop policy if exists "Tout le monde peut lire les messages" on public.messages; drop policy if exists "Chacun peut envoyer un message" on public.messages; drop policy if exists "read_messages" on public.messages; drop policy if exists "send_messages" on public.messages; drop policy if exists "delete_own_messages" on public.messages; drop policy if exists "messages_read" on public.messages; drop policy if exists "messages_send" on public.messages; drop policy if exists "messages_delete" on public.messages; create policy "messages_read" on public.messages for select to authenticated using (auth.uid() = user_id or auth.uid() = receiver_id); create policy "messages_send" on public.messages for insert to authenticated with check (auth.uid() = user_id); create policy "messages_delete" on public.messages for delete to authenticated using (auth.uid() = user_id); -- Temps de révision : l'espace admin montre les séances chronométrées de -- chaque étudiant. Lecture seule, et seulement pour les comptes is_admin — -- un étudiant ne voit toujours que les siennes, et personne d'autre que lui -- ne peut les modifier. do $sta$ begin if to_regclass('public.study_sessions') is null then return; end if; execute 'alter table public.study_sessions enable row level security'; execute 'drop policy if exists "study_sessions_admin_read" on public.study_sessions'; execute 'create policy "study_sessions_admin_read" on public.study_sessions for select to authenticated using (public.is_admin())'; end $sta$; do $shared$ declare t text; begin foreach t in array array['admin_courses', 'ue_templates', 'flashcards', 'admin_schedule_events'] loop if to_regclass('public.' || t) is null then continue; end if; execute format('alter table public.%I enable row level security', t); execute format('drop policy if exists %I on public.%I', 'public_' || t, t); if t = 'admin_schedule_events' then execute 'drop policy if exists "public_admin_schedule" on public.admin_schedule_events'; execute 'drop policy if exists "insert_admin_schedule" on public.admin_schedule_events'; execute 'drop policy if exists "delete_admin_schedule" on public.admin_schedule_events'; end if; execute format('drop policy if exists %I on public.%I', t || '_read', t); execute format('drop policy if exists %I on public.%I', t || '_write', t); execute format('create policy %I on public.%I for select to authenticated using (true)', t || '_read', t); execute format('create policy %I on public.%I for all to authenticated using (public.is_editor()) with check (public.is_editor())', t || '_write', t); end loop; end $shared$; do $ctsec$ begin if to_regclass('public.course_texts') is null then return; end if; execute 'drop policy if exists "course_texts_shared" on public.course_texts'; execute 'drop policy if exists "course_texts_shared_insert" on public.course_texts'; execute 'drop policy if exists "course_texts_shared_edit" on public.course_texts'; execute 'create policy "course_texts_shared_insert" on public.course_texts for insert to authenticated with check (user_id is null)'; execute 'create policy "course_texts_shared_edit" on public.course_texts for all to authenticated using (user_id is null and public.is_editor()) with check (user_id is null and public.is_editor())'; end $ctsec$; drop policy if exists "public_admin_files" on storage.objects; drop policy if exists "admin_files_read" on storage.objects; drop policy if exists "admin_files_insert" on storage.objects; drop policy if exists "admin_files_update" on storage.objects; drop policy if exists "admin_files_delete" on storage.objects; create policy "admin_files_read" on storage.objects for select to authenticated using (bucket_id = 'admin-files'); create policy "admin_files_insert" on storage.objects for insert to authenticated with check (bucket_id = 'admin-files' and public.is_editor()); create policy "admin_files_update" on storage.objects for update to authenticated using (bucket_id = 'admin-files' and public.is_editor()) with check (bucket_id = 'admin-files' and public.is_editor()); create policy "admin_files_delete" on storage.objects for delete to authenticated using (bucket_id = 'admin-files' and public.is_editor());