Supabase Project: Real-time Chat Application

-- User profiles
create table profiles (
  id uuid references auth.users on delete cascade primary key,
  username text unique not null,
  full_name text,
  avatar_url text,
  status text default 'offline' check (status in ('online', 'away', 'busy', 'offline')),
  last_seen timestamp with time zone default now(),
  created_at timestamp with time zone default now()
);

-- Chat rooms/channels
create table rooms (
  id uuid default gen_random_uuid() primary key,
  name text not null,
  description text,
  type text default 'group' check (type in ('direct', 'group', 'channel')),
  created_by uuid references profiles(id) on delete set null,
  created_at timestamp with time zone default now()
);

-- Room members
create table room_members (
  room_id uuid references rooms(id) on delete cascade,
  user_id uuid references profiles(id) on delete cascade,
  role text default 'member' check (role in ('admin', 'moderator', 'member')),
  joined_at timestamp with time zone default now(),
  last_read_at timestamp with time zone default now(),
  primary key (room_id, user_id)
);

-- Messages
create table messages (
  id uuid default gen_random_uuid() primary key,
  room_id uuid references rooms(id) on delete cascade not null,
  user_id uuid references profiles(id) on delete cascade not null,
  content text,
  type text default 'text' check (type in ('text', 'image', 'file', 'system')),
  file_url text,
  file_name text,
  file_size int,
  parent_id uuid references messages(id) on delete cascade, -- For threads/replies
  edited_at timestamp with time zone,
  created_at timestamp with time zone default now()
);

-- Message reactions
create table message_reactions (
  message_id uuid references messages(id) on delete cascade,
  user_id uuid references profiles(id) on delete cascade,
  emoji text not null,
  created_at timestamp with time zone default now(),
  primary key (message_id, user_id, emoji)
);

-- Direct message helper view
create view direct_messages as
select
  r.id as room_id,
  r.created_at,
  u1.id as user1_id,
  u1.username as user1_username,
  u1.avatar_url as user1_avatar,
  u2.id as user2_id,
  u2.username as user2_username,
  u2.avatar_url as user2_avatar
from rooms r
join room_members rm1 on rm1.room_id = r.id
join room_members rm2 on rm2.room_id = r.id and rm2.user_id != rm1.user_id
join profiles u1 on u1.id = rm1.user_id
join profiles u2 on u2.id = rm2.user_id
where r.type = 'direct';

-- Indexes for performance
create index idx_messages_room on messages(room_id, created_at desc);
create index idx_messages_user on messages(user_id);
create index idx_room_members_user on room_members(user_id);
create index idx_room_members_room on room_members(room_id);
create index idx_message_reactions_message on message_reactions(message_id);

-- Function to get or create direct message room
create or replace function get_or_create_dm_room(other_user_id uuid)
returns uuid as $$
declare
  room_id uuid;
begin
  -- Try to find existing DM room
  select r.id into room_id
  from rooms r
  join room_members rm1 on rm1.room_id = r.id and rm1.user_id = auth.uid()
  join room_members rm2 on rm2.room_id = r.id and rm2.user_id = other_user_id
  where r.type = 'direct'
  limit 1;

  -- Create new room if doesn't exist
  if room_id is null then
    insert into rooms (type, created_by)
    values ('direct', auth.uid())
    returning id into room_id;

    insert into room_members (room_id, user_id)
    values
      (room_id, auth.uid()),
      (room_id, other_user_id);
  end if;

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

-- Function to update last read timestamp
create or replace function mark_room_as_read(p_room_id uuid)
returns void as $$
begin
  update room_members
  set last_read_at = now()
  where room_id = p_room_id
    and user_id = auth.uid();
end;
$$ language plpgsql security definer;

-- Update last seen on activity
create or replace function update_last_seen()
returns trigger as $$
begin
  update profiles
  set last_seen = now()
  where id = auth.uid();
  return new;
end;
$$ language plpgsql security definer;

create trigger update_last_seen_on_message
  after insert on messages
  for each row
  execute function update_last_seen();

-- Enable RLS
alter table profiles enable row level security;
alter table rooms enable row level security;
alter table room_members enable row level security;
alter table messages enable row level security;
alter table message_reactions enable row level security;

-- Profiles policies
create policy "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);

