-- ============================================================================
-- Phase 7 Task 2: Email Templates - Hierarchical Template System
-- ============================================================================
-- Description: Database schema for multi-level email template configuration
--              Supports org → group → branch → department hierarchy
-- Author: AI Assistant
-- Date: October 18, 2025
-- ============================================================================

-- Email Templates Table (Hierarchical Configuration)
-- ============================================================================
CREATE TABLE IF NOT EXISTS email_templates (
    template_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    
    -- Template identification
    template_code VARCHAR(100) NOT NULL,  -- e.g., 'payment_reminder_7_days_before'
    template_name VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(50) NOT NULL,        -- 'payment_reminders', 'invoices', 'quotations', etc.
    
    -- Hierarchical scope (NULL = system default)
    org_id UUID REFERENCES organizations(org_id),
    group_id UUID,                        -- Future: user_groups table
    branch_id UUID,                       -- Future: branches table
    department_id UUID,                   -- Future: departments table
    
    -- Template content
    subject_template TEXT NOT NULL,
    html_body TEXT NOT NULL,
    text_body TEXT,                       -- Plain text fallback
    
    -- Template metadata
    variables JSONB,                      -- List of available variables with descriptions
    required_variables TEXT[],            -- Variables that must be present
    
    -- Template settings
    from_name VARCHAR(255),               -- Override default from_name
    from_email VARCHAR(255),              -- Override default from_email
    reply_to_email VARCHAR(255),
    cc_emails TEXT[],
    bcc_emails TEXT[],
    
    -- Status and versioning
    is_active BOOLEAN DEFAULT true,
    version INTEGER DEFAULT 1,
    is_default BOOLEAN DEFAULT false,     -- Is this the system default?
    priority INTEGER DEFAULT 100,         -- Higher priority = preferred template
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(user_id),
    updated_by UUID REFERENCES users(user_id),
    isdeleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP,
    deleted_by UUID REFERENCES users(user_id),
    
    -- Constraints
    CONSTRAINT email_templates_unique_scope UNIQUE (template_code, org_id, group_id, branch_id, department_id),
    CONSTRAINT email_templates_check_scope CHECK (
        -- Must have at least template_code
        template_code IS NOT NULL
    )
);

-- Indexes for performance
CREATE INDEX idx_email_templates_code ON email_templates(template_code) WHERE isdeleted = false;
CREATE INDEX idx_email_templates_org ON email_templates(org_id) WHERE isdeleted = false;
CREATE INDEX idx_email_templates_category ON email_templates(category) 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 DESC) WHERE isdeleted = false;

-- Composite indexes for hierarchical lookup
CREATE INDEX idx_email_templates_lookup_org ON email_templates(template_code, org_id, is_active) WHERE isdeleted = false;
CREATE INDEX idx_email_templates_lookup_group ON email_templates(template_code, org_id, group_id, is_active) WHERE isdeleted = false;
CREATE INDEX idx_email_templates_lookup_branch ON email_templates(template_code, org_id, branch_id, is_active) WHERE isdeleted = false;

-- Comments
COMMENT ON TABLE email_templates IS 'Hierarchical email template storage with org/group/branch/department scoping';
COMMENT ON COLUMN email_templates.template_code IS 'Unique identifier for template type (e.g., payment_reminder_due_date)';
COMMENT ON COLUMN email_templates.priority IS 'Higher number = higher priority. Most specific scope wins in case of ties.';
COMMENT ON COLUMN email_templates.variables IS 'JSON array of available template variables with descriptions';

