-- Actionable Playbook: Track AI-generated messages and sends
-- Created: Jan 18, 2026

-- Track playbook message generations and sends
CREATE TABLE IF NOT EXISTS playbook_messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  message_text TEXT NOT NULL,
  message_angle VARCHAR(50), -- 'follow_up', 'value_prop', 'urgency'
  was_sent BOOLEAN DEFAULT FALSE,
  sent_at TIMESTAMPTZ,
  generated_at TIMESTAMPTZ DEFAULT NOW(),
  batch_id UUID, -- Groups bulk sends together
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for finding recent generations (cache check)
CREATE INDEX IF NOT EXISTS idx_playbook_messages_lead_recent
ON playbook_messages(lead_id, generated_at DESC);

-- Index for batch tracking
CREATE INDEX IF NOT EXISTS idx_playbook_messages_batch
ON playbook_messages(batch_id) WHERE batch_id IS NOT NULL;

-- Index for user's recent messages
CREATE INDEX IF NOT EXISTS idx_playbook_messages_user
ON playbook_messages(user_id, created_at DESC);

-- Track message effectiveness (which angles get replies)
CREATE INDEX IF NOT EXISTS idx_playbook_messages_angle_sent
ON playbook_messages(message_angle, was_sent) WHERE was_sent = TRUE;

-- Enable RLS
ALTER TABLE playbook_messages ENABLE ROW LEVEL SECURITY;

-- RLS policy: users can see their own messages
CREATE POLICY "Users can view own playbook messages"
ON playbook_messages FOR SELECT
USING (auth.uid() = user_id);

-- RLS policy: users can insert their own messages
CREATE POLICY "Users can insert own playbook messages"
ON playbook_messages FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- RLS policy: users can update their own messages
CREATE POLICY "Users can update own playbook messages"
ON playbook_messages FOR UPDATE
USING (auth.uid() = user_id);
