Supabase SQL 가이드 - PostgreSQL로 백엔드 구축하기

-- ✅ Supabase 방식 - SQL 함수로 한 번에 해결
CREATE OR REPLACE FUNCTION like_post(post_id UUID)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  -- 인증은 RLS가 자동으로 처리
  -- 중복 좋아요 방지
  INSERT INTO likes (user_id, post_id)
  VALUES (auth.uid(), post_id)
  ON CONFLICT DO NOTHING;

  -- 좋아요 수 자동 업데이트
  UPDATE posts
  SET likes_count = (SELECT COUNT(*) FROM likes WHERE likes.post_id = post_id)
  WHERE id = post_id;
END;
$$;

-- ✅ Supabase RLS - 정책을 한 번만 정의
CREATE POLICY "Users can read public posts or their own posts"
ON posts
FOR SELECT
USING (
  is_private = false
  OR author_id = auth.uid()
);

-- ✅ 테이블만 생성하면 끝
CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  post_id UUID REFERENCES posts(id),
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Realtime 활성화
ALTER TABLE comments REPLICA IDENTITY FULL;

-- 사용자 프로필 테이블
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  username TEXT UNIQUE NOT NULL,
  avatar_url TEXT,
  bio TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 게시글 테이블
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  author_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  is_private BOOLEAN DEFAULT false,
  likes_count INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 좋아요 테이블
CREATE TABLE likes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  -- 사용자당 게시글 하나에 좋아요 한 번만
  UNIQUE(user_id, post_id)
);

-- 댓글 테이블
CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE NOT NULL,
  author_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 게시글 작성자로 자주 검색하는 경우
CREATE INDEX posts_author_id_idx ON posts(author_id);

-- 게시글의 댓글을 자주 가져오는 경우
CREATE INDEX comments_post_id_idx ON comments(post_id);

-- 좋아요 조회 성능 향상
CREATE INDEX likes_post_id_idx ON likes(post_id);
CREATE INDEX likes_user_id_idx ON likes(user_id);

-- 최신 게시글 정렬 성능 향상
CREATE INDEX posts_created_at_idx ON posts(created_at DESC);

-- 트리거 함수 생성
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 각 테이블에 트리거 적용
CREATE TRIGGER update_profiles_updated_at
  BEFORE UPDATE ON profiles
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_posts_updated_at
  BEFORE UPDATE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_comments_updated_at
  BEFORE UPDATE ON comments
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- 모든 테이블에 RLS 활성화
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE likes ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;

-- 모든 사람이 프로필 조회 가능
CREATE POLICY "Anyone can view profiles"
ON profiles
FOR SELECT
USING (true);

-- 본인 프로필만 생성 가능
CREATE POLICY "Users can create their own profile"
ON profiles
FOR INSERT
WITH CHECK (auth.uid() = id);

-- 본인 프로필만 수정 가능
CREATE POLICY "Users can update their own profile"
ON profiles
FOR UPDATE
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);

-- 본인 프로필만 삭제 가능
CREATE POLICY "Users can delete their own profile"
ON profiles
FOR DELETE
USING (auth.uid() = id);

-- 공개 게시글 또는 본인 게시글만 조회 가능
CREATE POLICY "Users can view public posts or their own posts"
ON posts
FOR SELECT
USING (
  is_private = false
  OR author_id = auth.uid()
);

-- 로그인한 사용자만 게시글 작성 가능
CREATE POLICY "Authenticated users can create posts"
ON posts
FOR INSERT
WITH CHECK (
  auth.uid() IS NOT NULL
  AND auth.uid() = author_id
);

-- 본인 게시글만 수정 가능
CREATE POLICY "Users can update their own posts"
ON posts
FOR UPDATE
USING (auth.uid() = author_id)
WITH CHECK (auth.uid() = author_id);

-- 본인 게시글만 삭제 가능
CREATE POLICY "Users can delete their own posts"
ON posts
FOR DELETE
USING (auth.uid() = author_id);

-- 모든 사람이 좋아요 조회 가능
CREATE POLICY "Anyone can view likes"
ON likes
FOR SELECT
USING (true);

