-- Default email template categories + starter templates

UPDATE email_template_categories
SET name = 'Welcome & Onboarding',
    description = 'First-touch emails for new contacts and subscribers',
    sort_order = 1
WHERE slug = 'general' AND deleted_at IS NULL;

INSERT INTO email_template_categories (name, slug, description, sort_order, status)
SELECT 'Sales Outreach', 'sales-outreach', 'Cold outreach, introductions, and meeting requests', 2, 'active'
WHERE NOT EXISTS (SELECT 1 FROM email_template_categories WHERE slug = 'sales-outreach');

INSERT INTO email_template_categories (name, slug, description, sort_order, status)
SELECT 'Follow-up & Nurture', 'follow-up-nurture', 'Reminders, check-ins, and pipeline nurture sequences', 3, 'active'
WHERE NOT EXISTS (SELECT 1 FROM email_template_categories WHERE slug = 'follow-up-nurture');

INSERT INTO email_template_categories (name, slug, description, sort_order, status)
SELECT 'Product Announcements', 'product-announcements', 'Launches, feature updates, and release notes', 4, 'active'
WHERE NOT EXISTS (SELECT 1 FROM email_template_categories WHERE slug = 'product-announcements');

INSERT INTO email_template_categories (name, slug, description, sort_order, status)
SELECT 'Customer Success', 'customer-success', 'Renewals, feedback requests, and account health', 5, 'active'
WHERE NOT EXISTS (SELECT 1 FROM email_template_categories WHERE slug = 'customer-success');

-- Helper: resolve category id by slug
SET @cat_welcome = (SELECT id FROM email_template_categories WHERE slug = 'general' AND deleted_at IS NULL LIMIT 1);
SET @cat_sales = (SELECT id FROM email_template_categories WHERE slug = 'sales-outreach' AND deleted_at IS NULL LIMIT 1);
SET @cat_nurture = (SELECT id FROM email_template_categories WHERE slug = 'follow-up-nurture' AND deleted_at IS NULL LIMIT 1);
SET @cat_product = (SELECT id FROM email_template_categories WHERE slug = 'product-announcements' AND deleted_at IS NULL LIMIT 1);
SET @cat_success = (SELECT id FROM email_template_categories WHERE slug = 'customer-success' AND deleted_at IS NULL LIMIT 1);

INSERT INTO email_templates (category_id, name, slug, subject, body_html, description, status, is_system)
SELECT @cat_welcome, 'Warm Welcome', 'warm-welcome',
  'Welcome to {{company.name}}, {{contact.name}}!',
  '<p>Hi {{contact.name}},</p><p>Thank you for connecting with <strong>{{company.name}}</strong>. We''re glad you''re here.</p><p>Our team is ready to help you get the most from our platform. If you have any questions, reply to this email or reach us at {{company.support_email}}.</p><p>Best regards,<br>{{company.from_name}}</p>',
  'Friendly welcome for new contacts', 'active', 1
WHERE NOT EXISTS (SELECT 1 FROM email_templates WHERE slug = 'warm-welcome');

INSERT INTO email_templates (category_id, name, slug, subject, body_html, description, status, is_system)
SELECT @cat_welcome, 'Account Setup Guide', 'account-setup-guide',
  '{{contact.name}}, your {{company.name}} account is ready',
  '<h2>Let''s get you started</h2><p>Hello {{contact.name}},</p><p>Your account is active. Here''s what to do next:</p><ul><li>Complete your profile</li><li>Invite your team</li><li>Explore the dashboard</li></ul><p>Need help? Contact {{company.support_email}} — we typically respond within one business day.</p><p>Cheers,<br>{{company.from_name}}<br>{{company.from_email}}</p>',
  'Onboarding checklist email', 'active', 1
WHERE NOT EXISTS (SELECT 1 FROM email_templates WHERE slug = 'account-setup-guide');

INSERT INTO email_templates (category_id, name, slug, subject, body_html, description, status, is_system)
SELECT @cat_sales, 'Introduction Outreach', 'introduction-outreach',
  'Quick intro from {{company.name}} — {{contact.name}}',
  '<p>Hi {{contact.name}},</p><p>I''m reaching out from <strong>{{company.name}}</strong>. We help teams like yours streamline operations and grow revenue.</p><p>Would you be open to a 15-minute call this week? I''d love to learn about your goals in {{contact.industry}} and share how we can help.</p><p>Looking forward to hearing from you,<br>{{company.from_name}}<br>{{company.from_email}}</p>',
  'Short cold intro with meeting ask', 'active', 1
WHERE NOT EXISTS (SELECT 1 FROM email_templates WHERE slug = 'introduction-outreach');

