Cloudflare Pages 上線部署 ＆ Supabase 雲端資料庫即時同步中心

填入您的 Supabase Project URL 與 Anon Public Key 後，全站的「註冊會員、Pro 權限、教練入駐資料、預約鎖定時段、訂單帳務」將自動雙向同步至雲端 PostgreSQL 資料庫！

🔗 串接您的 Supabase 雲端資料庫

🗄️ 貼入 Supabase SQL Editor 執行 (含三角色 RLS 權限把關)

1. 在 Supabase 左側點選「Authentication ➔ Providers ➔ Email」，建議關閉 Confirm email 以支援註冊後免收信立即登入。
2. 點選「SQL Editor ➔ New query」，點擊右上「📋 一鍵複製 SQL 指令」貼上並按下 Run：

-- =====================================================================
-- SKI WORLD V12 — Supabase Auth + Row Level Security (RLS) 完整建表指令
-- 使用方式：複製全部內容 ➔ 貼入 Supabase 左側 SQL Editor ➔ New query ➔ Run
-- （可重複執行，不會覆蓋既有資料）
--
-- 三種角色權限（由資料庫 RLS 與觸發器強制把關，瀏覽器無法偽造）：
--   1. member (一般/Pro 會員)：讀寫個人名稱、預約課程、對完課教練發表 1~5 星評論
--   2. coach  (學校 / 教練)：僅限讀寫「自己」的教練公開介紹、專屬課表、銀行/證件與回覆評論
--   3. owner  (總管理員本人)：完整 5 大後台讀寫權限（開通會員 Pro、核准教練、設定抽成、結算）
-- =====================================================================

create extension if not exists pgcrypto;

create table if not exists public.profiles (
  id          uuid primary key references auth.users(id) on delete cascade,
  email       text not null default '',
  name        text not null default '' check (char_length(name) <= 80),
  role        text not null default 'member' check (role in ('member','coach','owner')),
  is_pro      boolean not null default false,
  pro_note    text not null default '',
  created_at  timestamptz not null default now()
);

create table if not exists public.coaches (
  id               text primary key default ('c_' || substr(replace(gen_random_uuid()::text,'-',''),1,12)),
  user_id          uuid unique references auth.users(id) on delete set null,
  status           text not null default 'pending' check (status in ('pending','approved','suspended')),
  commission_rate  numeric not null default 15 check (commission_rate >= 0 and commission_rate <= 50),
  profile          jsonb not null default '{}'::jsonb,
  created_at       timestamptz not null default now()
);

create table if not exists public.coach_private (
  coach_id    text primary key references public.coaches(id) on delete cascade,
  data        jsonb not null default '{}'::jsonb,
  updated_at  timestamptz not null default now()
);

create table if not exists public.orders (
  id                text primary key default ('ORD-' || to_char(now(),'YYYY') || '-' || upper(substr(replace(gen_random_uuid()::text,'-',''),1,8))),
  member_id         uuid not null default auth.uid() references auth.users(id) on delete cascade,
  coach_id          text not null references public.coaches(id) on delete cascade,
  lesson_date       date not null,
  time_slot         text not null check (time_slot in (
                      '上午時段 (09:00–12:00)',
                      '下午時段 (13:30–16:30)',
                      '全日課程 (09:00–16:00)',
                      '夜間特訓 (18:00–20:30)')),
  mode              text not null default '一對一教學 (1-on-1)',
  group_size        int  not null default 1 check (group_size between 1 and 5),
  student_name      text not null check (char_length(student_name) between 1 and 80),
  resort            text not null default '' check (char_length(resort) <= 80),
  amount            int  not null default 0,
  rate              numeric not null default 15,
  status            text not null default 'escrow' check (status in ('escrow','settled','cancelled')),
  lesson_completed  boolean not null default true,
  tx_ref            text not null default '待撥款',
  created_at        timestamptz not null default now()
);
create index if not exists orders_coach_date_idx on public.orders(coach_id, lesson_date);
create index if not exists orders_member_idx on public.orders(member_id);

