-- Migration 030: Add vertical and pack_name columns to sequence_templates.
-- Enables grouping templates into AU-market vertical packs.

ALTER TABLE sequence_templates
    ADD COLUMN vertical   VARCHAR(60)  NULL AFTER is_system,
    ADD COLUMN pack_name  VARCHAR(100) NULL AFTER vertical;

-- -- Update existing templates with their vertical --------------------------
UPDATE sequence_templates SET vertical = 'general', pack_name = 'AU Outreach Essentials'
WHERE slug IN ('au-linkedin-cold','au-email-cold','au-email-followup');

-- -- Recruiter Pack ---------------------------------------------------------
INSERT IGNORE INTO sequence_templates (slug, name, channel, steps_count, steps_json, is_system, vertical, pack_name) VALUES

('au-recruiter-candidate-linkedin',
 'Recruiter: LinkedIn Candidate Outreach',
 'linkedin', 3,
 '[
   {"step_order":1,"type":"connect_request","delay_hours":0,"body_template":"Hi {{first_name}}, I came across your profile while searching for {{role}} talent in {{location}}. I''m recruiting for a fantastic opportunity that could be a great fit  -  would you be open to connecting?"},
   {"step_order":2,"type":"wait","delay_hours":48,"body_template":null},
   {"step_order":3,"type":"dm","delay_hours":0,"body_template":"Thanks for connecting, {{first_name}}! The role is {{role}} at a well-regarded AU company in {{industry}}. The package is {{package}}. Would you be open to a confidential 15-min chat this week?"}
 ]',
 1, 'recruiter', 'Recruiter Pack'),

('au-recruiter-job-board-followup',
 'Recruiter: Job Board Application Follow-up',
 'email', 2,
 '[
   {"step_order":1,"type":"email","delay_hours":0,"subject_template":"Following up on your application  -  {{role}}","body_template":"Hi {{first_name}},  Thank you for applying for the {{role}} position at {{company}}. I''ve had a chance to review your application and I''d love to have a quick chat to learn more about your background.  Are you available for a 20-minute call this week? I''m flexible on timing.  Looking forward to speaking with you.  {{recruiter_name}}"},
   {"step_order":2,"type":"email","delay_hours":72,"subject_template":"Still keen to chat  -  {{role}} at {{company}}","body_template":"Hi {{first_name}},  Just circling back on my previous message. The {{role}} position is still open and I''d love to move your application forward.  A quick 20-minute call is all it takes. Let me know if this week works.  {{recruiter_name}}"}
 ]',
 1, 'recruiter', 'Recruiter Pack'),

('au-recruiter-offer-congrats',
 'Recruiter: Offer Acceptance Congratulations',
 'email', 1,
 '[
   {"step_order":1,"type":"email","delay_hours":0,"subject_template":"Congratulations {{first_name}}  -  Welcome aboard!","body_template":"Hi {{first_name}},  Congratulations on accepting the offer for {{role}} at {{company}}! I''m thrilled for you.  Your start date is {{start_date}}. Please let me know if you have any questions before then  -  I''m here to help make the transition as smooth as possible.  Wishing you all the best in your new role!  {{recruiter_name}}"}
 ]',
 1, 'recruiter', 'Recruiter Pack'),

-- -- Real Estate Pack -------------------------------------------------------

('au-realestate-new-listing',
 'Real Estate: New Listing Announcement',
 'email', 2,
 '[
   {"step_order":1,"type":"email","delay_hours":0,"subject_template":"New listing: {{address}}  -  {{bedrooms}}bd {{bathrooms}}ba in {{suburb}}","body_template":"Hi {{first_name}},  I''m excited to share a new property that has just hit the market in {{suburb}}.  {{address}}  -  {{bedrooms}} bed / {{bathrooms}} bath Asking: {{price}}  Key features: {{features}}  I think this could be a great fit based on your preferences. Would you like to arrange a private inspection this week?  Kind regards, {{agent_name}}"},
   {"step_order":2,"type":"email","delay_hours":48,"subject_template":"Last chance  -  {{address}} inspections closing","body_template":"Hi {{first_name}},  Just a quick reminder that inspections for {{address}} are this weekend. Interest has been strong  -  if you''d like to view it before the first open home, I can arrange a private showing tomorrow or Friday.  Let me know, {{agent_name}}"}
 ]',
 1, 'real_estate', 'Real Estate Pack'),