-- Rooms policies
create policy "Users can view rooms they're members of"
  on rooms for select
  using (
    exists (
      select 1 from room_members
      where room_id = rooms.id
      and user_id = auth.uid()
    )
  );

create policy "Authenticated users can create rooms"
  on rooms for insert
  with check (auth.uid() = created_by);

-- Room members policies
create policy "Users can view members of their rooms"
  on room_members for select
  using (
    exists (
      select 1 from room_members rm
      where rm.room_id = room_members.room_id
      and rm.user_id = auth.uid()
    )
  );

create policy "Room admins can add members"
  on room_members for insert
  with check (
    exists (
      select 1 from room_members
      where room_id = room_members.room_id
      and user_id = auth.uid()
      and role in ('admin', 'moderator')
    )
  );

-- Messages policies
create policy "Users can view messages in their rooms"
  on messages for select
  using (
    exists (
      select 1 from room_members
      where room_id = messages.room_id
      and user_id = auth.uid()
    )
  );

create policy "Users can send messages to their rooms"
  on messages for insert
  with check (
    exists (
      select 1 from room_members
      where room_id = messages.room_id
      and user_id = auth.uid()
    )
    and auth.uid() = user_id
  );

create policy "Users can update own messages"
  on messages for update
  using (auth.uid() = user_id);

create policy "Users can delete own messages"
  on messages for delete
  using (auth.uid() = user_id);

-- Message reactions policies
create policy "Users can view reactions in their rooms"
  on message_reactions for select
  using (
    exists (
      select 1 from messages m
      join room_members rm on rm.room_id = m.room_id
      where m.id = message_reactions.message_id
      and rm.user_id = auth.uid()
    )
  );

create policy "Users can add reactions to messages"
  on message_reactions for insert
  with check (
    exists (
      select 1 from messages m
      join room_members rm on rm.room_id = m.room_id
      where m.id = message_reactions.message_id
      and rm.user_id = auth.uid()
    )
    and auth.uid() = user_id
  );

create policy "Users can remove own reactions"
  on message_reactions for delete
  using (auth.uid() = user_id);

