-- PUIDirect V29 — schéma Supabase public
-- À exécuter dans Supabase > SQL Editor > New query.
-- Ce script prépare les comptes, les profils, le forum, les contributions documentaires,
-- les PDF à relire, les offres, les questionnaires et les plannings Gardes / Astreintes.

create extension if not exists "pgcrypto";

-- -----------------------------------------------------------------------------
-- Helpers
-- -----------------------------------------------------------------------------
create or replace function public.touch_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

-- -----------------------------------------------------------------------------
-- Profils utilisateurs
-- -----------------------------------------------------------------------------
create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  email text,
  full_name text,
  pseudo text,
  role text not null default 'user' check (role in ('user','admin','establishment','etablissement','moderator')),
  profession text,
  organization text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists profiles_pseudo_unique
on public.profiles (lower(pseudo))
where pseudo is not null and length(trim(pseudo)) > 0;

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.profiles (id, email, full_name, pseudo)
  values (
    new.id,
    new.email,
    coalesce(new.raw_user_meta_data->>'full_name', ''),
    nullif(coalesce(new.raw_user_meta_data->>'pseudo', 'PUI-' || substring(new.id::text from 1 for 8)), '')
  )
  on conflict (id) do update set
    email = excluded.email,
    updated_at = now();
  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 function public.handle_new_user();

create or replace function public.is_admin()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.profiles
    where id = auth.uid()
      and role in ('admin','moderator')
  );
$$;

