-- ============================================================
-- 이선생 TKTX — Supabase 통합 셋업 (Fresh Install)
-- 2026-04-30
--
-- 사용법: 신규 Supabase 프로젝트의 SQL Editor에 이 파일 전체를 복붙 후 Run.
-- 한 번에 다 실행됨. 재실행 안전(IF NOT EXISTS / on conflict do nothing).
--
-- 산출물: 모든 테이블 + RLS + Storage bucket(site-images) + 시드 데이터.
-- 다음 단계: Netlify에 코드 배포 + 환경변수에 SUPABASE_URL / SUPABASE_ANON 입력.
-- ============================================================

-- ------------------------------------------------------------
-- 1. CMS 설정 (전역 — hero / footer / floating / inquiry / etc.)
-- ------------------------------------------------------------
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":"통관 고유번호 한 번에","stamp1":"정품 인증","stamp2":"직구 전용","stamp3":"SINCE 2026"}'::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"},"order":["telegram","line","instagram","kakao"]}'::jsonb),
  ('main_channel', '{"enabled":true,"icon":"telegram","label":"Telegram 메인 채널","sublabel":"실시간 응대 · 공지 + 신규 입고","url":"https://t.me/+a45_Ib2BOYMwOTZl"}'::jsonb),
  ('inquiry', '{"intro":"편하신 채널로 연락 주세요. 빠르게 답변드리겠습니다."}'::jsonb),
  ('inquiry_kakao', '{"enabled":true,"url":"","label":"카톡 문의"}'::jsonb),
  ('urgent_notice', '{"enabled":false,"title":"긴급 안내","body":"","cta_label":"","cta_url":""}'::jsonb),
  ('mente', '{"body":"이선생 TKTX는 정품 마취 크림을 합리적인 가격에 제공합니다."}'::jsonb),
  ('footer', '{"brand_line":"이선생 TKTX","line1":"정품만 직구로 · 통관 고유번호 필수","line2":"© 2026 LEESEONSAENG 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,
  images jsonb default '[]'::jsonb,
  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,
  images jsonb default '[]'::jsonb,
  image_url text,                       -- 레거시 호환(읽기 fallback). 신규 입력은 images로.
  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,
  images jsonb default '[]'::jsonb,
  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,
  admin_reply text,
  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,
  images text[] default '{}'::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). 인수 후 admin 화면에서 변경 권장.
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',
  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;

-- 공개 read (anon)
do $$
declare t text;
begin
  for t in select unnest(array['cms_config','notices','products','qna','reviews']) loop
    if not exists (select 1 from pg_policies where schemaname='public' and tablename=t and policyname=(t||'_public_read')) then
      execute format('create policy "%I" on public.%I for select using (true)', t||'_public_read', t);
    end if;
  end loop;
end$$;

do $$
begin
  if not exists (select 1 from pg_policies where schemaname='public' and tablename='external_links' and policyname='external_links_public_read') then
    create policy "external_links_public_read" on public.external_links for select using (enabled = true);
  end if;
  if not exists (select 1 from pg_policies where schemaname='public' and tablename='inquiries' and policyname='inquiries_public_insert') then
    create policy "inquiries_public_insert" on public.inquiries for insert with check (true);
  end if;
  if not exists (select 1 from pg_policies where schemaname='public' and tablename='inquiries' and policyname='inquiries_public_read_own') then
    create policy "inquiries_public_read_own" on public.inquiries for select using (true);
  end if;
  if not exists (select 1 from pg_policies where schemaname='public' and tablename='reviews' and policyname='reviews_public_insert') then
    create policy "reviews_public_insert" on public.reviews for insert with check (true);
  end if;
end$$;

-- ============================================================
-- Storage bucket — site-images (제품/공지/QnA/후기 이미지 통합)
-- ============================================================
insert into storage.buckets (id, name, public)
values ('site-images', 'site-images', true)
on conflict (id) do nothing;

do $$
begin
  if not exists (select 1 from pg_policies where schemaname='storage' and tablename='objects' and policyname='site_images_public_read') then
    create policy "site_images_public_read" on storage.objects for select using (bucket_id = 'site-images');
  end if;
  if not exists (select 1 from pg_policies where schemaname='storage' and tablename='objects' and policyname='site_images_public_insert') then
    create policy "site_images_public_insert" on storage.objects for insert with check (bucket_id = 'site-images');
  end if;
end$$;

-- ============================================================
-- 끝. 다음 단계: Netlify에 사이트 배포 + 환경변수 입력.
-- ============================================================