INSERT INTO email_templates (category_id, name, slug, subject, body_html, description, status, is_system)
SELECT @cat_sales, 'Meeting Request', 'meeting-request',
  '{{contact.name}}, can we schedule a quick chat?',
  '<p>Hello {{contact.name}},</p><p>Following up on my previous note — I''d appreciate the chance to connect and discuss how {{company.name}} can support your team.</p><p>Please reply with a time that works, or book directly via our calendar link.</p><p>Thank you,<br>{{company.from_name}}<br>{{company.reply_to}}</p>',
  'Direct meeting request follow-up', 'active', 1
WHERE NOT EXISTS (SELECT 1 FROM email_templates WHERE slug = 'meeting-request');

INSERT INTO email_templates (category_id, name, slug, subject, body_html, description, status, is_system)
SELECT @cat_nurture, 'Gentle Follow-up', 'gentle-follow-up',
  'Following up — {{contact.name}}',
  '<p>Hi {{contact.name}},</p><p>Just checking in to see if you had a chance to review my last message. No pressure — I know inboxes get busy.</p><p>If now isn''t the right time, let me know and I''ll follow up later. Otherwise, I''m happy to answer any questions.</p><p>Best,<br>{{company.from_name}}</p>',
  'Soft nurture follow-up', 'active', 1
WHERE NOT EXISTS (SELECT 1 FROM email_templates WHERE slug = 'gentle-follow-up');

INSERT INTO email_templates (category_id, name, slug, subject, body_html, description, status, is_system)
SELECT @cat_nurture, 'Value Add Check-in', 'value-add-check-in',
  'Thought you might find this useful, {{contact.name}}',
  '<p>Hello {{contact.name}},</p><p>I came across an insight that might be relevant for your work in {{contact.city}}. I wanted to share it in case it helps your team this quarter.</p><p>If you''d like to dig deeper, reply anytime — I''m here to help.</p><p>Warm regards,<br>{{company.from_name}}<br>{{company.support_email}}</p>',
  'Nurture email with value-first tone', 'active', 1
WHERE NOT EXISTS (SELECT 1 FROM email_templates WHERE slug = 'value-add-check-in');

INSERT INTO email_templates (category_id, name, slug, subject, body_html, description, status, is_system)
SELECT @cat_product, 'Feature Launch', 'feature-launch',
  'New from {{company.name}} — built for teams like yours',
  '<h2>We just shipped something big</h2><p>Hi {{contact.name}},</p><p>We''re excited to announce a major update to {{company.name}}. This release is designed to save your team time and improve visibility across projects.</p><p><strong>What''s new:</strong></p><ul><li>Faster workflows</li><li>Smarter notifications</li><li>Improved reporting</li></ul><p>Log in to explore, or reply if you''d like a guided tour.</p><p>— The {{company.name}} Team</p>',
  'Product launch announcement', 'active', 1
WHERE NOT EXISTS (SELECT 1 FROM email_templates WHERE slug = 'feature-launch');

INSERT INTO email_templates (category_id, name, slug, subject, body_html, description, status, is_system)
SELECT @cat_product, 'Monthly Product Digest', 'monthly-product-digest',
  'Your {{company.name}} update for {{contact.name}}',
  '<p>Hello {{contact.name}},</p><p>Here''s your monthly roundup of improvements, tips, and resources from {{company.name}}.</p><p>We''ve been listening to feedback from customers in {{contact.country}} and beyond — thank you for helping us build a better product.</p><p>Questions? Reach us at {{company.support_email}}.</p><p>Best,<br>{{company.from_name}}</p>',
  'Recurring product newsletter template', 'active', 1
WHERE NOT EXISTS (SELECT 1 FROM email_templates WHERE slug = 'monthly-product-digest');

INSERT INTO email_templates (category_id, name, slug, subject, body_html, description, status, is_system)
SELECT @cat_success, 'Renewal Reminder', 'renewal-reminder',
  'Your {{company.name}} subscription — action needed',
  '<p>Hi {{contact.name}},</p><p>Your subscription with {{company.name}} is coming up for renewal. We''d love to continue supporting you and your team.</p><p>Please review your plan or contact us at {{company.support_email}} if you need any changes before renewal.</p><p>Thank you for being a valued customer,<br>{{company.from_name}}</p>',
  'Subscription renewal notice', 'active', 1
WHERE NOT EXISTS (SELECT 1 FROM email_templates WHERE slug = 'renewal-reminder');

INSERT INTO email_templates (category_id, name, slug, subject, body_html, description, status, is_system)
SELECT @cat_success, 'Feedback Request', 'feedback-request',
  '{{contact.name}}, how are we doing?',
  '<p>Hello {{contact.name}},</p><p>Your opinion matters to us at {{company.name}}. Would you take two minutes to share how we''re doing?</p><p>Your feedback helps us improve for teams in {{contact.industry}} and around the world.</p><p>Reply to this email or contact {{company.support_email}} — we read every response.</p><p>With gratitude,<br>{{company.from_name}}<br>{{company.reply_to}}</p>',
  'Customer satisfaction / NPS style request', 'active', 1
WHERE NOT EXISTS (SELECT 1 FROM email_templates WHERE slug = 'feedback-request');