-- =====================================================
-- Coastal Command - Automated Follow-ups System
-- Purpose: Automated customer communication triggers
-- Date: 2026-01-26
-- =====================================================

-- =====================================================
-- MESSAGE TEMPLATES
-- Reusable templates for emails and SMS
-- =====================================================
CREATE TABLE IF NOT EXISTS message_templates (
    template_id INT AUTO_INCREMENT PRIMARY KEY,
    template_name VARCHAR(100) NOT NULL,
    template_key VARCHAR(50) NOT NULL UNIQUE,
    channel ENUM('email', 'sms', 'both') NOT NULL DEFAULT 'email',

    -- Email fields
    email_subject VARCHAR(255),
    email_body TEXT,

    -- SMS fields (160 char limit recommended)
    sms_body VARCHAR(500),

    -- Template variables: {{first_name}}, {{last_name}}, {{service_date}}, {{amount}}, {{review_link}}, {{company_phone}}

    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_template_key (template_key),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- AUTOMATED CAMPAIGNS
-- Defines triggers and timing for automated messages
-- =====================================================
CREATE TABLE IF NOT EXISTS automated_campaigns (
    campaign_id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_name VARCHAR(100) NOT NULL,
    campaign_key VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,

    -- Trigger type
    trigger_type ENUM(
        'post_service',           -- After service completed
        'invoice_overdue',        -- Invoice past due
        'service_reminder',       -- Upcoming service reminder
        'inactive_customer',      -- Customer hasn't ordered in X days
        'quote_followup',         -- Quote sent, no response
        'welcome',                -- New customer welcome
        'review_request',         -- Request review after service
        'birthday',               -- Customer birthday (if stored)
        'seasonal'                -- Seasonal promotions
    ) NOT NULL,

    -- Timing
    delay_days INT DEFAULT 0,              -- Days after trigger event
    delay_hours INT DEFAULT 0,             -- Hours after trigger event
    send_time TIME DEFAULT '10:00:00',     -- Preferred send time (local)

    -- Conditions
    min_days_since_last_contact INT DEFAULT 1,  -- Don't spam
    max_sends_per_customer INT DEFAULT 1,       -- Per campaign cycle

    -- Template reference
    template_id INT NOT NULL,

    -- Status
    status ENUM('active', 'paused', 'inactive') DEFAULT 'active',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (template_id) REFERENCES message_templates(template_id),
    INDEX idx_trigger (trigger_type),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CAMPAIGN MESSAGE LOG
-- Tracks all automated messages sent
-- =====================================================
CREATE TABLE IF NOT EXISTS campaign_message_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NOT NULL,
    customer_id INT NOT NULL,

    -- Reference to triggering event
    trigger_reference_type ENUM('order', 'invoice', 'quote', 'subscription', 'manual') NOT NULL,
    trigger_reference_id INT,

    -- Message details
    channel ENUM('email', 'sms') NOT NULL,
    recipient VARCHAR(255) NOT NULL,  -- Email or phone
    subject VARCHAR(255),
    message_body TEXT,

    -- Status tracking
    status ENUM('queued', 'sent', 'delivered', 'failed', 'bounced', 'opened', 'clicked') DEFAULT 'queued',

    -- External IDs
    external_message_id VARCHAR(255),  -- Twilio SID or Gmail message ID

    -- Timestamps
    scheduled_at DATETIME NOT NULL,
    sent_at DATETIME,
    delivered_at DATETIME,
    opened_at DATETIME,
    clicked_at DATETIME,

    -- Error tracking
    error_message TEXT,
    retry_count INT DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (campaign_id) REFERENCES automated_campaigns(campaign_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),

    INDEX idx_campaign (campaign_id),
    INDEX idx_customer (customer_id),
    INDEX idx_status (status),
    INDEX idx_scheduled (scheduled_at),
    INDEX idx_trigger_ref (trigger_reference_type, trigger_reference_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CUSTOMER COMMUNICATION PREFERENCES
-- Opt-out tracking for compliance
-- =====================================================
CREATE TABLE IF NOT EXISTS customer_comm_preferences (
    preference_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL UNIQUE,

    -- Opt-in/out settings
    email_marketing TINYINT(1) DEFAULT 1,
    sms_marketing TINYINT(1) DEFAULT 1,
    email_transactional TINYINT(1) DEFAULT 1,
    sms_transactional TINYINT(1) DEFAULT 1,
    review_requests TINYINT(1) DEFAULT 1,

    -- Unsubscribe tracking
    email_unsubscribed_at DATETIME,
    sms_unsubscribed_at DATETIME,
    unsubscribe_reason VARCHAR(255),

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
    INDEX idx_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- DEFAULT MESSAGE TEMPLATES
-- =====================================================

-- Review Request (Post-Service)
INSERT INTO message_templates (template_name, template_key, channel, email_subject, email_body, sms_body) VALUES
('Review Request', 'review_request', 'both',
'How did we do, {{first_name}}?',
'<p>Hi {{first_name}},</p>
<p>Thank you for choosing Coastal Command Bin Cleaning! We hope your bins are sparkling clean.</p>
<p>We''d love to hear about your experience. Would you take a moment to leave us a review?</p>
<p><a href="{{google_review_link}}" style="background: #00a86b; color: white; padding: 12px 24px; text-decoration: none; border-radius: 6px; display: inline-block; margin: 10px 0;">Leave a Google Review</a></p>
<p>Your feedback helps other Florida families discover our services!</p>
<p>Thank you,<br>The Coastal Command Team<br>(850) 818-8318</p>',
'Hi {{first_name}}! Thanks for choosing Coastal Command. We''d love your feedback! Leave a review: {{google_review_link}} - Reply STOP to opt out');

-- Invoice Reminder (3 days overdue)
INSERT INTO message_templates (template_name, template_key, channel, email_subject, email_body, sms_body) VALUES
('Invoice Reminder - 3 Days', 'invoice_reminder_3', 'both',
'Friendly Reminder: Invoice #{{invoice_number}} Due',
'<p>Hi {{first_name}},</p>
<p>This is a friendly reminder that Invoice #{{invoice_number}} for ${{amount}} is now past due.</p>
<p>If you''ve already sent payment, please disregard this message. Otherwise, you can pay securely online:</p>
<p><a href="{{payment_link}}" style="background: #00a86b; color: white; padding: 12px 24px; text-decoration: none; border-radius: 6px; display: inline-block; margin: 10px 0;">Pay Now</a></p>
<p>Questions? Call us at (850) 818-8318.</p>
<p>Thank you,<br>Coastal Command Bin Cleaning</p>',
'Hi {{first_name}}, friendly reminder: Invoice #{{invoice_number}} (${{amount}}) is past due. Pay online: {{payment_link}} or call (850) 818-8318');

-- Invoice Reminder (7 days overdue)
INSERT INTO message_templates (template_name, template_key, channel, email_subject, email_body, sms_body) VALUES
('Invoice Reminder - 7 Days', 'invoice_reminder_7', 'email',
'Second Notice: Invoice #{{invoice_number}} - Payment Required',
'<p>Hi {{first_name}},</p>
<p>Our records show that Invoice #{{invoice_number}} for ${{amount}} is now 7 days past due.</p>
<p>Please submit payment at your earliest convenience to avoid any service interruption.</p>
<p><a href="{{payment_link}}" style="background: #ff6b35; color: white; padding: 12px 24px; text-decoration: none; border-radius: 6px; display: inline-block; margin: 10px 0;">Pay Now</a></p>
<p>If you''re experiencing difficulties, please contact us at (850) 818-8318 to discuss payment options.</p>
<p>Thank you,<br>Coastal Command Bin Cleaning</p>',
NULL);

-- Invoice Reminder (14 days overdue)
INSERT INTO message_templates (template_name, template_key, channel, email_subject, email_body, sms_body) VALUES
('Invoice Reminder - 14 Days', 'invoice_reminder_14', 'email',
'Final Notice: Invoice #{{invoice_number}} - Immediate Payment Required',
'<p>Hi {{first_name}},</p>
<p><strong>This is a final notice.</strong> Invoice #{{invoice_number}} for ${{amount}} is now 14 days past due.</p>
<p>To avoid service suspension, please submit payment immediately:</p>
<p><a href="{{payment_link}}" style="background: #dc3545; color: white; padding: 12px 24px; text-decoration: none; border-radius: 6px; display: inline-block; margin: 10px 0;">Pay Now - Final Notice</a></p>
<p>If there''s an issue with your account, please call us at (850) 818-8318 right away.</p>
<p>Coastal Command Bin Cleaning</p>',
NULL);

-- Service Reminder (Upcoming)
INSERT INTO message_templates (template_name, template_key, channel, email_subject, email_body, sms_body) VALUES
('Service Reminder', 'service_reminder', 'both',
'Reminder: Bin Cleaning Scheduled for {{service_date}}',
'<p>Hi {{first_name}},</p>
<p>Just a reminder that your bin cleaning service is scheduled for <strong>{{service_date}}</strong>.</p>
<p><strong>Please remember to:</strong></p>
<ul>
<li>Leave your bins curbside after trash pickup</li>
<li>Ensure bins are accessible</li>
</ul>
<p>No need to be home - we''ll take care of everything and notify you when complete!</p>
<p>Questions? Call (850) 818-8318</p>
<p>Thank you,<br>Coastal Command Bin Cleaning</p>',
'Hi {{first_name}}! Reminder: Your bin cleaning is scheduled for {{service_date}}. Please leave bins curbside after trash pickup. Questions? (850) 818-8318');

-- Inactive Customer (90 days)
INSERT INTO message_templates (template_name, template_key, channel, email_subject, email_body, sms_body) VALUES
('We Miss You', 'inactive_90_days', 'email',
'{{first_name}}, we miss you! Time for a bin refresh?',
'<p>Hi {{first_name}},</p>
<p>It''s been a while since your last bin cleaning, and we wanted to check in!</p>
<p>Florida''s heat and humidity mean bacteria and odors build up fast. If it''s been 90+ days, your bins could use some attention.</p>
<p><strong>Book now and get 10% off your next service:</strong></p>
<p><a href="{{booking_link}}?promo=WELCOMEBACK10" style="background: #00a86b; color: white; padding: 12px 24px; text-decoration: none; border-radius: 6px; display: inline-block; margin: 10px 0;">Schedule Now - 10% Off</a></p>
<p>We''d love to have you back!</p>
<p>The Coastal Command Team<br>(850) 818-8318</p>',
NULL);

-- Welcome Email (New Customer)
INSERT INTO message_templates (template_name, template_key, channel, email_subject, email_body, sms_body) VALUES
('Welcome New Customer', 'welcome', 'email',
'Welcome to Coastal Command, {{first_name}}!',
'<p>Hi {{first_name}},</p>
<p>Welcome to the Coastal Command family! We''re excited to help keep your bins clean and your home healthier.</p>
<p><strong>What happens next:</strong></p>
<ol>
<li>Your first service is scheduled for <strong>{{service_date}}</strong></li>
<li>Leave your bins curbside after trash pickup</li>
<li>We''ll clean, sanitize, and return them to your curb</li>
<li>You''ll receive a notification when complete</li>
</ol>
<p><strong>Your Customer Portal:</strong></p>
<p>Log in anytime to view your schedule, update your info, or contact us:</p>
<p><a href="{{portal_link}}" style="background: #00a86b; color: white; padding: 12px 24px; text-decoration: none; border-radius: 6px; display: inline-block; margin: 10px 0;">Access My Account</a></p>
<p>Questions? We''re here to help: (850) 818-8318 or info@coastalcommandfl.com</p>
<p>Thank you for choosing us!</p>
<p>Joe Collins, Owner<br>Coastal Command Bin Cleaning</p>',
NULL);

-- =====================================================
-- DEFAULT AUTOMATED CAMPAIGNS
-- =====================================================

-- Get template IDs for campaigns
SET @review_template = (SELECT template_id FROM message_templates WHERE template_key = 'review_request');
SET @invoice_3_template = (SELECT template_id FROM message_templates WHERE template_key = 'invoice_reminder_3');
SET @invoice_7_template = (SELECT template_id FROM message_templates WHERE template_key = 'invoice_reminder_7');
SET @invoice_14_template = (SELECT template_id FROM message_templates WHERE template_key = 'invoice_reminder_14');
SET @service_reminder_template = (SELECT template_id FROM message_templates WHERE template_key = 'service_reminder');
SET @inactive_template = (SELECT template_id FROM message_templates WHERE template_key = 'inactive_90_days');
SET @welcome_template = (SELECT template_id FROM message_templates WHERE template_key = 'welcome');

-- Review Request (1 day after service)
INSERT INTO automated_campaigns (campaign_name, campaign_key, description, trigger_type, delay_days, delay_hours, send_time, template_id, status) VALUES
('Post-Service Review Request', 'review_after_service', 'Request Google review 1 day after service completion', 'review_request', 1, 0, '10:00:00', @review_template, 'active');

-- Invoice Reminders
INSERT INTO automated_campaigns (campaign_name, campaign_key, description, trigger_type, delay_days, delay_hours, send_time, template_id, status) VALUES
('Invoice Reminder - 3 Days', 'invoice_overdue_3', 'First reminder 3 days after invoice due date', 'invoice_overdue', 3, 0, '09:00:00', @invoice_3_template, 'active'),
('Invoice Reminder - 7 Days', 'invoice_overdue_7', 'Second reminder 7 days after invoice due date', 'invoice_overdue', 7, 0, '09:00:00', @invoice_7_template, 'active'),
('Invoice Reminder - 14 Days', 'invoice_overdue_14', 'Final notice 14 days after invoice due date', 'invoice_overdue', 14, 0, '09:00:00', @invoice_14_template, 'active');

-- Service Reminder (1 day before)
INSERT INTO automated_campaigns (campaign_name, campaign_key, description, trigger_type, delay_days, delay_hours, send_time, template_id, status) VALUES
('Service Reminder - 1 Day Before', 'service_reminder_1day', 'Remind customer 1 day before scheduled service', 'service_reminder', -1, 0, '18:00:00', @service_reminder_template, 'active');

-- Inactive Customer (90 days)
INSERT INTO automated_campaigns (campaign_name, campaign_key, description, trigger_type, delay_days, delay_hours, send_time, template_id, status) VALUES
('Inactive Customer - 90 Days', 'inactive_90', 'Re-engagement email after 90 days of no orders', 'inactive_customer', 90, 0, '10:00:00', @inactive_template, 'active');

-- Welcome Email (immediate)
INSERT INTO automated_campaigns (campaign_name, campaign_key, description, trigger_type, delay_days, delay_hours, send_time, template_id, status) VALUES
('Welcome New Customer', 'welcome_new', 'Welcome email sent immediately after signup', 'welcome', 0, 0, NULL, @welcome_template, 'active');

-- =====================================================
-- VERIFICATION
-- =====================================================
-- SELECT * FROM message_templates;
-- SELECT * FROM automated_campaigns;
