-- =====================================================================
--  GameDev Tracker — Mise à jour complète (v2 + v3 + v4)
--  À coller dans : Supabase > SQL Editor > New query > Run
--  Sans danger si une partie a déjà été appliquée (tout est idempotent).
-- =====================================================================

-- ---------------------------------------------------------------------
--  v2 — Pôles, assignation multiple, notifications, suppression jalons
-- ---------------------------------------------------------------------
alter table public.tasks add column if not exists pole member_role;
alter table public.bugs  add column if not exists pole member_role;

alter table public.tasks
  add column if not exists assignee_ids uuid[] not null default '{}';

-- Reprend l'assigné unique existant dans le tableau (si la colonne existe encore).
do $$ begin
  if exists (select 1 from information_schema.columns
             where table_schema='public' and table_name='tasks' and column_name='assignee_id') then
    update public.tasks
      set assignee_ids = array[assignee_id]
      where assignee_id is not null and (assignee_ids is null or assignee_ids = '{}');
    alter table public.tasks drop column assignee_id;
  end if;
end $$;

alter table public.tasks drop column if exists milestone_id;
drop table if exists public.milestones cascade;
drop type  if exists milestone_status;

create table if not exists public.notifications (
  id         uuid primary key default gen_random_uuid(),
  user_id    uuid not null references public.profiles(id) on delete cascade,
  project_id uuid references public.projects(id) on delete cascade,
  kind       text not null,
  title      text not null,
  body       text,
  entity_id  uuid,
  pole       member_role,
  read       boolean not null default false,
  created_at timestamptz not null default now()
);
create index if not exists idx_notifications_user on public.notifications(user_id);
alter table public.notifications enable row level security;

drop policy if exists notifications_select on public.notifications;
drop policy if exists notifications_insert on public.notifications;
drop policy if exists notifications_update on public.notifications;
drop policy if exists notifications_delete on public.notifications;
create policy notifications_select on public.notifications
  for select to authenticated using (user_id = auth.uid());
create policy notifications_insert on public.notifications
  for insert to authenticated with check (true);
create policy notifications_update on public.notifications
  for update to authenticated using (user_id = auth.uid()) with check (user_id = auth.uid());
create policy notifications_delete on public.notifications
  for delete to authenticated using (user_id = auth.uid());

do $$ begin
  alter publication supabase_realtime add table public.notifications;
exception when duplicate_object then null; end $$;

-- ---------------------------------------------------------------------
--  v3 — Feuille de temps : entrées libres (réunion, etc.)
-- ---------------------------------------------------------------------
-- Crée la table si elle n'a jamais existé (ancienne base sans suivi du temps).
create table if not exists public.time_logs (
  id         uuid primary key default gen_random_uuid(),
  project_id uuid references public.projects(id) on delete cascade,
  task_id    uuid references public.tasks(id) on delete cascade,
  label      text,
  user_id    uuid references public.profiles(id) on delete set null,
  minutes    integer not null check (minutes > 0),
  note       text,
  logged_at  date not null default current_date,
  created_at timestamptz not null default now()
);

-- Si la table existait déjà (ancienne version), on l'aligne sur le nouveau format.
alter table public.time_logs alter column task_id drop not null;
alter table public.time_logs add column if not exists label text;
alter table public.time_logs
  add column if not exists project_id uuid references public.projects(id) on delete cascade;

update public.time_logs tl
  set project_id = t.project_id
  from public.tasks t
  where tl.task_id = t.id and tl.project_id is null;

create index if not exists idx_time_logs_task    on public.time_logs(task_id);
create index if not exists idx_time_logs_project on public.time_logs(project_id);
create index if not exists idx_time_logs_user    on public.time_logs(user_id);

alter table public.time_logs enable row level security;
drop policy if exists time_logs_all on public.time_logs;
create policy time_logs_all on public.time_logs
  for all to authenticated using (true) with check (true);

do $$ begin
  alter publication supabase_realtime add table public.time_logs;
exception when duplicate_object then null; end $$;

-- ---------------------------------------------------------------------
--  v4 — Plusieurs pôles (rôles) par membre
-- ---------------------------------------------------------------------
alter table public.profiles
  add column if not exists roles member_role[] not null default '{}';

update public.profiles
  set roles = array[role]
  where roles = '{}';

-- ---------------------------------------------------------------------
--  v5 — Accueil (onboarding) des nouveaux membres
-- ---------------------------------------------------------------------
alter table public.profiles
  add column if not exists onboarded boolean not null default false;