-- ============================================================================
-- Template Resolution Function (Hierarchical Lookup)
-- ============================================================================
CREATE OR REPLACE FUNCTION get_email_template(
    p_template_code VARCHAR,
    p_org_id UUID DEFAULT NULL,
    p_group_id UUID DEFAULT NULL,
    p_branch_id UUID DEFAULT NULL,
    p_department_id UUID DEFAULT NULL
)
RETURNS TABLE (
    template_id UUID,
    template_code VARCHAR,
    template_name VARCHAR,
    subject_template TEXT,
    html_body TEXT,
    text_body TEXT,
    from_name VARCHAR,
    from_email VARCHAR,
    reply_to_email VARCHAR,
    cc_emails TEXT[],
    bcc_emails TEXT[],
    variables JSONB,
    scope_level VARCHAR
) AS $$
BEGIN
    -- Hierarchical lookup order (most specific to least specific):
    -- 1. Department-specific template
    -- 2. Branch-specific template
    -- 3. Group-specific template
    -- 4. Org-specific template
    -- 5. System default template
    
    RETURN QUERY
    SELECT
        t.template_id,
        t.template_code,
        t.template_name,
        t.subject_template,
        t.html_body,
        t.text_body,
        t.from_name,
        t.from_email,
        t.reply_to_email,
        t.cc_emails,
        t.bcc_emails,
        t.variables,
        CASE
            WHEN t.department_id IS NOT NULL THEN 'department'
            WHEN t.branch_id IS NOT NULL THEN 'branch'
            WHEN t.group_id IS NOT NULL THEN 'group'
            WHEN t.org_id IS NOT NULL THEN 'org'
            ELSE 'system'
        END::VARCHAR as scope_level
    FROM email_templates t
    WHERE t.template_code = p_template_code
      AND t.is_active = true
      AND t.isdeleted = false
      AND (
          -- Department match (most specific)
          (t.department_id = p_department_id AND t.branch_id = p_branch_id AND t.group_id = p_group_id AND t.org_id = p_org_id)
          OR
          -- Branch match
          (t.branch_id = p_branch_id AND t.group_id = p_group_id AND t.org_id = p_org_id AND t.department_id IS NULL)
          OR
          -- Group match
          (t.group_id = p_group_id AND t.org_id = p_org_id AND t.branch_id IS NULL AND t.department_id IS NULL)
          OR
          -- Org match
          (t.org_id = p_org_id AND t.group_id IS NULL AND t.branch_id IS NULL AND t.department_id IS NULL)
          OR
          -- System default
          (t.org_id IS NULL AND t.group_id IS NULL AND t.branch_id IS NULL AND t.department_id IS NULL AND t.is_default = true)
      )
    ORDER BY
        -- Order by specificity (most specific first)
        CASE
            WHEN t.department_id IS NOT NULL THEN 1
            WHEN t.branch_id IS NOT NULL THEN 2
            WHEN t.group_id IS NOT NULL THEN 3
            WHEN t.org_id IS NOT NULL THEN 4
            ELSE 5
        END,
        t.priority DESC,
        t.created_at DESC
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_email_template IS 'Resolves email template using hierarchical lookup: department → branch → group → org → system default';

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

