-- ============================================================================
-- PAYMENT RECOVERY TABLES - ADD PAYMENT TRACKING COLUMNS
-- ============================================================================
-- Purpose: Add new_payment_id column to track successful payments
--          and seed with real-time data from payments table
-- Date: 2025-12-20
-- Usage: Import this file in phpMyAdmin or run via mysql command line
-- ============================================================================

-- ============================================================================
-- STEP 1: ALTER TABLES - ADD MISSING COLUMNS
-- ============================================================================

-- Add columns to uid_registration_recoveries
ALTER TABLE `uid_registration_recoveries` 
ADD COLUMN `new_payment_id` BIGINT UNSIGNED NULL AFTER `login_count_after_email`,
ADD COLUMN `payment_completed_at` TIMESTAMP NULL AFTER `new_payment_id`,
ADD INDEX `idx_new_payment_id` (`new_payment_id`);

-- Add foreign key constraint for uid_registration_recoveries
ALTER TABLE `uid_registration_recoveries`
ADD CONSTRAINT `fk_uid_recovery_new_payment` 
FOREIGN KEY (`new_payment_id`) REFERENCES `payments` (`id`) ON DELETE SET NULL;

-- Add columns to membership_renewal_recoveries
ALTER TABLE `membership_renewal_recoveries` 
ADD COLUMN `new_payment_id` BIGINT UNSIGNED NULL AFTER `login_count_after_email`,
ADD COLUMN `payment_completed_at` TIMESTAMP NULL AFTER `new_payment_id`,
ADD INDEX `idx_new_payment_id` (`new_payment_id`);

-- Add foreign key constraint for membership_renewal_recoveries
ALTER TABLE `membership_renewal_recoveries`
ADD CONSTRAINT `fk_renewal_recovery_new_payment` 
FOREIGN KEY (`new_payment_id`) REFERENCES `payments` (`id`) ON DELETE SET NULL;

-- Add columns to certificate_payment_recoveries
ALTER TABLE `certificate_payment_recoveries` 
ADD COLUMN `new_payment_id` BIGINT UNSIGNED NULL AFTER `login_count_after_email`,
ADD COLUMN `payment_completed_at` TIMESTAMP NULL AFTER `new_payment_id`,
ADD INDEX `idx_new_payment_id` (`new_payment_id`);

-- Add foreign key constraint for certificate_payment_recoveries
ALTER TABLE `certificate_payment_recoveries`
ADD CONSTRAINT `fk_certificate_recovery_new_payment` 
FOREIGN KEY (`new_payment_id`) REFERENCES `payments` (`id`) ON DELETE SET NULL;

-- ============================================================================
-- STEP 2: SEED DATA - MATCH WITH SUCCESSFUL PAYMENTS
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 2.1: UID REGISTRATION RECOVERIES - Match with successful payments
-- ----------------------------------------------------------------------------
UPDATE `uid_registration_recoveries` urr
INNER JOIN `payments` p ON p.user_id = urr.user_id
SET 
    urr.new_payment_id = p.id,
    urr.payment_completed_at = p.updated_at,
    urr.status = 'payment_completed'
WHERE 
    p.status IN ('captured', 'authorized', 'success')
    AND p.created_at > urr.created_at  -- Payment made after recovery email sent
    AND urr.new_payment_id IS NULL  -- Only update records that haven't been updated
    AND p.id = (
        -- Get the latest successful payment for this user after recovery was sent
        SELECT MAX(p2.id)
        FROM `payments` p2
        WHERE p2.user_id = urr.user_id
            AND p2.status IN ('captured', 'authorized', 'success')
            AND p2.created_at > urr.created_at
    );

-- ----------------------------------------------------------------------------
-- 2.2: MEMBERSHIP RENEWAL RECOVERIES - Match with successful payments
-- ----------------------------------------------------------------------------
UPDATE `membership_renewal_recoveries` mrr
INNER JOIN `payments` p ON p.user_id = mrr.user_id
SET 
    mrr.new_payment_id = p.id,
    mrr.payment_completed_at = p.updated_at,
    mrr.status = 'payment_completed'