create table if not exists public.reviews (
  id            bigint generated always as identity primary key,
  coach_id      text not null references public.coaches(id) on delete cascade,
  member_id     uuid not null default auth.uid() references auth.users(id) on delete cascade,
  order_id      text unique references public.orders(id) on delete set null,
  student_name  text not null default '',
  resort        text not null default '',
  lesson_date   date,
  stars         int  not null check (stars between 1 and 5),
  comment       text not null check (char_length(comment) between 1 and 100),
  coach_reply   text check (coach_reply is null or char_length(coach_reply) <= 100),
  reply_at      timestamptz,
  created_at    timestamptz not null default now()
);

create or replace function public.is_owner() returns boolean
language sql stable security definer set search_path = public as $fn$
  select exists (select 1 from public.profiles where id = auth.uid() and role = 'owner');
$fn$;

create or replace function public.my_coach_id() returns text
language sql stable security definer set search_path = public as $fn$
  select id from public.coaches where user_id = auth.uid() limit 1;
$fn$;

create or replace function public.get_booked_slots()
returns table (coach_id text, lesson_date date, time_slot text, group_size int, resort text)
language sql stable security definer set search_path = public as $fn$
  select o.coach_id, o.lesson_date, o.time_slot, o.group_size, o.resort
  from public.orders o
  where o.status <> 'cancelled';
$fn$;

create or replace function public.claim_first_owner() returns boolean
language plpgsql security definer set search_path = public as $fn$
begin
  if auth.uid() is null then raise exception 'LOGIN_REQUIRED'; end if;
  if exists (select 1 from public.profiles where role = 'owner') then
    return public.is_owner();
  end if;
  perform set_config('app.bypass_profile_guard', 'on', true);
  update public.profiles
     set role = 'owner', is_pro = true, pro_note = '系統創辦人 (Owner)'
   where id = auth.uid();
  return true;
end $fn$;

create or replace function public.purchase_my_pro(note text default '線上付費開通 Pro (NT$390/季)')
returns boolean
language plpgsql security definer set search_path = public as $fn$
begin
  if auth.uid() is null then raise exception 'LOGIN_REQUIRED'; end if;
  perform set_config('app.bypass_profile_guard', 'on', true);
  update public.profiles
     set is_pro = true,
         pro_note = left(coalesce(nullif(note,''), '線上付費開通 Pro'), 120)
   where id = auth.uid();
  return true;
end $fn$;

create or replace function public.handle_new_user() returns trigger
language plpgsql security definer set search_path = public as $fn$
declare
  meta jsonb := coalesce(new.raw_user_meta_data, '{}'::jsonb);
  r    text  := case when meta->>'signup_role' = 'coach' then 'coach' else 'member' end;
  cid  text;
begin
  insert into public.profiles (id, email, name, role)
  values (
    new.id,
    coalesce(new.email,''),
    left(coalesce(nullif(meta->>'name',''), split_part(coalesce(new.email,''),'@',1)), 80),
    r
  )
  on conflict (id) do nothing;

  if r = 'coach' then
    insert into public.coaches (user_id, status, profile)
    values (new.id, 'approved', coalesce(meta->'coach_profile', '{}'::jsonb))
    returning id into cid;
    insert into public.coach_private (coach_id, data)
    values (cid, coalesce(meta->'coach_private', '{}'::jsonb) || jsonb_build_object('email', coalesce(new.email,'')));
  end if;
  return new;
end $fn$;

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 function public.handle_new_user();

create or replace function public.guard_profiles() returns trigger
language plpgsql security definer set search_path = public as $fn$
begin
  if coalesce(current_setting('app.bypass_profile_guard', true), '') = 'on' then
    return new;
  end if;
  if current_setting('role', true) in ('authenticated', 'anon') and not public.is_owner() then
    new.role     := old.role;
    new.is_pro   := old.is_pro;
    new.pro_note := old.pro_note;
    new.email    := old.email;
    new.id       := old.id;
  end if;
  return new;
end $fn$;
drop trigger if exists guard_profiles on public.profiles;
create trigger guard_profiles before update on public.profiles
  for each row execute function public.guard_profiles();