// hooks/useMessages.ts
import { useState, useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'
import type { RealtimeChannel } from '@supabase/supabase-js'

interface Message {
  id: string
  content: string
  user_id: string
  room_id: string
  type: string
  file_url?: string
  created_at: string
  user: {
    username: string
    avatar_url: string
  }
}

export function useMessages(roomId: string) {
  const [messages, setMessages] = useState<Message[]>([])
  const [loading, setLoading] = useState(true)
  const supabase = createClient()

  useEffect(() => {
    if (!roomId) return

    loadMessages()

    // Subscribe to new messages
    const channel = supabase
      .channel(`room:${roomId}`)
      .on(
        'postgres_changes',
        {
          event: 'INSERT',
          schema: 'public',
          table: 'messages',
          filter: `room_id=eq.${roomId}`,
        },
        async (payload) => {
          // Fetch message with user details
          const { data } = await supabase
            .from('messages')
            .select('*, user:profiles(username, avatar_url)')
            .eq('id', payload.new.id)
            .single()

          if (data) {
            setMessages((current) => [...current, data])
          }
        }
      )
      .on(
        'postgres_changes',
        {
          event: 'UPDATE',
          schema: 'public',
          table: 'messages',
          filter: `room_id=eq.${roomId}`,
        },
        (payload) => {
          setMessages((current) =>
            current.map((msg) =>
              msg.id === payload.new.id ? { ...msg, ...payload.new } : msg
            )
          )
        }
      )
      .on(
        'postgres_changes',
        {
          event: 'DELETE',
          schema: 'public',
          table: 'messages',
          filter: `room_id=eq.${roomId}`,
        },
        (payload) => {
          setMessages((current) =>
            current.filter((msg) => msg.id !== payload.old.id)
          )
        }
      )
      .subscribe()

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

  async function loadMessages() {
    const { data } = await supabase
      .from('messages')
      .select('*, user:profiles(username, avatar_url)')
      .eq('room_id', roomId)
      .order('created_at', { ascending: true })
      .limit(100)

    if (data) {
      setMessages(data)
    }
    setLoading(false)
  }

  async function sendMessage(content: string, type = 'text', fileUrl?: string) {
    const { data: { user } } = await supabase.auth.getUser()
    if (!user) return

    const { error } = await supabase.from('messages').insert({
      room_id: roomId,
      user_id: user.id,
      content,
      type,
      file_url: fileUrl,
    })

    if (error) throw error
  }

  async function updateMessage(messageId: string, content: string) {
    const { error } = await supabase
      .from('messages')
      .update({
        content,
        edited_at: new Date().toISOString(),
      })
      .eq('id', messageId)

    if (error) throw error
  }

  async function deleteMessage(messageId: string) {
    const { error } = await supabase
      .from('messages')
      .delete()
      .eq('id', messageId)

    if (error) throw error
  }

  return {
    messages,
    loading,
    sendMessage,
    updateMessage,
    deleteMessage,
  }
}

// components/MessageList.tsx
import { useEffect, useRef } from 'react'
import { useMessages } from '@/hooks/useMessages'
import { formatDistanceToNow } from 'date-fns'

export function MessageList({ roomId }: { roomId: string }) {
  const { messages, loading } = useMessages(roomId)
  const bottomRef = useRef<HTMLDivElement>(null)

  // Auto-scroll to bottom on new messages
  useEffect(() => {
    bottomRef.current?.scrollIntoView({ behavior: 'smooth' })
  }, [messages])

  if (loading) return <div>Loading messages...</div>

  return (
    <div className="flex-1 overflow-y-auto p-4 space-y-4">
      {messages.map((message) => (
        <div key={message.id} className="flex gap-3">
          <img
            src={message.user.avatar_url || '/default-avatar.png'}
            alt={message.user.username}
            className="w-10 h-10 rounded-full"
          />
          <div className="flex-1">
            <div className="flex items-baseline gap-2">
              <span className="font-semibold">{message.user.username}</span>
              <span className="text-xs text-gray-500">
                {formatDistanceToNow(new Date(message.created_at), { addSuffix: true })}
              </span>
            </div>
            <div className="mt-1">
              {message.type === 'text' && <p>{message.content}</p>}
              {message.type === 'image' && (
                <img
                  src={message.file_url}
                  alt="Attachment"
                  className="max-w-md rounded-lg"
                />
              )}
              {message.type === 'file' && (
                <a
                  href={message.file_url}
                  className="text-blue-600 hover:underline"
                  download
                >
                  📎 {message.file_name}
                </a>
              )}
            </div>
          </div>
        </div>
      ))}
      <div ref={bottomRef} />
    </div>
  )
}

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

interface PresenceState {
  [userId: string]: {
    username: string
    online_at: string
  }[]
}

export function usePresence(roomId: string) {
  const [onlineUsers, setOnlineUsers] = useState<string[]>([])
  const [typingUsers, setTypingUsers] = useState<Set<string>>(new Set())
  const supabase = createClient()

  useEffect(() => {
    if (!roomId) return

    const channel = supabase.channel(`presence:${roomId}`, {
      config: {
        presence: {
          key: roomId,
        },
      },
    })

    // Track online presence
    channel
      .on('presence', { event: 'sync' }, () => {
        const state = channel.presenceState() as PresenceState
        const users = Object.keys(state)
        setOnlineUsers(users)
      })
      .on('presence', { event: 'join' }, ({ key, newPresences }) => {
        console.log('User joined:', key)
      })
      .on('presence', { event: 'leave' }, ({ key, leftPresences }) => {
        console.log('User left:', key)
      })

    // Track typing indicators
    channel.on('broadcast', { event: 'typing' }, ({ payload }) => {
      const { userId, isTyping } = payload
      
      setTypingUsers((current) => {
        const updated = new Set(current)
        if (isTyping) {
          updated.add(userId)
          // Auto-remove after 3 seconds
          setTimeout(() => {
            setTypingUsers((curr) => {
              const next = new Set(curr)
              next.delete(userId)
              return next
            })
          }, 3000)
        } else {
          updated.delete(userId)
        }
        return updated
      })
    })

    // Subscribe and track presence
    channel.subscribe(async (status) => {
      if (status === 'SUBSCRIBED') {
        const { data: { user } } = await supabase.auth.getUser()
        if (user) {
          await channel.track({
            userId: user.id,
            online_at: new Date().toISOString(),
          })
        }
      }
    })

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

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

    const channel = supabase.channel(`presence:${roomId}`)
    await channel.send({
      type: 'broadcast',
      event: 'typing',
      payload: { userId: user.id, isTyping },
    })
  }

  return {
    onlineUsers,
    typingUsers: Array.from(typingUsers),
    broadcastTyping,
  }
}

// components/MessageInput.tsx
import { useState, useRef } from 'react'
import { usePresence } from '@/hooks/usePresence'

export function MessageInput({
  roomId,
  onSend,
}: {
  roomId: string
  onSend: (message: string) => Promise<void>
}) {
  const [message, setMessage] = useState('')
  const [sending, setSending] = useState(false)
  const { broadcastTyping, typingUsers } = usePresence(roomId)
  const typingTimeoutRef = useRef<NodeJS.Timeout>()

  function handleInputChange(value: string) {
    setMessage(value)

    // Broadcast typing indicator
    broadcastTyping(true)

    // Clear previous timeout
    if (typingTimeoutRef.current) {
      clearTimeout(typingTimeoutRef.current)
    }

    // Stop typing indicator after 2 seconds of inactivity
    typingTimeoutRef.current = setTimeout(() => {
      broadcastTyping(false)
    }, 2000)
  }

  async function handleSubmit(e: React.FormEvent) {
    e.preventDefault()
    if (!message.trim()) return

    setSending(true)
    try {
      await onSend(message)
      setMessage('')
      broadcastTyping(false)
    } catch (error) {
      console.error('Error sending message:', error)
    } finally {
      setSending(false)
    }
  }

  return (
    <div className="border-t p-4">
      {/* Typing Indicator */}
      {typingUsers.length > 0 && (
        <div className="text-sm text-gray-500 mb-2">
          {typingUsers.length === 1
            ? `${typingUsers[0]} is typing...`
            : `${typingUsers.length} people are typing...`}
        </div>
      )}

      <form onSubmit={handleSubmit} className="flex gap-2">
        <input
          type="text"
          value={message}
          onChange={(e) => handleInputChange(e.target.value)}
          placeholder="Type a message..."
          className="flex-1 px-4 py-2 border rounded-lg"
          disabled={sending}
        />
        <button
          type="submit"
          disabled={sending || !message.trim()}
          className="px-6 py-2 bg-blue-500 text-white rounded-lg disabled:opacity-50"
        >
          Send
        </button>
      </form>
    </div>
  )
}

// lib/uploadFile.ts
import { createClient } from './supabase/client'

export async function uploadFile(file: File, roomId: string) {
  const supabase = createClient()
  
  // Generate unique filename
  const fileExt = file.name.split('.').pop()
  const fileName = `${Date.now()}-${Math.random().toString(36).substring(7)}.${fileExt}`
  const filePath = `${roomId}/${fileName}`

  // Upload to Supabase Storage
  const { data, error } = await supabase.storage
    .from('chat-attachments')
    .upload(filePath, file, {
      cacheControl: '3600',
      upsert: false,
    })

  if (error) throw error

  // Get public URL
  const { data: { publicUrl } } = supabase.storage
    .from('chat-attachments')
    .getPublicUrl(data.path)

  return {
    url: publicUrl,
    name: file.name,
    size: file.size,
    type: file.type,
  }
}

// components/FileUploadButton.tsx
import { useRef, useState } from 'react'
import { uploadFile } from '@/lib/uploadFile'

export function FileUploadButton({
  roomId,
  onUpload,
}: {
  roomId: string
  onUpload: (fileUrl: string, fileName: string, type: string) => Promise<void>
}) {
  const [uploading, setUploading] = useState(false)
  const inputRef = useRef<HTMLInputElement>(null)

  async function handleFileSelect(e: React.ChangeEvent<HTMLInputElement>) {
    const file = e.target.files?.[0]
    if (!file) return

    // Check file size (max 10MB)
    if (file.size > 10 * 1024 * 1024) {
      alert('File size must be less than 10MB')
      return
    }

    setUploading(true)
    try {
      const { url, name, type } = await uploadFile(file, roomId)
      
      // Determine message type based on file type
      const messageType = type.startsWith('image/') ? 'image' : 'file'
      
      await onUpload(url, name, messageType)
    } catch (error) {
      console.error('Error uploading file:', error)
      alert('Failed to upload file')
    } finally {
      setUploading(false)
      if (inputRef.current) {
        inputRef.current.value = ''
      }
    }
  }

  return (
    <>
      <input
        ref={inputRef}
        type="file"
        onChange={handleFileSelect}
        className="hidden"
        accept="image/*,.pdf,.doc,.docx,.txt"
      />
      <button
        type="button"
        onClick={() => inputRef.current?.click()}
        disabled={uploading}
        className="p-2 hover:bg-gray-100 rounded-lg disabled:opacity-50"
        title="Attach file"
      >
        {uploading ? '⏳' : '📎'}
      </button>
    </>
  )
}

-- Storage bucket setup (run in SQL Editor)
-- Create bucket for chat attachments
insert into storage.buckets (id, name, public)
values ('chat-attachments', 'chat-attachments', true);

-- RLS policy for uploads
create policy "Users can upload files to their rooms"
  on storage.objects for insert
  with check (
    bucket_id = 'chat-attachments' and
    exists (
      select 1 from room_members
      where room_id::text = (storage.foldername(name))[1]
      and user_id = auth.uid()
    )
  );

create policy "Users can view files in their rooms"
  on storage.objects for select
  using (
    bucket_id = 'chat-attachments' and
    exists (
      select 1 from room_members
      where room_id::text = (storage.foldername(name))[1]
      and user_id = auth.uid()
    )
  );

// app/chat/[roomId]/page.tsx
import { MessageList } from '@/components/MessageList'
import { MessageInput } from '@/components/MessageInput'
import { FileUploadButton } from '@/components/FileUploadButton'
import { OnlineUsers } from '@/components/OnlineUsers'
import { useMessages } from '@/hooks/useMessages'

export default function ChatRoomPage({ params }: { params: { roomId: string } }) {
  const { sendMessage } = useMessages(params.roomId)

  async function handleSendMessage(content: string, type = 'text', fileUrl?: string) {
    await sendMessage(content, type, fileUrl)
  }

  async function handleFileUpload(fileUrl: string, fileName: string, type: string) {
    await sendMessage(fileName, type, fileUrl)
  }

  return (
    <div className="flex h-screen">
      {/* Sidebar */}
      <aside className="w-64 bg-gray-100 border-r">
        <div className="p-4">
          <h2 className="text-xl font-bold mb-4">Rooms</h2>
          {/* Room list component */}
        </div>
        <OnlineUsers roomId={params.roomId} />
      </aside>

      {/* Main Chat Area */}
      <main className="flex-1 flex flex-col">
        {/* Chat Header */}
        <header className="border-b p-4">
          <h1 className="text-xl font-semibold">Chat Room</h1>
        </header>

        {/* Messages */}
        <MessageList roomId={params.roomId} />

        {/* Input Area */}
        <div className="flex items-center gap-2 px-4">
          <FileUploadButton
            roomId={params.roomId}
            onUpload={handleFileUpload}
          />
          <div className="flex-1">
            <MessageInput
              roomId={params.roomId}
              onSend={handleSendMessage}
            />
          </div>
        </div>
      </main>
    </div>
  )
}

// components/OnlineUsers.tsx
import { usePresence } from '@/hooks/usePresence'
import { useEffect, useState } from 'react'
import { createClient } from '@/lib/supabase/client'

export function OnlineUsers({ roomId }: { roomId: string }) {
  const { onlineUsers } = usePresence(roomId)
  const [profiles, setProfiles] = useState<any[]>([])
  const supabase = createClient()

  useEffect(() => {
    loadProfiles()
  }, [onlineUsers])

  async function loadProfiles() {
    if (onlineUsers.length === 0) return

    const { data } = await supabase
      .from('profiles')
      .select('id, username, avatar_url, status')
      .in('id', onlineUsers)

    if (data) setProfiles(data)
  }

  return (
    <div className="p-4 border-t">
      <h3 className="font-semibold mb-2">Online ({onlineUsers.length})</h3>
      <div className="space-y-2">
        {profiles.map((profile) => (
          <div key={profile.id} className="flex items-center gap-2">
            <div className="relative">
              <img
                src={profile.avatar_url || '/default-avatar.png'}
                alt={profile.username}
                className="w-8 h-8 rounded-full"
              />
              <div className="absolute bottom-0 right-0 w-3 h-3 bg-green-500 border-2 border-white rounded-full" />
            </div>
            <span className="text-sm">{profile.username}</span>
          </div>
        ))}
      </div>
    </div>
  )
}