WHERE 
    p.status IN ('captured', 'authorized', 'success')
    AND p.created_at > mrr.created_at  -- Payment made after recovery email sent
    AND mrr.new_payment_id IS NULL  -- Only update records that haven't been updated
    AND p.id = (
        -- Get the latest successful payment for this user after recovery was sent
        SELECT MAX(p2.id)
        FROM `payments` p2
        WHERE p2.user_id = mrr.user_id
            AND p2.status IN ('captured', 'authorized', 'success')
            AND p2.created_at > mrr.created_at
    );

-- ----------------------------------------------------------------------------
-- 2.3: CERTIFICATE PAYMENT RECOVERIES - Match with successful payments
-- ----------------------------------------------------------------------------
UPDATE `certificate_payment_recoveries` cpr
INNER JOIN `payments` p ON p.user_id = cpr.user_id
SET 
    cpr.new_payment_id = p.id,
    cpr.payment_completed_at = p.updated_at,
    cpr.status = 'payment_completed'
WHERE 
    p.status IN ('captured', 'authorized', 'success')
    AND p.created_at > cpr.created_at  -- Payment made after recovery email sent
    AND cpr.new_payment_id IS NULL  -- Only update records that haven't been updated
    AND p.id = (
        -- Get the latest successful payment for this user after recovery was sent
        SELECT MAX(p2.id)
        FROM `payments` p2
        WHERE p2.user_id = cpr.user_id
            AND p2.status IN ('captured', 'authorized', 'success')
            AND p2.created_at > cpr.created_at
    );

-- ============================================================================
-- STEP 3: VERIFICATION QUERIES - CHECK RESULTS
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 3.1: UID REGISTRATION RECOVERIES - Payment Statistics
-- ----------------------------------------------------------------------------
SELECT 
    '=== UID REGISTRATION RECOVERY STATISTICS ===' AS report_section,
    NULL AS total_recoveries,
    NULL AS payments_received,
    NULL AS still_pending,
    NULL AS conversion_rate;