create or replace function public.guard_coaches() returns trigger
language plpgsql security definer set search_path = public as $fn$
begin
  if tg_op = 'INSERT' then
    if current_setting('role', true) in ('authenticated', 'anon') and not public.is_owner() then
      raise exception 'NOT_ALLOWED';
    end if;
    return new;
  end if;
  if current_setting('role', true) in ('authenticated', 'anon') and not public.is_owner() then
    new.status          := old.status;
    new.commission_rate := old.commission_rate;
    new.user_id         := old.user_id;
    new.id              := old.id;
  end if;
  return new;
end $fn$;
drop trigger if exists guard_coaches on public.coaches;
create trigger guard_coaches before insert or update on public.coaches
  for each row execute function public.guard_coaches();

create or replace function public.guard_orders() returns trigger
language plpgsql security definer set search_path = public as $fn$
declare
  c        public.coaches%rowtype;
  max_grp  int;
begin
  if tg_op = 'INSERT' then
    if auth.uid() is null and not public.is_owner() then raise exception 'LOGIN_REQUIRED'; end if;
    select * into c from public.coaches where id = new.coach_id;
    if not found or c.status <> 'approved' then raise exception 'COACH_NOT_AVAILABLE'; end if;

    if not public.is_owner() then
      new.member_id        := auth.uid();
      new.status           := 'escrow';
      new.lesson_completed := true;
      new.tx_ref           := '待撥款';
      new.amount           := coalesce(nullif(c.profile->>'priceFullDay','')::int, 17500);
      new.rate             := c.commission_rate;
    end if;

    max_grp := least(5, greatest(1, coalesce(nullif(c.profile->>'maxGroupSize','')::int, 5)));
    if position('團體' in new.mode) = 0 then new.group_size := 1;
    else new.group_size := least(new.group_size, max_grp); end if;

    perform pg_advisory_xact_lock(hashtext(new.coach_id || new.lesson_date::text));
    if exists (
      select 1 from public.orders o
      where o.coach_id = new.coach_id and o.lesson_date = new.lesson_date and o.status <> 'cancelled'
        and ( o.time_slot = new.time_slot
           or (new.time_slot like '全日%' and (o.time_slot like '上午%' or o.time_slot like '下午%'))
           or (o.time_slot like '全日%' and (new.time_slot like '上午%' or new.time_slot like '下午%')) )
    ) then
      raise exception 'SLOT_TAKEN';
    end if;
    return new;
  end if;

  new.id := old.id; new.member_id := old.member_id; new.coach_id := old.coach_id;
  return new;
end $fn$;
drop trigger if exists guard_orders on public.orders;
create trigger guard_orders before insert or update on public.orders
  for each row execute function public.guard_orders();

create or replace function public.guard_reviews() returns trigger
language plpgsql security definer set search_path = public as $fn$
declare
  o public.orders%rowtype;
begin
  if tg_op = 'INSERT' then
    if auth.uid() is null then raise exception 'LOGIN_REQUIRED'; end if;
    select * into o from public.orders x
      where x.member_id = auth.uid() and x.coach_id = new.coach_id and x.status <> 'cancelled'
      order by x.lesson_date desc limit 1;
    if not found and not public.is_owner() then raise exception 'NOT_ELIGIBLE_TO_REVIEW'; end if;
    new.member_id    := auth.uid();
    new.order_id     := o.id;
    new.resort       := coalesce(nullif(new.resort,''), o.resort, '白馬雪場');
    new.lesson_date  := coalesce(new.lesson_date, o.lesson_date, current_date);
    new.student_name := coalesce((select name from public.profiles where id = auth.uid()), o.student_name, '完課學員');
    new.coach_reply  := null;
    new.reply_at     := null;
    new.created_at   := now();
    return new;
  end if;

  if not public.is_owner() then
    new.id := old.id; new.coach_id := old.coach_id; new.member_id := old.member_id; new.order_id := old.order_id;
    new.student_name := old.student_name; new.resort := old.resort; new.lesson_date := old.lesson_date;
    new.stars := old.stars; new.comment := old.comment; new.created_at := old.created_at;
  end if;
  if new.coach_reply is distinct from old.coach_reply then new.reply_at := now(); end if;
  return new;
