-- Seed Scripts Migration
-- 30 Scripts: 10 SMS, 10 Call, 8 Objection, 2 Email
-- Rules: Contractors = FREE, Workers = 9.9% fee
-- SMS opt-out ONLY on re-engagement messages (gone_cold, inactive_30d)

-- ============================================
-- SCHEMA ADDITIONS: Add closing column for call scripts
-- ============================================
ALTER TABLE scripts ADD COLUMN IF NOT EXISTS closing TEXT;

-- ============================================
-- SMS SCRIPTS (10)
-- ============================================

-- 1. SMS - Initial Outreach - Contractor
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, variables, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Contractor First Touch',
  'sms',
  'initial_outreach',
  'contractor',
  'new',
  'Hey {first_name}, saw you''re a {trade} in {region}. RateRight connects tradies with quality leads at zero cost to you - we only charge the homeowner. Worth a quick chat?',
  ARRAY['first_name', 'trade', 'region'],
  true,
  true,
  'approved'
);

-- 2. SMS - Initial Outreach - Worker
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, variables, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Worker First Touch',
  'sms',
  'initial_outreach',
  'worker',
  'new',
  'Hey {first_name}, heard you''re looking for {trade} work in {region}. RateRight has solid gigs coming through - 9.9% platform fee, paid weekly. Keen to hear more?',
  ARRAY['first_name', 'trade', 'region'],
  true,
  true,
  'approved'
);

-- 3. SMS - Follow-up Warm
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, variables, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Warm Follow-up',
  'sms',
  'follow_up',
  'both',
  'interested',
  'Hey {first_name}, just checking in from RateRight. Any questions from our last chat? Happy to jump on a quick call if easier.',
  ARRAY['first_name'],
  true,
  true,
  'approved'
);

-- 4. SMS - Follow-up Cold
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, variables, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Cold Follow-up',
  'sms',
  'follow_up',
  'both',
  'contacted',
  'Hey {first_name}, {sender_name} from RateRight. Tried to catch you last week - still interested in hearing how we''re helping {trade}s find work?',
  ARRAY['first_name', 'sender_name', 'trade'],
  true,
  true,
  'approved'
);

-- 5. SMS - Re-engagement (gone_cold) - WITH OPT-OUT
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, variables, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Re-engage Gone Cold',
  'sms',
  're_engagement',
  'both',
  'gone_cold',
  'Hey {first_name}, been a while! RateRight''s grown heaps since we last spoke. Got some solid {trade} opportunities in {region} if you''re still keen. Reply STOP to opt out.',
  ARRAY['first_name', 'trade', 'region'],
  true,
  true,
  'approved'
);

-- 6. SMS - Re-engagement (inactive_30d) - WITH OPT-OUT
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, variables, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Re-engage Inactive',
  'sms',
  're_engagement',
  'both',
  'inactive_30d',
  'Hey {first_name}, noticed you haven''t been on RateRight lately. Everything alright? We''ve got new {trade} jobs coming through in {region}. Reply STOP to opt out.',
  ARRAY['first_name', 'trade', 'region'],
  true,
  true,
  'approved'
);

-- 7. SMS - Booking Confirmation - Contractor
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, variables, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Contractor Booking Confirm',
  'sms',
  'booking',
  'contractor',
  'qualified',
  'Confirmed! Your RateRight call is booked for {call_date} at {call_time}. We''ll chat about how to get quality leads sent your way. Talk soon, {sender_name}',
  ARRAY['call_date', 'call_time', 'sender_name'],
  true,
  true,
  'approved'
);

-- 8. SMS - Booking Confirmation - Worker
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, variables, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Worker Booking Confirm',
  'sms',
  'booking',
  'worker',
  'qualified',
  'Sorted! Your RateRight call is booked for {call_date} at {call_time}. We''ll run through how the platform works and get you set up. Cheers, {sender_name}',
  ARRAY['call_date', 'call_time', 'sender_name'],
  true,
  true,
  'approved'
);

