Supabase Project: Social Media Platform with Feeds

-- User profiles with stats
create table profiles (
  id uuid references auth.users on delete cascade primary key,
  username text unique not null check (length(username) >= 3),
  full_name text,
  bio text,
  avatar_url text,
  cover_url text,
  website text,
  location text,
  followers_count int default 0,
  following_count int default 0,
  posts_count int default 0,
  is_verified boolean default false,
  is_private boolean default false,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Posts
create table posts (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references profiles(id) on delete cascade not null,
  content text,
  media_urls text[], -- Array of image/video URLs
  media_type text check (media_type in ('image', 'video', 'none')),
  likes_count int default 0,
  comments_count int default 0,
  shares_count int default 0,
  is_edited boolean default false,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Followers/Following relationships
create table follows (
  follower_id uuid references profiles(id) on delete cascade,
  following_id uuid references profiles(id) on delete cascade,
  created_at timestamp with time zone default now(),
  primary key (follower_id, following_id),
  check (follower_id != following_id)
);

-- Post likes
create table post_likes (
  post_id uuid references posts(id) on delete cascade,
  user_id uuid references profiles(id) on delete cascade,
  created_at timestamp with time zone default now(),
  primary key (post_id, user_id)
);

-- Comments
create table comments (
  id uuid default gen_random_uuid() primary key,
  post_id uuid references posts(id) on delete cascade not null,
  user_id uuid references profiles(id) on delete cascade not null,
  content text not null,
  parent_id uuid references comments(id) on delete cascade, -- For nested replies
  likes_count int default 0,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- Comment likes
create table comment_likes (
  comment_id uuid references comments(id) on delete cascade,
  user_id uuid references profiles(id) on delete cascade,
  created_at timestamp with time zone default now(),
  primary key (comment_id, user_id)
);

-- Notifications
create table notifications (
  id uuid default gen_random_uuid() primary key,
  recipient_id uuid references profiles(id) on delete cascade not null,
  sender_id uuid references profiles(id) on delete cascade,
  type text not null check (type in ('like', 'comment', 'follow', 'mention', 'share')),
  post_id uuid references posts(id) on delete cascade,
  comment_id uuid references comments(id) on delete cascade,
  content text,
  is_read boolean default false,
  created_at timestamp with time zone default now()
);

-- Saved posts (bookmarks)
create table saved_posts (
  user_id uuid references profiles(id) on delete cascade,
  post_id uuid references posts(id) on delete cascade,
  created_at timestamp with time zone default now(),
  primary key (user_id, post_id)
);

-- Hashtags
create table hashtags (
  id uuid default gen_random_uuid() primary key,
  name text unique not null,
  posts_count int default 0,
  created_at timestamp with time zone default now()
);

-- Post hashtags junction
create table post_hashtags (
  post_id uuid references posts(id) on delete cascade,
  hashtag_id uuid references hashtags(id) on delete cascade,
  primary key (post_id, hashtag_id)
);

-- Indexes for performance
create index idx_posts_user on posts(user_id, created_at desc);
create index idx_posts_created on posts(created_at desc);
create index idx_follows_follower on follows(follower_id);
create index idx_follows_following on follows(following_id);
create index idx_post_likes_post on post_likes(post_id);
create index idx_post_likes_user on post_likes(user_id);
create index idx_comments_post on comments(post_id, created_at desc);
create index idx_notifications_recipient on notifications(recipient_id, created_at desc);
create index idx_notifications_unread on notifications(recipient_id, is_read, created_at desc);

-- Full-text search on posts
alter table posts add column search_vector tsvector;
create index idx_posts_search on posts using gin(search_vector);

create or replace function posts_search_update()
returns trigger as $$
begin
  new.search_vector := to_tsvector('english', coalesce(new.content, ''));
  return new;
end;
$$ language plpgsql;

create trigger posts_search_trigger
  before insert or update on posts
  for each row execute function posts_search_update();

-- Functions to handle follow/unfollow with counters
create or replace function follow_user(target_user_id uuid)
returns void as $$
begin
  -- Insert follow relationship
  insert into follows (follower_id, following_id)
  values (auth.uid(), target_user_id)
  on conflict do nothing;

  -- Update counters
  update profiles
  set following_count = following_count + 1
  where id = auth.uid();

  update profiles
  set followers_count = followers_count + 1
  where id = target_user_id;

  -- Create notification
  insert into notifications (recipient_id, sender_id, type)
  values (target_user_id, auth.uid(), 'follow');
end;
$$ language plpgsql security definer;

create or replace function unfollow_user(target_user_id uuid)
returns void as $$
begin
  -- Remove follow relationship
  delete from follows
  where follower_id = auth.uid()
    and following_id = target_user_id;

  -- Update counters
  update profiles
  set following_count = greatest(following_count - 1, 0)
  where id = auth.uid();

  update profiles
  set followers_count = greatest(followers_count - 1, 0)
  where id = target_user_id;
end;
$$ language plpgsql security definer;

// hooks/useFollow.ts
import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'

export function useFollow(userId: string) {
  const [isFollowing, setIsFollowing] = useState(false)
  const [loading, setLoading] = useState(false)
  const supabase = createClient()

  useEffect(() => {
    checkFollowStatus()
  }, [userId])

  async function checkFollowStatus() {
    const { data: { user } } = await supabase.auth.getUser()
    if (!user) return

    const { data } = await supabase
      .from('follows')
      .select('*')
      .eq('follower_id', user.id)
      .eq('following_id', userId)
      .single()

    setIsFollowing(!!data)
  }

  async function toggleFollow() {
    setLoading(true)
    try {
      if (isFollowing) {
        await supabase.rpc('unfollow_user', { target_user_id: userId })
        setIsFollowing(false)
      } else {
        await supabase.rpc('follow_user', { target_user_id: userId })
        setIsFollowing(true)
      }
    } catch (error) {
      console.error('Error toggling follow:', error)
    } finally {
      setLoading(false)
    }
  }

  return { isFollowing, loading, toggleFollow }
}

// components/FollowButton.tsx
import { useFollow } from '@/hooks/useFollow'

export function FollowButton({ userId }: { userId: string }) {
  const { isFollowing, loading, toggleFollow } = useFollow(userId)

  return (
    <button
      onClick={toggleFollow}
      disabled={loading}
      className={`px-6 py-2 rounded-lg font-medium disabled:opacity-50 ${
        isFollowing
          ? 'bg-gray-200 text-gray-800 hover:bg-gray-300'
          : 'bg-blue-500 text-white hover:bg-blue-600'
      }`}
    >
      {loading ? 'Loading...' : isFollowing ? 'Following' : 'Follow'}
    </button>
  )
}

-- Function to get personalized feed
create or replace function get_feed(
  page_size int default 20,
  page_offset int default 0
)
returns table (
  post_id uuid,
  user_id uuid,
  username text,
  avatar_url text,
  content text,
  media_urls text[],
  likes_count int,
  comments_count int,
  created_at timestamp with time zone,
  is_liked boolean
) as $$
begin
  return query
  select
    p.id as post_id,
    p.user_id,
    pr.username,
    pr.avatar_url,
    p.content,
    p.media_urls,
    p.likes_count,
    p.comments_count,
    p.created_at,
    exists(
      select 1 from post_likes pl
      where pl.post_id = p.id
      and pl.user_id = auth.uid()
    ) as is_liked
  from posts p
  join profiles pr on pr.id = p.user_id
  where
    -- Show posts from users you follow
    p.user_id in (
      select following_id from follows
      where follower_id = auth.uid()
    )
    -- Or your own posts
    or p.user_id = auth.uid()
  order by p.created_at desc
  limit page_size
  offset page_offset;
end;
$$ language plpgsql security definer;

-- Function for explore/discover feed
create or replace function get_explore_feed(
  page_size int default 20,
  page_offset int default 0
)
returns table (
  post_id uuid,
  user_id uuid,
  username text,
  avatar_url text,
  content text,
  media_urls text[],
  likes_count int,
  comments_count int,
  engagement_score float,
  created_at timestamp with time zone
) as $$
begin
  return query
  select
    p.id as post_id,
    p.user_id,
    pr.username,
    pr.avatar_url,
    p.content,
    p.media_urls,
    p.likes_count,
    p.comments_count,
    -- Simple engagement score
    (p.likes_count * 2.0 + p.comments_count * 3.0) /
    (extract(epoch from (now() - p.created_at)) / 3600 + 2) as engagement_score,
    p.created_at
  from posts p
  join profiles pr on pr.id = p.user_id
  where p.created_at > now() - interval '7 days'
  order by engagement_score desc
  limit page_size
  offset page_offset;
end;
$$ language plpgsql security definer;

// hooks/useFeed.ts
import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'

interface Post {
  post_id: string
  user_id: string
  username: string
  avatar_url: string
  content: string
  media_urls: string[]
  likes_count: number
  comments_count: number
  created_at: string
  is_liked: boolean
}

export function useFeed(feedType: 'home' | 'explore' = 'home') {
  const [posts, setPosts] = useState<Post[]>([])
  const [loading, setLoading] = useState(true)
  const [hasMore, setHasMore] = useState(true)
  const supabase = createClient()

  useEffect(() => {
    loadFeed()

    // Subscribe to new posts
    const channel = supabase
      .channel('feed-updates')
      .on(
        'postgres_changes',
        {
          event: 'INSERT',
          schema: 'public',
          table: 'posts',
        },
        () => loadFeed() // Refresh feed
      )
      .subscribe()

    return () => {
      supabase.removeChannel(channel)
    }
  }, [feedType])

  async function loadFeed() {
    const rpcFunction = feedType === 'home' ? 'get_feed' : 'get_explore_feed'
    const { data, error } = await supabase.rpc(rpcFunction, {
      page_size: 20,
      page_offset: 0,
    })

    if (data) {
      setPosts(data)
      setHasMore(data.length === 20)
    }
    setLoading(false)
  }

  async function loadMore() {
    const rpcFunction = feedType === 'home' ? 'get_feed' : 'get_explore_feed'
    const { data } = await supabase.rpc(rpcFunction, {
      page_size: 20,
      page_offset: posts.length,
    })

    if (data) {
      setPosts([...posts, ...data])
      setHasMore(data.length === 20)
    }
  }

  return { posts, loading, hasMore, loadMore, refresh: loadFeed }
}

// hooks/useNotifications.ts
import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'

interface Notification {
  id: string
  type: string
  sender: {
    username: string
    avatar_url: string
  }
  post_id?: string
  content?: string
  is_read: boolean
  created_at: string
}

export function useNotifications() {
  const [notifications, setNotifications] = useState<Notification[]>([])
  const [unreadCount, setUnreadCount] = useState(0)
  const supabase = createClient()

  useEffect(() => {
    loadNotifications()

    // Subscribe to new notifications
    const channel = supabase
      .channel('notifications')
      .on(
        'postgres_changes',
        {
          event: 'INSERT',
          schema: 'public',
          table: 'notifications',
        },
        async (payload) => {
          const { data: { user } } = await supabase.auth.getUser()
          if (payload.new.recipient_id === user?.id) {
            loadNotifications()
          }
        }
      )
      .subscribe()

    return () => {
      supabase.removeChannel(channel)
    }
  }, [])

  async function loadNotifications() {
    const { data } = await supabase
      .from('notifications')
      .select(`
        *,
        sender:profiles!sender_id(username, avatar_url)
      `)
      .order('created_at', { ascending: false })
      .limit(50)

    if (data) {
      setNotifications(data)
      setUnreadCount(data.filter((n) => !n.is_read).length)
    }
  }

  async function markAsRead(notificationId: string) {
    await supabase
      .from('notifications')
      .update({ is_read: true })
      .eq('id', notificationId)

    setNotifications(
      notifications.map((n) =>
        n.id === notificationId ? { ...n, is_read: true } : n
      )
    )
    setUnreadCount(Math.max(0, unreadCount - 1))
  }

  async function markAllAsRead() {
    const { data: { user } } = await supabase.auth.getUser()
    if (!user) return

    await supabase
      .from('notifications')
      .update({ is_read: true })
      .eq('recipient_id', user.id)
      .eq('is_read', false)

    setNotifications(notifications.map((n) => ({ ...n, is_read: true })))
    setUnreadCount(0)
  }

  return {
    notifications,
    unreadCount,
    markAsRead,
    markAllAsRead,
  }
}

// components/NotificationDropdown.tsx
import { useNotifications } from '@/hooks/useNotifications'
import { formatDistanceToNow } from 'date-fns'

export function NotificationDropdown() {
  const { notifications, unreadCount, markAsRead, markAllAsRead } =
    useNotifications()

  return (
    <div className="absolute right-0 mt-2 w-96 bg-white rounded-lg shadow-lg border">
      <div className="flex items-center justify-between p-4 border-b">
        <h3 className="font-semibold">Notifications</h3>
        {unreadCount > 0 && (
          <button
            onClick={markAllAsRead}
            className="text-sm text-blue-600 hover:underline"
          >
            Mark all as read
          </button>
        )}
      </div>

      <div className="max-h-96 overflow-y-auto">
        {notifications.length === 0 ? (
          <div className="p-8 text-center text-gray-500">
            No notifications yet
          </div>
        ) : (
          notifications.map((notification) => (
            <div
              key={notification.id}
              onClick={() => markAsRead(notification.id)}
              className={`p-4 border-b hover:bg-gray-50 cursor-pointer ${
                !notification.is_read ? 'bg-blue-50' : ''
              }`}
            >
              <div className="flex gap-3">
                <img
                  src={notification.sender.avatar_url || '/default-avatar.png'}
                  alt={notification.sender.username}
                  className="w-10 h-10 rounded-full"
                />
                <div className="flex-1">
                  <p className="text-sm">
                    <strong>{notification.sender.username}</strong>{' '}
                    {getNotificationText(notification.type)}
                  </p>
                  <p className="text-xs text-gray-500 mt-1">
                    {formatDistanceToNow(new Date(notification.created_at), {
                      addSuffix: true,
                    })}
                  </p>
                </div>
                {!notification.is_read && (
                  <div className="w-2 h-2 bg-blue-500 rounded-full" />
                )}
              </div>
            </div>
          ))
        )}
      </div>
    </div>
  )
}

function getNotificationText(type: string): string {
  switch (type) {
    case 'like':
      return 'liked your post'
    case 'comment':
      return 'commented on your post'
    case 'follow':
      return 'started following you'
    case 'mention':
      return 'mentioned you'
    default:
      return 'interacted with your content'
  }
}

-- Function to like/unlike post
create or replace function toggle_post_like(p_post_id uuid)
returns boolean as $$ -- Returns true if liked, false if unliked
declare
  liked boolean;
begin
  -- Check if already liked
  select exists(
    select 1 from post_likes
    where post_id = p_post_id and user_id = auth.uid()
  ) into liked;

  if liked then
    -- Unlike
    delete from post_likes
    where post_id = p_post_id and user_id = auth.uid();

    update posts
    set likes_count = greatest(likes_count - 1, 0)
    where id = p_post_id;

    return false;
  else
    -- Like
    insert into post_likes (post_id, user_id)
    values (p_post_id, auth.uid());

    update posts
    set likes_count = likes_count + 1
    where id = p_post_id;

    -- Create notification
    insert into notifications (recipient_id, sender_id, type, post_id)
    select user_id, auth.uid(), 'like', p_post_id
    from posts
    where id = p_post_id and user_id != auth.uid();

    return true;
  end if;
end;
$$ language plpgsql security definer;

-- Function to add comment
create or replace function add_comment(
  p_post_id uuid,
  p_content text,
  p_parent_id uuid default null
)
returns uuid as $$
declare
  comment_id uuid;
begin
  -- Insert comment
  insert into comments (post_id, user_id, content, parent_id)
  values (p_post_id, auth.uid(), p_content, p_parent_id)
  returning id into comment_id;

  -- Update post comments count
  update posts
  set comments_count = comments_count + 1
  where id = p_post_id;

  -- Create notification
  insert into notifications (recipient_id, sender_id, type, post_id, comment_id)
  select user_id, auth.uid(), 'comment', p_post_id, comment_id
  from posts
  where id = p_post_id and user_id != auth.uid();

  return comment_id;
end;
$$ language plpgsql security definer;

// components/PostCard.tsx
import { useState } from 'react'
import { createClient } from '@/lib/supabase/client'
import { formatDistanceToNow } from 'date-fns'

interface PostCardProps {
  post: {
    post_id: string
    username: string
    avatar_url: string
    content: string
    media_urls: string[]
    likes_count: number
    comments_count: number
    created_at: string
    is_liked: boolean
  }
}

export function PostCard({ post }: PostCardProps) {
  const [isLiked, setIsLiked] = useState(post.is_liked)
  const [likesCount, setLikesCount] = useState(post.likes_count)
  const [showComments, setShowComments] = useState(false)
  const supabase = createClient()

  async function handleLike() {
    // Optimistic update
    setIsLiked(!isLiked)
    setLikesCount(isLiked ? likesCount - 1 : likesCount + 1)

    try {
      const { data } = await supabase.rpc('toggle_post_like', {
        p_post_id: post.post_id,
      })

      // Sync with actual result if needed
      if (data !== !isLiked) {
        setIsLiked(data)
        setLikesCount(isLiked ? likesCount - 1 : likesCount + 1)
      }
    } catch (error) {
      // Revert on error
      setIsLiked(isLiked)
      setLikesCount(likesCount)
      console.error('Error toggling like:', error)
    }
  }

  return (
    <div className="bg-white rounded-lg shadow p-6">
      {/* User Info */}
      <div className="flex items-center gap-3 mb-4">
        <img
          src={post.avatar_url || '/default-avatar.png'}
          alt={post.username}
          className="w-10 h-10 rounded-full"
        />
        <div>
          <p className="font-semibold">{post.username}</p>
          <p className="text-xs text-gray-500">
            {formatDistanceToNow(new Date(post.created_at), { addSuffix: true })}
          </p>
        </div>
      </div>

      {/* Content */}
      <p className="mb-4">{post.content}</p>

      {/* Media */}
      {post.media_urls && post.media_urls.length > 0 && (
        <div className="grid grid-cols-2 gap-2 mb-4">
          {post.media_urls.map((url, index) => (
            <img
              key={index}
              src={url}
              alt="Post media"
              className="w-full rounded-lg"
            />
          ))}
        </div>
      )}

      {/* Actions */}
      <div className="flex items-center gap-6 text-gray-600">
        <button
          onClick={handleLike}
          className={`flex items-center gap-2 ${
            isLiked ? 'text-red-500' : 'hover:text-red-500'
          }`}
        >
          <span>{isLiked ? '❤️' : '🤍'}</span>
          <span>{likesCount}</span>
        </button>

        <button
          onClick={() => setShowComments(!showComments)}
          className="flex items-center gap-2 hover:text-blue-500"
        >
          <span>💬</span>
          <span>{post.comments_count}</span>
        </button>

        <button className="flex items-center gap-2 hover:text-green-500">
          <span>🔄</span>
          <span>Share</span>
        </button>
      </div>

      {/* Comments Section */}
      {showComments && <CommentsSection postId={post.post_id} />}
    </div>
  )
}

// components/CreatePost.tsx
import { useState } from 'react'
import { createClient } from '@/lib/supabase/client'

export function CreatePost({ onPostCreated }: { onPostCreated: () => void }) {
  const [content, setContent] = useState('')
  const [files, setFiles] = useState<File[]>([])
  const [uploading, setUploading] = useState(false)
  const supabase = createClient()

  async function handleSubmit(e: React.FormEvent) {
    e.preventDefault()
    if (!content.trim() && files.length === 0) return

    setUploading(true)
    try {
      const { data: { user } } = await supabase.auth.getUser()
      if (!user) throw new Error('Not authenticated')

      // Upload media files
      const mediaUrls: string[] = []
      for (const file of files) {
        const fileExt = file.name.split('.').pop()
        const fileName = `${user.id}/${Date.now()}-${Math.random()}.${fileExt}`

        const { data, error } = await supabase.storage
          .from('posts')
          .upload(fileName, file)

        if (error) throw error

        const { data: { publicUrl } } = supabase.storage
          .from('posts')
          .getPublicUrl(data.path)

        mediaUrls.push(publicUrl)
      }

      // Determine media type
      const mediaType = files.length > 0
        ? files[0].type.startsWith('video/') ? 'video' : 'image'
        : 'none'

      // Extract hashtags
      const hashtags = content.match(/#\w+/g) || []

      // Create post
      const { data: post, error: postError } = await supabase
        .from('posts')
        .insert({
          user_id: user.id,
          content,
          media_urls: mediaUrls,
          media_type: mediaType,
        })
        .select()
        .single()

      if (postError) throw postError

      // Add hashtags
      for (const tag of hashtags) {
        const tagName = tag.slice(1).toLowerCase()
        
        // Upsert hashtag
        const { data: hashtag } = await supabase
          .from('hashtags')
          .upsert({ name: tagName })
          .select()
          .single()

        if (hashtag) {
          await supabase
            .from('post_hashtags')
            .insert({ post_id: post.id, hashtag_id: hashtag.id })
        }
      }

      // Update posts count
      await supabase.rpc('increment', {
        table_name: 'profiles',
        column_name: 'posts_count',
        row_id: user.id,
      })

      setContent('')
      setFiles([])
      onPostCreated()
    } catch (error) {
      console.error('Error creating post:', error)
      alert('Failed to create post')
    } finally {
      setUploading(false)
    }
  }

  return (
    <form onSubmit={handleSubmit} className="bg-white rounded-lg shadow p-6">
      <textarea
        value={content}
        onChange={(e) => setContent(e.target.value)}
        placeholder="What's on your mind?"
        className="w-full px-4 py-3 border rounded-lg resize-none"
        rows={3}
      />

      {files.length > 0 && (
        <div className="mt-4 grid grid-cols-3 gap-2">
          {Array.from(files).map((file, index) => (
            <div key={index} className="relative">
              <img
                src={URL.createObjectURL(file)}
                alt="Preview"
                className="w-full h-32 object-cover rounded-lg"
              />
              <button
                type="button"
                onClick={() =>
                  setFiles(files.filter((_, i) => i !== index))
                }
                className="absolute top-2 right-2 bg-red-500 text-white rounded-full w-6 h-6"
              >
                ×
              </button>
            </div>
          ))}
        </div>
      )}

      <div className="flex items-center justify-between mt-4">
        <div className="flex gap-2">
          <label className="cursor-pointer px-4 py-2 bg-gray-100 rounded-lg hover:bg-gray-200">
            📷 Photo
            <input
              type="file"
              accept="image/*"
              multiple
              onChange={(e) => setFiles(Array.from(e.target.files || []))}
              className="hidden"
            />
          </label>
        </div>

        <button
          type="submit"
          disabled={uploading || (!content.trim() && files.length === 0)}
          className="px-6 py-2 bg-blue-500 text-white rounded-lg disabled:opacity-50"
        >
          {uploading ? 'Posting...' : 'Post'}
        </button>
      </div>
    </form>
  )
}