-- Messaging system for RateRight v2
-- Creates tables for conversations and messages

-- Conversations table to group messages between users
CREATE TABLE conversations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Conversation participants
CREATE TABLE conversation_participants (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id uuid NOT NULL REFERENCES conversations ON DELETE CASCADE,
  profile_id uuid NOT NULL REFERENCES profiles ON DELETE CASCADE,
  last_read_at timestamptz,
  created_at timestamptz DEFAULT now(),
  UNIQUE (conversation_id, profile_id)
);

-- Messages table
CREATE TABLE messages (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id uuid NOT NULL REFERENCES conversations ON DELETE CASCADE,
  sender_id uuid NOT NULL REFERENCES profiles ON DELETE CASCADE,
  content text NOT NULL,
  attachments jsonb DEFAULT '[]',
  status text DEFAULT 'sent' CHECK (status IN ('sent', 'delivered', 'read')),
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Indexes for performance
CREATE INDEX idx_conversation_participants_conversation_id ON conversation_participants (conversation_id);
CREATE INDEX idx_conversation_participants_profile_id ON conversation_participants (profile_id);
CREATE INDEX idx_messages_conversation_id ON messages (conversation_id);
CREATE INDEX idx_messages_sender_id ON messages (sender_id);
CREATE INDEX idx_messages_created_at ON messages (created_at DESC);

-- Row Level Security
ALTER TABLE conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE conversation_participants ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- Conversations: participants can view their conversations
CREATE POLICY "Users can view their own conversations"
  ON conversations FOR SELECT
  USING (
    id IN (
      SELECT conversation_id FROM conversation_participants
      WHERE profile_id = auth.uid()
    )
  );

-- Conversation participants: participants can view participants of their conversations
CREATE POLICY "Users can view participants of their conversations"
  ON conversation_participants FOR SELECT
  USING (
    conversation_id IN (
      SELECT conversation_id FROM conversation_participants
      WHERE profile_id = auth.uid()
    )
  );

CREATE POLICY "Users can insert conversation participants"
  ON conversation_participants FOR INSERT
  WITH CHECK (profile_id = auth.uid());

CREATE POLICY "Users can update their own participant record"
  ON conversation_participants FOR UPDATE
  USING (profile_id = auth.uid());

-- Messages: participants can view messages in their conversations
CREATE POLICY "Users can view messages in their conversations"
  ON messages FOR SELECT
  USING (
    conversation_id IN (
      SELECT conversation_id FROM conversation_participants
      WHERE profile_id = auth.uid()
    )
  );

CREATE POLICY "Users can send messages in their conversations"
  ON messages FOR INSERT
  WITH CHECK (
    sender_id = auth.uid() AND
    conversation_id IN (
      SELECT conversation_id FROM conversation_participants
      WHERE profile_id = auth.uid()
    )
  );

CREATE POLICY "Users can update their own messages"
  ON messages FOR UPDATE
  USING (sender_id = auth.uid());

-- Update updated_at trigger for messages
CREATE TRIGGER messages_updated_at
  BEFORE UPDATE ON messages
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Function to get conversation preview data
CREATE OR REPLACE FUNCTION get_conversation_previews(user_id uuid)
RETURNS TABLE (
  conversation_id uuid,
  other_participant_name text,
  other_participant_avatar text,
  last_message text,
  last_message_time timestamptz,
  unread_count bigint,
  other_participant_is_online boolean
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    c.id as conversation_id,
    p.name as other_participant_name,
    p.avatar_url as other_participant_avatar,
    last_msg.content as last_message,
    last_msg.created_at as last_message_time,
    COALESCE(unread.unread_count, 0) as unread_count,
    true as other_participant_is_online -- TODO: Implement online status
  FROM conversations c
  INNER JOIN conversation_participants cp ON c.id = cp.conversation_id
  INNER JOIN conversation_participants other_cp ON c.id = other_cp.conversation_id
  INNER JOIN profiles p ON other_cp.profile_id = p.id
  LEFT JOIN LATERAL (
    SELECT content, created_at
    FROM messages
    WHERE conversation_id = c.id
    ORDER BY created_at DESC
    LIMIT 1
  ) last_msg ON true
  LEFT JOIN LATERAL (
    SELECT COUNT(*) as unread_count
    FROM messages
    WHERE conversation_id = c.id
      AND sender_id != user_id
      AND created_at > cp.last_read_at
  ) unread ON true
  WHERE cp.profile_id = user_id
    AND other_cp.profile_id != user_id
  ORDER BY last_msg.created_at DESC NULLS LAST;
END;
$$ LANGUAGE plpgsql STABLE;

-- Function to create or get existing conversation between two users
CREATE OR REPLACE FUNCTION get_or_create_conversation(user1_id uuid, user2_id uuid)
RETURNS uuid AS $$
DECLARE
  conversation_uuid uuid;
BEGIN
  -- Check if conversation already exists
  SELECT c.id INTO conversation_uuid
  FROM conversations c
  INNER JOIN conversation_participants cp1 ON c.id = cp1.conversation_id
  INNER JOIN conversation_participants cp2 ON c.id = cp2.conversation_id
  WHERE cp1.profile_id = user1_id
    AND cp2.profile_id = user2_id
  LIMIT 1;

  -- If conversation doesn't exist, create it
  IF conversation_uuid IS NULL THEN
    INSERT INTO conversations DEFAULT VALUES
    RETURNING id INTO conversation_uuid;

    INSERT INTO conversation_participants (conversation_id, profile_id)
    VALUES (conversation_uuid, user1_id), (conversation_uuid, user2_id);
  END IF;

  RETURN conversation_uuid;
END;
$$ LANGUAGE plpgsql;