end $fn$;
drop trigger if exists guard_reviews on public.reviews;
create trigger guard_reviews before insert or update on public.reviews
  for each row execute function public.guard_reviews();

revoke execute on function public.handle_new_user() from public, anon, authenticated;
revoke execute on function public.guard_profiles()  from public, anon, authenticated;
revoke execute on function public.guard_coaches()   from public, anon, authenticated;
revoke execute on function public.guard_orders()    from public, anon, authenticated;
revoke execute on function public.guard_reviews()   from public, anon, authenticated;
grant execute on function public.get_booked_slots()  to anon, authenticated;
grant execute on function public.is_owner()         to anon, authenticated;
grant execute on function public.my_coach_id()      to anon, authenticated;
grant execute on function public.claim_first_owner() to authenticated;
grant execute on function public.purchase_my_pro(text) to authenticated;

alter table public.profiles      enable row level security;
alter table public.coaches       enable row level security;
alter table public.coach_private enable row level security;
alter table public.orders        enable row level security;
alter table public.reviews       enable row level security;

drop policy if exists profiles_select on public.profiles;
create policy profiles_select on public.profiles for select
  using (id = auth.uid() or public.is_owner());
drop policy if exists profiles_update on public.profiles;
create policy profiles_update on public.profiles for update
  using (id = auth.uid() or public.is_owner()) with check (id = auth.uid() or public.is_owner());

drop policy if exists coaches_select on public.coaches;
create policy coaches_select on public.coaches for select
  using (status = 'approved' or user_id = auth.uid() or public.is_owner());
drop policy if exists coaches_insert on public.coaches;
create policy coaches_insert on public.coaches for insert
  with check (public.is_owner());
drop policy if exists coaches_update on public.coaches;
create policy coaches_update on public.coaches for update
  using (user_id = auth.uid() or public.is_owner()) with check (user_id = auth.uid() or public.is_owner());
drop policy if exists coaches_delete on public.coaches;
create policy coaches_delete on public.coaches for delete
  using (public.is_owner());

drop policy if exists coach_private_all on public.coach_private;
create policy coach_private_all on public.coach_private for all
  using (public.is_owner() or coach_id = public.my_coach_id())
  with check (public.is_owner() or coach_id = public.my_coach_id());

drop policy if exists orders_select on public.orders;
create policy orders_select on public.orders for select
  using (member_id = auth.uid() or coach_id = public.my_coach_id() or public.is_owner());
drop policy if exists orders_insert on public.orders;
create policy orders_insert on public.orders for insert
  with check (auth.uid() is not null and (member_id = auth.uid() or public.is_owner()));
drop policy if exists orders_update on public.orders;
create policy orders_update on public.orders for update
  using (public.is_owner()) with check (public.is_owner());
drop policy if exists orders_delete on public.orders;
create policy orders_delete on public.orders for delete
  using (public.is_owner());

drop policy if exists reviews_select on public.reviews;
create policy reviews_select on public.reviews for select using (true);
drop policy if exists reviews_insert on public.reviews;
create policy reviews_insert on public.reviews for insert
  with check (auth.uid() is not null and (member_id = auth.uid() or public.is_owner()));
drop policy if exists reviews_update on public.reviews;
create policy reviews_update on public.reviews for update
  using (coach_id = public.my_coach_id() or public.is_owner())
  with check (coach_id = public.my_coach_id() or public.is_owner());
drop policy if exists reviews_delete on public.reviews;
create policy reviews_delete on public.reviews for delete
  using (member_id = auth.uid() or public.is_owner());

revoke select (member_id) on public.reviews from anon;