-- Les membres déjà présents ne sont pas ré-embarqués : on les marque « fait ».
update public.profiles set onboarded = true where onboarded = false;

-- ---------------------------------------------------------------------
--  v6 — Photo de profil (avatar image) : colonne + bucket de stockage
-- ---------------------------------------------------------------------
alter table public.profiles add column if not exists avatar_url text;

-- Bucket public "avatars" pour héberger les photos de profil.
insert into storage.buckets (id, name, public)
  values ('avatars', 'avatars', true)
  on conflict (id) do nothing;

-- Lecture publique ; écriture réservée aux membres connectés.
drop policy if exists avatars_read   on storage.objects;
drop policy if exists avatars_insert on storage.objects;
drop policy if exists avatars_update on storage.objects;
drop policy if exists avatars_delete on storage.objects;

create policy avatars_read on storage.objects
  for select using (bucket_id = 'avatars');
create policy avatars_insert on storage.objects
  for insert to authenticated with check (bucket_id = 'avatars');
create policy avatars_update on storage.objects
  for update to authenticated using (bucket_id = 'avatars');
create policy avatars_delete on storage.objects
  for delete to authenticated using (bucket_id = 'avatars');

-- ---------------------------------------------------------------------
--  v7 — Plusieurs pôles par tâche
-- ---------------------------------------------------------------------
alter table public.tasks
  add column if not exists poles member_role[] not null default '{}';

-- Reprend le pôle unique existant dans le tableau (si présent).
update public.tasks
  set poles = array[pole]
  where pole is not null and poles = '{}';

-- ---------------------------------------------------------------------
--  v8 — Checklists (cases à cocher) sur les tâches
-- ---------------------------------------------------------------------
-- Liste d'éléments [{ "id": "...", "text": "...", "done": false }, ...]
alter table public.tasks
  add column if not exists checklist jsonb not null default '[]'::jsonb;

-- ---------------------------------------------------------------------
--  v9 — Assignation MULTIPLE des bugs (parité avec les tâches)
-- ---------------------------------------------------------------------
alter table public.bugs
  add column if not exists assignee_ids uuid[] not null default '{}';

-- Reprend l'assigné unique existant dans le tableau (si présent).
update public.bugs
  set assignee_ids = array[assignee_id]
  where assignee_id is not null and assignee_ids = '{}';


-- =====================================================================
--  GameDev Tracker — Migration v10 « Refonte »
--  À coller dans : Supabase > SQL Editor > New query > Run
--  Idempotent : sans danger si déjà appliquée en tout ou partie.
--
--  Ce que fait cette migration :
--    0. Colonnes « tableau » manquantes (dont tasks.tags) et non-NULL
--    1. Commentaires sur les tâches et les bugs
--    2. Pièces jointes (captures d'écran) sur les tâches et les bugs
--    3. Parité tâches/bugs : plusieurs pôles par bug
--    4. Suppression des colonnes « de compatibilité » devenues des pièges
--       (profiles.role, tasks.pole, bugs.assignee_id)
--    5. Réordonnancement du Kanban en UNE requête transactionnelle
--    6. Suppression d'un projet réservée à son créateur ou à un admin
--    7. Temps réel sur les projets
-- =====================================================================

-- ---------------------------------------------------------------------
--  0) Colonnes « tableau » garanties — À EXÉCUTER EN PREMIER
--
--  `tasks.tags` n'existait que dans schema.sql (installations neuves) et
--  n'a jamais été ajoutée par les migrations : sur une base mise à jour
--  pas à pas, la colonne manquait et l'application plantait en tentant
--  d'afficher les étiquettes d'une tâche.
--
--  Tant qu'on y est, on ré-affirme l'invariant sur TOUTES les colonnes
--  tableau : jamais NULL, toujours un tableau vide par défaut.
-- ---------------------------------------------------------------------
alter table public.tasks    add column if not exists tags text[] not null default '{}';
alter table public.tasks    add column if not exists poles member_role[] not null default '{}';
alter table public.tasks    add column if not exists assignee_ids uuid[] not null default '{}';
alter table public.tasks    add column if not exists checklist jsonb not null default '[]'::jsonb;
alter table public.bugs     add column if not exists assignee_ids uuid[] not null default '{}';
alter table public.profiles add column if not exists roles member_role[] not null default '{}';

-- Remet à vide les valeurs NULL héritées, puis verrouille la contrainte.
do $$
declare
  r record;
