-- 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$; 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; do $$ begin -- Tout le monde peut lire les profils (pour afficher les noms dans le chat) if not exists (select 1 from pg_policies where tablename='profiles' and policyname='read_profiles') then create policy "read_profiles" on public.profiles for select using (true); end if; if not exists (select 1 from pg_policies where tablename='profiles' and policyname='own_profile') then create policy "own_profile" on public.profiles for insert with check (auth.uid()=id); end if; if not exists (select 1 from pg_policies where tablename='profiles' and policyname='update_profile') then create policy "update_profile" on public.profiles for update using (auth.uid()=id); end if; -- Tout le monde peut lire et envoyer des messages if not exists (select 1 from pg_policies where tablename='messages' and policyname='read_messages') then create policy "read_messages" on public.messages for select using (true); end if; if not exists (select 1 from pg_policies where tablename='messages' and policyname='send_messages') then create policy "send_messages" on public.messages for insert with check (auth.uid()=user_id); end if; -- Chaque utilisateur peut supprimer ses propres messages if not exists (select 1 from pg_policies where tablename='messages' and policyname='delete_own_messages') then create policy "delete_own_messages" on public.messages for delete using (auth.uid()=user_id); end if; end $$; -- 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; do $$ begin if not exists (select 1 from pg_policies where tablename='ue_templates' and policyname='public_ue_templates') then create policy "public_ue_templates" on public.ue_templates for all using (true) with check (true); end if; if not exists (select 1 from pg_policies where tablename='admin_courses' and policyname='public_admin_courses') then create policy "public_admin_courses" on public.admin_courses for all using (true) with check (true); end if; end $$; insert into storage.buckets (id, name, public) values ('admin-files','admin-files',true) on conflict do nothing; do $$ begin if not exists (select 1 from pg_policies where tablename='objects' and policyname='public_admin_files') then create policy "public_admin_files" on storage.objects for all using (bucket_id='admin-files') with check (bucket_id='admin-files'); end if; end $$; -- 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 $$;