-- ============================================================
-- ShootTracker — Schéma Supabase complet
-- Migration 001 — Schéma initial
-- ============================================================

-- Extension UUID
create extension if not exists "uuid-ossp";

-- ─────────────────────────────────────────────────────────────
-- TABLES
-- ─────────────────────────────────────────────────────────────

-- Profils utilisateurs (étend auth.users)
create table if not exists public.profiles (
  id              uuid references auth.users on delete cascade primary key,
  first_name      text,
  last_name       text,
  club            text,
  disciplines     text[] default '{}',
  avatar_url      text,
  created_at      timestamptz default now(),
  updated_at      timestamptz default now()
);

-- Armes (coffre virtuel)
create table if not exists public.weapons (
  id              uuid default gen_random_uuid() primary key,
  user_id         uuid references auth.users on delete cascade not null,
  name            text not null,
  nickname        text,
  type            text not null check (type in ('pistolet','carabine','fusil','arc','arbalète','autre')),
  caliber         text,
  brand           text,
  model           text,
  serial_number   text,
  photo_url       text,
  notes           text,
  is_archived     boolean default false,
  created_at      timestamptz default now(),
  updated_at      timestamptz default now()
);

-- Séances d'entraînement
create table if not exists public.training_sessions (
  id                      uuid default gen_random_uuid() primary key,
  user_id                 uuid references auth.users on delete cascade not null,
  weapon_id               uuid references public.weapons on delete set null,
  session_date            timestamptz not null default now(),
  location                text,
  target_type             text not null check (target_type in ('issf','silhouette','libre')),
  distance_m              integer,
  notes_start             text,
  notes_end               text,
  total_shots_declared    integer default 0,
  total_shots_detected    integer default 0,
  total_score             integer default 0,
  avg_score               real default 0,
  best_series_score       integer default 0,
  ai_detection_rate       real default 0,
  avg_dispersion          real,
  is_completed            boolean default false,
  created_at              timestamptz default now(),
  updated_at              timestamptz default now()
);

-- Séries de tirs (dans une séance)
create table if not exists public.series (
  id                      uuid default gen_random_uuid() primary key,
  session_id              uuid references public.training_sessions on delete cascade not null,
  user_id                 uuid references auth.users on delete cascade not null,
  series_number           integer not null,
  shots_declared          integer not null default 0,
  shots_detected          integer default 0,
  shots_manual            integer default 0,
  photo_url               text,
  annotated_photo_url     text,
  score_zone              integer default 0,
  score_groupement        integer default 0,
  score_total             integer default 0,
  dispersion_radius       real,
  center_x                real,
  center_y                real,
  ai_data                 jsonb,
  created_at              timestamptz default now()
);

-- Impacts individuels
create table if not exists public.impacts (
  id              uuid default gen_random_uuid() primary key,
  series_id       uuid references public.series on delete cascade not null,
  user_id         uuid references auth.users on delete cascade not null,
  x               real not null,
  y               real not null,
  zone            integer,
  points          integer default 0,
  is_manual       boolean default false,
  created_at      timestamptz default now()
);

-- Invitations
create table if not exists public.invitations (
  id              uuid default gen_random_uuid() primary key,
  inviter_id      uuid references auth.users on delete cascade not null,
  email           text,
  token           text unique not null default encode(gen_random_bytes(32), 'hex'),
  is_used         boolean default false,
  used_by         uuid references auth.users on delete set null,
  created_at      timestamptz default now(),
  expires_at      timestamptz default (now() + interval '7 days')
);

-- Lieux mémorisés
create table if not exists public.user_locations (
  id              uuid default gen_random_uuid() primary key,
  user_id         uuid references auth.users on delete cascade not null,
  name            text not null,
  used_count      integer default 1,
  last_used_at    timestamptz default now(),
  unique (user_id, name)
);

