AeThex-OS/supabase/migrations/004_calls_system.sql

-- Migration 004: Voice/Video Calls System
-- WebRTC calling infrastructure for 1-on-1 and group calls

-- ============================================================================
-- CALLS TABLE
-- ============================================================================

CREATE TABLE IF NOT EXISTS calls (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  type VARCHAR(20) NOT NULL CHECK (type IN ('voice', 'video')),
  initiator_id UUID NOT NULL REFERENCES auth.users(id),
  status VARCHAR(30) NOT NULL DEFAULT 'ringing' CHECK (status IN ('ringing', 'active', 'ended', 'failed', 'missed')),
  sfu_room_id VARCHAR(100),
  started_at TIMESTAMP WITH TIME ZONE,
  ended_at TIMESTAMP WITH TIME ZONE,
  duration_seconds INTEGER,
  end_reason VARCHAR(50),
  metadata JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_calls_conversation ON calls(conversation_id);
CREATE INDEX IF NOT EXISTS idx_calls_initiator ON calls(initiator_id);
CREATE INDEX IF NOT EXISTS idx_calls_status ON calls(status);
CREATE INDEX IF NOT EXISTS idx_calls_created ON calls(created_at DESC);

-- ============================================================================
-- CALL PARTICIPANTS
-- ============================================================================

CREATE TABLE IF NOT EXISTS call_participants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  call_id UUID NOT NULL REFERENCES calls(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  joined_at TIMESTAMP WITH TIME ZONE,
  left_at TIMESTAMP WITH TIME ZONE,
  media_state JSONB DEFAULT '{"audio": true, "video": false, "screen": false}'::jsonb,
  connection_quality VARCHAR(20) DEFAULT 'unknown' CHECK (connection_quality IN ('excellent', 'good', 'fair', 'poor', 'unknown')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(call_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_call_participants_call ON call_participants(call_id);
CREATE INDEX IF NOT EXISTS idx_call_participants_user ON call_participants(user_id);

-- ============================================================================
-- TURN CREDENTIALS (for NAT traversal)
-- ============================================================================

CREATE TABLE IF NOT EXISTS turn_credentials (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  username VARCHAR(200) NOT NULL,
  credential VARCHAR(500) NOT NULL,
  expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id)
);

CREATE INDEX IF NOT EXISTS idx_turn_credentials_user ON turn_credentials(user_id);
CREATE INDEX IF NOT EXISTS idx_turn_credentials_expires ON turn_credentials(expires_at);

-- ============================================================================
-- ENABLE ROW LEVEL SECURITY
-- ============================================================================

ALTER TABLE calls ENABLE ROW LEVEL SECURITY;
ALTER TABLE call_participants ENABLE ROW LEVEL SECURITY;
ALTER TABLE turn_credentials ENABLE ROW LEVEL SECURITY;

-- ============================================================================
-- RLS POLICIES
-- ============================================================================

-- Calls: Users can see calls they're participants in
CREATE POLICY "calls_select" ON calls
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM call_participants
      WHERE call_participants.call_id = calls.id AND call_participants.user_id = auth.uid()
    )
    OR initiator_id = auth.uid()
  );

CREATE POLICY "calls_insert" ON calls
  FOR INSERT WITH CHECK (initiator_id = auth.uid());

CREATE POLICY "calls_update" ON calls
  FOR UPDATE USING (
    initiator_id = auth.uid()
    OR EXISTS (
      SELECT 1 FROM call_participants
      WHERE call_participants.call_id = calls.id AND call_participants.user_id = auth.uid()
    )
  );

-- Call Participants: Users can see participants in their calls
CREATE POLICY "call_participants_select" ON call_participants
  FOR SELECT USING (
    user_id = auth.uid()
    OR EXISTS (
      SELECT 1 FROM call_participants cp2
      WHERE cp2.call_id = call_participants.call_id
      AND cp2.user_id = auth.uid()
    )
  );

CREATE POLICY "call_participants_insert" ON call_participants
  FOR INSERT WITH CHECK (
    user_id = auth.uid()
    OR EXISTS (
      SELECT 1 FROM calls
      WHERE id = call_id AND initiator_id = auth.uid()
    )
  );

CREATE POLICY "call_participants_update" ON call_participants
  FOR UPDATE USING (user_id = auth.uid());

-- TURN Credentials: Users can only see their own
CREATE POLICY "turn_credentials_select" ON turn_credentials
  FOR SELECT USING (user_id = auth.uid());

CREATE POLICY "turn_credentials_insert" ON turn_credentials
  FOR INSERT WITH CHECK (user_id = auth.uid());

CREATE POLICY "turn_credentials_update" ON turn_credentials
  FOR UPDATE USING (user_id = auth.uid());

-- ============================================================================
-- TRIGGER TO TRACK CALL DURATION
-- ============================================================================

CREATE OR REPLACE FUNCTION update_call_duration()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status = 'ended' AND OLD.status != 'ended' THEN
    NEW.ended_at = NOW();
    IF NEW.started_at IS NOT NULL THEN
      NEW.duration_seconds = EXTRACT(EPOCH FROM (NEW.ended_at - NEW.started_at))::INTEGER;
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_call_duration ON calls;
CREATE TRIGGER trigger_call_duration
  BEFORE UPDATE ON calls
  FOR EACH ROW EXECUTE FUNCTION update_call_duration();