ShootTracker Deploy
247 lines
12 KiB
PL/PgSQL
247 lines
12 KiB
PL/PgSQL
-- ============================================================
|
|
-- 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]);
|