0001_init.sql

-- CODE SAMPLE — the first of 18 migrations in the full app; chosen here as the
-- clearest single demonstration of the schema/RLS design (owner-only policies,
-- triggers) without wading through every incremental change since.
--
-- ═══════════════════════════════════════════════════════════════════════════
-- 0001_init.sql — Fitness App initial schema
--
-- Security model: EVERY table below has Row Level Security (RLS) enabled and a
-- complete set of owner-only policies. With RLS on, Postgres denies all access
-- by default; a row is only visible/writable when a policy's USING / WITH CHECK
-- expression evaluates true. Every policy here ties access to the JWT's user id
-- via auth.uid(), so an authenticated user can ONLY ever read or write their own
-- rows. The anon role has no policies and therefore no access at all.
--
-- Apply this in the Supabase Dashboard → SQL Editor (paste + Run), or via the
-- Supabase CLI: `supabase db push`.
-- ═══════════════════════════════════════════════════════════════════════════

-- ─────────────────────────────────────────────────────────────────────────────
-- Helper: keep an updated_at column current on UPDATE.
-- ─────────────────────────────────────────────────────────────────────────────
create or replace function public.set_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

-- ═══════════════════════════════════════════════════════════════════════════
-- profiles — one row per user (PK = auth.users.id). Holds personal info, goals
-- and computed TDEE / macro targets.
-- ═══════════════════════════════════════════════════════════════════════════
create table if not exists public.profiles (
  id                    uuid primary key references auth.users (id) on delete cascade,
  full_name             text,
  age                   integer      check (age is null or (age between 13 and 120)),
  sex                   text         check (sex is null or sex in ('male', 'female', 'other')),
  height_cm             numeric(5,1) check (height_cm is null or (height_cm between 50 and 300)),
  weight_kg             numeric(5,1) check (weight_kg is null or (weight_kg between 20 and 500)),
  activity_level        text         check (activity_level is null or activity_level in
                          ('sedentary', 'light', 'moderate', 'active', 'very_active')),
  goal                  text         check (goal is null or goal in ('lose', 'maintain', 'gain')),
  goal_rate_kg_per_week numeric(3,2),
  -- Computed targets (nullable until onboarding completes).
  calorie_target        integer      check (calorie_target is null or calorie_target >= 0),
  protein_target_g      integer      check (protein_target_g is null or protein_target_g >= 0),
  carb_target_g         integer      check (carb_target_g is null or carb_target_g >= 0),
  fat_target_g          integer      check (fat_target_g is null or fat_target_g >= 0),
  units                 text         not null default 'metric'
                          check (units in ('metric', 'imperial')),
  onboarding_completed  boolean      not null default false,
  created_at            timestamptz  not null default now(),
  updated_at            timestamptz  not null default now()
);

create trigger profiles_set_updated_at
  before update on public.profiles
  for each row execute function public.set_updated_at();

alter table public.profiles enable row level security;

create policy "Profiles are viewable by owner"
  on public.profiles for select
  to authenticated
  using (auth.uid() = id);

create policy "Profiles are insertable by owner"
  on public.profiles for insert
  to authenticated
  with check (auth.uid() = id);

create policy "Profiles are updatable by owner"
  on public.profiles for update
  to authenticated
  using (auth.uid() = id)
  with check (auth.uid() = id);

create policy "Profiles are deletable by owner"
  on public.profiles for delete
  to authenticated
  using (auth.uid() = id);

-- ─────────────────────────────────────────────────────────────────────────────
-- Auto-create a profile row whenever a new auth user signs up. Runs as
-- SECURITY DEFINER (bypasses RLS) with a locked-down search_path so the app can
-- rely on the row existing. The app fills in the details during onboarding.
-- ─────────────────────────────────────────────────────────────────────────────
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.profiles (id)
  values (new.id)
  on conflict (id) do nothing;
  return new;
end;
$$;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

-- ═══════════════════════════════════════════════════════════════════════════
-- weight_logs — daily body-weight entries (one per day per user).
-- ═══════════════════════════════════════════════════════════════════════════
create table if not exists public.weight_logs (
  id         uuid primary key default gen_random_uuid(),
  user_id    uuid         not null references auth.users (id) on delete cascade,
  date       date         not null default current_date,
  weight_kg  numeric(5,1) not null check (weight_kg between 20 and 500),
  created_at timestamptz  not null default now(),
  unique (user_id, date)
);

create index if not exists weight_logs_user_date_idx
  on public.weight_logs (user_id, date desc);

alter table public.weight_logs enable row level security;

create policy "Weight logs are viewable by owner"
  on public.weight_logs for select
  to authenticated using (auth.uid() = user_id);

create policy "Weight logs are insertable by owner"
  on public.weight_logs for insert
  to authenticated with check (auth.uid() = user_id);

create policy "Weight logs are updatable by owner"
  on public.weight_logs for update
  to authenticated using (auth.uid() = user_id) with check (auth.uid() = user_id);

