-- Sequence Automation Migration
-- Adds columns needed for automated SMS sequence processing

-- Add next_step_at for scheduling when to process next step
ALTER TABLE sequence_enrollments
ADD COLUMN IF NOT EXISTS next_step_at TIMESTAMPTZ;

-- Add pause_reason for tracking why enrollment was paused
ALTER TABLE sequence_enrollments
ADD COLUMN IF NOT EXISTS pause_reason VARCHAR(50);

-- Index for processor queries - find due enrollments efficiently
CREATE INDEX IF NOT EXISTS idx_sequence_enrollments_due
ON sequence_enrollments(next_step_at)
WHERE status = 'active';

-- Index for finding enrollments by lead
CREATE INDEX IF NOT EXISTS idx_sequence_enrollments_lead
ON sequence_enrollments(lead_id, status);

-- Add channel to sequences for filtering (sms, email, call)
ALTER TABLE sequences
ADD COLUMN IF NOT EXISTS channel VARCHAR(20) DEFAULT 'sms';

-- Add user_id to sequences (who created it)
ALTER TABLE sequences
ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES auth.users(id);

-- Add step name for UI display
ALTER TABLE sequence_steps
ADD COLUMN IF NOT EXISTS name VARCHAR(100);

-- Add message_template to steps (for inline templates, not referencing sms_templates)
ALTER TABLE sequence_steps
ADD COLUMN IF NOT EXISTS message_template TEXT;

-- Enable RLS on sequence tables
ALTER TABLE sequences ENABLE ROW LEVEL SECURITY;
ALTER TABLE sequence_steps ENABLE ROW LEVEL SECURITY;
ALTER TABLE sequence_enrollments ENABLE ROW LEVEL SECURITY;

-- RLS policies for sequences (all authenticated users can use)
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies WHERE tablename = 'sequences' AND policyname = 'Authenticated users can manage sequences'
  ) THEN
    CREATE POLICY "Authenticated users can manage sequences"
      ON sequences FOR ALL TO authenticated
      USING (true) WITH CHECK (true);
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies WHERE tablename = 'sequence_steps' AND policyname = 'Authenticated users can manage sequence steps'
  ) THEN
    CREATE POLICY "Authenticated users can manage sequence steps"
      ON sequence_steps FOR ALL TO authenticated
      USING (true) WITH CHECK (true);
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies WHERE tablename = 'sequence_enrollments' AND policyname = 'Authenticated users can manage sequence enrollments'
  ) THEN
    CREATE POLICY "Authenticated users can manage sequence enrollments"
      ON sequence_enrollments FOR ALL TO authenticated
      USING (true) WITH CHECK (true);
  END IF;
END $$;

-- Trigger to update sequence_enrollments.paused_at when status changes to paused
CREATE OR REPLACE FUNCTION update_enrollment_paused_at()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status = 'paused' AND OLD.status != 'paused' THEN
    NEW.paused_at = NOW();
  END IF;
  IF NEW.status = 'completed' AND OLD.status != 'completed' THEN
    NEW.completed_at = NOW();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS enrollment_status_change ON sequence_enrollments;
CREATE TRIGGER enrollment_status_change
  BEFORE UPDATE ON sequence_enrollments
  FOR EACH ROW
  EXECUTE FUNCTION update_enrollment_paused_at();
