보안 설정 SQL입니다. 7번 항목의 이메일을 마스터 계정 이메일로 바꾼 뒤 Supabase SQL Editor에서 실행하세요. (여러 번 실행해도 안전)

-- ════════════════════════════════════════════════════════════════
--  폰대장 보안 설정 (마스터 계정 1개만 편집 가능)
--  Supabase → SQL Editor → New query → 전체 붙여넣기 → Run
--  ⚠️ 7번 항목의 'YOUR_EMAIL@example.com' 을 마스터 계정 이메일로 바꾼 뒤 실행하세요.
--  여러 번 실행해도 안전합니다.
-- ════════════════════════════════════════════════════════════════

-- 1. 필요한 테이블 (없으면 생성)
CREATE TABLE IF NOT EXISTS prices (
  id BIGSERIAL PRIMARY KEY, model_name TEXT NOT NULL, carrier TEXT DEFAULT '전체',
  original_price TEXT, sale_price TEXT NOT NULL, monthly TEXT, free_gift TEXT,
  is_hot BOOLEAN DEFAULT false, is_active BOOLEAN DEFAULT true, sort_order INT DEFAULT 0,
  author_store TEXT DEFAULT '본사', updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS board_posts (
  id BIGSERIAL PRIMARY KEY, store_name TEXT NOT NULL, title TEXT NOT NULL, content TEXT,
  image_url TEXT, is_active BOOLEAN DEFAULT true, created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS members (
  id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, phone TEXT NOT NULL, carrier TEXT,
  timing TEXT, interest_model TEXT, created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS franchise_inquiries (
  id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, phone TEXT NOT NULL, email TEXT, region TEXT,
  experience TEXT, message TEXT, is_contacted BOOLEAN DEFAULT false, created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS site_settings (
  key TEXT PRIMARY KEY,
  value TEXT,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS reviews (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  author_name TEXT NOT NULL,
  store_name TEXT,
  platform TEXT DEFAULT 'naver',
  rating INT DEFAULT 5,
  content TEXT,
  review_date DATE,
  visible BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. 관리자 명단 + 관리자 확인 함수
CREATE TABLE IF NOT EXISTS admin_users (email TEXT PRIMARY KEY);
ALTER TABLE admin_users ENABLE ROW LEVEL SECURITY;   -- 정책 없음 = 외부에서 읽기/쓰기 불가

CREATE OR REPLACE FUNCTION public.is_admin()
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM admin_users
    WHERE lower(email) = lower(coalesce(auth.jwt() ->> 'email', ''))
  );
$$;
GRANT EXECUTE ON FUNCTION public.is_admin() TO anon, authenticated;

-- 3. 기존 정책 전부 삭제 (예전 "누구나 쓰기 가능" 정책 제거)
DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT policyname, tablename FROM pg_policies
           WHERE schemaname = 'public'
             AND tablename IN ('prices','board_posts','members','franchise_inquiries','site_settings','reviews')
  LOOP
    EXECUTE format('DROP POLICY IF EXISTS %I ON public.%I', r.policyname, r.tablename);
  END LOOP;
END $$;

ALTER TABLE prices              ENABLE ROW LEVEL SECURITY;
ALTER TABLE board_posts         ENABLE ROW LEVEL SECURITY;
ALTER TABLE members             ENABLE ROW LEVEL SECURITY;
ALTER TABLE franchise_inquiries ENABLE ROW LEVEL SECURITY;
ALTER TABLE site_settings       ENABLE ROW LEVEL SECURITY;
ALTER TABLE reviews             ENABLE ROW LEVEL SECURITY;

-- 4. 홈페이지 내용: 누구나 읽기, 관리자만 쓰기
CREATE POLICY "read"  ON prices        FOR SELECT USING (true);
CREATE POLICY "admin" ON prices        FOR ALL    USING (is_admin()) WITH CHECK (is_admin());
CREATE POLICY "read"  ON board_posts   FOR SELECT USING (true);
CREATE POLICY "admin" ON board_posts   FOR ALL    USING (is_admin()) WITH CHECK (is_admin());
CREATE POLICY "read"  ON site_settings FOR SELECT USING (true);
CREATE POLICY "admin" ON site_settings FOR ALL    USING (is_admin()) WITH CHECK (is_admin());
CREATE POLICY "read"  ON reviews       FOR SELECT USING (visible = true OR is_admin());
CREATE POLICY "admin" ON reviews       FOR ALL    USING (is_admin()) WITH CHECK (is_admin());

-- 5. 고객 개인정보(시세 알림 신청): 손님은 신청만, 조회·삭제는 관리자만
CREATE POLICY "apply" ON members FOR INSERT WITH CHECK (true);
CREATE POLICY "admin" ON members FOR ALL    USING (is_admin()) WITH CHECK (is_admin());
-- 예전 가맹 문의 데이터도 관리자만 볼 수 있게
CREATE POLICY "admin" ON franchise_inquiries FOR ALL USING (is_admin()) WITH CHECK (is_admin());

-- 6. 이미지 저장소: 누구나 보기, 관리자만 업로드/삭제
INSERT INTO storage.buckets (id, name, public)
VALUES ('site-images', 'site-images', true)
ON CONFLICT (id) DO UPDATE SET public = true;

DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT policyname FROM pg_policies
           WHERE schemaname = 'storage' AND tablename = 'objects'
             AND (coalesce(qual,'') || coalesce(with_check,'')) LIKE '%site-images%'
  LOOP
    EXECUTE format('DROP POLICY IF EXISTS %I ON storage.objects', r.policyname);
  END LOOP;
END $$;

CREATE POLICY "site-images read"   ON storage.objects FOR SELECT USING (bucket_id = 'site-images');
CREATE POLICY "site-images insert" ON storage.objects FOR INSERT WITH CHECK (bucket_id = 'site-images' AND is_admin());
CREATE POLICY "site-images update" ON storage.objects FOR UPDATE USING (bucket_id = 'site-images' AND is_admin());
CREATE POLICY "site-images delete" ON storage.objects FOR DELETE USING (bucket_id = 'site-images' AND is_admin());

-- 7. 마스터 계정 등록 ← 여기 이메일을 본인 것으로 바꾸세요
INSERT INTO admin_users (email) VALUES ('YOUR_EMAIL@example.com')
ON CONFLICT DO NOTHING;

-- 8. 최저가 시세표 (엑셀 업로드 결과 저장, 날짜별 기록이 쌓임)
CREATE TABLE IF NOT EXISTS price_sheets (
  id BIGSERIAL PRIMARY KEY,
  sheet_date DATE NOT NULL DEFAULT CURRENT_DATE,
  data JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
ALTER TABLE price_sheets ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "read"  ON price_sheets;
DROP POLICY IF EXISTS "admin" ON price_sheets;
CREATE POLICY "read"  ON price_sheets FOR SELECT USING (true);
CREATE POLICY "admin" ON price_sheets FOR ALL    USING (is_admin()) WITH CHECK (is_admin());