-- ============================================================================
-- PAYMENT RECOVERY TABLES - COMPLETE EMAIL TRACKING COLUMNS
-- Date: December 21, 2025
-- Purpose: Add comprehensive Mailgun webhook tracking to all recovery tables
-- ============================================================================

-- Add user_viewed tracking columns (Added: Dec 21, 2025)
-- Purpose: Track when users view the recovery payment page
ALTER TABLE `certificate_payment_recoveries`
ADD COLUMN `user_viewed_page` BOOLEAN DEFAULT FALSE AFTER `email_sent`,
ADD COLUMN `user_viewed_at` TIMESTAMP NULL AFTER `user_viewed_page`;

-- Update status enum to include 'user_viewed' state
ALTER TABLE `certificate_payment_recoveries`
MODIFY COLUMN `status` ENUM('pending', 'email_sent', 'user_viewed', 'payment_completed', 'expired') DEFAULT 'pending';

-- ============================================================================
-- MAILGUN MESSAGE ID - Required for webhook tracking
-- ============================================================================
-- This column stores the Mailgun message ID to match webhook events

ALTER TABLE `uid_registration_recoveries`
ADD COLUMN `mailgun_message_id` VARCHAR(255) NULL AFTER `email_status`,
ADD INDEX `idx_mailgun_message_id` (`mailgun_message_id`);

ALTER TABLE `membership_renewal_recoveries`
ADD COLUMN `mailgun_message_id` VARCHAR(255) NULL AFTER `email_status`,
ADD INDEX `idx_mailgun_message_id` (`mailgun_message_id`);

ALTER TABLE `certificate_payment_recoveries`
ADD COLUMN `mailgun_message_id` VARCHAR(255) NULL AFTER `email_status`,
ADD INDEX `idx_mailgun_message_id` (`mailgun_message_id`);

-- ============================================================================
-- EMAIL LINK CLICK TRACKING
-- ============================================================================
-- Track when users click links in recovery emails

ALTER TABLE `uid_registration_recoveries`
ADD COLUMN `email_clicked` BOOLEAN DEFAULT FALSE AFTER `email_last_opened_at`,
ADD COLUMN `email_click_count` INT DEFAULT 0 AFTER `email_clicked`,
ADD COLUMN `email_first_clicked_at` TIMESTAMP NULL AFTER `email_click_count`,
ADD COLUMN `email_last_clicked_at` TIMESTAMP NULL AFTER `email_first_clicked_at`;

ALTER TABLE `membership_renewal_recoveries`
ADD COLUMN `email_clicked` BOOLEAN DEFAULT FALSE AFTER `email_last_opened_at`,
ADD COLUMN `email_click_count` INT DEFAULT 0 AFTER `email_clicked`,
ADD COLUMN `email_first_clicked_at` TIMESTAMP NULL AFTER `email_click_count`,
ADD COLUMN `email_last_clicked_at` TIMESTAMP NULL AFTER `email_first_clicked_at`;

ALTER TABLE `certificate_payment_recoveries`
ADD COLUMN `email_clicked` BOOLEAN DEFAULT FALSE AFTER `email_last_opened_at`,
ADD COLUMN `email_click_count` INT DEFAULT 0 AFTER `email_clicked`,
ADD COLUMN `email_first_clicked_at` TIMESTAMP NULL AFTER `email_click_count`,
ADD COLUMN `email_last_clicked_at` TIMESTAMP NULL AFTER `email_first_clicked_at`;

-- ============================================================================
-- EMAIL BOUNCE TRACKING
-- ============================================================================
-- Track when emails bounce (permanent delivery failure)

ALTER TABLE `uid_registration_recoveries`
ADD COLUMN `email_bounced` BOOLEAN DEFAULT FALSE AFTER `email_last_clicked_at`,
ADD COLUMN `email_bounced_at` TIMESTAMP NULL AFTER `email_bounced`,
ADD COLUMN `email_bounce_reason` TEXT NULL AFTER `email_bounced_at`;

ALTER TABLE `membership_renewal_recoveries`
ADD COLUMN `email_bounced` BOOLEAN DEFAULT FALSE AFTER `email_last_clicked_at`,
ADD COLUMN `email_bounced_at` TIMESTAMP NULL AFTER `email_bounced`,
ADD COLUMN `email_bounce_reason` TEXT NULL AFTER `email_bounced_at`;