('au-realestate-open-home-followup',
 'Real Estate: Open Home Follow-up',
 'email', 2,
 '[
   {"step_order":1,"type":"email","delay_hours":4,"subject_template":"Thanks for attending  -  {{address}}","body_template":"Hi {{first_name}},  Thank you for attending the open home at {{address}} today. It was great to meet you.  I''d love to get your thoughts on the property. Are you interested in making an offer, or would you like to see more options in the {{suburb}} area?  Happy to chat at a time that suits you.  {{agent_name}}"},
   {"step_order":2,"type":"email","delay_hours":72,"subject_template":"Following up  -  {{address}}","body_template":"Hi {{first_name}},  I''m following up on the open home at {{address}}. The vendors are keen to hear from interested buyers this week.  If you''re considering making an offer or have questions about the property, I''d love to help. Give me a call or reply to this email.  Kind regards, {{agent_name}}"}
 ]',
 1, 'real_estate', 'Real Estate Pack'),

('au-realestate-settlement-congrats',
 'Real Estate: Settlement Congratulations',
 'email', 1,
 '[
   {"step_order":1,"type":"email","delay_hours":0,"subject_template":"Congratulations on your new home at {{address}}!","body_template":"Hi {{first_name}},  Congratulations on the settlement of {{address}}  -  you''re officially a homeowner!  It''s been an absolute pleasure working with you throughout this process. Please don''t hesitate to reach out if you need any recommendations for movers, tradies, or conveyancers in the area.  Enjoy every moment in your new home.  Warm regards, {{agent_name}}"}
 ]',
 1, 'real_estate', 'Real Estate Pack'),

-- -- Finance Broker Pack ----------------------------------------------------

('au-finance-refinance-alert',
 'Finance Broker: Refinance Rate Alert',
 'email', 2,
 '[
   {"step_order":1,"type":"email","delay_hours":0,"subject_template":"Rates have dropped  -  could you save on your mortgage?","body_template":"Hi {{first_name}},  With the latest RBA rate movement, there''s a strong case for reviewing your current home loan rate of approximately {{current_rate}}%.  I''ve identified options that could save you around {{monthly_saving}} per month. A free 20-minute review is all it takes.  Would you like me to run the numbers for you?  {{broker_name}} Australian Credit Licence {{acl_number}}"},
   {"step_order":2,"type":"email","delay_hours":120,"subject_template":"Did you see my last message? Quick mortgage question","body_template":"Hi {{first_name}},  Just checking in  -  I sent you a note about potential savings on your home loan earlier this week.  No pressure at all, but if you''re open to a free review, the current market conditions are particularly favourable for refinancing. Takes less than 20 minutes.  Happy to help  -  just reply to this email.  {{broker_name}}"}
 ]',
 1, 'finance', 'Finance Broker Pack'),

('au-finance-eofy-super',
 'Finance Broker: EOFY Super Contribution Reminder',
 'email', 1,
 '[
   {"step_order":1,"type":"email","delay_hours":0,"subject_template":"30 June deadline  -  boost your super before EOFY","body_template":"Hi {{first_name}},  The 30 June end of financial year deadline is approaching fast. If you''re thinking about making a personal super contribution to claim a tax deduction, now is the time to act.  For example, contributing {{contribution_amount}} before 30 June could reduce your taxable income and grow your retirement savings.  I''d love to run through the options with you before the deadline. Are you free for a quick 15-minute call this week?  {{broker_name}} General advice only  -  this is not personal financial advice. Consider your objectives, financial situation and needs before acting."}
 ]',
 1, 'finance', 'Finance Broker Pack'),

('au-finance-preapproval-followup',
 'Finance Broker: Pre-Approval Follow-up',
 'email', 2,
 '[
   {"step_order":1,"type":"email","delay_hours":0,"subject_template":"Your pre-approval is ready  -  {{first_name}}","body_template":"Hi {{first_name}},  Great news  -  your home loan pre-approval has been conditionally approved up to {{approval_amount}}.  The pre-approval is valid for {{validity_days}} days. This puts you in a strong position to make offers with confidence.  Would you like to go through the details? I''m available for a call at your convenience.  {{broker_name}} Australian Credit Licence {{acl_number}}"},
   {"step_order":2,"type":"email","delay_hours":168,"subject_template":"Pre-approval update  -  {{first_name}}","body_template":"Hi {{first_name}},  Just checking in on your home search. Your pre-approval remains valid and I want to make sure you''re in the best position when you find the right property.  If you''d like to discuss any properties you''re considering, or if your situation has changed, please don''t hesitate to reach out.  {{broker_name}}"}
 ]',
 1, 'finance', 'Finance Broker Pack'),

-- -- Marketing Agency Pack --------------------------------------------------