-- -----------------------------------------------------------------------------
-- Ressources documentaires publiées
-- -----------------------------------------------------------------------------
create table if not exists public.resources (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  category text,
  theme text,
  description text,
  source text,
  external_url text,
  source_type text,
  country text default 'France',
  verified_at date,
  status text not null default 'published' check (status in ('draft','pending_review','published','archived','rejected')),
  submitted_by uuid references public.profiles(id) on delete set null,
  reviewed_by uuid references public.profiles(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create trigger resources_touch_updated_at
before update on public.resources
for each row execute function public.touch_updated_at();

-- -----------------------------------------------------------------------------
-- Propositions de sources / PDF à relire
-- -----------------------------------------------------------------------------
create table if not exists public.resource_suggestions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id) on delete set null,
  title text not null,
  theme text,
  url text,
  comment text,
  email text,
  suggestion_type text,
  pdf_path text,
  pdf_name text,
  pdf_size integer,
  admin_note text,
  status text not null default 'new' check (status in ('new','reviewed','accepted','rejected','archived','published')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create trigger resource_suggestions_touch_updated_at
before update on public.resource_suggestions
for each row execute function public.touch_updated_at();

-- Alias métier si vous préférez ce nom dans vos vues ou exports.
create or replace view public.resource_submissions as
select * from public.resource_suggestions;

create table if not exists public.resource_reports (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id) on delete set null,
  resource_id uuid references public.resources(id) on delete set null,
  resource_title text,
  issue_type text,
  comment text,
  email text,
  status text not null default 'new' check (status in ('new','reviewed','resolved','archived')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create trigger resource_reports_touch_updated_at
before update on public.resource_reports
for each row execute function public.touch_updated_at();

-- -----------------------------------------------------------------------------
-- Forum communautaire
-- -----------------------------------------------------------------------------
create table if not exists public.forum_questions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id) on delete set null,
  title text not null,
  theme text,
  body text not null,
  author_profile text,
  author_pseudo text,
  status text not null default 'pending_review' check (status in ('pending_review','published','answered','rejected','archived')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create trigger forum_questions_touch_updated_at
before update on public.forum_questions
for each row execute function public.touch_updated_at();

create table if not exists public.forum_replies (
  id uuid primary key default gen_random_uuid(),
  question_id uuid references public.forum_questions(id) on delete cascade,
  user_id uuid references public.profiles(id) on delete set null,
  author_profile text,
  author_pseudo text,
  body text not null,
  status text not null default 'pending_review' check (status in ('pending_review','published','rejected','archived')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create trigger forum_replies_touch_updated_at
before update on public.forum_replies
for each row execute function public.touch_updated_at();

-- -----------------------------------------------------------------------------
-- Emploi, établissements, alertes, thèses
-- -----------------------------------------------------------------------------
create table if not exists public.job_offers (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  position_type text,
  organization text,
  establishment_type text,
  structure_types text,
  location text,
  city text,
  department text,
  region text,
  contract_type text,
  work_time text,
  start_date text,
  duration text,
  salary text,
  team_composition text,
  risk_activities text,
  published_at date default current_date,
  contact_or_url text,
  description text,
  status text not null default 'published' check (status in ('draft','pending_review','published','archived','rejected')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create trigger job_offers_touch_updated_at
before update on public.job_offers
for each row execute function public.touch_updated_at();

create table if not exists public.etablissement_requests (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id) on delete set null,
  request_type text,
  title text,
  position_type text,
  organization text,
  establishment_type text,
  structure_types text,
  location text,
  city text,
  department text,
  region text,
  contract_type text,
  work_time text,
  start_date text,
  duration text,
  salary text,
  team_composition text,
  risk_activities text,
  published_at date,
  contact_email text,
  contact_or_url text,
  description text,
  status text not null default 'new' check (status in ('new','reviewed','contacted','qualified','published','archived','rejected')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create trigger etablissement_requests_touch_updated_at
before update on public.etablissement_requests
for each row execute function public.touch_updated_at();

create table if not exists public.alert_subscriptions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id) on delete set null,
  email text,
  theme text,
  comment text,
  status text not null default 'requested' check (status in ('requested','reviewed','active','archived')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create trigger alert_subscriptions_touch_updated_at
before update on public.alert_subscriptions
for each row execute function public.touch_updated_at();

create table if not exists public.thesis_questionnaires (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id) on delete set null,
  title text not null,
  author text,
  url text,
  audience text,
  deadline date,
  description text,
  status text not null default 'pending_review' check (status in ('pending_review','published','rejected','archived')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create trigger thesis_questionnaires_touch_updated_at
before update on public.thesis_questionnaires
for each row execute function public.touch_updated_at();

-- -----------------------------------------------------------------------------
-- Gardes / Astreintes
-- -----------------------------------------------------------------------------
create table if not exists public.guard_plannings (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id) on delete cascade,
  title text default 'Gardes / Astreintes',
  group_name text,
  share_token text unique default encode(gen_random_bytes(16), 'hex'),
  data jsonb not null default '{"members":[],"shifts":[]}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create trigger guard_plannings_touch_updated_at
before update on public.guard_plannings
for each row execute function public.touch_updated_at();

-- -----------------------------------------------------------------------------
-- Indexes utiles Supabase/Postgres
-- -----------------------------------------------------------------------------
-- Les politiques RLS et les pages publiques filtrent surtout par status, user_id,
-- question_id et date. Ces indexes gardent les listes rapides quand la base grossit.
create index if not exists resources_status_created_at_idx
on public.resources (status, created_at desc);

create index if not exists resources_theme_status_idx
on public.resources (theme, status);

create index if not exists resource_suggestions_user_created_at_idx
on public.resource_suggestions (user_id, created_at desc);

create index if not exists resource_suggestions_status_created_at_idx
on public.resource_suggestions (status, created_at desc);

create index if not exists resource_reports_user_created_at_idx
on public.resource_reports (user_id, created_at desc);

create index if not exists resource_reports_status_created_at_idx
on public.resource_reports (status, created_at desc);

create index if not exists forum_questions_status_created_at_idx
on public.forum_questions (status, created_at desc);

create index if not exists forum_questions_user_created_at_idx
on public.forum_questions (user_id, created_at desc);

create index if not exists forum_questions_theme_status_idx
on public.forum_questions (theme, status);

create index if not exists forum_replies_question_status_created_at_idx
on public.forum_replies (question_id, status, created_at);

create index if not exists forum_replies_user_created_at_idx
on public.forum_replies (user_id, created_at desc);

create index if not exists job_offers_status_published_at_idx
on public.job_offers (status, published_at desc);

create index if not exists job_offers_department_status_idx
on public.job_offers (department, status);

create index if not exists etablissement_requests_user_created_at_idx
on public.etablissement_requests (user_id, created_at desc);

create index if not exists etablissement_requests_status_created_at_idx
on public.etablissement_requests (status, created_at desc);

create index if not exists alert_subscriptions_user_created_at_idx
on public.alert_subscriptions (user_id, created_at desc);

create index if not exists thesis_questionnaires_status_created_at_idx
on public.thesis_questionnaires (status, created_at desc);

create index if not exists thesis_questionnaires_user_created_at_idx
on public.thesis_questionnaires (user_id, created_at desc);

create index if not exists guard_plannings_user_updated_at_idx
on public.guard_plannings (user_id, updated_at desc);

-- -----------------------------------------------------------------------------
-- Stockage des PDF proposés
-- -----------------------------------------------------------------------------
insert into storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
values ('resource-pdfs', 'resource-pdfs', false, 15728640, array['application/pdf'])
on conflict (id) do update set
  public = excluded.public,
  file_size_limit = excluded.file_size_limit,
  allowed_mime_types = excluded.allowed_mime_types;

-- -----------------------------------------------------------------------------
-- RLS
-- -----------------------------------------------------------------------------
alter table public.profiles enable row level security;
alter table public.resources enable row level security;
alter table public.resource_suggestions enable row level security;
alter table public.resource_reports enable row level security;
alter table public.forum_questions enable row level security;
alter table public.forum_replies enable row level security;
alter table public.job_offers enable row level security;
alter table public.etablissement_requests enable row level security;
alter table public.alert_subscriptions enable row level security;
alter table public.thesis_questionnaires enable row level security;
alter table public.guard_plannings enable row level security;

-- Nettoyage des politiques pour réexécution du script.
drop policy if exists profiles_select_self_or_admin on public.profiles;
drop policy if exists profiles_update_self_or_admin on public.profiles;
drop policy if exists resources_select_published on public.resources;
drop policy if exists resources_admin_write on public.resources;
drop policy if exists resource_suggestions_insert_own on public.resource_suggestions;
drop policy if exists resource_suggestions_select_own_or_admin on public.resource_suggestions;
drop policy if exists resource_suggestions_admin_update on public.resource_suggestions;
drop policy if exists resource_reports_insert_own on public.resource_reports;
drop policy if exists resource_reports_select_own_or_admin on public.resource_reports;
drop policy if exists resource_reports_admin_update on public.resource_reports;
drop policy if exists forum_questions_insert_own on public.forum_questions;
drop policy if exists forum_questions_select_public_own_admin on public.forum_questions;
drop policy if exists forum_questions_admin_update on public.forum_questions;
drop policy if exists forum_replies_insert_own on public.forum_replies;
drop policy if exists forum_replies_select_public_own_admin on public.forum_replies;
drop policy if exists forum_replies_admin_update on public.forum_replies;
drop policy if exists job_offers_select_published on public.job_offers;
drop policy if exists job_offers_admin_write on public.job_offers;
drop policy if exists etablissement_requests_insert_own on public.etablissement_requests;
drop policy if exists etablissement_requests_select_own_or_admin on public.etablissement_requests;
drop policy if exists etablissement_requests_admin_update on public.etablissement_requests;
drop policy if exists alert_subscriptions_insert_own on public.alert_subscriptions;
drop policy if exists alert_subscriptions_select_own_or_admin on public.alert_subscriptions;
drop policy if exists alert_subscriptions_admin_update on public.alert_subscriptions;
drop policy if exists thesis_questionnaires_insert_own on public.thesis_questionnaires;
drop policy if exists thesis_questionnaires_select_public_own_admin on public.thesis_questionnaires;
drop policy if exists thesis_questionnaires_admin_update on public.thesis_questionnaires;
drop policy if exists guard_plannings_owner_all on public.guard_plannings;
drop policy if exists guard_plannings_admin_all on public.guard_plannings;

create policy profiles_select_self_or_admin on public.profiles
for select using (auth.uid() = id or public.is_admin());
create policy profiles_update_self_or_admin on public.profiles
for update using (auth.uid() = id or public.is_admin()) with check (auth.uid() = id or public.is_admin());

create policy resources_select_published on public.resources
for select using (status = 'published' or public.is_admin());
create policy resources_admin_write on public.resources
for all using (public.is_admin()) with check (public.is_admin());

create policy resource_suggestions_insert_own on public.resource_suggestions
for insert to authenticated with check (auth.uid() = user_id);
create policy resource_suggestions_select_own_or_admin on public.resource_suggestions
for select using (auth.uid() = user_id or public.is_admin());
create policy resource_suggestions_admin_update on public.resource_suggestions
for update using (public.is_admin()) with check (public.is_admin());

create policy resource_reports_insert_own on public.resource_reports
for insert to authenticated with check (auth.uid() = user_id);
create policy resource_reports_select_own_or_admin on public.resource_reports
for select using (auth.uid() = user_id or public.is_admin());
create policy resource_reports_admin_update on public.resource_reports
for update using (public.is_admin()) with check (public.is_admin());

create policy forum_questions_insert_own on public.forum_questions
for insert to authenticated with check (auth.uid() = user_id);
create policy forum_questions_select_public_own_admin on public.forum_questions
for select using (status in ('published','answered') or auth.uid() = user_id or public.is_admin());
create policy forum_questions_admin_update on public.forum_questions
for update using (public.is_admin()) with check (public.is_admin());

create policy forum_replies_insert_own on public.forum_replies
for insert to authenticated with check (auth.uid() = user_id);
create policy forum_replies_select_public_own_admin on public.forum_replies
for select using (status = 'published' or auth.uid() = user_id or public.is_admin());
create policy forum_replies_admin_update on public.forum_replies
for update using (public.is_admin()) with check (public.is_admin());

create policy job_offers_select_published on public.job_offers
for select using (status = 'published' or public.is_admin());
create policy job_offers_admin_write on public.job_offers
for all using (public.is_admin()) with check (public.is_admin());

create policy etablissement_requests_insert_own on public.etablissement_requests
for insert to authenticated with check (auth.uid() = user_id);
create policy etablissement_requests_select_own_or_admin on public.etablissement_requests
for select using (auth.uid() = user_id or public.is_admin());
create policy etablissement_requests_admin_update on public.etablissement_requests
for update using (public.is_admin()) with check (public.is_admin());

create policy alert_subscriptions_insert_own on public.alert_subscriptions
for insert to authenticated with check (auth.uid() = user_id);
create policy alert_subscriptions_select_own_or_admin on public.alert_subscriptions
for select using (auth.uid() = user_id or public.is_admin());
create policy alert_subscriptions_admin_update on public.alert_subscriptions
for update using (public.is_admin()) with check (public.is_admin());

create policy thesis_questionnaires_insert_own on public.thesis_questionnaires
for insert to authenticated with check (auth.uid() = user_id);
create policy thesis_questionnaires_select_public_own_admin on public.thesis_questionnaires
for select using (status = 'published' or auth.uid() = user_id or public.is_admin());
create policy thesis_questionnaires_admin_update on public.thesis_questionnaires
for update using (public.is_admin()) with check (public.is_admin());

create policy guard_plannings_owner_all on public.guard_plannings
for all using (auth.uid() = user_id) with check (auth.uid() = user_id);
create policy guard_plannings_admin_all on public.guard_plannings
for all using (public.is_admin()) with check (public.is_admin());

-- Storage policies.
drop policy if exists resource_pdfs_insert_own on storage.objects;
drop policy if exists resource_pdfs_select_admin_or_owner on storage.objects;
drop policy if exists resource_pdfs_admin_update on storage.objects;
drop policy if exists resource_pdfs_admin_delete on storage.objects;

create policy resource_pdfs_insert_own on storage.objects
for insert to authenticated
with check (
  bucket_id = 'resource-pdfs'
  and (storage.foldername(name))[1] = auth.uid()::text
);

create policy resource_pdfs_select_admin_or_owner on storage.objects
for select to authenticated
using (
  bucket_id = 'resource-pdfs'
  and ((storage.foldername(name))[1] = auth.uid()::text or public.is_admin())
);

create policy resource_pdfs_admin_update on storage.objects
for update to authenticated
using (bucket_id = 'resource-pdfs' and public.is_admin())
with check (bucket_id = 'resource-pdfs' and public.is_admin());

create policy resource_pdfs_admin_delete on storage.objects
for delete to authenticated
using (bucket_id = 'resource-pdfs' and public.is_admin());

-- -----------------------------------------------------------------------------
-- Pour devenir admin : après création de votre compte sur le site, exécutez :
-- update public.profiles set role = 'admin' where email = 'votre-email@domaine.fr';
-- -----------------------------------------------------------------------------
