-- Seed sequences if table is empty
-- This ensures sequences exist for auto-enrollment on no-answer calls

DO $$
DECLARE
  worker_seq_id UUID;
  contractor_seq_id UUID;
  seq_count INTEGER;
BEGIN
  -- Check if any sequences exist
  SELECT COUNT(*) INTO seq_count FROM sequences WHERE channel = 'sms' AND is_active = true;

  IF seq_count = 0 THEN
    RAISE NOTICE 'No active SMS sequences found. Creating default sequences...';

    -- 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;

    -- Worker sequence steps
    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?'),
    (worker_seq_id, 2, 'Value Prop SMS', 'sms', 72, 'Hey {first_name}, just checking - still looking for work? We''ve got {trade} jobs coming through.'),
    (worker_seq_id, 3, '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 ID %', worker_seq_id;

    -- 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;

    -- Contractor sequence steps
    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?'),
    (contractor_seq_id, 2, 'Value Prop SMS', 'sms', 72, 'Hey {first_name}, just following up. Need any {trade}s for upcoming jobs? We''ve got good blokes ready.'),
    (contractor_seq_id, 3, '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 ID %', contractor_seq_id;
  ELSE
    RAISE NOTICE 'Found % active SMS sequences. Skipping seed.', seq_count;
  END IF;
END $$;