('au-agency-pitch-sequence',
 'Marketing Agency: New Business Pitch Sequence',
 'email', 3,
 '[
   {"step_order":1,"type":"email","delay_hours":0,"subject_template":"Quick question for {{company}}","body_template":"Hi {{first_name}},  I came across {{company}} and was impressed by {{compliment}}. We''re a {{city}}-based digital agency that specialises in {{specialty}} for AU businesses in your space.  I''d love to show you what we''ve done for similar businesses  -  could we grab 20 minutes this week?  {{sender_name}}"},
   {"step_order":2,"type":"email","delay_hours":96,"subject_template":"Re: Quick question for {{company}}","body_template":"Hi {{first_name}},  Following up on my last message. I put together a quick 2-page overview of results we''ve achieved for {{industry}} businesses  -  happy to share it if you''re interested.  No commitment, just a conversation.  {{sender_name}}"},
   {"step_order":3,"type":"email","delay_hours":168,"subject_template":"Last message  -  {{company}}","body_template":"Hi {{first_name}},  I''ll keep this brief  -  if now isn''t a good time for a chat about {{company}}''s digital strategy, no worries at all. Feel free to reach out whenever the timing is right.  Best, {{sender_name}}"}
 ]',
 1, 'agency', 'Marketing Agency Pack'),

('au-agency-retainer-renewal',
 'Marketing Agency: Monthly Retainer Renewal',
 'email', 2,
 '[
   {"step_order":1,"type":"email","delay_hours":0,"subject_template":"Your retainer renewal  -  {{month}} {{year}}","body_template":"Hi {{first_name}},  Your monthly retainer with us renews on {{renewal_date}}. I wanted to touch base before then to review performance and confirm you''re happy to continue.  Highlights from this month: {{highlights}}  I''d love to schedule a 30-minute review call before the renewal date. Does {{suggested_date}} work for you?  {{account_manager}}"},
   {"step_order":2,"type":"email","delay_hours":72,"subject_template":"Renewal reminder  -  {{month}} {{year}}","body_template":"Hi {{first_name}},  Just a reminder that your retainer renews in {{days_until_renewal}} days. Please let me know if you''d like to discuss any changes to scope or if you have questions about the upcoming month.  {{account_manager}}"}
 ]',
 1, 'agency', 'Marketing Agency Pack'),

-- -- Professional Services Pack ---------------------------------------------

('au-profservices-quote-followup',
 'Professional Services: Quote Follow-up',
 'email', 3,
 '[
   {"step_order":1,"type":"email","delay_hours":24,"subject_template":"Following up on your quote  -  {{service}}","body_template":"Hi {{first_name}},  Thank you for the opportunity to quote on {{service}} for {{company}}. I wanted to check in and see if you had any questions about the proposal I sent through.  I''m confident we can deliver excellent results for you, and I''m happy to adjust the scope if needed.  {{sender_name}}"},
   {"step_order":2,"type":"email","delay_hours":96,"subject_template":"Quote still valid  -  {{service}}","body_template":"Hi {{first_name}},  Just following up on the quote for {{service}}. The pricing is valid until {{expiry_date}}, after which I''ll need to recalculate based on current costs.  If you have any concerns about the proposal or would like to discuss scope, I''d be happy to jump on a call.  {{sender_name}}"},
   {"step_order":3,"type":"email","delay_hours":168,"subject_template":"Final follow-up  -  {{service}} quote","body_template":"Hi {{first_name}},  I''m sending one last note regarding the quote for {{service}}. If the timing isn''t right, no worries at all  -  please keep us in mind for the future.  If you''d like to proceed or revisit the proposal at a later date, I''m always here.  Best regards, {{sender_name}}"}
 ]',
 1, 'professional_services', 'Professional Services Pack'),

('au-profservices-invoice-reminder',
 'Professional Services: Invoice Reminder',
 'email', 2,
 '[
   {"step_order":1,"type":"email","delay_hours":0,"subject_template":"Friendly reminder: Invoice #{{invoice_number}} due {{due_date}}","body_template":"Hi {{first_name}},  I hope this finds you well. I''m writing to send a friendly reminder that Invoice #{{invoice_number}} for {{amount}} (inc. GST) is due on {{due_date}}.  If you''ve already arranged payment, please disregard this message. If you have any questions or need a copy of the invoice, please don''t hesitate to get in touch.  Thank you for your business!  {{sender_name}}"},
   {"step_order":2,"type":"email","delay_hours":120,"subject_template":"Payment overdue: Invoice #{{invoice_number}}","body_template":"Hi {{first_name}},  I''m following up on Invoice #{{invoice_number}} for {{amount}} which was due on {{due_date}}.  If there''s an issue with the invoice or you''re experiencing difficulties, please reach out and we can discuss options. Otherwise, we''d appreciate payment at your earliest convenience.  BANK DETAILS: Bank: {{bank_name}} BSB: {{bsb}} Account: {{account_number}} Ref: {{invoice_number}}  Thank you, {{sender_name}}"}
 ]',
 1, 'professional_services', 'Professional Services Pack');
