-- =====================================================================
--  GameDev Tracker — Schéma complet (Supabase / PostgreSQL)
--
--  NOUVELLE base :  exécute CE fichier, une seule fois.
--  Base EXISTANTE : exécute plutôt « update_all.sql » (ne perd rien).
--
--  Où ? Supabase > SQL Editor > New query > coller > Run.
-- =====================================================================

-- ---------- Types énumérés -------------------------------------------
do $$ begin
  create type member_role as enum
    ('lead','programmer','artist','game_designer','sound','writer','qa','other');
exception when duplicate_object then null; end $$;

do $$ begin
  create type task_status as enum ('todo','in_progress','review','done');
exception when duplicate_object then null; end $$;

do $$ begin
  create type priority_level as enum ('low','medium','high','urgent');
exception when duplicate_object then null; end $$;

do $$ begin
  create type bug_severity as enum ('low','medium','high','critical');
exception when duplicate_object then null; end $$;

do $$ begin
  create type bug_status as enum ('open','in_progress','resolved','closed');
exception when duplicate_object then null; end $$;

-- =====================================================================
--  TABLES
-- =====================================================================

-- ---------- Profils (étend auth.users) -------------------------------
-- `roles` est la SEULE source de vérité pour les pôles d'un membre.
create table if not exists public.profiles (
  id           uuid primary key references auth.users(id) on delete cascade,
  full_name    text not null default 'Membre',
  roles        member_role[] not null default '{}',
  avatar_color text not null default '#7c5cff',
  avatar_url   text,
  is_admin     boolean not null default false,
  onboarded    boolean not null default false,
  created_at   timestamptz not null default now()
);

-- ---------- Projets --------------------------------------------------
create table if not exists public.projects (
  id          uuid primary key default gen_random_uuid(),
  name        text not null,
  description text,
  created_by  uuid references public.profiles(id) on delete set null,
  created_at  timestamptz not null default now()
);

-- ---------- Tâches ---------------------------------------------------
-- Une tâche est confiée à un ou plusieurs PÔLES (poles) et assignée
-- nommément à une ou plusieurs PERSONNES (assignee_ids).
create table if not exists public.tasks (
  id           uuid primary key default gen_random_uuid(),
  project_id   uuid not null references public.projects(id) on delete cascade,
  title        text not null,
  description  text,
  status       task_status not null default 'todo',
  priority     priority_level not null default 'medium',
  poles        member_role[] not null default '{}',
  assignee_ids uuid[] not null default '{}',
  checklist    jsonb not null default '[]'::jsonb,   -- [{id, text, done}]
  tags         text[] not null default '{}',
  due_date     date,
  position     double precision not null default 0,
  created_by   uuid references public.profiles(id) on delete set null,
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now()
);

-- ---------- Bugs -----------------------------------------------------
-- Même modèle que les tâches (pôles + assignés multiples). La SÉVÉRITÉ
-- est le seul axe d'importance : la colonne `priority` faisait doublon
-- et n'est plus exposée dans l'interface.
create table if not exists public.bugs (
  id           uuid primary key default gen_random_uuid(),
  project_id   uuid not null references public.projects(id) on delete cascade,
  title        text not null,
  description  text,
  severity     bug_severity not null default 'medium',
  status       bug_status not null default 'open',
  priority     priority_level not null default 'medium',
  poles        member_role[] not null default '{}',
  assignee_ids uuid[] not null default '{}',
  reporter_id  uuid references public.profiles(id) on delete set null,
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now()
);

-- ---------- Journal du temps (tâche OU activité libre) ---------------
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,
  bug_id     uuid references public.bugs(id) on delete cascade,
  label      text,                                  -- si ni tâche ni bug
  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(),
  -- Une entrée porte sur une tâche OU un bug OU rien : jamais les deux.
  constraint time_logs_one_target check (not (task_id is not null and bug_id is not null))
);

-- ---------- Commentaires (tâches et bugs) ----------------------------
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()
);

-- ---------- Pièces jointes (captures d'écran, logs) ------------------
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,
  size        bigint not null default 0,
  mime        text,
  created_at  timestamptz not null default now()
);

-- ---------- Documentation (fiches créées ou importées) ----------------
-- Le contenu est du Markdown : format pivot, lisible et cherchable. Un
-- .docx importé est converti en Markdown au moment de l'import.
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,
  content     text not null default '',
  tags        text[] not null default '{}',
  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()
);

-- ---------- Notifications --------------------------------------------
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,                        -- 'task' | 'bug' | 'comment'
  entity_kind text not null default 'task',         -- ce qu'on ouvre au clic
  entity_id   uuid,
  title       text not null,
  body        text,
  pole        member_role,
  read        boolean not null default false,
  created_at  timestamptz not null default now()
);

