-- ══════════════════════════════════════════════
--  PICTRA - Supabase 데이터베이스 초기 설정 SQL
--  Supabase > SQL Editor 에 전체 붙여넣기 후 실행
-- ══════════════════════════════════════════════

-- 1. PROFILES 테이블 (사용자 정보)
CREATE TABLE IF NOT EXISTS profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  username TEXT UNIQUE NOT NULL,
  full_name TEXT,
  email TEXT,
  bio TEXT,
  avatar_url TEXT,
  role TEXT DEFAULT 'user',   -- 'user' | 'admin'
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. POSTS 테이블 (게시물)
CREATE TABLE IF NOT EXISTS posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  caption TEXT DEFAULT '',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 3. POST_IMAGES 테이블 (게시물 이미지 - 1게시물 다중 이미지)
CREATE TABLE IF NOT EXISTS post_images (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  image_url TEXT NOT NULL,
  order_index INT DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. LIKES 테이블 (좋아요)
CREATE TABLE IF NOT EXISTS likes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(post_id, user_id)
);

-- 5. COMMENTS 테이블 (댓글)
CREATE TABLE IF NOT EXISTS comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 6. FOLLOWS 테이블 (팔로우)
CREATE TABLE IF NOT EXISTS follows (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  follower_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  following_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(follower_id, following_id)
);

-- ══════════════════════════════════════════════
--  ROW LEVEL SECURITY (RLS) 설정
-- ══════════════════════════════════════════════

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE post_images ENABLE ROW LEVEL SECURITY;
ALTER TABLE likes ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;
ALTER TABLE follows ENABLE ROW LEVEL SECURITY;

-- PROFILES policies
CREATE POLICY "누구나 프로필 읽기 가능" ON profiles FOR SELECT USING (true);
CREATE POLICY "본인 프로필 수정 가능" ON profiles FOR UPDATE USING (auth.uid() = id);
CREATE POLICY "회원가입 시 프로필 생성" ON profiles FOR INSERT WITH CHECK (auth.uid() = id);

-- POSTS policies
CREATE POLICY "누구나 게시물 읽기 가능" ON posts FOR SELECT USING (true);
CREATE POLICY "로그인 사용자 게시물 작성" ON posts FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "본인 게시물 삭제 가능" ON posts FOR DELETE USING (auth.uid() = user_id);

-- POST_IMAGES policies
CREATE POLICY "누구나 이미지 읽기 가능" ON post_images FOR SELECT USING (true);
CREATE POLICY "로그인 사용자 이미지 추가" ON post_images FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);
CREATE POLICY "게시물 소유자 이미지 삭제" ON post_images FOR DELETE USING (
  auth.uid() IN (SELECT user_id FROM posts WHERE id = post_id)
);

-- LIKES policies
CREATE POLICY "누구나 좋아요 읽기 가능" ON likes FOR SELECT USING (true);
CREATE POLICY "로그인 사용자 좋아요 가능" ON likes FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "본인 좋아요 취소 가능" ON likes FOR DELETE USING (auth.uid() = user_id);

-- COMMENTS policies
CREATE POLICY "누구나 댓글 읽기 가능" ON comments FOR SELECT USING (true);
CREATE POLICY "로그인 사용자 댓글 작성" ON comments FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "본인 댓글 삭제 가능" ON comments FOR DELETE USING (auth.uid() = user_id);

-- FOLLOWS policies
CREATE POLICY "누구나 팔로우 읽기 가능" ON follows FOR SELECT USING (true);
CREATE POLICY "로그인 사용자 팔로우 가능" ON follows FOR INSERT WITH CHECK (auth.uid() = follower_id);
CREATE POLICY "팔로우 취소 가능" ON follows FOR DELETE USING (auth.uid() = follower_id);

-- ══════════════════════════════════════════════
--  STORAGE 버킷 설정
--  (SQL이 아닌 Supabase 대시보드에서 설정 필요)
-- ══════════════════════════════════════════════
-- Storage > New Bucket 클릭
-- 이름: posts
-- Public bucket: ✅ 체크
-- File size limit: 50MB
-- Allowed MIME types: image/jpeg, image/png, image/webp, image/gif

-- Storage 정책 (SQL Editor에서 실행)
INSERT INTO storage.buckets (id, name, public) 
VALUES ('posts', 'posts', true)
ON CONFLICT (id) DO NOTHING;

CREATE POLICY "누구나 이미지 보기 가능" ON storage.objects
  FOR SELECT USING (bucket_id = 'posts');

CREATE POLICY "로그인 사용자 업로드 가능" ON storage.objects
  FOR INSERT WITH CHECK (bucket_id = 'posts' AND auth.uid() IS NOT NULL);

CREATE POLICY "본인 파일 삭제 가능" ON storage.objects
  FOR DELETE USING (bucket_id = 'posts' AND auth.uid()::text = (storage.foldername(name))[1]);

-- ══════════════════════════════════════════════
--  관리자 계정 설정
--  (일반 가입 후 아래 SQL로 role 변경)
-- ══════════════════════════════════════════════
-- UPDATE profiles SET role = 'admin' WHERE email = '관리자이메일@example.com';

-- ══════════════════════════════════════════════
--  자동 프로필 생성 트리거
--  (회원가입 시 자동으로 profiles 레코드 생성)
-- ══════════════════════════════════════════════
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, username, email, full_name)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'username', SPLIT_PART(NEW.email, '@', 1)),
    NEW.email,
    COALESCE(NEW.raw_user_meta_data->>'username', SPLIT_PART(NEW.email, '@', 1))
  )
  ON CONFLICT (id) DO NOTHING;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

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();
