-- ============================================
-- Rocky Platform Seeding Sequence Migration
-- Created: 2026-01-21
-- ============================================

-- PART 1: Extend sequences table with new columns
-- ============================================
ALTER TABLE sequences ADD COLUMN IF NOT EXISTS channel VARCHAR(20) DEFAULT 'sms';
ALTER TABLE sequences ADD COLUMN IF NOT EXISTS stage VARCHAR(50);
ALTER TABLE sequences ADD COLUMN IF NOT EXISTS lead_type VARCHAR(20);
ALTER TABLE sequences ADD COLUMN IF NOT EXISTS sender_name VARCHAR(50) DEFAULT 'Rocky';

-- PART 2: Extend sequence_steps table
-- ============================================
ALTER TABLE sequence_steps ADD COLUMN IF NOT EXISTS name VARCHAR(100);
ALTER TABLE sequence_steps ADD COLUMN IF NOT EXISTS template TEXT; -- Inline template (alternative to template_id)
ALTER TABLE sequence_steps ADD COLUMN IF NOT EXISTS send_window VARCHAR(20); -- morning, afternoon, knockoff

-- PART 3: Create the Rocky Platform Seeding sequence
-- ============================================
INSERT INTO sequences (name, description, channel, stage, lead_type, trigger_type, sender_name, is_active)
VALUES (
  'Platform Seeding - Rocky Contacts',
  'Favour ask to Rocky''s old contacts - just need signups, not sales',
  'sms',
  'not_signed_up',
  'worker',
  'manual',
  'Rocky',
  true
)
ON CONFLICT DO NOTHING;

-- PART 4: Get the sequence ID and insert steps
-- ============================================
DO $$
DECLARE
  seq_id UUID;
BEGIN
  -- Get the sequence ID
  SELECT id INTO seq_id FROM sequences WHERE name = 'Platform Seeding - Rocky Contacts' LIMIT 1;

  -- Only insert steps if sequence exists and steps don't already exist
  IF seq_id IS NOT NULL THEN
    -- Step 1: Rocky Favour Ask (Day 0, knockoff)
    INSERT INTO sequence_steps (sequence_id, step_order, name, action_type, delay_hours, send_window, template)
    SELECT seq_id, 1, 'Rocky Favour Ask', 'sms', 0, 'knockoff',
      'Hey {first_name}, it''s Rocky. Been a while mate. I''ve built this site for tradies - trying to get it off the ground. Would mean a lot if you could just sign up, don''t even need to use it. Just need some profiles so it doesn''t look dead. rateright.com.au'
    WHERE NOT EXISTS (SELECT 1 FROM sequence_steps WHERE sequence_id = seq_id AND step_order = 1);

    -- Step 2: Soft Follow-up (Day 3, morning)
    INSERT INTO sequence_steps (sequence_id, step_order, name, action_type, delay_hours, send_window, template)
    SELECT seq_id, 2, 'Soft Follow-up', 'sms', 72, 'morning',
      'No stress if you''re flat out mate. Just 2 mins to sign up if you get a chance. Would owe you one.'
    WHERE NOT EXISTS (SELECT 1 FROM sequence_steps WHERE sequence_id = seq_id AND step_order = 2);

    -- Step 3: Last Nudge + Referral (Day 7 from start, knockoff)
    INSERT INTO sequence_steps (sequence_id, step_order, name, action_type, delay_hours, send_window, template)
    SELECT seq_id, 3, 'Last Nudge + Referral', 'sms', 168, 'knockoff',
      'Last nudge mate - if you know anyone else who''d help out, send em my way. Appreciate it either way.'
    WHERE NOT EXISTS (SELECT 1 FROM sequence_steps WHERE sequence_id = seq_id AND step_order = 3);

    RAISE NOTICE 'Sequence steps created for sequence ID: %', seq_id;
  ELSE
    RAISE NOTICE 'Sequence not found!';
  END IF;
END $$;

-- PART 5: Verify the sequence was created
-- ============================================
SELECT
  s.id,
  s.name,
  s.channel,
  s.stage,
  s.lead_type,
  s.trigger_type,
  s.sender_name,
  s.is_active,
  COUNT(ss.id) as step_count
FROM sequences s
LEFT JOIN sequence_steps ss ON ss.sequence_id = s.id
WHERE s.name = 'Platform Seeding - Rocky Contacts'
GROUP BY s.id;

-- Show all steps
SELECT
  step_order,
  name,
  action_type,
  delay_hours,
  send_window,
  LEFT(template, 50) || '...' as template_preview
FROM sequence_steps
WHERE sequence_id = (SELECT id FROM sequences WHERE name = 'Platform Seeding - Rocky Contacts')
ORDER BY step_order;