-- Template 1: 7 Days Before Due Date
INSERT INTO email_templates (
    template_code,
    template_name,
    description,
    category,
    subject_template,
    html_body,
    text_body,
    variables,
    required_variables,
    is_default,
    priority
) VALUES (
    'payment_reminder_7_days_before',
    'Payment Reminder - 7 Days Before Due',
    'Friendly advance notice 7-10 days before invoice due date',
    'payment_reminders',
    'Upcoming Payment Due: Invoice {{invoice_number}}',
    '<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Payment Reminder</title>
    <style>
        body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; margin: 0; padding: 0; background-color: #f4f4f4; }
        .container { max-width: 600px; margin: 20px auto; background: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
        .header { background: #4CAF50; color: #ffffff; padding: 30px; text-align: center; }
        .content { padding: 30px; }
        .invoice-details { background: #f9f9f9; padding: 20px; border-radius: 5px; margin: 20px 0; }
        .invoice-details table { width: 100%; }
        .invoice-details td { padding: 8px 0; }
        .invoice-details td:first-child { font-weight: bold; width: 40%; }
        .amount { font-size: 24px; color: #4CAF50; font-weight: bold; }
        .button { display: inline-block; padding: 12px 30px; background: #4CAF50; color: #ffffff; text-decoration: none; border-radius: 5px; margin: 20px 0; }
        .button:hover { background: #45a049; }
        .footer { background: #f4f4f4; padding: 20px; text-align: center; font-size: 12px; color: #666; }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <h1>Payment Reminder</h1>
        </div>
        <div class="content">
            <p>Dear {{customer_name}},</p>
            
            <p>This is a friendly reminder that your invoice will be due soon. We wanted to give you advance notice so you can prepare for payment.</p>
            
            <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>
                    <tr>
                        <td>Days Until Due:</td>
                        <td>{{days_until_due}} days</td>
                    </tr>
                </table>
            </div>
            
            <p>You can make payment at your convenience using the link below:</p>
            
            <center>
                <a href="{{payment_link}}" class="button">View Invoice & Pay Now</a>
            </center>
            
            <p>If you have any questions about this invoice or need to discuss payment arrangements, please don''t hesitate to contact us.</p>
            
            <p>Thank you for your business!</p>
            
            <p>Best regards,<br>
            {{company_name}}<br>
            {{company_phone}}<br>
            {{company_email}}</p>
        </div>
        <div class="footer">
            <p>This is an automated reminder. Please do not reply to this email.</p>
            <p>If you have already made this payment, please disregard this notice.</p>
            <p><a href="{{opt_out_link}}">Opt out of payment reminders</a></p>
        </div>
    </div>
    <img src="{{tracking_pixel_url}}" width="1" height="1" alt="" />
</body>
</html>',
    'Dear {{customer_name}},

This is a friendly reminder that your invoice will be due soon.

Invoice Details:
- Invoice Number: {{invoice_number}}
- Amount Due: {{currency_symbol}}{{amount_due}}
- Due Date: {{due_date}}
- Days Until Due: {{days_until_due}} days

You can view your invoice and make payment here:
{{payment_link}}

If you have any questions, please contact us.

Thank you for your business!

Best regards,
{{company_name}}
{{company_phone}}
{{company_email}}

---
This is an automated reminder. If you have already made this payment, please disregard this notice.
Opt out: {{opt_out_link}}',
    '{"customer_name": "Customer full name", "invoice_number": "Invoice identifier", "amount_due": "Outstanding amount", "due_date": "Invoice due date", "days_until_due": "Days remaining until due", "currency_symbol": "Currency symbol", "payment_link": "URL to payment page", "company_name": "Your company name", "company_phone": "Your phone number", "company_email": "Your email", "opt_out_link": "Unsubscribe URL", "tracking_pixel_url": "Open tracking pixel"}'::jsonb,
    ARRAY['customer_name', 'invoice_number', 'amount_due', 'due_date', 'payment_link'],
    true,
    100
);

-- Template 2: Due Date Reminder
INSERT INTO email_templates (
    template_code,
    template_name,
    description,
    category,
    subject_template,
    html_body,
    text_body,
    variables,
    required_variables,
    is_default,
    priority
) VALUES (
    'payment_reminder_due_date',
    'Payment Reminder - Due Date',
    'Urgent reminder sent on or near the invoice due date',
    'payment_reminders',
    'URGENT: Payment Due Today - Invoice {{invoice_number}}',
    '<!DOCTYPE html>
<html>
<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: Arial, sans-serif; line-height: 1.6; color: #333; margin: 0; padding: 0; background-color: #f4f4f4; }
        .container { max-width: 600px; margin: 20px auto; background: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
        .header { background: #FF9800; color: #ffffff; padding: 30px; text-align: center; }
        .urgent-badge { background: #FF5722; color: white; padding: 5px 15px; border-radius: 20px; display: inline-block; margin-bottom: 10px; }
        .content { padding: 30px; }
        .invoice-details { background: #fff3e0; padding: 20px; border-radius: 5px; margin: 20px 0; border-left: 4px solid #FF9800; }
        .invoice-details table { width: 100%; }
        .invoice-details td { padding: 8px 0; }
        .invoice-details td:first-child { font-weight: bold; width: 40%; }
        .amount { font-size: 28px; color: #FF5722; font-weight: bold; }
        .button { display: inline-block; padding: 15px 40px; background: #FF5722; color: #ffffff; text-decoration: none; border-radius: 5px; margin: 20px 0; font-weight: bold; }
        .button:hover { background: #E64A19; }
        .footer { background: #f4f4f4; padding: 20px; text-align: center; font-size: 12px; color: #666; }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <span class="urgent-badge">⚠️ PAYMENT DUE TODAY</span>
            <h1>Payment Reminder</h1>
        </div>
        <div class="content">
            <p>Dear {{customer_name}},</p>
            
            <p><strong>This is an urgent reminder that your invoice payment is due today.</strong></p>
            
            <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>
            
            <p>To avoid late fees and maintain your account in good standing, please make payment immediately using the button below:</p>
            
            <center>
                <a href="{{payment_link}}" class="button">PAY NOW</a>
            </center>
            
            <p>If you have already made this payment, thank you! Please disregard this reminder.</p>
            
            <p>If you are experiencing difficulties making payment, please contact us immediately to discuss payment arrangements.</p>
            
            <p>Best regards,<br>
            {{company_name}}<br>
            {{company_phone}}<br>
            {{company_email}}</p>
        </div>
        <div class="footer">
            <p>This is an automated reminder. Please do not reply to this email.</p>
            <p><a href="{{opt_out_link}}">Opt out of payment reminders</a></p>
        </div>
    </div>
    <img src="{{tracking_pixel_url}}" width="1" height="1" alt="" />
</body>
</html>',
    'URGENT: PAYMENT DUE TODAY

Dear {{customer_name}},

This is an urgent reminder that your invoice payment is due today.

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

Please make payment immediately to avoid late fees:
{{payment_link}}

If you have already paid, please disregard this notice.
If you need to discuss payment arrangements, please contact us immediately.

Best regards,
{{company_name}}
{{company_phone}}
{{company_email}}

---
Opt out: {{opt_out_link}}',
    '{"customer_name": "Customer full name", "invoice_number": "Invoice identifier", "amount_due": "Outstanding amount", "due_date": "Invoice due date", "currency_symbol": "Currency symbol", "payment_link": "URL to payment page", "company_name": "Your company name", "company_phone": "Your phone number", "company_email": "Your email", "opt_out_link": "Unsubscribe URL", "tracking_pixel_url": "Open tracking pixel"}'::jsonb,
    ARRAY['customer_name', 'invoice_number', 'amount_due', 'due_date', 'payment_link'],
    true,
    100
);

-- Template 3: 3 Days Overdue
INSERT INTO email_templates (
    template_code,
    template_name,
    description,
    category,
    subject_template,
    html_body,
    text_body,
    variables,
    required_variables,
    is_default,
    priority
) VALUES (
    'payment_reminder_3_days_after',
    'Payment Reminder - 3 Days Overdue',
    'First overdue notice, 3-5 days past due',
    'payment_reminders',
    'OVERDUE: Payment Required - Invoice {{invoice_number}}',
    '<!DOCTYPE html>
<html>
<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: Arial, sans-serif; line-height: 1.6; color: #333; margin: 0; padding: 0; background-color: #f4f4f4; }
        .container { max-width: 600px; margin: 20px auto; background: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
        .header { background: #F44336; color: #ffffff; padding: 30px; text-align: center; }
        .overdue-badge { background: #D32F2F; color: white; padding: 5px 15px; border-radius: 20px; display: inline-block; margin-bottom: 10px; font-weight: bold; }
        .content { padding: 30px; }
        .invoice-details { background: #ffebee; padding: 20px; border-radius: 5px; margin: 20px 0; border-left: 4px solid #F44336; }
        .invoice-details table { width: 100%; }
        .invoice-details td { padding: 8px 0; }
        .invoice-details td:first-child { font-weight: bold; width: 40%; }
        .amount { font-size: 28px; color: #D32F2F; font-weight: bold; }
        .warning-box { background: #fff9c4; border-left: 4px solid #FBC02D; padding: 15px; margin: 20px 0; }
        .button { display: inline-block; padding: 15px 40px; background: #D32F2F; color: #ffffff; text-decoration: none; border-radius: 5px; margin: 20px 0; font-weight: bold; }
        .button:hover { background: #B71C1C; }
        .footer { background: #f4f4f4; padding: 20px; text-align: center; font-size: 12px; color: #666; }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <span class="overdue-badge">❌ PAYMENT OVERDUE</span>
            <h1>Overdue Payment Notice</h1>
        </div>
        <div class="content">
            <p>Dear {{customer_name}},</p>
            
            <p><strong>Our records indicate that your payment is now overdue.</strong></p>
            
            <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>Original Due Date:</td>
                        <td><strong>{{due_date}}</strong></td>
                    </tr>
                    <tr>
                        <td>Days Overdue:</td>
                        <td><strong style="color: #D32F2F;">{{days_overdue}} days</strong></td>
                    </tr>
                </table>
            </div>
            
            <div class="warning-box">
                <strong>⚠️ Important:</strong> Late fees may apply. Please remit payment immediately to avoid additional charges and service interruption.
            </div>
            
            <p>Please make payment now to bring your account current:</p>
            
            <center>
                <a href="{{payment_link}}" class="button">PAY OVERDUE INVOICE NOW</a>
            </center>
            
            <p>If you have recently made this payment, please accept our apologies and disregard this notice.</p>
            
            <p>If you are unable to pay the full amount at this time, please contact us immediately to discuss payment options. We are here to work with you.</p>
            
            <p>Best regards,<br>
            {{company_name}}<br>
            {{company_phone}}<br>
            {{company_email}}</p>
        </div>
        <div class="footer">
            <p>This is an automated reminder. Please do not reply to this email.</p>
            <p><a href="{{opt_out_link}}">Opt out of payment reminders</a></p>
        </div>
    </div>
    <img src="{{tracking_pixel_url}}" width="1" height="1" alt="" />
</body>
</html>',
    'OVERDUE PAYMENT NOTICE

Dear {{customer_name}},

Our records indicate that your payment is now OVERDUE.

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

⚠️ IMPORTANT: Late fees may apply. Please remit payment immediately.

Pay now: {{payment_link}}

If you recently made this payment, please disregard this notice.
If you need payment arrangements, please contact us immediately.

Best regards,
{{company_name}}
{{company_phone}}
{{company_email}}

---
Opt out: {{opt_out_link}}',
    '{"customer_name": "Customer full name", "invoice_number": "Invoice identifier", "amount_due": "Outstanding amount", "due_date": "Original due date", "days_overdue": "Number of days overdue", "currency_symbol": "Currency symbol", "payment_link": "URL to payment page", "company_name": "Your company name", "company_phone": "Your phone number", "company_email": "Your email", "opt_out_link": "Unsubscribe URL", "tracking_pixel_url": "Open tracking pixel"}'::jsonb,
    ARRAY['customer_name', 'invoice_number', 'amount_due', 'due_date', 'days_overdue', 'payment_link'],
    true,
    100
);

-- Template 4: 7 Days Overdue
INSERT INTO email_templates (
    template_code,
    template_name,
    description,
    category,
    subject_template,
    html_body,
    text_body,
    variables,
    required_variables,
    is_default,
    priority
) VALUES (
    'payment_reminder_7_days_after',
    'Payment Reminder - 7 Days Overdue',
    'Second overdue notice, 7-10 days past due',
    'payment_reminders',
    'URGENT: Overdue Invoice {{invoice_number}} - Immediate Action Required',
    '<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Urgent Overdue Payment Notice</title>
    <style>
        body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; margin: 0; padding: 0; background-color: #f4f4f4; }
        .container { max-width: 600px; margin: 20px auto; background: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 4px 6px rgba(0,0,0,0.2); }
        .header { background: #D32F2F; color: #ffffff; padding: 30px; text-align: center; }
        .urgent-badge { background: #B71C1C; color: white; padding: 8px 20px; border-radius: 20px; display: inline-block; margin-bottom: 10px; font-weight: bold; animation: pulse 2s infinite; }
        @keyframes pulse { 0%, 100% { opacity: 1; } 50% { opacity: 0.7; } }
        .content { padding: 30px; }
        .invoice-details { background: #ffcdd2; padding: 20px; border-radius: 5px; margin: 20px 0; border: 2px solid #D32F2F; }
        .invoice-details table { width: 100%; }
        .invoice-details td { padding: 10px 0; }
        .invoice-details td:first-child { font-weight: bold; width: 40%; }
        .amount { font-size: 32px; color: #B71C1C; font-weight: bold; }
        .warning-box { background: #ffebee; border: 2px solid #D32F2F; padding: 20px; margin: 20px 0; border-radius: 5px; }
        .button { display: inline-block; padding: 18px 50px; background: #B71C1C; color: #ffffff; text-decoration: none; border-radius: 5px; margin: 20px 0; font-weight: bold; font-size: 16px; }
        .button:hover { background: #7f0000; }
        .consequences { background: #fff3e0; padding: 15px; margin: 20px 0; border-left: 4px solid #FF6F00; }
        .footer { background: #f4f4f4; padding: 20px; text-align: center; font-size: 12px; color: #666; }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <span class="urgent-badge">🚨 URGENT - IMMEDIATE ACTION REQUIRED</span>
            <h1>Overdue Payment Notice</h1>
        </div>
        <div class="content">
            <p>Dear {{customer_name}},</p>
            
            <p><strong>This is our second notice regarding your seriously overdue invoice. Your account requires immediate attention.</strong></p>
            
            <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>Original Due Date:</td>
                        <td><strong>{{due_date}}</strong></td>
                    </tr>
                    <tr>
                        <td>Days Overdue:</td>
                        <td><strong style="color: #B71C1C; font-size: 18px;">{{days_overdue}} days</strong></td>
                    </tr>
                    <tr>
                        <td>Late Fees:</td>
                        <td><strong style="color: #D32F2F;">{{currency_symbol}}{{late_fees}}</strong></td>
                    </tr>
                    <tr>
                        <td>Total Now Due:</td>
                        <td class="amount">{{currency_symbol}}{{total_with_fees}}</td>
                    </tr>
                </table>
            </div>
            
            <div class="warning-box">
                <h3 style="margin-top: 0; color: #D32F2F;">⚠️ URGENT NOTICE</h3>
                <p>Payment must be received within 48 hours to avoid:</p>
                <ul>
                    <li>Additional late fees</li>
                    <li>Service suspension</li>
                    <li>Collection agency referral</li>
                    <li>Credit reporting</li>
                </ul>
            </div>
            
            <p><strong>This is your final opportunity to resolve this matter before escalation.</strong></p>
            
            <center>
                <a href="{{payment_link}}" class="button">PAY IMMEDIATELY</a>
            </center>
            
            <div class="consequences">
                <strong>If payment is not received:</strong> Your account will be placed on hold, and we will be forced to pursue collection through legal means.
            </div>
            
            <p>If you have already made payment, please provide proof of payment immediately by replying to this email or calling us.</p>
            
            <p>If you are experiencing financial difficulties, contact us TODAY to arrange a payment plan. We must hear from you within 48 hours.</p>
            
            <p>Accounts Department<br>
            {{company_name}}<br>
            {{company_phone}}<br>
            {{company_email}}</p>
        </div>
        <div class="footer">
            <p>This is an official notice. Immediate action is required.</p>
            <p><a href="{{opt_out_link}}">Opt out of payment reminders</a></p>
        </div>
    </div>
    <img src="{{tracking_pixel_url}}" width="1" height="1" alt="" />
</body>
</html>',
    '🚨 URGENT - IMMEDIATE ACTION REQUIRED 🚨

Dear {{customer_name}},

This is our SECOND NOTICE regarding your seriously overdue invoice.

Invoice Details:
- Invoice Number: {{invoice_number}}
- Original Amount: {{currency_symbol}}{{amount_due}}
- Original Due Date: {{due_date}}
- Days Overdue: {{days_overdue}} days
- Late Fees: {{currency_symbol}}{{late_fees}}
- TOTAL NOW DUE: {{currency_symbol}}{{total_with_fees}}

⚠️ URGENT: Payment must be received within 48 hours to avoid:
- Additional late fees
- Service suspension
- Collection agency referral
- Credit reporting

This is your final opportunity before escalation.

PAY IMMEDIATELY: {{payment_link}}

If payment has been made, provide proof NOW.
If experiencing difficulties, contact us TODAY to arrange payment plan.

You MUST respond within 48 hours.

Accounts Department
{{company_name}}
{{company_phone}}
{{company_email}}

---
Opt out: {{opt_out_link}}',
    '{"customer_name": "Customer full name", "invoice_number": "Invoice identifier", "amount_due": "Original amount", "due_date": "Original due date", "days_overdue": "Number of days overdue", "late_fees": "Late fee amount", "total_with_fees": "Total including late fees", "currency_symbol": "Currency symbol", "payment_link": "URL to payment page", "company_name": "Your company name", "company_phone": "Your phone number", "company_email": "Your email", "opt_out_link": "Unsubscribe URL", "tracking_pixel_url": "Open tracking pixel"}'::jsonb,
    ARRAY['customer_name', 'invoice_number', 'amount_due', 'due_date', 'days_overdue', 'payment_link'],
    true,
    100
);

-- Template 5: 30+ Days Overdue (Final Notice)
INSERT INTO email_templates (
    template_code,
    template_name,
    description,
    category,
    subject_template,
    html_body,
    text_body,
    variables,
    required_variables,
    is_default,
    priority
) VALUES (
    'payment_reminder_30_days_after',
    'Payment Reminder - Final Notice',
    'Final notice before collections, 30+ days past due',
    'payment_reminders',
    'FINAL NOTICE: Invoice {{invoice_number}} - Collections Pending',
    '<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Final Notice Before Collections</title>
    <style>
        body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; margin: 0; padding: 0; background-color: #f4f4f4; }
        .container { max-width: 600px; margin: 20px auto; background: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 4px 8px rgba(0,0,0,0.3); border: 3px solid #B71C1C; }
        .header { background: #B71C1C; color: #ffffff; padding: 30px; text-align: center; }
        .final-notice { background: #7f0000; color: white; padding: 10px 25px; border-radius: 25px; display: inline-block; margin-bottom: 15px; font-weight: bold; font-size: 18px; }
        .content { padding: 30px; }
        .invoice-details { background: #ffcdd2; padding: 25px; border-radius: 5px; margin: 20px 0; border: 3px solid #B71C1C; }
        .invoice-details table { width: 100%; }
        .invoice-details td { padding: 12px 0; font-size: 16px; }
        .invoice-details td:first-child { font-weight: bold; width: 45%; }
        .amount { font-size: 36px; color: #7f0000; font-weight: bold; }
        .critical-box { background: #7f0000; color: white; padding: 25px; margin: 20px 0; border-radius: 5px; }
        .button { display: inline-block; padding: 20px 60px; background: #7f0000; color: #ffffff; text-decoration: none; border-radius: 5px; margin: 20px 0; font-weight: bold; font-size: 18px; }
        .button:hover { background: #4a0000; }
        .legal-notice { background: #ffebee; border: 2px solid #B71C1C; padding: 20px; margin: 20px 0; font-size: 14px; }
        .footer { background: #f4f4f4; padding: 20px; text-align: center; font-size: 12px; color: #666; }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <span class="final-notice">⛔ FINAL NOTICE BEFORE LEGAL ACTION</span>
            <h1>Account in Default</h1>
        </div>
        <div class="content">
            <p>Dear {{customer_name}},</p>
            
            <p><strong>This is your FINAL NOTICE. Your account is seriously delinquent and will be forwarded to our collection agency if payment is not received within 72 hours.</strong></p>
            
            <div class="invoice-details">
                <table>
                    <tr>
                        <td>Invoice Number:</td>
                        <td><strong>{{invoice_number}}</strong></td>
                    </tr>
                    <tr>
                        <td>Original Amount:</td>
                        <td><strong>{{currency_symbol}}{{amount_due}}</strong></td>
                    </tr>
                    <tr>
                        <td>Original Due Date:</td>
                        <td><strong>{{due_date}}</strong></td>
                    </tr>
                    <tr>
                        <td>Days Overdue:</td>
                        <td><strong style="color: #7f0000; font-size: 20px;">{{days_overdue}} days</strong></td>
                    </tr>
                    <tr>
                        <td>Accumulated Late Fees:</td>
                        <td><strong style="color: #B71C1C;">{{currency_symbol}}{{late_fees}}</strong></td>
                    </tr>
                    <tr>
                        <td>Collection Fees:</td>
                        <td><strong style="color: #B71C1C;">{{currency_symbol}}{{collection_fees}}</strong></td>
                    </tr>
                    <tr style="border-top: 3px solid #B71C1C;">
                        <td>TOTAL AMOUNT DUE:</td>
                        <td class="amount">{{currency_symbol}}{{total_with_fees}}</td>
                    </tr>
                </table>
            </div>
            
            <div class="critical-box">
                <h3 style="margin-top: 0;">🚨 CRITICAL: PAYMENT REQUIRED WITHIN 72 HOURS</h3>
                <p style="font-size: 16px; margin: 10px 0;">Failure to remit payment will result in:</p>
                <ul style="font-size: 16px;">
                    <li>Immediate referral to collection agency</li>
                    <li>Negative credit reporting</li>
                    <li>Legal action and court proceedings</li>
                    <li>Additional legal and collection fees</li>
                    <li>Permanent termination of services</li>
                </ul>
            </div>
            
            <p style="font-size: 18px; font-weight: bold; color: #B71C1C;">DO NOT IGNORE THIS NOTICE. IMMEDIATE PAYMENT IS REQUIRED.</p>
            
            <center>
                <a href="{{payment_link}}" class="button">PAY NOW TO AVOID COLLECTIONS</a>
            </center>
            
            <div class="legal-notice">
                <h4 style="margin-top: 0;">LEGAL NOTICE</h4>
                <p>This is a formal demand for payment. If payment is not received by {{final_deadline}}, this account will be:</p>
                <ol>
                    <li>Referred to {{collection_agency}} for collection proceedings</li>
                    <li>Reported to credit bureaus, negatively impacting your credit score</li>
                    <li>Subject to legal action, including court costs and attorney fees</li>
                </ol>
                <p><strong>Total fees and costs may exceed the original invoice amount.</strong></p>
            </div>
            
            <p>If you dispute this debt or require verification, you must contact us in writing within 30 days as required by the Fair Debt Collection Practices Act.</p>
            
            <p>If you have already made payment, provide immediate proof by calling {{company_phone}} or emailing proof to {{company_email}}.</p>
            
            <p><strong>This is your last chance to resolve this matter before legal action.</strong></p>
            
            <p>Collections Department<br>
            {{company_name}}<br>
            {{company_phone}} (URGENT)<br>
            {{company_email}}</p>
        </div>
        <div class="footer">
            <p><strong>FINAL NOTICE - LEGAL ACTION PENDING</strong></p>
            <p>This communication is from a debt collector. This is an attempt to collect a debt.</p>
        </div>
    </div>
    <img src="{{tracking_pixel_url}}" width="1" height="1" alt="" />
</body>
</html>',
    '⛔ FINAL NOTICE BEFORE LEGAL ACTION ⛔

Dear {{customer_name}},

This is your FINAL NOTICE. Your account is seriously delinquent.

ACCOUNT IN DEFAULT
-------------------
Invoice Number: {{invoice_number}}
Original Amount: {{currency_symbol}}{{amount_due}}
Original Due Date: {{due_date}}
Days Overdue: {{days_overdue}} days
Late Fees: {{currency_symbol}}{{late_fees}}
Collection Fees: {{currency_symbol}}{{collection_fees}}
-------------------
TOTAL AMOUNT DUE: {{currency_symbol}}{{total_with_fees}}

🚨 PAYMENT REQUIRED WITHIN 72 HOURS 🚨

Failure to pay will result in:
- Immediate collection agency referral
- Negative credit reporting
- Legal action and court proceedings
- Additional legal fees
- Service termination

LEGAL NOTICE:
If payment is not received by {{final_deadline}}, this account will be:
1. Referred to {{collection_agency}}
2. Reported to credit bureaus
3. Subject to legal action

Total fees may exceed original amount.

DO NOT IGNORE THIS NOTICE.

PAY NOW: {{payment_link}}

If payment made, provide immediate proof.
If disputed, contact us in writing within 30 days (FDCPA).

LAST CHANCE TO AVOID LEGAL ACTION.

Collections Department
{{company_name}}
{{company_phone}} (URGENT)
{{company_email}}

---
FINAL NOTICE - LEGAL ACTION PENDING
This is from a debt collector attempting to collect a debt.
',
    '{"customer_name": "Customer full name", "invoice_number": "Invoice identifier", "amount_due": "Original amount", "due_date": "Original due date", "days_overdue": "Number of days overdue", "late_fees": "Accumulated late fees", "collection_fees": "Collection fees", "total_with_fees": "Total including all fees", "final_deadline": "Deadline before collections", "collection_agency": "Collection agency name", "currency_symbol": "Currency symbol", "payment_link": "URL to payment page", "company_name": "Your company name", "company_phone": "Your phone number", "company_email": "Your email", "tracking_pixel_url": "Open tracking pixel"}'::jsonb,
    ARRAY['customer_name', 'invoice_number', 'amount_due', 'due_date', 'days_overdue', 'final_deadline', 'payment_link'],
    true,
    100
);

-- ============================================================================
-- Completion Message
-- ============================================================================
DO $$
BEGIN
    RAISE NOTICE '✅ Email templates schema created successfully';
    RAISE NOTICE '✅ 5 default payment reminder templates inserted';
    RAISE NOTICE '✅ Hierarchical template resolution function created';
    RAISE NOTICE '';
    RAISE NOTICE 'Next steps:';
    RAISE NOTICE '1. Deploy EmailTemplateService.php for template management';
    RAISE NOTICE '2. Update PaymentReminderService to use get_email_template()';
    RAISE NOTICE '3. Create template management UI module';
END $$;
