-- 이선생 TKTX — Supabase 스키마
-- 실행: Supabase SQL Editor에 복붙 후 Run
-- RLS는 anon=read, service_role=write 패턴 (admin은 Edge Function 또는 PW 검증 후 service_role 키 사용)

-- ============================================================
-- 1. CMS 설정 (전역)
-- ============================================================
create table if not exists cms_config (
  key text primary key,
  value jsonb not null default '{}'::jsonb,
  updated_at timestamptz default now()
);

-- 초기 데이터
insert into cms_config (key, value) values
  ('hero', '{"title":"이선생 TKTX","subtitle":"타투 마취 크림 정품 직구","tagline":"통관 고유번호 한 번에"}'::jsonb),
  ('floating', '{"telegram":{"enabled":true,"url":"https://t.me/e_tktx"},"kakao":{"enabled":true,"url":"https://www.kakaocorp.com"},"line":{"enabled":true,"url":"https://line.me"},"instagram":{"enabled":true,"url":"https://www.instagram.com"}}'::jsonb),
  ('inquiry_kakao', '{"enabled":true,"url":"","label":"카톡 문의"}'::jsonb),
  ('urgent_notice', '{"enabled":false,"title":"긴급 안내","body":"","cta_label":"","cta_url":""}'::jsonb),
  ('mente', '{"body":"이선생 TKTX는 정품 마취 크림을 합리적인 가격에 제공합니다."}'::jsonb)
on conflict (key) do nothing;

-- ============================================================
-- 2. 공지사항
-- ============================================================
create table if not exists notices (
  id bigserial primary key,
  title text not null,
  body text not null,
  external_url text,
  pinned boolean default false,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

create index if not exists idx_notices_pinned on notices (pinned desc, created_at desc);

-- ============================================================
-- 3. 제품
-- ============================================================
create table if not exists products (
  id bigserial primary key,
  name text not null,
  price integer not null,             -- KRW
  image_url text,
  description text,
  sort_order integer default 0,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

create index if not exists idx_products_sort on products (sort_order asc, created_at desc);

-- ============================================================
-- 4. QnA
-- ============================================================
create table if not exists qna (
  id bigserial primary key,
  question text not null,
  answer text not null,
  external_url text,                  -- 예: 통관 고유번호 받기
  external_label text,                -- 버튼 라벨
  sort_order integer default 0,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

create index if not exists idx_qna_sort on qna (sort_order asc, created_at desc);

-- ============================================================
-- 5. 문의 (방문자 → admin)
-- ============================================================
create table if not exists inquiries (
  id bigserial primary key,
  name text not null,
  contact text,                       -- 카톡/번호/이메일 등
  body text not null,
  is_private boolean default false,   -- 비공개
  password_hash text,                 -- 작성자 PW (sha256)
  admin_reply text,                   -- admin 답변
  replied_at timestamptz,
  created_at timestamptz default now()
);

create index if not exists idx_inquiries_created on inquiries (created_at desc);

-- ============================================================
-- 6. 후기
-- ============================================================
create table if not exists reviews (
  id bigserial primary key,
  author text not null,
  body text not null,
  rating integer check (rating between 1 and 5),
  is_private boolean default false,
  password_hash text,
  created_at timestamptz default now()
);

create index if not exists idx_reviews_created on reviews (created_at desc);

-- ============================================================
-- 7. Admin 계정 (단일)
-- ============================================================
create table if not exists admin_users (
  id integer primary key default 1,
  password_hash text not null,
  updated_at timestamptz default now(),
  constraint singleton check (id = 1)
);

-- 초기 PW = dltjstod@9977 (sha256)
-- echo -n "dltjstod@9977" | sha256sum → 22fdc2a6e6b73c6e6a5f76c0a82e2a5e... 직접 계산
-- 아래는 plaintext placeholder, 첫 admin 로그인 시 hash로 자동 마이그레이션
insert into admin_users (id, password_hash) values
  (1, encode(digest('dltjstod@9977', 'sha256'), 'hex'))
on conflict (id) do nothing;

-- ============================================================
-- 8. 외부 링크 버튼 (페이지 사이사이)
-- ============================================================
create table if not exists external_links (
  id bigserial primary key,
  label text not null,
  url text not null,
  position text default 'bottom',     -- top / middle / bottom / hero
  sort_order integer default 0,
  enabled boolean default true,
  created_at timestamptz default now()
);

-- ============================================================
-- RLS (Row Level Security)
-- ============================================================
alter table cms_config enable row level security;
alter table notices enable row level security;
alter table products enable row level security;
alter table qna enable row level security;
alter table inquiries enable row level security;
alter table reviews enable row level security;
alter table admin_users enable row level security;
alter table external_links enable row level security;

-- 공개 읽기 (anon)
create policy "public_read_cms" on cms_config for select using (true);
create policy "public_read_notices" on notices for select using (true);
create policy "public_read_products" on products for select using (true);
create policy "public_read_qna" on qna for select using (true);
create policy "public_read_reviews" on reviews for select using (true);
create policy "public_read_external_links" on external_links for select using (enabled = true);

-- 문의 작성 허용 (anon insert만)
create policy "public_insert_inquiries" on inquiries for insert with check (true);
create policy "public_read_own_inquiries" on inquiries for select using (true);

-- 후기 작성 허용
create policy "public_insert_reviews" on reviews for insert with check (true);

-- 후기/문의 본인 삭제: PW 해시 일치는 클라이언트에서 검증 후 service_role로 삭제 처리 (Edge Function 또는 admin proxy)

-- admin_users: 직접 접근 금지 (service_role만)
-- 실제 admin 로그인은 Edge Function `/admin-login`에서 PW 비교 후 JWT 발급