-- 로그인한 사용자만 좋아요 추가 가능
CREATE POLICY "Authenticated users can create likes"
ON likes
FOR INSERT
WITH CHECK (
  auth.uid() IS NOT NULL
  AND auth.uid() = user_id
);

-- 본인 좋아요만 삭제 가능
CREATE POLICY "Users can delete their own likes"
ON likes
FOR DELETE
USING (auth.uid() = user_id);

-- 모든 사람이 댓글 조회 가능
CREATE POLICY "Anyone can view comments"
ON comments
FOR SELECT
USING (true);

-- 로그인한 사용자만 댓글 작성 가능
CREATE POLICY "Authenticated users can create comments"
ON comments
FOR INSERT
WITH CHECK (
  auth.uid() IS NOT NULL
  AND auth.uid() = author_id
);

-- 본인 댓글만 수정/삭제 가능
CREATE POLICY "Users can update their own comments"
ON comments
FOR UPDATE
USING (auth.uid() = author_id)
WITH CHECK (auth.uid() = author_id);

CREATE POLICY "Users can delete their own comments"
ON comments
FOR DELETE
USING (auth.uid() = author_id);

-- 역할 테이블 추가
CREATE TABLE user_roles (
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('admin', 'moderator', 'user')),
  PRIMARY KEY (user_id, role)
);

-- 관리자용 헬퍼 함수
CREATE OR REPLACE FUNCTION is_admin()
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid()
    AND role = 'admin'
  );
$$ LANGUAGE sql SECURITY DEFINER;

-- 관리자는 모든 게시글 수정 가능
CREATE POLICY "Admins can update any post"
ON posts
FOR UPDATE
USING (is_admin())
WITH CHECK (is_admin());

-- 작성 후 1시간 이내만 수정 가능
CREATE POLICY "Users can update posts within 1 hour"
ON posts
FOR UPDATE
USING (
  auth.uid() = author_id
  AND created_at > NOW() - INTERVAL '1 hour'
)
WITH CHECK (
  auth.uid() = author_id
  AND created_at > NOW() - INTERVAL '1 hour'
);

-- 팔로우한 사용자의 private 게시글도 볼 수 있게
CREATE TABLE follows (
  follower_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  following_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (follower_id, following_id)
);

CREATE POLICY "Users can view posts from people they follow"
ON posts
FOR SELECT
USING (
  is_private = false
  OR author_id = auth.uid()
  OR EXISTS (
    SELECT 1 FROM follows
    WHERE follower_id = auth.uid()
    AND following_id = author_id
  )
);

