为 Birthday Wall 祝福墙配置 Supabase

-- ========================================
-- Table: messages
-- Stores birthday wish wall submissions
-- ========================================

create table messages (
  id uuid default gen_random_uuid() primary key,
  content text not null,
  ip_hash text not null,
  color text not null,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Allow anyone to read messages (displayed on the home page)
alter table messages enable row level security;
create policy "Allow public read messages" on messages
  for select using (true);

-- Only the service role can insert or delete messages
create policy "Service role full access messages" on messages
  for all using (auth.role() = 'service_role');


-- ========================================
-- Table: page_configs
-- Controls which pages appear in the bottom navigation bar
-- ========================================

create table page_configs (
  id text primary key,
  name text not null,
  path text not null,
  icon text,
  visible boolean not null default true,
  "order" integer not null default 0
);

-- Insert default page configuration
insert into page_configs (id, name, path, icon, visible, "order") values
  ('home', '首页', '/', '🏠', true, 1),
  ('write', '写祝福', '/write', '✏️', true, 2),
  ('report', '报告', '/report', '📊', true, 3),
  ('games', '游戏室', '/games', '🎮', true, 4);

-- Allow anyone to read page configs
create policy "Allow public read page_configs" on page_configs
  for select using (true);

-- Only the service role can modify page configs
create policy "Service role full access page_configs" on page_configs
  for all using (auth.role() = 'service_role');


-- ========================================
-- Table: users
-- Stores admin user records linked to Supabase Auth
-- ========================================

create table users (
  id uuid primary key references auth.users(id) on delete cascade,
  email text,
  role text not null default 'user' check (role in ('admin', 'user')),
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Only the service role can access user records
create policy "Service role full access users" on users
  for all using (auth.role() = 'service_role');


-- ========================================
-- Table: registration_codes
-- Used for invitation-code-gated admin registration
-- ========================================

create table registration_codes (
  id uuid default gen_random_uuid() primary key,
  code text not null unique,
  used boolean not null default false,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Allow anyone to read codes (needed for client-side verification)
create policy "Allow public read registration_codes" on registration_codes
  for select using (true);

-- Only the service role can modify registration codes
create policy "Service role full access registration_codes" on registration_codes
  for all using (auth.role() = 'service_role');


-- ========================================
-- Optional: seed an initial admin registration code
-- Replace 'ADMIN123' with a secret value before running
-- ========================================

insert into registration_codes (code) values ('ADMIN123');