-- 9. SMS - Post-Call Check-in
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, variables, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Post-Call Check-in',
  'sms',
  'post_call',
  'both',
  'negotiating',
  'Hey {first_name}, good chatting earlier! Just checking - any other questions about getting started with RateRight? Happy to help.',
  ARRAY['first_name'],
  true,
  true,
  'approved'
);

-- 10. SMS - Welcome New Member
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, variables, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Welcome New Member',
  'sms',
  'onboarding',
  'both',
  'converted',
  'Welcome to RateRight, {first_name}! Stoked to have you on board. Your profile is live - jobs will start coming through. Any questions, just reply here.',
  ARRAY['first_name'],
  true,
  true,
  'approved'
);

-- ============================================
-- CALL SCRIPTS (10)
-- ============================================

-- 11. Call - Opening - Contractor
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, opening, discovery_questions, closing, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Contractor Intro Call',
  'call',
  'intro',
  'contractor',
  'qualified',
  'Hey {first_name}, it''s {sender_name} from RateRight. Thanks for jumping on a call. So RateRight''s a platform that connects quality tradies like yourself with homeowners who need work done. Best part? It costs you nothing - we only charge the homeowner a small fee.',
  '["How long have you been in {trade}?", "What area do you mainly cover?", "How are you finding leads at the moment?", "What''s your capacity like - how many jobs could you take on per week?"]',
  'Sounds like RateRight could be a good fit. Want me to get your profile set up so you can start seeing leads come through?',
  true,
  true,
  'approved'
);

-- 12. Call - Opening - Worker
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, opening, discovery_questions, closing, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Worker Intro Call',
  'call',
  'intro',
  'worker',
  'qualified',
  'Hey {first_name}, {sender_name} from RateRight here. Appreciate you taking the time. So RateRight connects skilled tradies with quality gigs. There''s a 9.9% platform fee that comes out of your pay, but you get paid weekly and the work''s steady.',
  '["What {trade} experience have you got?", "Do you have your own tools and transport?", "What area are you based in?", "Are you after full-time work or just filling gaps?"]',
  'Reckon we''ve got work that suits you. Want me to get you set up so you can start seeing jobs?',
  true,
  true,
  'approved'
);

-- 13. Call - Discovery Deep Dive
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, opening, discovery_questions, closing, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Discovery Deep Dive',
  'call',
  'discovery',
  'both',
  'interested',
  'Good to connect, {first_name}. Before I tell you more about RateRight, want to understand what you''re looking for...',
  '["What''s your main challenge with finding work right now?", "How much time do you spend chasing quotes that go nowhere?", "What would make the biggest difference to your business?", "What does a good week look like for you in terms of jobs?"]',
  'Based on what you''ve told me, RateRight could really help with that. Here''s what I''d suggest...',
  true,
  true,
  'approved'
);

-- 14. Call - Qualification Script
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, opening, discovery_questions, closing, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Quick Qualification',
  'call',
  'qualification',
  'both',
  'new',
  'Hey {first_name}, quick call from RateRight. Just want to check a few things to see if we can help...',
  '["Are you licensed and insured?", "What suburbs do you work in?", "Can you usually respond to job requests same day?", "Any minimum job size you prefer?"]',
  'All sounds good. Let me tell you how RateRight works...',
  true,
  true,
  'approved'
);

-- 15. Call - Objection Handling - Price
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, opening, discovery_questions, closing, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Price Objection Call',
  'call',
  'objection',
  'worker',
  'negotiating',
  'I hear you, {first_name}. The 9.9% can seem like a lot at first glance. Let me break it down...',
  '["How much time do you spend each week looking for work?", "What''s your average job value?", "How many quotes do you do that don''t convert?"]',
  'When you factor in the time saved and guaranteed payment, most tradies find they actually come out ahead. Want to try a few jobs and see how it goes?',
  true,
  true,
  'approved'
);

