-- ============================================================================
-- Phase 7 Task 2: Payment Reminder Email Templates System
-- ============================================================================
-- Description: Database-stored email templates with hierarchical configuration
--              Supports: org → department → location → user_group levels
-- Author: AI Assistant
-- Date: October 18, 2025
-- ============================================================================

-- ============================================================================
-- Table: email_templates
-- Purpose: Store configurable email templates with hierarchical scope
-- ============================================================================
CREATE TABLE IF NOT EXISTS email_templates (
    template_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    
    -- Scope Configuration (hierarchical)
    org_id UUID REFERENCES organizations(org_id),
    department_id UUID REFERENCES departments(department_id),
    location_id UUID REFERENCES locations(location_id),
    user_group_id UUID,  -- Future: REFERENCES user_groups(user_group_id)
    
    -- Template Identification
    template_name VARCHAR(100) NOT NULL,  -- e.g., 'payment_reminder_7_days_before'
    template_type VARCHAR(50) NOT NULL,   -- 'payment_reminder', 'invoice', 'quotation', etc.
    stage VARCHAR(50),                    -- For payment reminders: '7_days_before', 'due_date', etc.
    
    -- Template Content
    subject_line TEXT NOT NULL,
    html_body TEXT NOT NULL,
    text_body TEXT,                       -- Plain text fallback
    
    -- Template Metadata
    description TEXT,
    available_variables JSONB DEFAULT '[]'::JSONB,  -- List of {{variable}} names
    preview_data JSONB,                   -- Sample data for preview
    
    -- Configuration
    is_active BOOLEAN DEFAULT true,
    is_default BOOLEAN DEFAULT false,     -- System default template
    priority INTEGER DEFAULT 100,         -- Lower = higher priority in hierarchy
    
    -- Styling
    custom_css TEXT,                      -- Custom CSS for this template
    header_image_url TEXT,
    footer_text TEXT,
    brand_color VARCHAR(7) DEFAULT '#3B82F6',  -- Tailwind blue-500
    
    -- Audit
    created_by UUID REFERENCES users(user_id),
    updated_by UUID REFERENCES users(user_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    isdeleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP,
    deleted_by UUID REFERENCES users(user_id),
    
    -- Constraints
    CONSTRAINT unique_template_scope UNIQUE NULLS NOT DISTINCT (
        org_id, department_id, location_id, user_group_id, 
        template_type, stage
    ),
    CONSTRAINT valid_template_type CHECK (template_type IN (
        'payment_reminder', 'invoice', 'quotation', 'sales_receipt', 
        'statement', 'welcome', 'notification', 'custom'
    ))
);

-- ============================================================================
-- Indexes for email_templates
-- ============================================================================
CREATE INDEX idx_email_templates_org ON email_templates(org_id) WHERE isdeleted = false;
CREATE INDEX idx_email_templates_dept ON email_templates(department_id) WHERE isdeleted = false;
CREATE INDEX idx_email_templates_location ON email_templates(location_id) WHERE isdeleted = false;
CREATE INDEX idx_email_templates_type_stage ON email_templates(template_type, stage) WHERE isdeleted = false;
CREATE INDEX idx_email_templates_active ON email_templates(is_active) WHERE isdeleted = false;
CREATE INDEX idx_email_templates_default ON email_templates(is_default) WHERE isdeleted = false;
CREATE INDEX idx_email_templates_priority ON email_templates(priority);

-- ============================================================================
-- Comments
-- ============================================================================
COMMENT ON TABLE email_templates IS 'Hierarchical email template storage with multi-level configuration support';
COMMENT ON COLUMN email_templates.org_id IS 'Organization level (highest specificity)';
COMMENT ON COLUMN email_templates.department_id IS 'Department level (mid specificity)';
COMMENT ON COLUMN email_templates.location_id IS 'Location/branch level (mid specificity)';
COMMENT ON COLUMN email_templates.user_group_id IS 'User group level (lowest specificity)';
COMMENT ON COLUMN email_templates.priority IS 'Template selection priority: org(10) > dept(20) > location(30) > group(40) > default(100)';
COMMENT ON COLUMN email_templates.available_variables IS 'Array of available template variables: ["customer_name", "invoice_number", etc.]';
COMMENT ON COLUMN email_templates.preview_data IS 'Sample data for template preview in UI';

-- ============================================================================
-- Function: get_email_template
-- Purpose: Retrieve best matching template based on hierarchical scope
-- Parameters:
--   p_template_type: Type of template (e.g., 'payment_reminder')
--   p_stage: Stage name (e.g., '7_days_before')
--   p_org_id: Organization ID
--   p_department_id: Department ID (optional)
--   p_location_id: Location ID (optional)
--   p_user_group_id: User group ID (optional)
-- Returns: Single template record with highest priority match
-- ============================================================================
CREATE OR REPLACE FUNCTION get_email_template(
    p_template_type VARCHAR,
    p_stage VARCHAR DEFAULT NULL,
    p_org_id UUID DEFAULT NULL,
    p_department_id UUID DEFAULT NULL,
    p_location_id UUID DEFAULT NULL,
    p_user_group_id UUID DEFAULT NULL
)
RETURNS TABLE (
    template_id UUID,
    template_name VARCHAR,
    subject_line TEXT,
    html_body TEXT,
    text_body TEXT,
    custom_css TEXT,
    header_image_url TEXT,
    footer_text TEXT,
    brand_color VARCHAR,
    available_variables JSONB,
    scope_level VARCHAR,
    priority INTEGER
) AS $$
BEGIN
    -- Hierarchical template lookup with priority:
    -- 1. Most specific scope (org + dept + location + group)
    -- 2. Org + dept + location
    -- 3. Org + dept
    -- 4. Org only
    -- 5. System default
    
    RETURN QUERY
    SELECT 
        t.template_id,
        t.template_name,
        t.subject_line,
        t.html_body,
        t.text_body,
        t.custom_css,
        t.header_image_url,
        t.footer_text,
        t.brand_color,
        t.available_variables,
        CASE
            WHEN t.org_id IS NOT NULL AND t.department_id IS NOT NULL 
                 AND t.location_id IS NOT NULL AND t.user_group_id IS NOT NULL 
                THEN 'org_dept_location_group'
            WHEN t.org_id IS NOT NULL AND t.department_id IS NOT NULL 
                 AND t.location_id IS NOT NULL 
                THEN 'org_dept_location'
            WHEN t.org_id IS NOT NULL AND t.department_id IS NOT NULL 
                THEN 'org_dept'
            WHEN t.org_id IS NOT NULL 
                THEN 'org'
            WHEN t.is_default = true 
                THEN 'system_default'
            ELSE 'unknown'
        END::VARCHAR AS scope_level,
        t.priority
    FROM email_templates t
    WHERE t.template_type = p_template_type
      AND (p_stage IS NULL OR t.stage = p_stage)
      AND t.is_active = true
      AND t.isdeleted = false
      AND (
          -- Exact match at most specific level
          (t.org_id = p_org_id AND t.department_id = p_department_id 
           AND t.location_id = p_location_id AND t.user_group_id = p_user_group_id)
          OR
          -- Org + dept + location match
          (t.org_id = p_org_id AND t.department_id = p_department_id 
           AND t.location_id = p_location_id AND t.user_group_id IS NULL)
          OR
          -- Org + dept match
          (t.org_id = p_org_id AND t.department_id = p_department_id 
           AND t.location_id IS NULL AND t.user_group_id IS NULL)
          OR
          -- Org only match
          (t.org_id = p_org_id AND t.department_id IS NULL 
           AND t.location_id IS NULL AND t.user_group_id IS NULL)
          OR
          -- System default
          (t.is_default = true AND t.org_id IS NULL)
      )
    ORDER BY 
        -- Most specific first
        CASE 
            WHEN t.org_id IS NOT NULL AND t.department_id IS NOT NULL 
                 AND t.location_id IS NOT NULL AND t.user_group_id IS NOT NULL THEN 1
            WHEN t.org_id IS NOT NULL AND t.department_id IS NOT NULL 
                 AND t.location_id IS NOT NULL THEN 2
            WHEN t.org_id IS NOT NULL AND t.department_id IS NOT NULL THEN 3
            WHEN t.org_id IS NOT NULL THEN 4
            WHEN t.is_default = true THEN 5
            ELSE 6
        END,
        t.priority ASC,
        t.created_at DESC
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_email_template IS 'Retrieves best matching email template based on hierarchical scope (org → dept → location → group → default)';

-- ============================================================================
-- Insert Default Payment Reminder Templates
-- ============================================================================

-- Template 1: 7 Days Before Due Date (Friendly Early Reminder)
INSERT INTO email_templates (
    template_name,
    template_type,
    stage,
    subject_line,
    html_body,
    text_body,
    description,
    available_variables,
    preview_data,
    is_default,
    priority,
    footer_text
) VALUES (
    'payment_reminder_7_days_before',
    'payment_reminder',
    '7_days_before',
    'Upcoming Payment: Invoice {{invoice_number}} Due on {{due_date}}',
    '<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Upcoming Payment Reminder</title>
    <style>
        body { font-family: -apple-system, BlinkMacSystemFont, ''Segoe UI'', Roboto, ''Helvetica Neue'', Arial, sans-serif; line-height: 1.6; color: #333; margin: 0; padding: 0; background-color: #f5f5f5; }
        .container { max-width: 600px; margin: 20px auto; background: #ffffff; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); overflow: hidden; }
        .header { background: {{brand_color}}; color: #ffffff; padding: 30px 20px; text-align: center; }
        .header img { max-width: 200px; height: auto; margin-bottom: 10px; }
        .header h1 { margin: 0; font-size: 24px; font-weight: 600; }
        .content { padding: 30px 20px; }
        .greeting { font-size: 18px; font-weight: 500; margin-bottom: 20px; }
        .message { margin-bottom: 20px; }
        .invoice-details { background: #f8f9fa; border-left: 4px solid {{brand_color}}; padding: 15px; margin: 20px 0; border-radius: 4px; }
        .invoice-details table { width: 100%; }
        .invoice-details td { padding: 8px 0; }
        .invoice-details td:first-child { font-weight: 600; width: 40%; }
        .amount { font-size: 28px; font-weight: 700; color: {{brand_color}}; }
        .button { display: inline-block; padding: 12px 30px; background: {{brand_color}}; color: #ffffff !important; text-decoration: none; border-radius: 6px; font-weight: 600; margin: 20px 0; }
        .button:hover { opacity: 0.9; }
        .footer { background: #f8f9fa; padding: 20px; text-align: center; font-size: 12px; color: #6c757d; border-top: 1px solid #e9ecef; }
        .footer a { color: {{brand_color}}; text-decoration: none; }
        @media only screen and (max-width: 600px) {
            .container { margin: 0; border-radius: 0; }
            .content { padding: 20px 15px; }
        }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            {{#if header_image_url}}
            <img src="{{header_image_url}}" alt="Company Logo">
            {{/if}}
            <h1>Payment Reminder</h1>
        </div>
        
        <div class="content">
            <div class="greeting">Hi {{customer_name}},</div>
            
            <div class="message">
                <p>This is a friendly reminder that you have an upcoming payment due in <strong>7 days</strong>.</p>
            </div>
            
            <div class="invoice-details">
                <table>
                    <tr>
                        <td>Invoice Number:</td>
                        <td><strong>{{invoice_number}}</strong></td>
                    </tr>
                    <tr>
                        <td>Amount Due:</td>
                        <td class="amount">{{currency_symbol}}{{amount_due}}</td>
                    </tr>
                    <tr>
                        <td>Due Date:</td>
                        <td><strong>{{due_date}}</strong></td>
                    </tr>
                </table>
            </div>
            
            <div class="message">
                <p>To avoid any late fees or service interruptions, please ensure payment is made by the due date.</p>
            </div>
            
            <center>
                <a href="{{payment_link}}" class="button">Make Payment Now</a>
            </center>
            
            <div class="message" style="margin-top: 30px; font-size: 14px; color: #6c757d;">
                <p>If you have already made this payment, please disregard this reminder. If you have any questions, feel free to contact us.</p>
            </div>
        </div>
        
        <div class="footer">
            <p>{{footer_text}}</p>
            <p>
                <a href="{{company_website}}">Visit Our Website</a> | 
                <a href="mailto:{{support_email}}">Contact Support</a> | 
                <a href="{{unsubscribe_link}}">Unsubscribe</a>
            </p>
        </div>
    </div>
    
    <!-- Tracking Pixel -->
    <img src="{{tracking_pixel_url}}" width="1" height="1" style="display:none;" alt="">
</body>
</html>',
    'Hi {{customer_name}},

This is a friendly reminder that you have an upcoming payment due in 7 days.

Invoice Number: {{invoice_number}}
Amount Due: {{currency_symbol}}{{amount_due}}
Due Date: {{due_date}}

To avoid any late fees or service interruptions, please ensure payment is made by the due date.

Make payment: {{payment_link}}

If you have already made this payment, please disregard this reminder.

{{footer_text}}',
    'Friendly early reminder sent 7 days before invoice due date',
    '["customer_name", "invoice_number", "amount_due", "due_date", "currency_symbol", "payment_link", "tracking_pixel_url", "header_image_url", "footer_text", "brand_color", "company_website", "support_email", "unsubscribe_link"]'::JSONB,
    '{
        "customer_name": "John Doe",
        "invoice_number": "INV-2025-001",
        "amount_due": "1,250.00",
        "due_date": "October 25, 2025",
        "currency_symbol": "$",
        "payment_link": "https://example.com/pay/inv-123",
        "company_website": "https://example.com",
        "support_email": "support@example.com",
        "brand_color": "#3B82F6"
    }'::JSONB,
    true,
    100,
    'Thank you for your business. This is an automated reminder.'
);

-- Template 2: Due Date (Urgent Due Date Notice)
INSERT INTO email_templates (
    template_name,
    template_type,
    stage,
    subject_line,
    html_body,
    text_body,
    description,
    available_variables,
    preview_data,
    is_default,
    priority,
    footer_text
) VALUES (
    'payment_reminder_due_date',
    'payment_reminder',
    'due_date',
    '⚠️ Invoice {{invoice_number}} is Due Today - {{currency_symbol}}{{amount_due}}',
    '<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Payment Due Today</title>
    <style>
        body { font-family: -apple-system, BlinkMacSystemFont, ''Segoe UI'', Roboto, ''Helvetica Neue'', Arial, sans-serif; line-height: 1.6; color: #333; margin: 0; padding: 0; background-color: #f5f5f5; }
        .container { max-width: 600px; margin: 20px auto; background: #ffffff; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); overflow: hidden; }
        .header { background: linear-gradient(135deg, #f59e0b 0%, #ef4444 100%); color: #ffffff; padding: 30px 20px; text-align: center; }
        .header img { max-width: 200px; height: auto; margin-bottom: 10px; }
        .header h1 { margin: 0; font-size: 24px; font-weight: 600; }
        .urgent-badge { background: #fef3c7; color: #92400e; padding: 8px 16px; border-radius: 20px; font-weight: 600; display: inline-block; margin: 10px 0; }
        .content { padding: 30px 20px; }
        .greeting { font-size: 18px; font-weight: 500; margin-bottom: 20px; }
        .message { margin-bottom: 20px; }
        .invoice-details { background: #fef2f2; border-left: 4px solid #ef4444; padding: 15px; margin: 20px 0; border-radius: 4px; }
        .invoice-details table { width: 100%; }
        .invoice-details td { padding: 8px 0; }
        .invoice-details td:first-child { font-weight: 600; width: 40%; }
        .amount { font-size: 32px; font-weight: 700; color: #ef4444; }
        .button { display: inline-block; padding: 14px 35px; background: #ef4444; color: #ffffff !important; text-decoration: none; border-radius: 6px; font-weight: 600; margin: 20px 0; font-size: 16px; }
        .button:hover { background: #dc2626; }
        .footer { background: #f8f9fa; padding: 20px; text-align: center; font-size: 12px; color: #6c757d; border-top: 1px solid #e9ecef; }
        .footer a { color: #ef4444; text-decoration: none; }
        @media only screen and (max-width: 600px) {
            .container { margin: 0; border-radius: 0; }
            .content { padding: 20px 15px; }
        }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            {{#if header_image_url}}
            <img src="{{header_image_url}}" alt="Company Logo">
            {{/if}}
            <h1>⚠️ Payment Due Today</h1>
            <div class="urgent-badge">URGENT: DUE TODAY</div>
        </div>
        
        <div class="content">
            <div class="greeting">Hi {{customer_name}},</div>
            
            <div class="message">
                <p><strong>This is an urgent reminder that your payment is due TODAY.</strong></p>
                <p>To avoid late fees and maintain uninterrupted service, please process your payment immediately.</p>
            </div>
            
            <div class="invoice-details">
                <table>
                    <tr>
                        <td>Invoice Number:</td>
                        <td><strong>{{invoice_number}}</strong></td>
                    </tr>
                    <tr>
                        <td>Amount Due:</td>
                        <td class="amount">{{currency_symbol}}{{amount_due}}</td>
                    </tr>
                    <tr>
                        <td>Due Date:</td>
                        <td><strong style="color: #ef4444;">TODAY ({{due_date}})</strong></td>
                    </tr>
                </table>
            </div>
            
            <center>
                <a href="{{payment_link}}" class="button">PAY NOW - {{currency_symbol}}{{amount_due}}</a>
            </center>
            
            <div class="message" style="margin-top: 30px;">
                <p><strong>Payment Options:</strong></p>
                <ul>
                    <li>Pay online via our secure payment portal (link above)</li>
                    <li>Bank transfer to our account</li>
                    <li>Contact us for alternative payment arrangements</li>
                </ul>
            </div>
            
            <div class="message" style="margin-top: 20px; font-size: 14px; color: #6c757d;">
                <p>If you have already made this payment, please disregard this reminder and thank you for your prompt payment!</p>
            </div>
        </div>
        
        <div class="footer">
            <p>{{footer_text}}</p>
            <p>
                <a href="{{company_website}}">Visit Our Website</a> | 
                <a href="mailto:{{support_email}}">Contact Support</a> | 
                <a href="{{unsubscribe_link}}">Unsubscribe</a>
            </p>
        </div>
    </div>
    
    <!-- Tracking Pixel -->
    <img src="{{tracking_pixel_url}}" width="1" height="1" style="display:none;" alt="">
</body>
</html>',
    'Hi {{customer_name}},

⚠️ URGENT: Your payment is DUE TODAY ⚠️

Invoice Number: {{invoice_number}}
Amount Due: {{currency_symbol}}{{amount_due}}
Due Date: TODAY ({{due_date}})

To avoid late fees and maintain uninterrupted service, please process your payment immediately.

Pay now: {{payment_link}}

Payment Options:
- Pay online via our secure payment portal
- Bank transfer to our account
- Contact us for alternative payment arrangements

If you have already made this payment, please disregard this reminder.

{{footer_text}}',
    'Urgent reminder sent on invoice due date',
    '["customer_name", "invoice_number", "amount_due", "due_date", "currency_symbol", "payment_link", "tracking_pixel_url", "header_image_url", "footer_text", "company_website", "support_email", "unsubscribe_link"]'::JSONB,
    '{
        "customer_name": "John Doe",
        "invoice_number": "INV-2025-001",
        "amount_due": "1,250.00",
        "due_date": "October 18, 2025",
        "currency_symbol": "$",
        "payment_link": "https://example.com/pay/inv-123"
    }'::JSONB,
    true,
    100,
    'Thank you for your business. Please contact us if you need payment assistance.'
);

-- Template 3: 3 Days Overdue (First Overdue Reminder)
INSERT INTO email_templates (
    template_name,
    template_type,
    stage,
    subject_line,
    html_body,
    text_body,
    description,
    available_variables,
    preview_data,
    is_default,
    priority,
    footer_text
) VALUES (
    'payment_reminder_3_days_after',
    'payment_reminder',
    '3_days_after',
    'OVERDUE: Invoice {{invoice_number}} - Immediate Payment Required',
    '<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Overdue Payment Notice</title>
    <style>
        body { font-family: -apple-system, BlinkMacSystemFont, ''Segoe UI'', Roboto, ''Helvetica Neue'', Arial, sans-serif; line-height: 1.6; color: #333; margin: 0; padding: 0; background-color: #f5f5f5; }
        .container { max-width: 600px; margin: 20px auto; background: #ffffff; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); overflow: hidden; border-top: 4px solid #dc2626; }
        .header { background: #dc2626; color: #ffffff; padding: 30px 20px; text-align: center; }
        .header img { max-width: 200px; height: auto; margin-bottom: 10px; }
        .header h1 { margin: 0; font-size: 24px; font-weight: 600; }
        .overdue-badge { background: #fee2e2; color: #991b1b; padding: 10px 20px; border-radius: 6px; font-weight: 700; display: inline-block; margin: 15px 0; font-size: 16px; }
        .content { padding: 30px 20px; }
        .greeting { font-size: 18px; font-weight: 500; margin-bottom: 20px; }
        .message { margin-bottom: 20px; }
        .invoice-details { background: #fef2f2; border: 2px solid #fecaca; padding: 20px; margin: 20px 0; border-radius: 6px; }
        .invoice-details table { width: 100%; }
        .invoice-details td { padding: 10px 0; }
        .invoice-details td:first-child { font-weight: 600; width: 45%; }
        .amount { font-size: 32px; font-weight: 700; color: #dc2626; }
        .late-fee { background: #fef3c7; color: #92400e; padding: 12px; border-radius: 4px; margin: 15px 0; font-weight: 600; }
        .button { display: inline-block; padding: 14px 35px; background: #dc2626; color: #ffffff !important; text-decoration: none; border-radius: 6px; font-weight: 600; margin: 20px 0; font-size: 16px; }
        .button:hover { background: #b91c1c; }
        .warning-box { background: #fffbeb; border-left: 4px solid #f59e0b; padding: 15px; margin: 20px 0; border-radius: 4px; }
        .footer { background: #f8f9fa; padding: 20px; text-align: center; font-size: 12px; color: #6c757d; border-top: 1px solid #e9ecef; }
        .footer a { color: #dc2626; text-decoration: none; }
        @media only screen and (max-width: 600px) {
            .container { margin: 0; border-radius: 0; }
            .content { padding: 20px 15px; }
        }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            {{#if header_image_url}}
            <img src="{{header_image_url}}" alt="Company Logo">
            {{/if}}
            <h1>🔴 OVERDUE PAYMENT NOTICE</h1>
            <div class="overdue-badge">{{days_overdue}} DAYS OVERDUE</div>
        </div>
        
        <div class="content">
            <div class="greeting">Dear {{customer_name}},</div>
            
            <div class="message">
                <p><strong>Our records indicate that the following invoice is now {{days_overdue}} days overdue.</strong></p>
                <p>We have not yet received payment for this invoice. Immediate payment is required to avoid additional late fees and potential service interruption.</p>
            </div>
            
            <div class="invoice-details">
                <table>
                    <tr>
                        <td>Invoice Number:</td>
                        <td><strong>{{invoice_number}}</strong></td>
                    </tr>
                    <tr>
                        <td>Original Due Date:</td>
                        <td><strong style="color: #dc2626;">{{due_date}}</strong></td>
                    </tr>
                    <tr>
                        <td>Amount Due:</td>
                        <td class="amount">{{currency_symbol}}{{amount_due}}</td>
                    </tr>
                    <tr>
                        <td>Days Overdue:</td>
                        <td><strong style="color: #dc2626; font-size: 18px;">{{days_overdue}} Days</strong></td>
                    </tr>
                </table>
            </div>
            
            {{#if late_fee_amount}}
            <div class="late-fee">
                ⚠️ Late Fee Applied: {{currency_symbol}}{{late_fee_amount}}
            </div>
            {{/if}}
            
            <center>
                <a href="{{payment_link}}" class="button">MAKE PAYMENT NOW</a>
            </center>
            
            <div class="warning-box">
                <p><strong>⚠️ Important Notice:</strong></p>
                <p>If payment is not received within the next {{grace_period_days}} days, your account may be:</p>
                <ul style="margin: 10px 0;">
                    <li>Subject to additional late fees</li>
                    <li>Placed on hold for future services</li>
                    <li>Referred to collections</li>
                </ul>
            </div>
            
            <div class="message">
                <p><strong>Need Help?</strong></p>
                <p>If you are experiencing financial difficulties or need to discuss payment arrangements, please contact our accounts team immediately at <a href="mailto:{{accounts_email}}">{{accounts_email}}</a> or call {{accounts_phone}}.</p>
            </div>
            
            <div class="message" style="margin-top: 30px; font-size: 14px; color: #6c757d;">
                <p>If you have recently made this payment, please contact us with your payment reference and disregard this notice.</p>
            </div>
        </div>
        
        <div class="footer">
            <p>{{footer_text}}</p>
            <p>
                <a href="{{company_website}}">Visit Our Website</a> | 
                <a href="mailto:{{accounts_email}}">Contact Accounts</a> | 
                <a href="{{unsubscribe_link}}">Unsubscribe</a>
            </p>
        </div>
    </div>
    
    <!-- Tracking Pixel -->
    <img src="{{tracking_pixel_url}}" width="1" height="1" style="display:none;" alt="">
</body>
</html>',
    'Dear {{customer_name}},

🔴 OVERDUE PAYMENT NOTICE - {{days_overdue}} DAYS OVERDUE

Our records indicate that the following invoice is now overdue. Immediate payment is required.

Invoice Number: {{invoice_number}}
Original Due Date: {{due_date}}
Amount Due: {{currency_symbol}}{{amount_due}}
Days Overdue: {{days_overdue}} Days

{{#if late_fee_amount}}
Late Fee Applied: {{currency_symbol}}{{late_fee_amount}}
{{/if}}

Make payment now: {{payment_link}}

⚠️ IMPORTANT NOTICE:
If payment is not received within {{grace_period_days}} days, your account may be:
- Subject to additional late fees
- Placed on hold for future services
- Referred to collections

Need Help?
If you need to discuss payment arrangements, please contact our accounts team:
Email: {{accounts_email}}
Phone: {{accounts_phone}}

If you have recently made this payment, please contact us with your payment reference.

{{footer_text}}',
    'First overdue reminder sent 3 days after due date',
    '["customer_name", "invoice_number", "amount_due", "due_date", "days_overdue", "currency_symbol", "payment_link", "late_fee_amount", "grace_period_days", "accounts_email", "accounts_phone", "tracking_pixel_url", "header_image_url", "footer_text", "company_website", "unsubscribe_link"]'::JSONB,
    '{
        "customer_name": "John Doe",
        "invoice_number": "INV-2025-001",
        "amount_due": "1,250.00",
        "due_date": "October 15, 2025",
        "days_overdue": "3",
        "currency_symbol": "$",
        "late_fee_amount": "50.00",
        "grace_period_days": "7",
        "accounts_email": "accounts@example.com",
        "accounts_phone": "(555) 123-4567"
    }'::JSONB,
    true,
    100,
    'This is an automated overdue notice. Please remit payment immediately to avoid further action.'
);

-- Templates 4 & 5 will be created in next section...
-- (Continuing with 7_days_after and 30_days_after templates)

-- ============================================================================
-- Trigger: Update updated_at timestamp
-- ============================================================================
CREATE OR REPLACE FUNCTION update_email_templates_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_email_templates_updated_at
    BEFORE UPDATE ON email_templates
    FOR EACH ROW
    EXECUTE FUNCTION update_email_templates_updated_at();

-- ============================================================================
-- Grant Permissions
-- ============================================================================
-- GRANT SELECT, INSERT, UPDATE ON email_templates TO app_user;
-- GRANT EXECUTE ON FUNCTION get_email_template TO app_user;

-- ============================================================================
-- Migration Complete
-- ============================================================================