SELECT 
    'Summary' AS report_section,
    COUNT(*) AS total_recoveries,
    SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) AS payments_received,
    SUM(CASE WHEN new_payment_id IS NULL THEN 1 ELSE 0 END) AS still_pending,
    CONCAT(
        ROUND(
            (SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 
            2
        ), 
        '%'
    ) AS conversion_rate
FROM `uid_registration_recoveries`;

-- Detailed breakdown by status
SELECT 
    'By Status' AS report_section,
    status,
    COUNT(*) AS count,
    CONCAT(ROUND((COUNT(*) * 100.0 / (SELECT COUNT(*) FROM uid_registration_recoveries)), 2), '%') AS percentage
FROM `uid_registration_recoveries`
GROUP BY status
ORDER BY count DESC;

-- Recent successful payments (last 30 days)
SELECT 
    'Recent Payments (Last 30 Days)' AS report_section,
    COUNT(*) AS recent_payments,
    SUM(amount) AS total_amount_recovered
FROM `uid_registration_recoveries`
WHERE new_payment_id IS NOT NULL
    AND payment_completed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- ----------------------------------------------------------------------------
-- 3.2: MEMBERSHIP RENEWAL RECOVERIES - Payment Statistics
-- ----------------------------------------------------------------------------
SELECT 
    '=== MEMBERSHIP RENEWAL RECOVERY STATISTICS ===' AS report_section,
    NULL AS total_recoveries,
    NULL AS payments_received,
    NULL AS still_pending,
    NULL AS conversion_rate;

SELECT 
    'Summary' AS report_section,
    COUNT(*) AS total_recoveries,
    SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) AS payments_received,
    SUM(CASE WHEN new_payment_id IS NULL THEN 1 ELSE 0 END) AS still_pending,
    CONCAT(
        ROUND(
            (SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 
            2
        ), 
        '%'
    ) AS conversion_rate
FROM `membership_renewal_recoveries`;

-- Detailed breakdown by status
SELECT 
    'By Status' AS report_section,
    status,
    COUNT(*) AS count,
    CONCAT(ROUND((COUNT(*) * 100.0 / (SELECT COUNT(*) FROM membership_renewal_recoveries)), 2), '%') AS percentage
FROM `membership_renewal_recoveries`
GROUP BY status
ORDER BY count DESC;

-- Recent successful payments (last 30 days)
SELECT 
    'Recent Payments (Last 30 Days)' AS report_section,
    COUNT(*) AS recent_payments,
    SUM(amount) AS total_amount_recovered
FROM `membership_renewal_recoveries`
WHERE new_payment_id IS NOT NULL
    AND payment_completed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- ----------------------------------------------------------------------------
-- 3.3: CERTIFICATE PAYMENT RECOVERIES - Payment Statistics
-- ----------------------------------------------------------------------------
SELECT 
    '=== CERTIFICATE PAYMENT RECOVERY STATISTICS ===' AS report_section,
    NULL AS total_recoveries,
    NULL AS payments_received,
    NULL AS still_pending,
    NULL AS conversion_rate;

SELECT 
    'Summary' AS report_section,
    COUNT(*) AS total_recoveries,
    SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) AS payments_received,
    SUM(CASE WHEN new_payment_id IS NULL THEN 1 ELSE 0 END) AS still_pending,
    CONCAT(
        ROUND(
            (SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 
            2
        ), 
        '%'
    ) AS conversion_rate
FROM `certificate_payment_recoveries`;

-- Detailed breakdown by status
SELECT 
    'By Status' AS report_section,
    status,
    COUNT(*) AS count,
    CONCAT(ROUND((COUNT(*) * 100.0 / (SELECT COUNT(*) FROM certificate_payment_recoveries)), 2), '%') AS percentage
FROM `certificate_payment_recoveries`
GROUP BY status
ORDER BY count DESC;

-- Recent successful payments (last 30 days)
SELECT 
    'Recent Payments (Last 30 Days)' AS report_section,
    COUNT(*) AS recent_payments,
    SUM(amount) AS total_amount_recovered
FROM `certificate_payment_recoveries`
WHERE new_payment_id IS NOT NULL
    AND payment_completed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- ----------------------------------------------------------------------------
-- 3.4: OVERALL RECOVERY STATISTICS - ALL TYPES COMBINED
-- ----------------------------------------------------------------------------
SELECT 
    '=== OVERALL RECOVERY STATISTICS ===' AS report_section,
    NULL AS recovery_type,
    NULL AS total,
    NULL AS paid,
    NULL AS pending,
    NULL AS conversion_rate;

SELECT 
    'Overall Summary' AS report_section,
    'UID Registration' AS recovery_type,
    COUNT(*) AS total,
    SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) AS paid,
    SUM(CASE WHEN new_payment_id IS NULL THEN 1 ELSE 0 END) AS pending,
    CONCAT(ROUND((SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 2), '%') AS conversion_rate
FROM `uid_registration_recoveries`
UNION ALL
SELECT 
    'Overall Summary',
    'Membership Renewal',
    COUNT(*),
    SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END),
    SUM(CASE WHEN new_payment_id IS NULL THEN 1 ELSE 0 END),
    CONCAT(ROUND((SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 2), '%')
FROM `membership_renewal_recoveries`
UNION ALL
SELECT 
    'Overall Summary',
    'Certificate Payment',
    COUNT(*),
    SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END),
    SUM(CASE WHEN new_payment_id IS NULL THEN 1 ELSE 0 END),
    CONCAT(ROUND((SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 2), '%')
FROM `certificate_payment_recoveries`;

-- Grand totals
SELECT 
    'Grand Total' AS report_section,
    'All Recovery Types' AS recovery_type,
    SUM(total) AS total,
    SUM(paid) AS paid,
    SUM(pending) AS pending,
    CONCAT(ROUND((SUM(paid) * 100.0 / SUM(total)), 2), '%') AS overall_conversion_rate
FROM (
    SELECT 
        COUNT(*) AS total,
        SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) AS paid,
        SUM(CASE WHEN new_payment_id IS NULL THEN 1 ELSE 0 END) AS pending
    FROM `uid_registration_recoveries`
    UNION ALL
    SELECT 
        COUNT(*),
        SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END),
        SUM(CASE WHEN new_payment_id IS NULL THEN 1 ELSE 0 END)
    FROM `membership_renewal_recoveries`
    UNION ALL
    SELECT 
        COUNT(*),
        SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END),
        SUM(CASE WHEN new_payment_id IS NULL THEN 1 ELSE 0 END)
    FROM `certificate_payment_recoveries`
) AS combined;