-- 게시글 좋아요 토글 함수
CREATE OR REPLACE FUNCTION toggle_like(post_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  like_exists BOOLEAN;
BEGIN
  -- 좋아요가 이미 있는지 확인
  SELECT EXISTS (
    SELECT 1 FROM likes
    WHERE user_id = auth.uid()
    AND likes.post_id = toggle_like.post_id
  ) INTO like_exists;

  IF like_exists THEN
    -- 좋아요 취소
    DELETE FROM likes
    WHERE user_id = auth.uid()
    AND likes.post_id = toggle_like.post_id;

    -- 좋아요 수 감소
    UPDATE posts
    SET likes_count = likes_count - 1
    WHERE id = toggle_like.post_id;

    RETURN false;
  ELSE
    -- 좋아요 추가
    INSERT INTO likes (user_id, post_id)
    VALUES (auth.uid(), toggle_like.post_id);

    -- 좋아요 수 증가
    UPDATE posts
    SET likes_count = likes_count + 1
    WHERE id = toggle_like.post_id;

    RETURN true;
  END IF;
END;
$$;

-- 인기 게시글 가져오기 (좋아요 + 댓글 수 기반)
CREATE OR REPLACE FUNCTION get_trending_posts(
  days_ago INTEGER DEFAULT 7,
  limit_count INTEGER DEFAULT 10
)
RETURNS TABLE (
  id UUID,
  title TEXT,
  author_username TEXT,
  likes_count INTEGER,
  comments_count BIGINT,
  score NUMERIC,
  created_at TIMESTAMPTZ
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY
  SELECT
    p.id,
    p.title,
    pr.username AS author_username,
    p.likes_count,
    COUNT(c.id) AS comments_count,
    -- 가중치: 좋아요 1점, 댓글 2점
    (p.likes_count + COUNT(c.id) * 2)::NUMERIC AS score,
    p.created_at
  FROM posts p
  JOIN profiles pr ON p.author_id = pr.id
  LEFT JOIN comments c ON p.id = c.post_id
  WHERE
    p.created_at > NOW() - (days_ago || ' days')::INTERVAL
    AND p.is_private = false
  GROUP BY p.id, pr.username
  ORDER BY score DESC
  LIMIT limit_count;
END;
$$;

-- 게시글 공유 (원본 게시글 ID를 참조하는 새 게시글 생성)
CREATE OR REPLACE FUNCTION share_post(
  original_post_id UUID,
  share_comment TEXT DEFAULT NULL
)
RETURNS UUID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  new_post_id UUID;
  original_author_id UUID;
BEGIN
  -- 원본 게시글 존재 확인
  SELECT author_id INTO original_author_id
  FROM posts
  WHERE id = original_post_id;

  IF original_author_id IS NULL THEN
    RAISE EXCEPTION 'Post not found';
  END IF;

  -- 자기 게시글은 공유 불가
  IF original_author_id = auth.uid() THEN
    RAISE EXCEPTION 'Cannot share your own post';
  END IF;

  -- 새 게시글 생성
  INSERT INTO posts (author_id, title, content, original_post_id)
  SELECT
    auth.uid(),
    'Shared: ' || title,
    COALESCE(share_comment, '') || E'\n\n---\n' || content,
    original_post_id
  FROM posts
  WHERE id = original_post_id
  RETURNING id INTO new_post_id;

  -- 공유 알림 생성 (notifications 테이블이 있다면)
  -- INSERT INTO notifications (user_id, type, post_id) ...

  RETURN new_post_id;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;
$$;

-- 회원가입 시 자동으로 프로필 생성
CREATE OR REPLACE FUNCTION create_profile_for_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO profiles (id, username)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'username', 'user_' || substr(NEW.id::text, 1, 8))
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION create_profile_for_user();

-- 댓글 추가/삭제 시 게시글의 댓글 수 자동 업데이트
ALTER TABLE posts ADD COLUMN comments_count INTEGER DEFAULT 0;

CREATE OR REPLACE FUNCTION update_post_comments_count()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts
    SET comments_count = comments_count + 1
    WHERE id = NEW.post_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE posts
    SET comments_count = comments_count - 1
    WHERE id = OLD.post_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_comment_created
  AFTER INSERT ON comments
  FOR EACH ROW
  EXECUTE FUNCTION update_post_comments_count();

CREATE TRIGGER on_comment_deleted
  AFTER DELETE ON comments
  FOR EACH ROW
  EXECUTE FUNCTION update_post_comments_count();

-- 알림 테이블
CREATE TABLE notifications (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  type TEXT NOT NULL,
  message TEXT NOT NULL,
  read BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 댓글 작성 시 게시글 작성자에게 알림
CREATE OR REPLACE FUNCTION notify_post_author_on_comment()
RETURNS TRIGGER AS $$
DECLARE
  post_author_id UUID;
BEGIN
  -- 게시글 작성자 ID 가져오기
  SELECT author_id INTO post_author_id
  FROM posts
  WHERE id = NEW.post_id;

  -- 자기 댓글은 알림 안 함
  IF post_author_id != NEW.author_id THEN
    INSERT INTO notifications (user_id, type, message)
    VALUES (
      post_author_id,
      'new_comment',
      '새로운 댓글이 달렸습니다.'
    );
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_comment_created_notify
  AFTER INSERT ON comments
  FOR EACH ROW
  EXECUTE FUNCTION notify_post_author_on_comment();

-- 테이블에 실시간 기능 활성화
ALTER TABLE posts REPLICA IDENTITY FULL;
ALTER TABLE comments REPLICA IDENTITY FULL;
ALTER TABLE likes REPLICA IDENTITY FULL;

-- UUID 확장 활성화
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 프로필
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  username TEXT UNIQUE NOT NULL,
  avatar_url TEXT,
  bio TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 태그
CREATE TABLE tags (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT UNIQUE NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 게시글
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  author_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  title TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  content TEXT NOT NULL,
  excerpt TEXT,
  cover_image TEXT,
  is_published BOOLEAN DEFAULT false,
  is_private BOOLEAN DEFAULT false,
  views_count INTEGER DEFAULT 0,
  likes_count INTEGER DEFAULT 0,
  comments_count INTEGER DEFAULT 0,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 게시글-태그 관계 (다대다)
CREATE TABLE post_tags (
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
  tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

-- 댓글
CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE NOT NULL,
  author_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  parent_id UUID REFERENCES comments(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 좋아요
CREATE TABLE likes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, post_id)
);

-- 조회수 (IP 기반 중복 방지)
CREATE TABLE post_views (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE NOT NULL,
  viewer_ip INET,
  user_id UUID REFERENCES profiles(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 성능 최적화를 위한 인덱스
CREATE INDEX posts_author_id_idx ON posts(author_id);
CREATE INDEX posts_slug_idx ON posts(slug);
CREATE INDEX posts_published_at_idx ON posts(published_at DESC) WHERE is_published = true;
CREATE INDEX comments_post_id_idx ON comments(post_id);
CREATE INDEX comments_parent_id_idx ON comments(parent_id);
CREATE INDEX post_tags_post_id_idx ON post_tags(post_id);
CREATE INDEX post_tags_tag_id_idx ON post_tags(tag_id);
CREATE INDEX post_views_post_id_idx ON post_views(post_id);

-- Full-text search를 위한 인덱스
ALTER TABLE posts ADD COLUMN search_vector tsvector;

CREATE INDEX posts_search_idx ON posts USING GIN(search_vector);

-- 검색 벡터 자동 업데이트
CREATE OR REPLACE FUNCTION update_post_search_vector()
RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector =
    setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.excerpt, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_search_update
  BEFORE INSERT OR UPDATE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION update_post_search_vector();

-- RLS 활성화
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE tags ENABLE ROW LEVEL SECURITY;
ALTER TABLE post_tags ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;
ALTER TABLE likes ENABLE ROW LEVEL SECURITY;
ALTER TABLE post_views ENABLE ROW LEVEL SECURITY;

-- 프로필 정책
CREATE POLICY "Public profiles are viewable by everyone"
ON profiles FOR SELECT USING (true);

CREATE POLICY "Users can update own profile"
ON profiles FOR UPDATE
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);

-- 게시글 정책
CREATE POLICY "Published posts are viewable by everyone"
ON posts FOR SELECT
USING (
  is_published = true
  AND is_private = false
  OR author_id = auth.uid()
);

CREATE POLICY "Authenticated users can create posts"
ON posts FOR INSERT
WITH CHECK (auth.uid() IS NOT NULL AND auth.uid() = author_id);

CREATE POLICY "Authors can update own posts"
ON posts FOR UPDATE
USING (auth.uid() = author_id)
WITH CHECK (auth.uid() = author_id);

CREATE POLICY "Authors can delete own posts"
ON posts FOR DELETE
USING (auth.uid() = author_id);

-- 태그 정책
CREATE POLICY "Tags are viewable by everyone"
ON tags FOR SELECT USING (true);

CREATE POLICY "Authenticated users can create tags"
ON tags FOR INSERT
WITH CHECK (auth.uid() IS NOT NULL);

-- 게시글-태그 정책
CREATE POLICY "Post tags are viewable by everyone"
ON post_tags FOR SELECT USING (true);

CREATE POLICY "Authors can manage post tags"
ON post_tags FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM posts
    WHERE posts.id = post_tags.post_id
    AND posts.author_id = auth.uid()
  )
);

-- 댓글 정책
CREATE POLICY "Comments are viewable by everyone"
ON comments FOR SELECT USING (true);

CREATE POLICY "Authenticated users can create comments"
ON comments FOR INSERT
WITH CHECK (auth.uid() IS NOT NULL AND auth.uid() = author_id);

CREATE POLICY "Authors can update own comments"
ON comments FOR UPDATE
USING (auth.uid() = author_id)
WITH CHECK (auth.uid() = author_id);

CREATE POLICY "Authors can delete own comments"
ON comments FOR DELETE
USING (auth.uid() = author_id);

-- 좋아요 정책
CREATE POLICY "Likes are viewable by everyone"
ON likes FOR SELECT USING (true);

CREATE POLICY "Authenticated users can like"
ON likes FOR INSERT
WITH CHECK (auth.uid() IS NOT NULL AND auth.uid() = user_id);

CREATE POLICY "Users can unlike"
ON likes FOR DELETE
USING (auth.uid() = user_id);

-- 조회수 정책
CREATE POLICY "Anyone can create views"
ON post_views FOR INSERT
WITH CHECK (true);

-- 게시글 조회 (조회수 증가)
CREATE OR REPLACE FUNCTION get_post_by_slug(post_slug TEXT, viewer_ip INET DEFAULT NULL)
RETURNS TABLE (
  id UUID,
  author_id UUID,
  author_username TEXT,
  author_avatar_url TEXT,
  title TEXT,
  slug TEXT,
  content TEXT,
  excerpt TEXT,
  cover_image TEXT,
  views_count INTEGER,
  likes_count INTEGER,
  comments_count INTEGER,
  is_liked BOOLEAN,
  tags JSON,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ,
  updated_at TIMESTAMPTZ
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  post_id UUID;
  user_id UUID := auth.uid();
BEGIN
  -- 게시글 ID 가져오기
  SELECT p.id INTO post_id
  FROM posts p
  WHERE p.slug = post_slug;

  IF post_id IS NULL THEN
    RAISE EXCEPTION 'Post not found';
  END IF;

  -- 조회수 증가 (중복 방지)
  IF viewer_ip IS NOT NULL THEN
    INSERT INTO post_views (post_id, viewer_ip, user_id)
    VALUES (post_id, viewer_ip, user_id)
    ON CONFLICT DO NOTHING;

    UPDATE posts SET views_count = (
      SELECT COUNT(DISTINCT viewer_ip) FROM post_views WHERE post_views.post_id = post_id
    )
    WHERE posts.id = post_id;
  END IF;

  -- 게시글 정보 반환
  RETURN QUERY
  SELECT
    p.id,
    p.author_id,
    pr.username AS author_username,
    pr.avatar_url AS author_avatar_url,
    p.title,
    p.slug,
    p.content,
    p.excerpt,
    p.cover_image,
    p.views_count,
    p.likes_count,
    p.comments_count,
    EXISTS(SELECT 1 FROM likes WHERE likes.post_id = p.id AND likes.user_id = user_id) AS is_liked,
    COALESCE(
      (
        SELECT json_agg(json_build_object('id', t.id, 'name', t.name, 'slug', t.slug))
        FROM tags t
        JOIN post_tags pt ON pt.tag_id = t.id
        WHERE pt.post_id = p.id
      ),
      '[]'::json
    ) AS tags,
    p.published_at,
    p.created_at,
    p.updated_at
  FROM posts p
  JOIN profiles pr ON p.author_id = pr.id
  WHERE p.id = post_id;
END;
$$;

-- 게시글 검색
CREATE OR REPLACE FUNCTION search_posts(
  search_query TEXT,
  limit_count INTEGER DEFAULT 10,
  offset_count INTEGER DEFAULT 0
)
RETURNS TABLE (
  id UUID,
  title TEXT,
  slug TEXT,
  excerpt TEXT,
  author_username TEXT,
  published_at TIMESTAMPTZ,
  rank REAL
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    p.id,
    p.title,
    p.slug,
    p.excerpt,
    pr.username AS author_username,
    p.published_at,
    ts_rank(p.search_vector, plainto_tsquery('english', search_query)) AS rank
  FROM posts p
  JOIN profiles pr ON p.author_id = pr.id
  WHERE
    p.is_published = true
    AND p.is_private = false
    AND p.search_vector @@ plainto_tsquery('english', search_query)
  ORDER BY rank DESC, p.published_at DESC
  LIMIT limit_count
  OFFSET offset_count;
END;
$$;

-- 태그별 게시글 가져오기
CREATE OR REPLACE FUNCTION get_posts_by_tag(
  tag_slug TEXT,
  limit_count INTEGER DEFAULT 10,
  offset_count INTEGER DEFAULT 0
)
RETURNS TABLE (
  id UUID,
  title TEXT,
  slug TEXT,
  excerpt TEXT,
  author_username TEXT,
  published_at TIMESTAMPTZ
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    p.id,
    p.title,
    p.slug,
    p.excerpt,
    pr.username AS author_username,
    p.published_at
  FROM posts p
  JOIN profiles pr ON p.author_id = pr.id
  JOIN post_tags pt ON p.id = pt.post_id
  JOIN tags t ON pt.tag_id = t.id
  WHERE
    t.slug = tag_slug
    AND p.is_published = true
    AND p.is_private = false
  ORDER BY p.published_at DESC
  LIMIT limit_count
  OFFSET offset_count;
END;
$$;

-- ❌ 나쁨: 필요 없는 데이터까지 모두 가져옴
SELECT * FROM posts;

-- ✅ 좋음: 필요한 컬럼만 선택
SELECT id, title, excerpt, published_at FROM posts;

-- ❌ 나쁨: N+1 쿼리 문제
-- 각 게시글마다 별도 쿼리 실행
SELECT * FROM posts;
-- 그 다음 각 author_id로 프로필 조회...

-- ✅ 좋음: 한 번에 JOIN
SELECT
  p.*,
  pr.username,
  pr.avatar_url
FROM posts p
JOIN profiles pr ON p.author_id = pr.id;

-- ❌ 나쁨: 인덱스가 없는 컬럼으로 검색
SELECT * FROM posts WHERE title LIKE '%javascript%';

-- ✅ 좋음: Full-text search 사용
SELECT * FROM posts
WHERE search_vector @@ to_tsquery('english', 'javascript');

-- ❌ 실수
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
-- 정책을 추가하지 않아서 아무것도 조회 안 됨!

-- ✅ 해결
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Enable read access for all users"
ON posts FOR SELECT
USING (true);

-- ❌ 위험: 모든 사용자가 RLS 우회 가능
CREATE FUNCTION get_all_posts()
RETURNS SETOF posts
LANGUAGE sql
SECURITY DEFINER -- 위험!
AS $$
  SELECT * FROM posts; -- RLS 무시됨
$$;

-- ✅ 안전: SECURITY INVOKER 사용 (기본값)
CREATE FUNCTION get_all_posts()
RETURNS SETOF posts
LANGUAGE sql
SECURITY INVOKER -- 호출자의 권한으로 실행
AS $$
  SELECT * FROM posts; -- RLS 적용됨
$$;

-- ❌ 위험: 사용자 삭제 시 모든 게시글 삭제
CREATE TABLE posts (
  id UUID PRIMARY KEY,
  author_id UUID REFERENCES profiles(id) ON DELETE CASCADE
  -- 사용자가 탈퇴하면 모든 게시글 삭제됨!
);

-- ✅ 더 나음: SET NULL로 게시글 보존
CREATE TABLE posts (
  id UUID PRIMARY KEY,
  author_id UUID REFERENCES profiles(id) ON DELETE SET NULL
  -- 사용자가 탈퇴해도 게시글은 남음 (익명 처리)
);

-- 또는 소프트 삭제
ALTER TABLE profiles ADD COLUMN deleted_at TIMESTAMPTZ;
-- 실제로 삭제하지 않고 deleted_at만 설정

-- ❌ 위험: 무한 루프 발생
CREATE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  -- 여기서 UPDATE를 실행하면 트리거가 다시 호출됨!
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_posts_timestamp
  BEFORE UPDATE ON posts -- ✅ BEFORE 사용
  FOR EACH ROW
  EXECUTE FUNCTION update_timestamp();

-- ✅ 안전: BEFORE 트리거에서 NEW 수정
-- AFTER 트리거에서는 별도 테이블 업데이트