create policy "Weight logs are deletable by owner"
  on public.weight_logs for delete
  to authenticated using (auth.uid() = user_id);

-- ═══════════════════════════════════════════════════════════════════════════
-- food_logs — individual food entries contributing to a day's intake.
-- ═══════════════════════════════════════════════════════════════════════════
create table if not exists public.food_logs (
  id         uuid primary key default gen_random_uuid(),
  user_id    uuid         not null references auth.users (id) on delete cascade,
  date       date         not null default current_date,
  food_name  text         not null,
  grams      numeric(7,1) not null check (grams > 0),
  calories   numeric(7,1) not null check (calories >= 0),
  protein    numeric(6,1) not null default 0 check (protein >= 0),
  carbs      numeric(6,1) not null default 0 check (carbs >= 0),
  fat        numeric(6,1) not null default 0 check (fat >= 0),
  source     text         not null default 'manual'
               check (source in ('manual', 'history', 'barcode', 'search')),
  created_at timestamptz  not null default now()
);

create index if not exists food_logs_user_date_idx
  on public.food_logs (user_id, date desc);

alter table public.food_logs enable row level security;

create policy "Food logs are viewable by owner"
  on public.food_logs for select
  to authenticated using (auth.uid() = user_id);

create policy "Food logs are insertable by owner"
  on public.food_logs for insert
  to authenticated with check (auth.uid() = user_id);

create policy "Food logs are updatable by owner"
  on public.food_logs for update
  to authenticated using (auth.uid() = user_id) with check (auth.uid() = user_id);

create policy "Food logs are deletable by owner"
  on public.food_logs for delete
  to authenticated using (auth.uid() = user_id);

-- ═══════════════════════════════════════════════════════════════════════════
-- user_foods — a user's personal food history (macros per 100 g) for fast
-- re-logging, ranked by how often / recently they're used.
-- ═══════════════════════════════════════════════════════════════════════════
create table if not exists public.user_foods (
  id                uuid primary key default gen_random_uuid(),
  user_id           uuid         not null references auth.users (id) on delete cascade,
  name              text         not null,
  calories_per_100g numeric(6,1) not null check (calories_per_100g >= 0),
  protein_per_100g  numeric(5,1) not null default 0 check (protein_per_100g >= 0),
  carbs_per_100g    numeric(5,1) not null default 0 check (carbs_per_100g >= 0),
  fat_per_100g      numeric(5,1) not null default 0 check (fat_per_100g >= 0),
  frequency_count   integer      not null default 1 check (frequency_count >= 0),
  last_used         timestamptz  not null default now(),
  created_at        timestamptz  not null default now()
);

create index if not exists user_foods_user_lastused_idx
  on public.user_foods (user_id, last_used desc);

alter table public.user_foods enable row level security;

create policy "User foods are viewable by owner"
  on public.user_foods for select
  to authenticated using (auth.uid() = user_id);

create policy "User foods are insertable by owner"
  on public.user_foods for insert
  to authenticated with check (auth.uid() = user_id);

create policy "User foods are updatable by owner"
  on public.user_foods for update
  to authenticated using (auth.uid() = user_id) with check (auth.uid() = user_id);

create policy "User foods are deletable by owner"
  on public.user_foods for delete
  to authenticated using (auth.uid() = user_id);

-- ═══════════════════════════════════════════════════════════════════════════
-- user_scanned_foods — barcode-scanned products cached per user.
-- ═══════════════════════════════════════════════════════════════════════════
create table if not exists public.user_scanned_foods (
  id                uuid primary key default gen_random_uuid(),
  user_id           uuid         not null references auth.users (id) on delete cascade,
  barcode           text         not null,
  product_name      text         not null,
  brand             text,
  calories_per_100g numeric(6,1) not null check (calories_per_100g >= 0),
  protein_per_100g  numeric(5,1) not null default 0 check (protein_per_100g >= 0),
  carbs_per_100g    numeric(5,1) not null default 0 check (carbs_per_100g >= 0),
  fat_per_100g      numeric(5,1) not null default 0 check (fat_per_100g >= 0),
  created_at        timestamptz  not null default now(),
  unique (user_id, barcode)
);

create index if not exists user_scanned_foods_user_idx
  on public.user_scanned_foods (user_id, created_at desc);

alter table public.user_scanned_foods enable row level security;

create policy "Scanned foods are viewable by owner"
  on public.user_scanned_foods for select
  to authenticated using (auth.uid() = user_id);

create policy "Scanned foods are insertable by owner"
  on public.user_scanned_foods for insert
  to authenticated with check (auth.uid() = user_id);

create policy "Scanned foods are updatable by owner"
  on public.user_scanned_foods for update
  to authenticated using (auth.uid() = user_id) with check (auth.uid() = user_id);

create policy "Scanned foods are deletable by owner"
  on public.user_scanned_foods for delete
  to authenticated using (auth.uid() = user_id);