ALTER TABLE `certificate_payment_recoveries`
ADD COLUMN `email_bounced` BOOLEAN DEFAULT FALSE AFTER `email_last_clicked_at`,
ADD COLUMN `email_bounced_at` TIMESTAMP NULL AFTER `email_bounced`,
ADD COLUMN `email_bounce_reason` TEXT NULL AFTER `email_bounced_at`;

-- ============================================================================
-- EMAIL DELIVERY TRACKING
-- ============================================================================
-- Track successful email delivery to recipient's inbox

ALTER TABLE `uid_registration_recoveries`
ADD COLUMN `email_delivered` BOOLEAN DEFAULT FALSE AFTER `email_bounce_reason`,
ADD COLUMN `email_delivered_at` TIMESTAMP NULL AFTER `email_delivered`;

ALTER TABLE `membership_renewal_recoveries`
ADD COLUMN `email_delivered` BOOLEAN DEFAULT FALSE AFTER `email_bounce_reason`,
ADD COLUMN `email_delivered_at` TIMESTAMP NULL AFTER `email_delivered`;

ALTER TABLE `certificate_payment_recoveries`
ADD COLUMN `email_delivered` BOOLEAN DEFAULT FALSE AFTER `email_bounce_reason`,
ADD COLUMN `email_delivered_at` TIMESTAMP NULL AFTER `email_delivered`;

-- ============================================================================
-- EMAIL COMPLAINT TRACKING (SPAM REPORTS)
-- ============================================================================
-- Track when recipients mark emails as spam

ALTER TABLE `uid_registration_recoveries`
ADD COLUMN `email_complained` BOOLEAN DEFAULT FALSE AFTER `email_delivered_at`,
ADD COLUMN `email_complained_at` TIMESTAMP NULL AFTER `email_complained`;

ALTER TABLE `membership_renewal_recoveries`
ADD COLUMN `email_complained` BOOLEAN DEFAULT FALSE AFTER `email_delivered_at`,
ADD COLUMN `email_complained_at` TIMESTAMP NULL AFTER `email_complained`;

ALTER TABLE `certificate_payment_recoveries`
ADD COLUMN `email_complained` BOOLEAN DEFAULT FALSE AFTER `email_delivered_at`,
ADD COLUMN `email_complained_at` TIMESTAMP NULL AFTER `email_complained`;

-- ============================================================================
-- EMAIL UNSUBSCRIBE TRACKING
-- ============================================================================
-- Track when recipients unsubscribe from recovery emails

ALTER TABLE `uid_registration_recoveries`
ADD COLUMN `email_unsubscribed` BOOLEAN DEFAULT FALSE AFTER `email_complained_at`,
ADD COLUMN `email_unsubscribed_at` TIMESTAMP NULL AFTER `email_unsubscribed`;

ALTER TABLE `membership_renewal_recoveries`
ADD COLUMN `email_unsubscribed` BOOLEAN DEFAULT FALSE AFTER `email_complained_at`,
ADD COLUMN `email_unsubscribed_at` TIMESTAMP NULL AFTER `email_unsubscribed`;

ALTER TABLE `certificate_payment_recoveries`
ADD COLUMN `email_unsubscribed` BOOLEAN DEFAULT FALSE AFTER `email_complained_at`,
ADD COLUMN `email_unsubscribed_at` TIMESTAMP NULL AFTER `email_unsubscribed`;

-- ============================================================================
-- EMAIL FAILURE TRACKING
-- ============================================================================
-- Track when email delivery fails (temporary or permanent)

ALTER TABLE `uid_registration_recoveries`
ADD COLUMN `email_failed` BOOLEAN DEFAULT FALSE AFTER `email_unsubscribed_at`,
ADD COLUMN `email_failed_at` TIMESTAMP NULL AFTER `email_failed`,
ADD COLUMN `email_failure_reason` TEXT NULL AFTER `email_failed_at`;

ALTER TABLE `membership_renewal_recoveries`
ADD COLUMN `email_failed` BOOLEAN DEFAULT FALSE AFTER `email_unsubscribed_at`,
ADD COLUMN `email_failed_at` TIMESTAMP NULL AFTER `email_failed`,
ADD COLUMN `email_failure_reason` TEXT NULL AFTER `email_failed_at`;

ALTER TABLE `certificate_payment_recoveries`
ADD COLUMN `email_failed` BOOLEAN DEFAULT FALSE AFTER `email_unsubscribed_at`,
ADD COLUMN `email_failed_at` TIMESTAMP NULL AFTER `email_failed`,
ADD COLUMN `email_failure_reason` TEXT NULL AFTER `email_failed_at`;

-- ============================================================================
-- INDEXES FOR PERFORMANCE
-- ============================================================================
-- Optimize queries for email tracking dashboards