insert into public.coaches (id, status, commission_rate, profile) values
  ('c1', 'approved', 12, '{"schoolName":"Evergreen International Ski School (白馬常青國際滑雪學校)","contactName":"林柏翰 (Brian Lin) — 華語部總監","entityType":"滑雪學校 Ski School","introBio":"白馬規模最大國際認證滑雪學校，逾 150 位 NZSIA/CSIA/CASI 華語與英語教練，專精新手破冰、兒童教學、Carving 刻滑與後山粉雪嚮導。","resorts":["iwatake","happo-one","tsugaike","cortina"],"country":"japan","langs":"繁體中文 / English / 粵語","lessonModes":["一對一教學 (1-on-1)","團體教學 (Group)"],"maxGroupSize":5,"certs":"NZSIA Level 3 / CSIA Level 3 / CASI Level 2","priceFullDay":18500,"igHandle":"@evergreenoutdoorcenter","igUrl":"https://www.instagram.com/evergreenoutdoorcenter/"}'::jsonb),
  ('c2', 'approved', 15, '{"schoolName":"Toomi Snow School (遠見中文滑雪學校)","contactName":"陳宇軒 (Leo Chen) — 創辦人兼總教練","entityType":"滑雪學校 Ski School","introBio":"白馬村公認全中文團隊（台港母語教練），SAJ 與 CASI L3 領軍，擅長帶領零基礎新手 2 小時安全學會轉彎，含全程動作跟拍。","resorts":["goryu","hakuba47","tsugaike","cortina","iwatake"],"country":"japan","langs":"繁體中文 / 普通話 / 粵語","lessonModes":["一對一教學 (1-on-1)","團體教學 (Group)"],"maxGroupSize":5,"certs":"SAJ 公認指導員 / CASI Level 3 / CSIA Level 2","priceFullDay":16900,"igHandle":"@toomisnow","igUrl":"https://www.instagram.com/toomisnow/"}'::jsonb),
  ('c3', 'approved', 15, '{"schoolName":"Cross Shred Snow Sports — 張凱文 (Kevin Chang)","contactName":"張凱文 (Kevin Chang)","entityType":"駐場教練 Independent Coach","introBio":"9 年日本與澳洲駐場經驗，持 CASI L2、Park L1 與 APSI L2 證照。專攻單板一對一客製教學：新手防摔、S Turn 換刃、平花與跳台入門。","resorts":["iwatake","happo-one","goryu"],"country":"japan","langs":"繁體中文 / 粵語 / English","lessonModes":["一對一教學 (1-on-1)"],"maxGroupSize":5,"certs":"CASI Level 2 & Park Instructor 1 / APSI Level 2","priceFullDay":16800,"igHandle":"@cross_shred_snow","igUrl":"https://www.instagram.com/cross_shred_snow/"}'::jsonb),
  ('c4', 'approved', 15, '{"schoolName":"Gran Paradiso Snow Academy (鹿島槍官方中英滑雪學院)","contactName":"黃雅婷 (Chloe Huang) — 教務代表","entityType":"滑雪學校 Ski School","introBio":"駐點鹿島槍與佐野坂的 ISIA 國際認證學院，專精親子家庭團體課、青木湖伴滑與雙板進階指導，配備專屬教學魔毯區。","resorts":["kashimayari","sanosaka"],"country":"japan","langs":"繁體中文 / 粵語 / English / 日本語","lessonModes":["團體教學 (Group)"],"maxGroupSize":5,"certs":"SAJ / ISIA 國際認證學校","priceFullDay":15800,"igHandle":"@granparadiso_hakuba","igUrl":"https://www.instagram.com/granparadiso_hakuba/"}'::jsonb),
  ('c5', 'approved', 15, '{"schoolName":"SnowWave Niseko & Yuzawa (北海道/湯澤雪浪中文滑雪學校)","contactName":"劉子豪 (Howard Liu)","entityType":"滑雪學校 Ski School","introBio":"橫跨二世谷、留壽都與湯澤苗場的華語學院，全團 CASI/CSIA L2–3 資深教練，提供東京當日往返新手課與北海道粉雪樹林帶滑。","resorts":["niseko-united","rusutsu","yuzawa-nakazato","naeba","shiga-kogen"],"country":"japan","langs":"繁體中文 / 粵語 / English","lessonModes":["一對一教學 (1-on-1)","團體教學 (Group)"],"maxGroupSize":5,"certs":"CASI Level 3 / CSIA Level 3","priceFullDay":18000,"igHandle":"@snowwave_japan","igUrl":"https://www.instagram.com/snowwave_japan/"}'::jsonb)
on conflict (id) do nothing;