-- ----------------------------------------------------------------------------
-- 3.5: USERS WITH SUCCESSFUL PAYMENT RECOVERY (Sample List)
-- ----------------------------------------------------------------------------
SELECT 
    '=== SAMPLE: USERS WHO COMPLETED PAYMENT AFTER RECOVERY EMAIL ===' AS report_section,
    NULL AS id,
    NULL AS user_id,
    NULL AS recovery_type,
    NULL AS amount,
    NULL AS email_sent_at,
    NULL AS payment_completed_at,
    NULL AS days_to_convert;

SELECT 
    'Successful Recoveries' AS report_section,
    urr.id,
    urr.user_id,
    'UID Registration' AS recovery_type,
    urr.amount,
    urr.email_sent_at,
    urr.payment_completed_at,
    DATEDIFF(urr.payment_completed_at, urr.email_sent_at) AS days_to_convert
FROM `uid_registration_recoveries` urr
WHERE urr.new_payment_id IS NOT NULL
ORDER BY urr.payment_completed_at DESC
LIMIT 10;

-- ============================================================================
-- STEP 4: MONITORING QUERY - RUN REGULARLY TO TRACK PROGRESS
-- ============================================================================

-- Create a view for easy monitoring
CREATE OR REPLACE VIEW v_payment_recovery_dashboard AS
SELECT 
    'UID Registration' AS recovery_type,
    COUNT(*) AS total_sent,
    SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) AS payments_received,
    SUM(CASE WHEN new_payment_id IS NULL THEN 1 ELSE 0 END) AS still_pending,
    SUM(CASE WHEN new_payment_id IS NOT NULL THEN amount ELSE 0 END) AS amount_recovered,
    SUM(CASE WHEN new_payment_id IS NULL THEN amount ELSE 0 END) AS amount_at_risk,
    ROUND((SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 2) AS conversion_rate
FROM `uid_registration_recoveries`
UNION ALL
SELECT 
    'Membership Renewal',
    COUNT(*),
    SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END),
    SUM(CASE WHEN new_payment_id IS NULL THEN 1 ELSE 0 END),
    SUM(CASE WHEN new_payment_id IS NOT NULL THEN amount ELSE 0 END),
    SUM(CASE WHEN new_payment_id IS NULL THEN amount ELSE 0 END),
    ROUND((SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 2)
FROM `membership_renewal_recoveries`
UNION ALL
SELECT 
    'Certificate Payment',
    COUNT(*),
    SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END),
    SUM(CASE WHEN new_payment_id IS NULL THEN 1 ELSE 0 END),
    SUM(CASE WHEN new_payment_id IS NOT NULL THEN amount ELSE 0 END),
    SUM(CASE WHEN new_payment_id IS NULL THEN amount ELSE 0 END),
    ROUND((SUM(CASE WHEN new_payment_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 2)
FROM `certificate_payment_recoveries`;

-- Usage: SELECT * FROM v_payment_recovery_dashboard;

-- ============================================================================
-- COMPLETION MESSAGE
-- ============================================================================
SELECT '============================================================================' AS message
UNION ALL SELECT 'PAYMENT TRACKING SETUP COMPLETE!'
UNION ALL SELECT '============================================================================'
UNION ALL SELECT ''
UNION ALL SELECT 'What was done:'
UNION ALL SELECT '1. ✓ Added new_payment_id and payment_completed_at columns to all recovery tables'
UNION ALL SELECT '2. ✓ Matched existing recovery records with successful payments from payments table'
UNION ALL SELECT '3. ✓ Updated status to payment_completed for users who paid after receiving recovery email'
UNION ALL SELECT '4. ✓ Created monitoring view: v_payment_recovery_dashboard'
UNION ALL SELECT ''
UNION ALL SELECT 'Next Steps:'
UNION ALL SELECT '- Review the statistics above to understand current recovery performance'
UNION ALL SELECT '- Use the payment_received filter in admin panel to segment paid vs pending users'
UNION ALL SELECT '- Monitor the dashboard: SELECT * FROM v_payment_recovery_dashboard;'
UNION ALL SELECT '- Focus follow-up efforts on users with new_payment_id = NULL (still pending)'
UNION ALL SELECT ''
UNION ALL SELECT '============================================================================';

-- ============================================================================
-- END OF SCRIPT
-- ============================================================================