-- 16. Call - Re-engagement
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, opening, discovery_questions, closing, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Re-engagement Call',
  'call',
  're_engagement',
  'both',
  'gone_cold',
  'Hey {first_name}, {sender_name} from RateRight. We chatted a while back but the timing wasn''t right. Just checking in to see if things have changed...',
  '["How''s work been going since we last spoke?", "What''s different about your situation now?", "What would need to be true for RateRight to work for you?"]',
  'Good to hear where you''re at. Based on that, here''s what I''d suggest as a next step...',
  true,
  true,
  'approved'
);

-- 17. Call - Closing - Hot Lead
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, opening, discovery_questions, closing, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Hot Lead Close',
  'call',
  'closing',
  'both',
  'interested',
  'Alright {first_name}, sounds like you''re ready to get started. Let me walk you through the sign-up...',
  '["Got your phone handy to verify your number?", "Want me to stay on while you complete the profile?", "Any questions before we lock this in?"]',
  'You''re all set! First job notifications should start coming through within 24 hours. Welcome to RateRight.',
  true,
  true,
  'approved'
);

-- 18. Call - Referral Ask
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, opening, discovery_questions, closing, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Referral Ask',
  'call',
  'referral',
  'both',
  'converted',
  'Hey {first_name}, how''s RateRight been treating you? Good to hear. Quick one - know any other tradies who might be keen?',
  '["Any mates in the trade who are looking for more work?", "Anyone you''ve worked with who''d be a good fit?"]',
  'Legend. Send me their number and I''ll give them a call. If they sign up, we''ll sort you out with a referral bonus.',
  true,
  true,
  'approved'
);

-- 19. Call - Follow-up No Answer (Voicemail)
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, opening, discovery_questions, closing, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Voicemail Script',
  'call',
  'voicemail',
  'both',
  'contacted',
  'Hey {first_name}, {sender_name} from RateRight. Tried to catch you. Give us a buzz back on this number when you get a chance - keen to chat about some {trade} opportunities. Cheers.',
  '[]',
  '',
  true,
  true,
  'approved'
);

-- 20. Call - Callback Scheduled
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, opening, discovery_questions, closing, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Scheduled Callback Intro',
  'call',
  'callback',
  'both',
  'qualified',
  'Hey {first_name}, it''s {sender_name} from RateRight calling as promised. How''s your day going? Ready to chat about how we can help you find more work?',
  '["Before we dive in, anything specific you wanted to know about RateRight?"]',
  'Perfect. Let me show you exactly how it works...',
  true,
  true,
  'approved'
);

-- ============================================
-- OBJECTION SCRIPTS (8)
-- ============================================

-- 21. Objection - Too Expensive (Worker)
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  '9.9% Too Much',
  'objection',
  'price',
  'worker',
  'negotiating',
  'I get it - 9.9% sounds like a chunk. But think about it this way: how much time do you spend each week quoting jobs that go nowhere? Most tradies tell us they spend 5-10 hours a week on admin and chasing leads. With RateRight, the work comes to you, you get paid weekly, no chasing invoices. When you do the maths, that 9.9% often works out cheaper than doing it yourself.',
  true,
  true,
  'approved'
);

-- 22. Objection - Already Have Enough Work
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Already Busy',
  'objection',
  'timing',
  'both',
  'interested',
  'That''s great you''re busy - means you''re doing something right. Here''s how some tradies use RateRight: they set their availability and job preferences, so they only see work that fits their schedule. If something sweet comes through, they take it. If not, no worries. It''s like having a backup pipeline without any effort.',
  true,
  true,
  'approved'
);

-- 23. Objection - Need to Think About It
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Need to Think',
  'objection',
  'stall',
  'both',
  'negotiating',
  'Fair enough - it''s a decision worth thinking through. Just so I can help, what specifically are you weighing up? Sometimes I can give you info that makes the decision easier. Or if you''d prefer, I can check in next week once you''ve had time to mull it over.',
  true,
  true,
  'approved'
);

