-- SMS Conversations Migration
-- Adds read tracking and optimizes for conversation queries
-- Created from: sms-conversations-migration.sql

-- Add read_at column to track when messages were read
ALTER TABLE communications ADD COLUMN IF NOT EXISTS read_at TIMESTAMPTZ;

-- Add index for efficient conversation queries
CREATE INDEX IF NOT EXISTS idx_communications_type ON communications(type);
CREATE INDEX IF NOT EXISTS idx_communications_direction ON communications(direction);
CREATE INDEX IF NOT EXISTS idx_communications_created_at ON communications(created_at DESC);

-- Add index for finding unread messages
CREATE INDEX IF NOT EXISTS idx_communications_unread ON communications(lead_id, read_at)
  WHERE type IN ('sms_inbound', 'sms_outbound') AND read_at IS NULL;

-- Create a view for conversation summaries
CREATE OR REPLACE VIEW sms_conversations AS
SELECT
  l.id as lead_id,
  l.first_name,
  l.last_name,
  l.company,
  l.phone,
  l.score,
  l.platform_stage,
  l.lead_type,
  (
    SELECT content
    FROM communications c
    WHERE c.lead_id = l.id
      AND c.type IN ('sms_inbound', 'sms_outbound')
    ORDER BY c.created_at DESC
    LIMIT 1
  ) as last_message,
  (
    SELECT direction
    FROM communications c
    WHERE c.lead_id = l.id
      AND c.type IN ('sms_inbound', 'sms_outbound')
    ORDER BY c.created_at DESC
    LIMIT 1
  ) as last_message_direction,
  (
    SELECT created_at
    FROM communications c
    WHERE c.lead_id = l.id
      AND c.type IN ('sms_inbound', 'sms_outbound')
    ORDER BY c.created_at DESC
    LIMIT 1
  ) as last_message_at,
  (
    SELECT COUNT(*)
    FROM communications c
    WHERE c.lead_id = l.id
      AND c.type = 'sms_inbound'
      AND c.read_at IS NULL
  ) as unread_count,
  (
    SELECT COUNT(*)
    FROM communications c
    WHERE c.lead_id = l.id
      AND c.type IN ('sms_inbound', 'sms_outbound')
  ) as total_messages
FROM leads l
WHERE l.deleted_at IS NULL
  AND EXISTS (
    SELECT 1 FROM communications c
    WHERE c.lead_id = l.id
      AND c.type IN ('sms_inbound', 'sms_outbound')
  );