ALTER TABLE `uid_registration_recoveries`
ADD INDEX `idx_email_delivered` (`email_delivered`),
ADD INDEX `idx_email_clicked` (`email_clicked`),
ADD INDEX `idx_email_bounced` (`email_bounced`),
ADD INDEX `idx_email_complained` (`email_complained`),
ADD INDEX `idx_email_failed` (`email_failed`);

ALTER TABLE `membership_renewal_recoveries`
ADD INDEX `idx_email_delivered` (`email_delivered`),
ADD INDEX `idx_email_clicked` (`email_clicked`),
ADD INDEX `idx_email_bounced` (`email_bounced`),
ADD INDEX `idx_email_complained` (`email_complained`),
ADD INDEX `idx_email_failed` (`email_failed`);

ALTER TABLE `certificate_payment_recoveries`
ADD INDEX `idx_email_delivered` (`email_delivered`),
ADD INDEX `idx_email_clicked` (`email_clicked`),
ADD INDEX `idx_email_bounced` (`email_bounced`),
ADD INDEX `idx_email_complained` (`email_complained`),
ADD INDEX `idx_email_failed` (`email_failed`);

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================
-- Run these queries to verify all columns were added successfully

-- Check UID Registration Recovery columns
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'uid_registration_recoveries'
    AND COLUMN_NAME IN (
        'mailgun_message_id',
        'email_clicked', 'email_click_count', 'email_first_clicked_at', 'email_last_clicked_at',
        'email_bounced', 'email_bounced_at', 'email_bounce_reason',
        'email_delivered', 'email_delivered_at',
        'email_complained', 'email_complained_at',
        'email_unsubscribed', 'email_unsubscribed_at',
        'email_failed', 'email_failed_at', 'email_failure_reason'
    )
ORDER BY 
    ORDINAL_POSITION;

-- Check Membership Renewal Recovery columns
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'membership_renewal_recoveries'
    AND COLUMN_NAME IN (
        'mailgun_message_id',
        'email_clicked', 'email_click_count', 'email_first_clicked_at', 'email_last_clicked_at',
        'email_bounced', 'email_bounced_at', 'email_bounce_reason',
        'email_delivered', 'email_delivered_at',
        'email_complained', 'email_complained_at',
        'email_unsubscribed', 'email_unsubscribed_at',
        'email_failed', 'email_failed_at', 'email_failure_reason'
    )
ORDER BY 
    ORDINAL_POSITION;

-- Check Certificate Payment Recovery columns
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'certificate_payment_recoveries'
    AND COLUMN_NAME IN (
        'user_viewed_page', 'user_viewed_at',
        'mailgun_message_id',
        'email_clicked', 'email_click_count', 'email_first_clicked_at', 'email_last_clicked_at',
        'email_bounced', 'email_bounced_at', 'email_bounce_reason',
        'email_delivered', 'email_delivered_at',
        'email_complained', 'email_complained_at',
        'email_unsubscribed', 'email_unsubscribed_at',
        'email_failed', 'email_failed_at', 'email_failure_reason'
    )
ORDER BY 
    ORDINAL_POSITION;

-- ============================================================================
-- SUMMARY OF CHANGES
-- ============================================================================
/*
COLUMNS ADDED TO ALL 3 RECOVERY TABLES:
1. mailgun_message_id          - Mailgun API message ID for webhook matching
2. email_clicked                - Whether any link was clicked
3. email_click_count            - Number of times links were clicked
4. email_first_clicked_at       - First click timestamp
5. email_last_clicked_at        - Most recent click timestamp
6. email_bounced                - Whether email bounced
7. email_bounced_at             - Bounce timestamp
8. email_bounce_reason          - Reason for bounce
9. email_delivered              - Whether email was delivered to inbox
10. email_delivered_at          - Delivery timestamp
11. email_complained            - Whether marked as spam
12. email_complained_at         - Spam report timestamp
13. email_unsubscribed          - Whether user unsubscribed
14. email_unsubscribed_at       - Unsubscribe timestamp
15. email_failed                - Whether delivery failed
16. email_failed_at             - Failure timestamp
17. email_failure_reason        - Reason for failure

ADDITIONAL FOR certificate_payment_recoveries ONLY:
18. user_viewed_page            - Whether user viewed payment page
19. user_viewed_at              - When user viewed payment page
20. status ENUM updated         - Added 'user_viewed' state

TOTAL: 17 columns per table + 2 extra for certificates = 53 total columns added
INDEXES: 6 per table = 18 total indexes added
*/