-- ─────────────────────────────────────────────────────────────
-- INDEXES
-- ─────────────────────────────────────────────────────────────
create index if not exists weapons_user_id_idx          on public.weapons(user_id);
create index if not exists weapons_type_idx             on public.weapons(type);
create index if not exists weapons_archived_idx         on public.weapons(is_archived);
create index if not exists sessions_user_id_idx         on public.training_sessions(user_id);
create index if not exists sessions_weapon_id_idx       on public.training_sessions(weapon_id);
create index if not exists sessions_date_idx            on public.training_sessions(session_date desc);
create index if not exists sessions_completed_idx       on public.training_sessions(is_completed);
create index if not exists series_session_id_idx        on public.series(session_id);
create index if not exists series_user_id_idx           on public.series(user_id);
create index if not exists impacts_series_id_idx        on public.impacts(series_id);
create index if not exists impacts_user_id_idx          on public.impacts(user_id);
create index if not exists invitations_token_idx        on public.invitations(token);
create index if not exists invitations_inviter_idx      on public.invitations(inviter_id);
create index if not exists locations_user_id_idx        on public.user_locations(user_id);

-- ─────────────────────────────────────────────────────────────
-- ROW LEVEL SECURITY
-- ─────────────────────────────────────────────────────────────
alter table public.profiles         enable row level security;
alter table public.weapons          enable row level security;
alter table public.training_sessions enable row level security;
alter table public.series           enable row level security;
alter table public.impacts          enable row level security;
alter table public.invitations      enable row level security;
alter table public.user_locations   enable row level security;

-- Profiles
create policy "profiles_own" on public.profiles
  for all using (auth.uid() = id);

-- Weapons
create policy "weapons_own" on public.weapons
  for all using (auth.uid() = user_id);

-- Training sessions
create policy "sessions_own" on public.training_sessions
  for all using (auth.uid() = user_id);

-- Series
create policy "series_own" on public.series
  for all using (auth.uid() = user_id);

-- Impacts
create policy "impacts_own" on public.impacts
  for all using (auth.uid() = user_id);

-- Invitations — le créateur voit ses invitations ; les non-connectés peuvent vérifier un token
create policy "invitations_own_select" on public.invitations
  for select using (auth.uid() = inviter_id or not is_used);

create policy "invitations_own_insert" on public.invitations
  for insert with check (auth.uid() = inviter_id);

create policy "invitations_own_update" on public.invitations
  for update using (auth.uid() = inviter_id or (auth.uid() is not null and not is_used));

-- User locations
create policy "locations_own" on public.user_locations
  for all using (auth.uid() = user_id);

-- ─────────────────────────────────────────────────────────────
-- FUNCTIONS & TRIGGERS
-- ─────────────────────────────────────────────────────────────

-- Crée automatiquement un profil à l'inscription
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, first_name, last_name)
  values (
    new.id,
    coalesce(new.raw_user_meta_data->>'first_name', ''),
    coalesce(new.raw_user_meta_data->>'last_name', '')
  );
  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 procedure public.handle_new_user();

-- Met à jour updated_at automatiquement
create or replace function public.set_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

create trigger weapons_updated_at        before update on public.weapons          for each row execute procedure public.set_updated_at();
create trigger sessions_updated_at       before update on public.training_sessions for each row execute procedure public.set_updated_at();
create trigger profiles_updated_at       before update on public.profiles          for each row execute procedure public.set_updated_at();

-- ─────────────────────────────────────────────────────────────
-- STORAGE BUCKETS (à exécuter dans Supabase Dashboard → Storage)
-- ─────────────────────────────────────────────────────────────
-- NB : ces commandes SQL ne fonctionnent pas directement depuis le SQL editor
-- Créer manuellement dans Dashboard → Storage :
--   bucket "avatars"         → privé, taille max 5 MB
--   bucket "weapon-photos"   → privé, taille max 10 MB
--   bucket "target-photos"   → privé, taille max 20 MB (photos haute res)
--
-- Puis appliquer ces policies de storage :
insert into storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
values
  ('avatars',       'avatars',       false, 5242880,  array['image/jpeg','image/png','image/webp']),
  ('weapon-photos', 'weapon-photos', false, 10485760, array['image/jpeg','image/png','image/webp']),
  ('target-photos', 'target-photos', false, 20971520, array['image/jpeg','image/png','image/webp'])
on conflict (id) do nothing;

-- Policies storage : chaque user n'accède qu'à son propre dossier
create policy "avatars_own" on storage.objects
  for all using (bucket_id = 'avatars' and auth.uid()::text = (storage.foldername(name))[1]);

create policy "weapon_photos_own" on storage.objects
  for all using (bucket_id = 'weapon-photos' and auth.uid()::text = (storage.foldername(name))[1]);

create policy "target_photos_own" on storage.objects
  for all using (bucket_id = 'target-photos' and auth.uid()::text = (storage.foldername(name))[1]);