-- Voicemail Drop Feature Schema
-- Created: Jan 18, 2026

-- Pre-recorded voicemail messages
CREATE TABLE IF NOT EXISTS voicemail_recordings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE, -- Owner (null = shared/system)
  name VARCHAR(100) NOT NULL,
  description TEXT,
  recording_url TEXT NOT NULL, -- URL to audio file
  recording_sid VARCHAR(50), -- Twilio Recording SID if stored there
  duration_seconds INTEGER,
  is_default BOOLEAN DEFAULT FALSE,
  is_shared BOOLEAN DEFAULT FALSE, -- Available to whole team
  usage_count INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_vm_recordings_user ON voicemail_recordings(user_id);
CREATE INDEX IF NOT EXISTS idx_vm_recordings_shared ON voicemail_recordings(is_shared) WHERE is_shared = TRUE;

-- Track voicemail drops for analytics
CREATE TABLE IF NOT EXISTS voicemail_drops (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  recording_id UUID REFERENCES voicemail_recordings(id) ON DELETE SET NULL,
  lead_id UUID REFERENCES leads(id) ON DELETE SET NULL,
  user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  call_sid VARCHAR(50),
  dropped_at TIMESTAMPTZ DEFAULT NOW(),
  -- Track effectiveness
  lead_called_back BOOLEAN,
  callback_within_hours INTEGER
);

-- Index for analytics
CREATE INDEX IF NOT EXISTS idx_vm_drops_recording ON voicemail_drops(recording_id);
CREATE INDEX IF NOT EXISTS idx_vm_drops_lead ON voicemail_drops(lead_id);
CREATE INDEX IF NOT EXISTS idx_vm_drops_user ON voicemail_drops(user_id);

-- Function to increment usage count
CREATE OR REPLACE FUNCTION increment_vm_usage(recording_uuid UUID)
RETURNS void AS $$
BEGIN
  UPDATE voicemail_recordings
  SET usage_count = usage_count + 1,
      updated_at = NOW()
  WHERE id = recording_uuid;
END;
$$ LANGUAGE plpgsql;

-- Enable RLS
ALTER TABLE voicemail_recordings ENABLE ROW LEVEL SECURITY;
ALTER TABLE voicemail_drops ENABLE ROW LEVEL SECURITY;

-- RLS Policies for voicemail_recordings
-- All authenticated users can view and manage recordings (internal tool)
CREATE POLICY "Authenticated users can manage voicemail recordings"
  ON voicemail_recordings FOR ALL TO authenticated
  USING (true) WITH CHECK (true);

-- RLS Policies for voicemail_drops
-- All authenticated users can view and create drops (internal tool)
CREATE POLICY "Authenticated users can manage voicemail drops"
  ON voicemail_drops FOR ALL TO authenticated
  USING (true) WITH CHECK (true);

-- Seed some default voicemails (team shared)
-- Note: Replace recording_url with actual uploaded file URLs after creating storage bucket
INSERT INTO voicemail_recordings (user_id, name, description, is_shared, recording_url) VALUES
  (NULL, 'Standard Introduction', 'General intro for first contact - mentions RateRight and asks for callback', TRUE, 'PENDING_UPLOAD'),
  (NULL, 'Follow-up Message', 'For leads who didn''t answer previous calls', TRUE, 'PENDING_UPLOAD'),
  (NULL, 'Hot Lead Re-engagement', 'For leads who showed interest but went quiet', TRUE, 'PENDING_UPLOAD')
ON CONFLICT DO NOTHING;
