supabase_setup.sql

-- ============================================================
-- 펫/아이 성장 다이어리 — Supabase 초기 설정
-- Supabase 콘솔 > SQL Editor 에서 전체 실행
-- ============================================================

-- 1. 테이블 생성 ------------------------------------------------

create table if not exists public.profiles (
  id               uuid primary key default gen_random_uuid(),
  user_id          text not null unique,
  subject_type     text not null check (subject_type in ('pet', 'baby')),
  name             text not null,
  birth_date       date not null,
  profile_image_url text,
  created_at       timestamptz not null default now()
);

create table if not exists public.records (
  id           uuid primary key default gen_random_uuid(),
  profile_id   uuid not null references public.profiles(id) on delete cascade,
  photo_url    text,
  memo         text,
  sticker_id   text,
  recorded_at  date not null,
  created_at   timestamptz not null default now()
);

create table if not exists public.badges (
  id          uuid primary key default gen_random_uuid(),
  profile_id  uuid not null references public.profiles(id) on delete cascade,
  badge_type  text not null check (badge_type in ('7days','30days','100days','200days','365days')),
  awarded_at  timestamptz not null default now(),
  unique (profile_id, badge_type)
);

create table if not exists public.purchased_stickers (
  id           uuid primary key default gen_random_uuid(),
  user_id      text not null,
  product_id   text not null,
  purchased_at timestamptz not null default now(),
  unique (user_id, product_id)
);

-- 2. 인덱스 ---------------------------------------------------

create index if not exists profiles_user_id_idx       on public.profiles(user_id);
create index if not exists records_profile_id_idx     on public.records(profile_id);
create index if not exists records_recorded_at_idx    on public.records(recorded_at desc);
create index if not exists badges_profile_id_idx      on public.badges(profile_id);
create index if not exists purchased_stickers_uid_idx on public.purchased_stickers(user_id);

-- 3. RLS 활성화 -----------------------------------------------
-- 앱인토스 미니앱은 Supabase Auth를 사용하지 않고 Toss 자체 토큰을 씁니다.
-- anon 키를 사용하므로 아래 정책은 anon 역할에 전체 허용을 줍니다.
-- 실제 보안은 앱 레이어(user_id 필터링)에서 담당합니다.

alter table public.profiles         enable row level security;
alter table public.records          enable row level security;
alter table public.badges           enable row level security;
alter table public.purchased_stickers enable row level security;

-- profiles
create policy "anon_all_profiles" on public.profiles
  for all to anon using (true) with check (true);

-- records
create policy "anon_all_records" on public.records
  for all to anon using (true) with check (true);

-- badges
create policy "anon_all_badges" on public.badges
  for all to anon using (true) with check (true);

-- purchased_stickers
create policy "anon_all_purchased_stickers" on public.purchased_stickers
  for all to anon using (true) with check (true);

-- 4. Storage 버킷 ---------------------------------------------
-- Supabase 콘솔 > Storage > New bucket 에서 직접 생성하거나
-- 아래 함수로 생성 (storage 확장이 활성화된 경우)

insert into storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
values (
  'growth-photos',
  'growth-photos',
  false,
  5242880,  -- 5MB
  array['image/jpeg','image/png','image/webp','image/heic']
)
on conflict (id) do nothing;

-- Storage RLS: anon 역할이 자신의 userId 경로에만 업로드/읽기 허용
create policy "anon_upload_growth_photos" on storage.objects
  for insert to anon
  with check (bucket_id = 'growth-photos');

create policy "anon_read_growth_photos" on storage.objects
  for select to anon
  using (bucket_id = 'growth-photos');

-- ============================================================
-- 완료 후 .env 파일에 아래 값 입력:
--   VITE_SUPABASE_URL=https://<project-ref>.supabase.co
--   VITE_SUPABASE_ANON_KEY=<anon-key>
-- ============================================================