-- Migration 025: sequence_templates table
-- Stores system (and future user-created) campaign sequence templates for the AU market.
-- Steps are stored as JSON in steps_json to avoid a normalised join on every list load.

CREATE TABLE IF NOT EXISTS sequence_templates (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    slug        VARCHAR(80)  NOT NULL,
    name        VARCHAR(120) NOT NULL,
    channel     ENUM('linkedin','email','mixed','x','instagram','facebook') NOT NULL DEFAULT 'email',
    steps_count TINYINT UNSIGNED NOT NULL DEFAULT 1,
    steps_json  JSON NOT NULL,
    is_system   TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1 = shipped with product, 0 = user-created',
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_st_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -- Seed 5 AU-market templates ---------------------------------------------

INSERT IGNORE INTO sequence_templates (slug, name, channel, steps_count, steps_json, is_system) VALUES

('au-linkedin-cold',
 'LinkedIn Cold Outreach (AU)',
 'linkedin',
 3,
 '[
   {"step_order":1,"type":"connect_request","delay_hours":0,"body_template":"Hi {{first_name}}, I noticed your work at {{company}} and thought it would be worth connecting. Happy to chat about {{value_prop}}."},
   {"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}}! I help AU businesses like {{company}} with {{value_prop}}. Would a 15-min call this week work for you?"}
 ]',
 1),

('au-email-cold-nurture',
 'Email Cold ? Warm Nurture (AU)',
 'email',
 4,
 '[
   {"step_order":1,"type":"email","delay_hours":0,"subject":"Quick question, {{first_name}}","body_template":"Hi {{first_name}},  I came across {{company}} and think there''s a great fit with what we do at {{sender_company}}.  Would you be open to a quick 10-min chat?  {{signature}}"},
   {"step_order":2,"type":"wait","delay_hours":72,"body_template":null},
   {"step_order":3,"type":"email","delay_hours":0,"subject":"Following up, {{first_name}}","body_template":"Hi {{first_name}},  Just wanted to bump this to the top of your inbox. We''ve helped similar AU businesses achieve {{outcome}}.  Happy to share a quick case study if useful.  {{signature}}"},
   {"step_order":4,"type":"email","delay_hours":72,"subject":"Last touch  -  {{first_name}}","body_template":"Hi {{first_name}},  I''ll keep this brief  -  if the timing isn''t right, no worries at all. If you''d like to reconnect later, just reply ''later'' and I''ll check back in a few months.  {{signature}}"}
 ]',
 1),

('au-post-event-followup',
 'Post-Event Follow-Up (AU)',
 'email',
 2,
 '[
   {"step_order":1,"type":"email","delay_hours":24,"subject":"Great meeting you at {{event_name}}, {{first_name}}","body_template":"Hi {{first_name}},  Really enjoyed our chat at {{event_name}} yesterday! As promised, here''s the resource I mentioned: {{resource_link}}.  Would love to continue the conversation  -  are you free for a call next week?  {{signature}}"},
   {"step_order":2,"type":"email","delay_hours":120,"subject":"Still worth a chat, {{first_name}}?","body_template":"Hi {{first_name}},  Following up on my note from earlier this week. Let me know if a 15-min call suits  -  happy to work around your schedule.  {{signature}}"}
 ]',
 1),

('au-re-engagement',
 'Re-engagement Sequence (AU)',
 'mixed',
 3,
 '[
   {"step_order":1,"type":"email","delay_hours":0,"subject":"We miss you, {{first_name}}!","body_template":"Hi {{first_name}},  It''s been a while since we last connected. A lot has changed at {{sender_company}}  -  including {{new_feature}}.  Would love to catch up  -  are you still the right person to speak with at {{company}}?  {{signature}}"},
   {"step_order":2,"type":"wait","delay_hours":168,"body_template":null},
   {"step_order":3,"type":"linkedin_dm","delay_hours":0,"body_template":"Hi {{first_name}}, just circling back on my email from last week. Happy to keep things brief  -  would a quick call this week work?"}
 ]',
 1),

('au-referral-request',
 'Referral Request (AU)',
 'email',
 2,
 '[
   {"step_order":1,"type":"email","delay_hours":0,"subject":"A small favour, {{first_name}}","body_template":"Hi {{first_name}},  I hope things are going well at {{company}}! I''m reaching out to ask if you know anyone in your network who might benefit from {{value_prop}}.  Even a warm intro means the world to a growing AU business like ours.  In return, I''d love to offer you {{referral_incentive}}.  Thanks so much!  {{signature}}"},
   {"step_order":2,"type":"email","delay_hours":72,"subject":"Gentle reminder  -  referral intro","body_template":"Hi {{first_name}},  Just a gentle nudge on my last note. If you can think of anyone, even a quick LinkedIn introduction works perfectly.  Thanks again, {{first_name}}!  {{signature}}"}
 ]',
 1);
