-- 在 Supabase 项目的 SQL Editor 中完整运行一次。
create table if not exists public.users (
  id text primary key,
  name text not null check (char_length(name) between 1 and 40),
  avatar text not null default '👤' check (char_length(avatar) between 1 and 32),
  sort_order bigint not null default 0
);

create table if not exists public.checkin_types (
  id text primary key,
  label text not null check (char_length(label) between 1 and 40),
  emoji text not null default '📝' check (char_length(emoji) between 1 and 32),
  sort_order bigint not null default 0
);

create table if not exists public.checkins (
  id uuid primary key default gen_random_uuid(),
  user_id text not null references public.users(id) on delete cascade,
  type_id text not null references public.checkin_types(id) on delete cascade,
  note text not null default '' check (char_length(note) <= 300),
  created_at timestamptz not null default now()
);

create index if not exists checkins_created_at_idx on public.checkins (created_at desc);

insert into public.users (id, name, avatar, sort_order) values
  ('mom', '妈妈', '👩🏻', 0),
  ('dad', '爸爸', '👨🏻', 1),
  ('kid', '小咪', '🐶', 2)
on conflict (id) do nothing;

insert into public.checkin_types (id, label, emoji, sort_order) values
  ('home', '平安到家', '🏡', 0),
  ('eat', '吃完饭了', '🍽️', 1),
  ('sleep', '准备睡了', '😴', 2),
  ('busy', '今天很忙', '⚡', 3)
on conflict (id) do nothing;

alter table public.users enable row level security;
alter table public.checkin_types enable row level security;
alter table public.checkins enable row level security;

grant usage on schema public to anon;
grant select, insert, update, delete on public.users to anon;
grant select, insert, update, delete on public.checkin_types to anon;
grant select, insert, update, delete on public.checkins to anon;

-- 当前版本面向受信任的小家庭：拿到站点地址的人可以读写数据。
-- 不要把 service_role key 放进 config.js；浏览器里只能放 Publishable/anon key。
drop policy if exists "family users access" on public.users;
create policy "family users access" on public.users for all to anon using (true) with check (true);

drop policy if exists "family types access" on public.checkin_types;
create policy "family types access" on public.checkin_types for all to anon using (true) with check (true);

drop policy if exists "family checkins access" on public.checkins;
create policy "family checkins access" on public.checkins for all to anon using (true) with check (true);

-- 将三张表加入 Realtime publication；重复执行时会跳过已加入的表。
do $$
begin
  if not exists (select 1 from pg_publication_tables where pubname = 'supabase_realtime' and schemaname = 'public' and tablename = 'users') then
    alter publication supabase_realtime add table public.users;
  end if;
  if not exists (select 1 from pg_publication_tables where pubname = 'supabase_realtime' and schemaname = 'public' and tablename = 'checkin_types') then
    alter publication supabase_realtime add table public.checkin_types;
  end if;
  if not exists (select 1 from pg_publication_tables where pubname = 'supabase_realtime' and schemaname = 'public' and tablename = 'checkins') then
    alter publication supabase_realtime add table public.checkins;
  end if;
end $$;