begin
  for r in
    select * from (values
      ('tasks',    'tags',         '''{}''::text[]'),
      ('tasks',    'poles',        '''{}''::member_role[]'),
      ('tasks',    'assignee_ids', '''{}''::uuid[]'),
      ('tasks',    'checklist',    '''[]''::jsonb'),
      ('bugs',     'poles',        '''{}''::member_role[]'),
      ('bugs',     'assignee_ids', '''{}''::uuid[]'),
      ('profiles', 'roles',        '''{}''::member_role[]')
    ) as t(tbl, col, empty)
  loop
    if exists (
      select 1 from information_schema.columns
      where table_schema = 'public' and table_name = r.tbl and column_name = r.col
    ) then
      execute format('update public.%I set %I = %s where %I is null', r.tbl, r.col, r.empty, r.col);
      execute format('alter table public.%I alter column %I set default %s', r.tbl, r.col, r.empty);
      execute format('alter table public.%I alter column %I set not null', r.tbl, r.col);
    end if;
  end loop;
end $$;

-- ---------------------------------------------------------------------
--  1) Parité bugs / tâches : plusieurs pôles par bug
-- ---------------------------------------------------------------------
alter table public.bugs
  add column if not exists poles member_role[] not null default '{}';

do $$ begin
  if exists (select 1 from information_schema.columns
             where table_schema = 'public' and table_name = 'bugs' and column_name = 'pole') then
    update public.bugs set poles = array[pole] where pole is not null and poles = '{}';
  end if;
end $$;

-- ---------------------------------------------------------------------
--  2) Commentaires (fil de discussion sur une tâche ou un bug)
-- ---------------------------------------------------------------------
create table if not exists public.comments (
  id          uuid primary key default gen_random_uuid(),
  project_id  uuid not null references public.projects(id) on delete cascade,
  entity_kind text not null check (entity_kind in ('task', 'bug')),
  entity_id   uuid not null,
  author_id   uuid references public.profiles(id) on delete set null,
  body        text not null check (length(btrim(body)) > 0),
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now()
);

create index if not exists idx_comments_entity  on public.comments(entity_kind, entity_id);
create index if not exists idx_comments_project on public.comments(project_id);

alter table public.comments enable row level security;

drop policy if exists comments_select on public.comments;
drop policy if exists comments_insert on public.comments;
drop policy if exists comments_update on public.comments;
drop policy if exists comments_delete on public.comments;

-- Toute l'équipe lit la discussion ; on n'écrit qu'en son propre nom ;
-- on ne modifie/supprime que ses propres messages (ou n'importe lequel si admin).
create policy comments_select on public.comments
  for select to authenticated using (true);
create policy comments_insert on public.comments
  for insert to authenticated with check (author_id = auth.uid());
create policy comments_update on public.comments
  for update to authenticated
  using (author_id = auth.uid()) with check (author_id = auth.uid());
create policy comments_delete on public.comments
  for delete to authenticated
  using (
    author_id = auth.uid()
    or exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
  );

-- ---------------------------------------------------------------------
--  3) Pièces jointes (captures d'écran, logs, fichiers)
-- ---------------------------------------------------------------------
create table if not exists public.attachments (
  id          uuid primary key default gen_random_uuid(),
  project_id  uuid not null references public.projects(id) on delete cascade,
  entity_kind text not null check (entity_kind in ('task', 'bug')),
  entity_id   uuid not null,
  uploader_id uuid references public.profiles(id) on delete set null,
  name        text not null,
  path        text not null,          -- chemin dans le bucket "attachments"
  size        bigint not null default 0,
  mime        text,
  created_at  timestamptz not null default now()
);

create index if not exists idx_attachments_entity  on public.attachments(entity_kind, entity_id);
create index if not exists idx_attachments_project on public.attachments(project_id);

alter table public.attachments enable row level security;

drop policy if exists attachments_select on public.attachments;
drop policy if exists attachments_insert on public.attachments;
drop policy if exists attachments_delete on public.attachments;

create policy attachments_select on public.attachments
  for select to authenticated using (true);
create policy attachments_insert on public.attachments
  for insert to authenticated with check (uploader_id = auth.uid());
create policy attachments_delete on public.attachments
  for delete to authenticated
  using (
    uploader_id = auth.uid()
    or exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
  );

-- Bucket de stockage des fichiers joints.
insert into storage.buckets (id, name, public)
  values ('attachments', 'attachments', true)
  on conflict (id) do nothing;

drop policy if exists attachments_read   on storage.objects;
drop policy if exists attachments_write  on storage.objects;
drop policy if exists attachments_remove on storage.objects;

create policy attachments_read on storage.objects
  for select using (bucket_id = 'attachments');
create policy attachments_write on storage.objects
  for insert to authenticated with check (bucket_id = 'attachments');
create policy attachments_remove on storage.objects
  for delete to authenticated using (bucket_id = 'attachments');

-- ---------------------------------------------------------------------
--  4) Notifications : sur QUOI pointe la notification
--     (une notif de commentaire ouvre la tâche ou le bug commenté)
-- ---------------------------------------------------------------------
alter table public.notifications
  add column if not exists entity_kind text not null default 'task';

update public.notifications
  set entity_kind = kind
  where kind in ('task', 'bug') and entity_kind is distinct from kind;

create index if not exists idx_notifications_user_unread
  on public.notifications(user_id, read, created_at desc);

-- ---------------------------------------------------------------------
--  5) Réordonnancement du Kanban en UNE requête
--     Avant : une requête UPDATE par carte de la colonne (jusqu'à 30
--     allers-retours non transactionnels — un échec corrompait l'ordre).
-- ---------------------------------------------------------------------
create or replace function public.reorder_tasks(p_status task_status, p_ids uuid[])
returns void
language plpgsql
security invoker            -- la RLS de l'appelant s'applique normalement
set search_path = public
as $$
begin
  update public.tasks t
     set status   = p_status,
         position = ordered.idx
    from (
      select id, (ord - 1)::double precision as idx
        from unnest(p_ids) with ordinality as u(id, ord)
    ) ordered
   where t.id = ordered.id;
end $$;

grant execute on function public.reorder_tasks(task_status, uuid[]) to authenticated;

-- ---------------------------------------------------------------------
--  6) Suppression d'un projet : créateur ou administrateur seulement
--     (avant : n'importe quel membre pouvait effacer un projet entier
--      et tout son contenu en cascade)
-- ---------------------------------------------------------------------
drop policy if exists projects_all    on public.projects;
drop policy if exists projects_select on public.projects;
drop policy if exists projects_insert on public.projects;
drop policy if exists projects_update on public.projects;
drop policy if exists projects_delete on public.projects;

create policy projects_select on public.projects
  for select to authenticated using (true);
create policy projects_insert on public.projects
  for insert to authenticated with check (true);
create policy projects_update on public.projects
  for update to authenticated using (true) with check (true);
create policy projects_delete on public.projects
  for delete to authenticated
  using (
    created_by = auth.uid()
    or exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
  );

-- ---------------------------------------------------------------------
--  7) Temps réel : projets, commentaires, pièces jointes
-- ---------------------------------------------------------------------
do $$ begin
  alter publication supabase_realtime add table public.projects;
exception when duplicate_object then null; end $$;
do $$ begin
  alter publication supabase_realtime add table public.comments;
exception when duplicate_object then null; end $$;
do $$ begin
  alter publication supabase_realtime add table public.attachments;
exception when duplicate_object then null; end $$;

-- ---------------------------------------------------------------------
--  8) Création de profil à l'inscription : couleur d'avatar variée
--     (avant, tout le monde héritait du même violet #7c5cff)
-- ---------------------------------------------------------------------
create or replace function public.handle_new_user()
returns trigger language plpgsql security definer set search_path = public as $$
declare
  palette text[] := array['#7c5cff','#00d2b8','#ff5c8a','#ffa53c','#3ca8ff','#9bdb4d','#e15cff','#ff6b6b'];
  r member_role;
begin
  r := coalesce((new.raw_user_meta_data->>'role')::member_role, 'other');
  insert into public.profiles (id, full_name, roles, avatar_color)
  values (
    new.id,
    coalesce(new.raw_user_meta_data->>'full_name', split_part(new.email, '@', 1)),
    array[r],
    palette[1 + (abs(hashtext(new.id::text)) % array_length(palette, 1))]
  )
  on conflict (id) do nothing;
  return new;
end $$;

-- ---------------------------------------------------------------------
--  9) Suppression des colonnes « de compatibilité »
--     Elles doublonnaient les tableaux et devaient être re-synchronisées
--     à la main dans six endroits du code. On sauvegarde d'abord, puis
--     on supprime. À exécuter EN DERNIER (la fonction du point 8 ne les
--     utilise plus).
-- ---------------------------------------------------------------------

-- a) profiles.role  →  profiles.roles[1]
do $$ begin
  if exists (select 1 from information_schema.columns
             where table_schema = 'public' and table_name = 'profiles' and column_name = 'role') then
    update public.profiles set roles = array[role] where roles = '{}' and role is not null;
    alter table public.profiles drop column role;
  end if;
end $$;

-- b) tasks.pole  →  tasks.poles[1]
do $$ begin
  if exists (select 1 from information_schema.columns
             where table_schema = 'public' and table_name = 'tasks' and column_name = 'pole') then
    update public.tasks set poles = array[pole] where pole is not null and poles = '{}';
    alter table public.tasks drop column pole;
  end if;
end $$;

-- c) bugs.assignee_id  →  bugs.assignee_ids[1]  ·  bugs.pole → bugs.poles[1]
do $$ begin
  if exists (select 1 from information_schema.columns
             where table_schema = 'public' and table_name = 'bugs' and column_name = 'assignee_id') then
    update public.bugs set assignee_ids = array[assignee_id]
      where assignee_id is not null and assignee_ids = '{}';
    alter table public.bugs drop column assignee_id;
  end if;
  if exists (select 1 from information_schema.columns
             where table_schema = 'public' and table_name = 'bugs' and column_name = 'pole') then
    alter table public.bugs drop column pole;
  end if;
end $$;

-- d) bugs.priority : plus exposée dans l'interface (la sévérité suffit).
--    La colonne reste en base, avec une valeur par défaut, pour ne rien
--    perdre — mais plus personne ne la remplit.
do $$ begin
  if exists (select 1 from information_schema.columns
             where table_schema = 'public' and table_name = 'bugs' and column_name = 'priority') then
    alter table public.bugs alter column priority set default 'medium';
  end if;
end $$;

-- ---------------------------------------------------------------------
--  10) Index manquants sur les colonnes les plus filtrées
-- ---------------------------------------------------------------------
create index if not exists idx_tasks_project_status on public.tasks(project_id, status, position);
create index if not exists idx_bugs_project_status  on public.bugs(project_id, status);
create index if not exists idx_time_logs_date       on public.time_logs(project_id, logged_at);

-- ---------------------------------------------------------------------
--  11) Temps passé sur un BUG (parité avec les tâches)
--      Une entrée de temps porte sur une tâche, OU sur un bug, OU sur
--      rien (activité libre : réunion, veille…). Jamais les deux.
-- ---------------------------------------------------------------------
alter table public.time_logs
  add column if not exists bug_id uuid references public.bugs(id) on delete cascade;

create index if not exists idx_time_logs_bug on public.time_logs(bug_id);

do $$ begin
  alter table public.time_logs
    add constraint time_logs_one_target
    check (not (task_id is not null and bug_id is not null));
exception when duplicate_object then null; end $$;


-- ---------------------------------------------------------------------
--  Dernière ligne : forcer l'API à relire le schéma.
--  Sans ça, PostgREST peut continuer à servir l'ancienne structure et
--  renvoyer « Could not find the 'xxx' column ... in the schema cache »
--  pendant plusieurs minutes après la migration.
-- ---------------------------------------------------------------------
notify pgrst, 'reload schema';


-- =====================================================================
--  v11 — Documentation (fiches créées ou importées)
-- =====================================================================

create table if not exists public.documents (
  id          uuid primary key default gen_random_uuid(),
  project_id  uuid not null references public.projects(id) on delete cascade,
  title       text not null,
  -- Contenu en Markdown : c'est le format pivot. Un .docx importé est
  -- converti en Markdown à l'import, ce qui garde les fiches lisibles,
  -- cherchables et modifiables dans l'application.
  content     text not null default '',
  tags        text[] not null default '{}',
  -- Fichier d'origine, quand la fiche vient d'un import (informatif).
  source_name text,
  created_by  uuid references public.profiles(id) on delete set null,
  updated_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 index if not exists idx_documents_project on public.documents(project_id, updated_at desc);

drop trigger if exists trg_documents_touch on public.documents;
create trigger trg_documents_touch before update on public.documents
  for each row execute function public.touch_updated_at();

alter table public.documents enable row level security;

drop policy if exists documents_all on public.documents;

-- Équipe de confiance : tout membre connecté lit et écrit la documentation.
create policy documents_all on public.documents
  for all to authenticated using (true) with check (true);

do $$ begin
  alter publication supabase_realtime add table public.documents;
exception when duplicate_object then null; end $$;

notify pgrst, 'reload schema';