-- =====================================================================
--  INDEX
-- =====================================================================
create index if not exists idx_tasks_project         on public.tasks(project_id);
create index if not exists idx_tasks_project_status  on public.tasks(project_id, status, position);
create index if not exists idx_bugs_project          on public.bugs(project_id);
create index if not exists idx_bugs_project_status   on public.bugs(project_id, status);
create index if not exists idx_time_logs_task        on public.time_logs(task_id);
create index if not exists idx_time_logs_bug         on public.time_logs(bug_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);
create index if not exists idx_time_logs_date        on public.time_logs(project_id, logged_at);
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);
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);
create index if not exists idx_documents_project     on public.documents(project_id, updated_at desc);
create index if not exists idx_notifications_user    on public.notifications(user_id);
create index if not exists idx_notifications_user_unread
  on public.notifications(user_id, read, created_at desc);

-- =====================================================================
--  FONCTIONS & TRIGGERS
-- =====================================================================

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

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

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

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();

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

-- ---------- Création auto du profil à l'inscription ------------------
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 $$;

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();

-- ---------- Réordonnancement du Kanban en une transaction ------------
-- L'appli envoie la liste ordonnée des tâches d'une colonne ; une seule
-- requête réécrit statut + position pour toute la colonne.
create or replace function public.reorder_tasks(p_status task_status, p_ids uuid[])
returns void
language plpgsql
security invoker
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;

-- ---------- Garde-fou : on ne se promeut pas admin soi-même ----------
create or replace function public.guard_is_admin()
returns trigger language plpgsql security definer set search_path = public as $$
begin
  if new.is_admin is distinct from old.is_admin
     and current_user = 'authenticated'
     and not exists (
       select 1 from public.profiles p where p.id = auth.uid() and p.is_admin
     ) then
    raise exception 'Seul un administrateur peut modifier le statut admin.';
  end if;
  return new;
end $$;

drop trigger if exists trg_profiles_guard_admin on public.profiles;
create trigger trg_profiles_guard_admin before update on public.profiles
  for each row execute function public.guard_is_admin();

-- =====================================================================
--  SÉCURITÉ (RLS)
--  Équipe privée et de confiance : tout membre connecté lit et écrit le
--  contenu des projets. Les exceptions sont explicites ci-dessous :
--    · un profil ne se modifie que par son propriétaire
--    · un projet ne se supprime que par son créateur ou un admin
--    · un commentaire ne s'édite que par son auteur
-- =====================================================================
alter table public.profiles      enable row level security;
alter table public.projects      enable row level security;
alter table public.tasks         enable row level security;
alter table public.bugs          enable row level security;
alter table public.time_logs     enable row level security;
alter table public.comments      enable row level security;
alter table public.attachments   enable row level security;
alter table public.documents     enable row level security;
alter table public.notifications enable row level security;

-- ---------- Contenu partagé (tâches, bugs, temps) --------------------
do $$
declare t text;
begin
  foreach t in array array['tasks','bugs','time_logs','documents'] loop
    execute format('drop policy if exists %I_all on public.%I', t, t);
    execute format(
      'create policy %I_all on public.%I for all to authenticated using (true) with check (true)',
      t, t);
  end loop;
end $$;

-- ---------- Profils ---------------------------------------------------
drop policy if exists profiles_all         on public.profiles;
drop policy if exists profiles_select      on public.profiles;
drop policy if exists profiles_update_self on public.profiles;

create policy profiles_select on public.profiles
  for select to authenticated using (true);
create policy profiles_update_self on public.profiles
  for update to authenticated
  using (id = auth.uid()) with check (id = auth.uid());
-- La création / suppression de comptes passe par l'Edge Function
-- « admin-users » (clé service_role, côté serveur).

-- ---------- Projets ---------------------------------------------------
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)
  );

-- ---------- Commentaires ----------------------------------------------
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;

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)
  );

-- ---------- Pièces jointes --------------------------------------------
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)
  );

-- ---------- Notifications ---------------------------------------------
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());
-- Insertion ouverte : un membre doit pouvoir notifier ses coéquipiers.
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());

-- =====================================================================
--  TEMPS RÉEL (synchro live entre membres)
-- =====================================================================
do $$
declare t text;
begin
  foreach t in array array['tasks','bugs','profiles','projects','time_logs',
                           'notifications','comments','attachments','documents'] loop
    begin
      execute format('alter publication supabase_realtime add table public.%I', t);
    exception when duplicate_object then null;
    end;
  end loop;
end $$;

-- =====================================================================
--  STOCKAGE
--    · avatars     : photos de profil
--    · attachments : captures d'écran et fichiers joints aux bugs/tâches
-- =====================================================================
insert into storage.buckets (id, name, public)
  values ('avatars', 'avatars', true), ('attachments', 'attachments', true)
  on conflict (id) do nothing;

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');

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');

-- =====================================================================
--  DERNIÈRE ÉTAPE — devenir administrateur (à faire UNE fois)
--  Après avoir créé ton compte dans l'application, exécute :
--
--    update public.profiles set is_admin = true
--    where id = (select id from auth.users where email = 'TON_EMAIL@exemple.com');
--
--  Tu pourras ensuite gérer tous les comptes depuis l'onglet Administration.
-- =====================================================================


-- ---------------------------------------------------------------------
--  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';