개발자가 없어도 판단하실 수 있게, 화면으로 적었습니다

-- ─────────────────────────────────────────────────────────────
-- 1) 기관(계정) — Supabase Auth 의 사용자와 1:1로 붙는다
-- ─────────────────────────────────────────────────────────────
create table public.organizations (
  id            uuid primary key references auth.users(id) on delete cascade,
  name          text        not null,                 -- 기관·기업명
  contact_email text        not null,
  business_no   text,                                 -- 사업자등록번호(선택)
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now()
);

-- ─────────────────────────────────────────────────────────────
-- 2) 진단 회차 — 1차 · 2차 · 3차 …
-- ─────────────────────────────────────────────────────────────
create table public.assessments (
  id            uuid        primary key default gen_random_uuid(),
  org_id        uuid        not null references public.organizations(id) on delete cascade,
  round         integer     not null check (round >= 1),
  status        text        not null default 'draft'
                            check (status in ('draft','submitted')),
  -- 지표 세트 버전. 지표를 개정해도 과거 회차의 점수 근거가 보존된다
  indicator_set text        not null default 'v1',
  total_score   numeric(5,1),                         -- 100점 환산
  grade         text,                                 -- 관찰 / 기초 / 보통 / 우수 / 최우수
  submitted_at  timestamptz,
  created_at    timestamptz not null default now(),
  unique (org_id, round)                              -- 같은 기관에 같은 회차는 하나뿐
);

create index assessments_org_round_idx
  on public.assessments (org_id, round desc);

-- ─────────────────────────────────────────────────────────────
-- 3) 문항 응답 — 회차당 20행
-- ─────────────────────────────────────────────────────────────
create table public.assessment_answers (
  id              uuid      primary key default gen_random_uuid(),
  assessment_id   uuid      not null references public.assessments(id) on delete cascade,
  indicator_code  text      not null,                 -- 'E1' · 'S3' · 'SV5' …
  score           smallint  not null check (score between 0 and 5),
  note            text,                               -- 근거 메모
  created_at      timestamptz not null default now(),
  unique (assessment_id, indicator_code)              -- 한 문항에 답은 하나
);

create index assessment_answers_assessment_idx
  on public.assessment_answers (assessment_id);

-- 회원가입과 동시에 기관 행을 만든다.
-- 앱 코드가 두 번 호출할 필요가 없어, 「가입은 됐는데 기관이 없는」 상태가 생기지 않는다.
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  insert into public.organizations (id, name, contact_email)
  values (
    new.id,
    coalesce(new.raw_user_meta_data->>'org_name', '(기관명 미입력)'),
    new.email
  );
  return new;
end;
$$;

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

-- 정책이 실제로 막는지 확인하는 방법 (Supabase SQL Editor 에서 5분)
-- 계정 A 로 로그인한 상태를 흉내 내고, 계정 B 의 진단을 조회해 본다.

-- 1) A 의 시점으로 전환
select set_config('request.jwt.claims',
  json_build_object('sub', '<계정 A 의 uuid>')::text, true);
set local role authenticated;

-- 2) 전체 진단을 조회 — A 의 것만 나온다
select org_id, round, total_score from public.assessments;

-- 3) B 의 진단 id 를 콕 집어 조회 — 0행이 나온다 (에러가 아니라 「없음」)
select * from public.assessments where id = '<계정 B 의 진단 id>';

-- 4) B 의 진단에 답을 밀어 넣어 본다 — with check 위반으로 거부된다
insert into public.assessment_answers (assessment_id, indicator_code, score)
values ('<계정 B 의 진단 id>', 'E1', 5);
-- ERROR: new row violates row-level security policy