-- Seed Sequences Migration
-- Creates default sequences for New Worker and New Contractor outreach

-- Only insert if sequences table is empty (avoid duplicates on re-run)
DO $$
DECLARE
  worker_seq_id UUID;
  contractor_seq_id UUID;
BEGIN
  -- Check if sequences already exist
  IF NOT EXISTS (SELECT 1 FROM sequences WHERE name = 'New Worker Outreach') THEN

    -- Create New Worker Outreach sequence
    INSERT INTO sequences (id, name, description, trigger_type, channel, is_active)
    VALUES (
      gen_random_uuid(),
      'New Worker Outreach',
      'Follow-up sequence for new worker leads who didn''t answer first call',
      'no_answer',
      'sms',
      true
    )
    RETURNING id INTO worker_seq_id;

    -- Step 1: Initial SMS (Day 0 - immediately after no-answer call)
    INSERT INTO sequence_steps (sequence_id, step_order, name, action_type, delay_hours, message_template)
    VALUES (
      worker_seq_id,
      1,
      'Initial SMS',
      'sms',
      0,
      'Hey {first_name}, Rocky from RateRight here. We connect tradies with work - 9.9% only when you get paid. Worth a quick chat?'
    );

    -- Step 2: Call reminder (Day 1)
    INSERT INTO sequence_steps (sequence_id, step_order, name, action_type, delay_hours, config)
    VALUES (
      worker_seq_id,
      2,
      'Follow-up Call',
      'call_reminder',
      24,
      '{"notes": "Follow-up call - they didn''t answer first attempt"}'::jsonb
    );

    -- Step 3: Value prop SMS (Day 3)
    INSERT INTO sequence_steps (sequence_id, step_order, name, action_type, delay_hours, message_template)
    VALUES (
      worker_seq_id,
      3,
      'Value Prop SMS',
      'sms',
      72,
      'Hey {first_name}, just checking - still looking for work? We''ve got {trade} jobs coming through.'
    );

    -- Step 4: Last chance SMS (Day 7)
    INSERT INTO sequence_steps (sequence_id, step_order, name, action_type, delay_hours, message_template)
    VALUES (
      worker_seq_id,
      4,
      'Last Chance SMS',
      'sms',
      168,
      'Last one from me {first_name}. If you''re sorted for work, no worries. If things change, we''re here.'
    );

    RAISE NOTICE 'Created New Worker Outreach sequence with 4 steps';
  END IF;

  -- Check if contractor sequence already exists
  IF NOT EXISTS (SELECT 1 FROM sequences WHERE name = 'New Contractor Outreach') THEN

    -- Create New Contractor Outreach sequence
    INSERT INTO sequences (id, name, description, trigger_type, channel, is_active)
    VALUES (
      gen_random_uuid(),
      'New Contractor Outreach',
      'Follow-up sequence for new contractor leads who didn''t answer first call',
      'no_answer',
      'sms',
      true
    )
    RETURNING id INTO contractor_seq_id;

    -- Step 1: Initial SMS (Day 0)
    INSERT INTO sequence_steps (sequence_id, step_order, name, action_type, delay_hours, message_template)
    VALUES (
      contractor_seq_id,
      1,
      'Initial SMS',
      'sms',
      0,
      'Hey {first_name}, Rocky from RateRight. We supply quality tradies to builders - FREE to use. Worth a chat about your crew needs?'
    );

    -- Step 2: Call reminder (Day 1)
    INSERT INTO sequence_steps (sequence_id, step_order, name, action_type, delay_hours, config)
    VALUES (
      contractor_seq_id,
      2,
      'Follow-up Call',
      'call_reminder',
      24,
      '{"notes": "Follow-up call - they didn''t answer first attempt"}'::jsonb
    );

    -- Step 3: Value prop SMS (Day 3)
    INSERT INTO sequence_steps (sequence_id, step_order, name, action_type, delay_hours, message_template)
    VALUES (
      contractor_seq_id,
      3,
      'Value Prop SMS',
      'sms',
      72,
      'Hey {first_name}, just following up. Need any {trade}s for upcoming jobs? We''ve got good blokes ready.'
    );

    -- Step 4: Last chance SMS (Day 7)
    INSERT INTO sequence_steps (sequence_id, step_order, name, action_type, delay_hours, message_template)
    VALUES (
      contractor_seq_id,
      4,
      'Last Chance SMS',
      'sms',
      168,
      'Final check-in {first_name}. If you''re all staffed up, no dramas. We''re here when you need crew.'
    );

    RAISE NOTICE 'Created New Contractor Outreach sequence with 4 steps';
  END IF;
END $$;