-- 24. Objection - Had Bad Experience with Platforms
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Bad Platform Experience',
  'objection',
  'trust',
  'both',
  'interested',
  'Yeah, I''ve heard some horror stories about other platforms. RateRight''s different - we''re Aussie owned, we actually vet the leads before they come through, and our support team picks up the phone. Tell me what went wrong before and I''ll show you how we handle it differently.',
  true,
  true,
  'approved'
);

-- 25. Objection - Not Sure How It Works
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'How Does It Work',
  'objection',
  'understanding',
  'both',
  'new',
  'No worries, let me break it down simply. Homeowners post jobs on RateRight. We match them with tradies in their area - that''s you. You get a notification, check the job details, and accept if it suits. You do the work, mark it complete, and get paid weekly. We handle the payment collection and customer service. For contractors, it''s zero cost. For workers, there''s a 9.9% platform fee.',
  true,
  true,
  'approved'
);

-- 26. Objection - Don't Trust Online Leads
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Don''t Trust Online',
  'objection',
  'trust',
  'contractor',
  'interested',
  'I hear you - there''s heaps of tyre-kickers online. Here''s what we do differently: every job that comes through RateRight is from a verified homeowner who''s already put in their payment details. No time-wasters. If a job falls through, we don''t charge you. It costs you nothing to try a few and see the quality yourself.',
  true,
  true,
  'approved'
);

-- 27. Objection - Want to Keep Current Arrangements
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Keep Current Setup',
  'objection',
  'change',
  'both',
  'interested',
  'That makes sense - if it''s working, why change? Most of our tradies don''t replace what they''re doing, they just add RateRight as another channel. Think of it as diversifying where your work comes from. When one source is quiet, the others pick up the slack. No commitment, no contracts.',
  true,
  true,
  'approved'
);

-- 28. Objection - Not Ready Yet
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Not Ready Yet',
  'objection',
  'timing',
  'both',
  'interested',
  'All good, no pressure. What would need to happen for the timing to be right? Happy to check back in when things settle down. In the meantime, you could set up a profile and just leave it paused - that way when you''re ready, you can switch it on straight away.',
  true,
  true,
  'approved'
);

-- ============================================
-- EMAIL SCRIPTS (2)
-- ============================================

-- 29. Email - Welcome/Onboarding
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, variables, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Welcome Email',
  'email',
  'onboarding',
  'both',
  'converted',
  'Subject: Welcome to RateRight, {first_name}!

Hey {first_name},

Stoked to have you on board. Your RateRight profile is now live and you''ll start seeing job opportunities come through.

Here''s what happens next:
- Jobs matching your skills and area will appear in your app
- Accept the ones that suit, skip the ones that don''t
- Complete the work, mark it done, get paid weekly

If you''ve got any questions, just reply to this email or give us a call.

Cheers,
{sender_name}
RateRight Team',
  ARRAY['first_name', 'sender_name'],
  true,
  true,
  'approved'
);

-- 30. Email - Re-engagement
INSERT INTO scripts (id, name, category, subcategory, target_type, target_stage, content, variables, is_active, is_seed, compliance_status)
VALUES (
  gen_random_uuid(),
  'Re-engagement Email',
  'email',
  're_engagement',
  'both',
  'gone_cold',
  'Subject: Miss you, {first_name}

Hey {first_name},

Been a while since we heard from you. How''s the {trade} work going?

RateRight''s grown heaps recently and we''ve got solid job flow in {region}. If you''re keen to get back into it, your profile is ready to go - just log in and switch it back on.

Any questions or feedback, hit reply and let us know.

Cheers,
{sender_name}
RateRight Team

To unsubscribe from these emails, reply with STOP.',
  ARRAY['first_name', 'trade', 'region', 'sender_name'],
  true,
  true,
  'approved'
);
